MySQL优化思路-后端

数据库
1866 words

基本思路

**首先是如何定位性能瓶颈？**
一般使用show命令、慢查询日志、explain、profiling等进行分析。

  • 当服务器的负载增加时，使用SHOW PROCESSLIST来查看有问题的查询
  • 打印慢查询日志，找出执行慢的查询
  • EXPLAIN查看SELECT时索引命中的情况，分析查询语句和表结构

根据分析结果，优化索引和查询，优化表结构；这是后端小伙伴可以着手的部分。然后，还可以找DBA优化MySQL配置和硬件。

查询优化

  1. 为搜索字段建索引，合理使用 （第一条，因为它最关键）

    • 在 WHERE、GROUP BY 和 ORDER BY 的列上加上索引
    • 尽量保证索引简单，避免在同一列上加多个索引
    • 有时MySQL会选择错误的索引，可考虑使用USE INDEX
    • 查询字段存在索引中，考虑Cover Index

  2. 当只要一行数据时使用 LIMIT 1

  3. 避免 SELECT *

    • 从数据库里读出越多的数据，那么查询就会变得越慢
    • 数据多会增加网络传输的负载

  4. 利用MySQL的查询缓存

    • 使用像NOW()和RAND()或是其它类似的SQL函数，都不会开启查询缓存
    • 保持查询条件次序一致
      第一次查select col from tab where a = 'x' and b = 'y'
      下次查是select col from tab where b = 'y' and a = 'x'，就无法利用查询缓存了

  5. WHERE 子句

    • 避免对于null的判断，否则会导致全表扫描
    • 避免使用 != 或 <> 操作符，否则会造成后面的索引字段失效
    • 不使用 % 前缀模糊查询
      • 例如like “%name” 或者like “%name%”，这种查询会导致索引失效
      • 但是可以使用like “name%”
    • 避免进行表达式操作
      • select uid from user where age*2=36;会导致索引失效
    • 使用同类型进行比较，比如用 ‘123’ 和 ‘123’ 比，123 和 123 比

  6. LIMIT M,N节制使用

    • 随着表数据量的增加，使用LIMIT分页会越来越慢
    • 可以采用记录上次查询结果ID的方法实现翻页

  7. IN和OR

    • OR 改写成 IN：OR的效率是O(n)级别，IN 的效率是log(n)级别
    • IN包含的值不应过多，如果较多，产生的消耗也是比较大的，IN 的个数建议控制在 200 以内

  8. COUNT()

    • 采用InnoDB时避免在整个表上使用count(*)，它可能会导致整个表hang住，因为count操作太耗时了
    • InnoDB是没有保存rows count的，因为在不同transaction中，看到的行可能不一样
    • InnoDB通过扫描索引来计数，count全表时，需要遍历全部索引；当索引不再内存中时，还需要从磁盘读取，会更慢更消耗资源
    • 如果只需要一个大概的行数，可以用SHOW TABLE STATUS
    • MyISAM引擎不一样，维护了count值，SELECT COUNT(*)能马上返回

  9. JOIN

    • 尽可能避免复杂的join和子查询
    • 建议减少对大表的 join 查询，InnoDB 引擎会产生行锁；MyISAM 引擎会产生表锁，会导致其他写操作被阻塞
    • 尽量使用inner join，避免left join；没有其他过滤条件时，MySQL默认会自动选择小表作为驱动表

设计优化

  1. 每张表都设置一个ID做为其主键

    • 推荐使用UNSIGNED INT，设置上AUTO_INCREMENT标志

  2. 把IP地址存成 UNSIGNED INT

    • 用VARCHAR(15)字段的字符串来存IP不划算
    • 如果用整形来存放只需要4个字节，并且可以有定长的字段

  3. 固定长度的表会更快

    • 固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快
    • 有VARCHAR，TEXT，BLOB字字段；这个表就不是“固定长度静态表”了

  4. 越小的列会越快

    • 对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈
    • 数据变得紧凑会，在一个内存页可以读取更多行数据，可以减少了对硬盘的访问

  5. 尽可能的使用 NOT NULL

    • NULL无法利用索引，且需要额外的空间
    • NULL在业务逻辑层需要额外的处理

  6. 选择正确的存储引擎

    • OLTP业界用InnoDB比较多，InnoDB支持行锁
    • MyISAM使用Cluster Index, 不支持行锁，适合于一些需要大量查询的应用

  7. 大表水平分拆

    • 降低每个表的行数，加快查询
    • 比如可以按user_id%100分100个表

  8. 多字段表垂直分拆

    • 把数据库中的表按列变成几张表
    • 降低表的复杂度和字段的数目

  9. 不适合建索引的情况

    • 区分度不高的字段，不适合建索引；消耗资源，对性能提升不大
    • 乱序的字段不适合作为索引，如MD5, UUID；否则，在插入数据时，更新索引需在B+树中大量移动结点，导致较多硬盘IO
    • 更新非常频繁的字段不适合创建索引；原因同上

  10. 适度冗余，让Query尽量减少Join

  11. 不推荐使用外键Foreign Key

其他优化

  1. 重启MySQL时，记得预热数据库，确保将数据加载到内存，提高查询效率

  2. 使用Redis等分布式缓存或本地缓存，减少对数据库的访问

  3. 考虑持久连接或连接池，而不是多次建立连接，以减少资源的消耗

  4. 进行分表时，可以考虑双写，以平稳过渡，防止数据丢失

本文由 络壳 原创或整理，转载请注明出处