Mysql 慢查询日志 优化篇

解决慢查询

上一篇文章分析了慢查询，本篇主要讲解如何优化。基于 slow_query_log 和使用 pt-query-digest 分析后的结果，我们就可以开始对数据库中产生的慢查询进行优化。主要方式有：

• 给表加索引
• 修改代码
  – 修改在 for 循环中连接数据库的行为
  – 修改 search 方法和用 PHP_INT_MAX 作为 limit 的滥用
  – 修改 sql 中的 where 后的条件顺序
• 修改业务逻辑
  下面会用具体的例子讲解如何进行优化

Mysql备份工具：XtraBackup

通常我们对数据库进行操作之前需要备份。 在 mysql5.6 版本之前创建索引会锁表。
推荐备份工具 XtraBackup，Xtrabackup 是 Percona 公司开发的一款基于 InnoDB 的在线热备工具，具有如下优点：

1. 开源、免费
2. 支持在线热备
3. 备份恢复速度快
4. 占用磁盘空间小
5. 支持增量备份

官网地址

安装 XtraBackup

wget https://repo.percona.com/apt/percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_0.1-4.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-xtrabackup-24

参考 Installing Percona XtraBackup on Debian and Ubuntu

XtraBackup 的使用

mkdir ~/backup
# 备份数据库
sudo xtrabackup --backup --user=root --password='YOUR_PASSWORD' --target-dir=~/backup/mysql-backup
sudo xtrabackup --prepare --user=root --password='YOUR_PASSWORD' --target-dir=~/backup/mysql-backup

建立索引

索引原理

索引（Index）:是帮助 MySQL 高效获取数据的数据结构。

查询是数据库的最主要功能之一，为了提高查询速度，数据库设计者从查询算法的角度进行了优化。

因为特定的查询算法只能应用于特定的数据结构之上，而数据本身的组织结构不可能完全满足各种数据结构，所以数据库在维护数据的基础上，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

上图展示了一种可能的索引方式。左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址（注意逻辑上相邻的记录在磁盘上也并不是一定物理相邻的）。为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在O(log2n)的复杂度内获取到相应数据。

目前大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构。BTree除了降低查找的时间复杂度之外，通过利用存储系统的I/O策略降低了读写复杂度。

原理详细解释

加索引

下面我们将从具体例子中介绍如何加索引。
::: notice
该给哪些慢查询添加索引？从慢查询日志和分析结果上来说，使用 pt-query-digest 导出慢查询报表之后，我们更应该着眼于那些出现次数高的、慢查询时间比例高的 sql。从业务上来说，我们要首先着眼于那些被广泛使用，或影响网站正常运行的慢查询。
:::

1. ex1: 一般情况下的优化

SELECT * FROM question WHERE parentId IN 
('4398459','4398460','4398461','4398462','4398463','4398464','4398465','4398466','4398467',
'4398468','4398469','4398470','4398471','4398472','4398473','4398474','4398475','4398476',
'4398477','4398478'/*... omitted 8901 items ...*/)\G

问题: 该sql在某网站的慢查询日志中多次出现且平均耗时在10S以上，有时单次查询甚至要耗时100多秒。
首先我们用 explain 命令去看看该sql的查询情况

           id: 1
  select_type: SIMPLE
        table: question
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1207
     filtered: 50.00
        Extra: Using where

从结果来看(type:ALL) 该查询采用的方式是全表扫描，当数据量很庞大的时候耗时很高。

解决：给parentId增加了索引后，查询耗时降低至0.1秒左右

ALTER TABLE `question` ADD INDEX `parentId` (`parentId`);

再次使用 explain 命令去看看该sql的查询情况

           id: 1
  select_type: SIMPLE
        table: question
   partitions: NULL
         type: range
possible_keys: parentId
          key: parentId
      key_len: 5
          ref: NULL
         rows: 20
     filtered: 100.00
        Extra: Using index condition

从结果来看增加索引之后，该查询使用了 parentId 作为 key 来查询。

select  `active` = 0 from testpaper_result where 
`testId` = '69530' AND `userId` = '83032' AND `active` = 1\G

问题: 该sql同上，也存在十分严重的慢查询 同样使用 explain 命令去查看查询结果，会发现没有利用任何 key,通过全表扫描来得到结果，这在数据量太大的时候耗时是很高的。

解决：建立（testId, userId）索引,查询耗时降低至0.1秒左右

ALTER TABLE `testpaper_result` ADD INDEX `testId_userId` (`testId`, `userId`);

创建完索引后，再用explain看看结果

explain select  `active` = 0 from testpaper_result where  
`testId` = '69530' AND `userId` = '83032' AND `active` = 1\G;

         type: ref
possible_keys: testId_userId
          key: testId_userId
      key_len: 8
          ref: const,const
         rows: 1

从 explain 的结果来看，通过利用 testId_userId 的 key, 查询耗时降低至常量级

2. ex2: 给varchar列增加索引需要选择合适的长（前缀索引）

SELECT * FROM question WHERE target = 'course-8840/lesson-272073'\G

问题: 该sql对应的业务是题目管理的根据归属课程或课时搜索题目，在未加索引的时候搜索功能常常要等待很久才有结果，用户体验极差。

解决：对 target 建立索引，注意该列是verchar,所以建立索引的时候要指定键长。 键长要在足够区分检索结果的情况下设置的短些，在该例子下 键长30足够区别

ALTER TABLE `question` ADD INDEX `target` (`target`(30));

索引的选择性：不重复的索引值和数据表记录总数的比值。比值越接近1，选择性越高，性能越好

当我们要索引索引很长的字符列时，会让索引变得很大又慢，所以要索引开始的部分字符（即前缀索引），节约索引空间。 创建前缀索引的诀窍在于在选择足够长的前缀以保证较高的选择性。

假设一张存员工 FirstName 的表，要给 FirstName 列加索引
先看该列的选择性

select 1.0*count(distinct FirstName)/count(*) from employee

得到结果0.7500，我们希望前缀索引的选择性能够尽量贴近于对整个字段建立索引时的选择性。

首先看看3个字符的选择性

select 1.0*count(distinct left(FirstName,3))/count(*) from employee

得到的结果是0.58784，好像差距有点大，我们再试一试4个字符呢：

select 1.0*count(distinct left(FirstName,4))/count(*) from employee

得到0.68919，已经提升了很多.
再试一试5个字符，得到的结果是0.72297，这个结果与0.75已经很接近了，所以我们这里认为前缀长度5是一个合适的取值
所以决定索引的键长为5

alter table test.Employee add key(FirstName(5))

3. ex3:最左前缀匹配原则
   非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整；

SELECT count(id) FROM course_lesson_learn course_lesson_learn 
  WHERE (status = 'finished') 
    AND (finishedTime >= '1531411200') 
    AND (finishedTime <= '1531497600') 
    AND (courseId IN ('9288','9217','9215','8882','8192'))\G

问题: 同样是存在慢查询的情况，但如果建立（status, finishedTime, courseId）索引，则实际搜索的时候无法匹配到courseId，效果会大打折扣

解决： 要修改sql顺序，status和courseId在前，建立（status，courseId）索引

4. ex4: =和in可以乱序
   比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

5. ex5:尽量选择区分度高的列作为索引
   区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0。

SELECT * FROM course_lesson_replay 
  WHERE lessonId = '228748' 
    AND type = 'live' 
  ORDER BY replayId ASC\G

问题: 存在慢查询情况，但是我们在这里要考虑到底是选择lessonId还是选择type来建立索引，不同的选择结果带来的优化效果完全不同

解决：表course_lesson_replay 中的数据列type基本为’live’,所以如果建立type的索引基本没有优化效果 通过lessonId索引比type可以大大缩小要搜索的范围，所以给lessonId增加索引

ALTER TABLE `course_lesson_replay` ADD INDEX `lessonId` (`lessonId`);

6. ex6: 索引列不能参与计算，保持列“干净”

SELECT COUNT(*) FROM sessions WHERE `sess_time` >= (unix_timestamp(now()) - '900');

DELETE FROM sessions WHERE sess_lifetime + sess_time < 1494490355;

问题： 管理后台在线用户数统计。SQL写法错误，不应让mysql去计算时间戳，这样无法利用上索引。

解决： 改代码。

$time = time() - 900;
$sql = SELECT COUNT(*) FROM sessions WHERE `sess_time` >= ?;

7. ex7: 尽量的扩展索引，不要新建索引。

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

#origin sql:
SELECT * FROM status status WHERE courseId IN ( '81' ) ORDER BY createdTime DESC LIMIT 5;

#status表已经存在的索引:
KEY `userId` (`userId`),
KEY `createdTime` (`createdTime`),
KEY `courseid_classroomid_private_createdTime` (`courseId`,`classroomId`,`private`,`createdTime`),
KEY `status_courseid` (`courseId`),
KEY `status_classroomId` (`classroomId`),
KEY `courseId_createdTime` (`courseId`,`createdTime`)

问题：MySQL自身的索引优化器选错了索引导致慢查询

解决：删掉了2个无效索引后，MySQL索引优化器工作恢复了正常，选择了正确的索引courseId_createdTime。

ALTER TABLE `status` DROP INDEX `status_courseid`;
ALTER TABLE `status` DROP INDEX `courseid_classroomid_private_createdTime`;

8. ex8: 复杂的sql，建议从修改业务逻辑的方向解决，而不是一味的增加索引。

SELECT COUNT(m.id) FROM course_member m 
  INNER JOIN course_v8 c ON m.courseId = c.id 
    WHERE m.userId = '87718' 
      AND m.role = 'student' 
      AND (m.learnedNum < c.publishedTaskNum 
        OR c.serializeMode = 'serialized')

问题：我的学习页面中，学习中的课程数量的查询。业务逻辑过于复杂，也不合理，可以简化。

解决：和产品沟通，修改业务

SELECT * FROM status status 
  WHERE courseId IN ( '7822','5049','4267','4266','5806','5646','4495' ) 
    OR classroomId = '370' ORDER BY createdTime DESC LIMIT 10 OFFSET 0\G

问题: 该 sql 的业务是班级动态的展示，原来的设计是课程的动态存 courseId，班级的动态存 classroomId,所以当需要查班级及包括班级下的课程的动态时，要用 or 去搜索，不仅速度慢，光给 classroomId 将所以还不能解决问题。

解决：这时就需要修改增加动态的逻辑了，如果是班级课程的动态，不仅要存 courseId,也要存 classroomId,这样 sql 的 where 条件只用到 classroomId，加索引就简单了。

SELECT * FROM status 
  WHERE classroomId = '370' 
  ORDER BY createdTime DESC LIMIT 10 OFFSET 0\G

ALTER TABLE `status` ADD INDEX `classroomId` (`classroomId`);

总结

1. 要多使用explain命令来查看sql的优化情况
2. 单纯的增加索引来提升查询效率不可取。 业务复杂的要简化业务和修改实现逻辑。 技术实现和产品需要之间需要权衡。(ex8)
3. search方法的滥用会导致 查找慢查询业务场景 和重构等的困难。要明确search方法的场景 （指明哪些场景）
4. 使用PHP_INT_MAX来做LIMIT查询数量有时候也是慢查询的罪魁祸首，要杜绝！在search方法中使用泛滥。
5. where后跟的条件顺序是有先后的，别乱加。(ex3)
6. 别看到1条sql就加1个索引，要把该表出现的慢查询sql汇总起来，权衡需要加的索引。（太多的索引会导致选择错误的索引引起慢查询）(ex7)

上一篇 Mysql慢查询日志 分析篇
下一篇 Debounce，Throttle概念及应用

EduSoho官网 https://www.edusoho.com/
EduSoho开源地址 https://github.com/edusoho/edusoho
我们正在寻找开发者