There are many attributes of fields in MySQL, and the impact on performance can be large or small, so it's necessary to explore the attributes.

1, NULL / NOT NULL

For external programs, NULL is an unknown, imprecise, and indescribable value. Therefore, it is clearly stipulated in the development specifications of many companies that it must be NOT NULL. In fact, the scenarios using NULL can be converted into meaningful characters or values. First, it can make use of the readability and maintainability of the data in the later stage; second, it can reduce the difficulty of writing SQL statements.

The properties of NULL are as follows:

1. The result of joining in NULL field splicing is NULL, which may be different in expectation

It is expected that field r1 will be spliced and then inserted into the new table t3. As a result, the records in table t3 are all NULL, which is not consistent with the expectation.

mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`r1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

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

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

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

mysql> select * from t3;
+------+
| r1 |
+------+
| NULL |
| NULL |
+------+
2 rows in set (0.00 sec)

The correct way to do this is to use the function IFNULL to handle NULL.

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

mysql> select * from t3;
+----------+
| r1 |
+----------+
| database |
| database |
+----------+
2 rows in set (0.00 sec)

2. The COUNT value containing NULL column is also inaccurate

t1 and t2 have the same number of records, but field r1 contains NULL, which causes the results to ignore these values.

mysql> select count(r1) as rc from t1;
+-------+
| rc |
+-------+
| 16384 |
+-------+
1 row in set (0.01 sec)

mysql> select count(r1) as rc from t2;
+-------+
| rc |
+-------+
| 32768 |
+-------+
1 row in set (0.03 sec)

At this time, we may think that the correct way is to use NULL correlation function,

mysql> select count(ifnull(r1,'')) as rc from t1;
+-------+
| rc |
+-------+
| 32768 |
+-------+
1 row in set (0.03 sec)

Or directly include all possible values with COUNT(*)

mysql> select count(*) as rc from t1;
+-------+
| rc |
+-------+
| 32768 |
+-------+
1 row in set (0.02 sec)

Of course, it's not just COUNT. Except for NULL related functions, most of them are not friendly to NULL processing. So in fact, from the above two points of view, NULL has to be handled in a special way, which increases the difficulty of writing SQL.

3. Index column with NULL

Index the NULL column, which takes up one BIT more than the NULL field. Example key_len is 43 and 42 respectively, t1 has one byte more than t2, so someone may ask here, doesn't it mean that it occupies a BIT bit? So why is there an extra byte? Can pay attention to me Previous article (issue 02: Art of MySQL data types - large object fields) A detailed description of BIT.

mysql> pager grep -i 'key_len'
PAGER set to 'grep -i 'key_len''

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

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

4. Processing of NULL related to each storage engine

After MySQL 8.0 is released, only InnoDB, MyISAM and Memory support indexes containing NULL columns, which are not supported by other engines. For example, NDB.

2, Auto? Increment

The auto increment property of a column is generally used to set the value that an integer column grows gradually according to a certain step, similar to the sequence of other databases. But the "sequence" here is based on a specific table. The relevant characteristics of the auto increment attribute are as follows:

1. The variable that controls the performance of the auto increment attribute: InnoDB? Autoinc? Lock? Mode

innodb_autoinc_lock_mode=0

Represents the traditional pattern, that is, when a record is inserted into a field with an auto increment attribute, a table level auto increment lock is held until the end of statement execution. For example, in the following two statements, during the execution of SQL 1, the table level auto increment lock for table f1 has been held all the time, and then the lock timed out during the execution of SQL 2.

innodb_autoinc_lock_mode=1

Represents the continuous mode, which is similar to the traditional mode. The difference is that for a simple insert statement, such as SQL 2, only a lightweight mutex (thread level, not transaction level) is held in the process of assigning a new ID, rather than a table lock released until the end of the statement.

-- SQL 1
mysql> insert into f1(c2) select rpad(uuid(),100,uuid()) from t1;
Query OK, 16777216 rows affected (3 min 35.92 sec)
Records: 16777216 Duplicates: 0 Warnings: 0

-- SQL 2
mysql> insert into f1(c2) select 'database';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

innodb_autoinc_lock_mode=2

Represents interleaved mode. In this mode, the auto increment table lock is abandoned, and the generated value will be discontinuous. However, this is the highest performance mode. Multiple insert statements can be executed concurrently. MySQL 8.0 is interleaved by default.

-- SQL 1
mysql> insert into f1(c2) select rpad(uuid(),100,uuid()) from t1;
Query OK, 16777216 rows affected (3 min 35.92 sec)
Records: 16777216 Duplicates: 0 Warnings: 0

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

Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

For copy security, the above three modes, 0 and 1 are statement level security, that is, the generated binary log copies to any other machine with the same data; 2 is not statement level security; and the three modes are row based binary log format.

2. Control the step size and offset of the auto increment attribute

It is generally used in primary primary replication architecture or multi-source replication architecture to actively avoid primary key conflict.

3. Requirements for obtaining the insertion value immediately

That is to say, to get the maximum value of a table at any time, the following SQL 3 should be executed at all times instead of SQL 2. The last INSERT id() function in SQL 2 only gets the ID at the beginning of the previous statement, which is only suitable for simple INSERT.

-- SQL 1
mysql> insert into f1(c2) values ('xx1'),('xx2'),('xx3');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

-- SQL 2
mysql> select last_insert_id() as last_insert_id;
+----------------+
| last_insert_id |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
-- SQL 3
mysql> select max(c1) as last_insert_id from f1;
+----------------+
| last_insert_id |
+----------------+
| 3 |
+----------------+
1 row in set (0.00 sec)

-- SQL 4
mysql> select * from f1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | xx1 |
| 2 | xx2 |
| 3 | xx3 |
+----+------+
3 rows in set (0.00 sec)

4. Overflow phenomenon of auto increase column

If the column of the auto increment property reaches the maximum value of this column data type, a value overflow will occur. For example, change the auto increment attribute of table f1 to tinyint. SQL 2 explicitly inserts a maximum value of 127, and SQL 3 reports an error. Therefore, it is recommended to plan the field type of auto increment column in advance and know the upper limit value in advance.

mysql> drop table f1;
Query OK, 0 rows affected (0.04 sec)

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

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

-- SQL 2
mysql> select * from f1;
+-----+
| c1 |
+-----+
| 127 |
+-----+
1 row in set (0.00 sec)

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

5. Add in columns can also explicitly insert signed values

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

mysql> select * from f1;
+-----+
| c1 |
+-----+
| -30 |
| -20 |
| -10 |
| 127 |
+-----+
4 rows in set (0.00 sec)

For this reason, it is recommended to avoid the request before it reaches the database.

summary

This article talks about whether a field should be NOT NULL. It should be clear that a field should be NOT NULL at this time. The other is the auto increasing property of a field, including relevant examples of performance and usage. I hope it will help you.

What else do you want to know about MySQL technology? Leave a message and tell Xiaobian!