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  | id