MySQL性能优化

基本原则

想要发挥 MySQL 的最佳性能，需要遵循 3 个基本使用原则。

  1. 让MySQL回归存储的基本职能：MySQL 数据库只用于数据的存储，不进行数据的复杂计算，不承载业务逻辑，确保存储和计算分离；
  2. 查询数据时，尽量单表查询，减少跨库查询和多表关联；
  3. 杜绝大事务、大 SQL、大批量、大字段等一系列性能杀手。
    • 大事务：运行步骤较多，涉及的表和字段较多，容易造成资源的争抢，甚至形成死锁。一旦事务回滚，会导致资源占用时间过长。
    • 大 SQL：复杂的SQL意味着过多的表的关联，MySQL 数据库处理关联超过3张表以上的SQL时，占用资源多，性能低下。
    • 大批量：多条SQL一次性执行完成，可以减少一条条执行SQL产生的额外开销，但必须确保进行充分的测试，并且在业务低峰时段或者非业务时段执行。
    • 大字段：blob、text类型的大字段要尽量少用，必须要用时，尽量与主业务表分离，减少对这类字段的检索和更新。

建库建表

  1. 必须指定默认存储引擎为 InnoDB，并且禁用 MyISAM 存储引擎，随着 MySQL 8.0 版本的发布，所有的数据字典表都已经转换成了 InnoDB，MyISAM 存储引擎已成为了历史。
  2. 默认字符集 UTF8mb4，以前版本的 UTF8 是 UTF8mb3，未包含个别特殊字符，新版本的 UTF8mb4 包含所有字符，官方强烈建议使用此字符集。
  3. 关闭区分大小写功能。设置参数lower_case_table_names的值为1，即可关闭区分大小写功能，即大写字母 T 和小写字母 t 一样。
  4. 存储过程、触发器、视图、event等功能尽量在程序中实现，一方面是为了存储和计算分离，另一方面是因为这些功能非常不完整，调试、排错、监控都非常困难，相关数据字典也不完善，存在潜在的风险。一般在生产数据库中，禁止使用。
  5. 单个数据库实例表数量控制在2000个以内。

InnoDB表的注意事项

  1. 主键列使用unsigned整数，可以使用auto_increment，但是要禁止手动更新主键。
  2. 每个列都必须添加comment注释。
  3. 在建表时必须显示指定engine。
  4. 表必备三字段：xxx_id、 xxx_create、 xxx_modified。其中xxx_id为主键，类型unsigned整数类型（例如：int unsigned）；xxx_create、xxx_modified的类型均为datetime类型，分别记录该条数据的创建时间、修改时间。
  5. 所有字段必须指定not null，为空值指定default值，因为MySQL难以优化null值，含null值的复合索引会失效，最终导致查询效率低。
  6. 单张表的字段数尽量空值在50个字段以内，如果字段过多可以考虑垂直拆分。
  7. 禁用enum和set类型，因为这样的类型兼容性不好且性能较差。
  8. 大文件不应该使用blob类型而是保存它们的路径，blob和text这样的类型会导致处理性能下降，全表扫描代价大大增加。
  9. 对货币等对精度敏感的数据，应该使用定点数（decimal）而不是浮点数（float）。
  10. 保存IP地址不要用char(15)，应该使用int unsigned，可以使用inet_aton和inet_ntoa函数实现整数和IP地址的转换。

使用索引

在前面《关系型数据库MySQL》一文中，我们已经讲到过索引的相关知识，这里我们做一个简单的回顾。

索引的设计原则

  1. 创建索引的列并不一定是select操作中要查询的列，最适合做索引的列是出现在where子句中经常用作筛选条件或连表子句中作为表连接条件的列。
  2. 具有唯一性的列，索引效果好；重复值较多的列，索引效果差。
  3. 如果为字符串类型创建索引，最好指定一个前缀长度，创建短索引。短索引可以减少磁盘I/O而且在做比较时性能也更好，更重要的是MySQL底层的高速索引缓存能够缓存更多的键值。
  4. 创建一个包含N列的复合索引（多列索引）时，相当于是创建了N个索引，此时应该利用最左前缀进行匹配。
  5. 不要过度使用索引。索引并不是越多越好，索引需要占用额外的存储空间而且会影响写操作的性能（插入、删除、更新数据时索引也需要更新）。MySQL在生成执行计划时，要考虑各个索引的使用，这个也是需要耗费时间的。
  6. 要注意可能使索引失效的场景，例如：模糊查询使用了前置通配符、使用负向条件进行查询等。

使用过程

过程，通常也称之为存储过程，它是事先编译好存储在数据库中的一组SQL的集合。调用存储过程可以简化应用程序开发人员的工作，减少与数据库服务器之间的通信，对于提升数据操作的性能是有帮助的，这些我们在之前的《关系型数据库MySQL》一文中已经提到过。

数据分区

MySQL支持做数据分区，通过分区可以存储更多的数据、优化查询，获得更大的吞吐量并快速删除过期的数据。关于这个知识点建议大家看看MySQL的官方文档。数据分区有以下几种类型：

  1. RANGE分区：基于连续区间范围，把数据分配到不同的分区。

    1. CREATE TABLE tb_emp (
    2.     eno INT NOT NULL,
    3.     ename VARCHAR(20) NOT NULL,
    4.     job VARCHAR(10) NOT NULL,
    5.     hiredate DATE NOT NULL,
    6.     dno INT NOT NULL
    7. )
    8. PARTITION BY RANGE( YEAR(hiredate) ) (
    9.     PARTITION p0 VALUES LESS THAN (1960),
    10.     PARTITION p1 VALUES LESS THAN (1970),
    11.     PARTITION p2 VALUES LESS THAN (1980),
    12.     PARTITION p3 VALUES LESS THAN (1990),
    13.     PARTITION p4 VALUES LESS THAN MAXVALUE
    14. );

  2. LIST分区：基于枚举值的范围，把数据分配到不同的分区。

  3. HASH分区 / KEY分区：基于分区个数，把数据分配到不同的分区。

    1. CREATE TABLE tb_emp (
    2.     eno INT NOT NULL,
    3.     ename VARCHAR(20) NOT NULL,
    4.     job VARCHAR(10) NOT NULL,
    5.     hiredate DATE NOT NULL,
    6.     dno INT NOT NULL
    7. )
    8. PARTITION BY HASH(dno)
    9. PARTITIONS 4;

SQL优化

  1. 定位低效率的SQL语句 - 慢查询日志。

    • 查看慢查询日志相关配置

      1.  mysql> show variables like 'slow_query%';
      2.  +---------------------------+----------------------------------+
      3.  | Variable_name             | Value                            |
      4.  +---------------------------+----------------------------------+
      5.  | slow_query_log            | OFF                              |
      6.  | slow_query_log_file       | /mysql/data/localhost-slow.log   |
      7.  +---------------------------+----------------------------------+
      9.  mysql> show variables like 'long_query_time';
      10.  +-----------------+-----------+
      11.  | Variable_name   | Value     |
      12.  +-----------------+-----------+
      13.  | long_query_time | 10.000000 |
      14.  +-----------------+-----------+

    • 创建慢查询日志文件并修改所有者。

      1.  touch /var/log/mysqld-slow.log
      2.  chown mysql /var/log/mysqld-slow.log

    • 修改全局慢查询日志配置。

      1.  mysql> set global slow_query_log_file='/var/log/mysqld-slow.log'
      2.  mysql> set global slow_query_log='ON'; 
      3.  mysql> set global long_query_time=1;

    • 或者直接修改MySQL配置文件启用慢查询日志。

      1.  [mysqld]
      2.  slow_query_log=ON
      3.  slow_query_log_file=/var/log/mysqld-slow.log
      4.  long_query_time=1

    注意：修改了配置文件需要重启MySQL，CentOS上对应的命令是systemctl restart mysqld。

  2. 通过explain了解SQL的执行计划。例如：

    1. explain select ename, job, sal from tb_emp where dno=20\G
    2. *************************** 1. row ***************************
    3.            id: 1
    4.   select_type: SIMPLE
    5.         table: tb_emp
    6.          type: ref
    7. possible_keys: fk_emp_dno
    8.           key: fk_emp_dno
    9.       key_len: 5
    10.           ref: const
    11.          rows: 7
    12.         Extra: NULL
    13. 1 row in set (0.00 sec)
    • select_type：查询类型（SIMPLE - 简单查询、PRIMARY - 主查询、UNION - 并集、SUBQUERY - 子查询）。
    • table：输出结果集的表。
    • type：访问类型（ALL - 全表查询性能最差、index、range、ref、eq_ref、const、NULL）。
    • possible_keys：查询时可能用到的索引。
    • key：实际使用的索引。
    • key_len：索引字段的长度。
    • rows：扫描的行数，行数越少肯定性能越好。
    • extra：额外信息。

  3. 通过show profiles和show profile for query分析SQL。

    MySQL从5.0.37开始支持剖面系统来帮助用户了解SQL执行性能的细节，可以通过下面的方式来查看MySQL是否支持和开启了剖面系统。

    1. select @@have_profiling;
    2. select @@profiling;

    如果没有开启剖面系统，可以通过下面的SQL来打开它。

    1. set profiling=1;

    接下来就可以通过剖面系统来了解SQL的执行性能，例如：

    1. mysql> select count(*) from tb_emp;
    2. +----------+
    3. | count(*) |
    4. +----------+
    5. |       14 |
    6. +----------+
    7. 1 row in set (0.00 sec)
    9. mysql> show profiles;
    10. +----------+------------+-----------------------------+
    11. | Query_ID | Duration   | Query                       |
    12. +----------+------------+-----------------------------+
    13. |        1 | 0.00029600 | select count(*) from tb_emp |
    14. +----------+------------+-----------------------------+
    15. 1 row in set, 1 warning (0.00 sec)
    17. mysql> show profile for query 1;
    18. +----------------------+----------+
    19. | Status               | Duration |
    20. +----------------------+----------+
    21. | starting             | 0.000076 |
    22. | checking permissions | 0.000007 |
    23. | Opening tables       | 0.000016 |
    24. | init                 | 0.000013 |
    25. | System lock          | 0.000007 |
    26. | optimizing           | 0.000005 |
    27. | statistics           | 0.000012 |
    28. | preparing            | 0.000010 |
    29. | executing            | 0.000003 |
    30. | Sending data         | 0.000070 |
    31. | end                  | 0.000012 |
    32. | query end            | 0.000008 |
    33. | closing tables       | 0.000012 |
    34. | freeing items        | 0.000032 |
    35. | cleaning up          | 0.000013 |
    36. +----------------------+----------+
    37. 15 rows in set, 1 warning (0.00 sec)

  4. 优化CRUD操作。

    • 优化insert语句

      • 在insert语句后面跟上多组值进行插入在性能上优于分开insert。
      • 如果有多个连接向同一个表插入数据，使用insert delayed可以获得更好的性能。
      • 如果要从一个文本文件装载数据到表时，使用load data infile比insert性能好得多。

    • 优化order by语句

      • 如果where子句的条件和order by子句的条件相同，而且排序的顺序与索引的顺序相同，如果还同时满足排序字段都是升序或者降序，那么只靠索引就能完成排序。

    • 优化group by语句

      • 在使用group by子句分组时，如果希望避免排序带来的开销，可以用order by null禁用排序。

    • 优化嵌套查询

      • MySQL从4.1开始支持嵌套查询（子查询），这使得可以将一个查询的结果当做另一个查询的一部分来使用。在某些情况下，子查询可以被更有效率的连接查询取代，因为在连接查询时MySQL不需要在内存中创建临时表来完成这个逻辑上需要多个步骤才能完成的查询。

    • 优化or条件

      • 如果条件之间是or关系，则只有在所有条件都用到索引的情况下索引才会生效。

    • 优化分页查询

      • 分页查询时，一个比较头疼的事情是如同limit 1000, 20，此时MySQL已经排序出前1020条记录但是仅仅返回第1001到1020条记录，前1000条实际都用不上，查询和排序的代价非常高。一种常见的优化思路是在索引上完成排序和分页的操作，然后根据返回的结果做表连接操作来得到最终的结果，这样可以避免出现全表查询，也避免了外部排序。

        1. select * from tb_emp order by ename limit 10000, 20;
        2. select * from tb_emp t1 inner join (select eno from tb_emp order by ename limit 10000, 20) t2 on t1.eno=t2.eno;

        上面的代码中，第2行SQL是优于第1行SQL的，当然我们的前提是已经在ename字段上创建了索引。

    • 使用SQL提示

      • USE INDEX：建议MySQL使用指定的索引。
      • IGNORE INDEX：建议MySQL忽略掉指定的索引。
      • FORCE INDEX：强制MySQL使用指定的索引。

配置优化

可以使用下面的命令来查看MySQL服务器配置参数的默认值。

  1. show variables;
  2. show variables like 'key_%';
  3. show variables like '%cache%';
  4. show variables like 'innodb_buffer_pool_size';

通过下面的命令可以了解MySQL服务器运行状态值。

  1. show status;
  2. show status like 'com_%';
  3. show status like 'innodb_%';
  4. show status like 'connections';
  5. show status like 'slow_queries';
  1. 调整max_connections：MySQL最大连接数量，默认151。在Linux系统上，如果内存足够且不考虑用户等待响应时间这些问题，MySQL理论上可以支持到万级连接，但是通常情况下，这个值建议控制在1000以内。
  2. 调整back_log：TCP连接的积压请求队列大小，通常是max_connections的五分之一，最大不能超过900。
  3. 调整table_open_cache：这个值应该设置为max_connections的N倍，其中N代表每个连接在查询时打开的表的最大个数。
  4. 调整innodb_lock_wait_timeout：该参数可以控制InnoDB事务等待行锁的时间，默认值是50ms，对于反馈响应要求较高的应用，可以将这个值调小避免事务长时间挂起；对于后台任务，可以将这个值调大来避免发生大的回滚操作。
  5. 调整innodb_buffer_pool_size：InnoDB数据和索引的内存缓冲区大小，以字节为单位，这个值设置得越高，访问表数据需要进行的磁盘I/O操作就越少，如果可能甚至可以将该值设置为物理内存大小的80%。

架构优化

  1. 通过拆分提高表的访问效率。

    • 垂直拆分
    • 水平拆分

  2. 逆范式理论。数据表设计的规范程度称之为范式（Normal Form），要提升表的规范程度通常需要将大表拆分为更小的表，范式级别越高数据冗余越小，而且在插入、删除、更新数据时出问题的可能性会大幅度降低，但是节省了空间就意味着查询数据时可能花费更多的时间，原来的单表查询可能会变成连表查询。为此，项目实践中我们通常会进行逆范式操作，故意降低范式级别增加冗余来减少查询的时间开销。

    • 1NF：列不能再拆分
    • 2NF：所有的属性都依赖于主键
    • 3NF：所有的属性都直接依赖于主键（消除传递依赖）
    • BCNF：消除非平凡多值依赖

  3. 使用中间表提高统计查询速度。

    使用insert into 中间表 select ... where ...这样的语句先将需要的数据筛选出来放到中间表中，然后再对中间表进行统计，避免不必要的运算和处理。

  4. 主从复制和读写分离，具体内容请参考《项目部署上线和性能调优》。

  5. 配置MySQL集群。

说明：本章内容参考了网易出品的《深入浅出MySQL》一书，该书和《高性能MySQL》一样，都对MySQL进行了深入细致的讲解，虽然总体感觉后者更加高屋建瓴，但是前者也算得上是提升MySQL技能的佳作（作者的文字功底稍显粗糙，深度也不及后者），建议有兴趣的读者可以阅读这两本书。