一. 前言
本文将会对 explain的每个参数进行更细致的解释和扩充,以便在方便的时候方便地进行查找。
二 . explain 使用
三. 业务实践
在实际操作中,我们怎样利用 explain所给出的查询来决定怎样设置索引?
以一个真实的商业案例为例子:第一,在一个案例中,所有的资料都是均匀的,这会造成在一个查询最优的情况下,设定的指标难以达到最佳的结果.
首先,我们来看看表格的构造:
CREATE TABLE `user_info` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '会员ID',
`user_no` bigint(20) NOT NULL DEFAULT '0' COMMENT '会员编号',
`open_id` varchar(128) NOT NULL DEFAULT '' COMMENT '外部ID',
`org_id` varchar(128) NOT NULL DEFAULT '0' COMMENT '组织ID',
`listen_num` int(11) NOT NULL DEFAULT '0' COMMENT '记录次数',
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`create_person` varchar(50) NOT NULL DEFAULT '' COMMENT '创建人',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`update_person` varchar(50) NOT NULL DEFAULT '' COMMENT '更新人',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_org_id_open_id` (`org_id`,`open_id`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE,
KEY `idx_update_time` (`update_time`) USING BTREE
) COMMENT='会员记录表';
要求获得的注册数量(listen_num)>0的成员号码(user_no)
org_id仅有4种类型的资料(A/B/C/D),每个类型的资料估计约为25%至30%
这些资料是反复修正的,修正后会被更新。
基础信息
// 1. 总记录数 4200000
// 2. 不同 org_id 下的记录数
- 1234567890 : 100万
- 9876543210 : 100万
- 8888888888 : 100万
- 6666666666 : 100万
- 其他 : 20万
// 3. 时间周期
2022-01
2022-12
3.1 以 user_id 为条件进行查找的思路
listen_num自己并没有建立任何的索引,以这个词段查,必然会走遍整个表格,所以第一个想法就是>user_id,然后依次进行排序:
explain select * from user_info where user_id > 69999887 and listen_num > 0
现在看来一切都很顺利,你看看,不是已经启动了索引,才16个字, Nice!
不过,考虑到 B+树的原理,在一个节点中,搜索的要求是从最小的顺序开始的,如果你有了user_id,那么它就会接近尾声,所以,它自然而然地会用idx_user_id来进行查询。
当您使用起始 ID进行询问时,您会看到,实际上该索引并不有效,并且该类型也是完全的。
explain select * from user_info where user_id > 10000025 and listen_num > 0
结论:如果一个索引域覆盖了所有的资料,并且搜索是非常零散的,那么这些在前面的序列中的资料就会被抛弃。
3.2在查询条件下的更新时间
因为在第二个指数中是有秩序的,用这个时间做一个查询是不是更好?
EXPLAIN SELECT * FROM user_info
WHERE update_time > "2022-08-03 01:04:55" AND update_time < "2022-09-03 01:04:55" AND listen_num > 0 LIMIT 100
3.3概要
从上述三个例子中,我们可以大致了解 explain的使用情况
根据类别判定比较的类别
根据 key来判定自己需要的指标
用 row来判定该指数的影响
3.4多索引选择
要注意的是,我们并没有想象中的那样, MySQL会在多个索引一起出现时做出相应的取舍,比如:
EXPLAIN SELECT * FROM user_info
WHERE org_id = "1234567890" and update_time > "2022-08-03 01:04:55" AND update_time < "2022-08-04 01:04:55"
and listen_num > 0 LIMIT 100
在此延长了这个时间段,其效果就会发生变化:
EXPLAIN SELECT * FROM user_info
WHERE org_id = "1234567890" and update_time > "2022-08-03 01:04:55" AND update_time < "2022-09-04 01:04:55"
and listen_num > 0 LIMIT 100
3.5连接查询的问题
在连接表格中,最重要的一个特性就是筛选,让我们来看一下该特性:
// org 是个很简单的表 , org_id 即对于其ID
EXPLAIN SELECT * FROM user_info as u , org as o WHERE org_id = "123" and u.org_id = o.id
在单一表格中,筛选值代表了指标有效的比率。
在多张表格中,此代表次表所要求的数据列比例,即被调用的表格中的其余部分。
四.问题的深度
4.1 explain的结论是否可以成为最后的决定?
explain的结论不能被视为最后的决定, explain就是一个实施方案,它与现实之间可能会有一些偏离,因为 explain并没有真正的实施.
即使我们最后只需100行,并且根据 ID顺序查找少量的数据,那么实际运行的开销也是巨大的.
概要
explain是以引用为主,但在实践中,它要求更多的实践考虑.最后的结论很有可能与 explain不相符.
比如前面的例子,根据 explain的方法,最好使用短期循环,然后是org_id.
不过,基于商业情况,我会使用> id进行循环查找.一个是由于商业上的考虑,由于大量的询问,以上两种方法都无法解决深翻页法.