mysql面试题总结

事务的基本特性和隔离级别

（1）基本特性ACID如下：

• 原子性（Atomicity）:一个事务中的操作要么全部成功要么全部失败

• 一致性（Consistency）：事务必须始终保持系统处于一致的状态，不管在任何给定的时间并发事务有多少。

• 隔离性（Isolation）：事务提交前对其他事务不可见

• 持久性（Durability）：一旦事务提交，所做的修改就会永久保存到数据库中

（2）四个隔离级别：

• 读未提交（read uncommitted）：可能会读到其它事务未提交的数据，也叫脏读
• 读已提交（read committed）：两次读取结果不一致，不可重复读
• 可重复读（repeatable read）：可重复读，但是可能幻读
• 串行化（serializable）：一般不适用，因为会大量加行锁，代价极大

innodb事务执行过程

通过buffer pool，log buffer，redo log，undo log来实现事务，以一个update为例。

1.innodb收到一个update后，先找到数据所在的页，并将该页缓存在buffer pool中

2.执行update语句，修改buffer pool中的数据，也就是内存中的数据

3.针对update 生成一个redo log对象，并存入logbuffer pool中

4.针对update语句生成undolog日志，用于事务回滚

5.如果事务提交，那么则把redo log对象进行持久化，后续还有其它机制将buffer pool中所修改的数据持久化到磁盘中

6.事务回滚则利用undo log日志

mysql执行过程

参考文章

ACID靠什么保证

（1）原子性：由undo log日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经执行成功的sql （2）一致性：由其他三大特性保证、程序代码要保证业务上的一致性 （3）隔离性：由MVCC来保证

（4）持久性：由内存+redolog来保证,mysql修改数据同时在内存和redo log记录这次操作,宕机的时候可以从redo Iog恢复

InnoDB redo log 写盘 InnoDB事务进入prepare状态 如果前面prepare成功, bin log写盘，再继续将事务持久化到bin log,如果持久化成功,那么InnoDB事务则进入commit状态(在redo log里面写一个commit记录）

MVCC

多版本并发控制:读友数据时通过一种类似快照的方式将数据保存下来,这样读锁就和写锁不冲突了,不同的事务session会看到自己特定版本的数据,版本链

MVCC只在READ COMMITTED和REPEATABLE READ两个隔离级别下工作。其他两个隔离级别和MVCC不兼容,因为READ UNCOMMITTED总是读取最新的数据行,而不是符合当前事务版本的数据行。而SERIALIZABLE则会对所有读取的行都加锁

聚集索引记录中有两个必要的隐藏列: trx_id:用来存储每次对某条聚集索引记录进行修改的时候的事务id.

roll_pointer:每次对哪条聚集索引记录有修改的时候,都会把老版本写入undo log中。这个roll_pointer就是存了一个指针,它指向这条聚集索引记录的上一个版本的位置,通过它来获得上一个版本的记录信息。(注意插入操作的undo log没有这个属性,因为它没有老版本)

己提交读和可重复读的区别就在于它们生成Readview的策略不同。

锁的类型

基于粒度划分：

• 表锁：锁住整个表，不会产生死锁

• 行锁：只锁住一行或者多行。其他事务可以正常访问别的记录

• 记录锁：行锁的一种，但是记录锁只锁住表的某一项记录，并且命中条件是唯一索引。避免了重复度和脏读

• 间隙锁：行锁的一种，锁住表记录的一个区间，用于防止幻读

慢查询处理

• 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写。

• explain,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引。

• 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表

mysql主从同步

mysql生从同步的过程:

Mysql的主从复制中主要有三个线程:master (binlog dump thread) slave(I/O thread、SQL thread)，Master一条线程和Slave中的两条线程。

• 主节点binlog,主从复制的基础是主库记录数据库的所有变更记录到binlog。binlog是数据库服务器启动的那一刻起,保存所有修改数据库结构或内容的一个文件。

• 主节点log dump线程，当binlog有变动时,log dump线程读取其内容并发送给从节点
• 从节点I/O线程接收binlog内容,并将其写入到relay log文件中。
• 从节点的SQL线程读友relaylog文件内容对数据更新进行复现,最终保证主从数据库的一致性

注:主从节点使用binglog文件+position偏移量来定位主从同步的位置,从节点会保存其已接收到的偏移量（也即增量同步），如果从节点发生宕机重启,则会自动从position的位置发起同步

由于mysql默认的复制方式是异步的,主库把日志发送给从库后不关心从库是否已经处理,这样会产生一个问题，假设主库挂了,从库处理失败,这时候从库升为主库后,日志就丢失了,由此产生两个概念

• 全同步复制：主库写入binlog后强制同步日志到从库,所有的从库都执行完成后才返回给客户端,但是性能会受到严重影响
• 半同步复制：从库写入日志成功后返回ACK给主库，主库收到至少一个从库的确认就认为写操作完成

mysql有哪些引擎

（1）InnoDB：mysql默认事务型引擎，被设计用来处理大量的短期（short-lived）事务。

（2）myisam：在5.1之前的版本，是默认引擎。不支持事务和行级锁，且崩溃后无法安全恢复。对整张表加锁，容易引起性能问题。

（3）memory：比myisam快至少一个量级，数据文件存在内存中。表结构重启后保留，数据丢失。在下列场景发挥作用较好：1）查找（look up ）或者映射（mapping）表。2）缓存周期性聚合数据（periodically aggregated data）的结果。3）用于保存数据分析产生的中间数据

（4）archive：只支持insert和select操作，会缓存所有的写并利用zlib对插入的表进行压缩，所以比myisam的磁盘io更少。但是每次select都全表扫描，更适合日志和数据采集类应用。

（5）csv：将普通csv文件作为mysql表处理，但是不支持索引。可以作为异众数据交换机制，非常有用。

（6）merge：一组MyISAM表的组合。Merge表就是几个相同MyISAM表的聚合器；Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作，这些操作实际上是对内部的MyISAM表进行操作。

（7）blackhole：该存储引擎支持事务，而且支持mvcc的行级锁，写入这种引擎表中的任何数据都会消失，主要用于做日志记录或同步归档的中继存储，这个存储引擎除非有特别目的，否则不适合使用。

（8）performance_schema：要用于收集数据库服务器性能参数。这种引擎提供以下功能：提供进程等待的详细信息，包括锁、互斥变量、文件信息；保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）。 MySQL用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。

（9）federated：该存储引擎可以不同的Mysql服务器联合起来，逻辑上组成一个完整的数据库。这种存储引擎非常适合数据库分布式应用。

参考文章

MyISAM和InnoDB的区别

MyISAM:

• 不支持事务，但是每次查询都是原子的。且没有data cache，所有DML操作只写到OS cache中，flush disk操作均由OS来完成
• 支持表级锁，即每次操作是对整个表加锁
• 不支持外键，因此包含外键的innoDB无法转为myisam
• 存储表的总行数
• 一个MYISAM表有三个文件：索引文件、表结构文件、数据文件
• 采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅助引与主索引基本一致,但是辅索引不用保证唯一性。

InnoDB:

• 支持ACID的事务，支持事务的四种隔离级别
• 支持行级锁及外键约束，因此可以支持写并发
• 不存储总行数
• 一个InnoDb引擎存储在一个文件空间（共享表空间,表大小不受操作系统控制,一个表可能分布在多个文件里），也有可能为多个(设置为独立表空间,表大小受操作系统文件大小限制,一舫为2GB),受操作系统文件大小的限制;
• 主键索引采用聚集索引，辅助索引的数据域存储主键值。因此从辅索引查找数据,需要回表;最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。

数据表设计时选择字段的原则

（1）优先级

整型>date,time>enum char>varchar>blob,text

选用字段长度最小、优先使用定长型、数字型字段中避免使用“zreofill”

time：定长运算快、节省文件、考虑时区、写sql不方便

enum：能约束值的目的，内部用整形来存储，但与char联查时，内部需要经历串与值的转化。

char：定长，考虑字符集和校对集

varchar：不定长，要考虑字符集的转换与排序时的校对集，速度慢。

text,blob：无法使用内存临时表（排序操作只能在磁盘上进行）

注意：date,time可以直接使用时间戳，可以使用tinyint最好就用tinyint

（2）可以选择整型就不选字符串。

整型定长，没有国家/地区之分，没有字符集差异

（3）够用即可

（4）尽量避免使用null，不利于索引也不利于查询。

零碎

（1）mysql一列（不包括隐藏列和头信息）占用的字节加起来不能超过65535个字节

提高insert性能

（2）合并多条insert为一条，减少了日志量，降低日志刷盘的数据量和频率，从而提高效率

（3）修改参数bulk_insert_buffer_size,调大批量插入的缓存

（4）设置innodb_flush_log_at_trx_commit=0

参数意义：

0：log buffer中的数据将以每秒一次的频率写入log file中，且同时会进行文件系统到磁盘的同步操作，但是每个事务的commit并不会触发任何log buffer到log file的刷新或者文件系统到磁盘的刷新操作。

1：在每次事务提交的时候将log buffer 中的数据写入log file，同时触发文件系统到磁盘的同步

2：事务提交会触发log buffer到log file的刷新，但是不会触发擦盘文件系统到磁盘的同步。此外，每秒会有一次文件系统到磁盘同步操作

3：手动使用事务。mysql默认是autocommit，每插入一条数据都会进行一次commit；所以为了减少创建事务的小号，可手动使用事务。即start transaction；insert ……. commit

mysql的锁的类型有哪些

基于所得属性分类：共享锁、排他锁

基于锁的粒度分类：行级锁（innoDB）、表级锁（innoDB、myisam）、页级锁（BDB引擎）、记录锁、间隙锁、临键锁。

基于锁的状态：意向共享锁、意向排它锁

共享锁又称读锁，是读取操作建立的锁。其它用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），知道已经释放所有共享锁。如果事务对读锁进行修改操作，很可能会造成死锁。

排他锁又称写锁。若某个事务对某一行加上了排它锁，只能这个事务对其进行读写，此事务结束前，其它事务不能对其加任何锁，其它进程可以读取，不能进行写操作，需等待其释放。排它锁是一种悲观锁。

行锁解决脏读， 间隙锁解决幻读。innoDB使用临建锁(记录锁和间隙锁的结合)，可以避免在查询范围里出现脏读、重复读、幻读问题。

全局锁是对整个数据库加锁，它的典型使用场景就是做全库逻辑备份。这个命令可以使整个库处于只读状态。使用该命令后，数据更新语句，数据定义语句，更新类事务的提交语句等操作都会被阻塞

mysql的死锁查看及解决

使用命令 show engine innodb status 查看最近的一次死锁。InnoDB Lock Monitor 打开锁监控，每15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。

1)通过innodblockwait_timeout来设置超时时间，一直等待直到超时 2)发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

待续