MySql必知必会

41.undo log、redo log、 bin log的作用是什么？

undo log 基本概念

• undo log是一种用于撤销回退的日志，在数据库事务开始之前，MySQL会先记录更新前的数据到 undo log日志文件里面，当事务回滚时或者数据库崩溃时，可以利用 undo log来进行回退。
• Undo Log产生和销毁：Undo Log在事务开始前产生；事务在提交时，并不会立刻删除undo log，innodb会将该事务对应的undo log放入到删除列表中，后面会通过后台线程purge thread进行回收处理。

注意: undo log也会产生redo log，因为undo log也要实现持久性保护。

undo log的作用

1. 提供回滚操作【undo log实现事务的原子性】
   在数据修改的时候，不仅记录了redo log，还记录了相对应的undo log，如果因为某些原因导致事务执行失败了，可以借助undo log进行回滚。
   undo log 和 redo log 记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。
2. 提供多版本控制(MVCC)【undo log实现多版本并发控制（MVCC）】
   MVCC，即多版本控制。在MySQL数据库InnoDB存储引擎中，用undo Log来实现多版本并发控制(MVCC)。当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据版本是怎样的，从而让用户能够读取到当前事务操作之前的数据【快照读】。

redo log 基本概念

• InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。
• redo log：被称作重做日志, 包括两部分：一个是内存中的日志缓冲： redo log buffer，另一个是磁盘上的日志文件： redo log file 。

redo log的作用

• mysql 每执行一条 DML 语句，先将记录写入 redo log buffer 。后续某个时间点再一次性将多个操作记录写到 redo log file 。当故障发生致使内存数据丢失后，InnoDB会在重启时，经过重放 redo，将Page恢复到崩溃之前的状态 通过Redo log可以实现事务的持久性 。

bin log基本概念

• binlog是一个二进制格式的文件，用于记录用户对数据库更新的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是不会记录SELECT和SHOW这类操作。
• binlog在MySQL的Server层实现(引擎共用)
• binlog为逻辑日志,记录的是一条SQL语句的原始逻辑
  • binlog不限制大小,追加写入,不会覆盖以前的日志.
  • 默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。

bin log的作用

1. 主从复制：在主库中开启Binlog功能，这样主库就可以把Binlog传递给从库，从库拿到Binlog后实现数据恢复达到主从数据一致性。
2. 数据恢复：通过mysqlbinlog工具来恢复数据。

42.redo log与undo log的持久化策略？

redo log持久化

缓冲区数据一般情况下是无法直接写入磁盘的，中间必须经过操作系统缓冲区( OS Buffer )。因此， redo log buffer 写入 redo logfile 实际上是先写入 OS Buffer，然后再通过系统调用 fsync() 将其刷到 redo log file.

Redo Buffer 持久化到 redo log 的策略，可通过 Innodb_flush_log_at_trx_commit 设置：

参数值	含义
0 (延迟写)	事务提交时不会将 redo log buffer中日志写入到 os buffer， 而是每秒写入 os buffer并调用 fsync()写入到 redo log file中。 也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
1 (实时写,实时刷)	事务每次提交都会将 redo log buffer中的日志写入 os buffer并 调用 fsync()刷到 redo log file中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
2 (实时写, 延时刷)	每次提交都仅写入到 os buffer，然后是每秒调用 fsync()将 os buffer中的日志写入到 redo log file。

一般建议选择取值2，因为 MySQL 挂了数据没有损失，整个服务器挂了才会损失1秒的事务提交数据

undo log持久化

MySQL中的Undo Log严格的讲不是Log，而是数据，因此他的管理和落盘都跟数据是一样的：

• Undo的磁盘结构并不是顺序的，而是像数据一样按Page管理
• Undo写入时，也像数据一样产生对应的Redo Log (因为undo也是对页面的修改，记录undo这个操作本身也会有对应的redo)。
• Undo的Page也像数据一样缓存在Buffer Pool中，跟数据Page一起做LRU换入换出，以及刷脏。Undo Page的刷脏也像数据一样要等到对应的Redo Log 落盘之后

当事务提交的时候，innodb不会立即删除undo log，因为后续还可能会用到undo log，如隔离级别为repeatable read时，事务读取的都是开启事务时的最新提交行版本，只要该事务不结束，该行版本就不能删除，即undo log不能删除。

但是在事务提交的时候，会将该事务对应的undo log放入到删除列表中，未来通过purge来删除。并且提交事务时，还会判断undo log分配的页是否可以重用，如果可以重用，则会分配给后面来的事务，避免为每个独立的事务分配独立的undo log页而浪费存储空间和性能。

43.bin log与undo log的区别？

1）redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。

2）redo log是物理日志，记录的是“在XXX数据页上做了XXX修改”；binlog是逻辑日志，记录的是原始逻辑，其记录是对应的SQL语句。

• 物理日志: 记录的是每一个page页中具体存储的值是多少，在这个数据页上做了什么修改. 比如: 某个事物将系统表空间中的第100个页面中偏移量为1000处的那个字节的值1改为2.
• 逻辑日志: 记录的是每一个page页面中具体数据是怎么变动的，它会记录一个变动的过程或SQL语句的逻辑, 比如: 把一个page页中的一个数据从1改为2，再从2改为3,逻辑日志就会记录1->2,2->3这个数据变化的过程.

3）redo log是循环写的，空间一定会用完，需要write pos和check point搭配；binlog是追加写，写到一定大小会切换到下一个，并不会覆盖以前的日志

• Redo Log 文件内容是以顺序循环的方式写入文件，写满时则回溯到第一个文件，进行覆盖写。

• write pos: 表示日志当前记录的位置，当ib_logfile_4写满后，会从ib_logfile_1从头开始记录；
• check point: 表示将日志记录的修改写进磁盘，完成数据落盘，数据落盘后checkpoint会将日志上的相关记录擦除掉，即 write pos -> checkpoint 之间的部分是redo log空着的部分，用于记录新的记录，checkpoint -> write pos 之间是redo log 待落盘的数据修改记录
• 如果 write pos 追上 checkpoint，表示写满，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

3）Redo Log作为服务器异常宕机后事务数据自动恢复使用，Binlog可以作为主从复制和数据恢复使用。Binlog没有自动crash-safe能力

CrashSafe指MySQL服务器宕机重启后，能够保证：

• 所有已经提交的事务的数据仍然存在。
• 所有没有提交的事务的数据自动回滚。

44.MySQL的binlog有几种日志格式？分别有什么区别？

binlog日志有三种模式

1）ROW（row-based replication, RBR）：日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。

• 优点：能清楚记录每一个行数据的修改细节，能完全实现主从数据同步和数据的恢复。而且不会出现某些特定情况下存储过程或function无法被正确复制的问题。
• 缺点：批量操作，会产生大量的日志，尤其是alter table会让日志量暴涨。

2）STATMENT（statement-based replication, SBR）：记录每一条修改数据的SQL语句（批量修改时，记录的不是单条SQL语句，而是批量修改的SQL语句事件）, slave在复制的时候SQL进程会解析成和原来master端执行过的相同的SQL再次执行。简称SQL语句复制。

• 优点：日志量小，减少磁盘IO，提升存储和恢复速度
• 缺点：在某些情况下会导致主从数据不一致，比如last_insert_id()、now()等函数。

3）MIXED（mixed-based replication, MBR）：以上两种模式的混合使用，一般会使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择写入模式。

企业场景如何选择binlog的模式

1. 如果生产中使用MySQL的特殊功能相对少（存储过程、触发器、函数）。选择默认的语句模式，Statement。
2. 如果生产中使用MySQL的特殊功能较多的，可以选择Mixed模式。
3. 如果生产中使用MySQL的特殊功能较多，又希望数据最大化一致，此时最好Row 模式；但是要注意，该模式的binlog日志量增长非常快.

45.mysql 线上修改大表结构有哪些风险？

在线修改大表的可能影响

• 在线修改大表的表结构执行时间往往不可预估，一般时间较长。
• 由于修改表结构是表级锁，因此在修改表结构时，影响表写入操作。
• 如果长时间的修改表结构，中途修改失败，由于修改表结构是一个事务，因此失败后会还原表结构，在这个过程中表都是锁着不可写入。
• 修改大表结构容易导致数据库CPU、IO等性能消耗，使MySQL服务器性能降低。
• 在线修改大表结构容易导致主从延时，从而影响业务读取。

修改方式：

1. 对表加锁(表此时只读)
2. 复制原表物理结构
3. 修改表的物理结构
4. 把原表数据导入中间表中 ，数据同步完后，**锁定中间表，并删除原表
5. rename中间表为原表
6. 刷新数据字典，并释放锁

使用工具： online-schema-change ，是percona推出的一个针对mysql在线ddl的工具。percona是一个mysql分支维护公司，专门提供mysql技术服务的。

46.count(列名)、count(1)和 count(*)有什么区别?

进行统计操作时,count中的统计条件可以三种选择:

EXPLAIN ?SELECT COUNT(*) FROM user;

EXPLAIN ?SELECT COUNT(列名) FROM user;

EXPLAIN ?SELECT COUNT(1) FROM user;

执行效果上：

• count(*) 包括了所有的列,在统计时 不会忽略列值为null的数据。
• count(1) 用1表示代码行,在统计时,不会忽略列值为null的数据。
• count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计。

执行效率上：

• InnoDB引擎：count（字段) < count(1) = count(*)
  • InnoDB通过遍历最小的可用二级索引来处理select count(*) 语句，除非索引或优化器提示指示优化器使用不同的索引。如果二级索引不存在，则通过扫描聚集索引来处理。
  • InnoDB已同样的方式处理count(1)和count(*)
• MyISAM引擎：count（字段) < count(1) <= count(*)
  • MyISAM存储了数据的准确行数，使用 count(*)会直接读取该行数， 只有当第一列定义为NOT NULL时，count（1），才会执行该操作，所以优先选择 count(*)
• count(列名) 会遍历整个表，但不同的是，它会先获取列，然后判断是否为空，然后累加，因此count(列名)性能不如前两者。

注意：count(*)，这是SQL92 定义的标准统计行数的语法，跟数据库无关，与NULL也无关。而count(列名) 是统计列值数量，不计NULL，相同列值算一个。

47.什么是分库分表？什么时候进行分库分表？

什么是分库分表

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果。

• 分库分表解决的问题

  分库分表的目的是为了解决由于数据量过大而导致数据库性能降低的问题，将原来单体服务的数据库进行拆分.将数据大表拆分成若干数据表组成，使得单一数据库、单一数据表的数据量变小，从而达到提升数据库性能的目的。

• 什么情况下需要分库分表

  • 单机存储容量遇到瓶颈.
  • 连接数,处理能力达到上限.

注意:

分库分表之前,要根据项目的实际情况 确定我们的数据量是不是够大,并发量是不是够大,来决定是否分库分表.

数据量不够就不要分表,单表数据量超过1000万或100G的时候, 速度就会变慢(官方测试),

分库分表包括： 垂直分库、垂直分表、水平分库、水平分表 四种方式。

垂直分库

• 数据库中不同的表对应着不同的业务，垂直切分是指按照业务的不同将表进行分类,分布到不同的数据库上面

  • 将数据库部署在不同服务器上，从而达到多个服务器共同分摊压力的效果

  

垂直分表

表中字段太多且包含大字段的时候，在查询时对数据库的IO、内存会受到影响，同时更新数据时，产生的binlog文件会很大，MySQL在主从同步时也会有延迟的风险

• 将一个表按照字段分成多表，每个表存储其中一部分字段。
• 对职位表进行垂直拆分, 将职位基本信息放在一张表, 将职位描述信息存放在另一张表

• 垂直拆分带来的一些提升
  • 解决业务层面的耦合，业务清晰
  • 能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下，垂直分库一定程度的提高访问性能
• 垂直拆分没有彻底解决单表数据量过大的问题

水平分库

• 将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈.

• 简单讲就是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面, 例如将订单表 按照id是奇数还是偶数, 分别存储在不同的库中。

  

水平分表

• 针对数据量巨大的单张表（比如订单表），按照规则把一张表的数据切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。

  

• 总结

  • 垂直分表: 将一个表按照字段分成多表，每个表存储其中一部分字段。
  • 垂直分库: 根据表的业务不同,分别存放在不同的库中,这些库分别部署在不同的服务器.
  • 水平分库: 把一张表的数据按照一定规则,分配到不同的数据库,每一个库只有这张表的部分数据.
  • 水平分表: 把一张表的数据按照一定规则,分配到同一个数据库的多张表中,每个表只有这个表的部分数据.

48.说说 MySQL 的主从复制？

主从复制的用途

• 实时灾备，用于故障切换
• 读写分离，提供查询服务
• 备份，避免影响业务

主从部署必要条件

• 主库开启binlog日志（设置log-bin参数）
• 主从server-id不同
• 从库服务器能连通主库

主从复制的原理

• Mysql 中有一种日志叫做 bin 日志（二进制日志）。这个日志会记录下所有修改了数据库的SQL 语句（insert,update,delete,create/alter/drop table, grant 等等）。
• 主从复制的原理其实就是把主服务器上的 bin 日志复制到从服务器上执行一遍，这样从服务器上的数据就和主服务器上的数据相同了。

1. 主库db的更新事件(update、insert、delete)被写到binlog
2. 主库创建一个binlog dump thread，把binlog的内容发送到从库
3. 从库启动并发起连接，连接到主库
4. 从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
5. 从库启动之后，创建一个SQL线程，从relay log里面读取内容，执行读取到的更新事件，将更新内容写入到slave的db

49. 说一下 MySQL 执行一条查询语句的内部执行过程？

• ①建立连接（Connectors&Connection Pool），通过客户端/服务器通信协议与MySQL建立连接。MySQL 客户端与服务端的通信方式是 “ 半双工 ”。对于每一个 MySQL 的连接，时刻都有一个线程状态来标识这个连接正在做什么。
  通讯机制：

  • 全双工：能同时发送和接收数据，例如平时打电话。
  • 半双工：指的某一时刻，要么发送数据，要么接收数据，不能同时。例如早期对讲机
  • 单工：只能发送数据或只能接收数据。例如单行道

  线程状态：
  show processlist; //查看用户正在运行的线程信息，root用户能查看所有线程，其他用户只能看自己的

  • id：线程ID，可以使用kill xx；
  • user：启动这个线程的用户
  • Host：发送请求的客户端的IP和端口号
  • db：当前命令在哪个库执行
  • Command：该线程正在执行的操作命令
    • Create DB：正在创建库操作
    • Drop DB：正在删除库操作
    • Execute：正在执行一个PreparedStatement
    • Close Stmt：正在关闭一个PreparedStatement
    • Query：正在执行一个语句
    • Sleep：正在等待客户端发送语句
    • Quit：正在退出
    • Shutdown：正在关闭服务器
  • Time：表示该线程处于当前状态的时间，单位是秒
  • State：线程状态
    • Updating：正在搜索匹配记录，进行修改
    • Sleeping：正在等待客户端发送新请求
    • Starting：正在执行请求处理
    • Checking table：正在检查数据表
    • Closing table : 正在将表中数据刷新到磁盘中
    • Locked：被其他查询锁住了记录
    • Sending Data：正在处理Select查询，同时将结果发送给客户端
  • Info：一般记录线程执行的语句，默认显示前100个字符。想查看完整的使用show full processlist;

• ②查询缓存（Cache&Buffer），这是MySQL的一个可优化查询的地方，如果开启了查询缓存且在查询缓存过程中查询到完全相同的SQL语句，则将查询结果直接返回给客户端；如果没有开启查询缓存或者没有查询到完全相同的 SQL 语句则会由解析器进行语法语义解析，并生成“解析树”。

  • 缓存Select查询的结果和SQL语句
  • 执行Select查询时，先查询缓存，判断是否存在可用的记录集，要求是否完全相同（包括参数值），这样才会匹配缓存数据命中。
  • 即使开启查询缓存，以下SQL也不能缓存
    • 查询语句使用SQL_NO_CACHE
    • 查询的结果大于query_cache_limit设置
    • 查询中有一些不确定的参数，比如now()
  • show variables like ‘%query_cache%’; //查看查询缓存是否启用，空间大小，限制等
  • show status like ‘Qcache%’; //查看更详细的缓存参数，可用缓存空间，缓存块，缓存多少等

• ③解析器（Parser）将客户端发送的SQL进行语法解析，生成"解析树"。预处理器根据一些MySQL规则进一步检查“解析树”是否合法，例如这里将检查数据表和数据列是否存在，还会解析名字和别名，看看它们是否有歧义，最后生成新的“解析树”。

• ④查询优化器（Optimizer）根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最优的执行计划，可以分为两类：静态优化（编译时优化）、动态优化（运行时优化）。

  • 等价变换策略
    • 5=5 and a>5 改成 a > 5
    • a < b and a=5 改成b>5 and a=5
    • 基于联合索引，调整条件位置等
  • 优化count、min、max等函数
    • InnoDB引擎min函数只需要找索引最左边
    • InnoDB引擎max函数只需要找索引最右边
    • MyISAM引擎count(*)，不需要计算，直接返回
  • 提前终止查询
    • 使用了limit查询，获取limit所需的数据，就不在继续遍历后面数据
  • in的优化
    • MySQL对in查询，会先进行排序，再采用二分法查找数据。比如where id in (2,1,3)，变成 in (1,2,3)

• ⑤查询执行引擎负责执行 SQL 语句，此时查询执行引擎会根据 SQL 语句中表的存储引擎类型，以及对应的API接口与底层存储引擎缓存或者物理文件的交互，得到查询结果并返回给客户端。若开启用查询缓存，这时会将SQL 语句和结果完整地保存到查询缓存（Cache&Buffer）中，以后若有相同的 SQL 语句执行则直接返回结果。

  • 如果开启了查询缓存，先将查询结果做缓存操作
  • 返回结果过多，采用增量模式返回

50.Mysql内部支持缓存查询吗？

使用缓存的好处：当MySQL接收到客户端的查询SQL之后，仅仅只需要对其进行相应的权限验证之后，就会通过Query Cache来查找结果，甚至都不需要经过Optimizer模块进行执行计划的分析优化，更不需要发生任何存储引擎的交互.

mysql5.7支持内部缓存，8.0之后已废弃

mysql缓存的限制

1. mysql基本没有手段灵活的管理缓存失效和生效，尤其对于频繁更新的表
2. SQL必须完全一致才会导致cache命中
3. 为了节省内存空间，太大的result set不会被cache (< query_cache_limit)；
4. MySQL缓存在分库分表环境下是不起作用的；
5. 执行SQL里有触发器,自定义函数时，MySQL缓存也是不起作用的；
6. 在表的结构或数据发生改变时，基于该表相关cache立即全部失效。

替代方案

• 应用层组织缓存，最简单的是使用redis，ehcached等