MySQL数据库索引知识点总结
1. 什么是索引?优缺点?
索引:是对数据库表的一列或者多个列进行排序的一种结构,可快速查找表中的特定信息。
优点:
- 加快检索速度;
- 通过创建唯一性索引,可以保证表中的每一行数据的唯一性;
- 加速表与表之间的连接。
缺点:
- 创建和维护索引消耗时间;
- 索引占用物理空间;
- 对表进行CURD要动态维护索引。
2. 索引的底层数据结构
-
哈希表:
通过哈希表的key可以快速检索到data;
哈希表容易产生hash冲突,且不支持顺序和范围查询;
一般用于精确查找。
-
B树(多路平衡查找树),B+树:
B+树具有有序性,数据都存放在叶子节点上,查询效率非常高,且支持顺序和范围查询;
在MySQL中,InnoDB和MyISAM都是使用B+树作为默认索引结构,但二者的实现方式不同:
-
在MyISAM引擎中,
B+树的叶子节点data域存放的是数据记录的地址;查询时,首先根据key找到数据的地址,在根据地址找到对应的数据。这属于”非聚簇索引“。
-
InnoDB引擎中:
B+树索引又分为主键索引和辅助索引,主键索引属于聚簇索引,辅助索引属于非聚簇索引;
主键索引以主键作为B+树索引的key,叶子节点存储着完整的数据记录;辅助索引以非主键作为索引的key,叶子节点存储着主键。所以,使用辅助索引查找时,首先找到主键值,然后回表根据主键索引找到主键对应的数据。
-
3. 聚簇索引和非聚簇索引
- 聚簇索引:将数据和索引放在一起存储,索引结构的叶子节点存储数据;
- 非聚簇索引:将数据和索引分开存储,索引结构的叶子节点存储的是数据的地址;
4. 索引的类型
- 哈希索引
- B+树索引
5. 索引的种类
- 唯一索引:唯一索引的列的数据不允许重复,但允许为null,一张表可以创建多个唯一索引;通过创建唯一索引,可保证每一行数据的唯一性。
- 普通索引:列的数据允许重复,允许为null;一张表可创建多个普通索引;主要用于快速查询。
- 前缀索引:只适用于字符串类型的数据;前缀索引只对文本的前几个字符创建索引,这样索引的长度更短,查询速度更快。
- 全文索引:主要为了查询大文本数据中的关键字,是目前搜索引擎数据库使用的一种技术。
- 联合索引:使用表中的多个字段创建索引。
6. B树和B+树的区别
B树的叶子节点和内部节点既存储key也存储data,而B+树的内部节点只存储key;
B+树的叶子节点通过链表相连,方便顺序和范围查询。
7. 数据库为什么使用B+树不使用B树?
- B树适用于随机索引,而B+树适用于随机索引和顺序索引(链表的作用);
- B+树的空间利用率更高,B+树的内部节点只存储key,这样一个节点可存储更多的索引,使得树的高度降低,加快查询;
- B+树叶子节点通过链表相连,方便顺序和范围查询;
- B+树性能更稳定,每次查询都是从根节点到叶子节点,而B树内部节点也存储data,有时候没到叶子节点就找到了data。使用B树时,把频繁访问的data放在根节点附近的地方,可以提高查询效率。
8. 非聚簇索引一定会回表查询吗(索引覆盖)?
不一定
如果查询的数据能在辅助索引上完全获取到就不必回表。比如:一张表存储着id,name,age字段,聚簇索引是以id为key构建的索引,非聚簇索引是以name为key构建的索引,那么“select id, name from user where name = 'Wangwu'”
这个查询就不需要回表,通过非聚簇索引就能检索到需要的全部数据。
9. 索引的使用场景
- 对中大型表建立索引非常有效,对小表或者超大表维护代价相对更高;
- 对查询较多的表建立索引,对增删改较多的不要建立索引;
- 不会出现在where语句中的字段不必要建立索引;
- 多个字段经常被一起查询的话建立联合索引;
- 字段多且字段值不重复的时候考虑建立唯一索引,字段多有重复的建立普通索引;
- 索引列的基数越大,索引效果越好,即索引列的区分度越高,索引效果越好。
10. 如何对索引进行优化?
除了考虑9. 索引的使用场景外,还可以从以下方面考虑:
- 删除长期不用的索引;
- 考虑在字符串类型的字段上使用前缀索引,会占用更小的空间;
- 在进行查询时,索引列不能是表达式的一部分,也不能说函数的参数,否则无法使用索引;
- 将区分度高的索引放在左边(最左前缀匹配原则)。
11. 最左前缀匹配原则
在使用联合索引时,MySQL会根据联合索引中的字段顺序,从左到右依次到查询条件中去匹配;如果查询条件中存在与联合索引中最左侧字段相匹配的字段,则就会使用该字段过滤掉一批数据,直至联合索引全部字段匹配完成,或遇到范围查询,如<
, >
,between
, 以%开头的like
等条件,才会停止匹配。
因此,优化索引的时候,可以将区分度高的字段放在最左边,这样能够过滤更多数据。
12. 索引在什么时候失效?
- where条件中有or;
- 索引是表达式的一部分;
- 索引是函数的参数;
- 在索引的类型上进行隐形数据转换;
- 使用like查询时以%开头;
13. MySQL如何为表字段添加索引?
-
添加主键索引:
alter table 'users' add primary key ('id')
-
添加唯一索引
alter table 'users' add unique ('id')
-
添加普通索引:
alter table 'users' add index indexName ('id')
-
添加全文索引:
alter table 'users' add fulltext ('id')
-
添加联合索引:
alter table 'users' add index indexName ('id', 'name', 'age')
-
删除主键索引:
alter table 'users' drop primary key
-
删除其他索引:
alter table 'users' drop key indexName