后端必会mysql知识_java后端知识点梳理——MySQL

MySQL的索引

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，就像一本书的目录一样，可以加快查询速度。InnoDB 存储引擎的索引模型底层实现数据结构为B+树，所有数据都是存储在 B+ 树中的。

为什么底层数据结构使用B+树，而不是B树？

B+树是B树的变种，B+树的非叶子节点只用来保存索引，不存储数据，所有的数据都保存在叶子节点；而B树的非叶子节点也会保存数据。这样就使得B+树的查询效率更加稳定，均为从根节点到叶子节点的路径。

B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低。

聚簇索引和非聚簇索引有了解吗

聚簇索引也称为主键索引，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑(索引)顺序相同。一个表只能包含一个聚集索引。因为索引(目录)只能按照一种方法进行排序。

非聚簇索引(普通索引)的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引(secondary index)。

MySQL回表：

如果语句是 select * from User where id=3，即主键查询方式，则只需要搜索 主键索引树。

如果语句是 select * from User where uid=23，即普通索引查询方式，则需要先搜索 普通索引树，得到其对应的主键值为 3，再到主键索引树搜索一次。这个过程称为回表。

覆盖索引：

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

索引的最左前缀原则：

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。

索引下推：

在MySQL5.6之前，只能从根据最左前缀查询到ID开始一个个回表。到主键索引上找出数据行，再对比字段值。MySQL5.6引入的索引下推优化，(联合索引前提)可以在索引遍历过程中，对索引中包含的其余字段先做判断，直接过滤掉不满足条件的记录，减少回表次数，提升查询效率。

哪些列上适合创建索引？创建索引有哪些开销？

经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。创建索引需要维护，在插入数据的时候会重新维护各个索引树(数据页的分裂与合并)，对性能造成影响。

自增主键：

为了在插入数据的时候不需要调整主键索引树的结构，强烈建议在建立表的时候使用自增主键。主键的顺序按照数据记录的插入顺序排列，自动有序。

MyISAM与InnoDB 的区别

InnoDB支持事务，MyISAM不支持，对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，(所以最好把多条SQL语言放在begin和commit之间，组成一个事务)；

InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败

InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和(主键)索引绑在一起的(表数据文件本身就是按B+Tree组织的一个索引结构)。 MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的。

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快

那么为什么InnoDB没有了这个变量呢？

因为InnoDB的事务特性，在同一时刻表中的行数对于不同的事务而言是不一样的，因此count统计会计算对于当前事务而言可以统计到的行数，而不是将总行数储存起来方便快速查询。

InnoDB支持表、行(默认)级锁，而MyISAM支持表级锁

InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

InnoDB表必须有主键(用户没有指定的话会自己找或生产一个主键)，而Myisam可以没有

Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

Innodb：frm是表定义文件，ibd是数据文件

Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

Mysql架构及其各个部分概述

连接器：验证客户端权限，建立和断开MySQL连接

分析器：进行SQL语句的语法分析

优化器：选择索引，生成具体的SQL语句执行计划

执行器：操作存储引擎，执行SQL，返回执行结果

存储引擎层：各个不同的存储引擎都提供了一些读写接口来操作数据库

更详细的说：

连接器：负责用户登录数据库，校验身份、校验权限。如果账户名和密码都正确，连接器就会到权限表中查询这个用户的所有权限。之后这个连接里的所有需要判断权限的情况都依赖于此时读到的权限数据。也就是说只要连接不断开，就算此时管理员修改了该用户的权限，也是不受影响的，还是按照之前查询到的权限来判断。

查询缓存：连接建立后，执行查询语句时要先查询缓存。如果缓存中存在这个key，就直接返回给客户端。没有这个key，执行后续的操作，最后会把结果缓存起来。真正执行缓存查询的时候会先校验用户的权限。MySQL8.0之后删除了缓存的功能。

分析器：如果没有命中缓存，就进入分析器，分析SQL语句究竟想干嘛，具体分为两步

词法分析：提取出关键字，如select、表名、字段名、查询条件等

语法分析：判断sql语句的语法是否正确

优化器：确定执行的较优方案，比如多个索引的时候如何选择索引，多表查询的时候如何选择关联顺序

执行器：准备执行sql，执行前会先校验用户权限，没有权限返回错误信息。有权限就去调用引擎接口，返回引擎接口的结果。

MySQL中where、group by、having关键字

where子句用来筛选from子句中指定的操作所产生的的行

group by 子句用来分组where子句的输出

having子句用来从分组的结果中筛选行

having和where的区别：

语法类似，where搜索条件在进行分组操作之前应用；having搜索条件在进行分组操作之后应用

having可以包含聚合函数sum、avg、max等

having子句限制的是组，而不是行

当同时含有where子句、group by 子句 、having子句及聚集函数时，执行顺序如下：

执行where子句查找符合条件的数据

使用group by 子句对数据进行分组

对group by 子句形成的组运行聚集函数计算每一组的值

最后用having 子句去掉不符合条件的组

MySQL的日志模块binlog和redo log

在MySQL的使用中，更新操作也是很频繁的，如果每一次更新操作都根据条件找到对应的记录，然后将记录更新，再写回磁盘，那么IO成本以及查找记录的成本都很高。所以，出现了日志模块，即我们的update更新操作是先写日志，在合适的时间才会去写磁盘，日志更新完毕就将执行结果返回给了客户端。

MySQL中的日志模块主要有redo log(重做日志)和binlog(归档日志)。

redo log：

redo log是InnoDB引擎特有的日志模块，redo log是物理日志，记录了某个数据页上做了哪些修改。InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么redo log总共就可以记录 4GB的操作。从头开始写，写到末尾就又回到开头循环写。

InnoDB的redo log保证了数据库发生异常重启之后，之前提交的记录不会丢失，这个能力称为crash-safe。

binlog：

binlog是Server层自带的日志模块，binlog是逻辑日志，记录本次修改的原始逻辑，说白了就是SQL语句。binlog是追加写的形式，可以写多个文件，不会覆盖之前的日志。通过mysqlbinlog可以解析查看binlog日志。binlog日志文件的格式：statement，row，mixed。

statement格式的binlog记录的是完整的SQL语句，优点是日志文件小，性能较好，缺点也很明显，那就是准确性差，遇到SQL语句中有now()等函数会导致不准确

row格式的binlog中记录的是数据行的实际数据的变更，优点就是数据记录准确，缺点就是日志文件较大。

mixed格式的binlog是前面两者的混合模式

业界目前推荐使用的是 row 模式，因为很多情况下对准确性的要求是排在第一位的。

在更新数据库的时候，通过redo log和binlog的两阶段提交，可以确保数据库异常崩溃之后数据的正确恢复。

在对数据库误操作之后，可以通过备份库+binlog可以将数据库状态恢复到“任意“时刻。

为什么MySQL会突然变慢一下？

当内存数据页和磁盘数据页内容不一致的时候，这个内存页就是“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，这个时候的内存页就是”干净页“。

前面我们介绍了更新数据库的时候是先写日志，当合适的机会(空闲)出现的时候才会更新磁盘。但是当redo log 写满了，要 flush 脏页，也就是把内存里的数据写入磁盘，会导致MySQL执行速度突然变慢一瞬间。

MySQL事务

ACID

原子性(Atomicity)：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；

一致性(Consistency)：保证数据库状态从一个一致状态转变为另一个一致状态；

隔离性(Isolation)：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；

持久性(Durability)：一个事务一旦提交，对数据库的修改应该永久保存

多个事务并发操作数据库会产生哪些问题

丢失更新：两个不同事务同时获得相同数据，然后在各自事务中同时修改了该数据，那么先提交的事务更新会被后提交事务的更新给覆盖掉，这种情况先提交的事务所做的更新就被覆盖，导致数据更新丢失。

脏读：事务A读取了事务B未提交的数据，由于事务B回滚，导致了事务A的数据不一致，结果事务A出现了脏读；

不可重复读：一个事务在自己没有更新数据库数据的情况，同一个查询操作执行两次或多次得到的结果数值不同，因为别的事务更新了该数据，并且提交了事务。

幻读：事务A读的时候读出了N条记录，事务B在事务A执行的过程中增加 了1条，事务A再读的时候就变成了N+1条，这种情况就叫做幻读。

注意：幻读是指一种结构上的改变，比如说条数发生了改变；不可重复读是指读出的数值发生了改变。

隔离级别

读未提交(Read Uncommitted)：允许脏读取。如果一个事务已经开始写数据，则另外一个数据则不允许同时进行写操作，但允许其他事务读此行数据。

读已提交(Read Committed)：允许不可重复读取，但不允许脏读取。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。

可重复读(Repeatable Read)：禁止不可重复读取和脏读取，但是有时可能出现幻读。读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务。

序列化(Serializable)：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。

事务的隔离级别越高，对数据的完整性和一致性保证越佳，但是对并发操作的影响也越大。MySQL事务默认隔离级别是可重复读。

MySQL建表的约束条件

主键约束(Primay Key Coustraint) 唯一性，非空性

唯一约束 (Unique Counstraint)唯一性，可以空，但只能有一个

检查约束 (Check Counstraint) 对该列数据的范围、格式的限制

默认约束 (Default Counstraint) 该数据的默认值

外键约束 (Foreign Key Counstraint) 需要建立两表间的关系并引用主表的列

锁机制

MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看，行级锁又可以分为独占锁和共享锁。

独占锁(排他锁)，也称X锁(Exclusive Lock)：

独占锁锁定的资源只允许进行锁定操作的程序使用，其它任何对它的操作均不会被接受。执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。

在select命令中使用独占锁的SQL语句为：select … for update;

共享锁，也叫S锁(Shared Lock)：

共享锁顾名思义，那就是其锁定的资源可以被其它用户读取，但其它用户不能修改。如果在select查询语句中要手动加入共享锁，那么对应的SQL语句为：select ... lock in share mode

这里需要注意：

一个事务在一行数据上加入了独占锁，那么其余事务不可以在该数据行上加入任何锁。也就是说加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

死锁

MySQL中的死锁主要是多个事务使用行级锁对某行数据加锁造成的，上一小节说了MyISAM不支持行级锁，所以MySQL中的死锁主要是在说InnoDB存储引擎的死锁。

业务逻辑上的死锁解决方案：

指定锁的获取顺序

大事务拆分成各个小事务

在同一个事务中，一次锁定尽量多的资源，减少死锁概率

给表建立合适的索引以及降低事务的隔离级别等

数据库的设置来解决死锁：

通过参数 innodb_lock_wait_timeout 根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。

发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑(默认是开启状态)。

行级锁什么时候会锁住整个表？

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

悲观锁与乐观锁：

从程序员的角度看，数据库中的锁又可以分为悲观锁和乐观锁。

悲观锁：利用数据库的锁机制实现，在整个数据处理过程中都加入了锁，以保持排他性。

乐观锁：乐观锁可以利用CAS实现，在操作数据的时候进行一个比较，按照当前事务中的数据和数据库表中的该数据是否一致来决定是否要执行本次操作。

乐观锁的ABA问题

ABA问题是指在当前事务读取该行数据时是A，经过别的事务修改成B，但是在当前事务要更新数据的时候，该行数据又被别的事务修改为A，事实上数据行是发生过改变的，存在并发问题。

ABA问题可以通过基于数据版本(Version)记录机制来解决。也就是为数据增加一个版本标识。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。根据当前事务的数据版本号和数据库中数据的版本号对比来决定是否更新数据。

与给当前数据增加一个数据版本类似，我们也可以增加基于时间戳机制来解决ABA问题，通过时间戳来记录当前数据行变化。