mysql分区表之三：MySQL分区建索引,唯一索引

article/2025/8/19 7:05:33

介绍

  • mysql分区后每个分区成了独立的文件，虽然从逻辑上还是一张表其实已经分成了多张独立的表，从“information_schema.INNODB_SYS_TABLES”系统表可以看到每个分区都存在独立的TABLE_ID,由于Innodb数据和索引都是保存在".ibd"文件当中（从INNODB_SYS_INDEXES系统表中也可以得到每个索引都是对应各自的分区(primary key和unique也不例外）），所以分区表的索引也是随着各个分区单独存储。

在INNODB_SYS_INDEXES系统表中type代表索引的类型;

  1. 一般的索引,
  2. (GEN_CLUST_INDEX)不存在主键索引的表,会自动生成一个6个字节的标示值，
  3. unique索引,
  4. primary索引;
  • 所以当我们在分区表中创建索引时其实也是在每个分区中创建索引，每个分区维护各自的索引（其实也就是local index）；
  • 对于一般的索引(非主键或者唯一)没什么问题由于索引树中只保留了索引key和主键key(如果存在主键则是主键的key否则就是系统自动生成的6个的key)不受分区的影响；
  • 但是如果表中存在主键就不一样了，虽然在每个分区文件中都存在主键索引但是主键索引需要保证全局的唯一性就是所有分区中的主键的值都必须唯一（唯一键也是一样的道理），所以在创建分区时如果表中存在主键或者唯一键那么分区列必须包含主键或者唯一键的部分或者全部列（全部列还好理解，部分列也可以个人猜测是为了各个分区和主键建立关系），由于需要保证全局性又要保证插入数据更新数据到具体的分区所以就需要将分区和主键建立关系,由于通过一般的索引进行查找其它非索引字段需要通过主键如果主键不能保证全局唯一性的话那么就需要去每个分区查找了，这样性能可想而知。

索引方式：

性能依次降低

1.主键分区

主键分区即字段是主键同时也是分区字段，性能最好

2. 部分主键+分区索引

使用组合主键里面的部分字段作为分区字段，同时将分区字段建索引（见下面详细说明）

3.分区索引

没有主键，只有分区字段且分区字段建索引

4.分区+分区字段没有索引

只建了分区，但是分区字段没有建索引

总结

因为每一个表都需要有主键这样可以减少很多锁的问题，由于上面讲过主键需要解决全局唯一性并且在插入和更新时可以不需要去扫描全部分区，造成主键和分区列必须存在关系；所以最好的分区效果是使用主键作为分区字段其次是使用部分主键作为分区字段且创建分区字段的索引，其它分区方式都建议不采取。

MYSQL的分区字段，必须包含在主键字段内

在对表进行分区时，如果分区字段没有包含在主键字段内，如表A的主键为ID,分区字段为createtime ，按时间范围分区，代码如下： 

CREATE TABLE T1 (id int(8) NOT NULL AUTO_INCREMENT,createtime datetime NOT NULL,PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))
(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p19 VALUES LESS ThAN  MAXVALUE);

复制代码

错误提示：#1503


MySQL主键的限制，每一个分区表中的公式中的列，必须在主键/unique key 中包括，在MYSQL的官方文档里是这么说明的

18.5.1. Partitioning Keys, Primary Keys, and Unique KeysThis section discusses the relationship of partitioning keys with primary keys and unique keys. The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have. In other words,every unique key on the table must use every columnin the table's partitioning expression. (This also includes the table's primary key, since it is by definition a unique key. This particular case is discussed later in this section.) For example, each of the following table creation statements is invalid:

分区字段必须包含在主键字段内，至于为什么MYSQL会这样考虑，CSDN的斑竹是这么解释的：

        为了确保主键的效率。否则同一主键区的东西一个在Ａ分区，一个在Ｂ分区，显然会比较麻烦。 下面讨论解决办法，毕竟在一张表里，日期做主键的还是不常见。 方法1： 顺应MYSQL的要求，就把分区字段加入到主键中，组成复合主键        

CREATE TABLE T1 (      
id INT ( 8 ) NOT NULL AUTO_INCREMENT,      
createtime datetime NOT NULL,      
PRIMARY KEY ( id, createtime )
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
PARTITION BY RANGE (TO_DAYS ( createtime ))(PARTITION p0 VALUES LESS THAN (TO_DAYS( '2010-04-15' )),PARTITION p1 VALUES LESS THAN (TO_DAYS( '2010-05-01' )),PARTITION p2VALUESLESS THAN (TO_DAYS( '2010-05-15' )),PARTITION p3VALUESLESS THAN (TO_DAYS( '2010-05-31' )),PARTITION p4VALUESLESS THAN (TO_DAYS( '2010-06-15' )),PARTITION p19VALUESLESS ThAN   MAXVALUE ); 

测试通过，分区成功。

 方法2： 既然MYSQL要把分区字段包含在主键内才能创建分区，那么在创建表的时候，先不指定主键字段，是否可以呢？？测试如下：

CREATE TABLE T1 (     id int(8) NOT NULL ,     createtime datetime NOT NULL      
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
PARTITION BY RANGE(TO_DAYS (createtime))(
PARTITION p0 VALUES LESS THAN (TO_DAYS('2010-04-15')),
PARTITION p1 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2010-05-15')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-05-31')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-06-15')),
PARTITION p19 VALUES LESS ThAN  MAXVALUE
);

测试通过，分区成功。

OK继续添加上主键

alter table t1 add PRIMARY KEY(ID)

错误1503，和前面一样的错误。

alter table t1 add PRIMARY KEY(ID,createtime)

创建主键成功，但还是复合主键，看来是没办法了，必须听指挥了。

主键创建成功，把ID加上自增字段设置

alter table t1 change id id int not null auto_increment;
alter table t1 auto_increment=1; 

最后结论，MYSQL的分区字段，必须包含在主键字段内。 

分区表中创建唯一索引：

例如，按create_time进行月分区的表里，唯一索引可能是orderNo，按照上面的要求，唯一索引就成为(order_no,create_time)了。但这样不满足业务需求。

解决办法：

为分区表增加一个before insert触发器，在插入前查询下是否已存在即可。

CREATE TRIGGER `trig_insert_t_order` BEFORE INSERT ON `t_order` FOR EACH ROW BEGINDECLARE v_count TINYINT UNSIGNED;DECLARE v_mess_str varchar(100);SELECT COUNT(1) INTO @v_countFROM t_orderWHERE order_no = new.order_noand new.create_time>=date_sub(SYSDATE(),INTERVAL 2 DAY);  ## 2天是否足够IF (@v_count > 0) THENSELECT concat('Duplicate entry ',new.order_no) INTO @v_mess_str;SIGNAL SQLSTATE '23000' SET MESSAGE_TEXT = @v_mess_str, MYSQL_ERRNO = 1022;END IF;END;

复制代码


http://chatgpt.dhexx.cn/article/lkyvwdCd.shtml

相关文章

分享mysql创建索引的3种方法

分享mysql创建索引的3种方法

大家应该都知道索引的建立对于MySQL数据库的高效运行是很重要的,索引可以大大提升MySQL的检索速度,下面这篇文章主要给大家介绍了关于mysql创建索引的3种方法,需要的朋友可以参考下 1、使用CREATE INDEX创建，语法如下： 1 CREATE INDEX indexName ON tab…
阅读更多...
js 监听浏览器刷新还是关闭事件

js 监听浏览器刷新还是关闭事件

// $(window).bind(beforeunload,function(){return 您输入的内容尚未保存，确定离开此页面吗？;}); // window.onbeforeunload function() { return "确定离开此页面吗？"; }; // function myFunction() {return "自定…
阅读更多...
浏览器刷新和页面手动为什么不一样？

浏览器刷新和页面手动为什么不一样？

Fiddler(2):AutoResponse修改返回结果_mb5fed6ec4336ce的技术博客_51CTO博客Fiddler(2):AutoResponse修改返回结果，前言怎么修改接口的返回数据呢步骤1.抓包，找到要拦截的请求，然后在AutoResponder中AddRule：2.在RuleEditor中的第…
阅读更多...
vue监听浏览器刷新和关闭事件，并在页面关闭/刷新前发送请求

vue监听浏览器刷新和关闭事件，并在页面关闭/刷新前发送请求

vue监听浏览器刷新和关闭事件，并在页面关闭/刷新前发送请求 1.需求背景：2.需求分析：3.实现方式：4.实现方式解析：1）浏览器页面事件基础2）在mounted监听beforeunload和unload事件 5.存在的问题/风…
阅读更多...
浏览器刷新和关闭时显示提示信息

浏览器刷新和关闭时显示提示信息

vue 刷新和关闭浏览器时显示提示信息 使用onbeforeunload事件 mounted() {window.onbeforeunload e > {e e || window.eventif (e) {e.returnValue 关闭提示}return 关闭提示}} }, beforeDestroy() {window.onbeforeunload null },如果是所有页面都需要页面销毁显示提…
阅读更多...
【Vue实用功能】Vue监听浏览器刷新和关闭事件

【Vue实用功能】Vue监听浏览器刷新和关闭事件

Vue监听浏览器刷新和关闭事件 在前端开发中，我们通常会遇到这样的需求，用户离开、刷新页面前，修改数据未进行保存操作，需要提示框提醒用户 效果实现 methods: {/** 在刷新和关闭之前询问 **/beforeRefreshClose() {let self t…
阅读更多...
vue监听浏览器刷新和关闭；

vue监听浏览器刷新和关闭；

注意&#xff1a;区分不了浏览器是触发了刷新还是关闭&#xff0c;而且提示的弹框是无法自定义的&#xff1b;如果有大佬有方法能区分&#xff0c;还请评论学习一下&#xff01;感谢&#xff01; 代码可直接复制&#xff1a; <template><div><div /></di…
阅读更多...
JS阻止浏览器刷新的方法

JS阻止浏览器刷新的方法

直接先给朋友们上阻止浏览器刷新的代码 <!DOCTYPE html> <html lang"en"> <head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width, initial-scale1.0"><meta http-equiv&quo…
阅读更多...
VSCODE同步浏览器刷新

VSCODE同步浏览器刷新

VSCODE同步浏览器刷新 安装插件 live server
阅读更多...
java中foreach的用法

java中foreach的用法

文章目录 前言语法用法用法1&#xff1a;输出一维数组用法2&#xff1a;输出二维数组foreach的局限性什么是索引 总结 前言 java中foreach,可以认为是增强版的for语句循环&#xff0c;它可以减少代码量&#xff0c;但是不是所有的foreach都可以代替for循环。 语法 foreach的…
阅读更多...
JAVA实现九九乘法表

JAVA实现九九乘法表

用java语言实现九九乘法表&#xff0c;这里使用的是for循环 public class NineNineDemo{public static void main(String[] args){int i1;//对行变量赋值int j1;//对列变量赋值for(i1;i<9;i){for(j1;j<i;j){//行变量外循环&#xff1b;列变量内循环System.out.print(i&q…
阅读更多...
Java的ASCII编码表

Java的ASCII编码表

数字和字符的对照关系表&#xff08;编码表&#xff09;&#xff1a; ASCII码表&#xff1a;American Standard Code for Information Interchange, 美国信息交换标准代码。 Unicode码表&#xff1a;万国码。也是数字和符号的对照关系&#xff0c;开头0-127部分和ASCII完全一样…
阅读更多...
JAVA——链表

JAVA——链表

一、链表概念及结构 链表&#xff1a;链表是一种物理存储结构上非连续存储结构&#xff0c;数据元素的逻辑顺序是通过链表中的引用链接次序实现的。如下图&#xff1a;&#xff08;通俗的说&#xff1a;就是由一个个节点组成&#xff0c;这些节点逻辑上连续&#xff0c;物理上…
阅读更多...
java对象复制_Java对象的复制三种方式

java对象复制_Java对象的复制三种方式

Java对象的复制三种方式 概述 在实际编程过程中，我们常常要遇到这种情况：有一个对象A，在某一时刻A中已经包含了一些有效值，此时可能 会需要一个和A完全相同新对象B，并且此后对B任何改动都不会影响到A中的值，也就是说，A与B是两个独立的对象，但B的初始值是由A对象确定的。…
阅读更多...
Java 如何复制 List ?

Java 如何复制 List ?

List 复制在项目开发时&#xff0c;使用到的频率还是比较高的。List 复制有浅拷贝和深拷贝两种方式。在陈述复制方法前&#xff0c;先总结下什么是浅拷贝和深拷贝(以下内容均站在 Java 语言基础上进行讨论)。 一、什么是浅拷贝&#xff08;Shallow Copy&#xff09;和深拷贝&a…
阅读更多...
Java对象复制

Java对象复制

文章目录 前言何不可变类对象复制方式1.直接赋值2.浅拷贝3.深拷贝 对象复制方案1.get/set2.Spring BeanUtils3.Apache BeanUtils4.BeanCopier5.Orika6.Dozer7.MapStruct8.Bean Mapping9.Bean Mapping ASM10.ModelMapper11.JMapper12.Json2Json 复制方案选择 前言 在我们实际项…
阅读更多...
Java 复制Excel工作表

Java 复制Excel工作表

本文归纳了关于Java如何复制Excel工作表的方法&#xff0c;按不同复制需求&#xff0c;可分为&#xff1a; 1. 复制工作表 1.1 在同一个工作簿内复制工作表 1.2 在不同工作簿间复制工作表 2. 复制指定单元格数据 对于复制方法copy()&#xff0c;这里简单整理了一个表格&am…
阅读更多...
个人如何接入微信支付和支付宝等支付接口，免签约

个人如何接入微信支付和支付宝等支付接口，免签约

企业的资质足够高了才能够得到微信或者支付宝官方的支付接口&#xff08;而且这个官方接口收费的最低费率在0.38%以上&#xff09;那么个人如何做&#xff1f; 个人开发者或者小微企业团队如何使用在线收款支付功能呢&#xff1f; 第四方支付&#xff0c;市面上各种收款工具要…
阅读更多...
微信支付接口开发详流程

微信支付接口开发详流程

微信支付 文章目录 1.支付接口分析2. 开发创建订单接口3. 开发根据订单id查询订单详情接口4. 开发生成二维码接口5. 开发查询订单支付状态接口 1.支付接口分析 引入依赖 <dependencies><dependency><groupId>com.github.wxpay</groupId><artifact…
阅读更多...
微信支付API v3接口使用应用篇

微信支付API v3接口使用应用篇

目录 前言版本应用基础配置1.申请商户API证书2.设置接口密钥3.下载平台证书 接口实测微信支付API官方客户端1.客户端2.支付调起参数签名3.回调通知 参考资料 前言 最近新项目中有涉及到微信支付相关接口业务的交互&#xff0c;毕竟原先开发接触过支付这块&#xff0c;轻车熟路…
阅读更多...
推荐文章

Copyright @ 2022~2023