老叶观点:MySQL开发规范之我见(更新版)

大多数MySQL规范在网上也都能找得到相关的分享,在这里要分享的是老叶个人认为比较重要的,或者容易被忽视的,以及容易被混淆的一些地方。

1、默认使用InnoDB引擎

【老叶观点】已多次呼吁过了,InnoDB适用于几乎99%的MySQL应用场景,而且在MySQL 5.7的系统表都改成InnoDB了,还有什么理由再死守MyISAM呢。

此外,频繁读写的InnoDB表,一定要使用具有自增/顺序特征的整型作为显式主键。

当然了,也不是说MyISAM就一无是处,比如老叶之前就把MyISAM用于临时导数据数据(把数据导入MyISAM,一番处理后再入到InnoDB表)、或者一些特殊的数据统计类场景用MyISAM(大数据量下MyISAM全表顺序读取比InnoDB有明显优势)可能比较合适。前提是,你得非常清楚MyISAM引擎的优势在哪里。

[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键

2、InnoDB表行记录物理长度不超过8KB

【老叶观点】InnoDB的data page默认是16KB,基于B+Tree的特点,一个data page中需要至少存储2条记录。因此,当实际存储长度超过8KB(尤其是TEXT/BLOB列)的大列(large column)时会引起“page-overflow存储”,类似ORACLE中的“行迁移”。

因此,如果必须使用大列(尤其是TEXT/BLOB类型)且读写频繁的话,则最好把这些列拆分到子表中,不要和主表放在一起存储。如果不太频繁,可以考虑继续保留在主表中。

当然了,如果将 innodb_page_size 选项修改成 8KB,那么行记录物理长度建议不超过4KB。

[MySQL优化案例]系列 — 优化InnoDB表BLOB列的存储效率

3、是否使用表分区(partition)

【老叶观点】在一些使用表分区后明显可以提升性能或者运维便利性的场景下,还是建议使用表分区。

比如老叶就在zabbix的数据库采用TokuDB引擎的前提下,又根据时间维度使用了分区。这样的好处是保证zabbix日常应用不受到影响前提下,方便管理员例行删除过去数据,只需要删除相应分区即可,不需再执行一个非常慢的DELETE而影响整体性能。

当然了,使用表分区可能不利于物理扩展,比如大数据量下想要做哈希水平拆分,这个就见仁见智了,如果你的业务场景下使用表分区更有好处,就放心大胆的用吧。该进行拆分就用拆分方案,不要继续抱着表分区方案不放。

迁移Zabbix数据库到TokuDB

4、是否使用存储过程、触发器

【老叶观点】在一些合适的场景下,用存储过程、触发器也完全没问题。

我们以前就是利用存储完成游戏业务逻辑处理,性能上不是问题,而且一旦需求有变更,只需修改存储过程,变更代价很低。我们还利用触发器维护一个频繁更新的表,对这个表的所有变更都将部分字段同步更新到另一个表中(类似物化视图的变相实现),也不存在性能问题。

有同行认为存储过程和触发器的应用可能会导致发生锁等待、死锁时排查问题上的困惑。嗯,这个是有这个可能性,不过如果真发生时,根据当时现场记录的SQL反查对应的存储过程或触发器,应该也不是难事,只不过要求DBA对线上业务环境更要了然于胸了。

总的来说,不要把MySQL的存储过程和触发器视为洪水猛兽,用好的话,没有问题的,真遇到问题了再优化也不迟。另外,MySQL因为对视图的处理也不太理想也没有物化视图,因此视图能不用就尽量少用吧。

5、选择合适的类型

【老叶观点】除了常见的建议外,还有其他几个要点:

5.1、用INT UNSIGNED存储IPV4地址,用INET_ATON()、INET_NTOA()进行转换,基本上没必要使用CHAR(15)来存储。

5.2、枚举类型可以使用ENUM,ENUM的内部存储机制是采用TINYINT或SMALLINT(并非CHAR/VARCHAR),性能一点都不差,记住千万别用CHAR/VARCHAR 来存储枚举数据。

5.3、还个早前一直在传播的“常识性误导”,建议用TIMESTAMP取代DATETIME。其实从5.6开始,建议优先选择DATETIME存储日期时间,因为它的可用范围比TIMESTAMP更大,物理存储上仅比TIMESTAMP多1个字节,整体性能上的损失并不大。

5.4、所有字段定义中,默认都加上NOT NULL约束,除非必须为NULL(但我也想不出来什么场景下必须要在数据库中存储NULL值,可以用0来表示)。在对该字段进行COUNT()统计时,统计结果更准确(值为NULL的不会被COUNT统计进去),或者执行 WHERE column IS NULL 检索时,也可以快速返回结果。

5.5、杜绝直接 SELECT * 读取全部字段,当表中存在 TEXT/BLOB 大列的时候就会是灾难了。可能本来不需要读取这些列,但因为偷懒写成 SELECT * 导致内存buffer pool被这些“垃圾”数据把真正需要缓冲起来的热点数据给洗出去了。

相应地,在写INSERT时,也要写上相对应的字段列表。

要求在SQL中写清楚每个字段的重要意义还在于,当业务需要表DDL发生更新后,如果不写清楚字段,可能会导致旧业务代码不可用,这个就折腾大发了。

6、关于索引

【老叶观点】除了常见的建议外,还有几个要点:

6.1、超过20个长度的字符串列,最好创建前缀索引而非整列索引(例如:ALTER TABLE t1 ADD INDEX(user(20))),可以有效提高索引利用率,不过它的缺点是对这个列排序时用不到前缀索引。前缀索引的长度可以基于对该字段的统计得出,一般略大于平均长度一点就可以了。

6.2、定期用 pt-duplicate-key-checker 工具检查并删除冗余的索引。比如 index idx1(a, b) 索引已经涵盖了 index idx2(a),就可以删除 idx2 索引了。

6.3、有多字段联合索引时,WHERE中过滤条件的字段顺序无需和索引一致,但如果有排序、分组则就必须一致了。

比如有联合索引 idx1(a, b, c),那么下面的SQL都可以完整用到索引:

SELECT ... WHERE b = ? AND c = ? AND a = ?;  --注意到,WHERE中字段顺序并没有和索引字段顺序一致

SELECT ... WHERE b = ? AND a = ? AND c = ?;

SELECT ... WHERE a = ? AND b IN (?, ?) AND c = ?;

SELECT ... WHERE a = ? AND b = ? ORDER BY c;

SELECT ... WHERE a = ? AND b IN (?, ?) ORDER BY c;

SELECT ... WHERE a = ? ORDER BY b, c;

SELECT ... ORDER BY a, b, c;  -- 可利用联合索引完成排序

而下面几个SQL则只能用到部分索引,或者可利用到ICP特性:

SELECT ... WHERE b = ? AND a = ?; -- 只能用到 (a, b) 部分

SELECT ... WHERE a IN (?, ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE (a BETWEEN ? AND ?) AND b = ?; -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND b IN (?, ?); -- EXPLAIN显示只用到 (a, b) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND (b BETWEEN ? AND ?) AND c = ?; -- EXPLAIN显示用到 (a, b, c) 整个索引,同时有ICP

SELECT ... WHERE a = ? AND c = ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

SELECT ... WHERE a = ? AND c >= ?; -- EXPLAIN显示只用到 (a) 部分索引,同时有ICP

ICP(index condition pushdown)是MySQL 5.6的新特性,其机制会让索引的其他部分也参与过滤,减少引擎层和server层之间的数据传输和回表请求,通常情况下可大幅提升查询效率。

下面的几个SQL完全用不到该索引:

SELECT ... WHERE b = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... WHERE b = ? AND c = ?;

SELECT ... ORDER BY b;

SELECT ... ORDER BY b, a;

从上面的几个例子就能看的出来,以往强调的WHERE条件字段顺序要和索引顺序一致才能使用索引的 “常识性误导” 无需严格遵守。

此外,有些时候查询优化器指定的索引或执行计划可能并不是最优的,可以手工指定最优索引,或者修改session级的 optimizer_switch 选项,关闭某些导致效果反而更差的特性(比如index merge通常是好事,但也遇到过用上index merge后反而更差的,这时候要么强制指定其中一个索引,要么可以临时关闭 index merge 特性)。

7、其他

7.1、哪怕是基于索引的条件过滤,如果优化器意识到总共需要扫描的数据量超过30%时(ORACLE里貌似是20%,MySQL目前是30%,没准以后会调整),就会直接改变执行计划为全表扫描,不再使用索引。

7.2、多表JOIN时,要把过滤性最大(不一定是数据量最小哦,而是只加了WHERE条件后过滤性最大的那个)的表选为驱动表。此外,如果JOIN之后有排序,排序字段一定要属于驱动表,才能利用驱动表上的索引完成排序。

7.3、绝大多数情况下,排序的大家通常要来的更高,因此如果看到执行计划中有 Using filesort,优先创建排序索引吧。

7.4、利用 pt-query-digest 定期分析slow query log,并结合 Box Anemometer 构建slow query log分析及优化系统。

[MySQL FAQ]系列 — EXPLAIN结果中哪些信息要引起关注

备注:若无特别说明,以上规范建议适用于MySQL 5.6及之前的版本(并且主要是5.6版本,尤其是ICP特性、DATETIME变化这两个地方)。5.7及之后的版本可能会有些变化,个别规范建议需要相应调整。

延伸阅读:

MySQL 5.7怎么爬出临时表空间的坑

导读

如何确认临时表是由哪个线程创建的?

上次我们介绍了MySQL 5.7临时表空间特性及使用注意事项,这次我们来介绍如何确认是哪个线程创建的临时表,以及如何释放临时表。

首先,我们查看当前的线程ID

yejr@imysql.com [test]>SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|            2470 |
+-----------------+

在当前会话中创建临时表

yejr@imysql.com [test]>create temporary table tmp1 select * from information_schema.global_status;

立即查看临时表信息

yejr@imysql.com [test]>select * from information_schema.innodb_temp_table_info;
+----------+-----------------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME                  | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+-----------------------+--------+-------+----------------------+---------------+
|      505 | #sql17ab5_4000003a6_4 |      5 |   512 | FALSE                | FALSE         |
+----------+-----------------------+--------+-------+----------------------+---------------+

我们观察到 NAME 列的值是 #sql17ab5_4000003a6_4,它由3部分构成:

  1. 第1部分,由“#sql”字符串开始,并加上随机值;
  2. 第2部分,一串”疑似”16进制字符;
  3. 第3部分,单调递增的数值;

第2部分,我们注意到是“疑似”16进制,我们把“3a6“从16进制转成10进制试试看:

yejr@imysql.com [test]>select conv('9a6', 16, 10);
+---------------------+
| conv('9a6', 16, 10) |
+---------------------+
| 2470                |
+---------------------+

可以看到,正好和当前的线程ID是一样的,这证实了我们的设想。

我手上有两个MySQL 5.7版本,下面是多次、不定时创建临时表的整个观察过程记录。
首先是Linux系统下的5.7.18版本:

Server version:    5.7.18-log MySQL Community Server (GPL)

yejr@imysql.com[test]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|            1737 |
+-----------------+

yejr@imysql.com[test]> select conv(1737, 10 ,16);
+--------------------+
| conv(1737, 10 ,16) |
+--------------------+
| 6C9                |
+--------------------+

yejr@imysql.com[test]> select * from information_schema.innodb_temp_table_info where NAME like ‘%6C9%’;;
+----------+----------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME           | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+----------------+--------+-------+----------------------+---------------+
|      121 | #sql7e95_6c9_5 |      5 |   190 | FALSE                | FALSE         |
|      120 | #sql7e95_6c9_4 |      5 |   190 | FALSE                | FALSE         |
|      119 | #sql7e95_6c9_3 |      5 |   190 | FALSE                | FALSE         |
|      118 | #sql7e95_6c9_2 |      5 |   190 | FALSE                | FALSE         |
+----------+----------------+--------+-------+----------------------+---------------+

以及Mac系统下的MySQL 5.7.16版本:

Server version:    5.7.16-log MySQL Community Server (GPL)


yejr@imysql.com[test]> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|            934  |
+-----------------+

yejr@imysql.com[test]> select conv(934, 10 ,16);
+--------------------+
| conv(1737, 10 ,16) |
+--------------------+
| 3A6                |
+--------------------+

yejr@imysql.com[test]> select * from information_schema.innodb_temp_table_info where NAME like ‘%3A6%’;;
+----------+-------------------------+--------+-------+----------------------+---------------+
| TABLE_ID | NAME                    | N_COLS | SPACE | PER_TABLE_TABLESPACE | IS_COMPRESSED |
+----------+-------------------------+--------+-------+----------------------+---------------+
|      518 | #sql17ab5_31000003a6_31 |      5 |   512 | FALSE                | FALSE         |
|      517 | #sql17ab5_29000003a6_29 |     20 |   512 | FALSE                | FALSE         |
|      516 | #sql17ab5_26000003a6_26 |      5 |   512 | FALSE                | FALSE         |
|      515 | #sql17ab5_23000003a6_23 |      5 |   512 | FALSE                | FALSE         |
|      514 | #sql17ab5_1e000003a6_1e |      5 |   512 | FALSE                | FALSE         |
|      513 | #sql17ab5_1b000003a6_1b |      5 |   512 | FALSE                | FALSE         |
|      512 | #sql17ab5_18000003a6_18 |      5 |   512 | FALSE                | FALSE         |
|      511 | #sql17ab5_16000003a6_16 |      5 |   512 | FALSE                | FALSE         |
|      510 | #sql17ab5_14000003a6_14 |      5 |   512 | FALSE                | FALSE         |
|      509 | #sql17ab5_12000003a6_12 |      5 |   512 | FALSE                | FALSE         |
|      508 | #sql17ab5_10000003a6_10 |      5 |   512 | FALSE                | FALSE         |
|      507 | #sql17ab5_d000003a6_d   |      5 |   512 | FALSE                | FALSE         |
|      506 | #sql17ab5_a000003a6_a   |      5 |   512 | FALSE                | FALSE         |
|      505 | #sql17ab5_4000003a6_4   |      5 |   512 | FALSE                | FALSE         |
+----------+-------------------------+--------+-------+----------------------+---------------+

从这个结果能看到临时表的 NAME 的第三部分数值在两个版本中的表现不一样。

• 在5.7.16版本上,虽然也是单调递增,但并不是顺序的,而是有跳跃,跳跃规则未知;
• 在5.7.18版本上,在保持单调递增的基础上,每次值都是顺序增长的,未跳跃。

好了,现在我们知道只要根据当前的线程ID,就能找到该线程ID里所创建的临时表,想要释放这些临时表,只需要查询 I_S.INNODB_TEMP_TABLE_INFO 表的 NAME 列值所有包含当前线程ID的记录,杀掉对应的线程ID即可。

参考

MySQL 5.7临时表空间怎么玩才能不掉坑里

导读

MySQL 5.7起支持独立临时表空间,但个别时候也可能会踩坑的。

MySQL 5.7起,开始采用独立的临时表空间(和独立的undo表空间不是一回事哟),命名ibtmp1文件,初始化12M,且默认无上限。

选项 innodb_temp_data_file_path 可配置临时表空间相关参数。

innodb_temp_data_file_path = ibtmp1:12M:autoextend

临时表空间的几点说明

• 临时表空间不像普通InnoDB表空间那样,不支持裸设备(raw device)。
• 临时表空间使用动态的表空间ID,因此每次重启时都会变化(每次重启时,都会重新初始化临时表空间文件)。
• 当选项设置错误或其他原因(权限不足等原因)无法创建临时表空间时,mysqld实例也无法启动。
• 临时表空间中存储这非压缩的InnoDB临时表,如果是压缩的InnoDB临时表,则需要单独存储在各自的表空间文件中,文件存放在 tmpdir(/tmp)目录下。
• 临时表元数据存储在 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO 视图中。

有时执行SQL请求时会产生临时表,极端情况下,可能导致临时表空间文件暴涨,帮人处理过的案例中最高涨到快300G,比以前遇到的 ibdata1 文件暴涨还要猛…

临时表使用的几点建议

• 设置 innodb_temp_data_file_path 选项,设定文件最大上限,超过上限时,需要生成临时表的SQL无法被执行(一般这种SQL效率也比较低,可借此机会进行优化)。
• 检查 information_schema.INNODB_TEMP_TABLE_INFO,找到最大的临时表对应的线程,kill之即可释放,但 ibtmp1 文件则不能释放(除非重启)。
• 择机重启实例,释放 ibtmp1 文件,和 ibdata1 不同,ibtmp1 重启时会被重新初始化而 ibdata1 则不可以。
• 定期检查运行时长超过N秒(比如N=300)的SQL,考虑干掉,避免垃圾SQL长时间运行影响业务。

附:临时表测试案例

原表DDL

CREATE TEMPORARY TABLE `tmp1` (
  `id` int(10) unsigned NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL DEFAULT '',
  `aid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `nid` int(11) unsigned GENERATED ALWAYS AS ((`id` + 1)) VIRTUAL NOT NULL,
  `nnid` int(11) unsigned GENERATED ALWAYS AS ((`id` + 1)) STORED NOT NULL,
  PRIMARY KEY (`aid`),
  KEY `name` (`name`),
  KEY `id` (`id`),
  KEY `nid` (`nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

原表大小只有 120MB,从这个表直接 INSERT…SELECT 导数据到tmp1表。

-rw-r-----  1 yejr  imysql   120M Apr 14 10:52 /data/mysql/test/sid.ibd

生成临时表(去掉虚拟列,临时表不支持虚拟列,然后写入数据),还更大了(我也不解,以后有机会再追查原因)。

-rw-r-----  1 yejr  imysql   140M Jun 25 09:55 /Users/yejinrong/mydata/ibtmp1

查看临时表元数据信息

yejr@imysql.com [test]>select * from 
 INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G
*********************** 1. row ***********************
            TABLE_ID: 405
                NAME: #sql14032_300000005_3
              N_COLS: 6
               SPACE: 421
PER_TABLE_TABLESPACE: FALSE
       IS_COMPRESSED: FALSE

再删除索引,结果,又更大了

-rw-r-----  1 yejr  imysql   204M Jun 25 09:57 /data/mysql/ibtmp1

第二次测试删除索引后,变成了200M(因为第二次测试时,我设置了临时表最大200M)

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:200M


-rw-r-----  1 yejr  imysql   200M Jun 25 10:15 /data/mysql/ibtmp1

执行一个会产生临时表的慢SQL

注:MySQL 5.7起,执行UNION ALL不再产生临时表(除非需要额外排序)。

yejr@imysql.com [test]>explain select * from tmp1 union 
  select id,name,aid from sid\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: tmp1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3986232
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 2
  select_type: UNION
        table: sid
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 802682
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Using temporary

文件涨到588M还没结束,我直接给卡了

-rw-r-----  1 yejr  imysql   588M Jun 25 10:07 /data/mysql/ibtmp1

第二次测试时,设置了临时表空间文件最大200M,再执行会报错:

yejr@imysql.com [test]>select * from tmp1 union 
 select id,name,aid from sid;
ERROR 1114 (HY000): The table '/var/folders/bv/j4tjn6k54dj5jh1tl8yn6_y00000gn/T/#sql14032_5_8' is full

参考

[MySQL FAQ]系列 — 什么情况下会用到临时表

MySQL在以下几种情况会创建临时表:

1、UNION查询;
2、用到TEMPTABLE算法或者是UNION查询中的视图;
3、ORDER BY和GROUP BY的子句不一样时;
4、表连接中,ORDER BY的列不是驱动表中的;
5、DISTINCT查询并且加上ORDER BY时;
6、SQL中用到SQL_SMALL_RESULT选项时;
7、FROM中的子查询;
8、子查询或者semi-join时创建的表;

EXPLAIN 查看执行计划结果的 Extra 列中,如果包含 Using Temporary 就表示会用到临时表。

当然了,如果临时表中需要存储的数据量超过了上限( tmp-table-sizemax-heap-table-size 中取其小者),这时候就需要生成基于磁盘的临时表了。

在以下几种情况下,会创建磁盘临时表:

1、数据表中包含BLOB/TEXT列;
2、在 GROUP BY 或者 DSTINCT 的列中有超过 512字符 的字符类型列(或者超过 512字节的 二进制类型列,在5.6.15之前只管是否超过512字节);
3、在SELECT、UNION、UNION ALL查询中,存在最大长度超过512的列(对于字符串类型是512个字符,对于二进制类型则是512字节);
4、执行SHOW COLUMNS/FIELDS、DESCRIBE等SQL命令,因为它们的执行结果用到了BLOB列类型。

从5.7.5开始,新增一个系统选项 internal_tmp_disk_storage_engine 可定义磁盘临时表的引擎类型为 InnoDB,而在这以前,只能使用 MyISAM。而在5.6.3以后新增的系统选项 default_tmp_storage_engine 是控制 CREATE TEMPORARY TABLE 创建的临时表的引擎类型,在以前默认是MEMORY,不要把这二者混淆了。

见下例:

mysql> set default_tmp_storage_engine = "InnoDB";
-rw-rw----   1 mysql mysql  8558 Jul  7 15:22 #sql4b0e_10_0.frm -- InnoDB引擎的临时表
-rw-rw----   1 mysql mysql 98304 Jul  7 15:22 #sql4b0e_10_0.ibd
-rw-rw----   1 mysql mysql  8558 Jul  7 15:25 #sql4b0e_10_2.frm

mysql> set default_tmp_storage_engine = "MyISAM";
-rw-rw----   1 mysql mysql     0 Jul  7 15:25 #sql4b0e_10_2.MYD -- MyISAM引擎的临时表
-rw-rw----   1 mysql mysql  1024 Jul  7 15:25 #sql4b0e_10_2.MYI

mysql> set default_tmp_storage_engine = "MEMORY";
-rw-rw----   1 mysql mysql  8558 Jul  7 15:26 #sql4b0e_10_3.frm -- MEMORY引擎的临时表

 

延伸阅读:

[MySQL优化案例]系列 — 频繁创建临时表

无需过分关注Created_tmp_disk_tables

8.4.4 How MySQL Uses Internal Temporary Tables