一、什么是索引
MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构，索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
通俗来讲，索引类似文章的目录，用来提高查询的效率。
二、创建索引语法及索引类型
按照功能逻辑来划分，MySQL 主要有以下索引类型
(一) 主键
数据列不允许重复，不允许为NULL，一个表只能有一个主键。就算没指定主键，MySQL 也会隐式创建主键。ALTER TABLE table_name ADD PRIMARY KEY (column);
(二) 普通索引
MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和NULL值。一个表允许多个列创建普通索引。ALTER TABLE table_name ADD INDEX index_name (column);
(三) 唯一索引
索引列中的值必须是唯一的，但是允许NULL值。建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。一个表允许多个列创建唯一索引。ALTER TABLE table_name ADD UNIQUE (column);
(四) 全文索引
主要是为了快速检索大文本数据中的关键字的信息。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引，基于倒排索引，类似于搜索引擎。MyISAM存储引擎支持全文索引，InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。ALTER TABLE table_name ADD FULLTEXT (column);
(五) 索引前缀
在文本类型如BLOB、TEXT或者很长的VARCHAR列上创建索引时，可以使用前缀索引，数据量相比普通索引更小，可以指定索引列的长度，但是数值类型不能指定。
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
(六) 组合索引
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀原则。
主键索引、普通索引、唯一索引等都可以使用多个字段形成组合索引。例如，
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 );
(七) 空间索引
MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
三、索引实现原理
索引的实现原理如果发在这里讲，篇幅就有点过长了，我们先留着等以后再讲。（主要是笔者还不太好，怕误人子弟）
四、索引访问方法实践
想必在座的各位都有使用过各种导航软件，比方说从当前位置出发广州塔，软件上面会给我们规划了好几条路线，切换交通工具又是新的路线了，不管路线怎么变化，我们的目的地是不变的。
MySQL 也是如此，我们只需要告诉 MySQL 需要那些数据，MySQL 怎么把数据搞出来那是 MySQL 自己的事情了，这种执行查询语句的方式称之为访问方法（access method）。下面简单来说说各种访问方法的具体内容。
(一) const
MySQL 会直接利用主键值在聚簇索引中定位对应的用户记录。只能用于等值匹配查询，查询效率是最快的，代价可以忽略不计。
(二) ref
当通过普通的二级索引列与常量进行等值匹配时来查询某个表，那么对该表的访问方法就可能是 ref。
(三) ref_or_null
我们不仅想找出某个二级索引列的值等于某个常数的记录，而且还想把该列中值为NULL的记录也找出来。其实就是比 ref 方法有了一些值为 NULL 的记录。
(四) range
在索引列与某一个常数进行等值比较时，才会使用到前面说的几种访问方法。但我们要查询某一个区间数据怎么办？比如下面这条语句，要搜索的区间数据为 [10001,10004],[10005,正无穷]。我们把这种索引获取某些区间范围的记录称之为 range。注意的是，对应的扫描区间要为若干个单点扫描区间或者范围扫描区间。
explain select * from employees where emp_no in (10001,10004) or emp_no > 10005;
(五) index
这种方法就比较有趣了。当我们查询的列都在一个组合索引里面，查询条件不是组合索引最左边的列，这时候就会走 index 方法。也就是说，我们直接遍历所有二级索引记录，再通过返回的值去判断key_part2='abc'，虽然是遍历所有记录，但是这个过程我们是不需要回表，所以效率比全表扫描要快了一点点。
#组合索引（key_part1,key_part2,key_part3）
select key_part1,key_part2,key_part3 from single_table where key_part2='abc';
(六) all
这个就不过多介绍了，直接简单粗暴全表扫描。
这些访问方法要牢记，这对我们后面学习 SQL 优化是必不可少的知识点。
五、为什么 B+树层级是 2-4 层
这里以主键索引为例子，组合索引情况比较特殊。
MySQL的InnoDB存储引擎的最小存储单元是页（大小默认是16k，可通过参数设置）。页可用于存放B+树叶节点数据，也可用于存放B+树非叶节点的 “键 + 指针”。
我们假设主键类型BIGINT（8字节）+ InnoDB 的行指针（6字节） ，每个键约 14字节，那么每页存储的键数量：16KB / 14B ≈ 1170 个键。
一个非叶节点可容纳约1170个指针，这里假设一行记录数据大小为 1k，那么底层叶节点一页16k就能存16条记录。叶节点数 一个叶节点能存放的记录数 = 1170 16 = 18720条 记录数。同理可得：高度为3的B+树能存的记录数为：1170*1170*16=21902400，2190w条记录，约2000w条记录。那么四层 B 树呢，那就达到了256亿级别，这种级别的数据量应该要用别的技术有存储了。
所以理论上三层就足够了，索引结构不会影响单表最大行数，2kw也只是推荐值，超过了这个值可能会导致B+树层级更高，影响查询性能。
六、索引的代价
索引是牺牲了空间和时间来换取的查询性能。
(一) 占用磁盘空间
每一颗B+树的每个节点都是一个数据页。一个数据页默认占用16KB空间，而一颗很大的B+树会有很多数据页组成，这将占用很大空间。我们可以用以下语句进行查询：
#查询这个库所有索引的大小
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema = 'xxx';
#查询这个库下某张表的索引大小
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM information_schema.TABLES WHERE table_schema = 'xxx' and table_name='xxx'; (二) 时间的代价
我们在增删改查时，都需要修改索引树。
B+树中每层节点都是按照索引值从小到大的排序。无论是叶子节点还是内节点中的记录，都按照索引列的值从小到大形成一个单相链表。
增删改查会对索引树进行破坏，所以存储引擎需要额外的时间进行页分裂、页面回收等操作，以维护节点和记录的顺序。
如果有很多索引，那么可能需要维护很多次 B+树。
七、索引为什么会失效
索引失效说到底还是没有对索引没有明确的认识，下面我总结了下索引失效的场景，我们简单来说说
(一) 对索引使用左或左右模糊查询
当我们使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。因为索引是有序的，左模糊查询无疑破坏了索引的有序性。
(二) 对索引使用函数
有时候我们会用一些 MySQL 自带的函数来得到我们想要的结果，这时候要注意了，如果查询条件中对索引字段使用函数，就会导致索引失效。
举个例子：某一张的日期是用时间戳进行存储，我们 to_char 转成年月日时分秒之后再进行比较，这个时候索引也会失效。
(三) 对索引进行表达式计算
explain select * from xxx where id+1 =2;
这种情况我想应该比较少吧，反正我是从来没写过这种语法。
(四) 组合索引没有遵循最左匹配原则
最左匹配原则顾名思义就是在组合索引中，最左侧的字段优先匹配。所以想让组合索引生效，必须要让最左侧字段出现在查询条件中。
(五) 对索引进行隐式类型转换
Sql语句中的字段和数据库字段类型不匹配，将进行类型转换以使作数兼容。
mysql> SELECT 1+'1';
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
也可以使用 CAST（） 函数将数字显式转换为字符串。 转换隐式发生在 CONCAT（） 函数，因为它 需要字符串参数。
mysql> SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, '38.8'
mysql> SELECT 38.8, CONCAT(38.8);
-> 38.8, '38.8'这种情况还有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，虽然使用了单引号或双引号。
(六) where子句中的or
查询条件使用or关键字，其中一个字段没有创建索引，则会导致整个查询语句索引失效； or两边为“>”和“<”范围查询时，索引失效。
(七) order by 导致索引失效
当查询条件涉及到order by、limit等条件时，是否走索引情况比较复杂，而且与Mysql版本有关，通常普通索引，如果未使用limit，则不会走索引。order by多个索引字段时，可能不会走索引。其他情况，建议在使用时进行expain验证。
八、索引额外知识
(一) 索引条件下推 index condition pushdown
ICP 技术是 MySQL5.6 引入的一种索引优化技术。它能减少在使用二级索引过滤 where 条件时的回表次数。
要想深入理解 ICP 技术，必须要先知道数据库是怎么处理 where 中的条件，对于 where 中过滤条件处理，根据索引使用情况可以分为三种：index key，index filter，table filter。
index key：用于确定 SQL 查询在索引中的连续范围。
index filter：在 index key 之后，如果还有一些记录是不符合 where 条件的，但这些条件还可以用索引进行过滤，那就是 index filter。
table filter：where 中不能使用索引进行处理，只能全表扫描然后再过滤。
// 索引下推默认是开启的
set optimizer_switch='index_condition_pushdown=off'; // 关闭
set optimizer_switch='index_condition_pushdown=on'; // 开启在 MySQL5.6 之前，并不区分 Index filter 和 Table filter，统统将 index key 回表读取到完整记录返回 MySQL Server 层进行过滤，而在 MySQL5.6 之后，Index filter 和 Table filter 分离，Index filter 下降到 InnoDB 的索引层面进行过滤，其实所谓的 ICP 技术就是利用了 Index filter 技术而已。因为 MySQL 架构的原因，分成了 server 层和引擎层，才有所谓“下推”的说法。我们来画个图就明白了。
没开启索引条件下推查询如下：（画的有点潦草，看得懂就行了）
开启了索引条件下推之后：
相信看得懂大部分读者都能看得懂这两张图，那我们就得出一个结论：索引条件下推只能存在于联合索引里。
(二) 索引合并 index merge
深入理解索引合并是使用索引进行优化的重要基础之一。理解了 index merge，我们才知道应该如何在表上建立索引。
为什么会有 index merge 呢？因为where 中会有多个条件，涉及到多个字段，这些字段有可能不是联合索引，它们直接进行 AND 或者 OR，那么此时就有可能使用到 index merge 技术。其实 index merge 往简单了说，就是把多个索引进行条件扫描，然后把它们的结果进行合并或者交集处理（union/intersect）。
MySQL5.0 之前，一张表一次只能使用一个索引，无法同时使用多个索引进行条件扫描。但是从 5.1 开始，引入了 index merge 优化技术，对同一张表可以使用多个索引进行条件扫描。
index merge 根据合并算法的不同分成了三种，intersect,union,sort_union.
intersect:多个索引条件扫描得到的结果进行交集运行，多半用于 AND 运算才会出现 index intersect merge，但出现这个可不是什么好事，这意味着你的索引建的有点不太合理，既然都是 AND，那可以建成组合索引。
union：多个索引条件扫描对得出的结果进行并集运行，多半用于 OR 运算。
sort_union:多个条件扫描进行 OR 运算，但是不符合 index union merge 算法，此时可能会使用 sort_union 算法。使用条件过于苛刻，它必须保证从各个索引中扫描到的记录的主键值是有序的。
(三) 索引潜水Index dive
索引潜水会读取索引来获取统计信息，速度慢，但是估计准确。
(四) 索引统计Index statistics
MySQL 会定期收集和存储索引的统计信息，优化器在需要的时候直接使用这些存储的统计信息，默认是插入数据超过表的 10%就会触发重新统计，当然也可以手动执行。速度快，但是估算不准确。
什么时候用 index dive，什么时候用 index statistice，由 eq_range_index_dive_limit 决定。
设置eq_range_index_dive_limit = 0：始终使用index dives
设置eq_range_index_dive_limit = 1：始终使用index statistics
设置eq_range_index_dive_limit = N（N>0）：in的个数<N时，使用index dives，in的个数>=N时，使用index statistics
(五) 索引跳跃扫描Index skip scan
Index skip scan 之前学 Oracle 的时候就有学到过，没想到 MySQL8.0 开始也有了。不愧是被 Oracle 收购了，啥时候也把 AWR 也开源下。
Index skip scan 是个啥呢？别急，听我慢慢道来，比如说我们有一张表 A，表 A 上的字段 a1 和 a2 建了组合索引，但是查询的时候只有 a2 这个条件，这时候走的应该是全表扫描或者索引全扫描，如果我们想让执行计划走组合索引怎么办。Index skip scan 就是在这样的背景下诞生了，但是它也有使用条件的。
如果我们组合索引的左键重复值比较多，就可以使用 Index skip scan。 Index skip scan 其实就是 MySQL8.0 推出合适组合索引左边唯一值较少的一种优化策略。
select * from t1 where rank2 >400
MySQL通过Index skip scan改写成
select * from t1 where rank1=1 and rank2 > 400 union all select * from t1 where rank1 = 5 and rank2 > 400九、怎么更好创建索引和使用索引
(一) 只为用于搜索，排序，分组的列创建索引
比如说我们有以下一个查询语句：
select key1,key2,key3 from single_table where key1=1,查询条件只有 key1，那么 key2 和 key3 就不需要建索引。
(二) 考虑索引不重复值的个数
我们建索引时，还要考虑该列的不重复值占比多少，如果比例太低，说明该列包括太多重复值， 那么通过二级索引加回表的方式查询时，就有可能执行太多次回表操作。
(三) 索引列的类型尽量小
数据类型越小，索引占用的存储空间就越小，在一个数据页能存储的数据就越多，从磁盘加载数据到 bufferpool 就越多，磁盘 I/O 带来的性能损耗就越小。
(四) 为列前缀建立索引
一个字符串由若干个字符组成，如果在 MySQL 中使用 utf8 字符集存储字符串，需要 1-3 个字节来编码一个字符。如果字符串很长，那么在存储这个字符串就需要占用很大的存储。我们可以把字符串前几个字符存放到索引中，
alter table single_table add index index_key1(key1(10));
(五) 覆盖索引
有一道面试题是这样的，为什么不推荐使用 select *，而是把需要列名写上。当时说是什么 SQL 优化，存粹是扯淡。
这是为了业务而考虑的，比如说一张表有一个列是存放一张图片的 base64 信息，而业务是不需要这个值的，难不成你也要返回吗？说是 SQL 优化还要考虑具体场景，查询的值是不需要回表的情况下确实可以提高效率。比方说下面的语句：
select id,key1 from single_table where key1 > 'a' and key1 < 'd';
我们只查询 id 和 key1 这两个值，就可以直接从二级索引记录读取到，不需要通过 id 值到聚簇索引中执行回表操作，这样就省去了回表带来的性能损耗，我们把这种索引中已经包括所有需要读取的列的查询方式称为覆盖索引。
(六) 新插入记录时主键大小的影响
索引是有序的，比方说一个数据页存放的 id 顺序是这样子的，1，2，3，4，5，6，7，20，30。但此时有一个 id 为 9 的数据插入进来，但这个数据页已经满了，那么我们就需要把这个页的一些记录移动到新的页，好让 9 可以插入到这个页上，这种现象称之为页分裂。这样就有性能损耗了。如果想避免这种情况，最好就是让插入的记录的主键依次递增。
(七) 冗余和重复索引
比如说有以下查询语句：
select key1,key2,key3 from single_table where key1 = 12;
这张表上已经有key1,key2,key3 建立的组合索引，此时再给 key1 单独建索引就没必要了
(八) 不要改变索引的列类型
比如以下语句，emp_no 是 int，但是我们把它转成 char 类型，这个时候就会导致索引失效，因为这个时候已经破坏了索引的有序性。
select * from employees.titlest where CAST(emp_no AS CHAR) > 10001;
我们再来看看下面这个例子，这个语句会不会跟上面一样导致索引失效呢。答案是不会的，因为优化器会进行隐式转换，但我们也要尽可能避免这种写法。
select * from employees.titlest where emp_no > "zxc";
参考资料
评论