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

image.png

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

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

  • 没有主键的表，innodb会给默认创建一个Rowid做主键
  • drop主键索引会导致其他索引失效，但drop普通索引不会。

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

重建索引

  1. alter table T drop index k;
  2. alter table T add index(k);

以上SQL可以这样代替

  1. 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 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

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

执行语句大体这样吧：

  1. CREATE TABLE `test_models`
  2. (
  3.     `id` INT(11) NOT NULL AUTO_INCREMENT,
  4.     `a`  INT(11) DEFAULT NULL,
  5.     `b`  INT(11) DEFAULT NULL,
  6.     `c`  INT(11) DEFAULT NULL,
  7.     `d`  INT(11) DEFAULT NULL,
  8.     `e`  INT(11) DEFAULT NULL,
  9.     PRIMARY KEY (`id`),
  10.     KEY `index_abc` (`a`, `b`, `c`)
  11. ) ENGINE = INNODB
  12.   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列，和顺序无关 都会使用 索引
  1. a = 1 AND b = 2 AND c = 3 ; 使用索引
  2. c = 1 AND b = 2 AND a = 3 ; 使用索引 
  3. a = 1 AND b = 2 ; 使用索引
  4. a = 1 AND c = 3 ; 使用索引
  5. c = 1 AND a = 2 ; 使用索引

不包含最左侧的 a 的不使用索引
  1. c = 3 ; 未使用索引
  2. b = 2 ; 未使用索引
  3. b = 2 AND c = 3 ; 未使用索引
  4. c = 1 AND b = 2 ; 未使用索引

OR 不使用索引
  1. a = 1 AND b = 2 OR c = 3 未使用索引
  2. a = 1 OR b = 2 AND c = 3 未使用索引
  3. a = 1 OR b = 2 OR c = 3 未使用索引

最左侧的‘a’列 被大于，小于，不等于比较的 ，最多只命中a，这要取决于数据量。
  1. a > 1 AND b = 2 AND c = 3  未使用索引
  2. a < 1 AND b =  2 AND c = 3  未使用索引 最多 a、b 走索引，c不会走索引。
  3. a > 1 ; 未使用索引
  4. a <> 1 AND b = 2 AND c = 3 未使用索引


最左侧a=某某，后面列大于小于无所谓，都使用索引（但后面必须 and and ）
  1. a = 1 AND b < 2 AND c = 3 使用索引
  2. a = 1 AND c = 2 AND b < 3 使用索引
  3. a = 1 AND b < 2 使用索引
  4. a = 1 AND b <> 2 AND c = 3 使用索引
  5. // 可以说 OR一出现就不使用
  6. a = 1 AND b < 2 OR c = 2 未使用索引

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

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

b = 某，不使用

  1. 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的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

索引长度

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

索引高度

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

索引设计规范

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

数据库 SQL 开发规范

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