MySQL 选择索引疑惑

yuanxb1985

希望大家指点

表结构如下所示：
mysql> show create table book\G
*************************** 1. row ***************************
     Table: book
Create Table: CREATE TABLE `book` (
`book_id` int(11) NOT NULL AUTO_INCREMENT,
`status` int(11) NOT NULL,
`level` int(11) NOT NULL,
`update_time` datetime DEFAULT NULL,
...... snip .......
PRIMARY KEY (`book_id`),
KEY `S_L_U` (`status`,`level`,`update_time`),
KEY `S_U` (`status`,`update_time`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
status 有6个取值，level只有2个取值。该表总共有72550行数据，status=2的有28527行数据，level=0的有72330行数据，level=1的有220行数据。

我有个Query是这样的：
select  update_time from book  where status = 2  order by update_time
desc limit offset,row_count

按照原理，该Query应该是使用S_U索引的。
当offset比较小的时候，该Query使用S_U(status,update_time)索引。
mysql> explain select  update_time from book  where status = 2  order
by update_time desc limit 100,50\G
*************************** 1. row ***************************
         id: 1
select_type: SIMPLE
      table: book
       type: range
possible_keys: S_L_U,S_U
        key: S_U
    key_len: 4
        ref: NULL
       rows: 27893
      Extra: Using where; Using index
1 row in set (0.00 sec)

但是随着offset的增大，该Query会选择使用S_L_U(status,level,update_time)索引。而且从explain的输出来看，Extra列显示还有using
index,但是我strace了这个sql的过程，发现该Query进行了50次的表数据读取，这个是第一个不理解的地方，还有为啥不选择S_U索引，S_U索引能够覆盖update_time，不是连排序都省了吗？？
mysql> explain select  update_time from book  where status = 2  order
by update_time desc limit 28000,50\G
*************************** 1. row ***************************
         id: 1
select_type: SIMPLE
      table: book
       type: ref
possible_keys: S_L_U,S_U
        key: S_L_U
    key_len: 4
        ref: const
       rows: 19199
      Extra: Using where; Using index; Using filesort
1 row in set (0.00 sec)

mysql> select  update_time from book  where status = 2  order by
update_time desc limit 28000,50;
+---------------------+
| update_time         |
+---------------------+
| 2008-08-11 17:08:17 |
  ...... snip ......
| 2008-08-11 14:00:39 |
+---------------------+
50 rows in set (0.03 sec)

如果使用force index 执行使用S_U索引的话，反而没有使用S_L_U索引速度快，这个是为啥呢？
mysql> explain select  update_time from book force index(S_U) where
status = 2  order by update_time desc limit 28000,50\G
*************************** 1. row ***************************
         id: 1
select_type: SIMPLE
      table: book
       type: ref
possible_keys: S_U
        key: S_U
    key_len: 4
        ref: const
       rows: 27893
      Extra: Using where; Using index
1 row in set (0.00 sec)

mysql> select  update_time from book force index(S_U) where status = 2
order by update_time desc limit 28000,50;
+---------------------+
| update_time         |
+---------------------+
| 2008-08-11 17:08:17 |
...... snip ......
| 2008-08-11 14:00:39 |
+---------------------+
50 rows in set (0.10 sec)

小版主杀手

where条件只有一个status
不用联合索引，直接用status做个索引呢
另外order by update_time --这个字段是没有索引的

yuanxb1985

感谢2楼，但是update_time是可以使用索引的。期待其他回答。

squall1

小版主杀手 发表于 2012-03-28 14:57
where条件只有一个status
不用联合索引，直接用status做个索引呢
另外order by update_time --这个字段是 ...

status要和update_time做个联合索引。

否则在status和update_time分别单独建立个索引，

会先选择索引status把记录扫描出来，然后对字段update_time逐行进行排序操作。

用explain优化器查看，应该会出现Using filesort

小版主杀手

yuanxb1985 发表于 2012-03-28 16:55
感谢2楼，但是update_time是可以使用索引的。期待其他回答。

记得手册上有关于优化器的选择介绍

每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表扫描更有效。是否使用扫描取决于是否最好的索引跨越超过30%的表。优化器更加复杂，其估计基于其它因素，例如表大小、行数和I/O块大小，因此固定比例不再决定选择使用索引还是扫描。

love100

try:

1. explain select  update_time from book  where status = 2  order by
   
2. update_time desc,status desc  limit 28000,50;

复制代码

witer666

只要有ORDER BY ，MYSQL就会用临时表！ODERY BY 中的字段用不到索引中！
为什么用S_L_U是因为MYSQL会选择最优的索引来查询！最优的索引一般都会看影响的行数！
S_L_U影响的行数明显比S_U少！

你可以再加个status索引看看！

yuanxb1985

首先谢谢你的回复。但是，你的说法是不对的。请自行查看手册相应说明。回复 7# witer666


   

yuanxb1985

首先感谢回复。另外，建立单个update_time索引，是可以对order by update_time 的排序做优化，但是当order by 和 limit一起使用，尤其是在offset较大的时候，使用索引排序，不如全表扫描的性能好。回复 8# kerlion