MySQL查询性能优化

1.为什么查询速度会慢？

​ 首先对一个查询的生命周期简化分析，大致可以按照顺序分为：从客户端、到服务器、然后在服务器进行解析、生成执行计划、执行、并返回结果给客户端。其中的执行可以被认为是查询的生命周期中最重要的部分，执行主要包括了大量为了检索数据到存储引擎的调用以及调用后数据的排序、分组等处理。

​ 在完成生命周期的这些任务的时候，查询需要在不用的地方消耗时间，包括网络、CPU计算、生成执行计划、锁等待等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用操作需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗时间，根据存储引擎不同，可能还会产生大量的上下文切换以及系统调用小号的时间。所有的这些任务消耗的时间共同构成了响应时间，在每一个消耗大量时间的查询操作中，我们都能看到一些不必要的额外操作，某些操作被额外的重复了很多次，某些操作执行太慢等，优化查询的目的就是减少和消除这些额外操作所花费的时间。

2.慢查询基础：优化数据访问

​ 查询性能低下的最根本原因就是访问的数据太多。虽然某些查询可能不可避免的需要筛选大量的数据，但这并不常见。大部分性能低下的查询都可以通过减少访问数据量的方式进行优化。可以通过以下两步进行分析：

​ 1.确认是否存在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是因为访问了太多的列。

​ 2.确认MySQL服务器层是否存在分析大量超过需要的数据行。

2.1是否向数据库请求了不需要的数据？

  • 查询不需要的记录
  • 多表关联时返回全部的列
  • 总是取出全部列

针对以上三点我这里试举几个典型案例：

​ 1.查询不需要的记录

​ 前端分页就是经典例子，查询实际需求显示第一页10条， 却返回100万条数据，性能低下显而易见，建议后端分页 LIMIT 0, 10

​ 2.多表关联时返回全部的列

​ 假如你想查询所在电影大话西游中出现的演员，千万不能按照如下的方式查询，这将返回这三个表全部的列：

SELECT * FROM actor
    INNER JOIN film_actor USING(actor_id)
    INNER JOIN film USING(film_id)
    WHERE film.title = '大话西游';

​ 正确的做法是：

-- 规定只查询 actor 表的列
SELECT actor.* from actor ...

​ 3.总是取出全部的列

​ 每次看到 SELECT * 的时候都要用怀疑的眼光审视，是不是真的需要返回全部的列？很可能是不必须的，取出全部列，会让优化器无法完成索引扫描这类优化，还会为服务器带来额外的 I/O、内存和CPU消耗。

2.2 MySQL是否在扫描额外的记录？

​ 在确定查询只返回需要的数据后，接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL而言，最简单的衡量查询开销的三个指标如下：

  • 响应时间
  • 扫描的行数
  • 返回的行数

​ 没有哪个指标能够完美的衡量查询的开销，但是他们大致反映了MySQL内部执行查询时候需要访问多少数据，并可以大概推算出查询运行的时间。这三个指标都会记录到MySQL的慢日志中，所以检查慢日志记录是找出扫描行数过多的查询的好办法。

下面通过一个例子来查看一下慢日志：

  • 存在一个部门表t_dept，包括100万条部门记录，现在查询部门名称为事询部的所有部门
-- 没建立索引的情况下，100万条数据中查询大概0.8秒
SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事询部';
  • 首先我将慢日志记录时间设置为0.5秒，然后指定了慢日志位置
-- 将慢日志时间设置为0.5
set long_query_time=0.5;

-- 设置慢日志位置
set global slow_query_log_file='E:\mysql_slow_query.log';
  • 执行上面的查询操作，之后查看慢日志
图1

  • 从慢日志中可以看到上面说到的主要指标：查询事件、返回行数、扫描行数

  • 问题来了，返回行数899行，却扫描了整个表100万条数据，当然效率低下了，明显用到了全表扫描，EXPLAIN执行计划中的type列说明了访问类型。执行看一下：

-- 执行查询计划
EXPLAIN SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事询部';
图2

​ 可以看到type访问类型ALL全表扫描，预估访问97万行数据，Using Where代表MySQL将通过WHERE条件来筛选存储引擎返回的记录。

  • 一般MySQL通过如下三种方式应用WHERE条件，从好到坏依次是：

    1.在索引中使用WHERE条件来过滤不匹配的记录，这是在存储引擎层完成的

    2.使用索引覆盖扫描（在Extra中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果，这是在MySQL服务器层完成的。

    3.从表中返回数据，然后过滤不满足条件的记录（在Extra中显示Using Where）再试在MySQL服务器层完成，MySQL需要先从数据表读出数据然后过滤。

2.3 加一个索引试试

-- 给部门表的部门名添加一条索引
ALTER TABLE t_dept ADD INDEX index_department(department);
-- 将慢sql记录时间调整成0秒
set long_query_time=0
-- 再次执行前面的查询
SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事询部';
-- 查看执行计划
EXPLAIN SELECT t.id, t.department
    FROM t_dept t
    WHERE t.department = '事询部';
图3
  • 可以看到访问类型已经从全表扫描ALL变成了非唯一性索引扫描ref,预计访问行数由97万行变成了899行，Extra显示了WHERE条件使用了索引覆盖扫描。
  • 下面看一下慢日志，注意要将慢日志记录时限改成0秒，不然记录不下来：
图4
  • 由原来的的0.8秒变成了0.000562秒，正确合适的使用索引优化速度显而易见。

总结：

如果发现查询需要扫描大量的数据行，而只返回其中少量的数据行，这样就可以尝试这样优化：

1.使用索引覆盖扫描，把所有需要用的列放到索引中，这样存储引擎无需会标获取对应的行就可以返回结果

2.改变表结构，例如使用单独的汇总表

3.重写这个复杂查询，让MySQL优化器能够以更优化的方式执行这个查询

3.重构查询的方式

3.1 一个复杂查询还是多个简单查询？

​ 在传统的实现中，总是强调需要数据库层完成尽可能多的工作，这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。但是MySQL从设计上让连接和断开都很轻量级，现代网络和速度比以前快很多，所以无论是带宽还是延迟，都支持MySQL在必要时可以将一个大的复杂查询转换成多个小的查询。

​ 虽然在其他条件都相同的情况下，使用尽可能少的查询当然是更好的，但是有时候，将一个大的查询分解成多个小的查询是很有必要的。

3.2 切分查询

​ 有时候对于一个大查询我们需要“分而治之”，将大查询切分成小查询，没个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果。

例如：

​ 删除旧的数据就是一个很好的例子，定期的清除大量数据的时候，如果一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的重要的查询。因此切分成很多小的查询多次完成，则可以尽可能小的影响MySQL的性能，同时还可以减少MySQL复制的延迟。例如：我们需要每个月运行一次下面的查询：

-- 从messages表中删除3个月之前创建的数据
DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH);

​ 我们可以将这个查询分成每次删除10000条数据，然后暂停一会再做下次删除，这样可以将服务器上原本一次性的压力分散到一个很长的时间段中，可以降低对服务器的影响，减少删除时锁的持有时间。如下：

-- 分成多次执行
DELETE FROM messages WHERE created < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000;

3.3分解关联查询

​ 很多高性能应用都会对关联查询进行分解。简单来说，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联，例如下面这个查询：

SELECT
    * 
FROM
    t_user t1
    JOIN t_dept t2 USING ( id )
    JOIN t_address t3 USING ( id ) 
WHERE
    t1.fname = '6Yn'

-- 可以分解成下面这些查询来代替：
SELECT * FROM t_user t WHERE t.fname = '6YN';
SELECT * FROM t_dept t WHERE t.id = 23456;
SELECT * FROM t_address t WHERE t.id in (12, 234, 23456, 100000);

这么做的优势是什么呢？

  • 1.让缓存更高效。许多应用程序可以方便的缓存单表查询对应的结果对象。例如，上面的查询中fname已经被缓存了，那么应用程序就可以跳过第一个查询。再例如，应用中已经缓存了ID为12， 34的内容，那么第三个查询的IN()中就可以少几个ID。

  • 2.将查询分解后，执行单个查询可以减少锁的竞争。

  • 3.在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。

  • 4.查询本身效率也可能会有所提升。

  • 5.可以减少冗余记录的查询。在应用层做关联查询，意味着对于某条记录应用只需要查询一次，而在数据库中做关联查询，则可能需要重复的访问一部分数据。从这点看，这样的重构还可能会减少网络和内存消耗。

  • 6.这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。

4.查询执行的基础

图5

4.1查询缓存

  • 在解析一个查询语句之前，如果查询缓存是打开的，那么MySQL会优先检查这个查询是否命中查询缓存中的数据。
  • 这个检查是通过一个对大小写敏感的哈希查找的。查询和缓存中的查询即使只有一个不同，也不会匹配缓存结果。
  • 如果命中缓存，那么在返回结果前MySQL会检查一次用户权限，有权限则跳过其他步骤直接返回数据。

缓存何时失效？

​ 在表的结构或数据发生改变时，查询缓存中的数据不再有效。有这些INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效。所以查询缓存适合有大量相同查询的应用，不适合有大量数据更新的应用。

如何清理查询缓存？

  • FLUSH QUERY CACHE; // 清理查询缓存内存碎片。
  • RESET QUERY CACHE; // 从查询缓存中移出所有查询。
  • FLUSH TABLES; //关闭所有打开的表，同时该操作将会清空查询缓存中的内容。

4.2 查询优化器

​ MySQL的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。下面是一些MySQL能够处理的优化类型：

  • 重新定义关联表的顺序
  • 将外连接转化成内连接
  • 使用等价变换规则
  • 优化COUNT()、MIN()和MAX()
  • 预估并转化为常数表达式
  • 覆盖索引扫描
  • 子查询优化
  • 提前终止查询
  • 等值查询
  • 列表IN()的比较

上面列举的并不是MySQL优化器的全部，MySQL还会做大量其他的优化，列举这些主要是让大家明白优化器的复杂性和智能型。如果说从上面这也例子中我们应该学到些什么，那就是不要自认为比优化器更聪明，你可能会占点便宜，但是更有可能会使查询变得更加复杂而难以维护，而最终受益为零。作为开发人员而不是DBA来说，让优化器按照他的方式工作就可以了。

4.3 排序优化

​ 无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。但是通常情况下，排序是不可避免的，所以就要考虑如何优化排序了。

​ 这里给出的建议是通过索引进行排序。

​ 当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中，数据量大则需要使用磁盘，不管是内存还是磁盘中进行排序都统称为文件排序（filesort）。MySQL文件排序的时候需要使用临时存储空间可能会比想象的大得多。原因是：在MySQL进行排序的时候，对于每一个排序记录都会分配一个足够长的定长空间来存放，这个定长空间必须足够长以容纳其中最长的字符串，例如，如果是VARCHAR列则需要分配其完整长度；如果使用UTF-8字符集，那么MySQL将会为每个字符预留三个字节。

5.关于MySQL层面优化的一些建议

总结到 SQL 优化中，就如下三点：

  • 最大化利用索引。
  • 尽可能避免全表扫描。
  • 减少无效数据的查询。

理解 SQL 优化原理 ，首先要搞清楚 SQL 执行顺序。

5.1 MySQL的执行顺序

MySQL的语法顺序如下：

SELECT
DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP bY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_condition>

MySQL的执行顺序：

FROM
<表名> #选取表，将多个表数据通过笛卡尔积变成一个表
ON
<筛选条件> #对笛卡尔积的虚表进行筛选
JOIN #指定join类型
<join表> #用于添加数据到on之后的虚表中
WHERE 
<where条件> #对上述虚表进行筛选
GROUP BY
<分组条件> #分组
<SUM()等聚合函数> #用于having进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> #对分组后的结果进行聚合筛选
SELECT
<返回数据列表> #返回的单列必须在group by子句中，聚合函数除外
DISTINCT
#数据去重
ORDER BY
<排序条件> #排序
LIMIT
<行数限制>

5.2 优化建议

注意：以下 SQL 优化策略适用于数据量较大的场景下，如果数据量较小一共几百几千条，属实没有必要考虑。

5.2.1 避免不走索引的场景

  • 尽量避免字段开头的模糊查询，会导致数据库引擎放弃索引进行全表扫描
-- 查看执行计划
EXPLAIN SELECT
    * 
FROM
    t_user 
WHERE
    fname LIKE '%a%'
图6
-- 查看执行计划
EXPLAIN SELECT
    * 
FROM
    t_user 
WHERE
    fname LIKE 'a%'
图7

如果需求是要在前面使用模糊查询：

1.使用 MySQL 内置函数 INSTR（str，substr）来匹配，作用类似于 Java 中的 indexOf()，查询字符串出现的角标位置。

2.使用 FullText 全文索引，用 match against 检索。

3.数据量较大的情况，建议引用 ElasticSearch 亿级数据量检索速度秒级。

4.当表数据量较少（几千条儿那种），别整花里胡哨的，直接用 like '%xx%'。

  • 使用 or 必须 全部有索引，否则全表扫描，建议使用 union all 代替
#前提：fname带索引，lname不带索引
-- 因为lname没有索引，会造成全表扫描
select * from t_user where fname = 'a' or lname = 'b';

-- 由于id和fname都有索引，所以不会全表扫描
select * from t_user where id = 1 or fname = 'a';

  • 最佳左前缀原则

    例如我给t_user 表创建了联合索引 index_fname_lname_age

-- 不走索引，全表扫描
select * from t_user where lname = 'v';
select * from t_user where age = '3';
select * from t_user where lname = 'b' and age = 3;
-- 走索引
select * from t_user where fname = 'v';
select * from t_user where fname = 'b' and lname = 'b' and age = 3;
-- 仅fname字段走索引
select * from t_user where fname = 'b' and age = 3;

实际上相当于创建了fname索引、fname_lname索引 、fname_lname_age索引

总结：联合索引必须保证有最左索引字段，中间字段不能断，直到最后字段，才能全部用上索引。

  • 索引值不会包含有NULL值的列

    ​ 只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

  • 不要在列上进行运算

-- 将在每个行上进行运算，导致扫描
select * from t_user where YEAR(create_time) < 2020;
-- 可以改写成
select * from t_user where create_time < '2020-01-01';

  • 不使用not in和 <>操作

    ​ NOT IN 和 <>都将导致不使用索引而全表扫描。NOT IN可以使用NOT EXISTS代替，id<>3可以使用id>3 or id<3代替

5.2.2 UNION和UNION ALL的区别

  • UNION 对两个结果集进行并集操作，会过滤掉重复的行，并且会进行默认规则的排序；
  • UNION ALL 对两个结果集进行并集操作，不过滤重复的行，也不排序。

如果你想取两个结果集的前20条操作，mysql会把两个结果集都放到临时表中，再取前20条，你可以分别取出两个结果集的前20条进行并集操作，然后再LIMIT 20

-- 注意，这里没有考虑排序问题，主要讲的是减少临时表中的数据
(select fname, lname
from t_user 
limit 20)
union all
(select fname, lname
from t_student
limit 20
) limit 20

​ 实际上，MySQL总是使用创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION中没法很好的使用，经常需要手工的将where、limit、order by等子句下推到UNION的各个子查询中，以便优化器可以充分的利用这些条件进行优化。

​ 除非确实需要服务器消除重复的行，否则一定要使用UNION ALL,这一点很重要，如果没有All关键字，MySQL会给临时表加上distinct，这会导致对真个临时表做唯一数据检查，之后进行排序，这样做的代价非常高。即使有ALL关键字，mysql仍会使用临时表存储结果。

5.2.3 COUNT的使用

  • 例如：业务中需要根据一个或者多个条件查询是否存在记录，不关心有多少条记录的时候，有人会这么写：
-- 这条sql的确定是，不管有多少条满足，会查出所有的行数，
-- 而前提是我们并不关心行数，只关心是否存在
select count(*) from table where a = "param1" and b = "param2";

-- 优化之后的写法
-- sql不使用count而是使用limit 1限制查到一条就返回，不在继续查找了，业务直接判断是否非空即可
select 1 from table where a = param1 and b = param2 limit 1;
  • COUNT(*) / COUNT(1) / COUNT(列名) / COUNT(DISTINCT 列名) 的区别
-- 返回表中的记录数（包括所有列），相当于统计行数(不会忽略列值为NULL的记录)
count(*) 

-- 忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替(不会忽略列值为NULL的记录)
count(1) 

-- 返回列名指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内
count(列名)

-- 只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。
count(distinct 列名)

count(*)&count(1)&count(列名)执行效率比较：

  • 如果列为主键，count(列名)效率优于count(1)
  • 如果列不为主键，count(1)效率优于count(列名)
  • 如果表中存在主键，count(主键列名)效率最优
  • 如果表中只有一列，则count(*)效率最优
  • 如果表有多列，且不存在主键，则count(1)效率优于count(*)

5.2.4 Limit的优化使用

​ 在系统中需要进行分页的时候，通常会使用LIMIT加上偏移量的方式实现，同时加上ORDER BY子句。如果有对应的索引，通常效率会不错，否则，MYSQL需要进行大量的文件排序操作。

​ 一个非常典型的问题是，在偏移量非常大的情况下，可能需要 limit 2000000,10 需要查询两百万加20条数据然后丢弃两百万，只返回20条，这样做的代价非常高。

  • 优化方法：
-- 例如原本的sql是
select * from t_user order by fname limit 2000000, 20;

-- 使用延迟关联优化
-- 原理是：通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获取需要的行。这可以减少mysql扫描那些需要丢弃的行。
select * from t_user
inner join (
    select id from t_user order by fname limit 2000000, 20
) as t1 USING (id)
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 206,214评论 6赞 481
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 88,307评论 2赞 382
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 152,543评论 0赞 341
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 55,221评论 1赞 279
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 64,224评论 5赞 371
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 49,007评论 1赞 284
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 38,313评论 3赞 399
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,956评论 0赞 259
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 43,441评论 1赞 300
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,925评论 2赞 323
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 38,018评论 1赞 333
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,685评论 4赞 322
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 39,234评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,240评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,464评论 1赞 261
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 45,467评论 2赞 352
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,762评论 2赞 345