大家看看这条SQL还有优化的余地么？

枫影谁用了

原帖由 老肥羊 于 2009-4-3 17:20 发表



看你上上貼懷疑limit麼

不是怀疑，是肯定
条件不是单一的。

所以我说看这些图证明不了什么，存储的数据都不同，数据不同，运行环境不同

[ 本帖最后由 枫影谁用了 于 2009-4-3 17:33 编辑 ]

老肥羊

原帖由 枫影谁用了 于 2009-4-3 17:23 发表

不是怀疑，是肯定
条件不是单一的。

所以我说看这些图证明不了什么，存储的数据都不同，数据不同，运行环境不同

要這麼說我也沒辦法了

不過表結構等能模擬的基本已經模擬了

枫影谁用了

原帖由 老肥羊 于 2009-4-3 17:35 发表



要這麼說我也沒辦法了

不過表結構等能模擬的基本已經模擬了

看这个：

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-10 00:00:00' order by id  limit 10\G;  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: aaa
         type: index
possible_keys: showtime
          key: id
      key_len: 8
          ref: NULL
         rows: 23
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-02 00:00:00' order by id  limit 10\G;  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: aaa
         type: range
possible_keys: showtime
          key: showtime
      key_len: 8
          ref: NULL
         rows: 10
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

老肥羊

原帖由 枫影谁用了 于 2009-4-3 17:42 发表


看这个：

show index看看

枫影谁用了

mysql> show index from aaa \G;
*************************** 1. row ***************************
       Table: aaa
  Non_unique: 1
    Key_name: id
Seq_in_index: 1
Column_name: id
   Collation: A
Cardinality: 1500243
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
*************************** 2. row ***************************
       Table: aaa
  Non_unique: 1
    Key_name: showtime
Seq_in_index: 1
Column_name: showtime
   Collation: A
Cardinality: 100016
    Sub_part: NULL
      Packed: NULL
        Null:
  Index_type: BTREE
     Comment:
2 rows in set (0.00 sec)

ERROR:
No query specified

mysql>

老肥羊

原帖由 枫影谁用了 于 2009-4-3 17:57 发表
mysql> show index from aaa \G;
*************************** 1. row ***************************
       Table: aaa
  Non_unique: 1
    Key_name: id
Seq_in_index: 1
Column_name: id
   Collati ...

刪除掉showtime的index

枫影谁用了

原帖由 老肥羊 于 2009-4-3 17:58 发表



刪除掉showtime的index

那有什么意义！还没明白问题在那里？

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-10 00:00:00' order by id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: aaa
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1500457
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-02 00:00:00' order by id \G;  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: aaa
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1500457
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-02 00:00:00' order by id \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: aaa
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1500457
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-10 00:00:00' order by id \G;  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: aaa
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1500457
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-10 00:00:00' order by id limit 10 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: aaa
         type: index
possible_keys: NULL
          key: id
      key_len: 8
          ref: NULL
         rows: 10
        Extra: Using where
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> show index from aaa;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| aaa   |          1 | id       |            1 | id          | A         |     1500243 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.01 sec)

mysql>

老肥羊

原帖由 枫影谁用了 于 2009-4-3 18:03 发表

那有什么意义！还没明白问题在那里？

mysql> explain select * from  aaa  where showtime > '2009-01-01 00:00:00' and showtime < '2009-01-10 00:00:00' order by id \G;
*************************** ...

[ 本帖最后由 老肥羊 于 2009-4-3 18:46 编辑 ]

simeiren

哎
没有索引是要扫描全表的；
虽然看不到 Using filesort 了，
但是更慢！
看看 rows 那可是 3百多万条 啊，而有索引才 1 万多条。

mysql> explain SELECT * FROM `notice_message` WHERE submit_date > '2009-03-05 00:00:00' AND submit_date < '2009-03-06 00:00:00' ORDER BY id DESC LIMIT  0,5000;
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table                   | type  | possible_keys | key        | key_len | ref    | rows        | Extra            |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE        | notice_message | index | NULL              | PRIMARY | 4           | NULL | 3805346 | Using where |
+----+-------------+----------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set (0.00 sec)

[ 本帖最后由 simeiren 于 2009-4-3 18:25 编辑 ]

iamyess

看！！这个ID又出现了，应老肥羊的要求上来回答，用中文吧，怕又有人理解错了


我没仔细看二位争论，但是就这句而言，没法优化连filesort没有，因为 range + order by 是不行的
SELECT * FROM `notice_message` WHERE submit_date > '2009-03-05 00:00:00' AND submit_date < '2009-03-06 00:00:00' ORDER BY id DESC LIMIT xxxx

老肥羊 把其他索引删掉，剩下主键当然不会有filesort，因为没索引去用于条件检索，很可能效率比那带filesort还要差很多（看数据具体分布情况）

所以，还不如用submit_date的索引出现filesort好，至于 index(submit_date,id)有没作用，我记得是没有，试试吧，如果这也没有想其他形式或办法