SQL优化的七个方法，你会哪个？( 二 ) _SQL优化

不使用索引情况：

文章插图
图片
新建联合索引：name,dep_id,age

#创建联合索引CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age);#查询当前索引show INDEX from employee#删除索引DROP INDEX idx_name_dep_id_age ON employee;

如果order by字段全部使用升序排序或者降序排序，则都会走索引.

文章插图
图片

#有索引情况顺序Explain select name,dep_id,age from employee orderby name,dep_id,age ;#有索引情况倒序Explain select name,dep_id,age from employee orderby name desc,dep_id desc,age desc;

但是如果一个字段升序排序，另一个字段降序排序，则不会走索引，explain的extra信息显示的是Using index, Using filesort.

文章插图
图片

#有索引情况有的顺序,有的倒序Explain select name,dep_id,age from employee orderby name desc,dep_id asc,age desc;

如果要优化掉Using filesort，此时我们可以再创建一个联合索引，即name按倒序，dep_id按升序创建索引，就可以解决。

文章插图
图片
注意的是虽然我已经创建了覆盖这些列的联合索引 idx_name_dep_id_age2 ，但 MySQL 优化器仍然可能会决定使用文件排序（filesort）来执行这个顺序的排序操作。
在内存中无法容纳整个结果集时， MySQL 将结果集存储在临时文件中并对其进行排序。这并不一定意味着性能问题，但是可能会影响查询的执行时间，尤其是当处理大量数据时。
总结：

根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
尽量使用覆盖索引
多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
如果不可避免出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort_buffer_size（默认256k）

四、group by优化
先删除全部的索引（保留主键id）

#删除索引DROP INDEX idx_name_dep_id_age2 ON employee;#无索引情况Explain select name,dep_id,age ,count(*)from employee groupby name,dep_id,age ;

无索引情况下，分组,出现filesort,type为All出现了全表扫描。

文章插图
图片
新建联合索引， name,dep_id，age再观察。

#创建联合索引CREATE INDEX idx_name_dep_id_age ON employee (name, dep_id, age);#有索引情况Explain select name,dep_id,age,count(*)from employee groupby name,dep_id,age ;

可见用到了索引

文章插图
图片
总结：

在分组操作时，可以通过索引来提高效率
分组操作时，索引的使用也是满足最左前缀法则的

如索引为idx_user_pro_age_stat ，则句式可以是select ... where profession order by age，这样也符合最左前缀法则
五、limit优化
语法复习：
#0表示起始位置，10表示每一页展示的数据。select * from student_info limit 0,10;
这条查询执行的速度非常快，但是如果我们将起始位置设置为100000呢？
limit分页查询在大数据量的时候，查询效率同样会非常的慢，例如一个常见又非常头疼的问题就是limit 2000000,10 此时需要MySQL排序前200010条记录，仅仅返回200000-2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化方案：一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化
例如：利用主键id,先获取要查询的10是个id.
explain select *from student_info t,(select id from student_info order by id limit 2000000,10) awhere t.id = a.id;