MySQL索引详解

2023-11-30 15:27:50 浏览数 (1)

什么是索引

  • 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
  • 索引类型分为主键索引和非主键索引
    • 主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引
    • 非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引
  • 主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。从性能和存储空间方面考量，自增主键往往是更合理的选择

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

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

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

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

重建索引

代码语言：sql复制
alter table T drop index k;
alter table T add index(k);

以上SQL可以这样代替

代码语言：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种插入情况

B 树删除操作的三种情况

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

  • 所有字段都匹配全值(=)
  • 范围匹配
  • 最左前缀匹配
  • 仅对索引列查询
  • 匹配列前缀,使用第一列索引
  • 部分精确,部分范围
  • 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

执行语句大体这样吧：

代码语言：sql复制
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列，和顺序无关 都会使用 索引
代码语言：sql复制
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 的不使用索引
代码语言：sql复制
c = 3 ; 未使用索引
b = 2 ; 未使用索引
b = 2 AND c = 3 ; 未使用索引
c = 1 AND b = 2 ; 未使用索引
OR 不使用索引
代码语言：sql复制
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，这要取决于数据量。
代码语言：sql复制
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 ）
代码语言：sql复制
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 无所谓 都 使用索引 （和最上面的最左匹配一样）

代码语言：sql复制
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 = 某，不使用

代码语言：sql复制
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 的查询就类似于：

代码语言：go复制
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 进行过滤。

代码语言：go复制
for a in A {
  if a == a1 {
      for b in B {
        if b == b1 {
          // 这就是你要的结果，去磁盘里面读取
        }
      }
  }
}

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

代码语言：go复制
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。如果非常小，那么用户需要考虑是否还有必要创建这个索引。

代码语言：sql复制
show index from xxx

索引设计规范

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

数据库 SQL 开发规范

  • 避免使用双 % 号的查询条件
  • 禁止使用 SELECT *
  • 避免使用子查询，可以把子查询优化为 JOIN 操作；
  • 避免使用 JOIN 关联太多的表。使用join的字段应尽量建立索引
  • 在使用了索引字段相关的SQL查询条件时，有可能还是查询慢，可能原因有 SQL 查询是事务中依赖mvcc的快照读需要多次版本回退， 或者是sql查询需要等待上一次更新操作释放表的写锁

我正在参与2023腾讯技术创作特训营第三期有奖征文，组队打卡瓜分大奖！

mysql mysql索引 索引 2023腾讯·技术创作特训营第三期

0 人点赞