author：skate
time:2013/04/10
mysql索引测试案例
实验条件：
mysql> show create table users\G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE `users` (
`id` int(11) NOT NULL,
`name` varchar(45) DEFAULT NULL,
`c1` text,
`c2` int(6) unsigned zerofill DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `c2_UNIQUE` (`c2`),
KEY `idx_users` (`c1`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> show create table user_action\G;
*************************** 1. row ***************************
Table: user_action
Create Table: CREATE TABLE `user_action` (
`user_id` int(11) NOT NULL,
`action` varchar(45) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `idx_userid` (`user_id`),
KEY `idx_action` (`action`),
KEY `idx_useraction_action_name` (`action`,`name`(10))
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> select * from users;
+----+--------+------+--------+
| id | name | c1 | c2 |
+----+--------+------+--------+
| 1 | libk | NULL | NULL |
| 2 | zyfon | NULL | NULL |
| 3 | daodao | NULL | NULL |
| 4 | 3333 | NULL | 000002 |
| 5 | 444 | NULL | NULL |
+----+--------+------+--------+
5 rows in set (0.00 sec)
mysql> select * from user_action;
+---------+--------+---------------+
| user_id | action | name |
+---------+--------+---------------+
| 1 | jump | aaaaaaaaaaaaa |
| 2 | run | bbbbbbbbbbb |
| 4 | swim | cccccc |
| 6 | kick | dd |
| 15 | jump1 | fff |
+---------+--------+---------------+
5 rows in set (0.00 sec)
mysql>
1. 复合索引
复合索引的使用原则是索引的前导列必须在条件里出现，否则将不使用符合索引
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)
mysql> explain select * from user_action where name='dd';
+----+-------------+-------------+------+---------------+------+---------+------+------+-------------+
| 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)
从type=ALL可以知道，使用了全表扫描
mysql> explain select * from user_action where action='run' and name='dd';
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-------------+
| 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 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)
上面这个例子中，也使用了覆盖索引扫描，但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;
+----+-------------+-------------+-------+--------------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+--------------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | user_action | range | PRIMARY,idx_userid | PRIMARY | 4 | NULL | 2 | Using where; Using filesort |
+----+-------------+-------------+-------+--------------------+---------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
上一个sql排序因为没有使用索引，所以需要排序（Using filesort）,而这个是使用了主键，为什么还需要排序，因为需要的顺序和主键的顺序不一样。
说明
mysql的排序有两种：index和filesort，index效率高，它指MySQL扫描索引本身而完成排序。FileSort方式效率较低，因为没有利用索引取到数据，需要mysql通过将取得的数据在内存中进行排序然后再将数据返回给客户。
mysql中filesort 的实现算法实际上是有两种的：
1) 在mysql4.1版本之前只有一种排序算法，首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer中进行排序，然后再回表取需要
的数据,这里需要二次回表，速度会慢些
2) 从mysql4.1开始,改进了第一种排序算法，一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。目的是为了减少第一次算法中需要二次回表的IO操作，将两
次变成了一次，但相应也会耗用更多的sort buffer空间。
mysql4.1开始以后所有版本同时支持两种算法，mysql主要通过比较我们所设定的系统参数max_length_for_sort_data 的大小和“select语句所取出的字段类型大小总和”，来判定需要使用哪一种排序算法。如果max_length_for_sort_data 更大，则使用第二种优化后的算法，否则使用第一种算法。如果希望ORDER BY操作的效率尽可能的高，一定要注意max_length_for_sort_data 参数的设置。
mysql> explain select * from user_action where action>'run' order by action,user_id;
+----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-------------+
| 1 | SIMPLE | user_action | range | idx_action,idx_useraction_action_name | idx_action | 138 | NULL | 1 | Using where |
+----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-------------+
1 row in set (0.00 sec)
这个sql使用了索引的排序，然后回表取数据得到结果集，没有对查询结果排序
mysql> explain select * from user_action where action>'run' order by action,a1;
+----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | user_action | range | idx_action,idx_useraction_action_name | idx_action | 138 | NULL | 1 | Using where; Using filesort |
+----+-------------+-------------+-------+---------------------------------------+------------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)
这个sql也使用了索引“idx_action”，为什么就需要对结果集排序呢？因为a1即不再索引idx_action里，也不是主键；所以需要对结果集排序，要使用filesort，可以通过增大max_length_for_sort_data来优化filesort。或者就避免filesort
mysql> explain select action,name from user_action order by action asc, name desc;
+----+-------------+-------------+------+---------------+------+---------+------+------+----------------+
| 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)
对索引列同时使用了ASC和DESC时，需要使用filesort
mysql> explain select action,name from user_action where user_id=1 order by action asc, name desc;
+----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | user_action | const | PRIMARY,idx_userid | PRIMARY | 4 | const | 1 | |
+----+-------------+-------------+-------+--------------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
虽然也对索引列同时使用了ASC和DESC，但是通过where语句（主键）将order by中索引列转为常量，则直接根据索引顺序回表读数据
mysql> explain select action,name from user_action where action='run' order by action asc, name desc;
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+
| 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 filesort |
+----+-------------+-------------+------+---------------------------------------+------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
where语句与order by语句使用了不同的索引也是需要排序的，where使用idx_action，order by使用了idx_useraction_action_name
总上面使用filesort的情况：
1) 查询的行数过多，优化器认为要全表扫描，且没有使用覆盖索引
2) 对索引列同时使用了ASC和DESC时，但是通过where语句（主键）将order by中索引列转为常量例外
3) where语句与order by语句使用了不同的索引也是需要排序的
4) order by子句中加入了非索引列,且非索引列不在where子句中
5) ORDER BY语句中索引列使用了表达式
------end------