MySQL查询优化与成本计算-优快云博客

本文链接：https://blog.youkuaiyun.com/Markland_l/article/details/125587846

Mysql的查询成本计算

再深入查询优化

EXPLAIN输出成本

explain format=json SELECT * FROM order_exp 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\G
- “cost_info”: {
  
  “query_cost”: “55.61” # 整个查询的执行成本预计为 55.61
  
  }
- “cost_info”: {
  
  “read_cost”: “55.60”,
  
  “eval_cost”: “0.01”,
  
  “prefix_cost”: “55.61”,
  
  #单独查询表的成本，也就是：read_cost + eval_cost
  
  “data_read_per_join”: “24” #和连接查询相关的数据量，单位字节，这里无用
  
  },

Optimizer Trace

SHOW VARIABLES LIKE ‘optimizer_trace’;
如果想打开这个功能，必须首先把 enabled 的值改为 on，就像这样：

SET optimizer_trace=“enabled=on”;

优化过程

执行sql语句，SELECT * FROM order_exp WHERE order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) AND expire_time> ‘2021-03-22 18:28:28’ AND insert_time> ‘2021-03-22 18:35:09’ AND order_note LIKE ‘%7排1%’;
在 information_schema 数据库下的 OPTIMIZER_TRACE 表中查看完

整的优化过程，SELECT * FROM information_schema.OPTIMIZER_TRACE\G
- “join_preparation”: { # prepare 阶段
- “join_optimization”: { # optimize 阶段
  - “transformation”: “equality_propagation”, # 等值传递转换
  - “transformation”: “constant_propagation”, # 常量传递转换
  - “transformation”: “trivial_condition_removal”, # 去除没用的条件
  - “substitute_generated_columns”: { # 替换虚拟生成列
  - “rows_estimation”: [ # 预估不同单表访问方法的访问成本
    - “table_scan”: { # 全表扫描的行数以及成本
    - “potential_range_indexes”: [ # 分析可能使用的索引
    - “analyzing_range_alternatives”: { # 分析各种可能使用的索引的成本
      - {
        
        “index”: “u_idx_day_status”, # 使用 u_idx_day_status 的成本分析
        
        “ranges”: [ # 使用 u_idx_day_status 的范围区间
        
        “0x99a92d28c9 < insert_time”
        
        ],
        
        “index_dives_for_eq_ranges”: true, # 是否使用 index dive
        
        “rowid_ordered”: false, # 使用该索引获取的记录是否按照主键排序
        
        “using_mrr”: false, # 是否使用 mrr
        
        “index_only”: false, # 是否是索引覆盖访问
        
        “rows”: 5172, # 使用该索引获取的记录条数
        
        “cost”: 6207.4, # 使用该索引的成本
        
        “chosen”: false, # 是否选择该索引
        
        “cause”: “cost” # 因为成本太大所以不选择该索引
        
        },
    - “analyzing_roworder_intersect”: { # 分析使用索引合并的成本
      
      “usable”: false,
      
      “cause”: “too_few_roworder_scans”
      
      }
    - “chosen_range_access_summary”: { # 对于上述单表查询 s1 最优的访问方法-
  - “considered_execution_plans”: # 分析各种可能的执行计划,负责对比各可行计划的开销，并选择相对最优的执行计划。
    
    #（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取
  - “attaching_conditions_to_tables”:# 尝试给查询添加一些其他的查询条件,增加主要是为了便于 ICP（索引条件下推）
  - “refine_plan”: # 再稍稍的改进一下执行计划
- “join_execution”: { # execute 阶段

总结

我们所说的基于成本的优化主要集中在 optimize 阶段，对于单表查询来说，我们主要关注 optimize 阶段的"rows_estimation"这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；
对于多表连接查询来说，我们更多需要关注"considered_execution_plans"这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用 EXPLAIN 语句所展现出的那种方案。
如果对使用 EXPLAIN 语句展示出的对某个查询的执行计划很不理解，就可以尝试使用 optimizer trace 功能来详细了解每一种执行方案对应的成本。

连接查询的成本

连接查询的成本 = 单次查询驱动表的成本 +
多次查询被驱动表的成本（具体查询多少次取决于对驱动表查询的结果集中有多少条记录）

扇出（fanout）

对驱动表进行查询后得到的记录条数称之为驱动表的扇出（英文名：fanout）。很显然驱动表的扇出值越小，对被驱动表的查询次数也就越少，连接查询的总成本也就越低。

condition filtering

预估扇出数
启发式规则
MySQL 把这个猜的过程称之为 condition filtering。当然，这个过程可能会使用到索引，也可能使用到统计数据，也可能就是 MySQL 单纯的瞎猜，整个评估过程非常复杂，所以我们不去细讲。
在 MySQL 5.7 之前的版本中，查询优化器在计算驱动表扇出时，如果是使用全表扫描的话，就直接使用表中记录的数量作为扇出值，如果使用索引的话，就直接使用满足范围条件的索引记录条数作为扇出值。
在 MySQL 5.7 中，MySQL 引入了这个 condition filtering 的功能，就是还要猜一猜剩余的那些搜索条件能把驱动表中的记录再过滤多少条，其实本质上就是为了让成本估算更精确。我们所说的纯粹瞎猜其实是很不严谨的，MySQL 称之为启发式规则。

实例分析

SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2;
- 不管是哪个表做驱动表，都是全表扫描
- 假设使用 s1 表作为驱动表，很显然对驱动表的单表查询只能使用全表扫描的方式执行，驱动表的扇出值也很明确，那就是驱动表中有多少记录，扇出值就是多少。统计数据中s1表的记录行数是10573，也就是说优化器就直接会把10573 当作在 s1 表的扇出值。
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2
WHERE s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’;
- 仍然假设 s1 表是驱动表的话，很显然对驱动表的单表查询可以使用 idx_expire_time 索引执行查询。此时范围区间( ‘2021-03-22 18:28:28’, ‘2021-03-22 18:35:09’)中有多少条记录，那么扇出值就是多少。
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.order_note > ‘xyz’;
- 本查询和查询一类似，只不过对于驱动表 s1 多了一个 order_note > 'xyz’的搜索条件。查询优化器又不会真正的去执行查询，所以它只能猜这 10573 记录里有多少条记录满足 order_note > 'xyz’条件。
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’ AND s1.order_note > ‘xyz’;
- 本查询和查询二类似，只不过对于驱动表 s1 也多了一个 order_note > ‘xyz’ 的搜索条件。不过因为本查询可以使用 idx_expire_time 索引，所以只需要从符合二级索引范围区间的记录中猜有多少条记录符合 order_note > 'xyz’条件，也就是只需要猜在 39 条记录中有多少符合 order_note > 'xyz’条件。
SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 WHERE s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’ AND s1.order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) AND s1.order_note > ‘xyz’;
- 本查询和查询四类似，不过在驱动表 s1 选取 idx_expire_time 索引执行查询后，优化器需要从符合二级索引范围区间的记录中猜有多少条记录符合下边两个条件：order_no IN (‘DD00_6S’, ‘DD00_9S’, ‘DD00_10S’) 和order_note > ‘xyz’，也就是优化器需要猜在 39 条记录中有多少符合上述两个条件的。

两表连接的成本分析

连接查询总成本 = 单次访问驱动表的成本 + 驱动表扇出数 x 单次访问被驱动表的成本
对于左（外）连接和右（外）连接查询来说，它们的驱动表是固定的，所以想要得到最优的查询方案只需要分别为驱动表和被驱动表选择成本最低的访问方法。
可是对于内连接来说，驱动表和被驱动表的位置是可以互换的，所以需要考虑两个方面的问题：

不同的表作为驱动表最终的查询成本可能是不同的，也就是需要考虑最优的表连接顺序。然后分别为驱动表和被驱动表选择成本最低的访问方法。

实例

SELECT * FROM s1 INNER JOIN s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’ AND s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’;

使用s1作为驱动表的情况

实际对s2表的单表访问如下
- SELECT * FROM s2 WHERE s2.order_no = xxx and s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’;
expire_time有索引，order_no没有索引
所以此时使用 s1 作为驱动表时的总成本就是（暂时不考虑使用 join buffer 对成本的影响）：

使用 idx_expire_time 访问 s1 的成本 + s1 的扇出 × 使用 idx_expire_time 访问 s2 的成本

使用s2作为驱动表的情况

实际对s1表的单表访问如下
- SELECT * FROM s1WHERE s1.order_no = xxx and s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’
order_no和expire_time都有索引
- 这里有个问题，因为 idx_expire_time 的范围区间是确定的，怎么计算使用 idx_expire_time 的成本我们上边已经说过了，可是在没有真正执行查询前， s1.order_no = 常数中的常数值我们是不知道的，怎么衡量使用 idx_order_no 执行查询的成本呢？其实很简单，直接使用我们前面说过的索引统计数据就好了（就是索引列平均一个值重复多少次）。一般情况下，ref 的访问方式要比 range 成本更低，这里假设使用 idx_order_no 进行对 s1 的访问。
- 所以此时使用 s2 作为驱动表时的总成本就是：使用 idx_expire_time 访问 s2 的成本 + s2 的扇出 × 使用 idx_order_no 访问 s1 的成本
最后优化器会比较这两种方式的最优访问成本，选取那个成本更低的连接顺序去真正的执行查询。从上边的计算过程也可以看出来，一般来讲，连接查询成本占大头的其实是驱动表扇出数 x 单次访问被驱动表的成本，所以我们的优化重点其实是下边这两个部分：
- 尽量减少驱动表的扇出
- 对被驱动表的访问成本尽量低

EXPLAIN 输出连接成本

explain format=json SELECT * FROM order_exp AS s1 INNER JOIN order_exp2 AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’ AND s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’\G
- “query_block”: {
  
  “select_id”: 1,# 整个查询语句只有 1 个 SELECT 关键字，该关键字对应的 id 号为 1
  
  “cost_info”: {
  
  “query_cost”: “840.51” # 整个查询的执行成本
  
  }
- “nested_loop”: [
  
  # 几个表之间采用嵌套循环连接（Nested-Loop Join）算法执行
  
  {
  
  “table”: {
  
  “table_name”: “s2”, s2 表是驱动表
  
  “access_type”: “range”, # 访问方法为 range
  
  “possible_keys”: [
  
  “idx_expire_time”
  
  ],
  
  “key”: “idx_expire_time”,
  
  “used_key_parts”: [
  
  “expire_time”
  
  ],
  
  “key_length”: “5”,
  
  “rows_examined_per_scan”: 321, # 查询 s2 表大致需要扫描 321 条记录
  
  “rows_produced_per_join”: 321, # 驱动表 s2 的扇出是 321
  
  “filtered”: “100.00”, 过滤率，最后到底有多少条需要到s1表中去查询，如果这里是50，就只需160条到s1中去查询，就是真实的扇出数，也就是condition filtering
  
  # condition filtering 代表的百分比
  
  “index_condition”: "((mysqladv.s2.expire_time > '2021-03-22
  
  18:35:09’) and (mysqladv.s2.expire_time <= ‘2021-03-22 18:35:59’))",
  
  “cost_info”: {
  
  “read_cost”: “386.21”,
  
  “eval_cost”: “64.20”,
  
  “prefix_cost”: “450.41”, # 查询 s2 表总共的成本，read_cost + eval_cost
  
  “data_read_per_join”: “152K” # 读取的数据量
  
  },
  
  “used_columns”: [
  
  “id”,
  
  “order_no”,
  
  “order_note”,
  
  “insert_time”,
  
  “expire_duration”,
  
  “expire_time”,
  
  “order_status”
  
  ]
  
  }
  
  },
- {
  
  “table”: {
  
  “table_name”: “s1”, s1 表是被驱动表
  
  “access_type”: “ref”,
  
  “possible_keys”: [
  
  “idx_order_no”,
  
  “idx_expire_time”
  
  ],
  
  “key”: “idx_order_no”,
  
  “used_key_parts”: [
  
  “order_no”
  
  ],
  
  “key_length”: “152”,
  
  “ref”: [
  
  “mysqladv.s2.order_note”
  
  ],
  
  “rows_examined_per_scan”: 1, # 查询一次 s1 表大致需要扫描 1 条记录
  
  “rows_produced_per_join”: 16, # 被驱动表 s2 的扇出是 16（由于没有多余的
  
  表进行连接，所以这个值无用）
  
  “filtered”: “4.94”, # condition filtering 代表的百分比
  
  “index_condition”: “(mysqladv.s1.order_no = mysqladv.s2.order_note)”,
  
  “cost_info”: {
  
  “read_cost”: “325.08”,
  
  “eval_cost”: “3.21”,
  
  “prefix_cost”: “840.51”, # 单次查询 s2、多次查询 s1 表总共的成本
  
  “data_read_per_join”: “7K”
  
  },
  
  “used_columns”: [
  
  “id”,
  
  “order_no”,
  
  “order_note”,
  
  “insert_time”,
  
  “expire_duration”,
  
  “expire_time”,
  
  “order_status”
  
  ],
  
  “attached_condition”: "((mysqladv.s1.expire_time >
  
  ‘2021-03-22 18:28:28’) and (mysqladv.s1.expire_time <= '2021-03-22
  
  18:35:09’))"
  
  }
s2 表的"cost_info"中 prefix_cost 就是单独查询 s2 表的成本。对于 s1 表的"cost_info"中，由于 s1 表是被驱动表，所以可能被读取多次，这里的 read_cost 和 eval_cost 是访问多次 s2 表后累加起来的值，而 s1 表中的 prefix_cost 的值代表的是整个连接查询预计的成本。
驱动表 S2 的查询成本为 450.41，总查询成本为 840.51，也就是说对被驱动表 S1 的查询成本也就是 390 左右，看起来用 S1 做驱动表好像更省一点。真的这样吗？我们把 SQL 语句改造一下，将 INNER JOIN 替换为 STRAIGHT_JOIN，强制让s1做驱动表
- explain format=json SELECT * FROM order_exp AS s1 STRAIGHT_JOIN order_exp2 AS s2 ON s1.order_no= s2.order_note WHERE s1.expire_time> ‘2021-03-22 18:28:28’ AND s1.expire_time<= ‘2021-03-22 18:35:09’ AND s2.expire_time> ‘2021-03-22 18:35:09’ AND s2.expire_time<= ‘2021-03-22 18:35:59’\G
- 发现总成本达到1万多

多表连接的成本分析

对于两表连接，比如表 A 和表 B 连接，只有 AB、BA 这两种连接顺序。其实相当于 2 × 1 = 2 种连接顺序。
对于三表连接，比如表 A、表 B、表 C 进行连接有 ABC、ACB、BAC、BCA、CAB、CBA 这么 6 种连接顺序。其实相当于 3 × 2 × 1 = 6 种连接顺序。
对于 n 表连接的话，则有 n × (n-1) × (n-2) × ··· × 1 种连接顺序，就是 n 的阶乘种连接顺序，也就是 n!。

提前结束某种顺序的成本评估

MySQL 在计算各种链接顺序的成本之前，会维护一个全局的变量，这个变量表示当前最小的连接查询成本。如果在分析某个连接顺序的成本时，该成本已经超过当前最小的连接查询成本，那就压根儿不对该连接顺序继续往下分析了。比方说 A、B、C 三个表进行连接，已经得到连接顺序 ABC 是当前的最小连接成本，比方说 10.0，在计算连接顺序 BCA 时，发现 B 和 C 的连接成本就已经大于 10.0 时，就不再继续往后分析 BCA 这个连接顺序的成本了。

系统变量 optimizer_search_depth

为了防止无穷无尽的分析各种连接顺序的成本，MySQL 提出了 optimizer_search_depth 系统变量，如果连接表的个数小于该值，那么就继续穷举分析每一种连接顺序的成本，否则只对与 optimizer_search_depth 值相同数量的表进行穷举分析。很显然，该值越大，成本分析的越精确，越容易得到好的执行计划，但是消耗的时间也就越长，否则得到不是很好的执行计划，但可以省掉很多分析连接成本的时间。
缺省值是62

根据某些规则压根儿就不考虑某些连接顺序

即使是有上边两条规则的限制，但是分析多个表不同连接顺序成本花费的时间还是会很长，所以 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

我们先看一下 server_cost 各个列都分别是什么意思：
- cost_name 表示成本常数的名称。
- cost_value 表示成本常数对应的值。如果该列的值为 NULL 的话，意味着对应的成本常数会采用默认值。
- last_update 表示最后更新记录的时间。
- comment 注释。
从 server_cost 中的内容可以看出来，目前在 server 层的一些操作对应的成本常数有以下几种：
- disk_temptable_create_cost 默认值 40.0 创建基于磁盘的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
- disk_temptable_row_cost 默认值 1.0 向基于磁盘的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于磁盘的临时表。
- key_compare_cost 0.1 两条记录做比较操作的成本，多用在排序操作上，如果增大这个值的话会提升 filesort 的成本，让优化器可能更倾向于使用索引完成排序而不是 filesort。
- memory_temptable_create_cost 默认值 2.0 创建基于内存的临时表的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
- memory_temptable_row_cost 默认值 0.2 向基于内存的临时表写入或读取一条记录的成本，如果增大这个值的话会让优化器尽量少的创建基于内存的临时表。
- row_evaluate_cost 默认值 0.2 这个就是我们之前一直使用的检测一条记录是否符合搜索条件的成本，增大这个值可能让优化器更倾向于使用索引而不是直接全表扫描。
这些成本常数在 server_cost 中的初始值都是 NULL，意味着优化器会使用它们的默认值来计算某个操作的成本，如果我们想修改某个成本常数的值的话，需要做两个步骤：
- 对我们感兴趣的成本常数做 update 更新操作
- 然后使用下边语句即可： FLUSH OPTIMIZER_COSTS;
在你修改完某个成本常数后想把它们再改回默认值的话，可以直接把 cost_value 的值设置为 NULL，再使用 FLUSH OPTIMIZER_COSTS 语句让系统重新加载。

mysql.engine_cost

engine_cost 表表中在存储引擎层进行的一些操作对应的成本常数，具体内

容如下：
- SELECT * FROM mysql.engine_cost;
与 server_cost 相比，engine_cost 多了两个列：
- engine_name 列指成本常数适用的存储引擎名称。如果该值为 default，意味着对应的成本常数适用于所有的存储引擎。
- device_type 列指存储引擎使用的设备类型，这主要是为了区分常规的机械硬盘和固态硬盘，不过在 MySQL 5.7.X 这个版本中并没有对机械硬盘的成本和固态硬盘的成本作区分，所以该值默认是 0。
我们从 engine_cost 表中的内容可以看出来，目前支持的存储引擎成本常数只有两个：
- io_block_read_cost 默认值 1.0 从磁盘上读取一个块对应的成本。请注意我使用的是块，而不是页这个词。对于 InnoDB 存储引擎来说，一个页就是一个块，不过对于 MyISAM 存储引擎来说，默认是以 4096 字节作为一个块的。增大这个值会加重 I/O 成本，可能让优化器更倾向于选择使用索引执行查询而不是执行全表扫描。
- memory_block_read_cost 默认值 1.0 与上一个参数类似，只不过衡量的是从内存中读取一个块对应的成本。

InnoDB中的统计数据

统计数据存储方式

非永久性的统计数据

非永久性的统计数据，这种统计数据存储在内存中，当服务器关闭时这些这些统计数据就都被清除掉了，等到服务器重启之后，在某些适当的场景下才会重新收集这些统计数据。

永久性的统计数据

永久性的统计数据，这种统计数据存储在磁盘上，也就是服务器重启之后这些统计数据还在。

命令

MySQL 给我们提供了系统变量 innodb_stats_persistent 来控制到底采用哪种方式去存储统计数据。在 MySQL 5.6.6 之前，innodb_stats_persistent 的值默认是 OFF，也就是说 InnoDB 的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent 的值默认是 ON，也就是统计数据默认被存储到磁盘中。
- SHOW VARIABLES LIKE ‘innodb_stats_persistent’;

基于磁盘的永久性统计数据

当我们选择把某个表以及该表索引的统计数据存放到磁盘上时，实际上是把这些统计数据存储到了两个表里：
- SHOW TABLES FROM mysql LIKE ‘innodb%’;
- innodb_table_stats 存储了关于表的统计数据，每一条记录对应着一个表的统计数据。
- innodb_index_stats 存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据

innodb_table_stats

直接看一下这个 innodb_table_stats 表中的各个列都是干嘛的：
- database_name 数据库名
- table_name表名
- last_update 本条记录最后更新时间
- n_rows 表中记录的条数
- clustered_index_size 表的聚簇索引占用的页面数量
- sum_of_other_index_sizes 表的其他索引占用的页面数量
我们直接看一下这个表里的内容： SELECT * FROM mysql.innodb_table_stats;
- 几个重要统计信息项的值如下：
- n_rows 的值是 10350，表明 order_exp 表中大约有 10350 条记录，注意这个数据是估计值。
- clustered_index_size 的值是 97，表明 order_exp 表的聚簇索引占用 97 个页面，这个值是也是一个估计值。
- sum_of_other_index_sizes 的值是 81，表明 order_exp 表的其他索引一共占用81 个页面，这个值是也是一个估计值。
n_rows 统计项的收集
- 按照一定算法（并不是纯粹随机的）选取几个叶子节点页面，计算每个页面中主键值记录数量，然后计算平均一个页面中主键值的记录数量乘以全部叶子节点的数量就算是该表的 n_rows 值。
- 可以看出来这个 n_rows 值精确与否取决于统计时采样的页面数量，MySQL 用名为 innodb_stats_persistent_sample_pages 的系统变量来控制使用永久性的统计数据时，计算统计数据时采样的页面数量。该值设置的越大，统计出的 n_rows 值越精确，但是统计耗时也就最久；该值设置的越小，统计出的 n_rows 值越不精确，但是统计耗时特别少。所以在实际使用是需要我们去权衡利弊，该系统变量的默认值是 20。

innodb_index_stats

直接看一下这个 innodb_index_stats 表中的各个列都是干嘛的：desc mysql.innodb_index_stats;
- database_name 数据库名
- table_name表名
- index_name 索引名
- last_update 本条记录最后更新时间
- stat_name 统计项的名称
- stat_value 对应的统计项的值
- sample_size为生成统计数据而采样的页面数量
- stat_description对应的统计项的描述
innodb_index_stats 表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于 order_exp 表的索引统计数据都有些什么：
- SELECT * FROM mysql.innodb_index_stats WHERE table_name =‘order_exp’;
- 先查看 index_name 列，这个列说明该记录是哪个索引的统计信息，从结果中我们可以看出来，PRIMARY 索引（也就是主键）占了 3 条记录，idx_expire_time 索引占了 6 条记录。
- 针对 index_name 列相同的记录，stat_name 表示针对该索引的统计项名称，stat_value 展示的是该索引在该统计项上的值，stat_description 指的是来描述该统计项的含义的。我们来具体看一下一个索引都有哪些统计项：
  - n_leaf_pages：表示该索引的叶子节点占用多少页面。
  - size：表示该索引共占用多少页面。
  - n_diff_pfxNN：表示对应的索引列不重复的值有多少。其中的 NN 长得有点儿怪呀，啥意思呢？
    - 其实 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 这三个列组合起来不重复的值有多少。
      
      n_diff_pfx04 表示的是统计 key_pare1、key_pare2、expire_time、id 这四个列组合起来不重复的值有多少。
    - 对于普通的二级索引，并不能保证它的索引列值是唯一的，比如对于 idx_order_no 来说，key1 列就可能有很多值重复的记录。此时只有在索引列上加上主键值才可以区分两条索引列值都一样的二级索引记录。
  - 在计算某些索引列中包含多少不重复值时，需要对一些叶子节点页面进行采
    
    样，sample_size 列就表明了采样的页面数量是多少。

定期更新统计数据

系统变量 innodb_stats_auto_recalc 决定着服务器是否自动重新计算统计数据，它的默认值是 ON，也就是该功能默认是开启的。每个表都维护了一个变量，该变量记录着对该表进行增删改的记录条数，如果发生变动的记录数量超过了表大小的 10%，并且自动重新计算统计数据的功能是打开的，那么服务器会重新进行一次统计数据的计算，并且更新 innodb_table_stats 和 innodb_index_stats 表。不过自动重新计算统计数据的过程是异步发生的，也就是即使表中变动的记录数

手动调用 ANALYZE TABLE 语句来更新统计信息

如果 innodb_stats_auto_recalc 系统变量的值为 OFF 的话，我们也可以手动调用 ANALYZE TABLE 语句来重新计算统计数据，比如我们可以这样更新关于order_exp 表的统计数据：
- ANALYZE TABLE order_exp;
ANALYZE TABLE 语句会立即重新计算统计数据，也就是这个过程是同步的，在表中索引多或者采样页面特别多时这个过程可能会特别慢最好在业务不是很繁忙的时候再运行。

手动更新innodb_table_stats 和innodb_index_stats

其实 innodb_table_stats 和 innodb_index_stats 表就相当于一个普通的表一样，我们能对它们做增删改查操作。这也就意味着我们可以手动更新某个表或者索引的统计数据。比如说我们想把 order_exp 表关于行数的统计数据更改一下可以这么做：
- 步骤一：更新 innodb_table_stats 表。
- 步骤二：让 MySQL 查询优化器重新加载我们更改过的数据。更新完 innodb_table_stats 只是单纯的修改了一个表的数据，需要让 MySQL查询优化器重新加载我们更改过的数据，运行下边的命令就可以了： FLUSH TABLE order_exp;