数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询和排序。索引的实现通常使用B-树或其变种的B+树。
- 通过建立唯一索引,可以保证数据库表中每一行数据的唯一性;
- 可以大大加快数据检索速度(主要原因);
- 可以加速表与表之间的连接,特别是在实现数据的参考完整性方面有重要作用;
- 可以在检索数据时,大大减少查询中分组和排序花费的时间;
- 使用索引可以在查询过程中使用优化隐藏器,提高系统性能。
- 创建和维护索引需要耗费时间,且会随着数据量的增加而增加;
- 创建索引需要占物理空间,聚集索引占空间更大;
- 对表中的数据进行增删改时,索引也需要动态维护。
需要创建索引 | 不应该创建索引 |
---|---|
1.经常需要搜索的列 | 1.查询中很少使用或者参考的列 |
2.经常用在连接上的列 | 2.只有很少数值的列(如:性别) |
3.作为主键的列 | 3.定义为text, image, bit数据类型的列 |
4.经常需要根据范围搜索的列 | 4.修改性能远远大于检索性能时 |
5.经常需要排序的列 | |
6.经常在Where子句中的列 |
-
普通索引
最基本的数据库索引,没有任何限制;
-
唯一索引
要求索引列的值必须唯一,但允许空值;
-
主键索引
是一种特殊的唯一索引,不允许空值,一般在创建表的时候同时创建主键索引;
-
聚集索引
聚集索引中,各行的物理顺序与索引键值的逻辑顺序一致,且每张表最多只能有一个聚集索引。创建聚集索引要花更长的时间。(A,B,C)相当于分别建立了(A,B,C),(A,B), (A) 三种组合的索引。
类别 | 解释 |
---|---|
聚集索引 | 表数据按照索引的顺序来存储,叶子节点即存储了真实的数据行 |
非聚集索引 | 表数据存储与索引顺序无关,叶子节点包含索引字段值及指向数据页数据行的逻辑指针 |
索引是在Mysql的存储引擎层实现的,而不是在服务层实现的。所以每种存储引擎的索引并不完全相同
类别 | 解释 |
---|---|
B-Tree索引 | 最常见的索引类型,大部分引擎都支持B-Tree索引 |
Hash索引 | 即使用列的Hash值作为索引查找,只用Memory引擎支持,使用场景简单 |
R-Tree索引 | MyISAM的一种特殊索引,主要用于地理空间数据类型 |
Full-text | 全文索引,MyISAM 和 InnoDB 支持 |
索引文件很大,一般存储在磁盘上,而磁盘I/O十分耗时。使用索引的过程中要尽可能地减少查找过程中磁盘I/O的次数。B+树作为多路查找树,高度小,磁盘I/O次数少。数据库系统巧妙地应用磁盘预读原理,将一个结点的大小设置为一页,所以每个节点一次I/O就可以完全载入。而红黑树类似的结构,高度更深,且逻辑上很近的节点物理上也可能很远,无法利用局部性。
CREATE INDEX indexName ON mytable(username(length)); //普通索引
CREATE UNIQUE INDEX indexName ON mytable(username(length)); //唯一索引
CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,PRIMARY KEY(ID) ); //主键索引
ALTER TABLE mytable ADD INDEX indexName (username(16),time(10)) //聚集索引
- 隐式转换导致索引失效;
- 对索引列进行计算导致索引失效;
- like "%_" 百分号在前;
- 索引字段进行判空查询时;
- 复合索引中的前导列没有被作为查询条件;
- 判断索引列是否不等于某个值时。
特点 | MyISAM | InnoDB |
---|---|---|
存储限制 | 无 | 64TB |
事务安全 | 支持 | |
锁机制 | 表锁 | 行锁 |
B树索引 | 支持 | 支持 |
哈希索引 | 支持 | |
全文索引 | 支持 | |
数据缓存 | 支持 | |
支持外键 | 支持 | |
操作效率 | 高 | 低 |
备注:最新版本的MySQL默认的存储引擎是InnoDB。count(*)操作,因为MyISAM内置了计数器,所以更快,InnnoDB需要扫描全表。