#来点儿干货#
当身为开发工作人员的人们，遭遇了执行时长比较久的sql之际，大体上来看大家基本上都会讲出”添加一个索引吧”这样的话语。然而索引究竟是什么样的事物呢，索引具备怎样的特性呢，接下来要和大家略微谈论探讨一番。
1 索引如何工作，是如何加快查询速度
索引，它等同于书本的目录，是能提升数据库的表数据访问速度的数据库对象。当我们的请求输入进来以后，要是存在目录，便会迅速地定位到章节，而后从章节当中找到数据。要是不存在目录，那就如同大海捞针一样，其难度显而易见。这便是我们时常遭遇的罪魁祸首，全表扫描哦。
包含在一条索引记录里的所含基本信息有：键值，也就是你在定义索引之际所指定的全部字段的值，加上逻辑指针，它指向数据页或者另一索引页。通常情形之下，鉴于索引记录只含有索引字段值，以及4至9字节的指针，索引实体比真实的数据行小好多，索引页相较于数据页而言要密集好多。一个索引页能够存储数目更多的索引记录，这表明在索引里查找之际在I/O方面占据相当大的优势，明白这一点有益于从根本上知晓运用索引具备的优势，还是大部分性能优化需要迈入的要点。
1）没有索引的情况下访问数据：
2）使用平衡二叉树结构索引的情况下访问数据：
关于第一张图，因未使用索引，我们会展开顺序查找，会依照数据具有的顺序，逐个去进行匹配，历经5次寻址操作，才将所需数据查询出来，而第二张图，借助了一个简单的平衡二叉树索引后，我们仅仅用了3次，这还是处于数据量较小的情形下，要是数据量增大，效果会更为显著，所以总括地讲，创建索引的目的便是为了加快数据查找的速度。
2 索引的组成部分和种类
有许多种是常见的索引的实现采取的方式，像hash，像数组，像树，接下来给各位讲一讲这几种模型在使用方面存在什么样的差异。
2.1 hash
hash的思路是挺简单的，它是要将我们所插入的key借助hash函数算法来计算出对应的value，以前通常是采用取余数这类方式，就像计算方式移位异或之类的，计算出来对应的value后把它放置到一个位置，这个位置就被称作哈希槽，然后把对应磁盘位置指针放进hash槽里面。一句话来总结hash索引，那就是其存储了索引字段的hash值以及数据所在磁盘文件指针。
然而，无法避免的是，不管是何种算法，一旦数据量增大，总归会出现不同的数据被安置于同一个hash槽之内的情况。举例来说，字典里的“吴”与“武”读音相同，当你去查字典时，到了此处就只能依次往后查找。索引的处理亦是这般，会形成一个链表，在需用时按顺序进行遍历便可。
2.2 有序数组
要是当我们处在需要进行区间查询的状况之下，hash索引所具备的性能就难以令人满意了。而在这样的时候，有序数组所拥有的优势便能够展现出来了。
当我们有从一个呈现有序状态的数组里获取处于A与B之间数值的需求时，仅需借助二分法来确定A所在的位置，其时间复杂度为O(log(N))，然后从A开始逐一遍历直至B就行，就速度方面而言，大体上能够说是最为快捷的了。然而当我们要进行更新操作时，所要开展的操作就会有许多。要是需要插入一个数据，你得移动该数据之后的所有数据，这会造成性能的浪费。所以总的来说，仅有不怎么发生变化的数据才适宜有序数组结构的索引。
2.3 二叉搜索树
基本原则是树的左节点都小于父节点，右节点都大于父节点
在此处我们能够瞧得出来，二叉搜索树的查询效率从原则上而言是O(log(N))，为了确保是平衡二叉树，其更新效率同样是O(log(N))。然而在数据量众多的情形下，树的高度会攀升至很高的程度，过多地去访问磁盘，这是不可取的行为。并且在极端状况之下，树会退化成链表，其查询的复杂度会被拉扯成为O(n)。
变成多叉树，就是有多个子节点的情形下，会极大地削减树的高度，使得访问磁盘的操作得以降低。
2.4 B树
在各个节点当中存储多个元素，于每个节点尽可能多地去存储数据。每个节点能够存储1000个索引（因为16k除以16等于1000），如此一来便把二叉树改造成了多叉树，借助增加树的叉数，把树从高瘦模样变为矮胖形态。构建1百万条数据 ，树的高度仅仅需要2层就行（1000乘以1000等于1百万），这意味着仅仅需要2次磁盘IO就能够查询到数据。磁盘IO次数减少了，查询数据的效率便可提高了。
这种数据结构我们称为B树，B树是一种多叉平衡查找树
2.5 B+树
B+树和B树最主要的区别在于非叶子节点是否存储数据的问题。
正是由于B + 树的叶子节点是以链表相连接的，进而在找到下限之后能够迅速开展区间查询，其速度相较于正常的中序遍历要快。
3 索引的维护
当你面对一条数据之际，索引得开展必要的举措以保证数据的有序性。通常自增数据直接于后面添加上即可，但在特殊情形下要是数据添加于中间位置上，便是须挪动后面的所有数据，这样一来效率会受到比较大的影响。
在最糟糕之际，若当下的数据页（此页乃mysql存储最小单元）已满，便需申请一个全新的数据页，而这一过程被称作页分裂。倘若致使了页分裂，必然会造成性能方面的影响。然而，mysql并非盲目地进行数据分裂，要是你是从中间着手数据分裂，对于自增主键，将会导致一半的性能被浪费。Mysql 会依据你索引的类型，还会根据追踪插入数据的情形，来决定分裂方式，一般存在于 mysql 数据页的 head 里面，要是零散地进行插入，就会从中间分裂，若为顺序插入，通常会选择从插入点开始分裂，或者是插入点往后几行所导致的那种情况，以此决定是从中间分裂，还是从最后分裂。
若是插入的为不规则数据，没办法确保后一个值比前一个是大的，那样就会触发上面所讲的分裂逻辑，最终达成下面那样的效果。
所以为数众多的情形之下，我们都需运用自增索引，除非存在业务自定义主键的需求，最好确保只有唯一一个索引，并且该索引属于唯一性索引。如此一来能够规避回表，致使出现查询搜索两棵树的状况。保障数据页具备有序性，可以更为妥善地运用索引。
4 回表
那么通俗来讲，就是这样一种情况。当索引的列处于想要获取到的列范围之内时，这是一种情况。因为在mysql里索引是按照索引列的值实施排序的，所以索引节点里存在该列内的部分值，这又是一种情况。在这两种情况下，若根据一次索引查询便能获取到记录，那就不需要回表操作。然而在另一种情况下，如果所需获取列之中有大量的并非索引的列，这是一种情况。此时索引就必须先去寻找到主键，这又是一种情况。然后再到表里面找到对应列的信息才行，像这样的操作就被称作回表。
要介绍回表自然就得介绍聚集索引和非聚集索引
集聚索引的叶片结点寄存行纪录，所以，一定要有，并且仅有一个集聚索引。
当我们运用普通索引查询方式时，先是要搜索普通索引树，接着在获取主键ID后，又要到ID索引树再搜索一回。这是由于非主键索引的叶子节点当中，实际存放的是主键的ID。此过程虽说运用了索引，然而实际上在底层进行了两次索引查询，这样的过程就被称作回表。也就是说，基于非主键索引的查询需要多扫描一棵索引树。所以，我们在应用里应当尽量采用主键查询。或者在有高频请求时，合理构建联合索引，以防止回表。
5 索引覆盖
从一句话角度来讲，是仅在一棵索引树上就可获取SQL所需全部列数据，不用回表，速度更加快。落实到sql方面，只要执行计划里输出结果的Extra字段是Using index时，就能引发索引覆盖。
平时常见的那种优化办法，是上面所提及的，把查询涉及的字段全都建立到索引当中，至于数据库管理员愿不愿意让你去建，这就得靠你们自己了。
全表 count 查询优化，列查询回表，分页回表，这些场景是一般索引覆盖适用的。高版本的 mysql 已做优化，若命中联合索引当中的一个字段，并且另一字段是 id，便会自动优化，无需回表。二级索引树叶上存有 key，因也算索引覆盖，所以无需额外成本。
6 最左匹配原则
简而言之，当你运用‘xx%’之际，若符合相应条件，同样会启用索引。
要是属于联合索引这种情况，我来进行举例说明，去创建一个联合索引，该联合索引是（a,b）所构成的。
能够看到，a的值呈现出有顺序的状态，分别是1，1，2，2，3，3，然而b的值却处于没有顺序的情形，为1，2，1，4，1，2。但我们又能察觉，在a处于等值的状况下，b值又会按照顺序进行排列，不过这种顺序是相对而言的。原因在于，MySQL创建联合索引的规则为，首先会针对联合索引最左边的第一个字段展开排序，在基于第一个字段排序后，接着再对第二个字段予以排序。所以，b = 2这种查询条件无法借助索引。例如，我设置一个索引。
KEY (,) USING BTREE
执行第一条sql,全表扫描
执行第二条sql，可以看到使用了索引。
去再瞧一瞧两条sql，所构建的索引乃是KEY (,) 运用BTREE方式。
依照正常的逻辑来讲，第二条sql并不符合索引字段给定的顺序情况，按道理应该是不能够使用索引才对，然而实际出现的状况却和我们内心所期望的情形不太一样，这究竟是出于什么原因？
自mysql被收购之后，mysql增添了诸多曾经的技术，高版本的mysql对where条件的先后顺序进行了自动优化。简言之，乃是查询优化器实施了这一操作，sql会开展预处理，哪一条能够实现更好查询便会采用那种规则。
顺便提一下mysql的查询优化器能帮忙干的一些事
6.1 条件转化
如若存在这样一种情况，即where a等于b且b等于2，那么便能够得出a等于2，这属于条件传递。最终所形成的sql体现为，a等于2且b等于2，大于、小于、等于、像这样的情况均能够进行传递。
6.2 无效代码的排除
打个比方，像那种where 1等于1而且a等于2的情况，1等于1始终是正确无误的，因而到最后就会被优化成a等于2。
例如，where 1=0这种永远为假的情况，也会被排除掉，使得整个sql无效。
或者，那些并非为空的字段，在“where a is null”这种状况下，同样是会被排除掉的。
6.3 提前计算
那种涵盖数学运算的部分，比如说在=1+2的情形下会替你计算妥当，=3。
6.4 存取类型
在我们对一个条件表达式予以评估之际，MySQL会去判定该表达式的存取类型，以下是一些存取类型且是依照从最优至最差的顺序来进行排列的 ，这是一些存取类型 ，它们是按照从最优到最差的顺序展开排列。
经常看执行计划的，一眼就能看出来这是啥意思，举个例子
哪里等于2并且这个还等于3，这儿就将选用等于2会当作驱动项。驱动项所表示的意思是说，在一个sql确定他的执行计划之际，或许会有多条执行路径情形出现，其中一条是展开全表扫描，接着去过滤是不是契合索引字段以及非索引字段的值。另外一种是借助索引字段，键值等于2去寻找到对应的索引树相关情况，经过过滤之后得出的结果，然后再去比较是不是符合非索引字段的值相关情况。通常状况下，选择用索引进行处理往往都比全表扫描所需要读取磁盘的次数要少，所以把它称作是更好的执行路径，也就是凭借索引字段，将其作为驱动表达式。
6.5 范围存取
简而言之，a处于（1，2，3）之中，与a等于1或者a等于2或者a等于3是相同的，1与2，还有a大于1同时a。
6.6 索引存取类型
切勿使用有着相同前缀的索引，这意味着一个字段在多个索引里不能存在相同的前缀。举例来讲，当一个字段已然建立了唯一索引，在这种情况下要是再为其创建一个联合索引，就会致使优化器根本不清楚您打算使用哪一个索引。又或者您构建了前缀一样的一个单索引、一个联合索引，哪怕您写明了条件，也不见得能够用到联合索引。当然，倘若进行强制使用，那就另当别论了。
6.7 转换
简单的表达式能够予以转换，像where-2=a会自行转变成为where a=-2，然而要是涉及到数学运算，那就无法进行转换了，像where 2=-a此时不会自动转变成where a=-2。
第二条sql就可以使用索引
所以，在我们进行开发期间，要留意sql语句的书写方式，自觉地将其写成where a等于负二。
我不太明确你具体的需求，你提供的内容似乎不完整且表述不太清晰。请你详细明确或重新准确描述一下问题，这样我才能更准确地按照要求进行改写。
若and条件之后，均不存在索引，那么便会扫描全表。存在一种存取类型更为优良，详情可见5.4 ，此时会运用存储类型更为优良的索引，要是皆相同，哪一个索引先被创建，就使用哪一个。
2）union
union 每条语句单独优化
这里就会分别执行两条sql，用到索引，再合并结果集
3）order by
order by 会过滤无效排序，比如一个字段本来就有索引
第二条sql和第一条的查询效果是一样的
所以，在去做写sql这个行为的时候，可千万不要去写被判定为是无用的排序情况，就好像是那种像order by ‘xxx’ 这种样子的排序，它是不存在任何意义的。
4）group by
简而言之，对于group by的字段而言，若有索引则会走索引，举例说明像group by a order by a这种情况，这里的order by相当于没写，因为结果集已然是排序好的了，参考部分为6.8 - 3中关于order by 的内容。
“col_a from table a”等同于“col_a from a group by col_a” ，它们在表述上有着这样那样的关联，意思大致是相关的，尽管形式上有所不同。
7 索引下推
重点的关键之处在于，将数据筛选的进程置于存储引擎层面予以处理，并非如先前那般放置到某一层面去开展过滤操作，这里的某一层在原句中未明确，推测改后表述的是数据过滤层，实际操作以实际需求为准。
要是在一张表里头，name和age都设置了索引，查询的条件是where name like ‘xx%’ 并且age=11，当处于低版本的mysql（5.6以下）时，依据索引的最左匹配原则，能够做到舍弃age，仅仅借着name来过滤数据，在根据name获取到全部的id之后，再凭借id进行回表。
在高版本的mysql之中，不存在忽略age这个属性的情况，带着age属性进行过滤，直接就把age为11的数据给过滤掉咯。假设有不依据age进行过滤的数据是10条，经过过滤之后就只剩下3条了，如此一来就少了7次回表操作。减少了io会大量地减少性能消耗。
8 小表驱动大表
小表去驱动大表，这也是我们平常听得多了的话语，它的含义主要是讲将小表的数据集拿来驱动大表的数据集，以此减少连接的次数，比如说：
要是表A存有1w个数据，表B存有100w个数据，若将A表当作驱动表，处于循环的外层范畴之内，以此说来仅仅需要经历1w次的连接便可达成。要是把B表置于外层，如此地话那就得循环100w次之多。
下面我们实际测试看看，准备环境mysql 5.7+
先要准备两张表，其中一张表的数据是9175，另一张表的数据是什么暂未明确，然而这两张表在商品编码字段上均有索引。
首先小表驱动大表
多次反复测试，执行时间大概7秒。
接下来看看大表驱动小表。
将近300秒，不是一个量级的。
接下来分别分析执行计划，执行计划里第一条就是驱动表。
数量少的表驱动数量多的表，数量多的表运用了索引，数量少的表进行全表扫描，仅仅扫描了八千多行。
大表驱动小表，大表全表扫描，需要扫描147w行。
经过多次测试得出了结论:
使用left join时，左表作为驱动表，右表作为被驱动表 ，使用right join时，右表是驱动表，左表是被驱动表 ，使用inner join时，mysql会挑选数据量较小的表当作驱动表，大的表视为被驱动表 ，驱动表索引不起作用，非驱动表索引起作用。
保证小表是驱动表很重要。
9总结覆盖索引，若查询条件运用的是普通索引，或者是联合索引的最左原则字段，查询结果为联合索引的字段亦或是主键，无需回表操作，直接返回结果，减轻IO磁盘读写用来读取整行数据的负担，故而高频字段构建联合索引是颇具必要的。最左前缀，是联合索引的最左N个字段，也能够是字符串索引的最左M个字符。在进行索引建立之际，要留意左前缀不可出现重复情况，以防查询优化器难以判定怎样去运用索引，索引下推为：name 像‘hello%’并且 age 大于 10 进行检索，于 MySQL 5.6 版本以前之时，会针对匹配的数据开展回表查询，在 5.6 版本之后，会先将 age 过滤掉。