V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
rqxiao
V2EX  ›  MySQL

MySQL 在数据区分度不高的字段上加了索引,并且总条数量大的表,有 where 条件时的 count(*)出现了走索引比不加索引时快的情况

  •  
  •   rqxiao · 2022-08-31 16:53:02 +08:00 · 2258 次点击
    这是一个创建于 813 天前的主题,其中的信息可能已经有所发展或是发生改变。
    库里单表 tb_task 5000w

    SELECT
    is_deleted,
    data_type,
    COUNT(*)
    FROM
    tb_task
    GROUP BY
    is_deleted,
    data_type



    is_deleted data_type COUNT(*)
    0 0 ---701
    0 1 ---10
    0 2 --- 2575
    0 127 --- 50000011


    SELECT COUNT(*) FROM tb_task WHERE is_deleted = 0 AND data_type != 1

    没有加索引 idx (`data_type`, `is_deleted` )时, 要 60 秒

    加了 idx (`data_type`, `is_deleted` )时, 这条 sql 会走索引 变成 17 秒

    id select_type table partitions type possible_keys key key_len ref rows filtered Extra
    1 SIMPLE tb_task range idx_data_type_is_deleted idx_data_type_is_deleted 2 24671413 10.00 Using where; Using index


    这是为啥,不是说区分度不高的列上加索引 ,查询效率不明显吗
    12 条回复    2022-09-01 00:02:41 +08:00
    zmal
        1
    zmal  
       2022-08-31 17:19:14 +08:00   ❤️ 1
    这么多点击没人回答,估计是懒得回答吧,有点基础···
    搜索下什么是“回表”。有 idx (`data_type`, `is_deleted` )时,where is_deleted = 0 AND data_type != 1 的 count(*)不需要回表,只通过索引就能完成统计。
    区分度低的索引查询慢说的是有回表的情况。
    sujin190
        2
    sujin190  
       2022-08-31 17:34:51 +08:00   ❤️ 1
    其实就是虽然都需要全表扫,但是你这个不需要查询除索引外的字段,所以直接在索引上统计就行,且不说索引数据量小了很多,而且大概率索引会在内存中,所以快一点很正常,你 SELECT 加个不在索引中的字段触发回表,你就会发现效率差不多了

    区分度不高的列上加索引查询效率不明显,这个主要问题是这个字段添加过滤条件后,需要扫描的数据条数几乎和不加索引差距不大,所以并不能明显提高效率,更不要说你这个地方都没有添加任何过滤条件,无论怎么着都要扫描所有数据
    rqxiao
        3
    rqxiao  
    OP
       2022-08-31 17:40:48 +08:00
    @zmal 不好意思 ,意思是 只要是走索引的 count(*) 就是比 全表扫描的 count(*) 快是吗。虽然两者有可能都是要找 5000w 条
    rqxiao
        4
    rqxiao  
    OP
       2022-08-31 17:50:11 +08:00
    @sujin190 不好意思。意思是 count(*) 如果是 只走索引不回表 或者 全表扫描 ,虽然都有可能是 5000w 的记录数,但因为索引 b 树结构的特点(非叶子节点只存索引数据),和全表扫描比,走索引不回表这种情况明显的快是么。
    ComTNT
        5
    ComTNT  
       2022-08-31 17:53:24 +08:00   ❤️ 2
    推荐你本书,sql 优化核心原理,微信读书上有,第一章就能把你这个问题讲清楚,虽然这本书是基于 oralce 讲的,但是核心思想适用于大部分 db
    ComTNT
        6
    ComTNT  
       2022-08-31 17:53:55 +08:00   ❤️ 1
    @ComTNT 打错了,是 sql 优化核心思想
    sujin190
        7
    sujin190  
       2022-08-31 17:59:33 +08:00   ❤️ 1
    @rqxiao #4 是的,索引应该是只保存索引字段和主键的值,磁盘 IO 会少很多,而且一般来说索引应该会尽可能保存在内存中,这也可以快一点吧
    LeegoYih
        8
    LeegoYih  
       2022-08-31 18:07:39 +08:00
    遇事不决看执行计划

    set optimizer_trace="enabled=on";
    select count(*) from tb_task where is_deleted = 0 and data_type != 1;
    select * from information_schema.optimizer_trace;
    set optimizer_trace="enabled=off";
    CEBBCAT
        9
    CEBBCAT  
       2022-08-31 21:01:57 +08:00
    这个排版真是无力吐槽……
    LuckyLight
        10
    LuckyLight  
       2022-08-31 22:05:45 +08:00
    不得不说,很多文章都是误人子弟,不看实际场景就给结论都是耍流氓。“区分度不高的列上加索引 ,查询效率不明显” 也得看具体业务是什么。
    GopherDaily
        11
    GopherDaily  
       2022-08-31 23:51:35 +08:00
    如果查询需要在字段都在 index 里面,可以避免回源。

    用 Explain ,看到不理解的 google ,这是正道
    reter
        12
    reter  
       2022-09-01 00:02:41 +08:00 via Android
    就我所看过数据库的文档,就算有了索引,数据库还会采集数据的元数据做辅助判断是否使用索引。用 explain 查看数据库实际的执行计划才是正解
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   2959 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 25ms · UTC 14:37 · PVG 22:37 · LAX 06:37 · JFK 09:37
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.