MySQL查询优化
Mysql执行顺序，即在执行时sql按照下面的顺序进行执行
from on join where group by having select distinct union order by
1.切片查询
将大查询切分成小查询,删除旧的数据就是一个很好的例子。定期清除大量数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。
2.分解联接查询
很多高性能的应用都会对联接查询进行分解。简单地说，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行联接。例如，下面这个查询：
优点:
1)多次查询利用缓存
2)可以有序IO读取数据
3)单个查询减少锁竞争
4)减少冗余记录访问
3.查询执行的基础
1. 客户端给服务器发送一条SQL查询语句。
2. 服务器端进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
3. MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询。
4. 将结果返回给客户端。
4.查询优化器
有很多种原因会导致MySQL优化器选择错误的执行计划，如下所示：
● 统计信息不准确。MySQL服务器依赖存储引擎提供的统计信息来评估成本，但是有的存储引擎提供的信息是准确的，有的偏差可能非常大。例如，InnoDB因为其MVCC的架构，并不能维护一个数据表的行数的精确统计信息。
● 成本指标并不完全等同于运行查询的实际成本，因此即使统计数据是准确的，查询的成本也可能超过或者低于MySQL估算的近似值。例如，有时候某个执行计划虽然需要读取更多的页面，但是它的成本却更低。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话，那么它的访问成本将很低。MySQL并不知道哪些页面在内存中、哪些在磁盘中，所以查询在实际执行过程中到底需要多少次物理I/O是无法得知的。
● MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能短，但是MySQL只是基于其成本模型选择最优的执行计划，而有些时候这并不是最快的执行方式。所以，这里我们看到的根据执行成本来选择执行计划并不是完美的模型。
● MySQL从不考虑其他并发执行的查询，这可能会影响到当前查询的速度。
● MySQL也并不是任何时候都是基于成本的优化。它有时也会基于一些固定的规则，例如，如果存在全文搜索的MATCH()子句，则在存在FULLTEXT索引的时候就使用全文索引。即使有时候使用其他索引和WHERE条件可以远比这种方式要快，MySQL也仍然会使用对应的全文索引。
● MySQL不会考虑不受其控制的操作的成本，例如，执行存储函数或者用户自定义函数的成本。
● 后面我们还会看到，优化器有时候无法估算所有可能的执行计划，所以它可能错过实际上最优的执行计划
在很多数据库服务器中，IN()完全等同于多个OR条件的子句，因为这两者是完全等价的。在MySQL中这点是不成立的，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O（logn）复杂度的操作，等价地转换成OR查询的复杂度为O（n），对于IN()列表中有大量取值的时候，MySQL的处理速度将会更快。
表和索引的统计信息
MySQL架构由多个层次组成。在服务器层有查询优化器，却没有保存数据和索引的统计信息。统计信息由存储引擎实现
MySQL如何执行联接查询
我们先来看一个UNION查询的例子。对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表中的数据来完成UNION查询。在MySQL的概念中，每个查询都是一次联接，所以读取临时表的结果也是一次联接。
在MySQL 8.0.20版本之后，已经不再使用基于块的嵌套循环联接操作，取而代之的是哈希联接（参见链接33）。
执行计划
MySQL生成查询的一棵指令树[10]，然后通过查询执行引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。如果你对某个查询执行EXPLAIN EXTENDED后，再执行SHOW WARNINGS，就可以看到重构出的查询。[11]
多表联接的一种方式
被称为一棵平衡树。但是，这并不是MySQL执行查询的方式。正如我们在前面章节中介绍的，MySQL总是从一个表开始，一直嵌套循环、回溯完成所有表联接。所以，MySQL的执行计划总是如图8-3所示，是一棵左侧深度优先的树。
MySQL如何实现多表联接
联接查询优化器通过评估不同顺序时的成本来选择一个成本最低的联接顺序。
这和我们前面给出的执行计划完全不同。MySQL从actor表开始（从上面的EXPLAIN结果的第一行输出可以看出这一点），然后与我们前面的计划按照相反的顺序进行联接。这样是否效率更高呢？我们来看看。我们先使用STRAIGHT_JOIN关键字，按照之前的顺序执行，下面是对应的EXPLAIN输出结果：
排序优化
无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序。
当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过MySQL将这个过程统一称为文件排序（filesort），即使完全是在内存中排序不需要任何磁盘文件时也是如此。
如果需要排序的数据量小于“排序缓冲区”，MySQL使用内存进行快速排序操作。如果内存不够排序，那么MySQL会先将数据分块，对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。
MySQL有如下两种排序算法。
两次传输排序（旧版本使用）
读取行指针和需要排序的字段，对其进行排序，然后再根据排序结果读取所需要的数据行。
这需要进行两次数据传输，即需要从数据表中读取两次数据，第二次读取数据的时候，因为是读取排序列进行排序后的所有记录，这会产生大量的随机I/O，所以两次传输排序的成本非常高。
单次传输排序（新版本使用）
先读取查询所需要的所有列，然后再根据给定列进行排序，最后直接返回排序结果。
因为不再需要从数据表中读取两次数据，对于I/O密集型的应用来说，这样做的效率高了很多。另外，相比两次传输排序，这个算法只需要一次顺序I/O就可读取所有的数据，而无须任何的随机I/O。然而，这种方式可能占用更多空间，因为会保存查询中每一行所需要的列，而不仅仅是进行排序操作所需要的列。这意味着更少的元组可以放入排序缓冲区，使得文件排序（filesort）操作必须执行更多的排序合并过程。
查询执行引擎
在解析和优化阶段，MySQL将生成查询对应的执行计划，MySQL的查询执行引擎会根据这个执行计划来完成整个查询。这里的执行计划是一个数据结构，而不是和很多其他的关系数据库那样生成对应的可执行的字节码。
将结果返回给客户端
MySQL将结果集返回客户端是一个增量且逐步返回的过程。
服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。另外，这样的处理也可让MySQL客户端第一时间获得返回的结果。[12]结果集中的每一行都会以一个满足MySQL客户端/服务器通信协议的封包发送，再通过TCP协议进行传输，在TCP传输的过程中，可能对MySQL的封包进行缓存，然后批量传输。
MySQL查询优化器的局限性
UNION的限制
等值传递
某些时候，等值传递会带来一些意想不到的额外消耗。例如，考虑一列上的巨大IN()列表，优化器知道它将等于其他表中的一些列，这是由于WHERE、ON或USING子句使列彼此相等。
优化器通过将列表复制到所有相关表中的相应列来“共享”列表。通常，因为各个表新增了过滤条件，所以优化器可以更高效地从存储引擎过滤记录。但是如果这个列表非常大，则会导致优化和执行都会变慢。在写作本书的时候，除了修改MySQL源代码，目前还没有什么办法能够绕过该问题（不过这个问题很少会碰到）。
并行执行
MySQL无法利用多核特性来并行执行查询。很多其他的关系数据库能够提供这个特性，但是MySQL做不到。这里特别指出是想告诉读者不要花时间去尝试寻找并行执行查询的方法。
在同一个表中查询和更新
MySQL不允许对一张表同时进行查询和更新。这其实并不是优化器的限制，如果你清楚MySQL是如何执行查询的，就可以避免这种情况。下面是一段无法运行的SQL语句，尽管这是一段符合标准的SQL语句。这个查询会将表中每一行的c字段值更新为和该行的type字段值相同的行数量：
可以使用生成表的形式来绕过上面的限制，因为MySQL只会把这个表当作一个临时表来处理。实际上，这执行了两个查询：一个是子查询中的SELECT语句，另一个是多表UPDATE查询，其中包含原表和子查询的联接结果。子查询会在UPDATE语句打开表之前就完成，所以下面的查询将会正常执行：
COUNT()的作用
COUNT()是一个特殊的函数，有两种非常不同的作用：它可以统计某列的值的数量，也可以统计行数。
在统计列值时要求列值是非空的（不统计NULL）。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数。
COUNT（*）实际上，它会忽略所有的列而直接统计所有的行数。
例如，假设可能需要通过一个查询返回各种不同颜色的商品数量，此时不能使用OR语句（比如，SELECT COUNT（color='blue'OR color='red'）FROM items；），因为这样做无法区分不同颜色的商品数量；也不能在WHERE条件中指定颜色（比如，SELECT COUNT（*）FROM items WHERE color='blue'AND color='RED'；），因为颜色的条件是互斥的。下面的查询可以在一定程度上解决这个问题：[13]
也可以使用COUNT()而不是SUM()实现同样的目的，只需要将满足条件设置为真，不满足条件设置为NULL即可：
使用近似值
有时候，某些业务场景并不要求完全精确的统计值，此时可以用近似值来代替。EXPLAIN出来的优化器估算的行数就是一个不错的近似值，执行EXPLAIN并不需要真正地去执行查询，所以成本很低。
更复杂的优化
通常来说，COUNT()查询需要扫描大量的行（意味着要访问大量数据）才能获得精确的结果，因此是很难优化的。除了前面提到的方法，在MySQL层面还能做的就只有索引覆盖扫描了。如果这还不够，那就需要考虑修改应用的架构，可以增加类似Memcached这样的外部缓存系统。不过，可能很快你就会陷入一个熟悉的困境：“快速、精确和实现简单”。三者永远只能满足其二，必须舍掉一个。
优化联接查询
1.确保ON或者USING子句中的列上有索引。索引只对联接查询的第二个表生效。
2.索引只对order by group by 中的一个表的列才能是使索引生效。
使用WITH ROLLUP优化GROUP BY
group by的基础上再进行统计
limit的详细用法
1、用于强制返回指定的记录行数
在查询中，经常要返回前几条或者中间某几行数据时，用到limit
语法如下：
select * from table_name limit [offset，] rows
1
参数说明：
offset：指定第一个返回记录行的偏移量（即从哪一行开始返回），注意：初始行的偏移量为0。
rows：返回具体行数。
总结：如果limit后面是一个参数，就是检索前多少行。如果limit后面是2个参数，就是从offset+1行开始，检索rows行记录。
举例：
select * from table_name limit 10;//检索前10行记录
select * from table_name limit 5 ，10;//从第6行开始，检索10行记录，即：检索记录行 6-15
优化LIMIT和OFFSET子句
一个非常常见又令人头疼的问题是，在偏移量非常大的时候，例如，可能是LIMIT 1000，20这样的查询，这时MySQL需要查询10020条记录然后只返回最后20条，前面10 000条记录都将被抛弃，这样的代价非常高。
要优化这种查询，要么是在页面中限制分页的数量，要么是优化大偏移量的性能。
优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描，而不是查询所有的行。然后根据需要做一次联接操作再返回所需的列。在偏移量很大的时候，这样做的效率会有非常大的提升。考虑下面的查询：
如果可以使用书签记录上次取数据的位置，那么下次就可以直接从该书签记录的位置开始扫描，这样就可以避免使用OFFSET。例如，若需要按照租借记录做翻页，那么可以根据最新一条租借记录向回追溯，这种做法可行是因为租借记录的主键是单调增长的。首先使用下面的查询获得第一组结果：
假设上面的查询返回的是主键为16，049到16，030的租借记录，那么下一页查询就可以从16，030这个点开始：
该技术的好处是无论翻页到多么靠后，其性能都会很好。
其他优化办法还包括使用预先计算的汇总表，或者联接到一个冗余表，冗余表只包含主键列和需要做排序的数据列。
优化SQL CALC FOUND ROWS
分页的时候，另一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示（hint），这样就可以获得去掉LIMIT以后满足条件的行数，因此可以作为分页的总数。
一个更好的设计是将具体的页数换成“下一页”按钮，假设每页显示20条记录，那么我们每次查询时都是用LIMIT返回21条记录并只显示20条，如果第21条存在，那么就显示“下一页”按钮，否则就说明没有更多的数据，也就无须显示“下一页”按钮了。
另一种做法是先获取并缓存较多的数据——例如，缓存1000条——然后每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略，如果结果集小于1000，就可以在页面上显示所有的分页链接，因为数据都在缓存中，所以这样做不会对性能造成影响。如果结果集大于1000，则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。这两种策略都比每次生成全部结果集再抛弃不需要的数据的效率高很多。
有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值（实际上，Google的搜索结果总数也是一个近似值）。当需要精确结果的时候，再单独使用COUNT（*）来满足需求，这时如果能够使用索引覆盖扫描则通常也会比SQL_CALC_FOUND_ROWS快得多。
优化UNION查询
MySQL总是通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地被使用。
手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中，以便优化器可以充分利用这些条件进行优化（例如，直接将这些子句冗余地写一份到各个子查询）。
UNION会对结果集进行唯一性检查，不需要消除重复的行 一定使用UNION ALL。
MySQL总是将结果放入临时表，然后再读出，再返回给客户端，虽然很多时候这样做是没有必要的（例如，MySQL可以直接把这些结果返回给客户端）。
小结
如果把创建高性能应用程序比作一个环环相扣的“难题”，除了前面介绍的schema、索引和查询语句设计之外，查询优化应该是解开“难题”的最后一步。要想写一个好的查询，你必须理解schema设计、索引设计等，反之亦然。
理解查询是如何被执行的以及时间都消耗在哪些地方，依然是前面我们介绍的响应时间的一部分。如果再加上一些诸如解析和优化过程的知识，就可以更进一步地理解我们在第7章中讨论的MySQL如何访问表和索引的内容了。这也可从另一个维度帮助你理解MySQL在访问表和索引时查询和索引的关系。
优化通常需要三管齐下：不做、少做、快速地做。
[1] 如果应用服务器和数据库不在同一台主机上，网络开销就十分明显。即使是在同一台服务器上，也仍然会有数据传输的开销。
[2] 更多内容请参考本章后面的“优化COUNT()查询”一节。
[3] Percona Toolkit中的pt-archiver工具就可以安全而简单地完成这类工作。
[4] 如果查询太大，服务端会拒绝接收更多的数据并抛出相应错误。
[5] 可以使用SQL_BUFFER_RESULT，后面会介绍这方面的知识。
[6] 例如，在联接操作中，范围检查的执行计划会针对每一行重新评估进行索引。可以通过EXPLAIN执行计划中的Extra列是否有“range checked for each record”来确认这一点。该执行计划还会增加select_full_range_join这个服务器变量的值。
[7] 一部电影没有演员，是有点儿奇怪。不过在示例数据库Sakila中，影片Slacker Liaisons中就没有任何演员，它的描述是“鲨鱼和见过中国古代鳄鱼的学生的简短传说”。
[8] 可以通过查看MySQL手册中的“索引提示”“优化器提示”等章节来确认在某个具体的版本中支持哪些提示，以及如何使用这些提示。
[9] 如后文所述，MySQL的执行计划并不是这里描述的这么简单，过程中有非常多的细节优化，使得整个过程是一个非常复杂的过程。
[10] 可以通过在EXPLAIN语句中新增FORMAT=TREE关键字来查看树形结构。
[11] MySQL根据执行计划生成输出。这和原查询有完全相同的语义，但是查询语句可能并不完全相同。
[12] 可以通过一些办法来影响这个行为，例如，可以使用SQL_BUFFER_RESULT。参考MySQL官方手册中的“Optimizer Hints”一节可获得更多信息。
