1、单列索引
最常见的索引类型涉及单个列,在数据结构中存储该列的值的副本,允许快速查找具有相应列值的行。 B树数据结构允许索引在WHERE子句中快速查找特定值,一组值或一系列值,对应于运算符如=,>,≤,BETWEEN,IN等。
mysql> create table people(
-> last_name varchar(50) not null,
-> first_name varchar(50) not null,
-> sex enum('m','f') not null,
-> key last_name(last_name) //单列索引
-> )engine=innodb;
Query OK, 0 rows affected (0.07 sec)
mysql> select * from people;
+-----------+------------+-----+
| last_name | first_name | sex |
+-----------+------------+-----+
| Akroyd | Christian | f |
| Akroyd | Lumi | m |
| Barrymore | Debbie | m |
| Barrymore | Kirsten | m |
| Cathy | Kim | m |
+-----------+------------+-----+
5 rows in set (0.00 sec)
mysql> explain select * from people where last_name > 'Barrymore'; //使用了索引进行查找
+----+-------------+--------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |