mysql-联合索引、最左前缀法则

联合索引

基于多个字段创建的索引我们成为联合索引，比如我们创建索引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条数据， 大概两千多万条数据