MySQL索引详解

前言

我们知道，数据库查询是数据库的最主要功能之一。但每种查找算法都只能应用于特定的数据结构之上，例如二分查找要求被检索数据有序，而二叉树查找只能应用于二叉查找树上，但是数据本身的组织结构不可能完全满足各种数据结构（例如，理论上不可能同时将两列都按顺序进行组织），所以，在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。

一、索引的定义

索引（Index）是帮助MySQL高效获取数据的数据结构。
索引一般以文件形式存储在磁盘上，索引检索需要磁盘I/O操作。所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。

二、索引的优缺点

1.优点

1、索引能够提高数据检索的效率，降低数据库的IO成本。

2、通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性，创建唯一索引

3、在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间（索引会对数据排序）

4、加速两个表之间的连接，一般是在外键上创建索引

2.缺点

1、索引本身也是一种表需要占用物理空间,建立的索引越多需要的空间越大

2、创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

三、创建索引

1.需要创建索引的字段

1、经常用在where语句之后的字段

2、主键或者外键

3、字段具有唯一性的时候建立唯一性索引

4、在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的

2.创建索引的原则

1.最左前缀匹配原则

非常重要的原则**，MySQL会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配**，比如a = 1 and b = 2 and c > 3 and d = 4，如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3，建立(a,b,c)索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的形式。

2. 尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。所以这种情况在某些特定情况下加索引还是有效果的。
另外区分度低的字段在组合索引中使用是没有问题的，但区分度低的字段一般放在组合索引的最右边。

3.计算列里无法使用索引

索引列不能参与计算，保持列“干净”。比如from_unixtime(create_time) = ’2017-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2017-05-29’)。

4.尽量扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。这样可以节省没必要的索引创建。

5.尽量使用唯一索引
考虑到某列中值的分布.索引的基数越大,索引的效果越好.
例如,存放出生日期的列具有不同值,很容易区分各行.而记录性别的列,只有"男",“女”,索引没什么用,不管搜索那个都会得出一半的值.

6.使用短索引
对字符串进行索引,尽量指定一个前缀长度.
例如,有一个CHAR(200)列,如果在前10个或前20个字符中,多数值是唯一的,那就不要对整列进行索引.
对前10个或前20个字符进行索引能够节省大量索引空间,可能会查询更快.
较小的索引设计的磁盘IO少也能提高速度.
对于较短的键值,索引块中能存更多的键值,因此,MYSQL在内存中能容纳更多的值.

7.多利用覆盖索引

MySQL利用B+树索引检索数据的时候，如果不是基于聚簇索引，或者说如果不是基于主键的检索，那么即便是SQL语句能够利用索引，但索引返回的信息也只是所需结果行的主键值，要取得全部数据，还需要通过这些主键值重新到数据文件里再做一次检索操作，这样就需要额外的IO，降低了查询效率。如果能优化SQL或索引，让MySQL只需要通过索引就可以返回所需的数据，而不必回表，从而提升效率，这就是覆盖索引。简单说就是返回字段就是where条件字段，比如select name from table where name=‘’;这样的。

8.组合索引等号条件字段在前

在创建组合索引时，比如“a>=xx and b=xx”查询，建立索引时应该为(b,a)，根据索引排序特性，这样能用到更多的索引字段，并且扫描的数据也会越少。如果查询为“a=xx and b=xx”，建立组合索引时，谁的选择性高就把谁放在索引前面，这样扫描的数据也会少。

9.负向条件查询不能使用索引

负向条件查询默认是不能很好的使用索引的，基本都是索引扫描。比如“!=”、“not in”、“not exists”都不是好习惯。

10.前导模糊查询不能使用索引

假如有这样一列code的值为’AAA’，’AAB’，’BAA’，’BAB’ ；如果where code like ‘%AB’条件，由于前面是模糊的，所以不能利用索引的顺序，必须一个个去找，看是否满足条件。这样会导致全索引扫描或者全表扫描。如果是这样的条件where code like ‘A% ‘，就可以查找CODE中A开头的CODE的位置，当碰到B开头的数据时，就可以停止查找了，因为后面的数据一定不满足要求，这样就可以利用索引了。

11.表字段字符集不同时不能使用索引

当进行多表join时，如果进行比较的条件字段字符集不同则可能不会使用索引，因为不同的字符集类型其比较规则不同。这里为什么使用可能不会使用索引呢？因为就算字符集不同时也要以驱动表为主，比如驱动表字符集为utf8，而被驱动表字符集为utf8mb4时，这时就无法使用索引。但是当驱动表字符集为utf8mb4时，被驱动表为utf8时，这时是可以使用索引的，因为utf8mb4字符集兼容utf8。

12.索引不存储NULL值

更准确的说，单列索引不存储NULL值，复合索引不存储全为NULL的值。如果允许NULL，可能会得到“不符合预期”的结果集，比如“select count(*) from user where name != “dkey””的查询，如果允许name字段为NULL，因为索引不存储NULL值，所以结果集中不会统计这些记录。
另外由于索引不能存储NULL，所以对这列采用IS NULL条件时，因为索引上根本没NULL值，不能利用到索引，只能全表扫描。
为什么索引列不能存NULL值？
将索引列值进行建树时，其中必然涉及到诸多的比较操作。NULL值的特殊性就在于参与的运算大多取值为NULL。这样的话，NULL值实际上是不能参与进建索引的过程。也就是说，NULL值不会像其他取值一样出现在索引树的叶子节点上。

13.强制类型转换不能使用索引

强制类型转换有一个名词叫“隐式转换”，简单来说就是查询条件值跟查询字段数据类型不匹配。比如：select * from user where phone = ‘xx’查询，条件查询值类型为字符型，但是phone字段数据类型为数值型，此时就发送了所谓的隐式转换，故不会使用索引。

3. 索引创建误区

1.不是所有的表都需要索引
数据量很小的表,索引扫描和全表扫描,并不会带来性能的大幅度提升.
而大表的查询,更新,删除则要尽可能通过索引.

2.不要过度索引
索引"越多多好"是错误的,因为索引会占用额外的磁盘空间,降低写操作的性能.修改表内容时,索引必须进行更新,可能还要重构,这回耗费大量时间.
尽量创建有需要的索引可以优化查询的速度.

3.谨慎创建低选择度索引
选择性低且数据分布均匀的列,因为过滤的结果集大,创建索引的效果不好;
选择性低且数据分布不均匀的列,比如男女比例为99%:1%,那么此时创建索引对于查询条件为"女"的过滤结果集就比较小.这时创建索引就比较合适.

四、索引的使用原则

以下几种情况下MySQL在查询中即使有索引也不会去使用：
1．在多列索引，查询条件中用的不是最左列，那么此时是不会使用索引。

2．like查询时%出现在第一位也不会使用索引。

3．where条件中有or、<>、not in、not exists可能也不会使用索引。

4．索引列类型是字符串，可在查询中省略了引号（有时字符串类型列也会存储数字），当做int型查询，此时不会使用索引。反之int型查询加引号也一样。

5．如果MySQL优化器估计使用全表扫描比使用索引快，它也不会使用索引。

6 多表Join时，条件字符集不同也可能不会使用索引。

五、索引的分类

主键索引:
主键是一种唯一性索引，但它必须指定为PRIMARY KEY，每个表只能有一个主键。

alter table table_name add primary key (`字段名`);

唯一索引:
索引列的所有值都只能出现一次，即必须唯一，值可以为空。

alter table table_name add primary key (`字段名`);

普通索引 :
基本的索引类型，值可以为空，没有唯一性的限制。

alter table table_name add index (`字段名`);

全文索引:
全文索引的索引类型为FULLTEXT。全文索引可以在varchar、char、text类型的列上创建。可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集，通过ALTER TABLE（或者CREATE INDEX）命令创建全文索引要比把记录插入带有全文索引的空表更快。MyISAM支持全文索引，InnoDB在mysql5.6之后支持了全文索引。
全文索引不支持中文。

alter table 表名 add FULLTEXT(`字段名`);

联合索引
利用最左匹配选择。

六、索引的数据结构

1.hash

hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。
可 能很多人又有疑问了，既然 Hash 索引的效率要比 B-Tree 高很多，为什么大家不都用 Hash 索引而还要使用 B-Tree 索引呢？任何事物都是有两面性的，Hash 索引也一样，虽然 Hash 索引效率高，但是 Hash 索引本身由于其特殊性也带来了很多限制和弊端，主要有以下这些。

（1）Hash 索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B+Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

2.b+树

1.为什么Mysql使用B+树当索引？

1、 B+树的磁盘读写代价更低：B+树的内部节点并没有指向关键字具体信息的指针，因此其内部节点相对B树更小，如果把所有同一内部节点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多，一次性读入内存的需要查找的关键字也就越多，相对IO读写次数就降低了。

2、B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

3、B+树更便于遍历：由于B+树的数据都存储在叶子结点中，分支结点均为索引，方便扫库，只需要扫一遍叶子结点即可，但是B树因为其分支结点同样存储着数据，我们要找到具体的数据，需要进行一次中序遍历按序来扫，所以B+树更加适合在区间查询的情况，所以通常B+树用于数据库索引。

4、B+树更适合基于范围的查询：B树在提高了IO性能的同时并没有解决元素遍历的我效率低下的问题，正是为了解决这个问题，B+树应用而生。B+树只需要去遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作或者说效率太低。

b+树相比于b树的优点

IO次数更少：由于B+树在内部节点上不包含数据信息，因此在内存页中能够存放更多的key。 数据存放的更加紧密，具有更好的空间局部性。因此访问叶子节点上关联的数据也具有更好的缓存命中率。
遍历更加方便：B+树的叶子结点都是相链的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历。相邻的元素可能在内存中不相邻，所以缓存命中性没有B+树好。

七、补充

1.Mysql索引查询失效的情况

（1）like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
（2）or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
（3）联合索引，不是使用第一列索引，索引失效（最左匹配原则）。
（4）数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
（5）在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引。
（6）在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
（7）当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。

2.覆盖索引

概念：
解释一： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。
解释二： 索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。
解释三：是非聚集组合索引的一种形式，它包括在查询里的Select、Join和Where子句用到的所有列（即建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据）。
举例说明:

select id,name from user where name=‘shenjian’;

能够命中name索引，索引叶子节点存储了主键id，通过name的索引树即可获取id和name，无需回表，符合索引覆盖，效率较高。