Summary of whether the MySQL null value field uses an index
The invalidation of null and not null indexes is mainly related to the establishment of fields in the table, which is divided into two corresponding cases. When using indexes on fields that cannot be null, the conditions are invalid, whether null or not null indexes are invalidated. When using indexes on fields that can be null, conditions whether null or not null indexes take effect.
The following is an example of a null field indexed:
(root@localhost)-[09:51:01]-[(none)] > create database test
Query OK, 1 row affected (0.02 sec)
(root@localhost)-[09:51:09]-[(none)] > CREATE TABLE `test_ null` (
-> `id` int (11) DEFAULT NULL
-> `mark` varchar (20) DEFAULT NULL
->) ENGINE=InnoDB DEFAULT CHARSET=utf8
(root@localhost)-[09:51:26]-[(none)] > use test
Database changed
(root@localhost)-[09:51:27]-[test] > CREATE TABLE `test_ null` (
-> `id` int (11) DEFAULT NULL
-> `mark` varchar (20) DEFAULT NULL
->) ENGINE=InnoDB DEFAULT CHARSET=utf8
Query OK, 0 rows affected (0.37 sec)
(root@localhost)-[09:51:29]-[test] > delimiter / /
(root@localhost)-[09:51:37]-[test] > DROP PROCEDURE IF EXISTS test_null
-> create procedure test_null (in num int)
-> BEGIN
-> DECLARE i int
-> set iTunes 1
-> while (I DO
-> if mod (iMagol 10)! = 0 then
-> insert into test_null values (iQuery concat ('aaa',i))
-> else
-> insert into test_null values (null,concat ('aaa',i))
-> end if
-> set i=i+1
-> END while
-> END
-> / /
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
(root@localhost)-[09:51:38]-[test] > delimiter
(root@localhost)-[09:51:44]-[test] > call test_null (10000)
Query OK, 1 row affected (12.34 sec)
(root@localhost)-[09:52:03]-[test] >
(root@localhost)-[09:52:03]-[test] >
(root@localhost)-[09:52:03]-[test] >
(root@localhost)-[09:52:03]-[test] >
(root@localhost)-[09:52:03]-[test] >
(root@localhost)-[09:52:03]-[test] > select count (*) from test_null
+-+
| | count (*) |
+-+
| | 9999 |
+-+
1 row in set (0.00 sec)
(root@localhost)-[09:52:24]-[test] > explain SELECT * from test_null WHERE id is null
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | test_null | NULL | ALL | NULL | NULL | NULL | NULL | 10003 | 10.00 | Using where |
+-- +
1 row in set, 1 warning (0.00 sec)
(root@localhost)-[09:52:34]-[test] > create index idx_test_null on test_null (id)
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
(root@localhost)-[09:52:46]-[test] > explain SELECT * from test_null WHERE id is null
+-- + -- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- + -- +
| | 1 | SIMPLE | test_null | NULL | ref | idx_test_null | idx_test_null | 5 | const | 999 | 100.00 | Using index condition |
+-- + -- +
1 row in set, 1 warning (0.00 sec)
(root@localhost)-[09:52:54]-[test] >
(root@localhost)-[09:52:54]-[test] > explain SELECT * from test_null WHERE id is not null
+-- +
| | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+-- +
| | 1 | SIMPLE | test_null | NULL | ALL | idx_test_null | NULL | NULL | NULL | 10003 | 89.97 | Using where |
+-- +
1 row in set, 1 warning (0.00 sec)
Recommendations:
Try to avoid NULL in the MySQL column and specify the column NOT NULL unless you want to store the NULL. In MySQL, it is difficult to optimize queries for columns with null values. Because they make the index, index statistics and comparison operations more complex. You should replace null values with 0, a special value, or an empty string.
