前言

前面三篇内容我们花了大量时间介绍了索引及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）类似，首先判断是否是“=”，“<=”中的一种，如果是，加入界定，继续下一个索引键值匹配，如果是“<”，加入界定，停止匹配。

注意： 这里可以简化为，如果比较符号中包含'='号，'>='也是包含'='，那么该索引键是可以被利用的，可以继续匹配后面的索引键值；如果不存在'='，也就是'>','<'，这两个，后面的索引键值就无法匹配了。同时，上下边界是不可以混用的，哪个边界能利用索引的的键值多，就是最终能够利用索引键值的个数。

分析完上面范围后，来分析一下between和like，MySQL是如何进行处理的呢？where c1 between 'a' and 'b' 等价于 where c1>='a' and c1 <='b'，所以进行相应的替换，然后带入上面的范围模型中，确定上下边界即可。

至于like首先需要确认的是%不能是最在最左侧，where c1 like '%a' 这样的查询是无法利用索引的，因为索引的匹配需要符合最左前缀原则where c1 like 'a%'  其实等价于 where c1>='a' and c1<'b' 大家可以仔细思考下。

二、利用索引调优SQL时的注意事项

1 、索引调优注意事项

1 ）联合索引时第一个字段尽量不用范围，否则会不走索引。 联合索引第一个字段就用范围查询时，MySQL内部可能会觉得第一个字段就用范围，结果集应该很大，回表效率不高，不如全表扫描。

2） 强制走索引方法

如果在正确的情况下，该走索引的地方却没有走索引，此时可以采用强制走索引。

如上SQL，在1）中没有走索引，采取强制走索引时，第一个字段范围查找也走了索引。

3） 尽量使用覆盖索引进行优化

比如1）中的SQL没有走索引，如果我们采用覆盖索引时会走索引的

4） in 和or在数据量比较少的情况下会不走索引而是选择全表扫描

如果将employees表弄一张复制表，数据结构一致，只是数据量很少，比如只有几十条数据，以上的SQL还会走索引么？

由此我们可以看到数据量少的情况下是不会走索引的。

5） 索引下推

什么是索引下推？在MySQL5.6版本引入了索引下推，就是在索引遍历过程中，对索引中所包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表查询，这样就减少了回表查询的次数，提高了查询效率。

比如：对于1）中的SQL语句：SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager'，虽然有辅助的联合索引(name,age,position)，正常情况下按照最左前缀原则，只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引，所以在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。但是在MySQL5.6及以后版本中，使用了索引下推优化后，联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完后剩下的索引对应的主键id再回表查整行数据。

6） trace 工具查看MySQL如何走索引

对于MySQL最终是否选择走索引或者一张表涉及多个索引，MySQL最终如何选择索引，我们可以用trace工具来一查究竟，至于trace工具的具体使用，各位可以自己去查找资料研究一下，这里就不再详细介绍了。要记住开启trace工具会影响MySQL性能，所以临时分析SQL时可以使用，最好用完之后立即关闭，以免影响性能。

2 、索引创建/设计需要注意哪些原则？

1 ）、频繁出现在where条件字段，order by排序，group by分组的字段可考虑建立索引；where与order by冲突时优先where；

2 ）、select频繁查询的列，可考虑创建联合索引（覆盖索引，不回表）；

3 ）、多表join关联查询，on字段两边的字段尽量都要创建索引；

4 ）、表记录很少不要创建索引（因为索引有存储、修改、删除的开销，通常表数据量较少时MySQL会选择全表扫描）；

5 ）、一个表的索引个数不能过多；

索引个数过多会造成：

a、空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间。

b、时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树。太多的索引也会增加优化器的选择时间。所以索引虽然能够提高查询效率，索引并不是越多越好，应该只为需要的列创建索引。

6 ）、频繁更新的字段不建议建立索引；频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高。

7 ）、区分度低的字段不要建索引；比如性别，只有男和女最多再多一个未知，区分度太低，会导致扫描行数过多，和回表查询次数过多。如果使用索引，比全表扫描的性能还要差。这些字段一般会用在组合索引中。姓名，手机号等区分度比较高的列非常适合建索引。

8 ）、不建议用无序的值作为索引；例如身份证、UUID。更新数据时会发生频繁的页分裂，页内数据不紧凑，浪费磁盘空间。

9 ）、尽量创建组合索引，而不是单列索引；因为1个组合索引等同于多个索引效果，节省空间；同时可以使用覆盖索引；

10 ）、组合索引应该把频繁用到的列、区分度高的值放在前面； 频繁使用说明了索引的利用率高，区分度高说明筛选粒度大，这样做可最大限度利用索引价值，缩小筛选范围；

11 ）、业务上具有唯一特性的字段，即使是组合字段，也尽量建成唯一索引； 因为唯一索引对提高查找速度是明显的；同时也可以防止脏数据的产生。

12 ）、在varchar字段上建立索引时，要指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。因为索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为20的索引，区分度会高达90%以上，可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。

三、 常用问题SQL优化

在写SQL时，常常有人为了方便或者由于不理解底层原理导致写了很多问题SQL，下面我们从常见的问题SQL入手逐个分析如何进行优化。

为了方便SQL优化实战，我们先创建一张示例表并插入数据：

CREATE TABLE employees (

 id int(11) NOT NULL AUTO_INCREMENT,

 name varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',

 age int(11) NOT NULL DEFAULT '0' COMMENT '年龄',

 position varchar(20) NOT NULL DEFAULT '' COMMENT '职位',

 hire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',

 PRIMARY KEY (id),

 KEY idx_name_age_position (name,age,position) USING BTREE

 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

1 、SELECT * 语句的优化

这样的SQL相信很多人都写过吧，这种写法在特定的环境下会存在性能损耗的。MySQL常用的存储引擎有InnoDB和MyISAM，其中InnoDB为默认存储引擎，在InnoDB下会默认创建主键索引（也就是聚簇索引），也就是说索引是基于B+Tree构成的，具体的行数据则存储在叶子节点。而MyISAM存储引擎下默认创建的主键索引、二级索引以及 InnoDB 的二级索引都属于非聚族索引，即在存储数据时，索引是基于B+Tree构成的，而叶子节点存储的是主键值（MyISAM存储引擎下叶子节点存储的是数据行的地址）。

如果一张表A（有基于a、b建立的联合索引）有多个字段a、b、c、d、e等，此时我们只需要查询a和b字段，比如：select a、b from A where a = xxx，如果写成select * from A where a = xxx的话会造成额外的性能损耗，因为它会先查询组合索引，通过组合索引获取到主键ID，再通过主键 ID去主键索引中获取对应行所有列的值，多了一次回表查询，而直接用a、b代替“*”的话只需要通过联合索引就能查询到我们需要的值。

2 、SELECT COUNT(*) 语句的优化

COUNT()是一个聚合函数，主要用来统计行数的，有时候也用来统计某一列的行数量（不统计NULL值的行）。平时最常用的就是COUNT()和COUNT(1)这两种写法，其实两者没有明显的区别，在拥有主键的情况下，它们都是利用主键实现了行数的统计。但COUNT()函数在MyISAM和InnoDB存储引擎所执行的原理是不一样的，通常在没有任何where子句条件下的COUNT()，MyISAM的查询速度要明显快于InnoDB。这是因为MyISAM存储引擎有记录整个表的行数，在COUNT(*)查询操作时无需遍历表计算，直接获取该值即可。而在InnoDB存储引擎中就需要扫描表来统计具体的行数。而当带上where条件语句之后，MyISAM跟InnoDB就没有区别了，它们都需要扫描表来进行统计。

如果对一张大表经常需要做SELECT COUNT(*) 操作，我们该如何进行优化呢？如果业务场景并不需要返回一个精确的COUNT值，此时我们可以使用近似值来代替。我们可以使用EXPLAIN对表进行估算，要知道，执行EXPLAIN并不会真正去执行查询，而是返回一个估算的近似值。如果需要一个精确的COUNT值，我们可以额外新增一个汇总统计表或者缓存字段来统计需要的COUNT值，这种方式在新增和删除时有一定的成本，但却可以大大提升COUNT()效率。

3 、分页查询语句的优化

通常我们是使用LIMIT M,N + order by来实现分页查询，这种实现方式如果在没有任何索引条件支持的情况下，需要做大量的文件排序操作（即file sort），性能会很慢。如果有对应的索引，通常刚开始的分页查询效率会比较理想，但越往后，分页查询的性能就越差。这是因为我们在使用LIMIT的时候，偏移量M在分页越靠后的时候，值就越大，数据库检索的数据也就越多。比如：LIMIT 10000,10这样的查询，数据库需要查询10010条记录， 最后返回10条记录，也就是说有前10000条记录被查询出来没有被使用到。那针对这种情况改如何优化呢？

1 ）、利用 自增且连续的主键排序进行优化

比如SQL语句：select * from employees limit 90000,5；该SQL表示查询从第90001开始的五行数据，没添加单独order by，表示通过主键排序。我们再看表employees，因为主键是自增并且连续的，所以可以改写成按照主键去查询从第90001开始的五行数据、即：select * from employees where id > 90000 limit 5；来对比一下这两条SQL的执行计划分析：

从上面的执行计划可以看出，显然改写后的SQL走了索引，而且扫描的行数大大减少，执行效率更高。

但是，这种情况下，改写后的SQL使用场景很有限，如果表中某些记录被删会造成主键空缺，导致查询结果不一致，比如将id<90000的数据随便删除一条或多条就会造成查询结果不一致。所以此种方法必须同时满足以下两个条件才行：a、主键自增且连续；b、结果是按照主键排序的。

除了上面的方案外还有一种相对比较灵活的方案？请看接下来的第2种方案。

2 ）、利用子查询来优化

比如针对上面原始的SQL语句：select * from employees limit 90000,5；我们可以先查询出所需要的5行数据中的最小的id值，然后通过偏移量返回所需要的5行数据，可以利用索引覆盖扫描，使用子查询的方式来实现分页查询，可以改写成：select * from employees where 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优化是一门艺术，除了以上内容外，还需要我们平时工作中根据业务做出更合理的优化方案！