mysql官网: https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html

 

1.索引

1.1 索引

聚集索引( InnoDB ): 叶子节点包含完整的data域

 

 

 

非聚集索引( MyISAM ): 叶子节点包含data数据文件的地址

 

 

 

1.2为什么使用B+Tree？

1.2.0 链表

内存消耗大, 内存碎片

1.2.1 哈希表

哈希虽然能够提供O(1)的单数据行的查询性能

对于范围查询(>, <, in...)和排序却无法很好支持，需全表扫描。

1.2.2 二叉树

当索引为自增时, 树过高, 效率低下

 

 

 

1.2.3 红黑树

索引量大，导致树过高，范围查找效率低

红黑树(Red Black Tree)是一种自平衡二叉查找树，在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。一般来说，索引本身也很大，往往不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗远远高于内存，所以评价一个数据结构作为索引的优劣最重要的指标就是查找过程中磁盘I/O次数。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的次数。在这里，磁盘I/O的次数取决于树的高度，所以，在数据量较大时，红黑树会因树的高度较大而造成磁盘IO较多，从而影响查询效率

 

 

 

1.2.4 B-Tree

B树中的B代表平衡(Balance)，而不是二叉(Binary)，B树是从平衡二叉树演化而来的。为了降低树的高度(也就是减少磁盘I/O次数)，把原来瘦高的树结构变得矮胖，B树会在每个节点存储多个元素(红黑树每个节点只会存储一个元素)，并且节点中的元素从左到右递增排列。如下图所示

 

 

 

 

 

 

B-Tree在查询的时候比较次数其实不比二叉查找树少，但在内存中的大小比较、二分查找的耗时相比磁盘IO耗时几乎可以忽略。 B-Tree大大降低了树的高度，所以也就极大地提升了查找性能。范围查找效率低

1.2.5 B+Tree

B+Tree是在B-Tree基础上进一步优化，使其更适合实现存储索引结构。InnoDB存储引擎就是用B+Tree实现其索引结构。

B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个节点的存储空间是有限的，如果data值较大时将会导致每个节点能存储的key的数量很小，这样会导致B-Tree的高度变大，增加了查询时的磁盘I/O次数，进而影响查询性能。在B+Tree中，所有data值都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以 增大每个非叶子节点存储的key值数量，降低B+Tree的高度，提高效率。

 

 

 

B+Tree相较于B-Tree的优势:

1. B-Tree的每个节点都有data域, B+Tree只有叶子节点含有data域, 增大每个非叶子节点存储的key值数量，降低B+Tree的高度，提高效率

2. B+Tree各个叶子节点之间通过双向指针相连, 提高了范围查找的效率

 

1.3.主键索引

1.3.0MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构时叶子节点的data域存放的是数据记录的地址。如下图所示：

由上图可以看出：MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，因此MyISAM的索引方式也叫做非聚集的，之所以这么称呼是为了与InnoDB的聚集索引区分

 

1.3.1InnoDB索引实现

InnoDB的主键索引也使用B+Tree作为索引结构时的实现方式却与MyISAM截然不同。InnoDB的数据文件本身就是索引文件。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶子节点data域保存了完整的数据记录，这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

 

 

InnoDB存储引擎中的主键索引(primary key)又叫做聚集索引(clustered index)。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。(详情见官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html)

聚集索引这种实现方式使得按主键搜索十分高效，直接能查出整行数据。

在InnoDB中，用非单调递增的字段作为主键不是个好主意，因为InnoDB数据文件本身是一棵B+Tree，非单增的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，因而使用递增字段作为主键则是一个很好的选择

1.3.2为什么要自增？

  1. 自增优于uuid, uuid是字符串自增是int，在比较时, int速度比uuid更快

  2. uuid过长， 占用空间更大， 有非主键索引会导致非主键索引过大(非主键索引叶子节点存储主键值)

  3. 使用自增不会出现插入比已经存在的索引更小的值使B+Tree频繁分裂(插入2、3、8, 8使b+tree分裂)

 

 

1.4. 非主键索引

1.4.0 MyISAM索引实现

MyISAM中，主键索引和非主键索引（Secondary key，也有人叫做辅助索引）在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复

1.4.1 InnoDB索引实现

InnoDB的非主键索引data域存储相应记录主键的值。换句话说，InnoDB的所有非主键索引都引用主键的值作为data域。如下图所示：

 

 

由上图可知：使用非主键索引搜索时需要检索两遍索引，首先检索非主键索引获得主键(primary key)，然后用主键到主键索引树中检索获得完整记录

3.2.1为什么非主键索引结构叶子节点存储主键值

为什么非主键索引结构叶子节点存储的是主键值,而不像主键索引那样直接存储完整的一行数据，这样就能避免回表二次检索？显然，这样做一方面节省了大量的存储空间，另一方面多份冗余数据，更新数据的效率肯定低下，另外保证数据的一致性是个麻烦事。

到了这里，也很容易明白为什么不建议使用过长的字段作为主键，因为所有的非主键索引都引用主键值，过长的主键值会让非主键索引变得过大

 

1.4. 联合索引

官方文档：https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html

比如INDEX idx_book_id_hero_name (book_id, hero_name) USING BTREE，即对book_id, hero_name两列建立了一个联合索引。

联合索引是多列按照次序一列一列比较大小，拿idx_book_id_hero_name这个联合索引来说，先比较book_id，book_id小的排在左边，book_id大的排在右边，book_id相同时再比较hero_name。如下图所示：

 

 

1. 4.0最左前缀法则：

就是说联合索引中的多列是按照列的次序排列的，如果查询的时候不能满足列的次序，比如说where条件中缺少col1 = ?，直接就是col2 = ? and col3 = ?，那么就走不了联合索引，从上面联合索引的结构图应该能明显看出，只有col2列无法通过索引树检索符合条件的数据。（同一块区域内, 若col1相同, 则col2有序, 只查询col2的话, 有多个相同的值, 无法确定索引）

根据最左前缀法则，我们知道对INDEX idx_book_id_hero_name (book_id, hero_name)来说，where book_id = ? and hero_name = ?的查询来说，肯定可以走索引，但是如果是where hero_name = ? and book_id = ?呢，表面上看起来不符合最左前缀法则啊，但MySQL优化器会根据已有的索引，调整查询条件中这两列的顺序，让它符合最左前缀法则，走索引

至于对联合索引中的列进行范围查询等各种情况，都可以先想联合索引的结构是如何创建出来的，然后看过滤条件是否满足最左前缀法则。比如说范围查询时，范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。

1.4.1 索引失效

　　不遵循最左前缀

　　<, <=, >, >=, != , in 等范围查询

　　or， like '%key' %在前

同一块区域内, 若col1相同, 则col2有序, 只查询col2的话, 有多个相同的值, 无法确定索引

2.优化建议

 

2.1主键的选择

在使用InnoDB存储引擎时，如果没有特别的需要，尽量使用一个与业务无关的递增字段作为主键，主键字段不宜过长。原因上面在讲索引结构时已提过。比如说常用雪花算法生成64bit大小的整数(占8个字节，用BIGINT类型)作为主键就是一个不错的选择

 

2.2索引的选择

(1) 表记录比较少的时候，比如说只有几百条记录的表，对一些列建立索引的意义可能并不大，所以表记录不大时酌情考虑索引。但是业务上具有唯一特性的字段，即使是多个字段的组合，也建议使用唯一索引(UNIQUE KEY)。 联合唯一

(2) 当索引的选择性非常低时，索引的意义可能也不大。所谓索引的选择性(Selectivity)，是指不重复的索引值(也叫基数Cardinality)与表记录数的比值，即count(distinct 列名)/count(*)，常见的场景就是有一列status标识数据行的状态，可能status非0即1，总数据100万行有50万行status为0，50万行status为1

对于上面说的status非0即1，而且这两种情况分布比较均匀的情况，索引可能并没有实际意义，实际查询时，MySQL优化器在计算全表扫描和索引树扫描代价后，可能会放弃走索引，因为先从status索引树中遍历出来主键值，再去主键索引树中查最终数据，代价可能比全表扫描还高

(3) 在varchar类型字段上建立索引时，建议指定索引长度，有些时候可能没必要对全字段建立索引，根据实际文本区分度决定索引长度即可【说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)来确定区分度】。

这种指定索引长度的索引叫做前缀索引 ，引语法如下：( 详情见 https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html#column-indexes-prefix)

 Copymysql> alter table tb_hero add index idx_hero_name_skill2 (hero_name, skill(2));

前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于group by和order by操作，也不能用于covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

(4) 当查询语句的where条件或group by、order by含多列时，可根据实际情况优先考虑联合索引(multiple-column index)，这样可以减少单列索引(single-column index)的个数，有助于高效查询。

建立联合索引时要特别注意column的次序，应结合上面提到的最左前缀法则以及实际的过滤、分组、排序需求。区分度最高的建议放最左边。

说明：

  • order by的字段可以作为联合索引的一部分，并且放在最后，避免出现file_sort的情况，影响查询性能。正例：where a=? and b=? order by c会走索引idx_a_b_c，但是WHERE a>10 order by b却无法完全使用上索引idx_a_b，只会使用上联合索引的第一列a

  • 存在非等号和等号混合时，在建联合索引时，应该把等号条件的列前置。如：where c>? and d=?那么即使c的区分度更高，也应该把d放在索引的最前列，即索引idx_d_c

  • 如果where a=? and b=?，如果a列的几乎接近于唯一值，那么只需要建立单列索引idx_a即可

 

2.3 order by与group by

尽量在索引列上完成分组、排序，遵循索引最左前缀法则，如果order by的条件不在索引列上，就会产生Using filesort，降低查询性能。

 

2.4 分页查询

MySQL分页查询大多数写法可能如下：

 Copymysql> select * from tb_hero limit offset,N;

MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下。

可以对超过特定阈值的页数进行SQL改写如下：

先快速定位需要获取的id段，然后再关联

 Copymysql> select a.* from tb_hero a, (select hero_id from tb_hero where 条件 limit 100000,20 ) b where a.hero_id = b.hero_id;
 # 或者这种写法
 Copymysql> select a.* from tb_hero a inner join (select hero_id from tb_hero where 条件 limit 100000,20) b on a.hero_id = b.hero_id;

 

2.5 多表join

(1) 需要join的字段，数据类型必须绝对一致； (2) 多表join时，保证被关联的字段有索引

 

2.6 覆盖索引

利用覆盖索引(covering index)来进行查询操作，避免回表，从而增加磁盘I/O。换句话说就是，尽可能避免select *语句，只选择必要的列，去除无用的列

当索引本身包含查询所需全部列时，无需回表查询完整的行记录。对于InnoDB来说，非主键索引中包含了所有的索引列以及主键值，查询的时候尽量用这种特性避免回表操作，数据量很大时，查询性能提升很明显

 

2.7 in和exsits

原则：小表驱动大表，即小的数据集驱动大的数据集

(1) 当A表的数据集大于B表的数据集时，in优于exists

 Copymysql> select * from A where id in (select id from B)

(2) 当A表的数据集小于B表的数据集时，exists优于in

 Copymysql> select * from A where exists (select 1 from B where B.id = A.id)

 

2.8 like

索引文件具有B+Tree最左前缀匹配特性，如果左边的值未确定，那么无法使用索引，所以应尽量避免左模糊(即%xxx)或者全模糊(即%xxx%)

 Copymysql> select * from tb_hero where hero_name like '%无%';
 +---------+-----------+--------------+---------+
 | hero_id | hero_name | skill        | book_id |
 +---------+-----------+--------------+---------+
 |       3 | 张无忌    | 九阳神功     |       3 |
 |       5 | 花无缺    | 移花接玉     |       5 |
 +---------+-----------+--------------+---------+
 2 rows in set (0.00 sec)
 ​
 mysql> explain select * from tb_hero where hero_name like '%无%';
 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----
 | id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | 
 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----
 |  1 | SIMPLE      | tb_hero | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |   
 +----+-------------+---------+------------+------+---------------+------+---------+------+------+----
 1 row in set, 1 warning (0.00 sec)

可以看出全模糊查询时全表扫了，这个时候使用覆盖索引的特性，只选择索引字段可以有所优化。如下：

 Copymysql> explain select book_id, hero_name from tb_hero where hero_name like '%无%';
 +----+-------------+---------+------------+-------+---------------+-----------------------+---------+-
 | id | select_type | table   | partitions | type  | possible_keys | key                   | key_len |                 |
 +----+-------------+---------+------------+-------+---------------+-----------------------+---------+-
 |  1 | SIMPLE      | tb_hero | NULL       | index | NULL          | idx_book_id_hero_name | 136     | 
 +----+-------------+---------+------------+-------+---------------+-----------------------+---------+-
 1 row in set, 1 warning (0.00 sec)

 

2.9 count(*)

阿里巴巴Java开发手册中有这样的规约：

不要使用count(列名)或count(常量)来替代count(*)，count(*)是SQL92定义的标准统计行数的语法，跟数据库无关，跟NULL和非NULL无关【说明：count(*)会统计值为NULL的行，而count(列名)不会统计此列为NULL值的行】。 count(distinct col)计算该列除NULL之外的不重复行数，注意count(distinct col1, col2)如果其中一列全为NULL，那么即使另一列有不同的值，也返回为0

官方文档中也明确指明了InnoDB对count(*)、count(1)的处理完全一致

 

2.10其他

索引列上做任何操作(表达式、函数计算、类型转换等)时无法使用索引会导致全表扫描

 

3.实战

前几周测试同事对公司的某产品进行压测，某单表写入了近2亿条数据，过程中发现配的报表有几个数据查询时间太长，所以重点看了几个慢查询SQL。避免敏感信息，这里对其提取简化做个记录。

 Copymysql> select count(*) from tb_alert;
 +-----------+
 | count(*)  |
 +-----------+
 | 198101877 |
 +-----------+

 

3.1表join慢

表join后，取前10条数据就花了15秒，看了下SQL执行计划，如下：

 Copymysql> select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 10;
 10 rows in set (15.46 sec)
 ​
 mysql> explain select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 10;
 +----+-------------+--------------------+------------+------+---------------+------+---------+------
 | id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | 
 +----+-------------+--------------------+------------+------+---------------+------+---------+------
 |  1 | SIMPLE      | tb_alert           | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 
 |  1 | SIMPLE      | tb_situation_alert | NULL       | ALL  | NULL          | NULL | NULL    | NULL |
 +----+-------------+--------------------+------------+------+---------------+------+---------+------
 2 rows in set, 1 warning (0.00 sec)

可以看出join的时候没有用上索引，tb_situation_alert表上联合主键是这样的PRIMARY KEY (situation_id, alert_id)，参与表join字段是alert_id，原来是不符合联合索引的最左前缀法则，仅从这条sql看，解决方案有两种，一种是对tb_situation_alert表上的alert_id单独建立索引，另外一种是调换联合主键的列的次序，改为PRIMARY KEY (alert_id, situation_id)。当然不能因为多配一张报表，就改其他产线的表的主键索引，这并不合理。在这里，应该对alert_id列单独建立索引。

 Copymysql> create index idx_alert_id on tb_situation_alert (alert_id);
 ​
 mysql> select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 100;
 100 rows in set (0.01 sec)
 ​
 mysql> explain select * from tb_alert left join tb_situation_alert on tb_alert.alert_id = tb_situation_alert.alert_id limit 100;
 +----+-------------+--------------------+------------+------+---------------+--------------+---------
 | id | select_type | table              | partitions | type | possible_keys | key          | key_len 
 +----+-------------+--------------------+------------+------+---------------+--------------+---------
 |  1 | SIMPLE      | tb_alert           | NULL       | ALL  | NULL          | NULL         | NULL    
 |  1 | SIMPLE      | tb_situation_alert | NULL       | ref  | idx_alert_id  | idx_alert_id | 8       
 +----+-------------+--------------------+------------+------+---------------+--------------+---------
 2 rows in set, 1 warning (0.00 sec)

优化后，执行计划可以看出join的时候走了索引，查询前100条0.01秒，和之前的取前10条数据就花了15秒天壤之别

 

3.2分页查询慢

从第10000000条数据往后翻页时，25秒才能出结果，这里就能使用上面的分页查询优化技巧了。上面讲优化建议时，没看执行计划，这里正好看一下。

 Copymysql> select * from tb_alert limit 10000000, 10;
 10 rows in set (25.23 sec)
 ​
 mysql> explain select * from tb_alert limit 10000000, 10;
 +----+-------------+----------+------------+------+---------------+------+---------+------+----------
 | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows     
 +----+-------------+----------+------------+------+---------------+------+---------+------+----------
 |  1 | SIMPLE      | tb_alert | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 190097118 
 +----+-------------+----------+------------+------+---------------+------+---------+------+----------
 1 row in set, 1 warning (0.00 sec)

再看下使用上分页查询优化技巧的sql的执行计划(先快速定位需要获取的id段，然后再关联)

 Copymysql> select * from tb_alert a inner join (select alert_id from tb_alert limit 10000000, 10) b on a.alert_id = b.alert_id;
 10 rows in set (2.29 sec)
 ​
 mysql> explain select * from tb_alert a inner join (select alert_id from tb_alert a2 limit 10000000, 10) b on a.alert_id = b.alert_id;
 +----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+
 | id | select_type | table      | partitions | type   | possible_keys | key           | key_len | ref 
 +----+-------------+------------+------------+--------+---------------+---------------+---------+----
 |  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL          | NULL    |NULL
 |  1 | PRIMARY     | a          | NULL       | eq_ref | PRIMARY       | PRIMARY       | 8       | b.alert_id |         1 |   100.00 | NULL        |
 |  2 | DERIVED     | a2         | NULL       | index  | NULL          | idx_processed | 5       |NULL
 +----+-------------+------------+------------+--------+---------------+---------------+---------+----
 3 rows in set, 1 warning (0.00 sec)

 

3.3分组聚合慢#

分析SQL后，发现根本上并非分组聚合慢，而是扫描联合索引后，回表导致性能低下，去除不必要的字段，使用覆盖索引。

这里避免敏感信息，只演示分组聚合前的简化SQL，主要问题也是在这。 表上有联合索引KEY idx_alert_start_host_template_id ( alert_start, alert_host, template_id)，优化前的sql为

 Copymysql> select alert_start, alert_host, template_id, alert_service from tb_alert where alert_start > {ts '2019-06-05 00:00:10.0'} limit 10000;
 10000 rows in set (1 min 5.22 sec)

使用覆盖索引，去掉template_id列，就能避免回表，查询时间从1min多变为0.03秒，如下：

 Copymysql> select alert_start, alert_host, template_id from tb_alert where alert_start > {ts '2019-06-05 00:00:10.0'} limit 10000;
 10000 rows in set (0.03 sec)
 ​
 mysql> explain select alert_start, alert_host, template_id from tb_alert where alert_start > {ts '2019-06-05 00:00:10.0'} limit 10000;
 +----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+
 | id | select_type | table    | partitions | type  | possible_keys                      | key                                | key_len | ref  | rows     | filtered | Extra                    |
 +----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+
 |  1 | SIMPLE      | tb_alert | NULL       | range | idx_alert_start_host_template_id   | idx_alert_start_host_template_id   | 9       | NULL | 95048559 |   100.00 | Using where; Using index |
 +----+-------------+----------+------------+-------+------------------------------------+------------------------------------+---------+------+----------+----------+--------------------------+
 1 row in set, 1 warning (0.01 sec)