目录

MySQL45讲总结

01 | 基础架构:一条 SQL 查询语句是如何执行的?

配置: wait_timeout:控制 Sleep 连接保持时间。线上未配置,默认 8 小时,

SQL 执行步骤

客户端-连接器(管理连接、权限验证)-查询缓存(命中则直接返回,基本没啥用)-分析器(词法分析、语法分析,语法错误在这就能看到了)-优化器(执行计划生成、索引选择)-存储引擎(存储数据、提供读写接口)

server 层

连接器、查询缓存、分析器、优化器、执行器等,涵盖 MySQL 的大多数核心服务功能,以及所有的内置函数(如日期、时间、数学和加密函数等),所有跨存储引擎的功能都在这一层实现,比如存储过程、触发器、视图等。

存储引擎

负责数据的存储和提取。

连接器

Sleep:表示是空闲连接。

数据库长连接和短连接: 长连接是连接成功后,如果客户端持续有请求,则一直使用同一个连接。优点:减少建立连接的动作,尽量使用 短连接则是指每次执行完很少的几次查询就断开连接,下次查询再重新建立一个。

全部使用长连接问题:可能 MySQL 占用内存会涨的很快,因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的,会在连接断开才释放,如果长连接积累,会导致内存占用较大。 解决办法:定期断开长连接;5.7 版本,执行比较大的操作的后,mysql_reset_connection 来重新初始化连接资源,恢复连接到刚刚创建时的状态。miproxy 连接数太多会自己断开

内存:操作系统 10G,应用 8G, 线程级别的内存占用, 一个连接占多大,tcp 占 1M,文件系统描述符。

为啥会用 swap 内存, 操作系统配置:是否使用 swap    cpu 结构有关 对称一致访问:每个核心访问最近的 32G 内存最快,开了的话,cpu0 访问 32G,这个进程用了 32G 就会用 swap 分区。 smp  numa

查询缓存

基本没用了

分析器

词法分析:识别关键字,以及表名之类。 语法分析:根据语法规则判断语法是否错误。

优化器

索引顺序选择,多表 join 顺序等。

执行器

执行前判断对表有没有相应权限。(没分析完词法,不知道是哪张表)

问题: 1.Sleep 连接是不是杀了一直没影响 2.miproxy 对连接的管理:长短连接,长连接的维护。功能怎么实现的, 3.MySQL 内存的使用。(innodb_buffer_size_pool 之外的内存)。 4.数据库权限和表权限验证时间不一样

02 | 日志系统:一条 SQL 更新语句是如何执行的?

线上配置: innodb_flush_log_at_trx_commit:为 1 时保证每次事务的 redo log 都会持久化到磁盘。 sync_binlog:为 1 时保证每次事务的 binlog 都持久化到磁盘。

更新流程:两阶段提交,涉及和 binlog

| | | | | |

redo log 存储引擎层 物理日志(记录在哪个数据页上做了什么修改) 大小固定,循环写
binlog server 层 逻辑日志(记录原始语句,对某行数据做了什么修改) 追加写

redo log:保证了 MySQL crash-safe 能力。记录更新时,先把记录写到 redo log 并更新内存。 binlog:。。, crash-safe:有了 redo-log,可以保证数据库异常重启时,从 redo log 读取后写入内存再写入磁盘,保证数据部丢失。

两阶段提交: 更新写入到内存后,redolog 处于 prapare 阶段,然后写 binlog,再调用引擎的提交事务接口,将 redolog 改成提交状态。 server 是作为一个协调者。innodb 只知道干啥。发起 commit 时候,innodb redo  write  innodb 没完成不能马上写磁盘。

MySQL 磁盘满,会休眠,磁盘满。innodb 队列变长了,可能都建立连接不成功:gdbc 建立连接会 show 变量,要不建立连接不成功,要不就连接数暴涨。 原因:如果在写完 binlog 到提交事务之间 MySQL crash 的问题。或者从备份恢复的时候。

问题: 备份恢复 apply log 是在:将 redo log 的数据写入磁盘吗?

03 | 事务隔离:为什么你改了我还看不见?

线上配置:

事务 ACID:Atomicity、Consistency、Isolation、Durability(原子性、一致性、隔离性、持久性)

多个事务同时执行会出现的问题:脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read) 隔离级别: 读未提交:可以看到未提交的事务 读提交:只有提交的事务才会被看到 可重复读:一个事务执行过程中看到的数据,总是和启动时看到的一样。不管其他的事务有没有启动。 串行化:顾名思义

|

脏读 不可重复读 幻读 视图开启时间(对于一个查询)
读未提交 0 0 0 直接返回记录最新值
读提交 1 0 0 SQL 执行时创建,此时有别的事务修改记录也可以看到
可重复读 1 1 0 事务启动时创建,事务启动后的改动都看不到
串行化 1 1 1 每次都加锁,避免并行

幻读的原因:update 等操作的当前读,需要读取当前的数据,这个时候就会读到最新的更新数据了。表现为:单独 select 没有值,然后 insert 或者 update,发现值已经有了。

MVCC:undo log 实现

04 | 深入浅出索引(上)

innodb 索引类型: 主键索引和:根据叶子节点的内容 主键索引:叶子节点内容是整行数据 非主键索引:叶子节点内容是主键的值。也叫二级索引。使用二级索引,如果想获得整列的值,需要先从索引找到主键值,然后再根据主键查,需要回表。

索引维护: 分裂:某个数据页满了,新插入数据的时候。 合并:相邻两个页删除了数据,利用率很低的时候。 ps:自增主键是不会分裂的,因为一直是增长的。

问题: 1.同时插入较多数据时,索引维护是不是比较占资源。 2.业务字段做主键:只有一个索引;该索引必须是唯一索引?

05 | 深入浅出索引(下)

避免回表的方式: 覆盖索引(组合索引): 索引使用注意: 1.最左前缀原则:不管是对于组合索引,还是对于 2.索引下推:MySQL5.6 优化,多个 and 条件的时候,对索引中的字段先过滤,减少回表次数。 2.索引列使用函数会导致索引失效

06 | 全局锁和表锁 :给表加个字段怎么有这么多阻碍?

加锁范围分类:全局锁、表级锁和行锁。 全局锁 加全局锁方法:Flush tables with read lock (FTWRL)。使用场景:全库逻辑备份(innodb 已经不需要了,可以使用–single-transaction) 表级锁 分为表锁和元数据锁(MDL) 表锁:lock tables … read/write。 元数据锁:当对一个表做增删改查操作的时候,加 MDL 读锁;当要对表做结构变更操作的时候,加 MDL 写锁。所以给小表加字段,如果读取较多的话,也会阻塞,会阻塞所有的查询和更新,如果客户端有重试机制,会导致该库线程打满。

尽量使用 FTWRL 而不是 set global read_only=1: 1.read_only 可能会用来判断是主库还是备库。 2.FTWRL 命令时,如果客户端异常断开,MySQL 会自动释放,set global read_only 会导致 MySQL 一直只读。

07 | 行锁功过:怎么减少行锁对性能的影响?

线上配置: innodb_lock_wait_timeout:10 innodb_deadlock_detect:on 两阶段锁协议:行锁在需要的时候才加上,但是在事务结束后才释放。所以要把可能影响并发的锁放在后面。 死锁和死锁检测策略: 一直等待到超时:innodb_lock_wait_timeout:一直等待锁,直到超时。值不能太大,也不能太小。 死锁检测:发现死锁后,主动回滚死锁链条中的某偶一个事务,让其他事务执行。

问题: 死锁检测成本:每个新来的线程,都要要判断会不会由于自己的加入导致死锁,如果同时更新同一行的线程很多的时候。CPU 消耗。 原理怎么做的。

08 | 事务到底是隔离的还是不隔离的?

undolog:实现了 MVCC 数据可见性,锁是实现隔离级别。 查询和更新是当前读还是一致性读。 一致性读:可重复读隔离级别下,读取数据时,会根据 row trx_id 找到是否能读到当前值,不在的话会根据 redo log 往前找。 当前读:操作数据都是要读当前。可重复读隔离级别下,如果需要更新数据,只能拿最新的数据更新,这个时候虽然别的事务还没有提交,但还是能看到的。 undolog 大小。共享表空间

09 | 普通索引和唯一索引,应该怎么选择?

12 | 为什么我的 MySQL 会“抖”一下?

线上配置: innodb_flush_neighbors:1 innodb_io_capacity:5000 innodb_max_dirty_pages_pct:75

为什么会抖一下: 1.redo log 满了。redo log 中的 checkpoint 前移动,需要刷脏页。(应该避免,因为此时数据库服务已经阻塞,需要控制刷脏页频率)  一般不会设置,不同百分比的时候不一样。 2.内存不足,需要淘汰脏页。buffer_pool     3.日常刷脏页。对应 MySQL 的配置。 4.MySQL 正常关闭。

正常使用的数据库,buffer 一般未被使用的情况比较少,如果一个查询,需要的内存比较大,查询需要从磁盘读取很多页的时候,也要将脏页刷到磁盘中。 可用的页,刷脏页,写到磁盘,redolog checkpoint 往前走,free list flush list lru list

脏页刷盘参考:一个是脏页比例,一个是 redo log 写盘速度。

如果高配机器 redo log 设置比较小的话:redo log 很容易被写满,就会导致经常要因为 redo log 满了而刷盘。表现为:磁盘压力很小,但是数据库出现间歇性的性能下跌

13 | 为什么表数据删掉一半,表文件大小不变?

:怎么正确的回收表空间 innodb_file_per_table:1 默认值 删除整个表数据会删除,但是删除表中的部分数据,空间是怎么回收的。

delete 删除一条记录:将记录标记为已删除,之后可以复用。 delete 删除多条记录:如果整个页被删除,则整页都被标记为可复用。相邻的两页使用率低,会合并。但空间均不会被释放。同理:插入数据,如果当前页已满,肯定会开启新页,也会造成数据空洞。

压缩空间:alter table A engine=InnoDB。5.5 之前不要用,会阻塞 DML。5.6 版本之后:Online DDL(先建新表,并将旧表的更新写在一个文件中,新表搞好之后,再读取文件中的更新。会有很短时间的 MDL 写锁

DDL 过程如果是 Online 的,就一定是 inplace 的?

14 | count(*)这么慢,我该怎么办?

不加 where 条件: MyISAM 引擎:把一个表的总行数存在了磁盘上,因此执行 count(*)的时候会直接返回这个数,效率很高; InnoDB 引擎:数据一行一行地从引擎里面读出来,然后累积计数。 show table status:不准确。

加 where 条件: InnoDB 引擎:选择从引擎读

效率排序:count(字段)<count(主键 id)<count(1)≈count() count():特殊处理过,因为不会为 null,server 会按行累计。

count(1):InnoDB 引擎遍历整张表,但不取值。server 层对于返回的每一行,放一个数字“1”进去,判断是不可能为空的,按行累加。 count(主键 id):InnoDB 引擎会遍历整张表,把每一行的 id 值都取出来,返回给 server 层。server 层拿到 id 后,判断是不可能为空的,就按行累加。 count(字段):根据是否为 not null 进行判断。

15 | 答疑文章(一):日志和索引相关问题

日志相关: 1.两阶段提交不同瞬间 MySQL 异常重启怎么保证数据完整性: 测试

16 | “order by”是怎么工作的?

全字段排序

排序过程对字符串做了“紧凑”处理。即使 name 字段的定义是 varchar(16),在排序过程中还是要按照实际长度来分配空间的。

max_length_for_sort_data

 rowid 排序