MySQL 选择索引疑惑

yuanxb1985

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

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

你好，你想说明什么，没有看懂。能否说的通俗点。

ruochen

请注意 key_len: 4，在你所有的explain里面都是这个

龙雪刚

楼上版主能不能说的更清楚一点，希望指教。

ruochen

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


LZ的mysql版本多少？

ruochen

下面是我的测试过程，始终是使用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)
    
17. 
    
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)
    
30. 
    
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)
    
39. 
    
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)
    
53. 
    
54. 
    
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)
    
62. 
    
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)
    
70. 
    
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)
    
78. 
    
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)
    
86. 
    
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

从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

yuanxb1985 发表于 2012-03-28 13:40
希望大家指点

表结构如下所示：

两个sql的explain输出key_len: 4；说明只使用了两个索引的status部分（索引的最左原则）
using index说明select update_time这个字段能在使用到的复合索引中直接取到（覆盖索引，也即dba建议为啥不用select *的主要原因）

ooooldman

学习了 ，今天的气氛很好啊，
一直这样，我等菜鸟怎么能不提高啊

ruochen

ooooldman 发表于 2012-04-11 16:19
学习了 ，今天的气氛很好啊，
一直这样，我等菜鸟怎么能不提高啊

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