大数据表查询优化方案
来源:互联网 发布:象过河软件客服 编辑:程序博客网 时间:2024/05/20 08:00
如果有一张大表,表中的数据有几百万、几千万甚至上亿,要实现实时查询,查询的结果要在十秒钟之内出来,怎么办?如何做优化?
本人现在做的项目中,有个表的数据超过3千万行,超过5G的数据。现在需要对表中的数据进行查询统计,之前由于没做优化,导致此表的查询效率非常低下,让使用者非常苦恼,于是本人参与了此表的优化。
举个类似的例子,比如表中的结构如下,现在要统计某一天出生的人口数,或者统计某一城市的人口数,或者某一城市某一天出生的人口数。
查询某一城市某一天出生的人口数SELECT COUNT(*) FROM population WHERE city='山东' AND birthday = '2017-05-01'查询某一城市的人口数SELECT COUNT(*) FROM population WHERE city='山东'查询某一天出生的人口数SELECT COUNT(*) FROM population WHERE birthday = '2017-05-01'
提出了两个优化方案,
(1).优化索引
通过添加索引后,查询的效率得到极大的提升,常用查询的查询时间从原来的几十秒下降到几秒。
建立以下两个单列索引
ALTER TABLE `population` ADD INDEX `fk_city` (`city`), ADD INDEX `fk_birthday` (`birthday`);也可以建立以下两个组合索引ALTER TABLE `population` ADD INDEX `fk_index1` (`city`, `birthday`), ADD INDEX `fk_index2` (`birthday`, `city`);
(2).使用中间表
虽然索引优化可以将查询时间大大减少,但如果数据量达到一定量时,有些情况下索引到的数据达到几百万时,查询仍然会很慢,因此索引优化无法从根本上解决问题。现在表中的数据量越来越大,平均每个月要增加一两百万的数据,索引的优化方法只是暂时的,只能解决小数据量的查询问题,随着数据量的快速增长,索引带来的性能优化很容易达到极限,要寻找其他的解决方案。
我们根据业务需求的特点,创建中间表population_statistics,将表population中的统计数据存放到中间表population_statistics中,查询时直接从中间表population_statistics中查询。注意,在对表population进行增、删、改时,必须同时更新population_statistics中的数据,否则会出现数据不一致的错误!
CREATE TABLE `population_statistics` ( `population_statistics_id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '人口统计表ID', `city` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '城市', `birthday` int(32) DEFAULT NULL COMMENT '出生日期', `total_count` int(32) DEFAULT NULL COMMENT '人口数量', PRIMARY KEY (`population_statistics_id`), KEY `fk_city` (`city`), KEY `fk_birthday` (`birthday`))
查询某一城市某一天出生的人口数
SELECT total_count FROM population_statistics WHERE city=’广州’ AND birthday = ‘2014-11-02’;
查询某一城市的人口数
SELECT COUNT(total_count) FROM population_statistics WHERE city=’广州’;
查询某一天出生的人口数
SELECT COUNT(total_count) FROM population_statistics WHERE birthday = ‘2014-11-02’;
某个城市某一天的人口在表population中可能有几千甚至万的数据,而在统计表population_statistics中最多只有一条数据,也就是说统计表population_statistics中的数据量只有人口表population的几千分之一,再加上索引的优化,查询的速度会极大提高。
下面总结一下常用的大数据表优化方案.
- 索引优化
通过建立合理高效的索引,提高查询的速度.
建议阅读本人写的一篇关于索引的博客
http://blog.csdn.net/qq_37131111/article/details/76297359
http://blog.csdn.net/qq_37131111/article/details/76297769
- SQL优化
组织优化SQL语句,使查询效率达到最优,在很多情况下要考虑索引的作用.
- 水平拆表
如果表中的数据呈现出某一类特性,比如呈现时间特性,那么可以根据时间段将表拆分成多个。
比如按年划分、按季度划分、按月划分等等,查询时按时间段进行拆分查询,再把查询结果进行合并;
比如按地区将表拆分,不同地区的数据放在不同的表里面,然后对查询进行分拆,对查询结果进行合并。
- 垂直拆表
将表按字段拆分成多个表,常用的字段放在一个表,不常用的字段或大字段放在另外一个表。由于数据库每次查询都是以块为单位,而每块的容量是有限的,通常是十几K或几十K,将表按字段拆分后,单次IO所能检索到的行数通常会提高很多,查询效率就能提高上去。
比如有成员表,结构如下:
CREATE TABLE `member` ( `member_id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '成员表ID', `name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '成员姓名', `age` int(32) DEFAULT NULL COMMENT '成员年龄', `introduction` text COLLATE utf8_bin COMMENT '成员介绍', PRIMARY KEY (`member_id`))
introduction是大字段,保存成员的介绍,这个大字段会严重影响查询效率,可以将它独立出来,单独形成一个表。
- 建立中间表,以空间换时间
在有些情况下,是可以通过建立中间表来加快查询速度的,详情可看文章开头的例子。
- 用内存缓存数据,以空间换时间
将常用而且不常修改的数据加载到内存中,直接从内存查询则可。
可以使用热门的缓存技术,如Memcache、Redis、Ehcache等。
- 使用其他辅助技术
Solr:一种基于Lucene的JAVA搜索引擎技术
- 大数据表查询优化方案
- 大数据表的查询优化方案
- 大数据表的查询优化方案
- 大数据表的查询优化方案
- 大数据表的查询优化方案
- 大数据查询优化方案
- ORACEL 大数据表 加快查询速度方案 总结
- oracle 大数据查询优化的方案
- MySQL大数据量查询优化最佳方案
- 大数据量中的模糊查询优化方案
- 大数据表的查询
- 大量数据表的优化方案
- ORACLE中 大数据表的交叉数据集的查询 SQL优化方法
- 数据表的物理优化方案(2)
- 数据表的物理优化方案(1)
- [数据库 开发] 大量数据表的优化方案
- 数据库查询优化方案
- 数据库查询优化方案
- 线程的状态转换
- 冒泡排序
- CentOS使用yum安装mysql
- C++温故笔记(二)
- 通过请求队列的方式来缓解高并发抢购(初探)
- 大数据表查询优化方案
- JS继承(inheritance)的几种方式分析
- VC/VC++的学习之路二生成动态库和静态库
- struts2-----访问web元素的三种方式and 常用返回类型
- Spring常用注解介绍【经典总结】
- Python函数参数到底是按值还是按引用
- KMP的Java实现
- Java泛型的局限性
- 大神的博客