搜尋
首頁資料庫mysql教程MySQL分区表技术解析_MySQL

bitsCN.com

MySQL分区概述:
 允许根据可以设置为任意大小的规则,跨文件系统分配单个表的多个部分。实际上,表的不同部分在不同的位置被存储为单独的表。用户所选择的、实现数据分割的规则被称为分区函数,这在MySQL中它可以是模数,或者是简单的匹配一个连续的数值区间或数值列表,或者是一个内部HASH函数,或一个线性HASH函数。函数根据用户指定的分区类型来选择,把用户提供的表达式的值作为参数。该表达式可以是一个整数列值,或一个作用在一个或多个列值上并返回一个整数的函数。[z1] 。这个表达式的值传递给分区函数,分区函数返回一个表示那个特定记录应该保存在哪个分区的序号。这个函数不能是常数,也不能是任意数。它不能包含任何查询,但是实际上可以使用MySQL 中任何可用的SQL表达式,只要该表达式返回一个小于MAXVALUE(最大可能的正整数)的正数值。
由于MySQL无全局索引的概念,只有本地分区索引,基于此种原因,一个表中如果有2个或2个以上的唯一索引,此表无法分区。分区函数应用的数据库的列必须是MySQL的主键,否则不能分区。[z2]
对于创建了分区的表,可以使用你的MySQL 服务器所支持的任何存储引擎。在MySQL 5.1版中,同一个分区表的所有分区必须使用同一个存储引擎;例如,不能对一个分区使用MyISAM,而对另一个使用InnoDB。但是,这并不妨碍在同一个 MySQL 服务器中,甚至在同一个数据库中,对于不同的分区表使用不同的存储引擎。
 
MySQL分区的建立
MySQL可以建立四种分区类型的分区: 
          RANGE 分区:基于属于一个给定连续区间的列值,把多行分配给分区。详情参见18.2.1节,“RANGE分区”。
·         LIST 分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。详情参见18.2.2节,“LIST分区”。
·         HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。详情参见18.2.3节,“HASH分区”。
·         KEY 分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL 服务器提供其自身的哈希函数。必须有一列或多列包含整数值。详情参照:18.2.4. KEY分区。
        子分区:子分区是分区表中每个分区的再次分割。书写格式参照:18.2.5. 子分区
        (1)关于子分区应注意的地方: 每个分区必须有相同数量的子分区。
·       (2)如果在一个分区表上的任何分区上使用SUBPARTITION 来明确定义任何子分区,那么就必须定义所有的子分区。
在建立分区的时候可以指定分区的数据存储位置和索引位置,这样可以跨磁盘或者文件系统保存不同的数据。数据分磁盘存储可以一定程度上增加数据读取速度,因为采用多磁盘后,每个磁盘的I/O操作会降低。而且采用指定分区存储位置能够增大存储量。
 
无论使用何种类型的分区,分区总是在创建时就自动的顺序编号,且从0开始记录,记住这一点非常重要。当有一新行插入到一个分区表中时,就是使用这些分区编号来识别正确的分区。例如,如果你的表使用4个分区,那么这些分区就编号为0, 1, 2, 和3。对于RANGE和LIST分区类型,确认每个分区编号都定义了一个分区,很有必要。对HASH分区,使用的用户函数必须返回一个大于0的整数值。对于KEY分区,这个问题通过MySQL服务器内部使用的 哈希函数自动进行处理。注意:分区的名字是不区分大小写的,且对于RANGE分区和LIST分区,分区的名称是不能重复的。这几种可根据不同的需求来选择,比较常用的是RANGE分区。
 
常用的MySQL的分区管理:
 RANGE 和LIST分区管理
         分区对于程序来说是透明的,而且只有删除能在分区层面上操作,其他如查询、修改、增加都不能指定分区。
 
ALTER TABLE …DROPPARTITION ….(删除分区)
ALTER TABLE … ADD PARTITION (PARTITION p3 VALUESLESS THAN (…));[z3] 增加分区
ALTER TABLE ... REORGANIZE PARTITION …,… INTO (
   PARTITION p0 VALUES LESS THAN (…)
);[z4] 合并拆分分区。
 
  HASH和KEY分区管理
         添加分区和RANGE、LIST分区方式相同,对于修改分区,不能使用与从按照RANGE或LIST分区的表中删除分区相同的方式,来从HASH或KEY分区的表中删除分区。但是,可以使用“ALTERTABLE ... COALESCE PARTITION”命令来合并HASH或KEY分区。
 
如果要查看分区的信息,可以通过sql语句来查询
SELECT * FROM INFORMATION_SCHEMA.partitions WHERETABLE_SCHEMA = schema() AND TABLE_NAME='xxx’
 
分区表效率比较
 
 
MySQL分区表实验
分区采用红色,不分区采用蓝色
 
测试环境:CentOS 虚拟机,1G内存,20G硬盘
实验数据库:test 不分区(内有1张表RPT_MALEVENTS)、test2(与test一样)
背景数据:
mysql> SELECT COUNT(*)FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (10.84 sec)
 
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS;
+----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (14.63sec)
 
数据分布:2011/8/4~2011/8/17
 
分区表结构:
CREATETABLE `RPT_MALEVENTS` (
  `RECORD_DATE` date NOT NULL,
  `RECORD_HOUR` tinyint(2) NOT NULL,
  `RECORD_MINUTE` tinyint(2) NOT NULL,
  `RECORD_DATETIME` datetime NOT NULL,
  `MC_IP` int(10) unsigned NOT NULL,
  `PC_IP` int(10) unsigned NOT NULL,
  `NETOBJECT_GROUP_ID` smallint(5) DEFAULTNULL,
  `ALERT_TYPE` tinyint(3) NOT NULL,
  `SUB_TYPE` smallint(5) NOT NULL,
  `SHOW_TYPE` smallint(5) NOT NULL,
  `ALERT_ID` tinyint(3) NOT NULL,
  `EVENT_COUNT` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY(`RECORD_DATE`,`RECORD_HOUR`,`RECORD_MINUTE`,`MC_IP`,`PC_IP`,`ALERT_TYPE`,`SUB_TYPE`,`ALERT_ID`),
  KEY `RECORD_DATETIME` (`RECORD_DATETIME`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci /*!50100 PARTITIONBY RANGE (TO_DAYS(RECORD_DATE)[z1] ) (PARTITION p2011 VALUES LESS THAN (734503)ENGINE = InnoDB, PARTITION p20110809 VALUES LESS THAN (734724) ENGINE = InnoDB,PARTITION p20110810 VALUES LESS THAN (734725) ENGINE = InnoDB, PARTITIONp20110811 VALUES LESS THAN (734726) ENGINE = InnoDB, PARTITION p20110812 VALUESLESS THAN (734727) ENGINE = InnoDB, PARTITION p20110813 VALUES LESS THAN(734728) ENGINE = InnoDB, PARTITION p20110814 VALUES LESS THAN (734729) ENGINE= InnoDB, PARTITION p20110815 VALUES LESS THAN (734730) ENGINE = InnoDB,PARTITION p20110816 VALUES LESS THAN (734731) ENGINE = InnoDB, PARTITIONp20110817 VALUES LESS THAN (734732) ENGINE = InnoDB, PARTITION p20110818 VALUESLESS THAN (734733) ENGINE = InnoDB, PARTITION pMax VALUES LESS THAN MAXVALUE[z2]  ENGINE = InnoDB)
 
 
 
分区表的物理存储如下,当前用的是innodB的存储引擎,采用分表结构
分析如下
 (条件查询查询全部数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (21.62sec)
 
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-01' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
| 17082107 |
+----------+
1 row in set (29.20sec)
 
(查询部分数据,不使用分区函数使用的列)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' ANDRECORD_DATETIME +----------+
| COUNT(*) |
+----------+
|  5083194 |
+----------+
1 row in set (2.83sec)
 
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-02' AND RECORD_DATETIME +----------+
| COUNT(*) |
+----------+
|  5083194 |
+----------+
1 row in set (5.60sec)
 
(使用其他条件查询部分数据)
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
|    88739 |
+----------+
1 row in set (8.49sec)
 
SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1;
+----------+
| COUNT(*) |
+----------+
|    88739 |
+----------+
1 row in set (12.88sec)
 
 
(小范围查询,在一个分区内查询)
 
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
|  2116249 |
+----------+
1 row in set (1.85sec)
 
 
mysql> SELECTCOUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE > '2011-08-13' AND RECORD_DATE +----------+
| COUNT(*) |
+----------+
|  2116249 |
+----------+
1 row in set (3.10sec)
 
 
分析SQL语句的执行过程
rows表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数。
 
 
mysql>EXPLAIN   PARTITIONS SELECT * FROMRPT_MALEVENTS WHERE RECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RPT_MALEVENTS
   partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p2011[z3] 0816,p20110817,p20110818,pMax
         type: range
possible_keys:RECORD_DATETIME
          key: RECORD_DATETIME
      key_len: 8
          ref: NULL
         rows: 355911[z4]
        Extra: Using where
1 row in set (0.00sec)
 
mysql>  EXPLAIN SELECT * FROM RPT_MALEVENTS WHERERECORD_DATETIME > '2011-08-12' AND RECORD_DATETIME ***************************1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RPT_MALEVENTS
         type: range
possible_keys:RECORD_DATETIME
          key: RECORD_DATETIME
      key_len: 8
          ref: NULL
         rows: 1002288[z5]
        Extra: Using where
1 row in set (0.00sec)
 
与分区函数使用列无关的查询条件
 
mysql>EXPLAIN   PARTITIONS SELECT COUNT(*) FROMRPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RPT_MALEVENTS
   partitions: p2011,p20110809,p20110810,p20110811,p20110812,p20110813,p20110814,p20110815,p20110816,p20110817,p20110818,pMax[z6]
         type: index
possible_keys: NULL
          key: RECORD_DATETIME
      key_len: 8
          ref: NULL
         rows: 17084274[z7]
        Extra: Using where; Using index
1 row in set (0.00sec)
 
mysql> EXPLAINSELECT COUNT(*) FROM RPT_MALEVENTS WHERE ALERT_TYPE = 1/G;
***************************1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RPT_MALEVENTS
         type: index
possible_keys: NULL
          key: RECORD_DATETIME
      key_len: 8
          ref: NULL
         rows: 17082459
        Extra: Using where; Using index
1 row in set (0.00sec)
 
采用分区函数使用的列
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RPT_MALEVENTS
   partitions: p20110810,p20110811,p20110812,p20110813,p20110814,p20110815[z8]
         type: range
possible_keys:PRIMARY
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 3767081[z9]
        Extra: Using where; Using index
1 row in set (0.08sec)
 
mysql> EXPLAINPARTITIONS SELECT COUNT(*) FROM RPT_MALEVENTS WHERE RECORD_DATE >'2011-08-09' AND RECORD_DATE ***************************1. row ***************************
           id: 1
  select_type: SIMPLE
        table: RPT_MALEVENTS
   partitions: NULL
         type: range
possible_keys:PRIMARY
          key: PRIMARY
      key_len: 3
          ref: NULL
         rows: 8541229[z10]
        Extra: Using where; Using index
1 row in set (0.00sec)
 
 
删除数据,如果删除1整天的数据,由于我们采用按天分区,
 
mysql> ALTER TABLERPT_MALEVENTS DROP PARTITION p20110809;[z11]
Query OK, 0 rowsaffected (0.65 sec)
Records: 0  Duplicates: 0 Warnings: 0
 
 
删除后包含索引的和数据的RPT_MALEVENTS#P#p20110809.ibd被删除了
 
如果采用传统的不分区的方式删除。
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 3929328rows affected (1 min 29.68 sec)
 
由此可见,删除整个分区内的数据还是很快的,
 
如果分区表采用传统的方式删除:
 
mysql> DELETEFROM  RPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (19.72 sec)
 
mysql> DELETE FROMRPT_MALEVENTS WHERE RECORD_DATE Query OK, 1153866rows affected (18.75 sec)
 
 
采用传统的方式删除一天的数据,用的时间都差不多。
 
 
只删除数据后,数据分区配p20110810还在,而且大小不变。可以用ALTER TABLE t1 OPTIMIZE PARTITION来进行回收,但是MySQL5.1.22还没有实现。
 
 
跨分区删除。
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (55.20 sec)
 
DELETE FROMRPT_MALEVENTS WHERE ALERT_TYPE =1;
Query OK, 63969 rowsaffected (50.26 sec)
 
分区表删除比不分区的略慢
 [z1]分区函数
 [z2]分区信息,从2011-08-09开始
 [z3]没有用分区函数使用的列会扫描所有分区
 [z4]数据量为681311,分区后扫描行数为355911,虽然查询条件没有分区函数的列,但是mysql的查询优化器会将其对应于时间分区,这样可以减少扫描行数
 [z5]数据量为681311,分区后扫描行数为1002288
 [z6]查找所有分区
 [z7]无关分区函数的字段,会遍历几乎所有行。
 [z8]扫描部分分区
 [z9]扫描行数随之减少
 [z10]估计扫描的行数
 [z11]这个分区的数据是所有2011-8-10之前的所有数据,共3929328。

 
总结:
分区表是在MySQL5.1中新增的的功能,截止到MySQL5.1.22-rc,分区技术并不很成熟,很多分区的维护和管理功能未实现。如,分区内数据存储空间的回收、分区的修复、分区的优化等,MySQL的分区可以用在可以按分区删除的表中,且对数据库的修改操作不大,且频繁按照分区字段进行查询的表中(如恶意代码中的统计表按天分区,经常按照时间进行查询、分组等,且可以按天删除分区)。此外,由于MySQL无全局索引只有分区索引,当一张有2个唯一索引[z5] 的时候,不能将此表分区,分区列中必须包含主键。否则MySQL会报错。
总之,MySQL对于分区的限制很多,且个人认为hash和key的分区实际意义不是太大。
 
分区引入了一种新的优化查询的方式(当然,也有相应的缺点)。优化器可以使用分区函数修整分区,或者把分区从查询中完全移除掉。它通过推断是否可以在特定的分区上找到数据来达成这种优化。因此在最好的情况下,修整可以让查询访问更少的数据。重要的是要在WHERE子句中定义分区键,即使它看上去像是多余的。通过分区键,优化器就可以去掉不用的分区,否则的话,执行引擎就会像合并表那样访问表的所有分区,这在大表上会非常慢。分区数据比非分区数据更好维护,并且可以通过删除分区来移除老的数据。分区数据可以被分布到不同的物理位置,这样服务器可以更有效地使用多个硬盘驱动器。
 [z1]分区函数的返回值必须是整数,新增分区的分区函数返回值应大于任何一个现有分区的分区函数的返回值。
 [z2]对于有主键的表错误提示:#1503
 A PRIMARY KEY MUST INCLUDE ALL COLUMNS INTHE TABLE'S PARTITIONING FUNCTION,没有主键的则无此约束
 [z3]注意:对于通过RANGE分区的表,只可以使用ADD PARTITION添加新的分区到分区列表的高端。即不能添加比这个分区的范围小的分区。
 
 [z4] 对于按照RANGE分区的表,只能重新组织相邻的分区;不能跳过RANGE分区。不能使用REORGANIZEPARTITION来改变表的分区类型;也就是说,例如,不能把RANGE分区变为HASH分区,反之亦然。也不能使用该命令来改变分区表达式或列。
 [z5]注意主键和唯一索引的区别
 
 
作者“深巷明朝卖杏花”

bitsCN.com
陳述
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn
MySQL中有哪些不同的存儲引擎?MySQL中有哪些不同的存儲引擎?Apr 26, 2025 am 12:27 AM

mysqloffersvariousStorageengines,每個suitedfordferentusecases:1)InnodBisidealForapplicationsNeedingingAcidComplianCeanDhighConcurncurnency,supportingtransactionsancions and foreignkeys.2)myisamisbestforread-Heavy-Heavywyworks,lackingtransactionsactionsacupport.3)記憶

MySQL中有哪些常見的安全漏洞?MySQL中有哪些常見的安全漏洞?Apr 26, 2025 am 12:27 AM

MySQL中常見的安全漏洞包括SQL注入、弱密碼、權限配置不當和未更新的軟件。 1.SQL注入可以通過使用預處理語句防止。 2.弱密碼可以通過強制使用強密碼策略避免。 3.權限配置不當可以通過定期審查和調整用戶權限解決。 4.未更新的軟件可以通過定期檢查和更新MySQL版本來修補。

您如何確定MySQL中的慢速查詢?您如何確定MySQL中的慢速查詢?Apr 26, 2025 am 12:15 AM

在MySQL中識別慢查詢可以通過啟用慢查詢日誌並設置閾值來實現。 1.啟用慢查詢日誌並設置閾值。 2.查看和分析慢查詢日誌文件,使用工具如mysqldumpslow或pt-query-digest進行深入分析。 3.優化慢查詢可以通過索引優化、查詢重寫和避免使用SELECT*來實現。

如何監視MySQL Server的健康和性能?如何監視MySQL Server的健康和性能?Apr 26, 2025 am 12:15 AM

要監控MySQL服務器的健康和性能,應關注系統健康、性能指標和查詢執行。 1)監控系統健康:使用top、htop或SHOWGLOBALSTATUS命令查看CPU、內存、磁盤I/O和網絡活動。 2)追踪性能指標:監控查詢每秒數、平均查詢時間和緩存命中率等關鍵指標。 3)確保查詢執行優化:啟用慢查詢日誌,記錄並優化執行時間超過設定閾值的查詢。

比較和對比Mysql和Mariadb。比較和對比Mysql和Mariadb。Apr 26, 2025 am 12:08 AM

MySQL和MariaDB的主要區別在於性能、功能和許可證:1.MySQL由Oracle開發,MariaDB是其分支。 2.MariaDB在高負載環境中性能可能更好。 3.MariaDB提供了更多的存儲引擎和功能。 4.MySQL採用雙重許可證,MariaDB完全開源。選擇時應考慮現有基礎設施、性能需求、功能需求和許可證成本。

MySQL的許可與其他數據庫系統相比如何?MySQL的許可與其他數據庫系統相比如何?Apr 25, 2025 am 12:26 AM

MySQL使用的是GPL許可證。 1)GPL許可證允許自由使用、修改和分發MySQL,但修改後的分發需遵循GPL。 2)商業許可證可避免公開修改,適合需要保密的商業應用。

您什麼時候選擇InnoDB而不是Myisam,反之亦然?您什麼時候選擇InnoDB而不是Myisam,反之亦然?Apr 25, 2025 am 12:22 AM

選擇InnoDB而不是MyISAM的情況包括:1)需要事務支持,2)高並發環境,3)需要高數據一致性;反之,選擇MyISAM的情況包括:1)主要是讀操作,2)不需要事務支持。 InnoDB適合需要高數據一致性和事務處理的應用,如電商平台,而MyISAM適合讀密集型且無需事務的應用,如博客系統。

在MySQL中解釋外鍵的目的。在MySQL中解釋外鍵的目的。Apr 25, 2025 am 12:17 AM

在MySQL中,外鍵的作用是建立表與表之間的關係,確保數據的一致性和完整性。外鍵通過引用完整性檢查和級聯操作維護數據的有效性,使用時需注意性能優化和避免常見錯誤。

See all articles

熱AI工具

Undresser.AI Undress

Undresser.AI Undress

人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

Undress AI Tool

免費脫衣圖片

Clothoff.io

Clothoff.io

AI脫衣器

Video Face Swap

Video Face Swap

使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱工具

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

這個專案正在遷移到osdn.net/projects/mingw的過程中,你可以繼續在那裡關注我們。 MinGW:GNU編譯器集合(GCC)的本機Windows移植版本,可自由分發的導入函式庫和用於建置本機Windows應用程式的頭檔;包括對MSVC執行時間的擴展,以支援C99功能。 MinGW的所有軟體都可以在64位元Windows平台上運作。

SAP NetWeaver Server Adapter for Eclipse

SAP NetWeaver Server Adapter for Eclipse

將Eclipse與SAP NetWeaver應用伺服器整合。

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser是一個安全的瀏覽器環境,安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問,並防止學生使用未經授權的資源。

mPDF

mPDF

mPDF是一個PHP庫,可以從UTF-8編碼的HTML產生PDF檔案。原作者Ian Back編寫mPDF以從他的網站上「即時」輸出PDF文件,並處理不同的語言。與原始腳本如HTML2FPDF相比,它的速度較慢,並且在使用Unicode字體時產生的檔案較大,但支援CSS樣式等,並進行了大量增強。支援幾乎所有語言,包括RTL(阿拉伯語和希伯來語)和CJK(中日韓)。支援嵌套的區塊級元素(如P、DIV),

Dreamweaver CS6

Dreamweaver CS6

視覺化網頁開發工具