MySQL入门系列11-索引

一、概念

索引是帮助MySQL高效获取数据的数据结构。数据库除了存储数据之外，还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，当我们在查找数据的时候，就可以在这些数据结构上实现高级查找算法，快速查找我们需要的数据，这种数据结构就是索引。

在没有索引的情况下，查询数据就需要从第一行开始扫描，一直扫描到最后一行，我们称为全表扫描，性能很低。

在建立索引的情况下，我们的索引数据就是一个类似二叉树的数据结构，我们只需要查询很少的次数就可以获取到我们需要的数据。

使用索引的优点和缺点如下表所示:

二、索引数据结构

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构。

不同的存储引擎对于索引结构的支持情况也有所不同：

我们平常所说的索引，如果没有特别指明，一般都是指B+树结构组织的索引。

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

三、索引分类

在MySQL数据中，索引的具体类型主要有以下几类:

四、相关操作

1.创建索引

语法:

CREATE [ UNIQUE | FULLTEXT ] INDEX 索引名 ON 数据表名 (字段1,字段2,... );

SQL语句:

CREATE UNIQUE INDEX name_unique ON t_user (name );

2.查看索引

语法:

SHOW INDEX FROM 数据表名;

SQL语句:

SHOW INDEX FROM t_user;

3.删除索引

语法:

DROP INDEX 索引名 ON 表名;

SQL语句:

DROP INDEX name_unique ON t_user;

五、索引失效的情况

1.最左前缀法则

如果索引了多列，要遵守最左前缀法则。最左前缀法指的是：查询会从索引的最左列开始，最左边的索引列必须存在，否则索引全部失效。

比如我们表中创建了一个多列的联合索引。

CREATE INDEX idx_name_age ON t_user ( name, age );

我们在查询数据的时候name必须存在，如果不存在那么索引就不会生效。

我们加上name的查询条件，发送索引生效。

2.模糊查询

尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引会失效。

头部模糊查询，索引没有生效:

尾部模糊查询，索引生效:

六、使用优化

当字段类型为字符串时，有时候这些字段的值非常大，如果直接对这些字段建立索引的话，这会让索引变得很大，查询时浪费大量的磁盘IO,影响查询效率，这个时候我们可以将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法:

create index 索引名 on 表名(字段名(索引长度)) ;

SQL语句:

CREATE INDEX idx_name_limit ON t_user (name ( 5 ));

设计原则:

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

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

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

4.如果是字符串类型的字段，字段的长度很长，可以建立前缀索引。

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

6.要控制索引的数量，数量过多也会影响性能。