MySQL索引机制
索引是一种高效获取记录的数据结构
1. 索引常用的一些数据结构
1.1 B-Tree
B-Tree.png
缺陷：
（1）数据与索引值一起存放在节点之中，使每一个数据页中能放入的数据量大大下降。
（2）该特性导致B树的深度大大增加，不利于元素的查询。
1.2 hash
hash结构.png
缺陷：
（1）不好维护：在插入，删除节点过程中会造成hash冲突
（2）不利用进行范围查询，如果要通过"in", "<="进行查询的话需要跨域多个桶。
1.3 B+Tree
B+Tree.png
1.4 备注
（1）数据页：数据在磁盘中以数据页的模式进行存储，一页的大小为16K，通过show Global Status like "Innodb_page_size"进行查询。
（2）通过索引搜索时引擎会采用折半查找法，查询路径中经过每个数据页会进行一次磁盘读取操作。
2. 数据库索引存储结构
2.1 数据库引擎及使用的索引
2.1.1 MyISAM（非聚集索引）
MyISAM的索引文件与数据文件是分离存放的。
（1）MyISAM由.MYD, .MYI, .frm三种格式的文件组成。
.MYD文件：表数据文件，存放主要数据记录
.MYI文件：表索引文件，存放索引信息
.frm文件：表结构文件
MyISAM结构.png
（2）特点
2.1.2 InnoDB（聚集索引）
InnoDB的索引与数据不分离储存。
（1）InnoDB索引由.frm文件与.idb文件组成。
.idb文件：存储索引与数据记录，由B+树实现。
.frm文件：表结构文件。
（2）InnoDB索引建立时要指定主键
通过指定主键Mysql实现对B+树数据结构的组织。
若不指定主键，Mysql系统会做如下操作：
从第一列开始向后寻找，直到找到所有记录属性不一致的列，以此列为准构建B+树。
若找不到唯一列，Mysql系统尝试建立隐藏列，以隐藏列为准组织索引树。
（3）指定主键使用int优点
相对于其他基本类型，int类型具有易于比较的优点，例如如果用string类型作为数据库的主键，插入三条主键值分别为"aaa", "abb", "abc"的数据，在比较过程中需要分别对比第一位，第二位及第三位字符的大小，效率较低。
（4）主键自增优点
减少索引树的维护成本。
非自增主键在插入时可能导致B+树的结构变化，增加了系统的维护成本。
非自增主键调整流程：插入节点 -> 分裂节点 -> 结构调整 -> 结束
若使用自增主键则在大多数情况下是需要进行插入节点。
2.2 聚集索引与非聚集索引
聚集索引中叶子结点会包含完整的数据记录，故使用了聚集索引的库表在进行二级索引查询时的效率较高（不用进行回表）
（1）非主键索引
非主键索引，又称二级索引，辅助索引。属于非聚集索引类型。Mysql建立索引时可以创建一个主键索引及至多15个非主键索引，建立过多索引则会导致插入/删除数据时Mysql索引树维护成本大大上升。
优点：节省空间，减少插入时维护的复杂度
缺点：需要回表
（2）回表操作
叶子结点中没有同时存放索引值与所有数据，故通过索引查到主键后需要通过主键值重新到主键索引中查询其他的数据信息，相当于进行了两次索引查询，常见于二级索引查询场景中。
回表操作.png
（3）回表优化：索引下推
由于使用二级索引会造成回表现象，故在Mysql5.6版本后对系统进行了优化，减少了回表次数。
概念：在二级索引查询到主键值后，先通过系统判断查询到的数据是否满足当前条件，若不满足直接丢弃，省去回表过程；若满足条件，则进行回表；
事例：待补充
2.3 联合索引
将几个字段联合创建索引树，查询时遵循最左匹配原则
例如对于职工表创建一个联合索引 idx_name(name, age, position)，mysql会按照字段的顺序依次比较构建索引B+树。
联合索引.png
（1）对于该联合索引，存在以下索引组合：
select * from employee where name = "xxx";
select * from employee where name = "xxx" and age = xx;
select * from employee where name = "xxx" and age = xx and position = "xxx";
联合索引中仅以最左字段为基础，依次向后排序；跳过该字段进行搜索将使索引效果下降（不一定会完全无效）
3. SQL调优工具
3.1 Explain工具
（1）explain查询关键属性
| 属性 | 含义 | 富含种类 |
|---|---|---|
| select_type | 查询类型 | simple,primary,subquery,derived |
| type | 表示关联类型或访问类型 | system>const>eq_ref>ref>range>index>All |
| key_len | 联合索引中命中的索引个数 | 不同类型字段的值不同 |
| Extra | 额外信息列 | using index,using where,using index condition,using temporary,using filesort |
（2） type字段
- Null: MySQL在系统优化前已拿到值，不用在访问表
例如：explain select min(id) from xxx; - const, system:命中主键索引或单条索引，查询效率极高
- eq_ref: 如果表查询使用主键关联，返回类型为eq_ref
- ref: 查询条件未用到唯一索引，但用到二级索引，有可能查到多条记录
- range：范围查询，有>, <, in等关键字
- index, all：效率较低
（3）索引覆盖：
查找的结果集若在主键与二级索引中都存在，查找后不用回表
（4）Extra含义： - using index: 表本次查询走了索引覆盖
- using where: where条件查询的列未被索引覆盖
- using index condition: 查询到的列不完全被索引覆盖
- using temporary: 查询时用到了虚拟表
例如：explain select distinct name from employee; - using filesort: 使用了文件排序的方式，是一种查询效率极低的方式。
4. 索引最佳实践经验
4.1 不要在索引上进行操作,可能导致索引失效
例如：explain select * from xxx where left(name, 3) = "xxx";
在索引树上找不到该种方式构成的索引排序，故走不到索引
4.2 注意通配符的使用
like以通配符开头时，mysql索引将会失效，变为全局扫描。
4.3 在任何情况下mysql都一定会使用索引查询吗？
不一定，优化器会根据具体情况进行sql优化，当使用二级索引查询记录时，mysql会综合分析直接扫表效率高还是回表查询效率高，并给出优化后的结果。
