-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM employees WHERE department_id = 10;

-- 启用慢查询日志
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;

-- 优化前
SELECT * FROM users WHERE DATE(created_at) = '2023-01-01';

-- 优化后
SELECT id, name, email FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-01-02';

-- 创建索引
CREATE INDEX idx_department_id ON employees(department_id);

-- 分析表
ANALYZE TABLE employees;

-- 优化表
OPTIMIZE TABLE employees;

MySQL性能瓶颈的主要来源

• 硬件瓶颈：CPU、内存、磁盘I/O、网络等硬件资源不足
• 数据库设计不合理：表结构设计不当、索引缺失或过多、数据类型选择不合适等
• 查询语句效率低下：SQL语句编写不规范、没有充分利用索引、执行计划不佳等
• 服务器配置不当：MySQL参数设置不合理、缓存配置不当等
• 锁争用：并发访问导致的锁竞争、死锁等问题
• 存储引擎选择不合适：不同的存储引擎有不同的特点和适用场景

MySQL性能优化的基本原则

• 定位瓶颈：通过监控和分析工具找到性能瓶颈的具体位置
• 先简单后复杂：先尝试简单的优化方法，如调整参数、添加索引等，再考虑复杂的优化方案
• 数据驱动：基于实际的性能数据进行优化，而不是凭经验或猜测
• 综合考虑：性能优化需要综合考虑数据库设计、查询优化、服务器配置等多个方面
• 持续优化：性能优化是一个持续的过程，需要定期评估和调整

MySQL性能优化的主要工具

• EXPLAIN：分析查询语句的执行计划
• SHOW PROFILE：分析查询语句的资源消耗
• Performance Schema：MySQL内置的性能监控工具
• 慢查询日志：记录执行时间超过阈值的SQL语句
• pt-query-digest：分析慢查询日志的工具
• INFORMATION_SCHEMA：查询数据库元数据的系统数据库
• MySQL Workbench：MySQL官方提供的图形化管理工具

使用EXPLAIN分析查询执行计划

EXPLAIN语句可以显示MySQL如何执行查询语句，包括如何连接表、如何使用索引等信息。

-- 使用EXPLAIN分析查询执行计划
EXPLAIN SELECT * FROM employees WHERE department_id = 10;

-- 使用EXPLAIN ANALYZE分析查询执行计划（MySQL 8.0.18及以上版本）
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

-- 分析复杂的查询语句
EXPLAIN SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 5000;

-- 分析带有子查询的语句
EXPLAIN SELECT * FROM employees WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);

EXPLAIN输出的主要字段说明

字段	说明
id	查询的标识符，表示查询中不同的select子句或操作的顺序
select_type	查询类型，如SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等
table	查询涉及的表名
partitions	查询涉及的分区
type	访问类型，如ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const（常量查找）等
possible_keys	可能使用的索引
key	实际使用的索引
key_len	使用的索引长度
ref	与索引比较的列或常量
rows	估计需要扫描的行数
filtered	按条件过滤的行百分比
Extra	额外信息，如Using index（使用覆盖索引）、Using where（使用WHERE条件过滤）、Using temporary（使用临时表）、Using filesort（使用文件排序）等

使用SHOW PROFILE分析查询资源消耗

SHOW PROFILE语句可以显示查询语句执行过程中各个阶段的资源消耗情况。

-- 启用SHOW PROFILE功能
SET profiling = 1;

-- 执行查询语句
SELECT * FROM employees WHERE department_id = 10;

-- 查看查询的性能分析信息
SHOW PROFILES;

-- 查看特定查询的详细性能分析信息
SHOW PROFILE FOR QUERY 1;

-- 查看特定查询的CPU使用情况
SHOW PROFILE CPU FOR QUERY 1;

-- 查看特定查询的IO使用情况
SHOW PROFILE BLOCK IO FOR QUERY 1;

-- 查看特定查询的内存使用情况
SHOW PROFILE MEMORY FOR QUERY 1;

-- 查看特定查询的所有性能指标
SHOW PROFILE ALL FOR QUERY 1;

索引的作用

• 加速数据检索：通过索引可以快速定位到数据，避免全表扫描
• 加速排序和分组：如果查询中包含ORDER BY或GROUP BY子句，且排序或分组的列上有索引，可以利用索引来加速排序和分组操作
• 加速表连接：在表连接操作中，索引可以加速连接条件的匹配过程
• 保证数据唯一性：唯一索引和主键索引可以保证数据的唯一性

索引的类型

• 普通索引（INDEX）：最基本的索引，没有唯一性限制
• 唯一索引（UNIQUE）：索引列的值必须唯一，但允许有空值
• 主键索引（PRIMARY KEY）：特殊的唯一索引，不允许有空值，一个表只能有一个主键索引
• 复合索引：由多个列组成的索引
• 全文索引（FULLTEXT）：用于全文检索，适用于CHAR、VARCHAR和TEXT类型的列
• 空间索引（SPATIAL）：用于地理空间数据的索引

索引的优缺点

优点	缺点
提高数据检索速度	占用额外的磁盘空间
加速排序和分组操作	降低数据更新（INSERT、UPDATE、DELETE）的性能
加速表连接操作	需要定期维护索引
保证数据唯一性	创建和维护索引需要额外的CPU和内存资源

创建合适的索引

-- 创建普通索引
CREATE INDEX idx_department_id ON employees (department_id);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON employees (email);

-- 创建复合索引
CREATE INDEX idx_lastname_firstname ON employees (last_name, first_name);

-- 创建全文索引
CREATE FULLTEXT INDEX idx_note ON employees (notes);

设计索引的基本原则

• 选择唯一性高的列作为索引：唯一性高的列（如主键、唯一约束列）上的索引效率更高
• 为经常用于查询条件的列创建索引：WHERE子句中经常使用的列适合创建索引
• 为经常用于排序和分组的列创建索引：ORDER BY和GROUP BY子句中经常使用的列适合创建索引
• 为经常用于表连接的列创建索引：JOIN子句中的连接列适合创建索引
• 考虑复合索引的列顺序：在复合索引中，选择性高的列应该放在前面
• 避免在频繁更新的列上创建索引：频繁更新的列上的索引会导致索引频繁重建，影响性能
• 限制索引的数量：一个表上的索引数量不宜过多，一般不超过5个

索引失效的常见情况

• 使用!=或<>运算符：WHERE子句中使用!=或<>运算符可能导致索引失效
• 使用IS NULL或IS NOT NULL：WHERE子句中使用IS NULL或IS NOT NULL可能导致索引失效
• 使用LIKE运算符且通配符在开头：如LIKE '%keyword'会导致索引失效
• 在索引列上进行运算：如WHERE salary*2 > 10000会导致索引失效
• 在索引列上使用函数：如WHERE DATE(hire_date) > '2023-01-01'会导致索引失效
• 使用OR连接条件：如果OR连接的两个条件中有一个没有索引，可能导致索引失效
• 复合索引不使用最左前缀：复合索引需要按照最左前缀原则使用
• 数据类型不匹配：如字符串列与数字比较会导致索引失效

避免索引失效的技巧

-- 避免在索引列上进行运算
-- 低效：
SELECT * FROM employees WHERE salary*2 > 10000;
-- 高效：
SELECT * FROM employees WHERE salary > 5000;

-- 避免在索引列上使用函数
-- 低效：
SELECT * FROM employees WHERE DATE(hire_date) > '2023-01-01';
-- 高效：
SELECT * FROM employees WHERE hire_date > '2023-01-01 00:00:00';

-- 避免使用LIKE运算符且通配符在开头
-- 低效：
SELECT * FROM employees WHERE last_name LIKE '%son';
-- 高效：
SELECT * FROM employees WHERE last_name LIKE 'son%';

-- 对于OR连接的条件，确保所有列都有索引
-- 低效（如果department_id没有索引）：
SELECT * FROM employees WHERE department_id = 10 OR salary > 5000;
-- 高效（确保department_id和salary都有索引）：
SELECT * FROM employees WHERE department_id = 10 OR salary > 5000;

-- 遵循复合索引的最左前缀原则
-- 假设有复合索引idx_lastname_firstname (last_name, first_name)
-- 高效（使用了最左前缀）：
SELECT * FROM employees WHERE last_name = 'Smith';
SELECT * FROM employees WHERE last_name = 'Smith' AND first_name = 'John';
-- 低效（没有使用最左前缀）：
SELECT * FROM employees WHERE first_name = 'John';

子查询的类型

• 标量子查询：返回单个值的子查询
• 列子查询：返回单列多行的子查询
• 行子查询：返回单行多列的子查询
• 表子查询：返回多行多列的子查询
• 相关子查询：依赖外部查询的子查询
• 不相关子查询：不依赖外部查询的子查询

优化子查询的技巧

-- 1. 使用JOIN替代IN子查询
-- 低效：
SELECT * FROM employees WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);
-- 高效：
SELECT e.* FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;

-- 2. 使用EXISTS替代IN子查询（当子查询表较大时）
-- 低效：
SELECT * FROM employees WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);
-- 高效：
SELECT * FROM employees e WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id AND d.location_id = 1700
);

-- 3. 避免在WHERE子句中使用NOT IN（使用LEFT JOIN替代）
-- 低效：
SELECT * FROM employees WHERE department_id NOT IN (
    SELECT department_id FROM departments WHERE location_id = 1700
);
-- 高效：
SELECT e.* FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id AND d.location_id = 1700
WHERE d.department_id IS NULL;

-- 4. 避免在相关子查询中使用ORDER BY
-- 低效：
SELECT e.*, (
    SELECT MAX(salary) FROM salaries s WHERE s.employee_id = e.employee_id ORDER BY salary DESC
) AS max_salary FROM employees e;
-- 高效：
SELECT e.*, (
    SELECT MAX(salary) FROM salaries s WHERE s.employee_id = e.employee_id
) AS max_salary FROM employees e;

-- 5. 使用LIMIT限制子查询的结果集大小
-- 低效（如果子查询返回大量数据）：
SELECT * FROM employees WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id > 1000
);
-- 高效（如果只需要部分结果）：
SELECT * FROM employees WHERE department_id IN (
    SELECT department_id FROM departments WHERE location_id > 1000 LIMIT 100
);

-- 6. 使用临时表存储子查询结果（对于复杂的子查询）
-- 先将子查询结果存储在临时表中
CREATE TEMPORARY TABLE temp_departments AS
SELECT department_id FROM departments WHERE location_id = 1700;
-- 然后在主查询中使用临时表
SELECT * FROM employees WHERE department_id IN (
    SELECT department_id FROM temp_departments
);

表分解的优点

• 减少数据冗余：通过表分解可以避免数据重复存储
• 提高查询效率：查询只涉及需要的字段，减少I/O操作
• 简化数据维护：数据结构更加清晰，维护更加方便
• 提高缓存效率：表的记录更小，可以在内存中缓存更多的记录

表分解的示例

-- 原始表（包含很多字段）
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(20),
    hire_date DATE,
    job_id VARCHAR(10),
    salary DECIMAL(8,2),
    commission_pct DECIMAL(2,2),
    manager_id INT,
    department_id INT,
    address VARCHAR(255),
    city VARCHAR(50),
    state VARCHAR(50),
    country VARCHAR(50),
    postal_code VARCHAR(20),
    profile_picture BLOB,
    resume LONGTEXT,
    notes TEXT,
    emergency_contact VARCHAR(100),
    emergency_phone VARCHAR(20)
);

-- 分解后的表1：基本信息表（常用字段）
CREATE TABLE employees_basic (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    phone_number VARCHAR(20),
    hire_date DATE,
    job_id VARCHAR(10),
    salary DECIMAL(8,2),
    commission_pct DECIMAL(2,2),
    manager_id INT,
    department_id INT
);

-- 分解后的表2：地址信息表（不常用字段）
CREATE TABLE employees_address (
    employee_id INT PRIMARY KEY,
    address VARCHAR(255),
    city VARCHAR(50),
    state VARCHAR(50),
    country VARCHAR(50),
    postal_code VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees_basic(employee_id)
);

-- 分解后的表3：个人资料表（大字段）
CREATE TABLE employees_profile (
    employee_id INT PRIMARY KEY,
    profile_picture BLOB,
    resume LONGTEXT,
    notes TEXT,
    emergency_contact VARCHAR(100),
    emergency_phone VARCHAR(20),
    FOREIGN KEY (employee_id) REFERENCES employees_basic(employee_id)
);

-- 查询时根据需要连接表
SELECT e.*, a.address, a.city, a.state, a.country, a.postal_code
FROM employees_basic e
LEFT JOIN employees_address a ON e.employee_id = a.employee_id
WHERE e.department_id = 10;

-- 只需要基本信息时，直接查询基本信息表
SELECT * FROM employees_basic WHERE department_id = 10;

中间表的优点

• 提高查询效率：避免重复执行复杂的计算操作
• 简化查询语句：复杂的查询可以简化为对中间表的查询
• 减轻服务器负担：将复杂计算的负担分散到非高峰时段

中间表的示例

-- 假设需要频繁查询每个部门的员工数量、平均工资、最高工资和最低工资
-- 原始查询（复杂且效率低）
SELECT d.department_id, d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name;

-- 创建中间表存储计算结果
CREATE TABLE department_stats (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    employee_count INT,
    avg_salary DECIMAL(8,2),
    max_salary DECIMAL(8,2),
    min_salary DECIMAL(8,2),
    last_update TIMESTAMP
);

-- 定期更新中间表（可以使用定时任务）
INSERT INTO department_stats (department_id, department_name, employee_count, avg_salary, max_salary, min_salary, last_update)
SELECT d.department_id, d.department_name,
    COUNT(e.employee_id) AS employee_count,
    AVG(e.salary) AS avg_salary,
    MAX(e.salary) AS max_salary,
    MIN(e.salary) AS min_salary,
    CURRENT_TIMESTAMP
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ON DUPLICATE KEY UPDATE
    department_name = VALUES(department_name),
    employee_count = VALUES(employee_count),
    avg_salary = VALUES(avg_salary),
    max_salary = VALUES(max_salary),
    min_salary = VALUES(min_salary),
    last_update = CURRENT_TIMESTAMP;

-- 直接查询中间表获取结果（高效）
SELECT * FROM department_stats;

增加冗余字段的原则

• 只在必要时增加冗余字段：冗余字段会增加数据存储量和维护成本
• 确保冗余字段的数据一致性：需要通过触发器、存储过程等方式确保冗余字段与源字段的数据一致性
• 冗余字段应该是不经常变化的字段：经常变化的字段不适合作为冗余字段，因为会增加维护成本

增加冗余字段的示例

-- 原始表结构
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50),
    location_id INT
);

-- 频繁执行的查询（需要连接两个表）
SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 增加冗余字段后的表结构
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    department_name VARCHAR(50), -- 冗余字段
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 创建触发器确保冗余字段的数据一致性
DELIMITER $$
CREATE TRIGGER update_employee_department_name
AFTER UPDATE ON departments
FOR EACH ROW
BEGIN
    UPDATE employees
    SET department_name = NEW.department_name
    WHERE department_id = NEW.department_id;
END$$
DELIMITER ;

-- 插入员工数据时同时设置部门名称
INSERT INTO employees (employee_id, first_name, last_name, department_id, department_name)
SELECT 1001, 'John', 'Smith', d.department_id, d.department_name
FROM departments d
WHERE d.department_id = 10;

-- 优化后的查询（不需要表连接）
SELECT employee_id, first_name, last_name, department_name
FROM employees;

优化插入记录速度的方法

-- 1. 使用INSERT INTO ... VALUES (), (), ...语句批量插入数据
-- 低效（单条插入）：
INSERT INTO employees (first_name, last_name, email) VALUES ('John', 'Smith', 'john.smith@example.com');
INSERT INTO employees (first_name, last_name, email) VALUES ('Jane', 'Doe', 'jane.doe@example.com');
INSERT INTO employees (first_name, last_name, email) VALUES ('Mike', 'Johnson', 'mike.johnson@example.com');

-- 高效（批量插入）：
INSERT INTO employees (first_name, last_name, email) VALUES 
    ('John', 'Smith', 'john.smith@example.com'),
    ('Jane', 'Doe', 'jane.doe@example.com'),
    ('Mike', 'Johnson', 'mike.johnson@example.com');

-- 2. 使用LOAD DATA INFILE语句导入数据（比INSERT语句更快）
LOAD DATA INFILE '/path/to/employees.csv'
INTO TABLE employees
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(first_name, last_name, email);

-- 3. 禁用索引和约束（导入完成后再启用）
-- 禁用索引
ALTER TABLE employees DISABLE KEYS;
-- 执行批量插入
-- ...
-- 启用索引
ALTER TABLE employees ENABLE KEYS;

-- 4. 调整innodb_flush_log_at_trx_commit参数（仅适用于InnoDB存储引擎）
-- 临时设置为0或2（导入完成后再改回1）
SET GLOBAL innodb_flush_log_at_trx_commit = 0;
-- 执行批量插入
-- ...
-- 恢复默认设置
SET GLOBAL innodb_flush_log_at_trx_commit = 1;

-- 5. 调整bulk_insert_buffer_size参数（仅适用于MyISAM存储引擎）
SET GLOBAL bulk_insert_buffer_size = 8388608; -- 8MB

-- 6. 使用事务批量提交
START TRANSACTION;
-- 执行多条插入语句
-- ...
COMMIT;

-- 7. 避免使用AUTO_INCREMENT主键（如果可能）
-- 或者使用innodb_autoinc_lock_mode = 2（MySQL 5.1及以上版本）
SET GLOBAL innodb_autoinc_lock_mode = 2;

-- 8. 使用临时表导入数据（对于非常大的数据集）
-- 创建临时表
CREATE TEMPORARY TABLE temp_employees LIKE employees;
-- 导入数据到临时表
LOAD DATA INFILE '/path/to/employees.csv' INTO TABLE temp_employees ...;
-- 将临时表中的数据插入到目标表
INSERT INTO employees SELECT * FROM temp_employees;
-- 删除临时表
DROP TEMPORARY TABLE temp_employees;

使用ANALYZE TABLE分析表

ANALYZE TABLE语句用于更新表的统计信息，帮助优化器生成更好的执行计划。

-- 分析单个表
ANALYZE TABLE employees;

-- 分析多个表
ANALYZE TABLE employees, departments, jobs;

-- 分析表并返回详细信息
ANALYZE TABLE employees EXTENDED;

使用CHECK TABLE检查表

CHECK TABLE语句用于检查表是否存在错误。

-- 检查单个表
CHECK TABLE employees;

-- 检查多个表
CHECK TABLE employees, departments, jobs;

-- 检查表并尝试修复错误（对于MyISAM表）
CHECK TABLE employees FAST MEDIUM CHANGED FOR UPGRADE QUICK REPAIR;

-- 对于InnoDB表，可以使用innodb_checksum_algorithm参数检查页校验和
SET GLOBAL innodb_checksum_algorithm = 'crc32';
-- 然后重启MySQL服务器，检查错误日志

使用OPTIMIZE TABLE优化表

OPTIMIZE TABLE语句用于优化表，回收碎片空间，提高表的I/O性能。

-- 优化单个表
OPTIMIZE TABLE employees;

-- 优化多个表
OPTIMIZE TABLE employees, departments, jobs;

-- 对于InnoDB表，可以使用ALTER TABLE ... FORCE语句重建表
ALTER TABLE employees FORCE;

-- 对于InnoDB表，也可以使用ANALYZE TABLE语句更新统计信息
ANALYZE TABLE employees;

服务器硬件优化的关键点

• CPU：选择多核、高频率的CPU，MySQL的许多操作（如连接处理、排序、聚合计算等）都需要CPU支持
• 内存：增加内存可以提高MySQL的缓存命中率，减少磁盘I/O操作
• 磁盘：选择高性能的磁盘（如SSD）可以显著提高磁盘I/O性能，RAID技术可以提高数据可靠性和读写性能
• 网络：使用高速网络设备和优化网络配置，减少网络延迟

硬件配置建议

应用场景	CPU建议	内存建议	磁盘建议
小型应用（<100并发）< /td>	4核	8GB+	SSD 200GB+
中型应用（100-500并发）	8核+	16GB+	SSD 500GB+
大型应用（>500并发）	16核+	32GB+	SSD RAID 10 1TB+

关键配置参数的优化建议

-- 在MySQL配置文件（my.cnf或my.ini）中进行配置

[mysqld]

-- 1. 内存相关参数
-- 设置MySQL使用的最大内存（根据服务器内存大小调整）
# 对于8GB内存的服务器，可以设置为4GB
innodb_buffer_pool_size = 4G

-- 设置缓冲区池实例数量（根据CPU核心数调整）
innodb_buffer_pool_instances = 4

-- 设置MyISAM索引缓冲区大小
key_buffer_size = 256M

-- 设置查询缓存大小（MySQL 8.0已移除查询缓存）
# query_cache_size = 64M
# query_cache_type = 1

-- 设置排序缓冲区大小
sort_buffer_size = 4M

-- 设置连接缓冲区大小
join_buffer_size = 4M

-- 设置随机读缓冲区大小
read_rnd_buffer_size = 2M

-- 设置临时表最大大小
tmp_table_size = 64M
max_heap_table_size = 64M

-- 2. 连接相关参数
-- 设置最大连接数
max_connections = 500

-- 设置等待连接超时时间
connect_timeout = 10

-- 设置交互式连接超时时间
interactive_timeout = 28800

-- 设置非交互式连接超时时间
wait_timeout = 28800

-- 3. InnoDB相关参数
-- 设置InnoDB日志文件大小
innodb_log_file_size = 512M

-- 设置InnoDB日志缓冲区大小
innodb_log_buffer_size = 16M

-- 设置InnoDB事务提交时日志刷新策略
# 0：每秒刷新一次日志（性能最好，安全性最差）
# 1：每次事务提交都刷新日志（性能最差，安全性最好）
# 2：每次事务提交都刷新日志到操作系统缓存，然后每秒刷新到磁盘（折中方案）
innodb_flush_log_at_trx_commit = 1

-- 设置InnoDB文件刷写方法
# O_DIRECT：绕过操作系统缓存（适用于有电池备份的RAID控制器）
# fdatasync：使用fsync()系统调用（默认）
innodb_flush_method = O_DIRECT

-- 设置InnoDB打开文件的最大数量
innodb_open_files = 4000

-- 设置InnoDB线程并发数
innodb_thread_concurrency = 0  # 0表示由MySQL自动控制

-- 4. 日志相关参数
-- 启用慢查询日志
slow_query_log = 1

-- 设置慢查询时间阈值（单位：秒）
long_query_time = 2

-- 设置慢查询日志文件路径
slow_query_log_file = /var/log/mysql/slow.log

-- 5. 其他参数
-- 设置临时文件目录（最好位于SSD上）
tmpdir = /tmp

-- 设置字符集
character-set-server = utf8mb4

-- 设置默认存储引擎
default-storage-engine = InnoDB

-- 设置最大允许的数据包大小
max_allowed_packet = 64M

-- 设置表定义缓存大小
table_definition_cache = 400

-- 设置表打开缓存大小
table_open_cache = 2000

-- 设置线程缓存大小
thread_cache_size = 100

-- 设置线程堆栈大小
thread_stack = 256K

使用SHOW VARIABLES和SHOW STATUS查看参数设置和状态信息

-- 查看所有全局变量设置
SHOW GLOBAL VARIABLES;

-- 查看特定参数的设置
SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

-- 查看所有会话变量设置
SHOW SESSION VARIABLES;

-- 查看MySQL服务器状态信息
SHOW GLOBAL STATUS;

-- 查看特定状态信息
SHOW GLOBAL STATUS LIKE 'Connections';
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Slow_queries';

临时表的类型

• 内存临时表：使用MEMORY存储引擎，存储在内存中，访问速度快
• 磁盘临时表：使用MyISAM或InnoDB存储引擎，存储在磁盘上，访问速度相对较慢

临时表的使用场景

• ORDER BY子句和GROUP BY子句同时使用不同的列
• ORDER BY子句使用的列不是索引列
• GROUP BY子句使用的列不是索引列
• 执行DISTINCT操作且没有使用索引
• 执行UNION操作
• 执行子查询
• 执行复杂的JOIN操作

临时表性能优化的方法

-- 1. 增加内存临时表的大小限制
-- 设置内存临时表的最大大小
SET GLOBAL tmp_table_size = 64M;
SET GLOBAL max_heap_table_size = 64M;

-- 2. 为ORDER BY和GROUP BY子句中的列创建索引
-- 创建复合索引覆盖ORDER BY和GROUP BY子句中的列
CREATE INDEX idx_department_salary ON employees (department_id, salary);

-- 3. 避免使用SELECT *，只查询需要的列
-- 低效：
SELECT * FROM employees ORDER BY department_id, salary;
-- 高效：
SELECT employee_id, department_id, salary FROM employees ORDER BY department_id, salary;

-- 4. 使用LIMIT限制结果集大小
-- 高效：
SELECT * FROM employees ORDER BY department_id, salary LIMIT 100;

-- 5. 优化子查询，避免创建临时表
-- 使用JOIN替代子查询
SELECT e.* FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1700;

-- 6. 选择合适的临时文件目录
-- 在配置文件中设置临时文件目录（最好位于SSD上）
tmpdir = /tmp

-- 7. 监控临时表的使用情况
-- 查看创建的临时表数量
SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

-- 查看创建的磁盘临时表数量
SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables';

-- 计算磁盘临时表比例（如果比例过高，说明需要优化）
-- Created_tmp_disk_tables / Created_tmp_tables * 100%

设置语句超时的方法

-- 1. 使用SET语句设置全局或会话级别的语句超时时间
-- 设置全局语句超时时间（单位：毫秒）
SET GLOBAL max_execution_time = 60000;  -- 60秒

-- 设置会话语句超时时间
SET SESSION max_execution_time = 30000;  -- 30秒

-- 2. 在配置文件中设置语句超时时间
[mysqld]
max_execution_time = 60000  -- 60秒

-- 3. 为特定查询设置超时时间
SELECT /*+ MAX_EXECUTION_TIME(10000) */ * FROM employees WHERE department_id = 10;

-- 4. 设置系统变量获取语句超时信息
-- 查看当前全局语句超时时间
SHOW GLOBAL VARIABLES LIKE 'max_execution_time';

-- 查看当前会话语句超时时间
SHOW SESSION VARIABLES LIKE 'max_execution_time';

-- 5. 设置交互式和非交互式语句的超时时间
-- 设置交互式语句超时时间
SET GLOBAL interactive_timeout = 28800;  -- 8小时

-- 设置非交互式语句超时时间
SET GLOBAL wait_timeout = 28800;  -- 8小时

-- 6. 使用KILL语句终止长时间运行的查询
-- 查看当前运行的查询
SHOW PROCESSLIST;

-- 终止特定的查询进程
KILL QUERY process_id;

-- 终止特定的连接进程
KILL process_id;

使用performance_schema监控语句执行时间

-- 启用statement_analysis消费者
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_summary_by_digest';

-- 查询执行时间最长的语句
SELECT DIGEST_TEXT, COUNT_STAR, SUM_TIMER_WAIT/1000000000000 AS SUM_SECONDS,
    AVG_TIMER_WAIT/1000000000000 AS AVG_SECONDS, MAX_TIMER_WAIT/1000000000000 AS MAX_SECONDS
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_SECONDS DESC
LIMIT 10;

-- 清除语句统计信息
TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

全局通用表空间的优点

• 减少文件数量：多个表可以共享一个表空间文件，减少文件系统中的文件数量
• 更好的空间管理：可以更灵活地管理表空间的大小和位置
• 提高I/O性能：大的表空间文件可以减少文件打开和关闭的开销
• 支持所有行格式：可以存储使用任何行格式的InnoDB表

创建和使用全局通用表空间

-- 1. 创建全局通用表空间
CREATE TABLESPACE `ts_general` ADD DATAFILE 'ts_general.ibd' ENGINE = InnoDB;

-- 指定表空间文件的路径和大小
CREATE TABLESPACE `ts_general` ADD DATAFILE '/path/to/ts_general.ibd' 
    ENGINE = InnoDB FILE_BLOCK_SIZE = 8192;

-- 2. 在全局通用表空间中创建表
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
) TABLESPACE ts_general;

-- 将已有的表移动到全局通用表空间
ALTER TABLE departments TABLESPACE ts_general;

-- 3. 查看全局通用表空间信息
SELECT * FROM information_schema.innodb_tablespaces WHERE NAME = 'ts_general';

-- 4. 查看哪些表使用了全局通用表空间
SELECT table_schema, table_name, tablespace_name
FROM information_schema.tables
WHERE tablespace_name = 'ts_general';

-- 5. 调整全局通用表空间的大小
-- 全局通用表空间会自动扩展，不需要手动调整大小

-- 6. 删除全局通用表空间（需要先删除表空间中的所有表）
DROP TABLESPACE ts_general;

不可见索引的用途

• 安全地测试索引删除：在实际删除索引之前，可以先将其设置为不可见，观察对查询性能的影响
• 临时禁用索引：在进行大批量数据导入或更新时，可以临时禁用索引，提高操作速度
• A/B测试：比较使用和不使用某个索引的查询性能差异

创建和管理不可见索引

-- 1. 创建不可见索引
CREATE INDEX idx_department_id ON employees (department_id) INVISIBLE;

-- 2. 修改现有索引为不可见
ALTER TABLE employees ALTER INDEX idx_department_id INVISIBLE;

-- 3. 修改不可见索引为可见
ALTER TABLE employees ALTER INDEX idx_department_id VISIBLE;

-- 4. 查看索引的可见性
SELECT index_name, is_visible
FROM information_schema.statistics
WHERE table_schema = 'hr' AND table_name = 'employees';

-- 5. 强制使用不可见索引（仅适用于当前会话）
-- 设置会话变量强制使用不可见索引
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

-- 恢复默认设置
SET SESSION optimizer_switch = 'use_invisible_indexes=off';

-- 6. 全局启用或禁用不可见索引的使用
SET GLOBAL optimizer_switch = 'use_invisible_indexes=on';
SET GLOBAL optimizer_switch = 'use_invisible_indexes=off';

资源组的用途

• 优先级管理：为不同类型的工作负载设置不同的优先级，确保关键任务获得足够的CPU资源
• 资源隔离：将不同的工作负载分配到不同的CPU核心，避免相互干扰
• 性能优化：根据工作负载的特点，优化CPU资源的使用，提高系统整体性能
• 成本控制：合理分配CPU资源，避免资源浪费

创建和管理资源组

-- 1. 创建资源组
-- 创建高优先级资源组
CREATE RESOURCE GROUP rg_high
    TYPE = USER
    VCPU = 0,1
    THREAD_PRIORITY = 10;

-- 创建低优先级资源组
CREATE RESOURCE GROUP rg_low
    TYPE = USER
    VCPU = 2,3
    THREAD_PRIORITY = 0;

-- 2. 修改资源组
ALTER RESOURCE GROUP rg_high
    VCPU = 0,1,2
    THREAD_PRIORITY = 15;

-- 3. 删除资源组
DROP RESOURCE GROUP rg_low;

-- 4. 查看资源组信息
SELECT * FROM information_schema.resource_groups;

-- 5. 为会话分配资源组
SET RESOURCE GROUP rg_high;

-- 6. 为存储过程或函数分配资源组
CREATE PROCEDURE get_employee_details()
    RESOURCE GROUP rg_high
BEGIN
    SELECT * FROM employees;
END;

-- 7. 为事件调度器事件分配资源组
CREATE EVENT update_stats
    ON SCHEDULE EVERY 1 HOUR
    RESOURCE GROUP rg_low
DO
    CALL update_department_stats();

-- 8. 查看当前会话的资源组
SELECT @@resource_group;

-- 9. 全局启用或禁用资源组
-- 在配置文件中设置
[mysqld]
resource_group_enabled = ON

-- 或者在运行时设置
SET GLOBAL resource_group_enabled = ON;
SET GLOBAL resource_group_enabled = OFF;

性能优化的一般原则

• 定期监控数据库性能：使用MySQL提供的性能监控工具，如Performance Schema、慢查询日志等，定期监控数据库性能
• 分析性能瓶颈：通过监控数据和执行计划分析，找到性能瓶颈的具体位置
• 制定优化计划：根据性能瓶颈的分析结果，制定合理的优化计划
• 实施优化措施：按照优化计划实施具体的优化措施
• 验证优化效果：优化后，通过性能测试验证优化效果
• 持续优化：性能优化是一个持续的过程，需要定期评估和调整

数据库设计的最佳实践

• 遵循范式设计：合理的数据库设计应遵循数据库范式，减少数据冗余
• 选择合适的数据类型：根据数据的特点选择合适的数据类型，如使用INT而不是VARCHAR存储整数
• 避免NULL值：尽量避免使用NULL值，因为NULL值会增加存储空间和查询复杂度
• 使用合适的存储引擎：根据应用需求选择合适的存储引擎，如InnoDB适用于事务处理，MyISAM适用于只读或读多写少的场景
• 分区表：对于大表，可以考虑使用分区表，提高查询和维护性能

查询优化的最佳实践

• 使用EXPLAIN分析查询：在编写复杂查询时，使用EXPLAIN分析查询执行计划，确保查询使用了最优的执行路径
• 避免SELECT *：只查询需要的列，减少数据传输和I/O操作
• 使用LIMIT限制结果集：如果只需要部分结果，使用LIMIT子句限制结果集大小
• 避免在WHERE子句中使用函数：在WHERE子句中使用函数会导致索引失效
• 使用JOIN替代子查询：在大多数情况下，JOIN操作比子查询更高效
• 使用预处理语句：预处理语句可以提高查询性能，避免SQL注入

索引优化的最佳实践

• 为常用的查询条件创建索引：WHERE子句、JOIN子句中经常使用的列适合创建索引
• 创建复合索引：对于经常一起使用的查询条件，可以创建复合索引
• 遵循最左前缀原则：在使用复合索引时，应遵循最左前缀原则
• 定期维护索引：定期使用ANALYZE TABLE和OPTIMIZE TABLE语句维护索引
• 删除无用的索引：定期检查并删除无用的索引，减少维护开销

服务器配置的最佳实践

• 合理设置内存参数：根据服务器内存大小，合理设置innodb_buffer_pool_size、key_buffer_size等内存参数
• 优化连接参数：根据应用需求，合理设置max_connections、wait_timeout等连接参数
• 配置日志参数：根据需要启用慢查询日志、二进制日志等，帮助监控和诊断问题
• 定期备份配置文件：定期备份MySQL配置文件，以便在出现问题时快速恢复
• 使用配置管理工具：使用配置管理工具（如Ansible、Puppet等）管理MySQL配置，确保配置的一致性

案例1：优化慢查询

问题描述：某电商网站的订单查询页面加载缓慢，用户体验差。

分析过程：

1. 查看慢查询日志，发现订单查询语句执行时间超过5秒
2. 使用EXPLAIN分析查询执行计划，发现查询没有使用索引，执行了全表扫描
3. 检查订单表的结构，发现查询条件中的用户ID列没有索引

优化方案：

-- 为用户ID列创建索引
CREATE INDEX idx_user_id ON orders (user_id);

优化效果：查询执行时间从5秒以上降低到0.1秒以下，页面加载速度显著提高。

案例2：优化高并发写入

问题描述：某秒杀活动中，大量用户同时下单，导致数据库写入性能下降，出现超时错误。

分析过程：

1. 监控数据库性能，发现InnoDB日志缓冲区不足，导致频繁的日志刷盘操作
2. 检查MySQL配置，发现innodb_log_buffer_size参数设置过小

优化方案：

-- 增加InnoDB日志缓冲区大小
SET GLOBAL innodb_log_buffer_size = 64M;

-- 调整innodb_flush_log_at_trx_commit参数（临时措施）
SET GLOBAL innodb_flush_log_at_trx_commit = 2;

-- 使用批量插入减少事务数量
INSERT INTO orders (user_id, product_id, quantity, order_time) VALUES
    (1001, 2001, 1, NOW()),
    (1002, 2001, 1, NOW()),
    (1003, 2001, 1, NOW());

优化效果：写入性能提高了3-5倍，超时错误明显减少。

案例3：优化大表查询

问题描述：某数据报表系统中的用户行为分析查询执行时间长，无法满足实时分析需求。

分析过程：

1. 检查发现用户行为表数据量超过1亿行，表结构没有分区
2. 查询需要扫描大量历史数据，导致I/O开销大

优化方案：

-- 对用户行为表进行分区（按时间分区）
ALTER TABLE user_behavior
PARTITION BY RANGE (YEAR(behavior_time)) (
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 创建本地分区索引
CREATE INDEX idx_behavior_time ON user_behavior (behavior_time) LOCAL;

-- 优化查询语句，利用分区裁剪
SELECT behavior_type, COUNT(*) AS count
FROM user_behavior
WHERE behavior_time >= '2023-01-01' AND behavior_time < '2024-01-01'
GROUP BY behavior_type;

优化效果：查询执行时间从30分钟以上降低到5分钟以内，满足了实时分析需求。