MySQL null值字段是否使用索引的总结
发布时间：2022-04-07 12:39:20 所属栏目：MySql教程 来源：互联网
导读：null和not null索引失效与否主要与表中字段的设立有关系，分为相应的两种情况，当对不能是null的字段使用索引时，条件无论是null或者not null 索引都失效，当对能是null的字段使用索引时，条件无论是null或者not null 索引都生效. 以下是null字段走索引的
|
null和not null索引失效与否主要与表中字段的设立有关系，分为相应的两种情况，当对不能是null的字段使用索引时，条件无论是null或者not null 索引都失效，当对能是null的字段使用索引时，条件无论是null或者not null 索引都生效. 以下是null字段走索引的一个例子： (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 i=1; -> while (i<num) -> DO -> if mod(i,10)!=0 then -> insert into test_null values (i,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) 建议: MySQL列中尽量避免NULL,应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。 （编辑：PHP编程网 - 黄冈站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
浙公网安备 33038102330482号