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
zealinux
V2EX  ›  MySQL

MySQL 该不该给 status 添加索引?

  •  
  •   zealinux · 2020-04-28 18:47:02 +08:00 · 4499 次点击
    这是一个创建于 585 天前的主题,其中的信息可能已经有所发展或是发生改变。

    现在某个表,有个 status,有两个值,0,1

    按照好多文档说明,这列不应该加索引。

    比如,0 是未处理的,1 是处理过的。

    一般都是要查 0,来进行处理。

    发现 status 加了索引之后,查找的速度飞快。

    2000w 的表,SELECT COUNT(*), 从 14s 下降到 0.1s 以下( SQL_NO_CACHE )

    26 条回复    2020-04-29 23:06:10 +08:00
    GTim
        1
    GTim  
       2020-04-28 18:53:21 +08:00
    加了索引,`select count(*)` 走的是索引,索引比起原表那真是小太多了
    gotonull
        2
    gotonull  
       2020-04-28 18:53:51 +08:00
    status 加索引对 count 有影响?不懂,插眼。等一个大佬解释
    virusdefender
        3
    virusdefender  
       2020-04-28 18:55:46 +08:00
    status 加索引对 count 有影响?+1

    看下 explain 的结果
    yumenawei
        4
    yumenawei  
       2020-04-28 18:57:37 +08:00
    这个是特例。
    你随便查点需要回表的数据试试。
    sadfQED2
        5
    sadfQED2  
       2020-04-28 19:01:54 +08:00 via Android
    Count 操作不会回表,你试试 select *
    xuanbg
        6
    xuanbg  
       2020-04-28 19:19:14 +08:00
    这种看情况,索引就是使无序的数据变得有序,相当于让乱糟糟的一群人按某种特性排起了整齐的队伍。如果你需要找的人在队伍里面占大多数,那这个队伍没多大意思。如果你要找的是队伍里面的少数人,那效率提升就是杠杠的。
    xinshoushanglu
        7
    xinshoushanglu  
       2020-04-28 19:24:50 +08:00
    count 应该是在 mysql.sys 表里记录的一个量,和数据表的规模没关系。我之前一张一千多万的表,查都查不动,但是 count(1)或者 count(*) 还是秒出
    encro
        8
    encro  
       2020-04-28 19:32:18 +08:00   ❤️ 2
    索引对于量少的数据有效。

    比如你表数据绝大部分都是 status=0 的,
    但是你查询是 status=1 的数据,这时候索引有效的,也是很快的,
    如果你查 status=0,因为数据量超过了总数一般,那么索引无效。
    glacer
        9
    glacer  
       2020-04-28 20:36:13 +08:00
    加索引有效的原因是 0 和 1 的数据量不均等吧。如果 0 只占很小一部分,status=0 的时候当然很快
    PopRain
        10
    PopRain  
       2020-04-28 20:38:22 +08:00
    @xinshoushanglu 人家是做条件 count, 不是统计全表的行数。 状态值用索引快是因为一个 Page 可以保存更多的记录,就算走索引全扫描,所需要的 I/O 比表扫描也少的多
    napsterwu
        11
    napsterwu  
       2020-04-28 20:41:43 +08:00 via iPhone
    区分度不大的字段加索引效果不大吧
    wdlth
        12
    wdlth  
       2020-04-28 20:45:57 +08:00
    应该与其他的字段一起加联合索引,单独加不一定能用上。
    gongwenhua
        13
    gongwenhua  
       2020-04-28 21:23:29 +08:00
    截个图看看 explain ?
    daozhihun
        14
    daozhihun  
       2020-04-28 21:25:29 +08:00
    不建议加索引,筛选的行数太少,系统很可能会忽略掉
    cheng6563
        15
    cheng6563  
       2020-04-28 21:27:01 +08:00
    这算是个特殊用法吧
    jam1024
        16
    jam1024  
       2020-04-28 21:50:40 +08:00
    建议加组合索引,不建议加单独索引
    Orenoid
        17
    Orenoid  
       2020-04-28 21:57:25 +08:00 via Android
    status 这种选择性很低的列,单独加索引收益很低
    love
        18
    love  
       2020-04-28 21:59:51 +08:00
    这个状态很少的列要看数据分布的。如果是分布很平均就不用加。
    dongisking
        19
    dongisking  
       2020-04-28 22:00:52 +08:00
    不建议,建议分表
    uxff
        20
    uxff  
       2020-04-28 22:20:29 +08:00
    加索引快是因为直接读索引就能统计到条数。而不需要再读表内容,减少了 io 次数。不加索引会增加额外的 io 开销。
    读完索引再读表内容一般至少多加一次 io,除非表很小(在 B+数的一簇以内)。
    如果不加索引,且 status=0 的值很多,分散在不同的簇上,那就很慢,需要很多次 io 。
    uxff
        21
    uxff  
       2020-04-28 22:23:15 +08:00
    看了 mysql 的实现原理后,你就懂了,mysql innodb 索引是针对现代磁盘高度优化的。只统计或查询索引上的字段都会快很多。
    littlewing
        22
    littlewing  
       2020-04-28 23:24:18 +08:00
    count(*) count(1) count(status) 确实是有用的,因为 MySQL 判断扫描 status 索引也能得到正确的 count,而 status 索引比其他索引和主键索引扫描起来快多了
    但是如果想用 status 索引加快 select * from xxx where status = 1 这样的查找速度的话,就不用想了,MySQL 绝对会选择扫描主键索引,因为 cardinality 太小了,再加上回表的开销,MySQL 会认为走主键索引更快
    littlewing
        23
    littlewing  
       2020-04-28 23:25:54 +08:00
    @xinshoushanglu innodb 是不能这样做的,因为有事务和隔离级别的存在,同一时间不同事务看到的 count 可以不一样,Myisam 是可以这样做的
    hooopo
        24
    hooopo  
       2020-04-29 00:34:18 +08:00
    具体情况具体分析
    thinkmore
        25
    thinkmore  
       2020-04-29 16:54:37 +08:00
    不建议加。status 区分度太低了,基本全是扫描叶子节点。

    如果你查这样的,你就能感受到了

    select column_1,column_2,column_3 from table where status = 1
    qyvlik
        26
    qyvlik  
       2020-04-29 23:06:10 +08:00
    根据业务要求加,就是根据哪种 sql 查询多的去加索引,还要根据具体 sql 查询拼接的条件。
    所谓 status 区分度低的前提是 0,1 均匀分布,说白了可能各 50%,这样加索引就没啥效果。按照你的问题描述,以及可能的常用场景,0 和 1 的分布不是均匀的,一般是 0 >> 1,就是说 status 为 0 可能就是全表记录的 一小部分(例如 1%),那么如下的查询 sql:
    select * from table where status = 0
    就会走索引,而且索引扫描的行数也不会太多。
    但是如下的 sql:
    select * from table where status = 1,可能会走索引,但是扫描的行数过多时会让 mysql 做出扫描全表的执行计划。
    select count(*) from table,在有索引的情况,可能会走索引,具体看看执行计划。
    关于   ·   帮助文档   ·   API   ·   FAQ   ·   我们的愿景   ·   广告投放   ·   感谢   ·   实用小工具   ·   2282 人在线   最高记录 5497   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 22ms · UTC 07:56 · PVG 15:56 · LAX 23:56 · JFK 02:56
    ♥ Do have faith in what you're doing.