mysql使用教程之分區(qū)表的使用方法(刪除分區(qū)表)
MySQL使用分區(qū)表的好處：
1，可以把一些歸類的數據放在一個分區(qū)中，可以減少服務器檢查數據的數量加快查詢。
2，方便維護，通過刪除分區(qū)來刪除老的數據。
3，分區(qū)數據可以被分布到不同的物理位置，可以做分布式有效利用多個硬盤驅動器。
MySQL可以建立四種分區(qū)類型的分區(qū)：
RANGE 分區(qū)：基于屬于一個給定連續(xù)區(qū)間的列值，把多行分配給分區(qū)。
LIST 分區(qū)：類似于按RANGE分區(qū)，區(qū)別在于LIST分區(qū)是基于列值匹配一個離散值集合中的某個值來進行選擇。 m.fzitv.net
HASH分區(qū)：基于用戶定義的表達式的返回值來進行選擇的分區(qū)，該表達式使用將要插入到表中的這些行的列值進行計算。這個函數可以包含MySQL 中有效的、產生非負整數值的任何表達式。
KEY 分區(qū)：類似于按HASH分區(qū)，區(qū)別在于KEY分區(qū)只支持計算一列或多列，且MySQL 服務器提供其自身的哈希函數。必須有一列或多列包含整數值。
一般用得多的是range分區(qū)和list分區(qū)。
RANGE分區(qū)
這里以一個銷售的業(yè)務來做測試
銷售表有日期/商品/銷售額三個字段
測試數據從2010年1月1日至2010年9月31日
以“月”為單位進行分區(qū)
初期分區(qū)定義
首先需要查看，當前數據庫是否支持分區(qū)
mysql>SHOW VARIABLES LIKE '%partition%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| have_partitioning | YES |
+-------------------+-------+
1 row in set (0.03 sec)
創(chuàng)建分區(qū)表，按照年月的方式分區(qū)。
mysql> CREATE TABLE sale_data (
-> sale_date DATETIME NOT NULL,
-> sale_item VARCHAR(2) NOT NULL ,
-> sale_money DECIMAL(10,2) NOT NULL
-> ) m.fzitv.net
-> PARTITION BY RANGE (YEAR(sale_date)*100+MONTH(sale_date)) (
-> PARTITION p201001 VALUES LESS THAN (201002),
-> PARTITION p201002 VALUES LESS THAN (201003),
-> PARTITION p201003 VALUES LESS THAN (201004),
-> PARTITION p201004 VALUES LESS THAN (201005),
-> PARTITION p201005 VALUES LESS THAN (201006),
-> PARTITION p201006 VALUES LESS THAN (201007),
-> PARTITION p201007 VALUES LESS THAN (201008),
-> PARTITION p201008 VALUES LESS THAN (201009),
-> PARTITION p201009 VALUES LESS THAN (201010),
-> PARTITION pcatchall VLAUES LESS THAN MAXVALUE
-> );
Query OK, 0 rows affected (0.20 sec)
新增分區(qū)
mysql> ALTER TABLE sale_data
-> ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
刪除分區(qū)
--當刪除了一個分區(qū)，也同時刪除了該分區(qū)中所有的數據。
mysql> ALTER TABLE sale_data DROP PARTITION p201010;
Query OK, 0 rows affected (0.22 sec) m.fzitv.net
Records: 0 Duplicates: 0 Warnings: 0
分區(qū)的合并
下面的SQL，將p201001 - p201009 合并為3個分區(qū)p2010Q1 - p2010Q3
mysql> ALTER TABLE sale_data
-> REORGANIZE PARTITION p201001,p201002,p201003,
-> p201004,p201005,p201006,
-> p201007,p201008,p201009 INTO
-> (
-> PARTITION p2010Q1 VALUES LESS THAN (201004),
-> PARTITION p2010Q2 VALUES LESS THAN (201007),
-> PARTITION p2010Q3 VALUES LESS THAN (201010)
-> );
Query OK, 0 rows affected (1.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
分區(qū)的拆分
下面的SQL，將p2010Q1 分區(qū)，拆分為s2009 與s2010 兩個分區(qū)
mysql> ALTER TABLE sale_data REORGANIZE PARTITION p2010Q1 INTO (
-> PARTITION s2009 VALUES LESS THAN (201001),
m.fzitv.net
-> PARTITION s2010 VALUES LESS THAN (201004)
-> );
Query OK, 0 rows affected (0.36 sec)
Records: 0 Duplicates: 0 Warnings: 0
一個利用不同物理位置數據源做分區(qū)的例子：
CREATE TABLE ts (id INT, purchased DATE)
ENGINE=innodb
PARTITION BY RANGE(YEAR(purchased))
SUBPARTITION BY HASH(id)
(
PARTITION p0 VALUES LESS THAN (1990)
(
SUBPARTITION s0 //在大的分區(qū)下又有小的分區(qū)
DATA DIRECTORY='/usr/local/mysql/data0' //數據源
INDEX DIRECTORY='/usr/local/mysql/index0', //索引數據源
SUBPARTITION s1
DATA DIRECTORY='/usr/local/mysql/data1'
INDEX DIRECTORY='/usr/local/mysql/index1'
),
PARTITION p1 VALUES LESS THAN (MAXVALUE)
(
SUBPARTITION s2
DATA DIRECTORY='/usr/local/mysql/data1'
INDEX DIRECTORY='/usr/local/mysql/index1',
SUBPARTITION s3
DATA DIRECTORY='/usr/local/mysql/data2'
INDEX DIRECTORY='/usr/local/mysql/index2'
)
);
分區(qū)索引的局限：
1，所有分區(qū)都要使用同樣的引擎。
2，分區(qū)表的每一個唯一索引必須包含由分區(qū)函數引用的列。
3，mysql能避免查詢所有的分區(qū)，但仍然鎖定了所有分區(qū)。
4，分區(qū)函數能使用的函數和表達式有限，例如函數有上面的4種。
5，分區(qū)不支持外鍵。 m.fzitv.net
6，不能使用LOAD INDEX INTO CACHE
7，分區(qū)并不能總是改善性能，要進行性能評測。
例如可以使用expalin partitions 來查看查詢語句是否使用分區(qū)過濾了數據：
mysql> explain partitions select * from fenqubiao where day<'2011-09-12';
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | fenqubiao | p_2010,p_2011 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-----------+---------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
相關文章
本文為大家介紹下php如何利用GD庫生成縮略圖的，下面有個不錯的示例，大家可以參考下2014-01-01
可以通過SELECT COUNT(*) FROM table_name查詢某個表中有多少條記錄。本文給出兩種可行的Java程序查詢所有別的記錄方法，感興趣朋友可以了解下2013-06-06
簡單了解MySQL union all與union的區(qū)別
這篇文章主要介紹了簡單了解MySQL union all與union的區(qū)別,文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值,需要的朋友可以參考下2020-03-03
MySQL中FIND_IN_SET函數與INSTR函數用法解析
這篇文章主要介紹了MySQL中FIND_IN_SET函數與INSTR函數用法解析,本文通過實例代碼給大家介紹的非常詳細,感興趣的朋友一起看看吧2025-04-04
這篇文章主要介紹了CentOS下安裝MySQL5.6.10和安全配置教的相關資料,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2016-12-12
Windows10下mysql 5.7.17 安裝配置方法圖文教程
這篇文章主要為大家詳細介紹了Windows10下mysql5.7.17安裝配置方法圖文教程，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-02-02
這篇文章主要為大家詳細介紹了MYSQL日志與備份還原問題，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-07-07
在數據庫中有時候我們需要將一些行數據轉換為列數據,這在處理數據透視表、報表生成等場景下非常常見,這篇文章主要給大家介紹了關于Mysql行轉列把逗號分隔的字段拆分成多行的兩種方法,需要的朋友可以參考下2024-05-05
MySQL動態(tài)創(chuàng)建表，數據分表的存儲過程
MySQL動態(tài)創(chuàng)建表，數據分表的存儲過程，需要的朋友可以參考下。2011-08-08
這篇文章主要介紹了MySQL 鎖的相關知識總結，幫助大家更好的理解和使用MySQL數據庫，感興趣的朋友可以了解下2021-01-01