MYSQL 索引原理及建索引原则剖析

MYSQL 索引原理及建索引原则剖析

索引类型

• 非聚集索引：像字典一样最前面的几页索引，或者书的目录
• 聚集索引：像书的页码下面的数字

Innodb 表存储引擎（聚集索引）

索引保存的类型：

1. 索引列的值
2. 指向数据行的指针

Innodb 中只有一个聚集索引：

1. 默认会拿主键 ID 作为聚集索引
2. 如果没有主键，会取非空的唯一索引作为聚集索引
3. 如果上面的都没有，innodb 会自己维护一个唯一 ID 来作为聚集索引

当存在多个索引时：

因为 Innodb 只有一个聚集索引，所以聚集索引会包含索引列，以及指向行数据的指针，而其他的索引包仅仅含索引列，对应的其他数据则是指向相应的聚集索引，通过聚集索引查找对应的其他值

Innodb 索引特点：

1. idb 文件：既存索引，又存对应的数据

MyIsam 的索引特点：

1. MYI: 存储索引
2. MYD: 存储数据
3. FRM: 存储数据结构

索引的优缺点及建索引的原则

索引的优点：提高检索速度，降低磁盘 I/O

索引的缺点：索引需要存储，需要磁盘空间，索引实际上就是一张表，当字段更新(INSERT, DELETE,UPDATE)的时候，存在性能损耗

建索引的原则：

适合建立索引：

1. 频繁作为 WHERE 条件的字段
2. 关联字段可以建索引，例如外键
3. ORDER BY ， GROUP BY(底层实现是先做排序，然后分组) 后面的可以建索引

不适合建立索引：

1. WHERE 条件中用不到的字段不适合建立索引
2. 频繁更新的字段不适合建立索引
3. 数据值分布比较均匀的不适合建立索引(区分度不高)
4. 表的数据可以确定行数的，而且数据量很少

索引失效

什么情况下索引失效

1. Order by

SELECT * FROM USER ORDER BY NAME;

上面的情况索引生效，如果存在下面的索引:

如果加上 Index(name, age)复合索引

则下面的查询索引失效

SELECT * FROM USER ORDER BY AGE; -- 失效
SELECT * FROM USER WHERE AGE = 18 AND NAME = 'ZHANGSANG' -- 失效

必须按照下面的排序，则不会索引失效

SELECT * FROM USER ORDER BY NAME, AGE;

为什么会失效呢，比如这个复合索引像一个楼梯。第一层使用 NAME，第二层使用 AGE，则必须先上一层，才能上二层

替换索引为 index(age, name),sql 改为如下方式：

SELECT * FROM USER WHERE AGE > 18 AND NAME = 'ZHANGSANG' -- 前面的范围索引有效，后半部分索引失效