Mysql索引

前言

什么是索引?

索引(index) 是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。

为什么使用索引？

使用索引可以很大程度上提高数据库的查询速度。

不使用索引，MySQL 就必须从第一条记录开始读完整个表，直到找出相关的行。表越大，查询数据所花费的时间就越多。如果表中查询的列有一个索引，MySQL 就能快速到达一个位置去搜索数据文件，而不必查看所有数据，这样将会节省很大一部分时间。

索引的优点总结如下：

• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在实现数据的参考完整性方面可以加速表与表之间的连接。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
• 每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。

MySQL中的索引

在 InnoDB 中，索引分为：

• 聚簇索引
• 非聚簇索引

聚簇索引（聚集索引）

聚簇索引是一种数据存储方式，InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。
这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。
InnoDB的表要求必须要有聚簇索引。

聚簇索引的优点：

• 可以把相关的数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件可能都会导致一次磁盘I/O。
• 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
• 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。

聚簇索引的缺点：

• 聚簇索引最大限度提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没有那么重要了，聚簇索引也就没什么优势了。
• 插入速度严重依赖插入顺序。按照主键的顺序插入式加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
• 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。
• 基于聚簇索引的表在插入新行，或者主键被更新导致移动行的时候，可能面临叶分裂的问题。
• 二级索引访问需要两次索引查找，而不是一次。

非聚簇索引

与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构
![在这里插入图片描述](https://img-blog.csdnimg.cn/2020112117304347.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MTk0Mzg4,size_16,color_FFFFFF,t_70#pic_center)

MySQL索引底层原理

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造。
B-Tree结构的索引值和data数据分布在整棵树结构中 ，每个节点可以存放多个索引值及对应的data数据 ，树节点中的多个索引值从左到右升序排列
![在这里插入图片描述](https://img-blog.csdnimg.cn/20201121172745515.png?x-oss-
process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MTk0Mzg4,size_16,color_FFFFFF,t_70#pic_center)
B树的搜索：从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。

B+Tree结构特点：

• 非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
• 叶子节点包含了所有的索引值和data数据
• 叶子节点用指针连接，提高区间的访问性能
  ![在这里插入图片描述](https://img-blog.csdnimg.cn/20201121172817782.png?x-oss-
  process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM2MTk0Mzg4,size_16,color_FFFFFF,t_70#pic_center)
  相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

MySQL索引的使用

创建索引

三种方式：

使用CREATE INDEX创建，语法如下：

CREATE INDEX indexName ON tableName (columnName(length));

例如我们对colunmName这一列创建一个长度为16的索引：

CREATE INDEX indexName ON tableName (colunmName(16));

使用ALTER语句创建，语法如下：

ALTER TABLE tableName ADD PRIMARY KEY (column_list); #该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tableName ADD UNIQUE indexName (column_list);#这条语句创建索引的值必须是唯一的（除了NULL外，NUL可能会出现多次)。
ALTER TABLE tableName ADD INDEX indexName (column_list);#添加普通索引，索引值可出现多次。
ALTER TABLE tableName ADD FULLTEXT indexName (column_list);#该语句指定了索引为FULLTEXT，用于全文索引。

查看索引

可以通过show语句查看索引：

SHOW INDEX FROM tableName;

删除索引

使用ALTER命令可以删除索引，例如：

ALTER TABLE tableName DROP INDEX indexName;

使用DROP命令删除，如下：

DROP INDEX indexName ON tableName; 

分析索引的使用情况

explain命令 ：可以查看SQL的执行计划，分析SQL是否正确使用索引。

student 表如下：

student 表存储的数据如下：

用explain查看下面SQL语句的执行计划：

explain分析SQL执行过程时：

possible_key： 表示SQL执行可能会命中的索引

key： 表示执行过程真正使用的索引名称

rows： 表示查询数据影响的行数

分析： 当前查询possible_keys、key都为null，则未命中索引，

rows=5表示查询了5条数据，即进行了全表查询，当前查询操作对每一行数据都进行比较。

当数据量较大时（400万时 ），全表扫描效率很低。

给name字段添加索引：

用explain查看上述同样语句的执行过程：

添加索引之后，再次查询 “ZhaoLei” 信息，只在表中查询一行数据即可得到，当前查询命中 idx_name索引。

最左匹配原则

MySQL采用b+树原理查询，MySQL使用联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。