通用性能优化口诀

  • 缓存
  • 异步
  • 批处理

应用层次

  • 针对 innodb 存储引擎

写操作

  • 批量 insert
    • 减少 sql 编译的时间和空间复杂度
    • 减少网络消耗的时间复杂度
    • 减少磁盘寻址
  • 批量 update

读操作

  • 用 索引
查询类型 记录规模 消耗时间
主键索引 千万 1-10ms
唯一索引 千万 10-100ms
非唯一索引 千万 100-1000ms
无索引 百万 1s +

MySQL 配置

单机

max_connection=1000

  • 连接数量，默认 100
  • 如果我们的应用了连接池，是由最小连接数量的，比如 5
    • 部署了多个应用后，各个应用的最小连接数会消耗 mysql 的连接池数量
    • 比如这里第 21 个后就无法连接了

innodb_file_per_table=1

  • 开启独立表空间， mysql 5.6 之后默认开启
  • 优点：
    • 每个表都有自己独立的表空间。
    • 每个表的数据和索引都在自己的表空间中。
    • 可以实现单表在不同的数据库中移动。
    • 空间可以回收
    • 性能优势(除insert共享表空间略有优势外，其他方面独立表空间都表现更好)

innodb_buffer_pool_size=4G

  • innodb 存储引起的 buffer_pool

innodb_flush_log_at_trx_commit=2

  • 事务提交后， redo 日志的刷盘策略

innodb_log_file_size=256M

  • 日志文件大小，当日志文件达到阈值后，会切割日志
    • 此时无法写日志，刷盘可能会出问题
  • 但是如果设置太大，会导致恢复时对日志文件的寻址很久

innodb_log_buffer_size=16M

  • 日志文件缓存的大小 (redo?)

innodb_data_file_path=ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend

  • 实际数据文件大小
    • 这里指定前三个日志文件的大小，后面就自动扩展

其他

  1. 慢查询的开启和捕获
  2. explain + 慢 sql 分析
  3. show profile 查询 sql 在 mysql server 中的执行细节和生命周期情况
  4. sql 数据库服务器的参数调优