问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

mysql:数据库之索引的主要4项劣势分析

发布网友 发布时间:2022-04-08 10:44

我来回答

1个回答

懂视网 时间:2022-04-08 11:12


a.where c1=x and c2=x and c4>x and c3=x
b.where c1=x and c2=x and c4=x order by c3
c.where c1=x and c4=x group by c3,c2
d.where c1=? and c5=? order by c2,c3
e.where c1=? and c2=? and c5=? order by c3,c2
2.建表语句如下,插入了50万条数据,如果表数据太少,mysql会优化成直接做全表扫描,而不需要使用索引:
CREATE TABLE `MNG_ROLE` (
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar(50) NOT NULL,
`CREATE_DATE` char(8) NOT NULL,
`CREATE_TIME` char(6) DEFAULT NULL,
`UUID` char(32) NOT NULL,
`REMARK` varchar(3000) NOT NULL DEFAULT ‘‘,
`RESERVER` varchar(3000) NOT NULL DEFAULT ‘RESERVER‘,
PRIMARY KEY (`ID`),
UNIQUE KEY `UK_UUID` (`UUID`) USING BTREE,
KEY `INDEX_NAME_CREATE_DATE_TIME_REMARK` (`NAME`,`CREATE_DATE`,`CREATE_TIME`,`REMARK`(255)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=510335 DEFAULT CHARSET=utf8
3.语句执行情况:
a.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ AND REMARK > ‘A‘ AND CREATE_TIME=‘200000‘G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: range
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 962
ref: NULL
rows: 1
Extra: Using index condition; Using where
分析:type=range表示用到了索引,实际就是REMARK的范围查询,MySQL并且对索引进行了过滤,就有了Using index condition。实际上这条语句在不影响语义的情况下,把过滤条件改为:WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ AND CREATE_TIME=‘200000‘ AND REMARK > ‘A‘,此处用到的索引列包括:NAME,CREATE_DATE,CREATE_TIME,REMARK,key_len为962=50*3+14*3+255*3+5,5个长度是由于NAME是一个可变长度

===========================性感的分割线====================================
b.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ AND REMARK = ‘A‘ ORDER BY CREATE_TIMEG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 176
ref: const,const
rows: 1
Extra: Using index condition; Using where
分析:这里用于查询的索引只用到两列:NAME,CREATE_DATE;而CREATE_TIME的索引是用于排序,而REMARK是用于索引结果集的过滤。为什么CREATE_TIME的索引能用于排序?
1.首先我们要知道InnoDB的索引的数据格式是类似于有序的平衡树,当我们用了NAME和CREATE_DATE作为过滤条件,刚好CREATE_TIME的索引已经完成排序,那存储引擎就没必要在内存中再进行排序,可以运行下面两个语句,观察两者区别:
MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ ORDER BY CREATE_TIMEG
MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND CREATE_DATE=‘20151012‘ ORDER BY REMARKG

===========================性感的分割线====================================
c.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND RESERVER = ‘A‘ GROUP BY CREATE_TIME, CREATE_DATEG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 152
ref: const
rows: 1
Extra: Using index condition; Using where; Using temporary; Using filesort
分析:这里只用到一个索引(NAME)用于查找,但是这个语句在内存中建立了临时表(Using temporary),并且进行了排序(Using filesort),这些都是非常耗资源的。因为group by的操作,实际是先把过滤的数据,放到一个内存的临时表中,再对数据排序,最后对数据进行聚合操作。
我现在修改一下语句,这样会更加高效,原因就是利用索引的排序:EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role100‘ AND RESERVER = ‘A‘ GROUP BY CREATE_DATE, CREATE_TIMEG

===========================性感的分割线====================================
d.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role2‘ AND RESERVER=‘A‘ ORDER BY CREATE_DATE,CREATE_TIMEG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 152
ref: const
rows: 1
Extra: Using index condition; Using where
分析:索引用于查找的,只用到NAME,用于排序的有CREATE_DATE,CREATE_TIME

===========================性感的分割线====================================
e.MariaDB [yjtmng]> EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role2‘ AND CREATE_DATE=‘20151010‘ AND RESERVER=‘A‘ ORDER BY CREATE_TIME,CREATE_DATEG
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: MNG_ROLE
type: ref
possible_keys: INDEX_NAME_CREATE_DATE_TIME_REMARK
key: INDEX_NAME_CREATE_DATE_TIME_REMARK
key_len: 176
ref: const,const
rows: 1
Extra: Using index condition; Using where
分析:这里的NAME和CREATE_DATE都是用于查找的索引,CREATE_TIME用于排序的索引,但是CREATE_DATE的排序是无效的,因为前面的where过滤条件,已经赋值:CREATE_DATE=‘20151010‘,直接无视一个常量的排序。下面这个语句就需要内存排序:EXPLAIN SELECT * FROM MNG_ROLE WHERE NAME=‘role2‘ AND CREATE_DATE=‘20151010‘ AND RESERVER=‘A‘ ORDER BY REMARKG

4.总结:
1.索引可用于查找(type: ref),排序,过滤(Using index condition):
2.根据最左前缀原则,一旦有范围查询的字段,后面的索引将无效,而且索引需要串联,如果where c1=? and c3=?,这样将无法使用索引
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),清晰度越高越好
4.索引不能用于计算,尽量扩展索引

 

MySQL索引题目分析

标签:

声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com
vivo y3t手机能拨打和接听电话,不能收发短信 vivoy3t手机突然接收不到短信 vivoy3短信消息怎么提醒 无奈什么意思是什么 怎样让炒出的丝瓜不发黑? 钟表是以什么计量时间 钟表以( )、()、( )计量时间。 钟表以( )、()、( )单位计量时间 紫荆花开放时间 《青春 须臾成殇》渭伊的txt全集下载地址 智利议员致信内政部要求彻查海啸错误预警一事,这件事有多严重? 请问智利这个国家怎样?治安好不好以及物价贵不贵? 智利现在情况解决了吗 打砸地铁站,点燃大楼,智利首都为何遭受如此暴力? 智利问题 智利内政部误向全国推送海啸预警,这一误报造成了什么影响?说明什么问题? 智利老龄化现象加剧,人口老龄化问题如何解决? 长年争议不断,智利与玻利维亚究竟有着怎样的恩怨情仇? 我是学播音主持的,现在老师要我们把艺考的自备稿件找好,先熟悉,我希望有人可以推荐一下 请问艺考考散文这个行不?是散文么? 播音主持自备稿件,女生声音大 急求几篇播音与主持用的自备稿件!要比较偏的那种~就是尽量不要考官都听过好多次的那种! 我是学习播音的,可是我的自备稿件总是怎么也读不出感情怎么办??? 播音主持自备稿件该选择什么样的那 急求播音自备文学稿件 希望是具有爆发性的诗歌 我声音是属于中高音的 2011艺考播音主持专业各学校分别考了哪些即兴评述题目 莆田学院~~~ “天上的草原”重音整篇稿件的重音和停连 福建医科大学公共卫生硕士 非全日制怎么上课 《天上的草原》的赏析背景谁知道啊? 智利如何消除银行存在的年龄歧视问题? 海啸预警被智利政府错误推送全国,海啸是如何产生的? 高三地理问题关于智利的!很急~~ 四川财经大学研究生2020录取分数线 智利的春夏秋冬是怎样划分的 2020年四川农业大学农村发展专业研究生初试291分能上复试线吗? 商人骑毛驴穿越沙漠的智利问题!!! 智利逻辑问题,高手进!急!!! 四川师大考研学校自划线出来了吗 关于出国(智利)的问题 智利问题李奇的儿子是我儿子的父亲,那么“我”是谁 川大考研分数线是多少 四川省社科院2021新闻与传播研究生录取分数线 2020年四川省考研录取分数线是多少? 2O2σ年四川大学法学院录取法学研究生多少分,多少人? 维朗香蕉果馅可以直接吃吗? 水果馅的汤圆怎么做? 问矿泉水从20°加热到70°,吸到了的热量为?(600ML) 麦当劳的甜筒多少克一支?奶茶和玉米杯多少克一份?大份小份都说下,我要详细滴!谢谢 梦见买生肉又把它绞碎啥意思?