MySQL千万级大表性能优化之表分区(运维DBA必回技能)

MySQL表分区介绍:
  • 可以允许在⼀个表⾥存储更多的数据,突破磁盘限制或者⽂件系统限制。

  • 对于从表⾥将过期或历史的数据移除在表分区很容易实现,只要将对应的分区移除即可。

  • 对某些查询和修改语句来说,可以⾃动将数据范围缩⼩到⼀个或⼏个表分区上,优化语句执⾏效率。⽽且可以通过显示指定表分区来执⾏语句,⽐如 select * from temp partition(p1,p2) where store_id < 5;

  • 表分区是将⼀个表的数据按照⼀定的规则⽔平划分为不同的逻辑块,并分别进⾏物理存储,这个规则就叫做分区函数,可以有不同的分区规则。

  • MySQL5.7版本可以通过show plugins语句查看当前MySQL是否⽀持表分区功能。

  • MySQL8.0版本移除了show plugins⾥对partition的显示,但社区版本的表分区功能是默认开启的

  • 但当表中含有主键或唯⼀键时,则每个被⽤作分区函数的字段必须是表中唯⼀键和主键的全部或⼀部分,否则就⽆法创建分区表。

MySQL表分区类型:

• range表分区:   范围表分区,按照⼀定的范围值来确定每个分区包含的数据
• list表分区:    列表表分区,按照⼀个⼀个确定的值来确定每个分区包含的数据
• hash表分区:    哈希表分区,按照⼀个⾃定义的函数返回值来确定每个分区包含的数据
• key表分区 :    key表分区,与哈希表分区类似,只是⽤MySQL⾃⼰的HASH函数来确定每个分区包含的数据
简单创建表分区:
# 创建temp表,然后指定 id 主键字段设置‘range 范围’分区规则
# 指定 id字段值小于 6 的数据行存放在 p1 分区中
# 指定 id字段值小于 11 大于等于 6  的数据行存放在 p2 分区中
mysql> create table temp(id int primary key auto_increment not null,
       name varchar(64) not null)
       partition by range(id)
       (partition p1 values less than (6),partition p2 values less than (11));
Query OK, 0 rows affected (0.07 sec)

# 插入两条数据,分别 id为 1 与 6
mysql> insert into temp values(1,'aa'),(6,'bb');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

# where条件查询 1 就只会遍历 p1这个表分区
mysql> desc select * from temp where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | temp  | p1         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# where条件查询 6 就只会遍历 p2这个表分区
mysql> desc select * from temp where id=6;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | temp  | p2         | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 同时查询多个分区中的数据就会遍历多个分区
mysql> desc select * from temp where id in (1,6);
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | temp  | p1,p2      | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# MySQL物理数据分区数据文件,表示物理分区存储数据
[root@olda-study ~]# ll -sh /usr/local/mysql-8.0.16/data/course/temp*
80K -rw-r----- 1 mysql mysql 112K Oct 26 12:05 /usr/local/mysql-8.0.16/data/course/temp#P#p1.ibd
80K -rw-r----- 1 mysql mysql 112K Oct 26 12:05 /usr/local/mysql-8.0.16/data/course/temp#P#p2.ibd
指定分区查询或修改数据:
限制以下用户组阅读此隐藏内容

请先登录

您的用户组:
「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论