引入索引
在每本书前面,都会有目录,而目录设计就是为了方便人们快速查找自己想看的内容。试想一下,如果书不这样设计呢?就张三查询的时间就足够看好一阵子了,那么暴躁的张三就可能在看书的时候把书扔了。大多时间都花费在查找上,效率低下。而索引就是为了提高数据库的检索速度而 “生的”。
索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
索引使用场景
索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。
- 可以创建索引的列:
- 经常需要查询(搜索)的列上,可以加快搜索速度;
- 经常需要根据范围进行搜索的列上创建索引。原因是索引已经排序了(依托于索引的结构),其指定的范围肯定是连续的;
- 经常需要排序的列上创建索引。原因是索引已经排序,可以加快排序速度;
- 经常使用 where 语句的列上。
其它情况下需要考虑是否要建立索引,原因如下:
- 增加索引会降低系统维护速度,增大需求空间;
- 更新字段的过程中,需要维护 B+ 树结构(索引的结构),会频繁更新索引文件,降低SQL性能;
索引分类
- 普通索引:普通索引就是最基本的索引,没有任何限制。
使用 sql 语句创建:
alter table 表名 add index 索引名称(列名);
# 或者
create index 索引名称 on 表名(列名);
- 唯一索引:唯一索引的列值必须唯一,允许为null。
使用 sql 语句创建:
alter table 表名 add unique index 索引名称(列名);
# 或者
create unique index 索引名称 on 表名(列名);
- 主键索引:主键索引是一种特殊的唯一索引,并且一张表只有一个主键,不允许为null。
使用 sql 语句创建:
alter table 表名 add PRIMARY KEY (列名);
- 联合索引:联合索引是同时在多个字段上创建索引,查询效率更高。
使用 sql 语句创建:
alter table 表名 add index 索引名(列名1, 列名2, 列名3);
- 全文索引: 全文索引主要用来匹配字符串文本中关键字。
使用 sql 语句创建:
alter table 表名 add fulltext index 索引名称(列名);
# 或者
create fulltext index 索引名称 on 表名(列名);
索引结构
MySQL 常用的索引结构是 B- 树,B+ 树(索引底层数据结构)等。
B-
B-(B杠树),也称B树。是一个N叉搜索树。
上述就是一个B数。其特点如下:
- 子节点的节点个数为父亲节点 key 值个数 +1
- 当节点的子树和保存的key多了,意味着在同样key的个数的前提下B树的高度就要比二叉搜索树低很多出,这样一来执行访问硬盘的次数就会减少,提高访问效率
- 结点中的key总是升序排序
- 最左侧子节点中的key值小于父亲节点中最小的key值
- 最右侧子节点中的key值大于父亲节点中最大的key值
- 中间子节点的key值在父亲结点(key1,key2)范围内
B+树
B+ 树在B树的基础上进行改进。也是N叉搜索树。
特点:
- 一个节点可以存储 N 个key, 而这个节点有 N 个子节点
- 每一个节点中的key值都会在子节点中存在(同时该key是子结点中的最大值或最小值)
- B + 树的叶子结点是首尾相连的,类似一个链表
- 叶子节点是完整的数据集合,只在叶子节点保存数据表中的每一行数据,非叶子节点只保存key值本身即可
优点:
- 一个节点可以保存更多的key,树的高度就会更矮. 查询时减少访问硬盘次数
- 所有查询最终都会落到叶子节点. 即查询任何一个数据 ,IO访问次数是一样的 .
- 所有叶子节点构成一个链表,方便进行范围查询
- 非叶子节点只存储key值,占用空间更少,就有可能在内存中缓存,进一步减少 IO 次数
补充,保存数据:
只在叶子结点保存完整的数据,而非叶子结点保存key值即可。
- 当一个表有多个主键时(Id,name),先按照 Id 为主键构建出 B+ 树,通过叶子节点组织所有数据行;
- 再以 Name 构建出 B + 树,该树的叶子节点就不再存储完整的数据行,而是存储主键 id
- 若根据Name进行查询,先查询到叶子节点得到主键id,再通过主键id去主键的 B+ 树里面查找.