Chinaunix首页 | 论坛 | 博客
  • 博客访问: 300566
  • 博文数量: 47
  • 博客积分: 1667
  • 博客等级: 上尉
  • 技术积分: 686
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-06 16:20
文章分类

全部博文(47)

文章存档

2013年(20)

2012年(20)

2011年(7)

分类: Mysql/postgreSQL

2012-10-25 11:51:49

昨天看到慢查询里面
SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15'  AND `status`=99 AND `id`<'201846' ORDER BY id DEsc ;

explain 后
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+
| id | select_type | table   | type | possible_keys                               | key                 | key_len | ref         | rows  | Extra       |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+
|  1 | SIMPLE      | v9_news | ref  | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 56502 | Using where |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-------------+

把catid和status加到order by
mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15'  AND `status`=99 AND `id`<'201846' ORDER BY catid,status,id DEsc limit 1;
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+
| id | select_type | table   | type | possible_keys                               | key                 | key_len | ref         | rows  | Extra                       |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+
|  1 | SIMPLE      | v9_news | ref  | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 56502 | Using where; Using filesort |
+----+-------------+---------+------+---------------------------------------------+---------------------+---------+-------------+-------+-----------------------------+

虽然有filesort但是这样执行的速度快很多,
###
原来是catid 和id 都是smallint 和int,是以数值定义的,但是''包含的是属于字符串类型,所以会有filesort的出现


mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`  WHERE `catid` = 15  AND `status`=99   ORDER BY catid,status,id  limit 1;
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
| id | select_type | table   | type | possible_keys                       | key                 | key_len | ref         | rows   | Extra       |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
|  1 | SIMPLE      | v9_news | ref  | catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 254418 | Using where |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`  WHERE `catid` = '15'  AND `status`=99   ORDER BY catid,status,id  limit 1;
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-----------------------------+
| id | select_type | table   | type | possible_keys                       | key                 | key_len | ref         | rows   | Extra                       |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-----------------------------+
|  1 | SIMPLE      | v9_news | ref  | catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 3       | const,const | 254418 | Using where; Using filesort |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------+--------+-----------------------------+

####




 和其他人讨论了下,是因为虽然选择idx_catid_status_id  这个索引,但是根本没用这个索引中的id这个键,这点从key_len 为3 可以看出来,


把语句改成mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`  force index (idx_catid_status_id) WHERE `catid` = '15'  AND `status`=99 AND `id`<'201846' ORDER BY id DEsc limit 1;
+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+
| id | select_type | table   | type  | possible_keys       | key                 | key_len | ref  | rows  | Extra                       |
+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | v9_news | range | idx_catid_status_id | idx_catid_status_id | 6       | NULL | 56502 | Using where; Using filesort |
+----+-------------+---------+-------+---------------------+---------------------+---------+------+-------+-----------------------------+
会发现强制带上id这个键,
原来优化器算法错了,这个是在5.5.9上去得到的结果

又安装新版本的5.5.28后发现
mysql> explain SELECT sql_no_cache * FROM `news`.`v9_news`   WHERE `catid` = '15'  AND `status`=99 AND `id`<'201846' ORDER BY id DEsc limit 1;
+----+-------------+---------+-------+---------------------------------------------+---------------------+---------+------+-------+-------------+
| id | select_type | table   | type  | possible_keys                               | key                 | key_len | ref  | rows  | Extra       |
+----+-------------+---------+-------+---------------------------------------------+---------------------+---------+------+-------+-------------+
|  1 | SIMPLE      | v9_news | range | PRIMARY,catid_2,catid_3,idx_catid_status_id | idx_catid_status_id | 6       | NULL | 56548 | Using where |
+----+-------------+---------+-------+---------------------------------------------+---------------------+---------+------+-------+-------------+
1 row in set (0.00 sec)

mysql> select version();
+------------+
| version()  |
+------------+
| 5.5.28-log |
+------------+
1 row in set (0.00 sec)

优化器可以自动识别,看来新版本的优化器的算法是经过改良的

阅读(2517) | 评论(0) | 转发(1) |
0

上一篇:监控memcached

下一篇:fastdfs

给主人留下些什么吧!~~