MySQL查询性能优化¶
1.查询慢的原因¶
1.1查询的生命周期¶
查询的生命周期大致可以按照顺序来看
从客户端 --> 到服务器 --> 然后再服务器上进行解析 --> 生成执行计划 --> 执行 ---> 返回结果给客户端
其中执行 是整个生命周期中最重要的阶段，因为这执行这个阶段 有着大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序、分组等。
查询会在不同的地方产生时间消耗，包括网络，CPU计算，生成统计信息和执行计划，锁等待(互斥等待) 等操作，特别是向底层存储引擎检索数据的调用操作，这些操作需要在内存操作、CPU操作和内存不足时导致等待I/O操作上消耗时间。如果存储引擎不同，可能会产生大量的上下文切换以及系统调用。
2.慢查询基础：优化数据访问¶
低效查询的两种情况: 2.1.确认应用程序是否在检索大量超过需要的数据。
【1】查询了不需要的记录
【2】多表关联时返回全部列
【3】总是取出全部列
【4】重复查询相同的数据
2.2.确认MySQL 服务器层是否在分析大量超过需要的数据行
【1】响应时间
【2】扫描的行数
【3】返回的行数
3.重构查询方式¶
【1】一个复杂查询还是多个简单查询 【2】切分查询,将大查询切分成小查询 【3】分解关联查询
4.查询执行的基础¶
当向MySQL 发送一个请求的时候，MySQL 到底做了些什么:
【1】.客户端发送一条查询给服务器
【2】.服务器先检查查询缓存，如果命中率缓存则立刻返回存储在缓存中的结果，否则进入下一阶段
【3】.服务端进行SQL解析、预处理，再由优化器生成对应的执行计划
【4】.MySQL 根据优化器生成的执行计划，调用存储引擎的API来执行查询
【5】.将上面的结果返回
4.1 MySQL 客户端/服务端通信协议¶
在MySQL中客户端与服务端之间的通信协议是半双工的,也就是说在任何一个时刻,要么是客户端想服务端发送数据,要么是服务端向客户端发送数据,这两个动作不能同时发生.因为无法也无需将一个消息切成小块独立发送.
因为采用了半双工这样一种方式虽然通信简单快速,但是同时也意味着没有办法进行流量控制了.一旦一端开始发送消息,那么另一端要接收完消息才能响应.即:在任何时刻,只有一个人能控制球,而且只用控制球的人才能将球抛回去.
在客户端将查询用单独的一个数据包发送给服务器,这时参数max_allowed_package会特别重要,因为当客户端发送的查询语句过长时,服务端会拒绝接收更多数据并返回错误. 与此相反服务器端响应给用户的数据通常更多,因此会有多个数据包,客户端必须在完整的接收整个返回结果,不能接受不了前面几条结果然后让服务器停止发送数据.这也是在必要的时候一定要加上LIMIT限制的原因.
MySQL通常需要等待所有的数据都已经发送给客户端才能释放这条查询所占用的资源,所有接收全部结果并缓存可以减少服务器的压力,尽快释放资源.不过当查询很大结果集时可以不使用缓存而是直接处理,这样的缺点是,服务器需要等查询完成后才能释放资源,因为在和客户端交互等整个过程中服务器资源都是被这个查询所占用的.(使用SQLBUFFER RESULT)
[查询的状态]
使用SHOW FULL PROCESSLIST 命令(该命令返回结果中的Command 列就表示当前的状态)
Sleep --> Query --> Locked -->Analyzing and statistics --> copying to tmp table [on disk] --> The Thred is
--> Sending data
4.2 查询缓存¶
在解析一个查询语句之前,如果查询缓存打开,MySQL 会优先检查查询是否命中查询缓存中的数据。通过对大小写敏感的哈希查找实现,如果查询和缓存不同查询会进入下一阶段的处理
如果命中查询缓存,MySQL在返回结果之前会检查一次用户权限,如果权限没有问题,会跳过所有其他就当,直接从缓存中拿到结果并返回给客户端,这种情况下,查询不会被解析,不用生成执行计划,不会被执行。
4.3 查询优化处理¶
查询的生命周期是将一个SQL转换成一个执行计划,MySQL再按照这个执行计划和存储引擎进行交互.这过程中任何错误(例如语法错误)都可能终止查询.
语法解析和预处理
查询优化器
数据和索引的统计信息
MySQL如何执行关联查询
执行计划
关联查询优化器
排序优化