前言
前面三篇内容我们花了大量时间介绍了索引及SQL调优工具，其实主要目的就是为接下来的这篇文章服务的，索引的目的就是为了加快查询，学习它也是为了我们更好地进行SQL调优，下面我们利用前面学到的知识实战一下吧！本篇文章从几个方面进行讨论：一是索引遵循的原则，二是利用索引进行SQL调优时的注意事项，三是对常见问题SQL该如何进行调优。
一、 索引遵循的原则
要想正确地使用某项技术，必须了解技术的原理及使用方式（原则）。索引底层原理我们前面已经介绍过了，那它的使用方式（原则）是什么呢？接下来我们详细了解一下：
1 、全值匹配
条件where语句中的列被包含在了索引列中。
比如一个索引项index（a、b、c），有以下三个where条件语句：
where a = 1；where a = 1 and b = 2；where a = 1 and b = 2 and c = 3；以上三个where条件语句中的列是被包含在索引列中的，所以这三个语句都是可以走索引的。
2 、最左前缀原则
组合索引中，要符合最左前缀法则，即查询时从索引最左边的列开始匹配并且不跳过索引中的列。
比如一个索引index（a、b、c），where a = 3使用到索引中的a字段；where b = 1是不能走索引的，因为跳过了索引中a字段不符合最左前缀原则；where a = 3 and c = 2使用到了索引a，但c走不了索引，因为中间b断掉了。
3 、不要在索引列上做任何计算
不要在索引列上做如下操作：计算、函数计算、类型转换等，不然会导致索引失效而转向全表扫描。
比如：虽然有name索引（即index(name)）但是由于对name列进行了函数计算，因此不能走索引。
4 、范围条件右边的列不能走索引
比如：position列是走不了索引的。
5 、尽量使用覆盖索引（即要查询的列包含在了索引列中），减少select * 的使用
组合索引中，使用覆盖索引可以减少回表查询，提高查询性能。
6 、索引列不要用不等于（!=或）、not in，not exists，不然会索引失效导致全表扫描
7 、索引列上不要使用is null或is not null判断，不然会导致索引失效
8 、索引列使用like不以通配符‘%’开头，不然会导致索引失效走全表扫描
针对like‘%字符串%’索引不被使用的情况解决方案：
a、 使用覆盖索引
b、 尽量改成以“%”结尾的查询，如不能则可借助搜索引擎
注意：like针对‘字符串%’是可以走索引的，如果后面还有其他索引项，是不会继续往下走索引的，即索引项到like对应的列为止。
9 、索引列上的字符串要加单引号
如上SQL语句，1000没有加单引号导致未走name索引。
10 、索引列不要使用or
如上SQL语句，使用or做连接导致name索引失效。
11 、范围查询使用索引情况
MySQL在使用索引时会使用一个index_key功能，该功能用来确定MySQL的一个扫描范围，分为上边界和下边界。
MySQL利用=、>=、> 来确定下边界（first key），利用最左匹配原则，首先判断第一个索引键值在where条件中是否存在，如果存在，则判断比较符号，如果为“=,>=”中的一种，加入下边界的界定，然后继续判断下一个索引键，如果是“>”，则将该键值加入到下边界的界定，停止匹配下一个索引键；如果不存在，直接停止下边界匹配。
例如：一个联合索引：idx_c1_c2_c3(c1,c2,c3)
where c1>=1 and c2>2 and c3=1
--> first key (c1,c2)
--> c1 为 '>=' ，加入下边界界定，继续匹配下一个
-->c2 为 '>'，加入下边界界定，停止匹配
上边界（last key）和下边界（first key）类似，首先判断是否是“=”，“='a' and c1 ='a' and c1 90000 limit 5；来对比一下这两条SQL的执行计划分析：
从上面的执行计划可以看出，显然改写后的SQL走了索引，而且扫描的行数大大减少，执行效率更高。
但是，这种情况下，改写后的SQL使用场景很有限，如果表中某些记录被删会造成主键空缺，导致查询结果不一致，比如将id= (select id from employees order by id limit 90000 1) limit 5 改写后的SQL在利用了主键索引性能方面会有很大提升；
还有一种方案就是不用子查询，在代码里先计算出要查询的5条数据中的最小id值，即select * from employees where id >= 90000 limit 5；其中90000是在代码里计算得出的，可直接赋值给SQL参数即可。
3 ）、利用非主键字段排序进行优化
比如一个根据非主键字段排序的分页查询：select * from employees ORDER BY name limit 90000,5；该SQL如何进行优化？其实关键是让排序时返回的字段尽可能少，所以可以先通过排序和分页操作查出主键，然后根据主键查到对应的记录，SQL改写如下：select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id；通过explain查看执行计划：
4 、Order by与Group by优化
我们先来看一下几种order by情况下的案例分析：
1) 案例1：select * from employees where name = ‘Lilei’and position = ‘dev’order by age;
通过最左前缀法则：中间字段不能断，此查询用到了name索引，从key_len=74也能看出，age索引列用在了排序过程中，因为Extra字段里没有using filesort。
2 ）案例2：select * from employees where name = ‘Lilei’ order by position;
从explain的执行结果来看：key_len=74，查询使用了name索引，由于用position进行排序，跳过了age，出现了Using filesort。
3） 案例3：select * from employees where name =‘Lilei’order by age, position;
查找只用到索引name，age和position用于排序，所以无Using filesort。
4） 案例4： select * from employees where name =‘Lilei’order by position, age;
和案例3中的执行结果一样，但是出现了Using filesort，因为索引的创建顺序为name,age,position，但是排序的时候为age和position颠倒位置了。
5） 案例5： select * from employees where name =‘Lilei’and age = 18 order by position, age;
与案例4对比，在Extra中并未出现Using filesort，因为age为常量，在排序中被优化，索引未颠倒，所以不会出现Using filesort。
6） 案例6： select * from employees where name =‘Lilei’order by age asc，position desc;
虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，与索引的排序方式不同，导致没有利用上position索引列，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。
7） 案例7： select * from employees where name in (‘Lilei’,’LiHua’) order by age，position;
对于排序来说，多个相等条件也是范围查询。
8） 案例8：select * from employees where name > ‘a’order by name;
此SQL语句没有走索引，可以使用覆盖索引进行优化：select name,age,position from employees where name > ‘a’order by name;这样就可以利用上name索引列：
由此针对order by和group by优化可总结为如下几点：
a、MySQL支持两种方式的排序filesort和index，Using index是指MySQL通过扫描索引本身来完成排序。Using index效率高，Using filesort效率低；
b、order by满足如下两种情况时会使用Using index：
（1）order by语句使用索引最左前列，
（2）where子句与order by子句使用的条件列组合起来满足索引最左前列；
c、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则；
d、如果order by的条件不在索引列上，就会产生Using filesort；
e、能用覆盖索引尽量用覆盖索引；
f、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则；对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能在where中限定的条件就不要用having限定了。
5 、Join关联查询优化
为了方便演示我们先来创建两张表：
CREATE TABLE t1 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_a (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE t2 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_a (a)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
两张表的表结构是一样的，唯一的区别就是数据量不一样，t1表的数据量远大于t2表。
在介绍join关联查询优化之前我们先来看一下MySQL常见的两种关联查询算法：
1 ）、嵌套循环链接关联（Nested-Loop Join(NLJ)）
先循环第从第一张表（也叫驱动表）中每次读取一行数据，并找到这行数据中的关联字段，然后根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。比如：
从执行计划中我们可以看到：
a、表t2是驱动表，表t1是被驱动表 (执行计划中id如果一样则按从上到下顺序执行，先执行的为驱动表)；优化器一般会优先选择小表做驱动表。所以使用inner join 时，排在前面的表并不一定就是驱动表。
b、使用left join时，左表是驱动表，右表是被驱动表，使用right join时，右表时驱动表，左表是被驱动表，使用join时，MySQL会选择数据量较小的表作为驱动表，数据量大的表作为被驱动表。
c、一般join语句中，如果执行计划Extra中未出现Using join buffer则表示使用的join 算法是 NLJ。
上面sql的大致流程如下：
a、从表t2中读取一行数据（如果t2表有过滤条件，会从过滤结果里取出一行数据）；
b、从上一步获取的数据中，取出关联字段a，到表t1中查找；
c、取出表t1中满足条件的行，跟t2中获取到的结果合并，作为结果返回给客户端；
d、重复上面a、b、c三步。
整个过程会先读取t2表的所有数据，然后遍历这每行数据中字段a的值，根据t2表中a的值索引扫描t1表中的对应行。如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，MySQL会选择Block Nested-Loop Join算法
2 ）、基于块的嵌套循环连接 （Block Nested-Loop Join(BNL)）
把驱动表的数据读到join_buffer中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer中的数据做对比。看如下示例：
Extra列中的Using join buffer(Block Nested Loop)说明该关联查询使用的是BNL算法。
上面SQL语句的大致流程：
a、把t2的所有数据读到join_buffer中；
b、把表t1中每一行取出来，跟join_buffer中的数据做对比；
c、返回满足join条件的数据。
整个过程对表t1和t2都做了一次全表扫描，因此扫描的总行数为表t1的数据总量+表t2 的数据总量。由于join_buffer里的数据是无序的，因此对表t1中的每一行，都要做很多次次（次数为join_buffer中的数据量）判断。
join_buffer 的大小是由参数 join_buffer_size设定的，默认值是256k，如果容量不够的话，会采取分段存放。比如t2表有600行记录， join_buffer一次只能放500行数据，那么就会先往join_buffer 里放500行记录，然后从t1表里取数据跟join_buffer中数据对比得到部分结果，然后清空join_buffer，再放入t2表剩余的100行记录，再次从t1表里取数据跟join_buffer中数据对比，所以就多扫了一次t1表。
被驱动表的关联字段没索引时为什么要选择使用BNL算法而不使用NLJ呢？
如果上面第二条SQL使用Nested-Loop Join，那么扫描行数为t2表数量*t1表数量，扫描行数过多且是磁盘扫描，性能很低的；而用BNL磁盘扫描次数会少很多，相比于磁盘扫描，BNL的内存计算会快得多。因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用BNL算法。如果有索引一般选择NLJ算法，有索引的情况下NLJ算法比BNL算法性能更高。
由此我们可以总结一下关联SQL的优化建议：
a、关联字段需要加索引，让MySQL做关联操作时尽量选择NLJ算法；
b、小表驱动大表，小表作为驱动表，大表作为被驱动表；
注意： 小表是指：每个表按照各自的条件过滤，过滤完成之后，参与join操作的总数据量，数据量小的那个表，就是小表，反之为大表。
因此，基于小表驱动大表的原则，我们在优化in和exsits时要注意：
a、 当B表的数据集小于A表的数据集时，优先使用in，比如：select * from A where id in (select id from B)；
b、 当A表的数据集小于B表的数据集时，优先使用exists，比如：select * from A where exists (select 1 from B where B.id = A.id)，将主查询A的数据，放到子查询B中作条件验证，根据验证结果（true或false）来决定主查询的数据是否保留。
SQL优化是一门艺术，除了以上内容外，还需要我们平时工作中根据业务做出更合理的优化方案！
