回顾前文: 一文学会MySQL的explain工具
(同时再次强调，这几篇关于MySQL的探究都是基于5.7版本，相关总结与结论不一定适用于其他版本)
MySQL官方文档中(https://dev.mysql.com/doc/refman/5.7/en/optimization-indexes.html)有这样一段描述：
就是说提高查询性能最直接有效的方法就是建立索引，但是不必要的索引会浪费空间，同时也增加了额外的时间成本去判断应该走哪个索引，此外，索引还会增加插入、更新、删除数据的成本，因为做这些操作的同时还要去维护(更新)索引树。因此，应该学会使用最佳索引集来优化查询。
索引结构
参考：
《MySQL索引背后的数据结构及算法原理》
http://blog.codinglabs.org/articles/theory-of-mysql-index.html《Mysql BTree和B+Tree详解》
https://www.cnblogs.com/Transkai/p/11595405.html《为什么MySQL使用B+树》
https://draveness.me/whys-the-design-mysql-b-plus-tree/《浅入浅出MySQL和InnoDB》
https://draveness.me/mysql-innodb/《漫画：什么是B树？》
https://mp.weixin.qq.com/s/rDCEFzoKHIjyHfI_bsz5Rw
什么是索引
在MySQL中，索引(Index)是帮助高效获取数据的数据结构。这种数据结构MySQL中最常用的就是B+树(B+Tree)。
就好比给你一本书和一篇文章标题，如果没有目录，让你找此标题对应的文章，可能需要从第一页翻到最后一页；如果有目录大纲，你可能只需要在目录页寻找此标题，然后迅速定位文章。
这里我们可以把书(book)看成是MySQL中的table，把文章(article)看成是table中的一行记录，即row，文章标题(title)看成row中的一列column，目录自然就是对title列建立的索引index了，这样根据文章标题从书中检索文章就对应sql语句select * from book where title = ?，相应的，书中每增加一篇文章(即insert into book (title, ...) values ('华山论剑', ...))，都需要维护一下目录，这样才能从目录中找到新增的文章华山论剑，这一操作对应的是MySQL中每插入(insert)一条记录需要维护title列的索引树(B+Tree)。
为什么使用B+Tree
首先需要澄清的一点是，MySQL跟B+树没有直接的关系，真正与B+树有关系的是MySQL的默认存储引擎InnoDB，MySQL中存储引擎的主要作用是负责数据的存储和提取，除了InnoDB之外，MySQL中也支持比如MyISAM等其他存储引擎(详情见https://dev.mysql.com/doc/refman/5.7/en/storage-engine-setting.html)作为表的底层存储引擎。
mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
提到索引，我们可能会立马想到下面几种数据结构来实现。
(1) 哈希表
哈希虽然能够提供O(1)的单数据行的查询性能，但是对于范围查询和排序却无法很好支持，需全表扫描。
(2) 红黑树
红黑树(Red Black Tree)是一种自平衡二叉查找树，在进行插入和删除操作时通过特定操作保持二叉查找树的平衡，从而获得较高的查找性能。
一般来说，索引本身也很大，往往不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗远远高于内存，所以评价一个数据结构作为索引的优劣最重要的指标就是查找过程中磁盘I/O次数。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的次数。
在这里，磁盘I/O的次数取决于树的高度，所以，在数据量较大时，红黑树会因树的高度较大而造成磁盘IO较多，从而影响查询效率。
(3) B-Tree
B树中的B代表平衡(Balance)，而不是二叉(Binary)，B树是从平衡二叉树演化而来的。
为了降低树的高度(也就是减少磁盘I/O次数)，把原来瘦高的树结构变得矮胖，B树会在每个节点存储多个元素(红黑树每个节点只会存储一个元素)，并且节点中的元素从左到右递增排列。如下图所示：
B-Tree在查询的时候比较次数其实不比二叉查找树少，但在内存中的大小比较、二分查找的耗时相比磁盘IO耗时几乎可以忽略。 B-Tree大大降低了树的高度，所以也就极大地提升了查找性能。
(4) B+TreeB+Tree是在B-Tree基础上进一步优化，使其更适合实现存储索引结构。InnoDB存储引擎就是用B+Tree实现其索引结构。
B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个节点的存储空间是有限的，如果data值较大时将会导致每个节点能存储的key的数量很小，这样会导致B-Tree的高度变大，增加了查询时的磁盘I/O次数，进而影响查询性能。在B+Tree中，所有data值都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以增大每个非叶子节点存储的key值数量，降低B+Tree的高度，提高效率。
这里补充一点相关知识 在计算机中，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。这样做的理论依据是计算机科学中著名的局部性原理：
由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页(page)的整数倍。
页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页(许多操作系统的页默认大小为4KB)，主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时操作系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。(如下命令可以查看操作系统的默认页大小)
$ getconf PAGE_SIZE
4096
数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为操作系统的页大小的整数倍，这样每个节点只需要一次I/O就可以完全载入。
InnoDB存储引擎中也有页(Page)的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB。
mysql> show variables like 'innodb_page_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.01 sec)
一般表的主键类型为INT（占4个字节）或BIGINT（占8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录。
B+Tree的高度一般都在2到4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1到3次磁盘I/O操作。
随机I/O对于MySQL的查询性能影响会非常大，而顺序读取磁盘中的数据会很快，由此我们也应该尽量减少随机I/O的次数，这样才能提高性能。在B-Tree中由于所有的节点都可能包含目标数据，我们总是要从根节点向下遍历子树查找满足条件的数据行，这会带来大量的随机I/O，而B+Tree所有的数据行都存储在叶子节点中，而这些叶子节点通过双向链表依次按顺序连接，当我们在B+树遍历数据(比如说范围查询)时可以直接在多个叶子节点之间进行跳转，保证顺序、倒序遍历的性能。
另外，对以上提到的数据结构不熟悉的朋友，这里推荐一个在线数据结构可视化演示工具，有助于快速理解这些数据结构的机制：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
主键索引
上面也有提及，在MySQL中，索引属于存储引擎级别的概念。不同存储引擎对索引的实现方式是不同的，这里主要看下MyISAM和InnoDB两种存储引擎的索引实现方式。
MyISAM索引实现
MyISAM引擎使用B+Tree作为索引结构时叶子节点的data域存放的是数据记录的地址。如下图所示：
由上图可以看出：MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址，因此MyISAM的索引方式也叫做非聚集的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现
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的特性而频繁的分裂调整，十分低效，因而使用递增字段作为主键则是一个很好的选择。
非主键索引
MyISAM索引实现
MyISAM中，主键索引和非主键索引（Secondary key，也有人叫做辅助索引）在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key可以重复。这里不再多加叙述。
InnoDB索引实现
InnoDB的非主键索引data域存储相应记录主键的值。换句话说，InnoDB的所有非主键索引都引用主键的值作为data域。如下图所示：
由上图可知：使用非主键索引搜索时需要检索两遍索引，首先检索非主键索引获得主键(primary key)，然后用主键到主键索引树中检索获得完整记录。
那么为什么非主键索引结构叶子节点存储的是主键值，而不像主键索引那样直接存储完整的一行数据，这样就能避免回表二次检索？显然，这样做一方面节省了大量的存储空间，另一方面多份冗余数据，更新数据的效率肯定低下，另外保证数据的一致性是个麻烦事。
到了这里，也很容易明白为什么不建议使用过长的字段作为主键，因为所有的非主键索引都引用主键值，过长的主键值会让非主键索引变得过大。
联合索引
官方文档：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。如下图所示：
了解了联合索引的结构，就能引入最左前缀法则：
就是说联合索引中的多列是按照列的次序排列的，如果查询的时候不能满足列的次序，比如说where条件中缺少col1 = ?，直接就是col2 = ? and col3 = ?，那么就走不了联合索引，从上面联合索引的结构图应该能明显看出，只有col2列无法通过索引树检索符合条件的数据。
根据最左前缀法则，我们知道对INDEX idx_book_id_hero_name (book_id, hero_name)来说，where book_id = ? and hero_name = ?的查询来说，肯定可以走索引，但是如果是where hero_name = ? and book_id = ?呢，表面上看起来不符合最左前缀法则啊，但MySQL优化器会根据已有的索引，调整查询条件中这两列的顺序，让它符合最左前缀法则，走索引，这里也就回答了上篇《一文学会MySQL的explain工具》中为什么用show warnings命令查看时，where中的两个过滤条件hero_name、book_id先后顺序被调换了。
至于对联合索引中的列进行范围查询等各种情况，都可以先想联合索引的结构是如何创建出来的，然后看过滤条件是否满足最左前缀法则。比如说范围查询时，范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
优化建议
主键的选择
在使用InnoDB存储引擎时，如果没有特别的需要，尽量使用一个与业务无关的递增字段作为主键，主键字段不宜过长。原因上面在讲索引结构时已提过。比如说常用雪花算法生成64bit大小的整数(占8个字节，用BIGINT类型)作为主键就是一个不错的选择。
索引的选择
(1) 表记录比较少的时候，比如说只有几百条记录的表，对一些列建立索引的意义可能并不大，所以表记录不大时酌情考虑索引。但是业务上具有唯一特性的字段，即使是多个字段的组合，也建议使用唯一索引(UNIQUE KEY)。
(2) 当索引的选择性非常低时，索引的意义可能也不大。所谓索引的选择性(Selectivity)，是指不重复的索引值(也叫基数Cardinality)与表记录数的比值，即count(distinct 列名)/count(*)，常见的场景就是有一列status标识数据行的状态，可能status非0即1，总数据100万行有50万行status为0，50万行status为1，那么是否有必要对这一列单独建立索引呢？
这句话我摘自stackoverflow上《MySQL: low selectivity columns = how to index?》下面一个人的回答。(详情见：https://stackoverflow.com/questions/2386852/mysql-low-cardinality-selectivity-columns-how-to-index)
对于上面说的status非0即1，而且这两种情况分布比较均匀的情况，索引可能并没有实际意义，实际查询时，MySQL优化器在计算全表扫描和索引树扫描代价后，可能会放弃走索引，因为先从status索引树中遍历出来主键值，再去主键索引树中查最终数据，代价可能比全表扫描还高。
但是如果对于status为1的数据只有1万行，其他99万行数据status为0的情况呢，你怎么看？欢迎有兴趣的朋友在文章下面留言讨论！
补充: 关于MySQL如何选择走不走索引或者选择走哪个最佳索引，可以使用MySQL自带的trace工具一探究竟。具体使用见下面的官方文档。https://dev.mysql.com/doc/internals/en/optimizer-tracing.htmlhttps://dev.mysql.com/doc/refman/5.7/en/information-schema-optimizer-trace-table.html
使用方法：
mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
mysql> select * from tb_hero where hero_id = 1;
mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
注意：开启trace工具会影响MySQL性能，所以只能临时分析sql使用，用完之后应当立即关闭
mysql> set session optimizer_trace="enabled=off";
(3) 在varchar类型字段上建立索引时，建议指定索引长度，有些时候可能没必要对全字段建立索引，根据实际文本区分度决定索引长度即可【说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)来确定区分度】。
这种指定索引长度的索引叫做前缀索引(详情见https://dev.mysql.com/doc/refman/5.7/en/column-indexes.html#column-indexes-prefix)。
前缀索引语法如下：
mysql> 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即可
order by与group by
尽量在索引列上完成分组、排序，遵循索引最左前缀法则，如果order by的条件不在索引列上，就会产生Using filesort，降低查询性能。
分页查询
MySQL分页查询大多数写法可能如下：
mysql> select * from tb_hero limit offset,N;
MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下。
可以对超过特定阈值的页数进行SQL改写如下：
先快速定位需要获取的id段，然后再关联
mysql> select a.* from tb_hero a, (select hero_id from tb_hero where 条件 limit 100000,20 ) b where a.hero_id = b.hero_id;
或者这种写法
mysql> 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;
多表join
(1) 需要join的字段，数据类型必须绝对一致；
(2) 多表join时，保证被关联的字段有索引
覆盖索引
利用覆盖索引(covering index)来进行查询操作，避免回表，从而增加磁盘I/O。换句话说就是，尽可能避免select *语句，只选择必要的列，去除无用的列。
当索引本身包含查询所需全部列时，无需回表查询完整的行记录。对于InnoDB来说，非主键索引中包含了所有的索引列以及主键值，查询的时候尽量用这种特性避免回表操作，数据量很大时，查询性能提升很明显。
in和exsits
原则：小表驱动大表，即小的数据集驱动大的数据集
(1) 当A表的数据集大于B表的数据集时，in优于exists
mysql> select * from A where id in (select id from B)
(2) 当A表的数据集小于B表的数据集时，exists优于in
mysql> select * from A where exists (select 1 from B where B.id = A.id)
like
索引文件具有B+Tree最左前缀匹配特性，如果左边的值未确定，那么无法使用索引，所以应尽量避免左模糊(即%xxx)或者全模糊(即%xxx%)。
mysql> 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 | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_hero | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
可以看出全模糊查询时全表扫了，这个时候使用覆盖索引的特性，只选择索引字段可以有所优化。如下：
mysql> explain select book_id, hero_name from tb_hero where hero_name like '%无%';
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | tb_hero | NULL | index | NULL | idx_book_id_hero_name | 136 | NULL | 6 | 16.67 | Using where; Using index |
+----+-------------+---------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
count(*)
阿里巴巴Java开发手册中有这样的规约：
截取一段官方文档对count的描述(具体见：https://dev.mysql.com/doc/refman/5.7/en/aggregate-functions.html#function_count)
可见5.7.18之前，MySQL处理count(*)会扫描主键索引，5.7.18之后从非主键索引中选择较小的合适的索引扫描。可以用explain看下执行计划。
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.18 |
+-----------+
1 row in set (0.00 sec)
mysql> explain select count(*) from tb_hero;
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_hero | NULL | index | NULL | idx_skill | 15 | NULL | 6 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select count(1) from tb_hero;
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tb_hero | NULL | index | NULL | idx_skill | 15 | NULL | 6 | 100.00 | Using index |
+----+-------------+---------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
有人纠结count(*)、count(1)到底哪种写法更高效，从上面的执行计划来看都一样，如果你还不放心的话，官方文档中也明确指明了InnoDB对count(*)、count(1)的处理完全一致。
其他
索引列上做任何操作(表达式、函数计算、类型转换等)时无法使用索引会导致全表扫描
实战
前几周测试同事对公司的某产品进行压测，某单表写入了近2亿条数据，过程中发现配的报表有几个数据查询时间太长，所以重点看了几个慢查询SQL。避免敏感信息，这里对其提取简化做个记录。
mysql> select count(*) from tb_alert;
+-----------+
| count(*) |
+-----------+
| 198101877 |
+-----------+
表join慢
表join后，取前10条数据就花了15秒，看了下SQL执行计划，如下：
mysql> 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 | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+
| 1 | SIMPLE | tb_alert | NULL | ALL | NULL | NULL | NULL | NULL | 190097118 | 100.00 | NULL |
| 1 | SIMPLE | tb_situation_alert | NULL | ALL | NULL | NULL | NULL | NULL | 8026988 | 100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+-----------+----------+----------------------------------------------------+
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列单独建立索引。
mysql> 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 | ref | rows | filtered | Extra |
+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+
| 1 | SIMPLE | tb_alert | NULL | ALL | NULL | NULL | NULL | NULL | 190097118 | 100.00 | NULL |
| 1 | SIMPLE | tb_situation_alert | NULL | ref | idx_alert_id | idx_alert_id | 8 | tb_alert.alert_id | 2 | 100.00 | NULL |
+----+-------------+--------------------+------------+------+---------------+--------------+---------+---------------------------------+-----------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
优化后，执行计划可以看出join的时候走了索引，查询前100条0.01秒，和之前的取前10条数据就花了15秒天壤之别。
分页查询慢
从第10000000条数据往后翻页时，25秒才能出结果，这里就能使用上面的分页查询优化技巧了。上面讲优化建议时，没看执行计划，这里正好看一下。
mysql> 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 | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+-----------+----------+-------+
| 1 | SIMPLE | tb_alert | NULL | ALL | NULL | NULL | NULL | NULL | 190097118 | 100.00 | NULL |
+----+-------------+----------+------------+------+---------------+------+---------+------+-----------+----------+-------+
1 row in set, 1 warning (0.00 sec)
再看下使用上分页查询优化技巧的sql的执行计划
mysql> 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 | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 10000010 | 100.00 | 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 | 190097118 | 100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------------+---------+-----------+-----------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
分组聚合慢
分析SQL后，发现根本上并非分组聚合慢，而是扫描联合索引后，回表导致性能低下，去除不必要的字段，使用覆盖索引。
这里避免敏感信息，只演示分组聚合前的简化SQL，主要问题也是在这。
表上有联合索引KEY idx_alert_start_host_template_id ( alert_start, alert_host, template_id)，优化前的sql为
mysql> 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秒，如下：
mysql> 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)
总结
任何不考虑应用场景的设计都不是最好的设计，就比如说表结构的设计、索引的创建，都应该权衡数据量大小、查询需求、数据更新频率等。
另外正如《阿里巴巴java开发手册》中提到的索引规约(详情见：《Java开发手册》之"异常处理、MySQL 数据库")： 创建索引时避免有如下极端误解: