Mysql索引基础理解

理解索引前要先理解下面的概念
索引是什么
索引是一个文件,一个表可以有多个索引文件,正常查找数据时查询优化器判断可以使用索引并且选择索引后会优先读取索引文件,根据索引找到对应数据或者数据ID后再做进一步筛选

索引方式
1：hash:无序索引,不能范围查找,不能用于排序,使用比较少,缺点较多一般纯内存引擎会用
2：B+树:有序索引,非叶子节点不保存数据,只保存索引key,只有叶子节点保存数据,每次查找都要至叶子节点才能返回数据,一般4层深度可索引千万级别数据(每个数据页16k,行数据1kb,一个索引key16字节的情况,大多数情况使用这个)

下面主要讨论B+树索引类型

重点:B+树索引是有序的(索引不就应该有序吗,不然找个毛线,hash 是个例外)

索引分类

按整个索引文件分
1:聚集索引：叶子节点保存的是真实索引对应行的完整数据(一个表有且必须有一个 聚集索引，主键，或者第一个唯一索引或者系统默认生成一个rowid)

2:非聚集索引：叶子节点保存的是主键值;

按索引使用分
1:主键索引:唯一,值不能为null,建立主键则自动生成主键索引,无需额外再建立

2:唯一索引:不能重复,值可以有一个为null

3:全文索引:匹配长文字,性能不太好,一般用ES等搜索引擎代替mysql8.0 已经删除

4:联合索引:多个字段一起建立的索引,索引key按照所选字段从左至右拼接,使用注意最左匹配原则

几个概念
1:回表：通过非聚集索引查找到数据后回到表的聚集索引查找原始数据

2:覆盖索引：单个索引文件就能找到所有需要的字段信息，无需回表查询原始数据（注意这里是单个索引文件,简单的说就是走一次索引就能完成查找的情况)

3:最左匹配原则：针对B+树索引中的联合索引,查找的多个字段必须匹配索引顺序,索引字段可以不需要完全使用,但使用了的必须匹配顺序;(hash索引的要百分百匹配)
为啥是最左匹配,不是最右匹配,右边可以通配,左边就不行,搞啥都要以左为准???
因为索引是有序的,而排序是从左开始排,所以必须左边能匹配上才能根据顺序筛选数据,如果排序从右边开始排,那就要最右原则了

eg:索引 a_b_c,则单字段a 可以用此索引,a and b 也是可用 a and b and c 也是可以;b and c, a and c ,a or b 是不可用的 (b and a 这种情况理论是不行的 但是sql有查询优化器可能会将你的sql 改成 a and b 这样就可以使用这个索引了)

4:索引失效:由于sql顺序或者查找范围 不满足索引使用,导致全表扫码的情况,主要有以下情况:
a:索引字段中使用函数,如count,sum 或者日期转换等
b:联合索引执行的sql字段顺序不符合最左匹配原则
c:索引字段使用like %xxx 左边通配的情况(xxx%,右边通配可以用索引,谁叫排序是左边开始排…)
d:连表字段类型不一致,一个int 一个 varchat;
e:使用 or 关联 并且or 后面字段没有索引

5:索引合并:一条sql 同时使用几个索引文件的情况(mysql 5.0之后),sql 会根据多个个索引返回的数据进行交集 或者并集处理最后得出所要数据,是否使用得看查询优化器计算,用 explain 查看sql type 显示 index_merge,一般用or 且字段都有索引 是会使用索引合并的;

索引的缺点
a:索引需要占用空间;
b:索引是需要维护的,新增,更新索引字段,删除 数据等都要对索引进行维护,索引越多维护成本越大;
c:索引太多会影响查询优化器的效率,甚至有可能选到效率更差的索引影响查询速度;

PS数据库索引为啥用B+树而不是B树?
A:磁盘最小单位页有大小限制 一般为16KB,如果每次IO操作只能读取特定的页数,那即一个页内保存的数据越多每次IO能获取更多的索引数据,就是说索引树的宽度可以更大,相对的树的高度就更短
1:B数每个节点都存储数据,每个节点数据不重复导致树的宽度是有限的,数据量一定时树的深度就会加深,磁盘io 就会增加
2:B+树只有叶子节点才保存数据,其他节点保存的是key的最大跟最小值,叶子节点数据有序且不重复,每次查找必须找到叶子节点,但是因为非叶子节点只保存key,所以树的宽度可以比较大,另一个因为数据都是在叶子节点所以遍历数据只需要获取叶子节点连表就行