Zuooの学习笔记

MySQL面试总结

  • MySQL
  • MySQL
大约 19 分钟

1、索引

mysql索引的种类：

  • 主键索引：Primary Key
  • 唯一索引：Unique Key
  • 全文索引：Fulltext Key
  • 组合索引：多个列值联合
  • 普通索引：Index

聚簇索引&&非聚簇索引：

聚簇索引的叶子节点保留了数据行，而非聚簇索引叶子节点储存的是指向数据行的地址

主键索引就是聚簇索引

MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针——非聚簇索引

B树和B+树

MySQL索引使用的是B+树结构，是一种磁盘友好的数据结构。它的叉的数量是可以指定的，Mysql中大约是1170，一个结点可以储存多个元素，相较于B树而言，B树节点之间是不存在重复元素的，B+树每一个父节点元素都是子节点的最大或最小元素，并且所有的叶子节点形成了一个有序的链表，从而支持范围查询。

  • B树的每个节点都存储了key和data，而B+树的data存储在叶子节点上。
  • B+树非叶子节点仅存储key不存储data，这样一个节点（数据页）就可以存储更多的key，可以使得B+树相对B树来说更矮（IO次数就是树的高度），所以与磁盘交换的IO操作次数更少。
  • B+树所有叶子节点构成一个 有序链表，按主键排序来遍历全部记录，能更好支持范围查找。由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历，相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。
  • B+树所有的查询都要从根节点查找到叶子节点，查询性更稳定；而B树，每个节点都可能查找到数据，需要在叶子节点和内部节点不停的往返移动，所以不稳定。

什么时候会索引失效全表扫描？

  1. 查询条件中带有 where...or...，除非所有条件字段都加了索引，否则就会全表扫描
  2. 模糊查询时where...like '%xx' 此时索引也会失效
  3. 索引列参与计算时会失效
  4. 违背最左匹配原则

场景题1：WHERE a > 1 and b=1 ORDER BY c 如何建立索引

可以建立联合索引，但是考虑到最左匹配原则，不能把a作为联合索引第一个，因为最左匹配原则是在前一个有序的情况下，才能走到后面的索引查询，而这里不确定a到底是2还是3还是什么，会导致索引失效。

如果c建立索引也会导致索引失效，因为有order by函数。

所以我选择(b,a)建立索引。

场景题2：比较效率COUNT(1)、COUNT(*)、COUNT(主键)、COUNT(字段)

性能：COUNT() = COUNT(1) > COUNT(主键字段) > COUNT(字段)

COUNT()的作用是，统计符合条件的记录中，函数指定的参数不为NULL的参数有多少。

COUNT(1)和COUNT(*)的效率是相同的，都只是统计结果集的行数，而不用对列进行计算和判断，因此效率较高。

COUNT(主键)的效率较低，在统计主键列需要判断是否存在空值，然后排除空值计算出行数，需要额外的开销。

COUNT(字段)的效率最低，字段如果不是索引字段，只是普通字段，将会采用全表扫描的方式来计数。

2、事务

事务的四个特性：ACID

原子性、隔离性、一致性、持久性

事务保证了一系列的操作要么全部成功要么全部失败，一旦事务提交，则其所做的修改会永久保存到数据库。

事务隔离级别

MySQL默认隔离级别是可重复读，可以避免脏读和不可重复读，可能会幻读

脏读和不可重复读的区别就是后者读到的是已提交的数据

幻读就是一个事务读取了某些记录，另一个事务又进行了插入或者删除操作，这种事务是没办法用行级锁来解决的

有四种隔离级别：读未提交、读已提交、可重复读、串行化

image.png

关于记录锁/间隙锁

在可重复读的隔离级别下，对一行数据进行查询或者更新都会加记录锁** record lock**

间隙锁 Gap Lock是为了解决幻读的，也是行锁的一种。它会锁住两个值之间的空隙【两边都是开区间】，当另一个事务去对这个锁住的间隙进行插入数据的时候就会阻塞，从而解决幻读的问题。间隙锁也是只在可重复读的级别下生效。

默认情况下间隙锁是开启的，innodb_locks_unsafe_for_binlog参数值默认是OFF，代表开启间隙锁

记录锁与行锁的组合：Next key Lock【前开后闭区间】Innodb在扫描索引记录的时候会先加record lock，再对索引记录两边的间隙加上Gap lock

什么情况下会产生next-key lock?

  1. 对于唯一索引

测试表记录：

  • 当查询单条记录的时候只会产生Record lock
select * from test where `id` = 3 for update
  • 当查询不存在的记录的时候会产生next-key lock【容易产生死锁】

间隙区间：向左扫描到第一个比给定参数小的值，向右扫描扫描到第一个比给定参数大的值，前开后闭

-- 事务1
BEGIN;
delete from test where `id` = 4;
-- 防止锁超时自动释放
SELECT sleep(10);
COMMIT;  ## 先不提交事务1


-- 事务2
BEGIN;
INSERT INTO `test` VALUES(5, 'hka'); ## 会阻塞
COMMIT;

这个案例中由于delete语句是删除不存在的数据，也会产生间隙锁，区间是（3，6]<br />所以事务2进行插入的时候就会阻塞，只有当事务1提交事务锁释放之后才能插入成功

  • 当进行范围查询的时候会产生next-key lock

事务隔离级别是如何实现的？

在访问数据库数据时，其实数据库会创建一个视图/快照，读未提交不会创建视图的，直接返回最新值，读已提交是在每个sql语句执行之前创建视图，可重复读是在事务开始之前创建视图，可序列化是直接加锁来实现。

可重复读的核心是一致性读，事务更新数据的时候只能使用当前读，不使用快照读，如果当前记录的行锁被其他事务占用就要进行锁等待。

要保证事务的原子性，就要通过undo log和MVCC机制，每个数据在MySQL中其实是有多个版本的，在更新一条记录的时候就会记录一条回滚日志，通过回滚可以得到前一个状态的值。这里的版本就是row trx_id。

3、分布式锁

用MySQl实现分布式锁的思想比较简单，就是加一张锁表，获得锁的时候就新增一条记录，释放锁就删除这条记录。这种方式基于数据库IO操作，比较低效，也存在着一些问题。

乐观锁

乐观锁大多是基于版本号机制来实现的。就是在数据表上增加一个version字段。更新数据后同时也将这个version加1，如果有多个线程并发修改一条记录，通过比较前后的版本号（在sql里加一个where条件）就可以判断是否要进行更新操作。

优点是不会影响请求的性能，但是并发写是有可能出现数据不一致的，适合读多写少的情景。高并发情况下出现严重的空自旋问题。

悲观锁

悲观锁也叫排它锁/写锁。操作数据的时候其他事务只能等待，阻塞请求，最大程度上保证了数据的一致性，但是容易导致锁超时、并发程度低甚至是死锁。适合写入操作比较频繁的场景。

实现悲观锁需要关掉Mysql的自动提交，开启事务

MySQL的insert 、update、delete语句会自动加上行锁（前提是要有索引，因为行锁是锁在索引上的）

4、主从复制

主要是为了缓解单一数据库的压力，mysql在事务提交的时候会把数据变更作为事件events记录到binlog二进制文件中，从库通过读取binlog进行数据同步。

原理是通过三个线程来实现数据同步，主库转储线程会在从库线程连接的时候将这个binlog日志发送给它，从库IO线程负责读取binlog日志并拷贝到中继日志relay log，还有一个sql线程负责从relay log执行日志中的事件

一般是主库用来写，从库用来读。

怎么降低主从延迟呢？

mysql5.6版本以后提供了并行复制的功能，可以将sql线程转换为多个woker线程并行复制

5、分库分表

有两种数据拆分方式：

  • 垂直拆分：字段太多，按照字段进行拆分
  • 水平拆分：字段比较少，但是单条记录很大，将表/库拆分，但是结构不变

可以按照哈希算法或者根据时间来进行分片。

分库分表后主要牵扯到的就是如何解决分布式事务问题--2PC 3PC XA协议

主要解决方案是使用Mycat、ShardingJDBC

6、慢查询

慢查询就是超过了long_query_time（默认10秒）时间阈值。

慢查询日志默认是不开启的，可以通过命令 set global slow_query_log='ON'来开启，通过 set global long_query_time=1来指定时间阈值，windows在my.ini文件中修改即可 <br />如何优化？

  1. **优化limit分页，**当偏移量很大的时候会很慢

查询字段比较少的时候可以构造联合索引，避免全表扫描

  1. 优化offset

利用自增主键有序的特性，比如现在要对以下sql进行优化：

SELECT * from xx LIMIT 1000000,10;

可以修改为这样：

SELECT * from xx where id >=(SELECT id from xx ORDER BY id limit 1000000,1) limit 10;

  1. 延迟关联

先查出要查的记录的id，再通过覆盖索引二次查询到该页的数据

SELECT * from xx inner join (SELECT id from xx order by id limit 1000000,10) as myNew using(id);

  1. 使用EXPLAIN命令查看sql执行情况，是否是全表扫描，如果有需要可以添加索引
  2. 尽可能避免使用子查询，使用合适的JOIN语句
  3. 对于需要复杂计算的查询，可以使用数据库缓存，将计算结果缓存到内存中避免重复计算
  4. 对于高并发的查询，可以考虑使用数据库的读写分离、分库分表等技术，以提高数据库的并发处理能力

7、三大日志

1.binlog

用于记录数据库执行的写入性操作(不包括查询)信息【逻辑日志sql语句】，以二进制的形式保存在磁盘中。任何引擎都会进行记录。

事务提交时会记录binlog。

mysql通过sync_binlog参数控制biglog的持久化时机，取值范围是0-N：

  • 0：不去强制要求，由系统自行判断何时写入磁盘；
  • 1：每次commit的时候都要将binlog写入磁盘；
  • N：每N个事务，才会将binlog写入磁盘。

从上面可以看出，sync_binlog最安全的是设置是1，这也是MySQL 5.7.7之后版本的默认值。但是设置一个大一些的值可以提升数据库性能，因此实际情况下也可以将值适当调大，牺牲一定的一致性来获取更好的性能。

日志格式：

  1. statement

基于语句模式：每一条修改都会进行记录，记录的是sql语句，从库可以根据sql语句进行复现，但是这种格式有动态函数的问题，比如使用了uuid或者now这种函数，在主库上执行的结果会和从库不一样

  1. row

基于数据行模式：记录的是行数据最终被修改成什么样子了，它没有动态函数的问题，但是由于每行数据的变化结果都会记录，如果执行过批量处理，binlog文件很容易变得很大

  1. mixed

混合模式：包括了statement和row模式，会根据不同情况自动使用row模式和statement模式

MySQL5.7版本之前默认格式为Statement，5.7开始默认格式为ROW，可以确保在数据复制和恢复的时候更加精确和一致。配置参数为：binlog_format

查看参数：show variables like 'binlog_format'

2.redo log

数据库逻辑的变更日志

Innodb引擎特有的。Innodb是以页为单位和磁盘进行交互，而一个事务可能只修改一页中的几个字节，如果全部刷新比较浪费资源，如果涉及到多个数据页，因为这些数据页在物理上是不连续的，使用随机IO写入的性能很差。

redo log会记录事务对数据页做了哪些修改，相当于一个归档。

redo log大小是固定的，采用了循环写入的方式，适用于崩溃恢复crash-safe

3.undo log

undo log保证了事务四大特性之一的原子性，记录了数据的逻辑变化，比如一条insert语句就对应着一条逻辑相反的delete语句，这样在发生错误的时候就可以进行回滚

4.二阶段提交原理

如果Mysql开启了binlog日志，在事务提交时需要同时完成binlog和redolog的事务写入。

MySQL的二阶段提交发生在binlog和redolog的日志写入阶段，目的是确保redolog和binlog写入的数据一致性：

  • prepare阶段

MySQL将事务操作记录到redolog中并标记为prepare状态

  • commit阶段

事务提交时Mysql将事务操作记录到binlog，然后将redolog中的日志设置为commit状态

如果在写入redo log之前崩溃，此时两个日志都没有记录，符合一致性

如果在写入redo log prepare阶段后立刻崩溃，在做崩溃恢复时，根据redolog的事务id去binlog中是无法查找到的，因为redolog没有被设置为commit状态，此时就会进行回滚

8、数据文件种类

在MySQL数据管理中，数据文件主要分为以下几种：

  1. 表结构文件：该文件存储数据库中表的元数据信息，比如表名、列名、数据类型、索引等信息。
  2. 数据文件：该文件存储表的实际数据。
  3. 日志文件：MySQL使用日志文件来记录对数据库中数据的修改操作，以便在需要时进行回滚或恢复操作。
  4. 临时文件：当进行一些复杂的查询或排序等操作时，MySQL可能会生成一些临时文件来辅助完成这些操作，这些临时文件通常存储在临时目录下。
  5. 锁文件：MySQL使用锁文件来控制多个并发操作之间的访问顺序和互斥性。
  6. 索引文件：该文件用于存储表中的索引信息，加快数据的检索速度。

9、MVCC机制

InnoDB 里面每个事务有一个唯一的事务 ID，叫作 transaction id。它是在事务开始的时候向 InnoDB 的事务系统申请的，是按申请顺序严格递增的。

而每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把 transaction id 赋值给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。

也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx_id。<br />MVCC（Multi-Version Concurrency Control）即多版本并发控制，是MySQL数据库中实现并发访问控制的一种机制。 读操作只读该事务开始前的数据库的快照（复制了一份数据）。这样在读操作不用阻塞写操作，写操作不用阻塞读操作的同时，避免了脏读和不可重复读。

MVCC机制在写数据之前会先将数据复制一份，从而创建一个新的快照--**写时复制技术 。**当一个事务需要修改数据时，MVCC会首先检查修改数据的快照版本号是否与该事务的快照版本号一致，如果一致就说明可以修改，否则这个事务需要等待其他事务完成对该数据的修改。另外，这个事务在新快照上修改的结果不会影响原始数据，其他事务可以继续读取原始数据的快照，从而解决脏读和不可重复读问题。

作用：在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数 据库并发读写的性能。同时还可以解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题。

实现原理： 它的实现原理主要是依赖记录中的 3 个隐式字段、undo 日志、Read View 来实现的。

表的隐藏列：

DB_TRX_ID记录操作该数据事务的事务ID；
DB_ROLL_PTR指向上一个版本数据在undo log 里的位置指针；
DB_ROW_ID:隐藏ID ，当创建表没有合适的索引作为聚集索引时，会用该隐藏ID创建聚集索引;

undolog日志：

  • 保证事务进行rollback时的原子性和一致性，当事务进行回滚的时候可以用undo log的数据进行恢复。
  • 用于MVCC快照读的数据，在MVCC多版本控制中，通过读取undo log的历史版本数据可以实现不同事务版本号都拥有自己独立的快照数据版本。

Read View：

在innodb 中每个事务开启后都会得到一个read_view。副本主要保存了当前数据库系统中正处于活跃（没有commit）的事务的ID号，其实简单的说这个副本中保存的是系统中当前不应该被本事务看到的其他事务id列表。

InnoDB 为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务 ID。“活跃”指的就是，启动了但还没提交。

数组里面事务 ID 的最小值记为低水位，当前系统里面已经创建过的事务 ID 的最大值加 1 记为高水位。

这个视图数组和高水位，就组成了当前事务的一致性视图（read-view）。

img

10、JOIN语句

MySQL的Join语句用于连接两个或多个表，以便在结果集中检索数据。

img

INNER JOIN 内连接

img

CROSS JOIN 交叉连接 返回两个表的笛卡儿积，后面没有ON条件

OUTER JOIN 外连接

  1. LEFT JOIN

左连接查询会返回左表中的所有记录，若左表的记录没有和右表匹配就会显示空值。

  1. RIGHT JOIN

和 LEFT JOIN 相反

  1. FULL JOIN 全连接

11、Innodb和MyISAM引擎区别

  1. 事务支持：InnoDB支持事务，而MyISAM不支持。这意味着使用InnoDB可以实现更可靠的数据处理，因为它具有ACID属性（原子性、一致性、隔离性和持久性）。
  2. 并发处理：InnoDB支持行级锁定，因此多个并发用户可以同时读取和写入不同的行，而不会阻塞彼此。MyISAM则只支持表级锁定，这意味着同时只能有一个用户访问整个表。
  3. 外键支持：InnoDB支持外键约束，MyISAM不支持。外键约束可以确保数据的完整性和一致性。
  4. 全文搜索：MyISAM比InnoDB更适合进行全文搜索，因为它支持全文索引。
  5. 索引：InnoDB在处理大量数据时比MyISAM效率更高，因为它使用了更复杂的B+Tree索引结构，可以更快地查找数据。

综上所述，如果需要事务支持、并发处理或外键约束等功能，则应选择InnoDB存储引擎。但如果需要进行全文搜索或需要处理较小的数据集，则可能更适合使用MyISAM存储引擎。

12、数据库范式&函数依赖

码：表中可以唯一确定一个元组的某个属性（或者属性组），如果这样的码有不止一个，就叫候选码，选定其中某个作为主码 外码：也叫外键，是其他表的码 全码：如果一个码包含了所有的属性就是全码 主属性：包含在任意一个候选码中的属性叫做主属性 元组：表中的一行数据就是元组

第一范式 1NF：属性不可再分

第二范式 2NF：满足第一范式，且非主属性完全依赖于码

比如（学生，课程）是一个码，但是课程-->教材，这就是不完全依赖，可以将一张表进行分解

第三范式 3NF：满足第二范式，且没有传递函数依赖

比如（学生，课程）是一个码，{学生，课程}-->老师， 老师-->老师职称，这就是有传递依赖，还是需要进行分解
上次编辑于: