说明
该文章针对InnoDB存储引擎B树索引的优化,前提要对B树索引有一定的理解。
查询SQL执行加载顺序
这是我们写SQL的流程:
SELECT a.`name`
FROM a
LEFT JOIN b
ON a.id=b.aid
WHERE a.name='xxx'
GROUP BY a.name
HAVING b.name!='xxx'
ORDER BY a.id
LIMIT 0,10
机器对查询SQL的执行加载顺序:
- FROM <left table> //计算出多个关联表表的笛卡尔积。
比如一个左连接a为 左表,b为右表。
比如a表有2条数据
id | name |
---|---|
1 | 令狐冲 |
2 | 东方不败 |
b表有2条数据:
id | name | a_id |
---|---|---|
1 | 独孤九剑 | 1 |
2 | 吸星大法 | 1 |
计算出笛卡尔结果为:
a.id | a.name | b.id | b.name | b.a_id |
---|---|---|---|---|
1 | 令狐冲 | 1 | 独孤九剑 | 1 |
1 | 令狐冲 | 2 | 吸星大法 | 1 |
2 | 东方不败 | 1 | 独孤九剑 | 1 |
2 | 东方不败 | 2 | 吸星大法 | 1 |
- ON <join condition> //通过ON条件去除笛卡尔积,以主表为主。比如左连接的左表是主表。
ON的条件是 a.id=b.a_id:
上面笛卡尔积表的第三第四条数据不符合条件,去掉,得到结果:
a.id | a.name | b.id | b.name | b.a_id |
---|---|---|---|---|
1 | 令狐冲 | 1 | 独孤九剑 | 1 |
1 | 令狐冲 | 2 | 吸星大法 | 1 |
- <join type> JOIN <right table> //添加外部行,就是把左表作为主表,右表没有的数据就置空。如果是内连接就不用添加外部行。
因为上面去除笛卡尔积导致主表东方不败被去掉了,所以添加外部行后的结果为:
a.id | a.name | b.id | b.name | b.a_id |
---|---|---|---|---|
1 | 令狐冲 | 1 | 独孤九剑 | 1 |
1 | 令狐冲 | 2 | 吸星大法 | 1 |
2 | 东方不败 | null | null | null |
- WHERE <where condition> //从上面的结果中再次通过条件过滤数据。
- GROUP <group_by_list> //以某些字段作为分组字段进行分组聚合。
- HAVING <having condition> //对分组聚合后的数据结果进行再次过滤。
- SELECT //对上面的结果,以及select后面指定的列,生成一个结果。
- DISTINCT select_list
- ORDER BY <order_by_list> //对结果进行排序
- LIMIT //对结果进行限制取指定偏移量和数量
其中JOIN理论
内连接(自然连接):
SELECT a.id,b.name FROM a INNER JOIN b on a.id=b.a_id
取交集:
左连接
SELECT a.id,b.name FROM a LEFT JOIN b on a.id=b.a_id
取主表的全集:
左连接取主表的补集
SELECT a.id,b.name FROM a LEFT JOIN b on a.id=b.a_id where b.a_id is NULL
右连接
SELECT a.id,b.name FROM a RIGHT JOIN b on a.id=b.a_id
取右表的全集:
右连接取右表的补集
SELECT a.id,b.name FROM a RIGHT JOIN b on a.id=b.a_id where a.id is NULL
全外连接
SELECT a.id,b.name FROM a FULL OUTER JOIN b on a.id=b.a_id
取两个表的并集:
全外连接取补集
SELECT a.id,b.name FROM a FULL OUTER JOIN b on a.id=b.a_id where a.id is NULL OR b.a_id is NULL
EXPLAIN
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。从而可以分析SQL语句的优劣。简单的说EXPLAIN可以看到优化器优化后的查询计划。要对SQL进行优化,理解EXPLAIN是关键。
能够看到哪些信息:
- 表的读取顺序。
- 数据读取操作的操作类型。
- 哪些索引可以使用。
- 哪些所以实际被使用。
- 表之间的引用。
- 每张表有多少行被优化器查询。
- …
EXPLAIN SELECT *FROM t1
得到:
接下来就是讲解这个表的字段的含义:
id
select查询的序列号,包含一组数字,表示查询中执行select子句或者操作表的顺序。
有以下三种情况:
- id相同,执行顺序由上往下。
EXPLAIN SELECT a.id,b.name FROM t1 a INNER JOIN t2 b ON a.id=b.`t1_id`
按顺序执行
- id不同,如果是子查询,id的序号会递增,id越大,越先执行。
EXPLAIN SELECT *FROM t1 WHERE id=(SELECT id FROM t2 LIMIT 1)
先执行2再执行1.
- Id有相同也有不相同。
EXPLAIN SELECT a.id,b.name FROM t1 a INNER JOIN t2 b ON a.id=b.`t1_id` WHERE a.id = (SELECT id FROM t2 LIMIT 1)
ID相同,认为是同一组,从上往下顺序执行,ID不同,从大往小执行。
select_type
查询的类型,主要用于区别普通查询、子查询、联合查询等的复杂查询。
- simple :简单的select查询,查询中不包含子查询或者union
EXPLAIN SELECT id FROM t1
-
PRIMARY:查询中若包含子查询,则最外面的查询就是PRIMARY。可以看上面例子。
-
SUBQUERY:在select或者where中包含了子查询,上面已经有了where的情况,下面再试试select的情况。
EXPLAIN SELECT id,(SELECT `name` FROM t2 WHERE id=1) t2_name FROM t1
- UNION:若第二个select出现union,则被标记为union。
EXPLAIN SELECT id FROM t1 WHERE id=1 UNION SELECT id FROM t1 WHERE id=1
- UNION RESULT:从union表获取结果的select,例子就在上面。
table
查询的表。
type
显示使用了何种类型。这个是调优最重要的标准之一。
下面是值,好处从上到下,也就是从快到慢。
- system:表只有一行记录,这个可以忽略不计。
- const:表示通过索引一次就找到了,const用于匹配主键主键索引和唯一索引,因为只匹配一行,所以很快将主键置于where列表中,mysql就能够将该查询转换为一个常量。
EXPLAIN SELECT *FROM t2 WHERE id=1 #id是主键
EXPLAIN SELECT *FROM t2 WHERE NAME='yehaocong' #name是唯一索引
- eq_ref :唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键索引或唯一性索引扫描。
EXPLAIN SELECT t1.* FROM t1 INNER JOIN t2 WHERE t1.id=t2.`t1_id`
- ref:非唯一性索引扫描,返回匹配单独值的所有行,本质上也是一种索引访问,可能会找到多行,所以他是查找和扫描的混合体。
EXPLAIN SELECT *FROM t1 WHERE t1.`t2_id`=1 #t2_id是一个普通索引
- range:只检索给定范围的行,使用一个索引来选择行,大于小于 between in等查询。这种范围查询通常比全表扫描好,因为呀只需要开始于索引某一点,结束与另一点。
EXPLAIN SELECT *FROM t1 WHERE id>=1
- index:使用覆盖索引,通常比ALL快,因为索引文件通常比数据文件小,也就是说all和index都是读全表,只是all是从硬盘中读取,而index是从索引中读取。
EXPLAIN SELECT id FROM t1
- ALL 将遍历全表找到匹配的行,没有使用到索引(索引失效)就是这种情况。
EXPLAIN SELECT *FROM t2 WHERE t1_id=1 #t1_id没有建立索引
possible_keys
显示可能应用到这张表的索引,一个或者多个,查询涉及到的字段上若存在索引,该索引就会被列出,但不一定被实际使用到。
EXPLAIN SELECT *FROM t1 WHERE t1.`name`='YeHaoxian23'
key
查询实际上用到的索引。查看上面例子。
key_len
索引的大小字节数,可通过该列计算查询中使用的索引长度,在不损失精确性的情况下,长度越短约好。key_len显示的值为索引字段的最大可能长度,也就是建表时该字段设置的长度。而并非实际长度。
有一个联合索引 (name,desc)
EXPLAIN SELECT *FROM t1 WHERE t1.`name`='YeHaoxian23' #查询name为YeHaoxian23的值
只用到了联合索引中的name字段,key_len 为403.
EXPLAIN SELECT *FROM t1 WHERE t1.`name`='YeHaoxian23' AND t1.`desc`='2dad'
用到了联合索引中的两个 ,key_len=806.
ref
显示索引中的哪一列被使用了。
row
根据表统计信息及索引使用情况,大致估算出找到所需记录所需要读取的行数,越少越好。
filtered
过滤率,实际符合条件的行数/扫描的函数。
EXPLAIN SELECT *FROM t2 WHERE t2.`desc`='1aa'
Extra
包含不适合在其他列显示的但十分重要的额外信息
- Using filesort:说明mysql会对数据使用,而不是按照表内索引顺序进行读取,MySQL无法利用索引完成的排序称为文件排序。这是一个危险的信号,会降低查询性能。要尽可能避免出现这种情况。
EXPLAIN SELECT *FROM t2 ORDER BY t2.`t1_id` #t1_id没有建立索引
EXPLAIN SELECT *FROM t1 WHERE t1.`name`='YeHaoxian23' ORDER BY t1.`t2_id` #有联合索引(name,desc,t2_id),因为name和t2_id之间隔了一个,所以就导致了文件排序。
EXPLAIN SELECT *FROM t1 WHERE t1.`name`='YeHaoxian23' AND t1.`desc`='2dad' ORDER BY t1.`t2_id` # 联合索引连续了,所以不用文件排序。
EXPLAIN SELECT *FROM t1 WHERE t1.`name`='YeHaoxian23' ORDER BY t1.`desc`,t1.`t2_id` #这样也不会导致文件排序
- Using temporary:出现这个表明使用了临时表来保存中间结果,常见于order by ,group by,union等情况,生成临时表是十分耗费性能的,比上面的文件排序还有严重!所以要尽可能杜绝这种情况出现。
EXPLAIN SELECT *FROM t1 WHERE id=1 UNION SELECT *FROM t1 WHERE id=2 #生成Union临时表
EXPLAIN SELECT `desc` FROM t1 GROUP BY t1.`desc` #还是那个联合索引
EXPLAIN SELECT t1.`name`,t1.`desc` FROM t1 GROUP BY t1.`name`,t1.`desc` #使用到了
- Using index:出现这个,表明了查询使用到了覆盖索引,避免主键回表查询数据这个过程,直接从索引文件得到需要的数据,这个是好的,跟上面的不一样。如果出现这种情况可以提高效率。
EXPLAIN SELECT t1.`name`,t1.`desc` FROM t1 WHERE t1.`name`='YeHaoxian23' AND t1.`desc`='2dad' #还是那个联合索引
- Using where :使用了where条件过滤。
EXPLAIN SELECT `desc` FROM t1 WHERE t1.`desc`='2dad' #如果出现了Using where 和 Using index 表明使用覆盖索引,并且索引被用来执行索引键查找。
- impossible where :表名where条件总是返回false,这个是不正确的语句, 永远不会有数据
EXPLAIN SELECT `desc` FROM t1 WHERE 1!=1 #1!=1总是返回false
总的来说这个字段就是避免 文件排序和生成临时表,追求index。