MySQL 创建高性能的索引
前言
索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响愈发严重。在数据量较小且负载较低时，不切当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。
在实际项目中，开发人员都会通过添加索引来增强性能，但是往往是糟糕的索引而导致严重的生产问题。
索引能够轻易将查询性能提高几个数量级，但有时创建一个真正的 “最优” 的索引经常需要重写查询。所以写好查询语句也是重中之重。
MySQL逻辑架构
MySQL 是基于 C/S 架构的，大多数基于网络的客户端 / 服务器工具等都有类似的架构。
在服务层的核心功能包括查询解析、分析、优化、缓存以及所有的内置函数（例如，日期、时间、数学和加密函数），所有跨存储引擎都在这一层实现：存储过程、触发器、视图等。
在存储引擎层，每个不同的存储引擎都有它的优劣势。服务层通过 API 与存储引擎进行通行。这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层的查询过程透明。
B+树
B+树与B树的区别
叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）；
非叶子节点中有多少个子节点，就有多少个索引。
B+树比B树更适合MySQL 数据库
1、B+树查询效率更加稳定
由于B+树内部不存储实际的记录数据，仅存放索引，因此在数据量相同的情况下，B+树要比B树更矮胖，查询底层节点的磁盘 I/O 次数会更少，查询的路径长度相同，每个数据的查询效率相当；
而 B 树最快可以在 O(1) 的时间查到，查询波动会比较大，因为每个节点即存索引又存记录，所以有时候访问到了非叶子节点就可以找到索引，而有时需要访问到叶子节点才能找到索引。
2、B+树的插入和删除效率高
B+树有大量的冗余节点，删除一个节点可以直接从叶子节点中删除，甚至可以不动非叶子节点。B 树可能出现复杂的树变形，高度调整等。同理插入数据，可能存在节点分裂，B+树会自动平衡。
3、B+树便于范围查询（最重要的原因，范围查找是数据库的常态）
因为 B+ 树所有叶子节点间有一个链表进行连接，只需要去遍历叶子节点就可以实现整棵树的遍历。
而B树没有将所有叶子节点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘l/O操作，范围查询效率不如 B+树。
MySQL中的B+树
Innodb 使用的B+树有一些特别的点，比如：
B+树的叶子节点之间是用「双向链表」进行连接，这样的好处是既能向右遍历，也能向左遍历。
B+树点节点内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页默认大小是 16 KB。
Innodb 根据索引类型不同，分为聚集和二级索引。他们区别在于，聚集索引的叶子节点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点，而二级索引的叶子节点存放的是主键值，而不是实际数据。
索引的分类
可以按照四个角度来分类索引。
按「数据结构」分类：B+树索引、哈希索引、全文索引。
按「物理存储」分类：聚簇索引（主键索引）、辅助索引（二级索引）。
按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
按「字段个数」分类：单列索引、多列索引。
主要讲一下按「物理存储」分类的聚簇索引和辅助索引。
聚簇索引：索引的叶子节点 data 域记录着完整的数据记录，这种索引方式被称为聚簇索引。因为无法把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。
辅助索引：叶子节点的 data 域记录着主键的值，因此在使用辅助索引进行查找时，需要先查找到主键值，然后再到主索引中进行查找。
覆盖索引：InnoDB存储引擎支持覆盖索引（covering index，或称索引覆盖），即从辅助索引中就可以得到查询的记录，而不需要查询聚簇索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。select * 会导致覆盖索引失效，而进行回表。
例如：查看 explain select store_id, create_time from item where store_id > 332604504321036698；这里使用了全表扫描，没有走索引。
然后我们把查询语句改为：explain select store_id from item where store_id > 332604504321036698 ，然后执行：
这样就变成了范围查询，走索引，因为索引中包含了需要查询的全部值，所以不需要再查询聚簇索引，减少磁盘IO，这样就可以提高速度。
高性能的索引策略
正确地创建和使用索引是实现高性能查询的基础。
独立的列
在进行查询时，索引列不能是表达式的一部分，也不能是函数的参数，否则无法使用索引。
例如下面的查询不能使用 actor_id 列的索引:
SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;前缀索引
对文本的前几个字符（具体是几个字符在建立索引时指定）建立索引，这样建立起来的索引更小，所以查询更快；
前缀索引适合于字段的字符串长度较长，且前面几个字符的重复度不高，否则没有太大必要创建。
前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。
最关键的参数就是prefix_length，这个值需要根据实际表的内容，得到合适的索引选择性（Index Selectivity）。索引选择性就是不重复的个数与总个数的比值。
select 1.0 * count(distinct column_name) / count(*)
from table_name逐渐增大字符数量大小，看是否接近该字段的索引选择性（由上一个 sql 计算得出）。
select 1.0 * count(distinct left(FirstName,5)) / count(*)
from Employee
---
alter table test.Employee add key(FirstName(5))多列索引
一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序建立多列索引。还有一种做法“把 WHERE 条件里面的列都创建索引”。
在需要使用多个列作为条件进行查询时，使用多列索引比使用多个单列索引性能更好。例如下面的语句中，最好把 actor_id 和 film_id 设置为多列索引
SELECT film_id, actor_ id FROM sakila.film_actor
WHERE actor_id = 1 AND film_id = 1;索引列的顺序
将索引选择性高的放前面，查询效率越高；
例如下面显示的结果中 customer_id 的选择性比 staff_id 更高，因此最好把 customer_id 列放在多列索引的前面。
SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
COUNT(*)
FROM payment;
---
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049聚簇索引
聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。一个表只能有一个聚簇索引，在聚簇索引中包含了行的所有数据，而不同索引只是存储了索引值和指向数据行的指针。
覆盖索引
如果一个索引包含了所有需要查询的字段的值，就称之为覆盖索引（covering index ），避免回表的产生减少了树的搜索次数，显著提升性能。
如果Extra使用了Using index，就说明它是满足了覆盖索引，这个就是覆盖索引的标志了。
注意：使用select * , 如果字段过滤是联合索引后面的字段，会导致索引失效。可以调整下 SQL 的写法，如：
select * from products join
(select product_id from products where actor = 'SEAN CARREY' and title like '%APOLLO%') as t1
on t1.product_id = products.product_id;其他
最左前缀匹配原则
非常重要的原则，mysql会一直向右匹配直到遇到范围查询(
>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序
比如
a = 1 and b = 2 and c = 3建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。尽量选择区分度高的列作为索引
区分度的公式是
count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。 索引的区分度不大，比如性别，这时候MySQL评估使用索引比全表更慢，则不使用索引。尽量的扩展索引，不要新建索引
比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
创建索引的时机
索引能够大大提高查询速度，但是索引也是有缺点的，比如：
占用物理空间，数量越大，占用空间越大；
创建索引和维护索引要耗费时间，并且随着数据量的增加而增大；
降低表的增删改的效率，每次增删可能导致 B+ 树为了维护索引有序性而进行树的平衡调整。
所以，索引不是银弹，需要结合实际场景来使用。
什么时候不需要创建索引？
WHERE条件，GROUP BY，ORDER BY里用不到的字段，反而会占用物理空间的。字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，那么无论搜索哪个值都可能得到一半的数据。在这些情况下，还不如不要索引，因为 MySQL 还有一个查询优化器，查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。
表数据太少的时候，不需要创建索引；
经常更新的字段不用创建索引，因为索引字段频繁修改，由于要维护 B+Tree 的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的。
什么时候适用索引？
字段有唯一性限制的，比如商品编码；
经常用于
WHERE查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。经常用于
GROUP BY和ORDER BY的字段，这样在查询的时候就不需要再去做一次排序了，因为在建立索引之后在 B+Tree 中都是排序好的。
查询性能优化
在我们已经了解了如何设计最优的库表结构、如何建立最好的索引，这还不够，还需要合理的设计查询。如果查询写得很糟糕，即使库表结构再合理、索引再合适，也无法实现高性能。
优化数据访问
是否向数据库请求了不需要的数据：例如，查询不需要的记录、多表关联时返回全部列、总是取出全部列、重复查询相同的记录等
是否扫描额外的记录：在确定查询只返回需要的数据以后，需要判断是否扫描了过多的数据。可通过
Explain的 type 和 rows 进行排查。
重构查询方式
一个复杂查询还是多个简单查询：有时候将一个大查询分解为多个小查询是很有必要的，具体可以查看切分查询和分解关联查询；但如果一个查询能够胜任时还写成多个独立查询是不明智的。
切分查询：有时对于大查询可以使用分而治之，将大查询切分成小查询，每个查询功能完全一样，只完成一部分，每次只返回一部分查询结果。例如，删除旧数据，一次心删除的话，需要一次锁住很多数据、占满整个事务日志、耗尽系统资、阻塞很多小但重要的查询。
分解关联查询：对每个表进行一次单表查询，然后将结果在应用程序中进行关联，例如，下面这个查询：
SELECT * FROM tag JOIN tag_post ON tag_post.tag_id = tag.id JOIN post ON tag_post.post_id = post.id WHERE tag.tag = 'mysql';可以分解成下面的查询：
SELECT * FROM tag WHERE tag = 'mysql'; SELECT * FROM tag_post WHERE tag_id = 1234; SELECT * FROM post WHERE post_id in (123,456,567,9098,8904);分解关联查询有如下优势：
缓存效率更高：可以有效利用应用程序缓存和 MySQL 查询缓存。
减少锁的竞争
更容易对数据库进行拆分，更容易做到高性能和可扩展
查询效率可能有所提升，例如，上面使用 IN 代替关联查询。
减少冗余记录的查询，数据库中做关联查询，可能需要反复查询一部分数据。
一个查询的执行过程
客户端发送一条查询给服务器。
服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。
服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。
MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。
将结果返回给客户端。
优化特定类型的查询
优化 COUNT查询：在 MySQL 层面能做只有索引覆盖扫描了。如果还不够，需要在应用架构层面，增加汇总表或者增加类似于 Redis 这样的外部缓存系统。
优化关联查询：确保 ON 或者 USING 子句中的列上有索引。确保任何的 GROUP BY 和 ORDER BY 中的表达式只涉及一个表中的列。注意：关联出现笛卡尔积的情况。
优化子查询：尽可能使用关联查询替代，但并不是绝对的，如果是 MySQL 5.6 及以上版本可以忽略这条建议。
优化 GROUP BY 和 DISTINCT：使用索引来优化，也是最有效的方式。GROUP BY 在无法使用索引时，会出现使用临时表或文件排序来分组。
优化 LIMIT分页：一般有两个问题，一是能否使用索引，二是大偏移量的性能。尽可能地使用索引覆盖扫描，而不是查询所有的列。对于大偏移量问题，可以使用延迟关联，现使用关联查询或者子查询通过覆盖索引查询数据，然后查询出所有的数据行。或者使用ID 标签记录法，每次查询通过 ID 记录的位置开始扫描。
优化 UNION 查询：除非确实需要消除重复的行，否则一定要使用
UNION ALL，如果没有ALL关键字，MySQL 会给临时表加上DISTINCT选项，这样代价非常高。即使有ALL关键字，仍然会使用临时表存储结果。
MySQL Explain 详解
我们在拥有一定的创建索引和优化 SQL的技巧后，有时查询性能还是很差，这时可以通过 Explain 语句来进行问题的排查诊断。
字段含义
id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
select_type ：查询类型 或者是 其他操作类型
table ：正在访问哪个表
partitions ：匹配的分区
type ：访问的类型
possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
key ：实际使用到的索引，如果为NULL，则没有使用索引
key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered ：查询的表行占表的百分比
Extra ：包含不适合在其它列中显示但十分重要的额外信息
select_type
常见的值有：
SIMPLE
简单SELECT，不使用UNION或子查询等
PRIMARY 当存在子查询时，最外面的查询被标记为主查询
SUBQUERY 子查询
子查询中的第一个SELECT
EXPLAIN SELECT SC.`cid` FROM stu_course AS SC WHERE SC.`sid` = ( SELECT S.`id` FROM student AS S WHERE S.`name` = "安其拉" ) --- id select_type table partitions type possible_keys key key_len ref ------ ----------- ------ ---------- ------ ------------- ------- ------- ------ 1 PRIMARY SC (NULL) ref PRIMARY PRIMARY 4 const 2 SUBQUERY S (NULL) ref name,name_age name 63 constUNION 当一个查询在
UNION关键字之后就会出现UNIONUNION RESULT
UNION连接几个表查询后的结果
EXPLAIN SELECT * FROM student WHERE id = 1 UNION SELECT * FROM student WHERE id = 2 --- id select_type table partitions type possible_keys key ------ ------------ ---------- ---------- ------ ------------------- ------- 1 PRIMARY student (NULL) const PRIMARY,id_name_age PRIMARY 2 UNION student (NULL) const PRIMARY,id_name_age PRIMARY (NULL) UNION RESULT <union1,2> (NULL) ALL (NULL) (NULL)DERIVED 在
FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中；MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率EXPLAIN SELECT * FROM ( SELECT * FROM student AS S JOIN stu_course AS SC ON S.`id` = SC.`sid` ) AS SSC --- id select_type table partitions type possible_keys key ------ ----------- ---------- ---------- ------ ------------------- -------- 1 PRIMARY <derived2> (NULL) ALL (NULL) (NULL) 2 DERIVED S (NULL) index PRIMARY,id_name_age name_age 2 DERIVED SC (NULL) ref PRIMARY PRIMARY
type
从左往右，越靠左边的越优秀
NULL > system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL
key
实际使用到的索引
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
filtered是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。
Extra
Using filesort表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。EXPLAIN SELECT * FROM course AS C ORDER BY C.`name`type possible_keys key key_len ref rows filtered Extra ------ ------------- ------ ------- ------ ------ -------- ---------------- ALL (NULL) (NULL) (NULL) (NULL) 20 100.00 Using filesort没有给
C.name建立索引，所以在根据C.name排序的时候，他就使用了外部排序Using tempporary表示在对MySQL查询结果进行排序时，使用了临时表,,这样的查询效率是比外部排序更低的，常见于
order by和group by。EXPLAIN SELECT C.`name` FROM course AS C GROUP BY C.`name`possible_keys key key_len ref rows filtered Extra ------------- ------ ------- ------ ------ -------- --------------------------------- (NULL) (NULL) (NULL) (NULL) 20 100.00 Using temporary; Using filesort上面这个查询就是同时触发了
Using temporary和Using filesort，可谓是雪上加霜。Using index直接使用了覆盖索引。Using where使用了 WHERE 子句过滤条件。Using join buffer表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。impossible where筛选条件没能筛选出任何东西distinct优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作
总结
到这里，本文已经详细介绍了索引的原理、分类和使用，同时结合优化查询和 Expain 语句，写出高性能的 SQL 语句。