首页  思维导图  详情

MySQL

2021-08-13 17:35:52   0  举报





AI智能生成

MySQL知识全解

架构

学霸笔记

存储引擎

知识脑图

数据库

计算机类

作者其他创作

大纲/内容

Server

连接器

负责客户端与服务器的链接，链接成功后没有后续操作会变为空闲状态， 之后一段时间没有动静，连接器就会自动将链接断开，默认是8小时，阔以通过 wait_timeout控制

查询缓存

优点

key-value 对的形式，被直接缓存在内存中，key 是查询的语句，value 是查询的结果， 命中缓存，直接返回结果，效率高

缺点

命中率低：只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来， 还没使用呢，就被一个更新全清空了

其他

你可以将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存。而对于你确定要使用查询缓存的语句， 可以用 SQL_CACHE 显式指定，像下面这个语句一样：mysql> select SQL_CACHE * from T where ID=10；

MySQL 8.0 版本直接将查询缓存的整块功能删掉了，也就是说 8.0 开始彻底没有这个功能了。

分析器

没有命中缓存则会执行分析器

语法分析

关键字判断，如：select 识别出来就是一个查询语句，会按照查询语法进行分析

优化器

执行器

判断有没有执行查询，修改等语句的权限

查询

修改

使用到了WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志，再写磁盘 <ol><li>InnoDB 引擎会先把记录写到 redo log，并更新内存 </li></ol>

redo log

redo log包括两部分：一是内存中的日志缓冲(redo log buffer)，该部分日志是易失性的；二是磁盘上的重做日志文件(redo log file)，该部分日志是持久的

redolog的内存大小是固定的，会使用完，当内存要使用完的时候就会进行数据刷盘腾出redolog内存，当进行数据刷盘的时候，更新操作是阻塞的

redo log 是循环写的，空间固定会用完

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

redo log 是 InnoDB 引擎特有的日志

数据持久性靠redolog实现

redo log 是物理日志，记录的是“在某个数据页上做了什么修改”

binlog

binlog 日志只能用于归档

inlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用

binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”

binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

模式

statemen

statement 格式的话是记sql语句，可能会导致主备不一致

row

row格式会记录行的内容，记两条，更新前和更新后都有

mixed

上面两种格式的混合

和redolog组成两阶段提交，保证数据的一致性 1 redo log prepare阶段 2 写binlog 3 commit 当在2之前崩溃时 重启恢复：后发现没有commit，回滚。备份恢复：没有binlog 。 一致 当在3之前崩溃 重启恢复：虽没有commit，但满足prepare和binlog完整，所以重启后会自动commit。备份：有binlog. 一致

undolog

undo log是用于事务的回滚、保证事务隔离级别读已提交、可重复读实现的。redo log是用于对暂不更新到磁盘上的操作进行记录，使得其可以延迟落盘，保证程序的效率。bin log是对数据操作进行备份恢复（并不能依靠 bin log 直接完成数据恢复，因为不确定binlog里记录是否已落盘，而redolog里面的数据肯定是没有落盘的）

执行流程

列：mysql> update T set c=c+1 where ID=2; <ul><li>执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。</li><li>执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。</li><li>引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。</li><li>执行器生成这个操作的 binlog，并把 binlog 写入磁盘</li><li>执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。</li></ul>

内置函数

日期

时间

加密

跨存储引擎的功能

存储过程

触发器

视图

MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

存储引擎

InnoDB

索引

数据结构

哈希表

优点： <ul><li>新增快</li><li>等值查询快</li></ul>

缺点： 无序，范围查找慢

有序数组

优点：<ul><li>等值或范围查询快</li></ul>

缺点： 在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高

搜索树

主键索引

主键索引也被称为聚簇索引

主键索引的叶子节点存的是整行数据

非主键索引

非主键索引也被称为二级索引

主键和非主键索引区别

列：主键索引是一棵树，非主键索引是另外一棵树 <ul><li>如果语句是 select * from T where ID=500，即主键查询方式，则只需要搜索 ID 这棵 B+ 树；</li><li>如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次。这个过程称为回表。</li></ul>

索引维护

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护 <ul><li>当数据落在数据页中间时，需要逻辑上挪动后面的数据，空出位置</li><li>当数据页满时（innoDb每页默认大小是16kb，内存中每页是4kb）这时候需要申请一个新的数据页，然后挪动部分数据过去。这个过程称为页分裂</li><li>页分裂操作还影响数据页的利用率。原本放在一个页的数据，现在分到两个页中，整体空间利用率降低大约 50%</li><li>页合并。当相邻两个页由于删除了数据，利用率很低之后，会将数据页做合并。合并的过程，可以认为是分裂过程的逆过程</li></ul>

子主题

覆盖索引

联合索引

唯一索引

普通索引

普通索引和唯一索引效率分析

查询：效率基本无差

修改

普通索引： <ol><li>数据页在内存中就直接写redolog并更新</li><li>内存中没有就会将更新写入change buffer中也会写入到磁盘中，下次查询命中这条数据时时再更新内存，这个过程叫merge，</li><li>除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作</li></ol>

唯一索引： 唯一索引没有使用change buffer 也就没有普通索引的2，3步，如果内存中没有要更新的数据，则会去磁盘读取数据页放入内存中，然后判断是否有冲突，再进行修改 数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一，这就是与普通索引性能差别的主要原因

change buffer： <ol><li>change buffer的主要作用时减少读磁盘，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率</li><li>change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。</li><li>redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗</li></ol>

索引下推

比如联合索引name和age，条件为name like '葛%' and age = 21,会现在索引上过滤出数据这样可以减少回表次数

索引空洞问题

原因：索引可能因为删除，或者页分裂等原因，导致数据页有空洞

解决：重建索引，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间

前缀索引-如何给字符串创建索引

我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。如下 mysql> select count(distinct email) as L from SUser;-- 总数 mysql> select    count(distinct left(email,4)）as L4,   count(distinct left(email,5)）as L5,   count(distinct left(email,6)）as L6,   count(distinct left(email,7)）as L7, from SUser; 然后，在返回的 L4~L7 中，找出不小于 L * 95% 的值，假设这里 L6、L7 都满足，你就可以选择前缀长度为 6

优点： 减少索引长度，节省磁盘空间

缺点： <ul><li>使用不了覆盖索引，结果集都需要回表进行二次查询</li><li>可能会增加扫描行数</li></ul>

锁

全局锁

Flush tables with read lock (FTWRL)命令可以加全局锁

全局锁的典型使用场景是，做全库逻辑备份

引发问题： <ul><li>如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；</li><li>如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。</li></ul>

表级锁

表锁

元数据锁（meta data lock，MDL)

在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁 <ul><li>读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查</li><li>读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行</li></ul>

所有对表的增删改查操作都需要先申请 MDL 读锁

session A读，session B读，session C进行DML操作，session D读，session A拥有MDL读锁如果session A是个长事务，就会一直持有MDL读锁，session C写操作就会被阻塞，session C之后的操作也会被阻塞，会造成完全不可读写

行锁

update 表名 set 列名 = 列名+1 where id =1，这个时候就对id=1这条数据上了锁， 在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议 如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，举个列子： 假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：从顾客 A 账户余额中扣除电影票价；给影院 B 的账户余额增加这张电影票价；记录一条交易日志。也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

死锁

解决死锁的两个策略： <ul><li>一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。</li><li>另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。</li></ul>

next-key lock

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间

间隙锁

sql突然执行慢的原因

刷脏页（flush）

子InnoDB 的 redo log 写满了

需要避免，这个时候所有更新操作都会阻塞

系统内存不足，需要淘汰数据页，如果是脏页则需要刷盘

优先淘汰干净页，避免刷盘

MySQL空闲的时候进行刷盘

MySQL正常关闭前会进行刷盘

参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%