文章目录
一、索引优缺点
索引的优点
- 大大加快数据的查询速度;
- 使用分组和排序进行数据查询时,可以显著减少查询时分组和排序的时间;
- 创建唯一索引,能够保证数据库表中每一行数据的唯一性;
- 在实现数据的参考完整性方面,可以加速表和表之间的连接;
索引的缺点
- 创建索引和维护索引需要消耗时间,并且随着数据量的增加,时间也会增加;
- 索引需要占据磁盘空间;
- 对数据表中的数据进行增加,修改,删除时,索引也要动态的维护,降低了维护的速度;
二、索引结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的索引结构,主要包含以下几种:
- BTree索引:最常见的索引类型,大部分引擎都支持 B+ 树索引 。
- Hash索引:底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询 。
- R-tree(空间索引):空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类 型,通常使用较少 。
- Full-text(全文索引):是一种通过建立倒排索引,快速匹配文档的方式。类似于 Lucene,Solr,ES。
2.1、B+Tree索引
B+Tree索引进化:
- 二叉树:
- 缺点:
- 顺序插入时,会形成一个链表,查询性能大大降低。
- 大数据量情况下,层级较深,检索速度慢。
- 缺点:
- 红黑树:
- 缺点:大数据量情况下,层级较深,检索速度慢。
- B-树:
-
简介:B-Tree,B树是一种多叉路衡查找树,相对于二叉树,B树每个节点可以有多个分支,即多叉。 以一颗最大度数(max-degree)为5(5阶)的b-tree为例,那这个B树每个节点最多存储4个key,5 个指针。
-
特点:5阶的B树,每一个节点最多存储4个key,对应5个指针。 一旦节点存储的key数量到达5,就会裂变,中间元素向上分裂。 在B树中,非叶子节点和叶子节点都会存放数据。
-
- B+树
-
B+Tree是B-Tree的变种,我们以一颗最大度数(max-degree)为4(4阶)的b+tree为例,来看一 下其结构示意图:
-
特点:所有的数据都会出现在叶子节点。 叶子节点形成一个单向链表。 非叶子节点仅仅起到索引数据作用,具体的数据都是在叶子节点存放的。
-
- 优化的B+树
- 在原B+Tree的基础上,增加一个指向相邻叶子节点 的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能,利于排序。
2.2、Hash索引
-
结构:哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在 hash表中。如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
-
特点:
- Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,< ,…)
- 无法利用索引完成排序操作
- 查询效率高,通常(不存在hash冲突的情况)只需要一次检索就可以了,效率通常要高于B+tree索引
-
存储引擎支持:
- 存储引擎支持 在MySQL中,支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能,hash索引是 InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。
思考题:
为什么InnoDB存储引擎选择使用B+tree索引结构?
- 相对于二叉树,层级更少,搜索效率高;
- 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,但是存储索引结构的一个页的大小有限,这样导致一页中存储 的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
- 相对Hash索引,B+tree支持范围匹配及排序操作;
三、索引分类
-
在MySQL数据库,将索引的具体类型主要分为以下几类:主键索引、唯一索引、常规索引、全文索引。
- 主键索引:针对于表中主键创建的索引,默认自动创建, 只能 有一个,关键字:PRIMARY
- 唯一索引:避免同一个表中某数据列中的值重复,可以有多个,关键字: UNIQUE
- 常规索引:快速定位特定数据,可以有多个
- 全文索引:全文索引查找的是文本中的关键词,而不是比 较索引中的值,可以有多个 关键字:FULLTEXT
-
在InnoDB存储引擎中,根据索引的存储形式,又分为两种:
- 聚集索引:将数据存储和索引放到一块,索引的叶子节点保存行数据,必须要有且只能存在一个。
- 二级索引:将数据和索引分开存储,索引结构的叶子节点关联的是对应的主键,可以存在多个
-
聚集索引和二级索引具体结构如下:
- 聚集索引的叶子节点下挂的是这一行的数据 。
- 二级索引的叶子节点下挂的是该字段值对应的主键值。
-
具体查找过程:
- 由于是根据name字段进行查询,所以先根据name='Arm’到name字段的二级索引中进行匹配查 找。但是在二级索引中只能查找到 Arm 对应的主键值 10。
- 由于查询返回的数据是*,所以此时,还需要根据主键值10,到聚集索引中查找10对应的记录,最 终找到10对应的行row。
- 最终拿到这一行的数据,直接返回即可。
-
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引 * 不存在主键,使用第一个唯一索引作为聚集索引
- 没有主键和唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
思考
以下SQL语句那个执行效率高?select * from user where id = 1; select * from user where name = 'Arm'
备注:id为主键,name字段创建索引
答案:第一条效率高,因为没有回表查询
回表查询: 这种先到二级索引中查找数据,找到主键值,然后再到聚集索引中根据主键值,获取数据的方式,就称之为回表查询。
四、索引语法
- 创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_nam