大資料表的查詢最佳化方案

標籤：mysql   資料庫   

如果有一張大表，表中的資料有幾百萬、幾千萬甚至上億，要實現即時查詢，查詢的結果要在十秒鐘之內出來，怎麼辦？如何做最佳化?

本人現在做的項目中，有個表的資料超過1千萬行，超過3G的資料。現在需要對錶中的資料進行查詢統計，之前由於沒做最佳化，導致此表的查詢效率非常低下，讓使用者非常苦惱，於是本人蔘與了此表的最佳化。

舉個類似的例子，比如表中的結構如下，現在要統計某一天出生的人口數，或者統計某一城市的人口數，或者某一城市某一天出生的人口數。

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).最佳化索引

    通過添加索引後，查詢的效率得到極大的提升，常用查詢的查詢時間從原來的幾十秒下降到幾秒。

    建立以下兩個單列索引

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的幾千分之一，再加上索引的最佳化，查詢的速度會極大提高。

下面總結一下常用的大資料表最佳化方案.

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所能檢索到的行數通常會提高很多，查詢效率就能提高上去。

比如有成員表，結構如下：

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是大欄位，儲存成員的介紹，這個大欄位會嚴重影響查詢效率，可以將它獨立出來，單獨形成一個表。

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搜尋引擎技術

 

 

大資料表的查詢最佳化方案