MySQL : explain 快速查询手册

一. 前言

本文将会对 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进行循环查找.一个是由于商业上的考虑，由于大量的询问，以上两种方法都无法解决深翻页法.