数据库索引

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(使用前将#替换为@)

数据库索引 的相关文章

随机推荐

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