第03期：列非空与自增

于由杨涛涛发布

MySQL 里字段的属性很多，对性能来说，影响也是可大可小，所以针对其属性这一块有必要进行一次探究。

一、NULL / NOT NULL

NULL 对外部程序来说，具体为不知道、不确切的、无法表述的值。所以在很多家公司的开发规范里都明确规定了，必须为 NOT NULL。

其实用到 NULL 的场景都可以转换为有意义的字符或者数值，一是有利用数据的易读性以及后期的易维护性；二是降低 SQL 语句的编写难度。

关于 NULL 的特性如下：

1. 参与 NULL 字段拼接的结果都为 NULL，预期的可能会有差异

预想把字段 r1 做个拼接，再插入到新的表 t3 里，结果 t3 表的记录全为 NULL，跟预期不符。
  1. mysql> show create table t1\G
  2. *************************** 1. row ***************************
  3.       Table: t1
  4. Create Table: CREATE TABLE `t1` (
  5.  `r1` varchar(10) DEFAULT NULL
  6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  7. 1 row in set (0.00 sec)

  9. mysql> show create table t2\G
  10. *************************** 1. row ***************************
  11.       Table: t2
  12. Create Table: CREATE TABLE `t2` (
  13.  `r1` varchar(10) NOT NULL
  14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  15. 1 row in set (0.00 sec)

  17. mysql> create table t3 like t1;
  18. Query OK, 0 rows affected (0.04 sec)

  20. mysql> insert into t3 select concat(r1,'database') from t1 limit 2;
  21. Query OK, 2 rows affected (0.02 sec)
  22. Records: 2  Duplicates: 0  Warnings: 0

  24. mysql> select * from t3;
  25. +------+
  26. | r1   |
  27. +------+
  28. | NULL |
  29. | NULL |
  30. +------+
  31. 2 rows in set (0.00 sec)
那正确的方法如下，对 NULL 用函数 IFNULL 特殊处理。
  1. mysql> insert into t3 select concat(ifnull(r1,''),'database') from t1 limit 2;
  2. Query OK, 2 rows affected (0.01 sec)
  3. Records: 2  Duplicates: 0  Warnings: 0

  5. mysql> select * from t3;
  6. +----------+
  7. | r1       |
  8. +----------+
  9. | database |
  10. | database |
  11. +----------+
  12. 2 rows in set (0.00 sec)

2. 对于包含 NULL 列的求 COUNT 值也不准确

t1 和 t2 的记录数是一样的，但是字段 r1 包含了 NULL，这导致结果忽略了这些值。

  1. mysql> select count(r1) as rc from t1;
  2. +-------+
  3. | rc    |
  4. +-------+
  5. | 16384 |
  6. +-------+
  7. 1 row in set (0.01 sec)

  9. mysql> select count(r1) as rc from t2;
  10. +-------+
  11. | rc    |
  12. +-------+
  13. | 32768 |
  14. +-------+
  15. 1 row in set (0.03 sec)
这时候我们可能想到了，正确的方法是用 NULL 相关函数处理，
  1. mysql> select count(ifnull(r1,'')) as rc from t1;
  2. +-------+
  3. | rc    |
  4. +-------+
  5. | 32768 |
  6. +-------+
  7. 1 row in set (0.03 sec)
或者是直接用 COUNT(*) 包含了所有可能的值
  1. mysql> select count(*) as rc from t1;
  2. +-------+
  3. | rc    |
  4. +-------+
  5. | 32768 |
  6. +-------+
  7. 1 row in set (0.02 sec)
当然了不仅仅是 COUNT，除了 NULL 相关的函数，大部分对 NULL 处理都不友好。
所以其实从上面两点来看，NULL 的处理都得特殊处理，额外增加了编写 SQL 的难度。

3. 包含 NULL 的索引列

对包含 NULL 列建立索引，比不包含的 NULL 的字段，要多占用一个 BIT 位来存储。

示例
key_len 分别为 43 和 42，t1 比 t2 多了一个字节，那这里可能有人要问了，不是说占了一个 BIT 位吗？那为什么多了一个字节？可以关注我上一篇文章（第02期：MySQL 数据类型的艺术 – 大对象字段）关于 BIT 的详细描述。
  1. mysql> pager grep -i 'key_len'
  2. PAGER set to 'grep -i 'key_len''

  4. mysql> explain select * from t1 where r1 = ''\G
  5.      key_len: 43
  6. 1 row in set, 1 warning (0.00 sec)

  8. mysql> explain select * from t2 where r1 = ''\G
  9.      key_len: 42
  10. 1 row in set, 1 warning (0.00 sec)

4. 各存储引擎相关的对 NULL 的处理

在 MySQL 8.0 发布后，仅有 InnoDB、MyISAM 和 Memory 支持对包含 NULL 列的索引，其他引擎不支持。比如 NDB。

二、AUTO_INCREMENT

列的自增属性，一般用来设置整数列根据一定步长逐步增长的值，类似于其他数据库的序列。不过这里的“序列”是基于特定一张表的。关于自增属性的相关特性如下：

1. 控制自增属性性能的变量：innodb_autoinc_lock_mode

innodb_autoinc_lock_mode=0
代表传统模式，也就是说，在对有自增属性的字段插入记录时，会持续持有一个表级别的自增锁，直到语句执行结束为止。比如说下面两条语句，SQL 1 在执行期间，一直持有对表 f1 的表级自增锁，接下来 SQL 2 执行时锁超时。
innodb_autoinc_lock_mode=1
代表连续模式，和传统模式差不多，不同的点在于对于简单的插入语句，比如 SQL 2，只在分配新的 ID 过程中持有一个轻量级的互斥锁（线程级别，而不是事务级别），而不是直到语句结束才释放的表锁。
  1. -- SQL 1
  2. mysql> insert into f1(c2) select rpad(uuid(),100,uuid()) from t1;
  3. Query OK, 16777216 rows affected (3 min 35.92 sec)
  4. Records: 16777216  Duplicates: 0  Warnings: 0

  6. -- SQL 2
  7. mysql> insert into f1(c2) select 'database';
  8. ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
innodb_autoinc_lock_mode=2
代表交错模式。这个模式下放弃了自增表锁，产生的值会不连续。不过这是性能最高的模式，多条插入语句可以并发执行。MySQL 8.0 默认就是交错模式。
  1. -- SQL 1
  2. mysql> insert into f1(c2) select rpad(uuid(),100,uuid()) from t1;
  3. Query OK, 16777216 rows affected (3 min 35.92 sec)
  4. Records: 16777216  Duplicates: 0  Warnings: 0

  6. -- SQL 2
  7. mysql> insert into f1(c2) select 'sql2';

  9. Query OK, 1 row affected (0.02 sec)
  10. Records: 1  Duplicates: 0  Warnings: 0
那针对复制安全来说，以上三种模式，0 和 1 对语句级别安全，也就是产生的二进制日志复制到任何其他机器都数据都一致；2 对于语句级别不安全；三种模式对二进制日志格式为行的都安全。

2. 控制自增属性的步长以及偏移量

一般用在主主复制架构或者多源复制架构里，主动规避主键冲突。
auto_increment_increment 控制步长
auto_increment_offset 控制偏移量

3. 对于要立刻获取插入值的需求

就是说要获取一张表任何时候的最大值，应该时刻执行以下 SQL 3 ，而不是 SQL 2。SQL 2 里的函数 last_insert_id() 只获取上一条语句最开始的 ID，只适合简单的 INSERT。
  1. -- SQL 1
  2.   mysql> insert into f1(c2) values ('xx1'),('xx2'),('xx3');
  3.   Query OK, 3 rows affected (0.01 sec)
  4.   Records: 3  Duplicates: 0  Warnings: 0

  6. -- SQL 2
  7.   mysql> select last_insert_id() as last_insert_id;
  8.   +----------------+
  9.   | last_insert_id |
  10.   +----------------+
  11.   |              1 |
  12.   +----------------+
  13.   1 row in set (0.00 sec)
  14. -- SQL 3
  15.   mysql> select max(c1) as last_insert_id from f1;
  16.   +----------------+
  17.   | last_insert_id |
  18.   +----------------+
  19.   |              3 |
  20.   +----------------+
  21.   1 row in set (0.00 sec)

  23. -- SQL 4
  24. mysql> select * from f1;
  25. +----+------+
  26. | c1 | c2   |
  27. +----+------+
  28. |  1 | xx1  |
  29. |  2 | xx2  |
  30. |  3 | xx3  |
  31. +----+------+
  32. 3 rows in set (0.00 sec)

4. 自增列溢出现象

自增属性的列如果到了此列数据类型的最大值，会发生值溢出。比如变更表 f1 的自增属性列为 tinyint。

SQL 2 显式插入最大值 127， SQL 3 就报错了。所以这点上建议提前规划好自增列的字段类型，提前了解上限值。
  1. mysql> drop table f1;
  2. Query OK, 0 rows affected (0.04 sec)

  4. mysql> create table f1(c1 tinyint auto_increment primary key);
  5. Query OK, 0 rows affected (0.05 sec)

  7. -- SQL 1
  8. mysql> insert into f1 values (127);
  9. Query OK, 1 row affected (0.01 sec)

  11. -- SQL 2
  12. mysql> select * from f1;
  13. +-----+
  14. | c1  |
  15. +-----+
  16. | 127 |
  17. +-----+
  18. 1 row in set (0.00 sec)

  20. -- SQL 3
  21. mysql> insert into f1 select null;
  22. ERROR 1062 (23000): Duplicate entry '127' for key 'PRIMARY'

5. 自增列也可以显式插入有符号的值

  1. mysql> insert into f1 values (-10),(-20),(-30);
  2. Query OK, 3 rows affected (0.01 sec)
  3. Records: 3  Duplicates: 0  Warnings: 0

  5. mysql> select * from f1;
  6. +-----+
  7. | c1  |
  8. +-----+
  9. | -30 |
  10. | -20 |
  11. | -10 |
  12. | 127 |
  13. +-----+
  14. 4 rows in set (0.00 sec)
那针对这样的，建议在请求到达数据库前规避掉。

总结

本文讲了一个是字段是否应该为 NOT NULL，这时候应该很清楚了，字段最好是 NOT NULL；另外一个是字段的自增属性，其中关于性能与用法的相关示例。希望对大家有帮助。

关于 MySQL 的技术内容，你们还有什么想知道的吗？赶紧留言告诉小编吧！

分类： MySQL 调优

相关文章

MySQL 调优

第45期：一条 SQL 语句优化的基本思路

SQL 语句优化是一个既熟悉又陌生的话题。面对千奇百怪的 SQL 语句 阅读更多…

MySQL 调优

第44期：无主键分区表该不该使用

本来想着分区表在上一篇后就不续写了，最近又有同学咨询我分区表的新问题： 阅读更多…

MySQL 调优

第43期：多表关联场景下如何用好分区表

如何在多表关联场景下合理利用分区表来提升查询性能？基于前几篇关于分区表 阅读更多…