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 lock1+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未被同步到磁盘的事务会被丢失恢复程序将无法恢复这部分事务。

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