MySQL高级篇之单表索引优化实战

单表优化

本篇主要讲解Mysql索引的单表优化实战
建立表student
例子：查询年龄大于18的大学生并将结果按照id排序

• 1，查询SQL

select * from students where age>18 and type=‘大学生’ order by id

• 2，分析这条SQL
  Explain
  select * from students where age>18 and type=‘大学生’ order by id
  
  索引类型为all，属于最不理想的结果（system>const>eq_ref>ref>range>index>all索引类型性能由高到低的排序）
  key:因为我们还没有建立索引，所以这里没有使用到索引
  rows：执行该sql预计会查询7行
  Extra：using filesort 使用了文件排序 ，同样很不理想

• 3，给这几个字段加上索引

3.1 给这三个字段加上索引
create index ind1_age_type_id on students(age,type,id);

3.2再次分析这条sql

Explain
select * from students where age>18 and type=‘大学生’ order by id
对比未建立索引的优缺点：
优点：type由之前的all变为了range，rows由之前的7行变成了现在的4行
不足：extra中仍然存在using filesort这个文件排序
原因：这是因为当索引类型为range范围索引时，其右边的索引会失效，所以刚才的sql其实只有age这个索引成功了，type和id索引失效了。

3.3 重新考虑建立索引

删除之前的索引：
drop index ind1_age_type_id on students
结合上面范围索引的原则，我们重新建立索引
alter table students add index ind_type_id_age(type,id,age)

3.4重新分析这条sql

Explain
select * from students where age>18 and type=‘大学生’ order by id

可以看到相比上个索引优缺点：
优点：type由range变成了ref，extra中文件排序消失了
缺点：索引的长度增加了，这也是因为使用了三个索引值，才导致索引键长度增加了