Mysql索引原理优化最佳实践

一、索引数据结构

索引就是排好序的数据结构

(索引和表数据都是存储到磁盘)

索引数据结构：

二叉树：缺点：特殊情况 在查找记录时跟没加索引的情况是一样（key：所要查询的值 value：指针）

红黑树：缺点：在数据量大的时候，深度也很大 树的高度不可控 高度过高导致查询性能不快

Hash表：对key hsah得到指针位置（指针位置和hash值有个映射关系 等值可以快速定位）

缺点：难以支持范围查找 也不适合模糊查询

B树（一次IO可以load到内存到好多数据）

B- tree:特点：

1.非叶子结点与叶子结点（每个关键字都保存数---指向数据的指针）

2.任何一个关键字出现且只出现在一个结点中

B+树：（每一个索引在 InnoDB 里面对应一棵 B+ 树）

1.非叶子结点（只用来存索引）

2.所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接

3.节点中的数据key从左到右递增排列

(每一层也就是页 默认是16KB)

非叶子节点 索引8字节 ＋指针6字节 = 14字节 一个节点大约能存16kb/14B = 1170个索引

列题：假设树的高度是3 每一个节点（层）为16kb 叶子节点一个索引＋data元素=1kb 那么这个B+树最多可以存多少个索引元素

解答:第一层1170*第二层1170*第三城16KB/1KB约等于2千多万

b+树相比于b树的查询优势、

1.b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；

2.对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历，

二、存储引擎

MYISAM存储引擎

文件后缀.frm（存放表结构）.MYD（存放表数据）、.MYI（存放表索引）

如果查询某个加索引的字段 则先去MYI里找到相应节点拿到value值（data值对应MYD里数据得位置）

果查询某个没有加索引的字段 则直接去MYD查询

innoDBc存储引擎

文件后缀.frm（存放表结构）ibd（存放表数据加索引）

三、索引类型

聚集索引（也叫主键索引）：索引存储和数据存储聚集到一个文件存储---innodb主键索引就是，mylsam就不是

聚集索引：表中那行数据的索引和数据都合并在一起了（叶子节点存的是整行数据）。

非聚集索引：表中那行数据的索引和数据是分开存储的（叶子节点内容是主键的值）。

InnoDB主键索引查找流程：通过.ibd文件找到对应的索引，索引的value即为那行对应的完整数据。

InnoDB普通索引查找流程：通过.ibd文件找到对应的索引，索引的value即为那行对应的主键的值，再根据主键值去主键索引树中找到对应的行数据。

为什么InnoDB表必须有主键？

因为整个数据文件本身就是按照B+树组织的一个索引文件，所以必须要有主键（建InnoDB表时不指定主键，默认会从表字段中选一列作为唯一主键，如果不存在这种字段，则后台默认生成一个长整型主键字段，MyISAM可以没有）。

为什么innodb推荐整形的自增主键？

答：1 整型（4个字节 长整形8个字节）占位空间小 2主键结构是b+树 需要比较大小 整形比较大小快 3.其他类型保证不了自增比如uuid类型 会大大增加树的分裂概率（自增不会增加分裂，依次递增）

覆盖索引

查询中 普通索引 “覆盖了”查询需求，称为覆盖索引，可以减少回表（回到主键索引树搜索的过程）

举例：：在一个市民信息表上，是否有必要将身份证号和名字建立联合索引？

身份证号是唯一的，如果有根据身份证号查询市民信息的需求，只要在身份证号字段上建立索引就够了。而再建立一个（身份证号、姓名）的联合索引，是不是浪费空间？

如果现在有一个高频请求，要根据市民的身份证号查询他的姓名，这个联合索引就有意义了。它可以在这个高频请求上用到覆盖索引，不再需要回表查整行记录，减少语句的执行时间。

联合索引

第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。

第二原则是，空间

比如联合索引（name,age）有的业务场景需要通过name查询到,也有通过age查询，但是name字段大于age,所以采用age,name比较合适

*****联合索引的底层存储结构长什么样？

单列索引其实也可以看做联合索引，索引列为1的联合索引，从下图就可以看出联合索引的底层存储跟单列索引时类似的，区别在于联合索引是每个树节点中包含多个索引值，在通过索引查找记录时，会先将联合索引中第一个索引列与节点中第一个索引值进行匹配，匹配成功接着匹配第二个索引列和索引值，直到联合索引的所有索引列都匹配完；如果过程中出现某一个索引列与节点相应位置的索引值不匹配的情况，则无需再匹配节点中剩余索引列，前往下一个节点

联合索引：多个索引合起来作为一个联合索引，如(id,name,date)

先比较id，如果id相等，再比较name，如果name也相等，则再比较date

索引下推

可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数

举例子

select * from tuser where name like '张%' and age=10

无索引下推过程

有索引下推执行过程

 

每一个虚线箭头表示回表一次 

InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次

四、change buffer buffer pool

 change buffer 内存中有拷贝，也会被写入到磁盘上，是个持久化数据

当需要更新一个数据页时，如果数据页在内存中就直接更新(数据页读入内存是需要占用 buffer pool )，而如果不在内存中，InnoDB 会将这些更新操作缓存在 change buffer 中（也是有个写入磁盘的操作，但是不需要读），这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，将 change buffer 中的操作merge到原数据页

除了第一次查询访问（也有定时更新merge,数据库关闭前也会merge）

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

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入一条唯一记录，就要先判断现在表中是否已经存在 这样的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

五、普通索引和唯一索引应该如何选择

两方面考虑：查询和更新性能

查询例子：select id from T where k=5

1.普通索引，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。

2.唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

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

对于普通索引来说，如果都在一页上，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。（如果在页的最后一个记录则需在读取一个数据页，出现概率低，可忽略不计）

所以查询性能看两者相差不大

更新例子：插入数据

第一种情况：要更新的数据页在内存中

1.唯一索引，找到相应的位置，判断到没有冲突，插入这个值，语句执行结束；

2.普通索引，找到相应的位置，插入这个值，语句执行结束。

这样看来，性能差别不大，

第二种情况：要更新的数据页不在内存中

1.唯一索引，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

2.普通索引，则是将更新记录在 change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的

所以以上 建议尽量选择普通索引

那普通索引的所有场景，使用 change buffer 都可以起到加速作用吗？
假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。所以，对于这种业务模式来说，change buffer 反而起到了副作用。

六、change buffer 和 redo log

例子：insert into t(id,k) values(id1,k1),(id2,k2);

我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。如图 2所示是带 change buffer 的更新状态图。

主要涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。

1.changebuffer跟普通数据页一样也是存在磁盘里，区别在于changebuffer是在共享表空间ibdata1里

2.redolog有两种，一种记录普通数据页的改动，一种记录changebuffer的改动

这条更新语句做了如下的操作（按照图中的数字顺序）：

1.Page 1 在内存中，直接更新内存；

2.Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”

3.这个信息将上述两个动作记入 redo log 中（图中 3 和 4）。

执行这条更新语句的成本很低，就是写了两处内存（redo写了两次内存），然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

更新后不久要执行select * from t where k in (k1, k2)

1.读 Page 1 的时候，直接从内存返回。

2.读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。

再举一个例子

1.当需要更新一个数据页时，如果数据页在内存(buffer pool缓冲池)中，比如修改页号4

（1）直接更新内存数据页，这个数据页就与磁盘数据页不一致了，称为脏页，

（2）要记下redo log，redolog会通过几种方式触达将脏页flush到磁盘 是顺序写，

（3）如果数据库崩溃能够从redo log中恢复数据

脏页flush到磁盘中，会有四种情形

（1）redo log写满了

（2）内存写满了

（3）MySQL 认为系统“空闲”的时候

（4）MySQL 正常关闭的时候

2.当需要更新一个数据页时，如果数据页不在内存(buffer pool缓冲池)中，比如修改页号40

（1）在写缓冲中记录这个操作，一次内存操作，(写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间；)

（2）写入redo log，一次磁盘顺序写操作；

（3）数据读取时，有另外的流程，将数据merge到缓冲池

（4）如果数据库崩溃能够从redo log中恢复数据

所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

七、问题抛出分析

问题1：对于 InnoDB 表 T，如果你要重建索引 k，你的两个 SQL 语句可以这么写：alter table T drop index k;alter table T add index(k);如果你要重建主键索引，也可以这么写：alter table T drop primary key;alter table T add primary key(id);那么通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是否合理。

答案：

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，你可以用这个语句代替 ： alter table T engine=InnoDB。

问题2：实际上主键索引也是可以使用多个字段的。DBA 小吕在入职新公司的时候，就发现自己接手维护的库里面，有这么一个表，表结构定义类似这样的：

CREATE TABLE `geek` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, `d` int(11) NOT NULL, PRIMARY KEY (`a`,`b`), KEY `c` (`c`), KEY `ca` (`c`,`a`), KEY `cb` (`c`,`b`)) ENGINE=InnoDB;

公司的同事告诉他说，由于历史原因，这个表需要 a、b 做联合主键，这个小吕理解了。但是，学过本章内容的小吕又纳闷了，既然主键包含了 a、b 这两个字段，那意味着单独在字段 c 上创建一个索引，就已经包含了三个字段了呀，为什么要创建“ca”“cb”这两个索引？同事告诉他，是因为他们的业务里面有这样的两种语句：select * from geek where c=N order by a limit 1;select * from geek where c=N order by b limit 1;

那么为了这两个查询模式，这两个索引是否都是必须的？为什么呢？

答案：

表记录
--a--|--b--|--c--
  1 2 3
  1 3 2
  1 4 3
  2 1 3
  2 2 2
  2 3 4
主键 a，b的聚簇索引组织顺序相当于 order by a,b
也就是先按a排序，再按b排序，c无序
索引 ca 的组织是先按c排序，在按a排序，同时记录主键
--c--|--a--|--主键ab--
  2 1 1,3
  2 2 2,2
  3 1 1,2
  3 1 1,4
  3 2 2,1
  4 2 2,3
索引 cb 的组织是先按c排序，在按b排序，同时记录主键
--c--|--b--|--c--|--主键ab--
  2 1 2,2
  2 3 1,3
  3 1 2,1
  3 2 1,2
  3 4 1,4
  4 3 2,3
对于下面的语句
select ... from geek where c=N order by a
走ca,cb索引都能定位到满足c=N主键
而且主键的聚簇索引本身就是按order by a,b排序，无序重新排序。所以ca可以去掉
select ... from geek where c=N order by b
这条sql如果只有 c单个字段的索引，定位记录可以走索引，但是order by b的顺序与主键顺序不一致，需要额外排序

问题3：change buffer 一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致 change buffer 丢失呢？

答：会导致change buffer丢失，会导致本次未完成的操作数据丢失，但不会导致已完成操作的数据丢失。
change buffer中分两部分，一部分是本次写入未写完的，一部分是已经写入完成的。
1.针对未写完的，此部分操作，还未写入redo log，因此事务还未提交，所以没影响。
2.针对已经写完成的，可以通过redo log来进行恢复。

推荐网址：https://www.linuxidc.com/Linux/2020-02/162355.htm

https://juejin.cn/post/6844903875271475213