MYSQL执行计划之成本计算
对于MySQL5.6之前的版本来说，只能通过EXPLAIN语句查看到最后优化器决定使用的执行 计划，却无法知道它为什么做这个决策。在MySQL 5.6以及之后的版本中，MySQL提出了一个optimizer trace的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程。
索引
在计算成本之前，我们需要先了解MYSQL的索引结构以及索引命中规则。
索引是什么？
索引是帮助MySQL高效获取数据的排好序的数据结构
数据结构
B-tree
B-Tree即B树，是一种多路平衡树类似于AVL（二叉平衡树），这里是多路。其特点如下:
带data的结构
1、叶节点具有相同的深度，叶节点的指针为空
2、所有索引元素不重复
3、节点中的数据索引从左到右递增排列
4、每个节点的查询效率不一样，最坏与树高度有关
B+Tree
B+Tree是B-Tree的变种，其特点如下：
带data的结构
1、非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
2、叶子节点包含所有索引字段
3、叶子节点用指针连接，提高区间访问的性能
4、数据都在叶节点上，查询的成本一样，与树高有关
想看数据结构的这里推荐一个地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
Hash
hash其实很熟悉了，Java中的HashMap和HashSet都是hash结构，只不过MTSQL实现了自己的算法。
其特点如下：
1、对索引的key进行一次hash计算就可以定位出数据存储的位置
2、很多时候Hash索引要比B+ 树索引更高效
3、仅能满足 “=”，“IN”，不支持范围查询
4、hash冲突问题
MYSQL的引擎索引
从存储角度上来说，MySQL索引分为聚集索引和非聚集索引。
逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一 索引等等。
从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB 引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）
聚集索引/聚簇索引
InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行 记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。
聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。 因此聚集索引的一个优点就是：
1、通过过聚集索引能获取完整的整行数据。
2、对于主键的排序查找和范围查找速度非常快。
思考：如果我们没有定义主键呢？
1、MySQL会使用唯一性索引。
2、如果 没有唯一性索引，MySQL也会 创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。
辅助索引/二级索引
聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据 都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办？我们一般会建立多个索引，这些索引被称为辅助索引/二级索引。 对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了相应行数据的聚集索引键。
自适应hash索引
InnoDB存储引擎除了我们前面所说的各种索引，还有一种自适应哈希索引，我们知 道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为3-4层,故 需要3-4次的IO查询。 所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么 就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通 过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查 询三四次节点的效率高了不少。 InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注 意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干 预。
通过命令：show engine innodb status
可以看到当前自适应哈希索引的使用 状况。
注意： 哈希索引只能用来搜索等值的查询,如 SELECT FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的*
回表
辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助 索引。当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级 别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个 完整的行记录。这个过程也被称为回表。
也就是根据辅助索引的值查询一条完整的 用户记录需要使用到2棵B+树----一次辅助索引，一次聚集索引。通过辅助索引查找数据如下：
为什么我们还需要一次回表操作呢?
直接把完整的用户记录放到辅助索引d的叶子节 点不就好了么？
如果把完整的用户记录放到叶子节点是可以不用回表，但是太占地 方了，相当于每建立一棵B+树都需要把所有的用户记录再都拷贝一遍，这就有点太 浪费存储空间了。
而且每次对数据的变化要在所有包含数据的索引中全部都修改一 次，性能也非常低下。
很明显，回表的记录越少，性能提升就越高，需要回表的记录越多，使用二级索引 的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。
那什么时候采用全表扫描的方式，什么时候使用采用二级索引 + 回表的方式去执 行查询呢？
这个就是查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录 数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。
MRR
我们知道二级索引在读取到一条记录后，就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的，也就是说这些 二级索引记录对应的聚簇索引记录所在的页面的页号是无序的。
每次执行回表操作时都相当于要随机读取一个聚簇索引页面，而这些随机IO带来的 性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read (MRR，多范围 读取)的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执 行回表操作。
相对于每读取一条二级索引记录就立即执行回表操作，这样会节省一些IO开销。使用这 个 MRR优化措施的条件比较苛刻，所以我们直接认为每读取一条二级索引记录就立即执行回表操作。
联合索引/复合索引
将表上的多个列组合起来进行索 引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。
建立联合索引只会建立1棵B+树，多个列分别建立索引会分别以 每个列则建立B+树，有几个列就有几个B+树，比如，index(note)、index(b)，就分别对note,b两个列各构建了一个索引。
联合索引的特点：
1、会按字段顺序，从左到右，从小到大的顺序排列。
2、相同的列则下一列也是顺序排好序的，依次类推。
最左前缀法则
在使用联合索引时，用到字段条件必须按字段的从左到右的从小到大的顺序特点，才能走多字段。
中间字段不能断，范围查询和模糊查询都可能中断。
索引下推
索引下推（Index Condition Pushdown，ICP）, 什么是索引下推？
如图表结构，对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like ‘LiLei%’ AND age = 22 AND position =‘manager’ 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和 position是无序的，无法很好的利用索引。 在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 ‘LiLei’ 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索 引上找出相应的记录，再比对age和position这两个字段的值是否符合。 MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 ‘LiLei’ 开头的索引之后，同时还会在索引里过 滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。 索引下推会减少回表次数，对于innodb引擎的表索引下推只能用于二级索引，innodb的主键索引（聚簇索引）树叶子节点上保存的是全 行数据，所以这个时候索引下推并不会起到减少查询全行数据的效果。
其他索引
hash索引和全文检索倒排索引。
全文索引
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开 始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。 注意，不管什么引擎，只有字段的数据类型为 char、varchar、text 及其系列才可以建 全文索引。 不过MySQL从设计之初就是关系型数据库，存储引擎虽然支持全文检索，整体架构 上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引， 不支持没有单词界定符( delimiter）的语言，如中文、日语、韩语等。
索引优化
索引覆盖/覆盖索引
简单地说索引覆盖就是使用二级索引时，不需要回表操作，数据直接从索引树就可以获取。
InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信 息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。
覆盖索引可以视为索引优化的一种方式，而并不是索引类型的一种。
前缀索引
有时候需要索引很长的字符列，这会让索引变得大且慢。
这种情况下我们需要保证某个列前缀 的选择性也是足够高的，以满足查询性能。（尤其对于BLOB、TEXT或者很长的VARCHAR类 型的列，应该使用前缀索引，因为MySQL不允许索引这些列的完整长度）。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间)。 前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。
通常来说前缀的长度为20比较合适，但我们可以通过更精确的统计来定义长度：
”count(distinct left(列名, 索引长度))/count(*)“的区分度来确定
创建前缀索引：
ALTER TABLE 表名ADD KEY (列名(长度));
缺点：
MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖索引。
三星索引
三星索引概念是在《Rrelational Database Index Design and the optimizers》 一书 （这本书也是《高性能MySQL》作者强烈推荐的一本书）中提出来的。原文如下： The index earns one star if it places relevant rows adjacent to each other, a second star if its rows are sorted in the order the query needs, and a final star if it contains all the columns needed for the query.
三星概念：
1、索引将相关的记录放到一起则获得一星；
2、如果索引中的数据顺序和查找中的排列顺序一致则获得二星；
3、如果索引中的列包含了查询中需要的全部列则获得三星。
二星（排序星）
在满足一星的情况下，当查询需要排序，group by、 order by，如果查询所需的顺序与 索引是一致的（索引本身是有序的），是不是就可以不用再另外排序了，一般来说排序可是影响性能的关键因素。
三星（宽索引星）
满足了二星的情况下，如果索引中所包含了这个查询所需的所有列（包括 where 子句 和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了， 减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。
一星
查询相关的索引行是相 邻的或者至少相距足够靠近的话，必须扫描的索引片宽度就会缩至最短，也就是说，让 索引片尽量变窄，也就是我们所说的索引的扫描范围越小越好。
优化建议：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index 效率高，filesort效率低。
2、order by满足两种情况会使用Using index。 1) order by语句使用索引最左前列。 2) 使用where子句与order by子句条件列组合满足索引最左前列。
3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
4、如果order by的条件不在索引列上，就会产生Using filesort。
5、能用覆盖索引尽量用覆盖索引
6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中 的限定条件就不要去having限定了。
Using filesort文件排序原理详解
filesort文件排序方式
1、单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可 以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
2、双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具 可以看到sort_mode信息里显示< sort_key, rowid >
MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来 判断使用哪种排序模式。
1、如果字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
2、如果 字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模式。
索引设计原则：
1、代码先行，索引后上
2、联合索引尽量覆盖条件
3、不要在小基数字段上建立索引
4、长字符串我们可以采用前缀索引
5、where与order by冲突时优先where
6、基于慢sql查询做优化
什么是成本？
MySQL执行一个查询可以有不同的执行方案，它会选择其中成本最低，或者说代价最低的 那种方案去真正的执行查询。什么是执行成本呢？其实在MySQL中一条查询语句的执行成 本是由下边这两个方面组成的：
I/O成本
我们的表经常使用的MyISAM、InnoDB存储引擎都是将数据和索引都存储到磁盘上的，当 我们想查询表中的记录时，需要先把数据或者索引加载到内存中然后再操作。这个从磁盘到内存这个加载的过程损耗的时间称之为I/O成本。
CPU成本
读取以及检测筛选记录是否满足对应的搜索条件、对结果集进行排序等这些操作损耗的时间称之为CPU成本。
对于InnoDB存储引擎来说，页是磁盘和内存之间交互的基本单位，MySQL规定读取一个页 面花费的成本默认是1.0，读取以及检测一条记录是否符合搜索条件的成本默认是0.2。 1.0、0.2这些数字称之为成本常数，这两个成本常数我们最常用到，当然还有其他的成 本常数。
注意，不管读取记录时需不需要检测是否满足搜索条件，其成本都算是0.2。
单表查询成本计算
表结构如下：
CREATE TABLE `order` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
`order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
`order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
`insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
`expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
`expire_time` datetime NOT NULL COMMENT '订单的过期时间',
`order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
KEY `idx_order_no` (`order_no`) USING BTREE,
KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10819 DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
在一条单表查询语句真正执行之前，MySQL的查询优化器会找出执行该语句所有可能使用 的方案，对比之后找出成本最低的方案，这个成本最低的方案就是所谓的执行计划，之 后才会调用存储引擎提供的接口真正的执行查询，这个过程总结一下就是这样：
1、根据搜索条件，找出所有可能使用的索引
2、计算全表扫描的代价
3、计算使用不同索引执行查询的代价
4、对比各种执行方案的代价，找出成本最低的那一个
语句：
SELECT * FROM order
WHERE
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
AND expire_time> ‘2021-03-22 18:28:28’
AND expire_time<= ‘2021-03-22 18:35:09’
AND insert_time> expire_time
AND order_note LIKE ‘%7 排1%’
AND order_status = 0;
1、根据搜索条件，找出所有可能使用的索引
MySQL把一个查询中可能使用到的索引称之为possible keys。
我们分析一下上边查询中涉及到的几个搜索条件：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) ，这个搜索条件可以使用二级索引 idx_order_no。 expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’， 这个搜索条件可以使用二级索引idx_expire_time。 insert_time> expire_time，这个搜索条件的索引列由于没有和常数比较，所以并不能 使用到索引。
order_note LIKE ‘%hello%’，order_note即使有索引，但是通过LIKE操作符和以通配符开头的字符串做比较，不可以适用索引。
order_status = 0，由于该列上只有联合索引，而且不符合最左前缀原则，所以不会用 到索引。
综上所述，上边的查询语句可能用到的索引，也就是possible keys只有 idx_order_no,idx_expire_time。
通过借助opitimeter trace工具查看：
EXPLAIN format=json SELECT * FROM order WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) AND expire_time> ‘2021-03-22 18:28:28’ AND expire_time<= ‘2021-03-22 18:35:09’ AND insert_time> expire_time AND order_note LIKE ‘%7 排1%’ AND order_status = 0;
{
"query_block": {
"select_id": 1,
"cost_info": {
"query_cost": "17.81"
},
"table": {
"table_name": "order",
"access_type": "range",
"possible_keys": [
"idx_order_no",
"idx_expire_time"
],
"key": "idx_expire_time",
"used_key_parts": [
"expire_time"
],
"key_length": "5",
"rows_examined_per_scan": 39,
"rows_produced_per_join": 0,
"filtered": "0.13",
"index_condition": "((`db01`.`order`.`expire_time` > TIMESTAMP'2021-03-22 18:28:28') and (`db01`.`order`.`expire_time` <= TIMESTAMP'2021-03-22 18:35:09'))",
"cost_info": {
"read_cost": "17.80",
"eval_cost": "0.01",
"prefix_cost": "17.81",
"data_read_per_join": "24"
},
"used_columns": [
"id",
"order_no",
"order_note",
"insert_time",
"expire_duration",
"expire_time",
"order_status"
],
"attached_condition": "((`db01`.`order`.`order_status` = 0) and (`db01`.`order`.`order_no` in ('DD00_6S','DD00_9S','DD00_10S')) and (`db01`.`order`.`insert_time` > `db01`.`order`.`expire_time`) and (`db01`.`order`.`order_note` like '%7 排1%'))"
}
}
}
2. 计算全表扫描的代价
对于InnoDB存储引擎来说，全表扫描的意思就是把聚簇索引中的记录都依次和给定的搜索条件做一下比较，把符合搜索条件的记录加入到结果集，所以需要将聚簇索引对应的 页面加载到内存中，然后再检测记录是否符合搜索条件。由于查询成本=I/O成本+CPU成 本，所以计算全表扫描的代价需要两个信息：
使用语句：SHOW TABLE STATUS LIKE ‘order’
得出表的记录数：10655，总大小：1589248
innodb默认page大小是16KB,则需扫描的聚簇索引页数为：1589248/16/1024=97页
成本计算：
1、I/O成本：
97 x 1.0+1.1=98.1
97指的是聚簇索引占用的页面数，1.0指的是加载一个页面的IO成本常数，后边的1.1是 一个微调值。
2、CPU：
10655 x 0.2+1.0=2132
10655指的是统计数据中表的记录数，对于InnoDB存储引擎来说是一个估计值，0.2指的 是访问一条记录所需的CPU成本常数，后边的1.0是一个微调值。
总成本：
98.1+2132=2230.1
注意：
表中的记录其实都存储在聚簇索引对应B+树的叶子节点中，所以只 要我们通过根节点获得了最左边的叶子节点，就可以沿着叶子节点组成的双向链表把所有记录都查看一遍。
也就是说全表扫描这个过程其实有的B+树非叶子节点是不需要访问的，但是MySQL在计算 全表扫描成本时直接使用聚簇索引占用的页面数作为计算I/O成本的依据，是不区分非叶 子节点和叶子节点的。
3. 计算使用不同索引执行查询的代价
条件中可以知道可能使用到idx_order_no，idx_expire_time这两个索 引，我们需要分别分析单独使用这些索引执行查询的成本。辅助索引的成本计算依赖于索引过滤后的行数和索引区间范围
索引区间范围区间多个则需要多次IO,
1、idx_order_no：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
3 x 1.0 个，IO成本为3
index dive
这里说明下Index dive ,有时候使用索引执行查询时会有许多单点区间，比如使用IN语句就很容易产生非常多的 单点区间，比如下边这个查询（下边查询语句中的…表示还有很多参数）： SELECT * FROM order WHERE order_no IN (‘aa1’, ‘aa2’, ‘aa3’, … , ‘zzz’);
很显然，这个查询可能使用到的索引就是idx_order_no，由于这个索引并不是唯一二级索引，所以并不能确定一个单点区间对应的二级索引记录的条数有多少，需要我们去计 算。就是先获取索引对应的B+树的区间最左记录和区间最右记录，然后再计算这两条记 录之间有多少记录（记录条数少的时候可以做到精确计算，多的时候只能估算）。MySQL 把这种通过直接访问索引对应的B+树来计算某个范围区间对应的索引记录条数的方式称 之为index dive。
如果IN语句里20000个参数怎么办？
这就意味着MySQL的查询优化器为了计算这些单点区间对应的索引记录条数，要进行 20000次index dive操作，
这性能损耗就很大，搞不好计算这些单点区间对应的索引记录 条数的成本比直接全表扫描的成本都大了。
MySQL考虑到了这种情况，所以提供了一个系 统变量eq_range_index_dive_limit;
查看默认值：
show variables like '%dive%';
不同版本默认值不同，5.7以后默认200，如果我们的IN语句中的参数个数小于200个的话，
将使用index dive的方式计算 各个单点区间对应的记录条数，如果大于或等于200个的话，
可就不能使用index dive 了，要使用所谓的索引统计数据来进行估算。
MYSQL为每个表的每个索引都维护了一份统计数据，可以通过命令：
show index from 表名；
字段说明：
Table 索引所属表的名称。
Non_unique 索引列的值是否是非唯一的，聚簇索引和唯一二级索引的该列值为0，普通二级索引该列值为1。
Key_name 索引的名称。
Seq_in_index 索引列在索引中的位置，从1开始计数。比如对于联合索引 u_idx_day_status，来说，`insert_time`, `order_status`, `expire_time`对应的位 置分别是1、2、3。
Column_name 索引列的名称。
Collation 索引列中的值是按照何种排序方式存放的，值为A时代表升序存放，为 NULL时代表降序存放。
Cardinality 索引列中不重复值的数量，索引的离散情况
Sub_part 对于存储字符串或者字节串的列来说，有时候我们只想对这些串的前n 个字符或字节建立索引，这个属性表示的就是那个n值。如果对完整的列建立索引的话， 该属性的值就是NULL。
Packed 索引列如何被压缩，NULL值表示未被压缩。这个属性我们暂时不了解，可以先 忽略掉。 Null 该索引列是否允许存储NULL值。
Index_type 使用索引的类型，我们最常见的就是BTREE，其实也就是B+树索引。
Comment 索引列注释信息。
Index_comment 索引注释信息。
回到统计估算：
1、这里估算需要用到Cardinality 值，再通过获取表的总行数（SHOW TABLE STATUS 表名）rows得到：
该索引的平均重复度：r=rows ÷ Cardinality
2、再用索引的平均重复度乘以当前in（）的个数n，得到：
估计成本=r × n=(rows ÷ Cardinality)×n
诚然，这个成本计算还是比较简单的，但却是不精准的。
2、idx_expire_time：
AND expire_time> ‘2021-03-22 18:28:28’
AND expire_time<= ‘2021-03-22 18:35:09’
1 x 1.0 个，IO成本为1
回表成本:
1、idx_expire_time：
扫描行数39行：
需要回表成本：
CPU: 39 x 0.2 +0.01=7.81；
I/O: 39 x 1.0 =39；
2、idx_order_no：
扫描行数58行：
需要回表成本：
CPU: 58x 0.2 +0.01=11.61；
I/O: 58x 1.0 =58；
回表后CPU成本：
1、idx_expire_time：
39 x 0.2 +0.01=7.81；
2、idx_order_no：
58x 0.2 +0.01=11.61；
总成本：
1、idx_expire_time：
1+7.81+39+7.81=55.62
2、idx_order_no：
3+11.61+58+11.61=84.22
4. 对比各种方案，找出成本最低的那一个
全表扫描的成本：2230.1
使用idx_expire_time的成本：55.62
使用idx_order_no的成本：84.22
通过对比显然 使用idx_expire_time索引成本更低
所以查看最终执行计划：
EXPLAIN输出成本
1、查询执行计划，低版本5.6以前使用
explain extended sql
2、查询执行计划，低版本5.6以前使用
explain partitions sql
3、组合模式
explain sql;
show WARNINGS;
4、JSON化执行计划
explain format=json sql;
连接查询的成本
mysql的表关联常见有两种算法
Nested-Loop Join 算法
Block Nested-Loop Join 算法
嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。
从执行计划中可以看到这些信息：
1、驱动表是s1，被驱动表是 s2。先执行的就是驱动表(执行计划结果的id如果一样则按从上到下顺序执行sql)；优化器一般会优先选择小表做驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
2、当使用left join时，左表是驱动表，右表是被驱动表，当使用right join时，右表时驱动表，左表是被驱动表，当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表。
3、使用了 NLJ算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的 join 算法是 NLJ。
上面流程如下：
1. 从表s1中读取一行数据（如果s1表有查询过滤条件的，会从过滤结果里取出一行数据）；
2. 从第1步的数据中，取出关联字段id，到表s2中查找；
3. 取出表s2中满足条件的行，跟s1中获取到的结果合并，作为结果返回给客户端；
4. 重复上面 3 步。
整个过程会读取s1 表的所有数据(扫描1行)，然后遍历这每行数据中字段id 的值，
根据s1表中id 的值索引扫描s2表中的对应行(扫描1次s2表的索引，
1次扫描可以认为最终只扫描s2表一行完整数据，也就是总共 s2表也扫描了1行)。
因此整个过程扫描了 1+1×1=2行。
假如s1的行数是20，那么总扫描行数就是20+20×1=40行。并且每次被驱动表就可能有回表操作
所以如果驱动表S1行数愈多，成本就越高。
基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。
Extra 中 的Using join buffer (Block Nested Loop)说明该关联查询使用的是 BNL 算法。
先说结论：如果被驱动表的关联字段没索引，mysql会选择Block Nested-Loop Join 算法。
上面流程如下：
1. 把 s2 的所有数据放入到 join_buffer 中
2. 把表 s1 中每一行取出来，跟 join_buffer 中的数据做对比
3. 返回满足 join 条件的数据.
整个过程对表 s1 和 s2 都做了一次全表扫描，
因此扫描的总行数为1(表 s1 的数据总量) + 1(表 s2 的数据总量) = 2。
并且 join_buffer 里的数据是无序的，因此对表 s1 中的每一行，都要做 1(s2的总行数) 次判断，
所以内存中的判断次数是 1 * 1= 1次。
假设s1有100行，s2有10000行，那扫描总行数就是100+10000=10100；
而内存判断就是100*10000=100万次。
要是表 s2 是一个大表(几十万或更多)，join_buffer 放不下怎么办呢？
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。
如果放不下表 s2 的所有数据话，MYSQL策略很简单， 就是分段放。
比如 s2 表有1000行记录， join_buffer 一次只能放800行数据，
那么执行过程就是先往 join_buffer 里放800行记录，
然后从 t1 表里取数据跟 join_buffer 中数据对比得到部分结果，
然后清空 join_buffer ，再放入 s2 表剩余200行记录，再 次从 t1 表里取数据跟 join_buffer 中数据对比。
所以就多扫了一次 s1 表。
如果上面使用 Nested-Loop Join，那么内存判断就会变成I/O判断，也就是磁盘扫描。 很显然，用BNL磁盘扫描次数少很多，相比于磁盘扫描，BNL的内存计算会快得多。 因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有 索引的情况下 NLJ 算法比 BNL算法性能更高
关于连表的优化建议
1、关联字段加索引，让mysql做join操作时尽量选择NLJ算法
2、小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去 mysql优化器自己判断的时间（straight_join只适用于inner join，并不适用于left join，right join。）
关于小表驱动大表：
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
in和exsits优化
原则：小表驱动大表，即小的数据集驱动大的数据集。
in：当B表的数据集小于A表的数据集时，in优于exists
select * from A where id in (select id from B);
#等价于：
for(select id from B){
select * from A where A.id = B.id
}
exists：当A表的数据集小于B表的数据集时，exists优于in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
select * from A where exists (select 1 from B where B.id = A.id)
#等价于:
for(select * from A){
select * from B where B.id = A.id
}
#A表与B表的ID字段应建立索引
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会 忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
连表成本计算
回到成本计算，从上可知，MySQL中连接查询采用的是嵌套循环连接算法，驱动表会被访问一次，被驱动表可能会被访问多次，所以对于两表连接查询来说，它的查询成本由下边两个部分构成：
1、单次查询驱动表的成本
2、多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）
对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然 驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。 当查询优化器想计算整个连接查询所使用的成本时，就需要计算出驱动表的扇出值。而不同的情况扇出值计算的复杂度是有很大区别的。
CREATE TABLE `order1` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
`order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
`order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
`insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
`expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
`expire_time` datetime NOT NULL COMMENT '订单的过期时间',
`order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
KEY `idx_order_no` (`order_no`) USING BTREE,
KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
CREATE TABLE `order2` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单的主键',
`order_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的编号',
`order_note` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '订单的说明',
`insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '插入订单的时间',
`expire_duration` bigint NOT NULL COMMENT '订单的过期时长，单位秒',
`expire_time` datetime NOT NULL COMMENT '订单的过期时间',
`order_status` smallint NOT NULL DEFAULT '0' COMMENT '订单的状态，0：未支付；1：已支付；-1：已过期，关闭',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`) USING BTREE,
KEY `idx_order_no` (`order_no`) USING BTREE,
KEY `idx_expire_time` (`expire_time`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=DYNAMIC;
查询一： SELECT * FROM order1 AS s1 INNER JOIN order2 AS s2;
分析：这里都没有任何条件。所以会使用全表扫描，依据小表驱动大表，会选择表行数少的作为驱动表，所以扇出值就是优化器选择的全表最少的行数作为扇出值。
查询二： SELECT * FROM order1 AS s1 INNER JOIN order2 AS s2 WHERE s1.expire_time> ‘2021-11-22 00:00:00’ AND s1.expire_time<= ‘2021-12-22 00:00:00’;
分析：显然s1可以使用索引，如果s2的行数多的情况下，优化器会选择s1作为驱动表，而s1经过expire_time条件筛选后的结果行数就是扇出值。
查询三： SELECT * FROM order1 AS s1 INNER JOIN order2 AS s2 WHERE s1.order_note > ‘abc’;
分析：这个其实和查询一差不多，但多了个条件，s1.order_note > ‘abc’，而这个字段是非索引的，优化器就只能估算，然后s2全表做比较。
查询四：SELECT * FROM order1 AS s1 INNER JOIN order2 AS s2 WHERE s1.expire_time> ‘2021-11-22 00:00:00’ AND s1.expire_time<= ‘2021-12-22 00:00:00’ AND s1.order_note > ‘abc’;
分析：与查询二类似，多了个s1.order_note > 'abc’条件，这里优化器就需要结合二、三，在二的条件上再去做估算，然后与s2全表做比较。
查询五： SELECT * FROM order1 AS s1 INNER JOIN order2 AS s2 WHERE s1.expire_time> ‘2021-11-22 00:00:00’ AND s1.expire_time<= ‘2021-12-22 00:00:00’ AND s1.order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) AND s1.order_note > ‘abc’;
分析：查询四类似，不过在驱动表s1选取idx_expire_time索引执行查询后，优化器需要从符合二级索引范围区间的记录中估算有多少条记录符合下边两个条件：
order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’)
order_note > ‘abc’
也就是优化器需要通过在条件（s1.expire_time> ‘2021-11-22 00:00:00’ AND s1.expire_time<= ‘2021-12-22 00:00:00’）下再估算这些记录中有多少符合上述两个条件的。
总结：
1、如果使用的是全表扫描的方式执行的单表查询，那么计算驱动表扇出时需要估算满足搜 索条件的记录到底有多少条。
2、如果使用的是索引执行的单表扫描，那么计算驱动表扇出的时候需要估算满足除使用到对应索引的搜索条件外的其他搜索条件的记录有多少条。
MYSQL称之为condition filtering。
在MySQL 5.7之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的 话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围 条件的索引记录条数作为扇出值。
在MySQL 5.7中，MySQL引入了这个condition filtering的功能，就是还要估算剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。MySQL称之为启发式规则。
两表连接的成本分析
公式如下：
连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最 优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。 可是对于内连接来说，驱动表和被驱动表的位置是可以互换的。不同的表作为驱动表查询成本可能是不同的，所以优化器需要按不同的连表顺序来估算成本，然后选择成本最低的一种方式作为最终的执行计划。
多表连接查询成本
可以知道，不同的连表顺序，查询成本都是不一样的。
对于两表连接，比如表A和表B连接 只有 AB、BA这两种连接顺序。其实相当于2 × 1 = 2种连接顺序。 对于三表连接，比如表A、表B、表C进行连接 有ABC、ACB、BAC、BCA、CAB、CBA这么6种连接顺序。其实相当于3 × 2 × 1 = 6种连 接顺序。
对于四表连接的话，则会有4 × 3 × 2 × 1 = 24种连接顺序。
对于n表连接的话，则有 n × (n-1) × (n-2) × ··· × 1种连接顺序，就是n的阶乘种连接顺序，也就是n!。
当然MYSQL也不是无限制的按这种方式处理，不然多表时，光计算成本就耗费很大的时间，显然时不妥的。所以MySQL做了以下处理
1、提前结束某种顺序的成本评估
MySQL在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小 的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接 查询成本，那就压根儿不对该连接顺序继续往下分析了。比方说A、B、C三个表进行连 接，已经得到连接顺序ABC是当前的最小连接成本，比方说10.0，在计算连接顺序BCA 时，发现B和C的连接成本就已经大于10.0时，就不再继续往后分析BCA这个连接顺序的成 本了。
2、系统变量optimizer_search_depth
为了防止无穷无尽的分析各种连接顺序的成本，MySQL提出了optimizer_search_depth系 统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否 则只对与optimizer_search_depth值相同数量的表进行穷举分析。很显然，该值越大， 成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不 是很好的执行计划，但可以省掉很多分析连接成本的时间。
3、根据经验估算
即使有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很 长，所以MySQL干脆提出了一些所谓的启发式规则（就是根据以往经验指定的一些规 则），凡是不满足这些规则的连接顺序压根儿就不分析，这样可以极大的减少需要分析 的连接顺序的数量，但是也可能造成错失最优的执行计划。他们提供了一个系统变量 optimizer_prune_level来控制到底是不是用这些启发式规则
调节成本常数
前面单表计算时的成本参数
读取一个页面花费的成本默认是1.0
检测一条记录是否符合搜索条件的成本默认是0.2。
实除了这两个成本常数，MySQL还支持很多，它们被存储到了MySQL数据库的两个表，
通过：SHOW TABLES FROM mysql LIKE ‘%cost%’;
可以查到
其实一条语句的执行其实是分为两层的：server层、存储引擎层。
在server层进行连接管理、查询缓存、语法解析、查询优化等操作，在存储引擎层执行 具体的数据存取操作。也就是说一条语句在server层中执行的成本是和它操作的表使用 的存储引擎是没关系的，所以关于这些操作对应的成本常数就存储在了server_cost表 中，而依赖于存储引擎的一些操作对应的成本常数就存储在了engine_cost表中。
mysql.server_cost表
通过：SELECT * FROM mysql.server_cost; （本人版本MYSQL8.0）
cost_name 表示成本常数的名称。
cost_value 表示成本常数对应的值。如果该列的值为NULL的话，意味着对应的成本常数会采用默认 值。
last_update 表示最后更新记录的时间。
comment 备注
default_value 默认值
格成本常数说明：
disk_temptable_create_cost 默认值20.0 创建基于磁盘的临时表的成本， 如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
disk_temptable_row_cost 默认值0.5 向基于磁盘的临时表写入或读取一条记录 的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
key_compare_cost 默认0.05 两条记录做比较操作的成本，多用在排序操作上， 如果增大这个值的话会提升filesort的成本，让优化器可能更倾向于使用索引完成排序 而不是filesort。 memory_temptable_create_cost 默认值1.0 创建基于内存的临时表的成本， 如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
memory_temptable_row_cost 默认值0.1 向基于内存的临时表写入或读取 一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
row_evaluate_cost 默认值0.1 这个就是我们之前一直使用的检测一条记 录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全 表扫描。
mysql.engine_cost表
通过 （本人版本MYSQL8.0）
SELECT * FROM mysql.engine_cost;
与server_cost相比，engine_cost多了两个列：
engine_name列 指成本常数适用的存储引擎名称。如果该值为default，意味着对应的成本常数适用于所 有的存储引擎。 。
device_type列 指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘。
存储引擎成本常数有两个：
io_block_read_cost 默认值1.0 从磁盘上读取一个块对应的成本。请注意我使用的是块，而不是页这个词。对于InnoDB存储引擎来说，一个页就是一个块，不过 对于MyISAM存储引擎来说，默认是以4096字节作为一个块的。增大这个值会加重I/O成 本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。
memory_block_read_cost 默认值0.25 与上一个参数类似，只不过衡量的是从内 存中读取一个块对应的成本。
InnoDB的统计数据
前面说了，在计算成本时，MySQL会根据一些统计数据进行估算的。
统计数据分为非永久和永久性统计数据
这里简单举例看下永久性统计数据
通过：SHOW TABLES FROM mysql LIKE ‘innodb%’;
innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。 innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统 计项的统计数据。
通过：desc mysql.innodb_table_stats
database_name 数据库名
table_name 表名
last_update 本条记录最后更新时间
n_rows 表中记录的条数
clustered_index_size 表的聚簇索引占用的页面数量
sum_of_other_index_sizes 表的其他索引占用的页面数量
通过：desc mysql.innodb_index_stats;
database_name 数据库名
table_name 表名
index_name 索引名
last_update 本条记录最后更新时间
stat_name 统计项的名称
stat_value 对应的统计项的值
sample_size 为生成统计数据而采样的页面数量
stat_description 对应的统计项的描述 innodb_index_stats表的每条记录代表着一个索引的一个统计项。
看下数据
index_name列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看 出来，PRIMARY索引（也就是主键）占了3条记录，idx_expire_time索引占了6条记录。 针对index_name列相同的记录，stat_name表示针对该索引的统计项名称，
stat_value展 示的是该索引在该统计项上的值，
sample_size列就表明了采样的页面数量是多少
stat_description指的是来描述该统计项的含义的。
stat_name统计项补充：
1、n_leaf_pages：表示该索引的叶子节点占用多少页面。
2、size：表示该索引共占用多少页面，innodb_page。
3、n_diff_pfxNN：表示对应的索引列不重复的值有多少。NN可以被替换为01、02、03… 这样的数字.
例如：对于u_idx_day_status来说：
n_diff_pfx01表示的是统计insert_time这单单一个列不重复的值有多少。
n_diff_pfx02表示的是统计insert_time,order_status这两个列组合起来不重复的值有 多少。
n_diff_pfx03表示的是统计insert_time,order_status,expire_time这三个列组合起来 不重复的值有多少。
定期更新统计数据
随着我们不断的对表进行增删改操作，表中的数据也一直在变化，innodb_table_stats 和innodb_index_stats表里的统计数据也在变化。MySQL提供了如下两种更新统计数据的方式：
1、开启innodb_stats_auto_recalc。
系统变量innodb_stats_auto_recalc决定着服务器是否自动重新计算统计数据，它的默认值是ON，也就是该功能默认是开启的。
2、手动调用ANALYZE TABLE语句来更新统计信息
手动更新innodb_table_stats和innodb_index_stats表
其实innodb_table_stats和innodb_index_stats表就相当于一个普通的表一样，我们能 对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。 比如说我们想把order表关于行数的统计数据更改一下可以这么做：
步骤一：更新innodb_table_stats表。
步骤二：让MySQL查询优化器重新加载我们更改过的数据。
更新完innodb_table_stats只是单纯的修改了一个表的数据，需要让MySQL查询优化器重 新加载我们更改过的数据，运行下边的命令就可以了： FLUSH TABLE order;
一些sql脚本
##分析
##表结构统计信息查询
SHOW TABLE STATUS LIKE '表名';
##数据库版本号
SELECT version()
##in dive 最大值
show variables like '%dive%';
## 查询索引统计信息
show index from 表名;
##查询所有正在执行的sql进程
show full PROCESSLIST;
##explain sql 查询执行计划
explain extended sql 查询执行计划
explain partitions sql 查询执行计划
explain+show WARNINGS;
### optimizer trace成本分析
#开启会话支持
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
sql;
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
#关闭设置
SET optimizer_trace="enabled=off";
###成本常数查询
SELECT * FROM mysql.server_cost;
SELECT * FROM mysql.engine_cost;
show table status like 'tb_name';
SHOW VARIABLES LIKE 'innodb_stats_persistent';
SELECT * FROM mysql.innodb_table_stats;
desc mysql.innodb_index_stats;
SELECT * FROM mysql.innodb_index_stats
WHERE table_name = 'tb_name' and database_name='db_name';
以上为自己互联网学习后所作笔记，仅供学习讨论，如有错误，还请指出，谢谢！！！
2944
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
