首页  思维导图  详情

MySQL知识点整理【2023】

2023-06-30 14:08:39   2  举报





AI智能生成

MySQL知识点整理【2023】

mysql

mysql知识点总结

作者其他创作

大纲/内容

事务

什么是事务

是数据库管理系统执行过程中的一个逻辑单位，由一个有限的数据库操作序列构成，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位

事务的基本特征

原子性 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

redolog

定义: redolog 是「InnoDB存储引擎所特有的一种日志」，是重做日志，用于记录事务操作的变化，记录的是数据修改之后的值，不管事务是否提交都会记录下来。

主要作用 可以做「数据恢复并且提供crash-safe 能力」 当有增删改相关的操作时，会先记录到 Innodb中，并修改缓存页中的 数据，「等到mysql闲下来的时候才会真正的将redolog中的数据写入到磁盘当中(刷盘)」

relaylog

定义: relaylog是中继日志，「在主从同步的时候使用到」，它是一个中介临时的日志文件，用于存储从master节点同步过来的binlog日志内容。

主要作用 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 会宕机一会儿，此时就是正在刷盘了

问题： 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查询语句，从存储引擎读取记录，返回给客户端;

索引

问题： 什么是索引，有什么优缺点？

定义：索引是一种帮助快速查找数据的数据结构，可以把它理解为书的目录，通过索引能够快速找到数据所在位置。

索引数据结构有: 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经常用到的字段

不适合的情况

1.字段频繁变化 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普通索引

select id,name from table where name='zhangsan';

查找过程：直接根据name的值去name的B+树上找到id和name，不需要进行回表，此时叫做索引覆盖

回表查询

定义：指的是一次查询，走的是二级索引，没有覆盖需要查询的字段，需要拿到主键ld去聚簇索引中二次查询，多扫描一次索引树

举个栗子

table表 id,name,age,gender,address 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）组合索引

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 默认是on show VARIABLES like '%optimizer_switch%';

table表 id,name,age,gender,address id主键，（name,age）组合索引

select * from table where name=zhangsan and age = 12;

没有索引下推之前：先根据name的值从存储引擎中把所有符合条件的数据拉取到server层，然后在server层中对age进行数据筛选 有了索引下推之后：直接根据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锁 。

小结

意向锁

定义： 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. 意向锁在保证并发性的前提下，实现了 行锁和表锁共存 且 满足事务隔离性 的要求。

自增锁

在使用MySQL过程中，我们可以为表的某个列添加 AUTO_INCREMENT 属性。

MDL锁

MySQL5.5引入了meta data lock，简称MDL锁，属于表锁范畴。MDL 的作用是，保证读写的正确性

比如，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个 表结构做变更 ，增加了一 列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。 因此，当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写 锁。

行级锁

记录锁（Record Locks）

定义: 记录锁也就是仅仅把一条记录锁上，官方的类型名称为： LOCK_REC_NOT_GAP 

小结

记录锁是有S锁和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. 悲观锁 适合 写操作多 的场景，因为写的操作具有 排它性 。采用悲观锁的方式，可以在数据库层 面阻止其他事务对该数据的操作权限，防止 读 - 写 和 写 - 写 的冲突。

加锁方式

隐式锁

当多个客户端并发访问同一个数据的时候，为了保证数据的一致性，数据库管理系统会自动的为该数据加锁、解锁，这种被称为隐式锁。隐式锁无需开发人员维护（包括锁粒度、加锁时机、解锁时机等）

显示锁

通过特定的语句进行加锁，我们一般称之为显示加锁

显示加共享锁 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

innodb主键使用自增bigint效率比uuid高 1.方便比较大小 ⒉.不会破坏B+TREE结构

聚集索引:索引和数据在同—张表 非聚集索引:索引在—张表，数据在─张表

innodb使用b+tree存索引和数据不使用hash的原因: 范围查找使用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强制走索引

问题: 一条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()防止优化器选错索引

其它

问题： WAI是什么?有什么好处?

WAL就是Write-Ahead Logging，其实就是「所有的修改都先被写入到日志中，然后再写磁盘」，用于保证数据操作的原子性和持久性。

1.「读和写可以完全地并发执行」，不会互相阻塞 2.先写入 log中，磁盘写入从「随机写变为顺序写」，降低了client端的延迟。并且，由于I顺字写入大概率是在一个磁盘块内，这样产生的io次数也大大降低 3.写入日志当数据车崩溃的时候「可以使用日志来恢复磁盘数据」

<ol><li>问题：缓冲池 buffer pool是做什么的？</li></ol>

buffer pool是一块内存区域，为了「提高数据库的性能」，当数据库操作数据的时候，把硬盘上的数据加载到 buffer pool，不直接和硬盘打交道，操作的是 buffer pool里面的数据，数据库的增删改查都是在buffer pool上进行

预读机制: Buffer Pool有一项特技叫预读，存储引擎的接口在被Server层调用时，会在响应的同时进行预判，将下次可能用到的数据和索引加载到Buffer Pool

 收藏

立即使用

Java🚀技术选型梳理梳理

 收藏

立即使用

JUC并发编程与源码分析学习笔记

 收藏

立即使用

并发编程相关面试题2023

 收藏

立即使用

MySQL知识点整理【2023】

lvoyee

职业：本科

去主页





0 条评论

下一页

为你推荐

查看更多

