MySQL的执行计划和索引详解

使用explain关键字可以模拟优化器执行sql语句,从而知道mysql是如何处理sql语句的,分析你的查询语句或者是结构性能。

我们通过几张表来使用explain的例子:

在select语句之前增加explain关键字,MySQL会在查询的基础上设置一个标记,执行查询时,会返回执行计划的信息,而不是执行这条sql语句(如果from中包含子查询,仍会执行该子查询的,将结果放入临时表中)

使用的的表

1.actor表(不设置索引,存储引擎是InnoDB)

2.film表(设置name索引(辅助索引),存储引擎是InnoDB)

3.film-actor表(设置联合辅助索引(film_id,actor_id))

使用的表我们建立完毕了,那接下来就让我们熟悉一下explain的始终和返回执行计划中字段的含义

mysql>EXPLAIN SELECT  (SELECT  1 from actor ) from film;

在查询中的每一个表会输出一行,如果有两个表通过join连接查询,那么会输出两行,表的意思相当广泛:可以是子查询,一个union结果等。

explain有两个辅助写法:

1)explain extended:会在explain的基础上额外提供一些查询的优化的信息,紧随其后的通过show warnings 命令可以的到优化后的查询语句,从而看出优化器优化了什么,二外有filtered列,是一个半分比的值,rows*filtered/100可以估算出来将要和explain中前一个表进行连接的行数(前一个表指的是explain中的id值比当前id值小的表)

mysql>EXPLAIN EXTENDED SELECT * from film where id = 1;

mysql>show WARNINGS;(Note 得到优化后的语句)

2)explain partitions :相比explain多了个partition字段,如果查询是基于分表的的话,会显示查询将访问的分区

那接下来我们介绍一下explain中每个列的信息

1.id列

id列的编号是select的序列号,有几个select就有几个id,并且id的顺序是按select出现的顺序增长的。MySQL将 select查询分为简单查询(SIMPLE)和复杂查询(PRIMARY)。

复杂查询分为三类:简单子查询、派生表(from语句中的子查询) 、union查询。

id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行1)简单子查询

1)简单子查询

mysql>EXPLAIN SELECT (SELECT 1 FROM actor LIMIT 1) FROM film;

2)from字句中的子查询(在mysql5.7的版中没有派生表的返回,具体原因不明,可能是5.7的优化了)

mysql>EXPLAIN SELECT id FROM (SELECT id FROM film) f;

这个查询执行时有个临时表别名为f,外部的select查询就是引用了这个临时表

3)union查询

mysql>EXPLAIN select 1 from film union all select 1 from actor;

union 结果总是放在一个匿名的临时表中,临时表不在sql中出现,因此他的id是null

2.select_type列

select_type表示对应行是简单的还是复杂的查询,如果是复杂的查询,又是上述三种哪一种

1)simple:简单查询,查询不包含子查询和union

mysql>explain select * from film where id =1;

2)primary:复杂查询中的最外层select

3)subquery:包含select中的子查询(不在from子句中)

4)derived:包含在from子句中的子查询,mysql会将结果存放在一个临时表中,也称派生表(derived的英文义)

用这个例子来解析primary,subquery,derived类型

mysql>explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;

5)union :在union中的第二和随后的select

6)union result:从union临时表检索结果的select

用这个例子来了解union和union result 类型:

mysql> explain select 1 from film union select 1 from film ;

3.table 列

这一列表示explain的一行正在访问哪个表。

当from子句中有子查询的时候,table列是<derivenN>格式,表示当前查询依赖id=N的查询,于是先执行id=N的查询。

当有union时,union result的table列的值为<union1,2>,1和2表示参与的select行id

4.type列

这一列表示关联类型和访问类型,即MySQL决定如何查找表中的行,查找的数据记录的大概范围。

依次从最优到最差分别为:system>const>eq_ref>ref>range>index>all(常用的)

一般来说,得到保证查询打到range级别,最好打到ref级别

NULL:MySQL能够在优化阶段分解查询语句,在执行阶段用不着再访问表或索引。例如再索引列中选区最小值,可以单独擦查找索引来完成,不需要再执行时访问表。

mysql>explain select min(id) from film;

注意:mysql中主键就是索引,mysql会帮主键主动建立索引的

const,system:mysql能对查询的某部分进行优化并将其转化成一个常量(可以看着show warnings的结果)。用于primary_key和union key的所有列和常数比较的时候,所以表最多一个匹配行,读一次,速度比较快,system时const的特例,表中只有一条元组数据匹配就是system

mysql>explain extended select * from (select * from film where id = 1) f;

注意:const就是查询条件时主键或者唯一性约束的,查询出来的就只有一条数据的,而system查询的表就只有一条数据的。

mysql>show warnings;(优化后的sql)

eq_ref:primary_key或者union key索引的所有部分被连接使用,最多返回一条符合条件的记录,这可能再const之外最好的连接类型了,简单的select查询就不会出现这种类型

mysql>explain select * from film_actor left join film on film_id = film.id ;

注意:film_actor也是使用索引查询的原因时覆盖索引(后面有说)

ref相比eq. ret,不使用唯一索引,而是使用普通索引或者唯一性索引的部分前缀,索引要和某个值相比较,可能会找到多个符合条件的行。
1.简单select查询,name是普通索引(非唯一索引)

mysql>explain select * from film where name = "film1";

2.关联表查询,idx_ film_ actor_ id是film_ 1d和actor_ id的联合索引,这里使用到了film_ actor的左边前缀film_id部分。

mysql> explain select * from film LEFT JOIN film_actor on film.id = film_actor.film_id;

range:范围扫描通常出现在in(), between,> <, >=等操作中。使用一个索引来检索给定范围的行。

mysql>explain select *  from actor WHERE id > 1

index:扫描全表索引,这通常比all快一些,(index是从索引中读取的,而all是从硬盘中读取)

mysql>explain select * from film ;

ALL:即全表扫描,意味着mysql需要从头到尾去查询所需要的好行,通常情况下这需要增加索引来进行优化

mysql>explain select * from actor;

5.possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain时可能出现posible. keys有列,而key显示NULL的情况,这种情况是因为表中数据不多, mysq认为索引对此查询帮助不大,选择了全表查询。

如果该列是NULL,则没有相关的素引,在这种情况下,可以通过检查where子句看是否可以创造一个适当的索引来提高查询性能,然后用explain查看效果。

6.key列

这一列是示mysq实际采用索引来优化対該表的访问
如果没有使用索引,則垓列是NULL.如果想强制mysq使用或忽视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;

key_len计算规则如下

字符串

char(n):n字节长度

varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2

数值类型

tinyint:1字节

smallint:2字节

int:4字节

bigint:8字节  

时间类型 

date:3字节

timestamp:4字节

datetime:8字节

如果字段允许为 NULL,需要1字节记录是否为 NULL

索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。

8. ref列
这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有: const (常量) ,字段名(例: film.id)
9. rows列
这一列是mysq估计要读取并检测的行数,注意这个不是结果集里的行数。

10. Extra列
这一列展示的是额外信息。常见的重要值如下:
Using index: 查询的列被索引覆盖,并且where筛选条件是索引的前导列,是性能高的表现。一般是使用了覆盖索引索引包含了所有查询的字段)。对于innodb来说,如果是辅助索引性能会有不少提高.

mysql>explain select film_id from film_actor where film_id = 1;

Using where : 查询的列未被索引覆盖 where筛选条件非索引的前导列

mysql>explain select * from actor where name = 'a';

Using where Using index: 查询的列被索引覆盖,并且where筛选条件是索引列之一但是不是索引的前导列,意味着无法直接通过索引查找来查询到符合条件的数据
mysql> explain select film_id from film_actor where actor_id = 1;

NULL:查询的列未被索引覆盖,并且where筛选条件是索引的前导列,意味着用到了索引,但是部分字段未被索引覆盖,必须通过“回表“来实现,不是纯粹地用到了索引,也不是完全没用到索引.

Using temporary: mysql需要創建一張临时表来处理査洵. 出现这种情况一般是要迸行优化的, 首先是想到用索引来优化.

1. actor.name没有索引,此时创建了张临时表来dstinct.

mysql>explain select distinct name from actor;

2.film.name 建立了idx_name索引,此时查询时extra时using index,没有使用临时表

mysql>explain select distinct name from film;

Using filesort: mysql会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。此时mysql会根据连接类型浏览所有符合条件的记录,井保存排序关键字和指针,然后排序关键字并按順序检索行信息。这种情况下一般也是要考虑使用索引来优化的。

1. actor .name未创建索引,会浏览actor整个表, 保存排序关键字name和对那个的id,然后排序name并检索记录

mysql>explain select * from actor order by name;

2.film.name 建立了idx_name索引,此时查询时extra时using index

索引最佳实践

使用的表

  1. CREATE TABLE `employees` (
  2. `id` int(11) NOT NULL AUTO_INCREMENT,
  3. `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  4. `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  5. `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  6. `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  7. PRIMARY KEY (`id`),
  8. KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
  9. ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='员工记录表';
  10. INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
  11. INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
  12. INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

 最佳实践

1. 全值匹配

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22;

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

2.最佳左前缀法则

 如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE position = 'manager';

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';

EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';

 4.存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 22 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age > 22 AND position ='manager';

5.尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句

EXPLAIN SELECT name,age FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

EXPLAIN SELECT * FROM employees WHERE name= 'LiLei' AND age = 23 AND position ='manager';

6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

EXPLAIN SELECT * FROM employees WHERE name != 'LiLei'

7.is null,is not null 也无法使用索引

EXPLAIN SELECT * FROM employees WHERE name is null

8.like以通配符开头('$abc...')mysql索引失效会变成全表扫描操作

EXPLAIN SELECT * FROM employees WHERE name like '%Lei'

EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'

问题:解决like'%字符串%'索引不被使用的方法?

a)使用覆盖索引,查询字段必须是建立覆盖索引字段

EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';

b)当覆盖索引指向的字段是varchar(380)及380以上的字段时,覆盖索引会失效!

9.字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = '1000';

EXPLAIN SELECT * FROM employees WHERE name = 1000;

10.少用or,用它连接时很多情况下索引会失效

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';

总结:

like KK%相当于=常量,%KK和%KK% 相当于范围

(0)

相关推荐

  • 95%的人都不知道 MySQL还有索引管理与执行计划

    1.1 索引的介绍 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息.如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息. ...

  • 神奇的 SQL 之 MySQL 执行计划 → EXPLAIN,让我们了解 SQL 的执行过程!

    前言 开心一刻 某人养了一头猪,烦了想放生,可是猪认识回家的路,放生几次它都自己回来了.一日,这个人想了个狠办法,开车带着猪转了好多路进山区放生,放生后又各种打转,然后掏出电话给家里人打了个电话,问道 ...

  • MySQL主从复制集群—gtid实现详解

    老哥唠叨 上一篇发了MySQL主从复制集群搭建流程,不过好像小伙伴们对这个文章并不感兴趣,但是老哥出于对技术的热爱,和对小伙伴们的负责,我还是要写主从复制另一种实现方式:GTID.这些技术真的蛮重要的 ...

  • Mysql之锁、事务绝版详解

    一 锁的分类及特性 数据库锁定机制简单来说,就是数据库为了保证数据的一致性,而使各种共享资源在被并发访问变得有序所设计的一种规则.对于任何一种数据库来说都需要有相应的锁定机制,所以MySQL自然也不能 ...

  • MySQL操作笔记(五万字详解)

    文章目录 前言 MySQL笔记 1.sql.DB.DBMS分别及三者之间的关系 2.什么是表(table) 3.SQL语句分类 4.如何导入数据(sql脚本文件) 5.什么是sql脚本 6.查看表结构 ...

  • openGauss索引详解

    本文主要介绍openGauss中常见的索引结构,索引相关元数据,并结合代码重点讲解B-tree索引使用过程中的重要流程,希望对大家理解openGauss中的索引有所帮助. 索引方法 B-Tree索引 ...

  • 数据库索引详解

    什么是索引 索引是对 数据库中一列或者多列的值进行排序的一中结构,使用索引可以快速访问数据库中表的特定信息.索引的一个主要的目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录的 ...

  • 企业预算组织建立与协调,160页预算编制与执行方法和技巧详解

    为职场精英打造个人知识体系,升职加薪! 预算编制与执行方法和技巧 目录 经营计划 为了实现企业经营方针和经营战略,使企业能够适应未来变化的环境,正确地选择未来行为而作出的科学决策和统筹安排. 经营计划 ...

  • MySQL Explain详解,添加索引sql优化

    EXPLAIN语法(获取SELECT相关信息) EXPLAIN tbl_name 或: EXPLAIN [EXTENDED] SELECT select_options 当我们使用select查询时发 ...

  • MySQL索引底层:B+树详解

    前言 当我们发现SQL执行很慢的时候,自然而然想到的就是加索引.对于范围查询,索引的底层结构就是B+树.今天我们一起来学习一下B+树哈~ 公众号:「捡田螺的小男孩」 树简介.树种类 B-树.B+树简介 ...