日期:2014-05-16  浏览次数:20792 次

mysql索引问题
SQL code

mysql> explain select tc.ControlledPCMAC,tc.ControlledPCNickName,tb.CSK300SN,tb.Client_ID from T_BindCSK300 tb left join T_ControlledPC tc on tb.CSK300SN = tc.CSK300SN where tb.Client_ID='6099888bdfe7465098674070d2abd8ff';
+----+-------------+-------+------+-------------------------+-------------------------+---------+---------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys           | key                     | key_len | ref                       | rows  | Extra                    |
+----+-------------+-------+------+-------------------------+-------------------------+---------+---------------------------+-------+--------------------------+
|  1 | SIMPLE      | tb    | ref  | index_clientid_csk300sn | index_clientid_csk300sn | 153     | const                     |     1 | Using where; Using index |
|  1 | SIMPLE      | tc    | ref  | index_csk300sn_1        | index_csk300sn_1        | 153     | GoToMyCloudDB.tb.CSK300SN | 18215 |                          |
+----+-------------+-------+------+-------------------------+-------------------------+---------+---------------------------+-------+--------------------------+
2 rows in set

mysql> explain select tc.ControlledPCMAC,tc.ControlledPCNickName,tb.CSK300SN,tb.Client_ID from T_BindCSK300 tb left join T_ControlledPC tc on tb.CSK300SN = tc.CSK300SN where tb.Client_ID='6099888bdfe7465098674070d2abd8ff';
+----+-------------+-------+------+-------------------------+-------------------------+---------+---------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys           | key                     | key_len | ref                       | rows | Extra                    |
+----+-------------+-------+------+-------------------------+-------------------------+---------+---------------------------+------+--------------------------+
|  1 | SIMPLE      | tb    | ref  | index_clientid_csk300sn | index_clientid_csk300sn | 153     | const                     |    1 | Using where; Using index |
|  1 | SIMPLE      | tc    | ref  | index_csk300sn          | index_csk300sn          | 153     | GoToMyCloudDB.tb.CSK300SN |  478 |                          |
+----+-------------+-------+------+-------------------------+-------------------------+---------+---------------------------+------+--------------------------+


这里有个问题,我只是对表T_ControlledPC的列(CSK300SN)添加了一个单列索引,开始索引名称为index_csk300sn_1,通过explain查看rows是18215,但是我把索引名称改为index_csk300sn后,rows却变成478,如果我在改,仍会变,并且rows的值也不确定,一法知道这是什么原因。。。

------解决方案--------------------
正常 rows这里列本身就是大概值 不确定值
------解决方案--------------------
没遇到过这情况,但是我在本地建了一个测试表,和你的结果不一样。 分析器认为需要检索的列应该与索引名称无关吧。
------解决方案--------------------
rows就是大概的值。