联合索引
基于多个字段创建的索引我们成为联合索引,比如我们创建索引create index idx on table(A,B,C)
我们称在字段A,B,C上创建了一个联合索引
存储结构
联合索引相较于单个索引来说,联合索引的索引项都会出现在索引上,存储引擎会先根据第一个索引项排序,如果第一个索引相同才会去看第二个索引,所以在查询时,如果头索引不带的话,联合索引就会失效,因为在根节点他就不知道怎么往下走。比如我们现在select * from USER us where us.age=20 and us.money=30这个sql去查的,首先在根节点上age>1并且<60,那么读下一个节点,依此类推读到叶子节点上取出主键id回表查询所有的字段值。
最左前缀法则
如果索引了多个列,要遵循最左前缀法则,查询从索引的最左前列开始,并且不能跳过索引中的列,啥意思,组合索引(age,money,name)你select * from user where money=3 AND name='程序员fly’跳过了age这一些,索引失效,这点面试官还挺爱问的,我们一起来做几个例子
//正确的联合索引使用
select * from user where age>5
select * from user where age=5 and money>3
select * from user where age=5 and money=3 and name='程序员fly'
索引失效
select * from user where money>3 //跳过开头的
select * from user where money>3 and name='程序员fly' //跳过开头的索引失效
使用部分索引
select * from user where age>5 AND money=3 //范围查询仅仅能用到第一个age,(Mysql在5.6之后好像优化器会把sql语句顺序调整为select * from user where money=3 AND age>5 这里需要抽空研究一下)
面试题【InnoDB一颗B+树可以存储多少数据】
b+树的特点:只有叶子节点存储数据,非叶子节点存储可以找到叶子节点的索引(如下图,键值(一般是主键)和指针)
InnoDB存储引擎的最小存储单元是16kb,即b+树的一个节点大小为16k
假设一行数据的大小为1k,则有个节点可以存储16条数据,
而非叶子节点,一般key为主键大小为8个字节(bigint),指针在InnoDB中是6字节
则一个非叶子节点中,假设索引为n :
则 n * 8 + (n + 1) * 6 = 16 * 1024 ⇒ n = 1170 即一个非叶子节点可以存储1170个索引, 1171个指针
一颗高度为2 的b+树中,可以存储的数据为 1171 * 16 = 18736条数据
高度为3 的b+ 树中,可以存储 1171 * 1171 * 16条数据, 大概两千多万条数据