关于分析mysql不走索引的方法

技术交流
1

创建表的相关信息

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50724
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50724
File Encoding         : 65001

Date: 2020-07-22 13:31:18
*/

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 = '请假'

image
image973×127 5.77 KB

没走索引

第二条语句

EXPLAIN SELECT * FROM message where info = '请假' LIMIT 2

走了索引

原因：查看扫描成本

第一个语句部分 （着重看cost）

 {
                "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
                    }
                  ]
                },
                "condition_filtering_pct": 100,
                "rows_for_plan": 3,
                "cost_for_plan": 1.6,
                "chosen": true
              }
            ]
          },

第二个语句部分执行计划

{
                "plan_prefix": [
                ],
                "table": "`message` `m`",
                "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"
                    }
                  ]
                },

同样不改变语句改变直接将数据库数据更改（其中一条数据的info改为加班）

EXPLAIN SELECT * FROM message where info = '请假'

看执行计划

image
image1025×237 7.13 KB

{
                "plan_prefix": [
                ],
                "table": "`message` `m`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "INFO_INDEX",
                      "rows": 2,
                      "cost": 2.4,
                      "chosen": true
                    },
                    {
                      "access_type": "range",
                      "range_details": {
                        "used_index": "INFO_INDEX"
                      },
                      "chosen": false,
                      "cause": "heuristic_index_cheaper"
                    }
                  ]
                },

总结

  • 索引创建成功与否
  • explain查看走没走索引
  • 不走索引看explain 结果中的extra字段 具体什么原因
  • 无法找到原因直接追踪执行计划optimizer_trace
2 Likes