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

缺乏、安全感 2023-07-20 13:39 15阅读 0赞

### 文章目录 ###

*   *  服务器层面
     *  表设计层面
     *  SQL语句层面

## 服务器层面 ##

*  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执行时的资源使用情况