年末整理十表分区

最新推荐文章于 2021-12-12 22:53:00 发布

zhengjunwei2007

最新推荐文章于 2021-12-12 22:53:00 发布

阅读量103

点赞数

分类专栏： WEB 文章标签： MySQL SQL 数据结构多线程 performance

本文链接：https://blog.csdn.net/zhengjunwei2007/article/details/83835946

版权

WEB 专栏收录该内容

35 篇文章 0 订阅

订阅专栏

获得分区信息
MySQL可以通过如下方式来获取分区表的信息:
Show create tabe tablename;      //表详细结构
show table status;     //表的各种参数状态
select * from information_schema.partitions where table_name = '' ；//通过数据字典来查看表的分区信息
explain partitions select * from table;   // 通过此语句来显示扫描哪些分区，及他们是如何使用的.

修改分区
修改部分分区：
由于我们平常使用的数据库大都是动态运行的，所以只对某个表分区进行修改就OK了。
可以对range或list表分区进行add或drop，也可以对hash或key分区表进行合并或分解。这些动作都在alter table语句里进行。

Reorganize partition关键字可以对表的部分分区或全部分区进行修改，并且不会丢失数据。
Splitting即分解一个已有分区：
Alter table orders_range
reorganize partition p0 into

(
partition n0 values less than(5000),
partition n1 values less than(10000)
);

Merge分区：像上面把p0分成n0和n1，现在在把2个合并为一个。
Alter table orders_range reorganize partition n0,n1 into
(
Partition p0 values less than(10000)
);

修改所有的分区：在into关键字之前或之后都指定多个分区
Alter table orders_range reorganize partition p0,p1,p2,p3,p4,p5 into
(
Partition r0 values less than(25000),
Partition r1 values less than(50000),
Partition r2 values less than(maxvalue)
);

Coalesce 合并分区：
Merge分区的另一种方法就是alter table….coalesce partition语句，你不能对hash或key分区进行删除
Alter table orders_key coalesce partition1;

Redefine重定义分区
Alter table orders_range partition by hash(id) partitions 4;

对分区进行删除 (删除、删除所有分区)
Drop 分区：
可以对range或list类型的分区通过drop partition 关键字进行删除
Alter table orders_range drop partition p0;

注意：
1.对这个分区进行删除时，你会把这个分区的所有数据进行删除，与delete语句相等；
2.在做alter table..drop partition时，必须有drop权限；
3.运行这个删除命令，它不会返回删除了的行，可以通过select count()语句查看。
如果想对多个分区进行删除，可以使用如下命令语句：Alter table orders_range drop partition p1,p2;

删除所有分区
通过如下命令语句删除表中所有分区，最后是一个正规表.
Alter table orders_range remove partitioning;

##########################################12-23##############
1. 只有MYISAM引擎的表支持 DATA DIRECTORY/INDEX DIRECTORY, 经过半天的折腾,在windows环境下使用MYISAM并设置不同的分区存储位置将会产生一个无法使用的表!尽管能创建成功!

2. 如果用来分区的字段不是主键, 那么该表将不能使用主键类型的字段及唯一索引字段,通过老外的文档的文档中,有一种变通的解决方法,也就是使用复合主键! 主键本身肯定是唯一的,那么再增加一个或两个字段,复合主键的值依然是唯一的,所以可以用来变通,但复合主键会给表带来什么影响,暂时没查到资料!

3. 分区后的数据在单机单进程的查询并看不出性能上有非常大的差异,甚至会出现分区表比未分区表更慢几毫秒,哪怕是一百万的数据!

4.分区后应该更注意查询条件与索引的使用! 命中率低下的话,也许分区的性能会更慢更差!
##############################################3

在MySQL5.1和以上版本都能够支持表的分区了，今天也试了一下。查询效率确实是很高，不过每跨一个分区基本就多一倍时间。

Mysql分区表局限性总结：
一、关于Partitioning Keys, Primary Keys, and Unique Keys的限制：
在5.1中分区表对唯一约束有明确的规定，每一个唯一约束必须包含在分区表的分区键（也包括主键约束）。
二、关于存储引擎的限制
MERGE引擎不支持分区，分区表也不支持merge。
FEDERATED引擎不支持分区。这限制可能会在以后的版本去掉。
CSV引擎不支持分区
BLACKHOLE引擎不支持分区
在NDBCLUSTER引擎上使用分区表，分区类型只能是KEY(or LINEAR KEY) 分区。
当升级MYSQL的时候，如果你有使用了KEY分区的表（不管是什么引擎，NDBCLUSTER除外），那么你需要把这个表dumped在reloaded。
分区表的所有分区或者子分区的存储引擎必须相同，这个限制也许会在以后的版本取消。
不指定任何引擎（使用默认引擎）。
所有分区或者子分区指定相同引擎。
三、关于函数的限制
在mysql5.1中建立分区表的语句中，只能包含下列函数：
ABS()
CEILING() and FLOOR() （在使用这2个函数的建立分区表的前提是使用函数的分区键是INT类型）
DAY()
DAYOFMONTH()
DAYOFWEEK()
DAYOFYEAR()
DATEDIFF()
EXTRACT()
HOUR()
MICROSECOND()
MINUTE()
MOD()
MONTH()
QUARTER()
SECOND()
TIME_TO_SEC()
TO_DAYS()
WEEKDAY()
YEAR()
YEARWEEK()
四、其他限制
4.1 对象限制
下面这些对象在不能出现在分区表达式
Stored functions, stored procedures, UDFs, or plugins.
Declared variables or user variables.
4.2 运算限制
支持加减乘等运算出现在分区表达式，但是运算后的结果必须是一个INT或者NULL。
|, &, ^, <<, >>, , ~ 等不允许出现在分区表达式。
4.3 sql_mode限制
官方强烈建议你在创建分区表后，永远别改变mysql的sql_mode。
因为在不同的模式下，某些函数或者运算返回的结果可能会不一样。
4.4 Performance considerations.(省略)

4.5 最多支持1024个分区，包括子分区。
当你建立分区表包含很多分区但没有超过1024限制的时候，
如果报错 Got error 24 from storage engine，那意味着你需要增大open_files_limit参数。

4.6 不支持外键。MYSQL中，INNODB引擎才支持外键。
4.7 不支持FULLTEXT indexes（全文索引），包括MYISAM引擎。
4.8 不支持spatial column types。
4.9 临时表不能被分区。
4.10 log table不支持分区。
5.11 分区键必须是INT类型，或者通过表达式返回INT类型，可以为NULL。
唯一的例外是当分区类型为KEY分区的时候，可以使用其他类型的列作为分区键（ BLOB or TEXT 列除外）。
5.12 分区键不能是一个子查询。 A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL

5.13 只有RANG和LIST分区能进行子分区。HASH和KEY分区不能进行子分区。

5.14 分区表不支持Key caches。
5.15 分区表不支持INSERT DELAYED.
5.17 分区表不支持mysqlcheck和myisamchk
在5.1.33版本中已经支持mysqlcheck和myisamchk

5.18 分区表的分区键创建索引，那么这个索引也将被分区。分区键没有全局索引一说。
5.19 在分区表使用ALTER TABLE … ORDER BY，只能在每个分区内进行order by。

############################################################
我按年月对数据表进行分区，如下命令

mysql> create table tx (
    -> id int,
    -> info_time timestamp
    -> )
    -> PARTITION BY RANGE(info_time div 100)
    -> (
    ->     PARTITION p_2008_11 VALUES LESS THAN (200812),
    ->     PARTITION p_2008_12 VALUES LESS THAN (200901),
    ->     PARTITION p_2009_01 VALUES LESS THAN (200902),
    ->     PARTITION p_2009_02 VALUES LESS THAN (200903),
    ->     PARTITION p_2009_03 VALUES LESS THAN (200904),
    ->     PARTITION p_2009_04 VALUES LESS THAN (200905),
    ->     PARTITION p_catch_all VALUES LESS THAN MAXVALUE
    -> );
Query OK, 0 rows affected (0.16 sec)

mysql>
####################################
1. SHOW CREATE TABLE
可以查看创建分区表的CREATE语句

2. SHOW TABLE STATUS
可以查看表是否为分区表

3. 查看INFORMATION_SCHEMA.PARTITIONS表
可以查看表具有哪几个分区、分区的方法、分区中数据的记录数等重要信息

4. EXPLAIN PARTITIONS SELECT
查看select语句怎样使用分区

###########################################################
mysql 分区表没有全局索引 by ivan
因此表分区要求涉及的列都包含在pk和uk里面
这样就可以保证插入和更新的值在某个分区里是唯一的时候，那么全局就一定是唯一的

但是带来的问题是，如果一张表有2列唯一索引的时候，这表就没法分区了。

利用分区表对mysql进行分区
查询的时候
如果是针对分区的列尽享查询则速度很快
如果是针对其他的列进行查询就很慢
比未分区的还要慢

这张表大约容量30G，数据库服务器内存16G，无法一次载入。就是这个造成了问题。
这条SQL有两个条件，ID一到五十万和Program_id一到四千，因为program_id范围小得多，mysql选择它做为主要索引。
先通过索引文件找出了所有program_id在1到4000范围里所有的id，这个过程非常快。
接下来要通过这些id找出表里的记录，由于这些id是离散的，所以mysql对这个表的访问不是顺序读取。
而这个表又非常大，无法一次装入内存，所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存，大量的IO操作导致了速度的下降。

#################################################################3
建议分两个库，一个库专门用来写的，另外一个用来读的，引擎是MYISAM

写入:bdb 内存,查询: myisam 分区

innodb 支持行锁 160笔/s 80个并发的话,每秒才2笔
估计每条记录不大,innodb用内存表应该能应付

如果innodb效果不好的话,用bdb(程序就需要修改了)

瓶颈不在io,在线程切换上

避免查询和插入争夺机器io