基于索引的SQL调优

GaussDB SQL调优:建立合适的索引 所有调优手段都是围绕资源使用开展的。比如做典型点查询的时候,可以用seqscan+filter(即读取每一条元组和点查询条件进行匹配)实现,也可以通过indexscan实现,显然indexscan可以以更小的代价实现相同的效果。拥有云上高可用,高可靠,高安全,弹性伸缩,一键部署,快速备份恢复,监控告警等关键能力,能为企业提供功能全面,稳定可靠,扩展性强,性能优越的企业级数据库服务。很明显,执行计划中存在SubPlan,并且SubPlan中的运算相当重,即此SubPlan是一个明确的性能瓶颈点。 阅读详情

基于索引的SQL调优

关系型数据库MySQL是有一套明确的规则去执行各类SQL语句的。基于这套规则,开发人员可以在数据库层面上,提高SQL的执行效率。

官方的优化文档给了很详细的优化建议,本文结合自身理解,摘一些可执行性高,且比较好分类的进行介绍。开始前,我们需要先讲一下索引的类型。

 

重要的索引类型

主键(Clusterd Index)
在这里插入图片描述
主键索引会附带该行所有的数据信息,“打包”成一个数据页挂载在B+树的叶子节点

 

二级索引
在这里插入图片描述

二级索引只会挂载对应的主键ID

 

联合索引
在这里插入图片描述

联合索引是把多个列整合,并以第一个位置上的列为B+树的节点做排序和平衡性;在节点数据的最后,同样挂载对应的主键ID

 

Hash索引

Hash索引是MySQL针对热点数据,自动创建的索引类型。针对Hash碰撞问题,MySQL会根据长度在节点后加链表或红黑树

 

如何设置索引?

  • 从表格数据推算离散性
SELECT COUNT(DISTINCT name) / COUNT(*) FROM table1

把所有列都计算一遍离散值,越接近1则越适合做索引

  • 占用空间小,前缀索引

优先选择字节数小的数据类型;对于TEXT,BLOB这类长字段则需要使用前缀作为索引

SELECT COUNT(DISTINCT LEFT(article, 10)) / COUNT(*) FROM table1

计算不同长度前缀的离散值

  • 三星索引评价标准:
SELECT song FROM albums WHERE musician = 'Maroon5' AND album_name='Songs About Jane' ORDER BY song
  1. 一个SQL语句,在经过索引的过滤后,剩下的索引宽度越窄越好——剩下的数据量越小越好;

  2. 由于B+树本身会对索引排序,因此在用ORDER BY命令时能直接利用索引的排序是更好的;

  3. 如果SQL语句中需要的所有列都可以被一个索引包涵,是更好的

在这里插入图片描述

 

如何使用索引?

以下面这个表为例,重点介绍几个优化点

CREATE TABLE `order_exp` (
  `id` bigint(22) NOT NULL AUTO_INCREMENT,
  `order_no` varchar(50) NOT NULL,
  `order_note` varchar(100) NOT NULL,
  `insert_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `expire_duration` bigint(22) NOT NULL,
  `expire_time` datetime NOT NULL,
  `order_status` smallint(6) NOT NULL DEFAULT '0',

  PRIMARY KEY (`id`)
  UNIQUE KEY `u_idx_day_status` (`insert_time`,`order_status`,`expire_time`),
  KEY `idx_order_no` (`order_no`),
  KEY `idx_expire_time` (`expire_time`)
)

 

不要在索引上进行操作

避免 加减;函数;类型转换;使用null值

EXPLAIN SELECT * FROM order_exp WHERE id + 1 = 17;
typekeykey_lenExtra
ALLNULLNULLUsing where

可以看到,在主键用了+1操作后,执行计划中的key就变为NULL,进而导致查询效率变低。

 

最大化利用索引
  • 左前缀匹配;全字匹配;范围要放在最后;
EXPLAIN Select * from s1 where order_status=1 and expire_time='2021-03-22 18:35:14';
typekeykey_lenExtra
ALLNULLNULLUsing where

对于索引UNIQUE KEY u_idx_day_status (insert_time,order_status,expire_time)

联合索引是以最左侧的列建立索引的,所以要使用该索引,必须要有insert_time这一列

MySQL是按照声明的顺序安排列的前后关系,因此为了更大化利用索引——让key_len足够长,需要把范围条件尽量推到后面顺序的列上

EXPLAIN select * from order_exp where insert_time>'2021-03-22 18:23:42' and insert_time<'2021-03-22 18:35:00';
typekeykey_lenExtra
rangeu_idx_day_status6Using index condition
EXPLAIN select * from order_exp
where insert_time='2021-03-22 18:34:55' and order_status=0 and expire_time>'2021-03-22
18:23:57' and expire_time<'2021-03-22 18:35:00' ;
typekeykey_lenExtra
rangeu_idx_day_status13Using index condition

第二个查询语句的key_len明显更长一些。

 

  • like使用前缀;
explain SELECT * FROM order_exp WHERE order_no like '%_6S';
typekeykey_lenExtra
ALLNULLNULLUsing where

MySQL是只支持前缀做索引的,如果想要用后缀,最好自己根据后缀新建一个索引列。

 

  • OR尽量为同一个字段;排序时按固定顺序,且排序字段同属于一个索引;
explain SELECT * FROM order_exp WHERE expire_time= '2021-03-22 18:35:09' OR order_note = 'abc';
typekeykey_lenExtra
ALLNULLNULLUsing where

MySQL在优化查询时,一个语句只会用一个索引,因此对于OR,可以用两个语句分别查询,再拼接结果。

 

业务优化查询

按照主键顺序插入;count查询可通过redis完成;limit分页

有的功能需要用到分页功能,比如从满足条件的第100行开始,取10条数据返回

select * from order_exp limit 100,10;
typekeykey_lenExtra
ALLNULLNULL

这样的语句会直接扫描前100行数据,然后才能返回后10条。为了提高查询效率,我们可以直接在后端开发上做改进,进而优化SQL。

比如直接指定ID

select * from order_exp where id > 120 order by id limit 10;

 

结语

其实相比于SQL的优化来说,服务端架构的优化难度更小,且效果更好。因此在设计一个功能时,先考虑清楚架构问题;再根据需要对SQL进行调优。

 

https://dev.mysql.com/doc/refman/8.0/en/optimization.html

https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

https://book.douban.com/subject/26419771/

Agisoft Metashape 基于RTK技术的无人机航测免像控技术流程 利用无人机进行航空摄影测量,其使用方便、快速获得成果,已经是测绘行业外业生产的主要方式。RTK定位系统的出现,其厘米级的定位系统,使得航测无人机在作业时,能够准确获取拍照时的GPS位置,能够大量减少像控点,即使不使用像控点也能满足规范对精度的要求。可以有效解决特殊地形(如山川、峡谷、河流、沼泽、水库等)外业人员很难布设像控点的情况。 阅读详情

相关推荐

基于opencv自定义网格板的相机标定软件源码

本软件开发版使用的是VS2013和QT5.7, 安装版的需要安装VS核心库(目录里的vcredist_x64.exe, 已安装VS的不需要)。采用强大的模板匹配算法,本程序适用于自定义大范围网格标定(本例程网格使用的是自己画的网格板31*21*10cm),网格点识别度高,矫正速度快,精度高。 使用步骤 第1步: 打开图片,打开标定工具GridCalibTool.exe, 然后选择打开一张待标定的图(CalibFiles文件夹下的500.jpg)。如下图: 第2步: 提取待标定区域(去掉多余部分,只保留网

qq_33138437的博客 1123

索引与sql的优化

(2)主键顺序插入:因为innodb类型的表是按照主键的顺序存储的,所以导入的数据按照主键的顺序排列,可以有效的提高导入数据的效率,如果innoDB表没有主键,那么系统会自动默认创建一个内部列作为主键,所以如果可以给表创建一个主键,将可以利用主键顺序插入以提高导入数据的效率;尽量减少额外排序,通过索引直接返回数据,where条件和order by 使用相同的索引,并且order by的顺序和索引的顺序相同,且order by的字段要么都是升序,要么都是降序。如果没有索引,则应该考虑增加索引;...

m0_71467744的博客 1758

摄像机实时接入分析平台视频分析网关AI智能分析智慧营业厅方案

通过智能化的改造,可以提高营业厅的运营效率,降低运营成本,同时提升客户体验和安全保障。

EasyDarwin开源社区技术博客 705

SQL索引的使用以及优化

来自B站尚硅谷课程总结。

m0_50677223的博客 945

Windows服务器渗透必备:5款杀软在线识别平台实测对比(附避坑指南)

本文针对Windows服务器渗透测试中的关键环节,实测对比了五款主流的在线杀软识别平台。文章从实战场景出发,深入分析了各平台在识别准确率、响应速度、隐匿性及使用风险上的差异,并提供了详尽的避坑指南与高级隐匿技巧,旨在帮助安全人员快速、精准地识别目标环境安全防护,为后续行动奠定情报基础。

weixin_29322553的博客 434

afe模拟前端的重要性_高性能模拟前端信号链解决方案如何助力医疗超声系统发展...

1942年,奥地利TDussik使用A型超声成像系统穿透性探测颅脑,并于1949年获得头部的超声图像,此举昭示超声系统进入到医疗领域。直到如今,超声系统作为用于人体内部的无创可视化技术,被广泛用于医疗领域。最近一份报告称到2022年,中国医疗超声诊断仪器行业的市场规模预计将达到15.8亿美元,保持6%左右的年均增长率。高性能模拟信号链前端是超声设备的关键组成部分,作为高性能模拟技术主要的提供商AD...

weixin_28799111的博客 1091

SQL的索引以及优化

我们不管在写代码,或者对执行数据库操作的时候,SQL优化是不可缺少的一环。所以这个功能至关重要。下面我们来说说SQL语句优化: 定位慢查询 show status like 'connections' ------------------------当有多少客户端连接数据库 show status like 'slow_queries'----------------------查询有多少......

wszhm123的博客 3279

2022(一等奖)B360基于Arcpy与SVM的滑坡易发性评价

此外,在ArcGIS平台下,“易发性图预测(多进程)”工具的运行时间为2h 48m 3s,“易发性图预测(单进程)”工具的运行时间为5h 19m 27s,运行时间缩短了近2h 31m 24s,运行效率提高了47.39%。在ArcGIS Pro平台下,“易发性图预测(多进程)”工具的运行时间为20m 12s,“易发性图预测(单进程)”工具的运行时间为1h 26m 47s,将运行时间缩短了近1h 6m 35s,运行效率提高了76.72%。滑坡的发生受各种影响因素的影响,目前,影响因素的选择没有一个统一的标准。

2301_77310370的博客 1468

sql优化----索引(你奶奶看了都能学会)

通过创建索引,数据库可以直接定位到包含所需数据的位置,避免了全表扫描,从而大大提高了查询效率。特别是在处理大数据量时,索引能够显著减少查询时间。索引就好比一本书的目录部分,通过目录找到对应文章的页码,便可以快速定位到需要的文章。B-Tree和B+Tree目前大部分数据库系统及文件系统都采用B-Tree和B+Tree作为索引结构。如上图是一个b+树。

m0_68882181的博客 1564

STM8开发环境搭建(STVD + COSMIC)

一、STM8开发IDE 1. STVD + COSMIC ST Visual Develop (STVD)只支持汇编开发,C语言开发需要再安装COSMIC,配套起来使用。 2. IAR IAR也可以用来开发STM8 下面介绍STVD + COSMIC的搭建方式 二、环境搭建 1. STVD下载、安装 直接从官网下载:https://www.st.com/en/development-tools/stvd-stm8.html 安装很简单,基本下一步往下点。 2. COSMIC下载、安装 对于stm8可以使用3

a183635870的博客 1万+

C++多线程 task--std::promise和std::future

本文主要讲解C++11/14/17中所提供的应用于多线程编程中的Task机制。Task可由如下几部分取得而本文主要讲解std::promise和std::future相关的内容。std::promise和std::future通过一个数据管道进行交流,可以将std::promise看作生产者,std::future看作消费者,其两者形成如下的方式交互std::promise本身是一个模版类,通过std::promise可以像数据管道中设置value,异常和相应的通知信息。std::promise提供了如下接

qls315的博客 1819

sql索引优化实战总结

一、 避免索引失效 -- 最左匹配原则 * -- 范围条件右边的索引失效 -- 不再索引列上做任何操作 * -- 使用不等于(!=或者<>)索引失效 -- is not null无法使用索引 -- like以通配符开头(%qw)索引失效 * -- 字符串不加引号索引失效 -- 使用or连接索引失效 -- 尽量使用覆盖索引 1.1 全值匹配 -- 创建组合索引 create index idx_name_dep_age on employee(name,dep_id,age) -- 索引字

weixin_43811057的博客 2348

C# WinForm 基于DirectShow.dll编写的程序,可边预览边保存视频,音频和图片

基于微软的DirectShow.dll的二次开发1.自动识别连接的摄像头跟麦克风且可以对其进行选择(默认选中第一个)2.可录制wmv和avi两种格式的视频并且都带有音频,avi可以录制的分辨率和帧数进行选择,wmv默认进行了淸晰度优化如不喜欢可去除代码中有相关提示3.可在预览点开之后进行拍照,也可边录制视频边拍照4.添加了视频和音频的压缩功能(酌情使用)这次二次开发发现C#对DirectShow的相关信息太少,这次做完也不敢藏私共享出来,还望大家指正

索引调优 —— OceanBase SQL 性能调优实践分享(1)

当发现某一条 SQL 存在性能问题时,我们可以通过很多方式对这条 SQL 进行优化,其中最常见的是索引调优。本文介绍 OceanBase 中索引的一些基础知识,以及创建合适的索引的方法。

OceanBase分布式数据库的博客 1999

Data-Structures-and-Algorithm-Analysis-in-Java.pdf

作者: Weiss, Mark A. 出版社: Addison Wesley Data Structures and Algorithm Analysis in Java is an “advanced algorithms” book that fits between traditional CS2 and Algorithms Analysis courses. In the old ACM Curriculum Guidelines, this course was known as CS7. This text is for readers who want to learn good programming and algorithm analysis skills simultaneously so that they can develop such programs with the maximum amo...

ET2012新版免狗免安装绿色软件

ET2012新版免狗免安祖昂绿色软件。,新界面,新视觉,

MySQL索引和SQL调优手册

MySQL索引 MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等。为了避免混乱,本文将只关注于BTree索引,因为这是平常使用MySQL时主要打交道的索引。 MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。提取句子主干,就可以得到索引的本质:索引是数据结构。 ...

Java笔记虾 4106

霍尼韦尔SoftMaster使用说明

霍尼韦尔SoftMaster使用说明

MySQL SQL调优、索引设计

SQL调优和索引优化设计是提升数据库性能的关键步骤

KD357wtt的博客 885

SQL索引及调优

作为一个两年的java开发者,从A公司跳槽到B公司,给大家总结一下我遇到的面试题目,并且从今天开始要总结自己掌握的东西分享给大家。 数据库 一.SQL索引及调优 索引 1.创建索引 Create UNIQUE INDEX index_name ON TableName(col_name) 2.索引类型 Single column 单行索引 Concatenated 多行索引 Unique 唯...

qq_42208745的博客 217

MySQL索引调优和SQL调优,b站自整理笔记

在关系数据库中,索引是一种数据结构,他将数据提前按照一定的规则进行排序和组织,能够帮助快速定位到数据记录的数据减少数据扫描返回更少数据减少交互次数减少服务器CPU及内存开销。

qdm778890的博客 1642

SQL调优实战手册:索引、并行、参数调优一站式解决方案

摘要:KingbaseES数据库性能调优指南,从索引优化、执行计划分析到参数调整,提供系统性解决方案。索引是基础,需根据场景选择Btree、Hash等类型,并掌握表达式索引、联合索引等高级用法。进阶优化需看懂执行计划,必要时使用HINT干预。高级优化包括参数调优和并行查询,充分利用硬件资源。实战技巧如SQL改写、物化视图可显著提升性能。最后强调调优需分层递进,改一项验证一项,配合监控工具实现长期稳定。该指南适用于开发和DBA,能有效解决企业级业务中的慢SQL问题。

稻草人 1万+

慢SQL调优-索引详解面试题

最新的 Java 面试题,技术栈涉及 Java 基础、集合、多线程、Mysql、分布式、Spring全家桶、MyBatis、Dubbo、缓存、消息队列、Linux…等等,会持续更新。

qq_42872034的博客 1686

MySQL的SQL调优中的索引调优

(一)先说下调优的步骤吧 1、使用工具去发现慢SQL,工具有SkyWalking、VisualVM、JavaMelody、Alibaba Druid 等等。 2、分析慢SQL、常用SQL前加explain 3、使用索引调优,看最总执行SQL时间,如果能控制到100-200ms(参考值)是不错的SQL了,当然这个得结合系统实际使用来看。 (二)MySQL存储使用的数据结构 1)、索引有 B-Tree索引、hash索引、全文索引、空间索引 1、二叉树->平衡二叉树->B-Tree->B+Tre

A_234_789的博客 396

sql索引调优_使用内置索引利用率指标SQL Server索引性能调优

sql索引调优 描述 (Description) Indexing is key to efficient query execution. Knowing what indexes are unneeded, incorrectly used, or unused can allow us to reduce disk usage and improve write performanc...

culuo4781的博客 637

SQL Server调优系列进阶篇(如何索引调优)

前言 上一篇我们分析了数据库中的统计信息的作用,我们已经了解了数据库如何通过统计信息来掌控数据库中各个表的内容分布。不清楚的童鞋可以点击参考。 作为调优系列的文章,数据库的索引肯定是不能少的了,所以本篇我们就开始分析这块内容,关于索引的基础知识就不打算深入分析了,网上一搜一片片的,本篇更侧重的是一些实战项内容展示,希望通过本篇文章各位看官能在真正的场景中找到合适的解决方法足以。

dingjixian 1038

TiDB SQL调优实战——索引问题

作者:漆锐 原文来源: https://tidb.net/blog/29aa8e6b ...

TiDB 社区干货传送门 359
上一篇: Moveit笔记
下一篇: Redis数据结构应用场景及持久化原理
split_second
博客等级 码龄12年 0粉丝 20原创
评论 1
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值