高效查询方法

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+... 目录一、简介二、移除给定位置的元素三、移除与特定键值等价的元素四、移除满足特android定条件的元
阅读更多...
基于Python实现高效PPT转图片工具

基于Python实现高效PPT转图片工具

《基于Python实现高效PPT转图片工具》在日常工作中,PPT是我们常用的演示工具,但有时候我们需要将PPT的内容提取为图片格式以便于展示或保存,所以本文将用Python实现PPT转PNG工具,希望... 目录1. 概述2. 功能使用2.1 安装依赖2.2 使用步骤2.3 代码实现2.4 GUI界面3.效
阅读更多...
Java图片压缩三种高效压缩方案详细解析

Java图片压缩三种高效压缩方案详细解析

《Java图片压缩三种高效压缩方案详细解析》图片压缩通常涉及减少图片的尺寸缩放、调整图片的质量(针对JPEG、PNG等)、使用特定的算法来减少图片的数据量等,：本文主要介绍Java图片压缩三种高效... 目录一、基于OpenCV的智能尺寸压缩技术亮点：适用场景：二、JPEG质量参数压缩关键技术：压缩效果对比
阅读更多...
C#使用SQLite进行大数据量高效处理的代码示例

C#使用SQLite进行大数据量高效处理的代码示例

《C#使用SQLite进行大数据量高效处理的代码示例》在软件开发中,高效处理大数据量是一个常见且具有挑战性的任务,SQLite因其零配置、嵌入式、跨平台的特性,成为许多开发者的首选数据库,本文将深入探... 目录前言准备工作数据实体核心技术批量插入：从乌龟到猎豹的蜕变分页查询：加载百万数据异步处理：拒绝界面
阅读更多...
Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

Spring Boot + MyBatis Plus 高效开发实战从入门到进阶优化(推荐)

《SpringBoot+MyBatisPlus高效开发实战从入门到进阶优化(推荐)》本文将详细介绍SpringBoot+MyBatisPlus的完整开发流程,并深入剖析分页查询、批量操作、动... 目录Spring Boot + MyBATis Plus 高效开发实战：从入门到进阶优化1. MyBatis
阅读更多...
SpringBoot使用OkHttp完成高效网络请求详解

SpringBoot使用OkHttp完成高效网络请求详解

《SpringBoot使用OkHttp完成高效网络请求详解》OkHttp是一个高效的HTTP客户端,支持同步和异步请求,且具备自动处理cookie、缓存和连接池等高级功能,下面我们来看看SpringB... 目录一、OkHttp 简介二、在 Spring Boot 中集成 OkHttp三、封装 OkHttp
阅读更多...
使用Python高效获取网络数据的操作指南

使用Python高效获取网络数据的操作指南

《使用Python高效获取网络数据的操作指南》网络爬虫是一种自动化程序,用于访问和提取网站上的数据,Python是进行网络爬虫开发的理想语言,拥有丰富的库和工具,使得编写和维护爬虫变得简单高效,本文将... 目录网络爬虫的基本概念常用库介绍安装库Requests和BeautifulSoup爬虫开发发送请求解
阅读更多...
C++实现回文串判断的两种高效方法

C++实现回文串判断的两种高效方法

《C++实现回文串判断的两种高效方法》文章介绍了两种判断回文串的方法：解法一通过创建新字符串来处理,解法二在原字符串上直接筛选判断,两种方法都使用了双指针法,文中通过代码示例讲解的非常详细,需要的朋友... 目录一、问题描述示例二、解法一：将字母数字连接到新的 string思路代码实现代码解释复杂度分析三、
阅读更多...
轻松上手MYSQL之JSON函数实现高效数据查询与操作

轻松上手MYSQL之JSON函数实现高效数据查询与操作

《轻松上手MYSQL之JSON函数实现高效数据查询与操作》：本文主要介绍轻松上手MYSQL之JSON函数实现高效数据查询与操作的相关资料,MySQL提供了多个JSON函数,用于处理和查询JSON数... 目录一、jsON_EXTRACT 提取指定数据二、JSON_UNQUOTE 取消双引号三、JSON_KE
阅读更多...
Tomcat高效部署与性能优化方式

Tomcat高效部署与性能优化方式

《Tomcat高效部署与性能优化方式》本文介绍了如何高效部署Tomcat并进行性能优化,以确保Web应用的稳定运行和高效响应,高效部署包括环境准备、安装Tomcat、配置Tomcat、部署应用和启动T... 目录Tomcat高效部署与性能优化一、引言二、Tomcat高效部署三、Tomcat性能优化总结Tom
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2