MySQL索引
前言：
索引的目的是为了提高数据查询的效率，就像书的目录一样。同样，对于数据库的表而言，索引其实就是它的“目录”
索引可以让服务器快速地定位到表的指定位置
索引是存储引擎实现的，用于快速找到一条记录的一种数据结构。
所以索引对于良好的性能来说非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列。
索引优点
1、索引大大减少了服务器需要扫描的数据量
2、索引可以帮助服务器避免排序和临时表
3、索引可以将随机IO变为顺序IO
索引是否适合某个查询的三星评级：
1、索引将相关的记录放到一起
2、索引中的数据顺序和查找中的顺序一致
3、索引中的列包含了需要的全部列
一、B-Tree索引
它使用B+Tree（平衡多路搜索树）的数据结构来存储数据。每一个叶子节点都包含指向下一个叶子节点的指针，从而方便叶子节点的范围遍历。
B+Tree采用多叉树结构，降低了索引结构的深度，避免传统二叉树结构中绝大部分的随机访问操作，从而有效减少了磁盘磁头的寻道次数，降低了外存访问延迟对性能的影响。它保证树节点中键值对的有序性，从而控制search/insert/delete/update操作的时间复杂度在O(log(n))的范围内。鉴于上述优势，B+树作为索引结构的构建模块，被广泛应用在大量数据库系统和存储系统中，其中就包括MySQL。
更多B-Tree和B+Tree查看博客：https://www.cnblogs.com/yangyongjie/p/10610176.html
B树的每个节点对应innodb的一个page，page大小是固定的，一般设为16k。 其中非叶子节点只有键值，叶子节点包含完整数据。
B-Tree索引意味着所有的值都是按顺序存储的，并且每一个叶子页到跟的距离相同。B-Tree索引能够加快访问数据的速度，因为存储索引不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找；通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么是找到对应的值，要么该记录不存在。叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。
注意：B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据；若是联合索引，则索引对多个值进行排列的依据是创建索引时列的顺序，所以索引还可以用于查询中的ORDER BY操作。
可以使用B-Tree索引的查询类型：全键值、键值范围、键前缀查找（只适用于根据最左前缀的查找，最左原则），所以B-Tree对如下类型的查询有效：
（1）：全值匹配
和索引中的所有列进行匹配，查询条件和索引的所有列相同且顺序相同，且查询条件为相等
（2）：匹配最左前缀
只使用索引中的第一列
（3）：匹配列前缀
也可以只匹配某一列的值的开头部分
（4）：匹配范围值
对索引列进行范围匹配
（5）：精准匹配某一列并范围匹配另外一列
但是如果查询中有某个列的范围查询，则其右边所有列都无法使用索引进行查询
（6）：只访问索引的查询
即查询只需要访问索引，而无须访问数据行（覆盖索引）
二、索引失效的场景
1、被索引字段使用了表达式计算或函数。所以被索引字段不能是表达式的一部分，也不能是函数的参数。
2、被索引字段发生了隐式类型转换。MySQL在SQL语句执行过程中会自动地将SQL语句中与原字段类型不匹配的值进行类型转换，如被索引字段是字符串类型，而查询时条件使用的是数字类型，MySQL就会隐式地将条件中的数字类型转换为字符串类型
原因：索引的使用依赖于对整个BTree索引数的遍历，而索引树的遍历依赖于索引数底层叶子节点的有序性；如果被索引字段使用了表达式计算，函数计算，隐式类型转换后，有可能这个字段新的排列顺序和原来在索引树的叶子节点层的排列顺序不一样了；当SQL语句被执行时，SQL执行器无法判断原来的索引树是否还能被检索使用，索引SQL执行器会放弃使用该索引。
3、在like关键字后使用了左模糊匹配，'%XXX'。
4、被使用的索引字段不是联合索引的最左字段。
原因：MySQL中索引树的检索遵循了最左匹配原则，BTree索引叶子节点的有序性也是建立在最左匹配的基础上的，如果直接使用索引键的中部或者后部进行SQL查询，就违背了最左匹配原则，导致SQL执行器无法使用索引树进行检索，索引SQL执行器会放弃使用该索引。
但是，如果查询过程中发生了索引覆盖，这几种情况还是会使用索引的。
5、如果MySQL使用某个索引进行范围查询（>,<,between），也就无法再使用另外一个索引（或是该索引的后续字段）进行排序了
三、创建高性能的索引
索引选择度： 不重复的索引值和表记录总数的比值，最大为1，索引的选择度越高则查询效率越高。因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引和主键索引的选择度为1，所以唯一索引和主键索引的性能是最好的。
1、前缀索引和索引选择度
为一个很长的字符串列创建索引，如果将此列字符串全部索引，那么会将字符串变得大且慢。通常的做法是索引开始的部分字符， 这样可以大大节约索引空间，从而提高索引效率。
对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引。对于这类列创建索引要选择足够长的前缀以保证较高的选择性，同时又不能太长。前缀索引是一种能使索引更小，更快的有效办法，但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
2、联合索引
Where条件有多个列的情况下，如果为每列都建立一个单独的索引，在5.0及之后的版本可以通过使用索引合并的策略，一定程度上可以使用多个单列索引来查找定位指定的行。但在更早的版本只能使用其中一个单列索引。如where A or B，A和B都有一个单列索引，在5.0之前，MySQL会使用全表扫描。5.0及之后的版本中，查询能够同时使用这两个单列索引进行扫描，并将结果合并。where条件中的每个列都是单列索引的话，有以下几种情况：
1、OR条件的 union，EXPLAIN的Extra列可以看到 Extra: Using union(A,B); Using where，会耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上
2、AND 条件的 相交，这时表示需要创建一个联合索引。
3、AND和OR条件都出现
联合索引的索引列的顺序问题？
在一个多列BTree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或降序进行扫描，以满足精确符合列顺序的ORDER BY，GROUP BY和DISTINCT等子句的查询需求。所以，联合索引的列顺序至关重要。
原则：将选择性最高的列放在索引最前列。但不是绝对的。可以通过跑一些查询来确定在这个表中值的分布情况，并确定哪个列的选择性更高。如：select count(distinct columnName)/count(*) from tableName。另外 ，还要考虑Where子句中的排序、分组和范围条件等其他因素。如虽然某列选择性不是最高的，但是这列作为单独的条件出现的很频繁，可将此列作为联合索引的最前列。
如果查询条件中第一列是唯一标识，也就是说选择性为1，那么where条件后的查询就不必建立联合索引了。
四、聚集索引
聚集索引并不是一种单独的索引类型，而是一种数据存储方式。
InnoDB的聚集索引实际上就是在同一个结构中保存了BTree索引和数据行。因为无法同时将数据行存放在两个不同的地方，所以一个表只能有一个聚集索引。也可以理解为聚集索引就是表。
当表有聚集索引时，它的数据行实际上存放在索引的叶子页中。聚集表示数据行和相邻的键值紧凑地存储在一起。
聚集索引的每一个叶子节点都包含了主键值，事务ID，用于事务和MVCC的回滚指针以及所有的剩余列。
InnoDB通过主键聚集数据，也就是说被索引的列是主键。如果没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚集索引。
优点：
1、可以将相关数据保存在一起，通过
2、数据访问更快，聚集索引将索引和数据保存在同一个BTree树中。
3、用主键值查询即可使用覆盖索引
4、减少了当出现行移动或者数据页分裂时二级索引的维护工作，在移动行时无需更新二级索引叶子节点保存中的主键值。
缺点：
1、数据插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。
2、更新聚集索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
3、基于聚集索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能会面临 “页分裂” 的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳改行。
4、聚集索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
5、二级索引可能比想要的要大，因为在二级索引的叶子节点包含了引用行的主键列。
二级索引需要两次索引查找，因为二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值（并以此作为指向行的“指针”）。所以通过二级索引查找行，存储引擎需要找到二级索引的叶子节点获得对用的主键值，然后根据这个值去聚集索引中查找到对应的行（回到主键索引树搜索的过程，我们称为回表 ）。需要进行两次BTree查找。
与MyISAM存储引擎的区别：
MyISAM是非聚集存储引擎，其按照数据插入的是顺序存储在磁盘上。MyISAM的主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引
主键的选择：
1、推荐使用自增列作为主键，这样可以保证数据行是按顺序写入，对于主键做关联的性能也会更好。
在插入数据时将每条记录都存储在上一条记录的后面，当达到页的最大填充因子时（InnoDB默认的最大填充因子是页大小的15/16，留出部分空间用于以后修改），下一条记录就会写入到新的页中。
2、避免随机的（不连续且值的分布范围大，如UUID）数作为主键，会导致聚集索引的插入变得完全随机，会导致页分裂和碎片，造成插入行的耗时更长；另外主键字段更长，会导致索引占用的空间也很大。
在插入数据时，因为新行的主键值不一定比之前的大，所以InnoDB无法简单地总是把新行插入到索引的最后，而是需要为新的行寻找合适的位置，如果是已有数据的中间位置。
缺点：
（1）、在插入之前要为主键寻找目标页，因此要先在磁盘找到并从磁盘读取目标页到内存中，会导致大量的随机IO
（2）、因为写入是乱序的，InnoDB不得不频繁地做页分裂操作，以便为新的行分配空间，页分裂会导致移动大量数据，一次插入最少得修改三个页而不是一个页
（3）、由于频繁的页分裂，页会变得稀疏并被不规则地填充，所以最终数据会有碎片。因此在把数据插入到聚集索引之后，也许需要做一次OPTIMIZE TABLE来重建表并优化页的填充。
五、覆盖索引
通常我们会根据WHERE条件来创建合适的索引，不过设计优秀的索引应该考虑到整个查询，而不仅仅是WHERE条件部分。
如果一个索引包含（或者说覆盖）所有查询的字段的值，就称为覆盖索引。即索引的叶子节点中包含了所有要查询的列，不需要再通过主键索引查询数据行（回表查询）。
优点：
1、索引条目远小于数据行大小，如果只需要读取索引，那MySQL就会极大地减少数据访问量，减少缓存的负载和IO读取次数
2、因为索引是按照列值顺序存储的，范围查询会比随机从磁盘读取每一行的数据的IO要少得多
3、InnoDB的二级索引在叶子节点保存了行的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。
当发起一个被索引覆盖的查询，在EXPLAIN的Extra列可以看到Using index。
覆盖索引只能覆盖那些访问索引中部分列（可以包含主键）的查询。
应用：利用延迟关联和主键覆盖索引解决分页查询页码很大查询的问题
分页如果翻页到比较靠后时查询也可能缓慢，如：LIMTI 100000,10; 因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获得需要的行。这样可以减少MySQL扫描那些需要丢弃的行数。如：
select xxx from profiles INNER JOIN select <primary key col> FROM profiles where x.sex='M' ORDER BY rating LIMIT 100000,10) as x USING (<primary key col>);
六、使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果：1、通过排序操作 2、按索引顺序扫描（EXPLAIN的type列为 index）
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但是如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机IO，因此按索引读取数据的速度通常要比顺序地全表扫描慢。
所以希望MySQL可以使用一个索引既满足排序，又用于查找行。
只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。
如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时（MySQL优化器默认JOIN表为第一张表），才能使用索引做排序，ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行文件排序（filesort）操作，而无法利用索引排序。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候，如果where子句或者ORDER BY子句中对这些列指定了常量，就可以使用索引进行排序。如：表rental表有组联合索引idx_rental(rental_date,inventory_id,customer_id)，则sql：select xxx from rental where rental_date='2019-04-14' ORDER BY inventory_id,customer_id；可以使用索引idx_rental排序。即使ORDER BY子句不满足索引的最左前缀的要求，这是因为索引的第一列被指定为了一个常数。
七、索引和锁
索引可以让查询锁定更少的行。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外的开销；其次，锁定超过需要的行会增加锁争用并减少并发性。
InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数（因为无需全表扫描），从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才会有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后，MySQL服务器才能应用WHERE子句。这时已经无法避免锁定行了：因为InnoDB已经锁定了这些行，到适当时候才会释放。在MySQL 5.1之前的版本中，InnoDB只有在事务提交后才能释放锁。之后的版本，InnoDB可以在服务端过滤掉行后就释放锁。
即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话可能会更糟糕，MySQL会做全表扫描并锁住所有的行，而不管是不是需要。
关于InnoDB、索引和锁有一些鲜为人知的细节：InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。这消除了使用覆盖索引的可能性，并且使得SELECT FOR UPDATE比LOCK IN SHARE MODE或非锁定查询要慢得多。
八、索引设计的原则
1、索引不是越多越好，避免冗余索引
表中的索引越多插入速度会越慢，一个索引对应一个B+树，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢（需要维护和更新索引树）
2、考虑表上所有的选项，在优化查询和索引之间找到最佳平衡
当设计索引时，不要只为现有的查询考虑需要哪些索引，还需要考虑对查询进行优化。如果发现某些查询需要创建新索引，但是这个索引又会降低另一些查询的效率，那么应该想一下是否能优化原来的查询，应该同时优化查询和索引找到最佳的平衡，而不是闭门造车去设计最完美的索引
3、尽可能重用索引而不是建立大量的组合索引
4、尽可能将需要做范围查询的列放到索引的后面，以便优化器能使用尽可能多的索引列
5、重要的SQL必须被索引，核心SQL优先考虑覆盖索引
6、选择性高的字段放在前面
7、
九、优化查询
1、避免多个范围条件， 对于范围条件查询（>,<,between），MySQL无法再使用范围列后面的其他索引列了，但是对于 “多个等值条件查询（in）” 则没有这个限制。
对MySQL来说，null是一个特殊的值，Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。比如：不能使用=,<,>这样的运算符，对null做算术运算的结果都是null，count时不会包括null行等，null比空字符串需要更多的存储空间等。
QAQ
1、联合索引是怎么实现的？
2、 使用多个索引的查询规则
3、临时表是什么情况下出现
4、where条件多列都有单列索引，SQL执行器怎么选择索引？是否只能使用一个索引？
5、MySQL 5.6的索引提交推送（解决旧版本的存储引擎API不允许将过滤条件传到存储引擎层，现在只能把数据从存储引擎拉到服务器层，再根据查询条件过滤 ）
6、in会使用索引，为什么in()可以代替范围查询，优化器怎么做组合的
避免多个范围条件， 对于范围条件查询，MySQL无法再使用范围列后面的其他索引列了，但是对于 “多个等值条件查询（in）” 则没有这个限制。
7、优化一个选择性低的列做排序，
九、索引合并（index merge）
官方文档：The Index Merge access method retrieves rows with multiple range scans and merges their results into one. This access method merges index scans from a single table only, not scans across multiple tables. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans.
https://dev.mysql.com/doc/refman/5.6/en/index-merge-optimization.html
1、索引合并是把几个索引的范围扫描合并成一个索引。
2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。
如果where中可能有多个条件（或者join）涉及到多个字段，它们之间进行AND或者OR，那么此时就有可能会使用到index merge技术。
如果出现了索引合并，EXPLAIN关键字输出的执行计划type列会显示 index_merge，key列展示使用的所有的索引，key_len展示的用到的索引中长度最长的索引的长度。
索引合并的方式分为三种：intersection、union和sort_union
1、union，Extra显示Using union(A,B)；多个索引条件扫描得到的结果进行并集运算，多个条件之间进行的是 OR 运算时出现。
总结：只要通过索引取出的数据已经按rowid进行了排序，就可以使用union
2、 intersection，Extra显示Using intersect(A,B)；多个索引条件扫描得到的结果进行交集运算，在多个索引列之间是AND运算时出现。
3、sort_union，Extra显示Using sort_union(A,B)；多个条件扫描进行 OR 运算，但是不符合 index union merge算法的，此时可能会使用 sort_union算法
优点：
索引合并，让一条sql可以使用多个索引。对这些索引取交集，并集，或者先取交集再取并集。从而减少从数据表中取数据的次数，提高查询效率。
注意：
1、相同模式的sql语句，可能有时能使用索引，有时不能使用索引。是否能使用索引，取决于mysql查询优化器对统计数据分析后，是否认为使用索引更快。因此，单纯的讨论一条sql是否可以使用索引有点片面，还需要考虑数据。
2、index merge使得我们可以使用到多个索引同时进行扫描，然后将结果进行合并。听起来好像是很好的功能，但是如果出现了 index intersect merge，那么一般同时也意味着我们的索引设计得不太合理，因为 index intersect merge 是可以通过建立 复合索引进行更一步优化的。
3、另外index merge还会造成死锁的可能，如：事务1持有主键锁，等待二级索引idx_order上的锁，而事务2持有二级索引idx_order上的锁，等待主键锁，从而造成死锁。
十、总结
编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询
应该根据我们理解的索引的工作机制来创建最合适的索引，而不是如：1、在多列索引中将选择性最高的列放在第一位 2、为WHERE子句中出现的所有列创建索引
优化一个耗时的查询，判断是否扫描了太多的行，是否做了很多额外的排序或者使用了临时表，是否使用随机IO访问数据，或者是否有太多回表查询那些不在索引中的列的操作
如果一个查询无法从所有可能的索引中获益，则应该看看是否可以创建一个更合适的索引来提升性能。如果不行，也可以看看是否可以重写该查询，将其转化成一个能够高效利用现有索引或者新创建索引的查询。
END.
