MySQL笔记之查询优化其二

未分类

关于执行路径

查询缓存
在解析一个查询语句之前，如果查询缓存是打开的那么MySQL会通过一个对大小写敏感的哈希查找来检查这个查询是否命中查询缓存中的数据。如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前MySQL会检查一次用户权限，直到此时仍是无须解析查询语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。

语法解析及预处理
MySQL通过关键字将SQL语句解析并生成一颗解析树，MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器则根据一些MySQL规则进一步查询解析树是否合法。

查询优化
查询优化器的作用就是从数种执行计划中找到最好的执行计划。MySQL使用基于成本的优化器，它将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。但是有很多的因素会影响其估算结果，比如统计信息的不准确。
当然执行计划的成本估算并不等同于实际执行的成本，所以就算统计信息精准，优化器给出的执行计划也可能不是最优的。跟你想的不同，你可能希望执行时间尽可能的短，但是MySQL只是基于成本模型选择最优的执行计划，而有时候这并不是最快的执行方式。
数据表的关联查询并不总是按照在查询中指定的顺序进行，查询优化器可能会重新定义关联表的顺序。
关于IN()操作，这个操作我们经常会用到。在很多数据库中，IN()完全等同于多个OR字句。但在MySQL中并不是如此，MySQL在处理IN()时会先将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，因此这是一个O(log n)时间复杂度的操作，而上面多个OR子句的时间复杂度为O(n)。

切分

有时候对于一个大查询我们需要将之切分成小查询再执行。对于关联查询，有时也应如此，其实就是对每一个表进行一次单表查询然后将结果在应用程序中进行关联。那么这么做有什么好处呢

• 首先这样可以使缓存的效率更高，许多时候应用程序可以很方便的缓存单表查询的结果，如果某个表很少改变，那么基于该表的查询就可以重复利用缓存结果了。
• 同时将查询分解后，执行单个查询可以减少锁的竞争
• 可以减少冗余记录的查询，在应用层做关联，意味着对于某条记录只需要查询一次，而在数据库中做关联查询则可能需要重复访问一部分数据。而且在应用中实现的哈希关联，而MySQL中会是嵌套循环关联，某些场景哈希关联的效率要高得多。

关于上面提到的嵌套循环关联，即MySQL先在一个表中循环取出单条数据，然后再嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止。MySQL会尝试在最后一个关联表中找到所有匹配的行，如果最后一个关联表无法找到更多的行，MySQL将会返回到上一层关联表看是否能够找到更多的匹配记录，依此类推迭代执行。

　关于排序

无论如何排序都是一个成本很高的操作，应尽量避免排序或者尽量避免对大量数据进行排序。
我们知道，当不能使用索引生成排序结果时，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大就会借助磁盘空间，不过 MySQL 将这个过程统称为文件排序。
如果需要排序的数据量可以全部放入排序缓冲区，MySQL会在内存中进行快排操作，如果内存放不下，那么MySQL会先将数据分块，对每个单独的快使用快排，并将结果放回磁盘，然后将所有排序后的块进行合并最后返回结果。这是一个消耗巨大的操作过程。
并且MySQL在进行文件排序的时候需要使用的临时存储空间可能会比想象的大得多，原因在于MySQL在排序时，对每一个记录都会分配足额的定长空间。这个空间必须足够长以能够容纳其中最长的字符串。
综上如果不能使用索引排序，那就必须要好好的考虑怎样去优化你的查询了。