Mysql解释语句explain

select的读取顺序

优先级：从上至下

FROM <left table>

ON <join condition>

<join type> JOIN <right table>WHERE<where condition>GROUP BY

<group by_list>

HAVING <having condition>

SELECT

DISTINCT<select list>

ORDER BY <order by_condition>

LIMIT imit_number>

explain解释语句

explain的id列

id值相同则按顺序执行，id越大优先级越高，优先级高的先执行。

select_type:复合查询、子查询

1.SIMPLE：简单的 select 查询,查询中不包含子查询或者UNION

2.PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为SIMPLE

3.SUBQUERY ：在SELECT或WHERE列表中包含了子查询

4.DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生) ；

MySQL会递归执行这些子查询，把结果放在临时表里。

5.UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；

若UNION包含在FROM子句的子查询中外层SELECT将被标记为: DERIVED

6.UNION RESULT：从UNION表取结果的SELECT

type访问类型

从最好到最差依次是:system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

System

表只有一行记录(等于系统表),这是const类型的特例，平时不会出现，可以忽略不计

const

表示通过索引一次就找到了,const用于比较primary key或者unique索引。

因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量

例如: select * from user where u.id = 1 (例如id为主键，根据id=1查询到有且只有一条记录)

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。

常见于主键或唯一索引扫描。

ref

非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某如单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

range

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引

index

Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小.(也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)

select id from activity_area_info

possible_keys和key

显示可能应用在这张表的索引，一个或多个，查询涉及到的字段若存在索引，

则该索引将被列出，但不一定被查询实际使用，

而key是最后应用到的索引，实际使用的索引，如果为null，则没有使用索引，查询中若使用了覆盖索引，则该索引仅出现在key列表中

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra其他重要的信息

Using filesort（不好） :

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取

MySQL中无法利用索引完成的排序操作称为"文件排序”

create index index_123 on t1(col1,col2,col3)

explain select col1 from t1 where t1.col1 = 'a' order by col3；

此时:

id : 1

select_type:SIMPLE

table: t1

possible_keys: index_123

key: index_123

key_len:13

ref:const

rows:142

Extra:Using where;Using index;Using filesort （原因使用col1,col3，复合索引中从来没使用）

explain select col1 from t1 where t1.col1 = 'a' order by col2,col3；

id : 1

select_type:SIMPLE

table: t1

possible_keys: index_123

key: index_123

key_len:13

ref:const

rows:142

Extra:Using where;Using index

Using temporary(不好)：会导致查询变慢

使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by。

explain select col1 from t1 where t1.col1 in（'a','b','c'） group by col2；

ref:NULL

Extra:Using where;Using index;Using temporary;Using filesort (原因group by 后面的字段没按复合索引顺序，只有col2)

explain select col1 from t1 where t1.col1 in（'a','b','c'） group by col1,col2；

ref:NULL

Extra:Using where;Using index for group-by

Using index(好)

表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错!如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

覆盖索引 (Covering Index),一说为素引覆盖。

理解方式一:就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。

简单地说查询的列刚好是索引里的字段

Using jin buffer

使用了连接缓存

如何加索引

两表连接

左连接 (left join) 在右表加索引

右连接(right join)左表加索引

三表连接

除了第一个表的关联字段加上索引

Join语句的优化

1、尽可能减少Join语句中的NestedLoop的循环总次数;“永远用小结果集驱动大的结果集优先优化NestedLoop的内层循环

2、保证Join语句中被驱动表上Join条件字段已经被索引:

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBufer的设置

正确使用索引

1.全值匹配我最爱,中间索引不能断

2.最佳左前缀法则 +

3.不在索引列上（等号左边列）做任何操作(计算、函数、(自动r手动)类型转换)，会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select * 6.mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

7.is null ,is not null 也无法使用索引

8.like以通配符开头(%abc...)mysql索引失效会变成全表扫描的操作

当用like的字段是索引的时候，查询的字段也是索引，此时索引生效，当没覆盖索引，索引失效.

解决like '%字符串%'时索引不被使用的方法

即name是索引，id是主键

此时 select id,name where name like '%a%',type为index

当select id,name ,age where name like '%a%' 索引失效，因为emal不是索引字段

9.字符串不加单引号索引失效 目

10.少用or,用它来连接时会索引失效

查询优化

小表驱动大表

当B表的数据集必须小于A表的数据集时，用in优于exists

select * from A where id in ( select id from B)

当A表的数据集小于B的数据集时，用exists优于in

select * from A where exists (select 1 from B where B.id =A.id)

其中子查询的1只要是常数即可，代表有关联数据

(select 1 from B where B.id =A.id)其实返回的是true和false

注意:A表与B表的ID字段应建立索引

EXISTS

SELECT ... FROM table WHERE EXISTS (subquery)

该语法可以理解为:将主查询的数据，放到子查询中做条件验证，根据验证结果 (TRUE 或 FALSE) 来决定主查询的数据结果是否得以保留.

提示

1.EXISTS (subquery) 只 TRUE 或FALSE，因此子查询中的 SELECT也可以是 SELECT1 或select 'X'，官方说法是实际执行时会忽略 SELECT清单,因此没有区别

2.EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题

3.EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代，何种最优需费具体问题具体分析

order

ORDER BY子句，尽量使用Index方式排序避免使用FileSort方式排序+尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。

MySQL支持二种方式的排序，FileSort和Index，Index效率高它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序:

1、order by 语句使用最左前列

2、使用where子句和order by子句条件列组合满足索引最左前列

单路排序:

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

group by

优化:

1、实质是先排序后进行分组，遵照索引建的最佳左前缀。

2、当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置

3、where高于having，能写在where限定的条件就不要去having限定了。

[什么是间隙锁]

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，lnnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录，叫做“间隙 (GAP)”，

lnnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 (Next-Key锁)。

例如:线程1 update t1 set b='1' where a>1 and a<10

此时t1中不存在a=2的记录，update未commit

线程2 insert into t1(a,b) values(2,'2');当线程1未commit之前，线程2一直被阻塞(这种情况就是因为间隙锁)

如何锁定一行

select * from t where a = 8 for update;锁定a=8的行；待commit之后其他线程对a=8所在的行进行操作