MySQL知识点整理【2023】
2023-06-30 14:08:39 2 举报AI智能生成
MySQL知识点整理【2023】
作者其他创作
大纲/内容
事务
什么是事务
是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位
事务的基本特征
原子性 Atomicity
指的是⼀个事务中的操作要么全部成功，要么全部失败
一致性 Consistency
指的是数据库执行事务的前后，数据是—致的，不会出现数据丢失
隔离性 Isolation
指的是一个事务的修改在最终提交前，对其他事务是不可见的，相互隔离
持久性 Durability
指的是—旦事务提交，所做的修改就会永久保存到数据库中
问题：那ACID是靠什么保证的？
A 原子性 由undo log日志保证，它记录了需要回滚的日志信息，事务回滚时撤销已经执行成功的sqI
C 一致性 一般由代码层面来保证（由其它三个特性保证）
Ⅰ 隔离性 由MVCC来保证
D 持久性 由内存+redo log日志来保证,mysql修改数据同时在内存和redo log 记录这次操作，事务提交的时候通过redo log刷盘，宕机的时候可以从redo log恢复
事务的隔离级别
隔离级别
read uncommit(读未提交)
可能会读到其他事务未提交的数据,也叫做脏读
read commit(读已提交)
两次读取结果不—致，叫做不可重复读。不可重复读解决了脏读的问题,他只会读取已经提交的事务
repeatable read(可重复读)
mysql的默认级别，就是每次读取结果都一样,但是有可能产生幻读
serializable(串行化)
给每一行读取的数据加锁，会导致大量超时和锁竞争的问题。不管多少事务，都是「依次按序一个一个执行」
事务造成的三个问题
脏读
脏读指的是「读到了其他事务未提交的数据」，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了并一定最终存在的数据，这就是脏读
不可重复读
对比可重复读，不可重复读指的是在同一事务内，「不同的时刻读到的同一批数据可能是不一样的」
幻读
幻读是针对数据插入(INSERT）操作来说的。假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现「好像刚刚的更改对于某些数据未起作用」，但其实是事务B刚插入进来的这就叫幻读
事务的传播行为（7种）
PROPAGATION_REQUIRED （默认）
如果当前没有事务，就创建一个新事务，如果当前存在事务，就加入该事务，该设置是最常用的设置
PROPAGATION_REQUIRED_NEW
创建新事务，无论当前存不存在事务，都创建新事务
PROPAGATION_SUPPORTS
支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就以非事务执行
PROPAGATION_NOT_SUPPORTED
以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
PROPAGATION_MANDATORY
支持当前事务，如果当前存在事务，就加入该事务，如果当前不存在事务，就抛出异常
PROPAGATION_NEVER
以非事务方式执行，如果当前存在事务，则抛出异常
PROPAGATION_NESTED
如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与PROPAGATION_REQUIRED类似的操作
MySQL中有哪些日志
binlog
定义: binlog是归档日志，属于Server 层的日志，是一个二进制格式的文件，用于「记录用户对数据库更新的SQL语句信息」
主要作用：主从复制 数据恢复
undolog
定义: undolog是InnoDB存储引擎的日志，是回滚日志，用于保证数据的原子性，「保存了事务发生之前的数据的一个版本，也就是说记录的是数据是修
改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读(MVCC)
改之前的数据，可以用于回滚」，同时可以提供多版本并发控制下的读(MVCC)
主要作用： 事务回滚 MVCC
redolog
定义: redolog 是「InnoDB存储引擎所特有的一种日志」，是重做日志，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。
主要作用
可以做「数据恢复并且提供crash-safe 能力」
当有增删改相关的操作时，会先记录到 Innodb中，并修改缓存页中的
数据，「等到mysql闲下来的时候才会真正的将redolog中的数据写入到磁盘当中(刷盘)」
可以做「数据恢复并且提供crash-safe 能力」
当有增删改相关的操作时，会先记录到 Innodb中，并修改缓存页中的
数据，「等到mysql闲下来的时候才会真正的将redolog中的数据写入到磁盘当中(刷盘)」
relaylog
定义: relaylog是中继日志，「在主从同步的时候使用到」，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。
主要作用
master主节点的 binlog传到 slave 从节点后，从结点开启io线程写入relay log里。然后从节点的sql线程从relaylog里读取日志然后应用到slave从节点本地。从而「使从服务器和主服务器的数据保持一致」
master主节点的 binlog传到 slave 从节点后，从结点开启io线程写入relay log里。然后从节点的sql线程从relaylog里读取日志然后应用到slave从节点本地。从而「使从服务器和主服务器的数据保持一致」
errorlog
定义：记录 MySQL 服务器运行过程中的错误信息,默认开启无法关闭
slowlog
定义: MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的SQL语句，具体是指运行时间超过 long_query_time 值的SQL，这样的SQL则会被记录到慢查询日志中。long_query_time 的默认值为10秒
general log
定义： 查询日志里面记录了数据库执行的所有命令，不管语句是否正确，都会被记录，默认是关闭的
问题：redolog是怎么记录日志的？
InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么总共就可以记录4GB的操作。「从头开始写，写到末尾就又回到开头循环写」。
所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「内存抖动」现象，从肉眼的角度来观察会发现mysql 会宕机一会儿，此时就是正在刷盘了
所以，如果数据写满了但是还没有来得及将数据真正的刷入磁盘当中，那么就会发生「内存抖动」现象，从肉眼的角度来观察会发现mysql 会宕机一会儿，此时就是正在刷盘了
问题： redolog 和 binlog的区别？
所属层次
1. [redolog」是「Innodb」独有的日志，而「binlog」是「server」层的，所有的存储引擎都有使用到
记录内容
2. 「 redolog」记录了「具体的数值」，对某个页做了什么修改，「binlog」记录的「操作内容」
文件大小
3. [binlog」大小达到上限或者flush log 「会生成一个新的文件」，而「redolog」有固定大小「只能循环利用J
其它
4. 「binlog日志没有crash-safe 的能力」，只能用于归档。而redo log 有crash-safe能力
执行过程
图解
小结
连接器: 建立连接，管理连接、校验用户身份;
查询缓存: 查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0已删除该模块;
解析SQL: 通过解析器对SQL查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型;
执行SQL: 执行SQL 共有三个阶段:
1）预处理阶段:检查表或字段是否存在;将select *中的*符号扩展为表上的所有列。
2）优化阶段:基于查询成本的考虑，选择查询成本最小的执行计划;
3）执行阶段:根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端;
查询缓存: 查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0已删除该模块;
解析SQL: 通过解析器对SQL查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型;
执行SQL: 执行SQL 共有三个阶段:
1）预处理阶段:检查表或字段是否存在;将select *中的*符号扩展为表上的所有列。
2）优化阶段:基于查询成本的考虑，选择查询成本最小的执行计划;
3）执行阶段:根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端;
索引
问题： 什么是索引，有什么优缺点？
定义：索引是一种帮助快速查找数据的数据结构，可以把它理解为书的目录，通过索引能够快速找到数据所在位置。
索引数据结构有:
Hash表(通过hash算法快速定位数据，但不适合范围查询，因为需要每个key都进行一次hash) .
二叉树(查找和修改效率都比较高)，但是在InnoDB引擎中使用的索引是B+Tree，相较于二叉树，B+Tree这种多叉树，更加矮宽，更适合存储在磁盘中。
使用索引增加了数据查找的效率，但是相对的由于索引也需要存储到磁盘，所以增加了存储的压力，并且新增数据时需要同步维护索引。
但是合理的使用索引能够极大提高我们的效率!
Hash表(通过hash算法快速定位数据，但不适合范围查询，因为需要每个key都进行一次hash) .
二叉树(查找和修改效率都比较高)，但是在InnoDB引擎中使用的索引是B+Tree，相较于二叉树，B+Tree这种多叉树，更加矮宽，更适合存储在磁盘中。
使用索引增加了数据查找的效率，但是相对的由于索引也需要存储到磁盘，所以增加了存储的压力，并且新增数据时需要同步维护索引。
但是合理的使用索引能够极大提高我们的效率!
问题：说下mysql的索引有哪些？
按照数据结构来分
B+树索引
Hash索引
按照物理存储来分
聚簇索引（主键索引）
二级索引（辅助索引）
按照字段来分
主键索引
唯一索引
普通索引
前缀索引
按照个数来分
单列索引
联合索引
全文索引
对内容进行分词搜索，仅可用于Myisam，更多用ElasticSearch做搜索ALTER TABLE table_name ADD FULLTEXT ( filed_name )
问题：聚簇和非聚簇索引又是什么?
聚簇索引
定义：叶子节点包含索引列和数据，这种数据和索引在一起存储的索引方式叫做聚簇索引
一张表只能有一个聚簇索引，一般会选择主键作为聚簇索引，没有定义主键，InnoDB会选择一个唯一的非空字段代替，如果都没有的话则会隐式定义一个主键作为聚簇索引
非聚簇索引
定义：叶子结点包含索引列和主键ld值，这一点和MylSAM保存的是数据地址是不同的
问题：什么时候适合用索引?什么时候不适合?
适合的情况
1.字段有唯一性
2.where条件经常用到的字段
3.group by和order by经常用到的字段
2.where条件经常用到的字段
3.group by和order by经常用到的字段
不适合的情况
1.字段频繁变化
2.字段数据大量重复
3.不经常用的字段
4.数据太少
2.字段数据大量重复
3.不经常用的字段
4.数据太少
问题: 普通索引和唯一索引该怎么选择?
查询场景
当普通索引为条件时查询到数据会一直扫描，直到扫完整张表。
当唯一索引为查询条件时，查到该数据会直接返回，不会继续扫表
当唯一索引为查询条件时，查到该数据会直接返回，不会继续扫表
更新场景
普通索引会直接将操作更新到change buffer中，然后结束。唯一索引需要判断数据是否冲突
总结
唯一索引更加适合查询的场景
普通索引更适合插入的场景
普通索引更适合插入的场景
问题: 为什么采用B+树，而不是B-树作为索引的数据结构?
B+树只在叶子结点储存数据，非叶子结点不存具体数据，只存 key，查询更稳定，增大了广度，而一个节点就是磁盘一个内存页，内存页大小固定，那么相比B树，B-树这些「可以存更多的索引结点」，宽度更大，树高矮，节点小，拉取一次数据的磁盘IO次数少，并且B+树在叶子节点上加了双向指针，因此只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，效率更高。
问题： 索引什么时候会失效？
联合索引非最左匹配
对索引使用函数/表达式计算
索引字段上使用(!=或者<>)判断时，会导致索引失效而转向全表扫描
索引字段上使用is null / is not null判断时，会导致索引失效而转向全表扫描
联合索引的前面索引列使用范围查询(<,> ,like),会导致后续的索引失效
索引字段使用like以通配符开头（‘%字符串’)时，会导致索引失效而转向全表扫描，也是最左前缀原则。
索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
索引字段使用or时，会导致索引失效而转向全表扫描(取决于or链接的字段是否都使用索引)
索引覆盖
定义: 指的是在一次查询中，如果一个索引(二级索引)包含或者说覆盖所有需要查询的字段的值，我们就称之为覆盖索引，而不再需要回表查询
举个栗子
table表 id,name,age,gender,address
id主键，name普通索引
id主键，name普通索引
select id,name from table where name='zhangsan';
查找过程：直接根据name的值去name的B+树上找到id和name，不需要进行回表，此时叫做索引覆盖
回表查询
定义：指的是一次查询，走的是二级索引，没有覆盖需要查询的字段，需要拿到主键ld去聚簇索引中二次查询，多扫描一次索引树
举个栗子
table表 id,name,age,gender,address
id主键，name普通索引
id主键，name普通索引
select * from table where name='zhangsan';
查找过程：首先根据name的值去name的B+树上找到叶子节点中存储的id值，然后再根据id的值去id的B+树上查找整行的记录，这个过程叫做回表。
回表效率较低，尽量避免回表
回表效率较低，尽量避免回表
最左匹配
定义：最左前缀其实说的是，在where条件中出现的字段，「如果只有组合索引中的部分列，则这部分列的触发索引顺序」，是按照定义索引的时候的顺序从前到后触发，最左面一个列触发不了，之后的所有列索引都无法触发。
举个栗子
table表 id,name,age,gender,address
id主键，（name,age）组合索引
id主键，（name,age）组合索引
select * from table where name=zhangsan and age = 20;
select * from table where age = 20;
select * from table where name=zhangsan ;
select * from table where age = 20 and name=zhangsan;优化器优化调整顺序
select * from table where age = 20;
select * from table where name=zhangsan ;
select * from table where age = 20 and name=zhangsan;优化器优化调整顺序
索引下推
定义: 如果存在某些被索引的列的判断条件时，MySQL将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，「只有当索引符合条件时才会将数据检索出来返回给MySQL服务器」。
举个栗子
-- 查看是否开启了 索引下推 index_condition_pushdown 默认是on
show VARIABLES like '%optimizer_switch%';
show VARIABLES like '%optimizer_switch%';
table表 id,name,age,gender,address
id主键，（name,age）组合索引
id主键，（name,age）组合索引
select * from table where name=zhangsan and age = 12;
没有索引下推之前：先根据name的值从存储引擎中把所有符合条件的数据拉取到server层，然后在server层中对age进行数据筛选
有了索引下推之后：直接根据name，age的值从存储引擎中找到符合条件的数据，不需要在server层进行数据筛选了
有了索引下推之后：直接根据name，age的值从存储引擎中找到符合条件的数据，不需要在server层进行数据筛选了
锁
MySQL锁家族
按照数据类型操作来分
共享锁 S（Shared Lock）/ 读锁（read lock）
排它锁 X（Exclusive Lock）/ 写锁 （write lock）
按照锁粒度角度来分
表级锁
表级别的S锁和X锁
加锁
LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。
LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。
LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。
小结
意向锁
定义： InnoDB 支持 多粒度锁（multiple granularity locking），它允许 行级锁 与 表级锁 共存，而意向
锁就是其中的一种 表锁
锁就是其中的一种 表锁
意向锁分为两种
意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）
意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）
小结
1. InnoDB 支持 多粒度锁 ，特定场景下，行级锁可以与表级锁共存。
2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥 。
3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
4. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求。
2. 意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥 。
3. IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。
4. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求。
自增锁
在使用MySQL过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性。
MDL锁
MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性
比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一
列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写
锁。
列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写
锁。
行级锁
记录锁（Record Locks）
定义: 记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP
小结
记录锁是有S锁和X锁之分的，称之为 S型记录锁 和 X型记录锁
1.当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可
以继续获取X型记录锁；
2.当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不
可以继续获取X型记录锁。
1.当一个事务获取了一条记录的S型记录锁后，其他事务也可以继续获取该记录的S型记录锁，但不可
以继续获取X型记录锁；
2.当一个事务获取了一条记录的X型记录锁后，其他事务既不可以继续获取该记录的S型记录锁，也不
可以继续获取X型记录锁。
间隙锁（Gap Locks）
间隙锁之间不冲突。唯一目的是防止其他事务插入间隙。
只锁定一个范围【锁左不锁右】，不包含记录本身【加的排他锁】
只锁定一个范围【锁左不锁右】，不包含记录本身【加的排他锁】
Select * from emp where empid > 100 for update;
目的：防止幻读
对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)"
场景
默认地，只有在RR下才有gap lock，用于保护两条数据之间的gap，防止事务过程中被写入/删除数据，引发幻读问题
但事实上，即在RC级别，如果往一个有唯一约束属性的索引中（包含主键索引，唯一索引)插入新数据前，会对相应的位置加载一个共享的nextkey lock，而我们知道next-key lock是包含gap lock的，因此在这种特殊情况下也是有gap lock的
临键锁（Next-Key Locks）
间隙锁和行锁合称next-key lock,mysql是通过next-key lock来防止幻读的 [ RR隔离级别 ]
锁定的范围是（ ]，左开右闭
降级情况
当查询未命中任何记录时，会降级为间隙锁（Gap Locks）
当使用主键或者唯一索引命中了一条记录时，会降级为记录锁（Record Locks）
插入意向锁（Insert Intention Locks）
页级锁
按照对锁的态度来分
悲观锁（Pessimistic Locking）
悲观锁是一种思想，顾名思义，就是很悲观，对数据被其他事务的修改持保守态度，会通过数据库自身的锁机制来实现，从而保证数据操作的排它性
乐观锁（Optimistic Locking）
乐观锁认为对同一数据的并发操作不会总发生，属于小概率事件，不用每次都对数据上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，也就是不采用数据库自身的锁机制，而是通过程序来实现
小结
总结一下乐观锁和悲观锁的适用场景：
1. 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁
问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层
面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。
1. 乐观锁 适合 读操作多 的场景，相对来说写的操作比较少。它的优点在于 程序实现 ， 不存在死锁
问题，不过适用场景也会相对乐观，因为它阻止不了除了程序以外的数据库操作。
2. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层
面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。
加锁方式
隐式锁
当多个客户端并发访问同一个数据的时候，为了保证数据的一致性，数据库管理系统会自动的为该数据加锁、解锁，这种被称为隐式锁。隐式锁无需开发人员维护（包括锁粒度、加锁时机、解锁时机等）
显示锁
通过特定的语句进行加锁，我们一般称之为显示加锁
显示加共享锁
select .... lock in share mode
显示加排它锁
select .... for update
select .... lock in share mode
显示加排它锁
select .... for update
其它
死锁
原因:指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环
几种死锁场景
1.如果两个session访问两个表的顺序不同，发生死锁的机会就非常高!但如果以相同的顺序来访问，死锁就可能避免
⒉.在程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低死锁的可能
3.在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应该先申请共享锁，更新时再申请排他锁，甚至死锁
4.在RR隔离级别下，如果两个线程同时对相同条件记录用SELIECT.FOR UPDATE加排他锁，在没有符合该记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可以避免问题
解决死锁的两种策略 ：
一，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
二，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on ，表示开启这个逻辑。
一，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout 来设置。
二，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级排他锁的事务进行回滚），让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为on ，表示开启这个逻辑。
锁监控
innodb lock waits和innodb_trx两张表里的信息 innodb_locks表
全局锁
全局锁的命令
Flush tables with read lock
Flush tables with read lock
典型使用 场景 是：做 全库逻辑备份
存储引擎
innoDB
innodb主键使用自增bigint效率比uuid高
1.方便比较大小
⒉.不会破坏B+TREE结构
1.方便比较大小
⒉.不会破坏B+TREE结构
聚集索引:索引和数据在同—张表
非聚集索引:索引在—张表，数据在─张表
非聚集索引:索引在—张表，数据在─张表
innodb使用b+tree存索引和数据不使用hash的原因:
范围查找使用hash不合适，需要全表扫描，hash(主键)直接存储到位置，因此一般使用B+Tree
范围查找使用hash不合适，需要全表扫描，hash(主键)直接存储到位置，因此一般使用B+Tree
支持行锁
支持事务
myisam
myisam使用非聚集索引,主键和其他索引都是指向数据表
不支持事务
只支持表锁
MVCC
定义: Multi_Version_Concurrency_Control 多版本并发控制，是现代数据库(包括MySQL、Oracle、PostgreSQL等)引擎实现中常用的处理读写冲突的手段，目的在于「提高数据库高并发场景下的吞吐性能」
MVCC最大的优势：读不加锁，读写不冲突。读写不冲突是非常重要的，极大的增加了系统的并发性能。MVCC机制也是乐观锁的一种体现。
MVCC解决了什么问题
MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读
在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题
当前读
select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读。
当前读就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
快照读
在RR时普通查询就是快照读
快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读
快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本
TIPS
MVCC只在RR和RC两个隔离级别下工作
MVCC实现原理
MVCC由三部分组成：隐藏字段，undolog，readview
【隐藏字段】每一行记录有三个隐藏键，分别为DATA_TRX_ID、DATA_ROLL_PTR、DB_ROW_ID
readview: 当执行SQL语句查询时会产生一致性视图 【创建快照读这一刻，包含该条数据的所有活跃事务ID的列表】
SQL调优
问题: 一条Sql语句查询一直慢会是什么原因?
「1.没有用到索引」
比如函数导致的索引失效，或者本身就没有加索引
比如函数导致的索引失效，或者本身就没有加索引
「2.表数据量太大」考虑分库分表吧
「3.优化器选错了索引」
「考虑使用」force index强制走索引
「考虑使用」force index强制走索引
问题: 一条Sql语句查询偶尔慢会是什么原因?
「1.数据库在刷新脏页」
比如「 redolog 写满了」，「内存不够用了」释放内存如果是脏页也需要刷，mysql「正常空闲状态刷脏页」
比如「 redolog 写满了」，「内存不够用了」释放内存如果是脏页也需要刷，mysql「正常空闲状态刷脏页」
「2.没有拿到锁」
问题: 说说你的Sql调优思路?
预防
1.「表结构优化」
1.1拆分字段
1.2字段类型的选择
1.3字段类型大小的限制
1.4合理的增加冗余字段
1.5新建字段一定要有默认值
1.1拆分字段
1.2字段类型的选择
1.3字段类型大小的限制
1.4合理的增加冗余字段
1.5新建字段一定要有默认值
4.「分库分表」
执行
2.「索引方面」
2.1索引字段的选择
2.2利用好mysql支持的索引下推，覆盖索引等功能
2.3唯一索引和普通索引的选择
2.1索引字段的选择
2.2利用好mysql支持的索引下推，覆盖索引等功能
2.3唯一索引和普通索引的选择
3.「查询语句方面」
3.1避免索引失效
3.2合理的书写where条件字段顺序
3.3小表驱动大表
3.4可以使用force index()防止优化器选错索引
3.1避免索引失效
3.2合理的书写where条件字段顺序
3.3小表驱动大表
3.4可以使用force index()防止优化器选错索引
其它
问题： WAI是什么?有什么好处?
WAL就是Write-Ahead Logging，其实就是「所有的修改都先被写入到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性。
1.「读和写可以完全地并发执行」，不会互相阻塞
2.先写入 log中，磁盘写入从「随机写变为顺序写」，降低了client端的延迟。并且，由于I顺字写入大概率是在一个磁盘块内，这样产生的io次数也大大降低
3.写入日志当数据车崩溃的时候「可以使用日志来恢复磁盘数据」
2.先写入 log中，磁盘写入从「随机写变为顺序写」，降低了client端的延迟。并且，由于I顺字写入大概率是在一个磁盘块内，这样产生的io次数也大大降低
3.写入日志当数据车崩溃的时候「可以使用日志来恢复磁盘数据」
- 问题：缓冲池 buffer pool是做什么的？
buffer pool是一块内存区域，为了「提高数据库的性能」，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool里面的数据，数据库的增删改查都是在buffer pool上进行
预读机制:
Buffer Pool有一项特技叫预读，存储引擎的接口在被Server层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到Buffer Pool
Buffer Pool有一项特技叫预读，存储引擎的接口在被Server层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到Buffer Pool
0 条评论
下一页
