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 关联数据,效率可能还会更高一些