高效查询方法

2024-02-19 03:44
文章标签 高效 查询方法

本文主要是介绍高效查询方法，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

为了提高数据的查询速度，最常用的解决方案就是给表中变量创建索引。我们可以将索引理解成书的目录，如果一本书没有目录，那检索起来可能就比较麻烦，一旦有了目录，我们就可以根据目录进行索引，很快地找到我们需要的内容。同样的道理，如果数据表中有了索引，就可以大大提高MySQL的执行效率。

常见索引类型

普通索引

普通索引是一种没有任何约束的索引，它对表中变量的值不做任何的限制，不管变量的值是否存在重复值或缺失值（也就是NULL值），所以普通索引是使用最频繁的一种索引。

可以通过两种方式来建立普通索引：

  • 创建新表时设定某个字段为普通索引

    CREATE TABLE <table_name_>( field1 data_type1,field2 data_type2,field3 data_type3,……INDEX <index_name>(field1));

  • 基于已有的表添加普通索引

    CREATE INDEX <index_name> ON <table_name_>(field_list); -- 基于已有的表创建索引
-- 或者
ALTER TABLE <table_name_> ADD INDEX <index_name> ON(field_list); -- 基于已有表修改索引

例子

以校园一卡通消费数据stu_card为例，查询出交易时间在2013年9月1日的所有记录 (数据来源于菊花酱数据分析)

-- 没有创建索引之前的条件查询
SELECT * FROM stu_card
WHERE custom_date BETWEEN '2013-09-01 00:00:00'
AND '2013-09-01 23:59:59';

# 创建索引
CREATE INDEX date_index ON stu_card(custom_date);# 执行查询
SELECT * FROM stu_card
WHERE custom_date BETWEEN '2013-09-01 00:00:00'
AND '2013-09-01 23:59:59';

在返回的表格中是没有date_index索引字段的

唯一索引

相对于普通索引来说，唯一索引对字段或者字段组合是有约束的，也就是确保字段或者字段组合的每一个观测值都是唯一的，不能存在重复值。如果字段中还有多个空白字符串，也算是有重复值，因为空字符串代表一种值。

一个表中可以有多个唯一索引，创建唯一索引的方法也有两种，与创建普通索引类似：

  • 在创建新表的时候设置唯一索引；

    -- 建表时创建索引
CREATE TABLE <table_name_>( field1 data_type1,field2 data_type2,field3 data_type3,……UNIQUE <index_name>(field1,field2));

  • 对已有表添加唯一索引

    -- 对已有表添加索引，可以通过创建法或修改法
CREATE UNIQUE INDEX <index_name> ON <table_name_>(field_list); #基于已有的表创建索引
-- 或者
ALTER TABLE <table_name_> ADD UNIQUE <index_name> ON(field_list); #基于已有表修改索引

例子

以某平台的旅游交易数据为例 (数据来源于菊花酱数据分析)

新建数据表tourism_orders
CREATE TABLE tourism_orders(userid VARCHAR(20),orderid VARCHAR(12),orderTime VARCHAR(15),orderType VARCHAR(2),city VARCHAR(20),country VARCHAR(20),continent VARCHAR(10));
往表中插入数据
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server8.0/Uploads/tourism_orders.csv'
INTO TABLE tourism_orders
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;-- 查询数据前几行
SELECT * FROM tourism_orders LIMIT 10;
无索引下的条件查询
SELECT *
FROM tourism_orders
WHERE userid = '100000001445'; -- 约0.016秒
有索引下的条件查询
# 创建两个组合变量的唯一索引
CREATE UNIQUE INDEX id_idx ON tourism_orders (userid, orderid);# 再次执行查询语句
SELECT *
FROM tourism_orders
WHERE userid = '100000001445'; -- 几乎为0秒

主键索引

主键索引对字段的要求最为严格，必须确保字段中的值既不存在重复值也不存在缺失值。

与普通索引和唯一索引不同的是，一张表中只能有一个主键索引

建表时创建索引

CREATE TABLE <table_name_>( field1 data_type1,field2 data_type2,field3 data_type3,……PRIMARY KEY <index_name>(field1,field2));

对已有表添加索引

ALTER TABLE <table_name_> ADD PRIMARY KEY <index_name>(field_list); #基于已有表修改索引
例子

使用用户注册数据和用户交易数据 ( 数据来源于菊花酱数据分析)

创建用户注册表和RFM表
CREATE TABLE regit_info(uid VARCHAR(10),gender TINYINT,age TINYINT,regit_date DATE);CREATE TABLE RFM(uid VARCHAR(10),R INT,F TINYINT,M DECIMAL(10,2));
批量导入数据
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server8.0/Uploads/user_regit_RFM/regit_info.csv'
INTO TABLE regit_info
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server8.0/Uploads/user_regit_RFM/RFM.csv'
INTO TABLE RFM
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
内连接完成两表字段的合并
SELECT t1.*,t2.R,t2.F,t2.M
FROM regit_info AS t1
INNER JOIN RFM AS t2 ON t1.uid=t2.uid
LIMIT 10000;
添加主键索引,再次执行查询
-- 添加主键索引
ALTER TABLE regit_info ADD PRIMARY key (uid);
ALTER TABLE RFM ADD PRIMARY key (uid);-- 再次执行查询
SELECT t1.*,t2.R,t2.F,t2.M
FROM regit_info AS t1
INNER JOIN RFM AS t2 ON t1.uid=t2.uid
LIMIT 10000;

索引的查询

如果想要对表进行操作，通常需要知道:

  • 表是否已经存在索引
    • 如果存在这些索引
      • 是什么类型的
      • 名称是什么
      • 设置在哪些字段上
      • 等等

只有了解表中的索引信息，我们才能进一步管理索引

-- 查询索引信息语法
SHOW INDEX FROM <table_name_>;

例子

查询用户注册表regit_info和旅游交易表tourism_orders的索引信息 ( 数据来源于菊花酱数据分析)

SHOW INDEX FROM regit_info;
SHOW INDEX FROM tourism_orders;

删除索引

删除普通索引和唯一索引

DROP INDEX <index_name> ON <table_name_>;
-- 或者
ALTER TABLE <table_name_> DROP INDEX <index_name>;

用于删除主键索引

ALTER TABLE <table_name_> DROP PRIMARY KEY;

例子

删除用户注册表regit_info中的主键索引

ALTER TABLE regit_info DROP PRIMARY KEY; -- 删除索引
SHOW INDEX FROM regit_info; -- 查看索引

删除旅游交易表tourism_orders中的唯一索引

DROP INDEX id_idx ON tourism_orders; -- 删除索引
SHOW INDEX FROM tourism_orders; -- 查看索引

关于索引的注意事项

尽管索引有提速的功能（可以提高响应的select的效率），但是也不能滥用，因为它会降低数据表的写操作速度（insert 和 update的效率会变低），也会占用一定的磁盘空间。

何时创建索引

  • WHERE 关键词后面的字段创建索引，可以加快条件判断速度
  • ORDER BY关键词后面的字段创建索引，可以加快排序速度
  • 表连接关键词 ON 后面的字段创建索引，可以加快表连接速度
  • 包含大量NULL的字段不适合创建索引，因为索引不可以包含NULL值
  • 包含大量重复值的字段不适合创建索引，因为基于索引的查询规则，在进行条件筛选的时候可能会产生大量的数据行，此时索引并不能加快数据库搜索过程中的扫描速度

索引无效的情况

  • WHERE 关键词后面的条件表达式中如果使用IN、OR、!=或者<>，均会导致索引无效

    • 解决方法:将“!=”或者“<>”替换为">AND<“，将"IS NOT NULL"替换为”>=CHR(0)"。
      • CHR(0):代表一个空字符（null character）

  • 筛选或排序过程中，如果对索引列使用函数，则索引失效。

  • 筛选过程中，如果字符型字段写成了数值型的数字，则索引失效（比如，用户id字段是字符型，那筛选的时候需要写where uid = “1”）

  • 使用模糊查询的时候，如果将通配符放在开头，则索引失效（like “%aaa%” 不会使用索引而like“aaa%”可以使用索引）

  • 对于多列的组合索引，遵循左原则，例如对字段A,B,C设置索引 INDEX(A,B,C) ，则"A>0" 、“A=1AND B>10”、“A=10 AND B<6 AND C>100"都可以使组合索引有效，但是"B>10”、"B<6 ANDC>100"都会导致组合索引失效。

  • 在JOIN操作中，关键词ON后面的字段类型要保持一致（也就是左表中这个字段的数据类型和右表中同一字段的数据类型要保持一致），否则索引无效。

这篇关于高效查询方法的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/723391。

相关文章

高效+灵活，万博智云全球发布AWS无代理跨云容灾方案！

高效+灵活，万博智云全球发布AWS无代理跨云容灾方案！

摘要 近日，万博智云推出了基于AWS的无代理跨云容灾解决方案，并与拉丁美洲，中东，亚洲的合作伙伴面向全球开展了联合发布。这一方案以AWS应用环境为基础，将HyperBDR平台的高效、灵活和成本效益优势与无代理功能相结合，为全球企业带来实现了更便捷、经济的数据保护。 一、全球联合发布 9月2日，万博智云CEO Michael Wong在线上平台发布AWS无代理跨云容灾解决方案的阐述视频，介绍了
阅读更多...
嵌入式QT开发：构建高效智能的嵌入式系统

嵌入式QT开发：构建高效智能的嵌入式系统

摘要： 本文深入探讨了嵌入式 QT 相关的各个方面。从 QT 框架的基础架构和核心概念出发，详细阐述了其在嵌入式环境中的优势与特点。文中分析了嵌入式 QT 的开发环境搭建过程，包括交叉编译工具链的配置等关键步骤。进一步探讨了嵌入式 QT 的界面设计与开发，涵盖了从基本控件的使用到复杂界面布局的构建。同时也深入研究了信号与槽机制在嵌入式系统中的应用，以及嵌入式 QT 与硬件设备的交互，包括输入输出设
阅读更多...
高效录音转文字：2024年四大工具精选！

高效录音转文字：2024年四大工具精选！

在快节奏的工作生活中，能够快速将录音转换成文字是一项非常实用的能力。特别是在需要记录会议纪要、讲座内容或者是采访素材的时候，一款优秀的在线录音转文字工具能派上大用场。以下推荐几个好用的录音转文字工具！ 365在线转文字 直达链接：https://www.pdf365.cn/ 365在线转文字是一款提供在线录音转文字服务的工具，它以其高效、便捷的特点受到用户的青睐。用户无需下载安装任何软件，只
阅读更多...
【C++高阶】C++类型转换全攻略：深入理解并高效应用

【C++高阶】C++类型转换全攻略：深入理解并高效应用

📝个人主页🌹：Eternity._ ⏩收录专栏⏪：C++ “ 登神长阶 ” 🤡往期回顾🤡：C++ 智能指针 🌹🌹期待您的关注 🌹🌹 ❀C++的类型转换 📒1. C语言中的类型转换📚2. C++强制类型转换⛰️static_cast🌞reinterpret_cast⭐const_cast🍁dynamic_cast 📜3. C++强制类型转换的原因📝
阅读更多...
基于 YOLOv5 的积水检测系统：打造高效智能的智慧城市应用

基于 YOLOv5 的积水检测系统：打造高效智能的智慧城市应用

在城市发展中，积水问题日益严重，特别是在大雨过后，积水往往会影响交通甚至威胁人们的安全。通过现代计算机视觉技术，我们能够智能化地检测和识别积水区域，减少潜在危险。本文将介绍如何使用 YOLOv5 和 PyQt5 搭建一个积水检测系统，结合深度学习和直观的图形界面，为用户提供高效的解决方案。 源码地址： PyQt5+YoloV5 实现积水检测系统 预览： 项目背景
阅读更多...
MiniGPT-3D, 首个高效的3D点云大语言模型，仅需一张RTX3090显卡，训练一天时间，已开源

MiniGPT-3D, 首个高效的3D点云大语言模型，仅需一张RTX3090显卡，训练一天时间，已开源

项目主页：https://tangyuan96.github.io/minigpt_3d_project_page/ 代码：https://github.com/TangYuan96/MiniGPT-3D 论文：https://arxiv.org/pdf/2405.01413 MiniGPT-3D在多个任务上取得了SoTA，被ACM MM2024接收，只拥有47.8M的可训练参数，在一张RTX
阅读更多...
利用命令模式构建高效的手游后端架构

利用命令模式构建高效的手游后端架构

在现代手游开发中，后端架构的设计对于支持高并发、快速迭代和复杂游戏逻辑至关重要。命令模式作为一种行为设计模式，可以有效地解耦请求的发起者与接收者，提升系统的可维护性和扩展性。本文将深入探讨如何利用命令模式构建一个强大且灵活的手游后端架构。 1. 命令模式的概念与优势 命令模式通过将请求封装为对象，使得请求的发起者和接收者之间的耦合度降低。这种模式的主要优势包括： 解耦请求发起者与处理者
阅读更多...
PDFQFZ高效定制：印章位置、大小随心所欲

PDFQFZ高效定制：印章位置、大小随心所欲

前言 在科技编织的快节奏时代，我们不仅追求速度，更追求质量，让每一分努力都转化为生活的甜蜜果实——正是在这样的背景下，一款名为PDFQFZ-PDF的实用软件应运而生，它以其独特的功能和高效的处理能力，在PDF文档处理领域脱颖而出。 它的开发，源自于对现代办公效率提升的迫切需求。在数字化办公日益普及的今天，PDF作为一种跨平台、不易被篡改的文档格式，被广泛应用于合同签署、报告提交、证书打印等各个
阅读更多...
excel翻译软件有哪些？如何高效提翻译？

excel翻译软件有哪些？如何高效提翻译？

你是否曾在面对满屏的英文Excel表格时感到头疼？项目报告、数据分析、财务报表... 当这些重要的信息被语言壁垒阻挡时，效率和理解度都会大打折扣。别担心，只需3分钟，我将带你轻松解锁excel翻译成中文的秘籍。 无论是职场新人还是老手，这一技巧都将是你的得力助手，让你在信息的海洋中畅游无阻。 方法一：使用同声传译王软件 同声传译王是一款专业的翻译软件，它支持多种语言翻译，可以excel
阅读更多...
《C++中的移动构造函数与移动赋值运算符：解锁高效编程的最佳实践》

《C++中的移动构造函数与移动赋值运算符：解锁高效编程的最佳实践》

在 C++的编程世界中，移动构造函数和移动赋值运算符是提升程序性能和效率的重要工具。理解并正确运用它们，可以让我们的代码更加高效、简洁和优雅。 一、引言 随着现代软件系统的日益复杂和对性能要求的不断提高，C++程序员需要不断探索新的技术和方法来优化代码。移动构造函数和移动赋值运算符的出现，为解决资源管理和性能优化问题提供了有力的手段。它们允许我们在不进行不必要的复制操作的情况下，高效地转移资源
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2