【后端修行之MySQL】MySQL查询性能优化及方案

本文用于记录苦啃《高性能MySQL（第3版）》后，个人认为有价值的笔记，核心内容均围绕标题展开。既做一个学习的记录，同时也做一个沟通交流，欢迎各位大佬互动~

为什么查询速度会慢

编写快速查询，真正重要的是响应时间。

把查询看作一个任务，它由一系列子任务组成（都消耗时间）。

优化方向：消除部分子任务、减少子任务执行次数、让子任务执行更快

查询的大致生命周期：从客户端到服务器，服务器上解析，生成执行计划，执行（最重要，包括大量为了检索数据到存储引擎的调用以及调用后的数据处理，包括排序和分组），并返回结果给客户端。

时间花费：网络、CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作；

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

查询性能地下的最基本原因：访问数据太多。

分析步骤：

1. 确认应用程序是否在检索大量超过需要的数据（访问过多的行/列）；
2. 确认MySQL服务器层是否在分析大量超过需要的数据行。

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

1. 查询不需要的记录。网站取100条记录，页面只显示10条，最好的方式就是加上LIMIT。
2. 多表关联时返回全部列。
3. 总是取出全部列。SELECT *,会导致优化器无法完成索引覆盖优化，还会带来额外的I/O、内存和CPU的消耗。
4. 重复查询相同的数据。用户评论多次，重复获取头像URL，最好的方式是首次查询的时候将数据缓存起来。

        MySQL是否在扫描额外的记录

衡量查询开销的三个指标：响应时间（服务时间、排队时间）、扫描行数、返回的行数（理想情况下两者应该一样）。

一般MySQL能够使用三种方式应用WHERE条件，从好到坏为：

1. 在索引中使用WHERE条件来过滤不匹配的记录。存储引擎层完成。
2. 使用索引覆盖扫描（Extra列出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。服务器层完成，无须回表；
3. 从数据表中返回数据，然后过滤不满足条件的记录（Extra列出现了Using Where），服务器层完成。

某查询扫描大量数据但返回少数的行，策略：

1. 使用索引覆盖扫描，把所有需要的列放进索引；
2. 改变库表结构。例如单独的汇总表；
3. 重写复杂的查询。让优化器更加优化的方式执行这个查询。

重构查询的方式

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

网络速度块，无需让一次查询在服务器完成尽可能多的工作。

大查询拆分成小查询。

​​​​​​​​​​​​​​        切分查询

删除数据，避免一次锁住大量数据，占满整个事务日志，耗尽系统资源，阻塞很多很小的但是很重要的查询，将大的DELETE语句切分成多个较小的查询。

​​​​​​​​​​​​​​        分解关联查询

很多高性能应用都会对关联查询进行分解。每个表进行单表查询，然后结果在应用程序中进行关联。

优势：

1. 让缓存的效率更高。许多应用程序可以方便地缓存单表查询对应的结果对象。
2. 将查询分解后，执行单个查询可以减少锁的竞争。
3. 应用层关联，可以做到高性能和可拓展。
4. 查询本身效率可能提升。
5. 可以减少冗余记录查询。
6. 相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。

​​​​​​​查询执行的基础

向MySQL发送一个请求，MySQL做了什么：

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

查询缓存

查询优化处理（语法解析器和预处理、查询优化器、数据和索引的统计信息）-执行计划（指令树）

查询执行引擎（执行计划是一个数据结构）

返回结果给客户端