19 优化器成本模型：优化器为什么选择这个执行计划？
你好，我是俊达。
上一讲中，我们通过四十多个SQL，演示了MySQL各种不同的执行计划。对于给定的一个SQL语句，MySQL为什么选择了某一个执行计划，而没有采用其他的执行计划呢？优化器会评估每一个可能的执行计划的成本，从中选择一个成本最低的作为最终的执行计划。这一讲中我们就来聊一聊执行计划的成本是怎么计算的。
SQL预处理
一个SQL，在进行具体的成本计算之前，会先进行一系列的预处理。首先需要对SQL文本进行词法分析和语法解析，生成语法树。然后基于关系代数的一些基本规则，对SQL语句进行转换和改写。
常见的改写有以下几种方式。
- 等值条件传播
如果A=B，并且B=C，则A=C。
下面这个SQL中，原始条件是t1.a = t2.a and t2.a = t3.a，转换后，得到t1.a = t2.a and t1.a = t3.a，观察执行计划中的ref列就可以看到这一点。
mysql> explain select * from tab t1, tab t2, tab t3
where t1.a = t2.a
and t2.a = t3.a;
+----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------+
| 1 | SIMPLE | t1 | ALL | idx_abc | NULL | NULL | NULL | 9913 | 100.00 | NULL |
| 1 | SIMPLE | t2 | ref | idx_abc | idx_abc | 4 | rep.t1.a | 3304 | 100.00 | NULL |
| 1 | SIMPLE | t3 | ref | idx_abc | idx_abc | 4 | rep.t1.a | 3304 | 100.00 | NULL |
+----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------+
- 常量传播
如果A=B，并且 B=1，那么可以得到A=1。下面这个例子中， 通过show warnings可以看到，WHERE条件被写成了t1.a = 1 and t2.b = 1。
mysql> explain select * from tab t1, tab t2 where t1.a = t2.b and t2.b = 1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 9913 | 10.00 | Using where |
| 1 | SIMPLE | t1 | ALL | idx_abc | NULL | NULL | NULL | 9913 | 33.62 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `rep`.`t1`.`id` AS `id`,`rep`.`t1`.`a` AS `a`,
`rep`.`t1`.`b` AS `b`,`rep`.`t1`.`c` AS `c`,`rep`.`t1`.`padding` AS `padding`,
`rep`.`t2`.`id` AS `id`,`rep`.`t2`.`a` AS `a`,`rep`.`t2`.`b` AS `b`,
`rep`.`t2`.`c` AS `c`,`rep`.`t2`.`padding` AS `padding`
from `rep`.`tab` `t1` join `rep`.`tab` `t2`
where ((`rep`.`t2`.`b` = 1)
and (`rep`.`t1`.`a` = 1))
- 移除重复或多余的条件
mysql> explain select * from tab where a = 5 and a between 1 and 10;
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tab | ref | idx_abc | idx_abc | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------+---------------+---------+---------+-------+------+----------+-------+
- 移除永远为真的条件
条件1=1永远成立，优化器会直接移除类似这样的条件。
mysql> explain select * from tab where 1=1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | tab | ALL | NULL | NULL | NULL | NULL | 9913 | 100.00 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
- 对于永远无法满足的条件，直接返回无数据
下面这个例子中，t1.a < 0 and t1.a > 0逻辑上就不成立，因此查询可以直接返回无数据。
mysql> explain select * from tab t1, tab t2
where t1.a = t2.a and t1.a < 0 and t1.a > 0;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
- 外连接改写为内连接
下面这个SQL中，由于t2.a > 0这个条件，left join被转换成了普通的join。
mysql> explain select * from tab t1 left join t2 on t1.a = t2.a where t2.a > 0;
+----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------------+
| 1 | SIMPLE | t1 | ALL | idx_abc | NULL | NULL | NULL | 9913 | 67.25 | Using where |
| 1 | SIMPLE | t2 | ref | idx_abc | idx_abc | 4 | rep.t1.a | 3304 | 100.00 | NULL |
+----+-------------+-------+------+---------------+---------+---------+----------+------+----------+-------------+
mysql> show warnings\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `rep`.`t1`.`id` AS `id`,`rep`.`t1`.`a` AS `a`,
`rep`.`t1`.`b` AS `b`,`rep`.`t1`.`c` AS `c`,`rep`.`t1`.`padding` AS `padding`,
`rep`.`t2`.`id` AS `id`,`rep`.`t2`.`a` AS `a`,`rep`.`t2`.`b` AS `b`,
`rep`.`t2`.`c` AS `c`,`rep`.`t2`.`padding` AS `padding`
from `rep`.`tab` `t1` join `rep`.`tab` `t2`
where ((`rep`.`t2`.`a` = `rep`.`t1`.`a`)
and (`rep`.`t1`.`a` > 0))
- 子查询转换为SEMIJOIN
下面这个例子中，子查询被转换成了普通的表连接。注意到ID为1的两个查询单元，select_type都是SIMPLE。
mysql> explain select * from tab where a in (select b from tab);
+----+--------------+-------------+--------+---------------------+---------------------+---------+-----------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------+-------------+--------+---------------------+---------------------+---------+-----------+------+----------+-------------+
| 1 | SIMPLE | tab | ALL | idx_abc | NULL | NULL | NULL | 9913 | 100.00 | NULL |
| 1 | SIMPLE | <subquery2> | eq_ref | <auto_distinct_key> | <auto_distinct_key> | 4 | rep.tab.a | 1 | 100.00 | NULL |
| 2 | MATERIALIZED | tab | index | NULL | idx_abc | 12 | NULL | 9913 | 100.00 | Using index |
+----+--------------+-------------+--------+---------------------+---------------------+---------+-----------+------+----------+-------------+
当然，满足一定条件的子查询才能转换为普通的表连接，而且为了SQL能查询得到一样的数据，转换为普通表连接后，还需要进行一些特殊的处理。后续我们会有一讲专门介绍子查询的转换。
基于成本的优化
MySQL使用了基于成本的优化器。我们通过一些具体的例子来说明成本的一些计算方式。
先创建一个测试表，初始化一些数据，这里的numbers就是上一讲开头创建的那个视图。
mysql> create table t_cost (
id int not null auto_increment,
a int not null,
b int not null,
c int not null,
d int not null,
padding varchar(7000) DEFAULT NULL,
primary key (id),
key idx_ac(a,c),
key idx_bc(b,c)
) engine=InnoDB;
mysql> insert into t_cost (a,b,c,d, padding)
select n%6, n%1000, n%100, n%100, rpad('', 2000, 'ABCDEFG XYZ')
from
numbers;
mysql> analyze table t_cost;
我们先来看这个SQL的执行计划。
mysql> explain
select *
from t_cost
where a=3
and b between 90 and 100
and c in (1,2,3,4,5,6,7,8,9,10)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_cost
partitions: NULL
type: range
possible_keys: idx_ac,idx_bc
key: idx_bc
key_len: 8
ref: NULL
rows: 110
filtered: 2.00
Extra: Using index condition; Using where
对于上面这个SQL，优化器最终从全表扫描、使用索引idx_ac、使用索引idx_bc这几种可能的路径中选择了idx_bc。优化器认为通过索引idx_bc需要访问110行数据。
我们可以使用优化器跟踪来查看优化器的思考过程。先设置会话变量optimizer_trace，再执行SQL或Explain命令，然后就可以从information_schema.optimizer_trace查看优化器的跟踪信息了。
mysql> set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t_cost
where a=3
and b between 90 and 100
and c in (1,2,3,4,5,6,7,8,9,10) ;
Empty set (0.01 sec)
mysql> select * from information_schema.optimizer_trace\G
*************************** 1. row ***************************
QUERY: select * from t_cost where a=3 and b between 90 and 100
and c in (1,2,3,4,5,6,7,8,9,10)
TRACE: {
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"IN_uses_bisection": true
},
{
......
optimizer_trace的输出内容比较长，我们来看一下其中比较关键的几个部分。
- table_scan
table_scan部分记录了全表扫描的成本，这里是扫描8580行记录，成本是1220.85。
{
"rows_estimation": [
{
"table": "`t_cost`",
"range_analysis": {
"table_scan": {
"rows": 8580,
"cost": 1220.85
}
- range_scan_alternatives
range_scan_alternatives中记录了索引扫描的成本，优化器分别计算了使用idx_ac和idx_bc这两个索引的访问成本。使用idx_ac时，需要扫描172行数据，成本是62.71。使用idx_bc时，需要扫描110行数据，成本是38.76。
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_ac",
"ranges": [
"a = 3 AND c = 1",
"a = 3 AND c = 2",
"a = 3 AND c = 3",
"a = 3 AND c = 4",
"a = 3 AND c = 5",
"a = 3 AND c = 6",
"a = 3 AND c = 7",
"a = 3 AND c = 8",
"a = 3 AND c = 9",
"a = 3 AND c = 10"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 172,
"cost": 62.71,
"chosen": true
},
{
"index": "idx_bc",
"ranges": [
"90 <= b <= 100"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 110,
"cost": 38.76,
"chosen": true
}
]
- best_access_path
best_access_path记录了优化器最终选择的执行计划，由于使用idx_bc成本最低，优化器最终选择了这个执行计划。
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_ac",
"chosen": false,
"cause": "range_uses_more_keyparts"
},
{
"rows_to_scan": 110,
"access_type": "range",
"range_details": {
"used_index": "idx_bc"
},
"resulting_rows": 110,
"cost": 49.76,
"chosen": true
}
]
}
优化器是怎么来评估这几种不同的执行路径的成本的呢？执行路径的成本主要由IO成本和CPU成本组成。
- IO成本
IO成本是从内存或磁盘中读写数据块的成本。如果查询涉及到物化操作，IO成本还包括创建临时表、读写临时表的成本。IO成本和需要访问的数据块的数量相关。
- CPU成本
CPU成本包括行评估成本（行评估成本可以理解为判断一行记录是否满足SQL表达式中的条件），KEY比较的成本（比如在进行排序操作时，需要比较记录排序字段的大小）。
具体怎么计算IO成本和CPU成本呢？
优化器成本模型
首先优化器有一个成本模型，设置了一些基本操作的成本，比如读取1个数据块的成本是多少、评估1行记录的成本是多少。我把优化器中的成本模型常量整理成了下面这两个表格，你可以参考一下。
- Server层常量
- 存储引擎层常量
上面这两个表格中常量的取值从MySQL 8.0.32的代码中获取。不同的版本可能会设置不同的默认值，如果你去看5.7版本的源码，会发现有些常量的值不一样。一般我们并不需要修改这些常量，也不需要知道这些常量的具体取值，只有当我们想知道某个成本值具体是如何计算得到的时候，才会用到这些常量。
你还可以通过修改mysql库中的server_cost和engine_cost这2个表来改变某个成本常量的值，当然一般不会去修改这些值。
mysql> select cost_name, cost_value, default_value from mysql.server_cost;
+------------------------------+------------+---------------+
| cost_name | cost_value | default_value |
+------------------------------+------------+---------------+
| disk_temptable_create_cost | NULL | 20 |
| disk_temptable_row_cost | NULL | 0.5 |
| key_compare_cost | NULL | 0.05 |
| memory_temptable_create_cost | NULL | 1 |
| memory_temptable_row_cost | NULL | 0.1 |
| row_evaluate_cost | NULL | 0.1 |
+------------------------------+------------+---------------+
6 rows in set (0.00 sec)
mysql> select engine_name, device_type, cost_name, cost_value, default_value
from mysql.engine_cost;
+-------------+-------------+------------------------+------------+---------------+
| engine_name | device_type | cost_name | cost_value | default_value |
+-------------+-------------+------------------------+------------+---------------+
| default | 0 | io_block_read_cost | NULL | 1 |
| default | 0 | memory_block_read_cost | NULL | 0.25 |
+-------------+-------------+------------------------+------------+---------------+
如果你更新了这两个表，要执行FLUSH OPTIMIZER_COSTS命令后才会真正生效。
成本计算
MySQL怎么评估某个执行访问路径需要读取多少个数据块、扫描多少行数据呢？这里分为几种情况。对于全表扫描，MySQL会通过统计信息来估算。对于索引访问，MySQL会使用Index Dive机制来评估行数，或者使用索引统计信息来评估。接下来我们分别介绍这几种情况。
全表扫描成本计算
全表扫描需要读取表中的所有数据页。全表扫描的成本会根据表的统计信息来评估。InnoDB表的统计信息存储在mysql.innodb_table_stats表中。在我们的测试表中，统计信息是这样的。
mysql> select * from mysql.innodb_table_stats where table_name = 't_cost'\G
*************************** 1. row ***************************
database_name: rep
table_name: t_cost
last_update: 2024-08-22 16:14:21
n_rows: 8580
clustered_index_size: 1443
sum_of_other_index_sizes: 46
这几个字段的含义我整理到表格中了，供你参考。
我们来回顾一下前面全表扫描的成本。
访问行数rows就是从统计信息中直接获取的。访问成本的计算公式，我使用下面这段python代码来表示。
ROW_EVALUATE_COST = 0.1
MEMORY_BLOCK_READ_COST = 0.25
IO_BLOCK_READ_COST = 1
def io_cost(pages, in_mem_pct):
in_mem_pages = pages * in_mem_pct
disk_pages = pages - in_mem_pages
cost = in_mem_pages * MEMORY_BLOCK_READ_COST + disk_pages * IO_BLOCK_READ_COST
return cost
def row_eval_cost(rows):
cost = rows * ROW_EVALUATE_COST
return cost
def scan_cost(pages, rows, in_mem_pct):
cost = io_cost(pages, in_mem_pct)
row_cost = row_eval_cost(rows)
return cost + row_cost + 1.1 + 1
函数scan_cost的参数中，pages是表统计信息中的clustered_index_size字段，rows是统计信息中的n_rows字段，in_mem_pct是表缓存在内存中的一个预估的比例，在我们这个例子中，缓存率是100。计算全表扫描的成本时，优化器还加上了一些固定的成本，就是公式中的 1.1 + 1。
将统计信息中的数据代入scan_cost函数，就得到了全表扫描的成本1220.85。
索引访问成本计算
我们先来回顾下索引访问的大致步骤。下面这张图描述了普通非覆盖索引的访问步骤。
- 根据索引条件，在索引中定位到索引条目。
- 根据索引条目中的主键信息，到聚簇索引中查找数据。
一个SQL中，可能需要访问多个索引范围，比如在where中使用了or或者in条件。
从上面的示意图中，可以看出索引访问的成本主要包括：
- 需要访问的索引条目数量，以及这些条目分布在多少个页面中。
- 索引的区间数量。
- 需要读取的InnoDB聚簇索引的页面数量。
我们回顾下测试案例中两个索引的访问成本。使用索引idx_ac需要访问10个区间，总共172行记录，成本是62.71。
{
"index": "idx_ac",
"ranges": [
"a = 3 AND c = 1",
"a = 3 AND c = 2",
"a = 3 AND c = 3",
"a = 3 AND c = 4",
"a = 3 AND c = 5",
"a = 3 AND c = 6",
"a = 3 AND c = 7",
"a = 3 AND c = 8",
"a = 3 AND c = 9",
"a = 3 AND c = 10"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 172,
"cost": 62.71,
"chosen": true
}
使用索引idx_bc需要访问1个区间，110行记录，成本是38.76。
{
"index": "idx_bc",
"ranges": [
"90 <= b <= 100"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 1,
"rows": 110,
"cost": 38.76,
"chosen": true
}
可以看到，索引访问时，需要扫描的记录数决定了访问的成本。那么优化器怎么知道，访问索引的某一个区间时，需要扫描多少行记录呢？InnoDB使用了index dive机制来评估记录数。简单地说，index dive就是根据区间的边界值，到索引中统计区间内的记录数，后续我们再对index dive的原理做进一步介绍，这里先记住一个结论： 使用index dive机制通常能得到记录数比较准确的一个估计。
得到索引区间内的记录数之后，（非覆盖索引）索引访问成本的计算公式，我使用下面这段python代码来表示。
MEMORY_BLOCK_READ_COST = 0.25
IO_BLOCK_READ_COST = 1
ROW_EVALUATE_COST = 0.1
def row_evaluate_cost(rows):
return rows * ROW_EVALUATE_COST
def page_read_cost(in_mem_pct):
return MEMORY_BLOCK_READ_COST * in_mem_pct + IO_BLOCK_READ_COST * (1-in_mem_pct)
def range_normal_io_cost(ranges, rows, in_mem_pct):
pages = ranges + rows
result = pages * page_read_cost(in_mem_pct)
return result
def range_normal_cost(ranges, rows, in_mem_pct):
io_cost = range_normal_io_cost(ranges, rows, in_mem_pct)
result = io_cost + row_evaluate_cost(rows) + 0.01
return result
索引访问成本由IO成本和行评估成本组成。而IO的次数是怎么评估的呢？ MySQL认为每一行记录需要访问1次IO，此外，每一个区间需要访问1次IO。
根据上面的公式，可以计算出我们例子中两个索引的访问成本。
索引idx_ac的访问成本：
索引index_bc的访问成本：
我们这里介绍了非覆盖索引range执行路径下的访问成本。实际上索引访问还存在其他情况，比如上一讲中讲到，有一种ref的访问路径，此外，还存在覆盖索引的情况，这些情况下，索引访问的成本计算方式会有一些差异，我们下一讲再具体分析。
索引统计信息的作用
使用index dive通常能得到索引区间内记录数比较准确的一个估计。但是有些情况下，并不能使用index dive机制。比如在表连接的时候，连接条件来自于驱动表，而优化器并不知道连接条件的具体取值，因此无法使用index dive。另外一种情况是，如果待查询的区间数量特别多，对每一个区间都做一次index dive的开销太大了。当区间的数量超过参数eq_range_index_dive_limit的设置时，优化器就会放弃index dive。
我们来测试一下这种情况。
mysql> explain select * from t_cost
where b in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15)
and c in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
+----+-------------+--------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t_cost | range | idx_bc | idx_bc | 8 | NULL | 1800 | 100.00 | Using index condition |
+----+-------------+--------+-------+---------------+--------+---------+------+------+----------+-----------------------+
eq_range_index_dive_limit默认为200，上面这个测试SQL中，总共有225个区间，因此优化器会放弃使用index dive。
从optimizer trace中，可以看到index_dives_for_eq_ranges为false，rows为1800，成本为686.26。
{
"index": "idx_bc",
"ranges": [
"b = 1 AND c = 1",
"b = 1 AND c = 2",
"b = 1 AND c = 3",
......
"b = 15 AND c = 14",
"b = 15 AND c = 15"
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"in_memory": 0,
"rows": 1800,
"cost": 686.26,
"chosen": true
}
根据前面的计算公式，可以得到执行计划的成本。
那么这里的记录数是怎么评估出来的呢？这需要用到索引统计信息。索引统计信息可以通过information_schema.statistics表查看，也可以通过show indexes命令查看。
mysql> show indexes from t_cost;
+------------+----------+--------------+-------------+-----------+-------------+
| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality |
+------------+----------+--------------+-------------+-----------+-------------+
| 0 | PRIMARY | 1 | id | A | 8580 |
| 1 | idx_ac | 1 | a | A | 6 |
| 1 | idx_ac | 2 | c | A | 300 |
| 1 | idx_bc | 1 | b | A | 1000 |
| 1 | idx_bc | 2 | c | A | 1000 |
+------------+----------+--------------+-------------+-----------+-------------+
索引统计信息字段含义我整理在了这个表格中。
索引统计信息中最重要字段是基数。对于组合索引，字段的基数是当前字段以及序号比当前字段小的那些字段的组合字段的唯一值。基数值越大，则通常说明索引的选择性越高。当然在实际情况中，可能会存在数据分布不均匀的情况，某些值的记录数会特别高或特别低。
我们的例子中，索引idx_bc的基数为1000，因此对于where b=xx and c=xx 这样的条件，匹配到的记录数是8580/1000，然后再取整，得到行数是8。我们的例子中有225个这样的区间，因此总记录数是225 * 8，也就是1800。你也可以在mysql.innodb_index_stats表中查看索引统计信息，有兴趣的可以自己去试一试。
统计信息
通过前面这几个简单的例子，你可以看到统计信息的重要作用了。准确的统计信息对于优化器是否能找到最优的执行计划起着关键的作用。如果统计信息不准确，则优化器可能会选择错误的执行计划。那么应该如何维护统计信息呢？
有几个参数控制了InnoDB收集统计信息的行为。
- innodb_stats_auto_recalc
该参数控制InnoDB是否开启统计信息自动收集。如果开启了统计信息自动收集，则当表中的数据变化超过10%时，InnoDB后台会自动重新计算表的相关统计信息。该参数默认开启。如果关闭统计信息自动收集，则需要执行analyze table来更新表的统计信息。
- innodb_stats_persistent
该参数控制是否持久化存储InnoDB统计信息，默认开启。如果没有开启统计信息持久化存储，则统计信息只存在内存中，则当MySQL重启后，所有InnoDB表的统计信息都缺失。查询语句访问到缺失统计信息的表时，会收集统计信息。
- innodb_stats_persistent_sample_pages
该参数指定收集统计信息时扫描的页面数。该参数设置得越大，则analyze table得到的统计信息越精确，但是收集统计信息的时间会变长，也会消耗更多的系统IO。
- innodb_stats_transient_sample_pages
innodb_stats_persistent设置为OFF时，使用该参数来控制收集统计信息时扫描的页面数。
- innodb_stats_on_metadata
该参数用于控制在查看表的元数据时（如执行SHOW TABLE STATUS命令、查看information_schema中的TABLES、STATISTICS表），是否更新统计信息。该参数只有当统计信息没有持久化时才生效，默认关闭。
上述参数在全局控制统计信息收集行为。InnoDB也支持在表级别指定统计信息的收集策略。可以在create table或alter table语句中指定STATS_AUTO_RECALC、STATS_PERSISTENT、STATS_SAMPLE_PAGES这几个选项：
alter table tab
STATS_AUTO_RECALC = {DEFAULT|0|1},
STATS_PERSISTENT = {DEFAULT|0|1},
STATS_SAMPLE_PAGES = n;
我们也可以使用ANALYZE TABLE命令来收集表的统计信息。
ANALYZE [NO_WRITE_TO_BINLOG] TABLE tbl_name;
ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
TABLE tbl_name
UPDATE HISTOGRAM ON col_name
[WITH N BUCKETS];
ANALYZE TABLE命令的执行时间跟参数innodb_stats_persistent_sample_pages设置的采样页面数、索引字段的数量以及表的分区数相关。
表连接的成本评估
对于多表连接，优化器需要评估不同的连接顺序下的成本。
比如对于上面这个简单的三表连接查询，就存在6种可能的连接顺序。优化器需要评估每一个可能的执行路径的成本，从其中选择成本最低的执行路径来执行查询。
表连接的成本评估，以及表连接的执行，我们后续有专门的一节课来介绍。
影响执行计划的其他因素
优化器最终会选择哪个执行计划，还受到其他一些因素的影响。
- 优化器参数
前面我们介绍了参数eq_range_index_dive_limit的作用。还有其他几个参数也会影响优化器。
参数range_optimizer_max_mem_size用来限制range优化能使用的内存。对于where a in (x,x,x) and b in (x,x,x) and c in (x,x,x)这样的条件，每一个组合都会消耗一定的内存，当组合的数量特别多时，如果内存消耗量超出了range_optimizer_max_mem_size的限制，优化器就会放弃这个range优化，改为使用全表扫描。
下面这个例子中，我们将range_optimizer_max_mem_size设置得小一些，就能看到这样的waning信息“Memory capacity of 16384 bytes for ‘range_optimizer_max_mem_size’ exceeded”。现实中，我也遇到过由于in的组合数太多导致的全表扫描。这种情况下，即使使用了FORCE INDEX提示，优化器还是会使用全表扫描。
mysql> set range_optimizer_max_mem_size=16384;
Query OK, 0 rows affected (0.01 sec)
mysql> explain select * from tab
where a in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
and b in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
and c in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tab | ALL | idx_abc | NULL | NULL | NULL | 9913 | 12.50 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> explain select * from tab force index(idx_abc)
where a in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
and b in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20)
and c in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20) ;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | tab | ALL | idx_abc | NULL | NULL | NULL | 9913 | 12.50 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> show warnings\G
*************************** 1. row ***************************
Level: Warning
Code: 3170
Message: Memory capacity of 16384 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
参数optimizer_switch提供了优化器很多功能的开关。后续的几讲中，我会对这里面的部分选项做一些介绍。
mysql> show variables like 'optimizer_switch'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on
参数optimizer_search_depth和optimizer_prune_level对多表接连查询有影响，我们在表连接那一讲中再来介绍。
- 优化器提示（hint）
优化器提示也能影响执行计划，上一讲中我们就使用了NO_SEMIJOIN、QB_NAME、BKA等提示来固定查询的执行计划。
平时比较常用的可能是FORCE INDEX、IGNORE INDEX、USE INDEX这些提示。FORCE INDEX中，可以指定多个索引。语句中加入force index提示后，优化器只会考虑使用这里面指定的索引。
下面的这个测试SQL使用了force index(idx_ac, idx_bc)，虽然ID是主键，但是可以看到，优化器没有考虑使用PRIMARY这个索引，而是使用了全表扫描。注意执行计划中，possible_keys为NULL。
mysql> explain select * from t_cost force index(idx_ac, idx_bc) where id = 1;
+----+-------------+--------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t_cost | ALL | NULL | NULL | NULL | NULL | 8580 | 0.01 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+----------+-------------+
FORCE INDEX还有另外一个效果，如果语句可以使用到提示中的某个索引，那么就不使用全表扫描。
mysql> explain select * from t_merge where a>0;
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | t_merge | ALL | idx_ad | NULL | NULL | 9737 | 49.99 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+----------+-------------+
mysql> explain select * from t_merge force index(idx_ad, idx_bd) where a > 0;
+----+-------------+---------+-------+---------------+--------+---------+------+----------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | rows | filtered | Extra |
+----+-------------+---------+-------+---------------+--------+---------+------+----------+-----------------------+
| 1 | SIMPLE | t_merge | range | idx_ad | idx_ad | 4 | 4868 | 100.00 | Using index condition |
+----+-------------+---------+-------+---------------+--------+---------+------+----------+-----------------------+
总结
这一讲中，我们学习了MySQL优化器的一些工作原理。优化器会选择成本最低的执行计划。我们介绍了全表扫描和索引范围扫描这两种最基本，也是最重要的访问路径的成本计算方法。
实际工作中优化SQL时，我们并不需要真的知道某个执行计划的成本，那是优化器要计算的。但是我们需要理解全表扫描和索引访问大致的原理，理解这两种执行计划的开销来自哪里。
统计信息对优化器很重要，如果统计信息不准确，优化器很可能会选择错误的执行计划。我们还可以通过优化器参数、SQL提示来影响优化器。一般情况下我建议尽量少用SQL提示，让优化器自己选择最优的执行计划。在一些比较复杂的场景下，如果优化器确实选不到最优的执行计划时，再考虑使用SQL提示。
思考题
有时我们需要分段处理全表的数据。如果表使用了单列主键，那么可以很方便地按主键范围来进行数据分片。但有些情况下，业务使用了联合主键，那么此时你应该怎么按主键范围来对数据进行分片呢？
create table t_business(
id1 varchar(30) not null,
id2 varchar(30) not null,
id3 varchar(30) not null,
col1 ...,
col2 ...,
primry key(id1, id2, id3)
) engine=innodb;
考虑上面这个表，使用了联合主键(id1, id2, id3)。你需要分片处理这个表的数据，每次处理1000行数据，要求不要重复处理数据，但也不要漏掉任何一条数据。整个表的数据量比较大，要尽可能保证性能。你会怎么来写这个分页的SQL呢？
期待你的思考，欢迎在留言区中与我交流。如果今天的课程让你有所收获，也欢迎转发给有需要的朋友。我们下节课再见！