共计 2277 个字符，预计需要花费 6 分钟才能阅读完成。
关于 SQL 优化，这个问题，相信大家过多过少都有过一些了解。最近我也在研究 SQL 优化方面的东西，分享一些经验。
首先简单介绍下索引，“ 索引 ” 是 SQL 优化中很重要的一部分（但是索引并不是优化的唯一选项）
索引原理简述
如何理解索引？索引其实就是一种数据结构，用于快速定位和访问数据库中的数据。
通常来说索引使用的数据结构是 B-Tree / B+Tree。以 B -Tree 为例，假设每个节点存储 100 个 Key，三层的 B -Tree 可存储一百万数据，如果将根节点存入内存中的话，只需要读取两次磁盘就可以从 100 万数据中找到指定数据
关于 B -Tree 推荐阅读这篇 https://www.geeksforgeeks.org/introduction-of-b-tree-2/ 包含 B -Tree 的查询，新增，删除操作如何实现
MySQL 执行计划
SQL 优化中查看执行计划是必不可少的一项，通过 explain 关键字可以查看 MySQL 中的执行计划
注：G 含义是纵向显示结果
如果之前没有了解的 EXPLAIN 的同学，看到这个列表肯定是一脸懵逼。没关系我们先来挑几个重要的属性认识一下。
- type：ALL 代表全表扫描
- key：代表使用的索引，NULL 代表没有使用索引
- rows：扫描行数
关于explain 再扩展一下，先执行 explain extended ...;，再执行 SHOW WARNINGS 可以看到 MySQL 优化器对我们的 SQL 做了什么优化。如下图所示
利用索引来优化 SQL
使用索引的优点：减少服务器扫描的数据量、避免排序和临时表、将随机 I / O 变为顺序 I /O
通过下图，我们可以看到，添加了索引之后扫描行数从三十万行降到了 1，性能提升可想而知
生产环境要注意，创建索引是一个非常耗时的操作，并且会阻塞其他操作。
生产环境添加索引有没有什么完美方案？
有的，如果你的 MySQL 使用主从策略的时候，可以像 Nginx 不停机升级 web 服务那样，先移除一个节点为该节点执行ALTER TABLE操作，然后巴拉巴拉，因为具体我也没操作过就不细说了，感兴趣大家可以 Google 一下，动手尝试一下。如果是单机部署的话，只能用户少的时候在执行这种操作了
使用索引连接表
索引也可以提高表连接的性能，下面是个例子，用户表左连订单表，对 user_id 添加索引的前后对比
like 优化
通过上述例子，我们可以看出，如果模糊查询时以 % 开头的话，MySQL 无法使用索引，但是通常来说模糊查询时我们的匹配方式都会是 %xxx%，那么如何优化呢？
这里可以通过存 “ 反值 ” 的方式巧妙的解决这个问题，例如我现在在数据库加一列 reverse_order_no 存储订单号的反值（并添加索引），匹配的时候再通过 REVERSE(‘%910’) 函数将参数取反。
这里也可以使用 or，如下图，查看执行计划会发现 Extra 属性返回 "Using sort_union(order_no,reverse_order_no); Using where" 这里代表 MySQL 发生了索引合并，后文我们会讲到
排序以及多列索引
排序需要加索引！相信大家可能知道这个道理，但是如下图所示，user_id 和 addtime 两列都建立了索引，那么下面这条查询排序使用索引了吗？
答案是：并没有！为什么？注意 Extra 中的 using filesort，代表 MySQL 使用了内部文件排序算法对结果集进行了排序。MySQL 通常在一个表上只选择一个索引（有例外的情况），这种情况如果我们希望排序使用索引的话，可以建立一个多列索引，如下图所示
而且多列索引最左边的列，可以当作单列索引来使用
MySQL 优化器特性
我们刚刚说过 MySQL 通常在一个表上只选择一个索引，如何理解？例如索引 A 和索引 B 一个需要扫描十万行，一个需要扫描五万行，那么 MySQL 一定选择开销最小的索引方式。
在一些特殊情况下，MySQL 会选择 Index Merge（索引合并），即在一个表上使用多个索引
- Union：两个基数很高的索引执行 OR 操作时
- Sort-Union：与上述类似，一旦 or 的左右两边出现范围查询，会使用该算法，区别是 Sort-Union 会进行排序
- intersect：针对唯一值不多的索引列，例如在 is_pay（0- 未支付，1- 支付），is_send（0- 未发货，1- 发货）两列建立索引，查询已支付并且未发货的订单，如下图所示
根据 MySQL 5.7 开发文档所示，还有一种会使用 intersect，InnoDB 主键上的任何范围搜索
关于 Index Merge 的更多信息，参考 MySQL 开发文档
https://dev.mysql.com/doc/refman/5.7/en/index-merge-optimization.html
索引的影响
添加索引虽然可以提升我们的 SQL 性能，但是随之而来也会带来一定的开销
- 数据插入和更新的性能，因为需要构建索引的原因，在数据量大的时候会比较明显，下图是《Effective MySQL 之 SQL 语句最优化》中对添加索引前后的插入性能对比
- 磁盘空间的影响，同样也是来自于书中的测试
可以看到在添加了索引之后，空间占用是原来的 7 倍，在数据量庞大时，这是一个需要关注的点。
还有需要注意的一点是，在 MySQL Innodb 中有聚簇索引和二级索引，一般来说主键就是聚簇索引，而其他的索引都是二级索引。
二级索引所存储的值是聚簇索引。所以当使用二级索引来进行检索时，MySQL 会先通过该索引找到对应的聚簇索引，再通过该聚簇索引找到对应的数据。这时使用占用字节更小的类型来做主键会更好，会节省索引占用空间
参考
Effective MySQL 之 SQL 语句最优化
