MySQL分區技術 （一），mysql分區技術

MySQL分區技術 （一），mysql分區技術

4：MySQL 分區技術（是mysql 5.1以版本後開始用->是甲骨文mysql技術團隊維護人員以外掛程式形式插入到mysql裡面的技術）目前，針對海量資料的最佳化主要有2中方法：1：大表拆成小表的方式（物理上）一：垂直分表->一張垂直切成幾張二：水平分表（一般重點）->橫切，意思就是一張表有100個資料橫切10張表，一張表存10條（欄位一致）2：SQL語句的最佳化（可以通過增加索引等來調整，但是資料量大的增大會導致索引的維護代價增大）水平資料分割技術將一個表拆成多個表，比較常用的方式是將表中的記錄按照某種hash演算法進行拆分，簡單的拆分方法如取摸方式。同樣，這種分區方法也必須對前端的應用程式中的SQL進行修改方可以使用。而且對於一個SQL，它可能會修改兩個表，那麼你必須地寫出2個SQL語句從而可以完成一個邏輯事務，使得程式的判斷邏輯越來越複雜，這樣也導致程式的維護代價高，也就失去了採用資料庫的優勢。*因此：分區技術可以有力地避免如上的弊端，成為解決海量資料存放區的有力方法。分區技術：->>有效解決了：物理上拆分多個表，邏輯上操作一個表表明不變->>MySQL分區技術介紹（*主要用的是range 和 list 分區*）：-----分區在邏輯上是一張表，在硬體/物理上是多張表,就是拆分表索引和資料-----MySQL的分區技術不同與之前的分表技術，它與水平分表有點類似，但是它在邏輯層進行的水平分表，對與應用程式而言它還是一張表，    MySQL5.1版本後有4中分區類型：一：RANGE分區（用的最多）：基於屬於一個給定連續區間的列值（欄位），把多行分配給分區  -->基於女欄位為參考點來進行分區--將一個表拆分成：索引檔案，資料檔案分區儲存二：LIST分區：類似於按range分區，區別在於list分區是基於列值匹配一個離散值集合中的某個值來進行選擇（列裡面的值是固定值時候來進行分區，而且是枚舉類型的值適合用list分區  -->比如說 性別：男，女）三：HASH分區：基於使用者定義的運算式的傳回值來進行選擇的分區，改運算式使用將要插入到表中的這些行的      列值計算，這個函數可以包含MySQL中有效、產生負整數值的任何錶達式      --->把每次插入的資料隨機的平均的分配到多個分區裡面，最終多個分區裡面的資料時平均分配的，但是每個分區裡面的數值肯能不太一樣，因為是隨機分配的（一般可以用來做MySQL分區的測試來使用）四：KEY分區：類似於按hash分區，區別在於key分區只支援計算一列或多列，且MySQL伺服器提供其自身的雜湊函數測試一下（測試用hash類型的）：->myisam增刪改查的速度快create table t2（id int）engine=myisampartition by hash(id)partitions 5;                 ->能後當你插入資料的時候就會隨機分配插入個個分區中建立一個儲存\d //         ->修改結束符號  之前是；號改成 //create procedure p5()beginset @i=1;while @i<100000 doinsert into t4 values(@i);set @i=@i+1;end while;end //執行剛才建立的儲存call p3()     ->表p3就插入了9999條資料innodb的資料結構：分為：共用資料表空間及其獨佔資料表空間一：innodb表結構共用資料表空間不能做成分區表：所有檔案的資料和索引都在ibddata1（比如你建了2個表會對應產生frm檔案，但是2個表的所有資料和索引全部在這個檔案裡面共用，所有不能對錶做正真的分區，初始值是10M）原因：資料和索引全都是放在一個檔案裡面 .ibddata1檔案二：innodb表結構要想做出分區表必須是“獨佔資料表空間”原因：資料和索引全都是獨立的一個檔案    開啟獨佔空間：（*必須設定檔中開啟檔案才能做出獨佔資料表空間，才能做成分區表*）innodb_data_home_dir = C:\mysql\data\innodb_data_file_path = ibdata1:10M:autoextendinnodb_log_group_home_dir = C:\mysql\data\innodb_file_per_table=1   ->設定檔中innodb下方加上重啟：MySQL   -->pkill mysqld 關閉進程  重新啟動MySQL-bin/mysqld_safe --user=mysql &    測試：create table t4(id int)engine=innodbpartition by RANGE(id)(partition p0 values less than(10000),partition p1 values less than(20000),PARTITION p2 VALUES less than MAXVALUE);能後你在建立innodb類型的資料表後，你會發現建一個x表就有x.frm x.ibd檔案，就不會和其他表放到一起從而做表分區*重點總結：只有把innodb設定成獨立的資料表空間後，才能建立innodb表引擎的表分區相關命令：/s；   查看詳細資料版本啊編碼啊 什麼的。。。show engines; 查看預設表引擎show plugins; 查看當前MySQL的所有外掛程式，可以查看是否支援分區partitionshow index from from tabName;  查看索引show procedure status; 查看簡曆的儲存

mysql 怎處理億級資料

第一階段：
1，一定要正確設計索引
2，一定要避免SQL語句全表掃描，所以SQL一定要走索引（如：一切的 > < != 等等之類的寫法都會導致全表掃描）
3，一定要避免 limit 10000000,20 這樣的查詢
4，一定要避免 LEFT JOIN 之類的查詢，不把這樣的邏輯處理交給資料庫
5，每個表索引不要建太多，大資料時會增加資料庫的寫入壓力

第二階段：

1，採用分表技術（大表分小表）

a)垂直分表：將部分欄位分離出來，設計成分表，根據主表的主鍵關聯
b)水平分表：將相同欄位表中的記錄按照某種Hash演算法進行拆分多個分表

2，採用mysql分區技術（必須5.1版以上，此技術完全能夠對抗Oracle），與水平分表有點類似，但是它是在邏輯層進行的水平分表

第三階段（伺服器方面）：

1,採用memcached之類的記憶體對象緩衝系統，減少資料庫讀取操作
2,採用主從資料庫設計，分離資料庫的讀寫壓力
3,採用Squid之類的Proxy 伺服器和Web快取服務器技術

PS:由於篇幅問題，我只簡單說一些基本概念，其實裡面每個知識點關係到的內容都很多。特別是第一階段，很多工作幾年的程式員，都不能完全理解。我覺得要真正理解索引，最好的辦法就是在1000W-億級以上的資料，進行測試SQL語句，再結合 explain 命令進行查看SQL語句索引情況。
 
mysql 55 分區表 是否成熟？

我認為已經成熟了，原因：分區特性出現在5.1中，而且sun被oracle收購之後，大力改寫mysql很多東西都已被完善，和oracle分區表的效能無法相比，不過可以滿足基本的要求。
不過我想說的是，能不分就不分，分區之後雖然每個分區內的資料較少，但是如果分區索引（就是分區欄位）效率會比不走好一些，如果不走索引，效率甚至會更差些。