文章目录
1. 索引的优缺点
MySQL索引是一种数据结构，用于提高数据库查询效率。它可以快速定位到表中符合特定条件的数据行，从而加快查询速度。索引通常是根据表中的一个或多个字段创建的，它们存储了对应字段值的排序方式和位置信息，使得数据库系统可以更快地定位到所需的数据行。
1.索引的优点
- 提高检索速度：索引可以加快数据检索的速度，特别是在大型数据表中，可以显著减少查询所需的时间。
- 加速排序：索引可以加速排序操作，例如ORDER BY和GROUP BY，提高查询效率。
- 提高数据的唯一性和完整性：可以通过唯一索引和主键索引来确保数据的唯一性和完整性。
- 支持快速查找：索引可以提供快速的查找功能，使得查询更加灵活和高效。
- 减少IO访问次数：索引可以减少磁盘IO访问次数，提高数据库的性能。
2.索引的缺点
- 占用空间：索引会占用额外的存储空间，特别是在大型数据表中，索引可能会占用大量的磁盘空间。
- 维护成本高：索引需要定期维护，包括创建、更新和删除索引，这可能会增加数据库的负担和维护成本。
- 增加写操作的时间：对表进行插入、更新和删除操作时，索引也需要进行相应的更新，这可能会增加写操作的时间。
- 不适用于所有查询：并非所有的查询都适合使用索引，有些查询可能会因为索引而变得更慢，因此需要根据实际情况选择合适的索引策略。
- 索引失效：如果索引选择不当或者使用不当，可能会导致索引失效，从而影响查询性能。
2. 创建索引准则
- 选择合适的字段：选择常用于查询、排序和连接操作的字段来创建索引，例如经常出现在WHERE子句、JOIN子句、ORDER BY子句和GROUP BY子句中的字段。
- 考虑字段的基数：基数是指字段中唯一值的数量。选择基数高的字段来创建索引，因为基数越高，索引的选择性越好，查询效率也就越高。
- 避免过多的索引：不要为每个字段都创建索引，因为过多的索引会增加维护成本、占用存储空间，并可能导致索引失效。根据实际情况选择需要索引的字段。
- 考虑查询的性能需求：根据查询的性能需求选择合适的索引类型。例如，对于需要快速定位的查询，可以选择B树索引；对于范围查询和模糊查询，可以选择哈希索引。
- 注意索引的顺序：对于复合索引，要注意字段的顺序。通常情况下，将区分度高的字段放在前面，以提高索引的选择性。
- 避免在频繁更新的字段上创建索引：频繁更新的字段会导致索引的频繁更新和维护，可能会影响性能。因此，不建议在频繁更新的字段上创建索引。
- 定期维护索引：定期检查并维护索引，包括重新组织索引、重新构建索引等操作，以确保索引的有效性和性能。
3. 索引的分类
在MySQL中，索引可以根据不同的分类方式进行逻辑分类和物理分类。
1.逻辑分类
逻辑分类是根据索引的使用方式和作用范围来进行分类的，常见的逻辑分类包括：
- 单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
- 组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
- 唯一索引：索引列中的值必须是唯一的，但是允许为空值.
- 主键索引：是一种特殊的唯一索引，不允许有空值。
- 全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。
- 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
2.物理分类
物理分类是根据索引在存储引擎中的实现方式来进行分类的，常见的物理分类包括：
- B-Tree索引：基于B-Tree数据结构实现的索引，适用于范围查找和排序。
- Hash索引：基于哈希表实现的索引，适用于等值查找。
- 全文索引：全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。
- 空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
4. 索引实现
索引是在存储引擎中实现的，也就是说不同的存储引擎，会采用不同的索引实现方式。常见的MySQL存储引擎包括MyISAM、InnoDB、Memory等，它们在索引实现上有一些区别。
MyISAM
- MyISAM 使用 B+ 树作为索引结构，包括主键索引和辅助索引（非主键索引）。
- 主键索引是按照主键顺序构建的 B+ 树，非主键索引也是构建在相应的字段值上的 B+ 树。
- MyISAM 的索引文件和数据文件是分开存储的，因此索引文件和数据文件是两个独立的文件。
InnoDB
- InnoDB 也使用 B+ 树作为索引结构，包括主键索引和辅助索引。
- InnoDB 的主键索引和数据文件是绑定在一起的，因此主键索引也称为聚簇索引，它的叶子节点存储的是完整的行数据。
- 辅助索引（非主键索引）的叶子节点存储的是对应行数据的主键值，而不是完整的行数据。
- InnoDB 的索引文件和数据文件是交叉存储的，即数据和索引都存储在同一个表空间文件中。
Memory（也称为 Heap）
- Memory 存储引擎主要是基于内存的存储，不会将数据写入磁盘，因此适用于一些对读写速度要求高、数据量小且不需要持久化的场景。
- Memory 存储引擎的索引是使用哈希表实现的，因此查找速度非常快。
- 由于 Memory 存储引擎不支持持久化，一旦 MySQL 服务器关闭，存储在内存中的数据就会丢失。
InnoDB 和 MyISAM 在索引实现上都采用了 B+ 树结构，但是在主键索引和数据存储上有一些差异。InnoDB 的主键索引和数据文件是绑定在一起的，而 MyISAM 的索引文件和数据文件是分开存储的。
5. 操作索引
1.创建索引
在 MySQL 中，可以使用 CREATE INDEX 语句创建索引。语法如下：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
ON table_name (column1 [(length)] [ASC|DESC], column2 [(length)] [ASC|DESC], ...)
[USING index_type];
其中：
- UNIQUE：表示创建唯一索引，索引列的值必须唯一，不允许重复。
- FULLTEXT：表示创建全文索引，适用于对文本类型的列进行全文搜索。
- SPATIAL：表示创建空间索引，用于空间数据类型的列。
- index_name：指定索引的名称。
- table_name：指定要在哪个表上创建索引。
- (column1, column2, …)：指定要在哪些列上创建索引，可以是一个或多个列。
- length：可选参数，指定索引的前缀长度。
- ASC|DESC：可选参数，表示升序或降序排序，默认为升序。
- USING index_type：可选参数，指定索引的类型，常见的有 BTREE、HASH 等。
例如，要在 users 表的 name 列上创建一个普通的索引，可以使用以下语句：
CREATE INDEX idx_name ON users (name);
要在 products 表的 price 列上创建一个唯一索引，可以使用以下语句：
CREATE UNIQUE INDEX idx_price ON products (price);
2.删除索引
在 MySQL 中，可以使用 DROP INDEX 语句删除索引。语法如下：
ALTER TABLE table_name DROP INDEX index_name;
其中：
- table_name：指定要删除索引的表名。
- index_name：指定要删除的索引名称。
例如，要删除名为 idx_name 的索引，可以使用以下语句：
ALTER TABLE users DROP INDEX idx_name;