共计 4519 个字符,预计需要花费 12 分钟才能阅读完成。
优化查问语句的性能是 MySQL 数据库治理中的一个重要方面。在优化查问性能时,抉择正确的索引对于缩小查问的响应工夫和进步零碎性能至关重要。然而,如何确定 MySQL 的索引抉择策略?MySQL 的优化器是如何抉择索引的?
在这篇《索引生效了?看看这几个常见的状况!》文章中,咱们介绍了索引区分度不高可能会导致索引生效,而这里的“不高”并没有具体量化,实际上 MySQL 会对执行打算进行老本估算,抉择老本最低的计划来执行。具体咱们还是通过一个案例来阐明。
案例
还是以人物表为例,咱们来看一下优化器是怎么抉择索引的。
建表语句如下:
CREATE TABLE `person` (`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(64) NOT NULL,
`score` int(11) NOT NULL,
`age` int(11) NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_name_score` (`name`,`score`) USING BTREE,
KEY `idx_age` (`age`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;
而后插入 10 万条数据:
create PROCEDURE `insert_person`()
begin
declare c_id integer default 3;
while c_id <= 100000 do
insert into person values(c_id, concat('name',c_id), c_id + 100, c_id + 10, date_sub(NOW(), interval c_id second));
-- 须要留神,因为应用的是 now(),所以对于后续的例子,应用文中的 SQL 你须要本人调整条件,否则可能看不到文中的成果
set c_id = c_id + 1;
end while;
end;
CALL insert_person();
能够看到,最早的 create_time
是 2023-04-14 13:03:44
。
咱们通过上面的 SQL 语句对 person 表进行查问:
explain select * from person where NAME>'name84059' and create_time>'2023-04-15 13:00:00'
通过执行打算,咱们能够看到 type=All,示意这是一次全表扫描。接着,咱们将 create_time 条件中的 13 点改为 15 点,再次执行查问:
explain select * from person where NAME>'name84059' and create_time>'2023-04-15 15:00:00'
这次执行打算显示 type=range,key=create_time,示意 MySQL 优化器抉择了 create_time 索引来执行这个查问,而不是应用 name_score 联结索引。
兴许你会对此感到奇怪,接下来,咱们一起来剖析一下背地的起因。
OPTIMIZER_TRACE 工具介绍
为了更好地了解 MySQL 优化器的工作原理,咱们能够应用一个弱小的调试工具:OPTIMIZER_TRACE。它是在 MySQL 5.6 及之后的版本中提供的,能够查看具体的查问执行打算,包含查问优化器的决策、抉择应用的索引、连贯程序和优化器估算的行数等信息。
当开启 OPTIMIZER_TRACE 时,MySQL 将会记录查问的执行打算,并生成一份具体的报告。这个报告能够提供给开发人员或数据库管理员进行剖析,以理解 MySQL 是如何决定执行查问的,进而进行性能优化。
在 MySQL 中,开启 OPTIMIZER_TRACE 须要在查问中应用特定的语句,如下所示:
SET optimizer_trace='enabled=on';
SELECT * FROM mytable WHERE id=1;
SET optimizer_trace='enabled=off';
当执行查问后,MySQL 将会生成一个 JSON 格局的执行打算报告。
须要留神的是,开启 OPTIMIZER_TRACE 会减少查问的执行工夫和资源耗费,因而只应该在须要调试和优化查问性能时应用。
官网文档在这里:https://dev.mysql.com/doc/dev/mysql-server/latest/PAGE_OPT_TR…
全表扫描的总成本
MySQL 在查问数据之前,首先会依据可能的执行计划生成执行打算,而后根据老本决定走哪个执行打算。这里的老本,包含 IO 老本和 CPU 老本:
- IO 老本,是从磁盘把数据加载到内存的老本。默认状况下,读取数据页的 IO 老本常数是 1(也就是读取 1 个页老本是 1)。
- CPU 老本,是检测数据是否满足条件和排序等 CPU 操作的老本。默认状况下,检测记录的老本是 0.2。
MySQL 保护了表的统计信息,能够应用上面的命令查看:
SHOW TABLE STATUS LIKE 'person'
该命令将返回包含表的行数、数据长度、索引大小等信息。这些信息能够帮忙 MySQL 优化器做出更好的决策,抉择更优的执行打算。咱们应用上述命令查看 person
表的统计信息。
图中总行数为 100064 行(因为 MySQL 的统计信息是一个估算,多出 64 行是失常的),CPU 老本是 100064 * 0.2 = 20012.8 左右。
数据长度是 5783552 字节。对于 InnoDB 存储引擎来说,5783552 就是聚簇索引占用的空间,等于聚簇索引的页数量 * 每个页面的大小。InnoDB 每个页面的大小是 16KB,因而咱们能够算出页的数量是 353,因而 IO 老本是 353 左右。
所以,全表扫描的总成本是 20365.8 左右。
追踪 MySQL 抉择索引的过程
select * from person where NAME>'name84059' and create_time>'2023-04-15 13:00:00'
下面这条语句可能执行的策略有:
- 应用 name_score 索引;
- 应用 create_time 索引;
- 全表扫描;
接着咱们开启 OPTIMIZER_TRACE 追踪:
SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
SET optimizer_trace_offset=-30, optimizer_trace_limit=30;
顺次执行上面的语句。
select * from person where NAME >'name84059';
select * from person where create_time>'2023-04-15 13:00:00';
select * from person;
而后查看追踪后果:
select * from information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
我从 OPTIMIZER_TRACE 的执行后果中,摘出了几个重要片段来重点剖析:
1、应用 name_score 对 name84059<name 条件进行索引扫描须要扫描 26420 行,老本是 31705。
30435 是查问二级索引的 IO 老本和 CPU 老本之和,再加上回表查问聚簇索引的 IO 老本和 CPU 老本之和。
{
"index": "idx_name_score",
"ranges": ["name84059 < name"] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 26420,
"cost": 31705,
"chosen": true
}
2、应用 create_time 进行索引扫描须要扫描 27566 行,老本是 33080。
{
"index": "idx_create_time",
"ranges": ["2023-04-15 13:00:00 < create_time"] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 27566,
"cost": 33080,
"chosen": true
}
3、全表扫描 100064 条记录的老本是 20366。
{
"considered_execution_plans": [
{"plan_prefix": [] /* plan_prefix */,
"table": "`person`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "scan",
"rows": 100064,
"cost": 20366,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 20366,
"rows_for_plan": 100064,
"chosen": true
}
] /* considered_execution_plans */
}
所以 MySQL 最终抉择了全表扫描形式作为执行打算。
把 SQL 中的 create_time 条件从 13:00 改为 15:00,再次剖析 OPTIMIZER_TRACE 能够看到:
{
"index": "idx_create_time",
"ranges": ["2023-04-15 15:00:00 < create_time"] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 6599,
"cost": 7919.8,
"chosen": true
}
因为是查问更晚工夫的数据,走 create_time 索引须要扫描的行数从 33080 缩小到了 7919.8。这次走这个索引的老本 7919.8 小于全表扫描的 20366,更小于走 name_score 索引的 31705。
所以这次执行打算抉择的是走 create_time 索引。
人工干预
优化器有时会因为统计信息的不精确或老本估算的问题,理论开销会和 MySQL 统计进去的差距较大,导致 MySQL 抉择谬误的索引或是间接抉择走全表扫描,这个时候就须要人工干预,应用强制索引了。
比方,像这样强制走 name_score 索引:
explain select * from person FORCE INDEX(name_score) where NAME >'name84059' and create_time>'2023-04-15 13:00:00'