这是我参与「第三届青训营 -后端场」笔记创作活动的第3篇笔记 这是我在做抖音项目过程中遇到的一些问题，我把这些问题总结成了以下8个问题，希望对大家在开发过程中有一定的帮助。
1.InnoDB的索引模型 （大致了解mysql索引框架）
在InnoDB中， 表都是根据主键顺序以索引的形式存放的， 这种存储方式的表称为索引组织表。 InnoDB使用了B+树索引模型， 所以数据都是存储在B+树中的。
主键索引的叶子节点存的是整行数据。 在InnoDB里， 主键索引也被称为聚簇索引（clustered index） 。
非主键索引的叶子节点内容是主键的值。 在InnoDB里， 非主键索引也被称为二级索引（secondaryindex） 。 基于非主键索引的查询需要多扫描一棵索引树。
2.哪些场景下应该使用自增主键， 而哪些场景下不应该（建表的时候是否考虑自增主键）
自增主键是指自增列上定义的主键，插入新记录的时候可以不指定ID的值， 系统会获取当前ID最大值加1作为下一条记录的ID值。
- 自增主键的插入数据模式，每次插入一条新记录， 都是追加操作， 都不涉及到挪动其他记录， 也不会触发叶子节点的分裂。而有业务逻辑的字段做主键， 则往往不容易保证有序插入， 这样写数据成本相对较高。
- 主键长度越小， 普通索引的叶子节点就越小， 普通索引占用的空间也就越小。自增主键有时比有业务逻辑的字段长度更短一些。
- 自增主键最大的缺点就是每次查询需要搜索两棵树。 在KV存储上不适合自增主键。
3.最左前缀原则（建表的索引优化）
由于覆盖索引可以减少树的搜索次数， 显著提升查询性能， 所以使用覆盖索引是一个常用的性能优化手段。 B+树这种索引结构， 可以利用索引的“最左前缀”， 来定位记录。
因为可以支持最左前缀， 所以当已经有了(a,b)这个联合索引后， 一般就不需要单独在a上建立索引了。 因此， 第一原则是， 如果通过调整顺序， 可以少维护一个索引， 那么这个顺序往往就是需要优先考虑采用的。
如果既有联合查询， 又有基于a、 b各自的查询呢？ 查询条件里面只有b的语句， 是无法使用(a,b)这个联合索引的， 这时候你不得不维护另外一个索引， 也就是说你需要同时维护(a,b)、 (b) 这两个索引。
4.索引下推（建表的索引优化）
select * from tuser where name like '张%' and age=10 and ismale=1;
（这两张图来自mysql45讲中林大佬的配图）
每一个虚线箭头表示回表一次。
图2跟图1的区别是， InnoDB在(name,age)索引内部就判断了age是否等于10， 对于不等于10的记录， 直接判断并跳过。 在我们的这个例子中， 只需要对ID4、 ID5这两条记录回表取数据判断， 就只需要回表2次。
5.普通索引和唯一索引（建表索引的选择——普通索引和唯一索引，应该怎么选择）
普通索引允许被索引的数据列包含重复的值。 那么普通索引和唯一索引，应该怎么选择？
查询过程 ： 假设，执行查询的语句是 select id from T where k=1。这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。
对于普通索引来说，查找到满足条件的第一个记录(1,666)后，需要查找下一个记录，直到碰到第一个不满足k=1条件的记录。 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。 那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。因为当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。
更新过程： 当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。 那么，什么条件下可以使用change buffer呢？
对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入(2,888)这个记录，就要先判断现在表中是否已经存在k=2的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。
因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。如果一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。
综上所述：尽量使用普通索引。 注意是尽量，如果索引字段必须是唯一的，还是尽量使用唯一索引，因为业务逻辑层保证数据唯一，总是会出现bug的。
redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。
6. change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？change buffer丢失可不是小事儿，再从磁盘读入数据可就没有了merge过程，就等于是数据丢失了。会不会出现这种情况呢？（普通索引会默认使用change buffer，是否对数据一致性产生影响）
会导致change buffer丢失，会导致本次未完成的操作数据丢失，但不会导致已完成操作的数据丢失。 change buffer中分两部分，一部分是本次写入未写完的，一部分是已经写入完成的。
- 针对未写完的，此部分操作，还未写入redo log，因此事务还未提交，所以没影响。
- 针对，已经写完成的，可以通过redo log来进行恢复。
所以，不会对数据库造成影响。
7. 数据库删除操作的一些思考
表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数innodb_file_per_table控制的：
这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。
以下操作都是基于innodb_file_per_table控制的。假设，我们要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除，磁盘文件的大小并不会缩小。
数据页的复用跟记录的复用是不同的。
记录的复用，只限于符合范围条件的数据。而当整个页从B+树里面摘掉以后，可以复用到任何位置。
8.数据库插入操作的一些思考
数据库的插入操作也会使表产生空洞。你可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。
由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。显然地，表B的主键索引更紧凑，数据页的利用率也更高。如果我们把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。
这里，你可以使用alter table A engine=InnoDB命令来重建表。在MySQL 5.5版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表B不需要你自己创建，MySQL会自动完成转存数据、交换表名、删除旧表的操作。
存在以下问题： 显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表A的话，就会造成数据丢失。因此，在整个DDL过程中，表A中不能有更新。也就是说，这个DDL不是Online的。 引入了Online DDL之后，重建表的流程：
- 建立一个临时文件，扫描表A主键的所有数据页；
- 用数据页中表A的记录生成B+树，存储到临时文件中；
- 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
- 用临时文件替换表A的数据文件。 使用alter table A engine=InnoDB命令来重建表。
alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。
为什么要退化呢？为了实现Online，MDL读锁不会阻塞增删改操作。
那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。
而对于一个大表来说，Online DDL最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个DDL过程来说，锁的时间非常短。对业务来说，就可以认为是Online的。
