mysql数据库sql优化（四）看这里之group by优化与分页查询优化

目录

group by优化

优化分页查询

优化思路一

优化思路二

---

group by优化

①group by使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。

②group by先排序再分组，遵照索引建立的最佳左前缀法则

③当无法使用索引列增大max_length_for_sort_data和sort_buffer_size参数的设置

④where效率高于having，能写在where限定条件就不要写在having中

⑤减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by group by distinct这些语句较为耗费cpu，数据库的CPU资源是极其宝贵的

⑥包含了order by group by distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则sql会很慢。

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提供性能。一个常见又非常头疼的问题就是limit 2000000,10.此时需要mysql排序前2000000记录，仅仅返回200000-200010的记录，其他记录丢弃，查询排序的代价非常大。

EXPLAIN SELECT SQL_NO_CACHE * FROM student LIMIT 2000000,10;

优化思路一

在索引上完成排序分页，最后根据主键关联回表查询索需要的其他列内容。

EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 200000,10) a WHERE t.id=a.id;

优化思路二

该方案 适用于主键自增的表，可以把limit查询转换成某个位置的查询

EXPLAIN SELECT * FROM student WHERE id>2000000 LIMIT 10;