mysql45讲笔记

Filwaod

已于 2023-11-23 10:23:27 修改

阅读量615

点赞数

分类专栏： mysql 文章标签：笔记数据库 mysql mysql45讲

于 2023-05-23 14:51:04 首次发布

本文链接：https://blog.csdn.net/qq_42665745/article/details/130109310

版权

mysql 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

不一定要都学，有些感觉用不到，有选择的学！！！

文章目录

mysql45讲

mysql45讲

1.mysql基础架构

一条SQL查询语句是怎么执行的？

在这里插入图片描述

连接器：连接器负责跟客户端建立连接、获取权限、维持和管理连接
查询缓存：之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端。
分析器：分析器先会做“词法分析”。你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL需要识别出里面的字符串分别是什么，代表什么。做完了这些识别以后，就要做“语法分析”。根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒
优化器：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序
执行器：通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句，开始执行的时候，要先判断一下你对这个表T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，然后调用存储引擎提供的接口
存储引擎：存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的数据库管理系统支持多种不同的数据引擎，不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。

2.mysql日志系统

redo log

当一条记录需要更新时，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，这个更新往往是在系统比较空闲的时候做的

InnoDB 引擎的 redo log 是固定大小的，从头开始写，写到末尾就又回到开头循环写

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

binlog

redo log 是 InnoDB 引擎特有的日志，binlog(归档日志) 是 Server 层的日志

这两种日志有以下三点不同：

redo log 是 InnoDB 引擎特有，而 binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
redo log 是物理日志，记录的是 “在某个数据页上做了什么修改”，是对数据的更新；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如 “给 ID = 2 这一行的 c 字段加 1”。
redo log 是循环写，空间固定会用完；binlog 是可以追加写入的。“追加写” 是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志

正是因为 binlog 会记录所有的逻辑操作，并且是采用 “追加写” 的形式，那么备份系统中就可以保存所有的binlog，使用 binlog
就可以完全的恢复数据库任意指定时间的状态

update 语句的执行流程

# ID 是主键
update T set c=c+1 where ID=2;

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

两阶段提交

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是 “两阶段提交”。

为了让两份日志之间的逻辑一致，所以必须要有两阶段提交。如果不使用两阶段提交，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

3.事务隔离

读未提交（read uncommitted）：一个事务还没提交时，它做的变更就能被别的事务看到。
读提交（read committed）：一个事务提交之后，它做的变更才会被别的事务看到。
可重复读（repeatable read）：一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化（serializable）：顾名思义是对于同一行记录，“写” 会加 “写锁”，“读” 会加 “读锁”。当出现读写冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

在实现上，数据库里面会创建一个视图，访问时以视图的逻辑结果为准，在 “可重复读” 隔离级别下，这个视图是在事务启动时创建，整个事务存在期间都在用这个视图。
在 “读提交” 隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的（每个查询语句都重新创建一个视图）。
需要注意的是，“读未提交” 隔离级别下直接返回记录上的最新值，没有视图概念；
而 “串行化” 隔离级别下直接用加锁的方式来避免并行访问。

4.索引类型

索引的出现就是为了提高数据查询的效率，就像书的目录一样

索引的常见类型

1.哈希表

哈希的思路很简单，就是把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置，如果多个 key 值经过哈希函数的换算，出现同一个值的情况，就会拉出一个链表

因为经过哈希计算后，值并不是相邻递增的，所以并不是有序的，哈希索引做区间查询的速度很慢

所以，哈希这种结构适用于只有等值查询的场景，比如 Memcached 及其他一些 NoSQL 引擎。

2.有序数组

数组是按值递增的顺序保存的，如果要查询某个值，用二分法就可以快速得到，这个时间复杂度是 O(log(N)) 。

如果仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须挪动后面所有的记录，成本太高。

所以，有序数组索引只适用于静态存储引擎，比如保存的是2017年某个城市的所有人口信息，这类不会修改的数据。

3.二叉搜索树

二叉搜索树的查询时间复杂度也是 O(log(N))，但是为了维持 O(log(N)) 的查询复杂度，就需要保持这棵树是平衡二叉树，为了做这个保证，更新的时间复杂度也是 O(log(N))

但是实际上大多数的数据库存储引擎并不使用二叉树。其原因是，索引不止存在内存中，还要写到磁盘上。

在节点很多的二叉树中，树的高度会很高，一次查询就需要访问很多的数据块， I / O 成本太高，导致查询效率很低。

4.B+ 树

为了让一个查询尽量少地读磁盘，就必须让查询过程访问尽量少的数据块。那么，就不应该使用二叉树，而是要使用 N 叉树。

N 叉树由于在读写上的性能优点，以及适配磁盘的访问模式，已经被广泛应用在数据库引擎中了。

InnoDB中使用的就是B+树

在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表

InnoDB 中所有数据都是存储在 B+ 树中的

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护，可能会进行页分裂以及页合并

主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。

5.索引重点概念

覆盖索引

如果在一个查询中，二级索引已经 “覆盖了” 我们的查询需求，不需要再回表，我们称为覆盖索引

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段

索引下推

在 MySQL 5.6 之后，引入了索引下推优化（index condition pushdown），可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

例：name和age有索引，ismale没索引，索引查找过程中会找到符合name和age条件的主键，再回表判断ismale也满足的数据

select * from tuser where name like '张%' and age=10 and ismale=1;

总的来说，尽量少地访问资源是数据库设计的重要原则之一

最左前缀原则

联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符，只要满足最左前缀，就可以利用索引来加速检索

在建立联合索引的时候，如何安排索引内的字段顺序？
这里我们的评估标准是：索引的复用能力
因此，第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的

6.全局锁表级锁

全局锁

MySQL 加全局读锁的方法
Flush tables with read lock;(FTWRL)

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

如果不加锁，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的

使用 FTWRL 的风险：

如果是在主库上备份，那么备份期间不能执行更新，导致业务停摆
如果是在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，导致主从延迟

更好的解决办法是使用官方自带的逻辑备份工具 mysqldump ，当 mysqldump 使用参数 -single-transaction 时，导数据之前会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

一致性读是好，但前提是引擎要支持这个隔离级别，也就是 single-transaction 方法只适用于所有的表使用事务引擎的库。

还有一种方法，使用 set global readonly=true 也可以使全库进入只读状态，但更建议使用 FTWRL 的方式，主要有两个原因：

1.在有些系统中，readonly 的值会被用来做其他逻辑，修改 global 变量的方式影响更大，不建议使用
2.使用 FTWRL 的方式，假如客户端发生异常断开，MySQL 会自动释放这个全局锁。而将整个库设置为 readonly 之后，如果客户端发生异常断开，则数据库会一直保持 readonly 的状态，会导致整个库长时间处于不可写的状态，风险较高。

表级锁

表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL）。

# 表锁的语法
lock tables ... read/write;

与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开时自动释放

元数据锁 MDL 不需要显式使用，在访问一个表的时候会被自动加上。当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

1.读锁之间不互斥，因此可以有多个线程同时对一张表增删改查
2.读写锁之间、写写锁之间互斥，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。
3.事务中的 MDL 锁，在语句执行开始时申请，但是会等整个事务提交后再释放
4.在考虑做 DDL 变更的时候，要尽量避免长事务

7.行锁

两阶段锁

在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。

所以，如果在事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放

死锁和死锁检测

死锁：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

出现死锁后，有两种策略：

1.一种策略是，直接进入等待，直到超时，超时时候可以通过参数来设置。
2.另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。默认是开启状态。

使用第一种策略的话，意味着要么设置等待时间较大，默认值是 50 s，这个等待时间往往是无法接受的，要么等待时间设置较小，这样出现死锁的时候，确实很快可以解开，但是如果该线程并不是死锁呢，而只是简单的锁等待呢，往往会造成误伤。

正常情况下都是要采用第二种策略，但是这样也会有症结，就是死锁检测要耗费大量的 CPU 资源，对于每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个 O(N) 的操作。

那如果是我们上面说到的所有事务都要更新同一行的场景呢？，那么如何解决热点行更新导致的性能问题？问题的症结在于，死锁检测要耗费大量的 CPU 资源。

1.如果确保这个业务一定不会出现死锁，可以临时把死锁检测关掉
2.控制并发度，对于相同行的更新，在进入引擎之前排队。或者可以考虑使用中间件实现
3.将一行改成逻辑上的多行来减少锁冲突

减少死锁的主要方向，就是控制访问相同资源的并发事务量

8.MVCC

事务的两种启动方式

第一种，begin / start transaction 方式启动事务，一致性视图是在执行第一个快照读语句时创建的

第二种，使用 start transaction with consistent snapshot 命令启动，一致性视图在启动时就创建

在 MySQL 中，有两个 “视图” 的概念

一个是 view 。它是一个用查询语句定义的虚拟表，在调用的时候执行查询语句并生成结果。创建视图的语法是 create view … ，而它的查询方法与表一样。
另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图，即 consistent read view，用于支持 RC 和 RR隔离级别的实现。

快照在 MVCC 中是怎样工作的

快照是基于整库的

InnoDB 里面每个事务都有一个唯一的事务 ID，叫做 transaction id。（按申请顺序严格递增的）

每行数据都有多个版本，每次事务更新数据，都会生成一个新的数据版本，并且把 transaction id 赋给这个数据版本的事务 ID，记为 row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中有指针指向着它。

一个数据版本，对于一个事务视图来说，除了自己的更新总是可见以外，有三种情况：

版本未提交，不可见；
版本已提交，但是是在视图创建后提交的，不可见；
版本已提交，而且是在视图创建前提交的，可见；

InnoDB 利用了 “所有数据都有多个版本” 的这个特性，实现了 “秒级创建快照” 的能力。

MVCC中的更新

更新数据都是先读后写的，而这个读，只能读当前最新的值，成为 “当前读”（current read）。因为写操作会加锁。

当前读必须要读最新版本，如果此时该行数据上有事务尚未提交，则当前事务必须等待锁。（两阶段协议）

9.普通索引和唯一索引，该怎么选？

对查询语句的影响

对于查询语句来讲，如果是普通索引，在查找到满足条件的第一条记录后，需要继续查找下一个记录，直到碰到第一个不满足查询条件的记录；如果是唯一索引，由于索引定义了唯一性，在查找到第一个满足条件的记录后，就会停止继续检索。

但是，这个不同带来的性能差距是微乎其微的。

对更新语句的影响

当需要更新一个数据页时，如果数据页在内存中就直接更新，如果数据不在内存中，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。下次查询需求要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证逻辑的正确性。

change buffer 用的是 buffer pool 里的内存。

change buffer 也可以持久化，它在内存中有拷贝，也会被写入到磁盘上。

将 change buffer中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭的过程中，也会执行 merge 操作。

change buffer 的优点是，减少读磁盘，语句的执行速度会得到明显的提升。并且，数据页读取到内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

对于唯一索引来说，因为需要判断插入操作是否会违反唯一性约束，所以必须先将数据页读入内存中才能判断，也因此，唯一索引的更新就不能使用 change buffer 了，而实际上也只有普通索引可以使用

change buffer 的使用场景

适用于 写多读少 的业务，页面在写完以后马上被访问到的概率较小，change buffer 中记录变更的动作就会更多，一次 merge 操作的收益就越大。（账单类、日志类）

反过来，读多写少的场景下，记录更新之后马上就要访问，会立即触发 merge 过程。这样随机访问 I / O 的次数不会减少，反而增加了 change buffer 的维护代价

索引选择问题

唯一索引与普通索引在查询能力上没差别，主要考虑对更新性能的影响

首先需要保证的是业务的 “正确性”，如果业务不能保证数据的唯一性，那就只能创建唯一索引，如果可以保证业务的正确性，建议使用普通索引（因为普通索引可以用上 change buffer 的优化机制）。

change buffer 与 redo log 的不同

在 InnoDB 中，change buffer 机制不是一直会被用到，仅当待操作的数据页当前不在内存中，需要先读磁盘加载数据页时，change buffer 才有用武之地。而 redo log 机制，为了保证 crash-safe，一直都会用到

redo log 主要节省的是随机写磁盘的 I / O 消耗（转成顺序写），
change buffer 主要节省的则是随机读磁盘的 I / O 消耗。

10.MySQL 为什么会选错索引

MySQL 是如何判断一个查询的扫描行数的

基数：一个索引上不同值的个数，称之为基数。也就是说，基数越大，索引的区分度越好
MySQL 通过采样统计的方法来获取这个索引的基数

MySQL 会选错索引的原因之一就是没能准确地判断出扫描行数。

如果是统计信息不对，可以修正

# 重新统计索引信息
analyze table 表明;

索引选择异常和处理

1.采用 force index 强行选择一个索引
2.考虑修改语句，引导 MySQL 使用我们期望的索引
3.在某些场景下，可以考虑新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引

11.给字符串字段加索引

我们在建立索引时关注的是区分度，区分度越高越好。因为区分度越高，意味着重复的键值越少。因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

select 
	count(distinct email) as L0,
  count(distinct left(email,1)) as L1,
  count(distinct left(email,2)) as L2,
  count(distinct left(email,3)) as L3,
  count(distinct left(email,4)) as L4
from sys_user;

在这里插入图片描述

1.直接创建完整索引，这样做可能比较占空间
2.创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题
4.创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描

12.我的MySql会“抖”一下

SQL 语句为什么会突然变慢了

WAL 技术：全称是 Write-Ahead Logging ，它的关键点就是先写日志，再写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB 引擎就会先把记录写到 redo log 里面，并更新内存，这个时候更新就算完成了。同时，InnoDB 引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做

利用 WAL 技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能，但是由此也带来了内存脏页的问题。

脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为 “脏页”
干净页：内存数据写入磁盘后，内存和磁盘上的数据页的内容就一致了，称为 “干净页”

平时执行很快的更新操作，其实就是在写内存和日志，而 MySQL 偶尔 “抖” 一下的那个瞬间，可能就是在刷脏页（flush）

引发数据库 flush 的情况：

1.InnoDB 的 redo log 写满了，这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写（redo log 的空间是循环使用的，无所谓释放。对应的内存页会变成干净页，但是等淘汰的时候才会逐出内存）
2.系统空间不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是脏页，就要先将脏页写到磁盘。
3.MySQL 认为系统 “空闲” 的时候，但即使是不空闲的时候，MySQL 也会见缝插针地找时间，只要有机会就刷一点脏页
4.MySQL 正常关闭的情况下，这时候，MySQL 会把内存的脏页都 flush 到磁盘上。

对于脏页，脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，就有可能会让你的更新和查询语句的响应时间长一些。

刷脏页的控制策略

1.正确服务器的IO能力

innodb_io_capacity参数表示机器的磁盘能力，根据机器能力合理设置该参数的值；

2.刷脏页的速度

InnoDB的刷盘速度主要参考两个参数：脏页比例和redo log 的写盘速度；

innodb_max_dirty_pages_pct是脏页比例上限，默认值为75%；

InnoDB根据当前的脏页比例算出一个值M1；

InnoDB每次写入redo log 有一个序号，根据当前序号和checkpoint之间的差值算出一个值M2；

之后引擎取M1和M2之间（M1和M2都是0-100之间的数）的较大值除以100乘以innodb_io_capacity，这就是刷脏页的速度；

3.刷脏页机制

当一个查询请求执行过程中需要先flush一个脏页时，如果脏页的旁边也是脏页，此时引擎会将相邻的脏页也刷掉，直到下一个数据页不是脏页为止；

该机制通过参数 innodb_innodb_flush_neighbors 控制，值为0时表示只刷自己，值为1时则有连坐效果；

13.为什么表数据删除一半，表文件大小不变

为什么表中的数据被删除了，但是表空间却没有被回收？

假设我们要删除某条数据，InnoDB 引擎只会把这个记录标记为删除，如果再插入一条符合范围条件的数据，就可能会复用这个位置。但是磁盘文件的大小并不会缩小

假如说我们删掉了一个数据页上的所有记录，那整个数据页就可以被复用了

记录复用与页复用的区别是：

记录的复用，只限于符合范围条件的数据
数据页可以复用到任何位置

插入数据页进行分页后也会造成 “空洞”

如何重建表

经过大量增删改查的表，都是有可能存在空洞的，所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

而重建表，就可以达到这样的目的：

1.alter table t engine = InnoDB；

2.analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；

3.optimize table t 等于 recreate+analyze。

14.count(*)慢

首先需要明确的是，在不同的 MySQL 引擎中，count(*) 有不同的实现方式

MyISAM 引擎把一个表的总行数存放在磁盘上，查询时直接返回
InnoDB 在执行 count(*) 时，需要把数据一行一行的从引擎中读出来，然后累积计数

注意，这是没有在考虑过滤条件的情况下，如果加了 WHERE 条件，MyISAM 表也不可能返回的这么快

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢

这是因为即使是在同一时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表 “应该返回多少行” 也是不确定的

优化count(*)慢

将行数存起来，查的时候直接返回

使用缓存系统保存计数
由于写数据库和缓存（Redis）不是原子性操作，会存在丢失更新的问题，而且即使 Redis 正常，这个值还是逻辑上不精确的
在数据库保存计数
把这个计数直接放到数据库里单独的一张计数表 C 中，这样做是行得通的，利用事务这个特性，可以将问题解决掉

其实，把计数放在 Redis 里面，不能够保证计数和 MySQL 表里的数据精确一致的原因，是
这两个不同的存储构成的系统不支持分布式事务，无法拿到精确一致的视图。而把计数值也放在 MySQL 中，就解决了一致性视图的问题。

count(字段) < count(主键 id) < count(1) ≈ count( * )，所以建议是，尽量使用 count(*)

16.order by是怎样工作的

MySQL 会为每个线程分配一块内存用于排序，称为 sort_buffer

全字段排序

取出想要查询的所有列的值放入 sort_buffer 中，然后通过快速排序返回给客户端

如果要排序的数据量小于 sort_buffer_size ，排序就在内存中完成。但如果排序数据量太大，则不得不利用磁盘临时文件归并排序

rowid 排序

如果单行长度太大，全字段索引的效率就不够好，这时候可以采用 rowid 排序，只将要排序的列以及主键 id 放入 sort_buffer ，等排序完再按照 id 值回到原表中取出所有要查询的字段再返回给客户端

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表中去取数据。

如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了，不用再回到原表中去取数据。

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

Mysql的排序成本很高，所有推荐给排序字段加索引，如果想要进一步简化，可以考虑使用覆盖索引，直接返回的就是排序好的数据，不用再排序了。

17.如何正确的显示随机消息

select  word from words order by rand() limit 3;

执行流程:

创建临时表。memory 引擎，字段R（double），W varchar(64) 。没有索引。
按主键顺序取出所有word。调用 rand() 生成0 到1 随机数，存入临时表 R 和 W 字段中，扫描行数10000。
没有索引内存临时表上，按 R 排序。
初始化 sort_buffer
一行行地取出 R 值和位置信息，存入 sort_buffer 。对内存临时表做全表扫描，扫描行数 10000变20000。
sort_buffer 中R 排序。没有涉及到表，不会增加扫描行数。
排序完成后，取前三位置信息，到临时表中取出 word 值，返回给客户端。访问了表三行数据，总扫描行数变20003。

结论：
1.直接用 order by rand()，需Using temporary 和 Using filesort，查询代价大。设计时避开。

2.数据库规范用法：数据库只“读写”，随机方法写在业务逻辑中。

18.逻辑相同，性能差异巨大？

1.对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能
注意：虽然优化器权衡后依然选择了走索引，但是这个索引没有办法通过树搜索功能快速定位，只能全索引扫描(遍历索引树)。
2.隐式类型转换，索引字段被进行了隐式类型转换，就相当于使用了 CAST() 函数，对应第一点
注意：字符串和数字做比较的话，是将字符串转换成数字再进行比较
3.隐式字符编码转换，类似于第二条，就相当于使用了 CONVERT() 函数，对应第一点

tradelog utf8mb4编码,trade_detail utf8编码

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

实际执行的时候从tradelog取出了id=2这一行的tradeid，然后根据tradeid去trade_detail表找，sql如下

select * from trade_detail d where d.tradeid=(id=2的值);

由于id=2的值是utf8mb4不能向小的编码转，sql又变为

select * from trade_detail d where CONVERT(d.tradeid USING utf8mb4)=(id=2的值);

这样又回到了第一种情况

但是这样写就会走索引

select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and d.id=2;

实际执行的时候从trade_detail取出了id=2这一行的tradeid，然后根据tradeid去tradelog表找，sql如下：

select * from tradelog l where l.tradeid=(id=2的值);

utf8是utf8mb4的的子集，所以不用转型，这样就可以用到索引了

19.为什么只查一行数据，也执行这么慢？

第一类：查询长时间不返回

等 MDL 锁

状态：使用 show processlist 命令查看到状态为 Waiting for table metadata lock

原因：可能有一个线程正在表上请求或者持有 MDL 写锁，把 select 语句堵住了

解决办法：找到谁持有 MDL 写锁，然后把它 kill 掉。通过查询 sys.schema_table_lock_waits 这张表，就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。

等 flush

状态：查询出来线程的状态是 Waiting for table flush

原因：这个状态表示现在有一个线程正要对这个表做 flush 操作

解决办法：也是使用 show processlist ，然后就跟上一种解决方式一样了

等行锁

状态：加共享锁的查询语句被阻塞住

原因：有另一个事务在这行记录上持有一个写锁

解决办法：MySQL 5.7 之后可以通过 sys.innodb_lock_waits 查到是谁占着这个写锁，然后给它 kill 掉

第二类：查询慢

没加索引
回滚日志多

select * from t where id=1; # 800ms 查询结果：1
select * from t where id=1 lock in share mode; # 0.2ms，查询结果：1000001

可能是下面这种情况

在这里插入图片描述

因为 session B 更新完 100 万次，生成了 100 万个回滚日志（undo log）

带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行 100 万次以后，才将 1 这个结果返回。

20.幻读

幻读的定义与问题

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入数据的。因此，幻读在当前读下才会出现
幻读仅专指 “新插入的行”
幻读首先会产生语义上的问题，会破坏加锁声明，其次是数据一致性的问题，即使把所有的记录都加上锁，还是阻止不了新插入的记录

如何解决幻读

产生幻读的原因是，行锁只能只能锁住行，但是新插入记录这个动作，要更新的是记录之间的 “间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁（Gap Lock）。

间隙锁之间并不存在冲突关系，跟间隙锁存在冲突关系的，是 “往这个间隙中插入一个记录” 这个操作

间隙锁与行锁合称为 next-key lock ，每个 next-key lock 是前开后闭区间，间隙锁和 next-key lock 的引入，帮助我们解决了幻读的问题，但同时因为间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

21.行锁

总结的加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。
原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止

22.业务高峰期 MySQL 中临时提高性能的方法

注意：这些临时方案可能都是对业务有损的

短连接风暴

MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限

第一种方法：先处理掉哪些占着连接但是不工作的线程
使用 show processlist 结合 information_schema 库的 innodb_trx 表，然后使用 kill connection + id 的命令主动断开客户端连接

优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。

第二种方法：减少连接过程的消耗⚠
如果现在数据库确认是被连接行为打挂了，一种可能的做法是，让数据库跳过权限验证阶段。

跳过权限验证的方法是：重启数据库，并使用 -skip-grant-tables 参数启动。这样整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内

但是这样特别不建议使用，尤其是数据库在外网可访问的情况下

慢查询性能问题

第一种可能是，索引没有设计好
第二种可能是，语句没写好，MySQL 5.7 提供了 query_rewrite （查询重写）功能，可以把输入的一种语句改写成另外一种模式
第三种可能是，MySQL 选错了索引，应急方案是可以给这个语句加上 force index ，同样也可以使用查询重写功能

QPS 突增问题

如果是由全新业务的 bug 导致，可以从数据库端直接把白名单去掉
如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删除
使用查询重写功能，把压力最大的 SQL 语句直接重写成 select 1 返回 ⚠

第三种方案跟前面的去掉权限验证一样，应该是所有选项中优先级最低的一个方案

23.MySQL 如何保证数据不丢

WAL 机制为什么好
WAL 机制主要得益于两个方面：

redo log 和 bin log 都是顺序写，磁盘的顺序写比随机写速度要快
组提交机制，可以大幅度降低磁盘的 IOPS 消耗

实际上数据库的 crash-safe 保证的是：

1.如果客户端收到事务成功的消息，事务就一定持久化了
2.如果客户端收到事务失败（比如主键冲突、回滚等）的消息，事务就一定失败了
3.如果客户端收到 “执行异常” 的消息，应用需要重连后通过查询当前状态来继续后续的逻辑。此时数据库只需要保证内部（数据和日志之间，主库和备库之间）一致就可以了

24.MySQL 是如何保证主备一致的

在这里插入图片描述
binlog 的三种格式对比
binlog 有三种格式，statement、row 以及这两种模式的混合 mixed

statement

statement 格式下记录到 binlog 里的是语句原文，因此可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a ；而在备库执行这条语句的时候，却使用了索引 b 。因此，MySQL 认为这样写是有风险的

row

当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除指定行的数据，不会出现主备删除不同行的问题

mixed

为什么会有 mixed 这种 binlog 格式的存在场景呢

因为 statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式，但 row 格式的缺点是很占空间，要记录下所有被修改行的记录，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度

所以，MySQL 就取了个折中方案，也就是有了 mixed 格式的 binlog。mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能会引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式

所以说，至少应该把 binlog 的格式设置为 mixed ，最好是设置成 row 格式的

循环复制问题

实际生产上使用比较多的双M结构
在这里插入图片描述
循环复制问题：节点 A 更新了语句，将生成的 binlog 发给节点 B ，节点 B 执行完之后，生成 binlog 再发给节点 A，如此不断循环地执行这个语句，也就造成了循环复制了

如何解决

1.规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系；
2.一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog；
3.每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志

25.MySQL 如何保证高可用

主备延迟

所谓主备延迟，就是同一个事务，在备库执行完成的时间和主库执行完成的时间之间的差值

在备库上执行 show slave status 命令，它的返回结果里会显式 seconds_behind_master ，用于表示当前备库延迟了多少秒

主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。

主备延迟来源

第一种可能是，在有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。

第二种可能是，备库的压力大，一般可以这样处理：

1.一主多从。除了备库外，可以多接几个从库，让从库来分担读的压力
2.通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的功能

还有第三种可能，就是大事务，这种情况就是因为主库必须等事务执行完成才会写入 binlog ，再传给备库。所以，如果一个主库上的语句执行了 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。还有另一种典型的大事务场景，就是大表 DDL 。

造成主备延迟还有一个大方向的原因，就是备库的并行复制能力

由于主备延迟的存在，所以在主备切换的时候，就相应的有不同的策略

可靠性优先策略

在双M结构下，从状态 1 到状态 2 切换的详细过程是这样的：

1.判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步
2.把主库 A 改成只读状态，即把 readonly 设置为 true
3.判断备库 B 的 seconds_behind_master 的值，直到这个值变为 0 为止
4.把备库 B 改成可读写状态，也就是把 readonly 设置为 false
5.把业务请求切换到备库 B

可以看到，这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复

在这里插入图片描述

可用性优先策略

如果强行把步骤 4、5 调整到最开始执行，也就是说不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统就几乎没有不可用时间了，这个切换流程称之为可用性优先流程。这个切换流程的代价，就是可能出现数据不一致的情况。

总结来说，在满足数据可靠性的前提下，MySQL
高可用系统的可用性，是依赖于主备延迟的。延迟时间越小，在主库故障的时候，服务恢复需要的时间就越短，可用性就越高。
添加链接描述

26.备库为什么会延迟好几个小时

https://blog.csdn.net/u012131610/article/details/100090270

27.主库出问题了，从库怎么办

https://blog.csdn.net/u012131610/article/details/100112609

28.读写分离有哪些坑？

可能出现过期读

Simple Nested-Loop Join

Simple Nested-Loop Join是最简单也是最粗暴的join方法，上面的sql在testb 的col2字段是没有加索引的，所以当testa为驱动表，testb为被驱动表时，就会拿着testa的每一行，然后去testb的全表扫描，执行流程如下：

1.从表testa中取出一行数据，记为ta。
2.从ta中取出col1字段去testb中全表扫描查询。
3.找到testb中满足情况的数据与ta组成结果集返回。
4.重复执行1-3步骤，直到把testa表的所有数据都取完。
因此扫描的时间复杂度就是100*2000=20W的行数，所以在被驱动表关联字段没有添加索引的时候效率就非常的低下。

假如testb是百万数据以上，那么扫描的时间复杂度就更恐怖了，但是在Mysql中没有使用这个算法，而是使用了另一种算法Block Nested-Loop Join，目的就是为了优化驱动表没有索引时的查询。

Block Nested-Loop Join

Block Nested-Loop Join算法中引入了join buffer区域，而join buffer是一块内存区域，它的大小由join_buffer_size参数大小控制，默认大小是256k

在执行上面的sql的时候，它会把testa表的数据全部加载到join buffer区域，因为join buffer是内存操作，因此相对于比上面的simple算法要高效，但join buffer大小有限，所以要进行分段

比如：先加载testa中的80条数据，与testb比较完数据后，清空再加载testa后20条数据，再与testb进行比较。具体执行流程如下：

1.先加载testa中的80条数据到join buffer
2.然后一次遍历testb的所有数据，与join buffer里面的数据进行比较，符合条件的组成结果集。
3.清空join buffer，再加载testa后面的20条数据。
4.然后一次遍历testb的所有数据，与join buffer里面的数据进行比较，符合条件的组成结果集并返回。

假如驱动表的行数是N，分段参数为K（join_buffer影响k），被驱动表的行数是M，那么总的扫描行数还是N+KM，而内存比较的次数还是NM。
其中K段数与N的数据量有关，若是N的数据量越大，那么可能K被分成段数就越多，这样多次重复扫描的被驱动表的次数就越多。

所以在join buffer不够的情况小，驱动表是越小越好，能够减少K值，减少重复扫描被驱动表的次数。这也就是为什么提倡小表要作为驱动表的原因。

Index Nested-Loop Join

当使用testa表的字段col1去testb查找的时候，testb走的是col1索引的b+树的搜索，时间复杂度近似log2M，并且因为是select*，也就是要查找testb的所有字段，所以这里也涉及到回表查询，因此就变成了2*log2M

在这个过程中，testa表的扫描行数是全部，所以需要扫描100行，然后testa的每一行都与testb也是一一对应的，所以col1索引查询扫描的行数也是100行，所以总的扫描行数就是200行。

我们假设驱动表的数据行位N，被驱动表的数据行为M，那么近似的复杂度为：N+N2log M，因为驱动表的扫描行数就是N，然后被驱动表因为每一次都对应驱动表的一次，并且一次的时间复杂度就是近似2log M，所以被驱动表就是N2*log M。

明显N的值对于N+N2log M的结果值影响更大，所以N越小越好，所以选择小表作为驱动表是最优选择。

35.join语句怎么优化

https://blog.csdn.net/u012131610/article/details/97240601

36.为什么临时表可以重名

https://blog.csdn.net/u012131610/article/details/97757120

37.什么时候会使用内部临时表

https://blog.csdn.net/u012131610/article/details/97884757

38.都说InnoDB好，那还要不要使用Memory引擎

https://blog.csdn.net/u012131610/article/details/98028479

39.自增主键为什么不是连续的？

https://blog.csdn.net/u012131610/article/details/98168871

40.insert语句的锁为什么这么多？

https://blog.csdn.net/u012131610/article/details/98937622

41.怎么最快的复制一张表

https://blog.csdn.net/u012131610/article/details/99406030

42.grant之后要跟着flsh privileges吗？

https://blog.csdn.net/u012131610/article/details/99539832

43.要不要使用分区表

https://blog.csdn.net/u012131610/article/details/99620613

45.自增id用完了怎么办

数据库自增主键用完后分两种情况：

有主键，会再次插入最大主键，报主键冲突
无主键，InnDB 会自动生成一个全局的row_id。它到达最大值后会从 0 开始算，遇到 row_id 一样时，新数据覆盖旧数据。所以，我们还是尽量给表设置主键。

Filwaod

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql45讲笔记

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入数据的。因此，幻读在当前读下才会出现幻读仅专指 “新插入的行”幻读首先会产生语义上的问题，会破坏加锁声明，其次是数据一致性的问题，即使把所有的记录都加上锁，还是阻止不了新插入的记录。
复制链接

扫一扫