面试题
MySQL架构的了解
MySQL的基本架构分为两个部分：Server、存储引擎层
Server层主要有连接器、分析器、优化器、执行器
说说innoDB和MyISAM的区别
innoDB中锁的粒度最小可以到行锁，而MyISAM只能支持表锁
InnoDB支持事务，MyISAM不支持事务
InnoDB采用聚簇索引，而MyISAM采用的非聚簇索引
InnoDB支持外键，MyISAM不支持外键
InnoDB读效率比MyISAM低，但写效率比较高
聚簇索引和非聚簇索引
聚簇索引，表中的数据存放在主键索引的叶节点上。找到索引就找到了数据（所以InnoDB中的表都需要设置主键，如果不设置主键，mysql会将表中的唯一索引做为主键索引，如果还不满足条件就会生成默认的主键索引）。
非聚簇索引，索引和数据是分开存放的。
为什么使用B+树做为索引，它相比B树的优势
B+树的非叶子节点是不存放数据的，只存放key，而B树所有节点都会存放数据。B树会出现一个问题就是数据量如果大了每层的节点数就会比较少从而导致树比较高。而索引中树每一层都会涉及到一次IO，所以B树的查询效率相对较低。并且B树因为数据遍布在每一层，每次查询时间不同有些很长有些很短。而B+树的查询时间很稳定。
UNION和UNION ALL的区别?
两个的作用都是合并结果集，union会去掉结果集中重复的数据项（效率较低），而union all不会去重直接进行合并
Left Join和Inner Join
left join是指以左表为主，查询结果是左表中所有数据，并且是和右表中数据有交集的部分（若左表有，右表中无的部分也会查询出来），inner join查询的是两个表的交集部分。
Mysql事务的隔离级别
事务隔离级别有四种：①读未提交 ②读已提交 ③可重复读 ④可串行化
读未提交：事务可以读取其他事务未提交的数据。这个级别会出现脏读，不可重复读,幻读
读已提交：事务只能读取其他事务已经提交的数据。这个级别会出现不可重复读，幻读，但不会出现脏读。
可重复读：事务期间多次读取同一行数据，数据保持一致。（Mysql的InnoDB采用的就是可重复读的事务隔离级别，基于MVCC实现的）
可串行化：最高的事务隔离级别，所有的事务依次执行
MVCC多版本并发控制
MVCC是MySQL用来解决事务隔离的，MVCC用于读已提交和可重复读两种隔离级别。MVCC是一种基于快照读的方式实现的无锁并发控制，每行数据都有两个隐藏的字段当前事务的ID和回滚指针。针对一行数据来说每个事务能看到的数据版本是不同的，同一行数据有不同版本是依靠undolog+readview（记录当前活跃事务ID、创建该事务ID、最小事务ID、最大事务ID）实现的，在可重复读的隔离级别下，每个事务开启后第一次查询时会生成一个readview并且只生成一次，而在读已提交的隔离级别下事务每次select都会生成新的readview，所以这种情况下是可以看到其他事务提交的新数据的。而可重复读的状态下因为事务ID不会变化所以不会看到后来的事务修改的数据。
MVCC是否解决了幻读
MVCC没有完全解决幻读
-
MVCC基于快照实现了快照读的幻读问题
-
当前读是通过间隙锁实现的（select * from table for update这种语句就会去获取锁）
间隙锁只有在事务隔离级别 RR 中才会产生；
唯一索引只有锁住多条记录或者一条不存在的记录的时候，才会产生间隙锁，指定给某条存在的记录加锁的时候，只会加记录锁，不会产生间隙锁；
普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；
普通索引的间隙，优先以普通索引排序，然后再根据主键索引排序(多普通索引情况还未研究)；
事务级别是RC(读已提交)级别的话，间隙锁将会失效。
关系型数据库和非关系型数据库的区别
关系型数据库数据的存储一般采用表结构来存放数据。非关系型数据库对数据的存储有K-V键值对、图、文件等。
关系型数据库水平扩展相对复杂，而非关系型数据库因为本身基于键值对
redolog和binlog的区别
- redolog是innodb独有的，binlog是mysql提供的
- redolog是物理日志记录的是某个数据页上的修改，binlog是逻辑日志记录的是mysql的原始语句
- redolog是循环写的，它实际是一个循环队列。而binlog是全量日志是通过追加写的方式
- redolog的作用是为了提升mysql的写能力并且实现断电恢复功能
为什么说redolog具有断电恢复而binlog不能
因为binlog是一种全量日志，它采用的追加写的方式。而redolog采用的是循环写，redolog满了之后会进行刷盘操作，这时日志中记录的操作都会被持久化到磁盘上。也就是说数据库在断电重启后，只需要将redolog中记录的操作重写执行就可以了。而binlog因为记录的是所有日志，数据库重启后不知道哪些操作是已经落盘了的，哪些操作是还没有执行的。
MySQL limit查询关键字优化
Mysql-Limit 优化 - 小白个人成长记 - 博客园 (cnblogs.com)
mysql中limit 20，10 的意思是从满足条件的第20条开始到第30条，limit数字小时查询效率不会受影响，但是对于 limit 20000,10 来说，即使有索引数据库也会从第一条符合条件的数据开始扫描20000条数据再丢弃掉最后取得20000到20010条数据
优化手段（尽量获取到第20000行数据的id，加入where条件使得数据库从后面开始扫描）
SELECT * FROM xxx WHERE ID > =(select id from xxx limit 1000000, 1) limit 20;
ACID是CyclicBarrier靠什么来保证的
ACID分别是原子性、一致性、持久性、隔离性
原子性：MySQL中依靠undolog来实现原子性，通过记录回滚日志来实现，事务回滚时就通过日志来撤销操作
隔离性：MVCC来实现隔离性
持久性：redolog和binlog来实现持久性
一致性：前面三种的总和保证了一致性
lock in share mode 和 for update
lock in share mode是加共享锁，for update 是加排它锁。
MySQL为什么需要主从同步
-
主从同步可以用来实现读写分离，读写分离后提高了并发访问的效率
-
数据备份
MySQL执行计划怎么看
使用explain+sql语句可以看到数据库对这个sql的具体执行过程的信息
type字段标识当前这个sql以什么方式获取数据，尽量避免使用全表扫描
- all：全表扫描
- index：全索引扫描
- range：在索引中值扫描一定范围
sql尽量保证在range级别
key表示使用的哪个索引
rows表示预估扫描的值
extra表示额外信息
数据库中索引类型有哪些
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 组合索引
如何处理MySQL慢查询
- 开启慢查询日志：设定慢查询的时长
- 使用explain分析sql语句
- 考虑拆分表（横向、纵向拆分）
如何设计索引
- 适合索引的列一般是where语句或者链接字句中指定的列
- 表比较小的话一般不创建索引
- 更新频繁的字段最好不要创建索引
- 存在外键的字段一定要创建索引
- 大对象的字段最好不使用索引
- 充分利用组合索引
存储引擎
MyISAM
myisam是mysql5.5之前的默认存储引擎
-
不支持事务
-
不支持行锁
-
索引采用的非聚簇索引（索引和数据分开存放）
-
不支持热备份且数据修复较慢
-
缺点：不支持事务、数据修改效率低，不支持行锁
InnoDB
myisam是mysql5.5之后的默认存储引擎
- 支持事务（默认实现了事务隔离里面的可重复读的隔离级别）
- 支持表锁和行锁（InnoDB的行锁是针对索引实现的，只有通过where条件使用索引时才能使用行锁，否者使用表锁）
- 采用聚簇索引
- 支持热备份，数据修复块
- 缺点：查询效率没有MyISAM高，行锁可能会产生死锁
MyISAM和InnoDB的区别
MySQL5.5版本之前使用MyISAM做为默认存储引擎，5.5之后使用InnoDB做为默认存储引擎
| MyISAM | InnoDB | |
|---|---|---|
| 索引类型 | 非聚簇索引 | 聚簇索引 |
| 支持事务 | 否 | 是 |
| 支持表锁 | 是 | 是 |
| 支持行锁 | 否 | 是 |
| 支持外键 | 否 | 是 |
| 适合操作类型 | 增删改较少的情况 | 增删改较多的情况 |
InnoDB和MyISAM对于count(*)的效率的比较
MyISAM为每个表都维护了一个总行数的字段（磁盘上），使用count（*）时效率很高，当然如果count(*)配合了where条件筛选MyISAM效率也会降低
InnoDB执行count(*)时只能将数据一行一行的从引擎中读取出来进行累加，之所以InooDB不对总行数进行存储的原因是：InnoDB中采用了MVCC导致一个时刻同一行数据可能有多行，而每个事务可见的条数也是不同的。
Memory
Memory存储引擎是基于内存的存储引擎，底层采用哈希表做为存储数据结构。适合拿来做查询中间集的临时表
缺点
- 因为低层是基于内存的哈希表结构，它是不支持范围查找。
- 并且只支持表锁，并发性能较低
- 不支持Blog、Text类型的数据
索引
优点：索引的作用：加快数据访问
缺点：索引并不是越多越好，索引本身的维护也会浪费空间和时间，数据量大时才使用。
索引底层数据结构
Hash
数组+链表的形式
缺点：利用hash存储的话，需要将所有的数据文件添加到内存中，比较耗内存空间，hash范围查找困难
二叉搜索树
每个结点的左结点小于结点的值，右节点大于结点的值
缺点：根节点的值不平均时查找效率降低
AVL树
AVL树是一种自平衡的二叉搜索树
它的任何结点的左右子树高度差不会超过1
增加和删除结点时，会通过旋转来平衡这个树。
旋转平衡过程参考博客
详细图文——AVL树_带翅膀的猫的博客-CSDN博客_avl树
B树
B+树
InnoDB中的B+Tree和MyISAM中的B+Tree的区别
InnoD中的B+Tree，上层索引和叶节点的数据时是存放在一起的，而MyISAM中上层索引和叶节点的数据是分开存放的，叶节点存放的是数据的地址，需要再进行一次IO读取具体的叶节点数据。分开存放的缺点是查询是需要多进行一次IO。
如果创建索引的字段不是主键，此时会有两个B+树，一个是各节点存放主键的树，另一个棵树存放创建索引的字段（这颗树的叶节点不存放具体的数据，存放的是主键ID），根据非主键索引查询时是分为两个步骤的，①在非主键树上获取到对应的主键Id，再根据主键id在主键树上获取真实的数据。
索引类型
mysql中索引的主要分类有五种
主键索引、唯一索引、普通索引、全文索引、组合索引
主键索引
主键是一种唯一性索引，每个表只有一个主键索引。InnoDB中数据都直接和主键存放在一起。所以当使用InnoDB时我们不主动设置主键，数据库也会生成一个默认的主键索引
唯一索引
索引列的所有值只能出现一次，值可以为空
普通索引
基本的索引类型，索引值没有唯一限制
普通索引和唯一索引的选择
查询
- 针对查询效率来说，普通索引在查询时因为索引字段不唯一，需要对每条符合条件的索引进行回表，直到字段不符合条件为止
- 唯一索引因为字段唯一，所以只需要找到第一个满足条件的索引即可相对普通索引查询效率较高
- 但因为索引是以数据页形式读入内存（一个数据页大小为16kb，一般存放近千条数据）所以普通索引在查询时效率并不会低很多
- 总结：普通索引的查询效率比唯一索引略低
更新
- 针对更新效率来说，普通索引可以使用changebuffer来优化查询效率，更新数据时只需要将更新语句放入changebuffer中即可
- 而对于唯一索引来说因为每次修改数据时需要对索引进行唯一性约束的判断，没有办法使用changebuffer来优化更新效率。
- 总结：普通索引因为可以使用changebuffer来优化，所以更新效率比唯一索引要高
总结：优先使用普通索引
全文索引
创建在varchar、char、text类型上的索引
组合索引
多列字段共同组成的索引，字段的匹配顺序按创建索引时的字段顺序。
前缀索引
一般前缀索引要选择区分度较高的前缀，前缀索引相比普通索引的优势就是节省空间
但因为查询数据时，前缀索引不能确定字段的完整数据，每次都需要回表，会增加查询扫描的次数，并且因为字段不完整，不能使用覆盖索引。
索引优化
最左匹配
最左匹配：根据索引的顺序去查找，用在组合索引上，按顺序匹配。当某个索引字段没有时，索引失效
例如我们创建了一个name、age的联合索引。数据库在查找时是先根据name的条件查找，再从中根据age查找。
select * from t1 where name='张三';
select * from t1 where name='张三' and age=10;
select * from t1 where age=10;
select * from t1 where age=10 and name='张三';
对于上诉四条sql，1 2 都是符合最左匹配，数据库会先查找name符合条件的再进行筛选。
第3条不符合最左匹配，因为缺少了先决条件。而第四条mysql中有相应的优化，最后也会符合最左匹配
回表
mysql覆盖索引与回表 - 简书 (jianshu.com)
在InnoDB中采用的聚簇索引，这种索引的特点是数据和主键存放在一棵树上，普通索引、唯一索引、组合索引等索引的B+树上只会存放主键id。
例如：存在普通索引name、和主键索引id
select * from where name='sy';
这种sql就存在回表，即mysql会先查到普通索引name的B+树上获取到主键id，再通过主键id到主键索引的树上进行查询最后回去到最后的数据。
覆盖索引
select id from where name='sy';
对于上诉那个例子，如果查询的结果可以在一个索引树上 就获取到，就称为覆盖索引。 减少了回表的次数。这也是sql查询优化的主要途径
索引下推
普通索引中，我们使用条件查询时如果直接在当前索引中对不符合条件的索引进行过滤减少回表次数。
SELECT * from user where name like '陈%' and age=19;
非主键索引结构为
| name | age | id |
|---|---|---|
| 陈某 | 12 | 1 |
| 陈某某 | 19 | 2 |
| 陈某某某 | 23 | 3 |
在没有索引下推时，mysql每次查到一行数据后都会重新去主键索引上获取数据并判断，此时回表次数达到3次
有索引下推时，mysql在当前索引就会判断age是否符合减少回表次数，此时回表次数只有1次。
Mysql性能优化：什么是索引下推？ - 知乎 (zhihu.com)
重建索引
在InnoDB中，删除一行数据或者删除整个表的数据，数据库只会将对应的数据标记为可复用，不会对数据进行真实的删除，用户后续的复用。但这样就会造成我们删除了表中的数据但表占用的大小没有发生变化的情况。
解决办法
- 使用全局的表重建
# 这时可以使用如下语句来实现表重构
# 重构过程类似新建一个B+树，将原来树中的所有有效数据重新放入B+树
# 再将新的树作为表的树
alter table A engine=InnoDB
上面这种语句在执行期间不能再对表进行DDL，否者会造成数据丢失
-
5.5之后采用了OnlineDDL技术
在重建表期间允许用户线程执行数据库的DDL，并将期间产生的语句存入日志中，最后再将日志中的语句同步到新的索引中。一开始执行重建时会获取MDL写锁，随后在进行复制时，降级为MDL读锁。
SQL执行
SELECT
MySQL隐式转换问题
mysql中当字符串涉及到 +、=、-、/ 时都会进行隐式转换
比如下面这个SQL
select '1234aaa'=1234
因为后面是数字1234，mysql在比较时会将’1234aaa’转换成浮点数，并且非数字部分默认为0，这里自动转换成了浮点数1234来进行比较。
不同count的区别
MySQL-count(1)、count(*)与count(列名)的执行区别 - simple_孙 - 博客园 (cnblogs.com)
count(*)、count(主键id)、count(1)都返回的是当前表中所有的行数
而count(字段)则返回的是所有该字段不为null的行数
效率
对于count(*)来说，mysql会选择一个最小的索引来进行计数
对于count(1)来所，mysql会对主键索引进行遍历，每扫描到一行就返回给server层1，server层进行累加
对于count(id)来说，mysql会对主键索引进行遍历，每扫描到一行就将该行的主键字段返回给server层进行累加
结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。
INSERT
INSERT的几种插入方式
普通插入
普通插入语句
insert into table_name(xx,xx,,xx) value(xx,xx,xx)
插入或更新
插入数据时如果遇到重复数据就放弃插入改为更新原来的数据
INSERT INTO table_name (total_amount)
VALUES (30)
ON DUPLICATE KEY UPDATE total_amount=total_amount + 30;
插入或忽略：插入数据时如果遇到重复数据就忽略这个插入
插入或替换
插入数据时如果遇到重复数据就删除原来的数据，插入新的数据
replace insert table_name(xx) value(xx)
replace有个问题是如果表中有多个唯一索引时，且新插入的数据造成了不止一个字段上的重复索引。将会删除多条数据
例如表中有三个字段且均有唯一约束 a , b , c 并且表中如下结构
| a | b | c |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
新插入一条数据
REPLACE INTO table1(a, b, c) VALUES(1,2,3);
执行结束后，因为造成了三个字段的重复索引，原来表中的三条数据都会被删除
插入或忽略
插入数据时如果遇到重复数据，就忽略这条插入
insert ignore into table_name(x) value(x)
批量插入
- 使用insert values实现批量插入（insert values插入的数据是有限的，如果数据量太多可以使用分批次批量插入）或者通过设置mysql的参数来扩大批量插入的最大数据容量
- 关闭其他进程
- 关闭唯一性检查，因为mysql插入数据时需要对数据进行唯一性校验这会降低插入数据的速度。我们可以先禁用唯一性检查，等数据插入完毕后再开启。
- 关闭自动提交
DELETE
MySQL删除数据的方式有哪些
常用的删除方式有：drop、truncate、delete
执行效率上来讲，drop>truncate>delete
因为delete需要一条一条的删除数据，并且删除是会触发事务和记录日志（delete删除数据不会真的将数据删除，而是将数据标记为不可见，只是在后面插入数据时可以覆盖这部分数据）。而truncate和delete都不会触发事务和记录日志，数据删除后不可恢复。truncate比delete慢的原因是truncate相当于先执行drop再执行建表语句，所以要慢一点。
Drop
drop是删除整张表，包括表结构
Truncate
truncate是删除整个表的数据，不删除表结构，并且重置计数器
Delete
使用delete来删除所有数据不会删除表的结构，也不会重置计数器。
SQL语句的执行流程
mysql中主要分为两层：Server层和存储引擎层
Server层主要负责数据库用户链接、sql语句分析、sql语句优化、binlog日志等
存储引擎主要是负责存储、读取数据
Server层中的几大组件
- 连接器
- 负责对用户权限的认定
- 分析器
- 分析sql语句的正确性
- 优化器
- 优化sql语句（例如最左匹配的优化）
- 执行器
- 鉴定用户是否具有权限
- 执行sql语句（调用）调用存储引擎的接口
一条select语句的执行流程
-
连接器校验用户身份
-
分析器分析语句是否符合语法
-
查看缓存中是否能命中（缓存中存放的是key-value,key是查询语句，value是返回结果）。命中则直接返回(MySQL8开始移除了缓存机制)
-
优化器对语句进行优化（选择合适的索引）
-
执行器将调用引擎的接口进行查询（执行前校验该用户是否具有操作权限）
一条update语句的执行流程
update T set money = money + 500 where username = '陈哈哈';
- 连接器对用户身份进行认证
- 分析器分析语法是否正确
- 优化器对sql语句进行优化
- 执行器调用引擎去找到符合修改条件的那行数据，如果数据页已经存在内存则直接返回给执行器，若没有则去磁盘中将数据读出放入内存并返回给执行器
- 执行器将数据修改再调用引擎写入新数据
- 写redo log（引擎来决定是否直接落盘还是写到内存中）（两段提交来保证日志一致性：prepara阶段）
- 执行器写binlog并同步到磁盘
- 修改redolog的状态为提交状态
Order By的工作流程
MySQL中主要有两种排序方式：全字段排序、rowid排序
全字段排序
数据库对数据进行排序的流程
以如下sql为例
select city,name,age from t where city='杭州' order by name limit 1000 ;
假设在city字段上建立索引
-
在city索引上找到所有city为杭州的数据（通过回表取出city、name、age）
-
在内存中建立sort_buffer
-
将所有符合条件的数据放入sort_buffer中，再进行快排
如果此时sort_buffer的空间不足，将会使用磁盘作为辅助
此时外部的磁盘排序算法使用的是归并排序
rowid排序
当我们返回的
如果单行数据过大时
如果单行数据量过大，mysql会采用另外一种算法，即确定要排序的字段有哪些。第一次只将要排序的字段和主键取出放入sort_buffer中，排序结束后根据要返回数据的行数再回主键索引树上将数据直接返回给客户端。
事务
概念
事务是指一系列的操作，这一系列操作要么全部执行，要么全部不执行。
事务特性
事务有四大特性-ACID
原子性
事务要么全部执行，要么全部不执行
原子性依靠mysql中的undolog，mysql会在执行写入操作时先记录对应的日志。如果事务失败后需要回滚，就通过执行undolog中的日志来解决。
一致性
事务执行前后，数据整体不会发生变化。
mysql的一致性是依靠 redo log+binlog(归档日志)
隔离性
事务与事务之间互不干扰
依靠读写锁+MVCC来实现的
持久性
事务执行后的结果是持久的
并发事务
脏读
事务读取到其他事务修改但为提交的数据
幻读
事务在读取数据时发现表中多出了部分数据
不可重复读
事务多次读取同一数据时，数据不一致。
幻读和不可重复读的区别
幻读主要是指的数据的增加，不可重复读指的是数据的修改和删除。
丢失修改
两个事务对数据进行修改时，事务一修改后先提交，事务二提交时会覆盖事务一的数据
事务隔离级别
参考资料：Innodb中的事务隔离级别和锁的关系 - 美团技术团队 (meituan.com)
Mysql中的InnoDB默认采用的事务隔离级别是可重复读，大部分数据库采用的是读取已提交的隔离级别。
读取未提交
最低的隔离级别，事务可以读取其他事务未提交的数据。
存在脏读、幻读、不可重复读的问题
读取已提交
事务可以读取其他事务已提交的数据
可以阻止脏读，但存在幻读和不可重复读的问题
可重复读
事务对同一个字段的多次读取结果一致，除非是被事务本身修改的
阻止脏读和不可重复读，但仍存在幻读。
MySQL中默认事务级别就是可重复读，其实现原理MVCC
MYSQL中的乐观锁实现(MVCC)简析 - pautcher - 博客园 (cnblogs.com)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sSWqYkBX-1677676470590)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20211009164458254.png)]
InnoDB基于MVCC实现的可重复读并不是一定没有不可重复读的风险
下图可以看到，事务B在两次读的过程中数据不匹配，这是因为mysql更新数据时
当前读机制当前读是指mysql每次更新数据时都需要先读再写，此时读的数据一定是最新值
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GLh8uIMB-1677676470591)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20211216160858701.png)]
可串行化
事务之间采取串行执行
不存在任何问题
MVCC多版本并发控制
MVCC多版本并发控制是指同一行数据在数据库中可以有多个版本不同的数据，事务之间根据数据的版本号来决定该数据是否对自己可见。每行数据都有一个创建事务的ID和删除事务ID。而每个事务都有自己的版本号并且版本号是递增的。
MVCC是基于快照读实现的，没有使用锁来提高了并发效率。在
mysql为每张表都生成两个默认字段：创建事务ID、删除事务ID。每个事务开启时都有自己唯一的事务ID，并且是递增的。底层是通过undolog来实现对于同一行数据有多个版本的
SELECT
只有同时满足两种情况的数据才能在当前事务中可见
- 如果数据的创建事务ID等于当前事务ID或者数据的事务ID小于当前最小活跃事务ID
- 并且删除事务大于当前事务ID
DELETE
将数据的删除事务ID保存为当前事务的ID
INSERT
插入一行数据，设置创建事务ID为当前事务ID
UPDATE
复制并插入一行新数据，设置创建事务ID为当前事务ID，设置原来数据的删除事务ID为当前事务ID
MySQL如何解决幻读的
MVCC能否解决幻读 - xuwc - 博客园 (cnblogs.com)
并发场景下锁的使用
Select for update使用详解 - 知乎 (zhihu.com)
两种方案
悲观锁
事务开启后，先查询剩余数量使用select … for update语句对数据行加锁
然后再执行update语句，不过这种方案有个缺点是会阻塞其他访问该记录的事务，适用于写入频繁的场景。
for update默认使用行锁，但使用不当会导致锁表。并且
乐观锁
每次获取商品时不对其进行加锁，采用CAS的方式来修改
MySQL中的锁
全局锁、表锁、行锁、排它锁、共享锁
全局锁
全局锁是对整个数据库实例进行加锁，上锁期间数据库处于只读状态，所有DDL语句会进入阻塞状态。
作用
一般用来做数据库备份时使用，但是在InnoDB这种引擎中可以使用事务+一致性视图来进行数据备份，就不需要再加全局锁了
表锁
表锁主要有两种一种是普通的表锁另一种是正对表结构的元数据锁
普通表锁
对表中数据进行操作时会进行上锁（MyISAM只支持表锁）
innoDB在行锁失效时也会选择使用表锁
在update、delete、insert语句中默认会进行加锁
元数据锁MDL
MDL锁有两种：MDL读锁、MDL写锁
DML语句在执行时会默认加MDL读锁，而DDL语句会加写锁。读读之间不互斥，读写互斥
行锁
行锁是InnoDB引擎中独有的锁粒度，事务可以只对表中的某一行数据进行加锁，但是因为InooDB中的行锁是通过索引实现的，所以当查询语句不通过索引时就没有办法获取行锁，此时会采用表锁。
行锁有两种锁：排它锁(for update)和共享锁(lock in share mode)
表锁的缺点
会产生死锁，事务A、B开启后，事务A更新id为1的数据（此时事务A会获取到id=1的行锁），事务B更新id为2的数据（此时事务B会获取到id=2的行锁），事务A尝试对id=2的数据进行修改，而事务B尝试对id=1的数据进行修改，又因为两阶段锁协议导致事务只能在提交时才会释放所有锁。所以两个事务会进入死锁状态。
两阶段锁协议
事务A和事务B在执行时，A在需要锁时获取对应的行锁。执行结束后并不会直接释放，而是要等到事务提交时才会释放，此时事务B会被阻塞。
所以我们将最容易造成事务冲突的语句尽量往后放，减少冲突等待时间
死锁问题
两个事务互相持有对方的锁时就会出现死锁状态。
MySQL中解决死锁的策略有两种
超时释放
当事务等待超时时直接释放掉当前持有的锁资源，这个等待时间是由参数 innodb_deadlock_detect s设置的，默认时间是50s
缺点：时间设置太长没有意义，设置太短很容易误伤事务
主动锁检测
当检测到死锁时直接回滚一个事务。MySQL默认开启的是主动死锁检测
缺点：主动检测的消耗比较大，针对每一个事务都需要检测是否与其他事务冲突
针对死锁检测的缺点的解决办法：控制并发度（减少同一时间来操作同一行代码的线程）、关闭死锁（不推荐）
日志
mysql中主要有三个重要的日志
- redolog（重做日志）
- undolog（回滚日志）
- binlog（归档日志）
redolog
redo log是存储引擎层面的日志，是InnoDB特有的日志。redolog是循环写的（会覆盖），已经刷盘的数据不会再保留日志。redolog是针对磁盘写的
InnoDB为了解决每次更新数据都需要操作磁盘的问题，采用的重做日志。当需要修改数据时，InnoDB不会立刻去对磁盘进行修改，而是将修改的语句记录到一个redolog中。
WAL机制
先更新内存再更新磁盘
redolog同步磁盘的时机
mysql需要将redolog中更新操作同步到磁盘中才能保证数据的一直性，而redolog落盘的时机有如下4种情况
-
当redolog满了就会对日志进行落盘，redolog本身是一个循环队列，当队列慢了时就会触发落盘操作
-
系统内存不足时mysql会淘汰部分数据页出去，当淘汰的数据页是脏页时就需要对redolog中对应部分的数据进行落盘处理
-
MySQL认为系统为空闲状态时也会进行落盘操作
-
MySQL正常关闭时
MySQL系统抖动问题
在redolog进行更新到磁盘时，可能会出现系统抖动问题
mysql会根据innodb_io_capacity这个参数来决定刷脏页的速度，一般这个值最好和系统的iops值相同。mysql会根据当前脏页的笔迷来计算出刷脏页的速度（默认是75%）。并且mysql在刷脏页时会有个脏页蔓延的机制，即当需要刷的脏页数据旁边也是脏页时会连带一起进行刷脏页（这个策略由innodb_flush_neighbors参数控制）不过在mysql8.0开始这个机制默认是关闭的，因为这可能会导致查询速度变得更慢。
两阶段提交
redolog的提交是分两段的，数据库会先提交一次redolog，数据库在更新数据时会直接对内存进行更新，并且写入redolog，此时redolog处于prepare状态。
再写binlog，binlog写完后再将事务进行提交，并且将redolog置为commit状态。
两段时提交的崩溃恢复
当在时刻A数据库崩溃，恢复时由于只有redolog，没有binlog数据库会放弃这个更新语句
当在时刻B数据库崩溃，恢复时由于有redolog和binlog，只不过此时redolog状态为prepara状态，此时数据库会将redolog的状态置为commit状态。
binlog
binlog是Server层面的日志，是mysql中公用的日志。binlog是追加写的（不会覆盖）。binlog是记录所有的逻辑日志。
binlog也是MySQL中的主从复制的关键
undolog
事务的每个数据变更都会生成一个日志，当事务需要回滚时只需要逆向执行就可以了。
changebuffer
changebuffer的作用为了减少数据页读取的次数，当需要更新数据时，如果数据页在内存中则直接更新，否者需要将数据页读取到内存中再进行更新，而changebuffer的作用就是将更新的动作保存下来，等到数据页被加载内存时才将changebuffer中的更新语句全部执行。
changebuffer中的更新语句积攒的越多，changebuffer的优化效果就越好。一般适用于写多读少的场景
chengebuffer本身是进行了磁盘持久化的，防止changebuffer中的数据丢失。
主从复制和读写分离
主从复制
主从复制是指将原本的一个数据库配置一个或多个从数据库，数据库的读写操作还是作用到主数据上。从数据库读取主数据库记录的Binlog日志来保持两个数据库中的数据一致性。这样即时主数据库宕机了，从数据库也能继续使用。
从库会一定时间间隔内探测主库的Binlog是否发生变化
复制过程
- 从数据库一定间隔时间去探测主数据库的Binlog是否发生改变
- 如果发生改变主数据库会开启一个dump线程，从数据库开启两个线程：I/O线程和SQL线程
- 从数据库I/O线程读取binlog并写到从库的中继日志上
- 从库SQL线程读取中继日志并执行
读写分离
通过实现主从分离后，我们需要对数据库进行写时就操作主数据库，遇到读操作时就使用从数据库，这样可以减少数据库的读写压力。
数据库优化
SQL优化
面试题-慢查询优化
Mysql中有一种慢查询日志，数据库会将执行时间超过阈值的语句写入日志文件中，我们可以通过开启慢查询日志来分析那些执行效率低的sql语句
分析流程
- 开启慢查询日志
- 使用explain语句分析慢sql
- 对sql语句进行优化或者对表进行优化
优化方式
- 最大化利用索引
- 尽量不适用%开始的模糊查询（如果非要用的话可以使用全文索引或者ES）
- 符合最左匹配原则
- 少使用or（or会放弃使用索引，用union代替）
- 避免使用null（同样也会放弃使用索引，用默认值代替）设计数据库时尽量让字段为not null
- 避免使用 where 1=1（数据库会放弃索引，有条件就加where没条件就不加）
- 避免全表扫描
- 减少无效数据的查询
- 避免使用select * ，取出全部列，增加消耗
- 关联表查询时，小表在前，大表在后（第一张表会涉及全表扫描，）
- 对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。
- +,-等运算也会是优化器放弃索引
- 关联查询时，关联字段的字符集不同也会造成查询不走索引（如果一个字段在两个表中编码类型呈子集/超集的形式，实际查询时就会进行全表扫描来依次转换字段查询）
查询时由于对索引字段进行函数操作导致不走索引的情况有很多，但如果是对输入参数进行函数操作就不会影响索引的选中例如：
# 这种不会走索引
select * from id where id+1 = 1000
# 下面这种会走索引
select * from id where id = 1000-1
数据库结构优化（分库分表）
分库
将单个数据库拆分成多个数据库，分表主要有水平分库和垂直分库
水平分库
将一个库中的数据分到多个数据库中。不过水平分库比较难实现，并且后端在开发时也不方便。不建议使用
- 每个数据库的结构相同
- 每个数据库中的数据不相同
- 所有数据库中的数据构成全集
垂直分库
垂直分库是指将原本一个库中的一些表放到其他数据库中，比如按业务拆分成一个库。
- 每个数据的结构不同
- 数据库中的表不一样
- 每个库之间相对独立
分表
将一张表拆分成多个表，分表主要有水平分表，垂直分表
水平分表
将表中的数据横向拆分成多张表（一般要按一定的规律拆分）
- 每个表结构相同
- 每个表中的数据没有交集
- 所有表中的数据构成整个数据的全集
垂直分表
按表中的字段拆分，原本一张表，将一些热门数据放到一张表，一些数据量大或者冷门的数据放到另外一张表中，通过主键来链接。
- 每个表的结构不同
2394
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
