Chinaunix

标题: slow.log中记录的全是一条语句，请教优化方法 [打印本页]

作者: scc_166 时间: 2008-10-16 14:12
标题: slow.log中记录的全是一条语句，请教优化方法
发现全是
select id,title,content,uid,url,count(uid) from data where statue=1 group by uid order by sr desc

表结构：
create table data (...);
alter table data add key (uid)
alter table data add key (sr)
alter table data add key (statue)

tmp_table_size = 96M

加上上面的索引后还是没什么用.

还有没有别的查询语句可以比这个更快的呢？

作者: yueliangdao0608 时间: 2008-10-16 14:16
你的这个索引加的不对吧？

作者: scc_166 时间: 2008-10-16 14:27
用
alter table data add key(statue,uid,sr)
替换也是一样的慢

作者: ruochen 时间: 2008-10-16 14:33
status字段有哪些可选值？

作者: ruochen 时间: 2008-10-16 14:34
group by order by的很难优化

作者: ruochen 时间: 2008-10-16 14:35
考虑在uid和status上做index

作者: 猪知猪之道 时间: 2008-10-16 19:17

原帖由 ruochen 于 2008-10-16 14:33 发表
status字段有哪些可选值？

估计不是1就是0 重复的值太多了吧，

explain 下结果看看需要读取多少行

作者: scc_166 时间: 2008-10-16 22:09
对 statue中的确是0和1

explain 结果显示有2030行

暂时通过更改表结构来使用负载也降低很多

请问除了更改表结构外还有什么好的方法吗?

作者: 猪知猪之道 时间: 2008-10-16 22:58

原帖由 scc_166 于 2008-10-16 22:09 发表
对 statue中的确是0和1

explain 结果显示有2030行

暂时通过更改表结构来使用负载也降低很多

请问除了更改表结构外还有什么好的方法吗?

sql 要效率高，主要的就是索引，要好好掌握。像你这样只有 0和1的数据列上用索引是没有什么效果的

作者: scc_166 时间: 2008-10-16 23:10
谢谢各位的解答.

学习了

作者: yueliangdao0608 时间: 2008-10-17 07:13

原帖由 猪知猪之道 于 2008-10-16 22:58 发表

sql 要效率高，主要的就是索引，要好好掌握。像你这样只有 0和1的数据列上用索引是没有什么效果的

你错了，效果是有的~

作者: 猪知猪之道 时间: 2008-10-17 15:14

原帖由 yueliangdao0608 于 2008-10-17 07:13 发表

你错了，效果是有的~

表达错误，我更正一下，我意思是说，效果不怎么样了

作者: jb96_xlwang 时间: 2008-10-19 20:44
那个语句可以用下面的来替代，
速度方面应该有些提高。

select A.id,title,content,A.uid,url,B.CC From
data A,
(select uid,count(*) as CC , min(id) mid
From data where status =1
group by uid ) B
where A.id= B.mid
Order by A.sr ;

另外试试下面哪个索引起作用，
create index stall on data(status,uid,id) ;
create index uidsta on data(uid,id,status) ;

我简单构造的2万条数据是第二个索引有效，可能是数据问题。
现在看执行时间从 2秒到 0.3 秒了。

+----+-------+-----------+------+-------+------------+
| id | title | content | uid  | url | count(uid) |
+----+-------+-----------+------+-------+------------+
| 17 | t5 | content15 | us2  | url41 |    49152 |
| 49 | t13 | content23 | us3  | url14 |    16384 |
|  1 | t1 | content11 | us1  | url11 |    131072 |
+----+-------+-----------+------+-------+------------+
3 rows in set (2.21 sec)

mysql>
mysql>
mysql> select A.id,title,content,A.uid,url,B.CC From
-> data A,
-> (select uid,count(*) as CC , min(id) mid
-> From data where status =1
-> group by uid ) B
-> where A.id= B.mid
-> Order by A.sr desc ;
+----+-------+-----------+------+-------+--------+
| id | title | content | uid  | url | CC    |
+----+-------+-----------+------+-------+--------+
| 17 | t5 | content15 | us2  | url41 |  49152 |
| 49 | t13 | content23 | us3  | url14 |  16384 |
|  1 | t1 | content11 | us1  | url11 | 131072 |
+----+-------+-----------+------+-------+--------+
3 rows in set (0.38 sec)

mysql>
mysql>
mysql>
mysql>
mysql> explain select id,title,content,uid,url,count(uid)
-> from data where status=1
-> group by uid order by sr desc ;
+----+-------------+-------+-------+---------------+--------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type  | possible_keys | key | key_len | ref  | rows | Extra                                     |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE    | data  | index | stall       | uidsta | 54    | NULL | 213204 | Using where; Using temporary; Using filesort |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

mysql>
mysql>
mysql> explain select A.id,title,content,A.uid,url,B.CC From
-> data A,
-> (select uid,count(*) as CC , min(id) mid
-> From data where status =1
-> group by uid ) B
-> where A.id= B.mid
-> Order by A.sr desc ;
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+---------------------------------+
| id | select_type | table    | type | possible_keys | key    | key_len | ref | rows | Extra                         |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+---------------------------------+
|  1 | PRIMARY    | <derived2> | ALL | NULL       | NULL | NULL | NULL  |    3 | Using temporary; Using filesort |
|  1 | PRIMARY    | A       | eq_ref | PRIMARY    | PRIMARY | 8    | B.mid |    1 |                               |
|  2 | DERIVED    | data    | index  | stall       | uidsta  | 54    | NULL  | 213204 | Using where; Using index       |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+---------------------------------+
3 rows in set (0.32 sec)

mysql> show create table data \G;
*************************** 1. row ***************************
   Table: data
Create Table: CREATE TABLE `data` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(20) DEFAULT NULL,
  `content` varchar(200) DEFAULT NULL,
  `uid` varchar(10) DEFAULT NULL,
  `url` varchar(100) DEFAULT NULL,
  `status` varchar(10) DEFAULT NULL,
  `sr` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `stall` (`status`,`uid`,`id`),
  KEY `uidsta` (`uid`,`id`,`status`)
) ENGINE=InnoDB AUTO_INCREMENT=851966 DEFAULT CHARSET=cp932
1 row in set (0.00 sec)

ERROR:
No query specified

[ 本帖最后由 jb96_xlwang 于 2008-10-19 20:51 编辑 ]

欢迎光临 Chinaunix (http://bbs.chinaunix.net/)