菜鸟笔记
提升您的技术认知

MySQL实战45讲学习笔记

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

1. Mysql结构

数据库总共分为两大部分:Server层和存储层,其中Server层又分为:连接器、(查询缓存)、分析器、优化器和执行器。存储层是以插件的形式,常见的有InnoDB和MyISAM。

2. 查询语句(select * from T where id = 2)

  1. 通过连接器获取连接
  2. 查看语句是存在于查询缓存中,如果存在则直接返回,不存在则进行 步骤3,Mysql 8.0版本之后已经去掉了查询缓存功能
  3. 通过分析器判断SQL语句是否正确(要做什么)
  4. 通过优化器判断使用哪个索引去查询(怎么做)
  5. 执行器判断是否拥有该表权限,如果有则调用存储引擎的拿第一条数据的接口,判断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)

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

  1. 查询⾸先从 root->branch->leaf block->table records
  2. 在leaf block中存放了我们的数据⾏,并且按照主键pk的顺序进⾏排序;
  3. Root和branch的通常被cache;
  4. 叶⼦节点的数据通常需要从磁盘中访问,所以整个过程需要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元)需要下面三个过程:

  1. 从用户A账户中扣掉 3 元
  2. 在电影院B的余额中增加 3 元
  3. 记录一条交易日志

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

7.1 死锁和死锁检测

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

死锁解决

  1. 超时释放
    设置超时时间,通过参数innodb_lock_wait_timeout设置,当超过这个时间之后将自动释放锁资源。默认是50s
  2. 死锁检测
    设置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.如何给大表加索引

  1. mysql 版本 >=5.6 ,直接使用无锁加索引
ALTER TABLE table_name  ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
  1. 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在刷"脏页"的过程。

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

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

  1. redo log 写满了。当redo log写满了之后,系统会停止所有更新操作,将redo log的checkpoint往前推进,让redo log可以留出空间继续写。
  2. 系统内存不足,淘汰脏页时。当需要新的内存页,而内存不足时就需要淘汰一些数据页空出内存给别的数据页使用,如果淘汰的是“脏页”,那么就需要把“脏页”数据写入到磁盘中。
  3. 当系统空闲时。当系统空闲时就会将一点一点的将脏页数据刷新到磁盘中
  4. 当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

三种重建方式对比:

  1. recreate重建表
alter table 表名 engine = InnoDB
  1. 重新统计索引信息
analyze table 表名
  1. recreate + 重新统计索引信息
optimize table 表名

14. count(*)这么慢,我该怎么办?

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

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

执行速度比较

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

解决方案

  1. 将总数存储在Redis中(不推荐,可能会导致数据不一致)
  2. 单独存储到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还做不到。