【mysql面试复习总结】

mysql实战学习总结

学习极客时间mysql45讲总结如下

sql查询

• mysql组件有哪些？大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。
  
• 连接器的作用。你会先连接到这个数据库上，这时候接待你的就是连接器。连接器负责跟客户端建立连接、获取权限、维持和管理连接。用户密码认证通过后，连接器回到权限表里面查询出你拥有的权限。即使修改了权限也对之前的连接不生效。
• 分析器功能。从sql语句映射表名和列名，分析需要做什么，根据sql语法判断当前语法是否正确；
• 优化器。多索引的时候决定使用哪个索引，join表的时候怎么实现最优。
• 执行器。首先看对标是否有查询权限，如果没有权限会报错。有权限就对表内的每一行进行查询，直到最后一行，返回满足的结果集合。

sql更新：日志系统

之前你可能经常听 DBA 同事说，MySQL 可以恢复到半个月内任意一秒的状态，惊叹的同时，你是不是心中也会不免会好奇，这是怎样做到的呢？
更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）

• redo log,每次更新都写进磁盘，io成本比较高，InnoDB引擎会先把update记录到 redo log,并更新内存。是空闲的时候把内存更新到磁盘。
  redo log大小是固定的，如果文件满了需要即使更新到磁盘。
  redo log的工作原理，先看下图：
  
  write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
  有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。
• 重要模块：binlog
  redo log是innodb引擎特有的日志，而binlog是 server层的日志（归档日志）；
  两种日志有三点不同：
  1、redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
  2、redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
  3、redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
  有了对这两个日志的概念性理解，我们再来看执行器和 InnoDB 引擎在执行这个简单的 update 语句时的内部流程。
  1、执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
  2、执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
  3、引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
  4、执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
  5、执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

两阶段提交说明

binlog 会记录所有的逻辑操作，并且是采用“追加写”的形式。如果你的 DBA 承诺说半个月内可以恢复，那么备份系统中一定会保存最近半个月的所有 binlog，同时系统会定期做整库备份。这里的“定期”取决于系统的重要性，可以是一天一备，也可以是一周一备。
保证 redo log和binlog的一致性。binlog用来恢复数据库。redo log是引擎自带的，保证日常使用的数据不会出现错误。

事务

1.事务的概念是什么?
事务是对数据库中数据操作的保证数据逻辑一致的最小操作单位。

2.mysql的事务隔离级别读未提交, 读已提交, 可重复读, 串行各是什么意思?
读未提交：一个事务读取到了其他事务未提交的操作。
读已提交：一个事务读取到了其他事务已经提交的操作。
可重复读：一个事务从它开始到结束整个生命周期中，所能读取到的数据内容和它启动的时候所能读到的数据内容是相同的。不会出现在事务运行的整个过程中，不同的时间点读取到的数据不一样的情况。
串行化：所有的事务都进行排队执行，事务之间不存才并发的情况。读有读锁，写有写锁。读、读之间不影响，读、写和写、写之间互相排斥，当遇到排斥的情况后，后发生的事务需要等待先发生的事务执行完成后才可以执行。

3.读已提交, 可重复读是怎么通过视图构建实现的?
读已提交：会在事务中的每一个SQL语句执行的时候都为对应的SQL创建一个一致性视图。此时这个SQL能读取到已经提交的事务对数据的操作。
可重复读：会在事务启动的时候，为整个事务创建一个一致性视图，这个视图会贯穿到这个事务执行结束。在整个事务执行过程中，都使用这个视图中的数据作为一致性读的依据。

4.可重复读的使用场景举例?
对账的时候应该很有用
库管盘货

5.事务隔离是怎么通过read-view(读视图)实现的?
每一行数有多个版本，当我们要去读取数据的时候，要判断这个数据的版本号，对当前事务而言，是否可见，如果不可见，则要根据undolog计算得到上一个版本。如果上一个版本也不符合要求，则要找到再上一个版本，
直到找到对应正确的数据版本。

6.并发版本控制(MCVV)的概念是什么, 是怎么实现的?
同一个数据行，在数据库中存在多个版本号，这个版本号可以理解为当初操作这行数据的事务的事务ID。当多个事务在并发进行的时候，判断某个事务是否可以读取到某一行时，
会使用行的版本号，和当前事务的ID进行比较。如果发现比这个事务ID小，表示之前的事务提交的操作，对当前事务来说，此版本的数据可见。
如果发现这个数据的版本号比当前事务的ID大，则表示有未来发生的事务提交生成的，对当前事务来说，此版本的数据不可见。
如果发现这个竖行的版本号刚好等于某个正在运行的是事务ID，表示当前数据是由正运行的某个事务提交生成的，对当前事务来说，此版本数据不可见。
如果当前事务自己修改的数据，当前事务还是可以看到的。

7.使用长事务的弊病? 为什么使用常事务可能拖垮整个库?
长事务导致表空间持续增长，即便是事务提交或者回滚后，回滚表空间被是否后，表空间大小仍然不会被缩小。
长事务的存在导致锁发生冲突或等待的几率大大增加。
如果某个应用有发生锁等待后尝试重新建立连接的机制，那么在发生锁等待或冲突的时候，应用就会不断地发起新的连接，导致MySQL的连接数被占用爆满。MySQL不能在提供连接服务，就挂掉了。

8.事务的启动方式有哪几种?
begin;–一致性读的视图不会马上创建，而是在执行begin后面的第一个操作innodb表的SQL语句时生成。这个SQL可以是select,update,delete,insert。事务ID也是此时被分配的
start transaction;–和begin的功能效果一样。
start transaciton with consistent snapshot;–该语句执行后，会马上创建一致性读的视图。这个是它和begin的区别。事务ID也是此时被分配的。

9.commit work and chain的语法是做什么用的?
提交上一个事务，并且再开启一个新的事务。它的功能等效于：commit + begin。

10.怎么查询各个表中的长事务?
select * from information_schema.innodb_trx;
这个表中记录了所有正在运行的事务信息，里面有事务的开始时间。可以从这里看出哪些事务运行的时间比较长。

11.如何避免长事务的出现?
从数据库方面：
a.设置autocommit=1，不要设置为0。
b.写脚本监控information_schemal.innodb_trx表中数据内容，发现长事务，kill掉它。
c.配置SQL语句所能执行的最大运行时间，如果查过最大运行时间后，中断这个运行事情长的SQL语句。
d.设置回滚表空单独存放，便于回收表空间。
从业务代码方面：
a.确认是否使用了autocommit=0的配置，如果有关闭它，然后再业务代码中手动的使用begin;commit来操作。
b.检查业务逻辑代码，能拆分为小事务的不要用大事务。
c.检查代码，把没有必要的select语句被事务包裹的情况去掉。

索引

1.InnoDB中的索引模型：B+Tree
2.索引类型：主键索引、非主键索引
主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)
3.主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)
4.一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
5.从性能和存储空间方面考量，自增主键往往是更合理的选择。
6、联合索引的技巧

1、覆盖索引：如果查询条件使用的是普通索引（或是联合索引的最左原则字段），查询结果是联合索引的字段或是主键，不用回表操作，直接返回结果，减少IO磁盘读写读取正行数据

2、最左前缀：联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符
3、联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。
4、索引下推：like 'hello%’and age >10 检索，MySQL5.6版本之前，会对匹配的数据进行回表查询。5.6版本后，会先过滤掉age<10的数据，再进行回表查询，减少回表率，提升检索速度

7.回表：回到主键索引树搜索的过程，称为回表
8.覆盖索引：某索引已经覆盖了查询需求，称为覆盖索引，例如：select ID from T where k between 3 and 5
在引擎内部使用覆盖索引在索引K上其实读了三个记录，R3~R5(对应的索引k上的记录项)，但对于MySQL的Server层来说，它就是找引擎拿到了两条记录，因此MySQL认为扫描行数是2
9.最左前缀原则：B+Tree这种索引结构，可以利用索引的"最左前缀"来定位记录
只要满足最左前缀，就可以利用索引来加速检索。
10.最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
第一原则是：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
11.索引下推：在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。
12.MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
13.选择普通索引还是唯一索引？
对于查询过程来说：
a、普通索引，查到满足条件的第一个记录后，继续查找下一个记录，知道第一个不满足条件的记录
b、唯一索引，由于索引唯一性，查到第一个满足条件的记录后，停止检索
但是，两者的性能差距微乎其微。因为InnoDB根据数据页来读写的。
对于更新过程来说：
概念：change buffer
当需要更新一个数据页，如果数据页在内存中就直接更新，如果不在内存中，在不影响数据一致性的前提下，InnoDB会将这些更新操作缓存在change buffer中。下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中的与这个页有关的操作。

change buffer是可以持久化的数据。在内存中有拷贝，也会被写入到磁盘上

purge:将change buffer中的操作应用到原数据页上，得到最新结果的过程，成为purge
访问这个数据页会触发purge，系统有后台线程定期purge，在数据库正常关闭的过程中，也会执行purge

唯一索引的更新不能使用change buffer

change buffer用的是buffer pool里的内存，change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的50%。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。
change buffer 因为减少了随机磁盘访问，所以对更新性能的提升很明显。

change buffer使用场景
在一个数据页做purge之前，change buffer记录的变更越多，收益就越大。
对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时change buffer的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer,但之后由于马上要访问这个数据页，会立即触发purge过程。
这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。所以，对于这种业务模式来说，change buffer反而起到了副作用。
索引的选择和实践：
尽可能使用普通索引。
redo log主要节省的是随机写磁盘的IO消耗(转成顺序写)，而change buffer主要节省的则是随机读磁盘的IO消耗。

1：MySQL选错索引，啥意思？

我们认为使用K索引检索的速度会更快的，但是MySQL没有使用，决定使用什么索引是由Server层的优化器来决定的，她也是想选择最佳的方案来检索数据的，不过他也是人写的程序也是存在bug的。

2：MySQL为啥会选错索引？

优化器认为使用那个索引检索数据的速度比较快是一个需要各种因素综合评估的事情，比如：是否使用临时表、是否排序、扫描的行数多少、回表的次数等，文中的例子优化器判断失误的主要原因是扫描行数的判断存在误差，因为这个信息是采样评估得到的。索引的创建是非常的耗时的，因为需要真正的建索引的过程，但是删除索引却不需要耗费太多时间，因为是标记删除，这个是以空间换时间的思路。优化器采用采样评估出现误差的原因也在于，索引的标记删除影响的。

3：MySQL选错索引怎么破？

3-1：强制指定使用某个索引，不常用不推荐用

3-2：调整SQL语句，使优化器选择的和我们想的一样，不具有通用性

3-3：新建更合适的索引或者删除不合适的索引，是一个思路

3-4：使用analyze table可以解决索引统计信息不准确导致的索引选错的问题

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

1：直接创建完整索引，这样可能比较占用空间；

这种方式最简单，如果性能没问题，我会这么创建，简单直接且存储空间的费用越来越低

2：创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

这种方式需要判断出前缀的长度多少合适，需要根据自己的业务来定，主要是看区分度多少合适

3：倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

这种方式用于前缀区分度不高后缀区分度高的场景，目的还是要提高索引的区分度，使用这种方式不适合范围检索

4：创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

mysql的锁

一、全局锁：
对整个数据库实例加锁。
MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
使用场景：全库逻辑备份。
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

一致性读是好，但是前提是引擎要支持这个隔离级别。
如果要全库只读，为什么不使用set global readonly=true的方式？
1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。

二、表级锁
MySQL里面表级锁有两种，一种是表锁，一种是元数据所(meta data lock,MDL)
表锁的语法是:lock tables … read/write
可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

三、行级锁

两阶段锁：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放， 而是要等到事务结束时才释放。
建议：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。
解决方案：
1、通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。
如何解决热点行更新导致的性能问题？
1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。

innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。

mysql抖动问题

1：MySQL抖一下是什么意思？

抖我认为就是不稳定的意思，一个SQL语句平时速度都挺快的，偶尔会慢一下且没啥规律，就是抖啦！

2：MySQL为啥会抖一下？

因为运行的不正常了，或者不稳定了，需要花费更多的资源处理别的事情，会使SQL语句的执行效率明显变慢。针对innoDB导致MySQL抖的原因，主要是InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知MySQL“抖”了一下的原因。

3：MySQL抖一下有啥问题？

很明显系统不稳定，性能突然下降对业务端是很不友好的。

4：怎么让MySQL不抖？

设置合理参数配配置，尤其是设置 好innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%

5：啥是脏页？

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

按照这个定义感觉脏页是不可避免的，写的时候总会先写内存再写磁盘和有没有用WAL没啥关系？

6：啥是干净页？

内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

7：脏页是咋产生的？

因为使用了WAL技术，这个技术会把数据库的随机写转化为顺序写，但副作用就是会产生脏页。

8：啥是随机写？为啥那么耗性能？

随机写我的理解是，这次写磁盘的那个扇区和上一次没啥关系，需要重新定位位置，机械运动是很慢的即使不是机械运动重新定位写磁盘的位置也是很耗时的。

9：啥是顺序写？

顺序写我的理解是，这次写磁盘那个扇区就在上一次的下一个位置，不需要重新定位写磁盘的位置速度当然会快一些。

10：WAL怎么把随机写转化为顺序写的？

写redolog是顺序写的，先写redolog等合适的时候再写磁盘，间接的将随机写变成了顺序写，性能确实会提高不少。

mysql数据存储原理，为啥数据删除了，表空间不变

1：为啥删除了表的一半数8据，表文文件大小没变化？

因为delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也可以认为是一种逻辑删除，所以物理空间没有实际释放，只是标记为可复用，表文件的大小当然是不变的啦！

2：表的数据信息存在哪里？

表数据信息可能较小也可能巨大无比，她可以存储在共享表空间里，也可以单独存储在一个以.ibd为后缀的文件里，由参数innodb_file_per_table来控制，老师建议总是作为一个单独的文件来存储，这样非常容易管理，并且在不需要的时候，使用drop table命令也能直接把对应的文件删除，如果存储在共享空间之中即使表删除了空间也不会释放。

3：表的结构信息存在哪里？

首先，表结构定义占有的存储空间比较小，在MySQL8.0之前，表结构的定义信息存在以.frm为后缀的文件里，在MySQL8.0之后，则允许把表结构的定义信息存在系统数据表之中。

系统数据表，主要用于存储MySQL的系统数据，比如：数据字典、undo log(默认)等文件

4：如何才能删除表数据后，表文件大小就变小？

重建表，消除表因为进行大量的增删改操作而产生的空洞，使用如下命令：

1：alter table t engine=InnoDB

2：optimize table t( 等于 recreate+analyze)。

3：truntace table t (等于drop+create)

5：空洞是啥？咋产生的？

空洞就是那些被标记可复用但是还没被使用的存储空间。

使用delete命令删除数据会产生空洞，标记为可复用

插入新的数据可能引起页分裂，也可能产生空洞

修改操作，有时是一种先删后插的动作也可能产生空洞

mysql排序order by

1：如果排序的字段有索引，怎么排？

由于B+树是天然有序的，所以直接取出的数据也是天然有序的，因此依次取数就行不用再排序了。若取出的字段是覆盖索引，则连回表的动作也不必做啦！

2：如果排序的字段没索引，怎么排？

非索引字段，取出来的时候是按索引的顺序取出的，针对非索引字段无序，需要在内存中通过排序算法来排序。

3：如果数据大到内存放不下，怎么排？

取出的数据无序且体量较大，只能先放到磁盘，然后用外排序的方法排序。

4：如果排序的字段有多个，怎么排？

有多个还是一个其实思路没啥变化，如果原本就有序，则直接取出，否则只能进行排序，体量小在内存中排序，体量大则必须借助磁盘排序。