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

于 2024-09-08 11:57:53 发布
阅读量975 收藏 10
点赞数 26
分类专栏： MySQL Linux 文章标签： mysql 数据库 linux 运维 服务器
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44666786/article/details/141754377
版权
Linux 同时被 2 个专栏收录
12 篇文章 0 订阅
订阅专栏
MySQL
8 篇文章 0 订阅
订阅专栏

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 子句中，或者常用于过滤数据，这种情况非常适合创建索引。索引可以显著减少查询时的扫描行数，从而提高查询速度。
  1. 需要 排序或分组的字段
  • 当 某个字段经常出现在 ORDER BY 或 GROUP BY 子句中时，可以为其创建索引以优化排序或分组的效率。索引可以帮助数据库在查询时更快地找到排序所需的数据，而不是全表扫描后再排序。
  1. 外键字段
  • 外键字段用于关联不同表之间的数据。在外键字段上创建索引可以加速联表查询（JOIN操作） ，使数据之间的关联操作更加高效。

  • 示例：订单表中有 customer_id 字段，该字段是用户表 user_id 的外键。在 customer_id 字段上创建索引有助于快速获取用户订单。
  1. 组合查询条件
  • 如果查询时经常需要基于多个条件进行过滤，可以为多个字段创建组合索引。组合索引在多字段查询中能够大幅减少全表扫描，提高查询效率。组合索引要由于单值索引

  • 示例：电商订单表中，用户可能经常按 customer_id 和 order_date 两个字段查询用户的历史订单记录。为 customer_id 和 order_date 创建组合索引可以加快查询。
  1. 大数据量的表

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

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

思考题

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

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

  1. 小数据量的表

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

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

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


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

  • 示例：
    定期批量删除的历史记录表，频繁的批量操作会导致索引频繁调整，带来额外的开销。
  1. 包含大量重复数据的字段

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

  • 示例：
    一个商品表中的 status字段，只有“在售”和“下架”两种状态值。这个字段具有高重复性，索引在过滤时无法发挥其应有的作用。

    性别字段（仅有“男”和“女”两种值）或布尔值字段（例如 is_active），索引的选择性低，不适合创建索引。
  1. 频繁变动的无序数据

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

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

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

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

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

66678666
关注
  • 26
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL高级篇——索引失效的11种情况
vincewm的博客
05-21 2万+
索引优化思路、要尽量满足全值匹配、最佳左前缀法则、主键插入顺序尽量自增、计算、函数导致索引失效、类型转换（手动或自动）导致索引失效、范围条件右边的列索引失效、不等于符号导致索引失效、is not null、not like无法使用索引、左模糊查询导致索引失效、“OR”前后存在非索引列，导致索引失效、不同字符集导致索引失败，建议utf8mb4
MySQL高级篇——索引简介
qq_43499848的博客
10-22 5380
介绍MySQL中的索引内容，助推小伙伴们在面试中通关！
MySQL高级篇——索引失效案例
★★光亭★★
04-14 5956
MySQL高级篇——索引失效案例
MySQL高级篇——索引的数据结构
热门推荐
★★光亭★★
04-11 1万+
MySQL高级篇——索引的数据结构
MySQL高级篇——索引的创建与设计原则
★★光亭★★
04-11 8357
MySQL高级篇——索引的创建与设计原则
MySQL高级篇——索引解决查询相关的优化问题
★★光亭★★
04-15 6050
MySQL高级篇——索引解决查询相关的优化问题
MySQL高级篇——存储引擎和索引
vincewm的博客
04-23 2004
MyISAM：不支持外键和事务，表锁不适合高并发，只缓存索引，内存要求低，查询快MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务、行级锁、外键，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。5.5之前默认的存储引擎优势是访问的速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用针对数据统计有额外的常数存储。故而 count(*) 的查询效率很高表名.frm 存储表结构；表名.MYD 存储数据 (MYData)；
MySQL高级篇知识点——索引的数据结构
代码星辰的博客
08-18 2500
索引的数据结构
Mysql高级——索引篇
weixin_46301906的博客
05-30 1568
索引 1. 索引的概念 索引（index）是帮助 MySQL 高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据， 这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。 优点 缺点 提高数据检索效率，降低数据库的 IO 成本 索引列也是要占用空间 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗 索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELE
Java高级编程——MySQL索引实现及优化原理解析
02-25
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的...
MySQL高级篇——锁
m0_67391907的博客
09-11 567
现在很多的工具和编程语言都是多线程异步的（相互之间同时进行或交错进行），但是在某些情况下想要多个线程同步进行（前一个执行完，后一个才能执行），这时就离不开锁。在mysql中，例如最简单的如果有一列是自增的，那么表中该列同一时刻只能有一个线程在申请自增的id，此时就需要一个锁来保证同步进行，这个锁就叫自增锁。在java中，当想让一个方法成为同步方法，就在方法前加synchronized。本文，主要讲解mysql中各种各样的锁。
MySQL高级——索引 （内附索引高频面试题）
猿兄
02-01 724
目录 一、索引介绍以及使用 1.1 什么是索引？ 1.2 索引的分类？ 1.3 索引的创建、查询、删除 的方式？ 二、高频面试题（通过面试题更能了解索引） 2.1 什么是索引？ 2.2 MySQL索引是什么样的数据结构？ 2.3 MySQL中的常用的存储引擎 MyISAM与InnoDB的区别？ 2.4 如何选择存储引擎 MyISAM与InnoDB？ 2.5 MyIS...
MySQL高级知识（四）——索引结构
dh12313012的博客
05-28 351
MySQL高级知识（四）——索引结构1. B-Tree索引（即BTree）1.1. 原理图1.2. 关于时间复杂度2. B+Tree索引2.1. 原理图2.2. B+Tree优点2.3. B-Tree与B+Tree区别：3. 聚簇索引与非聚簇索引3.1. 聚簇索引含义3.2. 聚簇索引优缺点3.3. 非聚簇索引含义3.4. 区别3.5. 适用场景4. full-text全文索引4.1. 全文索引用...
策略规划：在MySQL中实现数据恢复的全面指南
2401_85339615的博客
09-04 1071
数据恢复是数据库管理中至关重要的一环，它确保在发生数据丢失或损坏的情况下，能够迅速且准确地恢复数据。在MySQL中，实现有效的数据恢复策略规划需要综合考虑备份策略、备份类型、存储管理、故障转移机制以及恢复流程。本文将深入探讨如何在MySQL中进行数据恢复的策略规划，包括策略制定、技术实现和最佳实践。
Java项目: 基于SpringBoot+mysql房产销售系统 (含源码+数据库+开题报告+答辩PPT+毕业论文)
weixin_43860634的博客
09-03 594
Java项目: 基于SpringBoot+mysql房产销售系统 (含源码+数据库+开题报告+答辩PPT+毕业论文)
MySQL数据备份的版本控制：策略、实践与自动化
2401_85341950的博客
09-04 882
数据备份的版本控制是指对数据库备份进行管理，以便可以追踪、访问和恢复到数据库的特定历史状态。数据恢复：在数据丢失或损坏的情况下快速恢复数据。审计和合规性：满足法规要求，提供数据访问和变更的历史记录。开发和测试：为开发和测试环境提供数据的特定版本。
【重学 MySQL】十四、显示表结构
最新发布
CSDN全栈领域优秀博主，阿里云博客专家，工信部权威认证物联网技术开发工程师，曾30分钟高分通过C语言二级，两天手撸代码完成EasyX飞机大战，靠接软件私活一个月换苹果电脑
09-06 623
以上就是在MySQL中显示表结构的几种常用方法。DESCRIBE或DESC命令和命令提 供了快速查看表列信息的简便方式，而查询数据库则提供了更详细、更灵活的信息获取方式。命令则适用于需要查看或复制表完整结构的情况。
使用视图方式操作MySQL数据表
m0_64148419的博客
09-06 910
学习使用视图方式操作MySQL数据表
【MySQL】批量插入数据造数-存储过程
兴焉的博客
09-05 250
日常工作中可能有针对需要对某个表进行造数，如何批量插入呢？可以使用存储过程循环结构。下面是一个存储过程以插入100条，while语句后的
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值