mysql explain(分析查询语句并用于优化)
小表驱动大表:主要是使最外层循环减少,连接数据库次数减少,减少资源消耗
explain能做什么?
查看表的读取顺序(id)
数据读取操作的操作类型(select_type)
哪些索引理论上可以使用(possible_keys)
哪些索引被实际使用(key)
表之间的引用(table)
每张表有多少行被优化器查询(rows)
id 的介绍(id相同 顺序执行,id不同 id大的优先执行 null最后)
type介绍(访问类型排序)
从最好到最差依次是:
system>const>eq_ref>ref>range>index>all
一般来说查询至少达到range级别,最好达到ref级别
system:表只有一行记录
const:常量连接,表最多只有一行匹配,通用用于主键或者唯一索引比较时(where id = 1)
eq_ref(一行):每次与之前的表合并行都只在该表读取一行,这是除了system,const之外最好的一种,特点是使用=,而且索引的所有部分都参与join且索引是主键或非空唯一键的索引(where t1.id = t2.id)
ref(多行):非唯一性索引扫描,返回匹配某个单独值的所有行
range(范围):只检索给定范围的行,使用一个索引来选择行。例如:between , < , > ,in() 等查询
index:全索引扫描,使用索引查询
all:遍历全表查询
key_len
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的
ref
显示索引的哪一列被使用了,如果可能的话,是一个常数(const)。哪些列或常量被用于查找索引列上的值
rows
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
Extra
using filesort(坏):如果一个排序操作不能通过索引来完成(group order),那这次排序操作就叫做filesort(没有遵守索引的最左前缀原则)
using temporary(坏):使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by(没有遵守索引的最左前缀原则)(左联接表时,如果orderBy使用的字段是第二张表的字段)
using index(好):表示相应的select操作中使用了覆盖索引(不可用select *)(索引的列(c1,c2)与查找的列(c1,c2)都对应上,直接根据索引文件查找,不需要再在表中查),避免访问了表的数据行,效率不错!
如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
using where:使用了where过滤
using join buffer:使用了连接缓存
impossible where:使用的where过滤错误
select tables optimized away:在没有GROUPBY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作
索引失效情况
在最后,总结一下什么最左前缀原则:查询从索引的最左前列开始并且不跳过索引中的列,通俗易懂的来说就是:带头大哥不能死、中间兄弟不能断
索引优化案例
结论:
join优化:尽可能减少Join语句中的NestedLoop的循环总次数;“永远用小结果集(书类)驱动大的结果集(书)”。
单表
SELECT id,author_id FROM article WHERE category_id =1 AND comments > 1 ORDER BY views DESC LIMIT1; (因为comments使用的范围,之后的索引会失效)
索引(category_id,views)
两表
SELECT * FROM class LEFT JOIN book on class.card = book.card; (左连接(left join)(因为左表都必须要有,建立索引没有区别)在右表建立索引)
索引(book.card)
三表
SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card; (左连接(left join)(因为左表都必须要有,建立索引没有区别)在右表建立索引)
索引(book.card , phone.card)