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

本文共 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日志

发表评论

最新留言

网站不错 人气很旺了 加油
[***.192.178.218]2024年03月29日 02时21分22秒

关于作者

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

推荐文章

中考大数据大连79_中考大数据 | 大连部分初中2019年中考指标生录取最低分及人数统计!... 2019-04-21
vue 地理位置定位_HTML5地理位置 2019-04-21
pac代理模式什么意思_托管仓库租赁电商仓储运营模式托管什么意思 2019-04-21
validated 验证数组_在 Laravel 中处理请求验证的智能方法 2019-04-21
洞泾智能机器人产业基地_G60科创走廊洞泾人工智能产业基地(核心区块)暨洞泾镇招商人员培训班顺利开班... 2019-04-21
java 拼接路径优雅方式_Java安全编码实践总结 2019-04-21
realme x2 深度测试打不开_搭载65W超级闪充,realme真我X7手机充电评测 2019-04-21
整数取反编程_【每日编程185期】数字的补数 2019-04-21
能用别的软件吗_手机软件能用蓝牙传送吗 2019-04-21
为什么图片要2的倍数_为什么宝宝喜欢流“口水”?这种2种原因父母要知道,建议收藏... 2019-04-21
下载了XAMPP怎样打开MYSQL_xampp mysql安装启动 2019-04-21
pdo转mysql_mysql转mysqli或pdo 2019-04-21
mysql如果没有表就创建_mysql – 改变表是否存在或创建如果没有 2019-04-21
ireport连接mysql_ireport 4.5教程之数据源介绍 2019-04-21
mysql多维模型_数据仓库数据库设计方法---关系模型和多维模型比较分析 2019-04-21
局域网聊天程序 java MySQL_java 基于TCP/IP协议的局域网聊天小程序 2019-04-21
r glm 中的p值_假设检验中的P值 2019-04-21
mysql中sql语句结构_MySQL中使用sql语句获得表结构 2019-04-21
如何增加mysql主键约束_mysql修改表时怎么添加主键约束? 2019-04-21
java选择路径窗口_Java实现选择电脑路径的方法 2019-04-21