查询性能优化
衡量查询真正重要的是响应时间。可把查询看成是一个任务,那么它由一系列的子任务组成,每个子任务都会消耗一定的时间。(ps. 可参考mysql的SHOW PROFILE命令)
在完成任务时,查询会在包括网络,CPU,生成统计信息和执行计划,锁等等消耗掉时间。
查询的生命周期大致可以按照顺序来看:从客户端,到服务端,然后在服务器上进行解析,生成执行计划,执行,并返回结果给客户端。

慢查询基础

查询性能低下最基本的原因是访问的数据太多。
对于低效查询,我们发现通过下面两个步骤来分析总是有效:
  1. 1.
    确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时也可能访问了太多的列。
  2. 2.
    确认MySQL服务器层是否在分析大量超过需要的数据行。
对于MySQL,最简单的衡量查询开销的三个指标如下:
  1. 1.
    响应时间
  2. 2.
    扫描的行数
  3. 3.
    返回的行数
没有那个指标能完美的衡量查询的开销,他们只能大致的反映。这三个指标都会记录到MySQL的慢查询日志
简单点点一些点:
响应时间是两部分之和:服务时间,排队时间。
在不同类型的应用压力和环境下,响应时间没有什么一致的规律和公式。既可能是响应时间慢造成了CPU等问题,也可能是CPU、资源竞争等造成的响应时间慢。具体问题具体分析。
书中有一个“快速上限估计”法来估算查询的响应时间,该查询时间是否合理。请参考:Tapio Lahdenmaki和Mike Leach编写的Relational Database Index Design and the Optimizers(Wiley出版社)一书中提到的技术。

重构查询的方式

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

在传统的实现中,总是强调需要数据库层完成尽可能多的工作,这样做的逻辑在于以前总是认为网络通信、查询分析和优化是一件代价很高的事情。但是这样的想法对于MySQL并不适用:
  1. 1.
    MySQL从设计上让连接和断开连接都很轻量级,在返回一个小的查询结果方面很高效。
  2. 2.
    现代的网络速度比以前要快很多,无论是带宽还是延迟。在某些版本的MySQL上即使在一个通用的服务器上,也能够运行每秒超过10万的查询。
话虽如此,相比起来,MySQL每秒能处理内存成百万的数据,而响应数据到客户端要慢多了。当然,有时候是存在分解大查询的必要性的。

切分查询

有效减缓数据库的压力

分解关联查询

  1. 1.
    对于MySQL的查询缓存来说,如果关联中的某个表发生了改变,那么久无法使用查询缓存。
  2. 2.
    减少锁竞争。
  3. 3.
    应用层做关联,这更有利于数据库拆分(sharding等等),做到可扩展。
  4. 4.
    某些时候,分解查询对于数据库和应用来说,只查询了一次表或数据块。而一个或多个关联查询可能会重复多次查询了某些表或数据。

查询执行的基础

一个查询的完整过程

MySQL客户端/服务端通信协议

MySQL客户端和服务端之间的通信协议是“半双工”的。这意味着,某一时刻,只能是由一方传数据到另一方。所以,我们无法将数据切分成小块独立发送。一个明显的限制是,意味着无法进行流量控制。(ps.曾听说我朋友因为这个原因出过一个不小的bug)。注意参数 max_allowed_packet。
相应地,客户端也不能贸然只接受前几条就终止(当然也不要暴力终止)。

查询状态

可通过SHOW FULL PROCESSLIST命令查看当前各个MySQL查询连接的状态。各状态值请看考MySQL文档。

查询优化处理

书中该节谈查询优化器的。平时用EXPLAIN调优可结合该节和附录看着调。

MySQL如何执行关联查询

MySQL中“关联”是一个广泛的词。总的来说,MySQL认为任何一个查询都是一次“关联” (包括子查询,甚至基于单表的select)都可能是关联查询。
MySQL关联执行的策略:MySQL对任何关联都执行嵌套循环关联操作,即MySQL先在一个表中循环取出单条数据,然后再嵌套循环到下一个表中寻找匹配的行。

关联查询优化器

有时候,MySQL查询优化器会更改我们写的SQL的join的顺序,这一点,可对比执行的SQL和EXPLAIN SQL的结果,这是由于查询优化器重定义了关联的顺序,这是查询优化器一个重要的功能。我们可使用STRAINGHT_JOIN关键字重写查询,使查询按照我们SQL的JOIN顺序来执行。不过,绝大多数时候,优化器的优化都比常人的判断准确。

排序优化

无论如何,排序都是一个成本很高的操作,所以从性能角度考虑,应尽可能避免排序或者尽可能避免对大量数据进行排序。
当不能使用索引生成排序结果,MySQL则会再自行排序,MySQL将这一过程统称为文件排序。

HINTS

可通过HINTS控制最终执行计划。
具体看文档:8.9.2 Optimizer Hints
控制查询优化器的方式不止以上一种,具体可查阅:8.9 Controlling the Query Optimizer,个人认为都是骚操作,假若在生产中使用,需事前阅读好文档。
书中,关于查询的章节,案例篇的计算两点之间的距离,它用实例告诉了我们,在实际应用中如何取舍,什么时候该冗余计算值,如何避免矫枉过正,值得细细阅读。
Copy link
On this page
慢查询基础
重构查询的方式
一个复杂查询还是多个简单查询
切分查询
分解关联查询
查询执行的基础
MySQL客户端/服务端通信协议
查询优化处理