10 分钟让你明白MySQL 索引的使用原则

MySQL索引的使用原则主要包括最左前缀匹配、合理选择索引列、避免索引失效场景、尽量扩展索引等，以下为具体说明：

最左前缀匹配原则
定义：MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。例如，对于索引(a,b,c,d)，查询条件a = 1 and b = 2 and c > 3 and d = 4，d 无法使用索引；若索引为(a,b,d,c)，则所有条件均可使用索引，a、b、d 的顺序可任意调整。
示例：在查询语句WHERE a = 1 AND b > 2 AND c = 3中，若索引为(a,b,c)，则a和b能利用索引，c无法利用；若索引为(a,c,b)，则只有a能利用索引。

=和in可以乱序
定义：在查询条件中，=和in条件的顺序可以任意，MySQL的查询优化器会自动优化成索引可以识别的形式。例如，对于索引(a,b,c)，查询条件a = 1 and b = 2 and c = 3，无论写成a = 1 and b = 2 and c = 3、b = 2 and a = 1 and c = 3还是其他顺序，都能使用索引。
示例：在查询语句WHERE b = 2 AND a = 1 AND c = 3中，若索引为(a,b,c)，优化器会调整顺序，使查询能利用索引。
尽量选择区分度高的列作为索引
定义：区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例。比例越大，扫描的记录数越少。唯一键的区分度是1，而一些状态、性别字段在大规模数据面前区分度可能为0。一般需要join的字段要求区分度在0.1以上，即平均扫描10条记录找到1条符合条件的记录。
示例：在一个用户表中，user_id是唯一键，区分度为1，适合作为索引；而gender字段只有“男”和“女”两个值，区分度低，不适合单独作为索引。
索引列不能参与计算
定义：保持索引列“干净”，避免在索引列上进行计算或函数操作。因为B+树中存储的是数据表中的字段值，若进行检索时需要对所有元素应用函数才能比较，成本太大。
示例：查询语句from_unixtime(create_time) = '2014-05-29'无法使用索引，应改为create_time = unix_timestamp('2014-05-29')。

尽量扩展索引，不要新建索引
定义：如果表中已有某个列的索引，现在要添加包含该列和其他列的复合索引，只需修改原来的索引即可，避免新建索引增加维护成本和空间占用。
示例：表中已有索引(a)，现在要添加索引(a,b)，只需将原索引修改为(a,b)，而不是新建一个(a,b)索引。
其他能用到索引的条件
复合索引遵循最左匹配原则：对于复合索引，查询条件必须从索引的最左列开始，否则索引无法生效。例如，索引(a,b,c)，查询条件WHERE b = 1 AND c = 2无法使用索引。
范围查询后的列无法使用索引：在复合索引中，如果查询条件包含范围查询（如>、<、between、like等），则范围查询后的列无法使用索引。例如，索引(a,b,c)，查询条件WHERE a = 1 AND b > 2 AND c = 3，c无法使用索引。
使用覆盖索引避免回表：如果查询的列都包含在索引中，无需回表查询数据行，可提高查询效率。例如，索引(a,b)，查询语句SELECT a, b FROM table WHERE a = 1，可直接从索引中获取数据，无需回表。

热门标签