SQL使用复合索引实现数据库查询的优化

目录
  • 一 问题
  • 二 分析
  • 三 解决方案

一 问题

程序再在一次查询时出现查询时间过长，每次查询要1-2分钟业务反馈用户操作体验很差，sql如下:

select *
FROM edi_booking edibooking0_
WHERE 1 = 1
        AND edibooking0_.load_port_code IN ('CNCWN', 'CNDCB', 'AA', 'CNMWN'
        , 'CWHSD', 'CNSHK', 'CNYTN', 'CNSKU')
        AND edibooking0_.carrier_code = 'WHL'
        AND upper(edibooking0_.so_no) LIKE upper('025%')
        AND edibooking0_.load_port_code = 'CNSHK'
        AND edibooking0_.status <= 1
        AND edibooking0_.tfc_code = 'E19957'
ORDER BY edibooking0_.so_no ASC;

需要对查询进行优化。

二 分析

还是老样子，先看看执行计划，看看走没走索引，不查不知道一查吓一跳，执行计划上居然显示走了索引，索引是函数索引upper(so_no) ，走了索引为什么还慢呢，根据以往的经验，走了索引不是应该很快才对吗？奇奇怪怪，于是注意到了查询条件中还有一个条件tfc_code，又发现这个字段其实也有建立索引，是不是数据库的执行计划有问题，没有走tfc_code索引呢？或者说tfc_code索引本身有问题，于是进行重建tfc_code索引。

alter index EDI_BOOKING_IDX_TFC_CODE rebuild online;

执行后，哇塞，查询速度果然上来了，2s钟返回查询结果。查看执行计划，走了tfc_code索引，nice！ 但是故事还没有结束！ 过了一天后，业务又反馈查询慢了，查看执行计划，走的索引又变成了upper(so_no)。让人头秃。

那还个方向思考，既然走了索引，为什么还会慢！！！ 原来走了索引并不一定就会快，这是一个大大的误区。

upper(edibooking0_.so_no) LIKE upper('025%')这个过滤条件走了索引，但是索引的类型是range_scan,这种类型查询返回的数据量会比较大，这就是这次走索引还慢的问题所在，因为走了索引之后返回了150w条数据，而150w条数据被后去的条件过滤，这样导致了查询速度慢的问题。

而引发这个问题，一个是upper(so_no)索引返回数据量大，另外一个就是oracle的执行计划没有选择最优的索引，如果选择tfc_code索引，那么查询也会很快。

三 解决方案

指定数据库选择索引：

由于执行计划是数据库自动生成的，我们无法改变执行计划，但是我们可以通过指定索引的方式，让数据库去执行我们指定的索引，如：

select /*+index(edibooking0_ IDX_EDI_BOOKING_SO_TFC_CT)*/*
FROM edi_booking edibooking0_

但是这种有一个弊端，要对每一个执行的语句都要进行指定索引，修改量比较大。

建立复合索引：

CREATE INDEX IDX_EDI_BOOKING_SO_TFC_CT
ON edi_booking (UPPER("SO_NO"), "TFC_CODE","CONTRACT_NO");

复合索引很容易给人一种鸡肋的感觉，因为他对应的查询条件一定是他最左边的索引字段被查询才能生效，但是其实他是非常有用的，比如我们现在的场景，进行复核索引过滤时就会产生非常大的性能提升,最终通过建立组合索引解决问题

到此这篇关于SQL使用复合索引实现数据库查询的优化的文章就介绍到这了,更多相关SQL查询优化内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

赞 (0)

相关推荐

  • 一个 20 秒 SQL 慢查询优化处理方案

    目录 1.背景 2.复杂SQL语句的构成 3.关联查询 4.子查询 5.耗时在哪? 6.问题定位 7.初步断定 9.再进一步验证 10.解决方案 11.另外一个需要注意的点 1.背景 页面无法正确获取数据,经排查原来是接口调用超时,而最后发现是因为SQL查询长达到20多秒而导致了问题的发生.这里,没有高深的理论或技术,只是备忘一下经历和解读一些思想误区. 2.复杂SQL语句的构成 这里不过多对业务功能进行描述,但为了突出问题所在,会用类比的语句来描述当时的场景 复杂的SQL语句可以表达如下: S

  • sql server查询语句阻塞优化性能

    在生产环境下,有时公司客服反映网页半天打不到,除了在浏览器按F12的Network响应来排查,确定web服务器无故障后.就需要检查数据库是否有出现阻塞 当时数据库的生产环境中主表数据量超过2000w,子表数据量超过1亿,且更新和新增频繁.再加上做了同步镜像,很消耗资源. 这时就要新建一个会话,大概需要了解以下几点: 1.当前活动会话量有多少? 2.会话运行时间? 3.会话之间有没有阻塞? 4.阻塞时间 ? 查询阻塞的方法有很多.有sql 2000 的sp_lock, 有sql 2005及以上的d

  • Mysql大数据量查询优化思路详析

    Mysql大数据量查询优化思路详析

    目录 1. 千万级别日志查询的优化 2. 几百万黑名单库的查询优化 3. Mybatis批量插入处理问题 项目场景: Mysql大表查询优化,理论上千万级别以下的数据量Mysql单表查询性能处理都是可以的. 问题描述: 在我们线上环境中,出现了mysql几千万级别的日志查询.几百万级别的黑名单库查询分页查询及条件查询都慢的问题,针对Mysql表优化做了一些优化处理. 原因分析:首先说一下日志查询,在Mysql中如果索引加的比较合适,走索引情况下千万级别查询不会超过一秒,Mysql查询的速度和检索

  • MySQL定位并优化慢查询sql的详细实例

    MySQL定位并优化慢查询sql的详细实例

    目录 1.如何定位并优化慢查询sql a.根据慢日志定位慢查询sql b.使用explain等工具分析sql c.修改sql或者尽量让sql走索引 2.联合索引的最左匹配原则的成因 简单说下什么是最左匹配原则 最左匹配原则的原理 3.索引是建立得越多越好吗 总结 1.如何定位并优化慢查询sql a.根据慢日志定位慢查询sql SHOW VARIABLES LIKE '%query%' 查询慢日志相关信息 slow_query_log 默认是off关闭的,使用时,需要改为on 打开 slow_qu

  • MySQL慢查询优化解决问题

    目录 1. MySQL慢查询介绍 2.发现问题(主动/被动) 3.找到原因-对症下药 1.  MySQL慢查询介绍 MySQL的慢查询日志是MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阀值的语句,具体指运行时间超过long_query_time值的SQL,则会被记录到慢查询日志中.long_query_time的默认值为10,意思是运行10S以上的语句.默认情况下,Mysql数据库并不启动慢查询日志,需要我们手动来设置这个参数,当然,如果不是调优需要的话,一般不建议启动该参

  • Mysql查询优化的一些实用方法总结

    目录 1. count的优化 2. 避免使用不兼容的数据类型. 3. 索引字段上进行运算会使索引失效. 4. 避免使用!=或<>.IS NULL或IS NOT NULL.IN ,NOT IN等这样的操作符. 5. 尽量使用数字型字段. 6. 合理使用EXISTS,NOT EXISTS子句.如下所示: 7. 能够用BETWEEN的就不要用IN 8. 能够用DISTINCT的就不用GROUP BY 9. 尽量不要用SELECT INTO语句. 10. 必要时强制查询优化器使用某个索引 11. 消除

  • MySQL的索引原理以及查询优化详解

    MySQL的索引原理以及查询优化详解

    目录 一.介绍 1.什么是索引? 2.为什么要有索引呢? 二.索引的原理 一 索引原理 二 磁盘IO与预读 三.索引的数据结构 四.Mysql索引管理 一.功能 二.MySQL的索引分类 三. 索引的两大类型hash与btree 四.创建/删除索引的语法 五.测试索引 1.准备 2 .在没有索引的前提下测试查询速度 3. 加上索引 六.正确使用索引 一.覆盖索引 二.联合索引 三.索引合并 七.慢查询优化的基本步骤 总结 一.介绍 1.什么是索引? 一般的应用系统,读写比例在10:1左右,而且插

  • SQL使用复合索引实现数据库查询的优化

    目录 一 问题 二 分析 三 解决方案 一 问题 程序再在一次查询时出现查询时间过长,每次查询要1-2分钟业务反馈用户操作体验很差,sql如下: select * FROM edi_booking edibooking0_ WHERE 1 = 1 AND edibooking0_.load_port_code IN ('CNCWN', 'CNDCB', 'AA', 'CNMWN' , 'CWHSD', 'CNSHK', 'CNYTN', 'CNSKU') AND edibooking0_.car

  • MySQL数据库查询性能优化的4个技巧干货

    MySQL数据库查询性能优化的4个技巧干货

    目录 前言 SQL的执行频率 慢查询日志 show profiles详情分析 explain执行计划 1.ID参数 2.select_type参数 3.type参数 前言 MySQL性能优化是一个老生常谈的问题,无论是在实际工作中还是面试中,都不可避免遇到相应的场景,下面博主就总结一些能够帮助大家解决这个问题的小技巧. SQL优化之前需要确认哪些SQL需要优化,这时就需要引起SQL性能分析工具,主要优化的是查询语句. SQL的执行频率 SQL性能优化一般是针对查询语句,所以在定位是否需要优化之前

  • MySQL数据库查询性能优化策略

    优化查询 使用Explain语句分析查询语句 Explain 用来分析 SELECT 查询语句,开发人员可以通过分析 Explain 结果来优化查询语句. 通过对查询语句的分析,可以了解查询语句的执行情况,找出查询语句执行的瓶颈,从而优化查询语句. 使用索引查询 MySql中提高性能的一个最有效的方式就是对数据表设计合理的索引. 索引提供了高效访问数据的方法,并且加快查询速度. 如果查询时没有使用索引,那么查询语句将扫描表中所有的记录.在数据量大的时候,这样查询速度会很慢. 使用索引进行查询,查

  • 一次数据库查询超时优化问题的实战记录

    一次数据库查询超时优化问题的实战记录

    目录 问题发现 查找原因 解决问题 额外话:Transaction Timeout.Statement Timeout.Socket timeout 的区别 它们三者的关系是在怎样的呢 总结 参数文章 问题发现 在七月份时,经常发现有几个定时任务报错,查看了下异常原因,大概定位是数据库执行异常 ### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionExcepti

  • CI框架数据库查询缓存优化的方法

    本文实例讲述了CI框架数据库查询缓存优化的方法.分享给大家供大家参考,具体如下: CI框架中有个比较好的查询优化,就是数据库缓存优化 1.开启缓存 //在application/config.php中开启 $db['default']['cache_on'] = TRUE; //在application/config.php中开启 $db['default']['cachedir'] = './cache'; //并在对应的目录中加一个可写缓存目录cache 2. 在对应的查询中开启缓存语句 /

  • 用实例详解Python中的Django框架中prefetch_related()函数对数据库查询的优化

    用实例详解Python中的Django框架中prefetch_related()函数对数据库查询的优化

    实例的背景说明 假定一个个人信息系统,需要记录系统中各个人的故乡.居住地.以及到过的城市.数据库设计如下: Models.py 内容如下: from django.db import models class Province(models.Model): name = models.CharField(max_length=10) def __unicode__(self): return self.name class City(models.Model): name = models.Ch

  • MySQL查询性能优化索引下推

    MySQL查询性能优化索引下推

    目录 前言 1. 索引下推的作用 2. 案例实践 3. 索引下推配置 4. 索引下推原理剖析 5. 索引下推应用范围 前言 前面已经讲了MySQL的其他查询性能优化方式,没看过可以去了解一下: MySQL查询性能优化七种方式索引潜水 MySQL查询性能优化武器之链路追踪 今天要讲的是MySQL的另一种查询性能优化方式 — 索引下推(Index Condition Pushdown,简称ICP),是MySQL5.6版本增加的特性. 1. 索引下推的作用 主要作用有两个: 减少回表查询的次数 减少存

  • 多列复合索引的使用 绕过微软sql server的一个缺陷

    然而,微软sql server在处理这类索引时,有个重要的缺陷,那就是把本该编译成索引seek的操作编成了索引扫描,这可能导致严重性能下降 举个例子来说明问题,假设某个表T有索引 ( cityid, sentdate, userid), 现在有个分页列表功能,要获得大于某个多列复合索引V0的若干个记录的查询,用最简单表意的方式写出来就是 V >= V0, 如果分解开来,就是: cityid > @cityid0 or (cityid = @cityid0 and (sentdate >

  • Sql Server 查询性能优化之走出索引的误区分析

    Sql Server 查询性能优化之走出索引的误区分析

    据了解绝大多数开发人员对于索引的理解都是一知半解,局限于大多数日常工作没有机会.也什么没有必要去关心.了解索引,实在哪天某个查询太慢了找到查询条件建个索引就ok,哪天又有个查询慢了,再建立个索引就是,或者干脆把整个查询SQL直接发给DBA,让DBA直接帮忙优化了,所以造成的状况就是开发人员对于索引的理解.认识很局限,以下就把我个人对于索引的理解及浅薄认识和大家分享下,希望能解除一些大家的疑惑,一起走出索引的误区 误区1.在表上建立了索引,在查询时用到了索引的列,索引就一定会生效 首先明确下这样的

  • 深入SQL Server 跨数据库查询的详解

    语句SELECT * FROM 数据库A.dbo.表A a, 数据库B.dbo.表B b WHERE a.field=b.field"DBO"可以省略 如SELECT * FROM 数据库A..表A a, 数据库B..表B b WHERE a.field=b.fieldSqlServer数据库:--这句是映射一个远程数据库EXEC sp_addlinkedserver '远程数据库的IP或主机名',N'SQL Server'--这句是登录远程数据库EXEC sp_addlinkedsr

随机推荐