mysql实战45讲学习笔记-ag真人游戏

1&2一条语句的执行过程

1. mysql结构

数据库总共分为两大部分：server层和存储层，其中server层又分为：连接器、（查询缓存）、分析器、优化器和执行器。存储层是以插件的形式，常见的有innodb和myisam。

2. 查询语句（select * from t where id = 2）

通过连接器获取连接
查看语句是存在于查询缓存中，如果存在则直接返回，不存在则进行步骤3，mysql 8.0版本之后已经去掉了查询缓存功能
通过分析器判断sql语句是否正确（要做什么）
通过优化器判断使用哪个索引去查询（怎么做）
执行器判断是否拥有该表权限，如果有则调用存储引擎的拿第一条数据的接口，判断id是否是等于2，如果不等则调用拿下一条数据的接口，如果等于则将结果放入到结果集中，继续判断下一条。

3. 日志

mysql中的日志分为两个部分：redo log 和 binlog。
redo log ：这个服务于存储引擎，这个是innodb引擎特有的日志系统
binlog : 这个服务于server层，是所有引擎都可以使用的。

redo log 是为了减少磁盘的读写和crash-safe而产生的，它记录了更新、删除、插入语句，当mysql空闲时就从redo log中读取这些日志，把数据更新到磁盘中。而binlog是记录做了哪些事情，当我们恢复备份信息时，可以读取binlog中的记录，然后去执行redo log中的记录去恢复信息。

3.1 binlog

binlog 分为3种模式，在5.5之前默认为 statement 后面推荐使用 mixed模式

statement：基于sql语句的模式，某些语句和函数如uuid, load data infile等在复制过程可能导致数据不一致甚至出错。
row：基于行的模式，记录的是行的变化，很安全。但是binlog会比其他两种模式大很多，在一些大表中清除大量数据时在binlog中会生成很多条语句，可能导致从库延迟变大。
mixed：混合模式，根据语句来选用是statement还是row模式。

举例

delete * from users where id < 100

在 statement 模式下只会记录这一行sql，但是在 row 模式下会记录具体删除的那些行，所以 row模式下可能会产生大量数据。

4. 更新语句（update t c = c 1 where id = 2）

执行器找存储引擎拿到 id = 2 这一行记录，id是主键，存储引擎直接通过树搜索找到这一行记录，如果这一行所在的数据页在内存中则直接返回，如果不在则去读取磁盘到内存中再返回。
执行器拿到这一行记录，将 c 列的值 1 得到一行新数据，然后再调用存储引擎接口写入这行新数据
存储引擎拿到这行数据写入到内存中，同时将这个更新操作记录到redo log日志中，此时redo log处于prepare状态，并告诉执行器可以提交事务了
执行器生成这个操作的binlog，并把binlog写入到磁盘中。
执行器调用存储引擎的提交事务的接口，存储引擎把刚刚写入的redo log日志状态更新为commit，这也就是两阶段提交

3. 事务隔离

3.1 事务隔离的特性

acid：原子性、一致性、隔离性、持久性

一致性：由原子性，隔离性和持久性保证
原子性：由 undo log 保证，undo log 会保存每次变更之前的记录，从而在发生错误时进行回滚。
隔离性：由 mvcc 和 lock 保证
持久性：由 redo log 保证。每次真正修改数据之前，都会将记录写到 redo log 中，只有 redo log 写入成功，才会真正的写入到 b 树中，如果提交之前断电，就可以通过 redo log 恢复记录。

3.2 事务的隔离级别

读未提交(ru)：一个事务还未提交，其他事务就可以看到它做的更改
读已提交(rc)：一个事务在提交之后，其他事务才能看到它的更改。
可重复读(rr)：一个事务是启动和提交之间读到数据是一致的，它在未提交时，其他事务无法看到它做的更改。
串行化(s)：对同一行记录，写会加写锁，读会加读锁，当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完才能继续执行。

修改隔离级别：设置参数：transaction-isolation

3.2.1 事务隔离级别实现原理

读未提交(ru)：读不加读锁，写加了写锁，由于没有读锁所以写锁一直不会排它
读已提交(rc)：利用mvcc，生成语句级别的快照
可重复读(rr)：利用mvcc，不过它生成的是事务级别的快照。
串行化(s)：读加读锁，写加写锁

3.2.2 mvcc

如果没有mvcc，加了写锁之后就不能进行读取了，这显然是不能接受的，所以就出现了mvcc，多版本并发控制，说白了就是生成数据快照，比如在 rc 级别下，对语句生成快照，在读取的时候生成一个版本号v1，等到其他事务commit了之后会生成版本号v2，如果有最新的版本号，读取时则会读取最新的，也就是v2，如果没有commit，那么还读取刚生成的v1。而在 rr 级别下，快照的是当前事务的版本，即时数据别其他事务改了，也是能读到最初事务的数据，这也就解决了不可重复读的问题。

事务版本号

每开启一个事务，我们都会从数据库中获得一个事务 id（也就是事务版本号），这个事务 id 是自增长的，通过 id 大小，我们就可以判断事务的时间顺序。

行记录的隐藏列

innodb 的叶子段存储了数据页，数据页中保存了行记录，而在行记录中有一些重要的隐藏字段

db_row_id：隐藏的行 id，用来生成默认聚簇索引。如果我们创建数据表的时候没有指定聚簇索引，这时 innodb 就会用这个隐藏 id 来创建聚集索引。采用聚簇索引的方式可以提升数据的查找效率。
db_trx_id：事务id，操作这个数据的事务 id，也就是最后一个对该数据进行插入或更新的事务 id。
db_roll_ptr：回滚指针，也就是指向这个记录的 undo log 信息，

每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。回滚指针将数据行的所有快照记录都通过链表的结构串联了起来，每个快照的记录都保存了当时的 db_trx_id，也是那个时间点操作这个数据的事务 id。这样如果我们想要找历史快照，就可以通过遍历回滚指针的方式进行查找。

3.3 脏读、不可重复读、幻读

脏读：事务a读取到了事务b还没有提交的数据（ru存在）
不可重复读（前后多次读取，数据内容不一致）：事务a读取了其他事务更改的数据，针对update操作（ru、rc存在）
解决：使用行级锁，锁定该行，事务a多次读取操作完成后才释放该锁，这个时候才允许其他事务更改刚才的数据。
幻读（前后多次读取，数据总量不一致）：事务a读取到了其他事务新增的数据，针对insert和delete操作 (ru、rc存在)
解决：mvcc 间隙锁行锁，在rr级别下只解决了大部分幻读情况，但是不能完全防止幻读。

间隙锁行锁合成为 next-key lock

幻读：事务a执行了select…where，检索一定范围内数据，然后事务b在这个范围内插入了一条记录，该记录正好也满足事务a的where子句，当事务a再次执行select，就会发现多了一条记录，这就是幻读。

3.3 事务隔离的实现

每条记录在更新的时候都会记录一条回滚操作，记录上的最新值通过回滚操作都能回到它上一个版本，同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（mvcc），它是由undo log来实现的

3.4 回滚日志(undo log)

回滚日志什么时候删除?
系统会判断当没有实物需要用到这些回滚日志的时候，回滚日志会被删除。
什么时候不需要？
当系统中没有比这个回滚日志更早的read-view的时候

3.5 为什么尽量不使用长事务？

长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还会占用锁资源，可能会拖垮整个库。

4、5 深入浅出索引（上，下）

4.1 索引的实现方式有哪些

哈希表

将值放到数组里，然后用哈希函数将key换算为一个确定的位置，将value放到数组的这个位置，当哈希冲突时，会拉出一个链表进行保存。适用场景：只适合等值查询情况，不适合用于范围查询

有序数组

将值按顺序放入到数组中，可采用二分法查询，时间复杂度为o(lg(n))，但是插入比较麻烦，需要移动很多值。适用场景：不再变化的值。

二叉树搜索树

每个结点的左儿子小于父节点，右儿子大于父节点，平衡二叉树是搜索速度最快的数据结构，但是索引不仅存在于内存，也要存储到硬盘中，如果用平衡二叉树，那么100万的数据就是一个树高20的二叉树，对应磁盘就是20个数据块，要查询一个数据要访问20个数据块，这就很慢了。

n叉树

n叉树顾名思义就是每个节点有n儿子，儿子之间从左到右递增。它是为了解决二叉树占用数据块太多而产生的。

4.2 innodb引擎使用的索引

innodb是使用b 树来存储数据的。每一张表其实就是由多个b 树组成的，树结点的key值就是某一行的主键，value是该行的其他数据。每一个索引都会创建一个b 树。

4.2.1 b 树

innodb 存储引擎的表就是索引组织表，表中数据按照主键顺序存放
索引组织表每张表的主键构造⼀棵b 树，在叶⼦节点中存放整张表的⾏记录
所有叶⼦节点到根节点的⾼度h都相同，所以⼜称平衡树（balance）
叶⼦节点的数据按照key升序排列，节点间通过叶编号组成⼀个双向链表
主键索引树，叶子节点存放的是整行记录，而二级索引树叶子节点存放的数据是主键id

主键查询过程

select * from table where primary_key=10

查询⾸先从 root->branch->leaf block->table records
在leaf block中存放了我们的数据⾏，并且按照主键pk的顺序进⾏排序；
root和branch的通常被cache；
叶⼦节点的数据通常需要从磁盘中访问，所以整个过程需要1个iops

4.2.2 b树

b树每一个节点都存储数据，而且树层级不确定，有时层级会特别高，这样查询会多出很多iops。

4.2.3 b树与b 树对比

b树中的同⼀键值不会出现多次，它有可能出现在叶⼦节点上，也有可能出现在内节点上；⽽b 树的键⼀定会出现在叶⼦节点上，同时也有可能在⾮叶⼦节点中重复出现。简单来说，b 树的内节点存储的都是键值，键值对应的具体数据都存储在叶⼦节点上。所以，b 树⽐b树会多占⽤⼀些空间，这部分空间就是b 树内节点的所有空间，但b 树通过这种⽅式提⾼了整体性能，更适合于性能要求很⾼的⽂件检索。
由于b树的每⼀个节点都存储了真实的数据，会导致每⼀个节点存储的数据量变⼩，所以整个b树的层数就会相对变⾼，当数据量变⼤之后，维护代价是⽐较⼤的，⽽且层数越⾼，搜索或修改的性能就会越低；⽽在b 树的内节点中，只存储键值，相对⽽⾔，⼀个内节点存储的记录个数⽐b树多很多，数据存放的更加紧密，具有更好的空间局部性，因此访问叶⼦⼏点上关联的数据也具有更好的缓存命中率。由于b 树是横向扩展的，所以随着其中数据量的增长，最终会成长为⼀个矮胖⼦，不像b树⼀样是纵向扩展，最终只会变成⼀个瘦⾼个⼦。这样整体⽽⾔，b 树在搜索时，从上到下直到叶⼦节点只需要遍历层数个节点⽽已，因此性能会⽐较⾼。
b 树的叶⼦节点之间使⽤双向链表相连，根据页⾯编号，因此对整棵树的遍历只需要⼀次线性遍历叶⼦结点即可。⽽且由于数据顺序排列并且相连，所以便于区间查找和搜索。⽽b树则需要进⾏每⼀层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有b 树好。
b树的查询效率与键在b树中的位置有关，（在叶⼦节点的时候）最⼤时间复杂度与b 树相同，最⼩时间复杂度为1（在根节点的时候）；⽽b 树的复杂度对某个建成的树是固定的。
b树中，键的位置不固定，且在整个树结构中只出现⼀次，虽然可以节省存储空间，但却使得插⼊、删除等操作复杂度明显增加。⽽且性能不平衡，有可能会很快找到合适的位置，也有可能需要做⽐较多的io操作才能找到。⽽b 树相对来说是⼀种较好的折中，因为内节点相对叶⼦节点⽽⾔，相当于是⼀个索引，在插⼊的过程中，只需要通过在每⼀层搜索⼀个节点，依次找到叶⼦节点之后，在叶⼦节点处做插⼊操作即可，只是在遇到⼀个节点存储满了的情况下会进⾏b 树分裂，但总体⽽⾔性能还是⽐较稳定的。

4.3 索引类型与回表

索引分为主键索引（聚簇索引）和非主键索引，主键索引的叶子结点存放的是这一行的数据，而非主键索引的叶子结点存放的是主键索引的值。当使用主键索引去查询时可以直接获取到该行数据，而使用非主键索引去查询时，会先拿到主键的值，再根据主键获取到该行数据，这个过程被称为回表

4.4 覆盖索引

表 user，id（主键），name（普通索引）

当我们想查询 name = 张三的id 时我们可以使用

select * from user where name = '张三'

这条语句的执行过程为：先去索引树name中找到张三拿到张三的id，再去主键索引树中根据id拿到这条记录，而我们只是需要它的id的，使用这条语句会进行一次回表操作，所以我们可以改为下面语句：

select id from user where name = '张三'

这种方式就叫做覆盖索引，我们可以通过一些联合索引的方式去避免进行二次回表操作。

4.5 索引最左前缀

表 user，id（主键），gender（性别），name（姓名），age（年龄）
联合索引（name，age）

当我们查询姓张并且年龄为10岁的男孩时：

select * from user where name like '张%' and age = 10 and gender = 1

它会先找到第一个姓张的记录，然后再向后依次遍历，这种就避免了全表扫描。

一般来说如果建立了（a，b）联合索引，就不需要在a上单独建立一个索引了，但是如果是根据b来查，那么还是需要在b上建立索引。

4.6 虚拟列

我们可以创建虚拟列，来解决模糊查询带来的性能问题，下列语句将 name字段的第一个值作为 name_first 字段的 value，这样我们查询的时候就可以用这个虚拟列去等值查询，这个功能是mysql版本5.7之后才支持的。

alert table user add name_first varchar(2) generated alwawys as (left(name,1))

6.全局锁和表级锁

61. 全局锁

全局锁即是锁住整个数据库，mysql提供了一个加全局读锁的语句(ftwrl)：

flush table with read lock

加完全局读锁之后，数据库整个的更新，删除，添加语句都会被阻塞，这个使用场景就是数据库备份。但是让数据库处于只读状态，这种方式就会让所有更新被阻塞，整个业务就会停摆。这时我们可以使用官方为我们提供的数据库备份工具mysqldump，通过--single-transaction参数来确保拿到一致性视图:

备份：mysqldump --single-transaction -u用户名 -p密码 数据库名 > back.sql
恢复：mysql -u用户名 -p密码 -f 数据库名< back.sql

这样在备份数据库之前就会启动一个事务，来确保拿到一致性视图，采用这种方式数据库也可以正常更新的。但这种方式有种局限性，那就是必须支持事务，而myisam存储引擎就不支持事务，所以还是得采用全局锁的方式。

问 : 让数据库处于只读状态为什么不用set global readonly=true ?
这是因为使用readonly的话，一旦客户端出现异常，那么整个数据库都处于不可用状态了，而使用 ftwrl一旦客户端出现异常，那么就会自动释放这个锁，整个数据库即可恢复到正常状态（可读可写）。

6.2 表级锁

mysql中的表级锁有两种，一种就是表锁，另一种是元数据锁（mdl）：meta data lock。

6.2.1 表锁

mysql添加表锁可采用下面语句

lock tables 表名 read,表名 write

释放锁和ftwrl类似，当客户端出现异常后也会自动释放锁。也可手动释放：

unlock tables 表名

6.2.2 元数据锁（mdl）

元数据锁是隐式锁，当访问某一张表时，数据库自动加的锁。

当对表增删改查时：加mdl读锁
当更改表结构时：加mdl写锁

读锁之间不互斥，也就是多个线程可以同时对一个表进行增删改查，但是读写锁和写写锁之间互斥，也即是当更改表结构时要等待读锁或写锁释放后才能进行更改。

问：为什么我就给表加个字段，数据库就崩了？
如果在更改表结构之前有一个长事务在操作表（mdl读锁），当我们去添加表中一个字段时那么这个操作会添加一个mdl写锁，由于读写锁互斥，那么这个mdl写锁就会被阻塞，以至于后面的增删改查操作要加mdl读锁的都会被阻塞下去。

为了更加安全的更改表结构我们可以使用下面语句：

alert table 表名 wait 5 add colunm 列名

这个会等待5秒，如果5秒钟拿不到mdl写锁，那么就不再继续阻塞，也就可以后面的操作继续进行下去。

7. 行锁

行锁是在引擎层实现的，但不是每个引擎都支持行锁，像myisam引擎就不支持行锁，它想控制并发就只能加表锁。

行锁分为读锁（共享锁、s锁）和写锁（排它锁、x锁），行锁实际上是作用在索引之上的，当sql命中了索引，那锁住的就是命中条件内的索引节点，如果没有命中索引，那我们锁的就是整个索引树（表锁）。

行锁是有需要时才添加的，但释放是在事务提交之后才进行释放的（两阶段锁），根据这个特性，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。

举个例子：用户a在电影院b买一张电影票（3元）需要下面三个过程：

从用户a账户中扣掉 3 元
在电影院b的余额中增加 3 元
记录一条交易日志

这个三个过程是放在一个事务中的，但是 2 过程是最可能造成锁冲突的，因为其他用户买了票之后也要在电影院b的余额中增加 3 元，所以我们要把最可能造成锁冲突的放在后面，这样电影院b余额这一行的锁时间就最少，我们调整顺序为：3 、 1 、 2

7.1 死锁和死锁检测

事务a在等待事务b释放id=2的行锁，而事务b在等待事务a释放id=1的行锁，事务a和事务b互相等待对方的资源释放，这就造成了死锁。

死锁解决

超时释放
设置超时时间，通过参数innodb_lock_wait_timeout设置，当超过这个时间之后将自动释放锁资源。默认是50s
死锁检测
设置innodb_deadlock_detect为on来开启死锁检测，但它会造成额外的负担，每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

8. 间隙锁

只在rr隔离级别下生效，主要为了防止幻读，通过设置 innodb_locaks_unsafe_for_binlog= true 可关闭间隙锁，默认为 false

8.1 间隙锁目的

是为了防止幻读，其主要通过两个方面实现这个目的：
（1）防止间隙内有新数据被插入
（2）防止已存在的数据，更新成间隙内的数据

8.2 间隙锁锁定范围

根据检索条件向左寻找最靠近检索条件的记录值a，作为左区间，向右寻找最靠近检索条件的记录值b作为右区间，即锁定的间隙为（a，b）。

8.3 间隙锁何时加上

由于间隙锁是作用与索引上的，不同的索引类型，加间隙锁的条件也不同

唯一索引

唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；

对于指定查询某一条记录的加锁语句，如果该记录不存在，会产生记录锁和间隙锁，如果记录存在，则只会产生记录锁，如：where id = 5 for update;
对于查找某一范围内的查询语句，会产生间隙锁，如：where id between 5 and 7 for update;

普通索引

普通索引不管是锁住单条，还是多条记录，都会产生间隙锁

在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；
在普通索引跟唯一索引中，数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序。

9. 选择普通索引还是唯一索引？

9.1 查询过程

其实查询过程两者的时间差距是微乎其微的，普通索引要比唯一索引多一次判断下一条记录是否符合，但innodb 的数据是按数据页为单位来读写的，所以就算多读一次也占用不了多少时间

9.2 更新过程

普通索引可以使用change buffer，可以将一系列的更新写到change buffer中，后期再一次性写入到磁盘中，极大的提高了更新的效率，而唯一索引没有办法使用change buffer

9.3 change buffer 的使用场景

对于写多读少的业务来说，页面在写完以后马上被访问到的概率比小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。反过来，如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer

10. mysql为什么有时候会选错索引?

mysql是根据扫描行数来判断选择哪个索引，扫描行数越少，则越容易被选择，查看扫描行数可以通过explain关键字来查看。

explain select * from user where sex = 1

既然mysql是根据扫描行数来选择索引的，它选错索引肯定也是因为在判断扫描行数的时候出了问题，那mysql又是怎么来判断扫描行数的呢？答案是：采样统计
是的就是这么不靠谱的采样，mysql官方也说了这个误差可能会达到30%~50%

对于这种情况我们可以使用analyze table 表名来重新统计索引信息达到让mysql选择正确的索引。或者使用force index来强制给它指定一个索引

select * from user force index(sex) where sex = 1

11. 怎么给字符串字段加索引？

1. 利用前缀索引

如果字符串过长，而前面几个字段可以确定一个唯一值，比如邮箱，前面都是几位数字 @qq.com，我们不用给全部字段加上索引，而只需要索引前面几个数字即可，这样就极大的节省索引占的空间了。

alter table user add index index_email(email,9)

这个数字9怎么去确定呢，我们可以通过下面的语句，来尝试，如果查出来的值越少，就越好。

select count(distinct left(email,7)) as l7,
	   count(distinct left(email,8)) as l8,
	   count(distinct left(email,9)) as l9,
from user

2. 反转字符串

有的时候字段前面都是一样，而后面是不一样的，比如身份证号，这时就不好利用前缀索引了，不过我们可以将身份证的倒序存储，这样就巧妙的再次利用前缀索引的优势了。

select * from t where id_card = reverse('input_id_card');

3. 使用hash

这种就是将字符串计算出一个hash值，然后给表新增一个字段将hash存储进去，下次查找时先将字符串换算为hash再去表中查找hash列，不过这种只适合等值查询，不能进行范围查询。

4.如何给大表加索引

mysql 版本 >=5.6 ，直接使用无锁加索引

alter table table_name  add primary key (column), algorithm=inplace, lock=none;

mysql 版本小于 < 5.6 ，可在通过 “影子拷贝”来解决，就是先创建一张和源表无关的新表，然后通过重命名和删表操作交换两张表；

#操作步骤：
#1、创建一张和原表结构一样的空表，只是表名不一样
    create table tb_name_tmp like tb_name;
 
#2、把新建的空表非主键索引都删掉，因为这样在往新表导数据的时候效率会很快（因为除了必要的主键以外，不用再去建立其它索引数据了）
    alter tb_name_tmp drop index index_name;
 
#3、从旧表往主表里导数据，如果数据太大，建议分批导入，只需确保无重复数据就行，因为导入数据太大，会很占用资源(内存，磁盘io, cpu等)，可能会影响旧表在线上的业务。我是每批次100万条数据导入，基本上每次都是在 20s左右
    insert into tb_name_tmp select * from tb_name where id between start_id and end_id;
 
#4、数据导完后，再对新表进行添加索引
     create index index_name on tb_name_tmp(column_name);
 
#5、当大部分数据导入后，索引也建立好了，但是旧表数据量还是会因业务的增长而增长，这时候为了确保新旧表的数据一至性和平滑切换，建议写一个脚本，判断当旧表的数据行数与新表一致时，就切换。我是以 max(id)来判断的。
    rename table tb_name to tb_name_tmp1;
    rename table tb_name_tmp to tb_name;

12. 为什么我的mysql会“抖”一下？

当mysql执行过程中会突然慢下来，过一会又好了，而且不是随机的，持续时间很短，看起来就好像mysql“抖”了一下。这个过程其实是mysql在刷"脏页"的过程。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“”脏页”，当把内存中的数据更新到硬盘之后这个页也就变成了“干净页”

大概下面四种情况会发生刷“脏页”过程：

redo log 写满了。当redo log写满了之后，系统会停止所有更新操作，将redo log的checkpoint往前推进，让redo log可以留出空间继续写。
系统内存不足，淘汰脏页时。当需要新的内存页，而内存不足时就需要淘汰一些数据页空出内存给别的数据页使用，如果淘汰的是“脏页”，那么就需要把“脏页”数据写入到磁盘中。
当系统空闲时。当系统空闲时就会将一点一点的将脏页数据刷新到磁盘中
当mysql正常关闭时。mysql正常关闭时会将内存中的脏页数据全部刷新到磁盘中。

我们可以通过设置innodb_io_capacity参数来控制mysql刷脏页的速度，如果你用的是ssd，那么建议你把这个参数设置大点。这个参数在information_schema数据库中的global_variables表中设置。

13. 为什么表数据删掉一半，表文件大小不变？

当innodb_file_per_table的参数为off时，表的数据会放到共享内存中，也就是和数据字典放一块。而为on时，表的数据存储在以.ibd为后缀的文件中，当我们使用drop table删除表时，会直接删除这个文件达到回收的目的，而如果数据是放到了共享内存中，那么即使表删除了，空间也是不会回收的。所以我们一般都将此参数设置为on，mysql5.5.6版本之后默认就是on了。

13.1 删除流程

当我们删除某一行记录时，其实mysql只是把此行记录标记为了“可复用”，但磁盘大小是不会变的，所以通过delete表中记录是达不到回收表空间的。这些被标记为“可复用”而没有使用的空间看起来就像是“空洞”，其实不止删除会造成空洞，插入一样可以，如果我们不是按顺序插入，而是随机插入，那么就可能造成页分裂，而之前那一页末尾可能还有未使用的空间。

13.2 怎么回收表空间

我们可以通过重建表来达到回收表空间，可以使用下面这个命令：

alter table 表名 engine = innodb

三种重建方式对比：

recreate重建表

alter table 表名 engine = innodb

重新统计索引信息

analyze table 表名

recreate 重新统计索引信息

optimize table 表名

14. count(*)这么慢，我该怎么办？

以下的讨论都是没有where条件判断的，如果有条件判断，则不适用。

对于myisam引擎，它会将一个表的总行数存储在磁盘中，所以它的count(*)效率很高
而对于innoid引擎，由于mvcc多版本并发控制，它必须一行一行的去读取然后计算总数。

执行速度比较

count(其他字段) < count(主键) < count(1) ≈ count(*)

ag真人游戏的解决方案

将总数存储在redis中（不推荐，可能会导致数据不一致）
单独存储到mysql一张表中（可使用事务，来避免数据不一致等情况）

15. 日志和索引相关问题

15.1 mysql 怎么知道 binlog 是完整的?

答: 一个事务的 binlog 是有完整格式的

statement 格式的 binlog，最后会有 commit；
row 格式的 binlog，最后会有一个 xid event

查看binlog 事件列表

可通过该命令拿到binlog 文件名

show binlog events
## 查看某个文件的binlog event
show binlog events in 'mysql-bin.001'

查看binlog内容

由于binlog是二进制文件，所以只能用官方提供的工具进行查看

mysqlbinlog -vv mysql-bin.001

是否启用binlog日志

show variables like 'log_bin'

15.2 redo log 和 binlog 是怎么关联起来的?

答：它们有一个共同的数据字段，叫 xid。崩溃恢复的时候，会按顺序扫描 redo log

如果碰到既有 prepare、又有 commit 的 redo log，就直接提交；
如果碰到只有 parepare、而没有 commit 的 redo log，就拿着 xid 去 binlog 找对应的事务。

15.3 处于 prepare 阶段的 redo log 加上完整 binlog，重启就能恢复，mysql 为什么要这么设计?

答：其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。在时刻 b，也就是 binlog 写完以后 mysql 发生崩溃，这时候 binlog 已经写入了，之后就会被从库（或者用这个 binlog 恢复出来的库）使用。所以，在主库上也要提交这个事务。采用这个策略，主库和备库的数据就保证了一致性。

15.4 能不能只用redo log，不要binlog？

回答：如果只从崩溃恢复的角度来讲是可以的。你可以把binlog关掉，这样就没有两阶段提交了，但系统依然是crash-safe的。
但是，如果你了解一下业界各个公司的使用场景的话，就会发现在正式的生产库上，binlog都是开着的。因为binlog有着redo log无法替代的功能。

一个是归档。

redo log是循环写，写到末尾是要回到开头继续写的。这样历史日志没法保留，redo log也就起不到归档的作用。

一个就是mysql系统依赖于binlog。

binlog作为mysql一开始就有的功能，被用在了很多地方。其中，mysql系统高可用的基础，就是binlog复制。

还有很多公司有异构系统（比如一些数据分析系统）

这些系统就靠消费mysql的binlog来更新自己的数据。关掉binlog的话，这些下游系统就没法输入了。

总之，由于现在包括mysql 高可用在内的很多系统机制都依赖于binlog，所以“鸠占鹊巢”redo log还做不到。