MySQL 的表分区详解-查看分区数据量，查看全库数据量
查看分区数据量，查看全库数据量
USE information_schema;
SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME = 'sale_data';
SELECT table_name,table_rows FROM TABLES
WHERE TABLE_SCHEMA = 'db_name'
ORDER BY table_rows DESC ;
这篇⽂章主要介绍了MySQL 的表分区，例如什么是表分区、为什么要对表进⾏分区、表分区的4种类型详解等，需要的朋友可以参考下
⼀、什么是表分区
通俗地讲表分区是将⼀⼤表，根据条件分割成若⼲个⼩表。mysql5.1开始⽀持数据表分区了。
如：某⽤户表的记录超过了600万条，那么就可以根据⼊库⽇期将表分区，也可以根据所在地将表分区。当然也可根据其他的条件分区。
⼆、为什么要对表进⾏分区
有哪些适合python的ide为了改善⼤型表以及具有各种访问模式的表的可伸缩性，可管理性和提⾼数据库效率。
分区的⼀些优点包括：
食肉牛龙vs异特龙1）、与单个磁盘或⽂件系统分区相⽐，可以存储更多的数据。
2）、对于那些已经失去保存意义的数据，通常可以通过删除与那些数据有关的分区，很容易地删除那些数据。相反地，在某些情况下，添加新数据的过程⼜可以通过为那些新数据专门增加⼀个新的分区，来很⽅便地实现。通常和分区有关的其他优点包括下⾯列出的这些。MySQL 分区中的这些功能⽬前还没有实现，但是在我们的优先级列表中，具有⾼的优先级；我们希望在5.1的⽣产版本中，能包括这些功能。
3）、⼀些查询可以得到极⼤的优化，这主要是借助于满⾜⼀个给定WHERE 语句的数据可以只保存在
⼀个或多个分区内，这样在查时就不⽤查其他剩余的分区。因为分区可以在创建了分区表后进⾏修改，所以在第⼀次配置分区⽅案时还不曾这么做时，可以重新组织数据，来提⾼那些常⽤查询的效率。
4）、涉及到例如SUM()和COUNT()这样聚合函数的查询，可以很容易地进⾏并⾏处理。这种查询的⼀个简单例⼦如 “SELECT salesperson_id, COUNT (orders) as order_tot al FROM sales GROUP BY salesperson_id ；”。通过“并⾏”，这意味着该查询可以在每个分区上同时进⾏，最终结果只需通过总计所有分区得到的结果。
5）、通过跨多个磁盘来分散数据查询，来获得更⼤的查询吞吐量。
三、分区类型
for循环指令· RANGE 分区：基于属于⼀个给定连续区间的列值，把多⾏分配给分区。
· LIST 分区：类似于按RANGE 分区，区别在于LIST 分区是基于列值匹配⼀个离散值集合中的某个值来进⾏选择。
· HASH 分区：基于⽤户定义的表达式的返回值来进⾏选择的分区，该表达式使⽤将要插⼊到表中的这些⾏的列值进⾏计算。这个函数可以包含MySQL 中有效的、产⽣⾮负整数值的任何表达式。
· KEY 分区：类似于按HASH 分区，区别在于KEY 分区只⽀持计算⼀列或多列，且MySQL 服务器提供其⾃⾝的哈希函数。必须有⼀列或多列包含整数值。
1.RANGE 分区
基于属于⼀个给定连续区间的列值，把多⾏分配给分区。这些区间要连续且不能相互重叠，使⽤VALUES LESS THAN
操作符来进⾏定义。以下是实例。 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) partition BY RANGE (store_id) ( partition p0 VALUES LESS THAN (6), partition p1 VALUES LESS THAN (11),
partition p2 VALUES LESS THAN (16),
partition p3 VALUES LESS THAN (21)
按照这种分区⽅案，在商店1到5⼯作的雇员相对应的所有⾏被保存在分区P0中，商店6到10的雇员保存在P1中，依次类推。注意，每个分区都是按顺序进⾏定义，从最低到最⾼。这是PARTITION BY R ANGE 语法的要求；在这点上，它类似于C 或Java 中的“switch ... case”语句。
对于包含数据(72, 'Michael', 'Widenius', '1998-06-25', NULL, 13)的⼀个新⾏，可以很容易地确定它将插⼊到p2分区中，但是如果增加了⼀个编号为第21的商店，将会发⽣什么呢？在这种⽅案下，由于没有规则把store_id ⼤于20的商店包含在内，服务器将不知道把该⾏保存在何处，将会导致错误。 要避免这种错误，可以通过在CREATE TABLE 语句中使⽤⼀个“catchall” VALUES LESS THAN ⼦句，该⼦句提供给所有⼤于明确指定的最⾼值的值：
MAXVALUE 表⽰最⼤的可能的整数值。现在，store_id 列值⼤于或等于16（定义了的最⾼值）的所有⾏都将保存在分区p3中。在将来的某个时候，当商店数已经增长到25, 30, 或更多 ，可以使⽤ALTE R TABLE 语句为商店21-25, 26-30，等等增加新的分区。
在⼏乎⼀样的结构中，你还可以基于雇员的⼯作代码来分割表，也就是说，基于job_code 列值的连续区间。例如——假定2位数字的⼯作代码⽤来表⽰普通（店内的）⼯⼈，三个数字代码表⽰办公室和⽀持⼈员，四个数字代码表⽰管理层，你可以使⽤下⾯的语句创建该分区表：
在这个例⼦中, 店内⼯⼈相关的所有⾏将保存在分区p0中，办公室和⽀持⼈员相关的所有⾏保存在分区p1中，管理层相关的所有⾏保存在分区p2中。
带有数字的组词
在
VALUES LESS THAN ⼦句中使⽤⼀个表达式也是可能的。这⾥最值得注意的限制是MySQL 必须能够计算表达式的返回值作为LESS THAN (<)⽐较的⼀部分；因此，表达式的值不能为NULL 。由于这个原因，雇员表的hired, separated, job_code，和store_id 列已经被定义为⾮空（NOT NULL ）。
除了可以根据商店编号分割表数据外，你还可以使⽤⼀个基于两个DATE （⽇期）中的⼀个的表达式来分割表数据。例如，假定你想基于每个雇员离开公司的年份来分割表，也就是说，YEAR(separ ated)的值。实现这种分区模式的CREATE TABLE 语句的⼀个例⼦如下所⽰：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01',
separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (store_id) ( PARTITION p0 VALUES LESS THAN (6), PARTITION p1 VALUES LESS THAN (11),
PARTITION p2 VALUES LESS THAN (16),
PARTITION p3 VALUES LESS THAN MAXVALUE
1
2
3
4
5
6
7
8
9
10
11
12
13
14 CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHA
R(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY RANGE (job_code) ( PARTITION p0 VALUES LESS THAN (100),
PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (10000)
在这个⽅案中，在1991年前雇佣的所有雇员的记录保存在分区p0中，1991年到1995年期间雇佣的所有雇员的记录保存在分区p1中， 1996年到2000年期间雇佣的所有雇员的记录保存在分区p2中，2000年后雇佣的所有⼯⼈的信息保存在p3中。
RANGE 分区在如下场合特别有⽤：
1）、 当需要删除⼀个分区上的“旧的”数据时，只删除分区即可。如果你使⽤上⾯最近的那个例⼦给出的分区⽅案，你只需简单地使⽤ “ALTER TABLE employees DROP PARTITION p0；”来删除所有在1991年前就已经停⽌⼯作的雇员相对应的所有⾏。对于有⼤量⾏的表，这⽐运⾏⼀个如“DELETE FROM employees WHERE YEAR (separated) <= 1990；”这样的⼀个DELETE 查询要有效得多。
2）、想要使⽤⼀个包含有⽇期或时间值，或包含有从⼀些其他级数开始增长的值的列。
3）、经常运⾏直接依赖于⽤于分割表的列的查询。例如，当执⾏⼀个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id ；”这样的查询时，MySQL 可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE ⼦句的任何记录。注释：这种优化还没有在MySQL 5.1源程序中启⽤，但是，有关⼯作正在进⾏中。
2.LIST 分区
类似于按RANGE 分区，区别在于LIST 分区是基于列值匹配⼀个离散值集合中的某个值来进⾏选择。
LIST
分区通过使⽤“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或⼀个基于某个列值、并返回⼀个整数值的表达式，然后通过“VALUES IN (value_list)”的⽅式来定义每个分区，其中“value_list”是⼀个通过逗号分隔的整数列表。
注释：在MySQL 5.1中，当使⽤LIST 分区时，有可能只能匹配整数列表。
假定有20个⾳像店，分布在4个有经销权的地区，如下表所⽰：
====================
地区 商店ID 号
------------------------------------
北区 3, 5, 6, 9, 17
东区 1, 2, 10, 11, 19, 20
西区 4, 12, 13, 14, 18
中⼼区 7, 8, 15, 16
====================
要按照属于同⼀个地区商店的⾏保存在同⼀个分区中的⽅式来分割表，可以使⽤下⾯的“CREATE TABLE”语句：
5
6 7
8
9
10
11
12
13
14
15 lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY RANGE (YEAR(separated)) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1996),
PARTITION p2 VALUES LESS THAN (2001),
PARTITION p3 VALUES LESS THAN MAXVALUE
1
2
3
4
5
6
7
8
9 CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR
(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT,
store_id INT
这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定西区的所有⾳像店都卖给了其他公司。那么与在西区⾳像店⼯作雇员相关的所有记录（⾏）可以使⽤查询“ALTER TABLE employ ees DROP PARTITION pWest ；”来进⾏删除，它与具有同样作⽤的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”⽐起来，要有效得多。
【要点】：如果试图插⼊列值（或分区表达式的返回值）不在分区值列表中的⼀⾏时，那么“INSERT”查询将失败并报错。例如，假定LIST 分区的采⽤上⾯的⽅案，下⾯的查询将失败：这是因为“store_id”列值21不能在⽤于定义分区pNorth, pEast, pWest，或pCentral 的值列表中到。要重点注意的是，LIST 分区没有类似如“VALUES LESS THAN MAXVALUE”这样的包含其他值在内的定义。将要匹配的任何值都必须在值列表中到。
LIST 分区除了能和RANGE 分区结合起来⽣成⼀个复合的⼦分区，与HASH 和KEY 分区结合起来⽣成复合的⼦分区也是可能的。
3.HASH 分区
基于⽤户定义的表达式的返回值来进⾏选择的分区，该表达式使⽤将要插⼊到表中的这些⾏的列值进⾏计算。这个函数可以包含MySQL 中有效的、产⽣⾮负整数值的任何表达式。
要使⽤HASH 分区来分割⼀个表，要在CREATE TABLE 语句上添加⼀个“PARTITION BY HASH (expr)”⼦句，其中“expr”是⼀个返回⼀个整数的表达式。它可以仅仅是字段类型为MySQL 整型的⼀列的名字。此外，你很可能需要在后⾯再添加⼀个“PARTITIONS num”⼦句，其中num
是⼀个⾮负的整数，它表⽰表将要被分割成分区的数量。 5
6
7
8
9
10
11
12
13
14
15 lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT ) PARTITION BY LIST(store_id) PARTITION pNorth VALUES IN (3,5,6,9,17),
PARTITION pEast VALUES IN (1,2,10,11,19,20),
PARTITION pWest VALUES IN (4,12,13,14,18),
PARTITION pCentral VALUES IN (7,8,15,16)
1 INSERT INTO employees VALUES(224, 'Linus', 'Torvalds', '2002-05-01', '2004-10-12', 42, 21
如果没有包括⼀个PARTITIONS ⼦句，那么分区的数量将默认为1。 例外： 对于NDB Cluster （簇）表，默认的分区数量将与簇数据节点的数量相同，
这种修正可能是考虑任何MAX_ROWS 设置，以便确保所有的⾏都能合适地插⼊到分区中。
1.)LINER HASH
MySQL 还⽀持线性哈希功能，它与常规哈希的区别在于，线性哈希功能使⽤的⼀个线性的2的幂（powers-of-two ）运算法则，⽽常规 哈希使⽤的是求哈希函数值的模数。线性哈希分区和常规哈希分区在语法上的唯⼀区别在于，在“PARTITION BY” ⼦句中添加“LINEAR”关键字。假设⼀个表达式expr, 当使⽤线性哈希功能时，记录将要保存到的分区是num 个分区中的分区N ，其中N 是根据下⾯的算法得到：
1. 到下⼀个⼤于num.的、2的幂，我们把这个值称为V ，它可以通过下⾯的公式得到：
2. V = POWER(2, CEILING(LOG(2, num)))
（例如，假定num 是13。那么LOG(2,13)就是3.7004397181411。 CEILING(3.7004397181411)就是4，则V = POWER(2,4), 即等于16）。
3. 设置
N = F(column_list) & (V - 1).
4. 当 N >= num:
· 设置 V = CEIL(V / 2)
· 设置 N = N & (V - 1)
例如，假设表t1，使⽤线性哈希分区且有4个分区，是通过下⾯的语句创建的：
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY LINEAR HASH( YEAR(col3) )
PARTITIONS 6;
现在假设要插⼊两⾏记录到表t1中，其中⼀条记录col3列值为'2003-04-14'，另⼀条记录col3列值为'1998-10-19'。第⼀条记录将要保存到的分区确定如下：
(3 >= 6 为假（FALSE ）: 记录将被保存到#3号分区中)第⼆条记录将要保存到的分区序号计算如下：
5
6
7
8
9
10
11 lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT )
PARTITION BY HASH(store_id)
PARTITIONS 4 1
2
3 4
5
6
7
8
9
10
11 CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT, store_id INT )
PARTITION BY LINEAR HASH(YEAR(hired))
headers是什么意思中文PARTITIONS 4 1
mysql查看所有存储过程2
3
4 V = POWER(2, CEILING(LOG(2,7))) = 8N = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
=
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论