MySQL索引

🏆有志者，事竟成🏆

---

简介

索引是数据库中用于提高查询性能的数据结构。它类似于书籍的目录，可以帮助数据库系统快速定位数据表中的特定行，而无需扫描整个表。这样可以大大减少查询所需的时间，尤其是在处理大量数据时。

索引又分为不同的类型，不同类型的索引适用于不同的场景。常见的索引类型包括主键索引、唯一索引、普通索引、全文索引等。主键索引用于唯一标识表中的每一行，唯一索引确保列中的所有值都是唯一的。

索引可以在创建表时定义，也可以在表已存在的情况下添加。使用CREATE INDEX语句可以为表的列创建索引。

语法如下：

CREATE INDEX index_name ON table_name (column1, column2, ...);

索引可以显著提高查询性能，特别是对于大型数据表。它们通过减少数据库引擎需要扫描的数据量，从而降低查询的时间复杂度。尽管索引提高了查询速度，但在插入、更新和删除操作时可能会引入一些额外的开销。因为在这些操作中，索引也需要进行相应的更新。

因此选择适当的索引列很重要。一般来说，选择常用于查询条件的列作为索引列是明智的。过多或不必要的索引可能会导致性能下降。总体而言，索引是数据库中重要的性能优化工具，但在使用时需要谨慎权衡性能提升和维护成本。

初见索引

前面说了索引可以显著提高查询性能，有无索引差别到底有多大？可以用一个例子来查看有无索引的区别。首先需要在MySQL中生成一个海量数据表，如下：

-- 创建海量表
CREATE TABLE massive_table (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    description TEXT
);

-- 设置变量
SET @row := 0;

-- 插入大量数据
INSERT INTO massive_table (id, name, description)
SELECT
    @row := @row + 1 AS id,
    CONCAT('Name', @row),
    CONCAT('Description for ', @row)
FROM
    information_schema.columns t1
    JOIN information_schema.columns t2
    JOIN information_schema.columns t3
LIMIT 8000000;

这个例子中，通过使用information_schema.columns表来连接，生成了足够多的行，并插入到massive_table表中。

查看前五个数据。

由此，可以进行查找测试，对于没有索引的字段进行查找，如下：

从结果可以看出，没有索引时我们进行查找是非常慢的，那么有索引的话，又会是什么结果呢？如下：

• 在MySQL中，主键字段默认会自动带有索引。主键是用于唯一标识表中每一行的字段，而索引则用于加速对表的检索。因此，MySQL会自动在主键字段上创建一个称为主键索引的索引。
• 当你在创建表时指定某个列为主键时，MySQL会自动为该列创建一个主键索引。主键索引确保表中的每个主键值都是唯一的，并且这样的索引能够提高对主键的检索速度。

由此可见，索引对于查询的提升不是一点半点的，所以正确合理的利用索引对于管理数据库是非常重要的。

索引操作

主键索引

主键索引是一种特殊的索引，用于唯一标识数据库表中的每一行数据。主键是表中的一列或一组列，其值对于表中的每个行都是唯一的，并且不允许为空（NULL）。主键索引用于确保表中的每个记录都可以通过主键值进行唯一标识和快速检索。

• 创建主键索引
  在创建表时，可以使用primary key关键字定义主键索引。示例：

  -- 创建表并添加主键索引
  create table example_table (
      id int primary key,
      name varchar(255),
      -- other columns
  );
  

  上述示例中，id列被定义为主键，主键索引将会自动创建。

如果对于已存在的表，想要为某列添加主键索引，你可以使用alter table语句来修改表结构并添加主键索引。如下示例：

-- 添加主键索引
alter table existing_table
add primary key (existing_column);

上述语句假设已有一个表 existing_table，并且要将 existing_column 列作为主键。执行这个语句后，数据库系统会为该列创建一个主键索引。
请注意以下几点：

1. 主键索引要求列中的值是唯一的，因此在执行上述语句之前，确保 existing_column 列中没有重复的值。
2. 如果表中已经存在主键索引，可能需要先删除现有的主键索引，然后再添加新的主键索引。
3. 修改表结构的操作可能影响到表中的数据，因此在执行之前请确保做好备份，尤其是在生产环境中。

在大多数数据库系统中，定义主键时通常会自动创建主键索引。主键索引通常用于在表中快速查找或检索具有唯一标识的行。在查询中，可以使用主键索引进行等值查询。例如：

select * from example_table
where id = 1;

上述查询将使用主键索引快速定位id为1的行。

主键索引的作用：

• 提供唯一性约束，确保表中的每个记录都有唯一的标识。
• 用于加速通过主键值的等值查询。
• 通常与外键关系一起使用，确保表之间的关联关系。

在设计数据库时，通常会选择一个唯一、稳定、不可变的列作为主键，以确保主键索引的有效性。主键索引在数据库中扮演了非常重要的角色，用于确保数据的一致性和唯一性。

唯一索引

唯一索引（Unique Index）是一种保证索引列中的所有值都是唯一的索引类型。与主键索引类似，唯一索引可以用来确保表中的数据行具有唯一的值，但唯一索引允许包含空值（NULL）。

• 创建唯一索引
  在创建表时或创建表后，可以使用unique关键字定义唯一索引。示例：

  -- 创建表并添加唯一索引
  create table example_table (
      id int primary key,
      username varchar(255) unique,
      -- other columns
  );
  

  上述示例中，username列被定义为唯一索引，确保表中的每个username值都是唯一的。

• 添加唯一索引
  在已存在的表中，可以使用alter table语句添加唯一索引。示例：

  -- 添加唯一索引
  alter table example_table
  add unique (username);
  

• 唯一索引的作用：

  • 确保索引列的值是唯一的，不允许重复。
  • 可以加速对索引列的等值查询。
  • 允许包含空值，但空值在唯一索引中仅允许出现一次。

在查询中，唯一索引可用于加速检索。例如，使用where条件中的唯一索引列进行等值查询。

select * from example_table
where username = 'example_username';

唯一索引通常用于确保表中某一列的值都是唯一的，例如用户名、电子邮件地址等。这有助于维护数据的一致性和避免重复值的插入。在设计数据库时，根据业务需求选择使用唯一索引来满足数据完整性和查询性能的要求。

普通索引

普通索引（Non-Unique Index）是一种用于加速数据检索的索引，与唯一索引不同，普通索引允许索引列中存在重复的值。它可以用于提高查询性能，但不对索引列的唯一性进行强制。

• 创建普通索引
  在创建表时或创建表后，可以使用index关键字定义普通索引。示例：

  -- 创建表并添加普通索引
  create table example_table (
      id int primary key,
      column_name varchar(255),
      index idx_column_name (column_name),
      -- other columns
  );
  

  上述示例中，column_name列被定义为普通索引，以加速对该列的检索。

• 添加普通索引
  在已存在的表中，可以使用alter table语句添加普通索引。示例：

  -- 添加普通索引
  alter table example_table
  add index idx_column_name (column_name);
  

• 普通索引的作用：

  • 加速对索引列的等值查询、范围查询和排序操作。
  • 允许索引列中存在重复的值，不强制唯一性。
  • 在某些情况下，可以提高查询性能，减少数据库引擎需要扫描的数据量。

在查询中可以使用where条件中的普通索引列进行查询。

select * from example_table
where column_name = 'example_value';

普通索引通常用于需要频繁进行查询操作的列，以提高查询性能。在设计数据库时，根据实际查询需求和业务场景选择是否使用普通索引。

注意，过多的索引可能会增加写操作的开销，因此需要权衡查询性能和维护成本。

全文索引

全文索引（Full-Text Index）是一种用于对文本数据进行全文本搜索的索引类型。它允许在文本列上执行更复杂的文本搜索操作，包括全文搜索、模糊搜索和关键词搜索等。全文索引通常用于处理包含大量自然语言文本的列，如文章内容、评论等。

• 创建全文索引
  在创建表时，可以使用fulltext关键字定义全文索引。示例：

  -- 创建表并添加全文索引
  create table fulltext_table (
      id int primary key,
      content text,
      fulltext(content)
  );
  

  

  上述示例中，content列被定义为全文索引，以便进行全文本搜索。

• 查询使用全文索引
  在查询中，可以使用match against语句进行全文搜索。例如：

  select * from fulltext_table 
  where match(content) against('indexing');
  

  
  

  上述查询将返回包含搜索词（‘indexing’）的行。

• 全文索引的作用：

  • 支持全文本搜索，允许用户执行更复杂的文本匹配操作。
  • 可用于执行模糊搜索，寻找相关的文本内容。
  • 提供关键词搜索功能，有助于提高文本搜索的灵活性。

全文索引的限制：

• 一般来说，全文索引的使用是在支持全文搜索的数据库引擎中，例如MySQL的InnoDB引擎。
• 全文索引不适用于所有类型的文本数据，例如短文本字段可能不会受益于全文索引。

在设计数据库时，如果需要对文本进行复杂的搜索操作，全文索引是一个强大的工具。需要根据实际业务需求和数据库引擎的支持情况来决定是否使用全文索引。

查询索引

在MySQL中，可以使用 show index 或 show keys 语句来查询表的索引信息。例如查询表的所有索引可以使用如下语句：

show index from fulltext_table;

或者

show keys from fulltext_table;

这将返回 fulltext_table 表的所有索引信息，包括索引名称、列名、唯一性等。

请注意，具体的语法可能因数据库系统而异。上述示例适用于MySQL数据库。在其他数据库系统中，类似的查询语句可能有所不同。

删除索引

在MySQL中，可以使用 drop index 语句删除索引。如下示例：

• 删除表的指定索引：

    alter table fulltext_table
    drop index content;
  

  

  替换 content 为你想要删除的索引名称。上述语句将删除 fulltext_table 表中名为 content 的索引。

• 删除表的主键索引：

  如果要删除表的主键索引，可以使用以下语句：

  alter table fulltext_table
  drop primary key;
  

  

  这将删除 fulltext_table 表的主键索引。

• 删除表的所有索引：

  如果希望删除表的所有索引，可以通过以下方式重新定义表结构，不包括索引定义：

  create table new_fulltext_table as
  select * from fulltext_table;
  

  

  然后，删除原表并将新表重命名为原表：

  drop table fulltext_table;
  alter table new_fulltext_table rename to fulltext_table;
  

  这种方法会创建一个不包括索引的新表，并最终将其重命名为原表。请谨慎使用此方法，因为它将删除表的所有索引。在执行删除索引的操作之前，请确保你了解删除索引可能对查询性能产生的影响，并确保已经做好了必要的备份。

总结—索引创建原则

在设计数据库时，索引是一项关键的优化技术，能够显著提高数据库查询性能。合理的创建索引对于数据库管理是非常重要的，因此提供如下10条索引创建的原则和最佳实践：

原则	说明
选择合适的列	选择那些经常用于查询条件和连接条件的列来创建索引。通常选择主键、外键、经常用于where子句的列以及经常用于连接的列。
避免过多的索引	虽然索引能够提高查询性能，但每个索引都需要额外的存储空间和维护成本。不要过度索引，仅创建必要的索引。
考虑查询的性能需求	不同类型的查询可能需要不同的索引。例如，全文搜索需要全文索引，范围查询可能需要普通索引，等值查询可能需要唯一索引。
理解数据库引擎	不同的数据库引擎对索引的实现和性能影响有所不同。在选择索引类型时，了解数据库引擎的特性，例如InnoDB、MyISAM等。
主键索引的选择	在设计表时，考虑选择一个适当的主键。通常，自增长的整数列是一个不错的选择，因为它们对查询性能和索引效率有积极的影响。
避免在小表上创建过多索引	对于小型表，可能不需要太多的索引，因为它们的整体查询性能可能已经足够快。
定期维护和优化索引	随着时间的推移，数据库中的数据分布可能发生变化。定期进行索引优化和重建，以确保索引的有效性。
了解查询执行计划	使用数据库工具查看查询执行计划，以了解查询是如何利用索引的。这有助于调整索引以优化查询性能。
注意复合索引的顺序	对于复合索引（多列组成的索引），索引列的顺序很重要。根据查询的特点选择合适的列顺序，以支持最频繁的查询。
使用覆盖索引	覆盖索引是一种可以满足查询需求而无需回表（访问实际数据行）的索引。这可以提高查询性能。

总的来说，索引的设计应该根据具体的查询需求和数据库引擎来进行调整。在创建索引之前，仔细分析查询模式、业务需求和数据分布是非常重要的。

最后的最后，如果文章对你有帮助的话，就帮忙点上一个👍呗！感谢支持。