SQL——索引

💡 索引

  在关系型数据库中，索引是一种单独的、物理上的对数据库表中的一列或多列的值进行排序的一种存储结构，他是某个表中的一列或着若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单（类似于图书目录，通过图书页码迅速找到所需内容）。一个索引是存储的表中一个特定列的值数据结构。索引是在表的列上创建。索引包含一个表中列的值，并且这些值存储在一个数据结构中。简单来说，不需要加内存，不用改程序，不用调SQL，查询速度就可以提高百倍千倍。

优点 ：提高数据的检索速度，降低数据排序的成本。
缺点 ：会降低更新表的速度。

举例说明：创建一张表，表中存了8000000条数据，这时查询数据的速度就会变慢。

SELECT * FROM emp WHERE ename='hPBmJv'

会发现数据检索的时间是4.591s，非常的慢。
在表的存放文件夹中可以看到，emp表现在的大小是655360KB。

之后创建索引，再次查询

-- ename_index :索引名称
-- ON emp(ename) ：表示在emp表的ename列创建索引
CREATE INDEX ename_index ON emp(ename);
-- 然后再次查询数据
SELECT * FROM `emp` WHERE ename='QZABhi'

  可以看到，检索速度明显变快，只用了0.034s就完成查询。但是相应的，我们再次查看emp表的.ibd文件，就会发现此时的文件大小为827392KB,可以得出创建索引，索引本身也会占用空间。

总结:在创建索引前，emp.ibd文件大小为655360KB，检索时间为4.591s；创建索引后，文件内存增加至827392KB，检索速度提升至0.034s。

常见的索引类型包括以下几种：

1. 主键索引（Primary Key Index）：主键索引是一种特殊的唯一索引，它默认为表的主键，即用来唯一标识每一行数据的索引。主键索引可以保证数据的唯一性，并且可以快速定位到指定的行。
2. 唯一索引（Unique Index）：唯一索引也是一种保证数据唯一性的索引，与主键索引不同的是，唯一索引可以定义多个，一个表中可以有多个唯一索引。唯一索引可以加速数据查找，并且可以帮助避免重复数据的插入。
3. 普通索引（Normal Index）：普通索引是最基本的索引类型，它可以加速数据的查找。对于非唯一值，普通索引可以在查找操作中提供一个快速的匹配过程。
4. 全文索引（Fulltext Index）：全文索引是用于大文本字段的索引类型，可以支持高速的文本搜索。

🎈 索引机制

💧索引的原理

没有索引为什么会慢？

  如果SQL查询语句没有合适的索引，那么它将执行全表扫描（full table scan），这种方式需要对整个表进行遍历，查找符合查询条件的数据，会产生以下几种性能问题：

1. 大量的I/O操作：全表扫描会将整个表的数据读入内存，导致额外的 I/O 操作（包括磁盘读取、内存分配等），占用大量计算资源。
2. 低效的查询速度：由于全表扫描需要遍历整个表，因此查询速度较慢，尤其是当表中数据量很大时。
3. 阻塞其他操作：全表扫描的过程中，会占用大量的 CPU 和内存资源，从而影响数据库的性能，阻塞其他操作的执行。

  相比之下，使用索引能够降低查询的时间复杂度，快速定位到符合条件的数据，提高查询效率。因为索引可以直接跳过不满足查询条件的数据，只对符合条件的数据进行读取和处理，避免了无谓的数据读取和处理。此外，索引还可以减少 I/O 操作、缩短查询时间、提高数据库的并发能力。

使用索引为什么会快？

  SQL 索引是一种用于加速数据库查询操作的数据结构，它可以降低查询所需的时间复杂度，提高查询效率，从而优化数据库的性能。索引通常是通过 B-tree 或哈希表等数据结构来实现的。

  B-tree 索引是 SQL 中最常见的索引类型之一，它是一种基于平衡树（balanced tree）的数据结构，可以快速定位需要的数据。B-tree 索引在每个节点上存储一些关键字和指向下层子节点的指针，根据关键字的大小对节点进行排序，并将其分裂成较小的节点，从而保证平衡树的深度不会太大。

  当查询语句中包含索引列时，数据库会通过索引，快速地定位到相关数据的物理位置，然后进行数据的读取和处理。这种方式称为索引扫描（index scan），相比于全表扫描（table scan），可以大大减少查询所需的时间和资源。

  索引的选择是一个重要的问题。虽然索引可以加速查询操作，但过多的索引可能会降低数据库的性能，因为索引的维护也需要消耗计算和存储资源。一般来说，应该仅在必要的列上创建索引，优先考虑高频使用、复杂查询或者需要排序 / 分组的列，并且要注意避免对索引列进行计算或类型转换等操作，否则可能会导致索引失效。

  此外，当数据量较小时，索引对性能的提升可能并不显著；而当数据量很大或者查询条件较为复杂时，合理使用索引可以大大提高查询效率和性能。因此，在使用索引时需要根据具体情况进行权衡和调整，以达到最佳的性能优化效果。

💧索引的代价

虽然SQL索引可以提高查询性能和效率，但也会增加一些代价，主要包括以下几个方面：

1. 空间代价：索引需要占用一定的磁盘空间，特别是当表中数据很大时，会消耗大量的存储空间。此外，在建立索引的过程中，需要对索引列进行排序和分组，也会增加额外的存储开销。
2. 更新代价：当表中数据被修改时，索引也需要相应地进行维护和更新。这会耗费计算资源和时间，并且在大规模更新操作时，可能会导致锁定表的情况，影响数据库的并发性能。
3. 查询代价：虽然索引可以提高查询效率，但是在某些情况下，索引查询可能比全表扫描更慢，尤其是当索引列不优化或者数据分布不均匀时。此时索引查询还需要读取索引文件、I/O 操作等，会增加一定的查询代价。
4. 维护代价：当索引不再需要或者失效时，也需要对其进行删除或者清理操作。此操作也需要消耗一定的时间和资源，如果索引失效不及时清理，还可能产生额外的性能损失和异常情况。

  因此，在选择和使用索引时，需要根据数据库的实际情况和需求，综合考虑上述代价因素，做出合理的决策和管理调整，以实现优化查询性能的目标。

🎈索引使用

在使用索引前，先创建一张表测试，方便使用索引举例。

CREATE TABLE IF NOT EXISTS student(id INT,`name` VARCHAR(32)
);

💧查询索引

-- 查询表是否有索引，table_name是需要查询索引的表名。
-- 在sql中，可以使用 SHOW INDEX 或者 SHOW INDEXES 语句来查询表的索引信息
SHOW INDEX FROM table_name;
-- 例查询刚才创建的表的索引
SHOW INDEX FROM student;

💧创建索引

💍主键索引

在 SQL 中添加主键索引有两种方式：在表创建时添加主键索引，或者在表创建后通过 ALTER TABLE 语句添加主键索引。

1. 在表创建时添加主键索引

CREATE TABLE table_name (column1 datatype PRIMARY KEY,column2 datatype,column3 datatype,   
);

在创建表时添加主键索引，需要在列名和数据类型之间加上 PRIMARY KEY 关键字，表示该列是主键，并且需要建立主键索引。

2. 在表创建后添加主键索引

ALTER TABLE table_name ADD PRIMARY KEY (column1);

在表创建后添加主键索引，需要使用 ALTER TABLE 语句，使用 ADD PRIMARY KEY 子句来指定添加主键索引的列名。

需要注意的是，每个表只能有一个主键，所以无论是在表创建时还是在表创建后添加主键索引，都只能为一个列指定主键属性。

💍唯一索引

在 SQL 中添加唯一索引有两种方式：在表创建时添加唯一索引，或者在表创建后通过 ALTER TABLE 语句添加唯一索引。

1. 在表创建时添加唯一索引

CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,UNIQUE (column1)
);

  在创建表时添加唯一索引，需要在列名和数据类型之后添加 UNIQUE 关键字，并指定需要建立唯一索引的列名。这样就可以为该列或多个列建立唯一索引。

2. 在表创建后添加唯一索引

ALTER TABLE table_name ADD UNIQUE (column1);

在表创建后添加唯一索引，需要使用 ALTER TABLE 语句，使用 ADD UNIQUE 子句来指定添加唯一索引的列名。

需要注意的是，每个表可以有多个唯一索引，但每个唯一索引只能包含一个或多个列，且不能重复。

💍普通索引

在 SQL 中添加普通索引有两种方式：在表创建时添加普通索引，或者在表创建后通过CREATE INDEX或ALTER TABLE语句添加普通索引。

1. 在表创建时添加普通索引

CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,INDEX index_name (column1)
);

在创建表时添加普通索引，需要使用 INDEX 关键字指定需要建立索引的列名，并指定索引名称。这样就可以为该列或多个列建立普通索引。

2. 在表创建后添加普通索引

CREATE INDEX index_name ON table_name (column1);

或者

ALTER TABLE table_name ADD INDEX index_name (column1);

在表创建后添加普通索引，可以使用 CREATE INDEX 或 ALTER TABLE 语句。其中，CREATE INDEX 语句用于创建新的索引，而 ALTER TABLE 语句用于在现有表中添加索引。需要指定索引名称和需要建立索引的列名。

  需要注意的是，每个表可以有多个普通索引，但每个普通索引只能包含一个或多个列。普通索引可以在等值查询（如 = 和 IN 等操作）和范围查询（如 <、>、BETWEEN 等操作）时提高查询效率。

💍全文索引

  在 SQL 中添加全文索引需要先确保使用的数据库支持全文索引功能，如 MySQL、SQL Server 等，在此基础上可以通过以下步骤添加全文索引：

1. 创建包含全文索引列的表

CREATE TABLE table_name (column1 datatype,column2 datatype,column3 datatype,FULLTEXT (column1, column2)
);

在创建表时指定需要包含全文索引的列，并使用 FULLTEXT 关键字表示这是一个全文索引列

2. 添加全文索引

ALTER TABLE table_name ADD FULLTEXT (column1, column2);

在已有的表中添加全文索引，使用 ALTER TABLE 语句，并指定需要添加全文索引的列。

3. 使用全文索引进行查询

  使用全文索引进行查询，需要使用 MATCH AGAINST 函数。该函数会根据关键词匹配全文索引列的内容，并返回匹配度得分，可以单独使用也可以与其他条件合并使用。

SELECT * FROM table_name WHERE MATCH (column1, column2) AGAINST ('search_keyword');

注意：全文索引通常只适用于大型文本数据，如文章、博客等。对于结构化数据（如数值、日期等），使用普通索引或主键索引会更有效率。同时，全文索引可能会占用较多的存储空间和内存资源，需要谨慎使用。

💧删除索引

💍主键索引

使用 ALTER TABLE 语句来删除表中的主键索引，具体步骤如下：

1. 查看表中的主键约束名

SHOW INDEX FROM table_nmae;
-- 或者使用下面的语句
SHOW CREATE TABLE table_name;

在结果中找到主键的约束名，通常以 PRIMARY KEY 关键字开头，形如 PRIMARY KEY (column_name)。

2. 删除主键索引

ALTER TABLE table_name DROP PRIMARY KEY;

其中 PRIMARY KEY 表示要删除的主键约束名，可以替换为实际的约束名。

  注意：删除主键索引会同时删除主键约束，如果该列还存在外键依赖，则无法删除主键索引。此外，删除主键索引会影响到已有数据的完整性，需要谨慎操作。因此，在删除主键索引之前，最好先备份数据并确认没有外键依赖。

💍唯一索引

使用 ALTER TABLE 语句来删除表中的唯一索引，具体步骤如下：

1. 查看表中的唯一索引名

使用以下 SQL 语句查看表中的唯一索引名：

SHOW CREATE TABLE table_name;
-- 或者使用下面的语句
SHOW CREATE TABLE table_name;

在结果中找到需要删除的唯一索引，通常以 UNIQUE KEY 关键字开头，形如 UNIQUE KEY index_name (column_name)。

2. 删除唯一索引

使用以下 SQL 语句删除唯一索引：

ALTER TABLE table_name DROP INDEX index_name;

其中 index_name 表示需要删除的唯一索引名称，可以替换为实际的索引名称。

注意：删除唯一索引会影响到已有数据的完整性，需要谨慎操作。因此，在删除唯一索引之前，最好先备份数据并确认该列没有对应的外键依赖。

💍普通索引

你可以使用 ALTER TABLE 语句来删除表中的普通索引，具体步骤如下：

1. 查看表中的普通索引名

使用以下 SQL 语句查看表中的普通索引名：

SHOW CREATE TABLE table_name;
-- 或者使用下面的语句
SHOW CREATE TABLE table_name;

在结果中找到需要删除的普通索引，通常以 KEY 关键字开头，形如 KEY index_name (column_name)。

2. 删除普通索引

使用以下 SQL 语句删除普通索引：

ALTER TABLE table_name DROP INDEX index_name;

其中 index_name 表示需要删除的普通索引名称，可以替换为实际的索引名称。

注意：删除普通索引会影响到查询性能，因此需要谨慎操作。在删除索引之前，最好先确认该索引没有被其他查询语句使用。

💍全文索引

使用 ALTER TABLE 语句来删除表中的全文索引，具体步骤如下：

1. 查看表中的全文索引名

使用以下 SQL 语句查看表中的全文索引名：

SHOW CREATE TABLE table_name;

在结果中找到需要删除的全文索引，通常以 FULLTEXT KEY 关键字开头，形如 FULLTEXT KEY index_name (column_name)。

2. 删除全文索引

使用以下 SQL 语句删除全文索引：

ALTER TABLE table_name DROP INDEX index_name;

其中 index_name 表示需要删除的全文索引名称，可以替换为实际的索引名称。

注意：删除全文索引会影响到查询性能和搜索功能，因此需要谨慎操作。在删除索引之前，最好先确认该索引没有被其他查询语句或搜索功能使用。

🎈 创建索引规则

在 SQL 中，创建索引有以下几个规则：

1. 索引只应该针对经常被查询的列创建，而不是所有列都要创建索引。

2. 索引的名称应该具有描述性，以便于识别和维护，并且名称应该唯一。

3. 索引应该基于选择性较高的列进行创建。选择性是指该列中包含的唯一值的数量与表中总记录数之比。选择性较高的列通常可以提供更好的搜索性能，因为它们可以过滤掉大量的记录。

4. 如果多个列一起经常用于查询或排序，则应该将它们放在一个联合索引中，而不是各自创建单独的索引。这样可以提高这些列的查询效率。

5. 如果某个列包含 NULL 值的百分比很高，那么就不应该为其创建索引，因为索引对 NULL 值的过滤效果很差。

6. 如果表中的数据量很小，则可能没有必要为其创建索引，因为索引的建立和维护本身也会带来一定的开销。

7. 对于频繁更新的表，过多的索引可能会增加更新时间和磁盘空间占用，因此需要谨慎创建索引。

总之，在创建索引时需要根据具体情况进行综合考虑，权衡索引的使用效果和成本，以达到优化查询性能的目的。