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语句，如果要修改索引，必须先删除，再新增
五、适合创建索引的情况
- 数据库默认为
主键和唯一键建立索引 - 频繁作为查询条件的字段 适合创建索引
- 如果 某个字段经常出现在
SELECT查询的WHERE子句中，或者常用于过滤数据，这种情况非常适合创建索引。索引可以显著减少查询时的扫描行数，从而提高查询速度。
- 需要
排序或分组的字段
- 当 某个字段经常出现在
ORDER BY或GROUP BY子句中时，可以为其创建索引以优化排序或分组的效率。索引可以帮助数据库在查询时更快地找到排序所需的数据，而不是全表扫描后再排序。
外键字段
外键字段用于关联不同表之间的数据。在外键字段上创建索引可以加速联表查询（JOIN操作），使数据之间的关联操作更加高效。- 示例：
订单表中有customer_id字段，该字段是用户表 user_id的外键。在customer_id字段上创建索引有助于快速获取用户订单。
组合查询条件
- 如果查询时经常需要基于
多个条件进行过滤，可以为多个字段创建组合索引。组合索引在多字段查询中能够大幅减少全表扫描，提高查询效率。组合索引要由于单值索引- 示例：电商订单表中，用户可能经常按
customer_id和order_date两个字段查询用户的历史订单记录。为customer_id和order_date创建组合索引可以加快查询。
大数据量的表
当表中的
数据量非常大时，查询速度可能会下降，索引可以有效减少查询扫描的行数，从而提高性能。
总结：索引主要用于加速查询，特别是频繁进行查询、排序、过滤的大数据表中，或在高选择性、多条件组合的查询情况下。适当创建索引不仅可以提高查询性能，还能保证数据的完整性和一致性（如主键和唯一键）。
思考题
六、不适合创建索引的情况
小数据量的表
如果
表的数据量非常小，全表扫描的速度可能比使用索引更快，因为使用索引还需要额外的跳转操作。对于小表而言，索引反而会增加存储开销和查询复杂度，而不会明显提升性能。
频繁更新、插入或删除数据的字段或表
索引会随着数据的变化而更新，特别是在INSERT、UPDATE和DELETE操作时，索引的维护会增加额外的性能开销。如果某个表的字段数据经常发生更新或插入操作，索引的维护成本可能会超过其带来的查询优化效果。
如果表经常被大批量地更新或删除记录，索引的维护代价会非常高，影响整体性能。在这种场景下，不适合创建索引。
- 示例：
定期批量删除的历史记录表，频繁的批量操作会导致索引频繁调整，带来额外的开销。
包含大量重复数据的字段
索引对于高重复值的字段无效，因为索引无法有效减少数据扫描。例如，某个字段中80%的记录都具有相同的值，索引无法通过选择性有效过滤记录。
- 示例：
一个商品表中的status字段，只有“在售”和“下架”两种状态值。这个字段具有高重复性，索引在过滤时无法发挥其应有的作用。
性别字段（仅有“男”和“女”两种值）或布尔值字段（例如 is_active），索引的选择性低，不适合创建索引。
频繁变动的无序数据
无序且频繁变动的数据（例如自动生成的随机数、时间戳）不适合创建索引，因为每次数据插入或更新时都需要重新排列和维护索引，导致性能下降。
- 示例：
系统日志表中的时间戳字段，如果频繁插入新日志且要求以时间顺序排列，那么在timestamp字段上创建索引会导致插入时的额外开销
查询字段很少使用
如果
某个字段很少用于查询或过滤操作，即使创建了索引，也无法得到明显的性能提升，反而会增加不必要的存储和维护开销。
- 示例：
员工表中的profile_description字段很少用于查询，如果为其创建索引，可能会带来不必要的索引维护负担，而查询效率不会有显著改善。
总结，不适合创建索引的情况主要涉及频繁的写操作、低选择性或大量重复值的字段、小数据量的表或大范围查询。创建不必要的索引不仅不会提升性能，反而会增加维护成本、占用存储空间，并可能导致整体性能下降。合理地选择需要索引的字段非常关键，以避免负面影响。