MySQL InonoDB 索引

MySQL InonoDB 索引

• 聚簇索引
• 辅助索引
• 主键索引
• 唯一索引
• 复合索引
• 覆盖索引
• 全文索引
• 索引失效的情况

MySQL大多数索引是B+Tree索引，在无特殊说明的情况下均为B+Tree索引

B+Tree通常意味着所有的值都是按顺序存储的

InnoDB支持以下三种索引：

1. B+Tree索引
2. 全文索引
3. 哈希索引

InnoDB支持的哈希索引是自适应的，InnoDB会根据表的使用情况自动为表生成哈希索引，不能人为干预在表中生产哈希索引

聚簇索引

1，如果一个主键被定义了，那么这个主键就是作为聚簇索引 2，如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚簇索引 3，如果没有主键也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚簇索引，这个隐藏的主键是一个6个字节的列，改列的值会随着数据的插入自增。

辅助索引

叶子结点不是数据结点 叶子结点包含辅助索引的键值和指向聚簇结点的指针

主键索引

独立的列 不可为null

唯一索引

唯一索引不为聚集索引时可以为null 索引为null时，该条数据的该索引失效

复合索引

对多个列进行列索引 同时对多个列进行了排序，索引的B+Tree就已经完成了对多个列排序的工作 遵循最左前缀原则

覆盖索引

索引的叶子结点包含列所有需要查询的字段的值，就称为覆盖索引

全文索引

从InnoDB 1.2.x版本开始支持全文索引，通常使用倒排索引来实现的，它在辅助表中存储了单词与单词自身在一个或者多个文档中的位置进行了映射

索引失效的情况

1. 条件中使用or时，以下配置索引不会失效，否则索引失效 1.1 数据量太少，制定执行计划时发现全表更快，可以往表里填几百万id和tittle不一样的数据试试。 1.2 确保mysql版本5.0以上，且查询优化器开启了index_merge_union=on, 也就是变量optimizer_switch里存在index_merge_union且为on
2. like查询是以%开头
3. 对于多列索引，不满足最左前缀原则的
4. 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引 因为字符串和数字比较的时候，字符串和数字比较都被统一转成浮点数再比较
5. 如果mysql估计使用全表扫描要比使用索引快,则不使用索引

参考：

1. 高性能MySQL 第3版
2. MySQL技术内幕-InnoDB存储引擎 第2版