【MySQL】深层理解索引及特性(重点)--下(12)

索引(重点)

  • 1. 索引的作用
  • 2. 索引操作
    • 2.1 主键索引
      • 2.1.1 主键索引的特点
      • 2.1.2 创建主键索引
    • 2.2 唯一键索引
      • 2.2.1 唯一键索引的特点
      • 2.2.2 唯一索引的创建
    • 2.3 普通索引
      • 2.3.1 普通索引的特点
      • 2.3.2 普通索引的创建
    • 2.4 全文索引
      • 2.4.1 全文索引的作用
      • 2.4.2 全文索引的创建
  • 3. 查询索引
  • 4. 删除索引
  • 5. 索引创建原则

1. 索引的作用

索引提高数据库的性能,索引是物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行正确的create index,查询速度就可能提高成百上千倍。但是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些操作,增加了大量的IO。所以它的价值,在于提高一个海量数据的检索速度。

常见索引类型分为:

  1. 主键索引(Primary Key)
    主键索引是一种特殊的唯一索引,不允许有重复值,并且每个表只能有一个主键。主键通常用于唯一标识表中的每一行数据。在创建表的时候,如果指定了某列为主键,那么该列会自动创建一个主键索引。
  2. 唯一索引(Unique)
    唯一索引确保了索引列中的所有值都是唯一的,但与主键不同的是,一个表可以有多个唯一索引。唯一索引允许有一个或多个NULL值存在,这取决于数据库系统的设计。
  3. 普通索引(Index)
    普通索引是最基本的索引类型,它没有任何限制,可以包含重复的值。通过在查询条件中频繁使用的列上创建普通索引,可以显著提高查询效率。
  4. 全文索引(Fulltext
    全文索引主要用于全文本搜索,它可以对文本内容进行复杂的搜索操作,比如查找包含特定单词或短语的记录。全文索引特别适合于处理大量的文本数据,如新闻文章、博客帖子等。值得注意的是,不同的数据库管理系统支持的全文索引功能可能有所不同,例如MySQL中的InnoDB和`MyISAM存储引擎都支持全文索引,但实现方式和性能特点可能有所区别。

示例:
当我们在数据量少的表中查询数据不会发现主键查询和普通查询之间的差异。

创建一个海量表:

--构建一个8000000条记录的数据--构建的海量表数据需要有差异性,所以使用存储过程来创建, 拷贝下面代码就可以了,暂时不用理解-- 产生随机字符串
delimiter $$create function rand_string(n INT)returns varchar(255)begin 
declare chars_str varchar(100) default'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';declare return_str varchar(255) default '';declare i int default 0;while i < n do 
set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));set i = i + 1;end while;return return_str;end $$delimiter ;--产生随机数字
delimiter $$create function rand_num()returns int(5)begin 
declare i int default 0;set i = floor(10+rand()*500);return i;end $$delimiter ;--创建存储过程,向雇员表添加海量数据
delimiter $$create procedure insert_emp(in start int(10),in max_num int(10))begindeclare i int default 0; 
set autocommit = 0;  
repeatset i = i + 1;insert into EMP values ((start+i) 
,rand_string(6),'SALESMAN',0001,curdate(),2000,400,rand_num());until i = max_numend repeat;commit;end $$delimiter ;-- 执行存储过程,添加8000000条记录
call insert_emp(100001, 8000000);

注:已经绑定资源,可以自行下载。

进入到一个数据库,用soure命令导入数据:
我的数据包index_data.sql /root/MySQL_data/some 目录下
命令:

//进入到test数据库
use test// 导入数据
source /root/MySQL_data/some/index-data.sql

这是一个有8000000条记录的数据,需要等一会儿,我花费了7分钟。
在这里插入图片描述

此时的表还没有创建索引,进行查询
命令:select * from EMP where empno = 188888
在这里插入图片描述
查询花费了4.69秒,这是在本机一个人来操作,在实际项目中,如果放在公网,假如同时有
1000个人并发查询,那很可能就死机

怎么让查询变得快呢?
答:创建索引

创建索引
命令:alter table EMP add index(empno);
在这里插入图片描述
创建索引花费了26.7秒。

再次查询
命令:select * from EMP where empno = 188888;
在这里插入图片描述
当执行查询的时候会明显的变快。

2. 索引操作

2.1 主键索引

2.1.1 主键索引的特点

  1. 唯一性:主键索引确保了索引列中的每一个值都是唯一的,不允许出现重复值。这是主键索引的核心特性,确保了每一行数据在表中都有一个唯一的标识符。
  2. 非空性:主键索引的列不允许有NULL值。这意味着在插入或更新数据时,必须为该列提供一个有效的、非空的值。
  3. 自动创建:当您在创建表时指定某个列为PRIMARY KEY时,数据库会自动为该列创建一个主键索引。如果表中没有显式定义主键,某些数据库系统可能会自动创建一个隐式的主键(例如,SQL Server中的IDENTITY列)。
  4. 快速查找:主键索引通常是一个B+树索引,这种结构允许数据库高效地进行查找、插入和删除操作。因此,使用主键进行查询通常比使用其他索引或无索引的查询要快得多。
  5. 聚簇索引:在某些数据库系统中,主键索引默认是聚簇索引(Clustered Index)。聚簇索引决定了数据在物理存储上的顺序,这意味着按主键顺序访问数据时性能最佳。一个表只能有一个聚簇索引,因为数据只能有一种物理排序方式。
  6. 约束作用:主键不仅是一个索引,还是一种约束。它确保了表中数据的完整性和一致性,防止了重复记录的插入。
  7. 外键引用:主键通常被用作其他表的外键(Foreign Key),以建立表之间的关系。外键必须引用一个唯一的列,通常是另一个表的主键。

2.1.2 创建主键索引

  1. 创建表时在字段后面指定
    命令:

    create table t1(
    id int primary key,
    name varchar(10),
    age tinyint
    );
    
  2. 创建表时,在最后面指定某列为索引
    命令:

    create table t2(
    id int,
    name varchar(10),
    age tinyint,
    primary key(id)
    );
    
  3. 创建表结束后,用 alter命令
    命令:

    create table t3(
    id int,
    name varchar(10),
    age tinyint
    );alter table t3 add primary key(id);
    

2.2 唯一键索引

2.2.1 唯一键索引的特点

  1. 唯一性
    唯一键索引确保索引列中的每一个值都是唯一的,不允许出现重复值。这是唯一键索引的核心特性,确保了数据的唯一性和完整性。
  2. 允许多个NULL值
    与主键索引不同,唯一键索引允许列中有多个NULL值。这是因为NULL在数据库中被视为未知值,而不是具体的重复值。
  3. 提高查询性能
    唯一键索引可以显著提高查询性能,特别是在需要确保某一列或组合列的值唯一的情况下。通过创建唯一键索引,数据库可以在查询时更快地找到特定的记录。
  4. 约束作用
    唯一键索引不仅是一个索引,还是一种约束。它确保了表中数据的完整性和一致性,防止了重复记录的插入。
  5. 可以应用于多个列
    唯一键索引可以应用于单个列,也可以应用于多个列的组合。当应用于多个列的组合时,确保整个组合的值是唯一的,而不是单个列的值。

2.2.2 唯一索引的创建

  1. 创建表时在字段后面指定
    命令:

    create table t1(
    id int unique,
    name varchar(10),
    age tinyint
    );
    
  2. 创建表时,在最后面设置某列为索引
    命令:

    create table t2(
    id int,
    name varchar(10),
    age tinyint,
    unique(id)
    );
    
  3. 创建表结束后,用 alter命令
    命令:

    create table t3(
    id int,
    name varchar(10),
    age tinyint
    );alter table t3 add unique(id);
    

2.3 普通索引

2.3.1 普通索引的特点

  1. 允许重复值
    普通索引允许索引列中的值重复。这意味着同一个值可以在索引列中出现多次。
  2. 提高查询性能
    普通索引可以显著提高查询性能,特别是对于经常用于查询条件的列。通过创建索引,数据库可以更快地定位到所需的记录。
  3. 不强制非空
    普通索引不要求列中的值必须是非空的。列中的值可以是NULL,并且可以有多个NULL值。
  4. 可以应用于多个列
    普通索引可以应用于单个列,也可以应用于多个列的组合。当应用于多个列的组合时,索引会根据组合列的值进行排序和查找。
  5. 不影响数据插入和更新
    创建普通索引不会像唯一索引那样对数据插入和更新施加额外的约束。这意味着在插入或更新数据时,即使索引列中有重复值,也不会引发错误。

总的来说:普通索引的主要目的是提高查询性能,特别是在频繁用于查询条件的列上

2.3.2 普通索引的创建

  1. 创建表时,在最后面设置某列为索引
    命令:

    create table t1(
    id int,
    name varchar(10),
    age tinyint,
    index(id)
    );
    
  2. 创建表后,用alter命令
    命令:

    create table t2(
    id int,
    name varchar(10),
    age tinyint
    );alter table t2 add index(id);
    
  3. 创建表后,创建一个索引名为 idx_name 的索引,create index
    命令:

    create table t3(
    id int,
    name varchar(10),
    age tinyint
    );create index ind_t3_id on t3(id);
    

    在这里插入图片描述

2.4 全文索引

2.4.1 全文索引的作用

当对文章字段或有大量文字的字段进行检索时,会使用到全文索引。MySQL提供全文索引机制,但是有要求,要求表的存储引擎必须是MyISAM,而且默认的全文索引支持英文,不支持中文。如果对中文进行全文检索,可以使用sphinx的中文版(coreseek)。

早期版本的MySQL(5.6及之前)中,全文索引仅支持MyISAM存储引擎。
从MySQL 5.7开始,InnoDB存储引擎也支持全文索引。

全文索引的主要作用:

  1. 提高搜索性能
  • 快速查找:全文索引使用专门的算法(如倒排索引)来加速文本搜索。通过索引,数据库可以快速定位包含特定单词或短语的记录,而不需要扫描整个表。
  • 减少I/O操作:全文索引减少了磁盘I/O操作,提高了查询效率,特别是在处理大量文本数据时。
  1. 支持复杂的搜索操作
  • 模糊匹配:全文索引可以支持模糊匹配,如部分单词匹配、前缀匹配等。
  • 短语搜索:可以搜索包含特定短语的记录。
  • 近义词搜索:通过配置,可以支持近义词搜索,提高搜索的准确性和相关性。
  • 布尔搜索:支持使用布尔运算符(如AND、OR、NOT)进行复杂查询。
  1. 自然语言搜索
  • 自然语言处理:全文索引可以支持自然语言搜索,即用户可以用自然语言形式的查询语句进行搜索,系统会返回最相关的记录。
  • 权重计算:全文索引可以根据关键词在文档中的出现频率和位置等因素计算权重,返回最相关的记录。

2.4.2 全文索引的创建

创建全文索引:

CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body))engine=MyISAM;

插入数据:

 INSERT INTO articles (title,body) VALUES('MySQL Tutorial','DBMS stands for DataBase ...'),('How To Use MySQL Well','After you went through a ...'),('Optimizing MySQL','In this tutorial we will show ...'),('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),('MySQL vs. YourSQL','In the following database comparison ...'),('MySQL Security','When configured properly, MySQL ...');

查询有没有database数据
命令:select * from articles where body like '%database%';
在这里插入图片描述
显然,可以查询出结果。

但是,有没有使用全文索引呢?
可以explain 工具查看一下。
命令:
explain select * from articles where body like '%database%';
在这里插入图片描述
下面是各项的解释

  1. id: 查询块的标识号。在这个例子中只有一个查询块,所以id为1。

  2. select_type: 表明查询的类型。这里"SIMPLE"表示这个查询只涉及一张表。

  3. table: 被访问的表名。这里是"articles"表。

  4. partitions: 如果表被分区,则此列为分区名称。在这里,由于表未分区,因此为NULL。

  5. type: 访问类型,表明MySQL如何读取数据。这里的"ALL"意味着全表扫描,即MySQL将遍历整张表的所有行。

  6. possible_keys: 可能使用的索引列表。在这个例子中,没有列出任何可能的索引,说明查询没有使用索引。

  7. key: 实际使用的索引。同样,这里也是NULL,确认了没有使用索引的事实。

  8. key_len: 索引中使用的字节数。既然没有使用索引,这一项也为NULL。

  9. ref: 显示了哪个列或常量被用来查找行。这里为NULL,再次确认没有使用索引来优化查询。

  10. rows: MySQL估计的要检查的行数。这里估计为6行。

  11. filtered: 过滤掉不符合WHERE子句的行后剩余的百分比。这里为16.67%,意味着大约只有16.67%的数据会被实际返回给查询。

  12. Extra: 包含其他额外的信息。这里的"Using where"表示MySQL正在使用WHERE子句过滤行。

总的来说,这个EXPLAIN输出表明了一个全表扫描的操作,没有使用任何索引,可能会导致较慢的查询速度,尤其是在大表上的情况。如果可能的话,考虑添加适当的索引以改善查询性能。

已经创建全文索引了,什么没有使用呢?怎么才能使用呢?
命令:

select * from articles
where match(body) against ('database');

在这里插入图片描述

为什么会报错呢?

答:当你在一个全文索引中指定了多个列时,查询时必须使用相同的列集

命令:

select * from articles 
where match(title,body) against('database');

在这里插入图片描述
explain工具查询:
在这里插入图片描述
key对应的是title,可以看出,使用的是title索引,所以,改查询使用索引了。

为什么key只是对应title而不是对应titlebody 呢?

答:创建全文索引的时候使用的是 fulltext(title,body),所以该全文索引是多列,并且该全文索引的名字是第一个列名–title 。这里的key对应的是索引的名字

3. 查询索引

  1. 方法一:
    语法:show keys from table_name;
    上述命令显示的内容看着不方便可以使用:
    show keys from table_name\G

    示例:
    命令:show keys from articles \G

    在这里插入图片描述

  2. 方法2:
    语法:show index from table_name;
    或:show index from table_name\G

    示例:
    命令:show index from t1\G
    在这里插入图片描述

  3. 方法3(查询到的信息表简略):
    语法:desc table_name;

4. 删除索引

  1. 删除主键索引
    语法:alter table table_name drop primary key;

    一个表中只用一个主键索引,像这种范式的删除方法其实还是针对它一个索引。

    示例:
    命令:alter table t1 drop primary;
    在这里插入图片描述

  2. 其他索引的删除
    语法:alter table table_name drop index column_name;
    在这里插入图片描述

  3. 使用 drop index
    语法:drop index index_name on table_name;

    该语法不能删除主键,主键是表中重要的组成部分,只能用 alter table t1 drop primary进行删除

    示例:

    删除表t1的唯一索引unique
    命令:drop index unique on t1
    在这里插入图片描述
    为什么会报错呢?
    答:unique 是索引的类型,不是索引的名字。删除索引的时候要先查询索引的类型

    查询一下索引的名字:show index from t1\G
    在这里插入图片描述

    删除number列的索引drop index number on t1;
    在这里插入图片描述

5. 索引创建原则

索引创建原则

  • 比较频繁作为查询条件的字段应该创建索引
  • 唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件
  • 更新非常频繁的字段不适合作创建索引
  • 不会出现在where子句中的字段不该创建索引

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

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

相关文章

临街矩阵乘以自己转置的含义

总结: 临街矩阵* 邻接矩阵转置的(i,j) 位置表示有多少种线路从元素A跳转一条边最终落到元素j的路线. 这个也叫1_degree.

A010-基于SpringBoot的宠物健康咨询系统的设计与实现

&#x1f64a;作者简介&#xff1a;在校研究生&#xff0c;拥有计算机专业的研究生开发团队&#xff0c;分享技术代码帮助学生学习&#xff0c;独立完成自己的网站项目。 代码可以查看文章末尾⬇️联系方式获取&#xff0c;记得注明来意哦~&#x1f339; 赠送计算机毕业设计600…

DP3复现基础知识(一)—— Hydra 库

DP3 无论是 train 还是 eval 均使用了 Hydra 这一个python 库&#xff0c;这就有些代码在看的时候难以理解其通讯逻辑&#xff0c;例如&#xff1a; hydra.main(version_baseNone,config_pathstr(pathlib.Path(__file__).parent.joinpath(diffusion_policy_3d, config)) ) Hy…

记单词,不要迷信一种方法

记单词&#xff0c;不要迷信一种方法。因为&#xff0c;记单词的目的&#xff0c;就是记住单词呀。 哪一种方法能让你记住&#xff0c;快速、高效、长久地记住&#xff0c;你就使用哪种方法&#xff1b;而且&#xff0c;方法和方法之间&#xff0c;不见得是矛盾的呀。 我们举个…

【自动化利器】12个评估大语言模型(LLM)质量的自动化框架

LLM评估是指在人工智能系统中评估和改进语言和语言模型的过程。在人工智能领域&#xff0c;特别是在自然语言处理&#xff08;NLP&#xff09;及相关领域&#xff0c;LLM评估具有至高无上的地位。通过评估语言生成和理解模型&#xff0c;LLM评估有助于细化人工智能驱动的语言相…

IO流篇(一、File)

目录 一、学习前言 二、文件简介 三、文件使用 1. 绝对路径 vs 相对路径 2. 路径分隔符 3. 属性&#xff08;字段&#xff09; 4. 构造方法 5. 常用方法 5.1. 获取文件的相关信息 5.2. 判断功能 5.3. 新建和删除 5.4. 文件的获取 5.5. 重命名文件 四、文件使用练习…

spring ai 入门 之 结构化输出 - 把大模型llm返回的内容转换成java bean

目录 ​编辑 将AI非结构化文本转换为特定格式数据的应用场景说明 Spring AI 介绍 &#xff1a;为Java开发者打造的AI应用开发框架 Qwen 介绍 &#xff1a; 一个国内领先的开源大模型 Spring AI Alibaba框架介绍 &#xff1a; 一个国内最好的spring ai实现 使用spring ai …

文心一言 VS 讯飞星火 VS chatgpt (383)-- 算法导论24.5 3题

三、对引理 24.10 的证明进行改善&#xff0c;使其可以处理最短路径权重为 ∞ ∞ ∞ 和 − ∞ -∞ −∞ 的情况。引理 24.10(三角不等式)的内容是&#xff1a;设 G ( V , E ) G(V,E) G(V,E) 为一个带权重的有向图&#xff0c;其权重函数由 w : E → R w:E→R w:E→R 给出&…

漫途焊机安全生产监管方案,提升安全生产管理水平!

随着智能制造时代的到来&#xff0c;企业安全生产管理的重要性日益凸显。特别是在现代工厂中&#xff0c;焊机的安全生产监管成为了一个不容忽视的重要环节。传统的焊机安全生产监管方式存在诸多不足&#xff0c;如人工巡检频率低、数据延迟、安全隐患发现不及时等问题。因此&a…

csp2024T3

题目大意&#xff1a;对于每个数而言&#xff0c;可以将其染成红或蓝&#xff0c;对于每一个数&#xff0c;定义其贡献为&#xff0c;当且仅当这个数最近的同色数与其相等&#xff0c;否则其贡献为0&#xff0c;求最大贡献和。 思路&#xff1a;考虑dp 1.考场20多分钟想的奇怪…

十六届蓝桥杯嵌入式资料 看这个就够了(附CSDN开源程序)

蓝桥杯嵌入式终极模板&#xff0c;简单配置&#xff0c;功能全面 一小时玩转蓝桥杯嵌入式开发版 除按键和 LED 其余模块都来自官方选手资料包 代码简洁工整&#xff0c;参数&#xff0c;函数体分模块&#xff0c;有非常详细的注释&#xff0c;初始化由 cubemx 生成 &#xff08…

【测试工具】Fastbot 客户端稳定性测试

背景 做这个主要为了发版之前提前发现崩溃&#xff0c;风险前置。适合客户端很重的业务。 优点&#xff1a;你不改动也能用&#xff0c; 维护成本不高。 缺点&#xff1a;容易进入H5页面无法返回&#xff0c;效果有限。 备注&#xff1a;我这边接手别人维护&#xff0c;公司…

苍穹外卖Bug集合

初始化后端项目运行出现以下问题 以上报错是因为maven和jdk版本不符合&#xff0c;需要将jdk改成17&#xff0c;mavne改成3.9.9

中国雕塑、

孙溟㠭浅析“印章” 印章又称“图章”&#xff0c;玺印起源商代&#xff0c;至少在春秋战国时已出现&#xff0c;因战国时代已普遍使用。 商玺 古玺是先秦印章的通称&#xff0c;秦始皇统一六国之后&#xff0c;皇帝用印称“璽&#xff08;玺&#xff09;”&…

Android App 技能在DuerOS的调试方法

温故知新&#xff0c;我们先回顾一下DuerOS的技能分类。根据不同的视角可以对DuerOS 目前支持的技能类型进行不同的分类&#xff0c;例如&#xff0c;从用户与技能的语音交互方式来看&#xff0c; 可以将技能分为这四种技能类型: L1技能&#xff1a;只支持语音的打开和关闭L2技…

Ghidra无头模式(自动化批处理执行重复性任务)

Ghidra无头模式&#xff08;自动化批处理执行重复性任务&#xff09; 与Ghidra GUI探索单个项目中的单个文件不同&#xff0c;Ghidra headless analyzer&#xff08;Ghidra无头分析器&#xff09;更加适合批处理和用脚本控制Ghidra。 &#xff08;一&#xff09;启动analyzeHea…

ES海量数据插入如何优化性能?

2024年10月NJSD技术盛典暨第十届NJSD软件开发者大会、第八届IAS互联网架构大会在南京召开。百度文心快码总经理臧志分享了《AI原生研发新范式的实践与思考》&#xff0c;探讨了大模型赋能下的研发变革及如何在公司和行业中落地&#xff0c;AI原生研发新范式的内涵和推动经验。 …

el-date-picker日期选择器动态设置日期

需求&#xff1a;选择开始时间&#xff0c;或者在开始时间已存在的情况下&#xff1b;结束时间下拉日期选择框展示从开始日期展示&#xff1b;而不是当前日期&#xff0c;并且结束时间下拉框日期要禁用开始时间之前的日期。 <el-form-item label"开始时间" prop&q…

web实操2——idea创建普通web项目

创建项目 就是普通的java项目&#xff0c;项目右键add framework support&#xff08;添加框架支持&#xff09;,然后点击Web Application&#xff08;web应用程序&#xff09;&#xff0c;然后点击OK。即可。 文件下就会多一个web文件夹&#xff0c;里面是WEB-INF文件夹&…

ES跟Kafka集成

配合流程 1. Kafka作为分布式流处理平台&#xff0c;能够实时收集和处理不同数据源的数据流&#xff1b; 2. 通过Kafka Connect或者Logstash等中间件&#xff0c;可以将Kafka中的数据流实时推送到Elasticsearch中&#xff1b; 3. Elasticsearch接收到数据后&#xff0c;会根据…