数据库索引

2023-11-17

3.1 概括

索引(Index)是数据库中的一种存储结构,用于快速查找数据。索引通常是在数据库表上创建的,可以用于加速查询、排序和数据的唯一性验证。索引可以理解为图书中的目录,通过目录我们可以很快找到页码对应的内容。

当表中有大量数据需要查询时,有两种方式进行检索,一种时全表扫描,另一种就是建立索引。不难想到,全表扫描可以获取到一张表中的所有数据,但缺点就是往往我们都不需要同时使用一张表中的所有数据,也就是说即使我们只需要1到2行数据也要全部扫描,这极大程度占用资源。

常见索引包括如下:

  1. B-Tree索引:最常见的索引类型,适用于等值查询和范围查询。B-Tree索引会将索引列的值排序,并将数据分散到不同的叶子节点中。

  2. 哈希索引:适用于等值查询,不适用于范围查询。哈希索引会将索引列的值通过哈希算法计算出一个哈希值,并将哈希值映射到对应的数据块中。

  3. 全文索引:适用于文本数据的模糊查询。全文索引会将文本数据拆分成单词,并将单词和对应的文档编号存储在索引中。这里可以对比ES中的倒排索引

3.2 MySQL创建索引

索引也会占用存储空间,并且对于写操作(如插入、更新、删除)会有一定的性能影响。因此,在创建索引时需要根据实际情况进行权衡,避免过度索引导致性能下降。同时创建索引需要耗时,具体看实际情况。

3.2.1 在MySQL数据库中创建B-Tree索引
-- 在表my_table的列my_column上创建B-Tree索引,默认就是B-Tree
 CREATE INDEX my_index ON my_table(my_column);
3.2.2 在MySQL数据库中创建哈希索引

需要满足以下条件:

  1. 被索引的列必须是整数类型或字符串类型。

  2. 被索引的列必须是固定长度的,例如CHAR或BINARY类型。

因为哈希索引是通过哈希算法计算出一个哈希值,并将哈希值映射到对应的数据块中,所以需要保证被索引的列是固定长度的,以便在计算哈希值时能够准确地进行映射。示例代码如下:

 -- 在表my_table的列my_column上创建哈希索引
 CREATE INDEX my_index ON my_table(my_column) USING HASH;

使用USIGN 关键字指定索引类型为HASH类型

3.2.3 创建全文索引

在MySQL数据库中创建全文索引需要满足以下条件:

在MySQL数据库中创建全文索引的示例代码如下:

 -- 将表my_table的列my_column创建成全文索引
 ALTER TABLE my_table ADD FULLTEXT(my_column);

使用全文索引进行查询时,可以使用MATCH AGAINST语句进行查询,例如:

 -- 在my_table表中通过my_column列的全文索引进行查询
 SELECT * FROM my_table WHERE MATCH(my_column) AGAINST('search_string');

在使用MATCH AGAINST语句进行查询时,可以指定查询的字符串(search_string),MySQL会将查询字符串拆分成多个单词进行匹配,并返回匹配的结果。(分词与模糊查询)

  1. 被索引的列必须是字符类型,例如CHAR、VARCHAR或TEXT类型。

  2. 数据库的存储引擎必须是支持全文索引的引擎,例如MyISAM或InnoDB。

3.3 数据库数据结构

在关系型数据库中,索引通常是通过B树、B+树等数据结构实现的,也可以使用哈希表等数据结构。

索引可以提高查询速度的原因是它可以将查询的数据按照某种方式进行排序,并将排序后的结果存储在索引中。当进行查询时,数据库可以通过索引快速定位到符合条件的数据,而不需要遍历整个数据表。例如,如果在一个包含1000万条记录的表中,需要查询某个字段的值是否存在,使用索引可以将查询时间从数秒缩短到毫秒级别。(类比目录与文本内容的关系,索引就是目录)

在创建索引时,需要考虑索引所涉及的列、索引的类型、索引的大小等因素。通常情况下,索引适用于经常被查询但很少修改的列,如主键、外键、查询条件的列等。同时,索引的类型也需要根据具体的查询需求来选择,例如,如果需要在一个范围内进行查询,可以使用B+树索引;如果需要进行精确匹配查询,可以使用哈希索引。此外,索引的大小也需要考虑,过大的索引会占用过多的磁盘空间,而过小的索引则会影响查询性能。

需要注意的是,虽然索引可以提高查询性能,但是过多的索引也会导致数据表的修改性能下降。因此,在创建索引时,需要根据具体的业务场景和数据访问模式来进行选择,避免过多或不必要的索引。

3.4 常见的导致索引失效的关键字

在MySQL数据库中,常见的导致索引失效的关键字如下:

  • NOT:NOT关键字会对查询结果进行反转,这意味着MySQL需要扫描整个表来找到不符合条件的行,而无法使用索引来加速查询。例如:

     SELECT * FROM table WHERE NOT column = 'value'
  • LIKE:使用LIKE关键字进行模糊查询时通常需要对查询字符串进行模糊匹配,这需要对表中的每一行进行比较,而无法使用索引来加速查询。例如:

     SELECT * FROM table WHERE column LIKE '%value%'
  • OR:OR查询需要对多个条件进行比较,这需要在索引树上进行多次查找,而无法使用单个索引来加速查询。例如:

     SELECT * FROM table WHERE column1 = 'value1' OR column2 = 'value2'
  • IS NULL/IS NOT NULL:这些关键字需要对表中的每一行进行比较,所以也无法使用索引来加速查询。例如:

     SELECT * FROM table WHERE column IS NULL
  • 聚合函数:如果查询中包含聚合函数(如SUM、COUNT、MAX等),则可能会导致索引失效。因为聚合函数需要对整个表进行扫描,而无法使用索引来加速查询。例如:

     SELECT COUNT(*) FROM table

虽然这些关键字可能会导致索引失效,但并不是所有情况下都会出现这种情况。具体是否会导致索引失效,还需要根据具体的表结构、索引类型、查询条件等因素来进行具体判断。

3.5 in关键字

使用IN关键字查询时,如果IN子句中的值比较少,MySQL优化器会选择使用索引来加速查询。但是,如果IN子句中的值太多,MySQL优化器可能会选择不使用索引而进行全表扫描,这样会导致索引失效。

具体来说,当IN子句中的值很多时,MySQL优化器可能会认为使用索引比进行全表扫描更慢,因为使用索引需要进行多次索引查找和合并操作。此时,MySQL优化器可能会选择进行全表扫描,因为它可以避免多次索引查找和合并操作,从而提高查询效率。

为了避免IN关键字导致MySQL索引失效,可以考虑以下几种方法:

  1. 尽量减少IN子句中的值的数量,仅包含必要的值。

  2. 将IN子句中的值按照索引顺序进行排序,这有助于优化器决定是否使用索引。

  3. 使用JOIN语句代替IN语句,这样可以避免使用IN关键字,从而避免索引失效的问题。

  4. 使用EXISTS子查询代替IN子查询,这可以避免使用IN关键字,从而避免索引失效的问题。

以上3,4条都要联系多表查询,个人感觉不是很理解,感觉更麻烦了。

本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)

数据库索引 的相关文章

  • 跨境电商三大趋势已经涌现

    在过去的几年里 跨境电商在推动外贸增长中发挥了至关重要的作用 成为了引领行业发展的强大引擎 然而 随着2024年的到来 跨境电商行业又站在了崭新的起点 准备攀登新的发展高峰 数据显示 得益于经济的逐步复苏 未来三年跨境电商行业预计将以16
  • 拓数派加入 OpenCloudOS 操作系统开源社区,作为成员单位参与社区共建

    近日 拓数派签署 CLA Contributor License Agreement 贡献者许可协议 正式加入 OpenCloudOS 操作系统开源社区 拓数派 英文名称 OpenPie 是国内基础数据计算领域的高科技创新企业 作为国内云上
  • 【计算机开题报告】 网上茶叶销售平台设计与开发

    一 选题依据 简述国内外研究现状 生产需求状况 说明选题目的 意义 列出主要参考文献 1 研究背景 随着社会经济的迅速发展和科学技术的全面进步 以计算机与网络技术为基础的信息系统正处于蓬勃发展的时期 随着经济文化水平的提高 近年来 随着科学
  • 【计算机开题报告】基于JSP的服装店销售管理系统

    1 选课目的意义 21世纪是一个信息化时代 随着中国经济的发展和人民生活水平的提高 服装商场的普及程度日益增大 竞争也在逐渐白炽化 为了进一步提高服装商场的经营效率 在服装店销售管理中引入计算机管理系统成为了必然的选择 由于中国环境的特殊性
  • 如何处理不稳定的自动化测试?

    abluecolor 在解决这个问题之前 请停止编写更多测试 因为这将花费你较高的测试维护成本 你需要尽快行动起来对不稳定的原因进行深入研究 找到不稳定的根因 并且尝试在流程 环境和代码方面做一些优化工作解决它 MasterKindew 如
  • Kali Linux 安全渗透核心总结,444页核心知识点

    就像IT人离不开Linux系统一样 网安人也离不开Kali Linux 作为攻击性防御和渗透测试的代名词 越来越多的人开始学习Kali 如果你也对kali感兴趣 又想深入了解这方面内容 不妨收藏一下这份Kali Linux安全渗透教程 共4
  • 进程间通信

    进程间通信 进程间通信介绍 进程间通信目的 数据传输 一个进程需要将它的数据发送给另一个进程 资源共享 多个进程之间共享同样的资源 通知事件 一个进程需要向另一个或一组进程发送消息 通知它 它们 发生了某种事件 如进程终止 时要通知父进程
  • 拼多多详情API开启运营比价新纪元

    随着互联网的快速发展 电商行业正在迅速崛起 拼多多作为一家新兴的电商平台 凭借其独特的营销策略和创新的商业模式 成为了电商行业的一匹黑马 在拼多多的成功背后 其详情API接口营销起到了至关重要的作用 本文将详细介绍拼多多详情API接口营销的
  • 【计算机毕业设计】实验室预约管理

    身处网络时代 随着网络系统体系发展的不断成熟和完善 人们的生活也随之发生了很大的变化 人们在追求较高物质生活的同时 也在想着如何使自身的精神内涵得到提升 而读书就是人们获得精神享受非常重要的途径 为了满足人们随时随地只要有网络就可以看书的要
  • 图解python | 字符串及操作

    1 Python元组 Python的元组与列表类似 不同之处在于元组的元素不能修改 元组使用小括号 列表使用方括号 元组创建很简单 只需要在括号中添加元素 并使用逗号隔开即可 tup1 ByteDance ShowMeAI 1997 202
  • APP端网络测试与弱网模拟

    当前APP网络环境比较复杂 网络制式有2G 3G 4G网络 还有越来越多的公共Wi Fi 不同的网络环境和网络制式的差异 都会对用户使用app造成一定影响 另外 当前app使用场景多变 如进地铁 上公交 进电梯等 使得弱网测试显得尤为重要
  • python超详细基础文件操作【建议收藏】

    文章目录 前言 发现宝藏 1 文件操作 1 1 文件打开与关闭 1 1 1 打开文件 1 1 2 关闭文件 1 2 访问模式及说明 2 文件读写 2 1 写数据 write 2 2 读数据 read 2 3 读数据 readlines 2
  • 【计算机毕业设计】电影播放平台

    电影播放平台采用B S架构 数据库是MySQL 网站的搭建与开发采用了先进的java进行编写 使用了springboot框架 该系统从两个对象 由管理员和用户来对系统进行设计构建 主要功能包括 个人信息修改 对用户 电影分类 电影信息等功能
  • 【ES6】解构语句中的冒号(:)

    在解构赋值语法中 冒号 的作用是为提取的字段指定一个新的变量名 让我们以示例 const billCode code version route query 来说明 billCode code version 表示从 route query
  • 【计算机毕业设计】OA公文发文管理系统_xtv98

    近年来 人们的生活方式以网络为主题不断进化 OA公文发文管理就是其中的一部分 现在 无论是大型的还是小型的网站 都随处可见 不知不觉中已经成为我们生活中不可或缺的存在 随着社会的发展 除了对系统的需求外 我们还要促进经济发展 提高工作效率
  • 面试官问,如何在十亿级别用户中检查用户名是否存在?

    面试官问 如何在十亿级别用户中检查用户名是否存在 前言 不知道大家有没有留意过 在使用一些app注册的时候 提示你用户名已经被占用了 需要更换一个 这是如何实现的呢 你可能想这不是很简单吗 去数据库里查一下有没有不就行了吗 那么假如用户数量
  • 30天精通Nodejs--第二十天:express-操作mysql

    目录 前言 安装依赖并配置MySQL连接 安装mysql2库 配置连接信息 在Express应用中使用MySQL 结合Express路由实现CRUD操作 整合到主应用 结语 前言 在Node js中使用Expre
  • 每日变更的最佳实践

    在优维公司内部 我们采用发布单的方式进行每天的应用变更管理 这里给各位介绍优维的最佳实践 变更是需要多角色合作的 而且他是整体研发流程的一部分 在优维内部 我们坚持每日变更 打通开发环节到最终发布上线的全过程 在保证质量的前提下 尽可能提升
  • 光波导结构

    摘要 增强现实和混合现实 AR MR 领域的新应用引起了人们对带有光栅区域的光波导系统的越来越多的关注 这些光波导系统用于输入和输出耦合以及扩瞳目的 VirtualLab Fusion为这类系统的仿真和设计提供了几个强大的工具 其中一个是具
  • Python 使用 NoSQL 数据库的优选方案

    NoSQL 数据库因其高性能 可扩展性和灵活性而风靡一时 然而 对于 Python 程序员而言 选择合适的 NoSQL 数据库可能会令人困惑 因为有多种选择可供选择 那么 哪种 NoSQL 数据库最适合 Python 呢 2 解决方案 根据

随机推荐

  • [0x7FFBFB69D3E0] ANOMALY: meaningless REX prefix used问题的修复

    0x7FFBFB69D3E0 ANOMALY meaningless REX prefix used 困扰了我几个礼拜 导致idea git 用不了 差点重装系统了 今天还好 解决了 用360 各种修复 各种操作 cmd 没有那串东西咯 各
  • 宏观经济浅学20210724

    https www bilibili com video BV1Ng4y1B7ig p 14 8大类 251个基本分类 700个品种 120万个商品
  • 写一篇有关机器学习的论文

    写一篇有关机器学习的论文 需要从以下几个方面进行阐述 机器学习的定义 首先要明确机器学习的定义 并简要介绍其历史发展 机器学习的分类 接着介绍机器学习的分类方法 例如监督学习 非监督学习 半监督学习 强化学习等 机器学习的应用 最后详细阐述
  • 智能城市dqn算法交通信号灯调度_新基建激发AI新动力,助推人工智能产业化落地进程...

    8月7日 9日 由中国计算机学会主办 香港中文大学 深圳 雷锋网联合承办的全球人工智能和机器人峰会在深圳开幕 峰会期间揭晓了 2020AI 最佳成长榜 千方科技旗下博观智能凭借在智慧城市多个细分领域的商用落地实力 斩获 AI 智慧城市 领域
  • python判断语句

    判断语句 if if 语句的判断语法 if 要判断的条件 条件成立时 要做的事情 else语法格式如下 if 要判断的条件 条件成立时 要做的事情 else 条件不成立 要做的事情 逻辑运算 只有多个条件都满足 才能执行后续代码 这个时候需
  • 两张动图-彻底明白TCP的三次握手与四次挥手

    背景描述 通过上一篇中网络模型中的IP层的介绍 我们知道网络层 可以实现两个主机之间的通信 但是这并不具体 因为 真正进行通信的实体是在主机中的进程 是一个主机中的一个进程与另外一个主机中的一个进程在交换数据 IP协议虽然能把数据报文送到目
  • C++课程设计之高校学生简单信息管理系统

    给大家分享一个用C 语言编写的高校学生信息管理系统 程序比较简单 也比较好理解 适合刚入门C 的程序小白 大学生做C 相关的课程设计也可以给你提供一些思路 或者在此基础上修改一下 添加一些自己的类和成员函数 让系统的功能更加的强大 系统功能
  • iOS APP 启动页和icon图标尺寸

    前言 记录一下竖屏iPhone启动页和icon图标尺寸 好记性不如烂笔头 1 启动页尺寸 320x480 640x960 640x1136 750x1334 1125x2436 1242x2208 1242x2688 828x1792 2
  • chatgpt小程序安装指引

    chatgpt小程序安装指引 JAVA 创建数据库 名字随意 数据库编码为utf8mb4 导入sql文件 在renren api db目录下面 第一次导入数据的先执行chatgpt sql文件 在根据日期先后顺序执行 已导入过的同志 根据更
  • html chm 打不开,Win7系统中出现CHM打不开的具体解决方法

    一部分用户在使用Win7系统的时候 出现CHM打不开的情况 该怎么处理呢 下文就为你们带来了Win7系统中出现CHM打不开的具体解决方法 方法一 1 可以对注册表进行操作 按快捷键 Win R 启动 运行 程序 2 在 运行 程序的输入框中
  • 舒尔补-边际概率-条件概率

    margin求边际概率的时候喜欢通过舒尔补的形式去操作信息矩阵 如p b c 求积分p a b c da 从上图可知 边缘概率直接看协方差矩阵比较方便 边际概率的方差就是取对应联合分布中相应的协方差块 信息矩阵是由舒尔补的形式计算 此形式也
  • GBDT调参指南

    GBDT分类器和回归器的大部分参数都是相同的 除了损失函数的选项有些不同 因此下面我们统一说明各个参数的意义以及在什么情境下做什么调整方法 一 GBDT的boosting框架参数 1 n estimators 代表弱学习器的最大个数 即最多
  • Python+OpenCV人脸识别签到考勤系统

    前言 本项目为IOT实验室人员签到考勤设计 系统实现功能 1 人员人脸识别并完成签到 签退 2 考勤时间计算 3 保存考勤数据为CSV格式 Excel表格 PS 本系统2D人脸识别 节约了繁琐的人脸识别训练部分 简洁快捷 该项目为测试版 正
  • mac电脑的C语言安装包,Mac上运行C/C++程序

    由于工作需要 要在Mac环境里面运行C程序 遇到的问题及解决方法如下 1 确认环境里是否安装了command line developer tools 打开Terminal终端 输入g 提示 xcrun error invalid acti
  • (新)Chrome浏览器自定义背景插件

    一 效果预览 二 项目回顾 1 原理 主要是利用js脚本在页面打开前插入一个背景图片容器 在通过相应的事件控制来实现该功能 2 功能 将网络图片设置为浏览器背景 3 使用 下载插件 gt 修改js文件 加入图片链接 添加浏览器扩展 gt 刷
  • C++学习记录6--srand(time(NULL)产生随机数

    time 函数 返回从1970 1 1 00 00 00到调用time 函数时所经过的时间 以秒为单位 所以是个整数 time NULL 或time 0 表示在内存中不存储返回的数值 头文件 include
  • 如何保证测试用例覆盖全面

    测试用例覆盖度一般是从以下几方面衡量的 1 测试需求的覆盖 保证所有需求都已经设计用例 2 测试特性的覆盖 保证所有不同类型已覆盖 如 功能测试 性能测试等 3 平台与层次的覆盖 保证所有平台有用例覆盖 不同层次都有设计用例 如业务层 接口
  • JavaScript随机生成颜色功能

    思路 实现一个函数 随机生成颜色 格式为 000000 颜色由a f A F 0 9 3种字母任意组成 且 后面是3位或者6位 只要随机生成一个数字是奇数或者偶数来随机出是3位或者6位 然后在随机其下标循环上面步骤确认的次数 functio
  • 设计模式之简单工厂模式(Simply Factory)摘录

    从设计模式的类型上来说 简单工厂模式是属于创建型模式 又叫静态工厂方法 Static Factory Method 模式 但不属于23种GOF设计模式之一 简单工厂模式是由一个工厂对象决定创建出哪一种产品类的实例 简单工厂模式是工厂模式家族
  • 数据库索引

    3 1 概括 索引 Index 是数据库中的一种存储结构 用于快速查找数据 索引通常是在数据库表上创建的 可以用于加速查询 排序和数据的唯一性验证 索引可以理解为图书中的目录 通过目录我们可以很快找到页码对应的内容 当表中有大量数据需要查询