本文档风哥主要介绍MySQL性能优化导向的安装方法，包括硬件规划、系统配置、存储规划、安装配置优化等内容。风哥教程参考MySQL官方文档Optimization等。更多视频教程www.fgedu.net.cn
Part01-基础概念与理论知识
1.1 性能优化概述
MySQL性能优化是指通过调整系统配置、数据库结构和查询语句等方式，提高MySQL数据库的性能和可靠性。性能优化导向的安装是指在安装MySQL时就考虑性能因素，从源头上确保系统的高性能。学习交流加群风哥微信:
itpux-com
1.2 性能影响因素
影响MySQL性能的因素包括：
- 硬件配置：CPU、内存、存储等
- 系统配置：操作系统参数、文件系统等
- MySQL配置：参数设置、存储引擎等
- 数据库设计：表结构、索引设计等
- 查询语句：SQL语句的优化程度
1.3 性能优化策略
MySQL性能优化的策略包括：
- 硬件优化：选择合适的硬件配置
- 系统优化：调整操作系统参数
- 配置优化：调整MySQL参数
- 数据库优化：合理设计数据库结构和索引
- 查询优化：优化SQL语句
Part02-生产环境规划与建议
2.1 硬件规划
根据业务需求规划MySQL服务器的硬件配置：
- CPU：选择多核、高主频的CPU，推荐至少8核以上
- 内存：根据数据库大小和并发量，推荐至少16GB以上，建议内存大小是数据库大小的2-3倍
- 存储：使用SSD存储，推荐使用NVMe SSD以获得更高的I/O性能
- 网络：使用万兆网卡，确保网络带宽充足
2.2 系统配置
优化操作系统配置：
# vi /etc/sysctl.conf
# 内存管理
vm.swappiness = 10
vm.overcommit_memory = 1
vm.overcommit_ratio = 90
# 文件系统
fs.file-max = 655350
# 网络
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15
# 应用生效
# sysctl -p
# 步骤2：调整文件描述符限制
# vi /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
# 步骤3：关闭透明大页
# echo never > /sys/kernel/mm/transparent_hugepage/enabled
# echo never > /sys/kernel/mm/transparent_hugepage/defrag
# 步骤4：关闭NUMA
# 在GRUB配置中添加 numa=off
# vi /etc/default/grub
GRUB_CMDLINE_LINUX=”… numa=off”
# update-grub
2.3 存储规划
存储规划建议：
- 使用SSD存储：提供更高的I/O性能
- 合理分配分区：将数据目录、日志目录、临时目录等分开存储
- 使用RAID：根据需求选择合适的RAID级别，如RAID 10
- 配置文件系统：使用ext4或xfs文件系统
Part03-生产环境项目实施方案
3.1 安装前准备
MySQL安装前的准备工作：
# 检查CPU信息
# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 16
On-line CPU(s) list: 0-15
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Gold 6248 CPU @ 2.50GHz
Stepping: 7
CPU MHz: 2500.000
CPU max MHz: 3900.0000
CPU min MHz: 1200.0000
BogoMIPS: 5000.00
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 25600K
NUMA node0 CPU(s): 0-15
# 检查内存信息
# free -h
total used free shared buff/cache available
Mem: 32G 2.0G 28G 100M 2.0G 29G
Swap: 4.0G 0B 4.0G
# 检查磁盘信息
# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 1.8T 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 1.8T 0 part /
sdb 8:16 0 3.6T 0 disk
└─sdb1 8:17 0 3.6T 0 part /mysql
# 步骤2：创建MySQL用户和目录
# 创建MySQL用户
# useradd -r -s /sbin/nologin mysql
# 创建数据目录
# mkdir -p /mysql/data
# mkdir -p /mysql/logs
# mkdir -p /mysql/binlog
# mkdir -p /mysql/tmp
# 设置目录权限
# chown -R mysql:mysql /mysql
# 步骤3：安装依赖包
# yum install -y libaio-devel ncurses-devel cmake gcc gcc-c++ perl
3.2 优化配置安装
MySQL的优化配置安装：
# wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0.tar.gz
# 步骤2：解压源码包
# tar -xzf mysql-8.4.0.tar.gz
# cd mysql-8.4.0
# 步骤3：编译安装
# cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/mysql/data \
-DSYSCONFDIR=/etc \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DWITH_LIBWRAP=0 \
-DMYSQL_TCP_PORT=3306 \
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock \
-DENABLED_LOCAL_INFILE=1 \
-DEXTRA_CHARSETS=all \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_unicode_ci
# 编译
# make -j 16
# 安装
# make install
# 步骤4：初始化MySQL
# /usr/local/mysql/bin/mysqld –initialize –user=mysql –datadir=/mysql/data
# 步骤5：创建配置文件
# vi /etc/my.cnf
[mysqld]
# 基础配置
datadir=/mysql/data
socket=/tmp/mysql.sock
log-error=/mysql/logs/error.log
pid-file=/mysql/data/mysqld.pid
user=mysql
port=3306
bind-address=0.0.0.0
# 内存配置
innodb_buffer_pool_size=24G
key_buffer_size=256M
sort_buffer_size=2M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=4M
tmp_table_size=64M
max_heap_table_size=64M
# 连接配置
max_connections=1000
max_connect_errors=10000
wait_timeout=3600
interactive_timeout=3600
# 日志配置
slow_query_log=1
slow_query_log_file=/mysql/logs/slow.log
long_query_time=1
log_bin=/mysql/binlog/mysql-bin
expire_logs_days=7
binlog_format=ROW
# 存储引擎配置
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=2G
innodb_log_buffer_size=32M
innodb_io_capacity=2000
innodb_io_capacity_max=4000
innodb_write_io_threads=8
innodb_read_io_threads=8
innodb_purge_threads=4
innodb_thread_concurrency=0
# 性能配置
innodb_use_native_aio=1
innodb_stats_on_metadata=0
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
innodb_buffer_pool_instances=8
# 步骤6：启动MySQL服务
# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
# chmod +x /etc/init.d/mysqld
# systemctl enable mysqld
# systemctl start mysqld
# 步骤7：修改root密码
# /usr/local/mysql/bin/mysql -u root -p
Enter password: 输入临时密码
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘Password123!’;
mysql> FLUSH PRIVILEGES;
3.3 安装后优化
MySQL安装后的优化工作：
# mysql -u root -p
mysql> CREATE USER ‘monitor’@’localhost’ IDENTIFIED BY ‘Password123!’;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘monitor’@’localhost’;
mysql> FLUSH PRIVILEGES;
# 步骤2：配置定期优化任务
# 创建优化脚本
# vi /mysql/scripts/optimize.sh
#!/bin/bash
# daily_optimize.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 优化表
mysql -u root -pPassword123! -e “SELECT CONCAT(‘OPTIMIZE TABLE ‘, table_schema, ‘.’, table_name, ‘;’) FROM
information_schema.tables WHERE table_schema NOT IN (‘information_schema’, ‘performance_schema’, ‘mysql’,
‘sys’)” | grep -v CONCAT | mysql -u root -pPassword123!
# 分析表
mysql -u root -pPassword123! -e “SELECT CONCAT(‘ANALYZE TABLE ‘, table_schema, ‘.’, table_name, ‘;’) FROM
information_schema.tables WHERE table_schema NOT IN (‘information_schema’, ‘performance_schema’, ‘mysql’,
‘sys’)” | grep -v CONCAT | mysql -u root -pPassword123!
# 步骤3：配置定时任务
# crontab -e
0 2 * * * /mysql/scripts/optimize.sh
# 步骤4：配置监控
# 安装Prometheus和Grafana监控MySQL性能
# 配置MySQL Exporter
# wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz
# tar -xzf mysqld_exporter-0.14.0.linux-amd64.tar.gz
# cd mysqld_exporter-0.14.0.linux-amd64
# 创建MySQL Exporter配置文件
# vi .my.cnf
[client]
user=monitor
password=Password123!
host=localhost
# 启动MySQL Exporter
# ./mysqld_exporter –config.my-cnf=.my.cnf &
Part04-生产案例与实战讲解
4.1 高性能安装实战
MySQL高性能安装的实际案例：
# 步骤1：系统环境准备
# 检查系统信息
# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
# 检查硬件信息
# lscpu | head -20
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 32
On-line CPU(s) list: 0-31
Thread(s) per core: 2
Core(s) per socket: 16
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Gold 6248 CPU @ 2.50GHz
# free -h
total used free shared buff/cache available
Mem: 128G 2.0G 124G 100M 2.0G 125G
# lsblk
NAME MAJ:MIN RM SIZE RO TYPE MOUNTPOINT
sda 8:0 0 1.8T 0 disk
├─sda1 8:1 0 1G 0 part /boot
└─sda2 8:2 0 1.8T 0 part /
sdb 8:16 0 7.3T 0 disk
└─sdb1 8:17 0 7.3T 0 part /mysql
# 步骤2：系统优化
# 调整内核参数
# vi /etc/sysctl.conf
vm.swappiness = 10
vm.overcommit_memory = 1
vm.overcommit_ratio = 90
fs.file-max = 655350
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 300
net.ipv4.tcp_keepalive_probes = 5
net.ipv4.tcp_keepalive_intvl = 15
# 应用配置
# sysctl -p
# 调整文件描述符
# vi /etc/security/limits.conf
* soft nofile 65536
* hard nofile 65536
# 关闭透明大页
# echo never > /sys/kernel/mm/transparent_hugepage/enabled
# echo never > /sys/kernel/mm/transparent_hugepage/defrag
# 步骤3：MySQL安装
# 创建目录
# mkdir -p /mysql/{data,logs,binlog,tmp}
# chown -R mysql:mysql /mysql
# 下载并安装MySQL
# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# rpm -ivh mysql80-community-release-el7-3.noarch.rpm
# yum install -y mysql-community-server
# 步骤4：配置MySQL
# vi /etc/my.cnf
[mysqld]
datadir=/mysql/data
socket=/tmp/mysql.sock
log-error=/mysql/logs/error.log
pid-file=/mysql/data/mysqld.pid
user=mysql
port=3306
bind-address=0.0.0.0
# 内存配置
innodb_buffer_pool_size=96G
key_buffer_size=512M
sort_buffer_size=4M
read_buffer_size=4M
read_rnd_buffer_size=8M
join_buffer_size=8M
tmp_table_size=128M
max_heap_table_size=128M
# 连接配置
max_connections=2000
max_connect_errors=10000
wait_timeout=3600
interactive_timeout=3600
# 日志配置
slow_query_log=1
slow_query_log_file=/mysql/logs/slow.log
long_query_time=1
log_bin=/mysql/binlog/mysql-bin
expire_logs_days=7
binlog_format=ROW
# 存储引擎配置
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=4G
innodb_log_buffer_size=64M
innodb_io_capacity=4000
innodb_io_capacity_max=8000
innodb_write_io_threads=16
innodb_read_io_threads=16
innodb_purge_threads=8
innodb_thread_concurrency=0
# 性能配置
innodb_use_native_aio=1
innodb_stats_on_metadata=0
innodb_autoinc_lock_mode=2
innodb_doublewrite=1
innodb_buffer_pool_instances=16
# 步骤5：启动MySQL
# systemctl start mysqld
# systemctl enable mysqld
# 步骤6：设置密码
# grep ‘temporary password’ /mysql/logs/error.log
# mysql -u root -p
mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘Password123!’;
mysql> FLUSH PRIVILEGES;
# 步骤7：创建测试数据库和表
mysql> CREATE DATABASE fgedudb;
mysql> USE fgedudb;
mysql> CREATE TABLE fgedu_users (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50), email VARCHAR(100), age INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
mysql> CREATE INDEX idx_name ON fgedu_users (name);
mysql> CREATE INDEX idx_created_at ON fgedu_users (created_at);
# 插入测试数据
mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_test_data()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 1000000 DO
INSERT INTO fgedu_users (name, email, age) VALUES (CONCAT('user', i), CONCAT('user', i, '@fgedu.net.cn'), FLOOR(RAND() * 100));
SET i = i + 1;
END WHILE;
END //
mysql> DELIMITER ;
mysql> CALL insert_test_data();
4.2 性能测试与验证
MySQL性能测试与验证：
# 安装sysbench
# yum install -y sysbench
# 准备测试数据
# sysbench –db-driver=mysql –mysql-host=localhost –mysql-port=3306 –mysql-user=root –mysql-password=Password123! –mysql-db=fgedudb –table-size=1000000 –tables=10 –threads=16 –time=300 –report-interval=10 oltp_read_write prepare
# 运行测试
# sysbench –db-driver=mysql –mysql-host=localhost –mysql-port=3306 –mysql-user=root –mysql-password=Password123! –mysql-db=fgedudb –table-size=1000000 –tables=10 –threads=16 –time=300 –report-interval=10 oltp_read_write run
# 测试结果示例
SQL statistics:
queries performed:
read: 328560
write: 93874
other: 46937
total: 469371
transactions:
total: 23468 (78.22 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 300.0132s
total number of events: 23468
total time taken by event execution: 4798.8104
response time:
min: 13.04ms
avg: 204.48ms
max: 1234.56ms
approx. 95th percentile: 456.78ms
Threads fairness:
events (avg/stddev): 1466.7500/34.21
execution time (avg/stddev): 299.9256/0.03
# 步骤2：使用MySQL内置工具进行性能分析
# 查看数据库状态
# mysql -u root -p -e “SHOW GLOBAL STATUS;”
# 查看慢查询
# mysql -u root -p -e “SHOW GLOBAL VARIABLES LIKE ‘slow_query%’;”
# 分析慢查询日志
# mysqldumpslow /mysql/logs/slow.log
# 查看InnoDB状态
# mysql -u root -p -e “SHOW ENGINE INNODB STATUS\G;”
# 步骤3：使用pt-query-digest分析查询性能
# 安装Percona Toolkit
# yum install -y percona-toolkit
# 分析慢查询日志
# pt-query-digest /mysql/logs/slow.log
# 分析进程列表
# pt-processlist –user=root –password=Password123!
Part05-风哥经验总结与分享
5.1 常见性能问题与解决方案
MySQL性能优化中常见的问题包括：
- 内存不足：增加服务器内存或调整MySQL内存参数
- I/O瓶颈：使用SSD存储，调整I/O相关参数
- 索引失效：优化索引设计，避免索引失效的场景
- 查询缓慢：优化SQL语句，使用合适的索引
- 连接数过多：调整max_connections参数，优化应用程序连接管理
5.2 性能优化最佳实践
1. 硬件选择：选择高性能的CPU、内存和SSD存储
2. 系统优化：调整操作系统参数，关闭透明大页，配置合适的文件描述符
3. 存储优化：使用SSD存储，合理分配分区，配置合适的文件系统
4. 配置优化：根据服务器资源和业务需求调整MySQL参数
5. 数据库设计：合理设计表结构，创建合适的索引
6. 查询优化：优化SQL语句，避免全表扫描，使用索引覆盖
7. 监控管理：配置性能监控，定期分析性能数据
8. 定期维护：定期优化表，清理过期数据，更新统计信息
学习交流加群风哥QQ113257174
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
