mysql 超大數(shù)據(jù)/表管理技巧_MySQL教程
推薦：mysql占用CPU過(guò)高的解決辦法(添加索引)下面是MYSQL占用CPU高處理的一個(gè)例子，希望對(duì)遇到類(lèi)似問(wèn)題的朋友們有點(diǎn)啟發(fā)。一般來(lái)說(shuō)MYQL占用CPU高，多半是數(shù)據(jù)庫(kù)查詢(xún)代碼問(wèn)題，查詢(xún)數(shù)據(jù)庫(kù)過(guò)多。所以一方面要精簡(jiǎn)代碼，另一方面最好對(duì)頻繁使用的代碼設(shè)置索引
如果你對(duì)長(zhǎng)篇大論沒(méi)有興趣，也可以直接看看結(jié)果，或許你對(duì)結(jié)果感興趣。在實(shí)際應(yīng)用中經(jīng)過(guò)存儲(chǔ)、優(yōu)化可以做到在超過(guò)9千萬(wàn)數(shù)據(jù)中的查詢(xún)響應(yīng)速度控制在1到20毫秒�？瓷先ナ莻�(gè)不錯(cuò)的成績(jī)，不過(guò)優(yōu)化這條路沒(méi)有終點(diǎn)，當(dāng)我們的系統(tǒng)有超過(guò)幾百人、上千人同時(shí)使用時(shí)，仍然會(huì)顯的力不從心。
目錄：
分區(qū)存儲(chǔ)
優(yōu)化查詢(xún)
改進(jìn)分區(qū)
模糊搜索
持續(xù)改進(jìn)的方案
正文：
分區(qū)存儲(chǔ)
對(duì)于超大的數(shù)據(jù)來(lái)說(shuō)，分區(qū)存儲(chǔ)是一個(gè)不錯(cuò)的選擇，或者說(shuō)這是一個(gè)必選項(xiàng)。對(duì)于本例來(lái)說(shuō)，數(shù)據(jù)記錄來(lái)源不同，首先可以根據(jù)來(lái)源來(lái)劃分這些數(shù)據(jù)。但是僅僅這樣還不夠，因?yàn)槊總�(gè)來(lái)源的分區(qū)的數(shù)據(jù)都可能超過(guò)千萬(wàn)。這對(duì)數(shù)據(jù)的存儲(chǔ)和查詢(xún)還是太大了。MySQL5.x以后已經(jīng)比較好的支持了數(shù)據(jù)分區(qū)以及子分區(qū)。因此數(shù)據(jù)就采用分區(qū)+子分區(qū)來(lái)存儲(chǔ)。
下面是基本的數(shù)據(jù)結(jié)構(gòu)定義：
CREATE TABLE `tmp_sampledata` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(32) DEFAULT NULL,
`passwd` varchar(32) DEFAULT NULL,
`email` varchar(64) DEFAULT NULL,
`nickname` varchar(32) DEFAULT NULL,
`siteid` varchar(32) DEFAULT NULL,
`src` smallint(6) NOT NULL DEFAULT '0′,
PRIMARY KEY (`id`,`src`)
) ENGINE=MyISAM AUTO_INCREMENT=95660181 DEFAULT CHARSET=gbk
/*!50500 PARTITION BY LIST COLUMNS(src)
SUBPARTITION BY HASH (id)
SUBPARTITIONS 5
(PARTITION pose VALUES IN (1) ENGINE = MyISAM,
PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
PARTITION p62678 VALUES IN (8) ENGINE = MyISAM) */
對(duì)于擁有分區(qū)及子分區(qū)的數(shù)據(jù)表，分區(qū)條件（包括子分區(qū)條件）中使用的數(shù)據(jù)列，都應(yīng)該定義在primary key 或者 unique key中。詳細(xì)的分區(qū)定義格式，可以參考MySQL的文檔。上面的結(jié)構(gòu)是第一稿的存儲(chǔ)方式（后文還將進(jìn)行修改）。采用load data infile的方式加載，用時(shí)30分鐘加載8千萬(wàn)記錄。感覺(jué)還是挺快的（bulk_insert_buffer_size=8m）。
基本查詢(xún)優(yōu)化
數(shù)據(jù)裝載完畢后，我們測(cè)試了一個(gè)查詢(xún)：
mysql> explain select * from tmp_sampledata where id=9562468\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_sampledata
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 8
Extra:
1 row in set (0.00 sec)
這是毋庸置疑的，通過(guò)id進(jìn)行查詢(xún)是使用了主鍵，查詢(xún)速度會(huì)很快。但是這樣的做法幾乎沒(méi)有意義。因?yàn)閷?duì)于終端用戶來(lái)說(shuō)，不可能知曉任何的資料的id的。假如需要按照username來(lái)進(jìn)行查詢(xún)的話：
mysql> explain select * from tmp_sampledata where username = ‘yourusername'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_sampledata
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 74352359
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select * from tmp_sampledata where src between 1 and 7 and username = ‘yourusername'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_sampledata
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 74352359
Extra: Using where
1 row in set (0.00 sec)
那這個(gè)查詢(xún)就沒(méi)法用了。根本就沒(méi)人能等待一個(gè)上億表的全表搜索！這是我們就考慮是否給username創(chuàng)建一個(gè)索引，這樣肯定會(huì)提高查詢(xún)速度：
create index idx_username on tmp_sampledata(username);
這個(gè)創(chuàng)建索引的時(shí)間很久，似乎超過(guò)了數(shù)據(jù)裝載時(shí)間，不過(guò)好歹建好了。
mysql> explain select * from tmp_sampledata2 where username = ‘yourusername'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tmp_sampledata2
type: ref
possible_keys: idx_username
key: idx_username
key_len: 66
ref: const
rows: 80
Extra: Using where
1 row in set (0.00 sec)
和預(yù)期的一樣，這個(gè)查詢(xún)使用了索引，查詢(xún)速度在可接受范圍內(nèi)。
但是這帶來(lái)了另外一個(gè)問(wèn)題：創(chuàng)建索引需要而外的空間��！當(dāng)我們對(duì)username和email都創(chuàng)建索引時(shí)，空間的使用大幅度的提升！這同樣不是我們期望看到的（無(wú)奈的選擇？）。
除了使用索引，并保證其在查詢(xún)中能使用到此索引外，分區(qū)的關(guān)鍵字段是一個(gè)很重要的優(yōu)化因素，比如下面的這個(gè)例子：
mysql> explain select id from tsampledata where username='abcdef'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tsampledata
type: ref
possible_keys: idx_sampledata_username
key: idx_sampledata_username
key_len: 66
ref: const
rows: 80
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select id from tsampledata where username='abcdef' and src in (2,3,4,5)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tsampledata
type: ref
possible_keys: idx_sampledata_username
key: idx_sampledata_username
key_len: 66
ref: const
rows: 40
Extra: Using where
1 row in set (0.01 sec)
mysql> explain select id from tsampledata where username='abcdef' and src in (2)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tsampledata
type: ref
possible_keys: idx_sampledata_username
key: idx_sampledata_username
key_len: 66
ref: const
rows: 10
Extra: Using where
1 row in set (0.00 sec)
mysql> explain select id from tsampledata where username='abcdef' and src in (2,3)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: tsampledata
type: ref
possible_keys: idx_sampledata_username
key: idx_sampledata_username
key_len: 66
ref: const
rows: 20
Extra: Using where
1 row in set (0.00 sec)
同一個(gè)查詢(xún)語(yǔ)句在根據(jù)是否針對(duì)分區(qū)限定做查詢(xún)時(shí)，查詢(xún)成本相差很大：
where username='abcdef' rows: 80
where username='abcdef' and src in (2,3,4,5) rows: 40
where username='abcdef' and src in (2) rows: 10
where username='abcdef' and src in (2,3) rows: 20
從分析中看出，當(dāng)根據(jù)src（分區(qū)表的分區(qū)字段）進(jìn)行查詢(xún)限定時(shí)，被影響的數(shù)目（rows）在發(fā)生著變化。rows:80代表著需要對(duì)8個(gè)分區(qū)進(jìn)行搜索。
改進(jìn)數(shù)據(jù)存儲(chǔ)：另一種分區(qū)格式
既然在統(tǒng)計(jì)應(yīng)用中，最多用的是通過(guò)username, email進(jìn)行數(shù)據(jù)查詢(xún)，那么在表存儲(chǔ)時(shí)，應(yīng)該考慮使用username,email進(jìn)行分區(qū)，而不是通過(guò)id。因此重新創(chuàng)建分區(qū)表，導(dǎo)入數(shù)據(jù)：
CREATE TABLE `tmp_sampledata` (
`id` bigint(20) unsigned NOT NULL,
`username` varchar(32) NOT NULL DEFAULT ”,
`passwd` varchar(32) DEFAULT NULL,
`email` varchar(64) NOT NULL DEFAULT ”,
`nickname` varchar(32) DEFAULT NULL,
`siteid` varchar(32) DEFAULT NULL,
`src` smallint(6) NOT NULL DEFAULT '0′,
primary KEY (`src`,`username`,`email`, `id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
PARTITION BY LIST COLUMNS(src)
SUBPARTITION BY KEY (username,email)
SUBPARTITIONS 10
(PARTITION pose VALUES IN (1) ENGINE = MyISAM,
PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;
這個(gè)定義沒(méi)什么問(wèn)題，按照預(yù)期，它將根據(jù)primary key來(lái)進(jìn)行數(shù)據(jù)表分區(qū)。但是這有一個(gè)非常非常嚴(yán)重的性能問(wèn)題：數(shù)據(jù)在load data infile的時(shí)候，同時(shí)對(duì)數(shù)據(jù)進(jìn)行索引創(chuàng)建。這大大延長(zhǎng)了數(shù)據(jù)裝載時(shí)間，同樣是不可忍受的情況。上面這個(gè)例子，如果建表時(shí)啟用了 primary key 或者 unique key, 在我的測(cè)試系統(tǒng)上，load data infile執(zhí)行了超過(guò)12小時(shí)。而下面這個(gè)：
CREATE TABLE `tmp_sampledata` (
`id` bigint(20) unsigned NOT NULL,
`username` varchar(32) NOT NULL DEFAULT ”,
`passwd` varchar(32) DEFAULT NULL,
`email` varchar(64) NOT NULL DEFAULT ”,
`nickname` varchar(32) DEFAULT NULL,
`siteid` varchar(32) DEFAULT NULL,
`src` smallint(6) NOT NULL DEFAULT '0′
) ENGINE=MyISAM DEFAULT CHARSET=gbk
PARTITION BY LIST COLUMNS(src)
SUBPARTITION BY KEY (username,email)
SUBPARTITIONS 10
(PARTITION pose VALUES IN (1) ENGINE = MyISAM,
PARTITION p2736 VALUES IN (2) ENGINE = MyISAM,
PARTITION p736736 VALUES IN (3) ENGINE = MyISAM,
PARTITION p3838648 VALUES IN (4) ENGINE = MyISAM,
PARTITION p842692 VALUES IN (5) ENGINE = MyISAM,
PARTITION p7575 VALUES IN (6) ENGINE = MyISAM,
PARTITION p386386 VALUES IN (7) ENGINE = MyISAM,
PARTITION p62678 VALUES IN (8) ENGINE = MyISAM)?;
數(shù)據(jù)裝載僅僅用了5分鐘：
mysql> load data infile ‘cvsfile.txt' into table tmp_sampledata fields terminated by ‘\t' escaped by ”;
Query OK, 74352359 rows affected, 65535 warnings (5 min 23.67 sec)
Records: 74352359 Deleted: 0 Skipped: 0 Warnings: 51267046
So，所有的問(wèn)題，又回到了2.上
測(cè)試查詢(xún)中的模糊搜索
對(duì)于創(chuàng)建好索引的大數(shù)據(jù)表，一般般的針對(duì)性的查詢(xún)，應(yīng)該可以滿足需要。但是有些查詢(xún)可能不能通過(guò)索引來(lái)發(fā)揮效率，比如查詢(xún)以 163.com 結(jié)尾的郵箱:
select … from … where email like ‘%163.com'
即便數(shù)據(jù)針對(duì) email 建立有索引，上面的查詢(xún)是用不到那個(gè)索引的。如果我們使用的是 oracle，那么還可以建立一個(gè)反向索引，但是mysql不支持反向索引。所以如果發(fā)生類(lèi)似的查詢(xún)，只有兩種方案可以：
通過(guò)數(shù)據(jù)冗余，把需要的字段反轉(zhuǎn)一遍另外保存，并創(chuàng)建一個(gè)索引
這樣上面的那個(gè)查詢(xún)可以通過(guò) where email like ‘moc.361%' 來(lái)完成，但是這個(gè)成本（存儲(chǔ)、更新）太高昂了
通過(guò)全文檢索fulltext來(lái)實(shí)現(xiàn)。不過(guò)mysql同樣在分區(qū)表上不支持fulltext(或許等待以后的版本吧。)
自己做分詞fulltext
沒(méi)有最終方案
創(chuàng)建一個(gè)不含任何索引、鍵的分區(qū)表；
導(dǎo)入數(shù)據(jù)；
創(chuàng)建索引；
因?yàn)閯?chuàng)建索引要花很久時(shí)間，此處做了個(gè)小小調(diào)整，提高myisam索引的排序空間為1G（默認(rèn)是8m）：
mysql> set myisam_sort_buffer_size=1048576000;
Query OK, 0 rows affected (0.00 sec)
mysql> create index idx_username_src on tmp_sampledata (username,src);
Query OK, 74352359 rows affected (7 min 13.11 sec)
Records: 74352359 Duplicates: 0 Warnings: 0
mysql> create index idx_email_src on tmp_sampledata (email,src);
Query OK, 74352359 rows affected (10 min 48.30 sec)
Records: 74352359 Duplicates: 0 Warnings: 0
mysql> create index idx_src_username_email on tmp_sampledata(src,username,email);
Query OK, 74352359 rows affected (16 min 5.35 sec)
Records: 74352359 Duplicates: 0 Warnings: 0
實(shí)際應(yīng)用中，此表可能不需要這么多索引的，都建立一遍，只是為了展示一下創(chuàng)建的速度而已。
實(shí)際應(yīng)用中的效果
存儲(chǔ)的問(wèn)題暫時(shí)解決到這里了，接下來(lái)經(jīng)過(guò)了一系列的服務(wù)器參數(shù)調(diào)整以及查詢(xún)的優(yōu)化，我只能做到在這個(gè)超過(guò)9千萬(wàn)數(shù)據(jù)中的查詢(xún)響應(yīng)速度控制在1到20毫秒。聽(tīng)上去是個(gè)不錯(cuò)的成績(jī)。但是當(dāng)我們的系統(tǒng)有超過(guò)幾百個(gè)人同時(shí)使用時(shí)，仍然顯的力不從心�；蛟S日后還有機(jī)會(huì)能更優(yōu)化這個(gè)存儲(chǔ)與查詢(xún)。讓我慢慢期待吧。
分享：MySQL自增列插入0值的解決方案基于業(yè)務(wù)邏輯的要求，需要在MySQL的自增列插入0值，針對(duì)此需求，本文給予詳細(xì)的解決方案，感興趣的你可以參考下哈，希望可以幫助到你
- MSSQL清空日志刪除日志文件
- 關(guān)于數(shù)據(jù)庫(kù)中保留小數(shù)位的問(wèn)題
- 解析mysql與Oracle update的區(qū)別
- mysql 導(dǎo)入導(dǎo)出數(shù)據(jù)庫(kù)以及函數(shù)、存儲(chǔ)過(guò)程的介紹
- MySQL——修改root密碼的4種方法(以windows為例)
- 解決MYSQL出現(xiàn)Can''t create/write to file ''#sql_5c0_0.MYD''的問(wèn)題
- 深入理解SQL的四種連接-左外連接、右外連接、內(nèi)連接、全連接
- 解析:內(nèi)聯(lián),左外聯(lián),右外聯(lián),全連接,交叉連接的區(qū)別
- mysql出現(xiàn)“Incorrect key file for table”處理方法
- mysql重裝后出現(xiàn)亂碼設(shè)置為utf8可解決
- 淺析一個(gè)MYSQL語(yǔ)法(在查詢(xún)中使用count)的兼容性問(wèn)題
- 解析MySQL中INSERT INTO SELECT的使用
MySQL教程Rss訂閱編程教程搜索
MySQL教程推薦
- MySQL筆記之連接查詢(xún)?cè)斀?/a>
- KB967723補(bǔ)丁造成的MYSQL在Win2003上頻繁連接不上
- 怎么重置mysql的自增列AUTO_INCREMENT初時(shí)值
- 詳解MYSQL的備份還原(PHP實(shí)現(xiàn))
- MySQL數(shù)據(jù)庫(kù)InnoDB數(shù)據(jù)恢復(fù)工具的使用小結(jié)詳解
- 服務(wù)器不支持 MySql 數(shù)據(jù)庫(kù)的解決方法
- 網(wǎng)站模板:以數(shù)據(jù)庫(kù)字段分組顯示數(shù)據(jù)的sql語(yǔ)句
- MySQL筆記之字符串函數(shù)的應(yīng)用
- 利用Xtrabackup工具備份及恢復(fù)(MySQL DBA的必備工具)
- MySQL DELETE語(yǔ)法使用詳細(xì)解析
猜你也喜歡看這些
- SQL Server 數(shù)據(jù)庫(kù)分離與附加(圖文教程)
- SQL Server 2000數(shù)據(jù)庫(kù)升級(jí)到SQL Server 2005的最快速
- sql server查詢(xún)時(shí)間技巧分享
- 解析SQL Server數(shù)據(jù)應(yīng)用在不同的數(shù)據(jù)庫(kù)中
- SELECT 賦值與ORDER BY沖突的問(wèn)題
- 在SQL Server下數(shù)據(jù)庫(kù)鏈接的使用
- SQL Server 2005分析服務(wù) 統(tǒng)一整合視圖
- SQL Server查詢(xún)語(yǔ)句的使用
- 解讀SQL Server與Access語(yǔ)法小差異
- 基于存儲(chǔ)過(guò)程的詳細(xì)介紹
- 相關(guān)鏈接：
- 教程說(shuō)明：
MySQL教程-mysql 超大數(shù)據(jù)/表管理技巧
。