文章目录

EXPLAIN创建表explain字段解释索引优化索引的底层数据结构索引失效的几种情况索引下推in和orinor order by 和 group by的优化总结filesort 分页查询优化

EXPLAIN

explain查询计划，用来查看sql是否使用了索引，使用了多少索引，排序方式等。

创建表

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; emplyee表 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=8 DEFAULT CHARSET=utf8 COMMENT='员工记录表'; 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; 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;

explain字段解释

列含义id有几个select就有几个id，id越大执行优先级越高，从上往下执行select_type对应简单还是复杂查询 simple:简单查询。不包含子查询 primary:包含在select中的子查询 derived:包含在from语句中的子查询。结果放在一个临时表 subquery:在select中的子查询，不在from中table表示正在查询的表；1.有子查询时，<derivenN> 中N代表依赖id=N的查询2.有union时，<union1,2> 表示id为1和2参与了uniontype访问类型。执行效率system>const>eq_ref>ref>range>index>all,一般来讲查询需要达到range，尽量达到ref。字段详解possible_keys可能需要用到的索引。如果此列有值，而key显示null，则表示当前表数据量比较少，全表索引速度会更快。如果该列为null，可以通过where看是否能创造适当的索引来提高性能。key此列查看sql最终使用的哪个索引来查询key_len用到的索引的字节数。具体计算规则ref这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）rows预计需要扫描的行数extraextra详细

type字段详细解释

NULL:一般在查询阶段不需要访问表，直接查索引即可。例如查询列最小值id的列

const,system：一般查询条件可以确定为一个常量，与primary key和unique key的列进行比较，因为只有一个值，所以只会匹配一行，读取速度非常快。

eq_ref:unique key或primary key索引的被连接使用，最多只会返回一条符合条件的记录

ref:相比eq_ref，不使用唯一索引，使用普通索引或者唯一索引部分前缀，可能会找到多个符合的记录

range:一般表示范围查找，in，between，<,>等操作

index:扫描索引就能拿到结果（不需要回表），一般为覆盖索引（查询字段中包含了where的条件，且where条件符合走索引的规范）

ALL：全表扫描，不走索引。这个一般会根据mysql的底层算法判断应该走索引还是全表。所以有时候走全表速度不一定比走索引慢

type_len计算规则

字符串，char(n)和varchar(n)在5.0.3之后n代表字符数，不是字节数，utf-8一个汉字3个字节。 char(n)：3nvarchar(n)：3n+2 (2个字节为储存字符串长度)

---

数值 tinyint：1smallint：2int：4bigint： 8

---

时间 date：3timestamp:4datetime:8 如果字段允许为null，还额外需要1字节保存是否为null。

extra详细

Using index覆盖索引:select查询的字段都在where的索引字段中，只用通过索引就能拿到结果而不用回表查。 explain select film_id from film_actor where film_id = 1

Using where:与Using index相反，查询的字段没有被where的索引覆盖。 explain select * from film_actor where actor_id = 1

Using index condition：select查询的列没有完全被where的索引覆盖，where条件是一个前导列的范围 explain select * from film_actor where film_id > 3

Using temporary：mysql需要建立一张临时表来处理。一般情况需要优化 查询actor表，actor表没有name索引 EXPLAIN SELECT DISTINCT name from actor

查询film表，film表有name索引 EXPLAIN SELECT DISTINCT name from film

5. Using filesort:使用外部排序不走索引排序，数据比较少时会从内存中排序，否则在磁盘中完成排序，一般也是需要优化。

actor表 EXPLAIN SELECT name from actor ORDER BY name

film表 EXPLAIN SELECT name from film ORDER BY name

索引优化

索引的底层数据结构

默认使用INNODB引擎。INNODB下，主键索引和数据使用b+tree数据结构组织在一个文件当中，多级索引则是单独一个文件，这里我们使用一个二级索引。

索引失效的几种情况

假设有index(a,b,c)

where语句                                        索引使用情况a=1使用了aa=1 and b=2使用了a，ba=1 and b=2 and c=3使用了a,b,ca=1 and c=3使用了a(b中断)a=1 and b>2 and c=3使用了a,ba=1 and b>=2 and c=3使用了a,b,c。个人认为使用=号更能确定范围，而使用>不能确定范围，mysql底层会认为纯粹使用>这样不确定的范围会走全表扫描效率比较高b=2 或 b=2 and c=3 或c=3不使用like这里总结一下like，like和>= 和<=类似，所以凡是like ‘k%’ 这样k开头的会走索引，还有一种情况’k%kk%’，这里使用了索引下推,最终也会走索引。 employee表，全值索引 explain select * from employees where name = 'LiLei'

explain select * from employees where name = 'LiLei' and age = 10

explain select * from employees where name = 'LiLei' and age = 10 and position = 'sd'

最左前缀原则：指where条件语句中，需要按照索引的顺序来查询，且不能跳过中间某一个索引

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

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

4. 不能使用索引中范围条件右边的列，但是注意，使用>=可以走索引

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

5. 尽量使用覆盖索引 6. mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 <=、>= 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引 7. is null,is not null 一般情况下也无法使用索引 8. like以通配符开头（’$abc…’）mysql索引失效会变成全表扫描操作，但是使用覆盖索引可以使用到索引，但是后面的索引会失效。

索引下推

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

Mysql5.6以前这条查询语句会回表查找到所有like 'LiLei%'的数据，然后再通过age 和 position进行过滤。之后会每拿到一个 like ‘LiLei%’ 这样的数据就在内存中和后面的条件进行比对，所以这里依旧会用到索引（这里like如果和后面两个条件顺序打乱依旧会走索引）。

in和or

in

还有一种情况，这里我复制一张employees_copy1表，和employee表结构一样，里面有10w条数据，在数据量比较大的情况也会走索引

or

与in一样，在数据量大的情况的下会走索引

order by 和 group by的优化

### name匹配为 = ################ ##只要order by按最左前缀则不会出现using filesort explain select * from employees where name = 'LiLei' ORDER BY age,position; ## 有using filesort explain select * from employees where name = 'LiLei' ORDER BY position,age ## 有using filesort explain select * from employees where name = 'LiLei' ORDER BY name,position ##当有两个及以上的索引在where条件中=，并且排序也是使用索引，排序都会using index。 explain select * from employees where name = 'LiLei' and age = 10 order by age,position,name ##范围查找会using filesort explain select * from employees where name > 'LiLei' order by name ##使用覆盖索引可以using index explain select name from employees where name > 'LiLei' order by name

总结

排序有两种方式，using filesort和using index，效率index>filesortorder by满足的几种条件会使用索引排序 where中出现两个条件使用=时，order by后面（只包含索引）顺序，排序怎么样都会走索引范围查找时需要使用覆盖索引走索引排序where和order需要满足最左前缀原则

filesort

filesort使用临时文件进行排序，因为涉及到磁盘IO，因此出现fielsort的情况下都是需要优化sql的。

单路排序：一次性取出所有满足条件的字段，在sort buffer排序。双路排序：取出需要排序的字段和能定位数据行的id（主键，或者rowid），在sort buffer排好序后再回表把所有字段返回。

Mysql通过比较系统变量max_length_for_sort_data(默认1024字节)来区分使用单路或者双路， max_length_for_sort_data <排序字段，单路 max_length_for_sort_data >排序字段，双路

分页查询优化

分页查询的sql ##查询时间0.078s SELECT * from employees ORDER BY name LIMIT 20000,20; ##查询时间0.036 select * from employees e INNER JOIN (select id from employees order by name limit 20000,20) tmp on tmp.id = e.id

因为第一条sql中，根据name排序数据量太大，mysql算法全表扫描会比索引快(需要回表查的时间啊成本更高)，所以不会走索引。此时应该将数据量大的查询返回尽可能少的字段，走索引，然后关联查询。