高性能MySQL学习总结（六）

﹏ヽ暗。殇╰゛Y 2022-05-08 21:16 174阅读 0赞

**小结：**

1.  如何设计最优的表结构
2.  如何建立最好的索引
3.  合理的设计查询
4.  **查询优化，索引优化，库表结构优化需要齐发并进，一个不落**

**6.1.会什么查询速度会慢？**

1.    这需要了解查询的生命周期，清楚查询的时间消耗情况对于优化查询有很大的意义；                                                              详：通常来说，查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端。其中“执行”可以认为是整个生命周期中最重要的阶段，这其中包括了大量为了检索数据到存储引擎的调用以及调用后的数据处理，包含排序，分组等。
2.  优化查询的目的就是减少和消耗这些操作所消耗的时间；                                                                                                     注：在完成这些任务的时候，查询需要在不同的地方话费时间，包括网络，CPU计算，生成统计信息和执行计划，锁等待（互斥等待）等操作，尤其是向底层存储引擎检索数据的调用操作，这些调用需要在内存操作，CPU操作和内存不足时导致的I/O操作上消耗时间。根据引擎存储不同，可能会产生大量的上下文切换以及系统调用。
3.  真正重要的是响应时间（如果把查询看做是一个任务，那么它由一系列子任务组成，每个子任务都会消耗一定的时间。如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快。备注：有时候你可能会修改一些查询，减少这些查询对系统中运行得其他查询的影响）

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

查询性能低下最基本的原因是访问的数据太多。（思考：如何查最少的数据获取你想要的数据。。。。）

对于低效的查询，我们发现通过下面两个步骤来分析总是很有效。

1.确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行，但有时候也可能是访问了太多的列。

2.确认MySQL服务器层是否在分析大量超过需要的数据行。

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

（不需要的数据被查出来，好可惜浪费了感情不说，还给MySQL服务器带来额外的负担，并增加网络开销，另外也会消耗应用服务器的CPU和内存资源）注：如果应用服务器和数据库不在同一台主机上，网络开销就显得很明显了。即使是在同一台服务器上仍然会有数据传输的开销。

**案例：**

1.查询不需要的记录（使用limit）

实际上MySQL查询时先返回全部结果集在进行计算。例如：我只需要姓氏为李的10个用户，实际上是把所有的姓李用户查了出来，客户端的应用程序会接收全部的结果集数据，然后抛弃其中大部分的数据。最简单有效的解决方法就是加上LIMIT

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

3.总是取出全部列（使用缓存）

取出全部列，会让优化器无法完成索引覆盖扫描这类优化，还会为服务器带来额外的I/O，内存和CPU的消耗。因此，一些DBA是严格禁止SELECT \* 的写法的，这样做有时候还能避免某些列被修改带来的问题。

4.重复查询相同的数据（使用缓存）

6.2.2 MySQL 是否在扫描额外的记录

对于MySQL,最简单的衡量查询开销的三个指标如下：

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

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

3.1 使用索引覆盖扫描，把所有需要用的列都放到索引中，这样存储引擎无需回表获取对应行就可以返回结果le

3.2 该变表结构。例如使用单独的汇总表

3.3 重写这个复杂的查询。让MySQL优化器能够以最优化的方式执行这个查询

1.  响应时间                                                                                                                                                                                     响应时间是最重要的指标。响应时间是两个部分之和：服务是时间和排队时间。服务时间是指数数据库处理这个查询真正花了多少时间。排队时间是指服务器因为等待某些资源而没有真正执行查询的时间-----可能是I/O操作完成，也可能是等待行所，等等。一般最常见和最重要的等待是I/O和锁等待，但是实际情况更复杂。诸如存储引擎的锁（表锁，行锁），高并发资源竞争，硬件响应等诸多因素都会影响响应时间。                                                                                                            实际上可以使用“快速上限估计”法来估算查询的响应时间：概括的说，了解这个查询需要哪些索引以及它的执行计划是什么，然后计算大概需要多少个顺序和随机I/O，再用其乘以在具体硬件条件下一次I/O的消耗时间。最后把这些消耗都加起来，就可以获得一个大概参考值来判断当期响应时间是不是一个合理的值。
2.   扫描的行数和返回的行数        
3.   扫描的行数和返回的类型                                                                                                                                                          如果查询没有办法找到合适的访问类型，那么解决的最好办法通常就是增加一个合适的索引。索引让MySQL以最高效，扫描行数最少的方式找到需要的记录。好的索引可以让查询使用合适的访问类型，尽可能地只扫描需要的数据行。                  **如果发现查询需要扫描大量的数据但只返回少数的行，那么通常可以尝试下面的技巧去优化它：**                                           1.使用索引覆盖扫描，把所有需要用到的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果了

2.该表库表结构。例如使用单独的汇总表

3.重写这个复杂的查询。让MySQL优化器能够以更优化的方式执行这个查询

6.3 重构查询的方式

**查询**：一次查询和多次查询

有时候可以通过修改应用代码（接口层），用另一种方式完成查询，最终达成一样的目的。

1.  一个复杂查询还是多个简单查询                                                                                                                                                设计查询的时候一个需要考虑的重要问题是，是否需要将一个复杂的查询分成多个简单的查询。由于MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效。MySQL内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢的多了。在其它条件都相同的时候，使用尽可能少的查询当然是最好的。但是有时候将一个大查询分解为多个小查询是很有必要的。不过，在应用设计的时候，如果一个查询能够胜任时还写成多个独立查询是不明智的。
2.  切分查询 （分页查询）                                                                                                                                                              思想“分而治之”，将大查询分成小查询，每个查询功能是完全一样，只完成一小部分，每次只返回一小部分查询结果。         案例：删除旧的数据---------------------->定期删除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据，占满整个事务日志，耗尽系统资源，阻塞很多小的但很重要的查询。将一个大的DELETE语句切分成多个小的查询可以尽可能小地影响MySQL性能，同时还可以减少MySQL复制的延迟。（每删1000条记录都暂停一会在做下一次删除，还可以降低服务器影响减少删除时锁的特有时间）
3.  分解关联查询（使用缓存+in()关联代替关联查询）                                                                                                                  很多高性能的应有都会对关联查询进行分解。事实上，用分解关联查询的方式重构查询有如下的优势：                                3.1 让缓存的效率更高                                                                                                                                                                3.2 将查询分解后，执行单个查询可以减少锁的竞争                                                                                                                3.3 在应用层做关联，可以更容易对数据进行拆分，更容易做到高性能和可拓展                                                                      3.4  查询本身效率也可能会有提升                                                                                                                                            3.5 可以减少冗余记录的查询                                                                                                                                                    3.6  更进一步，这样做相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联。某些场景哈希关联的效率要高跟多