都将会
都将会

🚩MySQL

 都将会 收录于 202310  Interview
  约 4416 字 预计阅读 9 分钟  - 次阅读  

索引

🌟 什么是聚簇索引？

对于每张表，每个索引都对应一个独立的 B+ 树：

  • 聚簇索引（一个）：叶子节点保存了行数据，有且只有一个聚簇索引。
    • 主键 pk > 唯一索引 unique > 自动生成 row_id
  • 非聚簇索引（多个）：叶子节点关联主键，指向了数据的对应行，若没有覆盖索引则需要回表走聚簇索引。

在可重复读（RR）隔离级别下：聚簇索引更新 = 替换，非聚簇索引更新 = 删除+新建

聚簇索引的优点：

  1. 在操作系统中，采用的是按页存储，聚簇索引的主键和行数据是一起被载入内存的。当访问叶子节点的时候，这一页就已经加载到了 Buffer 中，可以直接叶子节点中拿到行数据，减少访问磁盘的时间。
  2. 辅助索引使用主键作为"指针"，而不是使用地址值作为指针的好处是，当出现页分裂的时候，不需要去维护地址值，相当于是一个空间换时间的思想。
  3. 适用于排序的场合、取出范围数据的场合。
  4. 在业务上，可以通过表主键id来聚合数据，通过聚簇索引可以减少磁盘I/O

索引分类

  • 逻辑维度

    • 唯一索引 unique：可以为空，但最多一个空
    • 联合索引：最左匹配原则
    • 全文索引 full text ：支持文本模糊查询用于 char、varchar、text
    • 前缀索引：可以选择在 varchar(128)的列上选择前 32 个字符作为索引
    • 主键索引 primary key：不能为空

  • 物理维度分为聚簇/非聚簇：叶子节点是否存储行数据

  • 覆盖索引：索引包含某次查询的所有列

  • 哈希索引：Innodb 和 MyISAM 都不适用，Memory 引擎支持。

选择索引列

  1. 数据量大、查询频繁的表，涉及 where、orderby、groupby 频繁的字段
  2. 区分度高的字段建立唯一索引或放在左边
  3. 字符串类型优先使用前缀索引，占用空间更小
  4. not null 约束

不选择索引情况

  1. 被频繁更新的字段
  2. 长期未使用的索引：sys 库的schema_unused_indexes视图

🌟索引失效情况？

  1. 没有覆盖索引，如 select *
  2. 联合索引未遵循最左前缀原则
    1. Mysql 从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分
    2. 例如索引是 key index （a,b,c）。 可以支持 a 、a,b 、a,b,c 3 种组合进行查找，但不支持 b,c 进行查找。
  3. 范围查询右边的列会失效，范围过大也会导致全表扫描
  4. 在索引列上运算操作
  5. like ‘%abc’
  6. or 的前后条件中有一个列没有索引，涉及的索引也失效
  7. in()查询时，应该尽可能减少值列表的长度，避免查询转化成过长的 OR 子句，以及避免在查询中使用 NULL 值。

事务

事务执行过程

宕机情形：

  • 在 redo log 刷新到磁盘（事务提交）之前，都是回滚 undo log。
  • 如果在 redo log 已经刷新到磁盘，在 MySQL 重启之后就会回放这个 redo log，以纠正数据库里的数据。

隔离级别

  • 脏读：读到其他事务未提交的数据
  • 不可重复读：同一事务内，先后读取的数据不同，因为其他事务 update 或 delete 会影响结果集。
  • 幻读：同一事务内，先后读取的 count 不同。例如，查询时没有某 id，插入时 id 已存在。
    • 原因：加锁后，不锁定间隙，其他事务可以 INSERT，导致相同的查询在不同的时间得到不同的结果。
    • 解决方案：使用临键锁，对于使用了唯一索引等唯一查询条件，InnoDB 只锁定索引记录，不锁定间隙；对于其他查询条件，InnoDB 会锁定扫描到的索引范围，通过临键锁来阻止其他会话在这个范围中插入新值。
  • RU 读未提交：脏读、不可重复读、幻读都会出现
  • RC 读已提交：解决脏读，每一次快照读时生成 ReadView 读取自己的新快照
    • 多数数据库默认
    • 仅支持基于行的 bin log
    • 当一个事务启动时，数据库会为该事务创建一个版本链，该版本链包含了数据库中所有数据对象的所有版本。当事务读取数据时，数据库会根据事务的启动时间戳选择相应的版本，并且在版本链中添加一个新的版本，以保证该事务所读取的数据不会被其他事务修改。
  • RR 可重复读：解决不可重复读，仅第一次生成 ReadView 的活跃事务 m_ids，后续复用（一致性快照）
    • MVCC = multiple version concurrent control 多版本并发控制
      • 目的：高并发场景下，锁的性能差，MVCC 可以避免读写阻塞
      • DB_ROW_ID：隐藏列
      • DB_ROLL_PIR：回滚指针，指向这条记录的上一版本
        • 版本链存储在 undo log 中
      • DB_TRX_ID：最近修改的事务 id
    • 理论上 RR 存在幻读，但 MySQL 临键锁 next key lock 解决了幻读问题
    • 问：如何保证 REPEATABLE READ 级别绝对不产⽣幻读？
      • 答：在 SQL 中加⼊for update (排他锁) 或 lock in share mode (共享锁)语句实现。就是锁住了可能造成幻读的数据，阻⽌数据的写⼊操作。
  • Serializable 串行化
    • 加锁读：在每一行数据上都加锁，导致大量的超时和锁争用的问题，只有在非常需要确保数据一致性并且可以接受没有并发的情况下，才考虑采用该级别

事务特性 ACID

  • Atomic 原子性
    • 一个事务要么全部提交，要么全部回滚
    • 通过 undo log 记录逻辑日志，回滚时通过逆操作来恢复
      • 保存位置：system tablespace(mysql5.7) 或 undo tablespaces(mysql8.0)
  • Consistent 一致性
    • 事务总是由一种状态转换为另一种状态，而不会停留在执行中的某个状态
    • A、I、D 同时作用，保证了 C（一致性）
  • Isolate 隔离性
    • 读写隔离：MVCC，undo log 版本链（从最新记录依次指向最旧记录的链表），ReadView 在 RC 和 RR 的不同
    • 写写隔离：锁
  • Ddurable 持久性
    • 事务只要提交了，那么数据库中的数据也永久的发生了变化
    • 通过 redo log 增量记录数据页的物理变化，服务宕机时用来同步数据，先写日志，再写磁盘
    • 日志文件：【ib_logfile0】【ib_logfile1】
    • 日志缓冲：innodb_logh_buffer_size
    • 强刷：fsync()

优化

🌟SQL 优化经验？

  1. 只检索需要的列，避免 select *，主查询聚簇索引，子查询覆盖索引
  2. 将 HAVING 中部分条件提前到 WHERE 里
  3. 将 RR 降低为 RC，避免临键锁引发死锁，提高性能。
    1. 可以通过缓存第一次数据，来避免第二次查询，来保证几乎全部事务内部对某一数据都只读取一次
    2. 可以单独指定 Session 或者事务的隔离级别。
  4. 减小事务粒度，常 COMMIT，尽早释放锁
  5. **用 union all **代替 union（去重、排序消耗性能）
  6. where 语句中不使用表达式，防止索引失效
  7. 能用 inner join（优先以小表驱动大表） 就不用 left/right join。
  8. 用 EXPLAIN 返回的预估行数
1

SELECT rows FROM (EXPLAIN SELECT * FROM xxx WHERE uid = 123) a;
  1. 使用视图
  2. 用IN代替OR

如何使用慢 SQL 优化？

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

# 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
# 蛮查询日志存放位置
SET GLOBAL slow_query_log_file = 'var/lib/mysql/slow_query.log'
# 未被索引的记录
SET GLOBAL log_queries_not_using_indexes = 'ON';
# 慢查询阈值（秒）
SET SESSION long_query_time = 1;
# 慢查询记录扫描行数阈值
SET SESSION min_examined_row_limit = 100;

找到慢 SQL 之后，用 EXPLAIN 命令分析：

  1. possible_key：可能用到的索引
  2. key：实际命中的索引
  3. key_len：索引占用的大小
  4. rows：扫描的行数
  5. filtered：所需数据占 rows 的比例
  6. extra：额外的优化建议
    1. Using where；Using Index 用到索引，不用回表
    2. Using index condition 用到索引，需要回表
  7. type：性能从好到差
    1. system：mysql 自带的表
    2. const：根据主键查询
    3. eq_ref：主键索引/唯一索引
    4. ref：索引查询
    5. range：范围查询
    6. index：索引树扫描
    7. all：全盘扫描

应用

连接池对比

  • HikariCP： SpringBoot 框架的默认连接池，性能好
  • Druid：Alibaba 开源的连接池，能防 SQL 注入，带有监控功能

分库分表 - sharding-sphere 中间件

原因：数据库遇到了性能瓶颈。

  • 水平分库：应对大数据，对性能要求不同的数据进行分库 sync_binlog = 0/1
  • 垂直分库：根据业务进行拆分微服务
  • 垂直分表：冷热数据分离（零零播项目中的 user、streamer、owner）

读写分离：主库写，从库读。会涉及一致性问题，解决方案：主从自动切换（keepAlived、云服务提供）

数据迁移

1

mysqldump -u root -p db_name > 'Desktop/test.sql'
  1. 加快导入速度
  • 关闭唯一性检查
  • 开启 extended-insert 将多行合并为一个 INSERT 语句
  • 关闭 binlog，迁移后再开启
  • 调整 redolog 时机
  1. 数据校验
    1. 先读 binlog，不一致再读源表，以源表为准。
    2. 先读从库，不一致再读主库，以主库为准。
  2. 增量同步数据
  • 时间戳
  • binlog

基础概念

结构化查询语言的 6 个部分

  • DDL：Data Define Language，数据定义语言

    • CREATE、ALTER、DROP

  • DML：Data Manipulation Language，数据操作语言

    • INSERT、UPDATE、DELETE

  • DQL：Data Query Language，数据查询语言

    • SELECT、WHERE、ORDER BY、GROUP BY、HAVING

  • TCL：Transactional Control Language，事务控制语言，确保 DML 影响的所有行及时得以更新

    • COMMIT、SAVEPOINT、ROLLBACK

  • DCL：Data Control Language，数据控制语言，实现权限控制

    • GRANT、REVOKE

  • CCL：Cursor Control Language，指针控制语言

    • DECLARE CURSOR、FETCH INTO、UPDATE WHERE CURRENT

MySQL 中的锁

InnoDB 引擎加锁的本质是锁住索引记录（可以理解为 B+树的叶子节点）。在可重复读 RR 的隔离级别下，

  • 一般都加临键锁，左开右闭 （防止幻读）
  • 上界无穷间隙锁
  • 唯一索引等值查询记录锁

区别

binlog 、redolog 和 undolog 的区别？

binlog 是二进制日志，redolog 和 undolog 是事务日志。

  • binlog： 所有引擎增量记录逻辑日志（二进制）
    • 数据库还原
    • 🌟主从同步（Canal 类中间件本质是把自己伪装成从节点）：
      • 主库提交，将 DDL 与 DML 数据写入 binlog；
      • 从库上启动复制
      • 创建IO线程连接主库
      • 主库创建 binlog dump 线程读取数据库事件并发送给 IO线程
      • 从库的IO线程获取到事件数据后，更新从库的中继日志 relay log
      • 从库上的SQL线程读取中继日志 relay log中更新的数据库事件，并应用
    • sync_binlog = 0：写入 page cache 就算成功（默认）
    • sync_binlog = N：每提交 N 次就刷新到磁盘，N 越小性能越差。默认是 1，可以调大，改善性能，但需要承受数据丢失的风险。
  • redolog：InnoDB 循环记录物理日志（某个页的修改），大小固定，写到结尾时(write pos 追上 check point)，会回到开头循环写日志，提供了崩溃恢复能力，保证了持久性。
    • 先更新 buffer pool，再写 redo log，等事务结束后刷盘到磁盘
    • 顺序写，性能优于随机写
    • innodb_flush_log_at_trx_commit = 1 默认，表示事务提交后刷盘，最安全，其次 2,0，但性能会更好。
  • undolog： 记录逻辑相反操作逻辑日志，用于回滚
    • delete：记录主键逻辑删除
    • insert：记录主键
    • update
      • 没有更新主键：主键+原值
      • 更新主键：delete+insert

Innodb 和 MyISAM 的区别？

  1. innodb 支持事务和外键
  2. innodb 默认表锁，使用索引检索条件时是行锁，而 myisam 是表锁（每次更新增加删除都会锁住表）
  3. innodb 和 myisam 的索引都是基于 b+树，但 innodb 的 b+树的叶子节点是存放数据的，myisam 的 b+树的叶子节点是存放指针的
  4. innodb 是聚簇索引，必须要有主键，一定会基于主键查询，但是辅助索引就会查询两次，myisam 是非聚簇索引，索引和数据是分离的，索引里保存的是数据地址的指针，主键索引和辅助索引是分开的。
  5. innodb 不存储表的行数，所以 select count( _ )的时候会全表查询，而 myisam 会存放表的行数，select count(_）的时候会查的很快。
  6. MyISAM 存储限制 256TB，更适合低并发、弱一致性场景；Innodb 存储限制 64TB，更适合高并发、更新频繁的场景。

B+树与 B 树的区别？

  • B 树中每个节点都存储数据，B+树只有叶子节点存储数据 =》 IO 次数少
    • 聚簇索引：行数据
    • 辅助索引：行的主键
    • 非聚簇索引：行所在的地址
  • B+树的键可能会在非叶子节点重复出现
  • B+树的叶子节点之间通过双向链表连接 =》 适合范围查询

delete/truncate/drop 的区别？

  • DELETE 删除表中的行，不删除表本身
    • 项目中一般使用逻辑删除，增加 deleted 字段
  • TRUNCATE 删除表，再创建一个空表
    • 初始化数据
  • DROP 删除表
更新于 2023-10-07 
CC BY-NC 4.0
MySQL
返回 | 主页
0%