MySQL优化的四大维度

MySQL优化不能完全依靠加索引，毕竟加索引也是有性能成本的，还得多考虑其它维度的优化。

我们可以从以下四个维度考虑：硬件升级、系统配置、表结构设计、SQL语句和索引。

从成本上来说：硬件升级>系统配置>表结构设计>SQL语句及索引，然而效果却是由低到高。所以我们在优化的时候还是尽量从SQL语句和索引开始入手。

1.硬件升级

硬件升级这里不在过多赘述，升级更好配置的机器、机械硬盘更换为SSD等等。

2.系统配置优化

2.1 调整buffer_pool

通过调整buffer_pool使数据尽量从内存中读取，最大限度的降低磁盘操作，这样可以提升性能。

  • 查看buffer_pool数据的方法：

    SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_page_%';

    img

    可以看出总页数34552032，空闲页数6835。

  • 查看buffer_pool大小

    // 查看buffer_pool大小,单位为M
SELECT @@innodb_buffer_pool_size/1024/1024

    innodb_buffer_pool_size默认为128M，理论上可以扩大到内存的3/4或4/5。我们修改mysql配置文件my.cnf,增加如下配置：

    # 我的机器配置为64G，这里改到48G内存。
innodb_buffer_pool_size = 49152M

  • 然后重启MySQL

2.2 数据预热

默认情况下，某条数据被读取过一次才会被缓存在innodb_buffer_pool里。所以数据库刚刚启动，可以进行一次数据预热，将磁盘上的数据缓存到内存中去。

预热脚本：

SELECT DISTINCT
 CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
  ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
  FROM
 (
    SELECT
     engine,table_schema db,table_name tb,
     index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index)
ndxcollist
    FROM
   (
      SELECT
       B.engine,A.table_schema,A.table_name,
       A.index_name,A.column_name,A.seq_in_index
      FROM
       information_schema.statistics A INNER JOIN
       (
          SELECT engine,table_schema,table_name
          FROM information_schema.tables WHERE
engine='InnoDB'
       ) B USING (table_schema,table_name)
      WHERE B.table_schema NOT IN ('information_schema','mysql')
      ORDER BY table_schema,table_name,index_name,seq_in_index
   ) A
    GROUP BY table_schema,table_name,index_name
 ) AA
ORDER BY db,tb;

将脚本保存为：loadtomem.sql

执行命令：

mysql -uroot -p -AN < /root/loadtomem.sql > /root/loadtomem.sql

在需要进行数据预热时就执行下面的命令：

mysql -uroot < /root/loadtomem.sql > /dev/null 2>&1

2.3 降低日志的磁盘落盘

  • 增大redolog，减少落盘次数，innodb_log_file_size设置为0.25 * innodb_buffer_pool_size

  • 通用查询日志、慢查询日志可以不开，bin-log要开，慢日志查询可以遇到性能问题再开

  • 写redolog策略 调整innodb_flush_log_at_trx_commit参数为0或2。当然涉及安全性非常高的系统（金融等）还是保持默认的就行。在配置文件里加上 innodb_flush_log_at_trx_commit =2 即可。

    SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit'

    3.表结构设计

    3.1 设计中间表

    设计中间表，一般针对于统计分析功能

    3.2 设计冗余字段

    为减少关联查询，创建合理的冗余字段

    3.3 拆表

    对于字段太多的大表，考虑拆表；对于表中经常不被使用的字段或存储数据比较多的字段，考虑拆表

    3.4 主键优化

    主键类型最好是int类型，建议自增主键（分布式系统下用雪花算法）

    3.5 字段的设计

    1. 字段的宽度设得尽可能的小。
    2. 尽量把字段设置为NOT NULL
    3. 对于某些文本字段，如省份、性别等，我们可以把他们定义为enum类型。在mysql里enum类型被当作数值类型数据来处理，而数值型数据处理起来比文本类型快得多。

    4.SQL语句和索引

    1. 学会用explain分析
    2. SQL语句中IN包含的值不应太多
      • MySQL对IN做了一些优化，将IN中的常量去不存在一个数组里，而且会进行排序。如果数值较多，这些步骤消耗也是比较大的。
    3. SELECT 语句务必指明字段名称 - SELECT * 增加了很多不必要的消耗（CPU、IO、内存、网络带宽）
    4. 当只需要一条数据时，使用limit
    5. 排序字段加索引
    6. 如果查询条件中其他字段没有索引，少用or - or两边的字段中，如果有一个不是索引字段，则会造成该查询都不会走索引的情况。
    7. 尽量用union all代替union
      • union和union all的区别是，union需要将结果集合并再进行唯一性过滤操作，这就会涉及到排序，增加了大量的CPU运算。当然，使用union all的前提条件是两个结果集没有重复数据。
    8. 区分in和exists、not in和not exists
      1. exists：以外表为驱动表，先被访问。适合外表小而内表大的情况
      2. in：先执行子查询。适合外表大而内表小的情况
    9. 不建议使用%前缀模糊查询，不会走索引
    10. 避免在where子句中对字段进行表达式或函数操作
    11. 避免隐式类型转换 如where age=’18’,如果确定是int类型，应写为where age = 18;
    12. 对于联合索引，要遵守最左前缀法则
    13. 必要时可以使用force index来强制查询使用某个索引
    14. 注意范围查询语句
      • 对于联合索引来说，如果存在范围查询，比如between，>，<等条件时，会造成后面的索引字段失效
    15. 使用JOIN优化 - LEFT JOIN里左边的表为驱动表，RIGHT JOIN里右边的表为驱动表，而INNER JOIN MySQL会自动找出数据少的表为驱动表