mysql 45讲
2023-03-12 23:58:12 1 举报AI智能生成
mysql
作者其他创作
大纲/内容
连接器、查询缓存、分析器、优化器、执行器
server层
数据的存储和提取插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎
存储引擎
MySQL 可以分为 Server 层和存储引擎层两部分
1.一条SQL查询语句是如何执行的
意义：在 MySQL 里也有这个问题，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率。而粉板和账本配合的整个过程，其实就是 MySQL 里经常说到的 WAL 技术，WAL 的全称是 Write-Ahead Logging，它的关键点就是先写日志（内存），再写磁盘，也就是先写粉板，等不忙的时候再写账本。自己理解：磁盘的一块循环空间WAL技术，批量更新，以减少IO寻址次数（查成本）。
write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
crash-safe，粉版上的数据保存下来，未更新到存储引擎上
write pos 和 checkpoint 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
数据结构：redo log 是固定大小的，比如可以配置为一组 4 个文件，每个文件的大小是 1GB从头开始写，写到末尾就又回到开头循环写。
是 InnoDB 引擎特有的日志
redo log（物理日志）
1.实现位置：redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用
2.物理逻辑：redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
3.结构：redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。
statement 格式的话是记sql语
row格式会记录行的内容，记两条，更新前和更新后都有
4.Binlog有两种模式
1.找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库
2.从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻
5.如何恢复到任何时间段
binlog（逻辑日志）
如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致
redolog\"两阶段提交\" 目的：两份日志之间的逻辑一致
更新语句具体操作（两阶段提交）
2.日志系统，更新流程还涉及两个重要的日志
问题：脏读（dirty read）、不可重复读（non-repeatable read）、幻读（phantom read）
实现 多版本并发控制（MVCC）undolog + read-view
长事务意味着系统里面会存在很老的事务视图，这就会导致大量占用存储空间
长事务还占用锁资源，也可能拖垮整个库，这个我们会在后面讲锁的时候展开
不要使用长事务
1.显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
事务的启动方式
隔离级别：读未提交（read uncommitted）读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。读提交（read committed）读提交是指，一个事务提交之后，它做的变更才会被其他事务看到可重复读（repeatable read）一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。串行化（serializable ）顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。
transaction-isolation 的值设置成 READ-COMMITTED
“可重复读”隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；“串行化”隔离级别下直接用加锁的方式来避免并行访问。
Atomicity 原子性Consistency 一致性Isolation 隔离性Durability 持久性
3.事务隔离：为什么你改了我还看不见
B+ 树
主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。
当然有分裂就有合并
页分裂与页合并，性能回受影响
索引维护
InnoDB 的索引模型
4.深入浅出索引（上）
回到主键索引树搜索的过程，我们称为回表
回表
所查的内容在索引里，不用进行回表由于覆盖索引可以减少树的搜索次数，显著提升查询性能，«所以使用覆盖索引是一个常用的性能优化手段；索引字段的维护总是有代价的，需要权衡考虑了；
覆盖索引
B+ 树这种索引结构，可以利用索引的“最左前缀”，来定位记录。如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。
最左前缀原则
只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
MySQL 5.6 之前
MySQL 5.6 引入的索引下推优化
那些不符合最左前缀的部分，如何处理？
select * from tuser where name like '张 %' and age=10 and ismale=1;
索引下推
5.深入浅出索引（下）
MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock
全局锁就是对整个数据库实例加锁。
全局锁的典型使用场景是，做全库逻辑备份。
全局锁
表锁的语法是 lock tables … read/write
表锁
当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
另一类表级的锁是 MDL（metadata lock)
MDL
1.我们要解决长事务，事务不提交，就会一直占着 MDL 锁information_schema 库的 innodb_trx 表中暂停 DDL，或者 kill 掉这个长事务
2.变更的表是一个热点表：比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。
ALTER TABLE tbl_name WAIT N add column ...
如何安全地给小表加字段
2）DDL执行阶段：执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL，但 DML 能可以正常执行。降级EXCLUSIVE-MDL锁，允许读写（copy不可写）。扫描old_table的聚集索引每一条记录rec。遍历新表的聚集索引和二级索引，逐一处理。根据rec构造对应的索引项将构造索引项插入sort_buffer块排序。将sort_buffer块更新到新的索引上。记录ddl执行过程中产生的增量(仅rebuild类型需要)重放row_log中的操作到新索引上(no-rebuild数据是在原表上更新的)。重放row_log间产生dml操作append到row_log最后一个Block。
FRM 表结构文件
onlineDDL
6.全局锁和表锁 ：给表加个字段怎么有这么多阻碍
如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。
在 InnoDB 事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。
innodb_lock_wait_timeout 的默认值是 50s
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
innodb_deadlock_detect 的默认值本身就是 on
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。
解决死锁
假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。
基本思路就是，对于相同行的更新，在进入引擎之前排队
1.中间件实现
比如 10 个记录，影院的账户总额等于这 10 个记录的值的总和。这样每次要给影院账户加金额的时候，随机选其中一条记录来加。这样每次冲突概率变成原来的 1/10，可以减少锁等待个数，也就减少了死锁检测的 CPU 消耗。
2.将一行改成逻辑上的多行来减少锁冲突
怎么解决由这种热点行更新导致的性能问题呢
负面影响
死锁和死锁检测
7.行锁功过：怎么减少行锁对性能的影响
更新完数据库后，查询时再次生成 readview
共享版本链的第一个与当前事务的readview进行比较：1.如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；2.如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；（不可见指针指向上一个版本）3.如果落在黄色部分，那就包括两种情况a. 若 row trx_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见，自己的事务可见；b. 若 row trx_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。
对比规则
在可重复读隔离级别下，只需要在事务开始的时候创建一致性视图，之后事务里的其他查询都共用这个一致性视图；在读提交隔离级别下，每一个语句执行前都会重新算出一个新的视图。
8.MVCC
如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就font color=\"#b71c1c\
change buffer
我从普通索引和唯一索引的选择开始，和你分享了数据的查询和更新过程，然后说明了 change buffer 的机制以及应用场景，最后讲到了索引选择的实践。由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。
9.普通索引和唯一索引，应该怎么选择
使用临时表
是否排序
analyze table t 命令重新计算扫描行数
扫描行数
优化器选择索引
一种方法是，像我们第一个例子一样，采用 force index 强行选择一个索引
第二种方法就是，我们可以考虑修改语句，引导 MySQL 使用我们期望的索引
第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。
选错索引处理
10.mysql 为什么有时候会选错索引（采样评估行数）
1.直接创建完整索引，这样可能比较占用空间；
2.创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引
3.倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
4.创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。
11.怎么给字符串字段加索引（技巧）
当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。
脏页
这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
1.第一种场景是，粉板满了，记不下了
2.“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
3.MySQL 空闲时的操作，这时系统没什么压力
4.数据库本来就要关闭了
子主题
redolog既有内存又有内存
InnoDB 会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用 IO 资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到 MySQL“抖”了一下的原因。要尽量避免这种情况，你就要合理地设置 innodb_io_capacity 的值，并且平时要多关注脏页比例，不要让它经常接近 75%
什么时候flush
而 MySQL 偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）
12.为什么我的MySQL会“抖”一下（buffer pool）
page A 已经满了，这时我要再插入一行数据，会怎样呢？需要去掉空洞；
删除会造成数据空洞，增加也会
与图 3 过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。
现在你已经知道了，如果要收缩一个表，只是 delete 掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过 alter table 命令重建表，才能达到表文件变小的目的。我跟你介绍了重建表的两种实现方式，Online DDL 的方式是可以考虑在业务低峰期使用的，而 MySQL 5.5 及之前的版本，这个命令是会阻塞 DML 的，这个你需要特别小心。
13.为什么表数据删掉一半，表文件大小不变
所以结论是：按照效率排序的话，count(字段)<count(主键 id)<count(1)≈count(*)，所以我建议你，尽量使用 count(*)。
对于 count(字段) 来说：如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加
14.count(*)这么慢，我该怎么办
外部排序一般使用归并排序算法。可以这么简单理解，MySQL 将需要排序的数据分成 12 份，每一份单独排序后存在这些临时文件中。然后把这 12 个有序文件再合并成一个有序的大文件。
如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。
1.全字段排序（一次回表，一次排序）Extra 这个字段中的“Using filesort”表示的就是需要排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。
两次回表
2.rowid 排序（两次回表，一次排序）
font color=\"#e74f4c\
4.索引覆盖（不用排序，不用回表）font color=\"#f44336\
16.“order by”是怎么工作的？
空间复杂度：N个rand()M*log(N)
优先级队列，比归并时间复杂度要小
内存临时表
mysql> select count(*) into @C from t;set @Y = floor(@C * rand()); set @sql = concat(\"select * from t limit \
随机排序方法
select word from words order by rand() limit 3;为每个记录增加随机数，然后取前3
17.如何正确地显示随机消息
案例一：条件字段函数操作
案例二：隐式类型转换
案例三：隐式字符编码转换
18.为什么这些SQL语句逻辑相同，性能却差异巨大；(索引失效)
等 MDL 锁
等 flush
等行锁
第一类：查询长时间不返回
扫描行数多，所以执行慢，这个很好理解。
一个事务for循环更新中。另一个事务查，mvcc会进行版本链的比对，版本链过长会导致慢
第二类：查询慢
19.为什么我只查一行的语句，也执行这么慢
行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。
产生幻读的原因是
间隙锁和 next-key lock 的引入，帮我们解决了幻读的问题，但同时也带来了一些“困扰”，导致同样的语句锁住更大的范围，这其实是影响了并发度的。
解决
因为 RC 级别下是没有间隙锁的。只有 RR 级别才会有间隙锁。
20.幻读是什么，幻读有什么问题（间隙锁）
原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁
索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
优化，锁退化
解决幻读都是建立在RR的隔离级别之上
21.为什么我只改一行的语句，锁这么多（间隙锁退化）
第一种方法：先处理掉那些占着连接但是不工作的线程，show processlist 的结果里，踢掉显示为 sleep 的线程
第二种方法：减少连接过程的消耗。skip-grant-tables 参数MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内；风险高
短连接风暴（连接数突然暴涨）
1.在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；2.执行主备切换；3.这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。
如何无痛的建立索引
索引没有设计好
SQL 语句没写好
MySQL 选错了索引
慢查询性能问题
上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；在测试表里插入模拟线上的数据，做一遍回归测试；观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致 -- （扫描行数）
22.MySQL有哪些“饮鸩止渴”提高性能的方法
sync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；sync_binlog=1 的时候，表示每次提交事务都会执行 fsync；sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。
sync_binlog
binlog
设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。
innodb_flush_log_at_trx_commit
redolog
双1模式
优化方案，组提交
23.MySQL是怎么保证数据不丢的
首先，有些部署条件下，备库所在机器的性能要比主库所在的机器性能差。
第二种常见的可能了，即备库的压力大
因为主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟。delete 太多
这就是第三种可能了，即大事务
来源
主备延迟
24.MySQL 高可用系统的基础，就是主备切换逻辑
SQL语句
statement
当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实删除行的主键 id，这样 binlog 传到备库去的时候，就肯定会删除 id=4 的行，不会有主备删除不同行的问题。
row
1. statement 格式的 binlog 可能会导致主备不一致，所以要使用 row 格式。2. row 格式的缺点是，很占空间。3.mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式。
mixed
binlog 的三种格式对比
死循环，binlog中含有server id，可以解决。
双M结构
25.MySQL 主备复制原理
按表分发
按照行分发
并行复制注意：1：不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。2：同一个事务不能被拆开，必须放到同一个 worker 中
26.备库为什么会延迟好几个小时（MySQL 的各种多线程复制策略）
备库 B 要设置为新主库 A：CHANGE MASTER TO MASTER_HOST=$host_name MASTER_PORT=$port MASTER_USER=$user_name MASTER_PASSWORD=$password master_auto_position=1
我们在实例 B 上执行 start slave 命令，取 binlog 的逻辑是这样的： 1.实例 B 指定主库 A’，基于主备协议建立连接。 2.实例 B 把 set_b 发给主库 A’。 3.实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合， 判断 A’本地是否包含了这个差集需要的所有 binlog 事务。 a. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误； b. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B； 之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行。
实例 A’的 GTID 集合记为 set_a，实例 B 的 GTID 集合记为 set_b。接下来，我们就看看现在的主备切换逻辑
Global Transaction Identifier，也就是全局事务 ID，是一个事务在提交的时候生成的，是这个事务的唯一标识；
基于 GTID 的主备切换
27.主库出问题了，从库怎么办（一主多从的主备切换流程）
直连
目前看，趋势是往带 proxy 的架构方向发展的。
代理
主从分离方案
对于必须要拿到最新结果的请求，强制将其发到主库上；对于可以读到旧数据的请求，才将其发到从库上。
强制走主库方案
以卖家发布商品为例，商品发布后，用 Ajax（Asynchronous JavaScript + XML，异步 JavaScript 和 XML）直接把客户端输入的内容作为“新的商品”显示在页面上，而不是真正地去数据库做查询。这样，卖家就可以通过这个显示，来确认产品已经发布成功了。等到卖家再刷新页面，去查看商品的时候，其实已经过了一段时间，也就达到了 sleep 的目的，进而也就解决了过期读的问题。
执行一条 select sleep(1) 命令。主备延迟在 1 秒之内，做一个 sleep 可以有很大概率拿到最新的数据。
sleep 方案
每次从库执行查询请求前，先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ，那就必须等到这个参数变为 0 才能执行查询请求。
show slave status 结果里的 seconds_behind_master 参数的值，可以用来衡量主备延迟时间的长短。
如果 Master_Log_File 和 Relay_Master_Log_File、Read_Master_Log_Pos 和 Exec_Master_Log_Pos 这两组值完全相同，就表示接收到的日志已经同步完成。
第二种方法，对比位点确保主备无延迟
Auto_Position=1 ，表示这对主备关系使用了 GTID 协议。Retrieved_Gtid_Set，是备库收到的所有日志的 GTID 集合；Executed_Gtid_Set，是备库所有已经执行完成的 GTID 集合。如果这两个集合相同，也表示备库接收到的日志都已经同步完成。
第三种方法，对比 GTID 集合确保主备无延迟：
判断主备无延迟方案
配合 semi-sync 方案
等主库位点方案
等 GTID 方案
其实，在实际应用中，这几个方案是可以混合使用的。比如，先在客户端对请求做分类，区分哪些请求可以接受过期读，而哪些请求完全不能接受过期读；然后，对于不能接受过期读的语句，再使用等 GTID 或等位点的方案。
“过期读” 优化
28.读写分离(过期读)
缺点：无法查到由于并发线程过多导致的数据库不可用的情况。
只能说明这个库的进程还在，并不能说明主库没问题。
select 1 判断
缺点：无法查到空间满了
一般的做法是，在系统库（mysql 库）里创建一个表，比如命名为 health_check，里面只放一行数据，然后定期执行：
查表判断
update mysql.health_check set t_modified=now();
更新判断
29.如何判断一个数据库是不是出问题了（查1，查表，更新表）
用 Flashback工具，原理（反写binlog-row模式）
使用 delete 命令删除的数据，你还可以用 Flashback 来恢复。而使用 truncate /drop table 和 drop database 命令删除的数据，就没办法通过 Flashback 来恢复了。为什么呢？这是因为，即使我们配置了 binlog_format=row，执行这三个命令时，记录的 binlog 还是 statement 格式。binlog 里面就只有一个 truncate/drop 语句，这些信息是恢复不出数据的。
1.把 sql_safe_updates 参数设置为 on。这样一来，如果我们忘记在 delete 或者 update 语句中写 where 条件，或者 where 条件里面没有包含索引字段的话，这条语句的执行就会报错。
2.代码上线前，必须经过 SQL 审计。
日常习惯
1.使用 delete 语句误删数据行；
1.指定一个库，没必要都恢复
2.执行特殊表
快速恢复方法
全量备份，加增量日志的方式
1.账号分离。这样做的目的是，避免写错命令。我们只给业务开发同学 DML 权限，而不给 truncate/drop 权限。而如果业务开发人员有 DDL 需求的话，也可以通过开发管理系统得到支持。即使是 DBA 团队成员，日常也都规定只使用只读账号，必要的时候才使用有更新权限的账号。
2.制定操作规范。这样做的目的，是避免写错要删除的表名。在删除数据表之前，必须先对表做改名操作。然后，观察一段时间，确保对业务无影响以后再删除这张表。改表名的时候，要求给表名加固定的后缀（比如加 _to_be_deleted)，然后删除表的动作必须通过管理系统执行。并且，管理系删除表的时候，只能删除固定后缀的表。
业务补救
2.使用 drop table 或者 truncate table 语句误删数据表
3.使用 drop database 语句误删数据库
4.使用 rm 命令误删整个 MySQL 实例。
30.误删数据后除了跑路，还能怎么办
kill 并不是马上停止的意思，而是告诉执行线程说，这条语句已经不需要继续执行了，可以开始“执行停止的逻辑了”。
第一种情况，线程没有执行到判断线程状态的逻辑。跟这种情况相同的，还有由于 IO 压力过大，读写 IO 的函数一直无法返回，导致不能及时判断线程的状态。
1.超大事务执行期间被 kill。这时候，回滚操作需要对事务执行期间生成的所有新数据版本做回收操作，耗时很长
2.大查询回滚。如果查询过程中生成了比较大的临时文件，加上此时文件系统压力大，删除临时文件可能需要等待 IO 资源，导致耗时较长。
3.DDL 命令执行到最后阶段，如果被 kill，需要删除中间过程的临时文件，也可能受 IO 资源影响耗时较久。
第二种情况，终止逻辑耗时较长。这时候，从 show processlist 结果上看也是 Command=Killed，需要等到终止逻辑完成，语句才算真正完成。这类情况，比较常见的场景有以下几种：
kill 无效
32.为什么还有kill不掉的语句
show processlist 可以查看到server是否正在向client发送
MySQL 是“边读边发的”，这个概念很重要。这就意味着，如果客户端接收得慢，会导致 MySQL 服务端由于结果发不出去，这个事务的执行时间变长。
改进后的 LRU 算法执行流程变成了下面这样在 InnoDB 实现上，按照 5:3 的比例把整个 LRU 链表分成了 young 区域和 old 区域。图中 LRU_old 指向的就是 old 区域的第一个位置，是整个链表的 5/8 处。也就是说，靠近链表头部的 5/8 是 young 区域，靠近链表尾部的 3/8 是 old 区域。图 7 中状态 1，要访问数据页 P3，由于 P3 在 young 区域，因此和优化前的 LRU 算法一样，将其移到链表头部，变成状态 2。之后要访问一个新的不存在于当前链表的数据页，这时候依然是淘汰掉数据页 Pm，但是新插入的数据页 Px，是放在 LRU_old 处。处于 old 区域的数据页，每次被访问的时候都要做下面这个判断：若这个数据页在 LRU 链表中存在的时间超过了 1 秒，就把它移动到链表头部；如果这个数据页在 LRU 链表中存在的时间短于 1 秒，位置保持不变。1 秒这个时间，是由参数 innodb_old_blocks_time 控制的。其默认值是 1000，单位毫秒。
内存的数据页是在 Buffer Pool (BP) 中管理的，在 WAL 里 Buffer Pool 起到了加速更新的作用。而实际上，Buffer Pool 还有一个更重要的作用，就是加速查询。
我用“大查询会不会把内存用光”这个问题，和你介绍了 MySQL 的查询结果，发送给客户端的过程。1.由于 MySQL 采用的是边算边发的逻辑，因此对于数据量很大的查询结果来说，不会在 server 端保存完整的结果集。所以，如果客户端读结果不及时，会堵住 MySQL 的查询过程，但是不会把内存打爆。2.而对于 InnoDB 引擎内部，由于有淘汰策略，大查询也不会导致内存暴涨。并且，由于 InnoDB 对 LRU 算法做了改进，冷数据的全表扫描，对 Buffer Pool 的影响也能做到可控。当然，我们前面文章有说过，全表扫描还是比较耗费 IO 资源的，所以业务高峰期还是不能直接在线上主库执行全表扫描的。
33.我查这么多数据，会不会把数据库内存打爆
直接使用 join 语句，MySQL 优化器可能会选择表 t1 或 t2 作为驱动表（straight_join）
1.从表 t1 中读入一行数据 R；2.从数据行 R 中，取出 a 字段到表 t2 里去查找；3.取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分；4.重复执行步骤 1 到 3，直到表 t1 的末尾循环结束。
非join 执行两次交互，还不如join
select * from t1 straight_join t2 on (t1.a=t2.a);
使用 join 有什么问题
N + N*2*log2M
type = ref
加索引
type = all
不加索引
索引
两个大小不同的表做 join，应该用哪个表做驱动表
更准确地说，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表；
34.到底可不可以使用join
Index Nested-Loop Join(NLJ)被驱动表用索引
这个流程才体现出了这个算法名字中“Block”的由来，表示“分块去 join”
Block Nested-Loop Join(BNL)被驱动表不用索引（block 意思是join buffer 不够分块）
上一节
我们可以看到 Extra 字段多了 Using MRR，表示的是用上了 MRR 优化。而且，由于我们在 read_rnd_buffer 中按照 id 做了排序
批量回表，由log（M）化为 O（M）链表遍历
基本回表流程，非聚集索引，一个一个的回表每一个log（M）
MRR 能够提升性能的核心在于，这条查询语句在索引 a 上做的是一个范围查询（也就是说，这是一个多值查询），可以得到足够多的主键 id。这样通过排序以后，再去主键索引查数据，才能体现出“顺序性”的优势
set optimizer_switch=\"mrr_cost_based=off\"
MRR 优化----Multi-Range Read 优化
从表 t1 里一次性地多拿些行出来，一起传给表 t2。先放到一个临时内存。这个临时内存不是别人，就是 join_buffer。
驱动表 t1，一行行地取出 a 的值，再到被驱动表 t2 去做 join。也就是说，对于表 t2 来说，每次都是匹配一个值。这时，MRR 的优势就用不上了。
Batched Key Access(BKA)
小结
35.join语句怎么优化
36.临时表
去重
untion
不去重
untion all
untion vs untion all
先过滤出 row 到临时表 再排序，再做 partion 分组
普通的gourp by 需要临时表进行排序。
1.索引
在 group by 语句中加入 SQL_BIG_RESULT 这个提示（hint），就可以告诉优化器：这个语句涉及的数据量很大，请直接用磁盘临时表。
2.直接排序
优化
group by
37.group by 实现方法
nnoDB 引擎把数据放在主键索引上，其他索引上保存的是主键 id。这种方式，我们称之为索引组织表（Index Organizied Table）
而 Memory 引擎采用的是把数据单独存放，索引上保存数据位置的数据组织形式，我们称之为堆组织表（Heap Organizied Table）
38.Memory引擎
39.自增主键为什么不是连续的
40
41
42
43
44
45
mysql 45讲
收藏
0 条评论
回复 删除
下一页
