3. Explain详解与索引最佳实践

3.1 Explain使用与详解

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user	NULL	ref	idx_name_age_dpt	idx_name_age_dpt	1023	const	1	100.0	NULL
中	重要	中	低	重要	重要	重要	重要		一般	一般	一般

通过EXPLAIN或DESC命令获取MySQL如何执行SELECT语句信息，包括SELECT语句执行过程中表如何连接和连接的顺序

# 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT column_... FROM table_name_... where condition_...;# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra

• id: 表的执行顺序。id相同，则从上往下执行。如果id不相同，则值大的先执行

• select_type: 表示select的类型。

  • SIMPLE简单表（无表连接或子查询）

  • PRIMARY（主查询，即最外层的查询）

  • UNION（连接的第二个或者后面的查询）

  • SUBQUERY(子查询)

• type：表示连接类型。性能由好到差依次是：NULL，system、const、eq_ref、ref、range、index、all

  • NULL：不访问任何表

  • system：访问系统表

  • const：唯一索引查询 (select * from table_name where id = 1)

  • eq_ref: 连表查询的等值比较, 有唯一索引(select a.*, b.* from a,b where a.id = b.id 如果a.id是a唯一索引，则a为eq_ref )

  • ref：非唯一性索引查询 (select * from table_name where name = "zs")

  • range: 使用了索引, 范围查找

  • index：用了索引，但遍历整个索引树 (select * from table_name)

  • all: 全表扫描, 从聚集索引的叶子节点链表依次向后扫描

• possible_key: 显示可能应用在这张表上的索引，一个或多个

• key： 实际用到的索引

• key_len: 使用的索引字节数（索引最大可能长度）越短越好

• ref:

• rows：预估值，需要执行查询的行数

• filtered：查询结果行数占读取行数的百分比% (表头也在结果行中)

• Extra: 额外信息

explain select * from user where name = "张三" ;
show warnings;

当执行上述sql后, 会展示出mysql对sql语句的优化

# Level	Code	Message
# Note	1003	/* select#1 */ select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`age` AS `age`,`test`.`user`.`dpt` AS `dpt`,`test`.`user`.`date` AS `date` from `test`.`user` where (`test`.`user`.`name` = '张三')/* select#1 */ select `test`.`user`.`id` AS `id`,`test`.`user`.`name` AS `name`,`test`.`user`.`age` AS `age`,`test`.`user`.`dpt` AS `dpt`,`test`.`user`.`date` AS `date` from `test`.`user` where (`test`.`user`.`name` = '张三')

如果一个查询的数据可以即可通过主键索引查询到, 也可以通过二级索引查询到, 那么优先会通过二级索引进行查询. 因为二级索引所占空间小, 查询效率高.( 硬盘读取到内存)

select id, name where name = "zs" # 二级索引name直接查到
select id, name where id = 1; # 主键索引id直接查到
select * from user where name = "zs"; # 二级索引查到主键，再进行回表查询

Extra 字段关键值	是否回表	含义解析
Using index	否	表示查询使用了覆盖索引，所需数据全部在索引中，无需回表到聚簇索引获取完整行。
Using index condition	否	表示使用了索引下推优化，过滤在存储引擎层进行，但仍然可能需要回表获取其他字段。
Using where	是	表示服务器层需要在从存储引擎拿到回表后的完整数据行后，再根据 WHERE 条件进行过滤。

3.2 从B+树底层分析常见索引优化原则

3.3 MySQL索引最佳实践