MySQL InnoDB索引

MySQL InnoDB索引

1. 什么是索引，为什么要使用索引，都有哪些索引

mysql中的索引是一种数据结构，主要为了提高查询的效率，主要有B+树、Hash、全文检索索引

2. InnoDB 为什么索引使用B+树呢

主要就是快，为什么快，因为其他索引慢。一定慢吗？综合情况下查询慢。

• 比如二叉树，没有办法平衡，影响效率
• 比如平衡二叉树，总树高太深，可能一张表都无法存储下，影响效率
• 比如B树，每个节点都会存储数据，那么一页下存储的数据就会变少，影响效率
• 那么B+树优点
  • 数据只存在叶子节点上并且有序，而且相邻节点中使用指针相互指向
  • 聚簇索引下，非叶子节点不存储数据，使页数据存储更多的索引节点
  • B+树比B树树高低，减少IO次数，增加使用效率
  • B+树比B树查询效率稳定
  • 一般B+树大概2~4层，存储数据大概2万 ~ 200亿

3.索引类型

聚簇索引

在创建表的时候就会生成一个唯一B+树，当没有主键时候，InnoDB会选择一个唯一的非空索引代替主键，如果还没有，InnoDB 会隐式定义一个rowid为聚簇索引

非聚簇索引

叶子节点包括一个列值和一个主键id，如果查询的内容在索引上没有叫做回表，如果有叫做覆盖索引。

联合索引

多个列的索引，按照列的顺序依次排序。（注：当创建(a,b)索引，where b=xx and a=xx，也是会走索引的，因为有优化选择器存在）

自适应HASH索引

命中某个页大于总数据的1/16时候自动创建这个索引，无需干预。

4.索引的创建

• FIC 快速创建索引，在InnoDB1.0.x引入（mysql5.5），在表上加S(共享)锁。（注:如果有写操作不可用）。删除索引，将索引空间内存标记为可用
• Online DDL（5.6之后），辅助索引创建和删除，改变自增长id，重命名列
  • NONE 模式 ，没有什么限制，就是快
  • SHARE 模式，加S锁
  • EXCLUSIVE 模式，加X（排查）锁

5.索引使用

## 查看索引
SHOW INDEX FROM 表名;
## 建立联合索引，（第一条对主键可以）
ALTER TABLE 表名 ADD INDEX 索引名(列名1,列名2)
CREATE INDEX 表名on 索引名(列名1,列名2)
## 删除索引，（第一条对主键可以）
ALTER TABLE 表名 DROP INDEX 索引名
DROP INDEX 索引名 ON 表名

6.索引创建因素

• select count(distinct(column_name))/count() from table_name; 超过0.5 或 show index 查看cardinality / count() 的数量 就创建索引
• 选择不经常更新的字段
• 选择value数据比较小的字段

7.索引失效 11种 情况

其他大佬的地址讲得很清楚: https://blog.csdn.net/xueping_wu/article/details/125835974