索引

1. 索引是什么
索引是将数据库排好序便于快速查询的数据结构。
快速查询，减少io，消除磁盘排序。

2. 索引的的分类

1. 按结构划分
   聚簇索引/主索引：叶子节点记录行数据
   非聚簇索引/辅助索引：叶子节点记录主键

Innodb有且只有一个聚簇索引
a. 如果有PK， PK是聚簇索引
b. 如果没有，第一个非空unique列是
c. 否则，innodb自己创建一个row-id作为聚簇索引

2. 按类型划分
   主键索引：不重复，不为空，一个表只有一个主键
   唯一索引：索引列的值必须唯一，允许有空值
   普通索引
   全文索引：仅可用于 MyISAM 表，使用倒排索引实现，记录了关键词到其所在文档的映射。
   组合索引：最左匹配原则

主键：ALTER TABLE table_name ADD PRIMARY KEY customer(customer_no);
唯一：ALTER TABLE table_name ADD UNIQUE (column1, column2); 
普通：ALTER TABLE table_name ADD INDEX (column1, column2);
全文：ALTER TABLE table_name ADD FULLTEXT (column);

2. 回表
通过普通索引去树中查找主键，根据主键去主索引树查找数据

3. 索引覆盖
从辅助索引中直接得到查询结果，不需要回表
using index

4. 索引下推、谓词下推
从存储引擎拉取数据的时候先根据where做筛选，存储引擎层过滤数据
using index condition

5. 什么时候会有索引失效

1. 不符合最左匹配原则：like %开头、联合索引中没有索引头
2. 组合索引如果中间某个值使用范围查询，范围条件后的索引会失效
3. 使用函数、表达式、!= 、<>、is null、is not null
4. 类型不匹配，涉及隐式转换 where id+1=4
5. 数据量特别大
6. or 在某些情况下会失效
7. order by后的列是用于排序的，不用于查找，失效

6. 索引的数据结构
B-Tree 平衡多路查找树，树的高度较低，减少磁盘io操作和时间复杂度，保持查找速度
B+Tree：可用于排序和分组，保存数据的索引，可以范围查找
树的叶子节点可用顺序指针访问，插入删除仅在叶子节点进行，需要维持平衡（分裂，合并，旋转）

7.不适合创建索引的情况

1. 经常需要更新的字段，更新时还需要进行索引重构消耗资源
2. 数据重复性较大的，唯一性较差的字段
3. 参与列计算的列不走索引

8.利用索引覆盖优化sql的场景

1. 用count(索引列)代替count(*)
2. 列查询表优化，单列索引升级为联合索引，避免回表
3. 分页查询

参考