mysql字段為NULL索引是否會(huì)失效實(shí)例詳解
項(xiàng)目場景：
很多博客說mysql在字段中創(chuàng)建普通索引，如果該索引中的數(shù)據(jù)存在null值是不走索引這個(gè)結(jié)論是錯(cuò)誤的，不過盡量還是設(shè)置默認(rèn)值。（版本8.0低于這個(gè)版本可能結(jié)果不一致）
1、創(chuàng)建表sc_base_color，其中普通索引為 “name，group_num”，這里暫時(shí)不測組合索引，下面再測試。
CREATE TABLE `sc_base_color` ( `id` bigint NOT NULL AUTO_INCREMENT, `group_num` bigint DEFAULT NULL COMMENT '顏色代碼', `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '顏色名稱', PRIMARY KEY (`id`) USING BTREE, KEY `idx_name` (`name`), KEY `idx_group_num` (`group_num`) ) ENGINE=InnoDB AUTO_INCREMENT=574 DEFAULT CHARSET=utf8mb3 COMMENT='顏色';
2、初始化測試數(shù)據(jù)
INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (30, 1, '米黃'); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (31, 1, '黑色'); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (32, 1, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (33, 1, '白色'); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (34, 1, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (35, 1, '綠色'); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (36, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (37, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (38, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (39, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (40, NULL, '紫色'); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (41, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (42, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (43, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (44, NULL, '藍(lán)色'); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (45, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (46, NULL, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (47, 2, '米藍(lán)色'); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (48, 2, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (49, 2, NULL); INSERT INTO `sc_base_color`(`id`, `group_num`, `name`) VALUES (50, 2, '黑紅色');
3、測試普通索引為NULL的情況是否使用了索引
使用 = 查詢，測試結(jié)果中使用到了索引，其中索引字段的值為“NULL”
EXPLAIN select * from sc_base_color where name = '米黃'; EXPLAIN select * from sc_base_color where group_num = 1;
截圖結(jié)果，兩列數(shù)據(jù)都存在空，最終走了索引。
使用 大于、小于 查詢
EXPLAIN select * from sc_base_color where name > '米黃'; EXPLAIN select * from sc_base_color where name < '米黃';
截圖結(jié)果
使用 不等于、not in 、isnull、!isnull查詢
EXPLAIN select * from sc_base_color where group_num != 1; EXPLAIN select * from sc_base_color where group_num not in (1); EXPLAIN select * from sc_base_color where isnull(group_num); EXPLAIN select * from sc_base_color where !isnull(group_num);
截圖結(jié)果
使用isnull、is not null查詢
# 使用is not null可能會(huì)導(dǎo)致索引失效，我測試了20條數(shù)據(jù)，只要null值占全部數(shù)據(jù)的百分之50就不會(huì)失效，否則會(huì)失效。又測了40條數(shù)據(jù)，23條數(shù)據(jù)不會(huì)為空，22條為null的會(huì)為空 EXPLAIN select * from sc_base_color where group_num is not null; # 使用is null也可能會(huì)導(dǎo)致索引失效，我測試了20條數(shù)據(jù)，6數(shù)數(shù)據(jù)不為空不會(huì)失效，也就是可能當(dāng)空的數(shù)據(jù)占比70%的時(shí)候索引會(huì)失效。 EXPLAIN select * from sc_base_color where group_num is null;
由此可以得出結(jié)論，字段為空是可以走索引的，但是部分場景可能會(huì)失效，盡量還是給默認(rèn)值。
4、測試組合索引為NULL是否走了索引
先刪除普通索引字段，增加組合索引
ALTER TABLE sc_base_color DROP INDEX idx_group_num; ALTER TABLE sc_base_color DROP INDEX idx_name; alter table `sc_base_color` add index idx_group_num_idx_name (group_num, name);
測試 = > < 查詢結(jié)果
EXPLAIN select * from sc_base_color where group_num > 1; EXPLAIN select * from sc_base_color where group_num < 1; EXPLAIN select * from sc_base_color where group_num = 1; EXPLAIN select * from sc_base_color where group_num = 1 and name = '米黃';
截圖結(jié)果，是可以走索引的，下面的邏輯就不用測試了和普通索引一樣，除非不符合最左匹配原則直接查詢name字段。
5、總結(jié)
在設(shè)計(jì)數(shù)據(jù)庫的時(shí)候盡量還是給字段的默認(rèn)值。
1、比如int、bigint類型默認(rèn)值為-1/0
2、比如varchar類型默認(rèn)值為空串
3、bigdecimal類型為0等等。
NULL值會(huì)有不少坑
1、count(字段NULL)會(huì)過濾統(tǒng)計(jì)的數(shù)據(jù)，sum這些函數(shù)也會(huì)
2、使用> < 的時(shí)候也會(huì)過濾掉為NULL的數(shù)據(jù)
3、group by 的時(shí)候會(huì)把所有為NULL的數(shù)據(jù)合并，可以隨機(jī)生成UUID解決
4、還有場景可能也有問題，這里我也忘記了，用的時(shí)候才會(huì)想起來。
總結(jié)
到此這篇關(guān)于mysql字段為NULL索引是否會(huì)失效的文章就介紹到這了,更多相關(guān)mysql字段NULL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
mysql 記錄不存在時(shí)插入 記錄存在則更新的實(shí)現(xiàn)方法
相信很多人都需要用到這個(gè)語句，請看下文:(在4.1以后的版本才有效)2008-08-08
CentOS 6、7下mysql 5.7 詳細(xì)安裝教程
這篇文章主要為大家介紹了CentOS 6、7下mysql 5.7 詳細(xì)安裝教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2018-08-08
MySQL進(jìn)行查詢結(jié)果求和的五種方法詳解
在數(shù)據(jù)庫中,求和查詢是一種常見的操作,特別是在處理大量數(shù)據(jù)時(shí),MySQL?提供了多種方法來執(zhí)行求和操作,下面小編就和大家詳細(xì)介紹一下吧2025-12-12
安裝MySQL后時(shí)間太長了會(huì)忘記密碼，在這里總結(jié)一下忘記密碼時(shí)如何重置本地MySQL Root密碼。感興趣的朋友跟隨腳本之家一起學(xué)習(xí)吧2018-03-03
MySQL 社區(qū)版 5.7.17 發(fā)布了，MySQL 是一個(gè)關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，由瑞典 MySQL AB 公司開發(fā)，目前屬于 Oracle 旗下產(chǎn)品，是最流行的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)。下面這篇文章主要介紹了Mysql 5.7.17 解壓版的安裝步驟，并且介紹了可能會(huì)遇到的坑，需要的朋友可以參考下。2017-01-01
這篇文章主要給大家介紹了關(guān)于Mysql修改server uuid的方法，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-09-09
詳解如何利用amoeba(變形蟲)實(shí)現(xiàn)mysql數(shù)據(jù)庫讀寫分離
這篇文章主要介紹了詳解如何利用amoeba(變形蟲)實(shí)現(xiàn)mysql數(shù)據(jù)庫讀寫分離,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2019-05-05
MySQL?作為一種常用的關(guān)系型數(shù)據(jù)庫,也提供了多種鎖類型,這篇文章主要給大家介紹了關(guān)于MySQL各種鎖的相關(guān)資料,文中通過代碼及圖文介紹的非常詳細(xì),需要的朋友可以參考下2024-06-06
MySQL創(chuàng)建數(shù)據(jù)庫和創(chuàng)建數(shù)據(jù)表
MySQL?是最常用的數(shù)據(jù)庫，在數(shù)據(jù)庫操作中，基本都是增刪改查操作，簡稱CRUD。但是，這篇文章主要介紹了數(shù)據(jù)庫和數(shù)據(jù)表如何創(chuàng)建，想詳細(xì)了解的小伙伴可以參考閱讀一下2023-03-03
MySQL中LIKE運(yùn)算符的多種使用方式及示例演示
無論是簡單的模式匹配還是復(fù)雜的模式匹配，LIKE運(yùn)算符都提供了強(qiáng)大的功能來滿足不同的匹配需求，通過本文的介紹，我們詳細(xì)了解了在MySQL數(shù)據(jù)庫中使用LIKE運(yùn)算符進(jìn)行模糊匹配的多種方式，感興趣的朋友跟隨小編一起看看吧2023-07-07