MySQL学习笔记（六）—— 性能优化小结

服务器层面

• innodb_buffer_pool_size

  将缓冲池的大小设置的尽可能大，比如设为总内存的3/4。这样可以减少mysql的磁盘IO次数，使得尽可能地从缓冲池里读数据

• innodb_log_file_size

  在生产环境下，可以尽可能地把一些日志开关给关掉。比如通用查询日志，慢查询日志，错误日志。并且，将redo log的大小设置的足够大，避免由于redo log过小，导致频繁的脏页刷磁盘

• innodb_flush_log_at_trx_commit

  当对数据的安全性要求不是那么高的时候，可以考虑将此参数设为0或者2，减少刷磁盘频率

表设计层面

• 对于统计和分析类等对实时性要求不高的需求（OLAP），设计中间表，避免直接查大量的raw data
• 创建合理的冗余字段，以减少连表查询
• 表中不经常使用的字段，或者存储了过多字段，考虑拆表
• 每张表都要有个主键，且主键最好是自增的int类型

SQL语句层面

1. 索引优化

   1. 为经常出现在where条件中的字段，需要排序的字段创建合适的索引（当读多写少时，可以考虑创建索引）
   2. 当需要对多个列建索引时，优先考虑组合索引，而不是多个单列索引，并且合理的组织组合索引的顺序，将筛选粒度大的列，放到组合索引的最左
   3. 尽量使用覆盖索引，而不要使用SELECT * ，可避免回表查询

2. LIMIT优化

   1. 若预计查询结果只有1条，使用LIMIT 1可以提前终止全表扫描

   2. 子查询优化

      当使用LIMIT进行分页时，页码过大时，LIMIT的偏移量会很大，此时会导致MySQL扫描大量不需要的行，然后再丢弃，性能很差。比如LIMIT 10000,20 ，会先扫描前10000行，然后丢弃，最后取10000后的20行。

      此时可以使用子查询来做优化

      -- 原SQL
      select * from product limit 10000,20;
      -- 优化后的SQL
      select * from product where id >(select id from product order by id limit 10000,1) limit 20;
      -- 由于子查询使用了id主键索引，且查询是覆盖索引，可以很快的定位到10000的位置
      -- 当单表查询，且主键已经是排序好的，可以直接简写如下
      select * from product where id > 10000 limit 20;
      

3. 其他优化

   1. 统计数量时，尽量用count(1)，或count(列)，而不要用count(*)
   2. 两张表进行关联时，关联字段最好都建立索引，且最好字段类型一致
   3. where 条件中不使用not in (可使用not exists)
   4. 合理使用慢查询日志，explain查看执行计划，show profile 查看SQL执行时的资源使用情况