代码之家 › 专栏 › 技术社区 › Maximus

MySQL优化orderby日期查询

mysql

Maximus · 技术社区 · 14 年前

我尽力解决了以下两个简单的查询,但对于每10行结果集,它会扫描整个表或至少10行。目前books表中有20000行。

ALTER TABLE books ADD INDEX search_INX (`book_status`, `is_reviewed`,`has_image`,`published_date`)

mysql> EXPLAIN SELECT book_id FROM books ORDER BY published_date DESC LIMIT 10;
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-----------------------------+
| id | se ref  |lect_type | table | type  | possible_keys | key        | key_len | rows  | Extra                       |
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | books | index | NULL          | search_INX | 11      | NULL | 20431 | Using index; Using filesort | 
+----+-------------+-------+-------+---------------+------------+---------+------+-------+-----------------------------+

mysql> EXPLAIN SELECT book_id FROM books WHERE book_status='available' AND is_reviewed=true AND has_image=true ORDER BY published_date DESC LIMIT 10;
+----+-------------+-------+------+---------------+------------+---------+-------------------+-------+--------------------------+
| id | select_type | table | type  ref               || possible_keys | key        | key_len | rows  | Extra                    |
+----+-------------+-------+------+---------------+------------+---------+-------------------+-------+--------------------------+
|  1 | SIMPLE      | books | ref  | search_INX    | search_INX | 3       | const,const,const | 10215 | Using where; Using index | 
+----+-------------+-------+------+---------------+------------+---------+-------------------+-------+--------------------------+

mysql> EXPLAIN SELECT book_id FROM books WHERE book_status='available' AND is_reviewed=true AND has_image=true ORDER BY published_date DESC LIMIT 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: books
         type: ref
possible_keys: search_INX
          key: search_INX
      key_len: 3
          ref: const,const,const
         rows: 10215
        Extra: Using where; Using index
1 row in set (0.00 sec)

Create Table: CREATE TABLE `books` (
  `book_id` int(10) unsigned NOT NULL auto_increment,
  `has_image` bit(1) NOT NULL default '',
  `is_reviewed` bit(1) NOT NULL default '\0',
  `book_status` enum('available','out of stock','printing') NOT NULL default 'available',
  `published_date` datetime NOT NULL,
  PRIMARY KEY  (`book_id`),
  KEY `search_INX` (`is_reviewed`,`has_image`,`book_status`,`published_date`)
) ENGINE=InnoDB AUTO_INCREMENT=162605 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

有人知道如何解决这个问题吗?

6 回复 | 直到 14 年前

zerkms 14 年前

另外,mysql在 rows 列显示的不是受影响的行数,而是近似可能受影响的行数, 排除这个 LIMIT 条款。

IamIC 14 年前

基数:

KEY `search_INX` (`is_reviewed`,`has_image`,`book_status`,`published_date`)

……很穷。如果你把发布日期放在最前面,它会加快你的查询速度。此外,为什么你要索引ISSRead和HasyIMAGE?布尔列甚至不能像SQL Server那样被索引,因为这样做是没有意义的(同样,基数)。请重新排列键,或在我提到的列上放置唯一键。

IamIC 14 年前

乍一看,问题似乎是您丢失了发布日期的索引。Order by使用此列。添加这个索引,看看会发生什么。

MBCook 14 年前

如果使用FORCE INDEX命令,有帮助吗?

spanky 14 年前

我不是索引专家,但是你能在发布的日期上创建索引,也可以在所有四个字段上创建索引吗?

ALTER TABLE books DROP INDEX `search_INX`;
ALTER TABLE books ADD INDEX `published_INX` (`published_date`);

IamIC 14 年前

@泽克斯@杰森我刚想到另一种解决办法。

这不是正统的,但会奏效的。如果您的主键是(publish_date,book_id)和DESC sort,那么您很容易得到最后10个结果。查询引擎将扫描表,应用where子句,直到找到10个结果,然后退出。

会很好的。如果需要按图书id进行特定查询,只需在图书id上添加另一个索引。

这是有意义的,因为数据库自然会按日期存储图书(InnoDB使用聚集索引),这正是您试图查询的内容。