什么是索引索引也可以称为键（key），是存储引擎用来快速查找记录的一种数据结构。索引是提高MySQL查询性能最有效的手段。我们常说的MySQL性能调优，基本上就是索引的优化。所以这是每一个开发都需要掌握和应用的知识点。索引是一种数据结构，也是存储在磁盘上的文件。在上一篇文章中，我们在学习MySQL的逻辑架构时，了解了InnoDB和MyISM存储引擎。InnoDB存储引擎索引和数据是同一个文件，而MyISAM索引和数据是两个独立的文件。在MySQL中，索引是在存储引擎层实现的，而不是在服务器层，因此不同存储引擎的索引工作方式不同。我们对索引的分析应该是基于存储引擎的，而InnoDB是MySQL默认的存储引擎。索引的优点：索引大大减少了服务器需要扫描的数据量。索引帮助服务器避免排序和临时表。索引可以将随机I/O更改为顺序I/O。索引的缺点：索引是占用额外磁盘空间的数据结构。当表数据量比较大的时候，维护索引的成本就比较高。索引数据模型每个存储引擎的数据结构和算法都不同。我们先来看看MySQL本身支持的索引类型。B-Tree索引一般我们所说的索引结构都是指B-Tree索引。MySQL的大部分存储引擎都支持这种索引，但是不同的存储引擎使用B-Tree索引的方式不同，性能也不同。InnoDB使用B+Tree，按照原始数据格式存储，根据主键引用索引行。B-Tree的所有值都是顺序存储的，每片叶子到根的距离是相同的。下图是B-Tree的抽象图：B-Tree可以加速数据的访问。存储引擎不需要全表扫描来获取需要的数据，它从索引的根节点开始查找。根节点的槽中存放着指向子节点的指针，搜索引擎根据这些指针向下层查找。通过将节点页面的值与要查找的值进行比较，找到合适的指针进入下层节点。最终引擎要么找到相应的值，要么记录不存在。如果B-Tree索引有多个列，索引值按照创建表时定义的索引顺序排序，所以索引的顺序比较重要。B-Tree是一棵N叉树，N的大小取决于数据块的大小。以InnoDB的整型字段索引为例，N约为1200，当树高为4时，可以存储1200次方的数据，约为17亿条。具有10亿次访问磁盘的表上整数字段的索引最多访问磁盘3次。其实在应用的时候，如果提前把第二层加载到内存中，那么访问磁盘的次数就少了。哈希索引哈希索引是基于哈希表实现的，只有完全匹配所有列的查询才有效。对于每一行数据，存储引擎都会为所有的索引列计算一个散列码（hashcode）。hashcode是一个比较小的值，不同key值的行计算的hashcode是不同的。哈希索引在索引中存储了所有的哈希码，在哈希表中保存了指向每个数据行的指针。创建表test_hash，存储引擎为memory，索引为full_name，索引类型为hash。createtable中的数据如下：mysql>select*fromtest_hash;+--------------------+------------+------+|全名|简称|年龄|+--------------------+------------+------+|DwayneJohnson|Johnson|NULL||TaylorSwift|Taylor|NULL||LeonardoDiCaprio|Leonardo|NULL||VinDiesel|Diesel|NULL||KobeBryant|Kobe|NULL|+------------------+------------+------+5rowsinset(0.00sec)那么hash索引的数据结构可能是：当我们执行查询语句：mysql>selectshort_namefromtest_hashwherefull_name='DwayneJohnson';这条sql语句的执行流程：1）根据where条件'DwayneJohnson'计算哈希码，得到的哈希码为1234。2）MySQL在索引中找到1234，根据这个值找到对应的行记录指针。3）根据指针地址找到对应的行，最后比较该行的full_name列是否为'DwayneJohnson'。那么现在有个问题，哈希码冲突了怎么办？学过HashMap的朋友这时候肯定有一个想法：哈希码冲突的时候用链表。是的，当key值的hashcode发生冲突的时候，MySQL也采用了链表结构。如果是链表结构，在查找的时候需要遍历每个链表指针指向的行记录进行匹配，所以当hash冲突比较大的时候，查找效率比较低。从上面的例子我们可以看出，哈希索引的结构中只存储了哈希值，其结构比较紧凑，精确查询的效率也比较快。但是哈希索引还是有一些局限性的：哈希索引存储的是键值的哈希值，它不是按照索引列的顺序排列的，所以不能用于排序。散列索引不支持部分索引匹配查找，因为散列索引始终是索引列的全部内容。如果我们的索引有两列（A，B），我们查询的时候只想用A列，此时不能应用索引。哈希索引只支持等值查询，如=、in等，不支持任何范围查询。当存在哈希冲突时，存储引擎必须遍历链表中的所有行指针，逐行比较，直到找到所有符合条件的行。如果哈希冲突比较多，那么索引维护的成本就比较高。在MySQL中，目前只有内存引擎明确支持哈希索引。InnoDB索引模型前面我们提到，InnoDB的索引结构是B+Tee，指的是有主键的索引行。因此，在InnoDB中，表按照主键的顺序以索引的形式存储，每个索引对应InnoDB中的一棵B+树。B+Tree索引B+Tree是我们前面提到的B-Tree的扩展。B-Tree的每个节点都包含数据项，这样每个磁盘存储的索引值会比较小，树的高度会增加。随着它变大，查询的磁盘I/O数量也会增加。B+Tree的数据结构是什么？下图是B+Tree的抽象图：B+Tree和B-Tree的区别：B+Tree的非叶子节点不保存数据信息，只保存索引值和指针指向的指针下一层节点。B+Tree的叶子节点存放数据。B+Tree的叶子节点是有序排列的，叶子的相邻节点之间有指针。B+Tree可以更好的配合磁盘的读写特性，减少单次查询。磁盘访问次数。InnoDB的索引类型分为主键索引和非主键索引。主键索引和非主键索引创建一个表user，它的存储引擎是InnoDB，id是主键，name是普通索引。创建表`user`(`id`int(10)NOTNULL,`name`varchar(32)DEFAULTNULL,`age`int(3)DEFAULTNULL,`sex`varchar(1)DEFAULTNULL,`comment`varchar(255)DEFAULTNULL,`date`dateDEFAULTNULL,PRIMARYKEY(`id`),KEY`idx`(`name`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8;表中数据如下：mysql>select*fromuser;+----+-------+------+------+----------+------------+|id|name|age|sex|comment|date|+----+--------+------+------+--------+-------------+|1|Alen|20|1|NULL|2021-02-16||2|Alex|21|1|NULL|2021-02-16||3|Saria|16|0|NULL|2021-02-16||4|Semyt|18|0|NULL|2021-02-16||5|总结|17|1|NULL|2021-02-16||6|汤姆|19|0|空|2021-02-16|+----+------+------+------+--------+------------+6rowsinset(0.00sec)主键索引也叫聚簇索引，它的叶子节点包含主键值、事务ID、事务和MVCC的回滚指针，以及所有剩余的List。mysql>select*fromuserwhereid=1;主键索引只需要查找ID的B+Tree就可以得到满足条件的行记录。InnoDB通过主键索引聚合数据。如果表中没有定义主键，InnoDB将选择一个唯一的非空索引。如果没有这样的索引，InnoDB将隐式定义一个主键作为聚簇索引。这就是Gogo为每个表创建主键的原因。聚簇索引的优点：将相关数据保存在一起，减少磁盘I/O；聚集索引将数据和索引保存在同一个BTree上，数据访问速度更快；聚集索引的缺点：如果数据在内存中，聚集索引的查询性能不是很好。插入速度在很大程度上取决于插入顺序。尽量确保主键索引是有序的。更新聚簇索引列的成本更高。在插入行或更新主键时，当需要移动行时可能会导致页面拆分。当插入一个完整的页面时，存储引擎会将页面拆分为两个页面来容纳数据，页面拆分会导致占用更多的磁盘空间。非主键索引也称为非聚集索引，在InnoDB中也称为二级索引。非主键索引的叶子节点的内容就是主键的值。mysql>select*fromuserwherename='Alen';查询非主键索引时，先按照name的普通查询查找name索引树，发现id为1，然后根据id=1查询一次id索引树，得到满足的行记录条件。我们把先查找普通索引树得到主键，再查找主键索引树的过程称为回表。普通索引的查询比主键索引多检索一颗B+Tree。在实际应用场景中，如果可以使用主键索引，尽量选择主键索引。创建索引时还有其他原则。下面继续学习高性能索引策略。索引策略朋友们在学习索引策略的时候可以使用上一篇文章中的explian关键字来查询执行计划。索引选择索引有很多种。我们可以根据索引字段的数量将索引分为单列索引和联合索引。单列索引：一个索引只包含一列，一张表中可以有多个单列索引。联合索引：一个索引包含多个列。我们还可以将索引分为普通索引、唯一索引和主键索引。普通索引：基本索引类型，常用于提高查询效率，对数据没有限制。允许在索引列中插入空值和重复值。唯一索引：被索引的列中的值必须是唯一的，允许空值。主键索引：一种特殊的唯一索引，不允许空值。索引的分类那么多，我们在创建索引的时候怎么选择呢？索引三星制：一星：与索引相关的记录放在一起。两颗星：索引中数据的顺序与查找列中的顺序相同。三星：索引列包含查询中需要的所有列。正确创建和使用索引是实现高性能查询的基础。索引的选择没有绝对的要求，主要是根据自己的业务需求，但是有一些原则我们在创建索引的时候可以作为参考。索引列的区分度越高，查询效率越高。将经常搜索的列添加到索引中可以提高搜索效率。索引不仅可以提高查询效率，还可以参与排序和分组。经常用于排序和分组的字段也需要考虑添加索引。创建索引时，应将区分度高的字段排在第一位。即需要注意索引字段的顺序。索引列不能参与任何操作。避免创建重复索引，即在同一列上以相同的顺序创建相同类型的索引。对于从未使用过的索引，尝试删除它们。对于blob、text、longvarchar类型的列，必须使用前缀索引，取最长的前缀，保证高度的区分度。普通索引和唯一索引在查询效率上没有太大区别，因为引擎是按页读取数据的。对于唯一索引，只要在查询的时候找到了，就不会再继续比较了，因为索引已经保证了唯一性。对于普通索引，找到满足条件的记录后，需要继续查找，直到找到第一条不满足条件的记录。但是对于按页读取数据的引擎来说，多判断一次对性能影响不大。选择普通索引和唯一索引除了要保证业务的准确性外，还要考虑更新数据时对性能的影响。独立列“独立列”意味着索引不能是表达式的一部分，也不能是函数的参数。比如下面的SQL语句中，索引字段名在查询时参与了函数运算，会导致索引失效，扫描全表。mysql>select*fromuserwhereCONCAT(name,'n')='Alen';添加索引age字段，如果我们在查询时对age字段进行操作，也会导致索引失败：mysql>select*fromuserwhereage+1=21;在我们平时的开发中，应该养成简化where条件的习惯，总是使用单独的索引列。覆盖索引如果我们修改按照普通索引查询的sql语句如下：mysql>selectnamefromuserwherenamelike'Al%';那么只需要查询普通的索引树就可以得到要查询的列，因为要查询的列已经在索引树中了，不需要回表查询。这种索引字段覆盖我们需要查询的结果字段的场景称为覆盖索引。覆盖索引可以减少表返回，减少索引树的搜索次数，显着提高查询性能，因此覆盖索引是一种较好的优化策略。在实际开发中，可以根据业务需要在索引中加入一些常用的搜索字段，使用覆盖索引来提高查询效率。但是在某些场景下，不能为了使用覆盖索引而过多维护索引。毕竟索引的维护成本也是很高的。高的。这时候我们还需要思考一个关于最左前缀的问题。在业务场景中，我们的查询是多样化的。我们不能为每个场景设计一个索引，以便使用索引吗？这时候我们就需要用到B+Tree树索引结构的另一个特点就是最左前缀。最左前缀可以是联合索引的最左字段，也可以是字符串索引的最左字符。以相同的顺序创建联合索引（姓名、年龄）。此时执行sql语句：mysql>select*fromuserwherename='Alen';虽然是联合索引，但是name字段排在第一位，索引也可以命中。mysql>select*fromuserwherenamelike'Al%';如果使用name索引字段最左边的N个字符串，也可以命中索引。但是如果我们使用%Al我们就不能命中索引。如果我们使用如下SQL查询语句：mysql>select*fromuserwhereage='16';age虽然也是联合索引的一个字段，但是它的顺序在name之后，直接用age查询是打不到索引的。因此，在创建联合索引时必须考虑索引字段的顺序。索引维护有一个原则：如果可以通过调整索引顺序来少维护一个索引，那么需要优先调整顺序而不是添加索引。MySQL可以使用同一个索引对行进行排序和扫描，但前提是索引的列顺序与orderby子句的列顺序完全相同，并且列的排序方向相同（正向或反向），MySQL可以使用结果进行排序Sort。orderby子句与查询类型限制相同，也需要满足“最左前缀”原则，否则MySQL无法使用索引排序。索引下推当我们的查询语句不满足最左边的前缀时会发生什么？比如我们查询第一个字符为A，年龄为20，性别为1（男）的人的信息，则sql语句如下：mysql>select*fromuserwherenamelike'A%'andage=20andsex=1;根据我们前面学过的最左前缀原则，根据'A'查找第一个满足条件的主键1，然后回表查询判断其他两个条件是否满足。MySQL5.6之后引入了索引下推的优化，即索引包含的字段会优先过滤，减少了回表的次数。在MySQL5.6之前，我们上面的sql语句会两次回表比较主键1和2的数据是否满足其他条件，但是引入索引下推优化后age=20，不满足有了条件，会直接过滤掉，只需要主键1回表一次就可以得到结果。摘要索引是一种用于快速查找数据的数据结构。合理创建和使用索引是提高查询效率的基础。在了解了索引数据结构的基础之后，我们还应该花时间掌握高性能的索引策略，了解实际开发中创建索引的一些原则，合理选择索引。
