转载自：http://www.2cto.com/database/201411/348519.html
如果有一张大表，表中的数据有几百万、几千万甚至上亿，要实现实时查询，查询的结果要在十秒钟之内出来，怎么办？如何做优化?
本人现在做的项目中，有个表的数据超过1千万行，超过3G的数据。现在需要对表中的数据进行查询统计，之前由于没做优化，导致此表的查询效率非常低下，让使用者非常苦恼，于是本人参与了此表的优化。
举个类似的例子，比如表中的结构如下，现在要统计某一天出生的人口数，或者统计某一城市的人口数，或者某一城市某一天出生的人口数。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
CREATE
`population` (
`population_id`
bigint
(64)
NOT
AUTO_INCREMENT COMMENT
'人口表'
,
`
name
`
varchar
(128)
COLLATE
DEFAULT
COMMENT
'姓名'
,
`city`
varchar
(32)
COLLATE
DEFAULT
COMMENT
'城市'
,
`birthday`
date
NULL
COMMENT
'出生日期'
,
PRIMARY
(`population_id`)
)
查询某一城市某一天出生的人口数
SELECT
(*)
FROM
WHERE
'广州'
birthday =
'2014-11-02'
查询某一城市的人口数
SELECT
(*)
FROM
WHERE
'广州'
查询某一天出生的人口数
SELECT
(*)
FROM
WHERE
'2014-11-02'
|
提出了两个优化方案，
(1).优化索引
通过添加索引后，查询的效率得到极大的提升，常用查询的查询时间从原来的几十秒下降到几秒。
建立以下两个单列索引
|
1
2
3
|
ALTER
`population`
ADD
`fk_city` (`city`),
ADD
`fk_birthday` (`birthday`);
|
也可以建立以下两个组合索引
|
1
2
3
|
ALTER
`population`
ADD
`fk_index1` (`city`, `birthday`),
ADD
`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
`population_statistics` (
`population_statistics_id`
bigint
(64)
NOT
AUTO_INCREMENT COMMENT
'人口统计表ID'
,
`city`
varchar
(128)
COLLATE
DEFAULT
COMMENT
'城市'
,
`birthday`
int
(32)
DEFAULT
COMMENT
'出生日期'
,
`total_count`
int
(32)
DEFAULT
COMMENT
'人口数量'
,
PRIMARY
(`population_statistics_id`),
KEY
KEY
)
查询某一城市某一天出生的人口数
SELECT
FROM
WHERE
'广州'
birthday =
'2014-11-02'
;
查询某一城市的人口数
SELECT
(total_count)
FROM
WHERE
'广州'
;
查询某一天出生的人口数
SELECT
(total_count)
FROM
WHERE
'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
`member` (
`member_id`
bigint
(64)
NOT
AUTO_INCREMENT COMMENT
'成员表ID'
,
`
name
`
varchar
(128)
COLLATE
DEFAULT
COMMENT
'成员姓名'
,
`age`
int
(32)
DEFAULT
COMMENT
'成员年龄'
,
`introduction` text
COLLATE
'成员介绍'
,
PRIMARY
(`member_id`)
)
|
introduction是大字段，保存成员的介绍，这个大字段会严重影响查询效率，可以将它独立出来，单独形成一个表。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
CREATE
`member` (
`member_id`
bigint
(64)
NOT
AUTO_INCREMENT COMMENT
'成员表ID'
,
`
name
`
varchar
(128)
COLLATE
DEFAULT
COMMENT
'成员姓名'
,
`age`
int
(32)
DEFAULT
COMMENT
'成员年龄'
,
PRIMARY
(`member_id`)
)
CREATE
`member_introduction` (
`member_introduction_id`
bigint
(64)
NOT
AUTO_INCREMENT COMMENT
'成员介绍表ID'
,
`member_id`
bigint
(64)
DEFAULT
COMMENT
'成员ID'
,
`introduction` text
COLLATE
'成员介绍'
,
PRIMARY
(`member_introduction_id`),
KEY
CONSTRAINT
FOREIGN
(`member_id`)
REFERENCES
)
|
5. 建立中间表，以空间换时间
在有些情况下，是可以通过建立中间表来加快查询速度的，详情可看文章开头的例子。
6. 用内存缓存数据，以空间换时间
将常用而且不常修改的数据加载到内存中，直接从内存查询则可。
可以使用热门的缓存技术，如Memcache、Redis、Ehcache等。
7. 使用其他辅助技术
Solr:一种基于Lucene的JAVA搜索引擎技术