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