MySQL null值字段是否使用索引的总结

发布时间:2020-08-09 14:26:38 作者:chenfeng
来源:ITPUB博客 阅读:260

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、一个特殊的值或者一个空串代替空值。


推荐阅读:
  1. Mysql之索引介绍
  2. MySQL索引不会被用到的情况汇总

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql 使用 null

上一篇:分库分表的5大方案,百度、腾讯、阿里等大厂都在用!

下一篇:串口SRAM和并口SRAM的引脚区别

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》