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<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、一個特殊的值或者一個空串代替空值。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/15498/viewspace-2222307/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- MySQL欄位究竟是否需要設定成not nullMySqlNull
- mysql 查詢欄位為null或者非nullMySqlNull
- MySQL-去掉不為null的欄位MySqlNull
- 資料庫索引欄位請不要為NULL資料庫索引Null
- MySQL中NULL欄位的比較問題MySqlNull
- 淺談索引序列之是否可以儲存NULL值?索引Null
- NULL 值與索引Null索引
- mysql索引使用經驗總結MySql索引
- MySQL修改欄位預設值MySql
- NULL 值與索引(二)Null索引
- mysql索引總結MySql索引
- 【Oracle】修改indexed 欄位是否影響索引的有效性OracleIndex索引
- laravel-query-builder 對於欄位 值為 null的排序方式LaravelUINull排序
- MySQL的主鍵和欄位型別問題總結MySql型別
- 面試題:對NotNull欄位插入Null值 有啥現象?面試題Null
- MySQL索引——總結篇MySql索引
- 關於MySQL InnoDB表的二級索引是否加入主鍵的總結MySql索引
- mysql如何判斷是否存在某個欄位MySql
- MySQL中IS NULL、IS NOT NULL、!=不能用索引?胡扯!MySqlNull索引
- Java判斷欄位是否為空,為空賦值 ?Java賦值
- 為什麼資料庫欄位要使用NOT NULL?資料庫Null
- 對錶中的欄位設定了預設值,新增記錄後卻發現該欄位為nullNull
- 索引裡的NULL值與排序小記索引Null排序
- 【mysql--求解求2個或以上欄位為NULL 的記錄】MySqlNull
- Mysql利用explain確認是否使用索引MySqlAI索引
- 一個500萬記錄的table, 一欄位索引,不過會有空值,是保持null好,還是讓它有預設值好索引Null
- 關於mysql設定varchar 欄位的預設值''和null的區別,以及varchar和char的區別MySqlNull
- mysql關於聚集索引、非聚集索引的總結MySql索引
- SQL語句中聚合函式忽略NULL值的總結SQL函式Null
- MySQL欄位預設值設定詳解MySql
- MySQL GROUP BY分組取欄位最大值MySql
- mysql-欄位設定Default值問題MySql
- MySQL重置 Auto_increment欄位起始值MySqlREM
- MySQL 更新一個表裡的欄位等於另一個表某欄位的值MySql
- 【Mongo】mongo更新欄位為另一欄位的值Go
- MySQL中JSON欄位的使用技巧MySqlJSON
- MySQL 中 JSON 欄位的使用技巧MySqlJSON
- Oracle 檢視 表屬性 :“表名(註釋)/列名(註釋)/欄位是否NULL”OracleNull