介绍通过本文我们可以收获：1.熟悉MySQL索引的基础知识：什么是索引常用索引模型InnoDB索引模型索引类型有哪些2.如何使用索引提高开发、DBA、QA在项目过程中对Mysql索引相关操作的技术分析能力。一、背景分享本文旨在提高开发、DBA、QA在项目过程中测试sql和sql变化分析增、改、删索引合理性的能力。二、MySQL索引1、概念描述简单来说，索引的出现其实就是为了提高数据查询的效率。当表数据量很大时，索引的重要性就显得尤为突出。可以理解为索引就像一本书的目录。比如：在一本1000页的书里，如果你想快速找到某个知识点，如果不按目录查找，直接一页一页查找，这样无疑是非常低效的。类似于数据库中的表，索引实际上是它的“目录”。2.普通索引模型哈希表哈希表是一种以键值（Key-Value）格式存储数据的结构。通过输入要查找的Key值，可以找到Key对应的Value。散列的思想比较简单。将value放入数组中，然后通过hash函数将输入的Key值转化为某个位置的值，最后将Value放入数组中的这个位置。因为当输入的多个Key值使用hash函数进行转换时，会把多个Key转换成同一个值，如下图，id1和idn的转换结果都是：x，本例ha表给出的解决方案是拉出一个链表。比如有一个已有的用户表信息，需要根据用户id查找用户名。对应的hash索引图如下：这时，当你想找出id1对应的name是什么时，处理步骤为：首先，通过hash函数将id1传给x。然后依次遍历找到User1，就可以查询到对应的名字了。注：图中id的值不是依次递增的。这样做的好处是添加新User的速度比较快，以后只需要添加即可。但是缺点也很明显，因为它是无序的，所以hash索引对于范围查询来说非常慢。因为需要全表扫描。总结：哈希表结构适用于只有等价查询的场景，比如一些NoSQL（非关系型数据库）引擎。排序数组排序数组在等价和范围查询场景中表现非常好。还是上面的根据用户id查找用户名的例子，如果使用有序数组实现，对应示意图如下：假设这里的id不重复，数组按照id递增的顺序保存,那么如果要查看id2对应的名字，使用二分法可以快速获取。这个的时间复杂度是O(log(N))。这种索引结构可以很好地支持范围查询。比如要查询[idm,idn]区间内User的姓名信息，可以先用二分法查找idm，如果没有idm，则查找第一个大于idm的User，然后向右遍历，直到找到第一个大于idn的id号退出循环。注意：单从查询效率的角度来看，有序数组是最好的数据结构。想一个问题，当这个数据结构遇到更新的数据（插入或删除）会发生什么？比如你删除或者插入一条记录，就会很麻烦，因为插入数据需要将数据的后半部分往后移动一个位置，删除数据需要将数据的后半部分向前移动一个位置，这太昂贵。总结：有序数组索引只适用于静态存储引擎，适用于存储不会被修改的数据。如果二叉查找树还是沿用上面的使用id查询名字的例子，我们来看看二叉查找树的数据结构是如何实现的。对应图如下：二叉查找树的特点：父节点左子树中所有节点的值都小于父节点的值，而右子树中所有节点的值大于父节点的值。如果要查看id2的信息，按照图中的查找顺序，按照UserA—>UserC—>UserF—>User2的路径获取即可，时间复杂度为O(log(N)).树有一个二叉树，它也可以有多个分支。多叉树是指每个节点有多个儿子，儿子之间的大小保证从左到右递增。二叉树是最高效的搜索，但实际上大部分数据库存储并不使用二叉树。原因是索引不仅存储在内存中，还写入磁盘。3.InnoDB索引模型在Mysql中，索引是在存储引擎层实现的，因此没有统一的索引标准，即使使用不同的存储引擎，对应的索引的工作方式也不同。InnoDB存储引擎在Mysql数据库中最常用。让我们看一下InnoDB索引模型。在InnoDB中，表按照主键的顺序以索引的形式存储。这种存储方式的表称为索引组织表，数据存储在B+树中。为什么使用B+树而不是其他数据索引模型？(1)减少磁盘IO次数。B+树的数据结构模型将所有数据放在叶子节点中，叶子节点组成一个列表（可用于范围查询），非叶子节点只存储键值，这样每个数据叶子可以存储更有效的数据，可以有效减少磁盘IO次数。(2)每次查询的时间复杂度是固定的。在B+树中，由于分支节点只是叶子节点的索引，所以搜索任何关键字都必须从根节点到分支节点。所有关键字查询路径的长度同样，每个查询的时间复杂度是固定的。但是，在B树中，数据也存储在它的分支节点上，每次数据查询的路径长度不同，所以查询效率也不同。(3)遍历效率更高，因为B+树的数据存储在叶子节点上，分支节点都是索引，便于扫描数据库，只需扫描一次叶子即可。但是，B树将数据存储在分支节点上。要找到具体的序列数据，需要进行中序遍历才能找到。因此，B+树更适合范围查询。在解决磁盘IO性能的同时，解决了B树元素遍历效率低的问题。索引分类聚类索引主键索引在Innodb中，Mysql中的数据是按照主键的顺序存储的。那么聚簇索引就是根据每张表的主键构造一颗B+树，叶子节点存放整张表的行数据。由于表中的数据只能按照B+树排序，所以一张表只能有一个聚簇索引。在InnoDB中，聚簇索引默认为主键索引。如果表没有设置主键，则按照以下规则创建聚簇索引。当没有主键时，一个唯一的非空索引列将作为主键成为这个表的聚集索引。如果没有这样的索引，InnoDB将隐式定义一个主键作为聚簇索引。比如有一张user表，其主键列为id，表中有字段t和name，在t上有一个索引。建表语句如下：createtableuser(idintprimarykey,tintnotnull,namevarchar(16),index(t))engine=InnoDB;非聚集索引联合索引使用多个列字段来构建索引，称为联合索引，也称为复合索引。联合索引是：(t,name)。建表语句如下：createtableuser(idintprimarykey,tintnotnull,namevarchar(16),index(t),index(t,name))engine=innodb;说到联合索引，就不得不说说最左匹配原则。所谓最左匹配原则，就是如果SQL语句中使用了联合索引中最左边的索引，那么这条SQL语句就可以使用联合索引进行匹配。值得注意的是，当一个范围查询（>、<、between、like）将停止匹配。设置表T已经建立了联合索引(id,name)。where条件为：id=1orid=1andname='tom'满足联合索引最左匹配原则，可以匹配索引执行sql。where条件为：name='tom'andid=1也满足联合索引最左匹配原则，因为Mysql优化器会自动调整id，name的顺序与索引的顺序一致，以便可以使用联合索引。where条件为：name='tom'不满足联合索引最左匹配原则，所以不能使用(id,name)的联合索引。集合表T已建立联合索引(a,b,c,d)。where条件为：a=10andb=20andc>100andd=5。where条件下只有a,b,c可以使用联合索引，d不能使用索引，因为c>100属于范围查询，会随着d的索引匹配中断。前缀索引当索引列有很多字符时，索引很大，速度很慢。这时可以对索引列进行优化，只对列开头的部分字符串进行索引，从而节省索引空间，提高索引效率。使用前缀索引的原则是减少重复的索引值。比如有下面这个student表，st_num是学号：从上表可以发现st_num字段的前7位都是重复的，都是0102021开头的，如果第一个1-7个字符作为前缀索引，大量的索引值会重复。此时索引值重复性高，查询效率低，不符合前缀索引的原则。因此，可以根据具体需要决定使用前8-10个字符作为前缀索引。前缀索引创建如下：createtable`student`(`st_num`varchar(255)notnull,`sex`int(10)notnull,`name`varchar(255)notnull,index(st_num(8)))引擎=InnoDB；普通索引下面的用户建表语句中的t是一个普通索引。普通索引和主键索引的区别在于，普通索引的叶子节点存储的不是行数据，而是主键值。（在索引原理中会详细说明）。比如有一张user表，其主键列为id，表中有字段t和name，在t上有一个索引。建表语句如下：createtableuser(idintprimarykey,tintnotnull,namevarchar(16),index(t))engine=InnoDB;例如：select*fromuserwheret=100;这个查询sql会通过t这个普通索引在自己的B+树上找到对应的主键：1，然后用1在主键索引所在的B+树上查询真实表的行数据并返回结果.此操作称为返回表。唯一索引与普通索引类似，只是唯一索引的索引列的值必须是唯一的，但允许空值，这与主键不同（主键索引列的值是唯一的，但不能为空）。如果是由多个字段组成的联合索引，列值的组合必须是唯一的，创建方法与普通索引类似。全文索引从5.6版本开始，InnoDB存储引擎开始支持全文索引。Mysql允许对char、varchar和文本类型进行全文索引。Mysql支持三种全文搜索模式：自然语言模式：通过匹配传递特定字符串进行检索。布尔模式：可以将运算符添加到检查的字符串中。布尔运算符可以通过以下sql语句查看：查询扩展方式：当查询的关键字太短，用户需要隐式知识时。例如，对于操作系统这个词的查询，用户可能希望查询结果除了操作系统的文档之外，还包括linux、windows、unix的词。这种查询将被搜索两次。第一次是使用给定操作系统的词组搜索，第二次结合第一次搜索相关性比较高的词组。索引原理聚簇索引以下面的学生表student为例，其中s_id为主键。对应的聚簇索引结构图如下：从图中可以看出结构图分为上下两部分。上半部分是：聚簇索引（B+树）由主键s_id组成，下半部分是：student表存储在磁盘上。真实数据。当我们使用主键s_id作为查询条件时，我们来看下面SQL的执行过程。select*fromstudentwheres_id='25';如上图所示，从根开始，经过3次查找，可以找到s_id=25对应的真实数据。如果不使用索引，则必须逐行扫描磁盘，直到找到数据位置。显然，使用索引会更快。但是写数据的时候需要维护这个B+树的结构，所以写性能会下降！聚集索引（主键索引）的叶子节点存储整行数据。非聚集索引还是以上述学生表student为例。在表中添加普通索引名称后，在结构图中添加了一个新的名称字段非聚集索引的B+树。如下图所示：因此，我们每增加一个索引，表的体积就会增加，占用磁盘存储空间。请注意name字段的非聚集索引B+树上的叶子节点。非聚集索引的叶节点不是真正的数据。它的叶子节点仍然是索引节点，存放索引字段的值和对应的主键。(s_id)索引（聚集索引）。这时执行如下查询语句：select*fromstudentwherename='Candy';从上图中的红线可以看出，查询路径是从非聚集索引树开始搜索，然后找到聚集索引然后根据聚集索引，在聚集索引的B+树上的s_id，找到完整的数据！这个过程被回调到表中。也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们应该在应用中尽量使用主键查询。索引维护因为为了维护索引的顺序，B+树需要在插入新值或者删除数据的时候做必要的维护。上图就是一个例子。如果需要插入新的s_id值为50，则需要在s_id=44的记录后插入新行。但是如果插入的s_id的值为：28，则需要将s_id=31的数据移回去。如果s_id=44所在的数据页已满，根据B+树算法，此时需要申请新的数据页，然后将一些数据移动到新的数据页中。此过程称为页面拆分。在这种情况下，性能自然会受到影响。页拆分不仅会影响性能，还会影响数据页的利用率。原本放在一页的数据现在分成了两个数据页，整体空间利用率下降明显。当相邻的两个页由于数据删除导致利用率很低时，会合并数据页。合并的过程可以看作分裂过程的逆过程。基于上面对索引维护过程的描述，我们来讨论一个具体的案例：自增主键应该在哪些场景下使用？哪些场景不应该使用自增主键？我们知道自增主键是指定义在自增列上的主键，建表语句中一般使用关键字：NOTNULLPRIMARYKEYAUTO_INCREMENT来定义。这样在插入新记录时，就不需要指定自增主键列id的值了，因为系统会取当前id+1的最大值作为自增主键的值下一条记录的键列ID。这种插入数据的方式是追加操作，不涉及移动其他记录的操作，所以不会触发叶子节点的分裂。从性能上看：如果使用业务逻辑的字段作为主键，相比自增主键id，不易保证有序插入，所以写入数据的成本比较高。从存储空间来看：假设user表中有一个字符串类型的身份证号字段，并且是唯一的，唯一的。这个时候用身份证号做主键好，还是用自增字段做主键好？说到索引的原理，提到过非聚集索引的叶子节点就是主键的值。如果用身份证号作为主键，那么非主键索引的每个叶子节点大约占用20个字节。对于主键，只需要4个字节，如果是长整数（bigint），则为8个字节。可以看出，主键长度越小，公共索引的叶子节点越小，公共索引整体占用的空间也越小。因此，从性能和存储空间上来说，使用自增主键作为索引是更好的选择。单个索引的值的字符长度不能太大，因为B+树索引不能直接找到行，只能找到行所在的页，从磁盘读取整个页到内存中，然后在内存中搜索。每页的大小是有规定的，页是InnoDB管理存储空间的基本单位：1页=16kb。原则是尽可能在一个页面中存储多个索引。覆盖索引还是用上面的例子来解释，现在查询语句如下：select*fromstudentwherename='Candy';改为：selects_idfromstudentwherename='Candy';此时只需要检查s_id的值，而s_id的值已经在普通索引名上了，所以查询结果可以直接从非聚集索引B+树中返回，不需要回表。也就是说，在这个查询中，索引名称已经覆盖了我们的查询需求，所以称为覆盖索引。由于覆盖索引可以减少树的搜索次数，显着提高查询性能，因此使用覆盖索引是一种常用的性能优化方法。应用场景中只有一个索引，索引必须是唯一索引。这种场景适合直接使用业务字段作为主键。使用业务时，尽量使用主键查询，避免回表。当表需要经常更新时，不适合建立索引。频繁更新会导致索引频繁更新，降低写入效率。使用索引进行模糊查询时，切记不能在like之后的关键字前加%（例如：namelike"%3"），只能在关键字后加%，因为索引是从左到右匹配的，如果索引前面有%，则无法找到该索引。当数据表过大时，如果索引字段的字符长度过长，不适合作为索引。因为在查询大量数据的时候，即使索引有效，速度还是很慢。当表数据量很大，字段值有很多相同的值时，适合使用普通索引。当字段较多且字段值不重复时使用唯一索引。当where条件后面的查询字段较多时，适合建立联合索引。不要为没有出现在where条件之后的查询字段创建索引。3.总结项目代码会在codediff中找到常用的业务查询sql，根据where条件判断频繁查询的字段，然后根据分享的索引知识判断sql审计中的索引相关操作是否合理有效本文。测试时，通过设置慢sql查询参数，找出对应的sql查询语句，分析慢sql的原因，并给出自己的解决方案，如添加必要的字段索引等。
