一、 索引类型
从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分：主键索引、辅助索引（二级索引）
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）
1.1 普通索引
基于普通字段建立的索引，没有任何限制。
create index <索引名> on tablename(字段名);
alter table tablename add index <索引名>(字段名);
create table tablename ([...],index <索引名>(字段名));
1.2 唯一索引
索引字段必须唯一，但允许有空值。在创建或修改表时，添加唯一约束，就会自动创建对应的唯一索引。
create unique index <索引名> on tablename(字段名);
alter table tablename add unique index <索引名>(字段名);
create table tablename ([...],unique <索引名>(字段名));
1.3 主键索引
特殊的唯一索引，不允许有空值。创建或修改表时添加主键约束，一个表只能有一个主键。
create table tablename([...],primary key(字段名));
alter table tablename add primary key(字段名);
1.4 复合索引
复合索引即在多个列上建立索引，复合索引可以代替多个单一索引，而且相比多个单一索引，复合索引在开销上的所需更小。
同时有两个概念叫窄索引和宽索引，窄索引为索引列1~2列的索引，宽索引为索引列大于2列的索引。设计索引的一个重要原则是能用窄索引就不用宽索引，因为窄索引比宽索引更有效。
create index <索引名> on tablename(字段名1,字段名2...);
alter table tablename add index <索引名>(字段名1,字段名2...);
create table tablename ([...],index <索引名>(字段名1,字段名2...));
1.5 全文索引
用于大量的文本检索。
create fulltext index <索引名> on tablename(字段名);
alter table tablename add fulltext <索引名>(字段名);
create table tablename ([...],fulltext key <索引名>(字段名));
二、 索引原理
2.1 B+Tree结构
MySQL数据库索引采用的是B+Tree结构，是在B-Tree结构上做的优化。
●B-Tree树结构
索引值和data数据分布在整棵树结构中；
每个节点可以存放多个索引值及对应的data数据；
树节点的索引值是从左往右升序排列。
B树的搜索：从根节点开始找，对节点内的索引序列采用二分法查找，如果命中就结束查找。没有命中就进入子节点重复查找过程，直到所对应的节点指针为空，或已经是叶子节点才结束。
●B+Tree树结构
非叶子节点不存储data数据，只存储索引值，这样可以存储更多的索引值；
叶子节点包含了所有的索引值和data数据；
叶子节点间使用了指针连接，提高了区间的访问性能。
B+树的搜索：只需要定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内的所有节点和数据。
2.2 聚簇索引和辅助索引
聚簇索引和非聚簇索引：B+Tree的叶子节点存放的主键索引值和行记录就属于聚簇索引；索引值和行记录分开存放就属于非聚簇索引。
主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。
●聚簇索引
InnoDB中主键索引就采用的是聚簇索引的结构。
InnoDB的聚簇索引就是按照主键的顺序构建的B+Tree结构，B+Tree的叶子节点就是行记录，行记录和主键值存储在一起。意味着InnoDB的主键索引就是数据表本身，按主键顺序存储了整张表。
InnoDB的表要求必须要有聚簇索引：
(1) 如果表定义了主键，则主键索引就是聚簇索引；
(2) 如果表没有定义主键，则第一个非空unique列作为聚簇索引；
(3) 否则InnoDB会创建一个隐藏row-id作为聚簇索引；
●辅助索引
辅助索引也叫二级索引，是根据索引列来创建的B+Tree结构。叶子节点上只存储了索引列和主键信息，所以辅助索引占用的空间会比聚簇索引小很多。一个表只能创建一个聚簇索引，但是可以创建多个辅助索引。
三、 索引分析与优化
3.1 EXPLAIN
通过explain命令对select语句进行分析，大致内容如下：
●select_type
表示查询的类型。常用值有：
SIMPLE：表示查询语句不包含子查询或union
PRIMARY：表示此查询是最外层的查询
UNION：表示此查询是UNION的第二个或后续的查询
DEPENDENT UNION：UNION中的第二个或后续的查询语句，使用了外面查询结果
UNION RESULT：UNION的结果
SUBQUERY：SELECT子查询语句
DEPENDENT SUBQUERY：SELECT子查询语句依赖外层查询的结果
●type
表示存储引擎查询数据的方式。常用值有（从上到下效率依次增强）：
ALL：表示全表扫描，性能最差
index：表示基于索引的全表扫描，先扫描索引再扫描全表数据
range：表示使用索引范围查询，使用>、>=、<、<=、in等等
ref：表示使用非唯一索引进行单值查询
eq_ref：一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一
行结果
const：表示使用主键或唯一索引做等值查询，常量查询
NULL：表示不用访问表，速度最快
●possible_keys
表示查询时能够使用到的索引。并不一定会真正使用，显示的是索引名称。
●key
表示查询时真正使用到的索引，显示的是索引名称。
●rows
MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。
●key_len
表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
●Extra
Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下：
Using where：表示查询需要通过索引回表查询数据。
Using index：表示查询需要通过索引，索引就可以满足所需数据。
Using filesort：表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort建议优化。
Using temprorary：查询使用到了临时表，一般出现于去重、分组等操作。
3.2 回表查询
先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，就需要扫两边索引树。
3.3 覆盖索引
只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是：将被查询的字段，建立到组合索引。
3.4 最左前缀原则
复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，
那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。
3.5 like查询
MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。
3.6 NULL查询
MySQL可以在含有NULL的列上使用索引，但NULL和其他数据还是有区别的，不建议列上允许为NULL。最好设置NOT NULL，并给一个默认值。
四、 查询优化
4.1 慢查询优化
●开启慢查询日志
查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下：
show variables like 'slow_query_log%'
通过如下命令开启慢查询日志：
set global slow_query_log = ON;
set global slow_query_log_file = 'OAK-slow.log';
set global log_queries_not_using_indexes = ON;
set long_query_time = 10;
long_query_time：指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询记录到日志文件中。
log_queries_not_using_indexes：表示会记录没有使用索引的查询SQL。前提是slow_query_log的值为ON，否则不会奏效。
●查看慢查询日志
(1) 文本方式查看
(2) 使用mysqldumpslow查看
在 MySQL bin目录下执行下面命令可以查看该使用格式。
perl mysqldumpslow.pl --help
运行如下命令查看慢查询日志信息：
perl mysqldumpslow.pl -t 5 -s at C:\ProgramData\MySQL\Data\OAK-slow.log
●慢查询优化
(1) 是否全表扫描：explain分析type属性all
(2) 是否全索引扫描：explain分析type属性index
(3) 是否索引过滤性不好：靠索引字段选型、数据量和状态、表设计
(4) 是否频繁的回表查询开销：尽量少用select *，使用覆盖索引
4.2 分页查询优化
一般的分页查询使用简单的 limit 子句就可以实现。limit格式如下：
select * from 表名 limit [offset,] rows
第一个参数指定第一个返回记录行的偏移量，从0开始；
第二个参数指定返回记录行的最大数目；
如果只给定一个参数，它表示返回最大的记录行数目；
如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。（这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。）
●分页优化方案
第一步：利用覆盖索引优化
select * from user limit 10000,100;
select id from user limit 10000,100;
第二步：利用子查询优化
select * from user limit 10000,100;
select * from user where id >= (select id from user limit 10000,1) limit 100;
原因：使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化。
