mysql索引有关问题

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 setmysql> 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就是大概的值。

查看更多 下一篇