02-explain详解与索引最佳实践
mysql安装文档参考:https://blog.csdn.net/yougoule/article/details/56680952
一、explain的使用和详解
1.1 explain工具介绍
使用EXPLAIN关键字可以模拟优化器执行SQL语句,分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字,MySQL 会在查询上设置一个标记,执行查询会返回执行计划的信息,而不是 执行这条SQL
注意:如果 from 中包含子查询,仍会执行该子查询,将结果放入临时表中
1.2 Explain分析示例
参考官方文档:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
示例表:
DROP TABLE IF EXISTS `actor`;
CREATE TABLE `actor` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `actor` */
insert into `actor`(`id`,`name`,`update_time`) values
(1,'a','2021-10-05 10:09:05'),
(2,'b','2021-10-22 10:09:13'),
(3,'c','2021-10-24 10:09:20');
/*Table structure for table `film` */
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`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*Data for the table `film` */
insert into `film`(`id`,`name`) values
(3,'film0'),
(1,'film1'),
(2,'film2');
/*Table structure for table `film_actor` */
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `film_actor` */
insert into `film_actor`(`id`,`film_id`,`actor_id`,`remark`) values
(1,1,1,NULL),
(2,1,2,NULL),
(3,2,1,NULL);
小试牛刀:
mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 3 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
在查询中的每个表会输出一行,如果有两个表通过 join 连接查询,那么会输出两行
1.3 explain 两个变种
1)explain extended
会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令可 以得到优化后的查询语句,从而看出优化器优化了什么。额外还有 filtered 列,是一个百分比的值,
rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数(前一个表指 explain 中的id值比当前表id值小的表)。
mysql> explain extended select * from film where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | film | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------+
| Note | 1003 | select '1' AS `id`,'film1' AS `name` from `test`.`film` where 1 |
+-------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select '1' AS `id`,'film1' AS `name` from `test`.`film` where 1;
+----+-------+
| id | name |
+----+-------+
| 1 | film1 |
| 1 | film1 |
| 1 | film1 |
+----+-------+
3 rows in set (0.00 sec)
演示filtered列的计算公式:
mysql> select * from actor;
+----+------+---------------------+
| id | name | update_time |
+----+------+---------------------+
| 1 | a | 2021-10-05 10:09:05 |
| 2 | b | 2021-10-22 10:09:13 |
| 3 | c | 2021-10-24 10:09:20 |
| 4 | d | 2021-10-24 12:51:52 |
+----+------+---------------------+
4 rows in set (0.00 sec)
mysql> select * from film_actor;
+----+---------+----------+--------+
| id | film_id | actor_id | remark |
+----+---------+----------+--------+
| 1 | 1 | 1 | NULL |
| 2 | 1 | 2 | NULL |
| 3 | 2 | 1 | NULL |
+----+---------+----------+--------+
3 rows in set (0.00 sec)
mysql> EXPLAIN EXTENDED SELECT * FROM actor a INNER JOIN film_actor f ON a.`id`=f.`actor_id`;
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | f | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | |
| 1 | SIMPLE | a | ALL | PRIMARY | NULL | NULL | NULL | 4 | 75.00 | Using where; Using join buffer |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+--------------------------------+
2 rows in set, 1 warning (0.00 sec)
2)explain partitions
相比 explain 多了个 partitions 字段,如果查询是基于分区表的话,会显示查询将访问的分区。11
建立表:
DROP TABLE IF EXISTS `hash_part`;
CREATE TABLE `hash_part` (
`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '评论ID',
`comment` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '评论',
`ip` VARCHAR(25) NOT NULL DEFAULT '' COMMENT '来源IP',
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (id)
PARTITIONS 3 */;
/*Data for the table `hash_part` */
INSERT INTO `hash_part`(`id`,`comment`,`ip`) VALUES
(3,'3','3'),
(1,'1','1'),
(2,'2','2');
/!../ 是一种特殊的注释,其他的数据库产品当然不会执行。mysql特殊处理,会选择性的执行。可以认为是:预编译中的条件编译。特别注意 50100,它表示5.01.00 版本或者更高的版本,才执行。
查看partitions的值:
mysql> explain partitions select * from hash_part where id =1;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | hash_part | p1 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain partitions select * from hash_part where id =2;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | hash_part | p2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain partitions select * from hash_part where id =3;
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | hash_part | p0 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+-----------+------------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain partitions select * from hash_part where id =4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+
1 row in set (0.00 sec)
1.4 explain中的列
1)id列
select查询的序列号,是一组数字,表示的是查询中执行select子句或者是操作表的顺序。
id的情况有三种,分别是:
- id相同表示加载表的顺序是从上到下。
- id不同id值越大,优先级越高,越先被执行。
- id有相同,也有不同,同时存在。id相同的可以认为是一组,从上往下顺序执行;在所有的组中,id的值越大,优先级越高,越先执行。id为NULL最后执行
mysql> EXPLAIN SELECT f.name,(SELECT id FROM film WHERE id=2) AS id FROM (SELECT id ,`name` FROM film WHERE id =1) f UNION SELECT `name`,id FROM film;
+----+--------------+------------+--------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------+----------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | film | const | PRIMARY | PRIMARY | 4 | | 1 | |
| 2 | SUBQUERY | film | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 4 | UNION | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |
| NULL | UNION RESULT | <union1,4> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------+----------+---------+-------+------+-------------+
5 rows in set (0.00 sec)
mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | film | const | PRIMARY | PRIMARY | 4 | | 1 | |
| 2 | SUBQUERY | actor | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.00 sec)
2)select_type列
select_type 表示对应行是简单还是复杂的查询。
a)simple
简单查询。查询不包含子查询和union
mysql> explain select * from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
b)primary
复杂查询中最外层的 select
c)subquery
包含在 select 中的子查询(不在 from 子句中)
d)derived
包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表(derived的英文含义)
mysql> explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | film | const | PRIMARY | PRIMARY | 4 | | 1 | |
| 2 | SUBQUERY | actor | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------------+
3 rows in set (0.00 sec)
set session optimizer_switch='derived_merge=off';
#关闭mysql5.7新特性对衍生表的合 并优化set session optimizer_switch='derived_merge=on';
#还原默认配置
e)union
在 union 中的第二个随后的 select
mysql> explain select 1 union all select 1;
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| 2 | UNION | NULL | NULL | NULL | NULL | NULL | NULL | NULL | No tables used |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+------+---------------+------+---------+------+------+----------------+
3 rows in set (0.00 sec)
3)table列
这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时,table列是 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查 询。当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的 select 行id。
4) type列
这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。 依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL
一般来说,得保证查询达到range级别,最好达到ref
NULL
mysql能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如:在索引列中选取最小值,可以单独查找索引来完成,不需要在执行时访问表
mysql> explain select max(id) from film;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)
const, system
mysql能对查询的某部分进行优化并将其转化成一个常量(可以看show warnings 的结果)。用于 primary key 或 unique key 的所有列与常数比较时,所以表最多有一个匹配行,读取1次,速度比较快。system是 const的特例,表里只有一条元组匹配时为system
mysql> explain extended select * from (select * from film where id = 1) tmp;
+----+-------------+------------+--------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+--------+---------------+---------+---------+------+------+----------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | 100.00 | |
| 2 | DERIVED | film | const | PRIMARY | PRIMARY | 4 | | 1 | 100.00 | |
+----+-------------+------------+--------+---------------+---------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+------------------------------------------------+
| Level | Code | Message |
+-------+------+------------------------------------------------+
| Note | 1003 | select '1' AS `id`,'film1' AS `name` from dual |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)
eq_ref
primary key 或 unique key 索引的所有部分被连接使用 ,最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了,简单的 select 查询不会出现这种 type。
mysql> explain select * from film_actor left join film on film_actor.film_id = film.id;
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+------+-------+
| 1 | SIMPLE | film_actor | ALL | NULL | NULL | NULL | NULL | 3 | |
| 1 | SIMPLE | film | eq_ref | PRIMARY | PRIMARY | 4 | test.film_actor.film_id | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+------+-------+
2 rows in set (0.00 sec)
ref
相比 eq_ref,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会 找到多个符合条件的行。
a)简单 select 查询,name是普通索引(非唯一索引)
mysql> explain select * from film where name = 'film1';
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | film | ref | idx_name | idx_name | 33 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
b)关联表查询,idx_film_actor_id是film_id和actor_id的联合索引,这里使用到了film_actor的左边前缀film_id部分。
mysql> explain select film_id from film left join film_actor on film.id = film_actor.film_id;
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
| 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |
| 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | test.film.id | 1 | Using index |
+----+-------------+------------+-------+-------------------+-------------------+---------+--------------+------+-------------+
2 rows in set (0.00 sec)
range
范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。
mysql> explain select * from actor where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | actor | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
index
扫描全索引就能拿到结果,一般是扫描某个二级索引,这种扫描不会从索引树根节点开始快速查找,而是直接 对二级索引的叶子节点遍历和扫描,速度还是比较慢的,**这种查询一般为使用覆盖索引,二级索引一般比较小,所占磁盘空间更小,**所以这种通常比ALL快一些。
mysql> explain select * from film;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| 1 | SIMPLE | film | index | NULL | idx_name | 33 | NULL | 3 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set (0.00 sec)
ALL
即全表扫描,扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。
mysql> explain select * from actor;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 4 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
5)possible_keys列
这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,mysql认为索引 对此查询帮助不大,选择了全表查询。 如果该列是NULL,则没有相关的索引。在这种情况下,可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能,然后用 explain 查看效果。
6)key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引,则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引,在查询中使用 force index、ignore index。
7)key_len列
这一列显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。 举例来说,film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成,并且每个int是4字节。通 过结果中的key_len=4可推断出查询使用了第一个列:film_id列来执行索引查找。
mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
| 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 1 | |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------+
1 row in set (0.00 sec)
key_len计算规则如下:
字符串,char(n)和varchar(n),5.0.3以后版本中,**n均代表字符数,而不是字节数,**如果是utf-8,一个数字 或字母占1个字节,一个汉字占3个字节
- char(n):如果存汉字长度就是 3n 字节
- varchar(n):如果存汉字则长度是 3n + 2 字节,加的2字节用来存储字符串长度,因为 varchar是变长字符串
数值类型
- tinyint:1字节
- smallint:2字节
- int:4字节
- bigint:8字节
时间类型
- date:3字节
- timestamp:4字节
- datetime:8字节
如果字段允许为 NULL,需要1字节记录是否为 NULL
索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索 引。
mysql> explain select * from film_actor where film_id = 2 and actor_id =1;
+----+-------------+------------+------+-------------------+-------------------+---------+-------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+-------------------+-------------------+---------+-------------+------+-------+
| 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 8 | const,const | 1 | |
+----+-------------+------------+------+-------------------+-------------------+---------+-------------+------+-------+
1 row in set (0.00 sec)
film_id和actor_id都是int类型,占4个字节,为4+4=8个字节
mysql> explain select * from film where name='tom';
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | film | ref | idx_name | idx_name | 33 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+----------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
name字段的长度为10,key_len=3*10+2+1(字段允许为null需要占用一个字节)=33
8) ref列
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id)
9)rows列
这一列是mysql估计要读取并检测的行数,注意这个不是结果集里的行数。
10) Extra列
这一列展示的是额外信息。常见的重要值如下:
a)Using index:使用覆盖索引
覆盖索引定义:mysql执行计划explain结果里的key有使用索引,如果select后面查询的字段都可以从这个索引的树中 获取,这种情况一般可以说是用到了覆盖索引,extra里一般都有using index;覆盖索引一般针对的是辅助索引,整个 查询结果只通过辅助索引就能拿到结果,不需要通过辅助索引树找到主键,再通过主键去主键索引树里获取其它字段值
mysql> explain select film_id from film_actor where film_id = 1;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | film_actor | ref | idx_film_actor_id | idx_film_actor_id | 4 | const | 2 | Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
b)Using where
使用 where 语句来处理结果,并且查询的列未被索引覆盖
mysql> explain select * from actor where name = 'a';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | actor | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
c)Using index condition
查询的列不完全被索引覆盖,where条件中是一个前导列的范围;
mysql> explain select * from film_actor where film_id > 1;
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | film_actor | NULL | range | idx_film_actor_id | idx_film_actor_id | 4 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
d)Using temporary
mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索 引来优化。
1) actor.name没有索引,此时创建了张临时表来distinct
mysql> explain select distinct name from actor;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
2) film.name建立了idx_name索引,此时查询时extra是using index,没有用临时表
mysql> explain select distinct name from film;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | idx_name | idx_name | 33 | NULL | 3 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
e)Using filesort
将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一 般也是要考虑使用索引来优化的。
1. actor.name未创建索引,会浏览actor整个表,保存排序关键字name和对应的id,然后排序name并检索行记录
mysql> explain select * from actor order by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 1 | SIMPLE | actor | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
- film.name建立了idx_name索引,此时查询时extra是using index
mysql> explain select * from film order by name;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | index | NULL | idx_name | 33 | NULL | 3 | 100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
f)Select tables optimized away
使用某些聚合函数(比如 max、min)来访问存在索引的某个字段时
mysql> explain select min(id) from film;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
| 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 | Select tables optimized away |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)
二、索引最佳实践
表的创建
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 '职位',
`hire_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
PRIMARY KEY (`id`),
KEY `idx_name_age_position` (`name`, `age`, `position`) USING BTREE
) ENGINE = INNODB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8 COMMENT = '员工记录表' ;
INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(NAME,age,POSITION,hire_time) VALUES('Lucy',23,'dev',NOW());
2.1 全值匹配
mysql> 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 | 74 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| 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 | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> 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 | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
74:name的长度=3n+2=324+2=74
78:name的长度+age的长度=(324+2)+4=78
140:name的长度+age的长度+position的长度=(324+2)+4+(203+2)=140
2.2 最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
mysql> 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 | 78 | const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> 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)
mysql> EXPLAIN SELECT * FROM employees WHERE position = 'manager';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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)
2.3 不在索引列做计算
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
mysql> 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 | 74 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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)
给hire_time增加一个普通索引:
mysql> ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> EXPLAIN select * from employees where date(hire_time) ='2018‐09‐30';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 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, 2 warnings (0.00 sec)
转化为日期范围查询,有可能会走索引:
mysql> EXPLAIN select * from employees where hire_time >='2018-09-30 00:00:00' and hire_time <='2018-09-30 23:59:59';
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_hire_time | idx_hire_time | 4 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
还原索引的状态:
ALTER TABLE `employees` DROP INDEX `idx_hire_time`;
2.4 存储引擎不能使用索引中范围条件右边的列
mysql> 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 | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manage r';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 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 | 78 | NULL | 1 | 33.33 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
2.5 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少 select * 语句
mysql> 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 | 140 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> 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 | 140 | const,const,const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
2.6 mysql在使用不等于(!=或者<>),not in ,not exists 的时候无法使用索引会导致全表扫描
< 小于、 > 大于、 <=、>= 这些,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.40 |
+-----------+
1 row in set (0.01 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name not in( 'LiLei');
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql8的情况下会走索引:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11 |
+-----------+
1 row in set (0.00 sec)
mysql> 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 | range | idx_name_age_position | idx_name_age_position | 74 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name not in( 'LiLei');
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 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 | 74 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
2.7 is null,is not null 一般情况下也无法使用索引
mysql> EXPLAIN SELECT * FROM employees WHERE name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name is not null;
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
2.8 like以通配符开头(‘$abc…’)mysql索引失效会变成全表扫描操作
mysql> 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.01 sec)
mysql> 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 | 74 | NULL | 1 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
问题:解决like’%字符串%'索引不被使用的方法?
a)使用覆盖索引,查询字段必须是建立覆盖索引字段
mysql> 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 | 140 | NULL | 3 | 33.33 | Using where; Using index |
+----+-------------+-----------+------------+-------+---------------+-----------------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
b)如果不能使用覆盖索引则可能需要借助搜索引擎
2.9 字符串不加单引号索引失效
mysql> 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 | ref | idx_name_age_position | idx_name_age_position | 74 | const | 1 | 100.00 | NULL |
+----+-------------+-----------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> 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)
2.10 少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.40 |
+-----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | ALL | idx_name_age_position | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-----------+------+-----------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql8会走索引:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11 |
+-----------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
| 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 | 74 | NULL | 2 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
2.11 范围查询优化
给年龄添加单值索引
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.40 |
+-----------+
1 row in set (0.00 sec)
mysql> explain select * from employees where age >=1 and age <=2000;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | ALL | idx_age | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
没走索引原因:mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引。比如这个例子,可能是 由于单次数据量查询过大导致优化器最终选择不走索引
优化方法:可以将大的范围拆分成多个小范围
mysql> explain select * from employees where age >=1 and age <=1000;
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employees | ALL | idx_age | NULL | NULL | NULL | 3 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from employees where age >=1001 and age <=2000;
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | employees | range | idx_age | idx_age | 4 | NULL | 1 | Using where |
+----+-------------+-----------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql8会走索引:
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.11 |
+-----------+
1 row in set (0.00 sec)
mysql> explain select * from employees where age >=1 and age <=2000;
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | employees | NULL | range | idx_age | idx_age | 4 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-----------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
还原最初索引状态
ALTER TABLE `employees` DROP INDEX `idx_age`;
2.12 索引使用总结
like KK%相当于=常量,%KK和%KK% 相当于范围
‐‐ mysql5.7关闭ONLY_FULL_GROUP_BY报错
select version(), @@sql_mode;SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
相关文章
- python pandas loc布尔索引(指定条件下的索引)
- 详解Python_Numpy库函数take_along_axis()【由索引矩阵生成新的矩阵】
- [转]Oracle中的索引详解
- 图解|从根上彻底理解MySQL的索引【转】
- MySQL not exists 真的不走索引么
- oracle 索引失效原因及解决方法
- mysql 添加列的索引
- jquery 获取索引值在一定范围的列表
- MySQL 索引与查询优化
- T-SQL查询高级--理解SQL SERVER中非聚集索引的覆盖,连接,交叉和过滤
- SQL Server 索引中include的魅力(具有包含性列的索引)
- MySQL索引15连问,抗住!
- mongodb查询优化,索引、复合索引、唯一索引、explain分析查询速度
- MongoDB复合索引详解
- MongoDB的地埋空间数据存储、空间索引以及空间查询
- PostgreSQL集群方案相关索引页
- 2.跟我学solr---在solr admin中加入索引
- pandas 读取文件时 header设置列名 index_col 设置索引 usecols 读取哪几列 parse_dates 将哪一列设置为时间类型 nrows 读取数据行数
- MySQL索引背后的数据结构及算法原理--转
- Oracle 索引