MySQL进阶实战7，查询的执行过程_飞道的博客

MySQL进阶实战7，查询的执行过程

2022-11-30 16:01 370人阅读评论(0)

一、拆分查询

将大查询拆分成小查询，每个查询功能完全一样，只是一小部分，每次只返回一小部分查询结果。

比如在删除旧数据的时候，如果用一个大的语句一次性删的话，则可能需要一次性锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞其它查询任务。如果将一个大的删除语句拆分成若干个较小的删除语句，可以提升MySQL的性能。一次删除一万行数据一般来说是一个比较高效并且对服务器影响最小的做法。

二、分解关联查询

分解关联查询的优势

让缓存更高效，如果某个表改变了，就无法使用查询缓存了，而拆分后，如果其它表没变化，那么基于这些表的查询就可以使用缓存结果了;
将查询分解后，执行单表查询时可以减少锁的竞争;
在应用层做关联，可以更容易对数据进行拆分，更容易做到高性能和高扩展;
查询本身的效率也可能会提升;
可以减少冗余记录的查询

三、查询的执行过程

客户端发送一条查询语句到服务器
服务器先查询缓存，如果命中缓存，则立即返回存储在缓存中的数据；
未命中缓存后，MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树，MySQL解析器将使用MySQL语法进行验证和解析。例如，验证是否使用了错误的关键字，或者关键字的使用是否正确；
预处理是根据一些MySQL规则检查解析树是否合理，比如检查表和列是否存在，还会解析名字和别名，然后预处理器会验证权限；
根据执行计划查询执行引擎，调用API接口调用存储引擎来查询数据；
将结果返回客户端，并进行缓存；

多数连接MySQL的库函数都可以获得全部结果集并缓存在内存中，还可以逐行获取需要的数据。默认一般是获得全部结果集并缓存到内存中。MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束，早点释放相应的资源。

四、优化器的一些优化手段

1、重新定义关联表的顺序

数据库的关联并不总是按照查询中指定的顺序进行，优化器可能会改变其关联顺序，生成最优的执行计划。

2、将外连接转化为内连接

3、使用增加变换规则

4、优化count()、max()、min()

如果使用min()获取最小值时，可以查询B-Tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。优化器会将这个表达式作为一个常数对待，max()也是如此。如果MySQL使用了这个类型的优化，在explain时就会看到“select tables optimized away”。它表示优化器已经从执行计划中移除了该表，并通过一个常量取而代之。

5、预估并转化为常量表达式

当MySQL检测到一个表达式可以转化为常数的时候，就会一直将该表达式作为常量进行优化处理。

6、覆盖索引扫描

当索引中的列包含所有查询中需要使用的列时，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行。

7、提前终止查询

可以通过limit主动终止查询。

五、MySQL如何执行关联查询

MySQL对任何关联都执行嵌套循环关联操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。然后根据各个表匹配的行，返回查询需要的各个列。

六、查询执行引擎

在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里的执行计划是一个数据结构，而不是像很多其他数据库那样会生成对应的字节码。

查询执行阶段，只是简单地根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成，这些即可被称为“handler API”。

MySQL在优化阶段就会为每个表创建一个handler实例，优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名、索引统计信息等。

七、分页

做法1：假设每页显示10条记录，那么每次查询时用limit返回11条记录，并显示其中的10条，如果第11条存在，那么就显示下一页按钮，否则就说明没有更多的数据。
做法2：现货区并缓存较多的数据，比如先查询1000行，然后每次分页都从这个缓存中获取，如果结果集小于1000，就可以在页面上显示所有的分页链接，因为数据在缓存中，这样做性能不会出现问题。如果结果集大于1000，则可以在页面上加一个查询更多数据的按钮。

这两种方式逗比每次获取全部结果集，再抛弃不需要的数据的方式效率高。