Mysql索引简单讲解
索引有哪几种分类
唯一索引:值不能重复、但可以为空
主键索引:值不能重复且不能为空、无需创建、建立主键默认创建
复合索引:一个索引包含多个字段
最左前缀原则:
1、如创建(user,name)复合索引,只要查询条件包含(user,name)则都可以启动索引,
2、mysql引擎为了更好利用引擎,在查询过程中会动态的调整字段顺序,查询条件只要包含符合索引字段,无论顺序是否一致,
mysql引擎会自动调整字段顺序,以便利用索引
单值索引:一个表可创建多个、且一个字段只能一个索引。
索引原理
1、B+tree是在B-tree(B-tree 中间不是减号)上优化,InnoDB引擎就是B+tree实现其索引结构
2、如果把id设置了主键索引,id会自动排序,以便查询,如链条一样,去一个一个链起来
3、索引会把数据分页,每页默认存储16kb
4、B-tree与B+tree的区别:
4.1、非叶子节点只存储值信息
4.2、所有叶子节点之间都有一个链指针
4.3、数据记录都存在叶子节点中
5、键索引默认聚簇索引:
聚簇索引图:
聚簇索引:将数据存储与索引放一块,索引结构的叶子节点保存了行数据
非聚簇索引: 将数据存储与索引分开存储,索引结构的叶子节点指向了数据
对应的位置
MyISAM使用的是非聚簇索引,非聚簇索引与聚餐索引的B+tree看上去没什么
不同,节点的结构完全一致只是存储的内容不同而已,主键索引的B+tree的节
点存储了主键,辅助建索引B+tree存储了辅助键。由于索引树是独立的,通过
辅助键检索无需访问主键的索引树
非聚簇索引图
使用聚簇索引的优势
问题:每次使用辅助索引检索都要经过两次B+TREE查询,看上去比非聚簇索
效率低,聚簇索引的优势在哪
1、由于行数据和聚簇索引的叶子节点存储在一起,同一页中会有多条行数
据,访问同一数据页不同行记录时,已经把数据加载到Buffer中(缓存器),再
次访问时,会在内存中完成访问,不必访问磁盘。这样主键和行数据是一起
被载入内存的,找到叶子节点就可以将行立刻返回,如果是主键ID组织数
据,获取数据更快
2、辅助索引的叶子节点,存储主键值,而不是数据存储地址。好处是当行
数据发生变化时,索引树的节点也需要分裂变化,辅助索引存储的是主键值,
减少了辅助索引的占用存储空间大小
聚簇索引需要注意什么?
1、使用主键索引为聚簇索引时,最好主键为自增,因为UUID不适合排序,可能出现新
的 索引树中间位置,导致索引树调整复杂变大,消耗更多时间和资源
2、建议使用INT的主键,方便排序默认索引树的末尾增加主键,对索引的结构更小。而且
主键值占用的存储空间更小,辅助索引中保存的主键值也会跟着变大,占用存储空间,也会
影响到IO操作读取到数据量
什么情况下无法使用索引?
1、查询语句中使用LIKE关键字
使用LIKE匹配的字符串第一个为‘%’,索引则不生效,‘%’为右边则有可能会有效
2、查询语句使用多个索引
1)多列索引是在表中多个字段创建了一个索引,查询条件必须使用第一个字段索引
才会生效
3、查询语句中使用OR关键字
1)查询语句中只有OR关键字,如果前后都是索引字段,索引则会生效
,如果OR前后有一个字段不 为索引,责不生效