有两个问题,希望知道的兄弟点拨一下!+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using file
| 1 | PRIMARY | T3 | eq_ref | PRIMARY | PRIMARY | 4 | T4.COMPANYID | 1 |
| 1 | PRIMARY | T2 | ref | PRIMARY,2 | PRIMARY | 4 | T4.COMPANYID | 727 | Using where
| 2 | DERIVED | mail_stat_tbl | index | 2 | PRIMARY | 11 | NULL | 2910 |
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
1、对于最后一行有一个疑问。possible_keys里面只有2,为什么key里面却是PRIMARY?key里面还能出现possible_keys里面没有的?+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using file
| 1 | PRIMARY | T3 | eq_ref | PRIMARY | PRIMARY | 4 | T4.COMPANYID | 1 |
| 1 | PRIMARY | T2 | ref | PRIMARY | PRIMARY | 4 | T4.COMPANYID | 3105 | Using where
| 2 | DERIVED | mail_stat_tbl | index | NULL | PRIMARY | 11 | NULL | 6211 | Using where
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
2、上面是我把2这个index删掉后的结果。rows由2910增加到了6211,这是不是说明2这个index本来还是有用的?
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using file
| 1 | PRIMARY | T3 | eq_ref | PRIMARY | PRIMARY | 4 | T4.COMPANYID | 1 |
| 1 | PRIMARY | T2 | ref | PRIMARY,2 | PRIMARY | 4 | T4.COMPANYID | 727 | Using where
| 2 | DERIVED | mail_stat_tbl | index | 2 | PRIMARY | 11 | NULL | 2910 |
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
1、对于最后一行有一个疑问。possible_keys里面只有2,为什么key里面却是PRIMARY?key里面还能出现possible_keys里面没有的?+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 3 | Using temporary; Using file
| 1 | PRIMARY | T3 | eq_ref | PRIMARY | PRIMARY | 4 | T4.COMPANYID | 1 |
| 1 | PRIMARY | T2 | ref | PRIMARY | PRIMARY | 4 | T4.COMPANYID | 3105 | Using where
| 2 | DERIVED | mail_stat_tbl | index | NULL | PRIMARY | 11 | NULL | 6211 | Using where
+----+-------------+---------------+--------+---------------+---------+---------+--------------+------+----------------------------
2、上面是我把2这个index删掉后的结果。rows由2910增加到了6211,这是不是说明2这个index本来还是有用的?
The possible_keys column indicates which indexes MySQL can choose from use to find the rows in this table. Note that this column is totally independent of the order of the tables as displayed in the output from EXPLAIN. That means that some of the keys in possible_keys might not be usable in practice with the generated table order. key The key column indicates the key (index) that MySQL actually decided to use. If MySQL decides to use one of the possible_keys indexes to look up rows, that index is listed as the key value. It is possible that key will name an index that is not present in the possible_keys value. This can happen if none of the possible_keys indexes are suitable for looking up rows, but all the columns selected by the query are columns of some other index. That is, the named index covers the selected columns, so although it is not used to determine which rows to retrieve, an index scan is more efficient than a data row scan. 上述是MYSQL的解释,也就是说possible_keys中的KEYS在实际是有些是没有用到的,
而KEY是实际用到的列。
2 ¦ DERIVED ¦ mail_stat_tbl ¦ index ¦ NULL ¦ PRIMARY ¦ 11 ¦ NULL ¦ 6211 ¦ Using where
possible_keys列指出MySQL能使用哪个索引在该表中找到行。该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
key列显示MySQL实际决定使用的键(索引)。
就是说possible_keys列可能使用索引,但实际执行的时候,不一定用possible里面的内容。2是常量,在type里面是index说明对索引树进行扫描,扫描条件为2的所有索引,比all要快。再理解一下,你的几表关联中用到了select xxx from tb where tb.indexcol=2
我现在疑惑的是key里面显示两次查询都只用了PRIMARY作为索引,可为啥rows结果不一样。现在想想可能rows也许只是预先结果而已。有啥办法可以精确知道这次查询使用了多少时间吗?
我现在疑惑的是key里面显示两次查询都只用了PRIMARY作为索引,可为啥rows结果不一样。
Mysql will use its own way to optimize this.