当前位置: 首页 > news >正文

【MySQL 进阶之路】索引的使用

5.索引的使用规则

数据库管理系统(DBMS)中,索引是提高查询效率的关键机制之一。MySQL索引优化是指通过设计、调整和选择合适的索引策略,以提高数据库的查询性能和降低资源消耗。以下是一些关键的索引使用规则:

1. 最左前缀法则

  • 定义:联合索引的使用顺序必须从索引的最左列开始。查询时,如果跳过联合索引的最左边某一列,后续列的索引将失效。
  • 例子
    CREATE INDEX idx_example ON table(a, b, c); 
    
    如果查询条件是 (a=1 AND b=2),会使用索引 idx_example。但如果查询条件是 (b=2 AND a=1),索引可能失效。

2. 索引列运算

  • 定义:尽量避免在索引列上进行运算操作(例如 +, -, *, =, LIKE 等),这会导致索引失效,影响查询效率。

  • 例子

    • 错误用法:WHERE YEAR(date_column) = 2024。此操作会使得 date_column 列的索引失效。
    • 正确用法:WHERE date_column >= '2024-01-01' AND date_column < '2025-01-01'

3. OR 和 AND 连接条件

  • 定义:在条件中使用 OR 时,如果 OR 前的条件使用了索引,而后面的条件没有使用索引,可能导致查询效率低下,因为 MySQL 会做全表扫描。
  • 例子
     -- 使用索引列        where column1 = 1 OR column2 = 2;
    

如果 column1 上有索引,而 column2 上没有,MySQL 会先用索引扫描 column1 = 1,然后再全表扫描来处理 column2 = 2,这会导致索引的部分失效。

4. 数据分布影响

  • 定义:如果 MySQL 评估发现使用索引的效率低于全表扫描时(例如索引选择性差,表的数据分布不均匀),则会选择不使用索引。
  • 例子:在某个字段值高度重复的情况下(如性别字段 malefemale),使用索引的效果不好,MySQL 会倾向于选择全表扫描。

5. SQL 提示

  • 定义:在复杂的查询中,我们可以通过 SQL 提示(Hint)来强制使用指定的索引,或者忽略某个索引,从而影响查询优化的执行。

  • 常见 SQL 提示

    • USE INDEX (index_name):强制查询使用指定的索引。
    • IGNORE INDEX (index_name):忽略指定的索引。
    • FORCE INDEX (index_name):强制查询使用指定的索引,即使 MySQL 认为不需要使用该索引。
  • 例子

SELECT * FROM table USE INDEX (idx_example) WHERE column1 = 10;

6. 覆盖索引

  • 定义:覆盖索引是指查询所需的所有字段都可以从索引中直接获取,而无需回表查询。这样可以显著提高查询效率。
  • 使用场景:覆盖索引通常用于 SELECT 查询,只涉及索引字段的查询,且避免使用 SELECT *
  • 例子
    假设有一个联合索引 idx_example (a, b, c),且查询只需要返回 ab 列:
    SELECT a, b FROM table WHERE a = 10 AND b = 20;
    此时,如果有 idx_example 索引,则可以直接通过索引返回结果,而不需要访问数据表。

7. 索引失效情况

  • 常见情况

    1. 运算操作:如 WHERE column1 + column2 = 10column1 * 2 = 100 等,运算会导致索引失效。
    2. 字符串未加单引号:例如 WHERE name = test,应改为 WHERE name = 'test'
    3. 头部模糊查询:如 WHERE column LIKE '%value%',这种查询无法利用前缀索引优化,效率低。
    4. OR 和 AND 连接条件:如果条件不完全使用索引,可能会导致部分索引失效。
  • 例子

     -- 错误:运算操作会导致索引失效SELECT * FROM table WHERE salary + bonus = 5000;-- 错误:未加单引号SELECT * FROM table WHERE name = test;  -- 错误,test 应加引号-- 错误:头部模糊查询SELECT * FROM table WHERE name LIKE '%abc%';  -- 头部模糊查询无法利用索引-- 错误:OR 连接条件中,后半部分没有索引SELECT * FROM table WHERE column1 = 1 OR column2 = 2;  -- 如果 column2 没有索引,可能导致效率低
    
总结:
  1. 最左前缀法则是联合索引的核心,必须遵循。
  2. 尽量避免在索引列上进行运算。
  3. ORAND 连接条件中的索引使用要谨慎。
  4. 覆盖索引是查询优化的关键手段。
  5. 使用 SQL 提示时,合理选择索引来优化查询。
  6. 注意索引失效的常见情况,避免不必要的性能损耗。

遵循索引的使用规则,尤其是最左前缀法则、避免索引列运算、合理使用 ORAND 连接条件、利用覆盖索引及 SQL 提示,可以有效提升查询性能。同时,避免常见的索引失效情况,才能确保数据库查询的高效执行。

http://www.lryc.cn/news/497019.html

相关文章:

  • 网络编程相关 API 学习
  • python使用python-docx处理word
  • 【笔记2-1】ESP32:基于vscode的espidf插件的开发环境搭建
  • 集成网关 -- 新节点的开发说明
  • 【Gitlab】CICD使用minio作为分布式缓存
  • ️️耗时一周,肝了一个超丝滑的卡盒小程序
  • 网络安全审计
  • 论文:IoU Loss for 2D/3D Object Detection
  • 2411mfc,修改按钮颜色
  • 互联网 Java 面试八股文汇总(2025 最新整理)
  • 如何在 Ubuntu 中更新 Linux 内核
  • FilterListenerAjax
  • 27. 移除元素 C++
  • 大模型专栏--Spring Ai Alibaba介绍和功能演示
  • Redis设计与实现第17章 -- 集群 总结2(执行命令 重新分片)
  • 微服务搭建----springboot接入Nacos2.x
  • 3.建立本地仓库及常用命令
  • linux arm下获取屏幕事件(rk3588)
  • 【机器学习】人工智能与气候变化:利用深度学习与机器学习算法预测和缓解环境影响
  • 物联网射频识别和RFID开发(二):RFID原理及硬件组成
  • LeetCode763. 划分字母区间(2024冬季每日一题 23)
  • python调用GPT-4o实时音频 Azure OpenAI GPT-4o Audio and /realtime
  • Hadoop生态圈框架部署 伪集群版(四)- Zookeeper单机部署
  • LuaJava
  • Maven下载安装、环境配置(超详细)(包括Java环境配置(Windows)、在IDEA中配置Maven)
  • Python中的实例方法、静态方法和类方法三者区别?
  • 【学习Go编程】
  • Linux系统:网络
  • shodan2-批量查找CVE-2019-0708漏洞
  • 面向对象(二)——类和对象(上)