MySQL数据库索引总结

B树与B+树介绍

• B+树：聚簇索引中只在叶子节点存储完整的记录数据，

• 叶子节点的页之间通过单项链表连接；页内记录之间通过双向链表连接；

• B+树有以下性质：

  • 1）所有数据都存储在叶子节点，非叶子节点只存储索引，是叶子节点的冗余；树相比较B树，比较矮胖，磁盘IO次数就会变少。
  • 2）叶子节点之间通过指针互相链接，按照递增的顺序；能实现范围查询；
  • 3）树的高度为：O(logn)，树的高度能够保证；

• B树性质：

  • 1）非叶子节点也会存储数据；
  • 2）叶子节点之间没有指针；无法满足范围查询；
  • 3）相同存储空间，B+树能够存储更多索引。

索引分类

聚集索引和非聚集索引

对表字段建的每一个索引对应一颗B+树。

• 索引即数据，数据即索引：在innodb存储引擎的表中，默认会以主键创建聚集索引，称作主键索引，所谓聚集索引，就是在B+树的叶子节点中，主键和其他列的数据存储在一起；而非聚集索引是指B+树的叶子节点中，只存储了索引列的值和主键值，在根据索引找到主键值后，还需要根据主键回表，去聚集索引查找其它列的值。
• MyIsam引擎：均是非聚集索引，即索引的叶子节点中存储主键合每一条记录的地址，然后根据地址去指定地址取数据。

主键索引，二级索引

• 在innodb存储引擎的表中，会自动为主键创建索引，称为主键索引；
• 二级索引，包括：唯一二级索引和普通二级索引。
• Mysql中创建字段索引的语句：

ALTER TABLE <表名> 
ADD INDEX `idx_索引名` (字段名) COMMENT '索引注释'

联合索引

• 在多个字段上建立联合索引

增删索引语句命令

• 创建索引语句：

ALTER TABLE <表名> 
ADD INDEX `idx_索引名` (字段名) COMMENT '索引注释'

• 删除索引语句：

ALTER TABLE <表名> 
drop index `idx_索引名`

索引优点和缺点

• 优点：1. 加快查询速度；
• 缺点：1.需要使用额外的存储空间；2.频繁插入和删除不方便；

索引相关问题总结

为什么在建表时不建议主键字段太长？为什么不建议主键用uuid？

• MySQL中查询成本主要在于磁盘IO，磁盘每页的大小是固定的，为16KB；如果主键字段太长，以聚簇索引为例，则每页存放的记录数越少；会导致磁盘IO次数增多。对于非聚簇索引，是同样的道理。

为什么建议主键设置为自增？

• 在innodb存储引擎的表中，默认会以主键创建聚集索引，索引中的记录是按照主键排好序的。

为什么联合索引只能使用最左前缀匹配？

• 比如对表中字段a,b,c建立了联合索引idx_a_b_c，相当于建了一个B+树，B+树叶子节点中记录是先按照a字段的值排序，在a字段值相同的情况下，再按照b字段的值排序，然后再按照c字段的值排序。如果查询条件是b='aaa' and c = 'ccc'这种查询，则联合索引不会生效；原因：B+树中b和c字段的并不是有序的。
• 生效场景：
  1. a='aaa'
  2. a='aaa' and b='bbb'
  3. a='aaa' and b='bbb' and c = 'ccc'

索引为什么对%aaa这种模糊查询会失效

• 原因：

索引失效的几种情况

1. 全量查询的成本小于使用某索引的查询成本，会使用全量查询
2. %aaa这种模糊查询索引会失效
3. 两个字段之间用or连接，索引也会生效；
4. 联合索引不满足最左前缀匹配时联合索引不生效；

欢迎走过路过的各位大佬关注我的微信公众号ItBeeCoder