MySQL优化专题

最新推荐文章于 2026-03-25 01:31:02 发布

原创最新推荐文章于 2026-03-25 01:31:02 发布 · 297 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#mysql

数据库专题专栏收录该内容

1 篇文章

订阅专栏

一、SQL执行过程

在这里插入图片描述

通信方式

MySQL使用半双工方式工作，要么客户端向服务端发送数据，要么服务端向客户端发送数据，并且在一次连接中数据是不能分成小块发送的，不管你的 SQL 语句有多大，都是一次性发送。
如存在数据量过大情况，调整此max_allowed_packet参数。同时尽量避免无limit查询操作。

单工：在两台计算机通信的时候，数据的传输是单向的。生活中的类比：遥控器。
半双工：在两台计算机之间，数据传输是双向的，但在这个通讯连接里面，同一时间只能有一台服务器在发送数据，一个发完另一个才能发。生活中的类比：对讲机。
全双工：数据的传输是双向的，并且可以同时传输。生活中的类比：打电话。

缓存

默认关闭 show variables like ‘query_cache%’; 不推荐使用，8.0之后已被移除。

二、常用命令

show 的参数说明：
1、级别：会话 session 级别（默认）；全局 global 级别
2、动态修改：set，重启后失效；永久生效，修改配置文件/etc/my.cnf

查看连接数

show global status like ‘Thread%’

Threads_cached：缓存中的线程连接数。
Threads_connected：当前打开的连接数。
Threads_created：为处理连接创建的线程数。
Threads_running：非睡眠状态的连接数，通常指并发连接数

最大连接数

show variables like ‘max_connections’;
set global max_connections = 1000; 设置最大连接数

查看当前连接状态

SHOW PROCESSLIST （root 用户）查看 SQL 的执行状态
常见状态

Sleep 线程正在等待客户端，以向它发送一个新语句
Query 线程正在执行查询或往客户端发送数据
Locked 该查询被其它查询锁定
Copying to tmp table on disk
临时结果集合大于 tmp_table_size。线程把临时表从存储器内部格式改
变为磁盘模式，以节约存储器
Sending data 线程正在为 SELECT 语句处理行，同时正在向客户端发送数据
Sorting for group 线程正在进行分类，以满足 GROUP BY 要求
Sorting for order 线程正在进行分类，以满足 ORDER BY 要求

三、查询优化器的模块（Optimizer）

查询优化器的目的就是根据解析树生成不同的执行计划（Execution Plan），然后选择一种最优的执行计划，MySQL 里面使用的是基于开销（cost）的优化器，那种执行计划开销最小，就用哪种。

show status like ‘Last_query_cost’;查看查询的开销

查看MySQL执行计划

EXPLAIN select name from user where id=1;

四、存储引擎

对数据一致性要求比较高，需要事务支持，可以选择 InnoDB。
数据查询多更新少，对查询性能要求比较高，可以选择 MyISAM。
需要一个用于查询的临时表，可以选择 Memory

表级别存储引擎，也称表类型。

show engines ; 查看数据库支持的存储引擎
show variables like ‘datadir’; 查看数据库数据存放路径
show table status from itxw2020; 查看数据库表对应的存储引擎

MyISAM（三个文件）

适用于只读或读为主的工作。
应用：快速插入百万级数据，先用MyISAM插入后修改存储引擎为InnoDB

支持表级别的锁，插入和更新会锁表。
不支持事务
拥有较高的插入和查询效率
存储了表的行数（count）速度快

InnoDB（两个文件）

适用于经常更新的表，存在并发读写以及事务处理的业务系统

支持行级别，表级别锁
支持事务，支持外键，数据完整性，一致性更高
支持读写并发
将用户数据存储在聚集索引中，可以减少IO，提高查询效率

Memory （一个文件）

数据存储在内存中，读写速度快，重启会造成数据丢失。

csv （三个文件）

不允许空行，不支持索引。格式通用，可以直接编辑，适合在不同数据库之
间导入导出。

五、InnoDB 缓冲池（Buffer Pool）

InnnoDB 的数据都是放在磁盘上的，InnoDB 操作数据有一个最小的逻辑单
位，叫做页（索引页和数据页）。InnoDB 使用了一种缓冲池的技术，也就是把磁盘读到的页放到一块内存区域里面。这个内存区域就叫 Buffer Pool。
在这里插入图片描述 Buffer Pool 主要分为 3 个部分： Buffer Pool、Change Buffer、Adaptive Hash Index，另外还有一个（redo）log buffer

Buffer Pool

Buffer Pool 默认大小是 128M（134217728 字节），可以调整。
查看参数（系统变量）：
SHOW VARIABLES like ‘%innodb_buffer_pool%’;

Change Buffer

数据库大部分索引都是非唯一索引，并且业务是写多读少，不会在写数据后立刻读取，就可以使用 Change Buffer（写缓冲）。写多读少的业务，调大这个值：
SHOW VARIABLES LIKE ‘innodb_change_buffer_max_size’;
代表 Change Buffer 占 Buffer Pool 的比例，默认 25%。

( redo )Log buffer

InnoDB 把所有对页面的修改操作专门写入一个日志文件，并且在数据库启动时从这个文件进行恢复操作（实现 crash-safe）——用它来实现事务的持久性,防止数据库宕机带来的数据丢失。
/var/lib/mysql/目录下的ib_logfile0 和 ib_logfile1，每个 48M。

关键点就是先写日志，再写磁盘
show variables like ‘innodb_log%’; 查看日志相关参数

六、InnoDB索引

索引三大类型

普通索引（Normal）:非唯一索引
唯一索引要求键值不能重复。主键索引（primary key）是特殊的唯一索引，要求键值不能为空
全文索引针对较大数据字段（char、varchar、text），解决like查询效率低，也可创建全文索引
全文索引使用

select * from fulltext_test where match(content) against('模糊值' IN NATURAL LANGUAGE MODE);

索引扩展补充

聚集索引（聚簇索引）：索引键值的逻辑顺序跟表数据行的物理存储顺序是一致的。主键索引是聚集索引，非主键都是非聚集索引
主键索引辅助索引
辅助索引存储的是辅助索引和主键值。如果使用辅助索引查询，会根据主键值在主键索引中查询，最终取得数据
如果没有主键索引
1、如果我们定义了主键(PRIMARY KEY)，那么 InnoDB 会选择主键作为聚集索引。
2、如果没有显式定义主键，则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
3、如果也没有这样的唯一索引，则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引，它会随着行记录的写入而主键递增。
列的离散度越高，重复记录越少。建立索引应选择离散度跟高的字段。
联合索引最左匹配 建立联合索引，将最常用的列放在左边
index(a,b,c) 相当于创建了 index(a), index(a,b),index(a,b,c)
覆盖索引
回表：
非主键索引，我们先通过索引找到主键索引的键值，再通过主键值查出索引里面没有的数据，它比基于主键索引的查询多扫描了一棵索引树，这个过程就叫回表。
避免回表:
在辅助索引里面，不管是单列索引还是联合索引，如果 select 的数据列只用从索引中就能够取得，不必从数据区中读取，这时候使用的索引就叫做覆盖索引，这样就避免了回表
索引条件下推
索引条件下推（Index Condition Pushdown），5.6 以后完善的功能。只适用于二级索引。ICP 的目标是减少访问表的完整行的读数量从而减少 I/O 操作