MySQL null值字段是否使用索引的總結

2024-07-24 12:32:21

字體：大中小

來源：轉載

供稿：網友

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

（編輯：武林網）

上一篇：Centos7安裝mysql數據庫

下一篇：semi-sync插件崩潰導致MySQL重啟的故障分析全過程