SQL 索引 - 掘金

索引是什么

优势:

可以提高数据检索的效率,降低数据库IO成本,类似于书的目录
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗
- 被索引的列会自动进行排序,包括单列索引和组合索引,只是组合索引的排序要更复杂
- 如果按照索引列的顺序进行排序,对应order by语句来说,效率会提高很多

劣势:

主键索引: 索引列中的值必须是唯一的,不允许有空值

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

普通索引: MySQL中基本索引类型,没有什么限制,允许再定义索引的列中插入重复值和空值(非聚集索引)

ALTER TABLE table_name ADD INDEX index_name (column_name);
唯一索引: 索引列中的值必须是唯一的,但是允许为空值

CREATE UNIQUE INDEX index_name ON table_name(column_name);
全文索引: 只能在文本类型CHAR、VARCHAR、TEXT类型字段上创建全文索引,字段长度比较大时,如果创建普通索引,在进行like模糊搜索时效率比较低,这时可以创建全文索引 (MyISAM和InnoDB中都可以使用全文索引)
空间索引: MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型,MySQL在空间索引方面遵循OpenGIS几何数据模型规则.MySQL - 空间数据结构
前缀索引: 再问题贲类型如CHAR、VARCHAR、TEXT类型上创建索引时,可以指定索引列的长度,但是数值类型不能指定 ALTER TABLE table_name ADD INDEX index_name(column(length));
按照索引的数量区分
- 单列索引: 索引中只有一个列
- 组合索引: 使用两个以上字段创建的索引
组合索引的使用,需要遵循最左前缀原则(最左匹配原则)
一般情况下,建议使用组合索引代替单列索引(主键索引除外) ALTER TABLE table_name ADD INDEX index_name(column1, column2);

索引的数据类型需要满足两个场景

场景一等值查询: 根据某个值查找数据(最好的数据结构应该是hash, 时间复杂度O(1))

SELECT * FROM table_name WHERE id = 1

场景二范围查询:根据某个范围区间查找数据

SELECT * FROM table_name WHERE id < 10

同时需要考虑时间和空间因素,在执行时间方面,我们希望通过索引,查询数据的时间尽可能小;在存储空间方面,我们希望索引不要消耗太多的内存和磁盘空间

需要同时满足两个场景时,可以使用二叉查找树(二叉排序树);

为了预防这种问题,可以选择平衡二叉查找树

特点: 时刻保持二叉树的左右子树的高度差小于等于1,如果大于1进行左旋或者右旋操作来保持平衡时间复杂度: O(log2n)
问题: MySQL的数据是存储在磁盘文件中的,查询处理数据时,需要先吧磁盘中的数据加载到内存中,磁盘IO操作非常耗时,访问二叉树的每个节点都会发生一次IO,当数据量过大时,从根节点遍历到叶子结点,需要多次磁盘IO(树的高度比较高)
解决方案: 让树的高度降低,那就不要局限于二叉树了,让这颗树分更多的

e.g:key为bigint=8字节,每个节点有两个指针,每个指针为4字节,一个节点占用空间16字节,在InnoDB引擎中,一次IO会读取一页16K的数据量,而二叉树一次IO的有效数据量只有16字节,利用率极低

使用b-tree (b树,不是b-树)

特点: 每个节点中保存多个元素,每个元素的左右两侧各有一个指针,指向下一个节点(左边元素小于根节点,右边元素大于等于根节点)
1. b树的节点中存储着多个元素,每个内节点有多个分叉
2. 节点中的元素包含键值和数据,节点中的键值从大到小排列(所有的节点都储存数据)
3. 父节点当中的元素不会出现在子节点中
4. 所有的叶子结点都位于同一层,叶节点具有相同的深度,叶节点之间没有指针连接
问题:
1. 节点的度,取决于数据行的大小,数据页的大小固定为16K,数据行大的时候,度会变小(原因:中间节点保存了数据,占了过多的空间);
2. 范围查询时效率不高(需要在父子磁盘块间 - 反复横跳)

终极解决方案: b+tree

特点:
1. 中间节点不保存数据,只包含主键信息,所有数据放到叶子结点中 - 解决节点占用空间过大
2. 每个叶子结点通过双向指针链接 - 范围查询时,可以通过双向指针横向遍历直接跳转到下一个存放数据的磁盘块(数据有序存储)

.ibd文件中保存索引和数据信息,索引和数据保存在同一个文件中,
- 主键索引:叶子结点就是记录行,存储完整的数据信息,根据主键索引查询,效率高于MyIsam引擎(IO次数少一次),因为数据和索引聚集存储在一起,所以也叫聚集索引/聚簇索引
- 辅助索引:辅助节点中,叶子节点保存主键信息,查询数据需要根据辅助索引找到对应的主键信息,然后根据主键索引找到对应的记录, 查询主索引的过程叫做回表, 效率低于MyIsam引擎