读高性能MySQL（第4版）笔记12_查询性能优化（下）

2023-09-21 06:33 由躺柒发表于 #数据库

1. “快速、精确和实现简单”

1.1. 三者永远只能满足其二，必须舍掉一个

2. 排序优化

2.1. 无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序

2.2. 文件排序（filesort）

2.2.1. MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘

2.2.2. 完全是在内存中排序不需要任何磁盘文件时也是如此

2.3. 排序算法

2.3.1. 两次传输排序（旧版本使用）

2.3.1.1. 读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行

2.3.1.2. 即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次传输排序的成本非常高

2.3.2. 单次传输排序（新版本使用）

2.3.2.1. 先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果

2.3.2.2. 不再需要从数据表中读取两次数据，对于I/O密集型的应用来说，这样做的效率高了很多

2.3.2.3. 只需要一次顺序I/O就可读取所有的数据，而无须任何的随机I/O

2.4. MySQL在进行文件排序时需要使用的临时存储空间可能会比想象的要大得多

2.5. 如果使用utf8mb4字符集，那么MySQL将会为每个字符预留4字节

2.6. 如果查询中有LIMIT的话，LIMIT也会在文件排序之后应用，所以即使需要返回较少的数据，临时表和需要排序的数据量仍然会非常大

3. 将结果返回给客户端

3.1. 执行查询的最后一个阶段是将结果返回给客户端

3.2. MySQL将结果集返回客户端是一个增量且逐步返回的过程

3.2.1. 服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存

4. MySQL查询优化器的局限性

4.1. MySQL查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让MySQL高效地完成工作

4.2. UNION的限制

4.2.1. MySQL无法将限制条件从UNION的外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上

4.2.2. 从临时表中取出数据的顺序并不是一定的，所以如果想获得正确的顺序，还需要在最后的LIMIT操作前加上一个全局的ORDER BY操作

4.3. 等值传递

4.3.1. 优化器通过将列表复制到所有相关表中的相应列来“共享”列表

4.4. 并行执行

4.4.1. MySQL无法利用多核特性来并行执行查询

5. 优化特定类型的查询

5.1. 多数优化技巧都和特定的版本有关，所以对于未来MySQL的版本未必适用

5.2. 优化COUNT()查询

5.2.1. COUNT()是一个特殊的函数

5.2.1.1. 可以统计某列的值的数量

5.2.1.2. 可以统计行数

5.2.2. 用COUNT（）的时候，这种情况下通配符并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数

5.2.2.1. 如果想要知道结果中的行数，应该始终使用COUNT（*），这样可以更清晰地传达意图，避免糟糕的性能表现

5.2.3. 常见的错误之一是当需要统计行数时，在COUNT()函数的括号内指定了列名

5.2.4. 计算精确值非常复杂，而计算近似值则非常简单

5.2.4.1. 可以增加类似Memcached这样的外部缓存系统

5.3. 优化联接查询

5.3.1. 确保ON或者USING子句中的列上有索引

5.3.1.1. 没有用到的索引只会带来额外的负担

5.3.2. 确保任何GROUP BY和ORDER BY中的表达式只涉及一个表中的列，这样MySQL才有可能使用索引来优化这个过程

5.3.3. 当升级MySQL的时候需要注意：联接语法、运算符优先级等其他可能会发生变化的地方

5.3.4. 使用WITH ROLLUP优化GROUP BY

5.3.4.1. 分组查询的一个变种就是要求MySQL对返回的分组结果再做一次超级聚合

5.3.4.2. 在应用程序中做超级聚合是更好的，虽然这需要给客户端返回更多的结果

5.3.4.3. 最好的办法是尽可能地将WITH ROLLUP功能转移到应用程序中处理

5.4. 优化LIMIT和OFFSET子句

5.4.1. 在系统中需要进行分页操作的时候，我们通常会使用LIMIT加上偏移量的办法实现，同时加上合适的ORDER BY子句

5.4.2. 在页面中限制分页的数量

5.4.3. 优化大偏移量的性能

5.4.4. 尽可能地使用索引覆盖扫描，而不是查询所有的行

5.4.5. 将LIMIT查询转换为已知位置的查询，让MySQL通过范围扫描获得对应的结果

5.4.6. OFFSET的问题

5.4.6.1. 会导致MySQL扫描大量不需要的行然后再抛弃掉

5.4.6.2. 可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET

5.4.6.2.1. 无论翻页到多么靠后，其性能都会很好

5.4.6.3. 冗余表，冗余表只包含主键列和需要做排序的数据列

5.5. 更好的设计

5.5.1. 将具体的页数换成“下一页”按钮

5.5.1.1. 假设每页显示20条记录，那么我们每次查询时都是用LIMIT返回21条记录并只显示20条

5.5.1.2. 如果第21条存在，那么就显示“下一页”按钮，否则就说明没有更多的数据，也就无须显示“下一页”按钮了

5.5.2. 先获取并缓存较多的数据

5.5.2.1. 缓存1000条——然后每次分页都从这个缓存中获取

5.5.2.2. 如果结果集小于1000，就可以在页面上显示所有的分页链接，因为数据都在缓存中，所以这样做不会对性能造成影响

5.5.2.3. 如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮

5.5.3. 比每次生成全部结果集再抛弃不需要的数据的效率高很多

5.6. 优化SQL CALC FOUND ROWS

5.6.1. 在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示（hint），这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数

5.6.2. 加上这个提示以后，不管是否需要，MySQL都会扫描所有满足条件的行，然后再抛弃掉不需要的行，而不是在满足LIMIT的行数后就终止扫描

5.6.3. 该提示的代价可能非常高

5.6.4. 当需要精确结果的时候，再单独使用COUNT（*）来满足需求，这时如果能够使用索引覆盖扫描则通常也会比SQL_CALC_FOUND_ROWS快得多

5.7. 优化UNION查询

5.7.1. 经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化

5.7.2. 除非你确实需要服务器消除重复的行，否则一定要使用UNION ALL