Index in MySQL

Weiyin
203 阅读5分钟

Index in MySQL

所谓索引，其主要功能就是帮助我们快速找到想要的数据。比如当我们使用字典查询某一个字时，可以根据拼音和音标，就能快速找到那个字的解释。如果一页一页找，那真不知道找到猴年马月。

常见的索引数据结构

  • 哈希表（Hash Table）
  • 平衡二叉搜索树（Balanced Binary Search Tree）
  • 跳表（Skip List）
  • 多路平衡搜索树

这些是常见的索引数据结构，用于加速数据的检索和访问。不同的数据结构在查询性能、插入/删除效率、空间占用等方面有所不同，具体的选择取决于应用场景和数据访问模式。

Index in InnoDB

InnoDB 作为MySQL的默认存储引擎，使用B+树作为索引结构。表中的每个索引都对应一个独立的B+树。

B+树索引分为主键索引（也叫聚簇索引）和辅助索引两种类型。主键索引是使用主键所在列的所有值构造而成的B+树，其叶子节点存储了整张表的行记录信息。辅助索引，即除了主键索引以外的索引，是使用除主键列的某一列（或多列）的所有值构造而成的B+树，其叶子节点存储的是列上的值和对应的主键值。使用索引可快速访问数据库表中的特定信息。不需要遍历表中的所有记录。

InnoDB为什么使用B+树作为索引结构？
  1. B+树是一种多路平衡搜索树，每个节点可以存储更多的数据和指针，因此可以减少查找时需要访问的节点数，降低磁盘I/O次数。
  2. B+树还是一种有序的数据结构，其叶子节点的有序性使得B+树在范围查询、排序和顺序访问时非常高效。

索引的优缺点

优点：

  • 加快数据的检索速度

缺点：

  • 维护索引要耗费时间。当对表中的数据进行增加、删除和修改的时候，索引也要动态维护。当我们对B+树插入数据时（即表中新增一条记录），树中的节点可能分裂；删除数据时，节点可能合并。

相关操作

1. 创建用户表
create table t_user(
    id bigint primary key auto_increment,
    nickname varchar(20),
    birth date,
    regist_time datetime
);
2. 查看索引
SHOW INDEX FROM 表名;
show index from t_user;
3. 建立索引
create [unique] index 索引名 on 表名(列名 [asc|desc]);
3.1 为 nickname 列上的所有值建立索引
create index nickname_index on t_user(nickname);
3.2 对 regist_time 列建立逆序索引
create index regist_index on t_user(regist_time desc);
3.3 建立复合索引
create index nick_regist_index on t_user(nickname,regist_time);
4. 插入数据
insert into t_user values(1,'Miku','1998-08-21',now());
5. 删除索引
drop index 索引名 on 表名;
drop index nickname_index on t_user;
6. 解释搜索语句，可以查看SQL语句是否走索引。key为NULL表示没有走索引。
explain select * from t_user where nickname = 'Miku';

关于复合索引

在复合索引中，列的排列顺序非常重要，原则上，应该首先定义最唯一的列。只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

通过 EXPLAIN 语句可以检查索引是否被使用。

InnoDB的存储

MySQL以Page为单位管理，每次从磁盘里读出一页。

表的结构：

Tablespace

  • Leaf node segment，保存记录相关信息

    • Extent，默认 1MB

      • Page，默认 16KB。一个Extent有64个page

        • 若干记录

  • Non-Leaf node segment，保存索引相关信息

  • Rollback segment，保存事务回滚相关信息

Page

  • page header
  • row - 记录，按插入顺序排序。
  • page directory - 类似目录，保存主键及对应row的位置，有序、定长

当从磁盘读出一页，拿到页的 page directory，二分查找，找到想要的记录。

如果想要找的快，page大小设小一些，但是设小的话，读出来的数据就少，IO花的时间会多。所以一般不改pageSize。 image.png

image.png

InnoDB的索引

这样的存储机制，MySQL是如何寻找记录的？

  • 对于主键索引

image.png

当使用主键索引进行查询时，得到的是满足查询条件的行数据本身。因为它的叶子节点直接存储了完整的行数据。

注：

InnoDB使用自适应哈希索引（Adaptive Hash Index）来加速对页的访问。通过在内存中创建和维护额外的哈希索引，提供更快的哈希查找能力，优化特定的查询。

  • 对于辅助索引，即除了主键索引以外的索引。

依然是B+树

而当使用辅助索引（非主键索引）进行查询时，得到的是满足查询条件的辅助索引键值所对应的行的主键值。然后再通过主键索引再次查找到对应的行数据。

image.png

插入数据伤害最大的是辅助索引，因为无法保证有序，所以很容易发生节点分裂。所以仅在外键建立辅助索引。

总结

对于全文检索，不要用关系数据库做，用ES。

对于统计，要么在业务非高峰做，要么抽出去不要在我这做。

数据库设计原则：

  1. 主键自增。降低索引树节点分裂的可能性。
  2. 记录逻辑删除。避免节点合并。非要删，在业务非高峰期删。
  3. 最多为外键建立索引。

索引失效

  1. 模糊查询 以 %开头 ，索引失效

    where name like '%三'

  2. 复合索引中，没有使用左侧的列查找，索引失效

  3. 索引列参加了运算，索引失效

    比如 keep + 1 = 8848;

    round(keep, 0) = 8848;

avatar
文章
阅读
粉丝