精华内容
下载资源
问答
  • 就是指你表的主键含有一个以上的字段组成比如create table test(name varchar(19),id number,value varchar(10),primary key (name,id))上面的name和id字段组合起来就是你test表的复合主键它的出现是因为你的name...

    什么是数据表的复合主键

    所谓的复合主键 就是指你表的主键含有一个以上的字段组成

    比如

    create table test

    (

    name varchar(19),

    id number,

    value varchar(10),

    primary key (name,id)

    )

    上面的name和id字段组合起来就是你test表的复合主键

    它的出现是因为你的name字段可能会出现重名,所以要加上ID字段这样就可以保证你记录的唯一性

    一般情况下,主键的字段长度和字段数目要越少越好

    KEY

    1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列

    2. 如果Key是PRI,  那么该列是主键的组成部分

    3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)

    4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL

    如果对于一个列的定义,同时满足上述4种情况的多种,比如一个列既是PRI,又是UNI

    那么"desc 表名"的时候,显示的Key值按照优先级来显示 PRI->UNI->MUL

    那么此时,显示PRI

    一个唯一性索引列可以显示为PRI,并且该列不能含有空值,同时该表没有主键

    一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引

    因为虽然索引的多列组合是唯一的,比如ID+NAME是唯一的,但是没一个单独的列依然可以有重复的值

    只要ID+NAME是唯一的即可

    展开全文
  • 分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题。比如表t_lock_test中有两个主键都为primary key(a,b),但是更新时...

    背景

    今天在配合其他项目组做系统压测,过程中出现了偶发的死锁问题。分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题。

    比如表t_lock_test中有两个主键都为primary key(a,b),但是更新时却通过update t_lock_test .. where a = ? ,然后该事务内又有insert into t_lock_test values(...)

    InnoDB中的锁算法是Next-Key Locking,很可能是因为这个点导致的死锁,但是复合主键下会出发Next-Key Locking吗,那多列联合unique索引下又会触发Next-Key Locking吗,书上并没有找到答案,得实际测试一下。

    InnoDB中的锁

    锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访[插图]。InnoDB存储引擎会在行级别上对表数据上锁,这固然不错。不过InnoDB存储引擎也会在数据库内部其他多个地方使用锁,从而允许对多种不同资源提供并发访问。例如,操作缓冲池中的LRU列表,删除、添加、移动LRU列表中的元素,为了保证一致性,必须有锁的介入。数据库系统使用锁是为了支持对共享资源进行并发访问,提供数据的完整性和一致性。

    由于使用锁时基本都是在InnoDB存储引擎下,所以跳过MyISAM,直接讨论InnoDB。

    锁类型

    InnoDB存储引擎实现了如下两种标准的行级锁:

    共享锁(S Lock),允许事务读一行数据

    排它锁(x lOCK),允许事务删除或更新一条数据

    如果一个事务T1已经获得了r的共享锁,那么另外的事务T2可以立即获得行r的共享锁,因为读取并没有改变r的数据,成这种情况为锁兼容(Lock Compatible)。但若有其他的事务T3箱获得行r的排它锁,则比如等待T1、T2释放行r上的共享锁——这种情况称为锁不兼容。

    排它锁和共享锁的兼容性:

    \

    X

    S

    X

    不兼容

    不兼容

    S

    不兼容

    兼容

    InnoDB中对数据进行Update操作会产生行锁,也可以显示的添加行锁(也就是平时所说的“悲观锁”)

    select for update

    锁算法

    InnoDB有3种行锁的算法,其分别是:

    Record Lock:单个行记录上的锁,就是字面意思的行锁

    Record Lock会锁住索引记录(注意这里说的是索引,因为InnoDB下主键索引即数据),ruguo InnoDB存储引擎表在建立的时候没有设置任何一个索引,那么这时对InnoDB存储引擎会使用隐士的主键来进行锁定。

    Gap Lock:间隙锁,锁定一个范围,但不包含记录本身

    Next-Key Lock:Gap Lock+Record Lock,锁定一个范围,并且锁定记录本身

    Gap Lock和Next-Key Lock的锁定区间划分原则是一样的。

    例如一个索引有10/11/13和20这四个值,那么该索引被划分的的区间为:

    (-∞,10]

    (10,11]

    (11,13]

    (13,20]

    (20,+∞]

    采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了解决Phantom Problem,这将在下一小节中介绍。而利用这种锁定技术,锁定的不是单个值,而是一个范围,是谓词锁(predict lock)的一种改进。

    当查询的索引含有唯一(unique)属性时(主键索引,唯一索引)InnoDB存储引擎会对Next-Key Lock优化,将其降级为Record Lock,即仅锁住索引本身,不是范围。

    下面来看一个辅助索引(非唯一索引)下的锁示例:

    CREATE TABLE z ( a INT, b INT, PRIMARY KEY(a), KEY(b) );

    INSERT INTO z SELECT 1,1;

    INSERT INTO z SELECT 3,1;

    INSERT INTO z SELECT 5,3;

    INSERT INTO z SELECT 7,6;

    INSERT INTO z SELECT 10,8;

    表z的列b是辅助索引,若果事务A中执行:

    SELECT * FROM z WHERE b=3 FOR UPDATE

    由于b列是辅助索引,所以此时会使用Next-Key Locking算法,锁定的范围是(1,3]。特别注意,InnoDB还会对辅助索引的下一个值加上Gap Lock,即还有一个辅助索引范围为(3,6]的锁。因此,若在新事务B中运行以下SQL,都会被阻塞:

    1. SELECT * FROM z WHERE a = 5 LOCK IN SHARE MODE;//S锁

    2. INSERT INTO z SELECT 4,2;

    3. INSERT INTO z SELECT 6,5;

    第1个SQL不能执行,因为在事务A中执行的SQL已经对聚集索引中列a=5的值加上X锁,因此执行会被阻塞。

    第2个SQL,主键插入4,没有问题,但是插入的辅助索引值2在锁定的范围(1,3]中,因此执行同样会被阻塞。

    第3个SQL,插入的主键6没有被锁定,5也不在范围(1,3]之间。但插入的b列值5在另下一个Gap Lock范围(3,6]中,故同样需要等待。

    而下面的SQL语句,由于不在Next-Key Lock和Gap Lock范围内,不会被阻塞,可以立即执行:

    INSERT INTO z SELECT 8,6;

    INSERT INTO z SELECT 2,0;

    INSERT INTO z SELECT 6,7;

    从上面的例子可以发现,Gap Lock的作用是为了组织多个事务将数据插入到统一范围内,这样会导致幻读问题(Phantom Problem)。例子中事务A已经锁定了b=3的记录。若此时没有Gap Lock锁定(3,6],其他事务就可以插入索引b列为3的记录,这会导致事务A中的用户再次执行同样查询会返回不同的记录,即导致幻读问题的产生。

    用户也可以通过以下两种方式来显示的关闭Gap Lock(但不推荐):

    将事务的隔离级别设置为READ COMMITED

    将参数innodb_locks_unsafe_for_binlog设置为1

    在InnoDB中,对于Insert的操作,会检查插入记录的下一条记录是否被锁定,若已经被锁定,则不允许插入。对于上面的例子,事务A已经锁定了表z中b=3的记录,即已经锁定了(1,3]的范围,这时若在其他事务中执行如下插入也会导致阻塞:

    INSERT INTO z SELECT 2,0

    因为在辅助索引列b上插入值为2的记录时,会监测到下一个记录3已经被索引,修改b列值后,就可以执行了

    INSERT INTO z SELECT 2,0

    幻读(Phantom Problem)

    幻读是指在同一事务下,连续执行两次同样的SQL语句可能会导致不同的结果,第二次的SQL可能会返回之前不存在的行。

    在默认的事务隔离级别(REPEATABLE READ)下,InnoDB存储引擎采用Next—Key Locking机制来避免幻读问题。

    复(联)合主键与锁

    上面的锁机制介绍(摘自《Mysql技术内幕 InnoDB存储引擎 第2版》),只是针对辅助索引和聚集索引,那么复合主键下行锁的表现形式又是怎么样呢?从书上并没有找到答案,实际来测试一下。

    首先创建一个复合主键的表

    CREATE TABLE `composite_primary_lock_test` (

    `id1` int(255) NOT NULL,

    `id2` int(255) NOT NULL,

    PRIMARY KEY (`id1`,`id2`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (10, 10);

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 8);

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 6);

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 6);

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (3, 3);

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (1, 1);

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (5, 1);

    INSERT INTO `composite_primary_lock_test`(`id1`, `id2`) VALUES (7, 1);

    事务A先来查询id2=6的列,并添加行锁

    select * from composite_primary_lock_test where id2 = 6 lock in share mode

    此时的锁会降级到Record Lock吗?事务B Update一条Next-Key Lock范围内的数据(id1=1,id2=8)证明一下:

    UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

    结果是UPDATE被阻塞了,那么再来试试加锁时在where中把两个主键都带上:

    select * from composite_primary_lock_test where id2 = 6 and id1 = 5 lock in share mode

    执行UPDATE

    UPDATE `composite_primary_lock_test` SE WHERE `id1` = 1 AND `id2` = 8;

    结果是UPDATE没有被阻塞

    上面加锁的id2=6的数据,不只1条,那么再试试对唯一的数据id2=8,只根据一个主键加锁呢,会不会降级为行级锁:

    select * from composite_primary_lock_test where id2 = 8 lock in share mode;

    UPDATE `composite_primary_lock_test` SE WHERE `id1` = 12 AND `id2` = 10;

    结果也是被阻塞了,实验证明:

    复合主键下,如果加锁时不带上所有主键,InnoDB会使用Next-Key Locking算法,如果带上所有主键,才会当作唯一索引处理,降级为Record Lock,只锁当前记录。

    多列索引(联合索引)与锁

    上面只验证了复合主键下的锁机制,那么多列索引呢,会不会和复合索引机制相同?多列unique索引呢?

    新建一个测试表,并初始化数据

    CREATE TABLE `multiple_idx_lock_test` (

    `id` int(255) NOT NULL,

    `idx1` int(255) NOT NULL,

    `idx2` int(255) DEFAULT NULL,

    PRIMARY KEY (`id`,`idx1`) USING BTREE

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

    ALTER TABLE `multiple_idx_lock_test`

    ADD UNIQUE INDEX `idx_multi`(`idx1`, `idx2`) USING BTREE;

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (1, 1, 1);

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (5, 2, 2);

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (7, 3, 3);

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (4, 4, 4);

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (2, 4, 5);

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (3, 5, 5);

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (8, 6, 5);

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (6, 6, 6);

    事务A查询增加S锁,查询时仅使用idx1列,并遵循最左原则:

    select * from multiple_idx_lock_test where idx1 = 6 lock in share mode;

    现在插入一条Next-Key Lock范围内的数据:

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

    结果是被阻塞了,再试一遍通过多列索引中所有字段来加锁:

    select * from multiple_idx_lock_test where idx1 = 6 and idx2 = 6 lock in share mode;

    插入一条Next-Key Lock范围内的数据:

    INSERT INTO `multiple_idx_lock_test`(`id`, `idx1`, `idx2`) VALUES (9, 6, 7);

    结果是没有被阻塞

    由此可见,当使用多列唯一索引时,加锁需要明确要锁定的行(即加锁时使用索引的所有列),InnoDB才会认为该条记录为唯一值,锁才会降级为Record Lock。否则会使用Next-Key Lock算法,锁住范围内的数据。

    总结

    在使用Mysql中的锁时要谨慎使用,尤其时更新/删除数据时,尽量使用主键更新,如果在复合主键表下更新时,一定通过所有主键去更新,避免锁范围变大带来的死锁等问题。

    好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对我们的支持。

    参考

    《Mysql技术内幕 InnoDB存储引擎 第2版》 - 姜承尧

    展开全文
  • Mysql 多形成主键(复合主键

    千次阅读 2017-07-27 14:13:58
    什么是数据表的复合主键  所谓的复合主键 就是指你表的主键含有一个以上的字段组成  比如  create table test  (   name varchar(19),  ...上面的name和id字段组合起来就是你test表的复合主键 
    什么是数据表的复合主键 
    
    所谓的复合主键 就是指你表的主键含有一个以上的字段组成 
    比如 
    create table test 

       name varchar(19), 
       id number, 
       value varchar(10), 
       primary key (name,id) 


    上面的name和id字段组合起来就是你test表的复合主键 
    它的出现是因为你的name字段可能会出现重名,所以要加上ID字段这样就可以保证你记录的唯一性 

    一般情况下,主键的字段长度和字段数目要越少越好 

    KEY

    1. 如果Key是空的, 那么该列值的可以重复, 表示该列没有索引, 或者是一个非唯一的复合索引的非前导列
    2. 如果Key是PRI,  那么该列是主键的组成部分
    3. 如果Key是UNI,  那么该列是一个唯一值索引的第一列(前导列),并别不能含有空值(NULL)
    4. 如果Key是MUL,  那么该列的值可以重复, 该列是一个非唯一索引的前导列(第一列)或者是一个唯一性索引的组成部分但是可以含有空值NULL

    如果对于一个列的定义,同时满足上述4种情况的多种,比如一个列既是PRI,又是UNI
    那么"desc 表名"的时候,显示的Key值按照优先级来显示 PRI->UNI->MUL
    那么此时,显示PRI

    一个唯一性索引列可以显示为PRI,并且该列不能含有空值,同时该表没有主键

    一个唯一性索引列可以显示为MUL, 如果多列构成了一个唯一性复合索引
    因为虽然索引的多列组合是唯一的,比如ID+NAME是唯一的,但是没一个单独的列依然可以有重复的值
    只要ID+NAME是唯一的即可


    展开全文
  • MyBatis定义复合主键

    2021-02-01 10:46:03
    前为别名后为主查询getXXX语句中字段 SELECTcolid,colname FROM table1SELECT * FROM table2 WHERE id = #{id} AND name = #{name} 复合主键@IdClass有时一个实体的主键可能同时为多个,例如同样是之前...

    前为别名后为主查询getXXX语句中字段

              SELECT colid,colname FROM table1

    SELECT * FROM table2 WHERE id = #{id} AND name = #{name}  

    复合主键@IdClass

    有时一个实体的主键可能同时为多个,例如同样是之前使用的“CustomerEO”实体,需要通过name和email来查找指定实体,当且仅当name和email的值完全相同时,才认为是相同的实体对象.要配 ...

    MyBatis insert返回主键(sqlserver2008)

    mybatis insert返回主键(sqlserver2008)   MyBatisXML配置,下面两种方式都行 方式1:

    MSSQL - 逻辑主键、业务主键和复合主键

    转载自:http://blog.csdn.net/sunrise918/article/details/5575054 这几天对逻辑主键.业务主键和复合主键进行了一些思考,也在网上搜索了一下相关的讨论 ...

    【Hibernate步步为营】--复合主键映射具体解释

    上篇文章讨论了继承映射,它是对象模型中最主要的特性,对于继承映射它的主要区分是字段类型的不同,所以在生成表结构时须要有新列来标识数据的类型,能够使用标签并在标签中加入d ...

    Mysql 多列形成主键(复合主键 )

    什么是数据表的复合主键 所谓的复合主键 就是指你表的主键含有一个以上的字段组成 比如 create table test (    name varchar(19),    id number,    ...

    Cassandra开发入门文档第二部分(timeuuid类型、复合主键、静态字段详解)

    timeuuid类型 timeuuid具有唯一索引和日期时间的综合特性,可以与日期和时间函数联合使用,常用的关联函数: dateOf() now() minTimeuuid() and maxTime ...

    Hibernate 表映射 主键生成策略与复合主键

    主要分析三点: 一.数据表和Java类的映射 : 二.单一主键映射和主键的生成策略 : 三.复合主键的表映射 : 一.数据表和Java类的映射  Hibernate封装了数据库DDL语句,只需要将数据 ...

    hibernate复合主键

    需要用到实体类Role的主键和Menu的主键结合起来作为实体类RoleMenu的主键,那么通过Hibernate具体实现如下: RoleMenu实体类:(注意该实体类需要实现Serializable接 ...

    Hibernate征途(七)之复合主键映射和集合映射

    把这两种映射放到一起说,是因为这两种映射不像前面的复用型映射.数量和方向型映射那么分类鲜明,所以放到了这个“其他”里面. 复合主键映射 在关系模型中,复合主键和其他的主键方式没有很大区别,但是反映到对 ...

    随机推荐

    vue吃进去的object已经变了样,不在是原来的!

    直接上代码: class data { public list:number[]; constructor() { this.list=[0,0,0]; } } class methods exten ...

    spring mvc 多视图配置

    展开全文
  • mysql-多列主键中的NULL我有一个包含几列组成主键的表。 存储的数据的性质允许这些字段中的某些字段具有NULL。 我这样设计我的桌子:CREATE TABLE `test` (`Field1` SMALLINT(5) UNSIGNED NOT NULL,`Field2` ...
  • 复合主键与联合主键

    万次阅读 多人点赞 2017-05-02 17:05:57
    一、复合主键  所谓的复合主键 就是指你表的主键含有一个以上的字段组成,不使用无业务含义的自增id作为主键。 比如  create table test ( name varchar(19), id number, value varchar(10), primary ...
  • 比如create table test(name varchar(19),id number,value varchar(10),primary key (name,id))上面的name和id字段组合起来就是你test表的复合主键 ,它的出现是因为你的name字段可能会出现重名,所以要加上ID字段...
  • 比如上面的name和id字段组合起来就是你test表的复合主键 ,它的出现是因为你的name字段可能会出现重名,所以要加上ID字段这样就可以保证你记录的唯一性 ,一般情况下,主键的字段长度和字段数目要越少越好 。...
  • MySQL 复合主键(Day02)

    万次阅读 2018-10-15 17:21:49
    主键约束是表中的某一,也可以是表中多个所构成的一个组合,其中有多一个列组合而成的主键称为复合主键 在MsSQL中,主键必须遵循以下规则: 1.每个表只能定义一个主键 2.唯一性原则,主键的也成只为键值,...
  • 数据库的表中有多个主键,我们和数据库交互的实体(Entity)中只定义了其中部分主键,也就是数据库表中的字段多于Entity中定义的字段。当我们查询时发现,查询返回的结果和我们预想的完全不一致。
  • 复合主键与联合主键、索引与联合(复合)索引

    万次阅读 多人点赞 2018-09-24 22:11:31
    回来顺便看了一下复合主键与联合主键,查漏补缺,慢慢学习吧。 一、复合主键与联合主键 1、复合主键:指表的主键含有一个以上的字段组成,不使用无业务含义的自增id作为主键。 create table test (  name ...
  • 当数据库创建表时,每个表只能有一个主键,但是如果想让多个列都成为主键时,就要用到复合主键。一、主键唯一约束我们知道当某列为主键时,Oracle会自动将此创建唯一约束。也就是说不允许有相同的出现。如:...
  • (二)测试--创建一个表,并设置一个组合主键drop table TEST_CON;create table TEST_CON(id int,name varchar2(20),description varchar2(100));alter table TEST_CON add constraint TEST_CO...
  • 一、复合主键与联合主键 1、复合主键:指表的主键含有一个以上的字段组成,不使用无业务含义的自增id作为主键。...上面的name和id字段组合起来就是test表的复合主键 ,它的出现是因为name字段可能会出现重名,所以要...
  • 概念篇: 复合主键: 就是指你表的主键含有一个以上的字段组成,不使用无业务含义的自增id作为...多个主键联合形成一个主键组合(主键原则上是唯一的,别被唯一所困扰。 新建从表-课程表 插入数据: ...
  • mysql表的组合主键

    2021-01-18 18:24:01
    mysql表的组合索引是由表的多字段的拼接成的主键,来形成唯一性。和以往单个的普通主键如id(自增长)的区别是一个字段主键和多个字段的区别。组合索引就是复合索引。如果一个表有id,name,sex,nunber三个字段,...
  • 因此数据库默认排序可以符合要求的情况下不要使用排序操作,尽量不要包含多个的排序,如果需要最好给这些复合索引。 时间字段索引 分区 为什么分区时,主键必须加分区键? mysql规定:作为分区的字段必须是...
  • 我们有一个表,该表具有由三个字段组成的复合主键(在MySQL 5.1中是这样)。 该表上每秒有近200个插入和200个选择,并且表的大小约为100万行,并且还在不断增加。我的问题是:"复合主键"是否会降低此表上"插入和选择...
  • 联合主键是多个主键联合形成一个主键组合复合主键是表的主键含有一个以上的其他字段组成;表中的一个字段既是主键又是其他表中某个字段的外键关联。 一般主键的字段的长度和数目越少越好。 下面是网友分析主从表...
  • 逻辑主键、业务主键和复合主键

    千次阅读 2018-07-17 00:09:49
    主键(PRIMARY KEY):表通常具有包含唯一标识表中每一行的的一或一组。这样的一或多称为表的主键 (PK),用于强制表的实体完整性。 外键(FOREIGN KEY):外键 (FK) 是用于建立和加强两个表数据之间的链接的...
  • 要查询以获取一行值,可以添加列值: select row from foo where column = 1; 但是你会得到这个警告: Bad Request: Cannot execute this query as it might involve data filtering and thus may have ...
  • MySQL 面试题

    万次阅读 多人点赞 2019-09-02 16:03:33
    4、复合索引:将多个列组合在一起创建索引,可以覆盖多个。 5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。 6、全文索引:MySQL 自带的全文索引只能...
  • Oracle主键的设置

    2021-05-01 02:59:18
    有两种方法可以设置Oracle主键,一种是自增长主键,另一种就是生成唯一序列。 一、自增长主键 --首先建一个表TEST有两种方法可以设置Oracle主键,一种是自增长主键,另一种就是生成唯一序列。一、自增长主键--首先建...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 22,300
精华内容 8,920
关键字:

复合主键的组合列值