mysql性能问题-explain

mysql性能问题-explain

可以通过分析sql执行计划：explain

sql解析过程

• from --- on join---where---group by--- having---select dinstinct---roder by limit---

首先创建一个表：

create table test(
    id  int(10) not null AUTO_INCREMENT,
    name varchar(30) not null,
    primary key(id)
)engine InnoDB default charset=utf8;

完后我们执行语句： explain select id from test;

可以看到有很多的选项

1.type：索引类型（要对type进行的优化前提是有索引）

system > const > eq_ref > ref > rang > index > all
其中system，const只是理想情况，实际能达到的ref>rang

• eq_ref：唯一索引，对于每条索引的查询，返回平匹配唯一索引的数据有且只有一个。
• ref：非唯一性索引，对于每个索引间的查询，返回匹配的所有
• range:检索指定范围的行，where后面是范围查询（between，in，>,<, >=,<=）,in有时候会使索引失效。
• index：查询全部索引数据
• all：查询全部表数据

执行语句 explain select id from test；--- id是索引，只需要扫描索引表，不需要扫描全表。

执行语句 explain select name from test；--- name不是索引需要扫描全表。

2.possible_keys：可能用到的索引

3.key：实际使用到的索引

4.key_len:索引的长度：用于判断复合索引是否被完全使用

现在添加一个字段，创建复合索引
alter table test add name1 varchar(30)
alter table test add index name_name1(name,name1)

创建好复合索引后，我们执行：explain select * from test where name = "zhangsan" and name1 = "zhangsan"

在执行：explain select * from test where name1 = "lisi"；

• 这里注意name1字段，是字符串类型，一定要加上双引号，不然索引会失效
• 还有如果索引字段值可以为空，mysql会使用一个字节去标识，会占用空间

5.ref：指明当前表所参照的字段

• select 。。。。 where a.c = b.c；（b.c可以是常量，ref为const）

6.rows：被索引优化的数据个数

explain select name from test

select name from test；

可以看到被优化的数据有四条

7.Extra

• using filesort（一般出现在order by语句）：性能消耗较大，需要额外一次查询或者排序

  • select name from test where name = "" order by name1;
  • 复合索引不能跨列（最佳左前缀）

• using where：需要回表查询（既需要在索引表里查询，又需要在原表查询）

  • select name，age where。。。。（name不是索引，age是索引，所以name需要回原表查询）

• using temporary（一般出现在group by语句）:性能消耗较大（用到了临时表）

  • 查询哪些列，就根据那些列分组

• useing index：性能提升，索引覆盖，不读取原文件，只从索引中读取数据（不需要回表查询）

  • 只要使用到的列，全在索引中，就叫索引覆盖。
• impossible where：where子句永远为false