GroupBy和索引的关系

默认情况下，mysql对group by col1,col2 字段进行排序，这与order by col1 col2类似，如果显式的堆一个包含相同列的order by 子句，实际上没有什么影响，如果查询group by 但是用户想要避免不必要的排序，则可以指定order by null.

优化分页查询

一般查询是，通过创建覆盖索引能够比较好的提高性能，一个常见的问题就是limit 1000,20 查询出1020行，但是返回的是1000到1020条数据，其他数据都进行抛弃了

1.使用主键回表查询原表的记录,下面我们发现直接查询是进行全表查询，而使用主键关联回表查询可以提高查询效率

mysql>explain select film_id, description from film order by title limit 50,5 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: film

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 1000

Extra: Using filesort

1 row in set (0.00 sec)

mysql>explain select a.film_id,a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id \G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: <derived2>

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 55

Extra: NULL

*************************** 2. row ***************************

id: 1

select_type: PRIMARY

table: a

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 2

ref: b.film_id

rows: 1

Extra: NULL

*************************** 3. row ***************************

id: 2

select_type: DERIVED

table: film

type: index

possible_keys: NULL

key: idx_title

key_len: 767

ref: NULL

rows: 1000

Extra: Using index

3 rows in set (0.00 sec)

2.记录上一次的某个位置，用记录上一页的最后一行的字段，在使用limit n ,

mysql>explain select * from payment where rental_id<15640 order by rental_id desc limit 10\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: payment

type: range

possible_keys: fk_payment_rental

key: fk_payment_rental

key_len: 5

ref: NULL

rows: 8043

Extra: Using index condition

1 row in set (0.00 sec)

使用排序rental_id 记录上一页的最后位置，在根据这个位置过滤且使用limit n，可以有效提高查询的效率，但是在rental_id有大量重复的情况下，这种优化会丢失数据。

让groupby使用索引而不创建临时表，使用索引的前提条件是：所有GROUPBY列引用同一索引的属性，并且索引按顺序保存其关键字（B-树索引，不是HASH索引）至于DISTINCT和GROUPBY哪个效率更高？理论上DISTINCT *** 作只需要找出所有不同的值就可以了。而GROUPBY *** 作还要为其他聚集函数进行准备工作。从这一点上将，GROUPBY *** 作做的工作应该比DISTINCT所做的工作要多一些。但是实际上，DISTINCT *** 作，它会读取了所有记录GROUPBY需要读取的记录数量与分组的组数量一样多，比实际存在的记录数目要少很多。

易客CRM之前的版本中有一个报表是按月统计销售情况，最近有个客户想按周统计销售情况。按月统计的Sql语句比较好写，sql语句如下：

SELECT DATE_FORMAT(ec_salesorder.duedate,’%Y-%m’) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m，也就是把duedate日期以月的形式显示，然后groupby，那么按周如何统计呢？

搜了一下mysql的manual，在这里找到一个解决方法，通过mysql的week函数来做，sql语句如下：SELECT WEEK(ec_salesorder.duedate) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m，这个方法有个缺陷，不能显示年份，仅仅靠一个周数不方便查看统计信息。

继续研究mysql manual，在DATE_FORMAT函数介绍发现2个格式符和周有点关系：

%X Year for the week where Sunday is the first day of the week, numeric, four digitsused with %V

%x Year for the week, where Monday is the first day of the week, numeric, four digitsused with %v

把上面的Sql语句中改成：

SELECT DATE_FORMAT(ec_salesorder.duedate,’%x %v’) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m

显示的结果如下：

m total so_count

2009 11 10000.00 3

2009 12 44000.00 5

如果周日为一周的第一天，那么sql语句应该为:

SELECT DATE_FORMAT(ec_salesorder.duedate,’%X %V’) as m, sum(ec_salesorder.total) as total, count(*) as so_count FROM ec_salesorder GROUP BY m ORDER BY m

结果应该没错，不出意外，易客CRM下个版本将增加按周统计销售情况的报表。

---