Mysql重要知识点

原创于 2025-09-26 22:15:00 发布 · 1k 阅读

22 ·

CC 4.0 BY-SA版权

文章标签：

#java

java 专栏收录该内容

11 篇文章

订阅专栏

慢查询

类型

聚合查询

多表查询

表数据量过大查询

深度分页查询

表象

页面加载过慢

接口压测相应时间过长

方案

1. 开源工具

调试工具：Arthas

运维工具：Prometheus、Skywalking

2. Mysql自带慢日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。

如果要开启慢查询日志，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：

# 开启MySQL慢日志查询开关。
slow_query_log=1
# 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会视为慢查询，记录慢查询日志。
long_query_time=2

分析方法

可以采用Explain或者Desc命令获取Mysql如何执行Select语句的信息。

语法

直接在select语句之前加上关键字 explain / desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;

1. 通过key和key_len检查是否命中了索引（索引本身存在是否有失效的情况）。

2. 通过type字段查看sql是否有进一步的优化空间，是否存在全索引扫描或全盘扫描。

3. 通过Extra建议判断，是否出现了回表的情况，如果出现了，可以尝试添加索引或修改返回字段来修复。

存储引擎

定义

存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型。

特性	MyISAM	InnoDB	MEMORY
事务安全	不支持	支持	不支持
锁机制	表锁	表锁/行锁	表锁
外键	不支持	支持	不支持

索引

定义

索引（index）是帮助MySQL高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（B+树），这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

数据结构

MySQL默认使用的索引底层数据结构是B+树。

B-Tree，B树是一种多叉路衡查找树，相对于二叉树，B树每个节点可以有多个分支，即多叉。

以一颗最大度数（max-degree）为5(5阶)的B-Tree为例，那这个B树每个节点最多存储4个key。

B+Tree是在BTree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

B树与B+树对比:

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

②：查询效率B+树更加稳定；

③：B+树便于扫库和区间查询，叶子节点是一个双向链表。

聚簇索引和非聚簇索引

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则

如果存在主键，主键索引就是聚集索引。

如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。

回表查询

通过二级索引找到对应的主键值，到聚集索引中查找整行数据，这个过程就是回表。

覆盖索引

覆盖索引是指查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到。

Mysql超大分页处理

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比：

因为，当在进行分页查询时，如果执行 limit 9000000,10 ，此时需要MySQL排序前9000010 记录，仅仅返回 9000000 - 9000010 的记录，其他记录丢弃，查询排序的代价非常大。

覆盖索引+子查询优化

select *
from tb_sku t,
(select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

索引创建原则

主键索引

唯一索引

复合索引

1. 针对于数据量较大，且查询比较频繁的表建立索引。

2. 针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。

5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。

7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

索引失效情况

1. 违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引。

2. 范围查询右边的列，不能使用索引。

3. 不要在索引列上进行运算操作，索引将失效。

4. 字符串不加单引号，造成索引失效。（查询优化器，会自动进行类型转换，造成索引失效。）

5. 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引会失效。

SQL优化

表的设计优化

1. 设置合适的数值（tinyint int bigint），要根据实际情况选择。

2. 设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低。

SQL语句优化

1. SELECT语句务必指明字段名称（避免直接使用select * ）。

2. SQL语句要避免造成索引失效的写法。

3. 尽量用union all代替union union会多一次过滤，效率低。

4. 避免在where子句中对字段进行表达式操作。

5. 能用innerjoin就不用left join right join，如必须使用一定要以小表为驱动，内连接会对两个表进行优化，优先把小表放到外边，把大表放到里边。left join 或 right join，不会重新调整顺序。

主从复制、读写分离

如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的架构。

读写分离解决的是，数据库的写入，影响了查询的效率。

索引优化

见上文索引章节。

分库分表

见下文分库分表章节。

事务

定义

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

事务的特性

原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。

一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。

隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

并发事务带来哪些问题

脏读、不可重复读、幻读

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据。
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了”幻影”。

隔离级别

读未提交、读已提交、可重复读、串行化

隔离级别	脏读	不可重复读	幻读
Read uncommitted 读未提交	√	√	√
Read committed 读已提交	×	√	√
Repeatable Read(默认) 可重复读	×	×	√
Serializable 串行化	×	×	×

Undo log和Redo log

缓冲池（buffer pool）

主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度。

数据页（page）

是InnoDB 存储引擎磁盘管理的最小单元，每个页的大小默认为 16KB。页中存储的是行数据。

重做日志（Redo log）

记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。

该日志文件由两部分组成：重做日志缓冲（redo log buffer）以及重做日志文件（redo log file）,前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复使用。

回滚日志（Undo log）

回滚日志，用于记录数据被修改前的信息，作用包含两个 : 提供回滚和 MVCC(多版本并发控制) 。undo log和redo log记录物理日志不一样，它是逻辑日志。undo log可以实现事务的一致性和原子性。

可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然。

当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。

区别

redo log: 记录的是数据页的物理变化，服务宕机可用来同步数据。

undo log: 记录的是逻辑日志，当事务回滚时，通过逆操作恢复原来的数据。

redo log保证了事务的持久性，undo log保证了事务的原子性和一致性。

事务的隔离性如何保证

锁：排他锁（如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁）。

MVCC：多版本并发控制。

MVCC

全称 Multi-Version Concurrency Control，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突。

MVCC的具体实现，主要依赖于数据库记录中的隐式字段、undo log日志、readView。

隐式字段

隐藏字段	含义
DB_TRX_ID	最近修改事务ID，记录插入这条记录或最后一次修改该记录的事务ID。
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本。
DB_ROW_ID	隐藏主键，如果表结构没有指定主键，将会生成该隐藏字段。