数据库 (三) 深入理解索引

密集索引(聚集索引)和稀疏索引(非聚集索引)的区别

• 密集索引文件中的每个搜索码值都对应一个索引值

• 稀疏索引文件只为索引码的某些键建立索引项

  由于密集索引决定了表的物理排列顺序，一张表只能有一个物理排列顺序，所以一张表只能创建一个密集索引

  MySQL两种存储引擎中索引的区别

  • InnoDB
    • 若一个主键被定义，该主键则作为密集索引
    • 若没有主键被定义，该表的第一个唯一非空索引作为密集索引
    • 若不满足上述条件，InnoDB内部会生成一个隐藏主键作为密集索引 (6字节的自增列)
    • 非主键索引存储相关键位和其对应的主键值，包含两次查找，一次是查找次级索引自身，然后查找主键
      

    如图，若查找例如where id = 7 的数据，则根据B+树的查找过程直接查找到id = 7 的叶子节点
    若对稀疏索引开始检索，则先检索Ellison，然后定位到 id = 14的主键信息，获取到主键信息之后，执行where id = 14 的B+树检索操作，最终到达叶子节点

索引并不是越多越好

• 数据量小的表并不需要建立索引，建立会增加额外的索引开销
• 数据变更需要维护索引，因此更多的索引意味着更多的维护成本
• 更多的索引意味着也需要更多的空间

联合索引最左匹配原则及成因

最左匹配原则

• mysql 会一直向右匹配知道遇到范围查询（>、<、between、like）就停止匹配，比如a = 3 and b = 4 and c >5 and d = 6 如果建立(a、b、c、d)顺序的索引，d是用不到索引的。如果建立(a、b、d、c)的索引都可以用到，a、b、d的顺序可以任意调整
• = 和 in 可以乱序，比如a = 1 and b = 2 and c = 3 建立(a、b、c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可识别的形式

最左匹配原则的成因

1. Mysql创建联合索引是首先会对最左边，也就是第一个索引字段进行排序

2. 在第一个排序的基础上，再对第二个索引字段进行排序，其实就像是实现了Order by字段1，再Order by 字段2这样一种排序规则

3. 所以第一个字段是绝对有序的，而第二个字段就是无序的了

4. 因此通常情况下，直接使用第二个字段进行条件判断是用不到索引的。这就是为什么mysql要强调最左匹配原则的成因。