MySQL

数据库架构

说说MySQL 的基础架构图，给面试官讲一下 MySQL 的逻辑架构，有白板可以把下面的图画一下

Mysql逻辑架构图主要分三层：

（1）第一层负责连接处理，授权认证，安全等等

（2）第二层负责编译并优化SQL

（3）第三层是存储引擎。

一条SQL查询语句在MySQL中如何执行的？

• 先检查该语句是否有权限，校验账户密码，权限等操作，如果用户账户密码已通过，连接器会到权限表中查询该用户的所有权限，之后在这个连接里的权限逻辑判断都是会依赖此时读取到的权限数据。如果没有权限，直接返回错误信息，如果有权限会先查询缓存(MySQL8.0 版本以前)。

• 如果没有缓存，分析器进行词法分析，提取 sql 语句中 select 等关键元素，然后判断 sql 语句是否有语法错误，比如关键词是否正确等等。Parser：解析器，负责对SQL的解析，解析器主要是用来分析 SQL 语句是来干嘛的，分析分为以下几步：mysql通过关键字将SQL语句进行解析，并生成一颗解析树。mysql解析器将使用mysql语法规则验证和解析查询，预处理器会进一步检查解析树是否合法，例如表名和列名是否存在，是否有歧义，还会验证权限等

• 最后优化器确定执行方案进行索引选择，权限校验，如果没有权限就直接返回错误信息，如果有权限就会调用数据库引擎接口，返回执行结果。Optimizer：优化器，要知道你的SQL语句，Mysql不一定会按你的理解来进行查询，而会通过优化器进行一些指令重排，比如在存在组合索引的情况下，where子句的顺序又不按照组合索引的顺序来，这个时候优化器会自动帮你进行符合索引的排列。

  总体来说，优化器优化策略分为两种：静态优化：直接对解析树分析并优化。动态优化：运行中根据上下文动态优化，每次执行时都要重新评估

  优化场景：

  调整关联表的顺序
  等价变换
  count(),min(),max() 例如，要找到某一列的最小值，只需要查询索引的最左端的记录即可，不需要全文扫描比较
  索引覆盖
  子查询变缓存，将经常查询的数据放入到缓存中

• 权限校验—(查询缓存)—分析器—优化器—权限校验—执行器—引擎

sql语句执行

我们平常构建一条SQL语句，一般通过以下思路构思

select (distinct)
	查询字段/'*'
from
	<left_table   <join_type join   <right_table   on  <join_condition
where
 	<where_condition
group by
	<group by_list
having
	<having_conditon
order by
	<order by_condition
limit
	<limit_number

那么，优化器是如何确定SQL的执行计划的？一般来说，优化器会这么去理解

from <left_table  确定表
on <join_conditon 连接条件
<join_type join <right_table 确定左右外连接类型及连接的表
where <where condition where之后的条件
group by <group by_list 按什么字段分组
having <having_conditon 分组条件
select 确定查询操作
distinct 是否去重
order by<order by_condition
limit <limit_number

这只是优化器觉得是最优的执行顺序，根据优化器的版本不同，顺序也会发生改变

SQL 优化

日常工作中你是怎么优化SQL的？

1，优化表结构

（1）尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

（2）尽可能的使用 varchar 代替 char

变长字段存储空间小，可以节省存储空间。

（3）当索引列大量重复数据时，可以把索引删除掉

比如有一列是性别，几乎只有男、女、未知，这样的索引是无效的。

2，优化查询

• 应尽量避免在 where 子句中使用!=或<操作符
• 应尽量避免在 where 子句中使用 or 来连接条件
• 任何查询也不要出现select *
• 避免在 where 子句中对字段进行 null 值判断

3，索引优化

• 对作为查询条件和 order by的字段建立索引
• 避免建立过多的索引，多使用组合索引

性能优化一般可以分为

• 主动优化
• 被动优化

所谓的主动优化是指不需要外力的推动而自发进行的一种行为，比如当服务没有明显的卡顿、宕机或者硬件指标异常的情况下，自我出发去优化的行为，就可以称之为主动优化。而被动优化刚好与主动优化相反，它是指在发现了服务器卡顿、服务异常或者物理指标异常的情况下，才去优化的这种行为。

性能优化原则

无论是主动优化还是被动优化都要符合以下性能优化的原则：

1. 优化不能改变服务运行的逻辑，要保证服务的正确性；
2. 优化的过程和结果都要保证服务的安全性；
3. 要保证服务的稳定性，不能为了追求性能牺牲程序的稳定性。比如不能为了提高 Redis 的运行速度，而关闭持久化的功能，因为这样在 Redis 服务器重启或者掉电之后会丢失存储的数据。

性能优化手段应该是：预防性能问题为主+被动优化为辅。也就是说，我们应该以预防性能问题为主，在开发阶段尽可能的规避性能问题，而在正常情况下，应尽量避免主动优化，以防止未知的风险，尤其对生产环境而言更是如此，最后才是考虑被动优化。

MySQL 被动性能优化

1. 单条 SQL 运行慢；
2. 部分 SQL 运行慢；
3. 整个 SQL 运行慢。

单条 SQL 运行慢

造成单条 SQL 运行比较慢的常见原因有以下两个：

1. 未正常创建或使用索引；
2. 表中数据量太大。

解决方案 1：创建并正确使用索引

索引是一种能帮助 MySQL 提高查询效率的主要手段，因此一般情况下我们遇到的单条 SQL 性能问题，通常都是由于未创建或为正确使用索引而导致的，所以在遇到单条 SQL 运行比较慢的情况下，首先要做的就是检查此表的索引是否正常创建。

如果表的索引已经创建了，接下来就要检查一下此 SQL 语句是否正常触发了索引查询，如果发生以下情况那么 MySQL 将不能正常的使用索引：

1. 在 where 子句中使用 != 或者 < 操作符，查询引用会放弃索引而进行全表扫描；
2. 不能使用前导模糊查询，也就是 ‘%XX’ 或 ‘%XX%’，由于前导模糊不能利用索引的顺序，必须一个个去找，看是否满足条件，这样会导致全索引扫描或者全表扫描；
3. 如果条件中有 or 即使其中有条件带索引也不会正常使用索引，要想使用 or 又想让索引生效，只能将 or 条件中的每个列都加上索引才能正常使用；
4. 在 where 子句中对字段进行表达式操作。

因此要尽量避免以上情况，除了正常使用索引之外，我们也可以使用以下技巧来优化索引的查询速度：

1. 尽量使用主键查询，而非其他索引，因为主键查询不会触发回表查询；
2. 查询语句尽可能简单，大语句拆小语句，减少锁时间；
3. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型；
4. 用 exists 替代 in 查询；
5. 避免在索引列上使用 is null 和 is not null。

回表查询：普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。

解决方案 2：数据拆分

当表中数据量太大时 SQL 的查询会比较慢，可以考虑拆分表，让每张表的数据量变小，从而提高查询效率。

1.垂直拆分

指的是将表进行拆分，把一张列比较多的表拆分为多张表。比如，用户表中一些字段经常被访问，将这些字段放在一张表中，另外一些不常用的字段放在另一张表中，插入数据时，使用事务确保两张表的数据一致性。垂直拆分的原则：

• 把不常用的字段单独放在一张表；
• 把 text，blob 等大字段拆分出来放在附表中；
• 经常组合查询的列放在一张表中。

2.水平拆分

指的是将数据表行进行拆分，表的行数超过200万行时，就会变慢，这时可以把一张表的数据拆成多张表来存放。通常情况下，我们使用取模的方式来进行表的拆分，比如，一张有 400W 的用户表 users，为提高其查询效率我们把其通过用户 ID 取模的方法分成 4 张表 users1，users2，users3，users4，同时查询、更新、删除也是通过取模的方法来操作。

表的其他优化方案：

1. 使用可以存下数据最小的数据类型；
2. 使用简单的数据类型，int 要比 varchar 类型在 MySQL 处理简单；
3. 尽量使用 tinyint、smallint、mediumint 作为整数类型而非 int；
4. 尽可能使用 not null 定义字段，因为 null 占用 4 字节空间；
5. 尽量少用 text 类型，非用不可时最好考虑分表；
6. 尽量使用 timestamp，而非 datetime；
7. 单表不要有太多字段，建议在 20 个字段以内。

部分 SQL 运行慢

部分 SQL 运行比较慢，我们首先要做的就是先定位出这些 SQL，然后再看这些 SQL 是否正确创建并使用索引。也就是说，我们先要使用慢查询工具定位出具体的 SQL，然后再使用问题 1 的解决方案处理慢 SQL。

解决方案：慢查询分析

MySQL 中自带了慢查询日志的功能，开启它就可以用来记录在 MySQL 中响应时间超过阀值的语句，具体指运行时间超过 long_query_time 值的 SQL，则会被记录到慢查询日志中。long_query_time 的默认值为 10，意思是运行 10S 以上的语句。默认情况下，MySQL 数据库并不启动慢查询日志，需要我们手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会给 MySQL 服务器带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。使用 mysql show variables like '%slow_query_log%'; 来查询慢查询日志是否开启，slow_query_log 的值为 OFF 时，表示未开启慢查询日志。

开启慢查询日志，可以使用如下 MySQL 命令：set global slow_query_log=1，不过这种设置方式，只对当前数据库生效，如果 MySQL 重启也会失效，如果要永久生效，就必须修改 MySQL 的配置文件 my.cnf，配置如下：slow_query_log =1 slow_query_log_file=/tmp/mysql_slow.log

当你开启慢查询日志之后，所有的慢查询 SQL 都会被记录在 slow_query_log_file 参数配置的文件内，默认是 /tmp/mysql_slow.log 文件，此时我们就可以打开日志查询到所有慢 SQL 进行逐个优化。

整个 SQL 运行慢

当出现整个 SQL 都运行比较慢就说明目前数据库的承载能力已经到了峰值，因此我们需要使用一些数据库的扩展手段来缓解 MySQL 服务器了。

解决方案：读写分离

一般情况下对数据库而言都是“读多写少”，换言之，数据库的压力多数是因为大量的读取数据的操作造成的，我们可以采用数据库集群的方案，使用一个库作为主库，负责写入数据；其他库为从库，负责读取数据。这样可以缓解对数据库的访问压力。

怎么看执行计划（explain），如何理解其中各个字段的含义？

在 select 语句之前增加 explain 关键字，会返回执行计划的信息。

（1）id 列：是 select 语句的序号，MySQL将 select 查询分为简单查询和复杂查询。

（2）select_type列：表示对应行是是简单还是复杂的查询。

（3）table 列：表示 explain 的一行正在访问哪个表。

（4）type 列：最重要的列之一。表示关联类型或访问类型，即 MySQL 决定如何查找表中的行。从最优到最差分别为：system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL

（5）possible_keys 列：显示查询可能使用哪些索引来查找。

（6）key 列：这一列显示 mysql 实际采用哪个索引来优化对该表的访问。

（7）key_len 列：显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

（8）ref 列：这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名。

（9）rows 列：这一列是 mysql 估计要读取并检测的行数，注意这个不是结果集里的行数。

（10）Extra 列：显示额外信息。比如有 Using index、Using where、Using temporary等。

其中最重要的就是 type 字段，type 值类型如下：

• all — 扫描全表数据；
• index — 遍历索引；
• range — 索引范围查找；
• index_subquery — 在子查询中使用 ref；
• unique_subquery — 在子查询中使用 eq_ref；
• ref_or_null — 对 null 进行索引的优化的 ref；
• fulltext — 使用全文索引；
• ref — 使用非唯一索引查找数据；
• eq_ref — 在 join 查询中使用主键或唯一索引关联；
• const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq_ref 效率高一点。

关心过业务系统里面的sql耗时吗？统计过慢查询吗？对慢查询都怎么优化过？

我们平时写Sql时，都要养成用explain分析的习惯。慢查询的统计，运维会定期统计给我们

优化慢查询思路：

• 分析语句，是否加载了不必要的字段/数据

• 分析 SQL 执行语句，是否命中索引等

• 如果 SQL 很复杂，优化 SQL 结构

• 如果表数据量太大，考虑分表

索引

我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯⼀索引等，没有特别说明，默认都是使⽤B+树结构组织（多路搜索树，并不⼀定是⼆叉的）的索引。

哪些情况需要创建索引

\1. 主键⾃动建⽴唯⼀索引

\2. 频繁作为查询条件的字段应该创建索引

\3. 多表关联查询中，关联字段应该创建索引 on 两边都要创建索引

\4. 查询中排序的字段，应该创建索引

\5. 频繁查找字段 覆盖索引

\6. 查询中统计或者分组字段，应该创建索引 group by

哪些情况不需要创建索引

\1. 表记录太少

\2. 经常进⾏增删改操作的表

\3. 频繁更新的字段

\4. where条件⾥使⽤频率不⾼的字段

\5.区分度低的字段不适合加索引（如性别）

索引有哪些优缺点？

优势：

可以提⾼数据检索的效率，降低数据库的IO成本，类似于书的⽬录。

通过索引列对数据进⾏排序，降低数据排序的成本，降低了CPU的消耗:

被索引的列会⾃动进⾏排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂⼀些。

如果按照索引列的顺序进⾏排序，对应order by语句来说，效率就会提⾼很多。

where 索引列 在存储引擎层处理

覆盖索引，不需要回表查询

劣势：

索引会占据磁盘空间

索引虽然会提⾼查询效率，但是会降低更新表的效率。⽐如每次对表进⾏增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引⽂件。

创建索引和维护索引要耗费时间

索引的分类

单列索引

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插⼊重复值和空值， 纯粹为了查询数据更快⼀点。add index

唯⼀索引：索引列中的值必须是唯⼀的，但是允许为空值。 add unique index

主键索引：是⼀种特殊的唯⼀索引，不允许有空值。 pk

组合索引

在表中的多个字段组合上创建的索引 add index(col1,col2..)

组合索引的使⽤，需要遵循最左前缀原则。

⼀般情况下，建议使⽤组合索引代替单列索引（主键索引除外）。

全⽂索引

只有在MyISAM引擎、InnoDB（5.6以后）上才能使⽤，⽽且只能在CHAR,VARCHAR,TEXT类型字段上使⽤全⽂索引。 fulltext 优先级最⾼，先执⾏，不会执⾏其他索引 。

空间索引

⼀般使⽤不到。

索引存储结构

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使⽤不同的索引

MyISAM和InnoDB存储引擎：只⽀持B+ TREE索引， 也就是说默认使⽤B+ TREE，不能够更换

MEMORY/HEAP存储引擎：⽀持HASH和BTREE索引

B树和B+树

B树是为了磁盘或其它存储设备⽽设计的⼀种多叉（相对于⼆叉，B树每个内结点有多个分⽀，即多叉）平衡查找树。B树的⾼度⼀般都是在2-4，树的⾼度直接影响IO读写的次数。如果是三层树结构—⽀撑的数据可以达到20G，如果是四层树结构—⽀撑的数据可以达到⼏⼗T。

B树和B+树的区别

B树和B+树的最⼤区别在于⾮叶⼦节点是否存储数据的问题。

B树是⾮叶⼦节点和叶⼦节点都会存储数据。

B+树只有叶⼦节点才会存储数据，⽽且存储的数据都是在⼀⾏上，⽽且这些数据都是有指针指向的，也就是有顺序的。 索引列 order by

为什么要用 B+ 树，为什么不用普通二叉树？

（1）为什么不是普通二叉树？

如果二叉树特殊化为一个链表，相当于全表扫描。平衡二叉树相比于二叉查找树来说，查找效率更稳定，总体的查找速度也更快。

（2）为什么不是平衡二叉树呢？

我们知道，在内存比在磁盘的数据，查询效率快得多。如果树这种数据结构作为索引，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，但是平衡二叉树可是每个节点只存储一个键值和数据的，如果是B树，可以存储更多的节点数据，树的高度也会降低，因此读取磁盘的次数就降下来啦，查询效率就快啦。

（3）为什么不是 B 树而是 B+ 树呢？

B+ 树非叶子节点上是不存储数据的，仅存储键值，而B树节点中不仅存储键值，也会存储数据。innodb中页的默认大小是16KB，如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，如此一来我们查找数据进行磁盘的IO次数会再次减少，数据查询的效率也会更快。B+ 树索引的所有数据均存储在叶子节点，而且数据是按照顺序排列的，链表连着的。那么 B+ 树使得范围查找，排序查找，分组查找以及去重查找变得异常简单。

Hash 索引和 B+ 树索引区别是什么？你在设计索引是怎么抉择的？

• B+ 树可以进行范围查询，Hash 索引不能。
• B+ 树支持联合索引的最左侧原则，Hash 索引不支持。
• B+ 树支持 order by 排序，Hash 索引不支持。
• Hash 索引在等值查询上比 B+ 树效率更高。
• B+ 树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。

聚集索引与非聚集索引的区别

⾮聚集索引（MyISAM）

B+树叶⼦节点只会存储数据⾏（数据⽂件）的指针，简单来说数据和索引不在⼀起，就是⾮聚集索引。

⾮聚集索引包含主键索引和辅助索引，都会存储指针的值。

主键索引

索引⽂件仅仅保存数据记录的地址。 col1为主键

辅助索引（次要索引）

主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯⼀的, ⽽辅助索引的 key 可以重复。如果我们建⽴⼀个辅助索引col2,则此索引的结构同样也是⼀颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为⾸先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

聚集索引（InnoDB）

主键索引（聚集索引）的叶⼦节点会存储数据⾏，也就是说数据和索引在⼀起，这就是聚集索引。

辅助索引只会存储主键值。

如果没有没有主键，则使⽤唯⼀索引建⽴聚集索引；如果没有唯⼀索引，MySQL会按照⼀定规则创建聚集索引。

主键索引

1.InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会⾃动选择⼀个可以唯⼀标识数据记录的列作为主键,如果不存在这种列,则MySQL ⾃动为 InnoDB 表⽣成⼀个隐含字段作为主键,类型为⻓整形。

上图是 InnoDB 主索引(同时也是数据⽂件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据⽂件本身要按主键聚集

辅助索引（次要索引）

2.第⼆个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值⽽不是地址。换句话说,InnoDB 的所有辅助索引都引⽤主键作为 data 域。

聚集索引这种实现⽅式使得按主键的搜索⼗分⾼效,但是辅助索引搜索需要检索两遍索引:⾸先检索辅助，索引获得主键,然后⽤主键到主索引中检索获得记录。 select * from user where name='Alice' 回表查询 检索两次 ⾮主键索引 — pk—索引—数据 , select id,name from user where name='Alice' 不需要回表 在辅助索引树上就可以查询到了 。

为什么不建议使⽤过⻓的字段作为主键

因为所有辅助索引都引⽤主索引,过⻓的主索引会令辅助索引变得过⼤。

同时，请尽量在InnoDB上采⽤⾃增字段做表的主键。

聚集索引和非聚集索引的区别可以按以下四个维度回答：

（1）一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多个。

（2）聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。

（3）索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。

（4）聚集索引：物理存储按照索引排序；非聚集索引：物理存储不按照索引排序；

什么是最左前缀原则？什么是最左匹配原则？

最左前缀匹配原则：

在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。 可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。所以b=2这种查询条件没有办法利用索引。

where后面的查询条件，不论是使用（id，age，name）（name，id，age）还是（age，name，id）顺序，在查询时都使用到了联合索引，可能会疑惑，为什么底下两个的搜索条件明明没有按照联合索引从左到右进行匹配，却也使用到了联合索引？ 这是因为MySQL中有查询优化器explain，所以sql语句中字段的顺序不需要和联合索引定义的字段顺序相同，查询优化器会判断纠正这条SQL语句以什么样的顺序执行效率高，最后才能生成真正的执行计划，所以不论以何种顺序都可使用到联合索引。

最左前缀原则，就是最左优先，在创建多列索引时，要根据业务需求，where 子句中使用最频繁的一列放在最左边。

当我们创建一个组合索引的时候，如 (a1,a2,a3)，相当于创建了(a1) (a1,a2)和(a1,a2,a3)三个索引，这就是最左匹配原则。

如果id是字符型，那么前缀匹配用的是索引，中缀和后缀用的是全表扫描。

select * from staffs where id like 'A%'; // 前缀都是排好序的，使用的都是联合索引
select * from staffs where id like '%A%'; // 全表查询
select * from staffs where id like '%A'; // 全表查询

锁

MySQL 遇到过死锁问题吗，你是如何解决的？

遇到过。我排查死锁的一般步骤：

（1）查看死锁日志 show engine innodb status;

（2）找出死锁Sql

（3）分析sql加锁情况

（4）模拟死锁案发

（5）分析死锁日志

（6）分析死锁结果

数据库锁

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，为了保证数据的一致性，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要机制。

锁的分类

按锁粒度从大到小分类：表锁，页锁和行锁；以及特殊场景下使用的全局锁

如果按锁级别分类则有：共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁；

还有就是我们面向编程的两种锁思想：悲观锁、乐观锁。

对表锁、行锁的理解

表锁：表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，大大降低并发度。使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

行锁：与表锁正相反，行锁最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力从而提高系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。使用行级锁定的主要是InnoDB存储引擎。

页锁：除了表锁、行锁外，MySQL还有一种相对偏中性的页级锁，页锁是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。使用页级锁定的主要是BerkeleyDB存储引擎。

适用场景：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新数据的情况，同时又有并发查询的应用场景。

全局锁是什么时候用的

全局锁，是对整个数据库实例加锁。使用场景一般在全库逻辑备份时。MySQL提供加全局读锁的命令：Flush tables with read lock (FTWRL)。这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等修改数据库的操作都会被阻塞。

如果在主库备份，在备份期间不能更新，业务停摆。如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟同步。

还有一种锁全局的方式：set global readonly=true ，相当于将整个库设置成只读状态，但这种修改global配置量级较重，和全局锁不同的是：如果执行Flush tables with read lock 命令后，如果客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。但将库设置为readonly后，客户端发生异常断开，数据库依旧会保持readonly状态，会导致整个库长时间处于不可写状态。

MySQL基于锁级别

共享（读）锁、排他（写）锁、意向共享（读）锁、意向排他（写）锁

1 共享（读）锁（Share Lock）
共享锁，又叫读锁，是读取操作（SELECT）时创建的锁。其他用户可以并发读取数据，但在读锁未释放前，也就是查询事务结束前，任何事务都不能对数据进行修改（获取数据上的写锁），直到已释放所有读锁。如果事务A对数据B加上读锁后，则其他事务只能对数据B上加读锁，不能加写锁。获得读锁的事务只能读数据，不能修改数据。

SELECT … LOCK IN SHARE MODE; 在查询语句后面增加LOCK IN SHARE MODE，MySQL就会对查询结果中的每行都加读锁，当没有其他线程对查询结果集中的任何一行使用写锁时，可以成功申请读锁，否则会被阻塞。其他线程也可以读取使用了读锁的表，而且这些线程读取的是同一个版本的数据。

2 排他（写）锁（Exclusive Lock）
排他锁又称写锁、独占锁，如果事务A对数据B加上写锁后，则其他事务不能再对数据B加任何类型的锁。获得写锁的事务既能读数据，又能修改数据。

SELECT … FOR UPDATE; 在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加写锁，当没有其他线程对查询结果集中的任何一行使用锁时，可以成功申请写锁，否则会被阻塞。另外成功申请写锁后，也要先等待该事务前的读锁释放才能操作。

对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。

共享锁：SELECT … LOCK IN SHARE MODE; 排他锁：SELECT … FOR UPDATE;

3 意向锁（Intention Lock）

意向锁属于表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁：事务加锁前必须获得该表的意向锁。

意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；

意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是 InnoDB 自动加的，不需要用户干预。

乐观锁和悲观锁

面试官：好，那最后一个问题，你上面提到了乐观锁和悲观锁，谈谈你对它的看法吧。

悲观锁和乐观锁，不是 MySQL 或者数据库中独有的概念，而是并发编程的基本概念。主要区别在于，操作共享数据时，“悲观锁”即认为数据出现冲突的可能性更大，而“乐观锁”则是认为大部分情况不会出现冲突，进而决定是否采取排他性措施。

反映到 MySQL 数据库应用开发中，悲观锁一般就是利用类似 SELECT … FOR UPDATE 这样的语句，对数据加锁，避免其他事务意外修改数据。

乐观锁则是利用 CAS 机制，并不会对数据加锁，而是通过对比数据的时间戳或者版本号，来实现乐观锁需要的版本判断。 CAS有3个操作数，内存值V，旧的预期值A，要修改的新值B。当且仅当预期值A和内存值V相同时，将内存值V修改为B并返回true，否则返回false。

MySQL的多版本并发控制 （MVCC），其本质就可以看作是种乐观锁机制，而排他性的读写锁、两阶段锁等则是悲观锁的实现。

MVCC 熟悉吗，知道它的底层原理？

MVCC (Multiversion Concurrency Control)，即多版本并发控制技术。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。

事务

MySQL事务四大特性以及实现原理

简单说，事务就是一组原子性的SQL执行单元。如果数据库引擎能够成功地对数据库应用该组査询的全部语句，那么就执行该组SQL。如果其中有任何一条语句因为崩溃或其 他原因无法执行，那么所有的语句都不会执行。要么全部执行成功commit，要么全部执行失败rollback。

• 原子性Atomicity：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
• 一致性Consistency： 数据库总是从一个一致性的状态转换到另外一个一致性的状态。指在事务开始之前和事务结束以后，数据不会被破坏，假如A账户给B账户转10块钱，不管成功与否，A和B的总金额是不变的。
• 隔离性Isolation： 通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。 多个事务并发访问时，事务之间是相互隔离的，即一个事务不影响其它事务运行效果。
• 持久性Durability：表示事务完成以后，该事务对数据库所作的操作更改，将持久地保存在数据库之中。 此时即使系统崩溃，修改的数据也不会丢失。

ACID具体实现

对MySQL来说，逻辑备份日志(binlog)、重做日志(redolog)、回滚日志(undolog)、锁技术 + MVCC就是MySQL实现事务的基础。

原子性：通过undolog来实现。
持久性：通过binlog、redolog来实现。
隔离性：通过(读写锁+MVCC)来实现。
一致性：MySQL通过原子性，持久性，隔离性最终实现（或者说定义）数据一致性。

MySQL里比较重要的日志

重做日志（redo log）
回滚日志（undo log）
归档日志（binlog）
错误日志（errorlog）
慢查询日志（slow query log）
一般查询日志（general log）
中继日志（relay log）

对 redo log 日志的理解

吕秀才柜台下面有一个小黑板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在板上。但如果赊账的人多了，小黑板没地儿了，这时秀才一定还有一个专门记录赊账的账本。如果有人要赊账或者还账的话，秀才一般有两种做法：一种做法是直接把账本翻出来，把这次赊的账加上去或者扣除掉；另一种做法是先在小黑板上记下这次的账，等打烊以后再把账本翻出来核算。在生意火爆时，秀才肯定会选择后者，因为直接记账本太麻烦了。得先翻出赊账人“老钱”那条记录，账本密密麻麻几十页，找到后再拿出算盘计算，最后更新到账本上。想想都麻烦。

在 MySQL 里，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似秀才记小黑板的思路来提升更新效率。而小黑板和账本配合的过程，其实就是 MySQL 里经常说到的 WAL 技术。

WAL技术

WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘，也就是先写小黑板，等不忙的时候再写账本。具体来说，当有一条update语句要执行的时候，InnoDB 引擎就会先把记录写到 redo log（小黑板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后秀才做的事。但如果某天赊账的特别多，小黑板写满了，这个时候秀才只好抓紧把小黑板中的一部分赊账记录更新到账本中，然后把这些记录从小黑板上擦掉，为记新账腾出空间。

与此类似，InnoDB 的 redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 100MB，那么这块“小黑板”总共就可以记录 400MB 的操作记录。从头开始写，写到末尾就又回到开头循环写。有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。

write position 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
write position 和 checkpoint 之间的是“小黑板”上还空着的部分，可以用来记录新的操作。

如果 write pos 追上 checkpoint，表示“小黑板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。

crash-safe：

可以对照前面赊账记录的例子。只要赊账记录记在了小黑板上或写在了账本上，即使秀才突然被老邢抓走几天，回来后依然可以通过账本和小黑板上的数据明确赊账账目。就是维护数据的持久性。本质上说，crash-safe 就是落盘处理，将数据存储到了磁盘上，断电重启也不会丢失。

binlog日志

MySQL 其实是分为 server层和引擎层两部分。Server 层：主要做的是 MySQL 功能层面的事情；引擎层：负责存储相关的具体事宜。redo log 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为binlog（归档日志），其实就是用来恢复数据用的。

MySQL为啥要有redo log 和 binlog两个日志呢

因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只用于归档。InnoDB 是另一个公司以插件形式引入 MySQL 的。我们知道，只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

这两个日志主要有哪些区别？

redo log 是 InnoDB 引擎特有的；binlog是 MySQL 的 Server 层实现的，所有引擎共用。

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=1 这一行的 c 字段加 1 ”。

redo log 是循环写的，空间固定会用完然后复写；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

SQL在MySQL内部的执行流程

update T set money = money + 500 where username = '陈哈哈';

（开始，原始数据接入）执行器先找引擎取 username = ‘陈哈哈’ 这一行。如果 username = ‘陈哈哈’ 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
（数据修改）执行器拿到引擎给的行数据，把 money 这字段的值加上 500，比如原来是 N，现在就是 N+500，得到新的一行数据，再调用引擎接口写入这行新数据。
（数据提交）提交操作，由于存储引擎层与server层之间需要保证两个事务的一致性,这里主要保证redo log和binlog的原子性，所以提交分为prepare阶段与commit阶段，也就是我们说的两阶段提交。
（写redo log）引擎将这个更新操作记录到 redo log 里面（写到内存或直接落盘），到这里， redo log 处于 prepare 状态。
（写binlog）然后告知执行器执行完成了，随时可以提交事务。执行器生成这个操作的 binlog，并把 binlog 同步到磁盘。
（数据更新到磁盘或内存，结束）执行器调用引擎的提交事务接口执行修改操作，需要将在二级索引上做的修改写入到change buffer page，等到下次有其他sql需要读取该二级索引时，再去与二级索引做merge，引擎把刚刚写入的 redolog 标记上（commit）状态，实际上是加上了一个与binlog对应的XID，使两个日志逻辑保持一致，到此结束，更新流程闭环。

为啥必须要分成prepare和commit两个阶段进行提交呢？

如果prepare阶段，步骤3 交易被打断，回过头来处理此次交易，发现只有记了redo log 但没有binlog，则交易失败，回滚；如果（commit阶段或待commit阶段，步骤4||5）交易被打断，然后回过头发现系统上有记录（prepare）而且有（binlog），则说明本次交易有效，补充修改commit状态，更新到库存中。

这里我们用反证法来进行解释为何需要两阶段提交。由于 redo log 和 binlog 是两个独立的逻辑，如果不用两阶段提交，要么就是先写完 redo log 再写 binlog，或者采用反过来的顺序。我们看看这两种方式会有什么问题。仍然用前面的 update 语句来做例子。假设当前 username = ‘陈哈哈’ 的行，账户余额字段 money 的值是0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash（异常宕机），会出现什么情况呢？update T set money = money + 500 where username = '陈哈哈';

1、先写 redolog 后写 binlog

假设在 redolog 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 money 的值是 money + 500。
但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 money 的值就是 0，与原库的值不同。

2、先写 binlog 后写 redolog

如果在 binlog 写完之后 crash，由于 redolog 还没写，崩溃恢复以后这个事务无效，用户余额 money 的值应当是 0。但是 binlog 里面已经记录了“把 money 从 0 改成 500 这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 money 的值就是 500，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。简单说，redolog 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致。

日志落盘

保证事务成功，日志必须落盘，这样，数据库crash后，就不会丢失某个事务的数据了。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这样可以保证 MySQL 异常重启之后数据不丢失。sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这样可以保证 MySQL 异常重启之后 binlog 不丢失。

1、原子性原理

事务通常是以BEGIN TRANSACTION 开始，以 COMMIT 或 ROLLBACK 结束。

• COMMIT 表示提交，即提交事务的所有操作并持久化到数据库中。
• ROLLBACK表示回滚，即在事务中运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库所有已完成的操作全部撤销，回滚到事务开始时的状态，这里的操作指对数据库的更新操作，已执行的查询操作不用管。这时候也就需要用到 undolog 来进行回滚。

undolog：

每条数据变更（INSERT/UPDATE/DELETE/REPLACE）等操作都会生成一条undolog记录，在SQL执行前先于数据持久化到磁盘。当事务需要回滚时，MySQL会根据回滚日志对事务中已执行的SQL做逆向操作，比如 DELETE 掉一行数据的逆向操作就是再把这行数据 INSERT回去，其他操作同理。

2、持久性原理

先了解一下MySQL的数据存储机制，MySQL的表数据是存放在磁盘上的，因此想要存取的时候都要经历磁盘 IO,然而即使是使用 SSD 磁盘 IO 也是非常消耗性能的。为此，为了提升性能 InnoDB 提供了缓冲池(Buffer Pool)，Buffer Pool 中包含了磁盘数据页的映射，可以当做缓存来使用：

读数据：会首先从缓冲池中读取，如果缓冲池中没有，则从磁盘读取在放入缓冲池；
写数据：会首先写入缓冲池，缓冲池中的数据会定期同步到磁盘中；

我们知道，MySQL表数据是持久化到磁盘中的，但如果所有操作都去操作磁盘，等并发上来了，那处理速度谁都吃不消，因此引入了缓冲池(Buffer Pool)的概念，Buffer Pool 中包含了磁盘中部分数据页的映射，可以当做缓存来用；这样当修改表数据时，我们把操作记录先写到Buffer Pool中，并标记事务已完成，等MySQL空闲时，再把更新操作持久化到磁盘里，从而大大缓解了MySQL并发压力。你可能会问，到底什么时候执行持久化呢？

1、MySQL线程低于高水位；

2、当有其他查询、更新语句操作该数据页时。

但是它也带来了新的问题，当MySQL系统宕机，断电时Buffer Pool数据不就丢了？因为我们的数据已经提交了，但此时是在缓冲池里头，还没来得及在磁盘持久化，所以我们急需一种机制需要存一下已提交事务的数据，为恢复数据使用。于是 redo log + binlog的经典组合就登场了。

3、隔离性原理

并发场景下事务存在的数据问题

1. 脏读（针对的是未提交读数据）
   事务A修改了数据，但未提交，而事务B查询了事务A修改过却没有提交的数据，这就是脏读，因为事务A可能会回滚。

2. 不可重复读（针对其他提交前后，读取数据本身的对比）
   事务A 先 查询了工资金额，是3000块钱，未提交 。事务B在事务A查询完之后，修改了工资金额，变成了13000, 在事务A前提交了；如果此时事务A再查询一次数据，就会发现钱跟上一次查询不一致，是13000，而不是3000。这就是不可重复读。在一个事务范围内，两个相同的查询，读取同一条记录，却返回了不同的数据，这就是不可重复读。

3. 幻读（针对其他提交前后，读取数据条数的对比)
   幻读是指在同一个事务中，存在前后两次查询同一个范围的数据，但是第二次查询却看到了第一次查询没看到的行，一般情况下只新增。事务A先修改了某个表的所有纪录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；事务A随后查询记录，却发现有一条记录是未处理的，很是诧异，刚刚不是全部修改为已处理嘛，以为出现了幻觉，这就是幻读。事务A查询一个范围的结果集，另一个并发事务B往这个范围中插入/删除了数据，并静悄悄地提交，然后事务A再次查询相同的范围，两次读取得到的结果集不一样了，这就是幻读。

   脏读说的是事务知道了自己本不应该知道的东西，强调的动作是查询，我看到了自己不该看的东西 ;
   不可重复读强调的是一个人查的时候，其他人却可以增删改, 但我却不知道数据被改了，还拿去做了之前的用途；
   幻读强调的是我修改了数据，等我要查的时候，却发现有我没有修改的记录，为什么，因为有其他人插了一条新的。

为了解决上述问题，MySQL制定了四种不同的“隔离级别”，包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

事务的隔离级别有哪些？MySQL的默认隔离级别是什么？

• 读未提交（Read Uncommitted） 一个事务还没提交时，它做的变更就能被别的事务看到。（别的事务指同一时间进行的增删改查操作）
• 读已提交（Read Committed） 一个事务提交（commit）之后，它做的变更才会被其他事务看到。
• 可重复读（Repeatable Read） 一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
• 串行化（Serializable） 在MySQL中同一时刻只允许单个事务执行，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

Mysql默认的事务隔离级别是可重复读(Repeatable Read)

原理描述

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在MySQL默认的隔离级别“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。show variables like 'transaction_isolation';

原子性，隔离性，持久性的目的都是为了要做到一致性，但隔离性跟其他两个有所区别，原子性和持久性是为了要实现数据的正确、可用，比如要做到宕机后的恢复、事务的回滚等，保证数据是正确可用的！

隔离性要管理的是：多个并发读写请求（事务）过来时的执行顺序。当并发处理多个DML更新操作时，如何让事务操作他该看到的数据，出现多个事务处理同一条数据时，让事务该排队的排队，别插队捣乱，保证数据和事务的相对隔离，这就是隔离性要干的事儿。

4、一致性原理

一致性，我们要保障的是数据一致性，数据库中的增删改操作，使数据库不断从一个一致性的状态转移到另一个一致性的状态。

事务该回滚的回滚，该提交的提交，提交后该持久化磁盘的持久化磁盘，该写缓冲池的写缓冲池+写日志；对于数据可见性，通过四种隔离级别进行控制，使得库表中的有效数据范围可控，保证业务数据的正确性的前提下，进而提高并发程度，支撑服务高QPS的稳定运行，保证数据的一致性，这就是咱们叨叨叨说的清楚想不明白的数据库ACID四大特性。

实战

MySQL数据库cpu飙升的话，要怎么处理呢？

排查过程：

（1）使用top 命令观察，确定是mysqld导致还是其他原因。

（2）如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。

（3）找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：

（1）kill 掉这些线程(同时观察 cpu 使用率是否下降)

（2）进行相应的调整(比如说加索引、改 sql、改内存参数)

（3）重新跑这些 SQL。

其他情况：也有可能是每个 sql 消耗资源并不多，但是突然之间，有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

MYSQL的主从延迟，你怎么解决？

主从复制分了五个步骤进行：

• 步骤一：主库的更新事件(update、insert、delete)被写到binlog
• 步骤二：从库发起连接，连接到主库。
• 步骤三：此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
• 步骤四：从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
• 步骤五：从库还会创建一个SQL线程，从relay log里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db

主从同步延迟的原因

一个服务器开放Ｎ个链接给客户端来连接，这样又会有大并发的更新操作, 但是从服务器的里面读取binlog的线程仅有一个，当某个SQL在从服务器上执行的时间稍长，或者由于某个SQL要进行锁表就会导致主服务器的SQL大量积压，未被同步到从服务器里。这就导致了主从不一致， 也就是主从延迟。

主从同步延迟的解决办法

• 主服务器要负责更新操作，对安全性的要求比从服务器要高，所以有些设置参数可以修改，比如sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置等。
• 选择更好的硬件设备作为slave。
• 把一台从服务器作为备份使用， 而不提供查询， 那边他的负载下来了， 执行relay log 里面的SQL效率自然就高了。
• 增加从服务器，这个目的还是分散读的压力，从而降低服务器负载。

如果让你做分库与分表的设计，简单说说你会怎么做？

分库分表方案

• 水平分库：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
• 水平分表：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
• 垂直分库：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
• 垂直分表：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。

常用的分库分表中间件

• sharding-jdbc
• Mycat

分库分表可能遇到的问题

• 事务问题：需要用分布式事务
• 跨节点Join的问题：解决这一问题可以分两次查询实现
• 跨节点的count,order by,group by以及聚合函数问题：分别在各个节点上得到结果后在应用程序端进行合并。
• 数据迁移，容量规划，扩容等问题
• ID问题：数据库被切分后，不能再依赖数据库自身的主键生成机制啦，最简单可以考虑UUID
• 跨分片的排序分页问题

最近我遇到了一个bug，我试着通过Rails在以“utf8”编码的MariaDB中保存一个UTF-8字符串，然后出现了一个离奇的错误：Incorrect string value: ‘😃 <…’ for column ‘summary’ at row 1我用的是UTF-8编码的客户端，服务器也是UTF-8编码的，数据库也是，就连要保存的这个字符串“😃 <…”也是合法的UTF-8。问题的症结在于，MySQL的“utf8”实际上不是真正的UTF-8。“utf8”只支持每个字符最多三个字节，而真正的UTF-8是每个字符最多四个字节。MySQL一直没有修复这个bug，他们在2010年发布了一个叫作“utf8mb4”的字符集，绕过了这个问题。当然，他们并没有对新的字符集广而告之（可能是因为这个bug让他们觉得很尴尬），以致于现在网络上仍然在建议开发者使用“utf8”，但这些建议都是错误的。

1.MySQL的“utf8mb4”是真正的“UTF-8”。

2.MySQL的“utf8”是一种“专属的编码”，它能够编码的Unicode字符并不多。

我要在这里澄清一下：所有在使用“utf8”的MySQL和MariaDB用户都应该改用“utf8mb4”，永远都不要再使用“utf8”。