索引优化三大原则

原作者: 南寒之星

原文地址: 索引优化三大原则

建索引的几大原则

对于索引我总结三大原则：

• 1、最左前缀原则；
• 2、不冗余原则；
• 3、最大选择性原则。

基本掌握这三条，对于索引的优化理论上是没有问题了。

最左前缀原则

一般在 where 条件中两个及以上字段时，我们会建联合索引。 高效使用索引的首要条件是知道什么样的查询会使用到索引，这个问题和 B+Tree 中的“最左前缀原理”有关，下面通过例子说明最左前缀原理。 MySQL 中的索引可以以一定顺序引用多个列，这种索引叫做联合索引，一般的，一个联合索引是一个有序元组<a1,a2,a3…an>，其中各个元素均为数据表的一列，实际上要严格定义索引需要用到关系代数。另外，单列索引可以看成联合索引元素数为 1 的特例 最左前缀是一个很重要的原则。 mysql 会从左至右匹配，直到遇到范围查找(> < like between)就停止。 如： select * from table1 where a=1 and b=2 and c<3 and d=9 ; 建立的联合索引为：(a,b,c,d) 实际使用的索引为(a,b,c)。因为遇到了 c<3 就停止了，d 列就没有用上。 前面讲过联合索引是有序元组， 则 mysql 实际建的索引为：(a) (a,b) (a,b,c) (a,b,c,d)。 举个例子：where b=2 and c=3 and d=9 ；按照最左匹配原则，这个条件就没法走索引了，首先必须有 a。 =,in 可以乱序，查询优化器会帮你优化成索引可以识别的形式。也就是说，where b=2 and a=1 and c<3 使用的索引任然为(a,b,c)组合。 回到线上案例： 索引:idx_whid_distributionorderid(wh_id,distribution_order_id) 索引组合 (wh_id) ,(wh_id,distribution_order_id) 相当于建了一个 wh_id 的单列索引，也就是说当你要根据 wh_id 查询时，是不需要再新建索引了。

不冗余原则

尽量扩展索引、不要新建索引 mysql 目前主要索引有：FULLTEXT,HASH,BTREE 好的索引可以提高我们的查询效率，不好的索引不但不会起作用，反而给 DB 带来负担，基于 BTREE 结构，插入、修改都会重新调整索引结构，存储成本增加，写效率降低，同时 DB 系统也要消耗资源去维护。 基于刚才的最左匹配原则，尽量在原有基础上扩展索引，不要新增索引。 能用单索引，不用联合索引；能用窄索引，不用宽索引；能复用索引，不新建索引。 回到线上案例： nc_tms_order、ct_order 看看分别有哪些索引

看到这里我开始凌乱，好像什么字段都可以加索引。 为此专门针对 ct_order 表两个具有比较性的索引做了性能测试，ct_order_code，lc_order_code 区分度都是非常高的字段，前者是好于后者（联合 station_id 并没有起到太多优化作用）。 idx_ct_order_code(ct_order_code), idx_ct_order_lc_order_code(station_id,lc_order_code) 那么接下来我们说说那些字段适合建索引。

最大选择性原则

选择区分度高列做索引 什么是区分度高的字段呢？ 一般两种情况不建议建索引： 1、一两千条甚至几百条，没必要建索引，让查询做全表扫描就好了。 因为不是你建了就一定会走索引，执行计划会选择一个最优的方式，msql 辅助索引的叶子节点并不直接存储实际数据，只是主建 ID，再通过主键索引二次查找。这么一来全表可能很有可能效率更高。 2、索引选择性较低的情况。 所谓选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值。

Index Selectivity = Cardinality / #T 显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由 B+Tree 的性质决定的。 回到线上案例 wh_id 最好不用做索引字段，这个和性别男、女作为索引字段没区别： SELECT count(DISTINCT(wh_id))/count(*) AS Selectivity FROM nc_tms_order_0340 nc_tms_order;

0

选择性不足 0.0001（精确值为 0.00000666），按 Selectivity 值越大价值越大原则，实在没有什么必要为其单独建索引。 再看下 distribution_order_id 单列索引。 SELECT count(DISTINCT(distribution_order_id))/count(*) AS Selectivity FROM nc_tms_order_0340 nc_tms_order;

0.0030

Selectivity = 0.0030 ，比之前有所优化，但其实不是特别理想。 联合索引 SELECT count(DISTINCT(concat(wh_id,distribution_order_id)))/count(*) AS Selectivity FROM nc_tms_order_0340 nc_tms_order;

0.0030

Selectivity = 0.0030 从值来看，这里建联合索引的价值并不是特别大。一个 distrubution_id 搞定。 那么我们在建一个索引或联合索引的时候拿不准的时候可以先计算下选择性值以及通过 explain 测试。 一般情况，status、is_deleted 列不建议建索引。 创建复合索引，需要注意把区分度最大的放到最前面。也就是值越大的放前面，当然需根据时间场景和 sql 通过执行计划进行优化。 前缀索引 有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引 key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引 key 变短而减少了索引文件的大小和维护开销。

其他

索引列不能参与计算

比如from_unixtime(create_time) = ’2017-11-11’就不能使用到索引，语句应该写成create_time = unix_timestamp(’2017-11-11’);

主键最好使用自增型，保证数据连续性（mysql innodb 主键默认采用 b+tree，索引和数据放在同一个 btree 中），不要使用 uuid、hash、md5 等 不要使用前匹配的 like 查询，会导致索引失效。可以使用后匹配 like，如”xxx%”。 在字符串列上创建索引，尽量使用前缀索引。前缀基数根据具体业务，在匹配度和存储量（索引的存储量）之前做一个平衡。 不要使用 not inlike，会导致索引失效。not in 可以用 not exists 替换。in 和 or 所在列最好有索引 说了这么多留 action 吧，大家回去看看 ct_order,nc_tms_order 看看如何优化吧。 其实数据库索引调优，光靠理论是不行的，需要结合实际情况。MySQL 机制复杂，如查询优化策略和各种引擎的实现差异等都会使情况变复杂。我们在了解这些原则和基础之上，要不断的实践和总结，从而真正达到高效使用 MySQL 索引的目的。 执行计划 explain 命令 explain 是 sql 优化神奇。 公司 IDB 的执行计划被包装过了，除了被动查慢 sql 调优上看到，一直没找主动做执行计划的地方，有知道的可以告诉我下。 以下均为工具介绍，可留做备查。 explain 用法 EXPLAIN tbl_name 或：EXPLAIN [EXTENDED] SELECT select_options 举例 mysql> explain select * from event;

+—-+————-+——-+——+—————+——+———+——+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+——-+
| 1 | SIMPLE | event | ALL | NULL | NULL | NULL | NULL | 13 | |
+—-+————-+——-+——+—————+——+———+——+——+——-+

1 row in set (0.00 sec) 各个属性的含义 id：select 查询的序列号

select_type：select 查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

table：输出的行所引用的表。

type：联合查询所使用的类型。

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是： system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL 一般来说，得保证查询至少达到 range 级别，最好能达到 ref。 就 type 进行详细的介绍：all : 即全表扫描

index : 按索引次序扫描，就 type 进行详细的介绍： System,const,eq_ref,ref,range,index,all

all : 即全表扫描

index : 按索引次序扫描，先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的。

range:以范围的形式扫描。

explain select * from a where a_id > 1\G

ref:非唯一索引访问(只有普通索引)

create table a(a_id int not null, key(a_id)); insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); mysql> explain select * from a where a_id=1\G

eq_ref:使用唯一索引查找(主键或唯一索引)

const:常量查询

当出现 using index 时，表示 sql 使用覆盖索引，性能较好，而当出现 using filesort、using temporary、using where 时，查询需要优化。 先读索引，再读实际的行，结果还是全表扫描，主要优点是避免了排序。因为索引是排好的。

range:以范围的形式扫描。

explain select * from a where a_id > 1\G

ref:非唯一索引访问(只有普通索引)

create table a(a_id int not null, key(a_id)); insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10); mysql> explain select * from a where a_id=1\G eq_ref:使用唯一索引查找(主键或唯一索引)

const:常量查询

当出现 using index 时，表示 sql 使用覆盖索引，性能较好，而当出现 using filesort、using temporary、using where 时，查询需要优化。

possible_keys：指出 MySQL 能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验 WHERE 子句，看是否引用某些字段，或者检查字段不是适合索引。

key：显示 MySQL 实际决定使用的键。如果没有索引被选择，键是 NULL。

key_len：显示 MySQL 决定使用的键长度。如果键是 NULL，长度就是 NULL。文档提示特别注意这个值可以得出一个多重主键里 mysql 实际使用了哪一部分。

ref：显示哪个字段或常数与 key 一起被使用。

rows：这个数表示 mysql 要遍历多少数据才能找到，在 innodb 上是不准确的。

Extra：如果是 Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。

如果是 where used，就是使用上了 where 限制。 如果是 impossible where 表示用不着 where，一般就是没查出来啥。 如果此信息显示 Using filesort 或者 Using temporary 的话会很吃力，WHERE 和 ORDER BY 的索引经常无法兼顾，如果按照 WHERE 来确定索引，那么在 ORDER BY 时，就必然会引起 Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

打赏