MySQLnull值字段是否使用索引的總結(jié)

null和not null索引失效與否主要與表中字段的設(shè)立有關(guān)系，分為相應的兩種情況，當對不能是null的字段使用索引時，條件無論是null或者not null 索引都失效，當對能是null的字段使用索引時，條件無論是null或者not null 索引都生效.

創(chuàng)新互聯(lián)公司專業(yè)成都網(wǎng)站建設(shè)、成都網(wǎng)站制作，集網(wǎng)站策劃、網(wǎng)站設(shè)計、網(wǎng)站制作于一體，網(wǎng)站seo、網(wǎng)站優(yōu)化、網(wǎng)站營銷、軟文發(fā)布平臺等專業(yè)人才根據(jù)搜索規(guī)律編程設(shè)計，讓網(wǎng)站在運行后，在搜索中有好的表現(xiàn)，專業(yè)設(shè)計制作為您帶來效益的網(wǎng)站！讓網(wǎng)站建設(shè)為您創(chuàng)造效益。

以下是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]>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中，含有空值的列很難進行查詢優(yōu)化。因為它們使得索引、索引的統(tǒng)計信息以及比較運算更加復雜。你應該用0、一個特殊的值或者一個空串代替空值。

本文名稱：MySQLnull值字段是否使用索引的總結(jié)
網(wǎng)站鏈接：http://www.muchs.cn/article10/geeido.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站策劃、定制網(wǎng)站、品牌網(wǎng)站設(shè)計、標簽優(yōu)化、虛擬主機、建站公司

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容