mysql-查询性能优化

前面我们介绍了如何设计最优的库表结构、如何建立最好的索引,但这些还不够,还需要合理的设计查询。

在尝试编写快速的查询之前,需要清楚一点,真正重要的是响应时间。如果把查询看作是一个任务,那么它由一些列子任务组成,每个子任务会消耗一定的时间。如果要优化查询,实际上要优化其子任务。

通常来说,查询的生命周期可以按照顺序来说:从客户端,到服务器,然后在服务器上进行解析,生成执行计划、执行、并返回结果给客户端。其中执行可以说是最重要的阶段,包括大量为了检索数据到存储引擎的调用以及调用后的数据处理,包括排序和分组等。

优化数据访问

查询性能低下最基本的原因是访问的数据太多。对于低效的查询,我们发现通过下面两个步骤来分析很有效:

  • 确认应该是否在检索大量超过需要的数据。可能是访问了太多的行了,也可能是访问了太多的列。
  • 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

  • 查询不需要的记录

    一个常见的错误是常常误以为MySQL会只返回需要的数据,实际上MySQL却是先返回全部结果集再进行计算。最简单有效的办法就是在这样的查询后面加上LIMIT。

  • 多表关联时返回全部列

    千万不要按下面的写法编写查询:

    1
    select * from A inner join B on using(user_id) inner join C using(user_id) where ...;

    这将返回这三个表的全部数据列。正确的方式应该时按需获取。

  • 总是取出全部列

    每次看到 select * 都要用怀疑的眼光去审视,是否真的需要所有的列。取出全部的列,会让优化器无法完成索引覆盖扫描这类优化。

  • 重复查询相同的数据

    不断的重复执行相同的查询,然后每次都返回完全相同的数据。比较好的方案是,将结果缓存起来。

是否在扫描额外的记录

在确定查询只返回需要的数据以后,接下来应该看看查询为了返回结果是否扫描了过多的数据。对于MySQL来说,最简单的衡量指标有三个:

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

三个指标都会记录到MySQL慢日志中,所以检查慢日志记录是找出扫描行数过多的查询的好办法。

响应时间

响应时间包括服务时间和排队时间。服务时间是指数据库处理这个查询真正花费了多长时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等待I/O操作完成,也可能是行锁等等。

扫描的行数和返回的行数

理想情况下,扫描的行数和返回的行数是相等的。实际上,这种美事并不多。如在做一个关联查询时,服务器必须要扫描多行才能生成结果集中的一行。

扫描行数和访问类型

MySQL有好多种访问方式可以查找并返回一行结果。有些访问方式可能需要扫描很多行才能返回一行结果,也有些访问方式可能无须扫描就能返回结果。

在EXPLAIN语句的type反应了访问类型。

如果查询没办法找到合适的访问类型,那么解决的最好办法通常就是增加一个合适的索引。

一般MySQL能够使用如下三种方式应用WHERE条件,从好到坏依次是:

  • 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成
  • 使用覆盖索引(Extra中出现using index)来返回记录。这是在MySQL服务器层完成,无须回表查询。
  • 从数据表中返回数据,然后过滤不满足条件的记录(Extra中出现 using where),在MySQL服务器层完成,MySQL需要先从数据表读取记录然后过滤。

MySQL不会告诉我们生成结果实际上需要扫描多少行数据(关联查询结果返回的一条记录通常是由多条记录组成),而只会告诉我们生成结果时一共扫描了多少行数据。扫描的行数中的大部分很可能被WHERE过滤掉,对最终结果没有贡献。

重构查询方式

优化查询时,目标是找到一个更优的方法获取实际需要的结果,而不一定总是需要从MySQL获取一模一样的结果集。有时候可以换一种写法其返回的结果一样,但性能更好。也可以通过修改应用代码,用另一种方式完成查询。

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

设计查询时一个需要考虑的重要问题是,是否需要将一个复杂的查询分成多个简单的查询。在传统的实现中,总是强调需要数据库层完成尽可能多的工作,前提是认为网络通信、查询解析和优化是一件代价很高的事情。

但是现在来说,对于MySQL并不适用,MySQL从设计上让连接和断开连接都是很轻量级的,在返回一个小的查询结果方面很高效。

MySQL内部每秒能够扫描内存中上百行数据,相比之下,MySQL响应数据给客户端就慢的多。在其他条件相同的情况下,使用尽可能少的查询当然是更好的。但是有时候,将一个大查询分解为多个小查询是很有必要的。

切分查询

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

删除旧数据就是一个很好的例子。定期清理大量数据时,如果用一个大的语句一次性完成的话,则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源。

每次删除后,都暂停一会儿再做下一次删除,这样也可以将服务器原本一次性的压力分散到一个很长的时间段中,就可以大大降低对服务器的影响,还可以大大减少删除时锁的持有时间。

分解关联查询

很多高性能的应用都会对关联查询进行分解。简单地,可以对每一个表进行一次单表查询,然后将结果在应用程序中进行关联。如下:

1
2
3
4
select * from tag
join tag_post on tag.id = tag_post.tag_id
join post on tag_post.post_id = post.id
where tag.tag = 'mysql';

可以分解成下面这些查询来代替:

1
2
3
select * from tag where tag = 'mysql';
select * from tag_post where tag_id = 1234;
select * from post where post.id in (123, 456);

有如下一些好处:

  • 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。如上面查询中的tag已经被缓存了,那么应用就可以跳过第一个查询。
  • 将查询分解后,执行单个查询就可以减少锁的竞争。
  • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
  • 查询本身效率也可能会有所提升。
  • 可以减少冗余记录的查询。

查询执行的基础

MySQL查询手写顺序:

1
2
3
4
5
6
7
8
9
select distinct <select_list> 
from <left_table> <join_type>
join <right_table>
on <join_condition>
where <where_list>
group by <group_by_list>
having <having_condition>
order by <order_by_list>
limit <limit_number>

MySQL机读顺序:

1
2
3
4
5
6
7
8
9
from left_table
on <join_condition> <join_type>
join <right_table>
where <where_list>
group by <group_by_list>
having <having_condition>
select distinct <select_list>
order by <order_by_condition>
limit <limit_number>

希望MySQL以更高性能运行查询时,最好的办法就是弄清楚MySQL是如何优化和查询的。

当向MySQL发送一个请求时,MySQL到底做了些什么:

  1. 客户端发送一条查询给服务器
  2. 服务器先检查查询缓存,如果命中缓存,则立刻返回存储在缓存中的结果。否则进入下一阶段
  3. 服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划
  4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询
  5. 将结果返回给客户端。

特别需要注意的是:服务层和存储引擎层是以记录为单位进行交互的。

一条更新语句是如何执行

在MySQL里,如果每一次的更新操作都需要写进磁盘,然后磁盘也要找到对应的那条记录,然后再更新,整个过程 IO 成本、查找成本都很高。MySQL使用了WAL技术,即Write-Ahead Logging,它的关键点就是先写日志,再写磁盘。

redo log

具体来说,当有一条记录需要更新的时候,InnoDB 引擎就会先把记录写到 redo log 里面,并更新内存,这个时候更新就算完成了。同时,InnoDB 引擎会在适当的时候,将这个操作记录更新到磁盘里面。

InnoDB 的 redo log 是固定大小的,比如可以配置为一组 4 个文件,每个文件的大小是 1GB,那么这块“粉板”总共就可以记录 4GB 的操作。从头开始写,写到末尾就又回到开头循环写。

有了 redo log,InnoDB 就可以保证即使数据库发生异常重启,之前提交的记录都不会丢失,这个能力称为 crash-safe

bin-log

binlog 日志只能用于归档。

不同

  1. redo log 是 InnoDB 引擎特有的;binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
  2. redo log 是物理日志,记录的是“在某个数据页上做了什么修改”;binlog 是逻辑日志,记录的是这个语句的原始逻辑,比如“给 ID=2 这一行的 c 字段加 1 ”。
  3. redo log 是循环写的,空间固定会用完;binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个,并不会覆盖以前的日志。
  1. 执行器先找引擎取 ID=2 这一行。ID 是主键,引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中,就直接返回给执行器;否则,需要先从磁盘读入内存,然后再返回。
  2. 执行器拿到引擎给的行数据,把这个值加上 1,比如原来是 N,现在就是 N+1,得到新的一行数据,再调用引擎接口写入这行新数据。
  3. 引擎将这行新数据更新到内存中,同时将这个更新操作记录到 redo log 里面,此时 redo log 处于 prepare 状态。然后告知执行器执行完成了,随时可以提交事务。
  4. 执行器生成这个操作的 binlog,并把 binlog 写入磁盘。
  5. 执行器调用引擎的提交事务接口,引擎把刚刚写入的 redo log 改成提交(commit)状态,更新完成。

客户端和服务器通信协议

MySQL客户端和服务器端之间的通信协议是”半双工“的,这意味着,任何一个时刻,要么是服务器向客户端发送数据,要么是客户端向服务器发送数据。

这种协议让那个MySQL通信简单快速,但是页很多地方限制了MySQL。意味着没法进行流控。

查询状态

对于一个MySQL连接,任何时刻都有一个状态,最简单的命令是:SHOW FULL PROCESSLIST

  • Sleep

    线程正在等待客户端发送新的请求

  • Query

    线程正在执行查询或者正将结果发送给客户端

  • Locked

    在MySQL服务器层,该线程正在等待表锁。

  • Analyzing and statistics

    线程正在收集存储引擎的统计信息,并生成查询的执行计划。

  • Copying to tmp table [on disk]

    线程正在执行查询,并且将结果集都复制都一个临时表中,这种状态要么是做GROUP BY操作,要么是文件排序操作。

  • Sorting result

    线程正在对结果集进行排序。

  • Sending data

    线程正在多个状态之间传送数据,或者正在生成结果集。

查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划,MySQL再依照这个执行计划和存储引擎进行交互。这个阶段包含多个字阶段:解析SQL、预处理、优化SQL执行计划。

语法解析器和预处理

MySQL通过关键字将SQL语句进行解析,并生成一颗对应的”解析树“。MySQL解析器将使用MySQL语法规则验证和解析查询。

预处理则根据一些MySQL规则进行进一步检查解析树是否合法。

再下一步预处理器会验证权限。

查询优化器

现在语法树被认为合法的了,并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式,最后都返回相同的结果。优化器的作用就是找到其中最好的执行计划。

很多原因会导致MySQL优化器选择错误的执行计划:

  • 统计信息不准确
  • 执行计划中的成本估算不等同于实际执行的成本。
  • MySQL最优可能和你想的最优不一样。
  • MySQL从不考虑其他并发执行的查询
  • MySQL也不是任何时候都是基于成本的优化
  • MySQL不会考虑不受其控制的操作的成本

下面是MySQL能够处理的优化类型:

  • 重新定义关联表的顺序

  • 将外链接转化成内连接

  • 使用等价变换规则

  • 优化COUNT()、MIN()和 MAX()

    查找某一列最小值,通常需要查询对应B-Tree索引最左端的记录。

  • 预估并转化为常数表达式

  • 覆盖索引扫描

  • 子查询(需要多次查询)优化

  • 提前终止查询

  • 等值传播

  • 列表in()的比较

查询优化

关联子查询

MySQL的子查询实现得非常糟糕。最糟糕的一类是WHERE条件中包含IN()的子查询语句。如:

1
2
3
select * from t_table_a where tag_id in (
select id from t_table_b where col = 10
);

因为MySQL对IN()列表中的选项有专门的优化策略,一般我们会认为先执行子查询,然后再执行外层查询。

但实际上,MySQL不是这样做到。MySQL会将外层表压到子查询中,它认为这样可以更高效率的查找到数据行。也就是说,MySQL实际会改写成下面的查询:

1
2
3
select * from t_table_a where EXISTS (
select * from t_table_b where col = 10 and a.tag_id = b.id
)

这时,子查询需要根据tag_id来关联外部的t_table_a,因为需要 tag_id 字段,所以MySQL认为无法先执行这个子查询。通过EXPLAIN可以看到,MySQL先选择对t_table_a进行全表扫描,然后根据返回的tag_id逐个执行子查询。如果外层是一个很大的表,那么这个查询的性能会非常糟糕。当然我们很容易用下面的办法来重写:

1
select t_table_a.* from t_table_a inner join t_table_b using(tag_id) where b.col = 10

另一个优化的办法是使用函数GROUP_CONCAT() 在 IN() 中构造一个由逗号分隔的列表,有时比上面使用关联改写更快。

因为IN()加子查询,性能经常会非常糟,所以通常建议使用EXISTS()等效的改写查询来获取更好的效率。

UNION的优化

有时候,MySQL无法将限制条件从外层下推到内层,这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。

如果希望UNION的各个子句能够根据LIMIT只取部分结果集,或者希望能够先排好序再合并结果集的话,就需要在UNION的各个子句中分别使用这些子句。例如,想将两个子查询结果联合起来,然后再取前20条记录,那么MySQL会将两个表都存放到同一个临时表中,然后再取出前20行记录:

1
2
3
4
(select a, b from t_table_a order by b)
union all
(select a, b from t_table_b order by b)
limit 20;

这个查询会把table_a的200条记录和table_b的1000条记录都放在一个临时表中,然后再从临时表取出前20条。可以通过在UNION的两个子查询中分别加上一个LIMIT 20来减少临时表中的数据:

1
2
3
4
(select a, b from t_table_a order by b limit 20)
union all
(select a, b from t_table_b order by b limit 20)
limit 20;

现在中间的临时表只会包含40条记录了,除了性能考虑外,这里有一点需要注意:从临时表取出的数据的顺序不是一定的,如果想获取正确的顺序,还需要加上一个全局的ORDER BY和LIMIT操作。

特定类型查询的优化

优化count查询

count()是一个特殊的函数,有两种不同的作用:可以统计某个列值的数量,也可以统计行数。

统计列值时要求列值时非空的(不统计NULL)。

另一个作用时统计结果集的行数。当MySQL确认括号内的表达式不可能为空时,实际上就是在统计行数。最简单的就是当我们使用count(*)的时候,这种情况下,通配符 * 并不像我们猜想的那样扩展成所有列,实际上,会忽略所有列而直接统计所有行数。

优化关联查询

  • 确保ON或者USING子句中的列上有索引。创建索引的时候就要考虑到关联的顺序。当表A和表B用列c进行关联的时候,如果优化器的关联顺序是B、A,那么就不需要在B表对应的列上建立索引。一般来说,除非特殊情况,只需要在关联顺序中的第二个表的相应列上创建索引。
  • 确保GROUP BY和ORDER BY中的表达式只涉及到一个表中的列,MySQL才有可能使用索引来优化

优化子查询

尽可能使用关联查询代替。如果是MySQL5.6或者更新的版本可以直接忽略这个建议。

优化GROUP BY 和DISTINCT

很多场景下,MySQL都使用同样的办法优化这两种查询,事实上,MySQL优化器会在内部处理的时候互相转化这两类查询。都可以使用索引优化。

在MySQL中,当无法使用索引的时候,GROUP BY使用两种策略来完成:使用临时表或者文件排序来做分组。

如果没有通过ORDER BY子句显式的指定排序列,当查询使用GROUP BY子句的时候,结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序,而这种默认排序又导致了需要文件排序,则可以使用ORDER BY NULL,让MySQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或ASC关键字,使分组的结果集按需要的方向排序。

优化LIMIT查询

在系统中需要进行分页操作的时候,我们通常会使用LIMIT加上偏移量的办法实现,同时加上合适的ORDER BY子句。如果有对应的索引,通常效率会不错,否则,MySQL需要做大量的文件排序操作。

一个非常常见又令人头疼的问题是,在偏移量非常大的时候,例如可能是LIMIT 10000,20这样的查询,这时MySQL需要查询10020条记录,然后只返回最后20条,前面10000条记录都将被抛弃,这样的代价非常高。如果所有的页面被访问的频率都相同,那么这样的查询平均需要访问半个表的数据。要优化这种查询,要么在页面中限制分页的数量,要么是优化最大偏移量的性能。

一个最简单的办法是尽可能地使用覆盖索引扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候,这样做的效率会提升非常大。

考虑下面的查询:

1
select a , b from table_a order by c limit 50, 5;

如果这个表非常大,那么这个查询最好改写成下面的样子:

1
2
3
4
select a, b from table_a 
inner join (
select id from table_a order by c limit 50, 5
) as tmp_b using(id);

有时候也可以将LIMIT查询转换为已知位置的查询,让MySQL通过范围扫描获得到对应的结果。例如,在一个位置列上有索引,并且预先计算出了边界值,上面的查询就可以改写为:

1
select a, b from table_a where c between 50 and 55 order by d;

LIMIT和OFFCET的问题,其实都是OFFCET的问题,它会导致MySQL扫描大量不需要的行然后再抛弃掉。如果可以使用书签记录上次取数据的位置,那么下次就可以直接从书签记录的位置开始扫描,这样久可以避免使用OFFSET。

例如:若需要按照租借记录做翻页,那么可以根据最新一条租借记录向后追溯,这种做法可行是因为租借记录的逐渐是单调增长的。首先使用下面的查询获取第一组结果:

1
select * from rental order by id desc limit 20;

假设上面查询返回的是主键16049到16030的记录,那么下一页查询就可以从16030开始:

1
select * from rental where id < 160300 order by id desc limit 20;

这样无论翻到多少页,性能都会很好。

优化UNION查询

MySQL总是通过创建临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中,以便优化器充分利用这些条件进行优化。

除非确实需要服务器消除重复的行,否则就一定要使用UNION ALL。如果没有ALL,MySQL会给临时表加上DISTINCT选项,这会导致对整个临时表的数据做唯一检查。这样的代价非常高。即使有ALL关键字,MySQL任然会使用临时表存储结果。事实上,MySQL总是将结果放入临时表,然后再读出,再返回给客户端。

用户自定义变量

用户自定义变量是一个用来存储内容的临时容器,在连接MySQL的整个过程中都存在。可以使用SET和SELECT语句来定义他们:

1
2
3
SET @one	:= 1;
set @min_actor := (select min(actor) form t_a)
set @last_week := current_date - interval 1 week;

然后,可以在任何可以使用表达式的地方使用这些变量:

1
select ... where col <= @last_week;

案例分析

前提

user(id, name, age, sex)。存在索引index(name,age)
存在如下记录:

id name age sex
1 zhangsan 10
2 zhangsan 2
3 lisi 20

问题

select * from user where name=’zhangsan’ and age=20 and sex=’男’
此查询语句mysql数据库的执行过程分析?

解答

此where条件包含mysql处理不同条件类型:

  1. index key:第一个索引列,用于确定索引范围(确定了1和2记录,索引结构B+树是有顺序的),发生在存储引擎层。
  2. index filter:对索引进行过滤,需要ICP的支持(将index filter从原来的service层下放到存储引擎层),发生在存储引擎层(确定了记录2)。
  3. table filter:索引中没有的条件,对表进行过滤,发生在service层(过滤sex=’男’)。
0%