本文参考了《高性能MySQL》(第三版),为学习笔记

一、为什么查询速度会慢

若果把查询看成是一个任务,那么它由一系列子任务组成,每个子任务都会消耗一定时间,优化查询,实际上就是要消除或减少其中一些子任务的执行次数,或优化其执行时间。

从生命周期来看,子任务包括:客户端,服务器,服务器上解析、执行,从服务器返回给客户端。

花费时间的地方:网络、CPU计算、生成统计信息和执行计划、锁等待、内存、磁盘等。

查询性能低下最基本的原因是访问的数据太多了。

二、慢查询基础:优化数据访问

1.向服务器请求了不需要的数据

  1. 查询不需要的记录

    部分开发者习惯先使用select查询大量结果,然后只获取其中的部分行,而他们误以为MySQL会只返回需要的数据,实际上MySQL先是返回全部结果集然后在此基础上计算。最简单的解决办法是在这样的查询后面加上limit。

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

  3. 总是取出全部列

    看到select *时要习惯性的用审视的眼光看待,若取出全部列,则无法触发覆盖索引,还会带来额外的I/O、内存和CPU的消耗。

  4. 重复查询相同的数据

    例如,用户评论的地方需要查询用户的头像,那么多次评论的时候就会反复查询这个头像数据,解决办法是把这个数据缓存起来。

2.MySQL扫描了额外的记录

MySQL简单衡量查询开销的三个指标:

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

这三个指标都会记录到MySQL的慢日志中,所以课件检查慢日志记录

  1. 响应时间

    包括服务时间和排队时间,服务时间指查询花费的时间,排队时间指等待执行查询的时间。

  2. 扫描的行数和返回的行数

    理想情况是扫描的行数和返回的行数是相同的,实际上这种概率很小。

  3. 扫描的行数和访问类型

    explain中的type列反应了访问类型,type的性能由好到坏排序,ref以上表示查询性能较好:system > const > eq_ref > ref > range > index > all

    system:表只有一行记录(等于系统表),这是const类型的特列,平时不会出现,这个也可以忽略不计
    const:表示通过索引一次就找到了,const通常用于比较primary key 或者unique索引,因为只匹配一行数据,所以很快
    eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行
    range:检索给定范围的行,一般在where语句中出现between、< 、>、in等的查询,开始于索引的某一点,而结束于另一点,不用扫描全部索引
    indexindexall区别为index类型只遍历索引树,这通常比aLL快,因为索引文件通常比数据文件小
    all:将遍历全表以找到匹配的行,性能最差

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

三、重构查询的方式

有时候,可以将查询转换一种写法让其返回一样的结果,但是性能更好。

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

    现在的网络速度已经可以满足运行多个小查询,相比之下,MySQL响应数据给客户端就慢得多了。如果一个查询还能够胜任时写多个独立查询是不明智的,这要结合查询复杂度和响应速度做出取舍。

  2. 切分查询

    即将大查询切分成小查询,每个查询功能完全一样,只完成一小部分,每次只返回一小部分结果。例如删除旧的数据就是一个很好的例子,若一次性删除大量数据的话,需要一次锁住很多数据,容易耗尽系统资源,阻塞很多小的但重要的查询,若将大查询切分成小查询,则可以避免这个问题。

  3. 分解关联查询

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

    • 让缓存的效率更高
    • 将查询分解后,执行单个查询可以减少锁的竞争
    • 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展
    • 查询本身效率会有所提升
    • 可以减少冗余记录的查询
    • 这样做相当于在应用中实现了哈希关联,而不是MySQL的嵌套关联,某些场景哈希关联的效率要高得多

四、查询执行的基础

关于MySQL的查询执行路径可以看我的这一篇笔记总结:MySQL 实战 45 讲学习笔记 1

1.MySQL客户端/服务器通信协议

MySQL客户端和服务器之间的通信协议是“半双工”的,意味着服务器和客户端之间的数据在同一时间只能由一方发向另一方,不能同时发送或接收,所以无法也无须将一个消息切成小块独立来发送。好处是通信简单快速,限制是没法进行流量控制,一旦一端开始发送,另一端需要接收完整消息才能响应它。多数连接MySQL的库函数都可以获得全部结果集并缓存到内存里,MySQL通常需要等待所有的数据都已经发送给客户端才能释放这条查询所占用的资源。

对于一个MySQL连接,或者说一个线程,任何时刻都有一个状态,可用show full processlist命令查看:

状态 解释
Sleep 线程正在等待客户端发送新的请求
Query 线程正在执行查询或者正在将结果发送给客户端
Locked 在MySQL的服务器层,该线程正在等待表锁
Analyzing and statistics 线程正在收集存储引擎的统计信息,并生成查询的执行计划
Copying to tmp table [on disk] 线程正在执行查询,并将其结果都复制到一个临时表
Sorting result 线程正在对结果集进行排序
Sending data 表示多种情况:线程可能在多个状态之间传送数据,或在生成结果集,或者在向客户端返回数据

2.查询缓存

如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据,如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前MySQL会检查一次用户权限,权限也在查询缓存中,如果权限没有问题,MySQL会跳过所有其他阶段,直接从缓存中拿到结果并返回给客户端。

3.查询优化处理

这里主要是语法解析和预处理,查询优化器

4.查询执行引擎

5.返回结果给客户端

五、查询优化器的局限

1.关联子查询

2.UNION的限制

3.索引合并优化

4.等值传递

5.并行执行

6.哈希关联

7.松散索引扫描

8.最大值和最小值优化

9.在同一个表上查询和更新