mysql索引追踪计划实践

目录

• explain
  结果中各参数详解

• 创建了索引后查询时有时走索引有时不走索引为什么？加了Limit后又走索引为什么？

• explain
  结果中不是ALL
  就一定用到索引了吗？Extra列
  有什么特殊之处？联合索引查询全值匹配顺序为什么不影响索引？而非全值有影响呢？

• optimizer_trace
  大法好在哪里？

具体回答

参数详解

Extra字段解释(部分)

创建了索引后查询时有时走索引有时不走索引为什么？加了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
即使用一个索引来检索给定范围的行

最后，mysql真强大，好好学