V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
James369
V2EX  ›  程序员

数据库多条件查询索引如何设计比较好?

  •  
  •   James369 · 2023-09-25 23:28:09 +08:00 · 1422 次点击
    这是一个创建于 415 天前的主题,其中的信息可能已经有所发展或是发生改变。

    SQLite 中有一张表 Table ,大约 30 个字段,该表不超过 50 万条数据。

    现要做一个查询,其中条件涉及到 6 个字段信息:

    Select * from Table 
    Where (f1==true || f2>0 || f3>0) && f4==true && f5!=null && f6==false
    

    这种情况如何设计索引比较好?

    第 1 条附言  ·  2023-09-27 00:01:43 +08:00
    其实我的 Where 下有一个 Order 字段的语句,忘记写出来了。
    最终我妥协了,把 Where 条件下的 Order 语句删除了,查询速度直接从秒级 下降到毫秒级。
    第 2 条附言  ·  2023-09-27 00:02:57 +08:00
    另外,我还想到一个办法,就是定期把一些数据归档到另外一张表中,剥离出来
    10 条回复    2023-09-26 22:07:57 +08:00
    Woood
        1
    Woood  
       2023-09-26 01:13:22 +08:00
    根据逻辑维护一个单独的字段然后给它索引。
    iseki
        2
    iseki  
       2023-09-26 08:04:02 +08:00 via Android
    条件是死的么,不知道 SQLite 有没有这种功能,尝试创建一个表达式索引
    andytao
        3
    andytao  
       2023-09-26 08:12:50 +08:00
    1 、如果这些查询条件是常态化的,可以把他们组合在一个索引实例里面,约重要的靠前;
    2 、如果主要用某一两个,就建包含这两个字段的索引就好了
    3 、一些非关键的索引,客户端内存索引是一个更快的选择;

    [袋鼠数据库工具] 持续创新,欢迎体验。。。
    dw2693734d
        4
    dw2693734d  
       2023-09-26 08:42:23 +08:00
    为了提高查询性能,你可以创建组合索引或多个单列索引。但是,需要考虑到 SQLite 的查询优化器可能不会使用多个索引。

    1. 选择被最频繁用作过滤条件的字段作为组合索引的第一列。
    2. 在涉及范围查询(如 `>` 或 `<`)的字段上创建单独的索引可能更有用。

    假设 `f4` 和 `f6` 是最常用于过滤的字段,可能的索引设计如下:

    ```sql
    -- 组合索引
    CREATE INDEX idx_f4_f6 ON Table (f4, f6);

    -- 单列索引
    CREATE INDEX idx_f1 ON Table (f1);
    CREATE INDEX idx_f2 ON Table (f2);
    CREATE INDEX idx_f3 ON Table (f3);
    CREATE INDEX idx_f5 ON Table (f5);
    ```

    注意:这只是一个例子,实际效果需要通过执行计划和性能测试来验证。
    gaifanking
        5
    gaifanking  
       2023-09-26 09:01:06 +08:00
    要明白最左前缀原理,另外 true false 这种建索引性能不好,f2 f3 可以。建议如下:
    1 修改 sql ,f2 f3 这种范围比较的放前面,true false null 判断放后面
    2 修改后从左边开始任意选择连续的 n 个字段建立索引即可。
    sweat89
        6
    sweat89  
       2023-09-26 15:32:24 +08:00   ❤️ 1
    @dw2693734d 别用 chatgpt 来回复,会被封号的
    dw2693734d
        7
    dw2693734d  
       2023-09-26 18:00:46 +08:00
    @sweat89 多谢大哥提醒
    James369
        8
    James369  
    OP
       2023-09-26 20:33:31 +08:00
    @dw2693734d 为何和我查的结果不大一样,先试试,呆会再来汇报
    James369
        9
    James369  
    OP
       2023-09-26 20:37:23 +08:00
    @iseki 条件是死的,还真有表达式索引 https://www.sqlite.org/expridx.html
    iseki
        10
    iseki  
       2023-09-26 22:07:57 +08:00 via Android
    @James369 大大小小数据库,可能也就 MySQL 要啥没啥了
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4511 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 24ms · UTC 04:06 · PVG 12:06 · LAX 20:06 · JFK 23:06
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.