MySQL

⋯
  1. 定位慢查询:

    1. 开启慢查询日志。设置 long_query_time 阈值(2秒),分析慢查询日志。

    2. 使用 EXPLAIN 分析执行计划。观察是否使用索引、扫描行数、Extra信息。

    3. 通过 SHOW PROFILE 或 performance_schema 查看SQL各阶段耗时。

  2. 分析和优化:

    1. 检查是否命中索引, EXPLAIN 中 key 为NULL表示未用索引,需添加或优化索引。

    2. 避免全表扫描,为WHERE、JOIN、ORDER BY字段创建合适索引。

    3. 优化SQL写法,避免 SELECT * 、函数操作索引列、隐式类型转换等。

    4. 分析数据量,若数据量过大,考虑分库分表或归档历史数据。

    5. 检查锁竞争,使用 SHOW PROCESSLIST 查看是否有锁等待。

先确认慢在哪里

  • 通过慢查询日志、Performance Schema 等找到具体 SQL,记录实际参数、执行频率、耗时和扫描行数,避免只拿一个命中数据很少的参数做判断。

  • 看是不是锁等待,结合进程状态、事务和锁等待信息排查。SQL 等锁很久,和执行计划扫描很慢,需要不同的处理方式。

  • 检查数据库当时的 CPU、I/O、连接数和 Buffer Pool 等情况,判断是个别 SQL 问题,还是整体资源已经接近瓶颈。

再分析执行计划和 SQL

  • 用 EXPLAIN 看扫描方式、实际索引、估算行数、回表、排序和临时表;必要时用 EXPLAIN ANALYZE 对比实际行数与估算,注意它会真正执行查询。

  • 根据 WHERE、JOIN、ORDER BY 设计合适的联合索引,尽量使用覆盖索引,避免对普通索引列做不必要的函数和类型转换。

  • 只查询需要的列和行,避免无条件 SELECT *。联表查询要关注过滤后的数据量、连接算法和关联字段索引,不能只根据表的总行数背「小表驱动大表」。

  • 深分页可以使用游标分页,或者先通过覆盖索引定位少量主键再回表;大批量更新可以在业务允许时分批处理。

  • 如果估算明显失真,可以评估更新统计信息,再对比计划和实际效果。

最后考虑结构和架构

  • 高频热点查询可以考虑缓存或汇总表,同时设计缓存一致性、失效和热点保护方案。

  • 大字段和低频字段可以结合访问模式拆分,减少常用查询读取的数据量。

  • 明确单机资源或写入吞吐已经成为瓶颈后,再评估读写分离、数据归档、分库分表。

使用 EXPLAIN 分析执行计划时,主要关注以下字段:

  • type:表示连接类型,从好到差依次为 system、const、eq_ref、ref、range、index、ALL。至少应达到 range 级别,避免 ALL 全表扫描。

  • key:实际使用的索引。如果为 NULL,则没有使用索引。

  • key_len:使用的索引键长度,越短越好,可判断是否使用了联合索引的全部或部分。

  • rows:预估需要扫描的行数,越小越好。

  • Extra:额外信息,常见的有 Using index(覆盖索引)、Using where(使用 where 条件过滤)、Using index condition(索引下推)

MySQL有哪些索引

MySQL可以按照四个角度来分类索引。

  • 按「数据结构」分类:B+tree索引、Hash索引、Full-text索引。

  • 按「物理存储」分类:聚簇索引(主键索引)、二级索引(辅助索引)。

  • 按「字段特性」分类:主键索引、唯一索引、普通索引、前缀索引。

  • 按「字段个数」分类:单列索引、联合索引。

按数据结构维度分:

  • BTree 索引:MySQL 里默认和最常用的索引类型。只有叶子节点存储 value,非叶子节点只有指针和 key。存储引擎 MyISAM 和 InnoDB 实现 BTree 索引都是使用 B+Tree。

  • 哈希索引:类似键值对的形式,一次即可定位。

  • RTree 索引:一般不会使用,仅支持 geometry 数据类型,优势在于范围查找,效率较低,通常使用搜索引擎如 ElasticSearch 代替。

  • 全文索引:对文本的内容进行分词,进行搜索。目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引。一般不会使用,效率较低,通常使用搜索引擎如 ElasticSearch 代替。

按底层存储方式角度分:

  • 聚簇索引:索引结构和数据一起存放的索引,InnoDB 中的主键索引就属于聚簇索引。

  • 非聚簇索引:索引结构和数据分开存放的索引,二级索引就属于非聚簇索引。MySQL 的 MyISAM 引擎,不管主键还是非主键,使用的都是非聚簇索引。

按应用维度分:

  • 主键索引:加速查询 + 列值唯一(不可以有 NULL)+ 表中只有一个。

  • 普通索引:仅加速查询。

  • 唯一索引:加速查询 + 列值唯一(可以有 NULL)。

  • 覆盖索引:一个索引包含(或者说覆盖)所有需要查询的字段的值。

  • 联合索引:多列值组成一个索引,专门用于组合搜索,其效率大于索引合并。

  • 全文索引:对文本的内容进行分词,进行搜索。目前只有 CHAR、VARCHAR、TEXT 列上可以创建全文索引。一般不会使用,效率较低,通常使用搜索引擎如 ElasticSearch 代替。

  • 前缀索引:对文本的前几个字符创建索引,相比普通索引建立的数据更小,因为只取前几个字符。

MySQL索引什么时候会失效?

  • 左模糊匹配:LIKE '%小林'、LIKE '%小林%' 不能按普通 B+ 树索引的前缀定位,LIKE '小林%' 则可能使用范围扫描。

  • 对索引列使用函数或计算:比如 YEAR(created_at) = 2024、price + 1 = 10,普通索引通常不能直接用于定位。可以改成时间范围,或者改写为 price = 9;MySQL 8.0 的函数索引也可以服务匹配的表达式。

  • 隐式类型转换:比如 phone 是 VARCHAR,却写 phone = 13800138000,可能需要把索引列转换成数字再比较。应该保持参数类型和列类型一致。

  • 缺少联合索引最左列:例如只有 (a, b, c),却只按 b 查询,通常不能按最左前缀定位。

  • OR 的某个分支无法利用索引:比如 a 有索引、b 没有索引,a = 1 OR b = 2 往往需要全表扫描。多个分支都有索引时,也可能使用 index_merge,但是否采用仍取决于成本。

  • MySQL索引(主要是InnoDB)使用B+树作为数据结构。

  • 为什么选B+树:

    • 相比B树:B+树所有数据存储在叶子节点,非叶子节点只存索引,查询更稳定(每次都要到叶子),且叶子节点形成有序链表,范围查询效率高。

    • 相比二叉树:二叉树在数据量大时树高过高,磁盘I/O次数多,B+树是多路搜索树,矮胖,减少I/O。

    • 相比哈希表:哈希表仅支持等值查询,不支持范围查询和排序,B+树支持范围查询和排序。

    • 磁盘I/O优化:B+树节点大小通常设为页大小(16KB),一次I/O能加载更多数据,减少磁盘访问次数。

  • 聚簇索引:将数据行和索引存储在一起,索引的叶子节点直接包含完整行数据。InnoDB中,主键索引即为聚簇索引。

  • 非聚簇索引(二级索引):索引的叶子节点只包含索引列和主键值,不包含完整行数据。

  • 区别:

    • 聚簇索引每个表只能有一个,非聚簇索引可以有多个。

    • 聚簇索引的查询速度快(直接获取数据),但插入和更新可能引起页分裂。

    • 非聚簇索引查询时可能需要回表(通过主键查找聚簇索引)。

  • 回表查询:当使用非聚簇索引查询时,如果查询的列不在索引中,需要先通过索引找到主键,再通过主键到聚簇索引中获取完整行数据,这个过程称为回表。

  • 覆盖索引:如果索引包含了查询所需的所有列,则无需回表,直接从索引中获取数据,称为覆盖索引。

  • 最左前缀原则:联合索引的查询必须从索引的最左列开始,不能跳过中间的列,否则后面的列无法使用索引。

  • 联合索引 (a,b,c) 生效条件:

    • 查询条件中包含a时,才能使用该索引。

    • 如果只包含a,则使用a索引。

    • 如果包含a和b,则使用a和b部分。

    • 如果包含a、b、c,则全部使用。

    • 如果条件中跳过b(只包含a和c),则只能使用a列索引,c列无法使用。

    • 范围查询(>、<、between)会导致后面的列失效,如 WHERE a=1 AND b>2 AND c=3 ,则c无法使用索引。

分库分表了解么

  • 分库分表是应对大数据量、高并发场景的数据库拆分策略:

    • 垂直分库:按业务模块拆分到不同数据库(用户库、订单库)。

    • 垂直分表:将大表按列拆分成多个小表(将长文本字段拆分出去)。

    • 水平分库:将同一张表的数据按规则(取模、范围)分布到多个数据库实例。

    • 水平分表:将一张表的数据按规则拆分到多个结构相同的表中(可放在同一库或不同库)。

MySQL 一张表的数据大小理论上受限于操作系统的文件大小上限以及存储引擎的限制。以 InnoDB 为例:

  1. 表空间文件(.ibd)最大可达到 64TB(取决于页大小和文件系统)。InnoDB的页大小默认为 16KB,每个表空间最多可容纳 2^32 个页,因此理论最大容量为 16KB * 2^32 = 64TB。

  2. 实际限制还受操作系统文件大小限制(ext4 最大单个文件 16TB)、MySQL 配置等因素影响。

  3. 计算方式:数据大小 = 行大小 × 行数。行大小受列类型限制,实际表大小取决于数据量。但 InnoDB 表空间可以自动扩展,只要磁盘空间允许,可以存储海量数据。

  • 事务隔离级别:

    • 读未提交:可能脏读、不可重复读、幻读。

    • 读已提交:避免脏读,但可能不可重复读、幻读。

    • 可重复读:MySQL默认级别,避免脏读、不可重复读,但可能幻读。

    • 可串行化:最高级别,强制事务串行执行,避免所有问题,但并发性能差。

  • 默认事务隔离级别是可重复读

什么是脏读、不可重复读、幻读?

  • 脏读:一个事务读取到另一个事务未提交的数据。

  • 不可重复读:同一事务内两次读取同一数据,结果不一致(因其他事务修改并提交)。

  • 幻读:同一事务内两次查询得到的记录数不同(因其他事务插入或删除)。

事务的四大特性(ACID)分别是什么

  1. 原子性(Atomicity):事务中的所有操作要么全部成功,要么全部失败回滚。

  2. 一致性(Consistency):事务执行前后,数据库完整性约束不被破坏。

  3. 隔离性(Isolation):并发事务之间互不干扰,通过隔离级别实现。

  4. 持久性(Durability):事务提交后,修改永久保存到数据库。

MySQL事务四大特性的实现怎么保证的?

  • 原子性是通过 undo log(回滚日志)来保证的;

  • 一致性需要这些机制、数据库约束和正确的业务逻辑一起保证。

  • 隔离性是通过 MVCC(多版本并发控制)或锁机制来保证的;

  • 持久性是通过 redo log(重做日志)来保证的;

  1. binlog(二进制日志):MySQL Server层日志,记录所有修改操作(逻辑日志),用于主从复制和数据恢复。

  2. redolog(重做日志):InnoDB特有,物理日志,记录数据页的修改,用于崩溃恢复。

  3. undolog(回滚日志):InnoDB特有,逻辑日志,记录数据修改前的值,用于事务回滚和多版本并发控制(MVCC)。

  4. 隔离性如何保证(MVCC):

    1. MVCC 的核心是「隐藏字段 + undo 版本链 + Read View」。

    2. 在可重复读隔离级别下,事务开始时创建Read View,查询时只读取版本早于该视图的已提交数据,避免脏读和不可重复读。

    3. 通过undolog构建历史版本链,实现非锁定读。

具体讲一下redolog、undolog的作用

  • redo log:用于保证事务的持久性。记录的是物理修改。事务提交时,必须先将 redo log 写入磁盘。当数据库宕机重启时,通过 redo log 重做已提交但未刷盘的数据,恢复数据到最新状态。

  • undo log:用于保证事务的原子性和实现 MVCC。记录的是逻辑修改的逆操作。当事务回滚时,通过 undo log 将数据恢复到事务开始前的状态。同时,在 MVCC 中,undo log 用于构建旧版本数据,供其他事务读取快照。

  • MySQL 集群的主从复制过程梳理成 3 个阶段:

    • 写入 Binlog:主库执行数据修改,在提交阶段记录并提交相应 binlog 事务。

    • 同步 Binlog:把 binlog 复制到所有从库上,每个从库把 binlog 写到暂存日志中。

    • 回放 Binlog:回放 binlog,并更新存储引擎中的数据。

  • 读写分离:基于主从复制,将写操作指向主库,读操作(SELECT)指向从库,分摊主库压力,提高并发能力。需注意数据延迟和事务一致性。

MySQL和Redis的区别

  • 存储介质:MySQL基于磁盘(内存+磁盘);Redis基于内存(可持久化)。

  • 数据模型:MySQL是关系型数据库,支持SQL、事务、复杂查询;Redis是键值存储,支持多种数据结构,但不支持SQL。

  • 性能:Redis读写性能极高(微秒级);MySQL相对较慢(毫秒级)。

  • 持久化:MySQL默认持久化;Redis需配置持久化,可能丢数据。

  • 适用场景:MySQL用于结构化数据存储、复杂查询;Redis用于缓存、计数器、实时排行榜等。

sql语句中union和union all有什么区别?

  • UNION:用于合并两个或多个 SELECT 语句的结果集,并自动去除重复的行(对结果进行 distinct 操作)。性能相对较低,因为需要排序和去重。

  • UNION ALL:也用于合并结果集,但保留所有行,包括重复行。性能更高,因为没有去重开销。


复盘手记

搜索笔记

输入关键词,搜索所有主题与问答。