Mysql高级篇（中）——索引介绍

本文主要是介绍Mysql高级篇（中）——索引介绍，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

一、索引本质

索引本质 是 一种数据结构，它用于加速数据库的查询操作。通过组织和存储数据的方式，索引可以让数据库更高效地查找和检索数据，而不需要逐行扫描整个表。可以理解为 排好序的快速查找结构

二、索引优缺点

优点	缺点
提高查询速度，减少数据检索的时间	占用额外的磁盘空间
通过索引可以 加快排序操作	增加了数据写入的开销，插入、更新、删除操作需要额外时间维护索引
支持快速的随机读取和范围查询	如果创建过多的索引，可能导致索引失效或查询优化器选择不当的索引
对于表连接操作，可以显著提升JOIN性能	对小表或者数据量较少的场景，索引带来的性能提升有限
能够通过 唯一索引保证数据的唯一性	维护多个索引的开销较大，影响性能
索引可以 减少磁盘I/O操作，提升系统效率	索引失效会导致全表扫描，反而降低查询性能
在大型数据集上查询时可以显著减少响应时间	对频繁变动的大型表，索引维护成本较高

三、索引分类

（1）按数据结构分类

（2）按功能分类

（3） 按存储引擎分类

（4） 按存储方式分类

（5） 按使用方式分类

四、 索引基本语法

（1）创建索引

创建 普通索引 ：

CREATE INDEX index_name ON table_name (column1, column2, ...);-- 示例
CREATE INDEX idx_user_name ON users (name);

创建 唯一索引 ：

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);-- 示例
CREATE UNIQUE INDEX idx_email_unique ON users (email);

创建 组合索引 ：

CREATE INDEX index_name ON table_name (column1, column2, ...);-- 示例
CREATE INDEX idx_name_age ON users (name, age);

创建 全文索引 ：

CREATE FULLTEXT INDEX index_name ON table_name (column1, column2, ...);-- 示例
CREATE FULLTEXT INDEX idx_content_fulltext ON articles (content);

创建表时定义索引 ：

-- 在创建表时添加索引： 可以在 CREATE TABLE 语句中直接定义索引
CREATE TABLE table_name (column1 datatype,column2 datatype,...INDEX index_name (column1),UNIQUE INDEX unique_index_name (column2),FULLTEXT INDEX fulltext_index_name (column3)
);-- 示例
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255),email VARCHAR(255),INDEX idx_name (name),UNIQUE INDEX idx_email_unique (email)
);

（2）查看索引

查看 表中索引：

SHOW INDEX FROM table_name;-- 示例
SHOW INDEX FROM users;

（3）删除索引

删除 表中索引：

DROP INDEX index_name ON table_name;-- 示例
DROP INDEX idx_user_name ON users;

（4）ALTER 关键字创建/删除索引

创建 普通索引 ：

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);-- 示例
ALTER TABLE users ADD INDEX idx_name (name);

创建 唯一索引 ：

ALTER TABLE table_name ADD UNIQUE index_name (column1);-- 示例
ALTER TABLE users ADD UNIQUE idx_email (email);

创建 组合索引 ：

ALTER TABLE table_name ADD INDEX index_name (column1, column2, ...);-- 示例
ALTER TABLE users ADD INDEX idx_name_age (name, age);

创建 全文索引 ：

ALTER TABLE table_name ADD FULLTEXT index_name (column1);-- 示例
ALTER TABLE articles ADD FULLTEXT idx_content_fulltext (content);

删除索引 ：

ALTER TABLE table_name DROP INDEX index_name;-- 示例
ALTER TABLE users DROP INDEX idx_name;

总结： 没有直接修改索引的SQL语句，如果要修改索引，必须先删除，再新增

五、适合创建索引的情况

1. 数据库默认为 主键和唯一键建立索引
2. 频繁作为查询条件的字段 适合创建索引

• 如果 某个字段经常出现在 SELECT 查询的 WHERE 子句中，或者常用于过滤数据，这种情况非常适合创建索引。索引可以显著减少查询时的扫描行数，从而提高查询速度。

3. 需要 排序或分组的字段

• 当 某个字段经常出现在 ORDER BY 或 GROUP BY 子句中时，可以为其创建索引以优化排序或分组的效率。索引可以帮助数据库在查询时更快地找到排序所需的数据，而不是全表扫描后再排序。

4. 外键字段

• 外键字段用于关联不同表之间的数据。在外键字段上创建索引可以加速联表查询（JOIN操作） ，使数据之间的关联操作更加高效。
  
  
• 示例：订单表中有 customer_id 字段，该字段是用户表 user_id 的外键。在 customer_id 字段上创建索引有助于快速获取用户订单。

5. 组合查询条件

• 如果查询时经常需要基于多个条件进行过滤，可以为多个字段创建组合索引。组合索引在多字段查询中能够大幅减少全表扫描，提高查询效率。组合索引要由于单值索引
  
  
• 示例：电商订单表中，用户可能经常按 customer_id 和 order_date 两个字段查询用户的历史订单记录。为 customer_id 和 order_date 创建组合索引可以加快查询。

6. 大数据量的表

当表中的 数据量非常大 时，查询速度可能会下降，索引可以有效减少查询扫描的行数，从而提高性能。

总结：索引主要用于加速查询，特别是频繁进行查询、排序、过滤的大数据表中，或在高选择性、多条件组合的查询情况下。适当创建索引不仅可以提高查询性能，还能保证数据的完整性和一致性（如主键和唯一键）。

思考题

六、不适合创建索引的情况

1. 小数据量的表

如果表的数据量非常小，全表扫描的速度可能比使用索引更快，因为使用索引还需要额外的跳转操作。对于小表而言，索引反而会增加存储开销和查询复杂度，而不会明显提升性能。

2. 频繁更新、插入或删除数据的字段或表

索引会随着数据的变化而更新，特别是在 INSERT、UPDATE 和 DELETE 操作时，索引的维护会增加额外的性能开销。如果某个表的字段数据经常发生更新或插入操作，索引的维护成本可能会超过其带来的查询优化效果。


如果表经常被大批量地更新或删除记录，索引的维护代价会非常高，影响整体性能。在这种场景下，不适合创建索引。

• 示例：
  定期批量删除的历史记录表，频繁的批量操作会导致索引频繁调整，带来额外的开销。

3. 包含大量重复数据的字段

索引对于高重复值的字段无效，因为索引无法有效减少数据扫描。例如，某个字段中80%的记录都具有相同的值，索引无法通过选择性有效过滤记录。

• 示例：
  一个商品表中的 status字段，只有“在售”和“下架”两种状态值。这个字段具有高重复性，索引在过滤时无法发挥其应有的作用。
  
  性别字段（仅有“男”和“女”两种值）或布尔值字段（例如 is_active），索引的选择性低，不适合创建索引。

4. 频繁变动的无序数据

无序且频繁变动的数据（例如自动生成的随机数、时间戳）不适合创建索引，因为每次数据插入或更新时都需要重新排列和维护索引，导致性能下降。

• 示例：
  系统日志表中的时间戳字段，如果频繁插入新日志且要求以时间顺序排列，那么在timestamp 字段上创建索引会导致插入时的额外开销

5. 查询字段很少使用

如果某个字段很少用于查询或过滤操作，即使创建了索引，也无法得到明显的性能提升，反而会增加不必要的存储和维护开销。

• 示例：
  员工表中的 profile_description 字段很少用于查询，如果为其创建索引，可能会带来不必要的索引维护负担，而查询效率不会有显著改善。
  

总结，不适合创建索引的情况主要涉及频繁的写操作、低选择性或大量重复值的字段、小数据量的表或大范围查询。创建不必要的索引不仅不会提升性能，反而会增加维护成本、占用存储空间，并可能导致整体性能下降。合理地选择需要索引的字段非常关键，以避免负面影响。

这篇关于Mysql高级篇（中）——索引介绍的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---