数据库索引的创建及失效

最新推荐文章于 2025-04-03 16:49:51 发布
最新推荐文章于 2025-04-03 16:49:51 发布
阅读量620 收藏 27
点赞数 9
分类专栏： 未雨绸缪系列 文章标签： 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/m0_46401054/article/details/140548514
版权

数据库索引

索引的概念

数据库索引是数据库管理系统（DBMS）使用的一种数据结构，用于提高检索数据的速度。没有索引，DBMS必须执行全表扫描（即检查数据库中的每一行），以找到与查询匹配的行。索引允许快速定位数据，而不必扫描每一行。索引可以显著加快数据检索速度，但会占用磁盘空间并可能在插入、删除和更新操作中降低性能。
简而言之图书的目录

索引的类型及创建

数据库索引的类型多种多样，每种类型都有其特定的用途和优点。下面是MySQL中常见的几种索引类型以及创建这些索引的示例代码：

1. 主键索引（Primary Key Index）

主键索引是唯一索引的一种，用于标识表中的每一行数据，不允许有重复值。

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

2. 唯一索引（Unique Index）

唯一索引确保列中的值是唯一的，但可以包含NULL值。

CREATE TABLE users (
    email VARCHAR(100),
    UNIQUE INDEX unique_email (email)
);

3. 普通索引（Index 或 General Index）

这是最常见的索引类型，用于加速查询。

CREATE TABLE products (
    product_id INT,
    name VARCHAR(50),
    price DECIMAL(10,2),
    INDEX idx_product_name (name)
);

4. 全文索引（Fulltext Index）

全文索引用于全文搜索，适用于较大的文本字段。

CREATE TABLE articles (
    article_id INT,
    title VARCHAR(100),
    content TEXT,
    FULLTEXT INDEX fulltext_content (content)
);

5. 复合索引（Composite Index 或 Multiple Column Index）

复合索引是在多个列上创建的索引，可以加速涉及这些列的查询。

CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    product_id INT,
    INDEX idx_order_customer_product (customer_id, product_id)
);

6. 空间索引（Spatial Index）

空间索引用于存储和检索地理数据，如纬度和经度坐标。

CREATE TABLE locations (
    location_id INT,
    geom GEOMETRY,
    SPATIAL INDEX spatial_geom (geom)
);

7. 聚簇索引（Clustered Index）

在InnoDB存储引擎中，主键索引实际上就是聚簇索引，数据行与主键索引条目存储在一起。

CREATE TABLE items (
    item_id INT AUTO_INCREMENT,
    description VARCHAR(100),
    PRIMARY KEY (item_id)
);

示例：创建和使用索引

假设我们有一个customers表，包含id, first_name, last_name, 和email字段。我们可以创建以下索引：

-- 创建普通索引
CREATE INDEX idx_first_name ON customers(first_name);

-- 创建唯一索引
CREATE UNIQUE INDEX uq_email ON customers(email);

-- 创建复合索引
CREATE INDEX idx_full_name ON customers(first_name, last_name);

使用这些索引，查询可以变得更高效：

-- 使用索引加速查询
SELECT * FROM customers WHERE first_name = 'John';

-- 使用唯一索引进行查找
SELECT * FROM customers WHERE email = 'john.doe@example.com';

-- 使用复合索引进行查找
SELECT * FROM customers WHERE first_name = 'Jane' AND last_name = 'Doe';

索引失效的原因以优化策略

  1. 使用了不等于（!= 或 <>）的查询条件：
    使用不等于的条件时，数据库可能会认为全表扫描更为高效，因此不会使用索引。例如：

    SELECT * FROM emp WHERE age != 18;

    优化策略是尽量避免在索引列上使用不等于操作。

  2. 对索引列进行了计算或函数操作：
    在索引列上进行计算或使用函数，数据库无法有效使用索引。例如：

    SELECT * FROM emp WHERE LEFT(name, 3) = 'abc';

    优化策略是尽量避免在查询条件中对索引字段使用函数或计算。

  3. 使用了 OR 条件：
    如果 OR 前后的任何一个条件列没有被索引，那么索引就会失效。例如：

    SELECT * FROM emp WHERE id = 10010 OR name = 'abcd';

    优化策略是避免在查询条件中使用 OR 语句，或者确保 OR 前后的每一个字段都被索引。

  4. 索引列上有 NULL 值：
    如果索引列上有 NULL 值，可能会导致索引失效。例如：

    EXPLAIN SELECT * FROM emp WHERE name IS NOT NULL;

    优化策略是尽量避免索引列的值为 NULL，可以在创建表的时候设置默认值或者将 NULL 替换为其他特殊值。

  5. 类型转换导致索引失效：
    查询条件中的类型和索引列的类型不一致，会导致索引失效。例如：

    EXPLAIN SELECT * FROM emp WHERE name = 123;

    优化策略是确保查询条件中的类型和索引列的类型一致。

  6. 不符合最佳左前缀原则：
    查询条件没有遵循最佳左前缀原则，导致索引失效。例如：

    EXPLAIN SELECT * FROM emp WHERE deptId = 1 AND name = 'abcd';

    优化策略是在创建索引和编写查询条件时，尽量遵循最佳左前缀原则。

  7. 索引列中的范围查询导致索引失效：
    如果查询条件中包含范围查询，可能会导致索引失效。例如：

    EXPLAIN SELECT * FROM emp WHERE age = 30 AND deptId > 1000 AND name = 'abc';

    优化策略是调整查询条件，确保范围查询的字段在索引中的位置合适。

  8. **使用了 SELECT ***：
    使用 SELECT * 会查询所有列，这可能导致需要回表查询非索引列，从而降低效率。优化策略是尽量只查询需要的列。

  9. LIKE 查询左边有 %：
    当 LIKE 查询的模式以 % 开头时，索引会失效。例如：

    SELECT * FROM emp WHERE name LIKE '%abc';

    优化策略是确保 LIKE 查询的模式不以 % 开头。

  10. 使用了 IN 或 NOT IN：
    当 IN 或 NOT IN 的取值范围较大时，可能会导致索引失效。优化策略是尽量避免使用大范围的 IN 或 NOT IN。

  11. 使用了 OR 操作：
    如果 OR 两边的条件中有一个字段没有索引，可能会导致索引失效。优化策略是确保 OR 两边的条件字段都有索引。

  12. 两列做比较：
    如果两个列数据都有索引，但在查询条件中对两列数据进行了对比操作，则会导致索引失效。例如：

    SELECT * FROM emp WHERE id > age;

    优化策略是避免在查询条件中进行两列的比较。

最左匹配原则

最左匹配原则（Most Left Prefix Principle）是复合索引（也称为多列索引或多字段索引）中一个重要的概念。这个原则决定了数据库管理系统（DBMS）如何在复合索引上进行搜索和匹配。简单来说，复合索引按照从左至右的顺序对多个列进行排序，而查询优化器在使用这样的索引时也会遵循相同的顺序。

最左匹配原则的含义

当你在一个包含多个列的复合索引上执行查询时，DBMS会尝试从索引的最左边的列开始匹配查询条件。如果第一个列的值匹配，则DBMS将继续向右移动到下一个列进行匹配，以此类推，直到所有的查询条件都被满足或者索引列用完为止。

例如，你有一个复合索引 (column1, column2, column3)，那么DBMS可以有效地处理以下类型的查询：

  • WHERE column1 = value1
  • WHERE column1 = value1 AND column2 = value2
  • WHERE column1 = value1 AND column2 = value2 AND column3 = value3

但是，如果查询条件不符合这个从左至右的顺序，比如只包含 column2 或者 column3 的条件，或者条件顺序为 column3 和 column2，那么复合索引可能不会被充分利用，甚至完全不被使用。

实例说明

假设我们有一个表 orders，它具有以下结构：

  • order_id (INT)
  • customer_id (INT)
  • order_date (DATE)

并且我们在 customer_id 和 order_date 上创建了一个复合索引：

CREATE INDEX idx_customer_date ON orders(customer_id, order_date);

现在，以下查询可以有效利用这个复合索引：

SELECT * FROM orders WHERE customer_id = 123 AND order_date = '2023-01-01';

但是，以下查询则可能不会利用复合索引：

SELECT * FROM orders WHERE order_date = '2023-01-01'; // 缺少最左前缀
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01'; // 右侧列使用了范围查询

在最后一个例子中，虽然查询包含了 customer_id（复合索引的最左列），但由于 order_date 列使用了范围查询（>），所以索引可能不会被完全利用，因为范围查询通常只能利用到范围条件之前的部分索引。
在这里插入图片描述

数据库索引失效的几种情况
12-14
数据库索引是数据库管理系统中用于加速数据检索的关键组件。它是一种特殊的文件，由数据库系统创建并维护，可以显著提高查询速度。索引通过对表中一列或多列的值进行排序，使得数据查找过程无需遍历整个表格。例如，...
oracle数据库索引失效
09-10
Oracle数据库索引失效是一个常见的性能问题，它可能导致查询效率降低，影响系统的整体性能。索引失效可能是由多种原因引起的，理解这些原因并采取适当的预防措施至关重要。 首先，当WHERE子句中的条件筛选出的数据...
MySQL索引创建原则以及不适合创建索引的情况
Black_Chenghang的博客
03-14 1128
①、冗余索引：比如联合索引中包含了字段a，那么字段a就不要再创建单列索引了②、重复索引：给主键索引又创建唯一索引和普通索引。
索引失效的情况及解决(超详细)
热门推荐
sy_white的博客
12-23 8万+
mysql索引优化
怎么避免索引失效
w4ngbo的博客
03-16 3205
一、如何判断数据库索引是否生效首先在接着探讨之前，我们先说一下，如何判断数据库的索引是否生效！相信大家应该猜到了，就是explain！explain显示了MySQL如何使用索引来处理select语句以及连接表。他可以帮助选择更好的索引和写出更优化的查询语句。例如我们有一张表user，为name列创建索引name_index，如下所示：  使用explain分析语句如下：可以看到，使用explain...
MySQL数据库索引及失效场景
靖节先生的博客
01-06 4097
MySQL数据库索引及失效场景1. 索引失效概述1.1 索引的概念1.2 索引的特点1.3 索引的分类1.4 索引的使用场景2. 索引失效场景2.1 常见索引失效的9种情况2.2 索引失效场景总结3. 索引失效验证3.1 全值匹配3.2 最佳左前缀3.3 索引计算3.4 索引范围：索引列上不能有范围查询3.5 索引覆盖：尽量使用覆盖索引3.6 不等： 使用不等于（!= 或者 <>）的时候3.7 null：字段的is not null 与is null3.8 like：like的前后模糊匹配3.9
数据库查询导致索引失效情况
精神小牛马
08-29 1824
查询age and classId and name时，(age,classId,name)索引比(age,classId)快。联合索引把频繁查询的列放左。索引（a,b,c），只能查(a,b,c),(a,b),(a)。如果主键不自增，需要查找目标位置再插入，并且如果目标位置所在数据页满了就必须得分页，造成性能损耗。计算例如where num+1=2，函数例如abs(num)取绝对值例如name=123，而不是name='123'。又例如使用了不同字符集。
数据库创建索引原则及索引失效
weixin_44223977的博客
10-16 387
数据库创建索引原则及索引失效
数据库索引失效的11种情况
qq_39964887的博客
06-26 1000
数据库索引失效的11种情况，大家在软件开发中应尽量避免
数据库索引失效的情况
weixin_42505381的博客
12-20 527
目的在于进行SQL优化过程中避免索引失效的情况
数据库索引及优化
世上无难事，只要肯攀登
03-28 1397
数据库索引以及优化
Mysql数据库索引创建、索引删除、索引失效场景详解
03-15
在MySQL数据库中，索引是提高查询效率的关键工具。它们为数据表的列提供了...了解各种索引类型、创建方法和可能导致失效的场景，可以帮助数据库管理员更好地管理和优化他们的数据库，从而提高查询效率和整体系统性能。
分库分表实战 - ShardingSphere
weixin_46619605的博客
04-02 1060
分库分表实战 - ShardingSphere
分布式锁方案-Redisson
不积跬步，无以至千里；不积小流，无以成江海。
04-02 265
分布式锁：Redisson还实现了Redis文档中提到像分布式锁Lock这样的更高阶应用场景。事实上Redisson并没有不止步于此，在分布式锁的基础上还提供了联锁（MultiLock），读写锁（ReadWriteLock），公平锁（Fair Lock），红锁（RedLock），信号量（Semaphore），可过期性信号量（PermitExpirableSemaphore）和闭锁（CountDownLatch）这些实际当中对多线程高并发应用至关重要的基本部件。
基础框架系列分享：一个通用的Excel报表生成管理框架
weixin_45357522的博客
04-01 482
一个通用的报表生成方案
192.168.145.129
最新发布
cqsizhuang的博客
04-03 256
学习 SQL 基础：SQL 是与数据库交互的语言，掌握基本的 SQL 语句，如SELECT、INSERT、UPDATE、DELETE等，以及数据定义语言（DDL），如CREATE TABLE、ALTER TABLE、DROP TABLE等。研究数据库内核：如果对数据库的底层原理感兴趣，可以深入研究 PostgreSQL 的内核代码，了解其存储引擎、查询优化器、事务处理等模块的实现原理，这有助于更深入地理解数据库的运行机制，提升性能优化和故障排查的能力。安装完成后，熟悉数据库的启动、停止等操作。
全面解析 Mybatis 与 Mybatis-Plus：深入原理、实践案例与高级特性对比
weixin_44876263的博客
04-03 648
在 Java 后端开发中，数据持久层始终是关键模块。如何既保证 SQL 调优的灵活性，又能提高开发效率，是众多开发者关注的焦点。本文将从基础介绍开始，全面解析Mybatis与的各个方面，包括工作原理、常见使用场景、详细实例、进阶特性、插件支持以及最佳实践，帮助你在项目中做出更明智的选择。让我们一起开始这段干货满满的技术之旅吧！💡Mybatis高度灵活开发者可以自定义 SQL，实现复杂查询及数据库操作。精细控制通过 XML 映射文件管理 SQL 与实体类之间的关系，充分掌控底层细节。低侵入性。
SpringBoot古典舞在线交流平台设计与实现
幽络源
04-03 702
本文详细介绍基于SpringBoot的古典舞在线交流平台设计与实现，涵盖用户与管理员功能模块。
数据库索引失效的常见问题及优化策略
数据库索引是数据库管理系统中一种关键的数据结构，它加速了数据检索速度，通过预先排序的方式，使查询性能得以提升。索引通常用于单列或多列，以便快速定位到特定数据。然而，在实际使用中，索引并非总是有效，可能...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值