文档章节

大数据表的查询优化方案

 蜗牛奔跑
发布于 2017/02/28 16:24
字数 1618
阅读 21
收藏 0
点赞 0
评论 0

如果有一张大表,表中的数据有几百万、几千万甚至上亿,要实现实时查询,查询的结果要在十秒钟之内出来,怎么办?如何做优化?

本人现在做的项目中,有个表的数据超过1千万行,超过3G的数据。现在需要对表中的数据进行查询统计,之前由于没做优化,导致此表的查询效率非常低下,让使用者非常苦恼,于是本人参与了此表的优化。

举个类似的例子,比如表中的结构如下,现在要统计某一天出生的人口数,或者统计某一城市的人口数,或者某一城市某一天出生的人口数。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CREATE TABLE `population` (

  `population_id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '人口表',

  `name` varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT '姓名',

  `city` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '城市',

  `birthday` date DEFAULT NULL COMMENT '出生日期',

  PRIMARY KEY (`population_id`)

)

 

查询某一城市某一天出生的人口数

SELECT COUNT(*) FROM population WHERE city='广州' AND birthday = '2014-11-02'

查询某一城市的人口数

SELECT COUNT(*) FROM population WHERE city='广州'

查询某一天出生的人口数

SELECT COUNT(*) FROM population WHERE birthday = '2014-11-02'

提出了两个优化方案,

(1).优化索引

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

建立以下两个单列索引

?

1

2

3

ALTER TABLE `population`  

  ADD  INDEX `fk_city` (`city`),

  ADD  INDEX `fk_birthday` (`birthday`);

也可以建立以下两个组合索引

?

1

2

3

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中的数据,否则会出现数据不一致的错误!

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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的几千分之一,再加上索引的优化,查询的速度会极大提高。

下面总结一下常用的大数据表优化方案.

1. 索引优化

通过建立合理高效的索引,提高查询的速度.

建议阅读本人写的一篇关于索引的博客

http://blog.csdn.net/brushli/article/details/39677387

2. SQL优化

组织优化SQL语句,使查询效率达到最优,在很多情况下要考虑索引的作用.

建议阅读考本人写的一篇关于索引的博客

http://blog.csdn.net/brushli/article/details/39677387

3. 水平拆表

如果表中的数据呈现出某一类特性,比如呈现时间特性,那么可以根据时间段将表拆分成多个。

比如按年划分、按季度划分、按月划分等等,查询时按时间段进行拆分查询,再把查询结果进行合并;

比如按地区将表拆分,不同地区的数据放在不同的表里面,然后对查询进行分拆,对查询结果进行合并。

4. 垂直拆表

将表按字段拆分成多个表,常用的字段放在一个表,不常用的字段或大字段放在另外一个表。由于数据库每次查询都是以块为单位,而每块的容量是有限的,通常是十几K或几十K,将表按字段拆分后,单次IO所能检索到的行数通常会提高很多,查询效率就能提高上去。

比如有成员表,结构如下:

?

1

2

3

4

5

6

7

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是大字段,保存成员的介绍,这个大字段会严重影响查询效率,可以将它独立出来,单独形成一个表。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

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 '成员年龄',

  PRIMARY KEY (`member_id`)

)

 

CREATE TABLE `member_introduction` (

  `member_introduction_id` bigint(64) NOT NULL AUTO_INCREMENT COMMENT '成员介绍表ID',

  `member_id` bigint(64) DEFAULT NULL COMMENT '成员ID',

  `introduction` text COLLATE utf8_bin COMMENT '成员介绍',

  PRIMARY KEY (`member_introduction_id`),

  KEY `fk_member_id` (`member_id`),

  CONSTRAINT `fk_member_id` FOREIGN KEY (`member_id`) REFERENCES `member` (`member_id`)

)

5. 建立中间表,以空间换时间

在有些情况下,是可以通过建立中间表来加快查询速度的,详情可看文章开头的例子。

6. 用内存缓存数据,以空间换时间

将常用而且不常修改的数据加载到内存中,直接从内存查询则可。

可以使用热门的缓存技术,如Memcache、Redis、Ehcache等。

7. 使用其他辅助技术

Solr:一种基于Lucene的JAVA搜索引擎技术

© 著作权归作者所有

共有 人打赏支持
粉丝 34
博文 595
码字总数 112669
作品 0
海淀
一个SQL查询优化问题

有一张数据表(company_table)存放企业基本信息,约有记录 150W 主要字段 id(主键,自增) company_name companyotherinfo 1 XXX科技有限公司 …… 2 XXX公司 …… …… XXX …… 1500000 XX...

归海一刀 ⋅ 2013/09/01 ⋅ 19

MySQL的基本命令

启动:net start mySql; 进入:mysql -u root -p/mysql -h localhost -u root -p databaseName; 列出数据库:show databases; 选择数据库:use databaseName; 列出表格:show tables; 显示表......

zyt_1978 ⋅ 2016/09/18 ⋅ 0

Mysql 基本命令

启动:net start mySql; 进入:mysql -u root -p/mysql -h localhost -u root -p databaseName; 列出数据库:show databases; 选择数据库:use databaseName; 列出表格:show tables; 显示表......

Kxvz ⋅ 2015/05/20 ⋅ 0

sqlserver 问题总结

问题总结: ----------------------------------------------------------- 索引优化很重要,SFA性能问题95%是索引没有正常触发导致,大体现在存贮过程中like,in,or,join等语句没有优化,导...

桃_91 ⋅ 2014/06/26 ⋅ 0

mysql 查询优化

数据一多!速度就慢!我这里讲解MySQL查询优化 当你执行管理员优化的时候,应该紧记以下规则: · 访问内存中的数据快于访问磁盘上的数据。 · 尽量把数据保存在内存中可以减少磁盘操作。 ·...

企图穿越 ⋅ 2010/06/10 ⋅ 0

ORACLE将执行过的SQL语句存放在内存的共享池

Oracle SQL性能优化深入浅出 ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中,可以被所有的数据库用户共享。当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执...

技术小甜 ⋅ 2017/11/09 ⋅ 0

MYSQL指令整理

启动:net start mySql; 进入:mysql -u root -p/mysql -h localhost -u root -p databaseName; 列出数据库:show databases; 选择数据库:use databaseName; 列出表格:show tables; 显示表......

君子之耀 ⋅ 2013/12/27 ⋅ 2

数据集的概念mysql

数据集的概念 开源:MySQL 高效: 性价比高 数据库,指的是以一定的方式储存在一起,能为多个用户共享,具有近可能的小的冗余的特点。是应用程序彼此独立数据集合 文件系统管理的问题: 数据...

lampit凌宇 ⋅ 2017/06/06 ⋅ 0

sqlserver 优化原则

--性能优化原则 对存在性能问题的SQL,先查看其执行计划,重点查看Table Scan和Clustered Index Scan节点 1.在这些节点对应的表字段添加索引 2.exists的代替in 3.like 百分号放在后边,例如:...

桃_91 ⋅ 2014/06/25 ⋅ 0

关于MySQL的知识点与面试常见问题都在这里

摘要: Java面试通关手册(Java学习指南,欢迎Star,会一直完善下去,欢迎建议和指导):https://github.com/Snailclimb/Java_Guide 书籍推荐 《高性能MySQL : 第3版》 文字教程推荐 MySQL ...

传授知识的天使 ⋅ 06/13 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Centos7重置Mysql 8.0.1 root 密码

问题产生背景: 安装完 最新版的 mysql8.0.1后忘记了密码,向重置root密码;找了网上好多资料都不尽相同,根据自己的问题总结如下: 第一步:修改配置文件免密码登录mysql vim /etc/my.cnf 1...

豆花饭烧土豆 ⋅ 30分钟前 ⋅ 0

熊掌号收录比例对于网站原创数据排名的影响[图]

从去年下半年开始,我在写博客了,因为我觉得业余写写博客也还是很不错的,但是从2017年下半年开始,百度已经推出了原创保护功能和熊掌号平台,为此,我也提交了不少以前的老数据,而这些历史...

原创小博客 ⋅ 今天 ⋅ 0

LVM讲解、磁盘故障小案例

LVM LVM就是动态卷管理,可以将多个硬盘和硬盘分区做成一个逻辑卷,并把这个逻辑卷作为一个整体来统一管理,动态对分区进行扩缩空间大小,安全快捷方便管理。 1.新建分区,更改类型为8e 即L...

蛋黄Yolks ⋅ 今天 ⋅ 0

Hadoop Yarn调度器的选择和使用

一、引言 Yarn在Hadoop的生态系统中担任了资源管理和任务调度的角色。在讨论其构造器之前先简单了解一下Yarn的架构。 上图是Yarn的基本架构,其中ResourceManager是整个架构的核心组件,它负...

p柯西 ⋅ 今天 ⋅ 0

uWSGI + Django @ Ubuntu

创建 Django App Project 创建后, 可以看到路径下有一个wsgi.py的问题 uWSGI运行 直接命令行运行 利用如下命令, 可直接访问 uwsgi --http :8080 --wsgi-file dj/wsgi.py 配置文件 & 运行 [u...

袁祾 ⋅ 今天 ⋅ 0

JVM堆的理解

在JVM中,我们经常提到的就是堆了,堆确实很重要,其实,除了堆之外,还有几个重要的模块,看下图: 大 多数情况下,我们并不需要关心JVM的底层,但是如果了解它的话,对于我们系统调优是非常...

不羁之后 ⋅ 昨天 ⋅ 0

推荐:并发情况下:Java HashMap 形成死循环的原因

在淘宝内网里看到同事发了贴说了一个CPU被100%的线上故障,并且这个事发生了很多次,原因是在Java语言在并发情况下使用HashMap造成Race Condition,从而导致死循环。这个事情我4、5年前也经历...

码代码的小司机 ⋅ 昨天 ⋅ 2

聊聊spring cloud gateway的RetryGatewayFilter

序 本文主要研究一下spring cloud gateway的RetryGatewayFilter GatewayAutoConfiguration spring-cloud-gateway-core-2.0.0.RC2-sources.jar!/org/springframework/cloud/gateway/config/G......

go4it ⋅ 昨天 ⋅ 0

创建新用户和授予MySQL中的权限教程

导读 MySQL是一个开源数据库管理软件,可帮助用户存储,组织和以后检索数据。 它有多种选项来授予特定用户在表和数据库中的细微的权限 - 本教程将简要介绍一些选项。 如何创建新用户 在MySQL...

问题终结者 ⋅ 昨天 ⋅ 0

android -------- 颜色的半透明效果配置

最近有朋友问我 Android 背景颜色的半透明效果配置,我网上看资料,总结了一下, 开发中也是常常遇到的,所以来写篇博客 常用的颜色值格式有: RGB ARGB RRGGBB AARRGGBB 这4种 透明度 透明度...

切切歆语 ⋅ 昨天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部