index.md

·interview-questions

索引

什么是索引？

索引是帮助存储引擎快速获取数据的一种数据结构，索引就是数据的目录

索引和数据一样，都位于存储引擎中

索引有哪几种？

一个索引是可以同时属于好几类的，比如主键索引既是 B+ 树索引，又是聚簇索引，通常又是一个联合索引或单列索引

  • 按数据结构：
    • B+ 树索引
      • 基于 B+ 树的索引结构，所有的数据（或主键）都出现在叶子节点，非叶子节点只存索引键 + 指针
      • 叶子节点里的数据是按照主键顺序存放的
      • 叶子节点之间是双向链表，层高一般很低，三到四层就可以覆盖几百万行，I/O 次数少，范围查询效率高
    • 哈希索引
      • 用一个哈希函数把索引列映射到哈希表的桶里，查找是算哈希值、定位桶，然后在桶里线性查找
      • 只适合等值查询，不支持范围查询，因为哈希之后是没有顺序概念的，且哈希冲突会影响性能
      • 本质上是一张哈希表，MEMORY 引擎支持显式哈希索引，InnoDB 更多是内部通过自适应哈希索引来加速热点数据访问，它会在内存里对热点页自动建一层哈希来加速访问，但是是没办法手动创建的
    • 全文索引
      • 主要用于长文本的搜索，底层是倒排索引，是把词项映射到包含这个词的文档或行列表
      • 可以做分词和相关性排序，适合做某个字符串是否出现在文本中这种模糊查询
  • 按物理存储：
    • 聚簇索引（主键索引）
      • 数据本身按索引 key 的顺序存储，表数据按照主键的顺序存储在 B+ 树的叶子节点上，叶子节点存的是完整的行数据
      • 也就是说 InnoDB 的数据文件本身就是一个按主键排序的 B+ 树，非叶子节点记录主键值和指针，叶子节点存完整行数据，这棵树就是聚簇索引
      • 一张表只能有一个聚簇索引，最好用自增整型来做主键，避免频繁页分裂
    • 二级索引（辅助索引）
      • 除了聚簇索引以外的其他 B+ 树索引都是二级索引，叶子节点存的是索引列 + 主键列
      • 查二级索引时，先在二级索引树上定位到叶子节点，然后通过主键回聚簇索引查完整行数据，也就是要查找两个 B+ 树才能找到数据，这被称为回表
      • 一张表可以有多个二级索引
    • 覆盖索引
      • 在二级索引的基础上，能在二级索引的 B+ 树就能满足查询所需的所有列，相当于索引包含了要查询的值，就不需要回表查聚簇索引
      • 这样查询只需要访问二级索引树，减少了 I/O 次数，提升查询性能
  • 按字段特性：
    • 主键索引（默认使用 B+ 树）
      • PRIMARY KEY (index_column_1)
      • 唯一标识一行数据，不能为空，在 InnoDB 中主键索引就是聚簇索引
      • 一张表只能有一个主键，一旦确定就不应该频繁修改
    • 唯一索引
      • UNIQUE KEY(index_column_1,index_column_2,...)
      • CREATE UNIQUE INDEX index_name ON table_name (index_column_1, index_column_2, ...);
      • 确保某一列或某些列的组合值在表内不重复
      • 允许有 NULL 值，但多个 NULL 被认为是不重复的
      • 同一张表可以有多个唯一索引，底层一般也是用 B+ 树实现
      • 适合用于用户手机号、邮箱、身份证号等天然唯一的字段
    • 普通索引
      • INDEX(index_column_1,index_column_2,...)
      • CREATE INDEX index_name ON table_name (index_column_1, index_column_2, ...);
      • 最普通的一种索引，只负责加速查询，不保证唯一性
      • 可以有重复值和 NULL 值
    • 前缀索引
      • CREATE TABLE table_name(column_list, INDEX(column_name(length)));
      • CREATE INDEX index_name ON table_name (column_name(length));
      • 对字符型字段，如 CHAR、VARCHAR、TEXT，只取前 N 个字符建立索引，而不是整个列
      • 可以节省存储空间和内存，但会降低索引的选择性，不能保证真正唯一
      • 在 select 需要完整值时，仍然需要回表查询完整数据
      • 只能保证前缀范围内有序，不支持后续字符的排序和范围查询
  • 按字段个数：
    • 单列索引
      • 只包含一个列的索引
      • 适合单字段查询频繁的场景
    • 联合索引（复合索引）
      • CREATE INDEX index_product_no_name ON products (product_no, product_name);
      • 索引 key 同时包含多个列，B+ 树是按照这些列的组合值从左到右排序的，可以多个前缀，也可以部分列
      • 在建立联合索引时，要把区分度大的字段排在前面，提高索引的选择性
      • 遵循最左前缀原则，查询条件必须从最左边的列开始才能使用索引，不符合最左前缀之所以会失效是因为索引树是按列的组合值排序的，跳过最左边的列就无法利用索引的有序性，利用索引的前提是索引里的 key 是有序的
      • 并且只有等值条件才能继续往后匹配，遇到范围查询则在范围查询字段后面的字段是无法利用联合索引的，因为只有最左边的的列是有序的，右边的字段的值在范围内是无序的
      • 但如果是 select * from t_table where a >= 1 and b = 2 这样的大于等于的范围查询，虽然在符合 a >= 1 条件的二级索引记录的范围里，b 字段的值是无序的，但是对于符合 a = 1 的二级索引记录的范围里，b 字段的值是有序的（先按照 a 字段的值排序，然后在 a 字段的值相同的情况下，再按照 b 字段的值进行排序），所以这条查询语句 a 和 b 字段都用到了联合索引进行索引查询
      • 如果是 SELECT * FROM t_table WHERE a BETWEEN 2 AND 8 AND b = 2，由于在 MySQL 中的 BETWEEN 是包含边界值的，所以这条查询语句 a 和 b 字段都用到了联合索引进行索引查询
      • 如果是 SELECT * FROM t_user WHERE name like 'j%' and age = 22，在符合前缀为 'j' 的 name 字段的二级索引记录的范围内，age 字段的值是无序的，但是对于符合 name = 'j' 的二级索引记录的范围里，age 字段的值是有序的（先按照 name 字段的值排序，然后在 name 字段的值相同的情况下，再按照 age 字段的值进行排序），所以在确定需要扫描的二级索引的范围时，可以通过 age 字段来进一步缩小范围，所以这条查询语句 name 和 age 字段都用到了联合索引进行索引查询
      • 综上，联合索引的最左匹配（前缀）原则在遇到大于或小于时，只有使用了范围查询的字段可以用到联合索引，其后面的字段是用不到的
      • 但如果是大于等于、小于等于、BETWEEN 或 like 前缀匹配的范围查询时，联合索引是会生效的
      • 在联合索引的 B+ 树中找到第一个满足条件的主键值之后，后续判断其他条件时是可以在联合索引遍历过程中就直接判断，过滤掉不满足条件的记录，而不是回到主键索引中判断，从而减少回表次数，这被称为索引下推，执行计划里出现了 Extra 为 Using index condition 时就说明启用了索引下推优化

针对下面这条 SQL，怎么通过索引来提高查询效率？select * from order where status = 1 order by create_time asc

给 status 和 create_time 建立一个联合索引，从而避免 MySQL 发生文件排序，直接让根据 status 筛选后的数据就是按照 create_time 排序好的，从而提升查询效率

在查询时，如果只用到 status 的索引，由于这条语句还需要对 create_time 进行排序，这时就会使用 filesort

什么时候需要创建索引，什么时候不需要创建索引？

索引最大的好处就是提高查询速度，但索引也是有缺点的：

  • 需要占用物理空间，数量越大，占用空间越大
  • 创建和维护索引需要耗费时间，并且时间会随着数据量的增加而增加
  • 会降低表的增删改的效率，因为 B+ 树为了维护索引有序性，每次增删改索引都会进行动态维护

适合使用索引的场景：

  • 字段有唯一性限制，比如商品编码
  • 经常用于 where 查询条件的字段
    • 能提升整个表的查询速度
    • 如果查询条件不止一个字段，可以建立联合索引
  • 经常用于 ORDER BY 和 GROUP BY 的字段
    • 在查询时可以避免一次排序
    • 建立索引之后在 B+ 树中的记录都是排序好的

不适合使用索引的场景：

  • where、group by、order by 中用不到的字段
    • 索引的价值是快速定位，如果起不到定位作用的字段通常是不需要创建索引的
  • 字段的选择性很低
    • 比如性别、年龄等，索引的选择性越低，使用索引的效率就越低，甚至会比全表扫描还慢
  • 表数据很少时
  • 经常被更新的字段
    • 频繁更新会导致索引频繁维护，影响性能

优化索引的方法有哪些？

前缀索引优化：

在一些大字符串的字段作为索引时，使用前缀索引可以减少索引字段大小，增加一个索引页中存储的索引值，有效提高索引的查询速度

但前缀索引不能被用作覆盖索引，并且 order by 也无法使用前缀索引

覆盖索引优化：

只通过 B+ 树的叶子节点就能满足查询所需的所有列，从二级索引中就能查询到记录，从而避免回表查询，减少了 I/O 次数，提高查询效率

主键索引自增：

在 B+ 树中，同一个叶子节点内的各个数据是按照主键顺序存放的，如果使用自增主键，那每次插入一条新纪录时，都是追加操作，而不需要重新移动数据，从而避免页分裂

页分裂是指如果每次插入时主键的索引值都是随机的，那插入时就有可能会插入到现有数据页中间的某个位置，导致需要移动其他数据来满足新数据的插入（因为 B+ 树的叶子节点是有序的），页分裂会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率

page-split

另外，主键字段的长度越小，二级索引的叶子节点也就越小（二级索引的叶子节点存放的是主键值），这样二级索引占用的空间也就越小

索引设置为 NOT NULL：

如果存在 NULL，优化器在做索引选择时就会更加复杂并且更难优化，因为 NULL 需要单独处理，比如在进行索引统计时，count 会忽略 NULL 值

并且 NULL 值是一个没有意义的值，它会占用物理空间

防止索引失效：

  • 避免在索引列上进行函数操作、计算或类型转换
  • 避免使用左或者左右模糊匹配的 like 查询
  • 联合索引的使用要遵循最左前缀原则
  • 避免在 where 子句中在 or 前的条件列是索引列，而 or 后的条件列不是索引列

通过 EXPLAIN 分析查询语句，执行计划有以下参数：

  • possible_keys
    • 查询时可能使用的索引列表
  • key
    • 实际使用的索引
    • 如果为 NULL，说明没有使用索引
  • key_len
    • 使用的索引长度
  • rows
    • 扫描的数据行数
  • type
    • 找到所需数据时使用的扫描方式
    • 常见扫描类型的执行效率从低到高：
    • ALL
      • 全表扫描
    • index
      • 全索引扫描
      • 和 all 差不多，是对索引表进行全表扫描，不再需要对数据排序，但开销仍然很大
    • range
      • 索引范围扫描，使用索引的范围查询
      • 要尽可能让 SQL 查询可以使用到 range 这一级别及以上的扫描方式
    • ref
      • 非唯一索引扫描，或者是唯一索引的非唯一性前缀
      • 索引列的值并不唯一，仍要进行目标值附近的小范围扫描
    • eq_ref
      • 唯一索引扫描
      • 使用主键或唯一索引时的访问方式，通常使用在多表联查中，即 JOIN
    • const
      • 使用了主键或者唯一索引与常量值进行比较
  • extra
    • 额外信息
    • Using filesort
      • 查询语句中包含 group by，并且无法利用索引完成排序操作
      • MySQL 需要进行额外的排序操作
    • Using temporary
      • 使用了临时表保存中间结果，在对查询结果排序时 MySQL 使用了临时表
      • 常见于排序 order by 和分组 group by 操作
    • Using index
      • 覆盖索引
      • 查询只使用了索引树就能满足，不需要回表查询

性别和年龄要不要建立索引，以及记录的如果是初中生的年龄，还要不要建立索引？

一般都不值得给性别和年龄单独建索引，这两个字段的选择性都太低了，它们更多是作为联合索引中的后缀辅助字段

索引最主要的索引就是排除大量不相关行的，索引本身也是有成本的，另外年龄最大的问题是它是会改变的，每经过一年理论上所有人的年龄都要 update 一遍，那对应的索引页也要全部重建或者调整，在实际开发中更多是比如用生日或者入学年份来建索引，如果只有初中生的话那年龄的值也会非常少，这点上跟性别是类似的

是不是有索引就一定会走，优化器选择走索引的边界值是多少？

不是的，MySQL 的优化器会对走索引 + 回表和直接全表扫描这两种方案进行成本估算，选择成本更低的方案来执行查询，一般来说并没有一个明确的边界值，只有当某个条件能把数据过滤到比较小的比例时，比如几个百分点以内，走索引才有明显优势

如果这个条件本身选择性很差，比如性别、年龄等，或者能命中表里 20 - 30% 甚至更多的行，而且还要通过二级索引回表，那这种情况下优化器往往会觉得还不如直接顺序全表扫描，就不会用索引

所谓的边界值是跟统计信息、索引类型、是否覆盖索引以及磁盘 I/O 成本一起综合的结果，所以并没有一个固定的数值，实际中更多是通过分析执行计划来判断查询到底有没有走索引

你在设计一张表的时候，会建哪些索引？为什么？

我一般会从以下三类索引去设计，InnoDB 也会自动建立主键索引、唯一索引（如果有唯一约束）和外键索引（如果没有索引会自动给外键列建索引，方便 JOIN）

保证数据正确性的索引：主键索引、唯一索引

绝大多数表我都会明确定义主键，用于唯一标识一行的同时方便 InnoDB 的聚簇索引按照主键组织整张表，让很多按照主键的点查和更新都非常高效

唯一索引则是用来保证业务的唯一性，同时兼顾性能，凡是业务上要求不能重复的字段都会优先用 UNIQUE 来实现，而不是只依赖代码逻辑检查

支撑高频查询的索引：普通索引、联合索引（必要时前缀索引）

普通索引经常出现在 where、order by、group by、join on 等子句中，但更多情况下不会只建一个单列索引，而是会根据查询的实际情况，设计联合索引，遵循最左前缀原则，把区分度高的字段放在前面

另外如果字段是很长的字符串，例如 url、邮箱等，我会考虑用前缀索引来节省空间，提高索引的缓存命中率

特殊场景的索引：全文索引、空间索引

如果需要对长本文做关键词搜索而不是精确匹配，我会考虑用全文索引来提升搜索效率，再就是对地理位置数据做空间查询时会用到空间索引

你有用过联合索引吗，联合索引在匹配的时候要考虑什么？

有的，我在实际建表和调优里都会用到联合索引，联合索引在匹配时主要要考虑最左前缀原则、查询/排序模式和列的选择性以及能不能做成覆盖索引

联合索引本质上是按照多列拼在一起建的一棵 B+ 树，优化器在用它做索引查找时，只有从最左边开始连续命中的那几列才能被用到索引

如果使用了范围查询（比如 >、<、BETWEEN、LIKE 'prefix%' 等），或者 order by、group by 等排序操作，后续的列就无法再利用索引了

比如一个高频查询 SQL 是有 user_id、status、create_time 这三个字段，那我们就可以用 user_id、status 来过滤，然后用 create_time 来排序和 LIMIT，从而避免分表

那比如说你要在一张表上去加一个索引，这个会比较快吗，还是其实挺慢的，你有没有试过，就是说加索引的这个动作本身，不是说加索引的效果，相当于对表结构做变更嘛，我需要加一个索引，这个操作的速度怎么样？

加索引这个动作相当于是全表扫描一遍 + 按照这个索引重建一棵 B+ 树，所以它的耗时是取决于这张表的行数的，如果是几万行的小表那感觉上会很快，但如果是几百万行的大表，那加索引的耗时就会比较明显

InnoDB 在加索引时会大概走下面的几步：

  • 拿到元数据锁
    • 确保这张表的结构没有同时被别人修改，这一步会短暂阻塞其他 DDL
  • 从聚簇索引把整张表扫一遍
    • 对每一行，取出要新建索引的列
    • 按照索引的列组合值插入到新的 B+ 树结构中
    • 即全表扫描 + 大量写索引页
  • 做元数据切换，让新建的索引生效

假如说现在有一个表它现在需要加索引，它加索引的耗时跟哪些因素有关系？跟硬件的哪些因素会有关系吗？比如说你加这个索引的时候你是在一台机器上加，机器它硬件有不同的规格，比如像你刚刚说的磁盘 I/O，如果它是高性能的固态硬盘，I/O 就快，那还有一些这个什么样的影响因素吗？

加索引相当于全表扫描 + 按索引重建一棵 B+ 树，它的耗时主要取决于表本身和 MySQL 的运行环境（配置 + 硬件），例如数据量、索引本身特性、当前读写并发和 MySQL 版本的 DDL 算法、磁盘 I/O、CPU 性能和内存大小/命中率等

  • 表本身
    • 数据量
      • 行数越多，要扫描的行越多，添加索引的耗时就越长
      • 行越宽（很多列、TEXT/JSON 大字段多），每一页能装的行就越少，要读写的页就越多
    • 索引本身的特性
      • 联合索引的列越多，每条索引记录就越大，排序和写入的成本也就越高
      • 索引列的类型也会影响，比如字符串类型的索引记录通常比整数类型的更大
      • 如果是唯一索引，需要额外检查冲突，构建时也会多一些开销
    • 当前的读写并发
      • 加索引的过程中 InnoDB 要扫描聚簇索引，对每一行插入新索引页
      • 如果是在业务低峰期添加索引的话，读写压力小，添加索引会更快
    • MySQL 版本和 DDL 算法
      • 不同版本的 MySQL 对 DDL 的支持和优化不一样
      • 老版本使用的是 COPY，会建临时表，拷贝所有数据，然后整体切换，很慢并且锁表时间长
      • 新版本支持 INPLACE 和 INSTANT，使用 INPLACE、LOCK=NONE 时，添加索引时不会阻塞 DML 操作，且不需要复制数据，直接在原表上原地地构建索引，可以降低锁表时间，但本质上还是要全表扫描和构建索引树
  • 硬件
    • 磁盘类型和 I/O 能力
      • HDD 的随机读写很慢，SSD 的随机 I/O 和 fsync 的延迟都小很多，建索引时间会明显缩短
      • 如果是远程云盘的话会多一层网络延迟的影响
    • CPU 单核性能和核数
      • 建索引过程中有大量的排序和页结构维护操作，CPU 性能越好，速度越快
      • 空闲核数越多，整体的耗时也会越短
    • 内存影响
      • InnoDB Buffer Pool 如果足够大，就可以把大部分数据页和索引页都缓存到内存中，写索引时很多读写都能命中内存，可以减少磁盘 I/O
      • InnoDB 建立索引时还会用到排序缓冲和临时文件，sort_buffer_size 和 innodb_sort_buffer_size 越大，排序时就越多数据能放在内存中，减少磁盘临时文件的使用，从而提升建索引速度

我们再把这个场景具象一些，就是比如说现在有一个线上运行的服务，然后它有一个表结构，现在它要做这个表结构变更，可能是加索引，可能是加字段，那为了避免说这个表结构的变更动作对现有业务产生影响，或尽量减少影响，你觉得有什么样的这个准备工作或策略？

我会先看一下这次具体要改的是什么，因为不同的表结构变更对业务的影响是不一样的，然后看一下表有多大、访问多频繁、当前 MySQL 支持的 DDL 算法是什么样的

如果这个服务本身就是主从架构，那我会选一台流量较少的从库，在这个从库上执行表结构变更，变更完成后确保这台从库和主库的数据结构保持一致，然后再把这台从库提升为主库，切换流量过去，最后再把原来的主库做成从库，等它同步完成后再作为备份

如果是单体，就要明确指定 ALGORITHM=INPLACE, LOCK=NONE 的 DDL 策略，然后选在业务低峰期执行变更，变更前后都要监控一下 MySQL 的性能指标，比如 CPU、磁盘 I/O、QPS、慢查询数量等，确保没有对业务造成影响

最后是应用侧的兼容和回滚预案，比如对于加字段、字段替代这种变更，我会先加字段，然后更新代码去同时写新旧字段、逐步迁移数据、切读流量，等确认没问题后再切换读新字段，最后再删掉旧字段，新索引加上去之后执行计划也是有可能会变的，然后如果真的出现了问题就回滚

DDL 是什么？比如说我们在加这个索引或者做别的 DDL 的时候，我们对现有业务可能带来什么样的风险呢？

DDL（Data Definition Language）是数据定义语言，主要用于定义和修改数据库结构，比如创建、修改、删除数据库、表、索引等

DML（Data Manipulation Language）是数据操作语言，主要用于对数据库中的数据进行增删改查操作

可以想象 DML 是在房间里搬东西，DDL 是改房子结构，MySQL 在执行 DDL 时，会对这张表加元数据锁，所以会阻塞读写，另外 DDL 操作本身也会消耗较多的系统资源（CPU、内存、磁盘 I/O），可能会影响到其他正常的 DML 操作，导致响应变慢，甚至超时失败

如果是主库写、从库读的场景，那在主库上执行一个 DDL 的时候，这条更改会记录到 binlog，然后从库会去重放这个 DDL 操作，如果从库的读流量比较大，那可能这个 DDL 还在被执行，业务以外读的是最新的数据，但实际上是落后主库的，并且执行期间其他的 SQL 重放也会被阻塞，如果有多个从库，那可能有的从库已经有新结构了，但有的还没有

再就是比如加了索引之后反而把执行计划给变差了，导致某些查询变慢了，或者新增/修改的某个字段不兼容现有的应用代码，我们这时应该先只做兼容性的扩展（加字段/索引，不立即删除旧字段），代码先双写或者兼容新旧两套，等确认没问题后再切换读新字段/索引，最后再删掉旧字段/索引

如果是不支持 INPLACE 的 DDL，那就会用 COPY 的方式，建一个临时表，拷贝数据，然后切换元数据，这个过程会锁表，阻塞所有的 DML 操作，可能会导致业务不可用

那如果是主从同步的场景，这种表结构变更也需要在多个实例中去重复做吗？

最终效果上是主从结构里每个实例上，这张表都会完成一次 DDL，但实际变更时通常只需要在主库执行一次 DDL，主库会把这次 DDL 变更记录到 binlog，然后从库会自动通过复制线程来拉 binlog 并按顺序重放这些 DDL 变更，从而让从库的数据结构和主库保持一致

如果是在从库执行这个 DDL，那这个从库不会有写到 binlog 然后其他实例自动通过复制线程来拉binlog然后顺序重放吗？如果是你刚刚说的把从库提升为主库然后切换流量那种情况，具体又会是什么样的？

  • 诶那个表结构怎么逐步变更啊，它是一张表，它可以部分数据变部分数据不变吗？业务要部分停机吗？

  • 我说的不是同步策略，我说的是机制是什么样的，比如你说的主从同步是说我们要靠运维主动去做一个这样的数据同步的脚本还是说 MySQL 自己有一些同步的机制？

  • 能说一下 MySQL 在建立联合索引的一些注意事项吗，比如说索引的一些列有什么样的规则或规范，比如在建立联合索引时哪些列应该列入到联合索引里，哪些列又不应该列到联合索引里面

  • 我建了五个列的联合索引，这样的联合索引对于我的读和写有哪些影响

  • 联合索引建的太多对于写的影响是什么

  • 我们在 MySQL 里面去创建一些数据库的话，为了提高数据库的查询效率，一般我们会去创建索引，那在创建索引时需要注意什么

  • 创建索引时我们的匹配原则应该是什么样的，是左前缀还是右前缀

  • 如果我对某个字段做了索引，那索引列它能参与计算或者函数操作吗

  • 索引的优缺点有什么，索引的类型有哪些

MySQL为什么使用B+树来作索引？

说一下索引失效的场景？

什么样的场景索引会失效