2. 执行计划成本不等价于实际执行的成本
有时候某些执行计划需要读取更多的页面，但是它的成本却更小，因为这些页面都是顺序读或这些页面都已经在内存中时，那么它的访问成本将会很小，MySQL 层面并不知道哪些页面在内存中，哪些在磁盘，所以查询时执行过程到底需要多少次 IO 是无法得知的
- MySQL 最优可能与你想的不一样
MySQL 优化是基于成本模型的优化，但是有可能不是最快的优化；
A join B join C，一定是先读 A 再读 B 再读 C 嘛？或者先读 C 再读 B 再读 A 嘛？这个过程是没办法预估的，它是根据优化器内部的一个规则来选择先读哪个再读哪个表的
- MySQL 不会考虑不受其控制的操作成本
在计算成本值时，并不会考虑有多少的并发情况，因为 MySQL 并不知道，也无法预估，只能基于单条查询来做预估，所以这个值不是那么准确，但大部分情况下是没有问题的
- MySQL 不会考虑不受其控制的操作成本
当执行存储过程或用户自定义函数时，因为不是 MySQL 提供的默认功能，所以 MySQL 中可能不会有对应的统计信息，因此不会考虑此类操作的成本
优化器的优化策略
- 静态优化表示直接对解析树进行分析后完成优化
- 动态优化表示与查询的上下文（查询缓存）有关，也可能跟取值、索引对应的行数有关
- MySQL 对查询的静态优化只需要一次，但对动态优化在每次执行时都需要重新评估，举例说明：比如 A join B，A 表在查询缓存里，B 表不在，这里就要做个判断，A 表在内存或磁盘完全是两种不一样的处理方式，所以查询上下文指的是在一个会话里，之前的查询操作对当前的 SQL 语句产生的影响
优化器的优化类型
- 重新定义关联表的顺序
数据表的关联并不总是按照查询中的指定顺序进行的，决定关联顺序是优化器很重要的功能，比如：A join B join C，看起来是先读 A 再读 B 再读 C，实际上可能不是这样的，可能会先读 C 再读 B 最后再读 A，它里面有一个优化机制在作判断
- 外连接转换为内连接，内连接的效率要高于外连接，原因：在于外连接在操作步骤上要比内连接多出来一步
- 使用等价变换规则，MySQL 可以使用一些等价的变化来简化、规划表达式
例如：条件中包含 a != 4 这个判断，可以替换成 a > 4 & a < 4 这个操作；
在实际工作时，能用一个表达式解决绝不要用两个表达式，方便优化器进行优化.
- 优化 COUNT、MAX、MIN
索引、列是否可以为空，通常可以帮助 MySQL 来优化这类表达式，例如：要找到某一列的最小值，只需要查询索引的最左端记录即可，无须进行全文扫描比较
- 预估并转化为常数表达式
当 MySQL 检测到一个表达式可以转换为常数时，就会一直把表达式作为常数进行处理
mysql> explain select film.film_id,film_actor.actor_id from film inner join film_actor using(film_id) where film.film_id = 1; +----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index | | 1 | SIMPLE | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | Using index | +----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
type = const，const 表示常数，执行效率是比较高的，尽可能把我们 SQL 中的表达式转化成常量值
- 索引覆盖扫描：当索引中的列包含在所有查询中需要使用到的列时，可以使用覆盖索引
- 子查询优化：MySQL 在某些情况下，可以将子查询转换为一种效率更高的形式，从而减少多个查询、多次对数据进行访问，例如：经常查询的数据放入到缓存中
- 等值传播
若两个列的值通过等值关联，那么 MySQL 能够把其中一个列的 where 条件传递到另外一个
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500;
使用了 film_id 字段进行等值关联，film_id 列不仅适用于 film 表同时也适用于 film_actor 表
explain select film.film_id from film inner join film_actor using(film_id) where film.film_id > 500 and film_actor.film_id > 500;
关联查询
MySQL 关联查询很重要，但是关联查询执行的策略比较简单；MySQL 对任何关联都会执行嵌套循环的关联操作，即 MySQL 先在一张表中循环取出单条数据，然后再嵌套到下一表中寻找匹配的行，直到找到所有表中匹配的行为止
根据各个表匹配的行，返回查询中需要的各个列，MySQL 会尝试在最后一个关联表中找到所有匹配的行，若最后一个关联表无法找到更多的行之后，MySQL 返回到上一层次的关联表，看是否能够找到更多匹配的记录，以此类推迭代执行
整体的思路如此，但是要注意实际的执行过程中有多种形式
通过案例来演示，不同顺序执行方式对查询性能的影响
mysql> explain select film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id); +----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+ | 1 | SIMPLE | actor | NULL | ALL | PRIMARY | NULL | NULL | NULL | 200 | 100.00 | NULL | | 1 | SIMPLE | film_actor | NULL | ref | PRIMARY,idx_fk_film_id | PRIMARY | 2 | sakila.actor.actor_id | 27 | 100.00 | Using index | | 1 | SIMPLE | film | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.film_actor.film_id | 1 | 100.00 | NULL | +----+-------------+------------+------------+--------+------------------------+---------+---------+---------------------------+------+----------+-------------+
执行完以后会发现执行顺序：actor、film_actor、film，查看所耗费的成本如下：
mysql> show status like 'last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 7892.932509 | +-----------------+-------------+
若想按照自己预想的规定顺序执行，使用 straight_join 关键字，如下：
mysql> explain select straight_join film.film_id,film.title,film.release_year,actor.actor_id,actor.first_name,actor.last_name from film inner join film_actor using(film_id) inner join actor using(actor_id); +----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+ | 1 | SIMPLE | film | NULL | ALL | PRIMARY | NULL | NULL | NULL | 1000 | 100.00 | NULL | | 1 | SIMPLE | film_actor | NULL | ref | PRIMARY,idx_fk_film_id | idx_fk_film_id | 2 | sakila.film.film_id | 5 | 100.00 | Using index | | 1 | SIMPLE | actor | NULL | eq_ref | PRIMARY | PRIMARY | 2 | sakila.film_actor.actor_id | 1 | 100.00 | NULL | +----+-------------+------------+------------+--------+------------------------+----------------+---------+----------------------------+------+----------+-------------+
表加载顺序：film、film_actor、actor，查看其耗费的成本
mysql> show status like 'last_query_cost'; +-----------------+-------------+ | Variable_name | Value | +-----------------+-------------+ | Last_query_cost | 8885.087226 | +-----------------+-------------+
通过这两种对比，可以看出 MySQL 选择了成本更低的方式来执行 SQL 语句
排序优化
无论如何排序，都是一个成本很高的操作，从性能的角度出发，应该尽可能的避免排序或尽可能避免对大量数据排序
推荐使用 索引排序，但是当不能使用索引时，MySQL 就需要自己进行排序，若数据量小则在内存中进行，若数据量大就需要使用磁盘，MySQL 称之为 filesort
若需排序的数量小于排序缓冲区（show variables like '%sort_buffer_size%'）MySQL 使用内存进行快速排序操作，若内存不够排序，MySQL 就会将树分块，对每个独立块使用快速排序进行排序，并将各个块排序结果存放在磁盘上，然后对每个排好序的块进行合并，最后返回结果
mysql> show variables like '%sort_buffer_size%'; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | innodb_sort_buffer_size | 1048576 | | myisam_sort_buffer_size | 8388608 | | sort_buffer_size | 262144 | +-------------------------+---------+
在进行排序时还会涉及到两种排序算法 > 两次传输排序、单次传输排序
两次传输排序
第一次数据读取是将需要排序的字段读取出来，然后进行排序；第二次是将排好序的结果按照所需去读取数据行
这种方式效率比较低，原因：第二次读取数据时因为已经排好序，需要去读取所有记录，而此时更多的是随机 IO，读取数据成本会比较高
两次传输的优势在于，在排序时存储尽可能少的数据，让排序缓冲区可以尽可能的容纳行数来进行排序操作
单次传输排序
先读取查询需要的所有列，然后再根据给定列进行排序，最后直接返回排序后的结果，此方式只需要一次顺序 IO 读取所有的数据，无须任何的随机 IO，问题在于查询列特别多时，会占用大量的存储空间，无法存储大量的数据
当需要排序的列总大小超过 max_length_for_sort_data 参数定义的字节数，MySQL 会选择两次传输排序，否则使用单次传输排序，Of Course，用户可以设置此参数的值来选择排序的方式
mysql> show variables like '%max_length_for_sort_data%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | max_length_for_sort_data | 1024 | +--------------------------+-------+
大数据量查询优化
基于大数据量查询优化场景下，从而优化特定类型的查询
优化 COUNT 查询
在进行 COUNT 计数查询时需要注意以下几点：
- 总有人认为 MyISAM 存储引擎中 COUNT 函数效率比较快，这是有前提条件的，只有没有任何 where 条件的
COUNT(*)才是比较快的；MyISAM 有一个变量来记录插入的行数，所以会比较快，但是一旦携带了 WHERE 条件去统计数量，变量记录的值就不准确了，仍然还是要和普通查询一样，挨个遍历 - 使用近似值，在某些应用场景中，不需要完全精确的值，可以参考使用近似值来代替，比如：使用 Explain 来获取近似值或采用
计算近似值的算法 > HyperLogLog - 一般情况下，COUNT 需要扫描大量的行才能获取精确的数据，其实是更复杂的优化，在实际操作时可以考虑
使用索引覆盖扫描或增加汇总表提前统计好数量或引入外部缓存系统；插入数据时进行汇总表、外部缓存的累加操作，等需要总的记录数时直接取值就可以了，不需要从数据表中进行统计，但是一定要借助外部的一些系统
优化关联查询
首先要确保 on、using 子句中的关联列有索引，在创建索引时要考虑到关联的顺序
业务系统中，一般都是关联主键、外键没有什么问题；特殊情况下，会用普通列作关联查询，这时就可能会出现问题，所以最好还是使用索引列；使用索引列的效率是比较高的，既然创建了索引就一定要用，比如 > 建了主键还不用主键来优化就没什么意义了
确保任何 group by、order by 中表达式只涉及到一个表中的列（关联表查询时，只使用一个表中的字段进行排序、分组）这样 MySQL 才有可能使用索引来优化这个过程；group by、order by 能使用索引还是要用，这样效率是比较高的，不使用的话就会有问题，比如：filesort 磁盘轮转排序效率低缓
优化子查询
子查询优化，建议是尽可能使用关联查询进行代替，不要使用对应的子查询；在某些应用场景里，可以用子查询也可以用 JOIN 方式，不推荐使用子查询；每次 SELECT 时会得到一个结果，子查询的记过会放到临时表里，临时表就会涉及到 IO，与其这样还不如直接使用 JOIN 关联数据，效率可能还会更高一些
