1 在oracle数据库中null值是不被索引存储的,需要使用nvl函数进行优化null,
2 在mysql中null值可以被索引保存,语句可以正常使用索引。
15:16: [ytt]> create index idx2 on t(password_last_changed);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
15:16: [ytt]> show index from t;
+-------+------------+----------+--------------+-----------------------+-----------+-------------+----
------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub
_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-----------------------+-----------+-------------+----
------+--------+------+------------+---------+---------------+---------+------------+
| t | 1 | idx1 | 1 | User | A | 9 |
NULL | NULL | | BTREE | | | YES | NULL |
| t | 1 | idx2 | 1 | password_last_changed | A | 8 |
NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+----------+--------------+-----------------------+-----------+-------------+----
------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.02 sec)
15:16: [ytt]> show index from t\G
*************************** 1. row ***************************
Table: t
Non_unique: 1
Key_name: idx1
Seq_in_index: 1
Column_name: User
Collation: A
Cardinality: 9
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
*************************** 2. row ***************************
Table: t
Non_unique: 1
Key_name: idx2
Seq_in_index: 1
Column_name: password_last_changed
Collation: A
Cardinality: 8
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
2 rows in set (0.00 sec)
15:17: [ytt]> select password_last_changed from t;
+-----------------------+
| password_last_changed |
+-----------------------+
| NULL |
| 2021-06-22 14:09:04 |
| 2021-06-22 14:09:04 |
| 2021-06-22 14:09:04 |
| 2022-07-28 15:39:08 |
| 2022-08-24 16:50:49 |
| 2022-08-24 16:51:04 |
| 2022-08-25 10:54:30 |
| 2022-09-29 11:10:51 |
| 2023-02-07 15:48:26 |
+-----------------------+
10 rows in set (0.00 sec)
15:17: [ytt]> explain select * from t where password_last_changed is null;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+------
----+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filte
red | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+------
----+-----------------------+
| 1 | SIMPLE | t | NULL | ref | idx2 | idx2 | 5 | const | 1 | 100
.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+------
----+-----------------------+
1 row in set, 1 warning (0.01 sec)
15:17: [ytt]>