建表脚本:
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%'号来说,就是不走索引的。
索引的一些测试总结
2021-12-31 21:53:07