Chinaunix

标题: MySQL 选择索引疑惑 [打印本页]
作者: yuanxb1985    时间: 2012-03-28 13:40
标题: MySQL 选择索引疑惑
希望大家指点

表结构如下所示：
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)
作者: 小版主杀手    时间: 2012-03-28 14:57
where条件只有一个status
不用联合索引，直接用status做个索引呢
另外order by update_time --这个字段是没有索引的
作者: yuanxb1985    时间: 2012-03-28 16:55
感谢2楼，但是update_time是可以使用索引的。期待其他回答。
作者: squall1    时间: 2012-03-28 22:36
小版主杀手 发表于 2012-03-28 14:57
where条件只有一个status
不用联合索引，直接用status做个索引呢
另外order by update_time --这个字段是 ...


status要和update_time做个联合索引。

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

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

用explain优化器查看，应该会出现Using filesort
作者: 小版主杀手    时间: 2012-03-29 11:00
yuanxb1985 发表于 2012-03-28 16:55
感谢2楼，但是update_time是可以使用索引的。期待其他回答。


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

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

作者: love100    时间: 2012-03-29 11:29
try:
  1. explain select  update_time from book  where status = 2  order by
  2. update_time desc,status desc  limit 28000,50;
复制代码
作者: witer666    时间: 2012-03-29 19:04
只要有ORDER BY ，MYSQL就会用临时表！ODERY BY 中的字段用不到索引中！
为什么用S_L_U是因为MYSQL会选择最优的索引来查询！最优的索引一般都会看影响的行数！
S_L_U影响的行数明显比S_U少！

你可以再加个status索引看看！
作者: kerlion    时间: 2012-03-30 09:55
提示: 作者被禁止或删除 内容自动屏蔽
作者: yuanxb1985    时间: 2012-03-30 10:05
首先谢谢你的回复。但是，你的说法是不对的。请自行查看手册相应说明。回复 7# witer666


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


   
作者: yuanxb1985    时间: 2012-03-30 10:10
小版主杀手 发表于 2012-03-29 11:00
记得手册上有关于优化器的选择介绍

每个表的索引被查询，并且使用最好的索引，除非优化器认为使用表 ...


你好，你想说明什么，没有看懂。能否说的通俗点。
作者: ruochen    时间: 2012-04-11 13:52
请注意 key_len: 4，在你所有的explain里面都是这个
作者: 龙雪刚    时间: 2012-04-11 14:12
楼上版主能不能说的更清楚一点，希望指教。
作者: ruochen    时间: 2012-04-11 14:49
本帖最后由 ruochen 于 2012-04-11 15:29 编辑

从LZ的测试结果上看到有些疑惑，回头我测试一下


LZ的mysql版本多少？

作者: ruochen    时间: 2012-04-11 15:34
下面是我的测试过程，始终是使用s_u上的索引
方便的话，LZ将这个表倒出来上传到cu供大家测试下
  1. mysql> show create table book\G
  2. *************************** 1. row ***************************
  3.        Table: book
  4. Create Table: CREATE TABLE `book` (
  5.   `book_id` int(11) NOT NULL AUTO_INCREMENT,
  6.   `status` int(11) NOT NULL,
  7.   `level` int(11) NOT NULL,
  8.   `update_time` datetime DEFAULT NULL,
  9.   `price` int(11) DEFAULT NULL,
  10.   `actor` varchar(20) DEFAULT NULL,
  11.   `pub` varchar(20) DEFAULT NULL,
  12.   PRIMARY KEY (`book_id`),
  13.   KEY `s_l_u` (`status`,`level`,`update_time`),
  14.   KEY `s_u` (`status`,`update_time`)
  15. ) ENGINE=MyISAM AUTO_INCREMENT=72551 DEFAULT CHARSET=utf8
  16. 1 row in set (0.00 sec)

  18. mysql> select distinct status,count(*) from book group by status;
  19. +--------+----------+
  20. | status | count(*) |
  21. +--------+----------+
  22. |      1 |     9000 |
  23. |      2 |    28550 |
  24. |      3 |    10500 |
  25. |      4 |    12000 |
  26. |      5 |     1500 |
  27. |      6 |    11000 |
  28. +--------+----------+
  29. 6 rows in set (0.00 sec)

  31. mysql> select distinct level,count(*) from book group by level;
  32. +-------+----------+
  33. | level | count(*) |
  34. +-------+----------+
  35. |     0 |    72330 |
  36. |     1 |      220 |
  37. +-------+----------+
  38. 2 rows in set (0.10 sec)

  40. mysql> select distinct update_time,count(*) from book group by update_time;
  41. +---------------------+----------+
  42. | update_time         | count(*) |
  43. +---------------------+----------+
  44. | 2012-04-11 15:14:25 |    11533 |
  45. | 2012-04-11 15:14:26 |    12853 |
  46. | 2012-04-11 15:14:27 |    12340 |
  47. | 2012-04-11 15:14:28 |    12019 |
  48. | 2012-04-11 15:14:29 |    12046 |
  49. | 2012-04-11 15:14:30 |    11588 |
  50. | 2012-04-11 15:14:31 |      171 |
  51. +---------------------+----------+
  52. 7 rows in set (0.11 sec)


  55. mysql> explain  select update_time from book where status=2 order by update_time desc;
  56. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  57. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                    |
  58. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  59. |  1 | SIMPLE      | book  | ref  | s_l_u,s_u     | s_u  | 4       | const | 20798 | Using where; Using index |
  60. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  61. 1 row in set (0.00 sec)

  63. mysql> explain  select update_time from book where status=2 order by update_time desc limit 100,50;
  64. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  65. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                    |
  66. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  67. |  1 | SIMPLE      | book  | ref  | s_l_u,s_u     | s_u  | 4       | const | 20798 | Using where; Using index |
  68. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  69. 1 row in set (0.00 sec)

  71. mysql> explain  select update_time from book where status=2 order by update_time desc limit 28000,50;
  72. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  73. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                    |
  74. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  75. |  1 | SIMPLE      | book  | ref  | s_l_u,s_u     | s_u  | 4       | const | 20798 | Using where; Using index |
  76. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  77. 1 row in set (0.00 sec)

  79. mysql> explain  select update_time from book where status=2 order by update_time desc limit 20000,50;
  80. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  81. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                    |
  82. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  83. |  1 | SIMPLE      | book  | ref  | s_l_u,s_u     | s_u  | 4       | const | 20798 | Using where; Using index |
  84. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  85. 1 row in set (0.00 sec)

  87. mysql> explain  select update_time from book where status=2 order by update_time desc limit 10000,50;
  88. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  89. | id | select_type | table | type | possible_keys | key  | key_len | ref   | rows  | Extra                    |
  90. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  91. |  1 | SIMPLE      | book  | ref  | s_l_u,s_u     | s_u  | 4       | const | 20798 | Using where; Using index |
  92. +----+-------------+-------+------+---------------+------+---------+-------+-------+--------------------------+
  93. 1 row in set (0.00 sec)
复制代码
作者: ruochen    时间: 2012-04-11 15:46
本帖最后由 ruochen 于 2012-04-11 15:55 编辑

从LZ的explain看到，下面这个sql的type是range，ref是NULL，很疑惑
楼主analyze local table book；后再explain看看呢？

可能和数据的碎片（update/delete）有关
  1. mysql> explain select  update_time from book  where status = 2  order
  2. by update_time desc limit 100,50\G
  3. *************************** 1. row ***************************
  4.          id: 1
  5. select_type: SIMPLE
  6.       table: book
  7.        type: range---------------
  8. possible_keys: S_L_U,S_U
  9.         key: S_U
  10.     key_len: 4------------------
  11.         ref: NULL----------------
  12.        rows: 27893
  13.       Extra: Using where; Using index
  14. 1 row in set (0.00 sec)
复制代码
作者: ruochen    时间: 2012-04-11 15:53
yuanxb1985 发表于 2012-03-28 13:40
希望大家指点

表结构如下所示：


两个sql的explain输出key_len: 4；说明只使用了两个索引的status部分（索引的最左原则）
using index说明select update_time这个字段能在使用到的复合索引中直接取到（覆盖索引，也即dba建议为啥不用select *的主要原因）
作者: ooooldman    时间: 2012-04-11 16:19
学习了 ，今天的气氛很好啊，
一直这样，我等菜鸟怎么能不提高啊
作者: ruochen    时间: 2012-04-11 16:43
ooooldman 发表于 2012-04-11 16:19
学习了 ，今天的气氛很好啊，
一直这样，我等菜鸟怎么能不提高啊



多来反馈问题和测试就是最快的提高



欢迎光临 Chinaunix (http://bbs.chinaunix.net/) Powered by Discuz! X3.2