blog | percy

life

0%

mysql 索引对null的处理

发表于 更新于 分类于

[TOC]

结论

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。这句的前半句是不对的（可参考官网说明： https://dev.mysql.com/doc/refman/5.7/en/is-null-optimization.html），但是后半句的结论确是可以采纳的。

虽然MySQL可以在含有null的列上使用索引，但不代表null和其他数据在索引中是一样的。不建议列上允许为空，最好限制 not null ，并设置一个默认值，比如0和’’空字符串等，如果是datetime类型，可以设置成’1970-01-01 00:00:00’这样的值。对MySQL来说，null 是一个特殊的值，Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。 对null做算术运算的结果都是null，count时不会包括null行，null 比空字符串需要更多的存储空间等。

事件

可以看到只要索引中用了 is not null 或者 is null 这种用法，就会导致索引失效。

Python

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
"""
use mydb;

drop table t1;


CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

alter table t1 drop index a,add index idx_ab(a,b);

show create table t1;
explain select id, a from t1 where a = 10;
explain select id, a from t1 where a is not null;
explain select id, a from t1 where a = 10 and b = 5;
explain select id, a from t1 where a = 10 and b is not null;
"""
from sqlalchemy import create_engine

engine = create_engine("mysql+mysqldb://root:123456@127.0.0.1:3306/mydb", max_overflow=5)


def create_many_data():
    delete_all()

    """ 插入数据 """
    for i in range(10):
        engine.execute(
            "INSERT INTO t1(id, a, b, c) VALUES ({}, {}, {}, {});".format(i, i, i, i)
        )

    for i in range(10):
        engine.execute(
            "INSERT INTO t1(id, a) VALUES ({}, {});".format(i + 10, i, )
        )

    for i in range(10):
        engine.execute(
            "INSERT INTO t1(id) VALUES ({});".format(i + 20)
        )


def delete_all():
    engine.execute("delete from t1")


if __name__ == "__main__":
    create_many_data()