[TOC] ## 什么是索引 >索引是存储引擎用于快速找到记录的一种数据结构 ### 索引是个什么样的数据结构呢? 索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引。 ### 索引的好处 * 减少查询需要扫描的数据量(加快了查询速度) * 减少服务器的排序操作和创建临时表的操作(加快了groupby和orderby等操作) * 将服务器的随机IO变为顺序IO(加快查询速度). ### 索引的坏处 * 索引占用磁盘或者内存空间 * 减慢了插入更新操作的速度 ### 索引种类 1. **「主键索引」**（`PRIMARY KEY`）：主键索引一般都是在创建表的时候指定，**「一个表只有一个主键索引」**，特点是**「唯一、非空」**。 2. **「唯一索引」**（`UNIQUE`）：唯一索引具有的特点就是唯一性，可以在创建表的时候指定，也可以在创建表后创建。 3. **「普通索引」**（`INDEX`）：普通索引唯一的作用就是加快查询。 4. **「组合索引」**（`INDEX`）：组合索引是创建一个**「多个字段的索引」**，这个概念是相对于上上面的单列索引而言，组合索引查询遵循**「最左前缀原则」**。 5. **「全文索引」**（`FULLTEXT`）：全文索引是针对一些大的**「文本字段」**创建的索引，也称为**「全文检索」**。 ### 索引的数据结构 1. B+树 2. hash 3. fulltext 4. R-tree #### Hash 索引和 B+ 树索引区别是什么？你在设计索引是怎么抉择的？ * B+ 树可以进行范围查询，Hash 索引不能。 * B+ 树支持联合索引的最左前缀原则，Hash 索引不支持。 * B+ 树支持 order by 排序，Hash 索引不支持。 * Hash 索引在等值查询上比 B+ 树效率更高。 * B+ 树使用 like 进行模糊查询的时候，like 后面（比如%开头）的话可以起到优化的作用，Hash 索引根本无法进行模糊查询。 * hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询。 * 数组的话适用于范围查询和等值查询，但是当插入和删除数据时，可能造成数据的移动，效率很低 ### 索引算法有哪些？ 索引算法有`BTree算法和Hash算法`。 * BTree算法 BTree是最常用的mysql数据库索引算法，也是mysql默认的算法。因为它不仅可以被用在=,>,>=,<,<=和between这些比较操作符上，而且还可以用于like操作符，只要它的查询条件是一个不以通配符开头的常量， 例如： ~~~ -- 只要它的查询条件是一个不以通配符开头的常量select * from user where name like 'jack%';  -- 如果一通配符开头，或者没有使用常量，则不会使用索引，例如： select * from user where name like '%jack';  ~~~ * Hash算法 Hash Hash索引只能用于对等比较，例如=,（相当于=）操作符。由于是一次定位数据，不像BTree索引需要从根节点到枝节点，最后才能访问到页节点这样多次IO访问，所以检索效率远高于BTree索引。 >物理存储 1. 聚簇索引 关系表记录的物理顺序与索引的逻辑顺序相同，一张表最多也只能存在一个聚簇索引，而且主键值和所有列数据放在一起 2. 非聚簇索引 索引文件和数据文件是分开的，索引文件只存储了值的地址 >前缀索引 在对一个比较长的字符串进行索引时,可以仅索引开始的一部分字符,这样可以大大的节约索引空间,从而提高索引效率.但是这样也会降低索引的选择性.  >联合索引 组合索引即用多个字段创建一个索引，组合索引能够避免**「回表查询」**，相对于多字段的单列索引，组合索引的查询效率更高。 ## 什么是回表查询？ **通过二级索引查询数据，得不到完整的数据行，需要再次查询主键索引来获得数据行** ## 索引覆盖 索引的叶子节点已经包含了查询的数据，没必要再回表进行查询。 ## 索引下推 就是在执行sql查询的时候，会将一部分的索引列的判断条件传递给存储引擎，由存储引擎通过判断是否符合条件，只有符合条件的数据才会返回给Mysql服务器 ## 索引碎片 在索引的创建删除过程中,不可避免的会产品索引碎片,当然还有数据碎片,我们可以通过执行`optimize table xxx`来重新整理索引及数据,对于不支持此命令的存储引擎来说,可以通过一条无意义的alter语句来触发整理,比如:将表的存储引擎更换为当前的引擎, `alter table xxxx engine=innodb` ### 碎片的分类？ 内部碎片：对于当前页的数据来说，我们**更新**了某条数据的某个列，造成当前页不能容纳数据，造成了页的分裂 外部碎片：对于当前页的数据来说，**新增**一条数据，造成了页的分裂 ## 创建索引的原则 1、表的主键、外键必须有索引； 2、经常与其他表进行连接的表，在连接字段上应该建立索引； 3、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引； 4、索引应该建在选择性高的字段上； 5、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引； 6、频繁进行数据更新的表，不要建立太多的索引，会早晨很多索引碎片； 7、删除无用的索引，避免对执行计划造成负面影响； ### 一般选择在这样的列上创建索引 1. 在经常需要搜索查询的列上创建索引，可以加快搜索的速度； 2. 在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构； 3. 在经常用在连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度； 4. 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的； 5. 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询 时间； 6. 在经常使用在Where子句中的列上面创建索引，加快条件的判断速度； 7. 为经常出现在关键字order by、group by、distinct后面的字段，建立索引。 ### 创建索引需要注意的地方 1. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。 2. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。 3. 删除不再使用，或者很少被使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。 ## explain 的各项解释 各列的含义如下: ~~~ * id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符. * select_type: SELECT 查询的类型. * table: 查询的是哪个表 * partitions: 匹配的分区 * type: 判断此次查询是`全表扫描`还是`索引扫描` * possible_keys: 此次查询中【可能】选用的索引 * key: 此次查询中【确切】使用到的索引. * key_len: 使用了索引的字节数，这个字段可以评估组合索引是否完全被使用。 * ref: 哪个字段或常数与 key 一起被使用 * rows: 显示此查询一共扫描了多少行. 这个是一个估计值.越小性能越好。 * filtered: 表示此查询条件所过滤的数据的百分比 * extra: 额外的信息， ~~~ 需要重点关注的列：select_type，type，possible_keys，keys，key_len， rows，Extra。 （1）type 类型的性能比较： `ALL < index < range ~ index_merge < ref < eq_ref < const < system` `ALL`：Full Table Scan， MySQL将遍历全表以找到匹配的行 `index`: Full Index Scan，index与ALL区别为index类型只遍历索引树 后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了. 。 `range`: 表示使用索引范围查询。 `ref `：非唯一性索引扫描，范围匹配某个单独值得所有行。本质上也是一种索引访问，他返回所有匹配某个单独值的行，然而，它可能也会找到多个符合条件的行，多以他应该属于查找和扫描的混合体 `eq_ref`：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件 `const`: 针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可. `system`: 表中只有一条数据. 这个类型是特殊的`const`类型。 （2）rows基本是第一眼要看的指标。 （3）全表扫描时, possible_keys 和 key 字段都是 NULL。 （4）当 Extra 显示： * Using filesort 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有`Using filesort`, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大。 * Using index "覆盖索引扫描", 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错 * Using temporary 查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化. ## 导致索引失效的原因 * 列参与了数学运算或者函数； * 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)； * 对于多列索引，不符合最左匹配的命中规则； * like查询是以%开头； * 如果直接查比用索引快，那么数据库会自动选择最优方式，不用索引； * in 和 not in 也要慎用，否则会导致全表扫描。 ## innodb索引的实现原理是什么？ InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。 ## 索引可以更快的查询到数据，但数据结构有很多，我们到底该选择哪一种数据结构才最适合数据库操作呢？ （1)首先我们从数组来说，根据数组的数据结构来看，数组的查询效率还是挺不错的，如果是有序数组，那么我们采用二分查找效率则会更高；数组的弊端就是插入和删除，数组为了保持内存的连续性，就会导致插入和删除效率较低； (2)其次是链表，从链表的数据结构来分析，链表的节点不仅存放了数据，也存放了储存上一个节点或下一个节点地址的指针域，这就使链表在插入和删除的时候只需要改变节点的指针域即可，效率很不错；但是链表在存储中是非连续、非顺序的，这就导致了链表在进行查询的时候需要从头到尾，依次遍历查找，效率不高； (3)然后再谈Hash表，哈希表又叫散列表，是根据关键码值来进行访问的数据结构，它通过散列函数(数据库表字段与散列值的映射)来加快查找的效率，虽说它的单一查询效率很高，但经不住范围查询，这就是我们选择使用Hash或是B+tree的条件之一。