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

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

mysql IS NULL使用索引案例講解

瀏覽:3日期: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’);

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

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的索引結構可以發現,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字節00 29 -> 0x00010063偏移0x0029 -> 0x0001008C,即ID為2的索引位置;

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

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

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

由此可見索引記錄是通過單向鏈表并以索引值排序串聯在一起,而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的執行計劃:

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

optimizer tracing輸出的執行計劃可見,該查詢下,使用全表掃描所需要的時間成本為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使用內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網!

標簽: MySQL 數據庫
相關文章:
主站蜘蛛池模板: 抓斗式清污机|螺杆式|卷扬式启闭机|底轴驱动钢坝|污水处理闸门-方源水利机械 | 欧美日韩国产一区二区三区不_久久久久国产精品无码不卡_亚洲欧洲美洲无码精品AV_精品一区美女视频_日韩黄色性爱一级视频_日本五十路人妻斩_国产99视频免费精品是看4_亚洲中文字幕无码一二三四区_国产小萍萍挤奶喷奶水_亚洲另类精品无码在线一区 | 家用净水器代理批发加盟_净水机招商代理_全屋净水器定制品牌_【劳伦斯官网】 | 网站优化公司_SEO优化_北京关键词百度快速排名-智恒博网络 | 安全,主动,被动,柔性,山体滑坡,sns,钢丝绳,边坡,防护网,护栏网,围栏,栏杆,栅栏,厂家 - 护栏网防护网生产厂家 | EFM 022静电场测试仪-套帽式风量计-静电平板监测器-上海民仪电子有限公司 | 滚珠丝杆升降机_螺旋升降机_丝杠升降机-德迈传动 | 万烁建筑设计院-建筑设计公司加盟,设计院加盟分公司,市政设计加盟 | 山东led显示屏,山东led全彩显示屏,山东LED小间距屏,临沂全彩电子屏-山东亚泰视讯传媒有限公司 | pbt头梳丝_牙刷丝_尼龙毛刷丝_PP塑料纤维合成毛丝定制厂_广州明旺 | 写方案网_方案策划方案模板下载| 高压油管,液压接头,液压附件-烟台市正诚液压附件 | 品牌策划-品牌设计-济南之式传媒广告有限公司官网-提供品牌整合丨影视创意丨公关活动丨数字营销丨自媒体运营丨数字营销 | 蓝莓施肥机,智能施肥机,自动施肥机,水肥一体化项目,水肥一体机厂家,小型施肥机,圣大节水,滴灌施工方案,山东圣大节水科技有限公司官网17864474793 | 颗粒机,颗粒机组,木屑颗粒机-济南劲能机械有限公司 | 紫外荧光硫分析仪-硫含量分析仪-红外光度测定仪-泰州美旭仪器 | 全自动定氮仪-半自动凯氏定氮仪厂家-祎鸿仪器 | 滤芯,过滤器,滤油机,贺德克滤芯,精密滤芯_新乡市宇清流体净化技术有限公司 | 尚为传动-专业高精密蜗轮蜗杆,双导程蜗轮蜗杆,蜗轮蜗杆减速机,蜗杆减速机生产厂家 | 深圳南财多媒体有限公司介绍| 并网柜,汇流箱,电控设备,中高低压开关柜,电气电力成套设备,PLC控制设备订制厂家,江苏昌伟业新能源科技有限公司 | 手机存放柜,超市储物柜,电子储物柜,自动寄存柜,行李寄存柜,自动存包柜,条码存包柜-上海天琪实业有限公司 | 电缆接头-防爆电缆接头-格兰头-金属电缆接头-防爆填料函 | 熔体泵|换网器|熔体齿轮泵|熔体计量泵厂家-郑州巴特熔体泵有限公司 | 铝扣板-铝方通-铝格栅-铝条扣板-铝单板幕墙-佳得利吊顶天花厂家 elisa试剂盒价格-酶联免疫试剂盒-猪elisa试剂盒-上海恒远生物科技有限公司 | 定时排水阀/排气阀-仪表三通旋塞阀-直角式脉冲电磁阀-永嘉良科阀门有限公司 | 陕西安玻璃自动感应门-自动重叠门-磁悬浮平开门厂家【捷申达门业】 | 制丸机,小型中药制丸机,全自动制丸机价格-甘肃恒跃制药设备有限公司 | 西门子伺服电机维修,西门子电源模块维修,西门子驱动模块维修-上海渠利 | 小型气象站_便携式自动气象站_校园气象站-竞道气象设备网 | 德国BOSCH电磁阀-德国HERION电磁阀-JOUCOMATIC电磁阀|乾拓百科 | 耐酸碱胶管_耐腐蚀软管总成_化学品输送软管_漯河利通液压科技耐油耐磨喷砂软管|耐腐蚀化学软管 | TwistDx恒温扩增-RAA等温-Jackson抗体-默瑞(上海)生物科技有限公司 | bkzzy在职研究生网 - 在职研究生招生信息咨询平台 | 土壤墒情监测站_土壤墒情监测仪_土壤墒情监测系统_管式土壤墒情站-山东风途物联网 | 盘煤仪,盘料仪,盘点仪,堆料测量仪,便携式激光盘煤仪-中科航宇(北京)自动化工程技术有限公司 | 二氧化碳/活性炭投加系统,次氯酸钠发生器,紫外线消毒设备|广州新奥 | 棉柔巾代加工_洗脸巾oem_一次性毛巾_浴巾生产厂家-杭州禾壹卫品科技有限公司 | 高考志愿规划师_高考规划师_高考培训师_高报师_升学规划师_高考志愿规划师培训认证机构「向阳生涯」 | 硫化罐_蒸汽硫化罐_大型硫化罐-山东鑫泰鑫智能装备有限公司 | 噪声治理公司-噪音治理专业隔音降噪公司 |