Explain两个变种：
explain extended：rows*filtered/100 关联的行数，比较少用

• show warnings;
• 
• 执行的语句可能呗mysql的优化，大体的优化结果。

explain partition：多了partition字段，分区会显示，很少用到
EXPLAIN EXTENDED SELECT * FROM film where id = 1；

字段：

id：

• 
• id指的是这条语句执行的先后顺序，id越大先执行，id越小后执行。
• id可重复，谁排在前面谁先执行。

select_type：

• simple：简单查询，查询不包含子查询和union
• 
• primary：复杂查询中最外层的select
• subquery：包含在select中的子查询（不在from子句中，select关键字后边的）
• derived：包含在from子句中的子查询，mysql会讲结果存放在一个临时表中，也称为派生表（在from关键字后边的查询）
• 

table：

• 
• 为衍生表，真实的衍生变是film（DERIVED）查询的时候生成的临时表的数据。
• table字段，一条sql语句中有几个查询，每个查询牵扯到那几张表，在table字段显示

partitions：

*type：sql的访问（关联）类型，查找数据行记录的大概范围

• 包含属性：以下按最优到最差，一般达到range，最好ref system->const->eq_ref->ref->range->index->all

  null：执行的时候用不着再访问表或者索引

  • 

    system、const：

  • 

  • const：查询的结果利用主键，只有一条，相当于查询常量

  • system：查询表的结果集只有一条，表本身只有一条数据

    eq_ref：

  • 

  • 关联，至少两张表

  • 如果有表关联，用的是主键关联，就是eq_ref

    ref：

  • 1：

  • 表关联查询的时候，没有用唯一键去查询，查询到的结果有可能是多条结果。可能使用普通索引（二级索引）或者唯一索引的部分前缀。
  • 2：

  • 联合索引

    range：

  • 

  • 通常是范围查找，底层实际还是用到了索引， 在 in(), between ,> ,<, >= 等
    

    index：

  • 

  • 
  • film表就两个字段，一个主键ID，一个普通索引字段，没有查询条件，但是还是走的索引，
  • 示例扫描二级索引，查询到的结果，如果二级索引和主键索引都有，优先使用二级索引，因为二级索引更小，主键索引的叶子节点包含所有的数据。
  • 臊面全索引可以拿到结果，一般是扫描某个二级索引。
  • 虽然用到索引，但是效率不高，至少加一个条件。

  • 上面示例全索引扫描，如果加一个where条件扫描索引，会折半扫描，节省很多时间。

    ALL：

  • 

  • 聚簇索引，扫描所有ibd里面的所有的子节点，扫描的是主键索引。
  • index为什么比all效率高？因为主键索引表包含了所有的数据，二级索引只有定义索引数据。

    possible_keys：放索引

• explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引 对此查询帮助不大，选择了全表查询。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提 高查询性能，
  

  key：sql执行的时候，真正用到的索引

• 如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用force index、ignore index
  

  key_len：

  • 
  • mysql在索引里面用到的字节数，例如int就是4字节

• 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会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索 引。
    

    ref：

    这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
    

    rows：

    这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
    

    filtered：

    Extra：

    1：Using index

• 覆盖索引：不是真正的索引，只是一种查询方式，查询的结果字段在索引树里面全包含了。类似于type字段里面的index查询，就是索引树里面用到了所有的索引进行查询。不需要回表。

• 

  2： Using where

• 使用 where 语句来处理结果，并且查询的列未被索引覆盖

• 

  3： Using index condition

• 查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

• 

  4： Using temporary

• mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索 引来优化。

• 1：distinct会去重，用到临时表，再从临时表去重。查询的字段没有索引
• 
• 2：对上一种的优化，extra的值为Using index，因为查询的字段是索引字段，索引字段是排好序的，扫描索引树的时候就做到了去重。用到了覆盖索引，查询的字段用到了索引

• 

  5：Using filesort

• 1：没有用到索引，没有索引，需要把结果集拿出来放到内存中，进行排序

• 
• 2：用到了索引，就用到了索引树，order by的时候，索引树已经排好序了。

• 

  6： Select tables optimized away

• 

• ：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段是。可以直接从索引树中找到最大最小的值

索引最佳实践

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());

全值匹配

下面三个语句都会走联合索引，只是用到的个数不相同，都遵循最左前缀原则。 如果顺序换了，依旧会走索引，因为mysql内部做了优化。

• 1：只用74字节，name字段 3*24 + 2
• 
• 2：78字节 3*24+2+4
• 
• 3：

• 

  最左前缀

  索引最左前缀原理
  建立索引的先后顺序进行创建，如果第一个字段就已经排好序，区分出要查找的值，就不会查找后面的字段了。如果第一个字段都相等，就对比第二个字段，一次类推
  联合主键（模拟三个字段：name,age,position）三个字段不可能相等的
  要用的时候一定要按照建索引的顺序去用，不能跳过第一个直接用后面的，顺序从左开

• 1：

• 
• 2：
• 
• 3：

• 

  不在索隐裂上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效转向全表臊面

• 第一条语句走索引，第二条没有走索引，因为第二天去了name的前三个值，但是索引树里面没有这个索引字段

  • 
  • 
• 下面的语句也不会走索引，计算后的结果去索引树里面找不到，所以不走索引
  • 
• 下面的语句走索引了，因为索引树里面可以匹配到，索引树是排好序的，
  • 
• 还原最初索引 | ALTER TABLE employees DROP INDEX idx_hire_time; | | —- |

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

• 第一个走所有的索引，能用到索引树里面所有的字段
• 
• 第二个只走前两个索引，第一个字段有序的情况下，第二个会根据第一个匹配，第二个是范围查找的话，第三个字段肯定是无序的

• 

  尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select*语句

• 查询的时候尽量指明查询的字段，字段尽量都在联合索引中

• 
• 尽量避免全字段查询
• 

  mysql在使用不等于（!= 或者<>）,not in，notexists的时候无法使用索引会导致全表扫描 <小于、>大于、<=/>=这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引

is null，is not null一般情况下也无法使用索引

null字段可能会集中放在一起，跟其他节点做一个双向指针

like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作

百分号在前面，可能查询的关键字前面还有其它的字符，跳过了这些字符串，索引树里面就不是有序的

用到了name的前缀

left需要进行计算，
问题：解决like’%字符串%’索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段 ，type优化到index

b）如果不能使用覆盖索引则可能需要借助搜索引擎

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

• 第一个语句使用了单引号
• 
• 第二个语句没用单引号，后台可能会帮忙转型。
• 

  少用in或or，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例，表大小等多个因素整体评估是否使用索引，详见范围查询优化

  or或in有可能会走索引，有可能不会走索引，尽量少好用
  

  范围查询优化

  有可能数据量太多了，全表扫描会更快，范围缩小可能会走索引
  
  优化：进行拆段后，可能会走索引
  
  
  还原字段

索引总结：index（a，b，c）

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

本次知识点所有sql

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;
 INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017‐12‐22
:27:18'), (2,'b','2017‐12‐22 15:27:18'), (3,'c','2017‐12‐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`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 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`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1),(2,1,2),(3,2,1);
EXPLAIN EXTENDED SELECT * FROM film where id = 1;
 show warnings;
explain select * from film where id = 2;
set session optimizer_switch='derived_merge=off';#关闭衍生表的合并优化
explain select (select 1 from actor where id = 1) from (select * from film where id = 1) der;
explain select min(id) from film;
explain extended select * from (select * from film where id = 1) tmp;
show warnings;
explain select * from film_actor left join film on film_actor.film_id = film.id
 explain select * from film where name = 'film1';
 explain select film_id from film left join film_actor on film.id = film_actor.film_id;
explain select * from actor where id > 1;
explain select * from film;
explain select * from actor;
explain select * from film_actor where film_id = 2;
explain select film_id from film_actor where film_id = 1;
explain select * from actor where name = 'a';
 explain select * from film_actor where film_id > 1;
 explain select distinct name from actor;
explain select distinct name from film;
explain select * from actor order by name;
 explain select * from film order by name;
explain select min(id) from film;
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());
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';
EXPLAIN SELECT * FROM employees WHERE name = 'Bill' and age = 31;
EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = 'dev';
EXPLAIN SELECT * FROM employees WHERE position = 'manager';
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei';
EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'LiLei';
 ALTER TABLE `employees` ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE ;
EXPLAIN select * from employees where date(hire_time) ='2018‐09‐30';
EXPLAIN select * from employees where hire_time >='2018‐09‐30 00:00:00' and hire_time <='2018‐09‐30 23:59:59';
 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';
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';
EXPLAIN SELECT * FROM employees WHERE name != 'LiLei';
EXPLAIN SELECT * FROM employees WHERE name is null
EXPLAIN SELECT * FROM employees WHERE name like '%Lei'
EXPLAIN SELECT * FROM employees WHERE name like 'Lei%'
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';
EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' or name = 'HanMeimei';
ALTER TABLE `employees` ADD INDEX `idx_age` (`age`) USING BTREE ;
explain select * from employees where age >=1 and age <=2000;
explain select * from employees where age >=1 and age <=1000;
explain select * from employees where age >=1001 and age <=2000;
ALTER TABLE `employees` DROP INDEX `idx_age`;