MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。那我们就来详细了解一下索引

索引是什么

MySQL官方对索引的定义为：索引是帮助 MySQL 高效获取数据的数据结构。

在数据之外，数据库系统还维护着满足特定查找算法的数据结构**，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

索引示意图
索引的出现就是为了提高查询效率，就像书的目录。其实说白了，索引要解决的就是查询问题。

查询，是数据库所提供的一个重要功能，我们都想尽可能快的获取到目标数据，因此就需要优化数据库的查询算法，选择合适的查询模型来实现索引。

优势

索引提高数据检索的效率，降低数据库的IO成本。
通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

实际索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引列也要占空间。
提高查询速度，降低更新表的速度。保存表数据，还要保存索引文件每次更新添加索引列字段。

索引的数据结构

B+树

任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

B+树

如上图，是一颗b+树，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

建索引的几大原则

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

索引优化

explain命令

explain命令的具体用法和字段含义可以参考官网explain-output。rows是核心指标，绝大部分rows小的语句执行一定很快，所以优化语句基本上是在优化rows。

explain结果列说明

id列

id为SELECT的标识符。它是在SELECT查询中的顺序编号。如果这一行表示其他行的union结果，这个值可以为空。在这种情况下，table列会显示为形如，表示它是id为M和N的查询行的联合结果。

注意：id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

select_type

SIMPLE： 简单的 select 查询，查询中不包含子查询或者 UNION。
PRIMARY： 查询中若包含任何复杂的子部分，最外层查询则被标记为 PRIMARY。
SUBQUERY： 在 SELECT 或 WHERE 列表中包含了子查询。
DERIVED： 在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询,把结果放在临时表里。
UNION：
- 若第二个SELECT出现在UNION之后，则被标记为UNION;
- 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED。
UNION RESULT： 从 UNION 表获取结果的 SELECT。

table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。如果是尖括号括起来的，这个表示子查询结果被物化，之后子查询结果可以被复用。

type

从最好到最差依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL一般来说，保证查询至少达到 range 级别，最好能达到 ref。除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引。

system： 表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，也可以忽略不计。
const： 表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快Dr如将主键置于where列表中，MySQL 就能将该查询转换为一个常量。
eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref： 非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。
例如：
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range： 只检索给定范围的行，使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另- -点，不用扫描全部索引。
index： Full Index Scan，index 与 ALL 区别是 index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的)
ALL： Full Table Scan，将遍历全表以找到匹配的行。

partitions

版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

possible_keys

查询可能使用到的索引都会在这里列出来

key

查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

这里是执行计划中估算的扫描行数，不是精确值

Extra

Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序成为“文件排序”。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询 group by。
Using index：表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错!如果同时出现 usingwhere,表明索引被用来执行索引键值的查找;如果没有同时出现 usingwhere,表明索引用来读取数据而非执行查找动作。
Using where：表明使用了 where 过滤。
Using join buffer：使用了连接缓存。
Impossible where：where 字句的值总是false，不能用来获取任何元素。
select tables optimized away：在没有 group by 字句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化 distinct 操作，在找到第一匹配的元素后即停止找同样值的动作。

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句,具体指运行时间超过 long_query_time 值的SQL,则会被记录到慢查询日志中。

运行时间超过 long_query_time 值的SQL，会被记录到慢查询日志中，默认关闭，调优需要手动开启。
long_ query_ time的默认值为10，意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

slow_query_log

<!--如何查看-->
SHOW VARIABLES LIKE '%slow_query_log%';

<!--如何开启 下面命令只对当前数据库有效-->
set global slow_query_log=1;

<!--永久生效 修改my.cnf[mysqld]下增加或修改参数-->
slow_query_log=1
slow_query_log_file=host_name-slow.log



<!--SQL语句健康查询-->
show global status like 'Slow_queries';

long_query_time

<!--是大于 > 10秒 而非 大于等于 >= 10-->
SHOW VARIABLES LIKE '%long_query_time%';

<!--设置时间-->
set global long_query_time=3;

<!--设置后可能时间看不到变化 需要用 global 或者重新连接或新开一个会话才能看到修改值-->
show global VARIABLES like 'long_query_time';

Slow_queries

1 2	<!--SQL语句健康查询--> show global status like 'Slow_queries';

mysqldumpslow –help 慢查询工具

s：是表示按照何种方式排序。
c：访问次数。
l：锁定时间。
r：返回记录。
t：查询时间。
al：平均锁定时间。
ar：平均返回记录数。
at：平均查询时间。
t：即为返回前面多少条的数据。
g：后边搭配一个正则匹配模式，大小写不敏感的。

全局查询日志

注：生产环境不开启这个功能

<!--开启功能，默认关闭-->
set global general_log=1;

<!--输出到表-->
set global log_output='TABLE';

<!--查看-->
select * from mysql.general_log;