MySQL 数据库系统全面指南

什么是 MySQL？

MySQL 是世界上最流行的开源关系型数据库管理系统（RDBMS）之一。它由瑞典公司 MySQL AB 开发，现在由 Oracle 公司维护。MySQL 以其高性能、可靠性和易用性而闻名，被广泛应用于 Web 应用程序、企业级应用和各种规模的项目中。

快速体验 MySQL

# Docker 快速启动 MySQL 8.0
docker run --name mysql-demo \
  -e MYSQL_ROOT_PASSWORD=your_password \
  -e MYSQL_DATABASE=demo_db \
  -e MYSQL_USER=demo_user \
  -e MYSQL_PASSWORD=demo_pass \
  -p 3306:3306 \
  -d mysql:8.0

# 连接到 MySQL
mysql -h localhost -u demo_user -p demo_db

-- 创建示例表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB;

-- 插入测试数据
INSERT INTO users (username, email) VALUES 
('john_doe', 'john@example.com'),
('jane_smith', 'jane@example.com'),
('bob_wilson', 'bob@example.com');

-- 基本查询
SELECT * FROM users WHERE username LIKE 'j%';

为什么选择 MySQL？

1. 开源免费：MySQL 社区版完全免费，源代码开放
2. 跨平台支持：支持 Linux、Windows、macOS 等多种操作系统
3. 高性能：优化的存储引擎和查询处理器提供卓越性能
4. 可扩展性：从小型应用到大型企业级系统都能胜任
5. 丰富的生态系统：拥有庞大的社区和丰富的工具支持
6. 标准兼容：遵循 SQL 标准，易于学习和迁移

MySQL 的发展历程

MySQL 的发展经历了几个重要阶段：

• 1995年：MySQL 1.0 发布，由 Michael Widenius 和 David Axmark 创建
• 2000年：MySQL 3.23 发布，引入了 InnoDB 存储引擎
• 2003年：MySQL 4.0 发布，支持联合查询和子查询
• 2005年：MySQL 5.0 发布，引入存储过程、触发器和视图
• 2008年：Sun Microsystems 收购 MySQL AB
• 2010年：Oracle 收购 Sun Microsystems，MySQL 成为 Oracle 产品
• 2010年：MySQL 5.5 发布，InnoDB 成为默认存储引擎
• 2013年：MySQL 5.6 发布，性能大幅提升
• 2015年：MySQL 5.7 发布，引入 JSON 数据类型
• 2018年：MySQL 8.0 发布，引入窗口函数、CTE 等新特性

MySQL 核心架构概览

MySQL 采用分层架构设计，主要包括：

1. 连接层（Connection Layer）

• 负责客户端连接管理
• 处理用户认证和权限验证
• 管理连接池和线程池

-- 查看当前连接状态
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Connections';
SHOW STATUS LIKE 'Max_used_connections';

-- 优化连接配置
SET GLOBAL max_connections = 1000;
SET GLOBAL wait_timeout = 28800;
SET GLOBAL interactive_timeout = 28800;

2. 服务层（Service Layer）

• SQL 解析器：解析 SQL 语句
• 查询优化器：生成最优执行计划
• 查询缓存：缓存查询结果
• 内置函数和存储过程支持

-- 查看查询缓存状态
SHOW VARIABLES LIKE 'query_cache%';
SHOW STATUS LIKE 'Qcache%';

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
EXPLAIN FORMAT=JSON SELECT u.*, p.title 
FROM users u 
JOIN posts p ON u.id = p.user_id 
WHERE u.created_at > '2023-01-01';

3. 存储引擎层（Storage Engine Layer）

• 可插拔的存储引擎架构
• 不同存储引擎提供不同特性
• 主要存储引擎：InnoDB、MyISAM、Memory 等

-- 查看可用存储引擎
SHOW ENGINES;

-- 查看表的存储引擎
SHOW TABLE STATUS LIKE 'users';

-- 修改表的存储引擎
ALTER TABLE users ENGINE = InnoDB;

-- InnoDB 状态监控
SHOW ENGINE INNODB STATUS;

4. 文件系统层（File System Layer）

• 数据文件存储
• 日志文件管理
• 配置文件处理

# 查看 MySQL 数据目录
mysql -e "SHOW VARIABLES LIKE 'datadir';"

# 查看日志文件配置
mysql -e "SHOW VARIABLES LIKE '%log%';"

# 查看二进制日志
mysql -e "SHOW BINARY LOGS;"
mysql -e "SHOW MASTER STATUS;"

核心能力详解

以下是 MySQL 的核心能力模块，每个模块都有详细的专门文档：

基础能力

1. MySQL 基础语法

   MySQL 基础语法是掌握数据库操作的基石，涵盖了完整的 SQL 语言体系。数据定义语言（DDL）负责数据库结构的创建和修改，包括 CREATE、ALTER、DROP 等命令，用于管理数据库、表、索引等对象。数据操作语言（DML）处理数据的增删改操作，INSERT 用于插入新数据，UPDATE 修改现有数据，DELETE 删除不需要的记录。数据查询语言（DQL）以 SELECT 为核心，支持复杂的查询条件、连接操作、聚合函数和子查询，是日常使用最频繁的语法。数据控制语言（DCL）管理用户权限和访问控制，通过 GRANT 和 REVOKE 命令确保数据安全。掌握这些基础语法不仅能够进行基本的数据库操作，更为后续的高级特性学习奠定坚实基础。

2. 服务模块组成

   MySQL 服务架构采用模块化设计，各组件协同工作提供完整的数据库服务。连接管理器作为客户端与服务器的桥梁，负责处理连接建立、身份验证、权限检查和连接池管理，确保系统能够高效处理大量并发连接。SQL 解析器将客户端发送的 SQL 语句进行词法和语法分析，构建抽象语法树，为后续处理做准备。查询优化器是 MySQL 的核心智能组件，通过成本估算和统计信息选择最优的执行计划，包括索引选择、连接顺序优化和查询重写等。缓存系统通过查询缓存和各种内存缓冲区提升性能，减少磁盘 I/O 操作。理解这些模块的工作原理有助于进行性能调优和问题诊断，是数据库管理员和开发人员必备的知识。

3. 数据类型系统

   MySQL 提供了丰富的数据类型系统，合理选择数据类型对性能和存储效率至关重要。数值类型包括整型（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）和浮点型（FLOAT、DOUBLE、DECIMAL），需要根据数据范围和精度要求选择合适的类型。字符串类型涵盖定长的 CHAR、变长的 VARCHAR、大文本的 TEXT 系列，以及二进制数据的 BINARY 和 BLOB 系列，选择时要考虑存储空间、查询性能和字符集支持。日期时间类型提供 DATE、TIME、DATETIME、TIMESTAMP 和 YEAR，满足不同的时间处理需求。MySQL 8.0 引入的 JSON 数据类型支持半结构化数据存储和查询，空间数据类型支持地理信息系统应用。正确的数据类型选择不仅影响存储效率，还直接关系到索引效果和查询性能。

核心机制

4. 事务处理机制

   事务处理是 MySQL 确保数据一致性和可靠性的核心机制。ACID 特性（原子性、一致性、隔离性、持久性）构成了事务处理的理论基础，原子性确保事务要么全部成功要么全部失败，一致性保证数据库从一个有效状态转换到另一个有效状态，隔离性防止并发事务相互干扰，持久性确保已提交的事务永久保存。事务隔离级别（读未提交、读已提交、可重复读、串行化）平衡了并发性能与数据一致性，MySQL 默认使用可重复读级别。锁机制通过共享锁和排他锁控制并发访问，支持行级锁、表级锁和意向锁。死锁检测算法能够自动发现和解决死锁问题。MVCC 多版本并发控制通过 Undo Log 实现读写分离，提高并发性能。深入理解事务机制对于设计高并发、高可靠的应用系统至关重要。

5. 索引系统

   索引系统是 MySQL 查询性能优化的关键技术，通过建立数据的快速访问路径显著提升查询效率。B+ 树是 InnoDB 的主要索引结构，具有平衡、有序、支持范围查询的特点，叶子节点存储完整数据或主键值。索引类型包括主键索引（聚簇索引）、唯一索引、普通索引、全文索引和空间索引，每种类型适用于不同的查询场景。复合索引遵循最左前缀原则，合理的字段顺序能够覆盖更多查询模式。索引优化策略包括避免冗余索引、选择合适的索引列、考虑索引的选择性和维护成本。覆盖索引能够避免回表操作，前缀索引可以减少索引空间占用。理解索引原理和优化技巧是数据库性能调优的核心技能，直接影响应用系统的响应速度和吞吐量。

6. 存储引擎

   存储引擎是 MySQL 架构的核心组件，不同引擎提供不同的存储特性和性能表现。InnoDB 是默认存储引擎，支持事务、外键、行级锁和崩溃恢复，采用聚簇索引组织数据，适合 OLTP 应用和需要高并发、高可靠性的场景。MyISAM 引擎结构简单，支持表级锁，查询速度快但不支持事务，适合读多写少的数据仓库和报表应用。Memory 引擎将数据存储在内存中，访问速度极快但数据易失，适合临时表和缓存场景。其他引擎如 Archive 适合归档数据，CSV 支持与外部系统数据交换。选择合适的存储引擎需要考虑事务需求、并发模式、数据量大小、查询模式等因素。理解各存储引擎的特点和适用场景，能够为不同业务需求选择最优的技术方案。

高级特性

7. 查询优化

   查询优化是提升 MySQL 性能的核心技术，涉及从 SQL 编写到执行计划分析的全过程优化。执行计划分析通过 EXPLAIN 命令揭示查询的执行路径，包括表访问顺序、索引使用情况、连接类型和预估成本，帮助识别性能瓶颈。查询优化技巧包括合理使用索引、避免全表扫描、优化 JOIN 操作、减少数据传输量、使用合适的数据类型等。慢查询诊断通过慢查询日志和性能监控工具识别问题 SQL，分析执行时间、锁等待、I/O 消耗等指标。性能调优方法涵盖 SQL 重写、索引优化、参数调整、硬件升级等多个层面。查询缓存、分区表、读写分离等技术能够进一步提升系统性能。掌握查询优化技能对于构建高性能数据库应用至关重要，直接影响用户体验和系统可扩展性。

8. 复制与高可用

   复制与高可用技术是构建企业级 MySQL 系统的基础，确保数据安全和服务连续性。主从复制通过二进制日志（binlog）将主库的变更同步到从库，支持异步、半同步和同步三种模式，平衡了性能与一致性需求。主主复制允许双向数据同步，但需要careful处理冲突和循环复制问题。读写分离架构将读操作分发到从库，写操作保留在主库，有效提升系统并发处理能力。故障转移机制包括手动切换和自动故障检测，MHA、Orchestrator 等工具能够实现秒级故障恢复。MySQL Group Replication 和 MySQL InnoDB Cluster 提供了更高级的高可用解决方案。理解复制原理和高可用架构设计，对于构建稳定可靠的生产环境至关重要。

9. 分区表技术 分区表技术是处理大数据量的重要手段，通过将大表分割成多个较小的、更易管理的部分来提升查询性能和维护效率。MySQL 支持范围分区、列表分区、哈希分区和键分区等多种分区策略，每种策略适用于不同的数据分布模式。范围分区常用于时间序列数据，列表分区适合离散值分布，哈希分区确保数据均匀分布，键分区基于主键或唯一键进行分区。分区表设计需要考虑查询模式、数据增长趋势、维护需求等因素，合理的分区键选择能够实现分区裁剪，显著提升查询性能。分区维护操作包括添加、删除、合并分区等，需要careful规划以避免影响业务。分区表在数据仓库、日志系统、历史数据归档等场景中发挥重要作用。

运维管理

10. 安全与权限

    安全与权限管理是 MySQL 生产环境的重要保障，构建多层次的安全防护体系。用户管理体系通过创建专用账户、设置强密码策略、定期轮换密码等措施确保访问安全。权限控制机制采用最小权限原则，通过全局权限、数据库权限、表权限和列权限实现精细化访问控制，角色管理简化权限分配和维护。数据加密技术包括传输加密（SSL/TLS）和存储加密（透明数据加密 TDE），保护数据在传输和存储过程中的安全。审计日志配置记录用户操作、权限变更、数据访问等关键事件，满足合规要求和安全审计需求。防火墙配置、网络隔离、定期安全扫描等措施进一步加强系统安全。建立完善的安全管理制度对于保护企业数据资产至关重要。

11. 备份与恢复

    备份与恢复是数据库运维的核心工作，确保数据安全和业务连续性。备份策略设计需要考虑 RTO（恢复时间目标）和 RPO（恢复点目标）要求，制定全量备份、增量备份和差异备份的组合方案。逻辑备份通过 mysqldump 导出 SQL 语句，具有跨平台、易于理解的优点，适合小到中等规模数据库。物理备份直接复制数据文件，速度快、占用空间小，适合大型数据库，工具包括 MySQL Enterprise Backup、Percona XtraBackup 等。增量备份技术基于二进制日志实现点对点恢复，最小化数据丢失。灾难恢复方案包括异地备份、热备份、冷备份等策略，确保在各种故障场景下能够快速恢复服务。定期备份验证和恢复演练是确保备份有效性的重要措施。

12. 监控与运维

    监控与运维是保障 MySQL 系统稳定运行的关键环节，通过主动监控和预防性维护避免故障发生。性能监控指标涵盖 QPS/TPS、连接数、缓存命中率、锁等待、I/O 使用率等关键指标，通过阈值告警及时发现异常。运维工具包括 MySQL Workbench、phpMyAdmin、Percona Toolkit、pt-query-digest 等，提供图形化管理界面和命令行工具。故障诊断方法包括日志分析、性能分析、慢查询分析、锁分析等，快速定位和解决问题。容量规划策略基于历史数据和业务增长预测，合理规划硬件资源、存储空间和网络带宽。自动化运维通过脚本和工具实现日常维护任务的自动化执行，提高运维效率和可靠性。建立完善的监控体系和运维流程对于保障系统稳定性至关重要。

学习路径建议

初学者路径（0-3个月）

1. 理解关系型数据库基本概念
2. 学习 MySQL 安装和基本配置
3. 掌握基础 SQL 语法和操作
4. 了解数据类型和表设计
5. 学习基本的查询和数据操作

进阶路径（3-6个月）

1. 深入理解索引原理和优化
2. 学习事务和锁机制
3. 掌握查询优化技巧
4. 了解存储引擎特性
5. 学习基本的性能调优

高级路径（6个月以上）

1. 掌握复制和高可用架构
2. 学习分区表和分库分表
3. 深入理解 MySQL 内核机制
4. 掌握运维和监控技能
5. 学习大规模部署和优化

实际应用场景

MySQL 在以下场景中表现出色：

Web 应用

电商平台数据库设计

-- 用户表
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_phone (phone)
) ENGINE=InnoDB;

-- 商品表
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category_id INT,
    status ENUM('active', 'inactive', 'deleted') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_status (status),
    INDEX idx_price (price)
) ENGINE=InnoDB;

-- 订单表
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending', 'paid', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- 订单详情表
CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB;

高并发场景优化

-- 库存扣减的原子操作
START TRANSACTION;
SELECT stock_quantity FROM products WHERE id = 1 FOR UPDATE;
UPDATE products SET stock_quantity = stock_quantity - 1 WHERE id = 1 AND stock_quantity > 0;
COMMIT;

-- 使用存储过程处理复杂业务逻辑
DELIMITER //
CREATE PROCEDURE CreateOrder(
    IN p_user_id BIGINT,
    IN p_product_id BIGINT,
    IN p_quantity INT,
    OUT p_order_id BIGINT,
    OUT p_result VARCHAR(100)
)
BEGIN
    DECLARE v_stock INT DEFAULT 0;
    DECLARE v_price DECIMAL(10,2) DEFAULT 0;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_result = 'Error occurred';
    END;
    
    START TRANSACTION;
    
    -- 检查库存
    SELECT stock_quantity, price INTO v_stock, v_price
    FROM products WHERE id = p_product_id FOR UPDATE;
    
    IF v_stock < p_quantity THEN
        SET p_result = 'Insufficient stock';
        ROLLBACK;
    ELSE
        -- 创建订单
        INSERT INTO orders (user_id, total_amount) 
        VALUES (p_user_id, v_price * p_quantity);
        SET p_order_id = LAST_INSERT_ID();
        
        -- 添加订单项
        INSERT INTO order_items (order_id, product_id, quantity, price)
        VALUES (p_order_id, p_product_id, p_quantity, v_price);
        
        -- 扣减库存
        UPDATE products SET stock_quantity = stock_quantity - p_quantity
        WHERE id = p_product_id;
        
        SET p_result = 'Success';
        COMMIT;
    END IF;
END //
DELIMITER ;

企业应用

CRM 系统客户管理

-- 客户表
CREATE TABLE customers (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(200) NOT NULL,
    contact_person VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    industry VARCHAR(50),
    status ENUM('prospect', 'active', 'inactive') DEFAULT 'prospect',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    assigned_to BIGINT,
    INDEX idx_company (company_name),
    INDEX idx_status (status),
    INDEX idx_assigned (assigned_to)
) ENGINE=InnoDB;

-- 销售机会表
CREATE TABLE opportunities (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    value DECIMAL(12,2),
    stage ENUM('qualification', 'proposal', 'negotiation', 'closed_won', 'closed_lost'),
    probability INT DEFAULT 0,
    expected_close_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    INDEX idx_customer (customer_id),
    INDEX idx_stage (stage),
    INDEX idx_close_date (expected_close_date)
) ENGINE=InnoDB;

-- 销售漏斗分析查询
SELECT 
    stage,
    COUNT(*) as opportunity_count,
    SUM(value) as total_value,
    AVG(probability) as avg_probability
FROM opportunities 
WHERE stage != 'closed_lost'
GROUP BY stage
ORDER BY FIELD(stage, 'qualification', 'proposal', 'negotiation', 'closed_won');

数据分析

业务报表查询优化

-- 创建汇总表提升查询性能
CREATE TABLE daily_sales_summary (
    date DATE PRIMARY KEY,
    total_orders INT DEFAULT 0,
    total_revenue DECIMAL(12,2) DEFAULT 0,
    avg_order_value DECIMAL(10,2) DEFAULT 0,
    unique_customers INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 定时任务更新汇总数据
CREATE EVENT update_daily_summary
ON SCHEDULE EVERY 1 DAY
STARTS '2024-01-01 01:00:00'
DO
INSERT INTO daily_sales_summary (date, total_orders, total_revenue, avg_order_value, unique_customers)
SELECT 
    DATE(created_at) as date,
    COUNT(*) as total_orders,
    SUM(total_amount) as total_revenue,
    AVG(total_amount) as avg_order_value,
    COUNT(DISTINCT user_id) as unique_customers
FROM orders 
WHERE DATE(created_at) = CURDATE() - INTERVAL 1 DAY
GROUP BY DATE(created_at)
ON DUPLICATE KEY UPDATE
    total_orders = VALUES(total_orders),
    total_revenue = VALUES(total_revenue),
    avg_order_value = VALUES(avg_order_value),
    unique_customers = VALUES(unique_customers);

-- 复杂分析查询
WITH monthly_trends AS (
    SELECT 
        DATE_FORMAT(date, '%Y-%m') as month,
        SUM(total_revenue) as monthly_revenue,
        SUM(total_orders) as monthly_orders
    FROM daily_sales_summary
    WHERE date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
    GROUP BY DATE_FORMAT(date, '%Y-%m')
),
revenue_growth AS (
    SELECT 
        month,
        monthly_revenue,
        LAG(monthly_revenue) OVER (ORDER BY month) as prev_month_revenue,
        ((monthly_revenue - LAG(monthly_revenue) OVER (ORDER BY month)) / 
         LAG(monthly_revenue) OVER (ORDER BY month)) * 100 as growth_rate
    FROM monthly_trends
)
SELECT 
    month,
    monthly_revenue,
    ROUND(growth_rate, 2) as growth_percentage
FROM revenue_growth
WHERE prev_month_revenue IS NOT NULL
ORDER BY month;

与其他数据库的对比

特性	MySQL	PostgreSQL	Oracle	SQL Server
开源性	开源	开源	商业	商业
性能	高	高	很高	高
扩展性	好	很好	很好	好
易用性	很好	好	一般	好
社区支持	很好	好	一般	一般
成本	低	低	高	高

生产环境配置实践

性能优化配置

# my.cnf 生产环境配置示例
[mysqld]
# 基础配置
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql

# 内存配置 (假设 16GB 内存服务器)
innodb_buffer_pool_size = 12G
innodb_log_file_size = 1G
innodb_log_buffer_size = 64M
key_buffer_size = 256M
tmp_table_size = 256M
max_heap_table_size = 256M

# 连接配置
max_connections = 1000
max_connect_errors = 100000
wait_timeout = 28800
interactive_timeout = 28800

# InnoDB 配置
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 查询缓存 (MySQL 5.7 及以下)
query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 2M

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

# 二进制日志
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G

# 安全配置
skip_name_resolve = 1
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

监控和诊断

-- 性能监控查询
-- 1. 查看当前运行的查询
SELECT 
    id,
    user,
    host,
    db,
    command,
    time,
    state,
    LEFT(info, 100) as query_preview
FROM information_schema.processlist 
WHERE command != 'Sleep' 
ORDER BY time DESC;

-- 2. 查看表锁等待情况
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 3. 查看 InnoDB 状态
SELECT 
    VARIABLE_NAME,
    VARIABLE_VALUE
FROM information_schema.global_status 
WHERE VARIABLE_NAME IN (
    'Innodb_buffer_pool_read_requests',
    'Innodb_buffer_pool_reads',
    'Innodb_buffer_pool_pages_dirty',
    'Innodb_buffer_pool_pages_free',
    'Innodb_rows_read',
    'Innodb_rows_inserted',
    'Innodb_rows_updated',
    'Innodb_rows_deleted'
);

-- 4. 分析慢查询
SELECT 
    schema_name,
    digest_text,
    count_star,
    avg_timer_wait/1000000000 as avg_time_seconds,
    max_timer_wait/1000000000 as max_time_seconds,
    sum_rows_examined/count_star as avg_rows_examined
FROM performance_schema.events_statements_summary_by_digest 
WHERE avg_timer_wait > 1000000000  -- 超过1秒的查询
ORDER BY avg_timer_wait DESC 
LIMIT 10;

常见问题解决方案

1. 连接数过多问题

-- 查看连接使用情况
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

-- 查看连接来源
SELECT 
    SUBSTRING_INDEX(host, ':', 1) as host_ip,
    COUNT(*) as connection_count,
    user
FROM information_schema.processlist 
GROUP BY host_ip, user 
ORDER BY connection_count DESC;

-- 解决方案：优化连接池配置
SET GLOBAL max_connections = 2000;
SET GLOBAL wait_timeout = 600;
SET GLOBAL interactive_timeout = 600;

2. 磁盘空间不足

# 查看数据库大小
mysql -e "
SELECT 
    table_schema as 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) as 'Size (GB)'
FROM information_schema.tables 
GROUP BY table_schema 
ORDER BY SUM(data_length + index_length) DESC;"

# 查看大表
mysql -e "
SELECT 
    table_schema,
    table_name,
    ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) as 'Size (GB)',
    table_rows
FROM information_schema.tables 
WHERE (data_length + index_length) > 1024*1024*1024 
ORDER BY (data_length + index_length) DESC;"

# 清理二进制日志
mysql -e "PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);"

3. 查询性能优化

-- 创建复合索引优化查询
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);

-- 使用分区表处理大数据量
CREATE TABLE orders_partitioned (
    id BIGINT AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id, created_at)
) ENGINE=InnoDB
PARTITION BY RANGE (YEAR(created_at)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- 查询重写优化
-- 原始查询（性能差）
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';

-- 优化后查询
SELECT * FROM orders 
WHERE created_at >= '2024-01-01 00:00:00' 
AND created_at < '2024-01-02 00:00:00';

高可用架构实践

主从复制配置

# 主库配置 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
gtid-mode = ON
enforce-gtid-consistency = ON

# 从库配置 (my.cnf)
[mysqld]
server-id = 2
relay-log = relay-bin
read-only = 1
gtid-mode = ON
enforce-gtid-consistency = ON

-- 主库创建复制用户
CREATE USER 'replication'@'%' IDENTIFIED BY 'strong_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%';
FLUSH PRIVILEGES;

-- 从库配置复制
CHANGE MASTER TO
    MASTER_HOST = '192.168.1.100',
    MASTER_USER = 'replication',
    MASTER_PASSWORD = 'strong_password',
    MASTER_AUTO_POSITION = 1;

START SLAVE;

-- 检查复制状态
SHOW SLAVE STATUS\G

读写分离应用代码示例

# Python 读写分离示例
import pymysql
from contextlib import contextmanager

class DatabaseManager:
    def __init__(self):
        self.master_config = {
            'host': '192.168.1.100',
            'user': 'app_user',
            'password': 'password',
            'database': 'myapp'
        }
        self.slave_config = {
            'host': '192.168.1.101',
            'user': 'app_user',
            'password': 'password',
            'database': 'myapp'
        }
    
    @contextmanager
    def get_connection(self, read_only=False):
        config = self.slave_config if read_only else self.master_config
        conn = pymysql.connect(**config)
        try:
            yield conn
        finally:
            conn.close()
    
    def execute_query(self, sql, params=None, read_only=True):
        with self.get_connection(read_only=read_only) as conn:
            cursor = conn.cursor()
            cursor.execute(sql, params)
            if read_only:
                return cursor.fetchall()
            else:
                conn.commit()
                return cursor.lastrowid

# 使用示例
db = DatabaseManager()

# 读操作使用从库
users = db.execute_query(
    "SELECT * FROM users WHERE status = %s", 
    ('active',), 
    read_only=True
)

# 写操作使用主库
user_id = db.execute_query(
    "INSERT INTO users (username, email) VALUES (%s, %s)",
    ('john_doe', 'john@example.com'),
    read_only=False
)

总结

MySQL 作为一个成熟的关系型数据库系统，提供了完整的数据管理解决方案。从简单的 Web 应用到复杂的企业级系统，MySQL 都能提供可靠的数据存储和处理能力。

通过本指南的实践示例，你将能够：

• 快速上手：使用 Docker 快速搭建开发环境
• 设计数据库：创建适合业务场景的表结构和索引
• 优化性能：通过配置调优和查询优化提升系统性能
• 解决问题：诊断和解决常见的生产环境问题
• 构建高可用：实施主从复制和读写分离架构
• 监控运维：建立完善的监控和告警机制

建议按照提供的学习路径，结合实际项目需求，逐步深入学习 MySQL 的各个方面。每个核心能力模块都提供了详细的理论解释和实践指导，帮助你全面掌握 MySQL 数据库技术。