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;

explain

可以看到有很多的选项

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是索引,只需要扫描索引表,不需要扫描全表。
index

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

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"
len

在执行:explain select * from test where name1 = "lisi";
len

  • 这里注意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