MySQL的索引说起来大家都不陌生，那MySQL一共有几种索引？每种索引都有什么作用？如何创建最优的索引？。。。
本文主要围绕InnoDB展开讲解，MyISAM捎带一提
示例给到的sql分析请忽略单表数据量、选择度问题。因为不同的where条件选择度不一样，可能会导致无法正常的命中索引
1、聚集索引、非聚集索引
1.1、聚集索引
又叫聚簇索引、主键索引。可以理解为数据顺序排列，即id为1的存在于第一条，id为2的存在于第二条
在MySQL中，如果一个主键被定义了，那么这个主键索引即为聚集索引。
如果没有主键被定义，那么该表的第一个唯一非空索引被作为聚集索引。
如果没有创建主键，也没有合适的唯一索引，那么innodb内部会生成一个隐藏的主键作为聚集索引，这个隐藏的主键类型为longint(6)，该列的值会随着数据的插入自增。
1.2、非聚集索引
可以简单理解为有序目录，是一种空间换时间的方法。
唯一索引、单列索引、联合索引均为非聚集索引。即针对创建的索引生成一份新的B+ Tree，用来储存需要的数据。
1.3、B+Tree存储
假设有如下订单记录表：
CREATE TABLE `trade` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`trade_id` varchar(20) NOT NULL DEFAULT '' COMMENT '订单号',
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
`product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品ID',
`num` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '购买数量',
`price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '单价',
`total_price` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '总价',
`status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态',
`create_time` int(10) NOT NULL DEFAULT '0' COMMENT '创建时间',
`update_time` int(10) NOT NULL DEFAULT '0' COMMENT '更新时间',
PRIMARY KEY (`id`),
UNIQUE key idx_trade_id (`trade_id`),
key product_id (`product_id`),
key uid_ctime (`user_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='订单表'针对聚集索引：系统会生成一个B+Tree用来记录索引数据，聚集索引存储结构见图中示例，示例为3层的B+Tree：
其叶子节点记录的为当前的主键ID，叶子节点的data存储的为该主键ID 对应的全部数据。
下图摘自《高性能MySQL》
注：实际的存储可能比我们图中的更复杂，图中只是为了较为直观的体现
针对非聚集索引：以 uid_ctime 索引为例，同样系统会生成一个B+ Tree用来记录索引数据，非聚集索引存储结构见下图:
其叶子节存储的为当前索引的全部数据、及指向聚集索引的主键地址，按照B+Tree的查找规则，可以看出这也是索引命中最左原则的原因。
下图摘自《高性能MySQL》
（这里有一个地方我也没搞清楚，存的是磁盘地址还是ID呢？）
另外给大家推荐一个好用的B+Tree模拟工具：www.cs.usfca.edu/~galles/vis…
1.4、存储文件对比
MyISAM中有三个文件，分别为：
.frm 主要存储表定义
.MYD 主要存储表数据
.MYI 主要存储索引内容InnoDB中有两个文件，分别为：
.frm 保存了每个表的元数据，包括表结构的定义等，该文件与数据库引擎无关。
.ibd 存放该表的数据和索引的文件（innodb_file_per_table = 1才起作用，代表开启独立表空间）。
如果my.ini未开启该配置，则innodb会将所有表及索引的数据存储在构成系统表空间的ibdata文件。1.5、表空间、段、区、页、行
表空间是由段、区、页、行组成的。
每个段拥有N个区：1区 = 1MB = 64页
页是磁盘管理的最小单位：1页 = 16KB
1页存储N条记录：N >= 2
行即为我们 select 出来的最小的一行
1.6、MySQL的体系结构
此处摘一张图：
2、唯一索引
顾名思义，保证数据唯一。如果有重复值插入或者update，mysql会直接报错。
针对B+Tree而言，如果我们创建的是一个唯一索引，那么在进行insert的时候，带来的开销其实是要比单列索引、联合索引高的。但是大多数情况下，这个速度损耗可以忽略。
3、单列索引
单独一列组成的B+Tree索引
4、联合索引
又叫复合索引，即多个列组成的B+Tree索引，参考上面给到非聚集索引B+Tree图
5、覆盖索引
简单来说就是在非聚集索引中就可以查询到我们需要的全部数据，无需再扫描聚集索引。也就是我们常说的无需回表。
下面给出查询示例：
//需要回表，因为在所命中的联合索引中无法查询到需要的全部数据
select * from trade where user_id = 500001 and create_time > 1587302939; //无需回表，直接命中覆盖索引
select user_id, create_time from table where user_id = 500001;其实mysql认为回表的效率很低，当需要用到主键排序时，考虑到需要回表，有些时候mysql可能会直接扫描聚集索引。
6、hash索引
基于哈希表实现，对于每一行数据，存储引擎都会计算一个hash code，hash code是一个较小的值，不同的键值的行计算出来的hash code也不一样。哈希索引将所有的hash code存在索引中，同时在哈希表中保存指向每个数据行的指针。
示例：
select * from trade where user_id = 10001;
先计算user_id的hash值，假设存在如下函数：f(10001) = 20666;
然后在索引中查找 20666，发现value的指针为第3行数据，然后对比第3行的值是否为10001，以确保数据准确6.1、常见场景
如果需要存储大量的URL，并根据URL进行搜索查找，如果使用B+Tree来存储的话，存储内容会很大，造成的磁盘开销也会很大，此时可以考虑hash索引。
6.2、hash冲突
如果表数据量较大，CRC32()会出现大量的hash冲突，而且hash冲突增长的概率可能远比我们想象的要快。
以 CRC32()为例，返回32位整数，当索引中存在 9.3W 条数据的时候出现hash冲突的概率为 1% ，如果表数量量较大，可以尝试使用 FNV64()
6.3、注意事项
hash索引只包含哈希值和行指针，而不存储字段值。
hash索引并不是按照索引值的顺序存储的，所以order by也无法命中索引。
7、空间数据索引
8、全文索引
9、常见注意事项
9.1、MySQL的锁
MySQL的锁是加在索引上的，如果是update、delete语句，请注意你的sql是否合理命中索引，最好保证扫描的条数较少
9.2、最左原则
索引是否命中遵循最左原则，即使是order by 、group by同样可以命中
//命中索引，符合最左原则，但只命中了联合索引的一半
//但当user_id = 500001的数据总条数大于表中的n%时，解析器可能不会命中该索引（其余sql同理）
select * from trade where user_id = 500001;
//未命中索引，因为不符合最左原则select * from trade where create_time > 1587302939;
//命中索引，符合最左原则。
select * from trade where user_id = 500001 order by create_time asc limit 10;
//命中索引，且命中覆盖索引
select create_time from trade where user_id = 500001 and create_time > 1587302939;
//具体情况具体分析
select * from table where user_id = 500001 and create_time > 1587302939 order by id desc limit 10;针对case1为什么会存在部分情况无法命中索引呢？
因为MySQL在解析层会进行计划执行，当发现按照where条件扫描索引的成本较高时，可能会直接放弃非聚集索引，而采用全表扫描的方式，n的值不同的版本中不太一致，说法也较多，不过大致在15-20之间
针对最后一个case，为什么说具体情况具体分析呢？
我们在其它文章中讲过：MySQL会预先通过解析器计划执行，来判断当前sql需要走哪个索引。
但部分情况下MySQL的解析器会认为order by id 可能走聚集索引成本会更低，很不幸，这种情况下你的sql就filesort了。具体分析可以通过explain来分析。
9.3、操作符
对于B+Tree索引，当使用 >、<、=、>=、<=、in 等操作符时，都可以使用相关列上的索引，但如果查询中有某列的范围查找，如："%%"、">"、"<="等，则右边所有的列都无法命中索引
9.4、SQL优化的十个原则
1、尽量避免在列上进行运算，这样会导致索引失效
2、使用 join 时，应该使用小结果集驱动大结果集，同时把负责的 join 查询拆分为多个Query，因为 join 多个表时，可能导致更多的锁定和堵塞
3、注意 like 模糊查询的使用，避免%%
4、仅列出需要查询的字段，这对速度并没有明显影响，主要考虑节省内存
5、使用批量插入语句节省交互
6、limit 的基数比较大时使用between
7、不要使用rand函数获取多条随机记录
8、避免使用NULL
9、不要使用count(id)，而要使用count(*)
10、不要做无畏的排序操作，而应尽可能在索引中完成排序
9.5、limit的优化
在 where 结果集较大的情况下 limit 可能会出现慢查询。假设单表500W数据，例子：
所以尽量用 id 去偏移比较好，如果是between的话需要确保 id 连续，如果 id 出现断层容易出现查询结果少数据的情况。
10、为什么要合理的创建、使用索引
索引建的好，慢查询没烦恼
如果索引创建使用不合理，势必造成两个结果：
1、MySQL连接数突增且不释放，CPU突增。当MySQL连接数满了的时候基本服务就瘫痪了。
2、一般的数据查询都是持久化连接，MySQL执行时间久的话，同样会导致http请求的耗时增加，容易引发集群过载，在php里常见的表现就是 cgi 一直被占用且无法释放，后续请求积压，fpm压力过大，cpu飙升，最终集群 idle 掉底。
