范文健康探索娱乐情感热点
投稿投诉
热点动态
科技财经
情感日志
励志美文
娱乐时尚
游戏搞笑
探索旅游
历史星座
健康养生
美丽育儿
范文作文
教案论文
国学影视

MySQL分区表,为什么分区键必须是主键的一部分?

  随着业务的不断发展,数据库中的数据会越来越多,相应地,单表的数据量也会越到越大,大到一个临界值,单表的查询性能就会下降。
  这个临界值,并不能一概而论,它与硬件能力、具体业务有关。
  虽然在很多 MySQL 运维规范里,都建议单表不超过 500w、1000w。
  但实际上,我在生产环境,也见过大小超过 2T,记录数过亿的表,同时,业务不受影响。
  单表过大时,业务通常会考虑两种拆分方案:水平切分和垂直切分。  水平拆分 VS 垂直拆分
  水平切分,拆分的维度是行,一般会根据某种规则或算法将表中的记录拆分到多张表中。
  拆分后的表既可在一个实例,也可在多个不同实例中。如果是后者,又会涉及到分布式事务。
  垂直切分,拆分的维度是列,一般是将列拆分到多个业务模块中。这种拆分更多的是上层业务的拆分。
  从改造的复杂程度来说,前者小于后者。
  所以,在单表数据量过大时,业界用得较多的还是水平拆分。
  常见的水平拆分方案有:分库分表、分区表。
  虽然分库分表是一个比较彻底的水平拆分方案,但一方面,它的改造需要一定的时间;另一方面,它对开发的能力也有一定的要求。相对来说,分区表就比较简单,也无需业务改造。  分区表
  很多人可能会认为 MySQL 的优势在于 OLTP 应用,对于 OLAP 应用就不太适合,所以,也不太推荐分区表这种偏 OLAP 的特性。
  但实际上,对于某些业务类型,还是比较适合使用分区表的,尤其是那些有明显冷热数据之分,且数据的冷热与时间相关的业务。
  下面,我们看看分区表的优点:  提升查询性能对于分区表的查询操作,如果查询条件中包含分区键,则这个查询操作就只会被下推到符合条件的分区内进行,无关分区将自动过滤掉。在数据量比较大的情况下,能提升查询速度。  对业务透明将表从一个非分区表转换为分区表,业务端无需做任何改造。  管理方便在对单个分区进行删除、迁移和维护时,不会影响到其它分区。尤其是针对单个分区的删除(DROP)操作,避免了针对这个分区所有记录的 DELETE 操作。
  遗憾的是,MySQL 分区表不支持并行查询。理论上,当一个查询涉及到多个分区时,分区与分区之间应进行并行查询,这样才能充分利用多核 CPU 资源。
  但 MySQL 并不支持,包括早期的官方文档,也提到了这个问题,也将这个功能的实现放到了优先级列表中。  These features are not currently implemented in MySQL Partitioning, but are high on our list of priorities.  - Queries involving aggregate functions such as SUM() and COUNT() can easily be parallelized. A simple example of such a query might be SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id;. By "parallelized," we mean that the query can be run simultaneously on each partition, and the final result obtained merely by summing the results obtained for all partitions.  - Achieving greater query throughput in virtue of spreading data seeks over multiple disks. MySQL 8.0 中分区表的变化
  在 MySQL 5.7 中,对于分区表,有个很重大的更新,即 InnoDB 存储引擎原生支持了分区,无需再通过 ha_partition 接口来实现。
  所以,在 MySQL 5.7 中,如果要创建基于 MyISAM 存储引擎的分区表,会提示 warning 。  The partition engine, used by table "sbtest.t_range", is deprecated and will be removed in a future release. Please use native partitioning instead.
  而在 MySQL 8.0 中,则更为彻底,server 层移除了 ha_partition 接口代码。
  如果要使用分区表,只能使用支持原生分区的存储引擎。在 MySQL 8.0 中,就只有 InnoDB。
  这就意味着,在 MySQL 8.0 中,如果要创建 MyISAM 分区表,基本上就不可能了。
  这也从另外一个角度说明了为什么生产上不建议使用 MyISAM 表。  mysql> CREATE TABLE t_range (     ->     id INT,     ->     name VARCHAR(10)     -> ) ENGINE = MyISAM     -> PARTITION BY RANGE (id) (     ->     PARTITION p0 VALUES LESS THAN (5),     ->     PARTITION p1 VALUES LESS THAN (10)     -> ); ERROR 1178 (42000): The storage engine for the table doesn"t support native partitioning 为什么分区键必须是主键的一部分?
  在使用分区表时,大家常常会碰到下面这个报错。  mysql> CREATE TABLE opr (     ->     opr_no INT,     ->     opr_date DATETIME,     ->     description VARCHAR(30),     ->     PRIMARY KEY (opr_no)     -> )     -> PARTITION BY RANGE COLUMNS (opr_date) (     ->     PARTITION p0 VALUES LESS THAN ("20210101"),     ->     PARTITION p1 VALUES LESS THAN ("20210102"),     ->     PARTITION p2 VALUES LESS THAN MAXVALUE     -> ); ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table"s partitioning function (prefixed columns are not considered).
  即分区键必须是主键的一部分。
  上面的 opr 是一张操作流水表。其中,opr_no 是操作流水号,一般都会被设置为主键,opr_date 是操作时间。基于操作时间来进行分区,是一个常见的分区场景。
  为了突破这个限制,可将 opr_date 作为主键的一部分。  mysql> CREATE TABLE opr (     ->     opr_no INT,     ->     opr_date DATETIME,     ->     description VARCHAR(30),     ->     PRIMARY KEY (opr_no, opr_date)     -> )     -> PARTITION BY RANGE COLUMNS (opr_date) (     ->     PARTITION p0 VALUES LESS THAN ("20210101"),     ->     PARTITION p1 VALUES LESS THAN ("20210102"),     ->     PARTITION p2 VALUES LESS THAN MAXVALUE     -> ); Query OK, 0 rows affected (0.04 sec)
  但是这么创建,又会带来一个新的问题,即对于同一个 opr_no ,可插入到不同分区中。如下所示:  mysql> insert into opr values(1,"2020-12-31 00:00:01","abc"); Query OK, 1 row affected (0.00 sec)  mysql> insert into opr values(1,"2021-01-01 00:00:01","abc"); Query OK, 1 row affected (0.00 sec)  mysql> select * from opr partition (p0); +--------+---------------------+-------------+ | opr_no | opr_date            | description | +--------+---------------------+-------------+ |      1 | 2020-12-31 00:00:01 | abc         | +--------+---------------------+-------------+ 1 row in set (0.00 sec)  mysql> select * from opr partition (p1); +--------+---------------------+-------------+ | opr_no | opr_date            | description | +--------+---------------------+-------------+ |      1 | 2021-01-01 00:00:01 | abc         | +--------+---------------------+-------------+ 1 row in set (0.00 sec)
  这实际上违背了业务对于 opr_no 的唯一性要求。
  既然这样,有的童鞋会建议给 opr_no 添加个唯一索引,But,现实是残酷的。  mysql> create unique index uk_opr_no on opr (opr_no); ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table"s partitioning function (prefixed columns are not considered)
  即便是添加唯一索引,分区键也必须包含在唯一索引中。
  总而言之,对于 MySQL 分区表,无法从数据库层面保证非分区列在表级别的唯一性,只能确保其在分区内的唯一性。
  这也是 MySQL 分区表所为人诟病的地方之一。
  但实际上,这个锅让 MySQL 背并不合适,对于 Oracle 索引组织表( InnoDB 即是索引组织表),同样也有这个限制。
  Oracle 官方文档( http://docs.oracle.com/cd/E11882_01/server.112/e40540/schemaob.htm#CNCPT1514),在谈到索引组织表(Index-Organized Table,简称 IOT)的特性时,就明确提到了 "分区键必须是主键的一部分"。  Note the following characteristics of partitioned IOTs:     - Partition columns must be a subset of primary key columns.    - Secondary indexes can be partitioned locally and globally.    - OVERFLOW data segments are always equipartitioned with the table partitions.
  下面,我们看看刚开始的建表 SQL ,在 Oracle 中的执行效果。  SQL> CREATE TABLE opr_oracle (   2      opr_no NUMBER,   3      opr_date DATE,   4      description VARCHAR2(30),   5      PRIMARY KEY (opr_no)   6  )   7  ORGANIZATION INDEX   8  PARTITION BY RANGE (opr_date) (   9      PARTITION p0 VALUES LESS THAN (TO_DATE("20170713", "yyyymmdd")),  10      PARTITION p1 VALUES LESS THAN (TO_DATE("20170714", "yyyymmdd")),  11      PARTITION p2 VALUES LESS THAN (MAXVALUE)  12  ); PARTITION BY RANGE (opr_date) (                     * ERROR at line 8: ORA-25199: partitioning key of a index-organized table must be a subset of the primary key
  同样报错。
  注意,这里指定了 ORGANIZATION INDEX ,创建的是索引组织表。
  看来,分区键必须是主键的一部分并不是 MySQL 的限制,而是索引组织表的限制。
  之所以对索引组织表有这样的限制,个人认为,还是基于性能考虑。
  假设分区键和主键是两个不同的列,在进行插入操作时,虽然也指定了分区键,但还是需要扫描所有分区才能判断插入的主键值是否违反了唯一性约束。这样的话,效率会比较低下,违背了分区表的初衷。
  而对于堆表则没有这样的限制。
  在堆表中,主键和表中的数据是分开存储的,在判断插入的主键值是否违反唯一性约束时,只需利用到主键索引。
  但与 MySQL 不一样的是,Oracle 实现了全局索引,所以针对上面的,同一个 opr_no,允许插入到不同分区中的问题,可通过全局唯一索引来规避。  SQL> CREATE TABLE opr_oracle (   2      opr_no NUMBER,   3      opr_date DATE,   4      description VARCHAR2(30),   5      PRIMARY KEY (opr_no, opr_date)   6  )   7  ORGANIZATION INDEX   8  PARTITION BY RANGE (opr_date) (   9      PARTITION p0 VALUES LESS THAN (TO_DATE("20170713", "yyyymmdd")),  10      PARTITION p1 VALUES LESS THAN (TO_DATE("20170714", "yyyymmdd")),  11      PARTITION p2 VALUES LESS THAN (MAXVALUE)  12  );  Table created.  SQL> create unique index uk_opr_no on opr_oracle (opr_no);  Index created.  SQL> insert into opr_oracle values(1,to_date("2020-12-31 00:00:01","yyyy-mm-dd hh24:mi:ss"),"abc");  1 row created.  SQL> insert into opr_oracle values(1,to_date("2020-12-31 00:00:01","yyyy-mm-dd hh24:mi:ss"),"abc"); insert into opr_oracle values(1,to_date("2020-12-31 00:00:01","yyyy-mm-dd hh24:mi:ss"),"abc") * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_IOT_TOP_87350) violated
  但 MySQL 却无能为力,之所以会这样,是因为 MySQL 分区表只实现了本地分区索引(Local Partitioned Index),而没有实现 Oracle 中的全局索引(Global Index)。  本地分区索引 VS 全局索引
  本地分区索引和全局索引的原理图如下所示:
  结合原理图,我们来看看两种索引之间的区别:  本地分区索引同时也是分区索引,分区索引和表分区之间是一一对应的。而全局索引,既可以是分区的,也可以是不分区的。如果是全局分区索引,一个分区索引可对应多个表分区,同样,一个表分区也可对应多个分区索引。  对本地分区索引的管理操作只会影响到单个分区,不会影响到其它分区。而对全局分区索引的管理操作会造成整个索引的失效,当然,这一点可通过 UPDATE INDEXES 子句加以规避。  本地分区索引只能保证分区内的唯一性,无法保证表级别的唯一性,但全局分区可以。  在 Oracle 中,无论是索引组织表还是堆表,如果要创建本地唯一索引,同样也要求分区键必须是唯一键的一部分。  SQL> create unique   index   uk_opr_no_local  on   opr_oracle(opr_no)  local  ; create unique  index   uk_opr_no_local  on   opr_oracle(opr_no)  local   * ERROR at line  1  : ORA- 14039  : partitioning columns must form a subset  of   key columns  of   a UNIQUE  index  总结
  1. MySQL 分区表关于"分区键必须是唯一键(主键和唯一索引)的一部分"的限制,本质上是索引组织表的限制。
  2. MySQL 分区表只实现了本地分区索引,没有实现全局索引,所以无法保证非分区列的全局唯一。
  如果要保证非分区列的全局唯一,只能依赖业务实现了。
  3. 不推荐使用 MyISAM 分区表。当然,任何场景都不推荐使用 MyISAM 表。

小技巧网盘限速怎么办?一个操作让下载速度起飞爸妈在追剧老婆在Shopping孩子在线兴趣班用网设备这么多,带宽争抢网络势必很卡顿!这把开黑恐怕又是送人头咦?操作手感竟如此丝滑?稳如泰山快如闪电,全家共享互不影响电信千兆宽带的无线还要RGB?黑爵K620T做给你看哈喽大家好,我是你们的校长!今天给大家带来的是老朋友黑爵家的键盘K620T!说实话就我个人来讲,对于这块键盘关注了是有一段时间了,K620T先是在国外上线之后才进入国内市场,所以我想让Switch玩法更多样?往这里看哈喽大家好,我是你们的校长,今天给大家带来三款来自绿联的Switch配件,让各位能够更好地畅玩Switch首先就是Switch的钢化膜,平时带着Switch出门,难免会有磕磕碰碰,外设想整一套?那就选杜伽吧哈喽大家好,我是你们的校长!今天给大家带来的是杜伽的LEO600鼠标和P300墨曜鼠标垫!由于之前体验过了杜伽的K310RGBK320WFusion,所以这次来试试看杜伽的其他外设无线有线,全部整上!南卡POW2无线快充充电宝安排哈喽大家好,我是你们的校长,今天给大家带来的是来自南卡的POW2无线快充充电宝!随着科技的发展,现在的手机越来越多都支持快充和无线充电了,可是很多充电宝依然只支持10W的充电,且大音乐盛宴,四不可挡1MORE四单元圈铁耳机体验Part1开箱评测在2017年的5月16日,1MORE召开发布会正式发布了旗下的新款旗舰耳塞产品四单元圈铁耳塞,这款产品不仅仅是增加了一个动铁单元这么简单,在动圈单元方面技术也有巨苹果新机渲染图曝光,搭载A14仿生芯片,价格略有上涨苹果旗下硬件产品很多,像iPhoneiPad都是大家非常熟悉的产品了。从用户量来看,iPhone是远远大于iPad的,毕竟使用手机的用户是远远超过平板电脑用户的。iPhone基本上看长安欧尚X7PLUS,得从联发科的芯片说起9月12日,当答答应长安欧尚之邀赴重庆试驾时,距离长安欧尚X7PLUS预售刚好过去15天。这款今年4月份在上海车展亮相的紧凑级五座SUV,在8月底的成都车展将预售价确定为7。99万20003000元左右的性能比较高的苹果手机有那些?第一款iphone8plus这款手机采用a11处理器,在今天的这个时代里已经算是很不错了,屏幕是5。5英寸的,1920X1080的分辨率,后置摄像头是1200万双摄,前置摄像头70如何使用多IMSISIM为可管理的全球物联网部署铺平道路您知道吗,许多物联网项目要么无法像预期的那样快速达到其ROI,要么完全失败了。事实上,只有26的公司有一个他们认为完全成功的计划,60的计划甚至没有通过概念验证阶段。复杂性是罪魁祸星辰大海演绎追梦,演绎时代变迁星辰大海讲述了小镇孤女简爱的励志故事。从茶水间打工小妹一路拼搏,凭借自我的不懈奋斗,不断充电不断改变自己进入中国外贸行业,最后混成董事长。刘涛遭遇恋爱家庭双重虐心星辰大海以外贸行业
云上医疗云计算加速医疗数字化转型升级随着全球疫情的持续,人们对健康的重视,对疾病的治疗观念已经进入到治未病的阶段。随着医疗科技的发展,人们对于健康的重视使得精准医疗的发展进入快车道。其中,基因测序作为一项高效的医疗辅一位中年女人的感慨步入二婚生活才明白,再婚是真的很现实金星曾经说过生活是最好的编剧,温暖的画面时,背后都有脆弱的声音而显得是多么苍白无力,随后她为了补充这句话的完美,又说到女人,该给你的都会给你,还没给你的,等着也是一门学问!但是在爱甄嬛传,皇帝为什么面对病容不堪的端妃还喜欢宠幸她甄嬛传每次看都会有不一样的感觉,对每一位的宫嫔娘娘有着不同的感觉,今天想聊聊端妃,在开场就是一个谜一样的存在,端妃的存在虽然戏份不是很多,但都是关键时刻助甄嬛扭转乾坤,救甄嬛于危急甄嬛传后宫宫女的另类上位史,感慨宫闱之中无善人甄嬛传中宫嫔的上位史,充满了尔虞我诈,人情的冷漠,华妃皇后甄嬛之间的各种明争暗斗,为的是自己在后宫中获得皇上的宠幸,也为了家族的荣耀。但后宫中还有一个群体,那就是宫女和太监,他们的甄嬛传皇帝和后宫的小主们真的有感情么,皇帝真的付出过真心么甄嬛传以演员精湛的演技和精彩的剧情,让人百追不厌,为之津津乐道。但皇帝到底后宫需要什么女人,是真的需要相互交心的感情么。俗话说,三个女人一台戏,何况是后宫呢,其实第一集,皇帝选秀女云计算赋能园区管理智慧化升级作为智慧城市中重要的一环,智慧园区建设已成为未来发展趋势,信息化逐渐成为智慧园区建设的关键手段之一。目前,我国智慧园区建设已经初步呈现集群分布特征,已经初步形成东部沿海集聚中部沿江华硕推出64核服务器11路PCIe4。020万兆网卡华硕近年来也加强了工作站服务器业务,日前正式推出了最新一代的2U单插槽GPU服务器ESC4000AE10,配备了AMD64核的EPYC7002系列处理器,最多可支持11路PCIe4强过骁龙875安卓终于有一款能和苹果A13抗衡的处理器了日前有韩国网友抢先爆料了搭载骁龙875处理器的三星GalaxyS21和所谓小米11的跑分数据。测试平台为GeekBench5,整体来看,骁龙875单核最高提升18多核最高提升25,cadence16。6安装步骤详细cadencecadence安装起来还是比较容易的,但细节的安装需要一步一步来,下面以cadence16。6为例,安装安装前,最好把杀毒软件关闭了。首先下载cadence16。6安装包链接htt云上高校有孚网络如何推动高校信息化进程?当前,教育行业数字化进程正走向纵深,用新技术提升校园管理水平课堂教学效率科研成果转换,正在成为高校教育信息化新常态。云计算以互联网作为媒介,将资源集合共享变成现实,提供给用户安全便小米智能插座WiFi版轻松实现全屋智能!随着5GAIlot技术的高速发展,加快普及着智能家居。智能家居可以给生活带来无尽的乐趣和便利!然而有许多传统电器,不能实现智能控制。若体验智能家居,换掉传统电器又于心不忍。小米智能