侧边栏壁纸
  • 累计撰写 781 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

索引

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 124 阅读 / 3,427 字
温馨提示:
本文最后更新于 2022-04-30,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。
/ DATA / 索引
索引在什么情况下失效?
join 右边的表上要联结的字段没有索引.
like % 开头
在字段上进行计算不能命中索引
select name from user where FROM_UNIXTIME(create_time) < CURDATE();
应该修改为:
select name from user where create_time < FROM_UNIXTIME(CURDATE());


索引碎片化 : 随着数据的更改,索引会变得碎片化,这时就要重新整理,通常用存储过程来进行
primary key 默认是聚簇索引

select * from table where a like 'adc%' 会触发全表扫描吗?


索引算法
1.
BTree
2.
Hash

innodb 中 AHI adaptive hash index 自适应,自己不能设置



AVL树,就是红黑树
因为每一个几点只存一个数据,查找的时候IO次数太多所以淘汰了。
一个节点分三部分,
1.
区间
2.
数据
3.
子节点指针

B-Tree 多路搜索树,多平衡查找树
左闭右开,每次查询io次数都一样。
节点里不存数据,数据只存在叶子节点里,方便order by 和 范围查询。
数据区天然有序
数越小树的高度越低


文件存储
数据存放位置
show variables like ['datadir'

独立表空间
show variables like 'innodb_file_per_table'

page 大小
show variables like 'innodb_page_size'

frm 表的结构文件
ibd 索引对应存的就是一行数据,查到就返回。 在innodb里只有主键是聚簇索引,其它键上的索引都是非聚簇索引。数据节点存的是主键的值,要再在主键索引里查一次。
不用select * 就是因为需要的少了就不用再查一次,要不然还得再查一次。

myd 地址对应数据
myi index,树形存地址


范围之后全失效


不手动制定主键会自动生成一个隐藏的主键,6Byte的int, 在update的时候会锁表而不锁行。

联合索引
以1,2,3建立的联合索引,查询是用1,3;1,2;3,1;2,1;都会命中联合索引。 2,3;3,2;就不会命中联合索引。

最左前缀原则:
顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建联合索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。这样的话扩展性较好,比如 userid 经常需要作为查询条件,而 mobile 不常常用,则需要把 userid 放在联合索引的第一位置,即最左边 ———————————————— 版权声明:本文为CSDN博主「深寒丶」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。 原文链接:https://blog.csdn.net/Abysscarry/java/article/details/80792876

联合索引本质:
当创建**(a,b,c)联合索引时,相当于创建了(a)单列索引**,(a,b)联合索引以及**(a,b,c)联合索引**想要索引生效的话,只能使用 a和a,b和a,b,c三种组合;当然,我们上面测试过,a,c组合也可以,但实际上只用到了a的索引,c并没有用到!
索引相关
关于MySQL的索引,曾经进行过一次总结,文章链接在这里 Mysql索引原理及其优化.
1.
什么是索引?
索引是一种数据结构,可以帮助我们快速的进行数据的查找.
1.
索引是个什么样的数据结构呢?
索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引.
1.
Hash索引和B+树索引有什么区别或者说优劣呢?
首先要知道Hash索引和B+树索引的底层实现原理:
hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.
对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.
那么可以看出他们有以下的不同:
hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.
因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.
而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围.
hash索引不支持使用索引进行排序,原理同上.
hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性.
hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.
hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.
因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引.
1.
上面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,什么是聚簇索引?
在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引.
在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键,则隐式的生成一个键来建立聚簇索引.
当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询.
1.
非聚簇索引一定会回表查询吗?
不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.
举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询.
1.
在建立索引的时候,都有哪些需要考虑的因素呢?
建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合.如果需要建立联合索引的话,还需要考虑联合索引中的顺序.
此外也要考虑其他方面,比如防止过多的索引对表造成太大的压力.这些都和实际的表结构以及查询方式有关.
1.
联合索引是什么?为什么需要注意联合索引中的顺序?
MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.
具体原因为:
MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引
那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.
当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推.
因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整.
1.
创建的索引有没有被使用到? 或者说怎么才可以知道这条语句运行很慢的原因?
MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息.
可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度.
1.
那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?
1.
使用不等于查询
2.
列参与了数学运算或者函数
3.
在字符串like时左边是通配符.类似于'%aaa'.
4.
当mysql分析全表扫描比使用索引快的时候不使用索引.
5.
当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.

0

评论区