ℹ️大家好，我是练小杰，今天又是美好的星期一了，新的工作又要开始了，努力！！奋斗！！😆
本文是针对Mysql 性能优化知识进行学习与讨论，后续将添加更多相关知识噢，谢谢各位的支持🙏前情回顾: 【Mysql 日志管理详解】
数据库专栏：👉【数据库专栏】【Mysql练习题】主页：👉【练小杰的CSDN】
申公豹：“人心中的成见是一座大山”
性能优化
理解性能优化
- 优化MySQL数据库是数据库管理员和数据库开发人员的必备技能。
- 通过合理的数据库设计、索引优化、查询优化、缓存优化、表结构优化、硬件和配置优化以及其他优化方法，可以显著提高 MySQL 数据库的性能，确保其在高负载和高并发环境下的稳定性和高效性。
MySQL优化的作用：
一方面是找出系统的瓶颈，提高MySQL数据库整体的性能；另一方面，需要合理的结构设计和参数调整，以提高用户操作响应的速度；同时还要尽可能的节省系统资源，以便系统可以提供更大负荷的服务。
优化查询
索引对查询速度的影响
MySQL中提高性能的一个最有效的方式就是对数据表设计合理的索引。
索引提供了高效访问数据的方法，并且可以加快查询的速度，因此，索引对查询的速度有着至关重要的影响。
-- 创建索引
CREATE INDEX idx_name ON table_name(column_name);
-- 查看索引
SHOW INDEX FROM table_name;
-- 删除索引
DROP INDEX idx_name ON table_name;
分析查询语句
通过对查询语句的分析，可以了解查询语句执行情况，找出查询语句执行的瓶颈，从而优化查询语句。同时要避免全表扫描，确保查询条件使用索引。
- 使用
EXPLAIN分析查询语句
EXPLAIN SELECT * FROM table_name WHERE condition;
//或者使用DESCRIBE语句
DESCRIBE SELECT select_options
⚠️注意： 留意type 列（其中ALL 表示全表扫描，ref 或 range 表示使用索引），以及 rows 列（扫描的行数越少越好）。
- 避免
SELECT *，优化中为 只选择需要的列
SELECT column1, column2 FROM table_name;
使用索引查询
索引可以提高查询的速度。但并不是使用带有索引的字段查询时，索引都会起作用。
- 使用
LIKE关键字的查询语句
使用LIKE关键字进行模糊查询时，如果查询字段上有索引，索引会被利用，从而提高查询速度。如下，查询名字以“练”开头的所有用户。
SELECT * FROM users WHERE name LIKE '练%';
- 使用多列索引的查询语句
多列索引（复合索引）是指在多个列上创建的索引。当查询条件涉及到这些列时，索引会被利用，从而提高查询的效率。如下，查询年龄在20到30岁之间且姓名为“练小杰”的用户。
SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND name = '练小杰';
同时，为了优化上述查询，可以在 users 表上创建复合索引：
CREATE INDEX idx_age_name ON users (age, name);
- 使用
OR关键字的查询语句
OR关键字连接多个条件时，如果涉及的字段上有索引，索引会被利用，从而提高查询速度。如下，查询年龄为25岁或姓名为“eason”的用户。
SELECT * FROM users WHERE age = 25 OR name = 'eason';
优化子查询
- 在MySQL中可以使用连接（
JOIN）查询来替代子查询。 - 连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。连接之所以更有效率，是因为MySQL不需要在内存中创建临时表来完成查询工作。
表结构优化
选择合适的数据类型
使用最小的数据类型存储数据。例如，使用
TINYINT代替INT，使用VARCHAR代替TEXT。
规范化与反规范化
规范化： 减少数据冗余，提高数据一致性。
反规范化：合理地加入冗余数据减少连接操作，提高查询性能。
建立分区表
将大表分成多个小表，提高查询效率。
CREATE TABLE partitioned_table (
id INT,
created_at DATE
) PARTITION BY RANGE (YEAR(created_at)) (
PARTITION p0 VALUES LESS THAN (2023),
PARTITION p1 VALUES LESS THAN (2024),
PARTITION p2 VALUES LESS THAN (2025)
);
分析表、检查表和优化表
- MySQL提供了分析表、检查表和优化表的语句。
- 分析表：主要是分析关键字的分布。
- 检查表：主要是检查表是否存在错误。
- 优化表：主要是消除删除或者更新造成的空间浪费。
优化插入记录的速度
- 影响插入速度的主要是索引、唯一性校验和一次插入记录条数等。根据这些情况，可以分别进行优化。
优化的方式：
- 禁用索引
- 禁用唯一性检查
- 使用批量插入
- 使用
LOAD DATA INFILE批量导入
配置优化
调整缓冲区大小
innodb_buffer_pool_size：InnoDB 缓冲池大小，建议设置为系统内存的 70%-80%。
SET GLOBAL innodb_buffer_pool_size = 1G;
key_buffer_size：MyISAM键缓冲区大小。
SET GLOBAL key_buffer_size = 512M;
调整连接数
max_connections：最大连接数。SET GLOBAL max_connections = 500;wait_timeout：连接空闲超时时间。SET GLOBAL wait_timeout = 600;
启用查询缓存
query_cache_size：查询缓存大小。SET GLOBAL query_cache_size = 64M;
利用日志与监控
慢查询日志
作用：记录执行时间超过指定阈值的查询。
-- 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
-- 设置慢查询阈值（单位：秒）
SET GLOBAL long_query_time = 2;
-- 查看慢查询日志路径
SHOW VARIABLES LIKE 'slow_query_log_file';
性能监控
SHOW STATUS：查看服务器状态。
SHOW STATUS LIKE 'Threads_connected';
SHOW PROCESSLIST：查看当前连接和查询。
SHOW PROCESSLIST;
优化MySQL服务器
优化服务器硬件
服务器的硬件性能直接决定着MySQL数据库的性能。硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率。
使用 SSD：因为 SSD 的读写速度远高于
HDD，适合高并发场景。增加内存： 增加内存可以提高缓冲区和缓存的效果。
调整文件系统： 使用
XFS或ext4文件系统，避免使用ext3。
优化MySQL的参数
通过优化MySQL的参数，可以提高资源利用率，从而达到提高MySQL服务器性能的目的。例如，下面的参数：
key_buffer_size、table_cache、query_cache_size
……
key_buffer_size 参数
MySQL 中用于索引块的缓冲区大小。它主要用于
MyISAM存储引擎，但也会影响InnoDB存储引擎的性能。这个参数决定了 MySQL 用于缓存索引块的内存大小。较大的
key_buffer_size可以存储更多的索引块，从而减少磁盘I/O，提高查询性能。调整方法：我们可以通过以下命令查看和设置：
SHOW VARIABLES LIKE 'key_buffer_size';
SET GLOBAL key_buffer_size = 256M;
建议值：通常设置为服务器总内存的
25%到30%，但具体值应根据实际工作负载进行调整。
table_cache 参数
table_cache（在 MySQL 5.1 及更高版本中称为table_open_cache）是用于缓存打开的表文件的缓存大小。参数决定了 MySQL 可以缓存的打开表文件数量。较大的
table_cache可以减少打开表文件时的磁盘 I/O，提高访问速度。同时，在高并发环境下，较大的表缓存可以提高并发处理能力，减少锁争用。配置建议：默认大小通常较小，建议根据服务器内存和并发连接数进行调整
SHOW VARIABLES LIKE 'table_open_cache';
SET GLOBAL table_open_cache = 1024;
建议值：通常设置为并发连接数的 10 倍左右，但具体值应根据实际工作负载进行调整。
query_cache_size 参数
query_cache_size是 MySQL 用于缓存查询结果的缓冲区大小。- 参数决定了 MySQL 用于缓存查询结果的内存大小。对于相同的查询，MySQL 可以直接从缓存中返回结果，避免重复执行查询，提高查询速度。
- 作用：对于频繁执行的相同查询，较大的查询缓存可以显著提高响应速度。
- 调整方法：
SHOW VARIABLES LIKE 'query_cache_size';
SET GLOBAL query_cache_size = 128M;
建议值：通常设置为服务器总内存的 10% 到 20%，但具体值应根据实际查询负载进行调整。
应用示例
- 假设有一台服务器，总内存为 8GB，可以考虑以下配置：
SET GLOBAL key_buffer_size = 2G;
SET GLOBAL table_open_cache = 2048;
SET GLOBAL query_cache_size = 1G;
高级优化
主从复制
作用：通过主从复制分担读负载。
-- 主服务器配置
SHOW MASTER STATUS;
-- 从服务器配置
CHANGE MASTER TO
MASTER_HOST='master_host',
MASTER_USER='replica_user',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=107;
分库分表
作用：将大表分成多个小表，或将数据分布到多个数据库中。
工具：MyCAT分布式数据库中间件，以及ShardingSphere分库分表解决方案。
综合案例
步骤1：分析查询语句，理解索引对查询速度的影响
- 使用分析查询语句“
SELECT * FROM fruits WHERE f_name='banana';”，执行的语句及执行结果如下：
EXPLAIN SELECT * FROM fruits WHERE f_name='banana';
- 结果说明
type: const表示这是一个常量查询，查询速度非常快。
key: idx_f_name表示使用了 idx_f_name 索引。
rows: 1表示只扫描了一行数据。
Extra: Using index表示使用了覆盖索引，查询效率高。
- 使用分析查询语句“
SELECT * FROM fruits WHERE f_name like '%na’”，执行的语句及执行结果如下：
EXPLAIN SELECT * FROM fruits WHERE f_name like '%na';
- 结果说明
type: ALL表示这是一个全表扫描，查询效率低。
key: NULL表示没有使用索引。
rows: 1000表示扫描了1000行数据（假设表中有1000行数据）
Extra: Using where表示需要使用 WHERE 子句进行过滤
⚠️ 使用 LIKE '%na' 时，查询条件以通配符 % 开头，MySQL 无法使用索引进行优化。
- 使用
EXPLAIN分析查询语句下面执行“SELECT * FROM fruits WHERE f_name like 'ba%';”语句如下：
EXPLAIN SELECT * FROM message WHERE title like ' ba%';
- 结果说明
type: range表示这是一个范围查询，查询效率较高。
key: idx_f_name表示使用了 idx_f_name 索引。
rows: 50表示扫描了50行数据（假设有50行数据符合条件）。
Extra: Using index表示使用了覆盖索引，查询效率高。
步骤2：练习分析表、检查表、优化表
- 使用
ANALYZE TABLE语句分析fruits表，执行的语句及结果如下：
ANALYZE TABLE fruits;
- 结果说明
Msg_text: Table is already up to date.表示表已经是最新的，无需重新分析。
Msg_text: OK表示分析操作成功完成。
- 使用
CHECK TABLE语句检查表fruits，执行的语句及结果如下：
CHECK TABLE fruits;
- 结果说明
Msg_text: Table is marked as crashed and should be repaired.表示表被标记为崩溃，需要修复。
Msg_text: Table 'fruits' is marked as crashed and should be repaired.同样表示表被标记为崩溃，需要修复。
- 修复方法及其修复结果
REPAIR TABLE fruits;
Msg_text: OK表示修复操作成功完成。
Msg_text: The repair has been completed.表示修复操作已经完成。
本文有关Mysql数据库性能优化内容已经讲完了， 明天再见啦👋
主页：【练小杰的CSDN】😆
ℹ️欢迎各位在评论区踊跃讨论，积极提出问题，解决困惑！！！
⚠️若博客里的内容有问题，欢迎指正，我会及时修改！！