如何写出高性能的SQL

前文【Mysql索引的数据结构】
介绍了索引的数据结构，以及索引的优点。下面我们来看一下该如何正确的使用索引实现高性能的查询。网上已经有很多文章说了常规的索引优化的技巧，例如查询语句尽量使用全值匹配、左前缀原则、将索引列单独放在比较符号的一侧、范围条件右边的列索引全失效、避免使用 != 或 <>、避免is null 或 is not null等，这种文章比较多，这些优化技巧通过前文的索引数据结构即可了解其本质原因，本文不再赘述。这里我将结合实际的开发场景描述索引优化的一些原则方法。
文中所用示例的表结构如下：

先写代码，后加索引
在实际开发中，往往在建表时不是特别清楚表的查询情况，所以在等主体业务功能开发完成之后，再将涉及到该表相关的sql都拿出来分析之后再去建立索引。
建立高选择性的索引
索引的选择性是指，不重复的索引值（也称为基数）和表记录总数(T)的比值，比值越高，索引的选择性越高,Mysql在查询时可以过滤掉更多的行。
建立高选择性的索引这一技巧规则可以适用再以下几种场景：
1、联合索引
首先再说联合索引之前，讨论下为什么要建立联合索引。
在早期的Mysql版本中，如果查询条件中有多个索引，他只会使用其中的一个，在Mysql5.0版本之后，更新了“索引合并”的策略，一定程度上可以使用表上的多个单独的索引来定位指定的行。但这个索引合并其实是使用这两个单独的索引进行扫描，然后将扫描结果进行合并。在这种情况下，建立联合索引才是高效的。
对于上面实例的表，如果建立 position, age,name这个顺序的索引是否是最高效的呢？不一定。
以下内容仅是经验法则，具体情况需要根据实际的场景创建索引。
对于如何选择索引列的顺序有一个经验法则：将选择性最高的列放在最前列。这个建议有用吗？在某些场景可能有帮助，但通常不如避免随机I/O和排序那么重要。（摘录自《MySQL高性能书籍_第3版（中文）》）
这个经验告诉我们，在where查询条件后的索引顺序，是会影响到查询效率的。当不考虑排序和分组时，选择性最高的列放在最前列可以快速的帮助我们过滤出查询的行。上图中的索引列的选择性可以用如下的Sql计算得出：
2、还有另一个经验法则是关于下面这种Sql：
这种情况往往建立索引时把查询的字段包含进去，会直接在索引树中既能得到查询列的值，这种索引也叫做覆盖索引。但实际情况需要根据具体的情况来确定。
3、前缀索引
有些情况下表字段的长度非常大，例如BLOB、TEXT或者很长的Varchar类型，这种情况下Mysql不允许使用这些列的完整长度作为索引列。
这种场景，一种解决方式是建立Hash索引。如果是Innodb存储引擎可以建立Hash索引，但是Hash索引对于等值比较效率较高，范围查找性能非常差。如果存储引擎不支持Hash索引可以自定义Hash索引，即在表中维护一列用来存储这个长列的Hash值，例如如下的查询Sql：
这里选用的是CRC32算法，缺点是当数据量非常大的时候，该函数会出现大量的hash冲突，可以选择自己实现一个Hash函数，返回一个整数hash值。
另一种解决方式是建立前缀索引。所谓的前缀索引是指将这个长列的前几个字符作为索引。
示例中我用了前7个字符作为前缀索引，注意这个数字最好是经过计算的，也就是通过计算前几个字符的选择性，选择选择性较高的前几个字符，但选择的字符过多会占用较多的内存，因此当大于某个字符数选择性提升不是很明显的时候，那此时选择这前几个字符作为前缀索引往往是比较合适的。这种索引的一个缺点是无法使用前缀索引做order by 和group by操作。
分页查询优化
下面的Sql在我们的业务系统里经常出现，表示从表中取出从第1000行开始的后10行记录。
然而实际上数据库并不是真的只取了10条记录，而是先读取了10010行记录，然后抛弃了前10000行，然后读到后面的10行数据。因此，经常可以发现一些页面，越往后翻页，查询速度越慢。 常见的优化技巧：
1、根据自增且连续的主键排序的分页查询
这种情况下，对于上面的Sql可以改写成
通过执行计划可知改写之后的Sql走了索引，大大的提升了效率。但是这种写法会有一个很严重的弊端，当主键不连续时，会导致查询结果错误。因此这种写法仅适用于主键自增且连续的场景。另外如果有order by时，结果是需要根据主键排序的。
2、根据非主键字段排序的分页查询

这里可以看到没有走索引，原因是扫描整个索引并查找到没索引 的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。可以将Sql改写为：

这里可以看到优化后的Sql走了索引，而原Sql使用的是 filesort 排序。核心就是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录。
Join关联查询优化
mysql的表关联常见有两种算法 ：
Nested-Loop Join 算法 和 Block Nested-Loop Join 算法
1、 嵌套循环连接 Nested-Loop Join(NLJ) 算法 一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动 表）里取出满足条件的行，然后取出两张表的结果合集。如果有三个及以上的表，则会先使用 NLJ 算法得到一、二个表的结果集，并将该结果集作为外层数据，遍历结果集到后第三个表中查询数据。

从上面的执行计划中可以得到的信息：

• t2表作为驱动表先执行，t1作为被驱动表。这里说一个规则，当使用inner join时，优化器会选择小表作为驱动表，当使用left join时，会使用左表作为驱动表，right join时会使用右表作为驱动表。
• 使用了NLJ算法。如果执行计划中的Extra 中未出现 Using join buffer 则表示使用的 join 算 法是 NLJ。
  对于上面的SQL的执行流程如下：
  从驱动表t2读取一行数据， 如果有过滤条件则在过滤之后的结果中取出一行数据。
  从上一步的结果中取出字段a，到被驱动表中查找。
  取出被驱动表t1中满足条件的行，跟t2中获取到的结果合并，作为返回结果返回给客户端。之后重复前面几步操作。整个过程会读取 t2 表的所有数据，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表 中的对应行。如果被驱动表t1的关联字段没有索引，使用NLJ算法性能会比较低，Mysql会选用BLJ算法。
  2、 基于块的嵌套循环连接 Block Nested-Loop Join(BLJ)算法
  
  从上面的执行计划可知这里用的时BLJ算法。
  上面的Sql执行流程大致如下：

1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据
   整个过程对表t1和t2都进行了全表扫描，并且join_buffer中的数据是无序的，所以对t1中取出来的每一行都会进行100次的判断，所以整个判断的次数为100*10337 约100万次。
   被驱表的关联字段没有索引，为什么选择BLJ算法而不是选择NLJ算法呢？
   这里先简单解释下BLJ算法。即 Block Nested-Loop Join，是MySQL 自己创建的方式。将指定的外层键对应的被驱动表缓存起来以提高性能。Join操作使用内存(join_buffer_size)：应用程序经常会出现一些两表（或多表）Join的操作需求，MySQL在完成某些 Join 需求的时候，为了减少参与Join的“被驱动表”的读取次数以提高性能，需要使用到 Join Buffer 来协助完成 Join操作。
   如果第二条Sql使用NLJ算法的话，那这一百万次的对比将会是一百万次的磁盘扫描，而不是BLJ算法的内存计算。
   总结一下对于关联sql的优化
   1、关联字段加索引，让mysql做join操作时尽量选择NLJ算法 。
   2、小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间 （straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执 行顺序；对于小表定义： 在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据 量，数据量小的那个表，就是“小表”，应该作为驱动表。）
   in和exsits优化
   原则：小表驱动大表，即小的数据集驱动大的数据集
   in：当B表的数据集小于A表的数据集时，in优于exists（select * from A where id in (select id from B)；）
   exists：当A表的数据集小于B表的数据集时，exists优于in（select * from A where exists (select 1 from B where B.id = A.id)；）。EXISTS子查询通常情况下也可以用JOIN来代替，但实际情况需要根据具体的情况来确定。
   最后一条，尽量不要写多表查询，尽量单表查询，业务计算逻辑处理用Java代码来处理，除非是你的系统对性能要求不高比如报表系统之类的。