sql调优

sql关键字的执行顺序

1. 关键字的顺序不可以颠倒

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

2. sql关键字执行顺序

FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

3. where子句中不能使用聚合函数作为表达式，聚合函数如下：

COUNT(*)——统计元组个数。
COUNT([DISTINCT|ALL]<列名>)——统计一列中值的个数。
SUM([DISTINCT|ALL]<列名>)——计算一列值的总和（此列必须是数值类型）。
AVG([DISTINCT|ALL]<列名>)——计算一列的平均值（同上）。
MAX([DISTINCT|ALL]<列名>)——求一列值中的最大值。
MIN([DISTINCT|ALL]<列名>)——求一列值中的最小值。

4. where后面不能使用聚合函数的原因：

聚合函数是针对结果集进行的，但是where条件，并不是在查询出结果集之后运行的，所以主函数放在where语句中会出现错误。

而having不一样，having是针对结果集做的筛选的，所以我们一般把主函数放在having中，用having来代替where。having一般是跟在group by 后面的。

5. 当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：

1. 执行where xx对全表数据做筛选，返回第1个结果集。
2. 针对第1个结果集使用group by分组，返回第2个结果集。
3. 针对第2个结果集中的每1组数据执行select xx，有几组就执行几次，返回第3个结果集。
4. 针对第3个结集执行having xx进行筛选，返回第4个结果集。
5. 针对第4个结果集排序。

大数据量分页查询优化

问题描述：

项目中，一张表里数据有几百万条，表有索引。在后台管理系统中，进行列表分页查询时，分页页数越大，查询速度越慢，还会报出慢查询，如何对其进行优化呢？

原因分析：

假设查询第100000页数据，每页10条，则分页查询的语句是

select * from table where … limit 1000000,10。

在mysql的底层处理中，对于上述的sql语句处理，是先排序，然后取出1000010条数据，然后舍去前100000条数据，返回10条数据，这就是查询慢的原因。即使有索引，在大数据量上，效率也会降低很多。

解决方案：

方案一:

对sql语句进行下面的优化:

select * from table where  ...  and id>1000000 limit 10;

这样的话,sql就不会把前100000条也查出来，然后排序了，而是直接查询第100000条数据，我们看其执行时间:

方案二:

对sql进行如下的优化:

select * from (select id from table limit 1000000,10) b, table a where a.id=b.id

这个sql是先只查询id出来，然后再拿id关联本表全部数据，进行内连接查询。id查询比较快，然后内连接的时候，只从全部数据中找条件满足的，所以效率很高，看执行时间:

可以看出,方案一效率是最快的，方案二其次，原始的sql查询效率是最慢的。