MySQL常见优化手段

1. 配置优化

1.1 缓存设置

• 查询缓存：查询缓存可以显著减少对同一查询的重复执行次数。

  SET GLOBAL query_cache_size = 268435456;  -- 设置查询缓存大小为 256MB
  
  SET GLOBAL query_cache_type = ON;         -- 启用查询缓存
  

  例如，执行 SELECT * FROM users WHERE id = 1; 后，再次执行相同查询将直接从缓存中读取结果。

• 表缓存：表缓存大小决定了可以同时打开的表数量。

  SET GLOBAL table_open_cache = 2000;  -- 设置表缓存大小为 2000
  

  例如，如果有大量并发查询访问不同的表，增大表缓存可以减少表打开和关闭的开销。

#### 1.2 内存分配
- **Innodb Buffer Pool**：用于缓存数据和索引，是 InnoDB 存储引擎的关键配置。
  ```sql
  SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 设置缓冲池大小为 1GB

例如，对于一个包含大量数据的表，增大 innodb_buffer_pool_size 可以减少磁盘 I/O，提高查询性能。

• 临时表内存：临时表大小限制了内存中可以创建的临时表大小。

  SET GLOBAL tmp_table_size = 67108864;       -- 设置临时表大小为 64MB
  SET GLOBAL max_heap_table_size = 67108864;  -- 设置最大内存表大小为 64MB
  

  例如，在执行复杂查询（如 GROUP BY 或 ORDER BY）时，临时表可能会存储在磁盘中，通过增大这两个参数可以减少磁盘写入次数。

1.3 日志和同步设置

• 二进制日志：二进制日志用于记录所有的写操作。

  SET GLOBAL sync_binlog = 1;  -- 每次写入事务提交后同步二进制日志
  

  例如，设置 sync_binlog = 1 确保每次事务提交后立即同步日志，提供最高数据安全性，但可能会影响性能。

• InnoDB 日志：InnoDB 日志文件大小影响恢复速度和性能。

  SET GLOBAL innodb_log_file_size = 268435456;  -- 设置 InnoDB 日志文件大小为 256MB
  SET GLOBAL innodb_log_buffer_size = 8388608;  -- 设置 InnoDB 日志缓冲区大小为 8MB
  

  例如，较大的日志文件减少了日志文件切换的频率，从而提高了写入性能。

2. 查询优化

2.1 索引

• 索引设计：为频繁查询的列创建索引。

  CREATE INDEX idx_users_email ON users(email);
  

  例如，执行 SELECT * FROM users WHERE email = 'example@example.com'; 时，索引 idx_users_email 可以显著提高查询速度。

• 索引使用情况：使用 EXPLAIN 分析查询计划。

  EXPLAIN