高效查询方法

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。

相关文章

C++高效内存池实现减少动态分配开销的解决方案

C++高效内存池实现减少动态分配开销的解决方案

《C++高效内存池实现减少动态分配开销的解决方案》C++动态内存分配存在系统调用开销、碎片化和锁竞争等性能问题,内存池通过预分配、分块管理和缓存复用解决这些问题,下面就来了解一下... 目录一、C++内存分配的性能挑战二、内存池技术的核心原理三、主流内存池实现：TCMalloc与Jemalloc1. TCM
阅读更多...
Python基于微信OCR引擎实现高效图片文字识别

Python基于微信OCR引擎实现高效图片文字识别

《Python基于微信OCR引擎实现高效图片文字识别》这篇文章主要为大家详细介绍了一款基于微信OCR引擎的图片文字识别桌面应用开发全过程,可以实现从图片拖拽识别到文字提取,感兴趣的小伙伴可以跟随小编一... 目录一、项目概述1.1 开发背景1.2 技术选型1.3 核心优势二、功能详解2.1 核心功能模块2.
阅读更多...
基于Python构建一个高效词汇表

基于Python构建一个高效词汇表

《基于Python构建一个高效词汇表》在自然语言处理（NLP）领域,构建高效的词汇表是文本预处理的关键步骤,本文将解析一个使用Python实现的n-gram词频统计工具,感兴趣的可以了解下... 目录一、项目背景与目标1.1 技术需求1.2 核心技术栈二、核心代码解析2.1 数据处理函数2.2 数据处理流程
阅读更多...
Python中bisect_left 函数实现高效插入与有序列表管理

Python中bisect_left 函数实现高效插入与有序列表管理

《Python中bisect_left函数实现高效插入与有序列表管理》Python的bisect_left函数通过二分查找高效定位有序列表插入位置,与bisect_right的区别在于处理重复元素时... 目录一、bisect_left 基本介绍1.1 函数定义1.2 核心功能二、bisect_left 与
阅读更多...
Python使用FFmpeg实现高效音频格式转换工具

Python使用FFmpeg实现高效音频格式转换工具

《Python使用FFmpeg实现高效音频格式转换工具》在数字音频处理领域,音频格式转换是一项基础但至关重要的功能,本文主要为大家介绍了Python如何使用FFmpeg实现强大功能的图形化音频转换工具... 目录概述功能详解软件效果展示主界面布局转换过程截图完成提示开发步骤详解1. 环境准备2. 项目功能结
阅读更多...
Python Pandas高效处理Excel数据完整指南

Python Pandas高效处理Excel数据完整指南

《PythonPandas高效处理Excel数据完整指南》在数据驱动的时代,Excel仍是大量企业存储核心数据的工具,Python的Pandas库凭借其向量化计算、内存优化和丰富的数据处理接口,成为... 目录一、环境搭建与数据读取1.1 基础环境配置1.2 数据高效载入技巧二、数据清洗核心战术2.1 缺失
阅读更多...
使用Python和SQLAlchemy实现高效的邮件发送系统

使用Python和SQLAlchemy实现高效的邮件发送系统

《使用Python和SQLAlchemy实现高效的邮件发送系统》在现代Web应用中,邮件通知是不可或缺的功能之一,无论是订单确认、文件处理结果通知,还是系统告警,邮件都是最常用的通信方式之一,本文将详... 目录引言1. 需求分析2. 数据库设计2.1 User 表（存储用户信息）2.2 CustomerO
阅读更多...
Java中的StringBuilder之如何高效构建字符串

Java中的StringBuilder之如何高效构建字符串

《Java中的StringBuilder之如何高效构建字符串》本文将深入浅出地介绍StringBuilder的使用方法、性能优势以及相关字符串处理技术,结合代码示例帮助读者更好地理解和应用,希望对大家... 目录关键点什么是 StringBuilder？为什么需要 StringBuilder？如何使用 St
阅读更多...
MySQL重复数据处理的七种高效方法

MySQL重复数据处理的七种高效方法

《MySQL重复数据处理的七种高效方法》你是不是也曾遇到过这样的烦恼：明明系统测试时一切正常,上线后却频频出现重复数据,大批量导数据时,总有那么几条不听话的记录导致整个事务莫名回滚,今天,我就跟大家分... 目录1. 重复数据插入问题分析1.1 问题本质1.2 常见场景图2. 基础解决方案：使用异常捕获3.
阅读更多...
如何高效移除C++关联容器中的元素

如何高效移除C++关联容器中的元素

《如何高效移除C++关联容器中的元素》关联容器和顺序容器有着很大不同,关联容器中的元素是按照关键字来保存和访问的,而顺序容器中的元素是按它们在容器中的位置来顺序保存和访问的,本文介绍了如何高效移除C+... 目录一、简介二、移除给定位置的元素三、移除与特定键值等价的元素四、移除满足特android定条件的元
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2