数据库索引 | 青训营笔记

vwmin
103 阅读4分钟

这是我参与「第三届青训营 -后端场」笔记创作活动的第2篇笔记

数据结构

构建对数据的快速查询，通常使用B树，B+树，Hash

  • Hash，好用，但是不支持范围查询
  • B树

索引类型

  • 主键索引，根据主键列做索引，没有会自动生成，不可为null

  • 二级索引（辅助索引），二级索引的叶子结点存的是主键，也就是说通过二级索引找到的是主键

    • 唯一索引（Unique Key）。是一种约束，而不是为了查询效率。一张表可以有多个，唯一索引要求指定的属性唯一，但可为null。
    • 普通索引（Index），是为了查询效率，一张表可以有多个，允许重复和null。
    • 前缀索引（Prefix），是为了查询效率，适用于字符串，对字符的前缀创建，数据量小
    • 全文索引（Full Text），全文索引主要是为了检索大文本数据中的关键字信息。

聚集索引

聚集索引，即索引结构与数据一起存放的索引。主键索引属于聚集索引。

InnoDB中.idb文件就包含了该表的索引和数据，B+树的非叶子节点存索引，叶子结点存索引和数据

优点

查询很快，定位到索引就定位到了数据

缺点

  • 依赖有序的数据
  • 更新代价大

非聚集索引

非聚集索引，即索引结构与数据分开存放的索引。二级索引属于非聚集。根据需要，可能拿到主键回表查。

优点

更新代价小。

缺点

  • 依赖有序数据
  • 可能需要二次查询

覆盖索引（一种查询情况）

查询的内容，恰好是索引的key，这时候查到了索引就返回索引。例如查select id from xxx where id=1

创建索引的注意

  1. 选择合适的字段

    1. 非空
    2. 频繁查询
    3. 被作为条件查询where
    4. 需要排序
    5. 逻辑上的外键

  2. 频繁更新的字段尽量不建立

  3. 多个字段联合建立索引，节约空间时间

  4. 避免冗余的索引

  5. 字符串使用前缀索引代替普通索引

使用索引的注意

  • 特大型表索引开销很大，不建议
  • where中加函数，会无法命中索引
  • 使用与业务无关的自增ID作为主键
  • 删除长期未使用的索引，查询sys库schema_unused_indexes视图

如何添加索引

  • 主键索引

    ALTER TABLE `xxx` ADD PRIMARY KEY (`xx`)

  • 唯一索引

    ALTER TABLE `xxx` ADD UNIQUE(`xx`)

  • 普通索引

    ALTER TABLE `xxx` ADD INDEX index_name ( `xx` )

  • 全文索引

    ALTER TABLE `xxx` ADD FULLTEXT ( `xx`)

  • 多列索引

    ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

为什么采用B+树而不是B树

  • B树的非叶节点需要存储数据值，而B+树不需要，当数据库页大小固定时，B+树一个节点可以存储更多的索引
  • B树的数据值散布在非叶节点中，而B+树数据值在叶节点形成链表，且有序，可以方便地做？

索引读盘次数

  • 根节点常驻内存
  • 不在内存的页放入内存
  • 如果是范围查找，到达叶节点后可以顺序遍历
  • 非聚集需要回到聚集索引查询

视图

视图是一张虚拟表，内容由查询定义。

有表结构文件，但并不存数据，数据在引用视图时通过查询获得。

起到类似筛选显示数据的作用。

优化全表扫描

  • where xxx == nullNull无法建立索引只能全表扫描，考虑在条件中去掉null
  • where xxx != xxx不等判断无法索引只能全表扫描
  • or是

联合索引

多个列共同作为一个索引，即联合索引。如

UNIQUE KEY `idx_a_b_c` (`a`,`b`,`c`) USING BTREE

数据库使用联合索引需要语句满足最左匹配，即where中至少有第一个a，

以下语句使用联合索引：

  • where a = '333' and b = '333' and c = '333';
  • where a = '333' and b = '333';
  • where a = '333' and c = '333';
  • where c = '333' and b = '333' and a = '333';

以下语句不使用联合索引：

  • where b = '333' and c = '333';
  • where (a = ’333’ or b = ‘333’) and c = ‘333’;

索引合并

如果where语句中出现对多个字段AND、OR，那么可能会发生索引合并，即按照各自的索引分别进行条件扫描，然后各自的结果进行合并。

但是如果出现了 index intersect merge，那么一般同时也意味着我们的索引建立得不太合理，因为 index intersect merge 是可以通过建立 复合索引进行更一步优化的。

SELECT * FROM t1 WHERE key1=**1** AND key2=**2** AND key3=**3;**

显然可以建立一个联合索引，扫描一次

avatar
文章
阅读
粉丝