mysql索引测试案例(三)
om user_action where action='run';
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+
| 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where; Using index |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
上面这个例子中，也使用了覆盖索引扫描，但idx_action所以里没有user_id字段，这是什么原因？因为idx_action是辅助索引，它要通过主键才能找到数据，而user_id是主键索引。所以也不用回表就可以得到所需数据。所以要想使用覆盖索引，就不要用"select *"
mysql> explain select action,a1 from user_action where action='run';
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | user_action | ref | idx_action,idx_useraction_action_name | idx_action | 138 | const | 1 | Using where |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
上面添加了一个字段a1，然后把a1放到select列表里，再看执行计划，发现只有“Using where”,说明需要回表取数据了。
mysql> explain select action from user_action order by action;
+----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | user_action | index | NULL | idx_action | 138 | NULL | 5 | Using index |
+----+-------------+-------------+-------+---------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
从上面可以看到type=index和“Using index”,说明只扫描了索引，使用的覆盖索引扫描，没有回表就把排序好的数据取出来
4. 数据排序（order by）
mysql> explain select * from user_action order by action;
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| 1 | SIMPLE | user_action | ALL | NULL | NULL | NULL | NULL | 5 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
这个type=ALL和“Using filesort”可以知道使用了全表扫描，然后对结果集排序
mysql> explain select * from user_action where user_id>1 order by action;
+----+------