MySQL索引详解

举报
Rolle 发表于 2023/11/30 19:56:08 2023/11/30
【摘要】 什么是索引索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。索引类型分为主键索引和非主键索引主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。从性能和存储空间方面考量，...

什么是索引

  • 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
  • 索引类型分为主键索引和非主键索引
    • 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引
    • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引

image.png

  • 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。从性能和存储空间方面考量，自增主键往往是更合理的选择

一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。

数据页的整理可以通过以下两种方式实现（都是内部操作）：

  1. 压缩页：这种方式会将数据页中的记录整理到一起，删除已标记为可重用的空间，并重新组织页中记录的存储方式。这种方式比较耗时，但是可以最大限度地压缩数据页，使其大小尽可能小。
  2. 重建页：这种方式会创建一个新的数据页，然后将原数据页中的记录复制到新数据页中，再将新数据页写入磁盘。这种方式可以避免频繁地压缩数据页，因为新数据页已经是连续的，并且没有未使用的空间。不过，这种方式需要更多的空间和时间来完成，因为需要创建新的数据页并复制数据。
  3. 没有主键的表，innodb会给默认创建一个Rowid做主键
  4. drop主键索引会导致其他索引失效，但drop普通索引不会。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

重建索引

alter table T drop index k;
alter table T add index(k);

以上SQL可以这样代替

alter table T engine=InnoDB

重建非主键索引的做法是合理的，可以达到省空间的目的，但是重建主键的过程不合理，无论是创建主键还是删除主键，都会将整个表重建。

MySQL 索引使用的注意事项

  • 不要在列上使用函数
  • 不要在列上进行运算
    • select from news where id / 100 = 1 改为 select from news where id = 1 * 100
  • 应该尽量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因为这几个操作符都会导致索引失效而进行全表扫描
  • 尽量避免使用 or 来连接条件
  • 只要列中包含有 NULL 值都将不会被包含在索引中，复合索引中只要有一列含有 NULL值，那么这一列对于此复合索引就是无效的。因此，在数据库设计时，除非有一个很特别的原因使用 NULL 值，不然尽量不要让字段的默认值为 NULL
  • like 语句的索引失效问题，考虑es或者solr
  • 使用前缀索引来减少索引长度：如果索引的长度较长，可能会降低索引的效率。可以使用前缀索引来减少索引长度，从而提高索引的效率。但需要注意的是，使用前缀索引可能会降低查询的准确性。

查询过程

假设有这么一张表，其中查询语句是

select name from CUser where id_card = ‘xxxxxxxyyyyyyzzzzz’;

  • 对于主键索引和非主键索引查询过程不同？
    • 假设，执行查询的语句是 select id from T where k=5。这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点。先搜索 k 索引树，得到 ID 的值为 xxx，再到 ID 索引树搜索一次。这个过程称为回表。
    • 对于普通索引来说，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
    • 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。只需要搜索 ID 这棵 B+ 树；

这个不同带来的性能差距会有多少呢？答案是，微乎其微。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。

因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

更新过程

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下

  • 对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。

第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：

  • 对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；
  • 对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

change buffer 的使用场景

普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

通常情况下，MySQL的Change Buffer是自动启用的，不需要手动设置。当InnoDB存储引擎检测到适当的条件时，它会自动使用Change Buffer。

但是，你可以通过修改一些配置参数来控制Change Buffer的使用。例如，可以使用innodb_change_buffer_max_size参数来设置Change Buffer所使用的最大内存大小。另外，你也可以使用innodb_change_buffering参数来控制Change Buffer的启用方式，可以将其设置为“none”以禁用Change Buffer，或将其设置为“inserts”以只启用插入操作的Change Buffer。

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

索引

注意

B+树中的B不是代表二叉（binary），而是代表平衡，因为B+树是从最早的平衡二叉树演化而来的，但B+树不是一个二叉树。另外一个被忽视的问题是，B+树索引并不找到一个给定键值的具体行，B+树索引能找到的只是被查找数据行所在的页，然后数据库通过把页读入到内存，再在内存中进行查找

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

普通索引和唯一索引应该怎么选择

这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以还是尽量选择普通索引（但在更新性能上，由于普通索引可以使用change buffer）。

MySQL 是怎样得到索引的基数的呢？

采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：设置为 on 的时候，表示统计信息会持久化存储。这时，默认的 N 是 20，M 是 10。设置为 off 的时候，表示统计信息只存储在内存中。这时，默认的 N 是 8，M 是 16。

B+树的3种插入情况

image.png

B+树删除操作的三种情况

image.png

哪些情况可以选择使用索引

  • 所有字段都匹配全值(=)
  • 范围匹配
  • 最左前缀匹配
  • 仅对索引列查询
  • 匹配列前缀,使用第一列索引
  • 部分精确,部分范围
  • IS NULL会用到索引
  • ICP特性,条件过滤下放到存储引擎
  • 字段的数值有唯一性的限制，比如用户名
  • 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下
  • 需要经常 GROUP BY 和 ORDER BY 的列
  • DISTINCT 字段需要创建索引
  • UPDATE、DELETE 的 WHERE 条件列，一般也需要创建索引

什么情况下索引失效

  • %开头LIKE查询,一般先条件缩小范围,再回表LIKE
  • 数据类型出现隐式转换,例如字段是字符串,where=整数
  • 联合索引不遵循最左前缀
  • Mysql认为扫索引比扫全表慢
  • 多个OR,其中字段有的有索引,有的没有
  • 索引进行了表达式计算，则会失效
    • SELECT * … WHERE comment_id+1 = 900001
  • 在索引列上使用了 !=,>,>=,<,<=,or, in 等 ，is null ,is not null (如果MySQL预估走索引的代价比全表的代价高的话，就不走索引)
  • 可以使用force index (“xxx”) 强制走索引

优化器选择不使用索引的情况

  • 多发生于范围查找、join连接等情况
    • 当访问数据站整个表中数据的大部分时，一般是20%，优化器会选择通过聚集索引来查找数据，因为顺序读要远远快于离散度（因为会回表、先查询orderID，然后再查询ID）
    • 如果确定强制使用索引会带来更好的性能。可以使用FORCE INDEX 来强制某个索引
      • SELECT * FROM TABLENAME FORCE INDEX(ORDERID) WHERE ORDERID >1000 AND ORDER < 10200

查看索引使用情况

  • SHOW status LIKE ‘Handler_read%’;
  • Handler_read_key越高证明使用索引越多；
  • Handler_read_rnd_next越高，证明全表扫描的多；

联合索引(todo)

一个表有联合索引a,b,c 。b = ？ and c = ？ and a = ？会使用索引吗

本地 MYSQL版本 5.7

mysql创建一张表，表名：‘test_models’

  • id列为 主键，int类型 ，自增
  • a,b,c,d,e 全部是int（11）
  • 为（a,b,c）添加一个联合索引 index_abc

执行语句大体这样吧：

CREATE TABLE `test_models`
(
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `a`  INT(11) DEFAULT NULL,
    `b`  INT(11) DEFAULT NULL,
    `c`  INT(11) DEFAULT NULL,
    `d`  INT(11) DEFAULT NULL,
    `e`  INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `index_abc` (`a`, `b`, `c`)
) ENGINE = INNODB
  DEFAULT CHARSET = utf8mb4 COMMENT = 'test';

用代码往表中写入100万条数据

使用 ‘EXPLAIN’ sql语句查看执行详情

EXPLAIN SELECT * FROM test_models WHERE a = 100 AND b = 1000 AND c = 10000;

AND AND 只要用到了最左侧a列，和顺序无关 都会使用 索引
a = 1 AND b = 2 AND c = 3 ; 使用索引
c = 1 AND b = 2 AND a = 3 ; 使用索引 
a = 1 AND b = 2 ; 使用索引
a = 1 AND c = 3 ; 使用索引
c = 1 AND a = 2 ; 使用索引
不包含最左侧的 a 的不使用索引
c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引
OR 不使用索引
a = 1 AND b = 2 OR c = 3 未使用索引
a = 1 OR b = 2 AND c = 3 未使用索引
a = 1 OR b = 2 OR c = 3 未使用索引
最左侧的‘a’列 被大于，小于，不等于比较的 ，最多只命中a，这要取决于数据量。
a > 1 AND b = 2 AND c = 3  未使用索引
a < 1 AND b =  2 AND c = 3  未使用索引 最多 a、b 走索引，c不会走索引。
a > 1 ; 未使用索引
a <> 1 AND b = 2 AND c = 3 未使用索引
最左侧a=某某，后面列大于小于无所谓，都使用索引（但后面必须 and and ）
a = 1 AND b < 2 AND c = 3 使用索引
a = 1 AND c = 2 AND b < 3 使用索引
a = 1 AND b < 2 使用索引
a = 1 AND b <> 2 AND c = 3 使用索引
// 可以说 OR一出现就不使用
a = 1 AND b < 2 OR c = 2 未使用索引

a = 某，后面order 无所谓 都 使用索引 （和最上面的最左匹配一样）

a = 1 AND b = 2 AND c = 3 ORDER BY a;// 或者 ORDER BY b ， ORDER BY c ，ORDER BY d, 使用索引
a = 1 ORDER BY a; // 或者 ORDER BY b,ORDER BY c,ORDER BY d 使用abc索引

b = 某，不使用

b = 1 ORDER BY a; //ORDER BY b 都 未使用索引

a and c，只会命中索引的a，不会命中a，b，c索引，abc索引相当于创建了三个索引，a，ab，abc

如果我们遇到了范围条件查询，比如（<）（<=）（>）（>=）和 between 等，那么范围列后的列就无法使用到索引了。

联合索引查找规则

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

所以执行一个 WHERE A=a1 AND B=b1 AND C=c1 的查询就类似于：

for a in A {
  if a == a1 {
    for b in B {
      if b == b1 {
        for c in C {
          if c == c1 {
            // 这就是你要的数据，拿到主键之后去磁盘里面加载出来
          }
        }
      }
    }
  }
}

如果查询条件是 WHERE A = a1 AND B = b1，那么你可以推断出来，数据库只会应用外层的两重循环，不会对 C 进行过滤。

for a in A {
  if a == a1 {
      for b in B {
        if b == b1 {
          // 这就是你要的结果，去磁盘里面读取
        }
      }
  }
}

如果查询条件是 WHERE A = a1 OR B = b1，那么这个查询并不会使用这个索引。

for a in A {
  if a == a1 {
      as = append(as, a)
  }
}
for b in B {
  if b == b1 {
      bs = append(bs, b)
  }
}
// as 和 bs 的并集就是你要的结果

按照组成索引的列的顺序，从左往右：AND 用 OR 不用，正用反不用，范围则中断。

索引的代价

索引并不是没有代价的，它会消耗很多的系统资源。

  1. 索引本身需要存储起来，消耗磁盘空间。
  2. 在运行的时候，索引会被加载到内存里面，消耗内存空间。
  3. 在增删改的时候，数据库还需要同步维护索引，引入额外的消耗。

索引长度

单列索引和复合索引的长度也需要控制，在 MySQL InnoDB 中，系统默认单个索引长度最大为 767 bytes，如果单列索引长度超过了这个限制，就会取前缀索引，也就是取前 255 字符。字符列会占用较大的空间，在数据表设计的时候，尽量采用数值类型替代字符类型，尽量避免用字符类型做主键，同时针对字符字段最好只建前缀索引。

索引高度

假设当前数据表的数据量为N，每个磁盘块的数据项的数量是m，则树高h=㏒(m+1)N

索引的选择性

指索引列唯一值的数目与表中记录数的比例

SHOW INDEX结果中的列Cardinality来观察。Cardinality是一个预估值，而不是一个准确值.Cardinality/n_rows_in_table应尽可能地接近1。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

show index from xxx

索引设计规范

  1. 限制每张表上的索引数量，建议单张表索引不超过 5 个；
  2. 每个 InnoDB 表必须有个主键；
  3. 区分度最高的放在联合索引的最左侧（区分度 = 列中不同值的数量 / 列的总行数）。尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）。使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）。
  4. 查询的时候考虑覆盖索引

数据库 SQL 开发规范

  • 避免使用双 % 号的查询条件
  • 禁止使用 SELECT *
  • 避免使用子查询，可以把子查询优化为 JOIN 操作；
  • 避免使用 JOIN 关联太多的表。使用join的字段应尽量建立索引
  • 在使用了索引字段相关的SQL查询条件时，有可能还是查询慢，可能原因有 SQL 查询是事务中依赖mvcc的快照读需要多次版本回退， 或者是sql查询需要等待上一次更新操作释放表的写锁
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL 数据库
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入