转载~
数据库优化是一个很常见的面试题,下面就针对这一问题详细聊聊如何进行索引与sql的分析与优化。
MySQL 提供了一个 EXPLAIN 命令,它「可以对 sql语句进行分析,并输出sql执行的详细信息」,可以让我们有针对性的优化。例如:
explain select * from student where id > 2;
这里需要注意一下版本差异
「在使用索引的时候首先应该学会分析SQL的执行,使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,可以知道MySQL是如何处理SQL语句」。
使用格式:
#explain sql语句 如下:
explain select * from student where id > 2;
从执行计划输出的结果可以看出,它有很多的字段,每个字段都有自己的含义
「id」
「选择标识符」:在一个查询语句中每个【SELECT】关键字都对应一个唯一的 id。两种例外的情况:
「id相同」优化器对子查询做了「半连接(semi-jion)优化」时,两个查询的 id 是一样的
explain select * from student where id in(select id from student where id > 1);
「id为null」
explain select * from student union select * from student where id > 1;
因为「union会对结果去重,内部创建了一个 <union1,2> 名字的临时表,把查询 1 和查询 2 的结果集都合并到这个临时表中,利用唯一键进行去重,这种情况下查询 id 就为 NULL」。
在之前《索引基本原理》 中提到InnoDB索引有聚簇索引和辅助索引。
由上图可知:「通过辅助索引无法直接定位行记录,通常情况下,需要扫两遍索引树。先通过辅助索引定位主键值,然后再通过聚簇索引定位行记录,即回表查询」。性能比扫一遍索引树低。
索引覆盖:「只需要在一棵索引树上就能获取SQL所需的所 有列数据,无需回表,速度更快」
覆盖索引形式:,搜索的索引键中的字段恰好是查询的字段
实现索引覆盖最常见的方法就是:将被查询的字段,建立到组合索引。
在之前《索引基本原理》 中提到组合索引的概念,在组合索引的使用中最关键的就是最左前缀原则。
「组合索引使用时遵循最左前缀原则,最左前缀顾名思义,就是最左优先,即查询中使用到最左边的列, 那么查询就会使用到索引,如果从索引的第二列开始查找,索引将失效」。
MySQL查询支持filesort和index两种方式的排序,
「使用index方式的排序的场景」
ORDER BY 子句索引列组合满足索引最左前列
explain select id from user order by id; //对应(id)、(id,name)索引有效
WHERE子句+ORDER BY子句索引列组合满足索引最左前缀
#对应(age,name)组合索引
explain select id from user where age=18 order by name;
「使用filesort方式的排序的场景」
对索引列同时使用了ASC和DESC
#对应(age,name)组合索引
explain select id from user order by age asc,name desc;
WHERE子句和ORDER BY子句满足最左前缀,但where子句使用了范围查询(例如>、<、in 等)
#对应(age,name)组合索引
explain select id from user where age>10 order by name;
ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前缀
#对应(age,name)组合索引
explain select id from user order by name;
使用了不同的索引,MySQL每次只采用一个索引,ORDER BY涉及了两个索引
#对应(name)、(age)两个索引
explain select id from user order by name,age;
WHERE子句与ORDER BY子句,使用了不同的索引
#对应(name)、(age)索引
explain select id from user where name='tom' order by age;
WHERE子句或者ORDER BY子句中索引列使用了表达式,包括函数表达式
#对应(age)索引
explain select id from user order by abs(age);
filesort有两种排序算法:双路排序和单路排序。
如果Explain分析SQL时Extra属性显示Using filesort,表示使用了filesort排序方式,需要优化。如果Extra属性显示Using index时,表示覆盖索引,所有操作在索引上完成。