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

缺乏、安全感 2023-07-20 13:39 19阅读 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行。

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

      1. -- 原SQL
      2. select * from product limit 10000,20;
      3. -- 优化后的SQL
      4. select * from product where id >(select id from product order by id limit 10000,1) limit 20;
      5. -- 由于子查询使用了id主键索引，且查询是覆盖索引，可以很快的定位到10000的位置
      6. -- 当单表查询，且主键已经是排序好的，可以直接简写如下
      7. 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执行时的资源使用情况

发表评论

表情：
评论列表 （有 0 条评论，19人围观）

还没有评论，来说两句吧...

相关阅读