MySQL45讲 第十一讲 怎么给字符串字段加索引?

文章目录

  • MySQL45讲 第十一讲 怎么给字符串字段加索引?
    • 一、引言
    • 二、前缀索引
      • (一)概念与创建方式
      • (二)数据结构与存储差异
      • (三)确定前缀长度的方法
    • 三、前缀索引对覆盖索引的影响
    • 四、其他索引创建方式
      • (一)倒序存储
      • (二)Hash 字段
    • 五、总结

MySQL45讲 第十一讲 怎么给字符串字段加索引?


一、引言

在数据库应用中,字符串字段的索引创建至关重要。第十一讲将以支持邮箱登录的系统用户表为例,深入探讨字符串字段索引的创建方式及其优缺点。

例子:现在维护一个支持邮箱登录的系统,用户表是这么定义

mysql> create table SUser(
ID bigint unsigned primary key,
email varchar(64),
...
)engine=innodb;

二、前缀索引

(一)概念与创建方式

  1. MySQL 支持前缀索引,可定义字符串的一部分作为索引。若创建索引语句不指定前缀长度,索引将包含整个字符串。例如,在用户表SUseremail字段上创建索引:

    • alter table SUser add index index1(email)创建的index1索引包含整个字符串

    • alter table SUser add index index2(email(6))创建的index2索引仅取每个记录email字段的前 6 个字节。

      在这里插入图片描述

(二)数据结构与存储差异

  1. index1index2为例,index2因只取前 6 个字节,占用空间更小。但使用前缀索引可能增加额外记录扫描次数。

    如查询语句select id,name,email from SUser where email='zhangssxyz@xxx.com'

    • 使用index1只需回主键索引取一次数据,系统认为扫描一行;

    • 使用index2则需回主键索引取 4 次数据,扫描 4 行。不过,**若将index2定义为email(7),则可只扫描一行。**说明定义合适长度的前缀索引可在节省空间的同时控制查询成本。

(三)确定前缀长度的方法

  1. 建立索引时关注区分度,可通过统计索引上不同值的数量判断前缀长度

    首先用select count(distinct email) as L from SUser;算出列上不同值的数量:再用select count(distinct left(email,4))as L4,count(distinct left(email,5))as L5,count(distinct left(email,6))as L6,count(distinct left(email,7))as L7,from SUser;查看不同长度前缀的情况。设定可接受的损失比例(如 5%),在返回结果中找出不小于L * 95%的值,若L6L7满足,可选择前缀长度为6。

三、前缀索引对覆盖索引的影响

  1. 对于语句select id,email from SUser where email='zhangssxyz@xxx.com';,若使用index1(整个字符串索引结构),可利用覆盖索引直接返回结果;若使用index2email(6)索引结构),需回主键索引判断email字段值。即使将index2定义为email(18),InnoDB 仍会回主键索引检查,因为系统不确定前缀索引是否截断完整信息。这表明使用前缀索引可能无法享受覆盖索引对查询性能的优化,选择时需考虑此因素。

四、其他索引创建方式

(一)倒序存储

  1. 当字符串前缀区分度不够时,如身份证号(前 6 位地址码在同一县可能相同),可使用倒序存储。存储时将身份证号倒过来,查询时使用mysql> select field_list from t where id_card = reverse('input_id_card_string');。由于身份证号最后 6 位无重复逻辑,可能提供足够区分度,但需用count(distinct)验证。倒序存储在主键索引上不消耗额外存储空间,每次读写需调用reverse函数,会增加扫描行数。

(二)Hash 字段

  1. 另一种方式是创建hash字段。在表上新增整数字段保存身份证校验码,并在该字段创建索引。插入记录时用crc32()函数计算校验码填入。查询语句为mysql> select field_list from t where id_card_crc=crc32('input_id_card_string') and id_card='input_id_card'。这种方式索引长度变为 4 字节,占用空间小。hash字段方式每次写读需调用crc32()函数,查询性能相对稳定,平均扫描行数接近 1,但存在校验码冲突概率,且不支持范围查询。

五、总结

字符串字段创建索引有多种方式:

  • 直接创建完整索引占用空间大;
  • 创建前缀索引节省空间但增加查询扫描次数且不能使用覆盖索引;
  • 倒序存储再创建前缀索引可解决字符串本身前缀区分度不够问题;
  • 创建hash字段索引查询性能稳定,但有额外存储和计算消耗,且不支持范围扫描。

实际应用中需根据业务字段特点选择合适方式。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/465927.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

字节青训-小S的倒排索引

问题描述 小S正在帮助她的朋友们建立一个搜索引擎。为了让用户能够更快地找到他们感兴趣的帖子,小S决定使用倒排索引。倒排索引的工作原理是:每个单词都会关联一个帖子ID的列表,这些帖子包含该单词,且ID按从小到大的顺序排列。 例…

讲讲分布式与集群的区别?

大家好,我是锋哥。今天分享关于【讲讲分布式与集群的区别?】面试题。希望对大家有帮助; 讲讲分布式与集群的区别? 1000道 互联网大厂Java工程师 精选面试题-Java资源分享网 在现代计算和信息技术领域,分布式系统和集…

大数据新视界 -- 大数据大厂之 Impala 性能优化:解锁大数据分析的速度密码(上)(1/30)

💖💖💖亲爱的朋友们,热烈欢迎你们来到 青云交的博客!能与你们在此邂逅,我满心欢喜,深感无比荣幸。在这个瞬息万变的时代,我们每个人都在苦苦追寻一处能让心灵安然栖息的港湾。而 我的…

大数据新视界 -- 大数据大厂之 Impala 性能优化:数据存储分区的艺术与实践(下)(2/30)

💖💖💖亲爱的朋友们,热烈欢迎你们来到 青云交的博客!能与你们在此邂逅,我满心欢喜,深感无比荣幸。在这个瞬息万变的时代,我们每个人都在苦苦追寻一处能让心灵安然栖息的港湾。而 我的…

【实用教程】Blazor 文件管理器中引入分页功能

分页是一项重要功能,可帮助我们有效地加载大量数据。我们的 Syncfusion Blazor 文件管理器允许在分段页面中显示文件和文件夹,从而更轻松地浏览大型目录。在文件管理器组件中处理大量数据时,此功能非常方便。此功能可用于有效地加载大量数据。…

C++上机实验|多态性编程练习

1.实验目的 (1)理解多态性的概念。 (2)掌握如何用虚函数实现动态联编 (3)掌握如何利用虚基类。 2.实验内容 设计一个飞机类 plane,由它派生出歼击机类fighter和轰炸机类 bomber,歼击机类fighter 和轰炸机类bomber 又共同派生出歼轰机(多用途战斗机)。利用虚函数和虚基类描述…

CSS弹性布局:灵活布局的终极指南

在网页设计中,CSS 弹性布局(Flexbox)是一个不可或缺的工具。它能帮助你轻松地排列和对齐元素,尤其是在响应式设计中表现出色。今天,我们就来深入探讨一下 Flexbox 的各个属性,让你彻底掌握这个强大的布局工…

Java:二维数组

目录 1. 二维数组的基础格式 1.1 二维数组变量的创建 —— 3种形式 1.2 二维数组的初始化 \1 动态初始化 \2 静态初始化 2. 二维数组的大小 和 内存分配 3. 二维数组的不规则初始化 4. 遍历二维数组 4.1 for循环 ​编辑 4.2 for-each循环 5. 二维数组 与 方法 5.1…

SQL,力扣题目1767,寻找没有被执行的任务对【递归】

一、力扣链接 LeetCode_1767 二、题目描述 表:Tasks ------------------------- | Column Name | Type | ------------------------- | task_id | int | | subtasks_count | int | ------------------------- task_id 具有唯一值的列。 ta…

Spring Security-02-Spring Security认证方式-HTTP基本认证、Form表单认证、HTTP摘要认证、前后端分离安全处理方案

Lison <dreamlison163.com>, v1.0.0, 2024.06.01 Spring Security-02-Spring Security认证方式-HTTP基本认证、Form表单认证、HTTP摘要认证、前后端分离安全处理方案 文章目录 Spring Security-02-Spring Security认证方式-HTTP基本认证、Form表单认证、HTTP摘要认证、…

3.1、软件需求分析

软件需求分析 1、 需求分析定义及获取2、 需求分析过程2.1 需求提炼2.2 需求描述2.3 需求验证 3、 需求分析任务3.1 软件需求规格文档编制沟通活动通用任务集软件需求规格说明的原则软件需求规格说明的结构 1、 需求分析定义及获取 需求分析&#xff1a;确定系统必须具有的功能…

qt QStandardItemModel详解

1、概述 QStandardItemModel是Qt框架中提供的一个基于项的模型类&#xff0c;用于存储和管理数据&#xff0c;这些数据可以以表格的形式展示在视图控件&#xff08;如QTableView、QTreeView等&#xff09;中。QStandardItemModel支持丰富的数据操作&#xff0c;包括添加、删除…

Ubuntu18.04更换PREEMPT RT内核

文章目录 1 安装环境2 下载实时内核3 安装必要库和软件4 配置4.1 解压kernel压缩包4.2 进入kernel文件夹4.2.1 操作步骤4.2.2 修改配置文件 5 构建和安装6 启动显示内核选择界面7 启动界面选择实时内核版本进入8 uname -a查看操作系统内核信息 1 安装环境 Ubuntu 18.04原生内核…

立冬到了,选择Codigger暖心陪伴

立冬了&#xff0c;寒风渐起&#xff0c;但Codigger开发者们依然热情如火&#xff0c;编程的热情不会因为冬天而减退&#xff0c;相反&#xff0c;更加激情澎湃。就像立冬的清晨&#xff0c;虽然寒冷&#xff0c;却有着一种清新的气息&#xff0c;让我们一起迎接新的挑战&#…

全文检索ElasticSearch到底是什么?

学习ElasticSearch之前&#xff0c;我们先来了解一下搜索 1 搜索是什么 ① 概念&#xff1a;用户输入想要的关键词&#xff0c;返回含有该关键词的所有信息。 ② 场景&#xff1a; ​ 1互联网搜索&#xff1a;谷歌、百度、各种新闻首页&#xff1b; ​ 2 站内搜索&#xff…

Ansys Zemax | 手机镜头设计 - 第 4 部分:用LS-DYNA进行冲击性能分析

该系列文章将讨论智能手机镜头模组设计的挑战&#xff0c;从概念和设计到制造和结构变形分析。本文是四部分系列中的第四部分&#xff0c;它涵盖了相机镜头的显式动态模拟&#xff0c;以及对光学性能的影响。使用Ansys Mechanical和LS-DYNA对相机在地板上的一系列冲击和弹跳过程…

Follow软件的使用入门教程

开篇 看到很多兄弟还不知道怎么用这个当下爆火的浏览器&#xff01;在这里简单给需要入门的小伙伴一些建议&#xff1a; 介绍 简单解释一下&#xff0c;RSS 意思是简易信息聚合&#xff0c;用户可以通过 RSS 阅读器或聚合工具自主订阅并浏览各个平台的内容源&#xff0c;不用…

Redis数据库测试和缓存穿透、雪崩、击穿

Redis数据库测试实验 实验要求 1.新建一张user表&#xff0c;在表内插入10000条数据。 2.①通过jdbc查询这10000条数据&#xff0c;记录查询时间。 ②通过redis查询这10000条数据&#xff0c;记录查询时间。 3.①再次查询这一万条数据&#xff0c;要求根据年龄进行排序&#…

无root权限在Linux虚拟环境安装指定版本python

创建虚拟环境见 Linux创建虚拟环境&#xff0c;并在虚拟环境中运行项目_如何进入虚拟zhi环境再打开项目-CSDN博客 若使用python -m venv创建虚拟环境则无法指定python版本&#xff0c;需要单独安装 1.在官网Download Python | Python.org 下载对应版本的python包 例如我这里…

OCR、语音识别与信息抽取:免费开源的AI平台在医疗领域的创新应用

一、系统概述 在医疗行业中&#xff0c;大量数据来自手写病历、医学影像报告、患者对话记录等非结构化数据源。这些数据常常存在信息碎片化和管理困难的问题&#xff0c;给医务人员的工作带来了不便。思通数科AI多模态能力平台正是为了解决这一行业痛点而生&#xff0c;产品集…