mysql索引测试案例(二)
-------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+
| 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)
mysql选择执行路径使用的是和oracle类似的基于cost，只不过mysql没有oracle做的那么强悍
2. 在索引列上不要用函数，否则会不使用索引
mysql> explain select * from user_action where abs(user_id)>0;
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | user_action | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
3. 覆盖索引使用
mysql> explain select * 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)
从上面的type=ref和“Using where”，可以看出，mysql先ref的方式找到索引，再通过索引回表找到数据
说明：
ref：当通过键值读数据时，键不是UNIQUE或PRIMARY KEY（换句话说，如果不能基于关键字选择单个行的话），则使用ref
Using where：提示mysql用where过滤结果集
mysql> explain select action 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; Using index |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
从上面可以看到“Using where; Using index”，说明是通过全索引扫描获得数据并过滤结果集，不用回表取数据，使用了覆盖索引扫描
mysql> explain select action,user_id fr