分区表场景下的 SQL 优化_MsSql

来源:脚本之家  责任编辑:小易  

可以。use master,执行系2113统存储过程5261 sp_cycle_errorlog,就可以删除。因为SQL Server 实例每启4102动一次,其便会重新生成一个错误日1653志文件,并将以前的日志文件的后缀改为 .1,.2 。。。依次类推。默认情况下,SQL Server 只保留 7 个错误日志文件。所以这个存储过程你要执行7次,才能完全清楚www.zgxue.com防采集请勿采集本网。

导读

有个表做了分区,每天一个分区。

优化数据库前的注意事项: 1、关键字段建立索引。 2、使用存储过程,它使SQL变得更加灵活和高效。 3、备份数据库和清除垃圾数据。 4、SQL语句语法的优化。(可以用Sybase的SQL Expert,可惜我没找到unexpired的序列号) 5、清理删除日志。 MSSQL语

该表上有个查询,经常只查询表中某一天数据,但每次都几乎要扫描整个分区的所有数据,有什么办法进行优化吗?

很大是因为数据记录很多。 数据库可以通过优化减小体积,但是减小的大小很有限。 因此,一般没必要处理,因为那些本来就是你需要的数据。除非你不要了。

待优化场景

有一个大表,每天产生的数据量约100万,所以就采用表分区方案,每天一个分区。

写程序的人,往往需要分析所写的SQL语句是否已经优化过了,服务器的响应时间有多快,这个时候就需要用到SQL的STATISTICS状态值来查看了。 通过设置STATISTICS我们可以查看执行SQL时的系统情况。选项有PROFILE,IO ,TIME。介绍如下: SET STATIS

下面是该表的DDL:

你这样 那么多等于 肯定会有很多冗余 为了提高查询速度,少用in查询 那么就要用left join on 的连接查询 在BLL里面处理逻辑

CREATE TABLE `t1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `date` date NOT NULL, `kid` int(11) DEFAULT '0', `uid` int(11) NOT NULL, `iid` int(11) DEFAULT '0', `icnt` int(8) DEFAULT '0', `tst` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `countp` smallint(11) DEFAULT '1', `isr` int(2) NOT NULL DEFAULT '0', `clv` int(5) NOT NULL DEFAULT '1', PRIMARY KEY (`id`,`date`), UNIQUE KEY `date` (`date`,`uid`,`iid`), KEY `date_2` (`date`,`kid`)) ENGINE=InnoDB AUTO_INCREMENT=3180686682 DEFAULT CHARSET=utf8mb4/*!50500 PARTITION BY RANGE COLUMNS(`date`)(PARTITION p20161201 VALUES LESS THAN ('2016-12-02') ENGINE = InnoDB, PARTITION p20161202 VALUES LESS THAN ('2016-12-03') ENGINE = InnoDB, PARTITION p20161203 VALUES LESS THAN ('2016-12-04') ENGINE = InnoDB,

很简单,最终查询的结果是新闻,所以新闻表是你的主要查询表 企业、品牌、媒体这三个都是条件 我认为,你的新闻表中应该有两种形式, 一是同时包括三种条件,包括企业、品牌、媒体代码、新闻标题、内容等关键字段 查询的时候sql语句非常简单,就

该表上经常发生下面的慢查询:

SELECT ... FROM `t1` WHERE `date` = '2017-04-01' AND `icnt` > 300 AND `id` = '801301';

SQL优化之路

SQL优化思路

想要优化一个SQL,一般来说就是先看执行计划,观察是否尽可能用到索引,同时要关注预计扫描的行数,以及是否产生了临时表(Using temporary) 或者 是否需要进行排序(Using filesort),想办法消除这些情况。

更进一步的优化策略则可能需要调整程序代码逻辑,甚至技术架构或者业务需求,这个动作比较大,一般非核心系统上的核心问题,不会这么大动干戈,绝大多数情况,还是需要靠DBA尽可能发挥聪明才智来解决。

SQL性能瓶颈定位

现在,我们来看下这个SQL的执行计划:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p20170302 type: rangepossible_keys: date,date_2 key: date key_len: 3 ref: const rows: 9384602 Extra: Using where

这个执行计划看起来还好,有索引可用,也没临时表,也没filesort。不过,我们也注意到,预计要扫描的行数还是挺多的 rows: 9384602,而且要扫描zheng整个分区的所有数据,难怪效率不高,总是SLOW QUERY。

优化思考

我们注意到这个SQL总是要查询某一天的数据,这个表已经做了按天分区,那是不是可以忽略 WHERE 子句中的 时间条件呢?

还有,既然去掉了 date 条件,反观表DDL,剩下的条件貌似就没有合适的索引了吧?

所以,我们尝试新建一个索引:

yejr@imysql.com[myDB]> ALTER TABLE t1 ADD INDEX iid (iid, icnt);

然后,把SQL改造成下面这样,再看下执行计划:

yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` partition(p2017030) WHERE `icnt` > 100 AND `iid` = '502302'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p20170302 type: refpossible_keys: date,date_2,iid key: iid key_len: 10 ref: const rows: 7800 Extra: Using where这优化效果,杠杠滴。事实上,如果不强制指定分区的话,也是可以达到优化效果的:yejr@imysql.com[myDB]> EXPLAIN PARTITIONS SELECT ... FROM `t1` WHERE `date` = '2017-03-02' AND `icnt` > 100 AND `iid` = '502302'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: t1 partitions: p20170302 type: refpossible_keys: date,date_2,iid key: iid key_len: 10 ref: NULL rows: 7800 Extra: Using where

后记

绝大多数的SQL通过添加索引、适当调整SQL代码(例如调整驱动表顺序)等简单手法来完成。

多说几句,遇到SQL优化性能瓶颈问题想要在技术群里请教时,麻烦先提供几个必要的信息: 表DDL 表常规统计信息,可执行 SHOW TABLE STATUS LIKE ‘t1' 查看 表索引分布信息,可执行 SHOW INDEX FROM t1 查看 有问题的SQL及相应的执行计划 没有这些信息的话,就别去麻烦别人了吧。

以上就是分区表场景下的 SQL 优化的详细内容,更多关于sql分区表优化的资料请关注真格学网其它相关文章!

你若过有磁盘阵列的话性能会高一些如果只有一块硬盘,即便是你分了区, 各个区还是在一个磁盘上, 他的读写速度并不会提高内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • mysql优化之分区表
  • 详解mysql分区表
  • mysql最佳实践之分区表基本类型
  • mysql分区表的正确使用方法
  • mysql分区表的局限和限制详解
  • postgresql分区表(partitioning)应用实例详解
  • mysql分区表的管理与维护
  • postgresql教程(三):表的继承和分区表详解
  • rails中使用mysql分区表一个提升性能的方法
  • mysql使用教程之分区表的使用方法(删除分区表)
  • 数据库left join , right join, inner join 的相关内容,非常实用
  • sqlserver 2000 远程连接 服务器的解决方案
  • sql server错误代码大全及解释(留着备用)
  • mssql @@error 使用
  • sql server误区30日谈 第19天 truncate表的操作不会被记录到日志
  • sql函数实现去除字符串中的相同的字符串
  • sql表连接查询使用方法(sql多表连接查询)
  • sql附加数据库失败问题的解决方法
  • sql server 开窗函数over()的使用实例详解
  • sql server学习笔记之事务、锁定、阻塞、死锁用法详解
  • MSSQL分区表性能并不比普通表强吗
  • Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSS...
  • 对MSSQL已存在的表 分区
  • 数据库MSSQL 2005 优化 索引 分区(回答有帮助追加...
  • MSSQL数据库如何优化?
  • MSSQL\Data 下的文件为什么会很大?该如何处理?
  • MSSQL如何查看sql语句执行时间判断执行效率
  • mssql语句优化,语句中的左连接如何优化
  • mssql sql语句多条件查询优化问题。
  • mssql数据库太大怎么压缩?
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql优化之分区表详解mysql分区表mysql最佳实践之分区表基本类型mysql分区表的正确使用方法mysql分区表的局限和限制详解postgresql分区表(partitioning)应用实例详解mysql分区表的管理与维护postgresql教程(三):表的继承和分区表详解rails中使用mysql分区表一个提升性能的方法mysql使用教程之分区表的使用方法(删除分区表)数据库left join , right join, inner join 的相关内容,非常实用sqlserver 2000 远程连接 服务器的解决方案sql server错误代码大全及解释(留着备用)mssql @@error 使用sql server误区30日谈 第19天 truncate表的操作不会被记录到日志sql函数实现去除字符串中的相同的字符串sql表连接查询使用方法(sql多表连接查询)sql附加数据库失败问题的解决方法sql server 开窗函数over()的使用实例详解sql server学习笔记之事务、锁定、阻塞、死锁用法详解sql server 2012 安装图解教程(附sql server数据库入门学习总结microsoft sql server 2012 数据sql重复记录查询的几种方法win7系统安装sqlserver2000的详细sqlserver中distinct的用法(不重sql server错误代码大全及解释(sql-order by 多字段排序(升序、sql将一个表中的数据插入到另一个用sql语句添加删除修改字段、一些sql server中检查字段的值是否为数字的方sql server 分页查询存储过程代码sql-order by 多字段排序(升序、降序)基于sql server通用分页存储过程的解决方sql server数据库的高性能优化经验总结sqlserver中union,cube,rollup,cumpute运sql 聚集索引和非聚集索引(详细整理)sqlserver清空service broker中的队列的语理解sql server中的聚集索引获取sql server2014的安装时间
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved