对整个数据库实例加锁。
MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
使用场景：全库逻辑备份。
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。
一致性读是好，但是前提是引擎要支持这个隔离级别。
如果要全库只读，为什么不使用set global readonly=true的方式？
1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。
有两种，一种是表锁，一种是元数据所(meta data lock,MDL)
表锁的语法是:lock tables … read/write
可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。
- 两阶段锁的概念是什么? 对事务使用有什么帮助?
概念：在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
帮助：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
- 死锁的概念是什么？
概念：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。
-
死锁的处理策略有哪两种？
策略一：直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置，默认值为50s。
策略二：发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。
-
等待超时处理死锁的机制什么?有什么局限?
机制：在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。
局限：我们又不可能直接把这个时间设置成一个很小的值，比如1s。这样当出现死锁的时候，确实很快就可以解开，但如果不是死锁，而是简单的锁等待呢？所以，超时时间设置太短的话，会出现很多误伤。
-
死锁检测处理死锁的机制是什么? 有什么局限?
-
有哪些思路可以解决热点更新导致的并发问题?
方法一：临时把死锁检测关掉，对业务有损。
方法二：客户端控制并发/在数据库服务端增加中间件，控制并发。
方法三：将并发的一行改成逻辑上的多行减少锁的冲突。还是以影院账户为例，可以考虑放在多条记录上，比如10个记录，影院的账户总额等于这10个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的1/10，可以减少锁等待个数，也就减少了死锁检测的CPU消耗。缺点：增加了代码复杂度。
给 k字段加上索引。
- 对于普通索引，查找到满足条件的第一个记录后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。
- 对于唯一索引，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。
普通索引会比唯一索引多做一次“查找和判断下一条记录”的操作,但是二者的性能差异可以忽略。
- InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在InnoDB中，每个数据页的大小默认是16KB。
- 因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
-
概念：当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作。
-
持久化：change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上
-
merge:将change buffer中的操作应用到原数据页上，得到最新结果的过程，成为merge
访问这个数据页会触发merge，系统有后台线程定期merge，在数据库正常关闭的过程中，也会执行merge. -
唯一索引的更新不能使用change buffer.
-
change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。
-
将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。
change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。 -
适用场景：
在一个数据页做merge之前，change buffer记录的变更越多，收益就越大。
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发merge过程。
这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。 -
changebuffer是否会丢失：（原因：1. changebuffer会持久化、2. 会写入redolog）
change buffer不会丢失，因为change buffer是可以持久化的数据，在磁盘上占据了系统表空间ibdata，对应的内部系统表名为SYS_IBUF_TABLE。同时对changebuffer修改会写入redolog，可以通过redolog进行恢复。因此在异常关机的时候，不会丢失。但是未写入redolog的操作，会正常丢失这部分数据。
redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。
（三）更新过程InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。(redo log)
三、 mysql更新抖动此时可能InnoDB在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了一下的原因。
如果刷太慢，可能原因：首先是内存脏页太多，其次是redo log写满。
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
（一）场景场景一：InnoDB的redo log写满了。这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写，此时可能会进行flush刷盘。
场景二：系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。
场景三：MySQL认为系统“空闲”的时候，自动进行刷盘。
场景四：MySQL正常关闭的情况。这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
（二）分析性能影响 1. 性能分析场景三和场景4属于正常情况，不需要关注性能。
场景一：是InnoDB要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为0。
场景二：内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
-
第一种是，还没有使用的；
-
第二种是，使用了并且是干净页；
-
第三种是，使用了并且是脏页。
我们需要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。
这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。
这个值我建议你设置成磁盘的IOPS。磁盘的IOPS可以通过fio这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest
3. 设计策略控制刷脏页的速度，参考因素如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是redo log写满。
所以，InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。
InnoDB会根据这两个因素先单独算出两个数字。
脏页比例计算：
select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_dirty’;
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’;
select @a/@b;
四、收缩表空间delete 表中不用的数据，表文件大小是不会变的，需要通过 alter table命令重建表。
重建表的两种方式：
（一）重建表方式一（mysql 5.6之前）命令：alter table t engine=innodb,ALGORITHM=copy;
copy table。
原表A上有空洞，以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。在这整个DDL过程中表A中不能有更新
（二）重建表方式二（mysql 5.6之后）命令：alter table t engine=innodb,ALGORITHM=inplace;
提供了online DDL。允许在DDL过程中更新数据。
建表流程：
- 建立一个临时文件，扫描表A主键的所有数据页；
- 用数据页中表A的记录生成B+树，存储到临时文件中；
- 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；
- 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；
- 用临时文件替换表A的数据文件。
MySQL各版本，对于add Index的处理方式是不同的，主要有三种：
（1）Copy Table方式
这是InnoDB最早支持的创建索引的方式。顾名思义，创建索引是通过临时表拷贝的方式实现的。
新建一个带有新索引的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建索引的操作。
这个方式创建索引，创建过程中，原表是可读的。但是会消耗一倍的存储空间。
（2）Inplace方式
这是原生MySQL 5.5，以及innodb_plugin中提供的创建索引的方式。所谓Inplace，也就是索引创建在原表上直接进行，不会拷贝临时表。相对于Copy Table方式，这是一个进步。
Inplace方式创建索引，创建过程中，原表同样可读的，但是不可写。
（3）Online方式
这是MySQL 5.6.7中提供的创建索引的方式。无论是Copy Table方式，还是Inplace方式，创建索引的过程中，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式创建索引。
InnoDB的online Add Index，首先是Inplace方式创建索引，无需使用临时表。在遍历聚簇索引，收集记录并插入到新索引的过程中，原表记录可修改。而修改的记录保存在Row Log中。当聚簇索引遍历完毕，并全部插入到新索引之后，重放Row Log中的记录修改，使得新索引与聚簇索引记录达到一致状态。
与Copy Table方式相比，online Add Index采用的是Inplace方式，无需Copy Table，减少了空间开销；与此同时，online Add Index只有在重放Row Log最后一个Block时锁表，减少了锁表的时间。
与Inplace方式相比，online Add Index吸收了Inplace方式的优势，却减少了锁表的时间。
五、日志问题redo log支持崩溃恢复；bin log不支持崩溃恢复。
两阶段提交的图：
A时刻发生crash，写binlog和redolog之间发生crash：此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。这时候，binlog还没写，所以也不会传到备库。
B时刻发生crash，binlog写完，redo log还没commit前发生crash：
- 如果redo log 里面事务完整，已经有了commit标识，则直接提交；
- 如果redo log里面的事务只有完整的的prepare，则判断对应的事务bin log是否存在并完整
- 如果是，则提交事务
- 不是，则回滚事务
一个事务的bin log有完整的格式：
- statement格式的binlog，最后会有COMMIT；
- row格式的binlog，最后会有一个XID event。
他们有共同的数据字段，XID。系统崩溃（crash）恢复数据时，会顺序扫描redo log.
- 如果碰到既有prepare、又有commit的redo log，就直接提交；
- 如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。
另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现。所以，MySQL还是有办法验证事务binlog的完整性的。
4. 处于prepare阶段的redo log加上完整binlog，重启就能恢复，MySQL为什么要这么设计采用这个策略，主库和备库的数据就保证了一致性。
在时刻B，也就是binlog写完以后MySQL发生崩溃，这时候binlog已经写入了，之后就会被从库（或者用这个binlog恢复出来的库）使用。
5. 不引入两个日志，也就没有两阶段提交的必要了。只用binlog来支持崩溃恢复，又能支持归档，不就可以了？不可以；
MySQL的原生引擎是MyISAM，设计之初就有没有支持崩溃恢复。
InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。
binlog没有能力恢复“数据页”，binlog里面并没有记录数据页的更新细节，是补不回来的。
6. 只用redo log 不要bin log两个重要原因：
- redo log不具备bin log的归档能力。
- bin log 复制，是mysql高可用的基础，现在mysql高可用的系统机制很多依赖与bin log。
业界各个公司的使用场景的话，就会发现在正式的生产库上，binlog都是开着的。因为binlog有着redo log无法替代的功能。
一个是归档。redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。
一个就是MySQL系统依赖于binlog。binlog作为MySQL一开始就有的功能，被用在了很多地方。其中，MySQL系统高可用的基础，就是binlog复制。
还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费MySQL的binlog来更新自己的数据。关掉binlog的话，这些下游系统就没法输入了。
总之，由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog，所以“鸠占鹊巢”redo log还做不到。你看，发展生态是多么重要。
7 .redo log 一般设置多大redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log，这样WAL机制的能力就发挥不出来了。
所以，如果是现在常见的几个TB的磁盘的话，就不要太小气了，直接将redo log设置为4个文件、每个文件1GB吧。
六、索引问题 1. insert into …on duplicate key update类似oracle中的merge语句，无则插入有则更新。
INSERT INTO T(A,B,C,D) VALUES (a,b,c,d) ON DUPLICATE KEY UPDATE C=C，D=d ；
2.索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。 （1）条件字段函数操作不走索引。
（1）隐式类型转换数据库字段类型为varchar(32)，而输入的参数却是整型，所以需要做类型转换，将varchar转为int及逆行比较，此时不走索引。
数据库字段类型为int，而输入的字段类型却是varchar类型，这个情况走索引。
（2）隐式字符编码转换两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。
为什么字符集不同就用不上索引呢？
字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。对索引字段做函数操作，优化器会放弃走树搜索功能。
七、order by 工作原理 （一）排序过程-
MySQL会为每个线程分配一个内存（sort_buffer）用于排序该内存大小为sort_buffer_size
（1）如果排序的数据量小于sort_buffer_size，排序将会在内存中完成（快速排序）
（2）如果排序数据量很大，内存中无法存下这么多数据，则会使用磁盘临时文件来辅助排序，也称外部排序
（3）在使用外部排序时，MySQL会分成好几份单独的临时文件用来存放排序后的数据，然后在将这些文件合并成一个大文件（归并排序） -
mysql会通过遍历索引将满足条件的数据读取到sort_buffer，并且按照排序字段进行快速排序
（1）如果查询的字段不包含在辅助索引中，需要按照辅助索引记录的主键返回聚集索引取出所需字段
（2）该方式会造成随机IO，在MySQL5.6提供了MRR的机制，会将辅助索引匹配记录的主键取出来在内存中进行排序，然后在回表
（3）按照情况建立联合索引来避免排序所带来的性能损耗，允许的情况下也可以建立覆盖索引来避免回表
全字段排序
1.通过索引将所需的字段全部读取到sort_buffer中
2.按照排序字段进行排序
3.将结果集返回给客户端
缺点：
1.造成sort_buffer中存放不下很多数据，因为除了排序字段还存放其他字段，对sort_buffer的利用效率不高
2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差
优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作
rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
3.按照排序后的顺序，取id进行回表取出想要获取的数据
4.将结果集返回给客户端
优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问
缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问
uffer的利用效率不高
2.当所需排序数据量很大时，会有很多的临时文件，排序性能也会很差
优点：MySQL认为内存足够大时会优先选择全字段排序，因为这种方式比rowid 排序避免了一次回表操作
rowid排序
1.通过控制排序的行数据的长度来让sort_buffer中尽可能多的存放数据，max_length_for_sort_data
2.只将需要排序的字段和主键读取到sort_buffer中，并按照排序字段进行排序
3.按照排序后的顺序，取id进行回表取出想要获取的数据
4.将结果集返回给客户端
优点：更好的利用内存的sort_buffer进行排序操作，尽量减少对磁盘的访问
缺点：回表的操作是随机IO，会造成大量的随机读，不一定就比全字段排序减少对磁盘的访问
