MySQL索引
1 跟索引相关算法
1.1 B 树
B树 每一个节点可以有两个以上的节点的多叉查找树,B树的同一键值不会出现多次,要么在叶子节点要么在内节点。
B树的缺点:因为每个节点都包含key值和data值,因此如果data比较大时,每一页存储的key就会比较少;当数据比较多时,也会出现要经历多层节点才能查询在叶子节点的数据的问题。
1.2 B+ 树
B+ 树是B树的变体,与B树不同的是所有的叶子节点中包含了全部关键字信息,各叶子节点用指针连接。非叶子节点上只存储key的信息,就可以增加/每一页中存储key的数量,B树是纵向扩展的,最终会成为一个“痩高个”;B+树是横向扩展的,最终会成为一个“矮胖子”
在B+树中,所有记录节点都是按键值的大小顺序放在同一层的叶子节点上。
B树和B+树最大的区别在B+树上的键不仅可以出现在叶子节点还可以出现在非叶子节点。而B树的键不会出现多次。
为什么使用索引可以提高查询速度?
B+树索引就是B+发展而来,在数据库中B+树的高度一般都在2-4层,所以查找某一行数据最多只要2-4次IO。而没有索引的情况需要逐行扫描,明显效率低。
2 B+树索引
2.1 聚集索引
InnoDB的数据是按照主键顺序存放的,而聚集索引就是按照每张表的主键构造的一颗B+树。它的叶子节点存在的是整行数据。
InnoDB的主键一定是聚集索引。如果没有定义主键,聚集索引可能是第一个不允许为null的唯一索引。也有可能为row id。
数据只能按照一颗B+树进行排序,因此每张表只能有一个聚集索引。聚集索引对于主键的排序查找和范围查找速度非常快。
2.2 非聚集索引
非聚集索引并不会放整行数据,而存放的是键值和主键id。
当通过非聚集索引来查找数据时,InnoDB存储引擎会遍历索引树查找对应记录的主键,然后通过主键索引来查找对应的行数据。所以非聚集索引的查询需要多扫描一颗索引树。
3 什么情况下该使用索引
- 数据检索
- 聚合函数
- 排序
- 避免回表
- 关联查询
联合索引 idx_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c) 三种索引,称为联合索引的最左原则。
4 Mysql有索引但不走索引的原因
函数操作
对条件字段做函数操作不走索引原因:索引树中存储的是列实际值和主键值,如果直接拿值去匹配将无法定位到索引树的值。因此只能全表扫描。
隐式转换
相当于对要转换的字段进行函数操作。
优化:写SQl是看清字段类型
模糊查询
模糊查询like如果通配符在前面则不走索引,不能以%开头
优化:优化SQL或者使用ElasticSearch等搜索引擎。
范围查询
优化器会根据检索比例、表大小,I/O块大小等进行评估是否使用索引。比如单次查询的数据量过大,优化器将不走索引。
优化:降低单次查询范围,分多次查询。
计算操作
对条件字段做计算操作时可能不走索引
优化:将计算操作放在等号后面