Tia

MySQL

前言

整理一下mysql的一些基本知识点

优化

定位慢查询sql

通常出现在，聚合查询，多表查询，表数据量过大查询，深度分页查询

  1. 外部工具

    1. Arthas(监控已经上线项目，记录慢方法)，Prometheus，Skywalking（记录接口响应时间）

  2. 内部日志

    1. 慢日志查询，/etc/my.cnf show_query_log = 1 #开启慢日志查询开关 long_query_time = 2 #时间单位是秒

如何分析慢查询

  • 使用EXPLAIN / DESC 关键字

    字段名

    含义

    优化建议

    id

    查询的执行顺序和优先级

    优先优化 id 值大的语句

    select_type

    查询的类型

    确保派生表和子查询的效率

    SIMPLE：简单查询，没有子查询。

    PRIMARY：最外层查询。

    SUBQUERY：子查询。

    DERIVED：派生表（子查询的结果作为临时表）。

    UNION：UNION 中的查询。

    table

    当前步骤访问的表

    注意优化复杂查询中的临时表

    partitions

    匹配的分区

    分区表优化相关

    type

    表的访问方式（性能优劣排序）

    避免 ALL 全表扫描

    system > const > eq_ref > ref > range > index > ALL

    possible_keys

    查询中可能使用的索引

    确保查询条件中使用了索引列

    key

    实际使用的索引

    若为 NULL，需检查是否需要创建索引

    key_len

    使用索引的长度

    确保索引覆盖了查询条件

    ref

    索引比较对象

    确保通过条件有效筛选

    rows

    预估需要扫描的行数

    扫描行数越少，性能越高

    filtered

    返回结果占扫描行数的百分比

    值越接近 100%，过滤条件越高效

    Extra

    补充信息，如临时表、排序等

    避免使用临时表、文件排序

    Using index：使用覆盖索引，性能较优。

    Using where：通过 WHERE 过滤条件筛选数据。

    Using temporary：使用了临时表，需优化。

    Using filesort：需要额外的排序步骤，尽量优化索引支持排序。

    Using index condition：MySQL 使用了索引条件下推

在没有使用ICP的情况下，MySQL的查询：

  • 存储引擎读取索引记录；

  • 根据索引中的主键值，定位并读取完整的行记录；

  • 存储引擎把记录交给Server层去检测该记录是否满足WHERE条件。

使用ICP的情况下，查询过程：

  • 存储引擎读取索引记录（不是完整的行记录）；

  • 判断WHERE条件部分能否用索引中的列来做检查，条件不满足，则处理下一行索引记录；

  • 条件满足，使用索引中的主键去定位并读取完整的行记录（就是所谓的回表）；

  • 存储引擎把记录交给Server层，Server层检测该记录是否满足WHERE条件的其余部分。

索引概念和索引底层数据结构

索引是帮助MySQL高效获取数据的数据结构，主要是用来提高数据检索的效率，降低数据库IO成本，降低数据库排序的成本，降低CPU消耗，mysql默认的存储引擎InnoDB采用的是B+树的数据结构来存储索引，选择B+树的主要原因有：

1、阶数更多，路径更短，查询更快。

2、磁盘读写代价B+树更低，非叶子节点只存储指针，叶子节点存储数据。

3、B+树便于扫库和区间查找，因为叶子节点是一个双向链表。

  • MyISAM： 这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。

  • InnoDB： InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能，也是目前MySQL默认的存储引擎。

  • Memory： 这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。

  • archive： 这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。

mysql默认使用了InnoDB存储引擎，索引底层使用B+树。

二叉树容易出现最坏二叉树（所有子节点都在一边），红黑树（比二叉树平衡，如果数据量大，搜索深度很深），B树（多叉路的平衡查找树，每个节点有多个分支）

B树

B+树，非叶子节点不存储数据

磁盘读写代价B+树更低，查找效率更稳定，因为在叶子节点之间使用双向指针进行连接，所以在范围查找时更快。

聚集索引和非聚集索引

分类

含义

特点

聚集索引

将数据存储和节点放到了一起，索引结构的叶子节点保存了行数据

必须有，唯一，不为空,通常为主键

二级索引

将数据和索引分开存储，索引结构的叶子节点关联的是对应主键

存在多个

回表查询

-- 表table建立了聚集索引id,普通索引name
select age from table where name = 'alex'

因为通过普通索引查出来的叶子节点上只有对应主键，和对应的索引值。所以还要通过主键id去查询出整行数据，这个回查叫回表查询。

避免回表查询 1、只查询对应的主键和对应的索引值。2、或者创建联合索引。3、5.6之后版本使用了索引条件下推，可以减少回表数量

覆盖索引和超大分页优化

覆盖索引：查询使用了索引，并且需要返回的列，在该索引中可以全部找到

超大分页查询（覆盖索引和子查询）

-- 先通过分页查询id字段（覆盖索引），再通过id进行自我关联（子查询）
select a.* from t5 a 
inner join (select id from t5 order by text limit 1000000, 10) b 
on a.id=b.id;

索引创建原则

索引失效情况

1、违反最左前缀法则

-- 使用联合索引时，where条件需要符合创建的组合索引的顺序，例如创建索引顺序（name,age,status）
select * from table where name = 'alex' and status = 1; -- 跳过一个索引，只走name的索引，status没有索引。
select * from table where age = 10 and status = 1; -- 开头查询不是联合索引的第一个，不走索引
select * from table where age = 10 and name= 'alex'; -- 包含索引前两个，走索引（字段顺序可以打乱）

2、范围查询右边的条件不能用到索引

select * from table where name = 'alex' and age >= 10 and status = 1; -- 只使用了name,alex的索引，右边的status索引失效

3、索引字段上进行运算操作

select * from table where substring(name,3,2) = 'nametext' -- 使用name索引时，进行了运算操作

4、查询字段类型转换

5、使用模糊查询时,%放在前面

6、使用 OR 连接多个条件

索引选择性太差也会进行全表扫描

当查询中包含 OR 时，如果 OR 连接的多个条件中有一个没有使用索引，可能会导致索引失效

相关实际优化经验

有一张人员大表，需要连接公司，部门，岗位等子表，30w+数据，预计之后还会更多，公司合并什么的（主要根据，公司，部门，姓名查询）人员表创建单个索引，发现查询变快，原先是单个索引，查询变快，但是后面发现数据导入时，批量插入变得很慢，后面改成组合索引，公司和人员名字放到最前面，因为根据最左前缀法则，最重要的条件放前面。

事务

事务特性

ACID，原子性，持久性，隔离性，一致性

并发事务问题，隔离级别

并发事务问题：

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

  • 不可重复读：一个事务先后读取同一条数据，但是两次读取的数据不同。

  • 幻读：一个事务按照条件进行查询时，没有对应数据，但是在插入时又发现对应数据已经存在。

隔离级别：

  • 读未提交：脏读，不可重复读，幻读

  • 读已提交：不可重复读，幻读

  • 可重复读：幻读（mysql默认隔离级别）

  • 串行化

undo log和redo log区别

MySQL中的三种日志：Undo Log（回滚日志）、Redo Log（重做日志）和Binlog（二进制日志），它们在数据库事务处理、数据持久性和复制等场景中发挥着关键作用。

  1. Undo Log (回滚日志)

    1. 设计与功能：

      1. Undo Log是InnoDB存储引擎内部用于实现事务原子性和一致性的重要机制，它记录了事务对数据库所做的更改的相反操作。例如，如果事务执行了一条INSERT语句，那么Undo Log会记录一个DELETE操作；若执行UPDATE，则记录一个相反的UPDATE操作。

    2. 应用场景：

      1. 回滚事务：当事务需要被回滚时，通过Undo Log可以恢复到事务开始前的数据状态。

      2. MVCC（多版本并发控制）：InnoDB利用Undo Log来提供不同事务之间的一致性读视图，使得事务可以看到其他事务未提交之前的旧版本数据，从而避免锁竞争，提高并发性能。

  2. Redo Log (重做日志)

    1. 设计与功能：

      1. Redo Log记录的是对数据库页的物理修改操作，即每次事务对数据页进行更改后，都会将变更以“redo record”的形式写入Redo Log。

    2. 应用场景：

      1. 数据库崩溃恢复：当系统发生异常重启或宕机时，通过重放Redo Log，能够确保已提交事务的修改不会丢失，保证了事务的持久性。

      2. 避免频繁刷盘：InnoDB采用WAL（Write-Ahead Logging）策略，先写日志再修改磁盘数据，这样可以在一定程度上减少磁盘I/O，提升写入性能

如何保证事务隔离性（mvcc）

  • 排他锁

  • MVCC（多版本并发控制，指维护一个数据的多个版本，使得读写操作没有冲突）

提高并发性能：读操作不会阻塞写操作，写操作也不会阻塞读操作，有效地提高数据库的并发性能。

降低死锁风险：由于无需使用显式锁来进行并发控制，MVCC可以降低死锁的风险。

  • MVCC实现主要依赖于数据库记录中的隐式字段，undo log日志（版本链），readView

  • 隐式字段

  • undo log

    • 当使用insert，update，delete产生便于数据回滚的日志。insert产生的undo log日志，只在回滚时需要，在事务提交之后可以立刻删除，而update，delete产生的undo log日志，在事务回滚和MVCC(多版本并发控制)都需要，不会马上删除。

    • 不同事物或者相同事务对同一条记录进行修改会生成一条记录版本链，链表的头部是最新的旧记录，尾部是最早的旧记录

  • readView

读视图（ReadView）是快照读Sql执行时，MVCC提取数据的依据，记录并维护系统当前活跃事务的id（未提交事务）

当前读：

读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如:select .. lock in share mode(共享锁)，select. .. for update、update、insert、delete(排他锁)都是一种当前读。

快照读：

简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁，是非阻塞读。

Read Committed:每次select，都生成一个快照读。

Repeatable Read:开启事务后第一个select语句才是快照读的地方。

readView核心字段

MySQL中的多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突

● 隐藏字段:

① trx id(事务id)，记录每一次操作的事务id，是自增的

② roll_pointer(DB_ROLL_PTR)(回滚指针)，指向上一个版本的事务版本记录地址

● undo log:

① 回滚日志，存储老版本数据

② 版本链:多个事务并行操作某一行记录，记录不同事务修改数据的版本，通过roll_pointer(DB_ROLL_PTR)指针形成一个链表

●readView

解决的是一个事务查询选择版本的问题

① 根据readView的匹配规则和当前的一些事务id判断该访问那个版本的数据

② 不同的隔离级别快照读是不一样的，最终的访问的结果不一样

RC:每一次执行快照读时生成ReadView

RR:仅在事务中第一次执行快照读时生成ReadView，后续复用

主从同步

Mysql主从同步核心就是二进制文件

分库分表

垂直分库

垂直分表

水平分库

水平分表

site logo

Hi,Friend

© 2025 Tia