大数据量表的查询优化及索引使用
发布日期:2021-07-20 20:53:20 浏览次数:28 分类:技术文章

本文共 3136 字,大约阅读时间需要 10 分钟。

一、对于运算逻辑,尽可能将要统计的各项目整合在一个查询语句中计算,而不是用分组条件或分项目调用多个查询语句,而后在代码里计算结果。

二、查询语句的优化,诸如不用"select *"、多表关联查询时添加别名于查询字段上、避免使用in、not in关键字、非去除重复时用union all替换union、先过滤后分组、排序等等。

三、在无法更改数据结构、不影响其它业务操作情况下,为查询、统计项建立索引,这里有一段关于创建索引的话:

创建索引的原则总结如下:(来自:)
    首先要判断表的存储数据量大小、高性能的操作要求(是频繁增删改操作还是频繁的查询操作)。对于要求频繁增删改操作的表,建立索引可能只会起到反作用。
  1. 对于只有几十、几百条记录的表,建立索引的效果可能还不如逐行扫描来得快
  2. 对于只有几个可能值的字段,最常见的如性别等字段,建立索引是无意义的
  3. 对于在查询语句的WHERE子句中频繁出现的字段可以建立索引,但注意如果索引字段上存在函数,该索引失效。如:WHERE SUBSTR(NAME,1)=’N’;NAME字段上的索引不会起作用,改写成WHERE NAME LIKE ‘N%’, NAME字段上的索引才能生效。另外IS NULL和IS NOT NULL也会使索引失效
  4. 适量的冗余字段可以减小查询的开销,虽然这样做不符合数据库范式的要求
  5. 建立在大数据类型字段上的索引没有意义,比如SQLSERVER的IMAGE,ORACLE的LOB

四、尽可能增大分配给数据库服务器的内存,sql语句的运算都是在内存中完成的,如果分配的内存过小,对于大量数据的运算虽不会导致内存溢出,但是运算速度会非常的慢。

 

-------------------------------------------------------------------------------------------------------------------------

    1:索引,我们最先想到的就是创建索引,创建索引可以成倍的提升查询的效率,节省时间。但是如果数据量太过于巨大的时候,这个时候单纯的创建索引是无济于事的,我们知道假如特别是在大数据量中统计查询,就拿1000W数据来说吧,如果使用count函数的话,最少要50-100秒以上,当然如果你的配置够高,处理够快,或许会少很多但是一样会超过10秒。

  单纯的建立索引是无济于事的。我们可以在创建索引的时候给索引加个属性,compress,这个属性可以将所创建的索引进行一个良好的归类,这样的话,查询速度会提升5-10倍,或者更高。但是唯一的缺点是,压缩索引只能手动创建,对于那些KEY是无法进行压缩的,因为KEY(主键)是自动创建的索引,compress必选的属性,一般默认是不创建。所以在创建压缩索引的时候,可以找其他的关键字段进行压缩,比如工单表里面的流水号

  2:尽量少的使用那些函数,比如 IS NUll;IS NOT NULL,IN;NOT IN等这样的匹配函数,可以使用符号程序进行操作

  3:尽量少使用子查询,如果你写个类,里面模仿子查询的效果,你就会发现,简直在要命,我们可以使用联合查询,或者是外连接查询,这样速度会比子查询快很多。

  4:在使用索引的时候,注意如下:

  Where子句中有"!="将使索引失效

  select account_name from test where amount != 0  (不使用)

  select account_name from test where amount > 0  (使用)

  Where条件中对字段增加处理函数将不使用该列的索引

  select * from emp where to_char(hire_date,'yyyymmdd')='20080411' (不使用)

  select * from emp where hire_date = to_char('20080411','yyyymmdd') (使用)

  避免在索引列上使用IS NULL和 IS NOT NULL

  select * from emp where dept_code is not null  (不使用)

  select * from emp where dept_code > 0  (使用)

  通配符% 的使用

  select * from emp where name like '%A'  (不使用索引)

  select * from emp where name like 'A%'  (使用索引)

---------------------------------------------------------------------------------------

    http://www.2cto.com/database/201411/348519.html

 

(1).优化索引

通过添加索引后,查询的效率得到极大的提升,常用查询的查询时间从原来的几十秒下降到几秒。

建立以下两个单列索引

ALTERTABLE population , ADD INDEX fk_city(city)  ,ADD INDEX fk_birthday(birthday);

 

建立组合索引

ALTERTABLE  population  ADD INDEX fk_index1(city,birthday),ADD INDEX fk_index2(birthday,city);

(2).使用中间表
     虽然索引优化可以将查询时间大大减少,但如果数据量达到一定量时,有些情况下索引到的数据达到几百万时,查询仍然会很慢,因此索引优化 无法从根本上解决问题。现在表中的数据量越来越大,平均每个月要增加一两百万的数据,索引的优化方法只是暂时的,只能解决小数据量的查询问题,随着数据量的快速增长,索引带来的性能优化很容易达到极限,要寻找其他的解决方案。

 我们根据业务需求的特点,创建中间表population_statistics,将表population中的统计数据存放到中间表population_statistics中,查询时 直接从中间表population_statistics中查询。注意,在对表population进行增、删、改时,必须同时更新population_statistics中的数据,否则会出现数据不一致的错误!

-------------------------------------------------------------------------------

主表A 20多W条数据,内连接了视图B两次,视图B有20多W条数据,然后又左连接了一个40多W条数据的表,总的查询再group by了一下,被by的字段有十几个,select中有一些字段做了一些sum计算,还select了一些其它字段,where有一些动态生成的查询条件,对应一个搜索页面,搜索条件根据用户选择生成,我做索引前查询需要5分钟,做了之后也要100多秒,原来所有的查询是写在一个存储过程来调用的,动态生成的查询条件也写在过程里,调用一个大视图来查询我前面讲的这些,我把查询条件直接写到视图里的话,可以缩短到50多秒,看来还是写进去比较好啊,但再也没办法再缩短了,要命的是,查询条件还有一些是数值比较的,是通过一些字段的计算再与其比较的,如果在where后面写一些字段的运算再比较,无疑效率很低,也用不上索引,有没有比较好的办法再优化一下。

转载地址:https://blog.csdn.net/liao0801_123/article/details/83616979 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!

上一篇:阻塞队列BlokingQueue与双向队列Deque
下一篇:查看JVM的GC日志

发表评论

最新留言

初次前来,多多关照!
[***.46.13.176]2022年12月04日 12时23分01秒

关于作者

    喝酒易醉,品茶养心,人生如梦,品茶悟道,何以解忧?唯有杜康!
-- 愿君每日到此一游!

最新文章