目录
-
explain结果中各参数详解 -
创建了索引后查询时有时走索引有时不走索引为什么？加了Limit后又走索引为什么？
-
explain结果中不是ALL就一定用到索引了吗？Extra列有什么特殊之处？联合索引查询全值匹配顺序为什么不影响索引？而非全值有影响呢？ -
optimizer_trace大法好在哪里？
具体回答
参数详解
| 参数 | 说明 |
|---|---|
id | id列的编号是select的序列号，有几个select就有几个id，并且id的顺序是按select出现的顺序增长的,id列越大执行优先级越高，id相同则从上往下执行，id为null最后执行 |
select_type | 简单还是复杂的查询 |
table | 一般是访问的表 |
type | MySQL决定如何查找表中的行的方式，优劣依次是system>const>eq_ref>ref>range>index>ALL ,当然还有null |
possible_keys | 表示使用了哪些索引来查询 |
key | 表示实际上使用哪个索引来访问 |
key_len | 表示索引字节数，可以用来快速找到使用的联合索引中的某个 |
ref | 这一列显示了在key列记录的索引中，表查找值所用到的列或常量,举例：wechat_app_server.sys_admin.user_id->库.表.字段 |
rows | mysql估计要读取并检测的行数,并不是查询结果列表的行数 |
Extra | 展示的是额外信息，因为涉及比较多以下分开单独说明 |
Extra字段解释(部分)
| 值 | 说明 |
|---|---|
Using index | 查询的列被索引覆盖，高性能表现，一般是使用了覆盖索引(索引包含了所有查询的字段) |
Using where | 查询的列未被索引覆盖，有可能创建了索引但是未使用索引，具体看后面专门分析（第二问） |
Using where Using index | 查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的前导列，无法直接通过索引查找到符合条件的数据 |
Using temporary | 创建临时表处理查询 |
Using filesort | 对结果使用一个外部排序，而不是按索引次序从表里读取行 |
NULL | 不是纯粹地用到了索引，也不是完全没用到索引 |
| ..... | ..... |
创建了索引后查询时有时走索引有时不走索引为什么？加了Limit后又走了？
- 创建表并插入数据
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for message
-- ----------------------------
DROP TABLE IF EXISTS `message`;
CREATE TABLE `message` (
`id` int(11) NOT NULL,
`u_id` int(11) DEFAULT NULL,
`info` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `INFO_INDEX` (`info`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of message
-- ----------------------------
INSERT INTO `message` VALUES ('1', '1', '请假');
INSERT INTO `message` VALUES ('2', '2', '请假');
INSERT INTO `message` VALUES ('3', '3', '请假');
EXPLAIN SELECT * FROM message where info = '请假'
-------------------explain结果-------------------
|1|SIMPLE|message|ALL|INFO_INDE|3|100|Using where|
上述查询可以看出没有走索引，但是索引是创建了的
- 将上述语句改下
EXPLAIN SELECT * FROM message where info = '请假' LIMIT 2
---------------------exlain结果------------------
1|SIMPLE|message|ref|INFO_INDEX |INFO_INDEX|768|const|3|100|null
可以看到用到了索引
- 如果不加
LIMIT直接将数据库中的三条数据中的某个数据改了也就是请假这个类型的数据小于总记录数会怎么样？
EXPLAIN SELECT * FROM message where info = '请假'
--------------------
1 1 请假
2 2 aaa请假
3 3 请假
-------------------
1|SIMPLE|message|ref|INFO_INDEX|INFO_INDEX|768|const|2|100|null
同样也是走了索引的，那原因是什么呢？具体看最后
explain结果中不是ALL就一定用到索引了吗？
- 左前缀匹配
不一定，上述extra中各种情况都有可能出现，如果创建三个字段的联合索引，我只用到了俩个，但是其中俩个并不是联合索引中的第一个和第二个，那么即使有索引也是不走索引的，因为
B+树的数据页和记录先是按照顺序来查找的。也就是我们所说的左前缀匹配。
- 全值匹配
但是如果
全值匹配呢？答案是：没有影响。因为MySQL有一个叫查询优化器的东西，会分析先使用哪个搜索条件，后使用哪个搜索条件。
optimizer_trace 大法好在哪里？
- 引
在上面例子中，我们无法知道为何查询数据有时用了索引，但是数据条数限制或者改了数据后又使用了索引，这时候应该怎么办？对！就是
optimizer_trace
- 用
-------------------------使用方式-----------------
SHOW VARIABLES LIKE 'optimizer_trace';
SET optimizer_trace="enabled=on";
EXPLAIN SELECT * FROM message where info = '请假'
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
- 证(着重看
considered_access_paths)
---------------没用索引-------------------
"plan_prefix": [
],
"table": "`message` `m`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "INFO_INDEX",
"rows": 3,
"cost": 2.6,
"chosen": true
},
{
"rows_to_scan": 3,
"access_type": "scan",
"resulting_rows": 3,
"cost": 1.6,
"chosen": true
}
]
},
......
----------------用了索引---------------------
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "INFO_INDEX",
"rows": 3,
"cost": 2.6,
"chosen": true
},
{
"access_type": "range",
"range_details": {
"used_index": "INFO_INDEX"
},
"chosen": false,
"cause": "heuristic_index_cheaper"
}
................
可以看到没有索引的
access_type为ref时的cost为2.6，但是scan的时候为1.6成本不一样，所以结果不一样，这就是为什么不走索引，后面请假数据条数限制后，可以看到后面就不一样了access_type分别为ref和range即使用一个索引来检索给定范围的行
评论区