目录
引言
在数据库世界中，MySQL作为一种广泛应用的关系型数据库管理系统，其性能优化是一项至关重要的工作。在这之中，索引作为优化查询性能的关键利器，扮演着举足轻重的角色。今天，让我们一起深入探讨MySQL索引的原理、类型、优缺点以及如何有效地使用它们。
一、索引的原理与作用
（一）索引的概念
索引（index）本质上是一种特殊的数据结构（如B+树、哈希索引等），是帮助MySQ高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
（二）索引的原理
以二叉树结构为例
无索引查找
当输入条件查找时，无索引查找会遍历整张表，将匹配的数据返回，此过程叫做全表扫描，效率极低
有索引查找
以二叉树数据结构为例，先匹配age=25，需要的数值比自己小会向下匹配，匹配到对应的数据后将数据返回，这样极大的减少了查找数据的时间
索引就像是书籍的目录，可以帮助数据库快速定位到所需要的数据行，从而极大地提高查询性能。在关系型数据库中，最常见且广泛应用的索引结构是B树（B-tree）家族，尤其是在MySQL的InnoDB存储引擎中广泛使用的B+树。
（三）索引的作用
加速查询速度： 索引为数据库表中的一列或多列数据建立了一个有序的数据结构，如同书的索引页一样，帮助数据库快速定位到符合条件的数据行，而不是对整张表进行线性扫描。尤其在处理大量数据的表时，索引可以显著提高查询效率。
提高排序性能： 当查询请求包含ORDER BY或GROUP BY子句时，如果排序的列有索引，那么数据库可以直接利用索引来完成排序，这通常要比没有索引时进行排序更加高效。
简化JOIN操作： 如果JOIN操作中涉及的列都有索引，数据库可以通过索引快速匹配两个表中的数据，从而减少JOIN操作所需的时间。
唯一性验证： 当索引被定义为唯一索引（UNIQUE INDEX）时，数据库系统会在插入或更新数据时自动验证数据的唯一性，确保表中不会有重复的键值。
限制数据规模：在某些情况下，如覆盖索引（Covering Index），索引不仅能定位到数据所在的页面，而且索引本身就已经包含了查询所需的全部数据，因此数据库可以直接从索引中获取结果，无需访问实际的数据行，从而降低了I/O开销。
二、索引的结构与分类
（一）索引的结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含以下几种
| 索引结构 | 描述 |
| B+Tree索引 | 最常见的索引类型，大部分引擎都支持 B+树索引 |
| Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
| R-tree(空间索引) | 空间索引是MVISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用 较少 |
| Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
InnoDB存储引擎选择使用B+tree索引结构
相对于二叉树，层级更少，搜索效率高
对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低;
相对Hash索引，B+tree支持范围匹配及排序操作
（二）索引的分类
| 分类 | 含义 | 特点 | 关键字 |
| 主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
| 普通索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键词 ，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
| 组合索引 | 一个索引关联多个字段 | 可以有多个 |
三、索引的操作
索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为key，索引通过存储引擎实现 。在创建索引之前建好库和表结构，并添加相应数据
（一）创建索引
1.普通索引
普通所以的创建语法有三种
1.1 直接创建
create index 索引名 on 表名 (字段……) ;
创建索引之后可以使用：show index from 表名；指令查看索引
或者使用：show create table emp;通过查看建表语句，查看索引
此时索引就创建成功了
1.2 修改表结构创建索引
使用alter指令
alter table 表名 add index 索引(字段名……)；
1.3 建表时指定索引
create table 表名 ( 字段1 数据类型,字段2 数据类型[,...],index 索引名 (字段名))；
2.唯一索引
建立唯一索引的语法与创建普通索引基本一致，需要添加关键字：unique
但是需要注意的是，如果该字段的值有重复，则不能创建唯一索引
修改字段创建唯一索引
建表时创建唯一索引
3.主键索引
主键索引的创建方法只有一种，就是在建表时指定主键，会自动创建主键索引
4.组合索引
可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为select语句的 where条件是依次从左往右执行的，所以在使用select 语句查询时where条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效
查询数据时，字段相反则索引不会生效
5.全文索引
全文索引是一种特殊的数据库索引，主要用于文本字段的全文本搜索。它不同于常规的B树索引，后者主要针对单个列的精确匹配和范围查询进行优化，而全文索引则旨在高效地处理包含大量文本数据的模糊匹配和关键词搜索。
在MySQL数据库中，全文索引主要用于处理CHAR、VARCHAR或TEXT类型字段中的大量文本数据。通过全文索引，MySQL能够快速地定位包含指定词汇或短语的记录，增强了对大规模文本数据进行全文搜索的能力。
创建全文索引
也可以通过修改字段或者建表时创建全文索引，语法与创建普通索引与唯一索引基本相同，将关键字替换为：fulltext；
（二）查看索引
查看所有的方法有多种
使用：show index from 表名；指令查看
show index from 表名\G; ：竖向查看信息
字段信息如下：
| Table | 表的名称 |
| Non_unique | 如果索引内容唯一，则为0；如果可以不唯一，则为1 |
| Key_name | 索引的名称。 |
| Seq_in_index | 索引中的列序号，从1开始 |
| Column_name | 列名称。 |
| Collation | 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。 |
| Cardinality | 索引中唯一值数目的估计值。 |
| Sub_part | 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引， 则为NULL |
| Packed | 指示关键字如何被压缩。如果没有被压缩，则为NULL。 |
| Null | 是否含有空值，YSE表示有,NO表示没有 |
| Index_type | 用过的索引方法 |
| Comment | 备注 |
show keys from 表名;与show index from 表名； 查看的信息一致
直接查看建表语句查看索引
（三）删除索引
1.直接删除
使用drop删除索引
语法为：drop index 索引名 on 表名;
2.改表删除
语法为：alter table 表名 drop index 索引名;
3.删除主键索引
因为每张表只有一个主键索引，所以语法只有一种
固定语法：alter table 表名 drop primary key;
总结
（一）索引的优点与缺点
1.优点
提高查询性能：索引提供了一种快速查找路径，使得查询操作能够快速定位到所需数据，从而显著提高查询速度，特别是对于大型表而言，索引的性能优势尤为突出。
减少磁盘I/O：索引通过减少数据库在物理磁盘上的读取次数来节省I/O资源，避免了全表扫描带来的性能瓶颈。
排序与分组更快：如果索引本身就包含了排序或分组操作所需要的字段，数据库可以直接利用索引来完成排序或分组，而无需额外排序操作。
唯一性验证：对于唯一性索引（UNIQUE INDEX），数据库在插入数据时可以自动检测并避免重复值的插入，维持数据的唯一性。
2.缺点
存储空间占用：创建索引会额外占用存储空间，索引文件的大小可能接近于数据表本身，特别是在大型表上创建多个索引时，空间占用会明显增大。
更新维护成本：当进行数据插入、删除和更新操作时，数据库不仅要更新数据表，还要同步更新相关的索引，这会增加写操作的执行时间和资源消耗。
索引创建和维护时间：创建索引需要花费一定的时间，尤其是在大数据量的表上创建索引。同样，重建或优化索引时也会消耗一定的时间和计算资源。
（二）索引分类
1.普通索引（Basic Index / Non-Unique Index）
特点：普通索引是最基本的索引类型，允许列中的值重复。它的主要目的是加快查询速度，但并不能保证数据的唯一性。
用途：适用于那些无需保证唯一且经常作为查询条件的列。
2.唯一索引（Unique Index）
特点：唯一索引要求索引列的值必须唯一（除了NULL，NULL可以有多个）。如果试图插入重复的值，数据库会拒绝此次操作。
用途：适用于需要确保数据唯一性的列，例如用户邮箱地址、身份证号等。不仅提升了查询效率，同时也保证了数据一致性。
3.组合索引（Composite Index）
特点：组合索引是对表中多个列创建的索引，其索引键是由多个列共同组成的。索引的顺序非常重要，MySQL会按照索引创建时列的顺序来依次匹配和利用索引。
用途：当查询条件中同时包含索引列中的多个列时，组合索引能有效提高查询效率。在查询时遵循“最左前缀匹配原则”，即查询从索引的第一个列开始匹配。
4.全文索引（Full-text Index）
特点：全文索引主要用于对文本类型数据进行全文本搜索，它可以对文本中的单词进行索引，并支持模糊匹配和自然语言搜索。
用途：适用于对长文本字段进行关键词搜索的场景，如文章内容、产品描述等。MySQL中的全文索引支持MATCH AGAINST查询语法。
5.主键索引（Primary Key Index）
特点：主键索引是唯一索引的一种特殊情况，每个表只能有一个主键索引，其索引列的值必须唯一且非空。主键索引不仅用于快速查找行，还用于确保数据完整性。
用途：主键索引是表结构设计的基础，用于唯一标识每行记录。在InnoDB存储引擎中，主键索引同时也是表的聚簇索引，决定了表中数据的物理存储顺序。
（三）创建方式
普通索引、唯一索引、组合索引、全文索引的创建语句分为以下三种
1.直接创建
create [关键字] index 索引名 on 表名 (字段1，……)；
2.改表创建
alter table 表名 add [关键字] index 索引名 (字段1，……)；
3.建表创建
create table 表名(字段1 类型，字段2 类型，…… ，index [关键字] 索引名 (字段1，……));
主键索引在建表时创建主键即可
993
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
