新闻资讯  快讯  焦点  财经  政策  社会
互 联 网   电商  金融  数据  计算  技巧
生活百科  科技  职场  健康  法律  汽车
手机百科  知识  软件  修理  测评  微信
软件技术  应用  系统  图像  视频  经验
硬件技术  知识  技术  测评  选购  维修
网络技术  硬件  软件  设置  安全  技术
程序开发  语言  移动  数据  开源  百科
安全防护  资讯  黑客  木马  病毒  移动
站长技术  搜索  SEO  推广  媒体  移动
财经百科  股票  知识  理财  财务  金融
教育考试  育儿  小学  高考  考研  留学
您当前的位置:首页 > IT百科 > 数据库 > 百科

在一个千万级的数据库查寻中,如何提高查询效率?

时间:2019-06-11 10:12:16  来源:  作者:

在一个千万级的数据库查寻中,如何提高查询效率?

1、数据库设计方面:

A. 对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

B. 应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num is null

可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:

select id from t where num=0

C. 并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

D. 索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有必要。

E. 应尽可能的避免更新索引数据列,因为索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新索引数据列,那么需要考虑是否应将该索引建为索引。

F. 尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

G. 尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

H. 尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

I. 避免频繁创建和删除临时表,以减少系统表资源的消耗。

J. 临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

K. 在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

L. 如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

2、SQL语句方面:

A. 应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

B. 应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

select id from t where num=10 or num=20

可以这样查询:

select id from t where num=10 union all select id from t where num=20

C. in 和 not in 也要慎用,否则会导致全表扫描,如:

select id from t where num in(1,2,3)

对于连续的数值,能用 between 就不要用 in 了:

select id from t where num between 1 and 3

D. 下面的查询也将导致全表扫描:

select id from t where name like ‘%abc%’

E. 如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:

select id from t where num=@num

可以改为强制查询使用索引:

select id from t with(index(索引名)) where num=@num

F. 应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where num/2=100

应改为:

select id from t where num=100*2

G. 应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:

select id from t where substring(name,1,3)=’abc’
–name以abc开头的id 
select id from t where datediff(day,createdate,’2005-11-30′)=0–‘2005-11-30’
生成的id 应改为: 
select id from t where name like ‘abc%’ select id from t where
createdate>=’2005-11-30′ and createdate<’2005-12-1′

H. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

I. 不要写一些没有意义的查询,如需要生成一个空表结构:

select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样: create table #t(…)

J. 很多时候用 exists 代替 in 是一个好的选择:

select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

K. 任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

L. 尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

M. 尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

N. 尽量避免大事务操作,提高系统并发能力。

O、当只要一行数据时使用LIMIT 1;

当你查询表的有些时候,你已经知道结果只会有一条结果,单因为你可能需要去fetch游标,或是你也许会去检查返回的记录数。

在这种情况下,加上LIMIT 1 可以增加性能。这样一样, MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查找下一条符合记录的数据。

P、千万不要ORDER BY RAND();

Q、避免SELECT *;

R、使用 ENUM 而不是 VARCHAR ?

ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。这样一来,用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。

S、把IP地址存成 UNSIGNED INT

很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放,只需要4个字节,并且你可以有定长的字段。而且,这会为你带来查询上的优势,尤其是当你需要使用这样的WHERE条件:IP between ip1 and ip2。

我们必需要使用UNSIGNED INT,因为 IP地址会使用整个32位的无符号整形

3、JAVA方面:重点内容

A.尽可能的少造对象。

B.合理摆正系统设计的位置。大量数据操作,和少量数据操作一定是分开的。大量的数据操作,肯定不是ORM框架搞定的。,

C.使用jDBC链接数据库操作数据

D.控制好内存,让数据流起来,而不是全部读到内存再处理,而是边读取边处理;

E.合理利用内存,有的数据要缓存


 

如何优化数据库,如何提高数据库的性能?

解答:

1) 硬件调整性能 最有可能影响性能的是磁盘和网络吞吐量,解决办法扩大虚拟内存,并保证有足够可以扩充的空间;把数据库服务器上的不必要服务关闭掉;把数据库服务器和主域服务器分开;把SQL数据库服务器的吞吐量调为最大;在具有一个以上处理器的机器上运行SQL。

2)调整数据库

若对该表的查询频率比较高,则建立索引;建立索引时,想尽对该表的所有查询搜索操作, 按照where选择条件建立索引,尽量为整型键建立为有且只有一个簇集索引,数据在物理上按顺序在数据页上,缩短查找范围,为在查询经常使用的全部列建立非簇集索引,能最大地覆盖查询;但是索引不可太多,执行UPDATE DELETE INSERT语句需要用于维护这些索引的开销量急剧增加;避免在索引中有太多的索引键;避免使用大型数据类型的列为索引;保证每个索引键值有少数行。

3)使用存储过程

应用程序的实现过程中,能够采用存储过程实现的对数据库的操作尽量通过存储过程来实现,因为存储过程是存放在数据库服务器上的一次性被设计、编码、测试,并被再次使用,需要执行该任务的应用可以简单地执行存储过程,并且只返回结果集或者数值,这样不仅可以使程序模块化,同时提高响应速度,减少网络流量,并且通过输入参数接受输入,使得在应用中完成逻辑的一致性实现。

4)应用程序结构和算法

建立查询条件索引仅仅是提高速度的前提条件,响应速度的提高还依赖于对索引的使用。因为人们在

使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,特别是对数据量不是特别大的数据库操作时,是否建立索引和使用索引的好坏对程序的响应速度并不大,因此程序员在书写程序时就忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在数据量特别大时或者大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。在对它们进行适当的优化后,其运行速度有了明显地提高!

<End>
 



Tags:数据库   点击:()  评论:()
声明:本站部分内容来自互联网,内容观点仅代表作者本人,如有任何版权侵犯请与我们联系,我们将立即删除。
▌相关评论
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表
▌相关推荐
1、Windows下安装MySQL数据库  具体参考:https://blog.csdn.net/dongDONG2014_/article/details/897169052、 MySQL目录结构MySQL的数据存储目录为data,data目录通常在C:\D...【详细内容】
2019-12-27   数据库  点击:(0)  评论:(0)  加入收藏
在软件应用开发过程中经常出现因数据库表、数据库表字段格式不规则而影响开发进度的问题。在维护应用使用原来数据库表时,也会因为数据库表的可读性不够高,表字段规则不统一,造...【详细内容】
2019-12-24   数据库  点击:(1)  评论:(0)  加入收藏
数据库作为业务的核心,在整个基础软件栈中是非常重要的一环。近几年社区也是新的方案和思想层出不穷,接下来我将总结一下近几年一些主流的开源数据库方案,其背后的设计思想以...【详细内容】
2019-11-14   数据库  点击:(2)  评论:(0)  加入收藏
各位志同道合的朋友们大家好,我是一个一直在一线互联网踩坑十余年的编码爱好者,现在将我们的各种经验以及架构实战分享出来,如果大家喜欢,就关注我,一起将技术学深学透,我会每一...【详细内容】
2019-12-24   数据库  点击:(3)  评论:(0)  加入收藏
本文以MYSQL数据库为例说明。一、数据库架构原则有以下几种:1、高可用2、高性能3、一致性4、扩展性二、常见的架构方案: 方案一:主备架构,只有主库提供读写服务,备库冗余作故障转...【详细内容】
2019-12-24   数据库  点击:(3)  评论:(0)  加入收藏
概述在几个流行的数据库中,我首先接触到的是MySQL,随着工作发展,接触到越来越多的是PostgreSQL数据库。这两个十分流行的开源数据库。在这之后,我就会经常和一些朋友进行讨论:MyS...【详细内容】
2019-12-23   数据库  点击:(6)  评论:(0)  加入收藏
配置MySQL服务器第一步是设置MySQL服务器,以侦听机器上的某个特定IP地址或所有IP地址。 如果MySQL服务器和客户端可以通过专用网络相互通信,那么最好的选择是设置MySQL服务器,...【详细内容】
2019-12-23   数据库  点击:(9)  评论:(0)  加入收藏
“数据就是一切!”我们都曾听过这种大胆的说法,但其实事实真是这样,而且数据还无处不在。如今,每个企业和组织发展都需要依赖大量的数据,数据也成为了企业最有价值的资产。为了让数据发挥最大的价值,通常我们都会采用数据库...【详细内容】
2019-12-19   数据库  点击:(7)  评论:(0)  加入收藏
第一部分 RDS MySQL 数据库迁移第二部分 ECS 服务器镜像迁移第三部分 迁移服务器后遇到的问题说明 为了更加容易方便阅读目的地账号 为 A账号(杭州)源账号 为 B 账号(新加坡...【详细内容】
2019-12-17   数据库  点击:(5)  评论:(0)  加入收藏
概述MySQL 访问控制实际上由两个功能模块共同组成,一个是负责“看守 MySQL 大门”的用户管理模块,另一个就是负责监控来访者每一个动作的访问控制模块。用户管理模块决定用户...【详细内容】
2019-12-16   数据库  点击:(5)  评论:(0)  加入收藏
C/C++连接MySql数据库本文对如何使用MySql的API连接MySql数据库,开发环境为VS2008.一、VS2008工程设置工作首先,建立一个windows应用程序的工程,将C/C++->预处理器->预处理器定...【详细内容】
2019-12-13   数据库  点击:(24)  评论:(0)  加入收藏
在PHP开发的时候,操作多维数组,比如我们查询数据库返回的数据数组,有些时候我们希望可以像数据库指定返回某一列那样获取数组中某一列的值,有些时候我们希望根据每一行中id的值...【详细内容】
2019-12-12   数据库  点击:(17)  评论:(0)  加入收藏
这次我们将介绍怎么防止Python注入SQL攻击。有上一篇的铺垫,我们废话不多说,开搞。。。制作安全查询参数在上一篇中,我们看到了入侵者如何利用系统并通过使用 字符串获得管理权...【详细内容】
2019-12-11   数据库  点击:(7)  评论:(0)  加入收藏
好的数据库规范有助于减少软件实现的复杂度,降低沟通成本,本铁律主要涵盖了建库建表、建索引、写 SQL、ORM 映射等方面的处理约定。...【详细内容】
2019-12-11   数据库  点击:(12)  评论:(0)  加入收藏
Redis在国内各大公司都很热门,比如新浪、阿里、腾讯、百度、美团、小米等。Redis也是大厂面试最爱问的,尤其是Redis客户端、Redis高级功能、Redis持久化和开发运维常用问题探...【详细内容】
2019-12-11   数据库  点击:(16)  评论:(0)  加入收藏
模式匹配及模糊查询数据查询是数据库的核心功能之一,其中使用like关键字可以实现待查询字段与指定的模式进行匹配查询。本文主要介绍SQL Server中用于实现LIKE模糊查询相关模...【详细内容】
2019-12-11   数据库  点击:(10)  评论:(0)  加入收藏
首先应考虑在 where 及 order by 涉及的列上建立索引。下面我们来以一个表中177条数据比较一下,全表扫描与建立索引之后性能的一个比较....【详细内容】
2019-12-10   数据库  点击:(16)  评论:(0)  加入收藏
SQLite数据库通常存储在单个普通磁盘文件中。但是,在某些情况下,数据库可能存储在内存中。 强制SQLite数据库单纯的存在于内存中的最常用方法是使用特殊文件名“ :memory: ” 打...【详细内容】
2019-12-09   数据库  点击:(19)  评论:(0)  加入收藏
相信大家在客户现场维护的时候一定会适时地做一些备份操作,对关键业务建立完备的备份机制,本篇文章就是给大家一个linux环境下的参考。一、 用imp/exp实现数据的导入导出(一)环...【详细内容】
2019-12-06   数据库  点击:(25)  评论:(0)  加入收藏
上两篇讲到了我们的系统在面临大并发读取的时候,采用了读写分离主从复制(数据库读写分离方案,实现高性能数据库集群)的方案去应对,后来又面临了大并发写入的时候,系统数据库采用了...【详细内容】
2019-12-06   数据库  点击:(40)  评论:(0)  加入收藏
最新更新
栏目热门
栏目头条