索引简介

索引简介

1、索引是什么

索引是个什么东东？

  1. MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
  2. 你可以简单理解为"排好序的快速查找数据结构"，即索引 = 排序 + 查找
  3. 一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上
  4. 我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
  5. 聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

2、索引原理

将索引理解为"排好序的快速查找数据结构"

  1. 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
  2. 下图就是一种可能的索引方式示例：
    • 左边是数据表，一共有两列七条记录，最左边的十六进制数字是数据记录的物理地址
    • 为了加快col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

image-20200803222848380
image-20200803222848380

3、索引优劣势

索引的优势

  1. 类似大学图书馆的书目索引，提高数据检索效率，降低数据库的IO成本
  2. 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

索引的劣势

  1. 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如果对表INSERT，UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  3. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

4、MySQL 索引分类

mysql 索引分类

参考资料：https://www.cnblogs.com/luyucheng/p/6289714.html

  1. 普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
  2. 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
  3. 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
  4. 复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
  5. 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

5、MySQL 索引语法

建立索引的 SQL 语句

  1. 创建索引：

    • 如果是CHAR和VARCHAR类型，length可以小于字段实际长度；
    • 如果是BLOB和TEXT类型，必须指定length。
    CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
' or '
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

  2. 删除索引

    DROP INDEX [indexName] ON mytable;

  3. 查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）

    SHOW INDEX FROM table_name\G

使用 ALTER 命令，有四种方式来添加数据表的索引：

  1. ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  2. ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)：这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
  3. ALTER TABLE tbl_name ADD INDEX index_name(column_list)：.添加普通索引，索引值可出现多次。
  4. ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)：该语句指定了索引为FULLTEXT，用于全文索引。

带你看看 mysql 索引：Index_type 为 BTREE

mysql> show index from tbl_emp;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_emp |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_emp |          1 | fk_dept_Id |            1 | deptId      | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

6、MySQL 索引结构

6.1、Btree 索引

Btree 索引搜索过程

【初始化介绍】

  1. 一颗 b 树， 浅蓝色的块我们称之为一个磁盘块， 可以看到每个磁盘块包含几个数据项（深蓝色所示） 和指针（黄色所示）
  2. 如磁盘块 1 包含数据项 17 和 35， 包含指针 P1、 P2、 P3
  3. P1 表示小于 17 的磁盘块， P2 表示在 17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块
  4. 真实的数据存在于叶子节点和非叶子节点中

【查找过程】

  1. 如果要查找数据项 29， 那么首先会把磁盘块 1 由磁盘加载到内存， 此时发生一次 IO， 在内存中用二分查找确定 29在 17 和 35 之间， 锁定磁盘块 1 的 P2 指针， 内存时间因为非常短（相比磁盘的 IO） 可以忽略不计
  2. 通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存， 发生第二次 IO， 29 在 26 和 30 之间， 锁定磁盘块 3 的 P2 指针
  3. 通过指针加载磁盘块 8 到内存， 发生第三次 IO， 同时内存中做二分查找找到 29， 结束查询， 总计三次 IO。

image-20200804093236612
image-20200804093236612

6.2、B+tree 索引

B+tree 索引搜索过程

【B+Tree 与 BTree 的区别】

B-树的关键字（数据项）和记录是放在一起的； B+树的非叶子节点中只有关键字和指向下一个节点的索引， 记录只放在叶子节点中。

【B+Tree 与 BTree 的查找过程】

  1. 在 B 树中， 越靠近根节点的记录查找时间越快， 只要找到关键字即可确定记录的存在； 而 B+ 树中每个记录的查找时间基本是一样的， 都需要从根节点走到叶子节点， 而且在叶子节点中还要再比较关键字。
  2. 从这个角度看 B 树的性能好像要比 B+ 树好， 而在实际应用中却是 B+ 树的性能要好些。 因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多， 树高比 B 树小， 这样带来的好处是减少磁盘访问次数。
  3. 尽管 B+ 树找到一个记录所需的比较次数要比 B 树多， 但是一次磁盘访问的时间相当于成百上千次内存比较的时间， 因此实际中B+ 树的性能可能还会好些， 而且 B+树的叶子节点使用指针连接在一起， 方便顺序遍历（范围搜索）， 这也是很多数据库和文件系统使用 B+树的缘故。

【性能提升】

真实的情况是， 3 层的 B+ 树可以表示上百万的数据， 如果上百万的数据查找只需要三次 IO， 性能提高将是巨大的，如果没有索引， 每个数据项都要发生一次 IO， 那么总共需要百万次的 IO， 显然成本非常非常高。

【思考： 为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？】

  1. B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。 因此其内部结点相对 B 树更小。 如果把所有同一内部结点的关键字存放在同一盘块中， 那么盘块所能容纳的关键字数量也越多。 一次性读入内存中的需要查找的关键字也就越多。 相对来说 IO 读写次数也就降低了。
  2. B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点， 而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同， 导致每一个数据的查询效率相当。

image-20200811145838930
image-20200811145838930

7、何时需要建索引

哪些情况下适合建立索引

  1. 主键自动建立唯一索引
  2. 频繁作为查询的条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引
  5. Where 条件里用不到的字段不创建索引
  6. 单间/组合索引的选择问题，Who？（在高并发下倾向创建组合索引）
  7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
  8. 查询中统计或者分组字段

哪些情况不要创建索引

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

案例分析：

  1. 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
  2. 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
  3. 一个索引的选择性越接近于1，这个索引的效率就越高。

转自此文章 与 B站此视频

posted on 2021-10-11 09:42  寄居の友人c  阅读(835)  评论(0)  编辑  收藏  举报