MySQL 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

-> 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]>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;

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

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;

+—-+————-+———–+————+——+—————+—————+———+——-+——+———-+———————–+

+—-+————-+———–+————+——+—————+—————+———+——-+——+———-+———————–+

| 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;

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

1 row in set, 1 warning (0.00 sec)

建议:

MySQL列中尽量避免NULL,应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化。因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

-> 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]>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;

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

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;

+—-+————-+———–+————+——+—————+—————+———+——-+——+———-+———————–+

+—-+————-+———–+————+——+—————+—————+———+——-+——+———-+———————–+

| 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;

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

+—-+————-+———–+————+——+—————+——+———+——+——-+———-+————-+

1 row in set, 1 warning (0.00 sec)

建议:

相关推荐: MySQL 数据库主从同步的实现方法

这篇文章主要讲解了MySQL数据库主从同步的实现方法，内容清晰明了，对此有开发云主机域名兴趣的小伙伴可以学习一下，相信大家阅读完之后会有帮助。安装环境说明系统环境：数据库：由于是模拟环境，主从库在同一台服务器上，服务器IP地址192.168.1.7下载软件包今…