MySQL优化思路-后端

数据库
2123 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 % 1000 分1000个表

  8. 多字段表垂直分拆

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

  9. 不适合建索引的情况

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

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

  11. 不推荐使用外键 - Foreign Key

  • MySQL外键的实现比较简单和粗糙，性能不佳
  • 引入外键后，不方便进行分表

  1. 尽量把数字定义成unsigned

  2. 尽量不使用ENUM

InnoDB lock

InnoDB utilizes indexes for locking records, so locking an existing record seems easy–simply lock the index for that record.

死锁

并发insert导致死锁

If the a record doesn’t exist, and let’s say two requests come at the same time.

Both SELECT ... FOR UPDATE will get gap locks instead of X locks and gap locks only prevent no more records can be inserted in this gap.

**Why X lock -> gap lock? **

If the record exists, DB will Xlock the index(row). Happy case. If the record not exist, cannot lock an index for a record that doesn’t exist.

If you are using the default isolation level of REPEATABLE READ, InnoDB will also utilize gap locks. As long as you know the id (or even range of ids) to lock, then InnoDB can lock the gap so no other record can be inserted in that gap until we’re done with it.

However, if we have two gap locks on the same gap what will happen? It will result in deadlock and make one transaction rollback.

循环等待

  • process M, lock row X, and try to lock row Y
  • process N, lock row Y, and try to lock row X

其他优化

  1. 重启MySQL时，记得预热数据库，确保将数据加载到内存，提高查询效率
  2. 使用Redis等分布式缓存或本地缓存，减少对数据库的访问
  3. 考虑持久连接或连接池，而不是多次建立连接，以减少资源的消耗
  4. 进行分表时，可以考虑双写，以平稳过渡，防止数据丢失
本文由 络壳 原创或整理，转载请注明出处