mysql慢查询优化方案

       

1. 分页查询优化

在平时开发中，免不了使用分页，很多时候我们业务系统实现分页功能可能会用如下sql实现：

mysql> select * from employees limit 10000,10;

       这种查询方式表示从表 employees 中取出从 10001 行开始的 10 行记录。看似只查询了 10 条记录，实际这条 SQL 是先读取 10010 条记录，然后抛弃前 10000 条记录，然后读到后面 10 条想要的数据。因此要查询一张大表比较靠后的数据，执行效率是非常低的。所以在应对大数据量分页查询时，需要进行分页查询优化！

注意：LIMIT不会对返回的数据施加任何排序。 除非使用ORDER BY子句，否则无法保证每次执行限制都会返回相同的顺序。如果插入数据，这可能会影响有序和无序结果。 如果省略ORDER BY子句，则基本上由数据库来决定排序，它可能基于表的索引方式。随着表的重新索引和/或添加更多数据，这将随时间而变化。所以要返回有序的分页数据请使用 order by + limit

分页查询优化方案一般有两种
       

方法一：当主键是自增并且连续时，先根据主键过滤一部分数据，然后在剩下的数据中，再取一页数据（5条）

首先来看一个根据自增且连续主键排序的分页查询的例子：

mysql> select * from employees limit 90000,5;

该 SQL 表示查询从第 90001开始的5行数据，这种方式就如上面所说，大数据时存在效率问题，可以做以下改进

mysql> select * from employees where id > 90000 limit 5;

因为主键是自增并且连续的，所以可以改写成按照主键去从索引树上直接定位id，查询第 90001开始的5行数据，这样不仅用到了索引，还减少了扫描次数！

       

两者查询结果是一致的！再对比一下执行计划：

select * from employees limit 90000,5; 的执行计划如下：

select * from employees where id > 90000 limit 5;的执行计划如下：

显然改写后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。

       
但需要注意的是：

       这种分页优化场景在开发中并不常用，因为这种的必须保证主键是自增并且连续的，然而在实际开发中，已插入的数据是有可能被删除的。如果数据被删除，则id就会变得不连续！那么limit 90000,5和id > 90000 limit 5取到的结果就可能不一致！

测试：

如果删除0 - 90000之间任意一条数据，则：

select * from employees limit 90000,5; 的执行结果如下：

select * from employees where id > 90000 limit 5;的执行结果如下：

可以看到两者的查询结果不一致

• limit 90000,5：以行数为基准，是从第 90001行开始查5行数据，因为前边删除了一行，所以第 90001行的id是90002
• id > 90000 limit 5：以id为基准，只查id > 90000行的5行数据，即使前边删除了一行，也依然从 id = 90001行开始收集！

另外，如果 原 SQL 是 order by 非主键的字段（比如 order by name），按照上面说的方法改写也会导致两条 SQL 的结果不一致。因为当order by name 时，是以name的Ascall码当做基准来排序的，id是乱的！

• limit 90000,5：表示的是按照name排序后的结果中，是从第 90001行开始取5行数据
• id > 90000 limit 5：表示按照name排序后的结果中，找到id>90000 的数据，取5条，因为按name排序，id是乱的，所以结果与上面的不一致！

所以这种改写得满足以下两个条件：

• 主键自增且连续
• 结果是按照主键排序的
         

方法二：使用 inner join 优化分页查询

当使用主键排序的分页查询时：

   select * from employees  a 
   INNER JOIN 
	(select * from employees where id > 90000 LIMIT 5) b 
   on  a.id = b.id

上面的sql可以看作是方法一的另一个版本，两个版本都是按照主键排序的，都需要主键自增且连续，只不过这里使用的是 inner join 的方式去查询

       
当使用非主键字段排序的分页查询时:

select * from employees ORDER BY name limit 90000,5;

执行计划如下：可以看到使用了全表扫描
这种分页查询才是开发中经常使用的：数据不一定连续，且不一定是按照主键排序！这种我们就无法使用方法一以id做排序查询！

       对于这种情况，可以对name字段建立索引，或者建立覆盖索引以name开头，当然就算为name建立了二级索引，但有的时候mysql执行器会觉得走二级索引再回表的效率，还不如直接扫一下全表，所以优化器放弃使用索引。这样也不会走name索引，如上执行计划所示！（建立的是（name、age、position）复合索引）

       可以使用inner join优化，优化的关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键（select id from employees order by name limit 90000,5，这个查询的数据都在覆盖索引上，肯定会走索引），然后根据查到的主键id再 inner join 全表查到对应的记录，SQL改写如下

SQL改写如下:

mysql> select * from employees e
inner join 
	(select id from employees order by name limit 90000,5) ed 
on e.id = ed.id;

执行计划如下：

       可以看到，需要的结果与原 SQL 结果一致，执行时间减少了一半以上，原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序，优化后的sql基本都用到了索引！

       

2. 最左前缀原则，覆盖索引优化

背景：建立 name,age,position复合索引

       

①：覆盖索引优化

       根据最左前缀原则，一般联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

查询语句如下：

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

可以看到没有走索引，全表扫描

       可以试着让该条查询语句强制走索引试试: force index,虽然使用了强制走索引让联合索引第一个字段范围查找也走索引，扫描的行rows看上去也少了点，但是最终查找效率不一定比全表扫描高，因为回表效率不高，所以要尽可能的相信mysql自己的索引成本计算，让mysql自己选择较低成本的查询方案

EXPLAIN SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei' AND age = 22 AND position ='manager';

使用覆盖索引优化

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

       因为select后面跟着覆盖索引的 索引列name,age,position，mysql在执行查询时，只需要在覆盖索引树上查询即可，也不用回表，因为一开始建立的那颗索引树上已经包含了本次要查询的结果了！
       

②：索引下推优化 like xx%

like 查询：

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

可以看到以like 'LiLei%'作为第一个查询条件也可以使用到 name,age,position复合索引， like LiLei%其实就是用到了索引下推优化！

什么是索引下推？

       对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。

       在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合，没有再进行下推优化！

       在MySQL 5.6版本中，引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。

       索引下推会减少回表次数，因为可以在索引中过滤掉很多数据，只对少数数据的id进行回表。 对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。

       

③：Order by与Group by优化

order by：排序时满足以下情况会使用到索引 Using index，

• order by后面的字段顺序遵循索引最左前缀原则。
• where子句与order by子句条件列组合满足索引最左前缀原则。
• 如果order by后面的字段不在索引列上，就会产生文件排序Using filesort。文件排序相当耗费性能，因为它会取出满足条件行的所有字段，在sort buffer，也就是内存中进行排序，挺耗性能的！尽量避免！

Using index： where子句与order by子句组合都在索引列name,age,position上

Using filesort：索引的创建顺序为name,age,position，但是排序的时候age和position颠倒位置了。

其他 Order by的索引使用情况

对于排序 Order by来说，多个相等条件也是范围查询，如in、>、<、or等，也会使用文件排序Using filesort

这种情况可以使用覆盖索引优化

       另外，虽然排序的字段列与索引顺序一样，但索引字段有的按照升序排列，有的按照降序排列，这种情况也会出现Using filesort，因为在排序时，order by的排序方式与索引的排序方式不一样！无法用到索引的排序方式

Group by

• group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。
• 对于group by的优化如果不需要排序的可以加上order by null禁止排序。
• 注意，where高于having，能写在where中的限定条件就不要去having限定了。

       

④：长字符串前缀索引优化

       在建立索引时，尽量对字段类型较小的列设计索引，比如说什么int 、tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，在搜索的时候性能也会比较好一点。

       但天不遂人愿，有些场景下就是要对一些长的字符串建立索引，比如：varchar(500)这样的，如果把这种字段建立索引，是比会占据较大的磁盘空间，可以使用长字符串前缀索引对其进行优化！

       可以对这种长字符串的前20个字符建立索引，也就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 index(name(20),age,position)，只对name字段的前20个字符建立索引。如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到前20个字符的前缀匹配的部分数据之后，再回表到主键聚簇索引提取出来完整的name字段值进行比对。

       但是这种前缀索引对于order by和group by 来说是无效的，因为前缀索引并不是完整索引，所以在排序时还是会进行文件排序filesort！

       

⑤：避免小基数字段上建立索引

       小基数字段是指数据库中那些分类较少的字段，比如User表中性别字段，其值不是男就是女，那么该字段的基数就是2。如果对其建立索引，那么B+Tree上就只有男、女两个分叉，每个分叉的数据量极大，在使用where sex = '男进行查询时，需要遍历男分叉上所有数据，其过程类似于全表扫描！无法发挥B+Tree的快速二分查找的优势。

       一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+Tree快速二分查找的优势来

       

⑥：join查询优化

mysql的表关联查询，一般底层常用的有两种算法

• Nested-Loop Join(NLJ)算法：嵌套循环连接算法
• Block Nested-Loop Join (BNLJ)算法：基于块的嵌套循环连接算法

       

1. 嵌套循环连接 Nested-Loop Join(NLJ) 算法

       NLJ算法的逻辑是：一次一行，循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段（on后边的字段id），根据关联字段id在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

背景：两张表 t1，t2，有字段id，a，b

• 往t1表插入10000行记录，作为一张大表，字段a为单值索引
• 往t2表插入100行记录，作为一张小表，字段a为单值索引

       
查询 t1 和 t2表中的公共数据sql，关联字段a为索引字段

select * from t1 inner join t2 on t1.a = t2.a;

由于 join on 的条件a在两表中都是索引列，可以使用NLJ算法，所以上面sql的大致流程如下：

1. 从驱动表 t2 中读取一行数据（如果t2表有查询过滤条件的，用先用条件过滤完，再从过滤结果里取出一行数据）；
2. 从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；
3. 取出被驱动表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4. 重复上面第 1、2、3 步，直到找到所有符合条件的结果集

       整个过程会读取 t2 表的所有数据(扫描100行)，然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行（扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据，也就是总共 t1 表也扫描了100行）。因此整个过程扫描了 200 行。

使用Explain查看执行计划

       可以看到，t2表使用的是全表扫描，而t1表使用的是ref级别的索引，所以驱动表是 t2，被驱动表是 t1。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；

小表驱动大表

• 小表的定义：两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
• 在使用inner join时，mysql的优化器一般会优先选择数据量小的表t2做驱动表，用where条件过滤完驱动表，然后再跟数据量大的被驱动表t1做关联查询。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
• 在使用left join时，无论数据大小，规定左表是驱动表，右表是被驱动表！
• 在使用right join时，无论数据大小，规定右表是驱动表，左表是被驱动表！

如何识别当前查询是否用的NLJ算法?

• 如果执行计划 Extra 列中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
• join on t1.a = t2.a中on后边跟的字段a，要在两个表中都是索引字段！

       

2. 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

       如果join on join on t1.b = t2.b中的b在被驱动表t1中不是索引字段，使用NLJ算法性能会比较低，mysql会自动优化为BNLJ算法。

       BNLJ算法会把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。可以看到与NLJ算法的区别就在于新增了 join_buffer 缓存！

       
查询操作，关联字段b为非索引字段！

 select * from t1 inner join t2 on t1.b= t2.b;

执行计划如下：

可以看到，Extra 列中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNLJ 算法。

上面sql的大致流程如下：

• 把驱动表 t2 的所有数据放入到 join_buffer 中
• 把被驱动表 t1 中每一行取出来，跟 join_buffer 中的数据做对比
• 返回满足 join 条件的数据

       由于关联字段b并不是索引字段，所以执行计划中的type都是ALL，整个过程对表 t1 和 t2 都做了一次全表扫描，因此磁盘扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) = 10100行，并且 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是 100 * 10000= 100 万次。但这个判断操作是在join_buffer中，也就是内存中完成的，相比于磁盘来说是较快的

       

为什么关联字段为非索引字段时，NLJ算法比较耗时呢？

       NLJ算法没有join_buffer的概念，会逐条取出驱动表t2的数据，根据取出的数据的关联字段b去扫描被驱动表t1中等于b的数据，由于关联字段b并非索引，极端情况可能会扫描10000 x 100 = 1000000次，注意这一百万次扫描都是磁盘扫描！还有一百万次的比较

       而BNL由于有 join_buffer 的存在，可以暂存一些扫描数据。一次扫描可以很多数据放入 join_buffer 中，磁盘扫描次数相对于NLJ算法会少很多。而且BNL的比较计算由于是直接在内存中比较的，也会快得多。

       因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高

       
join_buffer 放不下驱动表数据怎么办？

       驱动表 t2 才 100 行，要是表 t2 是一个大表，join_buffer 放不下怎么办呢？join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

       比如驱动表 t2 中有1000行记录， join_buffer 一次只能放800行数据，那么执行过程就是先往 join_buffer 里放800行记录，然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，然后清空 join_buffer ，再放入 t2 表剩余200行记录，再次从 t1 表里取数据跟 join_buffer 中数据对比。所以就多扫了一次 t1 表，扫描判断的次数增多了！

       

join查询优化总结

• 关联字段加索引，让mysql做join操作时尽量选择NLJ算法，驱动表因为需要全部查询出来，所以过滤的条件也尽量要走索引，避免全表扫描，总之，能走索引的过滤条件尽量都走索引
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表时，要合理使用left join、right join，以小表驱动大表！

 

3. 其他优化方案

1. 尽量避免在 WHERE 子句中对字段进行 NULL 值判断。创建表时 NULL 是默认值，建表时，大多数时候应该使用 NOT NULL，或者使用一个特殊的值，如 0，-1 作为默认值。

2. 应尽量避免在 WHERE 子句中使用 != 或 <> 操作符。MySQL 只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及某些时候的 LIKE。

3. 应尽量避免在 WHERE 子句中使用 OR 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，可以使用 UNION 合并查询：select id from t where num=10 union all select id from t where num=20。

4. 索引列上无计算，应尽量避免在 WHERE 子句中对字段进行函数操作。索引列上有函数计算会导致索引失效，如果一定要有计算，可以根据实际情况把计算公式转化到= 号右边，如下：

   //索引上有函数，索引失效，耗时13秒
   SELECT * FROM record WHERE substrINg(card_no, 1, 4) = '5378' --13秒 
   //转移函数位置 ，索引生效， 耗时1秒
   SELECT * FROM record WHERE card_no like '5378%' -- < 1秒 
   

5. 使用“临时表”暂存中间结果 。将需要查询的结果预先计算好放在表中，后面需要该数据就去该 临时表中查了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。

6. 合理使用in，MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。例如：select id from table_name where num in(1,2,3) 这种对于连续的数值，能用 between 1 and 3就不要用 in 了

 

4. 一条sql执行很慢，什么原因？

一个 SQL 执行的很慢，我们要分两种情况讨论：

• 情况一：大多数情况下很正常，偶尔很慢

  • 触发了同步刷盘机制：当我们要往数据库插入一条数据、或者要更新一条数据的时候，我们知道数据库会在内存中把对应字段的数据更新了，但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。不过，redo log 里的容量是有限的，如果更新太频繁导致 redo log 很快就会被写满了，这个时候就会触发同步刷盘。暂停正常的sql执行，就会表现出sql执行突然很慢！
  • 存在锁竞争，我们要操作的数据或表，刚好别的连接正在用，且加了表锁或者行锁。此时我们的sql操作就需要等到别的连接锁释放，才可以使用，同样表现出sql执行速度变慢

• 情况二：一直都执行很慢

  • 没有使用索引：给合适的列建立索引，并在操作时走到索引列！
  • 索引失效：可以使用explain打印sql执行计划，修改sql走到索引列

 

5. 建表时是否设置 not null

建表示例

CREATE TABLE `shopee_task_time_record` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `platform_id` smallint NOT NULL COMMENT '平台编号',
  `shop_id` bigint NOT NULL COMMENT '店铺编号',
  `task_id` int NOT NULL COMMENT '任务id',
  `task_name` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci  NOT NULL DEFAULT '' COMMENT '任务名称',
  `task_time_record`  datetime DEFAULT NULL COMMENT '本次数据同步的截至时间from_to，下次拉取从此时间开始',
  `original_price` decimal(19,4) NOT NULL DEFAULT '0.0000' COMMENT '商品原价',
  `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='shopee同步时间记录表';

先来看mysql官网的一段话

NULL columns require additional space in the rowto record whether
their values are NULL. For MyISAM tables, each NULL columntakes one
bit extra, rounded up to the nearest byte.

意思就是：NULL列在行中需要额外的空间以记录其值是否为NULL。 对于MyISAM表，每个NULL列都多花一位，四舍五入到最接近的字节。

所以建表时，默认null值会带来以下问题：

• 空间占用：“ ” 在存储过程中是不会占用空间的，但是NULL会

  mysql> SELECT length('1'),length(NULL),length('');
  
  +-------------+--------------+------------+
  | length('1') | length(NULL) | length('') |
  +-------------+--------------+------------+
  |           1 | NULL         |          0 |
  +-------------+--------------+------------+
  1 row in set
  
  

• Null值不会被统计：如果使用count()等统计函数，将不会统计NULL，使用sum()时，如果所有列都为null，则sum的结果也为null，需要使用IFNULL(SUM(age),0)再做转换

  mysql> SELECT * FROM `user`;
  +----+------+-----+---------+
  | id | name | age | address |
  +----+------+-----+---------+
  |  1 | wyf  |  32 | 合肥    |
  |  2 | xx   |  31 | 北京    |
  |  3 | yy   |  30 | 上海    |
  |  4 | zz   |  11 |         |
  |  5 | aa   |  21 | NULL    |
  +----+------+-----+---------+
  5 rows in set
  
  mysql> SELECT COUNT(address) FROM `user`;
  +----------------+
  | COUNT(address) |
  +----------------+
  |              4 |
  +----------------+
  1 row in set