MySQL查询性能优化

假设MySQL按照查询中的表顺序进行关联操作，我们则可以使用下面的伪代码表示MySQL将如何完成这个查询：

特定类型的查询优化

优化COUNT()查询

COUNT()可以统计某个列值的数量，也可以统计行数。在统计列值时要求列值是非空的(不统计NULL)。如果在COUNT()的括号中指定了列或者列的表达式，则统计的就是这个表达式有值的结果数（而非NULL）。

COUNT()的另一个作用是统计结果集的行数，当MySQL确认括号内的表达式值不可能为空时，实际上就是在统计行数。最简单的就是当我们使用COUNT()的时候，这种情况下通配符 并不会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数。

一种常见的错误就是，在括号内指定了一个列却希望统计结果集的行数。如果希望知道的是结果集的行数，最好使用count(*)，这样写意义清晰，性能也会很好。

MYISAM只有当没有任何WHERE条件的COUNT(*)才非常快，因为此时无须实际地计算表的行数。MySQL可以利用存储引擎的特性直接获得这个值。如果MySQL知道某列col不可能为NULL值，那么MySQL内部会将COUNT(col)表达式优化为COUNT(*）。当统计待WHERE子句的结果集行数时，MyISAM与其他数据库引擎没有太大区别。

优化关联查询

• 确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列C关联的时候，如果优化器的关联顺序是B、A，那么就不需要在B表的对应列上建上索引。没有用到的索引只会带来额外的负担。一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上创建索引。
• 确保任何的GROUP BY和ORDER BY 中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化这个过程。

优化子查询

尽可能使用关联查询代替子查询。

优化GROUP BY优化

针对以GROUP BY的优化 主要分为有索引和无索引两种情况。

当无法使用索引的时候，GROUP BY使用两种策略来完成分组工作：使用临时表或者文件排序来做分组，其实就是进行一次全表扫描筛选数据形成一个临时表，然后按照GROUP BY 指定的列进行排序。在这个临时表里面，对于每一个group的数据行来说是连续在一起的。完成排序之后，就可以发现所有的GROUPS，并可以执行聚合函数。所以，我们常常在explain后看到“Using temporary; Using filesort”。

如果没有通过 ORDER BY子句显示地指定排序列，当查询使用GROUP BY子句的时候，结果集会自动按照分组的字段进行排序。如果不关心结果集的顺序，而这种默认排序又导致了需要文件排序，则可以使用ORDER BY NULL，让MySQL不再进行文件排序。也可以在GROUP BY子句中直接使用DESC或者ASC关键字，使分组的结果集按需要的方向进行排序。

优化LIMIT分页查询

假设有如下的分页SQL语句：

SELECT * FROM table LIMIT offset , rows ;

这是一条典型的LIMIT语句，常见的使用场景是，某些查询返回的内容特别多，而客户端处理能力有限，希望每次只取一部分结果进行处理。

上述SQL语句的实现机制是：

1. 从“table”表中读取offset+rows行记录。
2. 抛弃前面的offset行记录，返回后面的rows行记录作为最终的结果。

这种实现机制存在一个弊端：虽然只需要返回rows行记录，但却必须先访问offset行不会用到的记录。对一张数据量很大的表进行查询时，offset值可能非常大，此时limit语句的效率就非常低了。

使用覆盖索引来优化：

尽可能使用索引覆盖扫描，确定需要返回行的主键等，然后再根据需要做一次关联操作再返回所需的列。通常此种优化都是使用子查询来实现。

比如我们有如下的SQL语句：

select * from student where score > 90 limit 1000,10 

我们就可以先利用覆盖索引查询速度快的优点先查询出对应分页段内的学号，然后再根据学号去做关联查询，第二步是直接使用主键做关联，也是非常快的。优化后的SQL如下：

select * from student as stu INNER JOIN (select id from student limit 1000,10) as tmp on stu.id = tmp.id;

确定分页起始值，减少扫描行数

我们可以记住上次取数据的位置，然后下次就可以直接从该位置开始扫描数据，然后取指定的长度。假设上次获取到的最后一个学生学号是：20180131200，则我们可以改写成如下SQL：

select * from student stu where stu.id > 20180131200 order by id limit 100 ;

上面的SQL首先使用主键进行排序，因为聚簇索引的特性，所以主键ID在索引树中本身已经有序存储了，所以此处的order by 非常快。然后再使用主键进行筛选 也是非常快的。

优秀网文：mysql大数据量之limit优化

MySQL查询性能优化

标签：大数   提前   group   校验   生命周期   条目   表达   src   不用