我在项目中对 MySQL 做的优化

3,259 阅读7分钟

原标题:我在 MySQL 上做了哪些优化
原文链接:zhoupq.com/我在-MySQL-上做…
转载请注明出处

  本文记录了我这一年的时间里是如何对项目中用到的 MySQL 进行优化。带有一定的主观性和局限性,请各位支持的同时,不吝赐教。

  

安装


  这是同事分享给我的。安装数据库也是一门学问,千万不要被安装的简单性而忽略一些细节。针对于 Win os 服务器而言,MySQL 的安装版可以选则三种不同的服务器类型:

  • Developer Machine(开发机器)
      为 MySQL 分配最少分系统资源
  • Server Machine(服务器)
      为 MySQL 分配一定比例的系统资源
  • Dedicated MySQL Server Machine(专用MySQL服务器)
      为 MySQL 分配所有的系统资源

  如果你跟我一样不幸,不仅选择了 Win os 做服务器系统,还选择了 Developer Machine(开发机器),兄弟抱一个,不要哭,重装。发生这些上述不幸的原因已经不重要,需要做的是必须切换成 Dedicated MySQL Server Machine(专用MySQL服务器)

  重装切换之后,你会发现,之前安装的一定是假的 MySQL。

主键


  主键或者唯一键可以用作某条记录的唯一标志符。主键生成有两种方式:
+ 自增
+ UUID

自增


  自增方式有个重要因素是“步长”,也就是则增的幅度,在单机模式下,一般步长为1。若是在分布式数据库系统下,步长设为节点的数量,这样一来,就可以避免主键重复的情况。建议预估好节点的数量,步长不可小于节点数。

UUID


  UUID 可以更有效地避免自增主键带来的烦恼,但是它也有不足之处:
+ UUID 过长,增大数据库总容量,降低性能
+ UUID 无序,插入数据时根据主键寻址费时

  针对上述 UUID 的缺点,推特开发了“雪花算法”,并开源。其中心思想是利用时间戳、数据中心码、机器码、序列号组成有规则的 UUID,使其有序降低性能消耗。

  我在项目中使用了“自增+步长2”,因为使用了主从,虽然不是分布式,但是双数据源也是两个节点,采用这种方式保险一些。

  更多内容请移步我的其他博文:数据库自增长主键与-UUID
  推荐 MySQL 使用自增ID主键和UUID 作为主键的优劣比较详细过程(从百万到千万表记录测试)

数据类型

长度


  我一直秉承杀鸡就用杀鸡刀,宰牛采用宰牛刀的原则。一个“tab_NAME” 的数据类型非要整一个 “VARCHAR(500)”,这是浪费,过多的长度分配会造成空间占用太多,最终造成性能下降。有同事说我杞人忧天,一个库才二十几张表,即使每个字段都设成500,也不过如此嘛。从短期的结果上来看,结果没有受到明显影响。但是别忘了,我们是来解决问题的,如果因为我们的操作违反了约定,造成严重后果,那么我们将背负罪过。“量身定做”的好处不言而喻,列的长度亦是如此。

NULL


  尽量不将列设为 NULL,从业务角度上看,NULL 是错误的,试想,既然是 NULL,哪有何必存在这个列呢?反之,既然存在这个列,那么 NULL 便失去了意义。让我设计表的时候,我都会给列设置一个初始值,“tab_UPDATETIME” 就设置为 “CURRENT_TIMESTAMP”,“tab_STATUS” 就设置为 “1”或者“0”。

  从开发维护的角度看,如果不确定列是否为 NULL,那么在 SQL 中,就必须加上 “AND tab_NAME != NULL AND tab_NAME != ''”,很容易被忽略,代码越多,出错的概率就越大。

索引


  好的索引是一颗仙丹,可以让迟缓的查询得到质的提升,否则,就是一碗毒药。

  索引我做了三点优化:

  • 勿滥用索引
  • 最左前缀索引
  • 前缀索引

勿滥用索引


  索引不是越多越好,因为生成索引需要时间,而且索引占表物理空间。表一大,查询速度多少会受影响。我亲眼看到同事建好表时候,无微不至地为每一个字段都建了索引,或者为每一个条件都建了索引,形如条件“a=1 and b =2 and c=3 and d=4”,为其建了“a”、“b”、“c”、“d”四个索引。浪费!低效!这种情况,应当使用“最左前缀索引”。

最左前缀索引


  在生成联合索引时会碰到最左前缀索引,什么是最左前缀索引呢?就是在联合索引中,从最左边的索引开始匹配,直到遇上“like”、“>”、“>=”、“<”、“<=”等范围匹配时停止,即使后面有“=”都不再匹配。

  简单举例:现有字段“a”、“b”、“c”、“d”组成的联合索引“abcd”,SQL 条件部分为:

  1. a=1 and b =2 and c=3 and d=4
  2. a=1 and b =2 and d=4 and c=3
  3. a=1 and b =2 and c>=3 and d=4

  1 用到索引为“abcd”,2 用到的索引为“abd”, (2 同 1)3 用到的索引为“abc”。条件的顺序很重要。跟自拍一样,脸大的站后面。
  
  抱歉,上述第二点同第一点,同样用到的索引为“abcd”。
  
  利用 EXPLAIN 工具分析:

// 建表语句略,已知创建了组合索引 (abcd) 
mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.c = 'e' AND t.d = 'f';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set

mysql> EXPLAIN SELECT * FROM test t WHERE t.a = 'q' AND t.b = 'w' AND t.d = 'f' AND t.c = 'e';
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref                     | rows | Extra                    |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
|  1 | SIMPLE      | t     | ref  | name          | name | 360     | const,const,const,const |    1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------------------------+------+--------------------------+
1 row in set

前缀索引


  前缀索引是针对某个字段而言,我们知道 MySQL 中,有一个全文索引,“BOLD”、“TEXT” 是不能建全文索引。想想也能理解,这么长的全文索引得占多少空间,这显然是不现实的。最好的办法是为其创建“左前缀索引”,只取字符串的前面一小段作为索引,具体取多少,决定于多长的字符可以尽可能多的确定唯一记录。“varchar”同样受用。

  更多内容请移步我的其他博文:MySQL 高性能索引之前缀索引

多表联合查询


  不管是 Heibernate 在代码中拼 SQL,还是 MyBatis 在 Mapper.xml 中写 SQL,由于数据库范式的规范,导致为完成某项查询,必须联合多表查询。一个 LEFT JOIN 很常见,三四个 LEFT JOIN 呢?

  多个 LEFT JOIN 肯定不行,即使有索引,也很容易造成全表扫描,为了减少该情况发生的概率,我一般会采取两种方法:

  • 反范式
  • 临时表

反范式

衡量一个 DBA 的水平有多高,得看他反范式能力有多强。
—— 知乎

  比如我要根据 A表 的日期,关联 B表,统计出每个日期下某个属性的数量。我可以在A表中添加一列,用来存储“数量”,虽然违反了范式,但是性能上得到了提升。我觉得这是一笔划算的买卖。

  规范化是为了技术服务,而技术是为业务服务。规范化也就是套路,能保证不出错,但是并不能解决特殊问题,特殊问题还需要特殊处理。

临时表


  当需要联合三张表以上时,轻微的反范式已经不适用了,推荐用临时表,或者物化视图,但是 MySQL 的物化视图实现起来比较困难。事实上,我用的就是临时表,将四张表的部分数据抽离出来,保存在一张临时表中,制定一个“计划”,每天凌晨会自动更新。

  • 好处
      加快查询速度
  • 缺点
    • 会在某一时刻(凌晨)数据库IO过高
    • 可能会出现异常,做好事务管理,让其回滚,重新执行,再有问题,就需要人工干预
    • 数据准确性会延迟一天,适合非敏感业务

  以上是仅针对数据库做的优化,至于缓存(一级缓存、二级缓存),那属于持久层框架的职责,不在此文记录范围之内。