谈谈MySQL

一、MySQL基础架构

MySQL逻辑架构

MySQL 包含 Server 层和存储引擎层两大部分。

Server 层包括连接池、查询缓存(8.0弃用)、解析器、优化器、执行器等，MySQL 核心服务都在这一层。

存储引擎层负责数据的存储和提取，MySQL 采用插件式的存储引擎，常见的存储引擎有 InnoDB、MyISAM、 CSV 等。其中 InnoDB 是 MySQL 的默认存储引擎（从5.5.5版本开始）。

MySQL物理组成

日志文件

• 归档日志-binlog

MySQL 重要的日志模块，在 Server 层实现,binlog 以二进制形式，将所有修改数据的 query 记录到日志文件中，包括 query 语句、执行时间、相关事务信息等

可在配置文件中指定存储路径log-bin = /mysql/log/mysql-bin # binlog的存储路径

• 重做日志-redo log

  是存储引擎 InnoDB 生成的日志，主要为了保证数据的可靠性。redo log 记录了 InnoDB 所做的所有物理变更和事务信息。

  redo log 默认存放在数据目录下面，可在配置文件中配置

innodb_log_file_size = 1000M # 每个redo log文件的大小
innodb_log_files_in_group = 3 # redo log文件数量

• 错误日志-error log

  记录 MySQL 每次启动关闭的详细信息，以及运行过程中比较严重的警告和错误信息。错误日志默认是关闭的log-error = /mysql/log/mysql-error.log # 错误日志的存储路径

• 慢查询日志-slow query log

  慢查询日志，记录 MySQL 中执行时间较长的 query，包括执行时间、执行时长、执行用户、主机等信息，慢查询日志默认是关闭的，可在配置文件中配置

slow_query_log = 1 #开启慢查询
long_query_time = 1 #设置慢查询阈值为1s
slow_query_log_file = /mysql/log/mysql-slow.log #设置慢查询日志存储路径

数据文件

.frm 文件存放表相关的元数据，包括表结构信息等

.ibd 文件和 ibdata 文件都是 InnoDB 引擎的数据文件

• 如果是独享表空间的存储方式，则使用.idb文件来存放数据，每张表都会有一个单独的 .ibd 文件。
• 如果是共享表空间的存储方式，则使用ibdata文件来存放数据，所有表共用一个 ibdata 文件。

binlog和redo log的区别

• binlog是逻辑日志，记录某个语句的基本逻辑，即SQL语句；redo log是物理日志，记录在某个数据页所做的修改
• binlog是在MySQL的Server层实现，所有的存储引擎都可以使用binlog这个日志模块；redo log是InnoDB存储引擎特有的日志模块
• binlog是追加写，在写满或重启之后，会生成新的binlog文件，之前的日志不会进行覆盖；redo log是循环写，空间大小是固定的
• binlog 是在事务最终提交前写入的；redo log是在事务执行过程不断的写入
• binlog可以应用于数据归档、主从搭建等场景；redo log作为异常宕机或者介质故障后的数据恢复使用

二、事务

1、事务的特性：原子性、一致性、隔离性、持久性
2、多事务同时执行的时候，可能会出现的问题：脏读、不可重复读、幻读
3、事务隔离级别：读未提交、读提交、可重复读、串行化
4、不同事务隔离级别的区别：
读未提交：一个事务还未提交，它所做的变更就可以被别的事务看到
读提交：一个事务提交之后，它所做的变更才可以被别的事务看到
可重复读：一个事务执行过程中看到的数据是一致的。未提交的更改对其他事务是不可见的
串行化：对应一个记录会加读写锁，出现冲突的时候，后访问的事务必须等前一个事务执行完成才能继续执行
5、配置方法：启动参数transaction-isolation
6、事务隔离的实现：每条记录在更新的时候都会同时记录一条回滚操作。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。
7、回滚日志什么时候删除？系统会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。
8、什么时候不需要了？当系统里么有比这个回滚日志更早的read-view的时候。
9、为什么尽量不要使用长事务。长事务意味着系统里面会存在很老的事务视图，在这个事务提交之前，回滚记录都要保留，这会导致大量占用存储空间。除此之外，长事务还占用锁资源，可能会拖垮库。
10、事务启动方式：一、显式启动事务语句，begin或者start transaction,提交commit，回滚rollback；二、set autocommit=0，该命令会把这个线程的自动提交关掉。这样只要执行一个select语句，事务就启动，并不会自动提交，直到主动执行commit或rollback或断开连接。
11、建议使用方法一，如果考虑多一次交互问题，可以使用commit work and chain语法。在autocommit=1的情况下用begin显式启动事务，如果执行commit则提交事务。如果执行commit work and chain则提交事务并自动启动下一个事务。

脏读

脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读。

可重复读

可重复读指的是在一个事务内，最开始读到的数据和事务结束前的任意时刻读到的同一批数据都是一致的。通常针对数据更新（UPDATE）操作。

不可重复读

对比可重复读，不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE）操作。

幻读

幻读是针对数据插入（INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

事务隔离级别

SQL 标准定义了四种隔离级别，MySQL 全都支持。这四种隔离级别分别是：

1. 读未提交（READ UNCOMMITTED）
2. 读提交 （READ COMMITTED）
3. 可重复读 （REPEATABLE READ）
4. 串行化 （SERIALIZABLE）

从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	可能	可能
读提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行化	不可能	不可能	不可能

三、索引

1.索引的作用：提高数据查询效率
2.常见索引模型：哈希表、有序数组、搜索树
3.哈希表：键 - 值(key - value)。
4.哈希思路：把值放在数组里，用一个哈希函数把key换算成一个确定的位置，然后把value放在数组的这个位置
5.哈希冲突的处理办法：链表
6.哈希表适用场景：只有等值查询的场景
7.有序数组：按顺序存储。查询用二分法就可以快速查询，时间复杂度是：O(log(N))
8.有序数组查询效率高，更新效率低
9.有序数组的适用场景：静态存储引擎。
10.二叉搜索树：每个节点的左儿子小于父节点，父节点又小于右儿子
11.二叉搜索树：查询时间复杂度O(log(N))，更新时间复杂度O(log(N))
12.数据库存储大多不适用二叉树，因为树高过高，会适用N叉树
13.InnoDB中的索引模型：B+Tree
14.索引类型：主键索引、非主键索引
主键索引的叶子节点存的是整行的数据(聚簇索引)，非主键索引的叶子节点内容是主键的值(二级索引)
15.主键索引和普通索引的区别：主键索引只要搜索ID这个B+Tree即可拿到数据。普通索引先搜索索引拿到主键值，再到主键索引树搜索一次(回表)
16.一个数据页满了，按照B+Tree算法，新增加一个数据页，叫做页分裂，会导致性能下降。空间利用率降低大概50%。当相邻的两个数据页利用率很低的时候会做数据页合并，合并的过程是分裂过程的逆过程。
17.从性能和存储空间方面考量，自增主键往往是更合理的选择。

回表：回到主键索引树搜索的过程，称为回表
覆盖索引：某索引已经覆盖了查询需求，称为覆盖索引，例如：select ID from T where k between 3 and 5
在引擎内部使用覆盖索引在索引K上其实读了三个记录，R3~R5(对应的索引k上的记录项)，但对于MySQL的Server层来说，它就是找引擎拿到了两条记录，因此MySQL认为扫描行数是2
最左前缀原则：B+Tree这种索引结构，可以利用索引的”最左前缀”来定位记录
只要满足最左前缀，就可以利用索引来加速检索。
最左前缀可以是联合索引的最左N个字段，也可以是字符串索引的最左M个字符
第一原则是：如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
索引下推：在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。
MySQL5.6引入的索引下推优化，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

什么时候使用索引

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3. 在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
6. 在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。

而下面这些情况不需要使用索引:

1. 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2. 对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3. 对于那些定义为text， image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4. 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

四、锁机制

总结：
根据加锁范围：MySQL里面的锁可以分为：全局锁、表级锁、行级锁

一、全局锁：
对整个数据库实例加锁。
MySQL提供加全局读锁的方法：Flush tables with read lock(FTWRL)
这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句和更新类事务的提交语句等操作都会被阻塞。
使用场景：全库逻辑备份。
风险：
1.如果在主库备份，在备份期间不能更新，业务停摆
2.如果在从库备份，备份期间不能执行主库同步的binlog，导致主从延迟
官方自带的逻辑备份工具mysqldump，当mysqldump使用参数–single-transaction的时候，会启动一个事务，确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。

一致性读是好，但是前提是引擎要支持这个隔离级别。
如果要全库只读，为什么不使用set global readonly=true的方式？
1.在有些系统中，readonly的值会被用来做其他逻辑，比如判断主备库。所以修改global变量的方式影响太大。
2.在异常处理机制上有差异。如果执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为readonly之后，如果客户端发生异常，则数据库就会一直保持readonly状态，这样会导致整个库长时间处于不可写状态，风险较高。
二、表级锁
MySQL里面表级锁有两种，一种是表锁，一种是元数据所(meta data lock,MDL)
表锁的语法是:lock tables … read/write
可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放。lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。
MDL：不需要显式使用，在访问一个表的时候会被自动加上。
MDL的作用：保证读写的正确性。
在对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。
读锁之间不互斥。读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。
MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。

行锁

两阶段锁：在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放， 而是要等到事务结束时才释放。
建议：如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态。
解决方案：
1、通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。
2、发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑（默认是开启状态）。
如何解决热点行更新导致的性能问题？
1、如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用
2、控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了。
3、将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高。

innodb行级锁是通过锁索引记录实现的，如果更新的列没建索引是会锁住整个表的。