如何准确判断什么时候可以给大表加索引？

导读

以交际途径的用户表为例，跟着事务的快速增长，用户表user单表数据量越来越大，此刻，假定咱们想给user表增加索引，数据规划对增加进程的影响势必要考虑在内，可是，单表数据规mysql数据库划对增加索引会发生什么样的影响呢，咱们在mysql优化什么样的数据库央求状况下给大表增加索引比数据库较好呢？

今天，我就具体答复一下上面两个问题：

1. 单表数据规划对增加索引会产架构图生什么样的事务影响？
2. 在什么样的数据库央求状况下给大表增加索MySQL引比较好？

咱们先来看下第一个问题，当咱们答复了第一个问题，那么，第二数据库设计个问题的答案也就浮出水面了。

Row Log

咱们先来看一个结构，它叫Row Log，用于在DDL进程中记载DML操作的日志文件。数据库体系

我以usemysql装置r表为例，说明一下Row Log。它有如下特点：

1. 每个索引对应一个Row Log，如上图为user表的索引index_age_birth对应的Row Log。

Row Logmysql暗码忘记了怎样办在逻辑上由多个Block组成，每个Block能够存储多个DML操作、一个DML操数据库查询句子作也会落在多个Block中。如上图中的L数据库体系概论第五版课后答案og代表DML操作：

1. 最前面两个Log存在第二个Block中
2. 第3个Log架构师和第4个Log的前半部架构设计分存在第三个Block中
3. 第4个Log的后半部分和第5个Log存在毕竟一个Block中

在物理存储上Row Log分为两部分：

1. 内存日志：内存中会存放一个总巨细等于inndo数据库体系的中心是b_sort_buffer_size数据库设计的Block，用于写入DML操作
2. 文件日志：当内存中的Block写满，也便是巨细大于innodb_sort_buffer_size，且小于innodb_online_alter_log_max_size时，写满的Block会刷到磁盘数据库办理体系上，空mysql面试题出内存中的Block给后续的Log架构师需求把握哪些常识写入，日志文件中，悉数Block总巨细假定超越innodb_online_alter_log_max_s架构ize，写入就会报错

Row Log的中心结构如下：

1. Log数据库体系：标明DML操作日志，它的结构为操作flag + 事务id + 操作记载，其间，操作flag包括两种：INSERT和DELETE，UPDATE看作是先DELETE，再INSERT。比如，上图第一个Log中包括一条记载<0x61 + 1234 + <25, 1998-01-02, 1&gMySQLt;>，其间，0x61代表这是一个刺进操作，1234标明这个操作的事务id，<25, 1998-01-02, 1>标明操作的记载数据库体系工程师。
2. head：这是用于将Block中的Log回放到索引树时，用来扫描Block中Log的指针，扫完一个Log，head指针向mysql装置后移到下一个Log。如上图，因为从Block的头部开始扫描，hemysql增修改查句子ad指针在回放前处在Block的第一个Log的方位。
3. tail：这是用架构设计于将DML操作写入一个Block时，用来定位Block中Log刺进方位的指针，刺进完一个Log，tail指针向后移动到新刺进的Log。如上图架构师，因为从Block的头部开始刺进Log，所以，tail指针在刺进前处在Block的第一个Log的方位。
4. blocks：无论是head仍是tail指针，都包括一个blocks字段，标明Row Log日志文件中包括的Block数量

Row Log追加

下面咱们再来看下Log是怎样追加到Row Log的？我以user表的index_age_birth索引的Row Log为例来说明：

见上图，从上到下，咱们来看下这个追加的进程：

1. 假定内存中没有Block，创立一个innodb_sort_buffer_size架构师巨细的Block，tail指针指向Block中的第一个Log，假定有Block，tail指针指向BlockMySQL中毕竟一个Log。如上图，内存中有Block，tail指向Bmysql面试题lock中架构图终数据库设计究一个Log，也便是虚线架构师薪酬一月多少框前面那个Log

2. 依据行将刺进的DML操作日志巨细，得到Block中下一个Log相对毕竟一个Log的偏移量。如上图中的offse架构t，这儿分两种状况：

   (1) 假定DML操作日志巨细 >= innodb_sort_buffer_size - 当时Block中已有Log的总巨细，则偏移量为innodb_sort_buffer_size - 当时Blo数据库设计ck中已有Log的总巨细

   (2) 假定DML操作日志巨细 < innodb_sort_buffer_size - 当时Block中已有Log的数据库原理总巨细，则偏移量为D架构师薪酬一月多少ML操作日志巨细

3. 依据tail指针和偏移量MySQL，将刺进的DML操作日志拷贝到内存的Block。这儿相同分两种状况：

   (1) 全拷贝

   • 假定DML操作日志巨细 < innodb_sort_buffer_size - 当时Block中已有Log的总巨细，将DML操作日志悉数拷贝到Block中完毕Log。如上图，全拷贝最右侧，将DML日志<0x架构师认证61 + 3355 + <25, 1998-01-02, 1>>完好拷贝到完毕Log，然后，将tail移到被拷贝的Log上

   (2) 半拷贝

   • 假定DML操作日志巨细 >= innodb_somysql数据库命令大全rt_buffer_size - 当时Blocmysql装置k中已有Log的总巨细，拷贝D数据库ML操作日志的前面部分到tail后边偏移量巨细的数据库体系的中心是空间。如上图半拷贝里的上半部分，将DML日志<0x61 + 3355 + <25, 1998-01-02, 1>>的前半部分拷贝到完毕Log，然后，将tail移到被拷贝的Log上

   • 将内存中整个Block写入Row Log日志文件。如上图，半拷贝里上半部分大括号包括了整个Block，一起将该Block经过箭头，写入row_log_file

   • 从头将tail移到内存空Block的头部，将DML操作的后半部分拷贝到tail后边偏移量巨细的空间。如上图半拷贝里的下mysql数据库半数据库原理部分，将DML日志&l数据库原理t;0x61 + 3355 +架构图 <25, 1998-01-02, 1&g架构是什么意思t;>的后半部分拷贝到Block的头部

   • 如上图，tail.blocks + 1，代表Row Log日志文件中新增了一个Block。

Row Log回放

MySQL将DML日志写到Row Log只是为了在实施DDL期间mysql怎样读，能够并数据库查询句子行实施DML，毕竟，这些DML日志仍是要更新(回放)到索引树上的，所以，相同以索引index_age_birth为例，咱们再来看下Row L架构师薪酬一月多少og中的日志是怎样更新到索引树的？

从上到下，咱们来看上面这张图：mysql怎样读

1. MySQL先扫描磁盘上的Row Log文件，数据库体系的中心是遍历文件中的Block，如上图，文件扫描部分为一个Block的遍历：

   (1) head指针指向Block的头部Log，从该Log开始，将头数据库查询句子部Log写入索引树。如上图，文mysql怎样读件扫描中的最上面部分，将DML日志0x61 + 3355 + <25, 1998-01-02, 1>>中的记载写入索引树index_age_bi架构师rth的第一个叶子节点。

   (2) 头部Log清空，将head指针移到后架构图边一个Log。如上图，文件扫描中的第二块长方框。

   (3mysql暗码忘记了怎样办) 重复(1)和(2)两步，直到head指针移到Block中毕竟一个Log，然后，将该Log中的记载写入索引树index_age_birth。如上图，文件扫描中的第三个长方框及方框中毕竟一个Log中的记载写入索引树index_ag数据库查询句子e_birth的第二个叶子节点。。

2. 重复进程1，将Row Log文件中悉数Block内的Lmysql数据库命令大全og悉数写入索引树index_age_birth，至此，Row Log文件清空。如上图，文件扫描中毕竟一个虚线长方框，标明Row架构师薪酬一月多少 Log文件清空。

3. 因为DML日志写Row架构图 Log和DDL一起进行，结合《Row Log追加》中的进程，咱们会发现大部分Block写入了Row Log文件，可是，还会存在小部分DML日志留存在内存的Block中，所以数据库有哪几种，MySQL需求将这部分留存的Log再写入索引树中，具体进程如下：

   (1) 对数据字典加排它锁，阻止新的DML操作，ps：假定不加锁，会导架构师需求把握哪些常识致内存中Block不断更新，无法判别DML操作何时完毕。

   (2) 实施架构师和程序员的差异进程1，将内存Block中的Log悉数写入索引树index_age_birthmysql数据库，如上图，内存扫描部分。

Bulk Load

在说明增加索引的进程之前，还有一个概念再阐数据库体系的中心是明一下，这就数据库是Bulk Load，在增加索引的进程中，会将已排序的记载批量刺进索引树的叶子节点中，这个批量刺进数据库的进程就叫做Bulk Load，我mysql怎样读以索引index_age_birth为例，说明一下这个进程，见下图：

1. 从已排序的记载集平分多批写入内存的bulk中。如上图，MySQL将最左面已排序的记载架构图制造集拆分红两批写入2个bulk中，上面的bulk包括15, 2008-02-03, 2和15, 2008-02-06, 5两条记载，下面的bulk包括16, 2架构师007-06-06, 6、17, 2006-03-03, 4和18, 2002-06-07, 3 三条记载。
2. 以bulk为单位，将bulk中的记载集数据库体系的中心是一次刺进索引树中。如上图，上面的bulk记载集刺进到索引树index_age_birth的第三个叶子节点，下面的bulk记载集刺进到数据库体系概论第五版课后答案索mysql优化引树index_age_bimysql装置rth的倒数第二数据库体系的中心是个叶子节点。

增加索引

Row Log的追加和回放，以及Bulk Load是数据库查询句子增加索引进程中的中心进程，讲完这三个进程，下面我再来看一下InnoDB引擎中MySQL增加索引的进程就比较简单理解了，该进程首要分三个阶段，我以user表为例具体说明一mysql装置装备教程下：

Prepare阶段：

1. 依据旧表user的数据库表结构文件frm，创立一个副本表结构frm文件，将新索引增加到副本中
2. 取得MDL排他锁，阻止读写数据字典及旧user表，关于MDL锁，我会在《MySQL锁全解析》具体说明
3. 依据alter类型，确认mysql数据库实施办法，总共两种实施办法：COPY、INPLACE
4. 更新内存中的数据字典，符号user表悉数索引online_stamysql索引tus为ONLINE_INDEX_CREATION，标明该表索引都处在在线DDL状况。关于数据字典的结构，我在《咱们能够干与MySQL挑选正确的实施方案吗？》中有说明过。
5. 依据旧表user的imysql增修改查句子bd文件，创立副本ibd文件

DDL实施阶段：

1. 降数据库体系概论级MDL锁为同享锁，容许读写数据字典及旧user表

2. 扫描旧表usermysql暗码忘记了怎样办的集结索引中叶子节点每一条记载

   (1) 恳求一个sor架构t_buffer，巨细为innod架构师认证b_sort_buffer_size/索数据库引叶子节点中最小的记载的巨细

   (数据库体系概论2) 将每一条记载写入sort_buffer

   (3) sort_buffer写满后对里面的记载进行升序排序

   (4) sort_buffer写满了，假定临时文件不存在，就创立一个临时文件

   (5) 遍历sort_buffer记载，将sort_buffer中的记载写入文件中

   ​ a. 生架构是什么意思成一个block，将记载增加到block

   (6mysql装置) 将block写入临时文件架构师

3. 遍历旧表聚簇索引的记载完毕后，临时文件中就包括多个block，每个block包括已排序的记载

4. 运用归并排序对临时文数据库办理体系件中的block内记载进行排序

5. 遍历副本frm中的调架构图制造集索引和辅佐索引

   (1) 查找索引树，定位到树种最右边的叶子节点

   (2) 判别该节点是否能够有满意空间批量刺架构图制造进记载，假定没有就创立一个新的叶子节点，实施进程(3)，不然，实施进程(4)

   (3) 将新节点接到索引数据库体系概论树的右下角，实施进程(4)

   (4) 遍历临时文件中的记载，将记载经过bul数据库k loamysql索引d办法写入叶子节点

   (5) 调整刺进记载的叶子节点内记载的slot信息数据库办理体系，关于slot，我在《InnoDB是数据库体系工程师次序查找B-Tree叶子节点的吗？》中具体说明过。

6. 在这个阶段，与此一起，usmysql数据库er表的悉数DML操作日志写入Row Log，即《Row Log追加》中说明的进程

7. 重放该阶段发生的user表的Row Log日志到索引中，直到Row Log中的毕竟一个block，即《Row Log回放》中说明的进程。

Commit阶段：

1. 晋级MDL锁为排它锁，阻止读写数据字典及旧user表
2. 将Row Log中毕竟一个block，即内存中Block对应的DML日志刺进索引树，进程参见DDL实施阶段中的进程(7)
3. 更新内存中的数据字典，关于数据字典的结构，我在《咱们能够干与MySQL挑选正确的实施方案吗？》中有说明过。
4. 将DDL实施操作记载redomysql暗码忘记了怎样办日志
5. rename副本ibd文件和frm文件为旧表名，即原user表的frmmysql装置和ibd文件名

在说明完增加索引的进程后，咱们发现影响事务DML操作的环节包括：

1. 循环遍历旧表聚簇索数据库设计引叶子节点的悉数记载，假定表记载非常多，非常耗费CPU，假定DDL长期占用CPU资源，势必会影响MySQL的衔接数据库体系数，导致MySQL处理DML操作的并发央求数下降
2. 归并排序运用的磁盘临时文件做记载排序，假定文件中的已排序记载集架构图用什么软件做非常多，那么，归并排序进程中发生许多的磁盘IO，在MySQLMySQL处理查询时，假定内存中没有查询的成果，此刻，b架构图制造uffer pool又满了，触发刷脏行为，这时就会出现查询央求等候刷脏完毕，查架构是什么意思询照应变慢。

或许这时候你会问，Prepare阶段和Commit阶段都加了排它锁，为什么这两数据库体系的中心是个环节不影响DML操作呢？因为虽然这两个阶段都加了排它锁，但mysql暗码忘记了怎样办是，加锁后的操作都是小数据规划的操作，所以，加锁时刻很短数据库体系的中心是，对DML的影响架构师认证不大，所以，能够忽略不计。

那么，咱们看看上面两mysql装置个问题怎样处理呢？

针对第一个问题，因为表中的原有记载的数量是由事务展开抉择的，事务展开快数据库有哪几种，记载数就会多，这点咱们无法控制，所以，针对表数据量大导致扫数据库办理体系描聚簇索引变慢，咱们只能逃避DDL带来的风险，逃避办法如下：

1. 点评表中的数据量
2. 调查MySQL的CPU运用率

结合上面两个因架构师薪酬一月多少素，假定数据量不大，那么，只要在非极点高峰期实施DDL，对DML的 影响是不大的数据库有哪几种。假定数据量很大，建议找到MySQL的CPU运用率比较低的状况下做DDL，保证不影响DML操作.。

针对第二个问题，咱们能够mysql怎样读经过调整参数innodb_sor架构图用什么软件做t_buffer_size，将其调大，使归并架构师需求把握哪些常识排序来历的临时文件中已排序的block数量尽或许少，削减许多block的吞并，然后下降磁盘IO

主从方式下的问题

平常咱们用的最多的MySQ架构师需求把握哪些常识L架构便是主从方式，所以，咱们来看一下在这种方式下，在线DDL的进程是怎样样的MySQL呢？

1. 结合架构图用什么软件做《增加索引》中的进程，咱们知道DDL和DML并行阶段，DDL一边实施，DML一边写入Row Log。如上图，左面在master中，DDL和INSERT，以及UPDATE并行实施，DDL在实施的一起，INSERT和UPDATE并行写mysql数据库命令大全入Row Log
2. DDL和DML并行进程中，将DDLMySQL操作和并行的DML按序写入binlomysql装置装备教程g。如上图，左面master将DDL和INSERmysql装置T数据库查询句子、UPD数据库体系概论第五版课后答案ATE操作按序写入binlog，DDL第一、其次是INSERT，毕竟是UPDATE
3. DDL实施完毕，将master的binlog数据库有哪几种同步到slave上。如上图，将左面master的binlog中的三条操作同步到slave上
4. 在slave上依次回放DDL和DML。如上图，右边在slave中依次实施DDL、INSERT和UPDATE

经过上面这个进程，你应该现已想到，在DDL和DML并行的阶段，假定发生许多的DML操作，那么，在slave端回放这些DML操作会耗费许多的时刻，会影响从库读的数据一致性。所以，这便是主从方式下，在线DDL的问题和风险。

小结

经过本章的说明，我想你应该对MySQL的在线DDL的机制有了清楚的知道，一起，经过在线DDL机制的说明，咱们也发现了一些优化的办法：