关于数据库你应该知道的

1.事务四大特性（ACID）

• 原子性：指包含事务的事件要么全部成功，要么全部失败回滚。
• 一致性：事务必须使数据库从一个一致性状态变化到另一个一致性状态。
• 隔离性：指多个用户并发访问数据库时，数据库为每一个用户开启的事务不能被另一个用户操作的事务所干扰。
• 持久性：一个事务一旦提交，数据库对数据的改变就是永久性的。

2.数据库隔离级别，每个级别会引发什么问题，mysql默认是哪个级别

• MySQL提供的四种隔离级别，通过设置transaction-isolation值来指定：
• 　　串行化：对于同一行记录，写和读都会加锁，当出现读写锁冲突时，会等待。避免脏读，不可重复读和幻读的发生。
• 　　可重复读：一个事务执行过程中，在启动时就会创建视图，所以这个事务总和它启动的时候看到的数据是一致的，核心是一致性读，事务更新数据时，只能使用当前读，如果当前记录行锁被占用，就需要进入锁等待。避免脏读，不可重复读的发生。
• 　　读已提交：它的视图是在每个sql语句开始执行的时候创建的，所以一个事务提交之后，他的变更才能被其他事务看到。可以避免脏读的发生。
• 　　读未提交：没有视图概念，所以一个事务还没提交时，他的变更就能被别的事务看到。任何情况都无法保证。
• MySQL默认隔离级别为可重复读。

3.MYSQL的两种存储引擎区别（事务、锁级别等等），各自的适用场景

• MyISAM:    适用大量检索场景

• 　　不支持事务，每次查询都是原子的；
• 　　支持表级锁，每次操作都是对整表加锁；
• 　　存储表的总行数，支持全文检索；
• 　　一个MyISAM有三个文件：索引文件，表结构文件和数据文件
• 　　采用非聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一直，但不用保持唯一性。
• InnoDb:    适用于有大量更新和插入操作的场景
• 　　支持ACID的事务，支持事务四种隔离级别；
• 　　支持行级锁和外键约束，可以用于并发场景；
• 　　不存储总行数，不支持全文检索；
• 　　使用B+树索引模型，主键索引为聚集索引（索引的数据存储数据文件本身），辅索引（也叫二级索引）的数据域存储主键的值；通过辅索引查找数据时，要先通过辅索引找到主键值，再访问数据，这个过程为回表。

4.数据库的优化

• 结构优化（使用索引）：
• 　　最左前缀匹配原则：通过调整顺序，可以少维护一个索引的情况
• 　　对主键外键建立索引
• 　　对where，on，group by，order by建立索引
• 　　选择区分度高的列作为索引
• 　　为较长的字符串建立索引
•        覆盖索引
•        索引下推（MySQL 5.6的优化）：在索引便利的过程中，对索引中包含的字段先进行判断，直接过滤不满足条件数据。
• SQL语句优化：
• 　　尽量使用列名；在业务密集的SQL中尽量不采用IN操作符，用EXIST代替
• 　　模糊查询like
• 　　两者都能使用尽量用where（和having相比）
• 　　尽量使用多表连接查询

5.索引有hash表、有序数组和搜索树

• 哈希表：以kv方式存储数据，因为是无序的，新增数据的时候会很快，但进行区间查找的时候会很慢，适用于只有等值查询的场景。
• 有序数组：使用二分法的算法，可以将查询复杂度降为logN，适用于等值查询和范围查询，但是在数组中间插入一个数据的时候比较麻烦，所以只适用于静态存储引擎。
• 二叉搜索树：借助二叉搜索树的优势，查询复杂度也是logN。但因为索引是需要写到内存和磁盘上的，为了尽可能少的读取磁盘，一般使用N叉树。为了维护索引有序性，B+树在插入索引时会挪动后面的数据，当数据页满了的时候，会申请一个新数据页，这个过程称作页分裂，影响性能和数据页利用率。

6.索引在什么情况下会失效？

• 条件中带上了or
• 对于多列索引，不是使用的第一部分
• like查询用%开头
• 字符串查询没用引号
• 优化器认为全表扫描比使用索引快

7.数据库的逻辑架构图，大体可以将Mysql分为Server层和存储引擎层。Server层包含连接器、查询缓存、分析器、优化器和执行器，所有的内置函数、存储过程和视图都在这一层实现。存储引擎层主要负责数据的存储和提取，支持InnoDB、MyISAM、Memory等多个存储引擎，InnoDB是默认存储引擎。

数据库的逻辑架构图

1. 连接器：和客户端建立连接、获取权限、维持和管理连接。使用 show processlist来看到客户端连接状态。长连接是指连接成功后，如果客户端持续有请求，会一直使用同一个连接，短连接指的是每次执行完很少的几次查询后就断开连接，之后再重新建立；长连接可能导致的问题是mysql内存占用过大，一般通过定时断开长连接和mysql_reset_connection来初始化连接资源
2. 查询缓存(从Mysql 8.0之后没有)：这里以kv的形式存储了之前执行过的语句和结果。但是一般不建议用，因为一个表的更新会导致所有的查询缓存被清空，只有当你的业务是很长时间才会更新一次的场景，才适合用查询缓存。通过query_cache_type设置为DEMAND来避免使用查询缓存，通过在语句里显式指定SQL_CACHE来使用查询缓存（select SQL_CACHE * from T_table where id = 1;）。
3. 分析器：对于收到的语句进行词法分析，识别关键字后，进行语法分析来判读是否为合法语句。（You have an error in your SQL syntax在这层抛出）
4. 优化器：当表内有多个索引或有join多表时，对于索引和表的连接顺序进行优化。
5. 执行器：首先判断用户是否有权限查询表，InnoDB引擎对于没有索引的where字段，使用遍历表的每一行的该字段来查询语句；对于有索引的表也差不多，通过引擎中已经定义好的接口，来查询满足条件的所有行

7.数据库的锁

• 全局锁：Flush tables with read lock.一般用于全库逻辑备份，官方工具为mysqldump，引擎为InnoDB的，可以在可重复读的隔离级别中实现一致性读，而MyISAM就需要加锁。
• 表级锁：分为表锁（lock tables ... read/write）和元数据锁，一般是不支持行锁的数据库引擎会用到的。执行表锁，不仅仅限制别的线程读写表，也限制本线程操作其他的表。元数据（metadata lock），是访问每个表自动加上的锁，在语句执行时申请，在整个事务提交后再释放，可以在information_schema库内的innodb_trx表中查到当前执行中的事务，为了修改表字段时因为前面有事务没提交导致阻塞，使用 NOWAIT/WAIT N来设置等待时间。对表做增删改查操作时加MDL读锁，读锁不互斥；表结构变更时MDL写锁，互斥。
• 行锁：在InnoDB中，行锁在需要时才加上，等到事务结束之后才释放，称为两阶段协议。如果事务中需要锁多行，要把最可能造成锁冲突和影响并发度的锁申请时机往后放。
• 死锁和死锁检测：死锁是当并发系统不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，会造成都进入无限等待状态。解决死锁的策略：1、设置超时时间iinnodb_lock_wait_timeout；2、发起死锁检测：主动回滚死锁链条中的事务，用innodb_deadloack_detect=on来开启,但会耗费大量的CPU资源。3、控制并发度：对于相同行的更新，在进入引擎之前排队；或者使用中间件.

8.视图

• view
• InnoDB为了实现MVCC（多版本并发控制）时用到的可读性视图，用来支持RC和RR隔离级别的实现。通过唯一的事务id： transaction id来实现，每次事务更新数据的时候，会生成一个新的数据版本，把transaction id复制给这个数据版本的事务ID，同时保留旧版本，对于可重复读来说，记录一个已经提交的事务id的最大值up_limit_id，不认比这个值大的row trx_id，这个旧版本只有当没有事务需要它的时候才会被删掉。一个事务拿到的值在访问一行已经被其他事务更新的数据的时候，会通过undo log重放，算出当前的值；而更新数据是先读后写，这个读是只能为当前读（current read）。
• 可重复读和读已提交最主要的区别是：前者只需要在事务开始时找到那个up_limit_id，之后的查询都共用这个up_limit_id，只承认在事务启动前就已经提交完成的数据；后者是每次执行的时候都会重新拿up_limit_id的值，只承认在语句启动前就已经提交完成的数据。

9. 唯一索引和普通索引：唯一索引每次更新都要判断是否唯一，每次都要将数据页读入内存才行；需要更新一个数据页时，InnoDB会将更新操作混存在change buffer中，普通索引会将更新记录在change buffer中，减少随机磁盘访问，比唯一索引快很多。将change buffer应用到元数据页的过程叫做purge，访问这个数据页会触发purge，后台也会定期执行。

9. binlog：mysql server层的文件，记录的是语句原始逻辑；事务执行过程中，把日志写到binlog cache，事务提交后，把binlog cache写到binlog文件中。参数sync_binlog=0：每次提交都只write cache，不fsync；sync_binlog=N，表示每N的事务后执行fsync，将数据持久化到磁盘。有三种格式：statement、row和mixed（statement和row的混合）：statement可能会导致主备不一致，row不会但很占空间，所以mysql会自己判断后采用mixed格式，但row在恢复数据（使用mysqlbinlog工具解析）场景十分好，一般都用row。

10. redo log：innodb存储引擎层的文件，记录的是修改后的值

三种颜色对应三种状态：1、存在redo log buffer中，当innodb_flush_log_at_trx_commit=0时，每次提交事务只是把redo log留在redo log buffer中；2、存在page cache中，当innodb_flush_log_at_trx_commit=2时，每次都把redo log写到page cache里；3、持久化到磁盘：innodb_flush_log_at_trx_commit=1。

innodb有一个一秒执行一次将redo log buffer中的日志，写到page cache然后调用fsync持久化到磁盘；

11. 两阶段提交：时序上redo log先prepare， 再写binlog，最后再把redo log commit。

12. Mysql如何保持主备一致

主备流程图

master收到客户端的更新请求后，执行内部事务的更新逻辑，同时写binlog；slave和master之间维持一个长连接，完成一个事务日志的同步流程为：

1. 在slave上执行change master命令，设置master信息以及从哪个位置开始请求binlog，包含文件名和偏移量信息。
2. slave执行start slave命令，启动两个线程，为io_thread（建立master连接）和sql_thread。
3. master校验用户名密码后，按照slave传过来的位置，读取binlog发给slave；
4. slave拿到binlog后，写本地ready_log。
5. sql_thread读取ready_log，执行命令。

13. 双master存在的循环复制问题的解决：mysql在binlog中会记录这个命令第一次执行的server id，两个库的server id不能相同，否则不能做主备关系；备库接到binlog重放时，会生成和原binlog相同的server id的新binlog；库收到来自自己主库发过来的日志的时候，会丢弃和自己