MySQL 8.4 Release Notes
- 10.1 优化概述10.1 Optimization Overview
- 10.2 SQL 语句优化10.2 Optimizing SQL Statements
- 10.2.1 SELECT 语句优化10.2.1 Optimizing SELECT Statements
- 10.2.2 子查询、派生表、视图引用和公共表表达式优化10.2.2 Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions
- 10.2.3 INFORMATION_SCHEMA 查询优化10.2.3 Optimizing INFORMATION_SCHEMA Queries
- 10.2.4 性能模式查询优化10.2.4 Optimizing Performance Schema Queries
- 10.2.5 数据修改语句优化10.2.5 Optimizing Data Change Statements
- 10.2.6 优化数据库权限10.2.6 Optimizing Database Privileges
- 10.2.7 其他优化技巧10.2.7 Other Optimization Tips
- 10.3 优化与索引10.3 Optimization and Indexes
- 10.3.1 MySQL 如何使用索引10.3.1 How MySQL Uses Indexes
- 10.3.2 主键优化10.3.2 Primary Key Optimization
- 10.3.3 空间索引优化10.3.3 SPATIAL Index Optimization
- 10.3.4 外键优化10.3.4 Foreign Key Optimization
- 10.3.5 列索引10.3.5 Column Indexes
- 10.3.6 多列索引10.3.6 Multiple-Column Indexes
- 10.3.7 验证索引使用情况10.3.7 Verifying Index Usage
- 10.3.8 InnoDB 和 MyISAM 索引统计收集10.3.8 InnoDB and MyISAM Index Statistics Collection
- 10.3.9 B 树与哈希索引比较10.3.9 Comparison of B-Tree and Hash Indexes
- 10.3.10 索引扩展的使用10.3.10 Use of Index Extensions
- 10.3.11 优化器使用生成列索引10.3.11 Optimizer Use of Generated Column Indexes
- 10.3.12 隐形索引10.3.12 Invisible Indexes
- 10.3.13 降序索引10.3.13 Descending Indexes
- 10.3.14 从 TIMESTAMP 列进行索引查找10.3.14 Indexed Lookups from TIMESTAMP Columns
- 10.4 优化数据库结构10.4 Optimizing Database Structure
- 10.4.1 优化数据大小10.4.1 Optimizing Data Size
- 10.4.2 优化 MySQL 数据类型10.4.2 Optimizing MySQL Data Types
- 10.4.3 多表优化10.4.3 Optimizing for Many Tables
- 10.4.4 MySQL 内部临时表的使用10.4.4 Internal Temporary Table Use in MySQL
- 10.4.5 数据库和表数量的限制10.4.5 Limits on Number of Databases and Tables
- 10.4.6 表大小的限制10.4.6 Limits on Table Size
- 10.4.7 表列数和行大小的限制10.4.7 Limits on Table Column Count and Row Size
- 10.5 为 InnoDB 表优化10.5 Optimizing for InnoDB Tables
- 10.5.1 优化 InnoDB 表的存储布局10.5.1 Optimizing Storage Layout for InnoDB Tables
- 10.5.2 优化 InnoDB 事务管理10.5.2 Optimizing InnoDB Transaction Management
- 10.5.3 优化 InnoDB 只读事务10.5.3 Optimizing InnoDB Read-Only Transactions
- 10.5.4 优化 InnoDB 重做日志10.5.4 Optimizing InnoDB Redo Logging
- 10.5.5 InnoDB 表的批量数据加载10.5.5 Bulk Data Loading for InnoDB Tables
- 10.5.6 优化 InnoDB 查询10.5.6 Optimizing InnoDB Queries
- 10.5.7 优化 InnoDB DDL 操作10.5.7 Optimizing InnoDB DDL Operations
- 10.5.8 优化 InnoDB 磁盘 I/O10.5.8 Optimizing InnoDB Disk I/O
- 10.5.9 优化 InnoDB 配置变量10.5.9 Optimizing InnoDB Configuration Variables
- 10.5.10 优化有大量表的 InnoDB 系统10.5.10 Optimizing InnoDB for Systems with Many Tables
- 10.6 MyISAM 表的优化10.6 Optimizing for MyISAM Tables
- 10.7 MEMORY 表的优化10.7 Optimizing for MEMORY Tables
- 10.8 了解查询执行计划10.8 Understanding the Query Execution Plan
- 10.8.1 使用 EXPLAIN 优化查询10.8.1 Optimizing Queries with EXPLAIN
- 10.8.2 EXPLAIN 输出格式10.8.2 EXPLAIN Output Format
- 10.8.3 扩展 EXPLAIN 输出格式10.8.3 Extended EXPLAIN Output Format
- 10.8.4 获取指定连接的执行计划信息10.8.4 Obtaining Execution Plan Information for a Named Connection
- 10.8.5 评估查询性能10.8.5 Estimating Query Performance
- 10.9 控制查询优化器10.9 Controlling the Query Optimizer
- 10.10 缓冲和缓存10.10 Buffering and Caching
- 10.11 优化锁操作10.11 Optimizing Locking Operations
- 10.12 优化 MySQL 服务器10.12 Optimizing the MySQL Server
- 10.13 测量性能（基准测试）10.13 Measuring Performance (Benchmarking)
- 10.14 检查服务器线程（进程）信息10.14 Examining Server Thread (Process) Information
- 10.14.1 访问进程列表10.14.1 Accessing the Process List
- 10.14.2 线程命令值10.14.2 Thread Command Values
- 10.14.3 一般线程状态10.14.3 General Thread States
- 10.14.4 复制源线程状态10.14.4 Replication Source Thread States
- 10.14.5 复制 I/O（接收器）线程状态10.14.5 Replication I/O (Receiver) Thread States
- 10.14.6 复制 SQL 线程状态10.14.6 Replication SQL Thread States
- 10.14.7 复制连接线程状态10.14.7 Replication Connection Thread States
- 10.14.8 NDB 集群线程状态10.14.8 NDB Cluster Thread States
- 10.14.9 事件调度器线程状态10.14.9 Event Scheduler Thread States
- 10.15 优化器跟踪10.15 Tracing the Optimizer
- 10.15.1 典型用法10.15.1 Typical Usage
- 10.15.2 控制跟踪的系统变量10.15.2 System Variables Controlling Tracing
- 10.15.3 可跟踪语句10.15.3 Traceable Statements
- 10.15.4 调优跟踪清理10.15.4 Tuning Trace Purging
- 10.15.5 跟踪内存使用情况10.15.5 Tracing Memory Usage
- 10.15.6 权限检查10.15.6 Privilege Checking
- 10.15.7 与 --debug 选项的交互10.15.7 Interaction with the --debug Option
- 10.15.8 optimizer_trace 系统变量10.15.8 The optimizer_trace System Variable
- 10.15.9 end_markers_in_json 系统变量10.15.9 The end_markers_in_json System Variable
- 10.15.10 选择要跟踪的优化器功能10.15.10 Selecting Optimizer Features to Trace
- 10.15.11 跟踪的一般结构10.15.11 Trace General Structure
- 10.15.12 示例10.15.12 Example
- 10.15.13 在其他应用程序中显示跟踪10.15.13 Displaying Traces in Other Applications
- 10.15.14 防止使用优化器跟踪10.15.14 Preventing the Use of Optimizer Trace
- 10.15.15 测试优化器跟踪10.15.15 Testing Optimizer Trace
- 10.15.16 优化器跟踪实现10.15.16 Optimizer Trace Implementation
本章解释了如何优化 MySQL 性能并提供示例。优化涉及在多个层面上配置、调优和测量性能。根据您的职位（开发人员、数据库管理员，或两者兼具），您可能会在单个 SQL 语句、整个应用程序、单个数据库服务器或多个网络数据库服务器层面进行优化。有时您可以主动规划性能，而其他时候您可能需要在问题发生后排查配置或代码问题。优化 CPU 和内存使用也可以提高可扩展性，使数据库在不降低速度的情况下处理更多负载。This chapter explains how to optimize MySQL performance and provides examples. Optimization involves configuring, tuning, and measuring performance, at several levels. Depending on your job role (developer, DBA, or a combination of both), you might optimize at the level of individual SQL statements, entire applications, a single database server, or multiple networked database servers. Sometimes you can be proactive and plan in advance for performance, while other times you might troubleshoot a configuration or code issue after a problem occurs. Optimizing CPU and memory usage can also improve scalability, allowing the database to handle more load without slowing down.