MySQL面试题整理-四年经验

I.SQL、表、索引知识

1.SQL语句组成部分

数据定义：Create Table,Alter Table,Drop Table, Craete/Drop Index等

数据操纵：Select ,insert,update,delete,

数据控制：grant,revoke

数据查询：select

2.MySQL的锁

• 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

• 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

• 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

3.索引

索引就像是目录，为了能增加我们的查询速度而被设计了出来。

B+树

B+树索引能轻易存储上百万的数据，且只需要查询三次索引，非叶子节点不存储数据，但存储指向数据的位置。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

且B+树有数据块，能够轻易进行范围查找

哈希索引

单次查询极快，但不适合做范围查询。

使用索引的技巧

• 最左前缀原则：如果name是索引，email不是，则查询得先查name再匹配email。

• 选择区分度高，可能会被命中的列作为索引。

• B+树用自增索引很适合，因为增删改查不会大幅度改变B+树的结构。

• 索引值不能参与计算，否则开销极大

索引不匹配的问题

• 使用了like

• 使用了函数

• 使用了or语句

• 类型不一致，数字和引号，特别要注意如果类型是字符串，则传入数字也得用单引号

• 普通索引!=运算不走索引，但主键还是会走索引

• order by如果有索引，select字段没有索引，则不能走索引

• 最左前缀原则，违反了不匹配

慢查询优化的基本步骤

0. 先运行看看是否真的很慢，注意设置SQL_NO_CACHE
1. where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2. explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3. order by limit 形式的sql语句让排序的表优先查
4. 了解业务方使用场景
5. 加索引时参照建索引的几大原则
6. 观察结果，不符合预期继续从0分析

char和varchar的区别

char是固定长度的，存储的时候会用空格填充，varchar会变长

索引除了优点，还需要知道它可能存在的缺点

创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速度。

4.B+树的结构

一个m阶的B+树具有如下几个特征：

• 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。
• 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
• 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

形象地理解(为了不在博客里贴图）：每个树枝存了一堆索引，每个索引都记录了对应的子树的最大值，如根节点存了8和15，而我们查找的是3，那么很容易发现是需要从左子树开始查找。随后左子树如果是三叉，分别是2、5、8，那么我们会知道3在中间的那棵树，随后遍历即可。

b+树的叶子节点是有序链表，所以很容易进行范围查询。

II.引擎

1.Myisam

不支持事务，但是每次查询都是原子的；

支持表级锁，即每次操作是对整个表加锁；

存储表的总行数；

一个MYISAM表有三个文件：索引文件、表结构文件、数据文件；

采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。

2.Innodb

支持ACID的事务，支持事务的四种隔离级别；

支持行级锁及外键约束：因此可以支持写并发；

不存储总行数；

一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制；

主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。

3.InnoDB特殊的锁

• 行锁

• 间隙锁，锁定一个范围，但不包括记录本身，为了防止一个事务两次读，出现幻读。

• next-key lock，1+2，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题。

III.事务与回滚

1.事务的隔离级别

意外情况

脏读：一个事务读了另一个事务尚未提交的修改数据

非重复读：同一个事务，在两个时刻读取某一行的结果不同。

幻读：同一个事务中，返回的结果集被改动。

隔离级别

read uncommitted：读未提交，三种故障都会发生。

read commited：读已提交，除了脏读都有可能发生，也是oracle数据库的默认隔离级别。

REPEATABLE READ:可重复读，MySQL的默认事务隔离级别，只会允许幻读。通过next-key lock锁定一定范围的数据，阻止他人插入。

SERIALIZABLE：串行，效率最低，但不会有任何意外情况。

2.事务的特性

• 原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

• 一致性或可串性。事务的执行使得数据库从一种正确状态转换成另一种正确状态

• 隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务，

• 持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

3.事务日志

innodb事务日志包括redo log和undo log。redo log是重做日志，提供前滚操作，undo log是回滚日志，提供回滚操作。

undo log不是redo log的逆向过程，其实它们都算是用来恢复的日志。

1.redo log通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

2.undo用来回滚行记录到某个版本。undo log一般是逻辑日志，根据每行记录进行记录。

redo log

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的。

在概念上，innodb通过force log at commit机制实现事务的持久性，即在事务提交的时候，必须先将该事务的所有事务日志写入到磁盘上的redo log file和undo log file中进行持久化。

在主从复制结构中，要保证事务的持久性和一致性，需要对日志相关变量设置为如下：

• 如果启用了二进制日志，则设置sync_binlog=1，即每提交一次事务同步写到磁盘中。
• 总是设置innodb_flush_log_at_trx_commit=1，即每提交一次事务都写到磁盘中。

上述两项变量的设置保证了：每次提交事务都写入二进制日志和事务日志，并在提交时将它们刷新到磁盘中。

日志刷盘的规则

log buffer中未刷到磁盘的日志称为脏日志(dirty log)。

默认情况下事务每次提交的时候都会刷事务日志到磁盘中，这是因为变量 innodb_flush_log_at_trx_commit 的值为1。但是innodb不仅仅只会在有commit动作后才会刷日志到磁盘，这只是innodb存储引擎刷日志的规则之一。

刷日志到磁盘有以下几种规则：

1.发出commit动作时。已经说明过，commit发出后是否刷日志由变量 innodb_flush_log_at_trx_commit 控制。

2.每秒刷一次。这个刷日志的频率由变量 innodb_flush_log_at_timeout 值决定，默认是1秒。要注意，这个刷日志频率和commit动作无关。

3.当log buffer中已经使用的内存超过一半时。

4.当有checkpoint时，checkpoint在一定程度上代表了刷到磁盘时日志所处的LSN位置。

binlog

binlog即二进制日志，它记录了所有的 DDL 和 DML 语句（除了数据查询语句select、show等），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

工作场景：主从复制、数据恢复。

参数

对支持事务的引擎如InnoDB而言，必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。

如果设置为0，则表示MySQL不控制binlog的刷新，由文件系统去控制它缓存的刷新；

如果设置为不为0的值，则表示每 sync_binlog 次事务，MySQL调用文件系统的刷新操作刷新binlog到磁盘中。

设为1是最安全的，在系统故障时最多丢失一个事务的更新，但是会对性能有所影响。

如果 sync_binlog=0 或 sync_binlog大于1，当发生电源故障或操作系统崩溃时，可能有一部分已提交但其binlog未被同步到磁盘的事务会被丢失，恢复程序将无法恢复这部分事务。

牺牲一部分安全性，但是能提高性能