MySQL优化思路-后端

基本思路

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

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

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

查询优化

  1. 为搜索字段建索引,合理使用 (第一条,因为它最关键)

    • WHEREGROUP BYORDER 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. INOR

    • 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. 进行分表时,可以考虑双写,以平稳过渡,防止数据丢失

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