索引
索引是帮助mysql高效获取数据的数据结构。数据库索引好比是一本书的目录，能加快数据库的查询速度。
优势：提高数据库检索的速度，减少数据库的IO访问次数；通过索引对数据排序，索引（单列、组合）的列会自动排序；where索引列在存储层处理，索引下推（ICP）；覆盖索引，select列是索引，不回表。
劣势：占据磁盘空间；增删改操作需维护索引；mysql不仅要保存数据，还要保存或修改索引数据。
分类
单列索引
一个索引只包含一个列，但一个表中可以有多个单列索引。
普通索引
mysql中的基本索引类型，没有限制，允许在定义索引中的列插入重复值和空值。
唯一索引
索引中的值必须是唯一的，和主键索引的区别在于索引中的列值可以为空值。
主键索引
特殊的索引类型，不允许有空值。在innodb存储引擎中，必须存在主键索引，如果建立表结构的时候没有指定主键索引，则会默认选用具有唯一性的列作为主键索引；若还不存在唯一列，则会自动生成伪列作为主键索引。故innodb存储引擎的表结构最好指定主键索引，最好不要使用uuid作为主键，不好排序，且占用空间。
组合索引
在表中的多个字段共同创建一个索引，只有在查询时满足最左前缀的条件，才会使用索引。
全文索引
全文索引，只有在myisam存储引擎中才可使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。由于其占用了较大的物理空间和降低了修改的维护性，不推荐使用。
使用
创建索引
# 单列索引-普通索引
create index index_name on table(column(length));
alter table table_name add index index_name (column(length)) using btree;
# 单列索引-唯一索引
create unique index index_name on table(column(length));
alter table table_name add unique index index_name(column(length)) using btree;
# 单列索引-全文索引
create fulltext index index_name on table(column(length));
alter table table_name add fulltext index_name(column(length)) using btree;
# 组合索引
alter table demeter add index index_title_time(title(50), time(10)) using btree;
删除索引
drop index index_name on table;
查看索引
show index from table_name;
原理
索引的存储结构
存储结构
- 索引是在存储引擎中实现的，不同的存储引擎会使用不同的索引
- myisam和innodb存储引擎只支持B+TREE索引
- memory/heap存储引擎支持hash和BTREE索引
B树和B+树
B树定义
B树又称作平衡多路查找树。一颗m阶的B树（意味着一个节点最多有m个子树）
- 树中每个结点最多含有m个孩子（m>=2）
- 除根结点和叶子结点外，其他每个结点至少有[ceil(m / 2)]个孩子（ceil是一个取上限的函数）
- 若根结点不是叶子结点，则至少有2个孩子
B树图示
基于上述图示的层级结构，入需查询29数据时，会发生多少次IO：
-
找到磁盘块1，加载到内存中（一次IO）
-
查到29大于17，小于35，故属于P2指针指向的磁盘块；找到磁盘块3，加载到内存中（一次IO）
-
查到29大于26，小于30，故属于当前磁盘块P2指针指向的磁盘块；找到磁盘块8，加载到内存中（一次IO）
-
在磁盘块8中找到匹配的29
故该次查找发生了三次IO操作，极大的增加了数据查找的性能。
B+树图示
B树是为了磁盘或其他存储设备设计的一种多叉平衡查找树，下面是其数据结构示例图：
- B树的高度一般在2-4这个高度，树的高度直接影响到IO读写的次数。
- 如果是三层结构--支撑的数据可以达到20个G；如果是四层结构--支撑的数据可以达到几十个T。
B树和B+树的区别：
- 非叶子节点是否存储数据，B树会存数据，B+树不会存数据。
- B树是非叶子节点和叶子节点都会存储数据。
- B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。
非聚集索引（myisam）
索引与数据存储在两个文件中（索引文件、数据文件），主键索引上的叶子结点存储的是数据的地址，通过该地址获取到对应的数据。
主键索引
辅助索引
聚集索引（innodb）
索引与数据存储在一个文件中，主键索引的叶子结点存储了完整的数据。
主键索引
辅助索引
次要索引叶子结点只会保存当前索引列的值和主键的值，当select列不满足时，会根据主键再查一次主键索引（回表）。
失效
-
最左前缀规则
-
索引上不要做计算或使用函数
-
尽量使用覆盖索引
-
索引字段不要使用不等（!=, <>），会导致主键索引使用范围索引，辅助索引会失效。
-
主键索引不能使用is not null语句，会导致全表扫描
-
索引字段使用like，不要用通配符开头
-
索引字段字符串类型要加引号
-
索引字段不要使用or
总结（假设index(a, b, c)）
where语句 索引是否被使用 where a = 3 使用-a where a = 3 and b = 5 使用-a，b where a= 3 and b = 5 and c = 4 使用-a，b，c where b = 3 或 where b = 3 and c = 4 或 where c = 4 不使用 where a = 3 and c = 5 使用-a where a =3 and b > 4 and c = 5 使用-a，b where a =3 and b like 'haha%' and c = 4 使用-a，b，c where a =3 and b like '%haha' and c = 4 使用-a where a =3 and b like '%haha%' and c = 4 使用-a where a =3 and b like 'h%aha%' and c = 4 使用-a，b，c
执行计划
参数说明
# explain 出来的信息有十列，分别是：
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
id
- 每个select语句会自动分配一个标识符
- 根据表的操作顺序分为以下几种情况
- id相同：从上往下顺序执行
- id不同：id越大，优先级越高
- id相同的不同的同时存在
- id为null：表示这是一个结果集，不需要使用它来查询
select_type
查询类型，用来区别普通查询、联合查询、子查询等查询操作。
-
simple
表示不需要union或子查询的简单select查询。
-
primary
一个需要union操作或者还有子查询的select，位于最外层的单位查询。
-
subquery
除了from子句中包含的子查询外，其余的所有子查询。
-
dependent subquery
与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
-
union
union连接的两个select查询，第一个查询是PRIMARY，除了第一个表外，第二个以后的表select_type都是union
-
dependent union
与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响
-
union result
包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null
-
derived
from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select
table
- 显示的查询表名，如果使用的别名，就展示别名
- 不涉及表操作，则显示null
- 尖括号（<>）括起来的表示一个临时表，后面的N就是计划执行的id
type
# 从左到右，依次变差
system、const、eq_ref、ref、fulltext、ref_or_null、unique_subquery、index_subquery、range、index_merge、index、ALL
除了ALl之外，其他所有的type都可以使用到索引。最少索引要使用到range级别。
-
system
表中只有一行数据或者空表。
-
const
使用唯一索引或者主键索引，返回记录一定是一行记录的等值where条件时，通常type是const。
-
eq_ref
关键字：连接字段主键或者唯一索引。该类型通常出现在关联查询中，对于前表的每一行结果，都能匹配到后表的一行结果。
-
ref
针对非唯一索引，使用**等值（=）**查询非主键。或者使用的最左前缀规则索引的查询。
-
fulltext
全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
-
ref_or_null
与ref方法类似，只是增加了null值的比较。实际用的不多
-
unique_subquery
用于where中的in形式子查询，子查询返回不重复值唯一值
-
index_subquery
用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重
-
range
索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中
-
index_merge
表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
-
index
关键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
-
all
全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录
possible_keys
此次查询可能使用的索引
key
此次查询真是使用的索引
key_len
- 用于处理查询的索引长度
- 单列索引：整个索引长度
- 多列索引：用了多少算多少（这里就可以根据该值分析多列索引的使用情况）
- 该值只计算where条件使用的索引情况，不包含group by和order by的索引使用。
ref
- 使用常数等值查询，这里显示const
- 连接查询，被驱动表的执行计划会显示驱动表的关联字段
- 表达式或者函数，或者隐式转换，可能显示为func
rows
显示执行计划中估算的扫描行数，不是精确值（innodb不是精确值，myisam是精确值，由于innodb中使用了mvcc）
Extra
-
no tables used
不带from字句的查询或者From dual查询；使用not in()形式子查询或not exists运算符的连接查询，这种叫做反连接，即，一般连接查询是先查询内表，再查询外表，反连接就是先查询外表，再查询内表
-
using fifilesort
-
排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
-
说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。
-
MySQL中无法利用索引完成的排序操作称为“文件排序”
-
-
using index
查询时不需要回表查询，直接通过索引就可以获取查询的数据。
- 表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错
- 如果同时出现Using Where ，说明索引被用来执行查找索引键值
- 如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作
-
using temporary
表示使用了临时表存储中间结果。
-
MySQL在对查询结果order by和group by时使用临时表
-
临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
-
distinct，在select部分使用了distinct关键字 （索引字段）
-
-
using where
- 表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤
- 查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition。
使用场景
需要索引
- 主键自动建立唯一索引。
- 频繁作为查询条件的字段应该创建索引。
- 多表关联中，关联字段应该创建索引，on两边都应该创建索引。
- 查询排序的字段因该创建索引。
- 覆盖索引不需要回表，视情况使用。
- 统计或分组字段，应该创建索引。
不需要索引
- 表记录较少，不需要创建索引（索引的创建也有开销）。
- 频繁更新的字段不需要创建索引（需维护索引）。
- 查询使用频率不高的字段不需要创建索引。
