文档章节

如何应付表数据过大的查询问题?(如何尽量避免大表关联)

 蜗牛奔跑
发布于 2017/02/28 16:26
字数 1817
阅读 31
收藏 0
点赞 0
评论 0

  一般来说,对于做B/S架构的朋友来说,更有机会遇到高并发的数据库访问情况,因为现在WEB的普及速度就像火箭升空,同时就会因为高访问量带来一系列性能问题,而数据库一直是用户与商人之间交流的重要平台.用户是没有耐心忍受一个查询需要用上10秒以上的,或者更少些,如果经常出现服务器死机或者是报查询超时,我想那将是失败的项目。做了几年的WEB工作,不才,一直没有遇到过大访问量或者是海量数据的情况.这里并不是说没有海量数据的项目就不是好项目,要看项目的应用场合.

  最近做项目时,偶然得到了这个机会,在我工作过程中,本人发现的单表最大记录数高达9位数.像订单表什么的也有8位数.在查询订单的时候往往不能通过单表查询就能解决,还要和其它相关表进行关联查询.如此关联的表数据不大还好,一旦发生大表关联大表,在查询时就有可能出现慢长的等待。

  主旨: 如何避免这种情况的发生呢?既然有了这样的数据,需求还是要实现,这里就我最近针对数据库的优化过程,我分两篇文章来说明下.

  第一篇:如何尽量避免大表关联.

  第二篇:对大表进行分区.

  背景:有两张表:

  1:订单表:记录用户订单的详细信息.order,其中有一个会员卡号字段cardNo,订单产生时间.

  2:会员表:记录会员相关信mber,一个会员有一个代理号:proxyID,代理下面有许多的会员卡:cardNo,它们共用一个代理号.

  两表通过cardNo来相关联.

  需求:查询一个用户或者某些用户某一时间段所有会员卡产生的订单情况.

  实现SQL:

  select 字段 from order          inner join member on        order.cardNo=member.cardNo         and member.proxyID in('a-01',代理号二)             and 时间 between '20080101' and '20080131'

  本人见解:我想一般的朋友看到这样的需求大多会写出这样的查询SQL,如果不喜欢用in或者认为in的性能不好的朋友可用union all 代替.SQL语句可以说简单的不能再简单了,本身并无问题,只是如果两表的数据都在百万以上,而且字段都特别多.此时如果只有索引的帮忙下并不一定能达到预期的效果.

  解决方案一:利用表变量来替换大表关联,表变量的作用域为一个批处理,批处理完了,表变量也会随之失效,比起临时表有它独特的优点:不用手动去删除表变量以释放内存。

  可行性:因为需求中的输出字段大多来自订单表,member表只起到数据约束的作用,和查询用户会员卡号的作用,所有可以先把代理的会员卡号先取到表变量中,然后利用带有卡号的表变量和订单表相关联查询.

  declare @t table   (cardNo int)   insert @t    select cardNo from member where in('a-01',代理号二)   select 字段 from order       inner join @t on        order.cardNo=@t.cardNo and 时间 between '20080101' and '20080131'

  这里我就不贴性能比较图了,有兴趣的朋友可以自己尝试下.这种方法在查询人员比较多的时候特别有帮助.它要开发员根据实际情况详细比较,结果并不是统一的,不同的环境结果可能不一样.希望大家理解.

  解决方案二:利用索引视图来提高大表关联的性能.

  可行性:一般在大表关联时,我们的输出列都远小于两表的字段合,像上面的member表只用到了其中的两个字段(cardNo,proxyID).设想一下,此时的member表如果只有这两个字段情况会不会好些呢?答案不言而喻.

  视图这个名词在我以前对它的印象中,从来没有认为视图能优化查询,因为我认为视图对于数据库来说就是一个虚假表,在数据库中并无实际物理位置来存储数据.对于用户来说无非就是通过不同的视角来观看结果.视图数据

  的产生都是实时的,即当调用视图时,自动扩展视图,去运行里面相应的select语句.后来才知道在2000后的版本中视图分一般视图和索引视图,一般视图就是没有创建索引的我印象中的视图.而创建了视图后就称为索引视图.索引视图是物理存在的,可在视图上首先创建一个唯一的聚集索引,其它字段上也可创建非聚集索引.在不改变基础表的情况下,起到了优化的效果.

  CREATE VIEW memberView WITH SCHEMABINDING AS   SELECT cardNo,proxyID from member GO --以会员卡号创建一个唯一聚集索引 CREATE UNIQUE CLUSTERED INDEX ix_member_cardNo   ON member (cardNo);    GO

  注意:创建索引视图要点:

  1: CREATE VIEW memberView后面要跟上WITH SCHEMABINDING

  理由:? 使用 schemaname.objectname 明确识别视图所引用的所有对象,而不管是哪个用户访问该视图.

  ? 不会以导致视图定义非法或强制 SQL Server 在该视图上重新创建索引的方式,更改视图定义中所引用的对象.

  2:视图上的第一个索引必须为 CLUSTERED 和 UNIQUE.

  理由:必须为 UNIQUE 以便在维护索引视图期间,轻松地按键值查找视图中的记录,并阻止创建带有重复项目的视图(要求维护特殊的逻辑).必须为 CLUSTERED,因为只有聚集索引才能在强制唯一性的同时存储行.

  3:以下情况可考虑创建索引视图:

  ? 可预先计算聚合并将其保存在索引中,从而在查询执行时,最小化高成本的计算.

  ? 可预先联接各个表并保存最终获得的数据集.

  ? 可保存联接或聚合的组合.

  4:基础表的更新会引发索引视力的更新.

  5:索引视图的创建同时会带来维护上的开销.

  理由:1:因为索引视图是物理存在的.

  2:要额外的维护索引.

  实现:SQL:select 字段 from order

  inner join memberView on

  order.cardNo=member.cardNo

  and member.proxyID=in('a-01',代理号二)

  and 时间 between '20080101' and '20080131'

  总结:两种解决方案来看,各有所长,一般可以优先考虑使用索引视图来优化大表关联.以上是本人对于如何尽量避免发生大表关联所采取的措施,望大家指教.

© 著作权归作者所有

共有 人打赏支持
粉丝 34
博文 594
码字总数 112254
作品 0
海淀
高性能可扩展MySQL数据库设计及架构优化 电商项目_1

一 数据库设计规范 1 命名规范 所有数据库对象名称必须用小写字母并下划线分割 对象名称禁止使用MySQL保留关键字 命名要做到见名识义,不超过32字节 临时表必须tmp前缀 日期后缀 备份库表bak...

yanfeilai528 ⋅ 2017/08/12 ⋅ 0

Mysql数据库开发之mycat分表分库如何做?

Mycat分表分库虽然能解决大表对数据库系统的压力,但也有一些不利,因此Mycat分表分库要先解决的问题是,分不分库,分哪些库,什么规则分,分多少分片。那么究竟是怎么分的呢? 1、能不分就不...

老男孩Linux培训 ⋅ 05/31 ⋅ 0

oracle存储过程 调优 基础篇

1、如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databsevv.dbo.tablename”,因为spdepends不能显示出该SP所使用的跨库table或view,不方便校验...

娲城小将 ⋅ 2014/11/11 ⋅ 0

项目中常用的19条MySQL优化

作者:zhangqh 一、EXPLAIN 做MySQL优化,我们要善用 EXPLAIN 查看SQL执行计划。 下面来个简单的示例,标注(1,2,3,4,5)我们要重点关注的数据 type列,连接类型。一个好的sql语句至少要达到r...

小数点 ⋅ 2017/11/27 ⋅ 0

Oracle存储过程的编写经验与优化措施

1、开发人员如果用到其他库的Table或View,务必在当前库中建立View来实现跨库操作,最好不要直接使用“databsevv.dbo.tablename”,因为spdepends不能显示出该SP所使用的跨库table或view,不...

有功夫 ⋅ 2016/11/13 ⋅ 0

mysql优化和索引

表的优化 1.定长与变长分离 如 int,char(4),time核心且常用字段,建成定长,放在一张表; 而varchar,text,blob这种变长字段适合单放一张表,用主键与核心表关联。 2.常用字段和不常用字段要分...

houss2012 ⋅ 2017/11/08 ⋅ 0

PHPer面试指南-MySQL 篇

本书的 GitHub 地址:https://github.com/todayqq/PHPerInterviewGuide 什么是索引,作用是什么?常见索引类型有那些?Mysql 建立索引的原则? 索引是一种特殊的文件,它们包含着对数据表里所...

angkee ⋅ 01/24 ⋅ 0

Web应用的缓存设计模式

ORM缓存引言 从10年前的2003年开始,在Web应用领域,ORM(对象-关系映射)框架就开始逐渐普及,并且流行开来,其中最广为人知的就是Java的开源ORM框架Hibernate,后来Hibernate也成为了EJB3的实...

追风蚂蚁 ⋅ 2014/05/08 ⋅ 0

MySQL大表优化方案

当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化: 单表优化 除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复...

umgsai ⋅ 2016/12/30 ⋅ 0

一次数据库问题优化

最近连续几天,mysql数据库会运行中假死,记录一下排查过程。 1、在数据库相应缓慢的时候,用client连接上去,执行show processlist 命令, 查看是哪条语句执行缓慢,或者哪些语句执行缓慢。...

applepaihs ⋅ 01/19 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

iExec Blockchain Marketplace for Cloud

iExec Releases the First-Ever Blockchain Marketplace for Trading Cloud Computing Berlin, Germany, May 29, 2018. iExec has released its blockchain-based decentralized cloud marke......

openthings ⋅ 9分钟前 ⋅ 0

OSChina 周二乱弹 —— 加班的代码不要枉费了我的童子功

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @小小编辑:推荐歌曲《29》- 未完成乐队 《29》- 未完成乐队 手机党少年们想听歌,请使劲儿戳(这里) @FalconChen :#看球提醒# 02:00 巴西v...

小小编辑 ⋅ 28分钟前 ⋅ 11

Docker Swarm的前世今生

概述 在我的《Docker Swarm集群初探》一文中,我们实际体验了Docker Swarm容器集群技术的魅力,与《Kubernetes实践录》一文中提到的Kubernetes集群技术相比,Docker Swarm没有Kubernetes显得...

CodeSheep ⋅ 今天 ⋅ 0

骰子游戏代码开源地址

因为阿里云现在服务器已经停用了,所以上面的配置已经失效。 服务端开源地址:https://gitee.com/goalya/chat4.git 客户端开源地址:https://gitee.com/goalya/client4.git 具体运行界面请参考...

算法之名 ⋅ 今天 ⋅ 0

设计模式--装饰者模式

装饰者模式 定义 动态地给一个对象添加一些额外的职责。就增加功能来说,装饰模式相比生成子类更为灵活。 通用类图 意图 动态地给一个对象添加一些额外的职责。就增加功能来说,装饰模式相比...

gaob2001 ⋅ 今天 ⋅ 0

JavaScript零基础入门——(八)JavaScript的数组

JavaScript零基础入门——(八)JavaScript的数组 欢迎大家回到我们的JavaScript零基础入门,上一节课我们讲了有关JavaScript正则表达式的相关知识点,便于大家更好的对字符串进行处理。这一...

JandenMa ⋅ 今天 ⋅ 0

sbt网络问题解决方案

转自:http://dblab.xmu.edu.cn/blog/maven-network-problem/ cd ~/.sbt/launchers/0.13.9unzip -q ./sbt-launch.jar 修改 vi sbt/sbt.boot.properties 增加一个oschina库地址: [reposit......

狐狸老侠 ⋅ 今天 ⋅ 0

大数据,必须掌握的10项顶级安全技术

我们看到越来越多的数据泄漏事故、勒索软件和其他类型的网络攻击,这使得安全成为一个热门话题。 去年,企业IT面临的威胁仍然处于非常高的水平,每天都会看到媒体报道大量数据泄漏事故和攻击...

p柯西 ⋅ 今天 ⋅ 0

Linux下安装配置Hadoop2.7.6

前提 安装jdk 下载 wget http://mirrors.hust.edu.cn/apache/hadoop/common/hadoop-2.7.6/hadoop-2.7.6.tar.gz 解压 配置 vim /etc/profile # 配置java环境变量 export JAVA_HOME=/opt/jdk1......

晨猫 ⋅ 今天 ⋅ 0

crontab工具介绍

crontab crontab 是一个用于设置周期性被执行的任务工具。 周期性执行的任务列表称为Cron Table crontab(选项)(参数) -e:编辑该用户的计时器设置; -l:列出该用户的计时器设置; -r:删除该...

Linux学习笔记 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部