Mysql底层原理之索引（四）

1、索引原理分析

1）聚簇索引

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。

　　Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

 

2）二级索引

在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。

　　Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。

　　辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。

 

3）联合索引

联合索引是指对表上的多个列进行索引，联合索引也是一棵B+树，不同的是联合索引的键值数量不是1，而是大于等于2。

最左匹配原则

某列使用范围查询，后面的列不能使用索引

Ø 联合索引：优于多列独立索引
Ø 索引顺序：选择性高的在前面
Ø 覆盖索引：Key里面包含要查询的数据
Ø 索引排序：索引同时满足查询和排序

Ø 数据库字符集使用utf8mb4;
Ø VARCHAR按实际需要分配长度；
Ø 文本字段建议使用VARCHAR；
Ø 时间字段建议使用long；
Ø bool字段建议使用tinyint；
Ø 枚举字段建议使用tinyint；
Ø 交易金额建议使用long；
Ø 禁止使用“%”前导的查询；
Ø 禁止在索引列进行数学运算，会导致索引失效；
l select * from t1 where id+1 >1121 不会使用索引
l select * from t1 where id >1121 - 1 会使用索引

Ø 表必须有主键，建议使用业务主键；
Ø 单张表中索引数量不超过5个；
Ø 单个索引字段数不超过5个；
Ø 字符串索引使用前缀索引，前缀长度不超过10个字符；