MySQL学习(8)基于成本的优化
什么是成本MySQL中一条SQL语句的执行成本包含两个部分：
[*]I/O成本：从磁盘中加载数据（页）到内存的的过程中消耗的时间称为I/O成本。
[*]CPU成本：读取记录以及检测记录是否满足搜索条件、对结果集进行排序等操作，消耗的时间称为CPU成本。
MySQL默认规定读取一个页面的I/O成本是1.0，读取及检测一条记录的CPU成本是0.2。
单表查询的成本
MySQL Server接收到客户端发过来的SQL语句后，经历过查找缓存（如有）和语法解析后，MySQL的优化器会找出所有可能用来执行这条语句的方案，并对比这些方案后选择一条成本最低的方案，这个成本最低的方案就是执行计划，在此期间会访问少量的数据。然后才会跳用存储引擎的结构真正地执行查询。这个过程总结一下：
[*]根据搜索条件，找出所有可能使用的索引。
[*]计算全表扫描的代价。
[*]计算使用不同索引执行查询的代价。
[*]对比各种执行方案的代价，找出成本最低的方案。
SELECT
*
FROM
single_table
WHERE
key1 IN ('a', 'b', 'c')
AND key2 > 10
AND key2 < 1000
AND key3 > key2
AND key_part1 LIKE '%a%'
AND common_field LIKE 'a%';
找出所有可能使用的索引possible keys
根据搜索条件，只要索引列和常数使用=、、IN、NOT IN、IS NULL、IS NOT NULL、>、=、 10 AND key2 < 1000使用了唯一二级索引uk_key2。
<li >key3 > key2未与常数进行比较，无法形成合适的扫描区间。
<li >key_part1 LIKE '%a%'使用LIKE时无法匹配开头，无法形成合适的扫描区间。
<li >common_field列未建立索引。
</ul>这条语句的possible keys为idx_key1、uk_key2。
计算全表扫描的代价
全表扫描的成本需要两个参数：
[*]聚簇索引占用的页面数
[*]该表中的记录数
通过SHOW TABLE STATUS语句查看表的统计信息
SHOW TABLE STATUS LIKE 'single_table';
[*]Rows：对于InnoDB存储引擎，这个值表示表中记录条数的估值。
[*]Data_length：表示表占用的存储空间字节数，对于InnoDB来说，相当于聚簇索引占用的存储空间大小。
Data_length = 聚簇索引的页面数量 ✖️ 每个页面的大小表的默认大小为16KB，可以推导出聚簇索引的页面数量：
聚簇索引的页面数量 = 1589248 ➗ 16 ➗ 1024 = 97I/O成本：页面数量为97，没加载一个页的成本为1.0，总共97 ✖️ 1.0 + 1.1 = 98.1，其中1.1为内部微调值。
CPU成本：读取一条记录的成本为0.2，总共9895 ✖️ 0.2 + 1.0 = 1939.6，其中1.0为内部微调值。
聚簇索引包含记录和目录项，只有叶子节点才是记录，但是计算成本时，直接将所有页面都计算进入。
计算使用不同索引的执行查询的代价
MySQL优化器优先分析使用唯一二级索引的成本，再分析使用普通二级索引的成本。
分析步骤：
[*]扫描区间数量
[*]需要回表的记录数
无论扫描区间的二级索引占用了多少页面，优化器都认为读取索引的一个扫描区间的I/O成本与读取一个页面的I/O成本是相同的，也就是几个扫描区间，就有几个1.0。
MySQL除了为每个表维护一份统计数据，还会为 表中的每一个索引维护一份统计数据。
SELECT * FROM single_table WHERE key1 IN ('aaa', 'aab', 'aac', 'aad'...'zzz');
属性名描述Table索引所属表名Non_unique是否唯一。0:是。1:否Key_name索引名称，其中聚簇索引的Key_name为PRIMARYSeq_in_index该列在索引包含的列中的位置，从1开始计数。Column_name该列的名称Cardinality该列不重复值的数量。联合索引表示从索引列的第一个列开始，到当前列为止的组合不重复的数量。Sub_part表示该列为字符串或字节串类型的列建立索引的前缀字符或字节数，若对完整的列建立索引，则为NULL。Packed该列的压缩方式，NULL表示未压缩。Null是否允许存储NULL值Index_type该列所属的索引的类型，一般最常见的是BTREE。在InnoDB存储引擎中，索引统计数据中的Cardinality是一个估值，并不精确。表统计数据的Rows也是。
根据索引统计数据中的Cardinality和表统计数据的Rows，可以计算出列中一个值平均重复多少次：
一个值平均重复次数 = Rows ➗ Cardinality就可以估算出所有的单点扫描区间有多少记录了：
记录数量 = 一个值平均重复次数 ✖️ 单点区间数量这种方法相对来说比index dive简单多了，但是不精确，适用于单点区间非常多的情况。
连接查询的成本
什么是条件过滤
查询驱动表后得到的记录条数称为驱动表的扇出，连接查询的成本分为两个部分：
[*]单词查询驱动表的成本
[*]多次查询被驱动表的成本（次数与驱动表的扇出决定）
显然， 驱动表的扇出越少，连查查询的成本越低。驱动表的扇出越多，成本成倍增加。
[*]当驱动表使用全表查询时，扇出值就是驱动表中的记录数量，查询优化器根据表统计数据得到表的记录数量，并作为扇出值。
[*]当驱动表使用二级索引查询的<arel="noopener">计算方法，可以得出扫描区间内的记录数量，这个就是扇出值。
[*]当驱动表无法使用索引时，只能姑且认为扇出值是全表记录数量。
[*]当驱动表可以使用二级索引生成合适的扫描区间，但是搜索条件中包含不可以使用索引的列，那就需要在索引列形成的扫描区间的记录数量中，猜测有多少记录符合条件。
这个猜测过程称为条件过滤（Condition Filtering）。猜测过程可能会使用到索引，也可能会使用到统计数据。
两表连接成本分析
连接查询总成本 = 单词访问驱动表的成本 + 驱动表扇出值 ✖️ 单词访问被驱动表的成本外连接的驱动表是固定的，只需要分别为驱动表和被驱动表选择成本最低的访问方法，就可以得到最好的方案。
内连接驱动表和被驱动可以互换，优化器需要分别考虑两种连接顺序的成本，然后选取成本更低的连接顺序，以及各个表最优访问方法作为最终的执行计划。
在连接查询中，最影响成本的是驱动表扇出值✖️单词访问被驱动表的成本，如果这两个值较高，成本则是成倍增长。优化重点就是：
[*]尽量减少驱动表的扇出；
[*]访问被驱动表的成本尽量低，尽量在被驱动表的连接列上建立索引，这样可以使用ref访问方法。如果是主键或唯一二级索引那就最好了。
多表连接成本分析
分析多表连接的成本，首先要考虑多表不同的连接顺序。MySQL在计算各种连接顺序的成本之前，会维护一个全局变量，就是当前最小的连接查询成本。在分析不同连接顺序的成本时，将保存当前已经分析连接顺序的成本最小值，如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就停止分析该连接顺序了，如果分析得出该连接顺序的成本小于当前最小的连接查询成本，则将覆盖新的值。
MySQL还提供optimizer_search_depth系统变量，表示依次分析不同连接顺序成本的最大表的数量。如果连接表的个数小于该值，就会分析每一种连接顺序的成本；若大于，也只分析该值数量的表。该值越大，成本分析越精确，越容易生成好的执行计划，但是消耗的时间越长；否则得到的就不是很好的执行计划，但是节省了分析时间。
SHOW VARIABLES LIKE 'eq_range_index_dive_limit'
启发式规则简介
根据以往经验指定的一些规则，凡事不满足这些规则的连接顺序压根就不分析。好处是减少分析不同连接顺序的数量，从而减少时间；不好是不能得到最好的执行计划。系统变量optimizer_prune_level控制是否使用启发式规则。
SHOW INDEX FROM single_table;
成本常数
MySQL中一条语句在server层进行操作的成本对应的成本常数存储在mysql.server_cost表中，存储引起的操作对应的成本常数存储在mysql.engine_cost表中。
SHOW VARIABLES LIKE 'optimizer_search_depth';
SHOW VARIABLES LIKE 'optimizer_prune_level';
阅读自《MySQL是怎样运行的》小孩子4919.
来源:https://www.cnblogs.com/haleyeung/p/17787401.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！
页:
[1]