MySQL优化

Tabll其它

法则：

  1. 当只需要查询一个结果时加上LIMIT 1
    • 这样的话数据库引擎会在找到一条数据后立即停止搜索，而不会尝试往后找第二条符合的数据
  2. 使用Prepared Statements防止SQL注入
    • PHP详见：http://php.net/manual/en/mysqli.quickstart.prepared-statements.php
  3. 使用查询缓存
    • query_cache_type
      值域为：0 -– 不启用查询缓存；
      值域为：1 -– 启用查询缓存，只要符合查询缓存的要求，客户端的查询语句和记录集斗可以缓存起来，共其他客户端使用；
      值域为：2 -– 启用查询缓存，只要查询语句中添加了参数：sql_cache，且符合查询缓存的要求，客户端的查询语句和记录集，则可以缓存起来，共其他客户端使用；
    • query_cache_size
      允许设置query_cache_size的值最小为40K，对于最大值则可以几乎认为无限制，实际生产环境的应用经验告诉我们，该值并不是越大， 查询缓存的命中率就越高，也不是对服务器负载下降贡献大，反而可能抵消其带来的好处，甚至增加服务器的负载，至于该如何设置，下面的章节讲述，推荐设置 为：64M；
    • query_cache_limit
      限制查询缓存区最大能缓存的查询记录集，可以避免一个大的查询记录集占去大量的内存区域，而且往往小查询记录集是最有效的缓存记录集，默认设置为1M，建议修改为16k~1024k之间的值域，不过最重要的是根据自己应用的实际情况进行分析、预估来设置；
    • query_cache_min_res_unit
      设置查询缓存分配内存的最小单位，要适当地设置此参数，可以做到为减少内存块的申请和分配次数，但是设置过大可能导致内存碎片数值上升。默认值为4K，建议设置为1k~16K
    • query_cache_wlock_invalidate
      该参数主要涉及MyISAM引擎，若一个客户端对某表加了写锁，其他客户端发起的查询请求，且查询语句有对应的查询缓存记录，是否允许直接读取查询缓存的记录集信息，还是等待写锁的释放。默认设置为0，也即允许；
  4. 每张表的主键为UNSIGNED（无符号整型）
    • 除关联表设置为外键ID
  5. 尽量使用固定长度的字段
    • 并且应将固定长度的字段表和非固定长度的表分开，SQL在处理都是固定长度的表时会默认把它当作固定表处理，这样执行效率会提升，否则需要一个主键一个主键的检索
  6. 使用PROCEDURE ANALYSE()
    • 用法：
      SELECT … FROM … WHERE … PROCEDURE ANALYSE([max_elements,[max_memory]])
    • max_elements （默认值256） analyze查找每一列不同值时所需关注的最大不同值的数量.
      analyze还用这个值来检查优化的数据类型是否该是ENUM,如果该列的不同值的数量超过了max_elements值ENUM就不做为建议优化的数据类型。
      max_memory （默认值8192） analyze查找每一列所有不同值时可能分配的最大的内存数量
  7. 尽可能使用NOT NULL
    • Mysql官网文档：

      NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.

  8. 了解无缓存查询
    • mysql_unbuffered_query()
    • 详细可查看：http://www.w3school.com.cn/php/func_mysql_unbuffered_query.asp
  9. 将IP地址存储为UNSIGNED INT
    • 可以使用 INET_ATON() 来把一个字符串IP转成一个整形，并使用INET_NTOA() 把一个整形转成一个字符串IP。在PHP中，也有这样的函数 ip2long()和long2ip()
  10. 拆分大的 DELETE 及 INSERT 语句
  11. 使用对象关系映射器ORM（Object Relational Mapper）
  12. 反范式设计：
    • 不必强制满足第三范式，尽量少使用外键
    • 外键用来保护参照完整性，可在业务端实现
    • 适度的冗余设计，减少多表join查询，更适应MPP架构的横向扩展能力
    • 直接基于I/O和查询进行优化
  13. 充分考虑业务逻辑和数据分离，数据库只作为一个保证ACID特性的关系数据的持久化存储系统，尽量避免使用自定义函数、存储过程、触发器和视图

避免使用：

ORDER BY RAND()

SELECT *

常用优化技巧：

  • 查看实例long_query和long_query_rate指标，分析慢查询出现频率及规律
  • 使用慢查询分析工具，从出现频率最高、耗时最长的SQL语句开始分析，通过优化SQL语句，添加索引等方式解决
  • 查看实例mem_hit_rate和mem_available指标，分析innodb的缓存池是否足够，内存是否是瓶颈
  • 查看cpu_usage_rate，结合慢查询分析CPU消耗是否合理，CPU是否是瓶颈
  • 调整innodb_page_size参数，对比性能测试，找到最合适的配置
  • 对常用语句使用EXPLAIN进行查询分析，找出潜在的设计问题
  • 根据业务场景设计合适的用例对不同规格实例进行性能测试