当MYSQL流行5年后,表的数量达到500万,RDS运行时很多SQL都会超过2秒.
在如今互联网业务中,一个页面展现超过10-15秒人们就觉得慢,感觉不耐烦,除非你是GOV的,否则都抛弃你公司,转而使用竞争对手的产品。
为此落实在数据库端的SQL响应时间就缩短到1-5秒时间范围。 目前一个简单的DML操作是规定在1秒之内，而查询一般规定在5秒之内。也就是说给DBA的时间不多了！很显然JAVA开发同学都是赶业务的，开始都是简单地搞起来，哪里想得到日后数量大的情况呢？
为此我们必须上分区。MYSQL也有分区，虽然被人喷得不要不要地。那是自然跟ORACLE分区前辈比了。MYSQL分区常用的是范围，LIST，HASH分区。其他什么KEY就不用去理解理解了！
RDS范围分区只支持数字！
一 创建数据库
mysql> create database books
-> ;
Query OK, 1 row affected (1.12 sec)
mysql> use books;
Database changed
二 创建分区表
mysql> CREATE TABLE trb3 (id INT, name VARCHAR(50), purchased DATE)
-> PARTITION BY RANGE( YEAR(purchased) ) (
-> PARTITION p0 VALUES LESS THAN (1990),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (2000),
-> PARTITION p3 VALUES LESS THAN (2005)
-> );
Query OK, 0 rows affected (7.73 sec)
RDS要使用TODAYS(DATATIME)来转化成数字，TODAYS是距离公元0000年以来的天数。最终你看到建表语句如下不一样的地方
/*!50100 PARTITION BY RANGE (TO_DAYS(Createtime)) PARTITION createtime_20210812 VALUES LESS THAN (738394) ENGINE = InnoDB, 738392
插入数据
INSERT INTO trb3 VALUES
(1, 'desk organiser', '2003-10-15'),
(2, 'alarm clock', '1997-11-05'),
(3, 'chair', '2004-03-10'),
(4, 'bookcase', '1989-01-10'),
(5, 'exercise bike', '2004-05-09'),
(6, 'sofa', '1987-06-05'),
(7, 'espresso maker', '2001-11-22'),
(8, 'aquarium', '1992-08-04'),
(9, 'study desk', '2003-09-16'),
(10, 'lava lamp', '1998-12-25');
分区常用操作
访问分区表
SELECT * FROM trb3 WHERE purchased BETWEEN '1995-01-01' AND '1999-12-31';
指定访问某分区 注意小挂号
SELECT * FROM trb3 PARTITION (createtime_20210812);
删除分区
ALTER TABLE trb3 DROP PARTITION createtime_20210812;
末尾追加分区
ALTER TABLE trb3 ADD PARTITION (PARTITION createtime_20210812 VALUES LESS THAN (2010));
清空分区
alter table trb3 truncate partition createtime_20210812;
移除分区
alter table trb3 remove partitioning;
重新拆分分区
ALTER TABLE trb3
REORGANIZE PARTITION createtime_20210812 INTO (
PARTITION createtime_19700812 VALUES LESS THAN (todays(1970)),
PARTITION createtime_19800812 VALUES LESS THAN (today(1980))
分区转普通表
ALTER TABLE trb3 EXCHANGE PARTITION createtime_20210812 WITH TABLE trb5;
普通表转分区表
1 ALTER TABLE `trb3` DROP PRIMARY KEY, ADD PRIMARY KEY(`id`,`createtime`);
2 alter table trb3 PARTITION BY range(CreateTime)(PARTITION createtime_20200812 VALUES LESS THAN (20160101),
...............
查看表分区信息
SELECT
table_schema,table_name,partition_name,partition_ordinal_position,table_rows,partition_description,
substring(partition_name,length(partition_name)-7,8) as leav_time, ##获取分区名最后8位时间数字
TO_DAYS(substring(partition_name,length(partition_name)-7,8)) as days, ##把8位时间数字转化成 天数
date_format(substring(partition_name,length(partition_name)-7,8),'%Y-%m-%d') as time
FROM
INFORMATION_SCHEMA.partitions
WHERE TABLE_SCHEMA = 'books'
and partition_name is not null
and TABLE_NAME='trb3'
检查分区操作时锁
查看元数据锁
mysql> select * from performance_schema.metadata_locks where OWNER_THREAD_ID != sys.ps_thread_id(connection_id());
+-------------+---------------+-----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| GLOBAL | NULL | NULL | NULL | 140397650075728 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5436 | 47 | 13 |
| BACKUP LOCK | NULL | NULL | NULL | 140397651412576 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5443 | 47 | 13 |
| SCHEMA | books | NULL | NULL | 140397651586416 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5423 | 47 | 13 |
| TABLE | books | trb3 | NULL | 140397651796624 | SHARED | TRANSACTION | GRANTED | sql_parse.cc:5768 | 47 | 13 |
| GLOBAL | NULL | NULL | NULL | 140397651871248 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:454 | 47 | 14 |
| TABLESPACE | NULL | books/trb3#p#p0 | NULL | 140397651888976 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:435 | 47 | 14 |
+-------------+---------------+-----------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
6 rows in set (0.01 sec)
删除分区ing...
查看元数据锁
mysql> select *
from performance_schema.metadata_locks
where OWNER_THREAD_ID != sys.ps_thread_id(connection_id());
+-------------+---------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
| GLOBAL | NULL | NULL | NULL | 140397651938256 | INTENTION_EXCLUSIVE | STATEMENT | GRANTED | sql_base.cc:5436 | 47 | 27 |
| BACKUP LOCK | NULL | NULL | NULL | 140397651938160 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5443 | 47 | 27 |
| SCHEMA | books | NULL | NULL | 140397651938064 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | sql_base.cc:5423 | 47 | 27 |
| TABLE | books | trb3 | NULL | 140397651936496 | SHARED_UPGRADABLE | TRANSACTION | GRANTED | sql_parse.cc:5768 | 47 | 27 |
| TABLESPACE | NULL | books/trb3#p#p0 | NULL | 140397649990656 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:802 | 47 | 27 |
| TABLESPACE | NULL | books/trb3#p#p1 | NULL | 140397649950080 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:802 | 47 | 27 |
| TABLESPACE | NULL | books/trb3#p#p2 | NULL | 140397650536864 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:802 | 47 | 27 |
| TABLESPACE | NULL | books/trb3#p#p3 | NULL | 140397652298064 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | lock.cc:802 | 47 | 27 |
| TABLE | books | #sql-2624_8 | NULL | 140397651656400 | EXCLUSIVE | STATEMENT | GRANTED | sql_table.cc:16522 | 47 | 27 |
| GLOBAL | NULL | NULL | NULL | 140397650854432 | INTENTION_EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:454 | 47 | 28 |
| TABLE | dummy_sdi_db | SDI_4 | NULL | 140397652518688 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:435 | 47 | 28 |
| TABLESPACE | NULL | books/trb3#p#p2 | NULL | 140397650854336 | EXCLUSIVE | TRANSACTION | GRANTED | dictionary_impl.cc:435 | 47 | 28 |
| TABLE | mysql | tablespace_files | NULL | 140397652444064 | SHARED_READ | TRANSACTION | GRANTED | table.h:2774 | 47 | 28 |
+-------------+---------------+------------------+-------------+-----------------------+---------------------+---------------+-------------+------------------------+-----------------+----------------+
13 rows in set (0.02 sec)
删除分区对本分区添加独占锁,其他分区添加意向独占锁,对表共享升级锁,后面还有源代码所在的文件名
意向锁(Intention Locks)
InnoDB为了支持多粒度锁机制(multiple granularity locking)，即允许行级锁与表级锁共存，而引入了意向锁(intention locks)。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。
意向锁是一个表级别的锁(table-level locking)；
意向锁又分为：
意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁；
意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁
虽然意向锁之间互相兼容，但是它与共享锁/排它锁互斥，其兼容互斥表如下:
| S | X | |
| IS | 兼 容 | 互 斥 |
| IX | 互 斥 | 互 斥 |
这么说来,删分区会导致其他分区添加IX锁,并且与X锁不兼容，MYSQL真大爷的！关别的分区什么鬼事。
文章转载自海鲨数据库架构师，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
