电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術文章
文章詳情頁

mysql IS NULL使用索引案例講解

瀏覽:2日期:2023-10-26 13:10:28
簡介

mysql的sql查詢語句中使用is null、is not null、!=對索引并沒有任何影響,并不會因為where條件中使用了is null、is not null、!=這些判斷條件導致索引失效而全表掃描。

mysql官方文檔也已經明確說明is null并不會影響索引的使用。

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

事實上,導致索引失效而全表掃描的通常是因為一次查詢中回表數量太多。mysql計算認為使用索引的時間成本高于全表掃描,于是mysql寧可全表掃描也不愿意使用索引。

案例

CREATE TABLE `user_info` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(11) DEFAULT NULL, `age` int(4) DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `user_info` (`id`, `name`, `age`) VALUES (’1’, ’tom’, ’18’);INSERT INTO `user_info` (`id`, `name`, `age`) VALUES (’2’, null, ’19’);INSERT INTO `user_info` (`id`, `name`, `age`) VALUES (’3’, ’cat’, ’20’);

執(zhí)行sql查詢時使用is null、is not null,發(fā)現依然使用的索引查詢,并沒有出現索引失效的問題。

mysql IS NULL使用索引案例講解

mysql IS NULL使用索引案例講解

分析

分析上述現象,則需要詳細了解mysql索引的工作原理以及索引數據結構。下面,分別通過工具解析和直接查看二進制文件兩種方式分別分析mysql索引數據結構。

工具解析

innodb_ruby是一個非常強大的mysql分析工具,可以用來輕松解析mysql的.ibd文件進而深入理解mysql的數據結構。

首先安裝innodb_ruby工具:

yum install -y rubygems ruby-devegem install innodb_ruby

innodb_ruby的功能很多,此處我們只需要用來解析mysql的索引結構,因此只需要如下的命令即可。更多的功能和命令詳見wiki。

innodb_space -s ibdata1 -T sakila/film -I PRIMARY index-recurse

解析主鍵索引:

$ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I PRIMARY index-recurseROOT NODE #3: 3 records, 89 bytes RECORD: (id=1) → (name='tom', age=18) RECORD: (id=2) → (name=:NULL, age=19) RECORD: (id=3) → (name='cat', age=20)

解析普通索引index_name:

$ innodb_space -s /usr/soft/mysql-5.6.31/data -T test/user_info -I index_name index-recurseROOT NODE #4: 3 records, 38 bytes RECORD: (name=:NULL) → (id=2) RECORD: (name='cat') → (id=3) RECORD: (name='tom') → (id=1)

通過解析工具數據mysql的索引結構可以發(fā)現,null值也被儲存到了索引樹中,并且null值被處理成最小的值放在index_name索引樹的最左側。

二進制文件

找到user_info表對應的物理文件user_info.ibd,通過軟件例如UltraEdit打開,直接定位到第5個數據頁(mysql默認一個數據頁占用16KB)。

mysql IS NULL使用索引案例講解

如圖,這些二進制數據就是index_name索引對應的索引頁數據,只挑選其中的索引記錄,展開如下:

最小記錄0x00010063

01 B2 01 00 02 00 29 記錄頭信息69 6E 66 69 6D 75 6D 最小記錄(固定值infimum)

最大記錄0x00010070

00 04 00 0B 00 00 記錄頭信息73 75 70 72 65 6D 75 6D 最大記錄(固定值supremum)

ID為1的索引0x0001007f

03 00 00 00 10 FF F1 記錄頭信息74 6F 6D 字段name的值:tom80 00 00 01 RowID:主鍵id的值為1

ID為2的索引0x0001008c

01 00 00 18 00 0B 記錄頭信息字段name的值:null80 00 00 02RowID:主鍵id的值為2

ID為3的索引0x00010097

03 00 00 00 20 FF E8 記錄頭信息63 61 74 字段name的值:cat80 00 00 03 RowID:主鍵id的值為3

最小記錄的記錄頭信息最后2字節(jié)00 29 -> 0x00010063偏移0x0029 -> 0x0001008C,即ID為2的索引位置;

ID為2的記錄頭信息最后2字節(jié)00 0B -> 0x0001008C偏移0x000B -> 0x00010097,即ID為3的索引位置;

ID為3的記錄頭信息最后2字節(jié)FF E8 -> 0x00010097偏移0xFFE8 -> 0x0001007F,即ID為1的索引位置;

ID為1的記錄頭信息最后2字節(jié)FF F1 -> 0x0001007F偏移0xFFF1 -> 0x00010070,最大記錄的記錄位置;

由此可見索引記錄是通過單向鏈表并以索引值排序串聯(lián)在一起,而null值被處理成最小的值放在了索引鏈表的最開始位置,也就是索引樹的最左側。與innodb_ruby工具解析出來的結果一致。

誤解原因

為何大眾誤解認為is null、is not null、!=這些判斷條件會導致索引失效而全表掃描呢?

導致索引失效而全表掃描的通常是因為一次查詢中回表數量太多。mysql計算認為使用索引的時間成本高于全表掃描,于是mysql寧可全表掃描也不愿意使用索引。使用索引的時間成本高于全表掃描的臨界值可以簡單得記憶為20%左右。

詳細的分析過程可以見筆者的另一篇博客:mysql回表致索引失效。

也就是如果一條查詢語句導致的回表范圍超過全部記錄的20%,則會出現索引失效的問題。而is null、is not null、!=這些判斷條件經常會出現在這些回表范圍很大的場景,然后被人誤解為是這些判斷條件導致的索引失效。

復現索引失效

復現索引失效,只需要回表范圍超過全部記錄的20%,如下插入1000條非null記錄。

delimiter //CREATE PROCEDURE init_user_info() BEGIN DECLARE indexNo INT;SET indexNo = 0;WHILE indexNo < 1000 DOSTART TRANSACTION; insert into user_info(name,age) values (concat(floor(rand()*1000000000)),floor(rand()*100));SET indexNo = indexNo + 1;COMMIT; END WHILE;END //delimiter ;call init_user_info();

此時user_info表中一共有1003條記錄,其中只有1條記錄的name值為null。那么is null判斷語句導致的回表記錄只有1/1003不會超過臨界值,而is not null判斷語句導致的回表記錄有1002/1003遠遠超過臨界值,將出現索引失效的現象。

由下兩圖也可以見,is null依然正常使用索引,而is not null如預期由于回表率太高而寧可全表掃描也不使用索引。

mysql IS NULL使用索引案例講解

mysql IS NULL使用索引案例講解

使用mysql的optimizer tracing(mysql5.6版本開始支持)功能來分析sql的執(zhí)行計劃:

SET optimizer_trace='enabled=on';explain select * from user_info where name is not null;SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

optimizer tracing輸出的執(zhí)行計劃可見,該查詢下,使用全表掃描所需要的時間成本為206.9;而使用索引所需要的時間成本為1203.4,遠遠高于全表掃描。因此mysql最終選擇全表掃描而出現索引失效的現象。

{ 'rows_estimation': [{ 'table': '`user_info`', 'range_analysis': {'table_scan': { 'rows': 1004, // 全表掃描需要掃描1004條記錄 'cost': 206.9 // 全表掃描需要的成本為206.9},'potential_range_indices': [ {'index': 'PRIMARY','usable': false,'cause': 'not_applicable' }, {'index': 'index_name','usable': true,'key_parts': [ 'name', 'id'] }],'setup_range_conditions': [],'group_index_range': { 'chosen': false, 'cause': 'not_group_by_or_distinct'},'analyzing_range_alternatives': { 'range_scan_alternatives': [{ 'index': 'index_name', 'ranges': ['NULL < name' ], 'index_dives_for_eq_ranges': true, 'rowid_ordered': false, 'using_mrr': false, 'index_only': false, 'rows': 1002, // 索引需要掃描1002條記錄 'cost': 1203.4, // 索引需要的成本為1203.4 'chosen': false, 'cause': 'cost'} ], 'analyzing_roworder_intersect': {'usable': false,'cause': 'too_few_roworder_scans' }} }} ]}

到此這篇關于mysql IS NULL使用索引案例講解的文章就介紹到這了,更多相關mysql IS NULL使用內容請搜索好吧啦網以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持好吧啦網!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 消泡剂_水处理消泡剂_切削液消泡剂_涂料消泡剂_有机硅消泡剂_广州中万新材料生产厂家 | 创绿家招商加盟网-除甲醛加盟-甲醛治理加盟-室内除甲醛加盟-创绿家招商官网 | 365文案网_全网创意文案句子素材站 | 发电机组|柴油发电机组-批发,上柴,玉柴,潍柴,康明斯柴油发电机厂家直销 | 低噪声电流前置放大器-SR570电流前置放大器-深圳市嘉士达精密仪器有限公司 | 理化生实验室设备,吊装实验室设备,顶装实验室设备,实验室成套设备厂家,校园功能室设备,智慧书法教室方案 - 东莞市惠森教学设备有限公司 | 工业淬火油烟净化器,北京油烟净化器厂家,热处理油烟净化器-北京众鑫百科 | 网站建设,北京网站建设,北京网站建设公司,网站系统开发,北京网站制作公司,响应式网站,做网站公司,海淀做网站,朝阳做网站,昌平做网站,建站公司 | 合肥礼品公司-合肥礼品定制-商务礼品定制公司-安徽柏榽商贸有限公司 | 耐酸碱胶管_耐腐蚀软管总成_化学品输送软管_漯河利通液压科技耐油耐磨喷砂软管|耐腐蚀化学软管 | 【黄页88网】-B2B电子商务平台,b2b平台免费发布信息网 | 屏蔽服(500kv-超高压-特高压-电磁)-徐吉电气 | 聚丙烯酰胺_厂家_价格-河南唐达净水材料有限公司 | 强效碱性清洗剂-实验室中性清洗剂-食品级高纯氮气发生器-上海润榕科学器材有限公司 | 反渗透水处理设备|工业零排放|水厂设备|软化水设备|海南净水设备--海南水处理设备厂家 | 青州开防盗门锁-配汽车芯片钥匙-保险箱钥匙-吉祥修锁店 | 防渗膜厂家|养殖防渗膜|水产养殖防渗膜-泰安佳路通工程材料有限公司 | 大连海岛旅游网>>大连旅游,大连海岛游,旅游景点攻略,海岛旅游官网 | 全自动实验室洗瓶机,移液管|培养皿|进样瓶清洗机,清洗剂-广州摩特伟希尔机械设备有限责任公司 | 睿婕轻钢别墅_钢结构别墅_厂家设计施工报价 | CTAB,表面活性剂1631溴型(十六烷基三甲基溴化铵)-上海升纬化工原料有限公司 | 武汉印刷厂-不干胶标签印刷厂-武汉不干胶印刷-武汉标签印刷厂-武汉标签制作 - 善进特种标签印刷厂 | 篮球地板厂家_舞台木地板品牌_体育运动地板厂家_凯洁地板 | 3A别墅漆/3A环保漆_广东美涂士建材股份有限公司【官网】 | 机器视觉检测系统-视觉检测系统-机器视觉系统-ccd检测系统-视觉控制器-视控一体机 -海克易邦 | 北京网站建设-企业网站建设-建站公司-做网站-北京良言多米网络公司 | 篷房[仓储-婚庆-展览-活动]生产厂家-江苏正德装配式帐篷有限公司 | 仿真植物|仿真树|仿真花|假树|植物墙 - 广州天昆仿真植物有限公司 | 微波消解仪器_智能微波消解仪报价_高压微波消解仪厂家_那艾 | 短信营销平台_短信群发平台_106短信发送平台-河南路尚 | 世界箱包品牌十大排名,女包小众轻奢品牌推荐200元左右,男包十大奢侈品牌排行榜双肩,学生拉杆箱什么品牌好质量好 - Gouwu3.com | 电动球阀_不锈钢电动球阀_电动三通球阀_电动调节球阀_上海湖泉阀门有限公司 | 外贮压-柜式-悬挂式-七氟丙烷-灭火器-灭火系统-药剂-价格-厂家-IG541-混合气体-贮压-非贮压-超细干粉-自动-灭火装置-气体灭火设备-探火管灭火厂家-东莞汇建消防科技有限公司 | 加盟店-品牌招商加盟-创业项目商机平台| 翅片管散热器价格_钢制暖气片报价_钢制板式散热器厂家「河北冀春暖气片有限公司」 | 健身器材-健身器材厂家专卖-上海七诚健身器材有限公司 | 沈飞防静电地板__机房地板-深圳市沈飞防静电设备有限公司 | 塑料异型材_PVC异型材_封边条生产厂家_PC灯罩_防撞扶手_医院扶手价格_东莞市怡美塑胶制品有限公司 | 杭州双螺杆挤出机-百科 | 散热器-电子散热器-型材散热器-电源散热片-镇江新区宏图电子散热片厂家 | 西门子气候补偿器,锅炉气候补偿器-陕西沃信机电工程有限公司 |