有关MySQL中的索引
一：索引是什么
索引是一种数据结构，用于加快数据库查询的速度和性能。
一般情况下，在数据库中的数据结构为B+树.主键作为B+树组成数据的物理结构。
为了给其他列的数据加快查找速度，因此需要为其他列添加索引，添加的索引是为其他列创建树结构。
索引要创建在经常进行查询的列，为这些列进行索引。
索引的作用：
- 加速查询：索引可以显著提高
SELECT查询的速度，尤其是在WHERE、JOIN、ORDER BY和GROUP BY等操作中。 - 唯一性约束：唯一索引可以确保某一列或列组合的值是唯一的。
- 优化排序和分组：索引可以帮助数据库快速完成排序和分组操作。
- 加速连接操作：在多表连接时，索引可以显著提高连接效率。
二：索引的使用与创建
2.1 有关索引的语法
普通索引是最常见的索引类型，用于加速对表中数据的查询。
创建索引的语法：
CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
删除索引的语法：
DROP INDEX index_name ON table_name;
修改表结构添加索引：
ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);
参数说明：
index_name：索引的名字.
一般使用index_表名_列名
table_name:表名
column1：列名
案例：
create INDEX index_stu1_stu_name on stu1(stu_name);
2.2 创建表的时候直接指定索引
我们可以在创建表的时候，你可以在 CREATE TABLE 语句中直接指定索引，以创建表和索引的组合。
CREATE TABLE table_name (column1 data_type,column2 data_type,...,INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...)
);参数说明：
table_name:表名
column1：列名
ASC：指定正序排列
DESC：倒序排列
案例：
CREATE TABLE stu2 (stu_id int(0) NOT NULL AUTO_INCREMENT PRIMARY KEY,stu_name varchar(20) ,stu_age int(0) , INDEX index_stu2_stu_name(stu_name) USING BTREE
)
2.3 唯一索引
在 MySQL 中，你可以使用 CREATE UNIQUE INDEX 语句来创建唯一索引。
唯一索引确保索引中的值是唯一的，不允许有重复值。
创建索引
CREATE UNIQUE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
2.4 聚集索引与非聚集索引
一般情况下，主键列就是聚集索引，属于物理排序，非聚集索引是单独列的数据进行排序，其他列数据是逻辑排序。
| 对比维度 | 聚集索引 | 非聚集索引 |
|---|---|---|
| 基本概念 | 决定表中数据在磁盘上的物理存储顺序，表中数据行按聚集索引键的值排序和存储，一个表只能有一个聚集索引。 | 有独立结构，包含索引键值和指向表中对应数据行的指针，不影响数据物理存储顺序，一个表可有多 个非聚集索引。 |
| 结构差异 | 通常采用B+树结构，叶子节点包含实际数据行，非叶子节点用于索引导航。 | 一般用B+树结构，叶子节点存储索引键值和指向数据行的指针，而非实际数据行。 |
| 优点 | 1.范围查询和按索引键排序的查询速度快，可直接定位数据范围，减少磁盘I/O 2.数据访问效率高，查找数据时无需额外查找操作定位数据行。 | 1.插入、删除和更新操作相对较快，仅需更新索引指针，无需移动大量数据行。 2.可创建多个，能根据不同查询需求灵活创建。 |
| 缺点 | 1.插入、删除和更新操作慢，可能导致大量数据移动以维护有序性。 2.表的创建和修改成本高，需对数据排序和重新组织，修改可能改变表物理结构。 | 1.查询效率相对较低，需先在索引中查找指针，再根据指针查找实际数据行。 2.占用额外存储空间，用于存储索引结构和指针。 |
| 使用场景 | 1.经常进行范围查询和排序的列，如日期、数值列。 2.主键列，可提高根据主键查找数据的效率。 | 1.常用于查询条件但不涉及范围查询的列，如姓名、性别列。 2.多列联合查询时，可创建复合非聚集索引。 |
| 示例 | CREATE INDEX idx_customer_id ON orders (customer_id); | CREATE INDEX idx_customer_id ON orders (customer_id); |
三：再谈索引
3.1 什么是索引？
索引是数据库中一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常使用B树及其变种B+树。
更通俗的说，索引就相当于目录。为了方便查找树中的内容，通过对内容建立索引形成目录。索引是一个文件，它是要占据物理空间的。
3.2 没有定义主键列的时候，索引是否建立？
如果未定义PRIMARY KEY你的表，MySQL有一个规则，索引所有键列在哪里NOT NULL和InnoDB使用它作为聚集索引
3.3 索引的类型有哪些？
数据库中的索引类型包括但不限于以下几种：
单列索引：针对表中单个列的索引，适用于该列的等值查询、范围查询和排序操作。
CREATE INDEX idx_name ON users (name);
**多列索引：**针对表中多个列的联合索引，适用于这些列的复合查询。
CREATE INDEX idx_name_age ON users (name, age);
**唯一索引：**确保索引列的值在整个表中是唯一的，可以加速唯一值的查询。
CREATE UNIQUE INDEX idx_email ON users (email);
**主键索引：**一种特殊的唯一索引，用于唯一标识表中的每一行，每个表只能有一个主键索引。
ALTER TABLE users ADD PRIMARY KEY (id);
**全文索引：**用于对文本类型的列进行全文搜索，能高效处理大文本字段的查找,适用于char,varchar,text类型的列。
CREATE FULLTEXT INDEX idx_content ON articles (content);
**空间索引：**用于地理空间数据类型的列，例如Point、LineString和Polygon，可以加速对地理空间数据的搜索和分析
CREATE SPATIAL INDEX idx_location ON places (location);
3.4 索引的分类
1.按数据结构分类
B+树索引：MySQL中默认和最常用的索引类型，适用于范围查询和全表扫描。
Hash索引：适用于等值查询，不适用于范围查询
CREATE INDEX idx_name ON users (name) USING HASH;
全文索引：专门用于处理文本数据的全文搜索
R树索引：用于多维空间数据的索引，如GIS应用
2.按物理存储分类
聚集索引：数据的物理顺序与索引的逻辑顺序一致，每个表只能有一个聚集索引
非聚集索引：数据的物理顺序与索引的逻辑顺序不一致，一个表可以有多个非聚集索引
3.按字段特性分类
主键索引：建立在主键上的索引，保证数据的唯一性和非空性。
唯一索引：保证索引列中的每个值都是唯一的，允许有空值。
普通索引：提高查询性能的索引，允许重复值和空值。
前缀索引：对字符串的前几个字符创建索引，减少系统空间开销。索引占用的存储空间
4.按字段个数分类
单列索引：对单个列创建的索引
联合索引：对多个列创建的索引，适用于多列一起使用的查询
3.5 索引的优缺点？
索引的优点
提高检索速度：索引可以显著加快数据检索的速度，尤其是在大型数据集中，通过减少磁盘I/O操作次数，降低系统I/O资源消耗
加速排序和分组操作：索引可以优化ORDER BY和GROUP BY子句的执行速度，因为索引本身是有序的
提高连接性能：索引能加快多表连接（JOIN）操作的执行效率
保证数据完整性和唯一性：通过唯一性约束或主键约束，索引可以帮助保证数据的唯一性和完整性
支持快速查找：索引提供快速的查找功能，使得查询更加灵活和高效
索引的缺点
占用存储空间：索引会占用额外的存储空间，这可能会随着数据量的增加而显著增大。
维护成本高：索引需要定期维护，包括创建、更新和删除索引，这可能会增加数据库的负担和维护成本
增加写操作的时间：对表进行插入、更新和删除操作时，索引也需要进行相应的更新，这可能会增加写操作的时间
不适用于所有查询：并非所有的查询都适合使用索引，有些查询可能会因为索引而变得更慢。
索引失效：如果索引选择不当或者使用不当，可能会导致索引失效，从而影响查询性能。
3.6 索引适用与不适用场景总结
| 场景 | 适用索引 | 不适用索引 |
|---|---|---|
| 频繁查询的列 | 常用于 WHERE 条件中的列。 | 很少用于查询条件的列。 |
| 连接操作的列 | 常用于 JOIN 操作的列（外键列）。 | 不参与连接操作的列。 |
| 排序和分组的列 | 常用于 ORDER BY 或 GROUP BY 的列。 | 不用于排序或分组的列。 |
| 高基数列 | 列中唯一值较多（如用户ID、邮箱等）。 | 低基数列（如性别、状态标志等）。 |
| 大表查询 | 数据量大的表，查询性能较差时。 | 数据量小的表（全表扫描可能更快）。 |
| 唯一性约束 | 需要确保数据唯一性的列（如主键、唯一索引）。 | 不需要唯一性约束的列。 |
| 频繁更新的列 | - | 频繁更新的列（索引维护开销大）。 |
| 写多读少的表 | - | 写操作远多于读操作的表（索引会增加写操作的开销）。 |
| 包含函数的列 | - | 查询中对列使用函数或表达式（如 WHERE UPPER(name) = 'JOHN'）。 |
| 文本搜索 | 全文索引适用于 CHAR、VARCHAR 或 TEXT 类型的列。 | 非全文搜索的文本列（如长文本）。 |
| 复合索引的顺序 | 复合索引中，将最常用的列放在前面。 | 复合索引中，顺序不合理（如将低基数列放在前面）。 |
| NULL 值较多的列 | - | 列中包含大量 NULL 值（索引效果不明显）。 |
| 数据分布均匀的列 | 数据分布均匀的列（如时间戳、序列号）。 | 数据分布不均匀的列（如布尔值列）。 |
| 频繁插入和删除的表 | - | 频繁插入和删除的表（索引维护开销大）。 |
| 查询返回大部分数据的场景 | - | 查询返回表中大部分数据（全表扫描可能更快）。 |
3.7 有关场景的详细说明
1. 适用索引的场景
- 频繁查询的列：如果某些列经常出现在
WHERE条件中，为其创建索引可以显著提高查询性能。 - 连接操作的列：在多表连接时，为连接条件的列创建索引可以加速连接操作。
- 排序和分组的列：如果查询中经常使用
ORDER BY或GROUP BY，为这些列创建索引可以加速排序和分组操作。 - 高基数列：高基数列（唯一值较多的列）适合创建索引，因为索引的选择性高，查询效率提升明显。
- 大表查询：对于数据量大的表，索引可以显著减少查询时间。
- 唯一性约束：主键和唯一索引可以确保数据的唯一性，同时提高查询性能。
2. 不适用索引的场景
- 频繁更新的列：索引会增加写操作的开销，频繁更新的列不适合创建索引。
- 写多读少的表：如果表的写操作远多于读操作，索引的维护成本可能超过其带来的性能提升。
- 包含函数的列：如果查询中对列使用函数或表达式，索引可能无法被使用。
- 低基数列：低基数列（如性别、状态标志等）不适合创建索引，因为索引的选择性低，效果不明显。
- NULL 值较多的列：如果列中包含大量
NULL值，索引的效果会大打折扣。 - 频繁插入和删除的表：频繁插入和删除操作会导致索引频繁更新，增加维护开销。
- 查询返回大部分数据的场景：如果查询返回表中大部分数据，全表扫描可能比使用索引更快。
3.8 主键索引与唯一索引的区别
| 特性 | 主键索引 | 唯一索引 |
|---|---|---|
| 唯一性 | 必须唯一，且不能为 NULL | 必须唯一，但允许 NULL |
| 空值处理 | 不允许 NULL | 允许 NULL |
| 数量限制 | 每个表只能有一个 | 每个表可以有多个 |
| 用途 | 唯一标识每一行记录 | 确保某一列或列组合的值唯一 |
| 索引类型 | 通常是聚集索引 | 非聚集索引 |
| 自动创建 | 定义主键时自动创建 | 需要显式创建 |
选择使用场景
- 使用主键索引：
- 当需要唯一标识表中的每一行记录时。
- 当列的值不允许为
NULL时。 - 当需要与其他表建立外键关系时。
- 使用唯一索引：
- 当需要确保某一列或列组合的值唯一，但不需要作为主键时。
- 当列的值可以为
NULL时。
