sql求优化

Question

本帖最后由 miraclestar 于 2013-09-13 18:24:48 编辑

Answer 1

贴出
explain select 
user_id,
count(*)as num,
sum(case when status=2 then 1 else 0 end),
sum(dup)
 from upload
 where upload_type = 'ct' and begin_date >= '2013-09-12'
 and begin_date <'2013-09-13'
 group by user_id
 order by num desc 
limit 50; 
show index from upload;以供分析。

Answer 2

按照1楼要explain，和show index。我想：
begin_date必须有索引。

Answer 3

begin_date也有索引的，忘了写。
+----+-------------+---------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows | Extra                                        |
+----+-------------+---------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
|  1 | SIMPLE      | mps_uploading | index | begin_date    | user_id | 4       | NULL | 9670 | Using where; Using temporary; Using filesort | 
+----+-------------+---------------+-------+---------------+---------+---------+------+------+----------------------------------------------+
谢谢大神~

Answer 4

索引
+---------------+------------+------------+--------------+-------------+-----------+-------------+
| Table         | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality |
+---------------+------------+------------+--------------+-------------+-----------+-------------+
| mps_uploading |          0 | PRIMARY    |            1 | id          | A         |     3176792 |
| mps_uploading |          1 | user_id    |            1 | user_id     | A         |      529465 |
| mps_uploading |          1 | begin_date |            1 | begin_date  | A         |     3176792 |
| mps_uploading |          1 | status     |            1 | status      | A         |          17 |

Answer 5

走begin_date索引，速度会快很多，但通过你的explain结果看实际走的是：user_id，所以要修改sql语句：select 
user_id,
count(*)as num,
sum(case when status=2 then 1 else 0 end),
sum(dup)
from upload
where 
user_id in(
select user_id 
from upload 
where 
upload_type = 'ct' 
and begin_date >= '2013-09-12'
and begin_date <'2013-09-13'
)
group by user_id
order by num desc 
limit 50; 

Answer 6

如果这个提高速度的话，再建立一个联合索引（begin_date ,upload_type, user_id），这样里边的查询可以全部走索引，而不用再去根据索引查了。

Answer 7

贴出
explainselect 
user_id,
count(*)as num,
sum(case when status=2 then 1 else 0 end),
sum(dup)
from upload
where 
user_id in(
select user_id 
from upload 
where 
upload_type = 'ct' 
and begin_date >= '2013-09-12'
and begin_date <'2013-09-13'
)
group by user_id
order by num desc 
limit 50; 

Answer 8

sort_buffer_size - Used for ORDER BY, GROUP BY, SELECT DISTINCT, UNION DISTINCT
  – Monitor Sort_merge_passes < 1-2 an hour optimal
  – Usually a problem in a reporting or data warehouse database大意是：
sort_buffer_size这个参数很重要，用于order by,group by,distinct,union。
监控Sort_merge_passes参数（show status like 'Sort_merge_passes';），比较好的状态：每个小时有2个。今天在官方文档上看到这个，可能你用得着，你可能要把sort_buffer_size调大一些！！！

Answer 9

+----+--------------------+---------------+----------------+--------------------+---------+---------+------+------+----------------------------------------------+
| id | select_type        | table         | type           | possible_keys      | key     | key_len | ref  | rows | Extra                                        |
+----+--------------------+---------------+----------------+--------------------+---------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | mps_uploading | index          | NULL               | user_id | 4       | NULL |  250 | Using where; Using temporary; Using filesort | 
|  2 | DEPENDENT SUBQUERY | mps_uploading | index_subquery | user_id,begin_date | user_id | 4       | func |    5 | Using where                                  | 
+----+--------------------+---------------+----------------+--------------------+---------+---------+------+------+----------------------------------------------+

Answer 10

lhs295988029说的是有可能的，你加一个字段超出了BUFFER限制用到了FILESORT，试试修改MySQL的配置参数解决。