MySQL数据库中的索引（含SQL语句）

文章目录

为什么要用索引

假设有一张表，表中有100万条数据，这100万条数据在硬盘上是存储在数据页上的，一页数据大小为16k。存储100万条数据那么就需要数据页，假设其中有一条数据是“id为7900”的，那么如果要查询这条数据，其中SQL是SELECT * FROM 表名 WHERE id = 7900。在执行这条SQL语句的时候，MySQL需要扫描全表来查询id = 7900的记录。
全表扫描就是从“数据页1”开始，向后逐页查询。对于少量数据来说，查询的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100万条数据进行逐页查询的时间是无法被用户所接受的。

索引是什么

索引是帮助MySQL高效获取数据的数据结构。
是排好序的快速查找的数据结构。

索引就是类似于书的目录。

数据库在存储数据本身以外，还有维护着一个满足特定查找算法的数据结构，这些数据结构就以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

索引的原理

索引的目的在于提高查询效率，与我们查询图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典、查火车车次，飞机航班等。

本质上都是：通过不断地缩小想要获取数据的范围来筛选出最最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们就可以总是用同一种查找方式来锁定数据。

索引类似于书的目录，在一本书前面加上目录，查找内容时就不必逐页翻阅就能够快速地找到所要查找的内容。借助索引，执行查询时就不必扫描整个表就能够快速地找到所需要的数据。

优点

索引提高了数据检索的效率，降低了数据库IO成本。

通过索引列对数据进行排序，降低了数据排序的成本，降低了CPU的消耗。

缺点

实际上，索引本质上也是一张表，这个表保存了主键与索引之间的字段，并指向实体表的记录，所以索引列也是要占用磁盘的空间。

虽然索引大大提高了插叙的速度，但同时也会降低更新表数据的速度。例如：INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一次索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

创建索引的原则

索引虽好，但也不能乱用。

什么情况下需要索引

1. 主键自动建立唯一索引。
2. 频繁作为查询条件的字段应该创建索引（WHERE 后面的语句）。
3. 查询中与其它表关联的字段，外键关系建立索引。
4. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度分组中的字段。

什么情况下不需要索引

1. 表记录过少
2. 经常性的增删改的表。虽然有提高了查询的速度，但同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySQL不仅要保存数据，还要保存一下索引的文件。（所以在遇到这种情况下，就会分表，分表就是将读、写的数据进行分离）
3. WHERE条件里用不到的字段不会创建索引
4. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列表建立索引，某个数据列包含许多重复的内容，建立索引没有太大的实际效果。

索引的分类

主键索引

就是设定主键后数据库会自动建立索引，一个表只能有一个主键。

ALTER TABLE 表名 ADD PRIMARY KEY 表名（列名）；

删除主键索引：

ALTER TABLE 表名 DROP PRIMARY KEY；

单值索引

就是一个索引只包含单个列，一个表可以有多个单列索引。
加这个索引肯定是比不加索引要快的。

创建单值索引：

CREATE INDEX 索引名 ON 表名（列名）；

删除索引：

DROP INDEX 索引名；

唯一索引

索引列的值必须唯一，允许为null；

CREATE UNIQUE INDEX 索引名 ON 表名 （列名）;

删除索引：

DROP INDEX 索引名 ON 表名;

组合索引（复合索引）

即一个索引包含多个列，在数据库操作期间，复合索引比单值索引所需要的开销是更小的（对于相同的多个列键索引）。

当表的行数远大于索引列的数目时可以使用复合索引。

创建复合索引

CREATE INDEX 索引名 ON 表名（列1，列2，…）；

删除索引

DROP INDEX 索引名 ON 表名;

组合索引最左前缀原则
例如表中有a，b，c 3列，为a，b两列创建组合索引，那么在使用时需要满足最左侧索引原则。即在使用组合索引的列为条件的时候，是必须要出现最左侧列作为条件，否则索引是不生效的。

例如：

SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; #索引生效

SELECT * FROM 表名 WHERE b = ' ' AND b = ' ' ; #索引生效

SELECT * FROM 表名 WHERE a = ' ' AND b = ' ' ; # 索引生效

SELECT * FORM 表名 WHERE b = ' ' AND c = ' ' ; # 索引不生效

全文索引(仅在MySQL8之后有)

是需要模糊查询的，一般索引是无效的，这个时候就可以使用全文索引。

比如： WHERE name LIKE %J%，这样查询的时候，即使name列添加了索引，但也会索引失效的。所以是不建议使用的，在MySQL8当中就建议使用全文索引。

创建全文索引列：

CREATE FULLTEXT INDEX 索引名 ON 表名（列名） WITH TARSER ngram;

使用全文索引：

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST(‘输入的参数’);

全文索引的插叙效率是比使用LIKE来查询要快的，所以在MySQL8之后是建议使用全文索引的。

查找索引：

SHOW INDEX FROM 表名；

索引的数据结构

在MySQL数据库中的InnoDB引擎是使用B+树来进行数据存储的。

B+树由二叉查找树、平衡二叉树（AVLTree）和平衡多落查找树（B-Tree）逐步优化而来的。使其更适合实现外存索引结构。

B+数的特点：

1. 它是排好序的，一个节点可以存储多个数据。
2. 非叶子节点是不存储数据的，只存储索引，是可以放更多的索引。
3. 数据都是存放在在叶子节点中的。
4. 所有的叶子节点之间都是有一个链指针的。

就是使用B+树使得树是横向扩展的，让树的高度降低了。在叶子节点也可以存储多个数据。

MySQL所以使用B+树，因为索引是用来加快查询的，而B+数通过对数据进行排序所以是可以提高查询速度，然后通过一个节点可以存储多个元素，从而可以使得B+树的高度不会太高。并且叶子节点之间存在指针，可以很好的支持全表扫描，范围查找等SQL语句。

聚簇索引和非聚簇索引

聚簇索引
就是找到了索引就找到了需要的数据，那么这个索引就是聚簇索引，所以主键就是聚簇索引。

非聚簇索引
索引的存储和数据的存储时分离的，也就是意味着找到了索引但没有找到数据，需要根据索引上的值（主键）再次回表查询，非聚簇索引也叫做辅助索引。

举例：
先创建一个表，用来说明说明情况下是聚簇索引和非聚簇索引：

CREATETABLE student (`id`INTPRIMARYKEYNOTNULLAUTO_INCREMENTCOMMENT'主键id',`name`VARCHAR(50)COMMENT'学生姓名',`age`INTNOTNULLDEFAULT0COMMENT'学生年龄',KEY`idx_name`(`name`))

创建的数据库表中，主键id是一个索引，给age添加了索引。

① 直接根据主键查询获取所有字段数据，此时主键就是聚簇索引。因为主键对应的索引叶子节点存储的就是id的所有字段值。

SELECT * FROM student WHERE id = 1;

② 根据name查询所有信息，name本身就是一个唯一索引，但查询的数据包括了所有的数据，所以当命中name索引的时候，该索引的节点的数据存储的就是主键的ID，然后需要ID再查询一次。
这就是非聚簇索引。

SELECT * FROM student WHERE name = “张三”;

索引就是再单独的创建一张表，在这个表中查找数据，然后拿到ID在返回所有数据的表中查询所有的数据。

③ 根据name查询的时候，我们只查询name的值，不查询其他的信息。这种查询就是命中name索引，直接返回name的值，因为所需要的数据就是索引，此时不需要会表查询。这种场景就是非聚簇索引。

SELECT name FROM student WHERE name = “张三”；

拓展
在MySQL中InnoDB引擎的索引和文件是存放在一起的，找到索引就可以找到数据，是聚簇式设计。（默认是聚簇设计的，当你加上其他的索引，根据变化就成为了非聚簇式）

而MyISAM引擎采用的是非聚簇式设计，索引文件和数据文件不在同一个文件中。（不管怎么创建，它本质上就是非聚簇的设计）

因为在MyISAM当中，它是在MYD中存储文件的数据；在MYI中存储文件的索引；在sdi中存储表的结构。