MySQL索引

一、索引概述

        索引（index）是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这样的数据结构就是索引。

        优点：

• 提高数据检索的效率，降低数据库的IO成本；
• 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗；

        缺点：

• 索引列也要占用空间；
• 索引大大提高了查询效率，同时也降低了表的更新速度；

二、索引结构

        MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要为一下几种：

索引种类

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+Tree索引
Hash索引	底层数据结构是用Hash表实现的，只有精准匹配索引列的查询才有效，不支持范围查询
R-Tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，通常使用较少
Full-text（全文索引）	是一种通过建立倒排索引，快速匹配文档的方式。类似于ES的索引结构

不同的存储引擎支持的索引结构

索引	InnoDB	MyISAM	Memory
B+Tree索引	支持	支持	支持
Hash索引	不支持	不支持	支持
R-Tree（空间索引）	不支持	支持	不支持
Full-text（全文索引）	5.6版本之后支持	支持	不支持

1.B+Tree索引

        MySQL索引数据结构对经典的B+Tree进行了优化。在原本B+Tree的基础上，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。典型的空间换时间。

2.Hash索引

        Hash索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

        特点：

• Hash索引只能用于对等比较（=，in）,不支持范围查找（between，<，>，...）。
• 无法利用索引完成排序操作。
• 查询效率高，通常（不出现Hash碰撞）只需要一次检索就够了，效率通常要高于B+Tree索引。

        存储引擎支持：

        在MySQL中，支持Hash索引的是Memory引擎，而InnoDB具有自适应Hash功能，Hash索引是存储引擎根据B+Tree索引在指定条件下，将B+Tree索引自动构建为Hash索引。

3.为什么InnoDB选择使用B+Tree索引结构？

• 相对于二叉树，层级少，搜索效率高；
• 对于BTree，无论是否为叶子节点，BTree都会保存对应的行数据，这样导致一页中存储的键值减少，指针跟着减少，要保存大量数据，只能增加树的高度，导致性能降低；
• 相对于Hash索引，B+Tree支持范围匹配及排序操作；

三、索引分类

        索引主要分为以下四类：

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键字，而不是比较索引中的值	可以有多个	FULLTEXT

        在InnoDB中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引	将数据存储与索引放到一块，索引结构的叶子节点保存了行数据。	必须要，而且只有一个
二级索引	将数据与索引分开存储，索引结构的叶子节点关联的时对应的主键。	可以存在多个

        聚集索引的选取规则：

• 如果存在主键，主键索引就是聚集索引；
• 如果不存在主键，将使用第一个唯一索引作为聚集索引；
• 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个RowId作为隐藏的聚集索引；

四、索引语法

1.创建索引

create [unique/fulltext] index index_name on table_name(index_cloumn_name, ...);

2.查看索引

show index from table_name;

3.删除索引

drop index index_name on table_name;

五、索引的使用

1.最左前缀法则：如果索引了多列（联合索引），要遵守最左前缀法则。最左前缀法则是指查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）。

2.在复合索引中，出现范围查询（ > , < ），范围查询右侧的列索引会失效。

3.不要在索引列上进行运算操作，否则索引会失效。

4.字符串类型字段使用时，不加引号，索引将失效。

5.模糊查询：如果仅仅是尾部模糊查询，索引不会失效。如果是头部使用，将会失效。

6.or连接的条件：用or分割开的条件，如果or前后中一列没有走索引，那么涉及的索引都不会被用到。

7.数据分布影响：如果MySQL（优化器）评估使用索引比全表慢，则不使用索引。

8.SQL提示：优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。例如，在查询某一列时，这一列既有复合索引又有单列索引，MySQL优化器会优先走复合索引。

#use index: 建议MySQL使用xx索引
explain select * from tb_user use index(index_user) where profession='MySQL';
#ignore index: 不使用xx索引
explain select * from tb_user ignore index(index_user) where profession='MySQL';
#force index: 必须使用xx索引
explain select * from tb_user force index(index_user) where profession='MySQL';

 9.覆盖索引：尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在索引中已经全部能够找到），减少select *  ，容易导致回表查询。

10.前缀索引：当字段类型为字符串（varchar、text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。此时可以将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。前缀索引会回表查询。

create index index_name on table_name(column(n) );

        前缀长度（n）：可以根据索引的选择性来决定，而选择性是指不重复的索引值（基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能是最好的。

select count(distinct email)/count(*) from tb_user;

select count(distinct substring(email,1,5))/count(*) from tb_user;

六、索引设计原则

1.针对数据量较大，且查询比较频繁的表建立索引。

2.针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。

3.尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。

4.如果是字符串类型的字段，字段的长度越长，可以针对于字段的特点，建立前缀索引。

5.尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。

6.要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也越大，会影响增删改的效率。

7.如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好的确定哪个索引最有效地用于查询。