精华内容
下载资源
问答
  • 唯一性约束通过唯一性索引来实现?我觉得这说法不对。 对于唯一性约束,索引是必须存在的,唯一性约束本质上是通过索引来保证的,但不一定是唯一性索引。 唯一性约束允许有NULL值,唯一性约束的列可允许有多个NULL...

    唯一性约束通过唯一性索引来实现?我觉得这说法不对。

    对于唯一性约束,索引是必须存在的,唯一性约束本质上是通过索引来保证的,但不一定是唯一性索引。

    唯一性约束允许有NULL值,唯一性约束的列可允许有多个NULL值。唯一性约束通过BTREE索引实现,而BTREE索引是不会包含NULL值,但使用NULL值过滤时不会走索引。

    在没有索引的情况下,创建唯一性约束会自动创建一个唯一性索引,但Oracle官方建议将唯一性约束和索引分开,这该怎么理解呢?原来在创建唯一性约束时默认创建的唯一性索引会随着唯一性约束DISABLE或DROP而删除,我们以三个实验来证明这个结论。

    一、创建唯一性约束时默认创建唯一性索引

    通过创建唯一性约束时默认创建唯一性索引,之后DISABLE唯一性约束,观察唯一性索引是否还存在。

    CREATE TABLE TEST (ID NUMBER, NAME VARCHAR2(30));

    ALTER TABLE TEST ADD CONSTRAINT CON_TEST_ID_UQ UNIQUE(ID);

    查看索引

    SELECT Index_Name, Uniqueness, Index_Type

      FROM User_Indexes

     WHERE Table_Name = 'TEST';

    可以看到,默认创建了一个同名的唯一性索引

    查看唯一性约束

    SELECT Owner, Constraint_Name, Table_Name, Generated, Index_Name

      FROM User_Constraints

     WHERE Table_Name = 'TEST';

    唯一性约束CON_TEST_ID_UQ绑定了唯一性索引CON_TEST_ID_UQ

    我们DISABLE这个唯一性约束。

    ALTER TABLE TEST DISABLE CONSTRAINT CON_TEST_ID_UQ;

    再次查看约束

    再次查看索引,发现CON_TEST_ID_UQ索引已经不见了

    二、创建唯一性索引后创建唯一性约束

    先创建唯一性索引,然后创建唯一性约束,之后DISABLE唯一性约束,观察唯一性索引是否还存在。

    CREATE TABLE TEST1 (ID NUMBER, NAME VARCHAR2(30));

    CREATE UNIQUE INDEX IDX_TEST1_ID_UQ ON TEST1(ID);

    ALTER TABLE TEST1 ADD CONSTRAINT CON_TEST1_ID_UQ UNIQUE(ID);

    查看唯一性索引

    SELECT Index_Name, Uniqueness, Index_Type

      FROM User_Indexes

     WHERE Table_Name = 'TEST1';

    查看唯一性约束

    SELECT Owner, Constraint_Name, Table_Name, Generated, Index_Name

      FROM User_Constraints

     WHERE Table_Name = 'TEST1';

    可以看到,唯一性约束CON_TEST1_ID_UQ与唯一性索引IDX_TEST1_ID_UQ绑定起来了。

    我们DISABLE这个唯一性约束CON_TEST1_ID_UQ,再次查看唯一性约束和索引的情况,发现唯一性索引仍存在。

     

    三、创建普通索引后创建唯一性约束

    先创建普通索引,然后创建唯一性约束,观察唯一性约束是否能成功建立。之后DISABLE唯一性约束,观察索引是否还存在。

    CREATE TABLE TEST2 (ID NUMBER, NAME VARCHAR2(30));

    CREATE INDEX IDX_TEST2_ID ON TEST2(ID);

    ALTER TABLE TEST2 ADD CONSTRAINT CON_TEST2_ID_UQ UNIQUE(ID);

    查看索引

    SELECT Index_Name, Uniqueness, Index_Type

      FROM User_Indexes

     WHERE Table_Name = 'TEST2';

    查看唯一性约束

    SELECT Owner, Constraint_Name, Table_Name, Generated, Index_Name

      FROM User_Constraints

     WHERE Table_Name = 'TEST2';

    发现唯一性约束CON_TEST2_ID_UQ也建立成功了,并且和普通索引IDX_TEST2_ID绑定了。

    我们DISABLE唯一性约束CON_TEST2_ID_UQ,查看唯一性约束和索引的情况,发现普通索引IDX_TEST2_ID也仍存在。

    四、结论

    由以上实验我们可以得出以下几个结论。

    1. 创建唯一性约束时默认创建的唯一性索引会随着唯一性约束DISABLE或DROP消失。
    2. 创建唯一性约束并不一定会创建索引,也可以使用现有的索引;
    3. 唯一性约束并不一定需要唯一性索引;

    在生产环境中,如果使用默认的唯一性索引,当唯一性约束需要DISABLE时,索引失效将会造成很大的问题,所以建议将唯一性约束与索引分开。

    展开全文
  • 约束和索引的区别:前者用来检查数据的正确性,后者用来优化...创建唯一性约束会创建一个约束和一个唯一性索引,创建唯一性索引只会创建一个唯一性索引。 主键约束和唯一性约束都会创建一个唯一性索引。    ...

    约束和索引的区别:前者用来检查数据的正确性,后者用来优化查询,目的不同。

    唯一性约束和唯一性索引:

    创建唯一性约束会创建一个约束和一个唯一性索引,创建唯一性索引只会创建一个唯一性索引。

    主键约束和唯一性约束都会创建一个唯一性索引。

     

     

    展开全文
  • 普通索引和唯一性索引的选择 两者之间读操作的区别 普通索引:查询操作时,在根据查询条件在索引树上寻找相同的值后,会再往下找一个值判断是否相等,(查,判断) 唯一性索引:直接查,查到之后直接返回,不需要判断 change ...

    普通索引和唯一性索引的选择

    两者之间读操作的区别

    • 普通索引:查询操作时,在根据查询条件在索引树上寻找相同的值后,会再往下找一个值判断是否相等,(查,判断)
    • 唯一性索引:直接查,查到之后直接返回,不需要判断
    • change buffer:当更新数据时,如果数据也在内存中就直接更新,如果数据页不在内存中,在不影响数据一次性原则,就把相关操作操作到change buffer中,等到下次有访问这个数据页的时候,再把这个数据页读到内存中执行change buffer的操作(叫merge,后台会定期更新merge,数据库正常关闭也会merge),change buffer也是可以持久化的,

    两者之间写操作的区别

    • 数据页在内存中:
      • 普通索引:会找个合理的位置,直接插入
      • 唯一性索引,找到合适的位置,判断没有冲突,插入
    • 数据页不在内存中
      • 普通索引:直接把操作保存到change buffer中结束
      • 唯一性索引,把数据页读到内存中,找到合适的位置,判断没有冲突,插入
    • 所以如果是在频繁插入,但是没有插入之后马上查询的,就可以用普通索引和change buffer(如果对唯一性没有要求,就多用普通索引和change buffer的使用)

    redo log和change buffer的区别

    • redo log主要是减少随机写磁盘的io消耗,(转成了顺序写)

    • change buffer主要是减少随机读磁盘的io消耗

    • change buffer用的是buffer pool里的内存,change buffer的大小,可以通过参数innodb_change_buffer_max_size来动态设置。这个参数设置为50的时候,表示change buffer的大小最多只能占用buffer pool的50%。

    展开全文
  • 唯一性索引unique影响: unique与primary key的区别: 存在唯一键冲突时,避免策略: insert ignore: replace into: insert on duplicate key update: 结论: 参考: 唯一性索引unique影响: 唯一性索引表创建: ...

    目录

    唯一性索引unique影响:

    唯一性索引表创建:

    DROP TABLE IF EXISTS `sc`;
    CREATE TABLE `sc` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
      `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
      `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
      `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

    在其中创建了唯一索引name,就是说这个学生表同名的学生只能由一位。

    命令添加unique:

    alter table sc add unique (name);
    
    alter table sc add unique key `name_score` (`name`,`score`);

    删除:

    alter table sc drop index `name`;

    唯一性索引作用:

    先行插入部分数据:

    insert into sc (name,class,score) values ('吕布','一年二班',67);
    insert into sc (name,class,score) values ('赵云','一年二班',90);
    insert into sc (name,class,score) values ('典韦','一年二班',89);
    insert into sc (name,class,score) values ('关羽','一年二班',70);

    再次查看表定义:

    show create table sc;
    
    CREATE TABLE `sc` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
      `class` varchar(200) CHARACTER SET utf8 DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
      `create_user_id` bigint(11) DEFAULT NULL COMMENT '创建人id',
      `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
      `modify_user_id` bigint(11) DEFAULT NULL COMMENT '最后修改人id',
      PRIMARY KEY (`id`),
      UNIQUE KEY `name` (`name`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='学生信息表';

    这时的Auto_Increment=5

    再次执行sql:

    insert into sc (name,class,score) values ('吕布','二年二班',77)
    > 1062 - Duplicate entry '吕布' for key 'name'
    > 时间: 0.01s

    此时再次查看表定义,会发现Auto_Increment=6

    unique除了在插入重复数据的时候会报错,还会使auto_increment自动增长

    unique与primary key的区别:

    简单的讲,primary key=unique+not null

    具体的区别:

    (1) 唯一性约束所在的列允许空值,但是主键约束所在的列不允许空值。

    (2) 可以把唯一性约束放在一个或者多个列上,这些列或列的组合必须有唯一的。但是,唯一性约束所在的列并不是表的主键列。

    (3) 唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下,创建唯一性的非聚簇索引,但是,也可以指定所创建的索引是聚簇索引。

    (4) 建立主键的目的是让外键来引用.

    (5) 一个表最多只有一个主键,但可以有很多唯一键

    存在唯一键冲突时,避免策略:

    insert ignore:

    insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断),如果数据库没有数据,就插入新的数据,如果有数据的话就跳过这条数据.

    insert ignore into sc (name,class,score) values ('吕布','二年二班',77)

    执行上面的语句,会发现并没有报错,但是主键还是自动增长了。

    replace into:

    • replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据。
    • 使用replace into,你必须具有delete和insert权限
    replace into sc (name,class,score) values ('吕布','二年二班',77);

    此时会发现吕布的班级跟年龄都改变了,但是id也变成最新的了,所以不是更新,是删除再新增

    insert on duplicate key update:

    • 如果在insert into 语句末尾指定了on duplicate key update,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致重复的问题,则插入新行,跟普通的insert into一样。
    • 使用insert into,你必须具有insert和update权限
    • 如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0
    insert into sc (name,class,score) values ('关羽','二年二班',80) on duplicate key update score=100;
    > Affected rows: 2
    > 时间: 0.008s

    旧数据中关羽是一年二班,70分,现在插入,最后发现只有分数变成了100,班级并没有改变。

    4   关羽  一年二班    100 2018-11-16 15:32:18     2018-11-16 15:51:51 

    id没有发生变化,数据只更新,但是auto_increment还是增长1了。

    死锁:

    insert ... on duplicate key 在执行时,innodb引擎会先判断插入的行是否产生重复key错误,
    如果存在,在对该现有的行加上S(共享锁)锁,如果返回该行数据给mysql,然后mysql执行完duplicate后的update操作,
    然后对该记录加上X(排他锁),最后进行update写入。

    如果有两个事务并发的执行同样的语句,
    那么就会产生death lock,如

    img

    解决办法:

    1、尽量对存在多个唯一键的table使用该语句

    2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句

    结论:

    • 这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
    • insert ignore能忽略重复数据,只插入不重复的数据。
    • replace into和insert ... on duplicate key update,都是替换原有的重复数据,区别在于replace into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert ... on duplicate key update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句。

    参考:

    Mysql中unique与primary约束的区别分析(转)

    MySQL避免插入重复记录:唯一性约束

    MySQL优化–INSERT ON DUPLICATE UPDATE死锁

    展开全文
  • 主键和唯一性索引区别如下: 1、主键是一种特殊的唯一性索引,索引不一定是主键; 2、主键只能有一个,唯一性索引可以设置多个; 3、主键不能为空,唯一性索引可以为空; 4、主键可以作为其他表的外键,唯一性索引不...
  • 唯一性索引的意义

    千次阅读 2016-10-11 17:29:32
    索引是我们经常使用的一种数据库搜索优化手段。适当的业务操作场景使用适当的索引方案可以显著的提升系统整体性能和用户体验。...其中,唯一性索引Unique Index是我们经常使用到的一种。   唯一性索引uni
  • 唯一性约束和唯一性索引的区别

    千次阅读 2015-05-09 10:53:33
    “唯一性约束”和“唯一性索引”是不同的。 一、 建立唯一性约束的语法,使用create table或者alter table 1. 在字段级约束定义 -- 命名 create table tmp_table (  a int constraint pk_tmp_table_a primary...
  • 可以通过selectconstraint_name,constraint_typefromuser_indexesuiwhereui.table_name='A'来查看主键上的唯一索引,如果此时我们在给ID列去创建唯一性索引或者非唯一性索引的话,都会报错,当然指定主键之后...
  • SQL> create unique index idx_uni_empno_copy on emp_copy...注意这里的index_type不代表是否唯一性索引,要看UNIQUENESS 字段 SQL> select index_name,status,num_rows,index_type from user_indexes ...
  • 使用唯一性索引实现数据新增不重复目的唯一性索引 目的 在很多业务场景中,我们很经常需要判断数据在数据库中是否已经存在,如果存在,则更新已有数据,不存在则insert。常规做法有2种: select count(1) from ...
  • 测试2,先创建唯一性索引,再禁用唯一性检查(如图):![图片说明](https://img-ask.csdn.net/upload/201603/07/1457317819_720331.png) 两次测试中我都禁用了唯一性检查,为什么最后不能够插入相同的id 值呢?求...
  • 下面小编就为大家带来一篇mysql为字段添加和删除唯一性索引(unique) 的方法。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧
  • 记得在tom的oracle 9i&10g编程艺术中这样一句话:在一个非唯一索引中,oracle会把rowid作为一个额外列加到键上,使索引键为唯一; 先按索引键排序,再按rowid升序排序;在一个唯一索引中,不会再加rowid到索引键上。...
  • 索引是我们经常使用的一种数据库搜索优化手段。适当的业务操作场景使用适当的索引方案可以... 唯一性索引unique index和一般索引normal index最大的差异就是在索引列上增加了一层唯一约束。添加唯一性索引的数据列...
  • 主键与唯一性索引

    2015-05-11 19:27:24
    主键与唯一性索引  primary key & unique 其实指定列的索引就相当于对指定的列进行排序,为什么要排序呢?因为排序有利于对该列的查询,可以大大增加查询效率。(那么可能有人认为应该对所有的列排序,这样就...
  • 在业务代码已经保证插入身份证唯一的情况下,可以选择建立唯一索引和普通普通索引,这时该如何选择呢?接下来,将从查询和更新的执行过程进行分析。 Top 查询过程 假设 k 是表 t 上的索引,在搜索 select id from...
  • 今天突然想起来,曾经在面试的时候被问起主键和唯一性索引之间的区别是什么,我只回答的是主键就是特殊的唯一性索引。其他就没有什么了。 总是感觉不太正确,今天特地的用度娘search下。见如下结果: 1、主键就是...
  • 唯一性索引(unique index),这部分主要是翻译了PostgreSql9.3的官方文档的11.6这一节,翻译加上自己的语言组织,之后会添加更多的内容。 当前只有B-tree索引才能使用唯一性索引唯一性索引可以使单字段和多字段...
  • 为emp表创建唯一性索引
  • 表设计需要注意的事项:唯一性索引的字段中,不建议使用字符型,也建议设置默认不为空。(not null) 违反注意事项,可能出现如下的问题: 某张表uni_test 的表结构: 1 CREATE TABLE `uni_test` ( 2 `id...
  • 索引普通索引唯一索引全文索多列索引隐藏索引删除索引设计原则 普通索引 索引存储: 数据库底层索引实现主要有两种存储类型,B树(BTREE)和哈希(HASH)索引,InnoDB和MyISAM 使用BTREE索引;而MEMORY 存储引擎可以...
  • 在Elasticsearch中,实现像数据库中的联合唯一性索引,有没有好的解决方案呀?
  • 删除重复数据,建立唯一性索引

    千次阅读 2017-03-02 15:55:53
    一开始数据库中未建立唯一性索引,但是在业务逻辑上是有唯一性约束的。在运行一段时间后,才发现这个问题,记录一下 对应的表T_ROUTE_ORBIT,唯一限制字段: longitude, latitude, task_id 删除重复记录,...
  • 1)index:clustered index(聚合索引),non- clustered index(非聚合索引),unique index(唯一性索引) 2)clustered index :通常为主键,在SQL Server中主键默认是聚合索引(从数据结构的层面讲,表是线性表,....
  • http://www.itpub.net/thread-1328087-1-1.html 非唯一性索引 百度
  • 1.主键一定是唯一性索引唯一性索引并不一定就是主键;2.一个表中可以有多个唯一性索引,但只能有一个主键;3.主键列不允许空值,而唯一性索引列允许空值。 转载于:...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,939
精华内容 2,375
关键字:

唯一性索引