一、索引的作用
通常,对MySQL数据库中数据的读取操作要多于写入操作,索引的作用就是为了优化查询,提升查询速度。
二、索引的分类
索引一般包括:BTree、HASH、RTree、Fulltext。
三、BTree索引结构分类
BTree包括:B-Tree、B+Tree、B*Tree。其中B+Tree和B*Tree的使用较多。
四、BTree种类
4.1、聚集索引
聚集索引(cluster index),也叫主键索引。在生成聚集索引时,会选择主键作为聚集索引列(主键一般在建表时加入)。如果没有主键,会自动选择唯一键。
聚集索引的结构:按照聚集索引列的值的顺序,顺序存储数据页,作为叶节点。根节点和枝节点只存储下层的最小值和指针。
4.2、普通二级索引
也叫辅助索引,通常和聚集索引一起使用,作为聚集索引的辅助。
创建辅助索引时,选择表中的某个列作为索引键。然后将整个列的值提取出来,做排序。将排序后的值,均匀的分布到BTree索引的叶节点中,进而生成枝节点,最终生成根节点。注意:应当尽量控制索引树的高度,来减少索引树遍历的次数。叶节点同时会存储原表的数据行的指针,通过指针可以找到行中其他列的数据,这个动作称为回表查询,伴随着随机IO。
如果同时有聚集索引,则叶节点保存指向该值对应的主键的指针,通过该指针根据聚集索引直接查找对应的数据,而不必进行回表查询。
4.3、覆盖索引
覆盖索引,也称为联合索引。
通常根据业务需求,将需要频繁查询的列,联合起来建立BTree,大量减少回表查询,从而减少随机IO。
4.4、唯一索引
唯一索引(unique index):列中的值必须是唯一、不重复的。
索引树的高度
索引本身也会占用磁盘空间,取决于数据行的多少和作为索引的列的值的长度,行数越多、值的长度越长,索引树的高度也越高。因为在MySQL中,一个数据页的长度为16K,存储的数据有限,在数据量大、值越长时,将会使用更多的数据页。
数据行数过多时,可以通过拆分表或者分布式来解决;
作为索引的列的值的长度太长时,通常会采用前缀索引。
五、索引的基本管理
5.1、创建索引
创建单列普通索引
:
语法:
ALTER TABLE `表名` ADD INDEX 索引名称(列名称);
例如:
ALTER TABLE students ADD INDEX cardid_idx(cardid);
创建覆盖索引
:
语法:
ALTER TABLE `表名` ADD INDEX 索引名称(列名,列名,...);
例如:
ALTER TABLE students ADD INDEX idx_cid_n(name,cardid);
创建前缀索引
:
在作为索引列的值的长度较大时,考虑使用前缀索引,如下,只取“name”字段的前10个字符:
ALTER TABLE students ADD INDEX idx_name(name(10));
创建唯一索引
:
首先判断列中是否有重复的值,可以使用以下语句来判断,判断“name”字段是否有重复的值:
SELECT name,COUNT(name) AS cname FROM students GROUP BY name HAVING cname>1;
选择没有重复值的列创建唯一索引:
ALTER TABLE students ADD UNIQUE INDEX idx_cid(cardid);
5.2、查看索引
语法:
SHOW INDEX FROM `表名`;
例如:
SHOW INDEX FROM students;
5.3、删除索引
语法:
ALTER TABLE `表名` DROP INDEX 索引名称;
例如:
ALTER TABLE students DROP INDEX cardid_idx;
六、执行计划分析
创建将要用到的表:
CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cardid` int(18) DEFAULT NULL,
`chinese` int(3) DEFAULT NULL,
`english` int(3) DEFAULT NULL,
`mathematics` int(3) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (1, '小王', 111111111, 67, 89, 90);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (2, '小赵', 222222222, 66, 77, 88);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (3, '小李', 333333333, 65, 79, 83);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (4, '小王', 444444444, 91, 89, 54);
INSERT INTO `students` (`id`, `name`, `cardid`, `chinese`, `english`, `mathematics`) VALUES (5, '小张', 555555555, 78, 80, 74);
6.1、使用EXPLAIN执行计划分析
通常使用“EXPLAIN”或“DESCRIBE”来进行执行计划分析:
mysql> EXPLAIN SELECT * FROM students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
- select_type:指明是单表查询还是多表查询;
- table:查询操作的表;
- type:索引类型
- possible_keys:可能会用到的索引;
- key:实际使用的索引;
- Extra:额外信息。
6.2、索引类型
- ALL:全表扫描
mysql> EXPLAIN SELECT * FROM students;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 4 | NULL |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
- index:全索引扫描
mysql> EXPLAIN SELECT id FROM students;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | students | index | NULL | PRIMARY | 4 | NULL | 4 | Using index |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
- range:范围索引扫描
通常在SELECT范围查询中,使用<,>,<=,>=,IN,OR,BETWEEN..ON..
操作符时出现:
mysql> EXPLAIN SELECT * FROM students WHERE id<3;
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | students | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |
+----+-------------+----------+-------+---------------+---------+---------+------+------+-------------+
- ref:辅助索引的等值查询
mysql> ALTER TABLE students ADD INDEX idx_cid(cardid); #使用“cardid”列创建辅助索引
mysql> EXPLAIN SELECT * FROM students WHERE cardid=111111111;
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | ref | idx_cid | idx_cid | 5 | const | 1 | NULL |
+----+-------------+----------+------+---------------+---------+---------+-------+------+-------+
- system/const:主键或唯一键的等值查询
mysql> EXPLAIN SELECT * FROM students WHERE id=3;
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | students | const | PRIMARY | PRIMARY | 4 | const | 1 | NULL |
+----+-------------+----------+-------+---------------+---------+---------+-------+------+-------+
- NULL:索引中不包含查询的值
mysql> EXPLAIN SELECT * FROM students WHERE id=1000;
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
6.3、额外信息Extra
看下面的查询语句:
mysql> EXPLAIN SELECT * FROM students WHERE name="小王" ORDER BY cardid;
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | students | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using filesort |
+----+-------------+----------+------+---------------+------+---------+------+------+-----------------------------+
在"Extra"中出现了“Using filesort”信息。说明在进行查询时,会对数据进行排序,这将会消耗CPU时间,在数据量大的表中,将会影响查询速度。
在创建索引时,会对数据进行排序,所以考虑将WHERE
和ORDER
查询的列作为联合索引列,这样可以减少查询时的排序。因为在执行查询语句时,WHERE条件的优先级是高于ORDER优先级
的,所以这里将“name”列作为第一索引列:
mysql> ALTER TABLE students ADD INDEX idx_cid_n(name,cardid);
mysql> EXPLAIN SELECT * FROM students WHERE name="小王" ORDER BY cardid;
+----+-------------+----------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+-----------+---------+-------+------+-------------+
| 1 | SIMPLE | students | ref | idx_cid_n | idx_cid_n | 767 | const | 2 | Using where |
+----+-------------+----------+------+---------------+-----------+---------+-------+------+-------------+
七、索引的应用规范
- 建表时一定要有主键;
- 选择唯一性索引;同一个表中不能有同名索引;
- 为经常需要排序、分组、联合操作的字段建立索引,可以大大提高查询效率;
- 为经常需要作为WHERE查询条件的字段建立索引;
- 如果作为索引的列的值很长,尽量使用前缀索引;
- 索引的条目不能过多,不使用的索引要删除;
- 索引的维护要避开业务高峰期。因为创建索引时会对数据进行排序,数据量很大时,将会占用较多的CPU资源。