1、什么是索引

索引就是提高 MySQL 的查找效率的数据结构。也就是说索引的本质是数据结构。简单的理解为:排好序的快速查找数据结构

索引的作用:查找排序,所以在 SQL 语句中会影响 while 后面的查询条件以及 order by 后面的排序。

一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。

平常所说的索引,如果没有特别说明,都是指 B 树(多路平衡查找树,并不一定是二叉树)组织结构的索引。其中聚集索引、次要索引、覆盖索引、符合索引、前缀索引、唯一索引默认都是使用 B+ 树索引。当然除了 B 树、B+ 树类型的索引,还有哈希索引(hash index)。

2、索引的优缺点

优点:

  • 类似于大学图书馆建书目索引,提高数据的检索效率,降低数据库 IO 成本。(将随机 IO 转换成有序 IO)。
  • 通过索引列对数据进行排序,降低数据排序的成本,降低了 CPU 的消耗。

缺点:

  • 实际上索引是一个描述数据表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是需要占用内存空间
  • 索引在提高数据的检索效率的同时,也降低了数据库表的更新效率,如对表进行 insert、update、delete。

    • 因为在更新表的时候,MySQL 不仅要保存数据,还要保存索引文件中每次更新添加的新的索引列的字段以及每次由于更新所带来的的键值变化的索引信息。
  • 索引只是提高效率的一个因素,如果 MySQL 有大量数据的表,就需要花时间研究建立最优秀的索引,或者优化查询。

3、索引分类

  • 单值索引

    • 一个索引只包含单个列,一个表可以有多个单值索引
  • 唯一索引

    • 索引列的值必须唯一,允许为空
  • 符合索引

    • 一个索引包含多个列

4、基本语法

  • 创建索引

    CREATE [UNIQUE] INDEX index_name ON table_name(column_name(length));
    ALTER table_name ADD [UNIQUE] INDEX index_name ON column_ame(length);
    • 使用 ALTER 命令添加索引

      // 添加主键索引,意味着索引值必须唯一,且不能为 null
      ALTER TABLE table_name ADD PRIMARY KEY(column_list);
      
      // 添加唯一索引,意味着索引值必须唯一,可以为空值。
      ALTER TABLE table_name ADD UNIQUE(column_list);
      // 删除唯一索引
      ALTER TABLE table_name DROP INDEX (column_list);
      
      // 添加普通索引,索引值可以出现很多次
      ALTER TABLE table_name ADD INDEX index_name(column_list);
      
      // 添加全文索引,指定索引为 FULLTEXT
      ALTER TABLE table_name ADD FULLTEXT(column_list);
  • 删除索引

    DROP INDEX index_name ON table_name;
  • 查看索引

    SHOW INDEX FROM table_name;

5、MySQL 索引结构

下面以 B+ 树结构为例来演示索引的检索原理:

B+树索引结构

【初始化介绍】

如图,一棵 B+ 树,浅蓝色的称为一个磁盘块,在叶子节点的磁盘块中,都包含 key 值(蓝色数值所示)和数据项 data,其他节点的磁盘块包含 key 值(蓝色数值所示)和指针(黄色所示)。也就是说只有叶子节点才会存储真实数据 data,而非叶子节点不存储真实数据 data。

以磁盘块1为例:指针 P1 指向 小于 17 的磁盘块 2,指针 P2 指向大于 17 小于 35 的磁盘块 3,指针 P3 指向 大于 35 的磁盘块 4。

【查找过程】

例如:要查找 key 值为 29 的数据项。首先,将磁盘块1 加载到内存中,此时发生第一次 IO,在内存中使用二分查找确定 29 在 17 和 35 之间,锁定磁盘1 的 P2 指针,内存内时间比较短(相比于磁盘的 IO)可以忽略不计;通过磁盘1 的 P2 指针的磁盘地址将磁盘3 加载到内存中,此时发生第二次 IO,在内存中通过二分查找确定 29 在 26 和 30 之间,锁定磁盘3 的 P2 指针;通过磁盘3 的 P2 指针的磁盘地址将磁盘9 加载到内存中,此时发生第三次 IO,再通过二分查找找到 key 值为29的数据项,查找结束,总计三次 IO。

6、哪些情况下适合建索引,哪些情况下不适合键索引

适合建索引的情况:

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段即 频繁作为 where 后面的查询条件
  • 查询中与其他表关联的字段,外键关系建立索引
  • 查询中作为排序条件的字段,即作为 ORDER BY 后面的排序条件的字段

    • 排序字段如果通过索引去访问将大大提高排序的速度
  • 查询中统计或者分组字段,例如:GROUP BY 后面所跟的字段

不适合建立索引的情况:

  • 表记录太少
  • 经常增删改的表
  • 数值重复率太高且分布平均的表字段

    • 例如:一个表有 10 万行数据,表中有一个性别字段只有男和女,且分布概率大约为 50%,也就是说有大约 5 万条记录的性别为男,大约 5 万条记录的性别为女,此时对表的 性别 字段建立索引一般不会提高数据库的查询效率。
    • 这里需要提到索引的选择性问题
      什么是索引的选择性?索引的选择性是指索引列中不同值的数目与表中记录数的比。

      • 例如:一个表中有 2000 条记录,表索引列有 1980 个不同的值,也就是说作为索引的表字段的值有 1980 种,那么这个索引
        的选择性就是 1980 / 2000 = 0.99,一个索引的选择性越接近 1,这个索引的效率就越高。
最后修改:2021 年 10 月 08 日 11 : 31 AM
如果觉得我的文章对你有用,请随意赞赏