扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >MySQL的分区（二）
  • 205

0
分享到

MySQL的分区（二）

2024-04-02 19:04:59 205人浏览 薄情痞子
摘要

分区裁剪 使用explain partitions能显示出是否进行了分区裁剪. Mysql> drop table t2; Query OK, 0 rows affected (

分区裁剪

使用explain partitions能显示出是否进行了分区裁剪.

Mysql> drop table t2;

Query OK, 0 rows affected (2.90 sec)

mysql> CREATE TABLE t2 (

    ->     fname VARCHAR(50) NOT NULL,

    ->     lname VARCHAR(50) NOT NULL,

    ->     region_code TINYINT UNSIGNED NOT NULL,

    ->     dob DATE NOT NULL

    -> )

    -> PARTITION BY RANGE( YEAR(dob) ) (

    ->     PARTITION d0 VALUES LESS THAN (1970),

    ->     PARTITION d1 VALUES LESS THAN (1975),

    ->     PARTITION d2 VALUES LESS THAN (1980),

    ->     PARTITION d3 VALUES LESS THAN (1985),

    ->     PARTITION d4 VALUES LESS THAN (1990),

    ->     PARTITION d5 VALUES LESS THAN (2000),

    ->     PARTITION d6 VALUES LESS THAN (2005),

    ->     PARTITION d7 VALUES LESS THAN MAXVALUE

    -> );

Query OK, 0 rows affected (1.19 sec)

mysql> explain partitions SELECT * FROM t2 WHERE dob = '1982-06-23';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | t2    | d3         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 2 warnings (0.10 sec)

mysql>  explain partitions SELECT * FROM t2 WHERE year(dob) = 1972;

+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions              | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | t2    | d0,d1,d2,d3,d4,d5,d6,d7 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+-------------------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 2 warnings (0.01 sec)

与oracle不同的是，不需要考虑分区键的函数（year）。使用year（）进行查询时，反而无法进行裁剪。

分区裁剪可以用于delete、update、select。insert操作也会自动选择分区。

mysql> explain partitions UPDATE t2 SET region_code = 8 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | UPDATE      | t2    | d5         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.38 sec)

mysql> explain partitions DELETE FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21';

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | DELETE      | t2    | d3,d4,d5   | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.41 sec)

mysql> explain partitions SELECT * FROM t2 WHERE dob < '1982-12-01';

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | t2    | d0,d1,d2,d3 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 2 warnings (0.00 sec)

使用不合法的日期是，执行计划也进行了分区裁剪，但实际查不到数据：

mysql> explain partitions SELECT * FROM t2 WHERE dob < '1982-12-00';

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions  | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | SIMPLE      | t2    | d0,d1,d2,d3 | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+-------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 5 warnings (0.00 sec)

mysql> select * from t4 where datecol <date '2000-01-01';

+----+------------+

| id | datecol    |

+----+------------+

|  1 | 1995-02-21 |

|  4 | 1996-03-14 |

|  5 | 1995-03-11 |

|  6 | 1997-05-07 |

|  9 | 1997-05-27 |

| 13 | 1996-02-06 |

| 22 | 1998-12-28 |

| 27 | 1997-10-28 |

| 29 | 1996-02-17 |

|  7 | 1999-03-01 |

| 12 | 1999-09-15 |

| 20 | 1999-03-11 |

| 21 | 1999-09-17 |

| 25 | 1999-03-03 |

| 26 | 1999-06-20 |

+----+------------+

15 rows in set (0.00 sec)

mysql> select * from t4 where datecol <date '2000-01-00';

ERROR 1525 (HY000): Incorrect DATE value: '2000-01-00'

不仅range分区可以裁剪，list、hash等分区也可以。如：

mysql> CREATE TABLE t8 (

    ->     fname VARCHAR(50) NOT NULL,

    ->     lname VARCHAR(50) NOT NULL,

    ->     region_code TINYINT UNSIGNED NOT NULL,

    ->     dob DATE NOT NULL

    -> )

    -> PARTITION BY KEY(region_code)

    -> PARTITIONS 8;

Query OK, 0 rows affected (1.07 sec)

mysql> explain update t8 set fname='1' where region_code=7;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

|  1 | UPDATE      | t8    | p2         | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |

+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set (0.09 sec)

####################################################

分区表的查询

可以在以下语句中指定分区名称列表：

select、delete、insert、replace、update、load data、load xml

可以同时指定多个分区或子分区，名称可以无需、相互包含。如：

mysql> show create table employees_sub\G

*************************** 1. row ***************************

       Table: employees_sub

Create Table: CREATE TABLE `employees_sub` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `fname` varchar(25) NOT NULL,

  `lname` varchar(25) NOT NULL,

  `store_id` int(11) NOT NULL,

  `department_id` int(11) NOT NULL,

  PRIMARY KEY (`id`,`lname`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

mysql> INSERT INTO employees_sub   # re-use data in employees table

    -> SELECT * FROM employees;

Query OK, 18 rows affected (0.40 sec)

Records: 18  Duplicates: 0  Warnings: 0

mysql> select table_name,partition_name,subpartition_name,table_rows from infORMation_schema.partitions where table_name='employees_sub';

+---------------+----------------+-------------------+------------+

| table_name    | partition_name | subpartition_name | table_rows |

+---------------+----------------+-------------------+------------+

| employees_sub | p0             | p0sp0             |          4 |

| employees_sub | p0             | p0sp1             |          0 |

| employees_sub | p1             | p1sp0             |          5 |

| employees_sub | p1             | p1sp1             |          0 |

| employees_sub | p2             | p2sp0             |          5 |

| employees_sub | p2             | p2sp1             |          0 |

| employees_sub | p3             | p3sp0             |          4 |

| employees_sub | p3             | p3sp1             |          0 |

+---------------+----------------+-------------------+------------+

8 rows in set (0.40 sec)

mysql> select * from employees_sub partition(p0,p1sp0,p1);

+----+-------+----------+----------+---------------+

| id | fname | lname    | store_id | department_id |

+----+-------+----------+----------+---------------+

|  1 | Bob   | Taylor   |        3 |             2 |

|  2 | Frank | Williams |        1 |             2 |

|  3 | Ellen | Johnson  |        3 |             4 |

|  4 | Jim   | Smith    |        2 |             4 |

|  5 | Mary  | Jones    |        1 |             1 |

|  6 | Linda | Black    |        2 |             3 |

|  7 | Ed    | Jones    |        2 |             1 |

|  8 | June  | Wilson   |        3 |             1 |

|  9 | Andy  | Smith    |        1 |             3 |

+----+-------+----------+----------+---------------+

9 rows in set (0.01 sec)

其他几个语句的举例；

UPDATE employees PARTITION (p0) SET store_id = 2 WHERE fname = 'Jill';

REPLACE INTO employees PARTITION (p3) VALUES (20, 'Jan', 'Jones', 3, 2);

############################################################

分区的限制条件

分区中不允许使用存储过程、函数等，不能声明变量

分区表达式中可以使用算数运算符，但结果必须为整数或NULL

sql mode的修改可能导致分区表的中断或数据丢失，因此不要对其进行修改

分区表的性能受文件系统类型、字符集、磁盘转速、swap空间等因素影响。

一般应确保开启了large_files_support,并合理设置open_files_limit.

innodb引擎开启innodb_file_per_table可提高性能。

表的分区操作会在表上施加写锁

使用MyISAM引擎要比Innodb、NDB快

在5.7版本中，LOAD DATA使用缓存提高性能，每个分区使用130KB的buffer来提高性能。

最大分区数：8192，包括子分区

不支持查询缓存

innodb分区表不支持外键

alter table …… order by操作只对分区内的数据进行排序

表中的主键和唯一索引必须包含分区键的所有列。

您可能感兴趣的文档:

--结束END--

本文标题: MySQL的分区（二）

本文链接: https://lsjlt.com/news/46015.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

猜你喜欢
  • MySQL的分区（二）
    分区裁剪 使用explain partitions能显示出是否进行了分区裁剪. mysql> drop table t2; Query OK, 0 rows affected (...
    99+
    2024-04-02
  • mysql分区及分表（二）
                                mysql...
    99+
    2024-04-02
  • MySQL的分区（一）
    mysql支持范围分区（range）、列表分区（list）、column分区、哈希分区（hash）、key分区、字段列表分区等 以timestamp类型字段作为分区键进行范围分区，有两种方式： C...
    99+
    2024-04-02
  • 腾讯云TDSQL MySQL版 - 开发指南 二级分区
    TDSQL MySQL版 目前支持 Range 和 List 两种格式的二级分区，具体建表语法和 MySQL 分区语法类似。 二级分区语法 一级 Hash，二级 List 分区示例如下： MySQL [test]> CREATE TABL...
    99+
    2020-10-21
    腾讯云TDSQL MySQL版 - 开发指南 二级分区
  • mysql分区表：日期分区
    mysql分区表：日期分区 1.创建分区表2.查看分区3.添加分区4.存储过程：分区删除与创建5.事件定时6.触发器设计：子表每插入一行，总表获得一行7.创建索引8.添加枚举型字段 1.创建分区表 CREATE TAB...
    99+
    2023-08-21
    mysql 数据库
  • MySQL：分库分表与分区的区别和思考
    一.分分合合 说过很多次，不要拘泥于某一个技术的一点，技术是相通的。重要的是编程思想，思想是最重要的。当数据量大的时候，需要具有分的思想去细化粒度。当数据量太碎片的时候，需要具有合的思想来粗化粒度。 1.1 分 很多技术都运用了分...
    99+
    2017-07-09
    MySQL：分库分表与分区的区别和思考
  • MySQL分区之LIST分区详解
    目录介绍一、创建分区1.插入数据2.插入不在列表中的值二、分区管理1.增加分区2.合并分区3.拆分分区4.删除分区三、其它分区1.对时间字段进行分区四、移除表的分区参考：总结介绍 L...
    99+
    2024-04-02
  • MySQL分区之RANGE分区详解
    目录介绍一、RANGE分区1.创建分区2.性能分析3.增加分区4.删除分区5.拆分合并分区二、日期字段分区方法三、null值处理四、移除表的分区总结介绍 RANGE分区基于一个给定的...
    99+
    2024-04-02
  • MySQL分区之KEY分区详解
    目录介绍一、常规KEY二、LINEAR KEY三、分区管理四、移除表的分区总结介绍 KEY分区和HASH分区相似，但是KEY分区支持除text和BLOB之外的所有数据类型的分区，而H...
    99+
    2024-04-02
  • MySQL分区之HASH分区详解
    目录介绍一、常规HASH二、线性HASH(LINEAR HASH)三、分区管理1.合并分区2.增加分区四、移除表的分区总结介绍 基于给定的分区个数，将数据分配到不同的分区，HASH分...
    99+
    2024-04-02
  • MySQL分区之子分区详解
    目录介绍一、创建子分区1.不定义每个子分区2.定义每个子分区3.测试数据二、分区管理1.合并分区2.拆分分区3.删除分区三、错误的子分区创建四、移除表的分区总结介绍 子分区其实是对每...
    99+
    2024-04-02
  • MySQL COLUMNS分区
    介绍COLUMN分区是5.5开始引入的分区功能，只有RANGE COLUMN和LIST COLUMN这两种分区；支持×××、日期、字符串；RANGE和LIST的分区方式非常的相似。  C...
    99+
    2024-04-02
  • MySQL/MariaDB 分区
    查看表的创建命令SHOW CREATE TABLE my_table_name;查看表是否分区（Create_options 字段为 partitioned 或空）SHOW TABLE STATUS LIK...
    99+
    2024-04-02
  • centos7-mysql-分区
    分区跟分表类似,目的是将一张表分到不通的存储中,这样可以减少单一磁盘IO占用,提高数据库效率分区主要有两种形式水平分区对表的行进行分区,垂直分区减少表的宽度, 想使用分区,需要先查看当前是否支持s...
    99+
    2024-04-02
  • mysql分表分区的示例分析
    这篇文章给大家介绍mysql分表分区的示例分析，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。面对当今大数据存储，设想当mysql中一个表的总记录超过1000W，会出现性能的大幅度下降吗...
    99+
    2024-04-02
  • MySql之分区分表
    MySql之分区分表分表的概念分表：将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些表可以分布在同一块磁盘上，也可...
    99+
    2024-04-02
  • MySQL的分表和分区介绍
        在日常开发或维护中经常会遇到大表的情况，所谓的大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在查询和插入的时候耗时太长，性能低下，如果...
    99+
    2024-04-02
  • mysql中分表与分区的区别是什么
    这篇文章将为大家详细讲解有关mysql中分表与分区的区别是什么，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。一，什么是mysql分表，分区什么是分表，从表面...
    99+
    2024-04-02
  • MySQL分区表的分区原理及优缺点
    这篇文章主要介绍“MySQL分区表的分区原理及优缺点”，在日常操作中，相信很多人在MySQL分区表的分区原理及优缺点问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL分...
    99+
    2024-04-02
  • 三分频和二分频有什么区别
    三分频和二分频的区别有功能不同、频率差异、目的不同、相互关系等。详细介绍：1、三分频主要决定了外设的传输速度和稳定性，二分频主要决定了处理器的速度和性能；2、三分频通常以更低的频率表示，以兆赫兹为单位，二分频通常以更高的频率表示，以兆赫兹或...
    99+
    2023-08-18
软考高级职称资格查询
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作