MySQL

概念

查询语句的执行顺序

(8) SELECT
(9) DISTINCT <column>,
(6) AGG_FUNC <column> or <expression>, ...
(1) FROM <left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(7) HAVING <having_condtion>
(10) ORDER BY <order_by_list>
(11) LIMIT <limit_number>;

存储引擎
- InnoDB
  - 事务支持、行级锁、外键约束
  - 通过redo log日志实现了崩溃恢复
- MyISAM
  - 不支持事务、行级锁、外键约束
  - 不支持崩溃恢复
  - 非聚簇索引，索引保存的是数据文件的指针
  - 存储了整个表的总行数，count(*)查询快
数据文件
- db.opt：当前数据库的默认字符集和字符校验规则
- .frm：表结构信息
- .ibd：表的数据、索引等

优化

怎样定位慢查询？
- 运维工具，如Skywalking等
- MySQL提供的慢查询日志
如何分析一个执行很慢的SQL语句？
使用EXPLAIN关键字。例如：EXPLAIN SELECT * FROM user WHERE id = 1;
- type
  - all、index、range、ref、eq_ref、const、system
- possible_keys
- key
- key_len
索引
- 索引是MySQL为表创建的数据结构，用于高效查找数据。
- 提高数据检索的效率，减低数据库的IO成本。
- 通过索引列队数据进行排序，降低了数据排序的成本。
索引的底层数据结构
MySQL的InnoDB引擎使用B+树作为索引的底层数据结构。
- 阶数更多，层数更小。
- 非叶子结点只存储指针和键值，叶子结点存储数据，磁盘读写代价更低。
- 叶子结点是一个有序的双向链表，便于全表扫描和区间查询。
聚簇索引和非聚簇索引（二级索引）
- 聚簇索引：有且只有一个。索引和数据存储在一起，索引结构的叶子结点保存了行数据。
  - 主键、唯一索引、隐含的行号。
- 非聚簇索引：可以存在多个。索引结构与数据分开存储，索引结构的叶子节点保存的是对应的主键。
回表查询
通过非聚簇索引查询得到主键值，然后通过聚簇索引查询整行数据。
覆盖索引
查询时使用了索引，并且需要返回的列在该索引中已经全部能够找到。
索引下推（Index Condition Pushdown, ICP）
将部分WHERE条件的过滤操作下放到存储引擎层，在遍历索引时提前过滤数据，减少回表次数。
例如：
1
2
3
4
5
6
7
8
9
10
-- 表结构
CREATE TABLE user (
id INT PRIMARY KEY,
name VARCHAR(32),
age INT,
INDEX idx_name_age (name, age)
);
-- 查询
SELECT * FROM user
WHERE name = 'Alice' AND age > 30;
- 无ICP ：存储引擎遍历idx_name_age找到所有name=’Alice’的主键，回表获取完整行后，Server层再过滤age > 30。
- 有ICP ：存储引擎在遍历idx_name_age时，直接过滤掉age <=30的记录，仅回表剩余符合条件的主键。
超大分页怎么处理？
在数据量很大时，使用limit进行分页会存在性能问题。可以通过覆盖索引获取目标数据的id，然后子查询过滤出目标数据。
索引的创建原则
- 数据量大，查询比较频繁的表
- 常用作查询条件、排序、分组的字段
- 字段内容的区分度高
- 内容较长时，可以使用前缀索引
- 尽量使用联合索引
- 要控制索引的数量
- 注意NULL值
索引失效
- 违反最左前缀法则
- 范围查询右边的列，索引失效
- 对索引列进行运算操作，索引失效
- 以%开头的LIKE模糊查询，索引失效
- 使用OR连接非索引的列
- 字符串不加单引号，发生类型转换，造成索引失效
SQL优化
- 表的设计优化
  - 选择合适的数据类型
- 索引优化
- SQL语句的优化
  - 指明字段名称，尽量避免直接使用SELECT *
  - 尽量避免索引失效的写法
  - 避免在where子句对字段进行表达式操作
  - 连接时，尽量使用内连接inner join。
  - 连接时，优先把小表放在外边。
  - 尽量用union all（不去除重复）代替union（去除重复）。
- 主从复制、读写分离
- 分库分表
  - 数据量很大时，使用分库分表。

事务

事务的特性
- 原子性（atomicity）：一个事务中的所有操作，要么都成功，要么都失败。
- 一致性（consistency）：事务完成前后，数据库都保持一致的状态。
- 隔离性（isolation）：并发执行的各个事务之间不会相互影响。
- 持久性（durability）：事务一旦提交或回滚，对数据库的修改就是永久的。
并发事务带来的问题
- 脏读：一个事务读取了另一个事务未提交的数据。
- 不可重复读：一个事务先后读取同一条数据，结果不一致。
- 幻读：一个事务查询某条数据时，没有对应的数据行。但是插入数据时，又发现这条数据已经存在。
隔离的级别
- 读未提交：允许脏读、不可重复读、幻读。
- 读已提交：允许不可重复读、幻读。
- 可重复读：允许幻读。（MySQL默认的隔离级别）
  - 尽量在开启事务之后，马上执行select ... for update这类锁定读的语句。
- 串行化：不允许脏读、不可重复读、幻读。
MVCC（多版本并发控制）
- 实现方式
  - 聚簇索引记录中的隐藏列
    - trx_id：最近更新该记录的事务ID。
    - roll_pointer：指向回滚日志（undo log）中旧版本记录的指针。
  - Read View
- Read View
  - creator_trx_id：创建该Read View的事务ID。
  - m_ids：创建该Read View时，活跃且未提交的事务ID列表。
  - min_trx_id：最小的活跃且未提交的事务ID。
  - max_trx_id：创建该Read View时，将分配的下一个事务ID。
- Read View的创建时机
  - 读已提交：每个select语句前重新生成一个新的Read View。
  - 可重复读：执行第一条select时，生成唯一的一个Read View。
undo log和redo log的区别
- undo log回滚日志：记录了数据被修改前的信息，提供回滚和MVCC（多版本并发控制），保证事务的原子性和一致性。
- redo log重做日志：记录了事务提交时对数据页的物理修改，用来实现事务的持久性。
如何保证事务的隔离性
- 锁
  - 共享锁
  - 排它锁
- MVCC（多版本并发控制）
MySQL的锁
- 全局锁
  - 全局逻辑备份
- 表级锁
  - 表锁
  - 元数据锁
    - 对表数据进行操作时添加读锁
    - 对表结构进行操作时添加写锁
  - 意向锁
    - 作用：快速判断表里是否有记录被加锁
- 行级锁
- 记录锁（Record Lock）
  - 锁定索引记录
- 间隙锁（Gap Lock）
  - 锁定索引记录的间隙
  - 作用：解决可重复读隔离级别下的幻读现象
- Next-Key Lock
  - 记录锁和间隙锁的结合
MySQL的日志
- redo log（重做日志）：存储引擎层日志
  - 实现事务的持久性。
- undo log（回滚日志）
  - 实现事务的原子性。
- bin log（二进制日志）：server日志
  - 记录了数据库的修改信息（DDL、DML），用于数据备份和主从复制
- relay log（中继日志）
  - 用于主从复制
- 慢查询日志
MySQL的主从同步原理
- 二进制日志Binlog：记录了数据库的修改信息（DDL、DML）。
- 复制步骤：
  1. 主库输出线程：主库在事务提交时，会将数据变更记录在二进制日志中。
  2. 从库IO线程：从库读取主库的二进制日志文件，写入到从库的中继日志中Relay log。
  3. 从库SQL线程：从库重做中继日志，将数据变更应用到从库上。
分库分表
- 垂直分库：以表为依据，根据业务将不同表拆分到不同库中
  高并发下提高磁盘IO和网络连接数
- 垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中
  冷热数据分离，多表互不影响
  - 将不常用的字段单独放在一张表
  - 将大字段拆分到一张表中
- 水平分库：将一个库的数据拆分到多个库中
  解决海量数据存储和高并发问题
- 水平分表：将一个表的数据拆分到多个表中
  解决单表存储和性能问题
分库带来的问题
使用中间件mycat等进行解决
- 分布式事务一致性问题
- 跨节点关联查询
- 跨节点分页、排序
- 主键唯一性问题