索引的一些测试总结

建表脚本：
 create table stu(sid int,msname varchar(20));

插入数据脚本：

mysql> insert into stu values(1,'a');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu values(2,'b');
Query OK, 1 row affected (0.03 sec)

mysql> insert into stu values(3,'c');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu values(4,'d');
Query OK, 1 row affected (0.03 sec)

mysql> insert into stu values(5,'e');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu values(6,'f');
Query OK, 1 row affected (0.03 sec)

mysql> insert into stu values(7,'g');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu values(8,'zsd');
Query OK, 1 row affected (0.02 sec)

mysql> insert into stu values(8,'liuzhonghao');
Query OK, 1 row affected (0.03 sec)


使用组合索引：
create index ind_stu_sid_sname on stu(sid,msname);

如果，查询用到了索引中最左边的列。是走索引的。
mysql> explain select * from stu where sid =1;
+----+-------------+-------+------+-------------------+-------------------+-----
----+-------+------+--------------------------+
| id | select_type | table | type | possible_keys     | key               | key_
len | ref   | rows | Extra                    |
+----+-------------+-------+------+-------------------+-------------------+-----
----+-------+------+--------------------------+
|  1 | SIMPLE      | stu   | ref  | ind_stu_sid_sname | ind_stu_sid_sname | 5
    | const |    1 | Using where; Using index |
+----+-------------+-------+------+-------------------+-------------------+-----
----+-------+------+--------------------------+

如果，查询用到的是按msname列走的话，不走索引。

mysql> explain select * from stu where msname ='a';
+----+-------------+-------+-------+---------------+-------------------+--------
-+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len
 | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------------+--------
-+------+------+--------------------------+
|  1 | SIMPLE      | stu   | index | NULL          | ind_stu_sid_sname | 48
 | NULL |    9 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------------+--------
-+------+------+--------------------------+
1 row in set (0.00 sec)


关于like查询的讨论。

再建立一个索引，
mysql> create index ind_msname on stu(msname);

用‘%a’去做查询条件，不走索引

explain select * from stu where msname like '%a';

mysql> explain select * from stu where msname like '%a';

+----+-------------+-------+-------+---------------+-------------------+--------
-+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key               | key_len
 | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+-------------------+--------
-+------+------+--------------------------+
|  1 | SIMPLE      | stu   | index | NULL          | ind_stu_sid_sname | 48
 | NULL |    9 | Using where; Using index |
+----+-------------+-------+-------+---------------+-------------------+--------
-+------+------+--------------------------+
1 row in set (0.00 sec)

用'a%'去做查询条件，走索引

mysql> explain select * from stu where msname like 'a%';

mysql> explain select * from stu where msname like 'a%';
+----+-------------+-------+-------+---------------+------------+---------+-----
-+------+-------------+
| id | select_type | table | type  | possible_keys | key        | key_len | ref
 | rows | Extra       |
+----+-------------+-------+-------+---------------+------------+---------+-----
-+------+-------------+
|  1 | SIMPLE      | stu   | range | ind_msname    | ind_msname | 43      | NULL
 |    1 | Using where |
+----+-------------+-------+-------+---------------+------------+---------+-----
-+------+-------------+

可知，如果针对like条件'%a%'号来说，就是不走索引的。