Explain详解与索引最佳实践①
explain 属于官网 optimization > understanding the query execution plan 的内容
EXPLAIN 可以在 SELECT, DELETE, INSERT, REPLACE, UPDATE 语句上工作,解释MySQL如何执行这些语句
课程内容
Explain使用与详解
show warning 展示伪SQL
explain结果列的含义
- id 执行序列,可以为null
- select_type 表示对应行是简单还是复杂的查询
- simple
- primary
- subquery
- derived
- union
- table 行所使用的表
- partitions 匹配的分区
- type 连接类型 (从优到劣排序)
- system
- const
- eq_ref
- primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。
- ref
- 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀
- range
- index
- ALL
- possible_keys 可能被选择的索引
- key 实际选择的索引
- ken_len 实际选择索引的长度
- 计算规则如下
- char(n) 3n
- varchar(n) 3n + 2
- tinyint 1字节
- smallint 2字节
- int 4字节
- bigint 8字节
- date 3字节
- timestamp 4字节
- datetime 8字节
- 允许为null, + 1字节
- 计算规则如下
- ref 与索引比较的列
- rows 预估行数
- filtered 按条件筛选行数的百分比
- Extra 附加信息(只关注常用信息,有需要查官网)
- Using index
- Using where
- Using index condition
- Using temporary
- Using filesort
- Select tables optimized away
从B+树底层分析常见索引优化原则
结合Mysql索引数据结构来理解索引的优化原则
Mysql索引最佳实践
DROP TABLE IF EXISTS `employees`;
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
#`position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
`position` varchar(20) COMMENT '职位',
`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) COMMENT='员工记录表';
INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW()),
('HanMeimei',23,'dev',NOW()),
('Lucy',23,'dev',NOW());
全值匹配
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 98 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 184 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# key_len 和 ref 字段不一致, 我的编码是 utf8mb4, 1个字符占4个字节
①key_len = 24 * 4 + 2 = 98
②key_len = 24 * 4 + 2 + 4 + 20 * 4 + 2 = 184
# ken_len 的计算验证通过
最左前缀法则
EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 102 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
#从底层数据结构来看,必然走不到索引
不在索引列上做任何操作
EXPLAIN SELECT * FROM employees WHERE id + 1 = 2;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT * FROM employees WHERE id = 29;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set, 1 warning (0.00 sec)
#id列也无法使用索引,超出范围也不会用到索引
存储引擎不能使用索引中范围条件右边的列
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 102 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
#使用范围,后面的列就是无序状态了
尽量使用覆盖索引
EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position='manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 185 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position='manager';
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| 1 | SIMPLE | employees | NULL | ref | idx_name_age_position | idx_name_age_position | 185 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
# 因为查询的字段在二级索引中没有,需要到聚集索引中再次查找
like以通配符开头
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_name_age_position | idx_name_age_position | 98 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT * FROM employees WHERE name like '%Lei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | employees | NULL | index | NULL | idx_name_age_position | 185 | NULL | 3 | 33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
#走索引需要开头的字母匹配,还可以指定二级索引已有的字段能把 ALL 提升为 index
字符串不加单引号索引失效
EXPLAIN SELECT * FROM employees WHERE name = 1000;
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | employees | NULL | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-----------+------------+------+-----------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
#数字当初字符串居然没有被转换成字符串,possible_keys中有索引,最终没有使用
实验总结
#查看数据库默认字符集和排序规则
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation%';
#查看某个指定数据库的默认字符集和排序规则
SHOW CREATE DATABASE `explain_test`;
#查看数据表的默认字符集和排序规则
SHOW TABLE STATUS FROM `explain_test`;
#查看某个表的字符集和排序规则
SHOW CREATE TABLE `actor`;
#查看某个列的字符集和排序规则
SHOW FULL COLUMNS FROM `actor`;
#测试数据如下
CREATE DATABASE IF NOT EXISTS `explain_test`;
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
);
INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-09-22 15:27:18'), (2,'b','2017-11-22 15:27:18'), (3,'c','2017-12-22 15:27:18'),(4,'a','2017-09-22 15:27:18');
DROP TABLE IF EXISTS `film`;
CREATE TABLE `film` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`)
) ;
INSERT INTO `film` (`id`, `name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');
DROP TABLE IF EXISTS `film_actor`;
CREATE TABLE `film_actor` (
`id` int(11) NOT NULL,
`film_id` int(11) NOT NULL,
`actor_id` int(11) NOT NULL,
`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_film_actor_id` (`film_id`,`actor_id`)
) ;
INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
