mysql索引优化

一、select_type

1、simple

简单查询

2、primary derived

主查询 和 from子查询

3、union

union 和 union字段顺序，数量、类型都要相同

union会去重并且排序

union all不会去重和排序

4、subquery

where 子查询

二、type

表示表的连接类型。

• 数据准备

1、 system

表仅有一行，这是const类型的特列，平时不会出现，这个也可以忽略不计。

2、const

数据表最多只有一个匹配行，因为只匹配一行数据，所以很快，常用于PRIMARY KEY或者UNIQUE索引的查询，可理解为const是最优化的。

EXPLAIN SELECT * from test where id=“1”

EXPLAIN SELECT * from test where b=“1”

3、eq_ref

用于联表查询的情况,按联表的主键或唯一键联合查询

4、all

没用到索引

EXPLAIN SELECT * from test where a=1

当a的字段类型是varchar时，就算给a设置索引，第一条语句也不会使用索引，因为进行了类型转换。

5、ref

查询条件索引既不是UNIQUE也不是PRIMARY KEY的情况。

EXPLAIN SELECT * from test where a=“1”

6、range

主键、唯一键、普通索引的范围查询

7、index

索引覆盖，该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小

三、possible_keys

指出mysql能使用哪个索引在表中找到行，如果为NULL，说明没有使用索引

四、key

显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。

可以强制使用索引或者忽略索引

五、key_len

显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。

注意：key_len是确定了MySQL将实际使用的索引长度

六、ref

显示使用哪个列或常数与key一起从表中选择行。

七、 rows

显示MySQL认为它执行查询时必须检查的行数

八、Extra

该列包含MySQL解决查询的详细信息

· Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。

· Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。

· range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。

· Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。

· Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。

· Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。

· Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。

· Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描。

· Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。

九、不能使用索引的情况

1、在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不起作用。只有“%”不在第一个位置，索引才会生效。

2、联合索引最左匹配原则

联合索引的最左原则就是建立索引KEY union_index (a,b,c)时，等于建立了(a)、(a,b)、(a,b,c)三个索引，从形式上看就是索引向左侧聚集，所以叫做最左原则，因此最常用的条件应该放到联合索引的组左侧。

利用联合索引加速查询时，联合查询条件符合“交换律”，也就是where a = 1 and b = 1 等价于 where b = 1 and a = 1，这两种写法都能利用索引KEY union_index (a,b,c)。

normal键也是一样

3、使用OR关键字的查询

查询语句的查询条件中只有OR关键字，且OR前后的两个条件中的列都是索引时，索引才会生效，否则，索引不生效。