MySQL笔记

• 常用命令
  • 在docker上使用MySQL
• MySQL的构成
  • 客户端
  • Server层
  • 存储引擎层
• 常见问题
  • 什么是redo log与binlog
  • 选择唯一索引还是普通索引
  • 衡量索引区分度
  • 隐式类型转换
  • 索引的最左前缀原则
  • 索引中的覆盖索引
  • 死锁
• 学习资料

常用命令

在docker上使用MySQL

# 运行一个版本为8.0.40，操作系统基于debian的MySQL实例
docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:8.0.40-debian

# 启动MySQL实例
docker start some-mysql

# 进入MySQL交互式命令行
docker exec -it some-mysql mysql -uroot -p

MySQL的构成

客户端

mysql命令行工具、各编程语言的mysql客户端，如PHP的PDO for MySQL, python的pymsql等

Server层

1. 连接器
2. 分析器
3. 优化器
4. 执行器

存储引擎层

• InnoDB(当下提到MySQL，默认大多数时候都是指InnoDB)
• MyISAM(很少使用)

常见问题

什么是redo log与binlog

redo log

在极客时间的《MySQL实战45讲》课程中作者提到鲁讯先生的文章《孔乙己》里酒店掌柜的粉板，把redo log比作粉板，是个比较形象的比方。

酒店掌柜有一个粉板，专门用来记录客人的赊账记录。如果赊账的人不多，那么他可以把顾客名和账目写在粉板上。但如果赊账的人多了，粉板总会有记不下的时候，这个时候掌柜一定还有一个专门记录赊账的账本

• 由InnoDB存储引擎维护，是记录数据变更情况的落盘的日志。
• 主要用于崩溃恢复。
• 采用WAL （Write-Ahead Logging)机制,先写redo log日志,再写数据到磁盘。

binlog

• binlog由MySQL Server层维护,与存储引擎无关(适用于所有存储引擎，如InnoDB, MyISAM)。
• 主要用于主从复制（Replication）和数据恢复（Point-in-Time Recovery，PITR）。
• 记录的是SQL语句（DML、DDL）,不会记录对数据变更无影响的SELECT语句

选择唯一索引还是普通索引

引出change buffer

认识change buffer

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

change buffer要点

• merge: 将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。除了访问这个数据页会触发 merge外，系统有后台线程会定期merge。在数据库正常关闭（shutdown）的过程中，也会执行merge操作。
• 目的： change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。
• 注意: 唯一索引无法使用change buffer，原因是为了确保数据的完整性和唯一性约束，每次写入操作都需要立即更新索引页，而change buffer是为了延迟更新而设计的。

总结

由于唯一索引用不上change buffer的优化机制，如果业务可以接受，从性能角度出发应该优先考虑非唯一索引。在强烈需要数据库保证表中某一列的值不能重复的情形下，该用就用。

衡量索引区分度

在MySQL中，索引区分度（也称为选择性）是衡量索引效率的重要指标。区分度越高，索引的效果通常越好，因为它能够更有效地缩小查询范围。区分度的计算公式如是：区分度 = 不同索引值的数量 / 总行数。区分度的值位于0到1之间，趋近于0说明重复值多，选择性低，不适合建索引；趋近于1说明重复值少，选择性高，适合建索引。

• 非前缀索引的区分度 = count(distinct(column_name)) / count(*)
• 前缀索引的区分度 = count(distinct(left(column_name, index_length))) / count(*), index_length为前缀索引指定的长度

隐式类型转换

• 当运算符操作的数据类型不一样时，系统会自动进行类型转换，让它们变得兼容。有些转换是自动发生的。
• 当MySQL需要将索引列的值进行类型转换时，索引通常会失效，因为索引是基于原始数据类型的。
• 在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。
• 隐式类型转换可能导致无法使用索引、查询结果不准确等。
• 例子：假如bill表里有个索引字段为order_no char(32)，写的SQL语句是SELECT * FROM bill WHERE order_no=20250212000001;, 但是实际上MySQL内部经优化器处理后执行的实际SQL可能为SELECT * FROM bill WHERE CAST(order_no AS signed int)=20250212000001

索引的最左前缀原则

当查询条件中使用了多个列的索引时，索引的匹配顺序要从左到右逐列匹配。也就是说，只有在查询条件中列的顺序与索引定义的顺序一致时，索引才会被有效使用。

索引中的覆盖索引

覆盖索引指的是，查询的所有列（包括SELECT的字段、WHERE子句中的条件、ORDER BY排序字段等）都已经包含在索引中，因此MySQL可以通过索引本身来返回结果，而不需要回表查找数据表中的实际行。

• 覆盖索引并不是一种特定的索引数据结构，而是指行为结果。即索引覆盖了查询的所有列。
• 回表：国内业内喜欢说的“回表”，指在查询时，索引只包含查询所需的一部分数据，而查询所需的其他数据必须通过索引找到的主键（或唯一键）回到数据表中去查找。这是因为索引本身仅存储了索引列的数据，并不存储整个表的所有字段数据。
• 最常见于使用联合索引 （composite index）的场景

死锁

什么是死锁

两个或多个事务在执行过程中，相互等待对方释放资源，导致无法继续执行，从而形成了一个闭环的等待状态。每个事务都在等待对方释放它需要的资源，导致无法完成操作，最终需要数据库系统介入来解决死锁问题，通常通过回滚其中一个事务来打破死锁。

如何应对死锁

• 如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。
• 对同一组资源(数据表)，要按照尽量相同的顺序访问

学习资料

• MySQL 实战 45 讲
• MySQL Glossary