MySQL 高频知识点

MySQL 高频知识点

1. 事务四大特性（ACID）原子性、一致性、隔离性、持久性？

原子性：指一个事务是一个不可分割的工作单位，其中的操作要么全做，要么全不做。

一致性：指事务执行前后，数据与操作保持一致。

隔离性：指多个事物并发执行时，是内部的操作与其他事物是隔离的，互补干扰

持久性：指事务一旦提交，对数据库的改变是永久性的。不管后面发生什么操作或者故障都不会对这个事物有影响。

2. 事务的并发？事务隔离级别，每个级别会引发什么问题，MySQL默认是哪个级别？

脏读、不可重复读、幻读概念

脏读：指一个事务处理过程中，读取了另一个未提交事务中的数据。比如一个事务正在多次修改某个数据，这个事物的所有改变都还未提交，这时另一个并发事物过来访问数据，就会造成两个事物得到的数据不一致。

不可重复读：指一个事务在读取数据库中的某个数据，多次查询却返回了不同的数据值，这是由于在查询的间隔，被另一个事务修改并提交了数据。

幻读：一个事务(同一个read view)在前后两次查询同一范围的时候，后一次查询看到了前一次查询没有看到的行。

两点需要说明：
　1、在可重复读隔离级别下，普通查询是快照读，是不会看到别的事务插入的数据的，幻读只在当前读下才会出现。
　2、幻读专指新插入的行，读到原本存在行的更新结果不算。因为当前读的作用就是能读到所有已经提交记录的最新值。

比如：事务T1在删除了数据a后，并发的事物T2又插入了a数据，并且提交。这时事务T1又查询出了数据a，发现数据a并没有删除，跟发生了幻觉一样。

幻读产生的原因

行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录。

如何解决幻读

将两行记录间的空隙加上锁，阻止新记录的插入；这个锁称为间隙锁。
间隙锁与间隙锁之间没有冲突关系。跟间隙锁存在冲突关系的，是往这个间隙中插入一个记录这个操作。

MySQL提供了四种事物隔离级别

Serializable（串行化）：可避免脏读、不可重复读、幻读的发生。
Repeatable（可重复读）：可避免脏读、不可重复读的发生。
Read committed（读已提交）：可避免脏读的发生。
Read uncommitted（读未提交）：最低级别，什么都保证不了。

MySQL中默认的隔离级别是Repeatable 可重复读，MySQL可以设置事务隔离级别

    set  [glogal | session]  transaction isolation level 隔离级别名称;
    set tx_isolation=’隔离级别名称;’

在Oracle中，只支持Serializable（串行化）级别和Read committed（读已提交）这两种级别，默认为读已提交级别。

3. MySQL常见的三种存储引擎（InnoDB、MyISAM、MEMORY）的区别？

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

4. MySQL的MyISAM与InnoDB两种存储引擎在，事务、锁级别，各自的适用场景？

MyISAM特点

不支持行锁(MyISAM只有表锁)，读取时对需要读到的所有表加锁，写入时则对表加排他锁；
不支持事务
不支持外键
不支持崩溃后的安全恢复
在表有读取查询的同时，支持往表中插入新纪录
支持BLOB和TEXT的前500个字符索引，支持全文索引
支持延迟更新索引，极大地提升了写入性能
对于不会进行修改的表，支持压缩表，极大地减少了磁盘空间的占用

InnoDB特点

支持行锁，采用MVCC来支持高并发，有可能死锁
支持事务
支持外键
支持崩溃后的安全恢复
不支持全文索引

MyISAM和InnoDB两者的应用场景：

MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。

但是实际场景中，针对具体问题需要具体分析，一般而言可以遵循以下几个问题：
- 数据库是否有外键？
- 是否需要事务支持？
- 是否需要全文索引？
- 数据库经常使用什么样的查询模式？在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。
- 数据库的数据有多大？大尺寸倾向于innodb，因为事务日志，故障恢复。

5. 查询语句（select）不同元素（where、jion、limit、group by、having等等）执行先后顺序？

开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->ORDER BY子句->SELECT子句->LIMIT子句->最终结果（F->W->G->H->O->S>L）

select 考生姓名, max(总成绩) as max总成绩      # 6
 
from tb_Grade                              # 1
 
where 考生姓名 is not null                   # 2
 
group by 考生姓名                            # 3
 
having max(总成绩) > 600                     # 4
 
order by max(总成绩)                         # 5

limit 4                                     # 7

6. 什么是临时表，临时表什么时候删除?

mysql临时表分为两种，一种是使用create temporary table创建的，称为为外部临时表；一种是因union、order by、group by、distinct等语句产生的，称为内部临时表。

MySQL最初会将临时表创建在内存中,当数据变的太大后,就会转储到磁盘上。

临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

7. MySQL B+Tree索引和Hash索引的区别？

索引是帮助获取数据的一种数据结构。最常见的索引是BTree索引和Hash索引。

不同的引擎对索引有不同的支持：Innodb和MyISAM默认的索引是BTree索引；而Mermory引擎默认是Hash索引。

BTree索引和Hash索引检索方式不一样，对查询的作用也不一样。

BTree索引

BTree索引是MySQL最常用的索引，因为它不仅可以被用在（“=” “>” “>=” “<” “<=” “between” ）这些比较操作符上，而且还可以作用于like操作符。注意：使用like时，查询条件不能是通配符开头

例如

like 'wang%' ; like 'w%ang%';这样就可以使用索引查询

like '%wang' 这样则不会使用索引查询。

Hash索引

Hash索引只能用于对等比较，如(“=”，“in”) 操作符。由于是根据hash值一次定位数据，不像BTree那样从根节点到枝节点，最后才能访问到页节点这样多次IO，所以检索效率远远高于BTree索引。但是由于Hash本身的特殊性，Hash索引有很多弊端：

1.只能满足等值查询(“=”，“in”)查询，不能使用范围查询。

2.Hash索引任何时候都不能避免全表扫描。

Hash索引是将索引键通过Hash运算之后，将Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行比较，并得到相应的结果。

3.Hash索引无法避免数据排序操作。

由于哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法利用Hash索引来加速任何排序操作

4.在联合索引中，Hash索引不能利用部分索引键查询。

对于联合索引中的多个列，Hash是要么全部使用，要么全部不使用，并不支持BTree支持的联合索引的最优前缀，也就是联合索引的前面一个或几个索引键进行查询时，Hash索引无法被利用。

5.Hash索引如果遇到大量的Hash冲突后，性能会下降

对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据访问，而造成整体性能底下。

8. sql查询语句确定创建哪种类型的索引？如何优化查询？

普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索

优化索引查询：

1、最左前缀匹配原则，非常重要的原则

对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。比如创建了多列索引(name,age,sex)，会先匹配name字段，再匹配age字段，再匹配sex字段的，中间不能跳过。mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。（比如，我们会选择学号做索引，而不会选择性别来做索引。）

3、=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4、索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

......

9. 聚集索引和非聚集索引区别？

聚集索引和非聚集索引的概念：

其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

如果遇到不认识的字，不知道它的发音，这时候，需要去根据“偏旁部首”查到您要找的字，然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“张”字，我们可以看到在查部首之后的检字表中“张”的页码是672页，检字表中“张”的上面是“驰”字，但页码却是63页，“张”的下面是“弩”字，页面是390页。很显然，这些字并不是真正的分别位于“张”字的上下方，现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字，但它需要两个过程，先找到目录中的结果，然后再翻到您所需要的页码。我们把这种目录纯粹是目录，正文纯粹是正文的排序方式称为“非聚集索引”。

通过以上例子，我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下，我们可以很容易的理解：每个表只能有一个聚集索引，因为目录只能按照一种方法进行排序。

区别及优缺点
区别：

聚集索引

1.一个表只能有一个，而非聚集索引一个表可以存在多个
2.存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
3.物理存储按照索引排序；聚集索引是一种索引组织形式，索引的键值逻辑顺序决定了表数据行的物理存储顺序。

非聚集索引

1.物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创建相应的索引，不影响整个表的物理存储顺序。
2.索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。
优势与缺点：

聚集索引插入数据时速度要慢（时间花费在“物理存储的排序”上，也就是首先要找到位置然后插入），查询数据比非聚集数据的速度快。

详细介绍：https://blog.csdn.net/riemann_/article/details/90324846

10. 乐观锁和悲观锁，各自的优缺点，他们的实现方式？

悲观锁

悲观锁指的是采用一种持悲观消极的态度，默认数据被外界访问时，必然会产生冲突，所以在数据处理的整个过程中都采用加锁的状态，保证同一时间，只有一个线程可以访问到数据，实现数据的排他性。

实现方式：通常，数据库的悲观锁是利用数据库本身提供的锁机制去实现的.

优点：
适合在写多读少的并发环境中使用，虽然无法维持非常高的性能，但是在乐观锁无法提更好的性能前提下，可以做到数据的安全性
缺点：
加锁会增加系统开销，虽然能保证数据的安全，但数据处理吞吐量低，不适合在读书写少的场合下使用

乐观锁

是相对悲观锁而言，乐观锁是假设认为即使在并发环境中，外界对数据的操作一般是不会造成冲突，所以并不会去加锁(所以乐观锁不是一把锁)，而是在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让返回冲突信息，让用户决定如何去做下一步，比如说重试，直至成功为止

**实现方式****（内在都是CAS思想的设计）

方式一： 使用数据版本（version）实现

就是在表中增添一个字段作为该记录的版本标识，比如叫version，每次对该记录的写操作都会让 version+ 1。

所以当我们读取了数据(包括version)，做出更新，要提交的时候，就会拿取得的version去跟数据库中的version比较是否一致，如果一致则代表这个时间段，并没有其他的线程的也修改过这个数据，给予更新，同时version + 1；如果不一致，则代表在这个时间段，该记录以及被其他线程修改过了，认为是过期数据，返回冲突信息，让用户决定下一步动作，比如重试（重新读取最新数据，再过更新）
方式二： 使用时间戳(timestamp)实现

表中增加一个字段，名称无所谓，比如叫update_time, 字段类型使用时间戳（timestamp）

检查方式和使用数据版本类似。

优点：
在读多写少的并发场景下，可以避免数据库加锁的开销，提高Dao层的响应性能
其实很多情况下，我们orm工具都有带有乐观锁的实现，所以这些方法不一定需要我们人为的去实现

缺点：
在写多读少的并发场景下，即在写操作竞争激烈的情况下，会导致CAS多次重试，冲突频率过高，导致开销比悲观锁更高

12. 数据库三范式？

第一范式（1NF）：确定每一列的原子性。每一列都是最小的数据单元，既满足第一范式。

第二范式（2NF）：非键字段必须依赖于键字段。如果一个关系满足1NF，并且除了主键以外的其它列，都依赖与该主键，则满足二范式(2NF)，第二范式要求每个表只描述一件事。

第三范式（3NF）：在1NF基础上，除了主键以外的其它列都不传递依赖于主键列，或者说：任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）。

13. 数据库的读写分离、主从复制，主从复制分析的 7 个问题？

主从复制：主数据库有写操作，从数据库自动同步。从数据库通过I/O线程去请求主数据库的binlog日志文件（二进制日志，包含SQL的增删改查等，用来做备份恢复等），并写到中继日志中，SQL线程会读取中继日志，并解析成具体操作同步数据到从数据库。
读写分离：数据库层面：主数据库复制写，从数据库复制读。软件（代码）层面：通过读写分离中间间，比如MyCat、shardingsphere等实现。

mysql主从复制存在的问题：

主库宕机后，数据可能丢失
从库只有一个sql Thread，主库写压力大，复制很可能延时

解决方法：

半同步复制---解决数据丢失的问题
并行复制----解决从库复制延迟的问题

14. 使用explain优化sql和索引？

对于复杂、效率低的sql语句，我们通常是使用explain sql 来分析sql语句，这个语句可以打印出，语句的执行过程。这样方便我们分析，进行优化

EXPLAIN

id
SELECT查询的序列号，包含一组数字，表示查询中执行SELECT语句或操作表的顺序
包含三种情况：
1.id相同，执行顺序由上至下
2.id不同，如果是子查询，id序号会递增，id值越大优先级越高，越先被执行
3.id既有相同的，又有不同的。id如果相同认为是一组，执行顺序由上至下；在所有组中，id值越大优先级越高，越先执行。
select_type
SIMPLE:简单SELECT查询，查询中不包含子查询或者UNION
PRIMARY:查询中包含任何复杂的子部分，最外层的查询
SUBQUERY：SELECT或WHERE中包含的子查询部分
DERIVED：在FROM中包含的子查询被标记为DERIVER(衍生)， MySQL会递归执行这些子查询，把结果放到临时表中
UNION：若第二个SELECT出现UNION，则被标记为UNION, 若UNION包含在FROM子句的子查询中，外层子查询将被标记为DERIVED
UNION RESULT：从UNION表获取结果的SELECT

table ：这一列是查询设计的表。
type ：很重要的一列，显示了查询使用了那种类型，是否使用的索引，能反映出语句的质量。一般这个指标从好到坏依次是：system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL
为了保证查询至少达到range级别。最好达到ref，否则的话，只能说明这条语句性能有待提高。

possible_keys：指出mysql在试用了哪个索引在该表中查找行。如果没有使用任何索引，就显示的NULL，可以用于对优化时的索引调整。

key：显示使用的索引，如果没有使用，则显示NULL
key_len：显示的是所使用的索引长度，如果没使用，则是NULL。当然，在使用索引的情况下，索引长度越小。效果越明显。

ref 表示所有具有匹配的索引的行都被用到

rows：执行查询的行数，如果行数越小，说明查询次数越少，效率越高。
extra：包含查询mysql解决查询的详细信息。

15. MySQL慢查询怎么解决？

一、开启mysql慢查询

二、分析慢查询日志

三、常见的慢查询优化

（1）索引没起作用的情况

使用LIKE关键字的查询语句

在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为“%”，索引不会起作用。只有“%”不在第一个位置索引才会起作用。

使用多列索引的查询语句

MySQL可以为多个字段创建索引。一个索引最多可以包括16个字段。对于多列索引，只有查询条件使用了这些字段中的第一个字段时，索引才会被使用。
（2）优化数据库结构

将字段很多的表分解成多个表

（3）分解关联查询

将一个大的查询分解为多个小查询是很有必要的。很多高性能的应用都会对关联查询进行分解，就是可以对每一个表进行一次单表查询，然后将查询结果在应用程序中进行关联，很多场景下这样会更高效

（4）优化sql语句

16. 什么是内连接、外连接、交叉连接、笛卡尔积等？

1、内连接（inner join）：取得两张表中满足存在连接匹配关系的记录。

完整语法：左表inner join 右表 on 匹配条件

MySQL语法：左表 join 右表 on 匹配条件

2、外连接（outer join）：取得两张表中满足存在连接匹配关系的记录，以及某张表（或两张表）中不满足匹配关系的记录。具体又分为：左外链接、右外连接、全外链接（一般使用中省略 “外” 字）。

2.1、左外连（left outer join）：除显示两表满足匹配关系的记录，还显示左边表不满足匹配关系的记录；

完整语法：左表left outer join 右表 on 匹配条件

MySQL语法：左表 left join 右表 on 匹配条件

2.2、右外连（right outer join）：除显示两表满足匹配关系的记录，还显示右边表不满足匹配关系的记录；

完整语法：左表right outer join 右表 on 匹配条件

MySQL语法：左表right join 右表 on 匹配条件

2.3、全外连（union /union all）：将两张字段相同的表结果拼接成一张表。通常使用union all

     (select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )
     或 (select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB )；

union语句注意事项：

     1.通过union连接的SQL它们分别单独取出的列数必须相同；

     2.不要求合并的表列名称相同时，以第一个sql 表列名为准；

     3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；

     4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；

3、交叉连接（cross join）：显示两张表所有记录一一对应，没有匹配关系进行筛选，也被称之为：笛卡尔积。

完整语法：左表cross join 右表

MySQL语法：左表join 右表或左表，右表

原文链接：https://blog.csdn.net/zjt980452483/article/details/82945663

17. mysql都有什么锁，死锁判定原理和具体场景，死锁怎么解决？

锁：https://www.kuangstudy.com/bbs/1374937607191949314

死锁

所谓死锁：是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用，它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB。

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序

https://blog.csdn.net/weixin_44337261/article/details/108970710

18. varchar和char的使用场景？

1、 char长度固定，即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。

2、 varchar可变长度，可以设置最大长度；适合用在长度可变的属性

由于char是固定长度的所以它的处理速度比varchar快很多。但是缺点是浪费存储空间，读取char类型数据时候时如果尾部有空格会丢失空格，所以对于那种长度变化不大的并且对查询速度有较高要求的数据可以考虑使用char类型来存储。

19. mysql 高并发环境解决方案？

高并发大多的瓶颈在后台数据逻辑处理，在存储，mysql的正常的优化方案如下：

1、代码中sql语句优化

2、数据库字段优化，索引优化

3、加缓存，redis/memcache等

4、主从，读写分离

5、分区表

6、垂直拆分，解耦模块

7、水平切分

点评：

1、方法1&方法2是最简单，也是提升效率最快的方式。也许有人说这两点你已经做的很好了，你的每条语句都命中了索引，是最高效的。但是你是否是为了你的sql达到最优而去建索引，而不是从整个业务上来考虑。比如，订单表上我需要增加xx索引满足某单一业务，是否就一定要加，其他方法能否解决。如果要满足所有业务的需求，那么索引就泛滥了，对于千万级以上的表来说，维护索引的成本大大增加，反而增加了数据库的内存的开销。

2、数据库字段的优化。如：一个日期类型，被设计为varchar类型，不规范的同时，无法对写入数据校验，做索引的效率也有差别。

3、缓存适合读多写少更新频度相对较低的业务场景，否则缓存异议不大，命中率不高。缓存通常来说主要为了提高接口处理速度，降低并发带来的db压力以及由此产生的其他问题。你的接口时延多少？有没有被用户吐槽？有没有必要提升？

4、分区不是分表，结果还是一张表，只不过把存放的数据文件分词了多个小块，分块后。在表数据非常大的情况下，可以解决无法一次载入内存，以及大表数据维护等问题。

5、垂直拆分将表按列拆成多表，常见于将主表的扩展数据独立开，文本数据独立开，降低磁盘io的压力。

6、水平拆，这是一把最有效的牛刀。但是存在一个误区，有的人会觉得，为什么不在最开始就直接水平线拆，免去了后面迁移数据的麻烦。我个人感觉是，下定某个决策之前，必须有一个非常充分的理由。水平拆分的主要目的是提升单表并发读写能力(压力分散到各个分表中)和磁盘IO性能(一个非常大的.MYD文件分摊到各个小表的.MYD文件中)。如果没有千万级以上数据，为什么要拆，仅对单表做做优化也是可以的；再如果没有太大的并发量，分区表也一般能够满足。所以，一般情况下，水平拆分是最后的选择，在设计时还是需要一步一步走。

原文链接 https://blog.csdn.net/qiuweihong/article/details/78751466

20. 数据库崩溃时事务的恢复机制（REDO日志和UNDO日志）？

Undo Log:

Undo Log是为了实现事务的原子性，在MySQL数据库InnoDB存储引擎中，还用了Undo Log来实现多版本并发控制(简称：MVCC)。

事务的原子性(Atomicity)事务中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在执行的过程中发生了错误，要回滚(Rollback)到事务开始前的状态，就像这个事务从来没有执行过。
原理Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为UndoLog）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

之所以能同时保证原子性和持久化，是因为以下特点：

更新数据前记录Undo log。
为了保证持久性，必须将数据在事务提交前写到磁盘。只要事务成功提交，数据必然已经持久化。
Undo log必须先于数据持久化到磁盘。如果在G,H之间系统崩溃，undo log是完整的，可以用来回滚事务。
如果在A-F之间系统崩溃,因为数据没有持久化到磁盘。所以磁盘上的数据还是保持在事务开始前的状态。

缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。
如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即Redo Log。

Redo Log:

原理和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。

原文链接：https://www.detechn.com/post/1411.html

21. 为什么不推荐在MySQL中使用UTF-8编码，而使用UTF-8mb4？

早期的时候，Unicode 只用到了 0~0xFFFF 范围的数字编码，这就是 BMP 字符集。所以，最初MySQL在设计之初，也就只涉及了包含BMP 字符集的utfmb3(utf-8)，但是随着文字越来越多，3个字节肯定无法全部表示，于是Unicode支持的字符就更多了。

因为MySQL使用Utf8编码时，其实默认的是Utf8mb3编码，支持3个字节的字符存储。Utf8mb3已经不能满足现在的业务需求了，随着文字越来越多，比如emoji的存储，3个字节无法全部显示。

所以，MySQL在5.5.3之后增加了utf8mb4的编码。它支持BMP和补充字符。补充字符每个多字节字符最多需要4个字节，utf8mb4比utf8mb3来说，他能表示更多的补充字符，但是同时占用的空间可能会更大一些。所以我们通常会使用utf8mb4字符集创建数据库。

22. 当前读和快照读

MySQL InnoDB下的当前读和快照读

当前读
像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

23. MVCC多版本并发控制

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读，它的实现原理主要是依赖记录中的 3个隐式字段，undo日志 ，Read View 来实现的。

准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念

而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现

posted @ 2021-05-25 10:16 wode虎纹猫阅读(86) 评论(0) 收藏举报

刷新页面返回顶部

MySQL 高频知识点