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

simeiren

mysql> desc notice_message;
+-------------+------------------+------+-----+---------+----------------+
| Field              | Type             | Null | Key | Default | Extra                         |
+-------------+------------------+------+-----+---------+----------------+
| id                   | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| member_id    | int(11) unsigned | NO   | MUL | 0         |                           |
| message       | char(255)            | NO   | MUL |            |                           |
| submit_date  | datetime             | NO   | MUL |            |                           |
+-------------+------------------+------+-----+---------+----------------+

mysql> show index from notice_message;
+----------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table          | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+----------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| notice_message |          0 | PRIMARY            |            1 | id                  | A         |     3790729 |     NULL | NULL   |      | BTREE      |         |
| notice_message |          1 | member_id        |            1 | member_id   | A         |     1263576 |     NULL | NULL   |      | BTREE      |         |
| notice_message |          1 | submit_date      |            1 | submit_date | A         |     3790729 |     NULL | NULL   |      | BTREE      |         |
| notice_message |          1 | message           |            1 | message       | A         |     3790729 |       20   | NULL   |      | BTREE      |         |
| notice_message |          1 | submit_date_id |            1 | submit_date | A         |     3790729 |     NULL | NULL   |      | BTREE      |         |
| notice_message |          1 | submit_date_id |            2 | id                  | A         |     3790729 |     NULL | NULL   |      | BTREE      |         |
+----------------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

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 | range | submit_date,submit_date_id | submit_date | 8            | NULL | 13762 | Using where; Using filesort |
+----+-------------+----------------+-------+----------------------------+-------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)
这里为什么用不到 聚合索引 submit_date_id ，使用 use index(submit_date_id) 也用不到的！

以前是
SELECT * FROM `notice_message`  WHERE  submit_date LIKE '2009-03-05%'  ORDER BY id DESC LIMIT 7500,50;
但是 LIKE 根本用不到索引，全表扫描，十几分钟。才换成上面的，但排序还是用不到索引！

老肥羊

凭感觉随便说一句:
索引太多了

simeiren

凭感觉随便说一句:
索引太多了

恩，强制使用聚合索引还是用到文件排序的：
mysql> explain SELECT * FROM `notice_message` use index(submit_date_id ) 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 | range | submit_date_id | submit_date_id | 8            | NULL | 13762 | Using where; Using filesort |
+----+-------------+----------------+-------+----------------------------+-------------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

老肥羊

原帖由 simeiren 于 2009-4-2 17:28 发表


恩，强制使用聚合索引还是用到文件排序的：
mysql> explain SELECT * FROM `notice_message` use index(submit_date_id ) WHERE submit_date > '2009-03-05 00:00:00' AND submit_date < '2009-03-06 00:0 ...

说了你这张表的索引建多了

除了PK和PK的内置索引,其它的都删掉吧

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

simeiren

说了你这张表的索引建多了
除了PK和PK的内置索引,其它的都删掉吧

全删掉了
还是有Using filesort
用不用 Using filesort  排序跟索引多少完全没关系！

老肥羊

原帖由 simeiren 于 2009-4-3 15:22 发表


全删掉了
还是有Using filesort
用不用 Using filesort  排序跟索引多少完全没关系！

我也弄了些數據測了測

大概一百萬條吧

按你的SQL

沒有filesort的

不過我用的是innodb

有圖有真相

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

枫影谁用了

去掉limit看看。这些也都证明不了什么。limit 1000,100000结果完全不一样。

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

ERROR:
No query specified

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

ERROR:
No query specified

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

老肥羊

原帖由 枫影谁用了 于 2009-4-3 17:02 发表
去掉limit看看。这些也都证明不了什么。limit 1000,100000结果完全不一样。

有圖有真相

枫影谁用了

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



有圖有真相

晕。。你跟我证明什么

老肥羊

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

晕。。你跟我证明什么

看你上上貼懷疑limit麼