精华内容
下载资源
问答
  • mysql唯一索引
    2021-01-18 21:52:54

    如果在一个列上同时建唯一索引和普通索引的话,mysql 会自动选择唯一索引

    唯一索引和普通索引使用的结构都是 B-tree,执行时间复杂度都是 O(log n)

    普通索引(非唯一索引)

    普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column =)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引

    唯一索引

    普通索引允许被索引的数据列包含重复的值

    如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。这么做的好处:一是简化了 MySQL 对这个索引的管理工作,这个索引也因此而变得更有效率(区分度:distinct * / count (*) 高);二是 MySQL 会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL 将拒绝插入那条新记录。也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复

    主键索引

    主键索引是唯一索引的特殊类型

    数据库表通常有一列或列组合,其值用来唯一标识表中的每一行。该列称为表的主键

    在数据库关系图中为表定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的。当在查询中使用主键索引时,它还允许快速访问数据

    它们的一些比较:

    (1) 对于主键 MySQL 会自动建立唯一索引;

    (2) 主键不一定只包含一个字段,所以可以在主键的其中一个字段建唯一索引;

    (3) 主键可作外键,唯一索引不可;

    (4) 主键不可为空,唯一索引可;

    (5) 主键也可是多个字段的组合;

    主键与唯一索引不同的是:

    a. 有not null属性;

    b. 每个表只能有一个。

    更多相关内容
  • MySQL唯一索引重复插入数据解决方案总结.docx
  • MySQL唯一索引和普通索引的区别

    千次阅读 2021-12-23 13:54:31
    Mysql唯一索引和普通索引的区别查询和更新上的区别查询操作更新操作 查询和更新上的区别 查询操作 这两类索引在查询上差别不大,主要是更新上的差别。 查询的时候 普通索引:查找到第一条满足条件的记录后,继续向...

    Mysql唯一索引和普通索引的区别

    查询和更新上的区别

    查询操作

    这两类索引在查询上差别不大,主要是更新上的差别。

    查询的时候

    普通索引:查找到第一条满足条件的记录后,继续向后遍历,直到第一个不满足条件的记录。
    唯一索引:由于索引定义了唯一性,查找到第一条满足条件的记录后,直接停止继续检索。

    普通索引会多检索一次,几乎没什么影响。因为InnDB是按照数据页为单位去读取数据的, 需要读取数据时,并不是直接去磁盘中读取的数据,而是读取数据页到内存,然后再从数据页中检索数据。每个数据页是16kb,对整型字段,一个数据页可以存放近1000个key,除非要读取的数据正好在数据页的最后一条记录,就需要重新再去读一个数据页,这种情况很少,对CPU的消耗基本可以忽略。
    因此,这两类索引在查询上的区别基本没差别。

    更新操作

    更新操作并不是直接对磁盘中的数据进行更新,而是把数据页从磁盘读到内存,再更新数据页。

    普通索引:
    以数据页为单位,把数据从磁盘读到内存,然后更新内存中的数据。
    唯一索引:
    以数据页为单位,把数据从磁盘督导内存,判断是否唯一再更新内存中的数据。

    mysql中有个change buffer机制,普通索引可以用chang buffer,唯一索引用不了。
    chang buffer 的作用是 降低IO操作,chang buffer 将数据写入数据页的操作叫做merge。
    普通索引,如果需要更新的数据页在内存中,则直接更新数据页,如果不在,则会先将操作记录到change buffer,当下次读取数据页是,顺带merge到数据页中,change buffer也有定期merge策略。数据库正常关闭时,也会触发merge。
    唯一索引,更新前需要先判断数据是否唯一。如果数据页在内存中 就可以直接判断并更新,如果不在内存中,则需要从磁盘中读出来,再判断更新,所以用不到change buffer.

    扩展:change buffer 用的是buffer pool里的内存,因此不能无限增大,change buffer 的大小可以通过参数inndb_change_buffer_max_size来动态设。例如这个参数设置的是50时,表示change buffer 的大小最多只能占用 change pool 的 50%。

    总结:mysql 唯一索引和普通索引 在读操作上基本没有什么差别,只是普通索引比唯一索引多检索一次,几乎没什么影响。在写操作上有区别。两种类型的索引在写操作时都是 把数据页先从磁盘读到内存,再更新,但唯一索引更新前会判断数据是否唯一再更新,并且当数据页不在内存中时,普通索引能用 change buffer 先记录更新操作,等下次再读数据页时,顺带merge到数据页,而唯一索引是 需要从磁盘中读取数据页后判断是否唯一再更新,用不到change buffer.

    展开全文
  • 昨天有位同事说,他的网页查询过程中发现普通索引和唯一索引的效率是有差别的,普通索引比唯一索引
  • 《Oracle唯一索引和NULL空值之间的关系》提到了当存在唯一索引的时候,不能插入两条(1, 'a', null),但是有朋友说,MySQL允许,实测一下,root@mysqldb:[test]>createtablett1(c1varchar(1),c2varchar(1),c3...

    Oracle唯一索引和NULL空值之间的关系》提到了当存在唯一索引的时候,不能插入两条(1, 'a', null),但是有朋友说,MySQL允许,实测一下,

    root@mysqldb:  [test]> create table tt1(c1 varchar(1), c2 varchar(1), c3 varchar(1));
    Query OK, 0 rows affected (0.05 sec)
    
    
    root@mysqldb:  [test]> create unique index idx_tt1_01 on tt1(c1, c2, c3);
    Query OK, 0 rows affected (0.05 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    
    root@mysqldb:  [test]> insert into tt1 values('a', 'a', null);
    Query OK, 1 row affected (0.01 sec)
    
    
    root@mysqldb:  [test]> insert into tt1 values('a', 'a', null);
    Query OK, 1 row affected (0.01 sec)
    
    
    root@mysqldb:  [test]> select * from tt1;
    +------+------+------+
    | c1   | c2   | c3   |
    +------+------+------+
    | a    | a    | NULL |
    | a    | a    | NULL |
    +------+------+------+
    2 rows in set (0.00 sec)

    MySQL官方文档明确写了支持null的这种使用方式,

    https://dev.mysql.com/doc/refman/5.7/en/create-index.html#create-index-unique

    ec7c50eddd36915de9bbaf3ac9e89fbf.png

    因此,当出现异构数据库同步的要求,例如要从MySQL同步数据到Oracle,MySQL允许两条('a', 'a', null),但是Oracle不允许,这就可能导致同步出现错误,这种问题就很细,了解了原理,碰到场景,才好理解。

    归根结底,还是数据库设计层面考虑的不同,这就需要在应用层设法抹平,达到一致的要求。

    近期更新的文章:

    MySQL的MDL锁解惑

    Oracle唯一索引和NULL空值之间的关系

    NFT,只可远观不可亵玩焉

    MySQL数据库设计开发规范

    SQL Server检索SQL和用户信息的需求

    文章分类和索引:

    公众号900篇文章分类和索引

    展开全文
  • Mysql唯一索引和普通索引的区别,

    千次阅读 2020-07-15 16:47:31
    文章目录Mysql唯一索引和普通索引的区别,那种速度快一点,原因是啥理由说明:结论:1 普通索引2 唯一索引注意:唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。3 主索引4 外键索引5 复合索引...

    Mysql唯一索引和普通索引的区别,那种速度快一点,原因是啥

    其实,如果业务上就要求我们数据库的值必须是唯一的,那没什么好讨论的,就选择唯一索引;那么如果业务上要求不严格,或者说不需要我们数据库后台来保障唯一性要求,这时候我们选择唯一索引还是普通索引就看谁在性能上更好,谁好选谁。

    唯一索引和普通索引怎么选择

    理由说明:

    我们都知道一个索引就是创建一个B+树,如果他是一个普通索引我们现在去查找,当查找到符合的数据的时候这个时候他就会在进行第二次查找,因为列里面的数据是唯一的,所以第二次查找肯定是没有数据的,这一次查找通过二分法去查找B+树查找到二叉树的叶子结点,每个叶子结点都会对应一个数据块(数据页),如果这个数据页在内存中那么他也就不用去在磁盘或硬盘去读区这个数据了,直接在内存中读取,我们也知道一个数据块大概是可以存储几千条数据的,所以在第一次查找的时候刚好这个数据块的尾部,在进行第二次查找的时候就要在进行IO读取了 一次IO读取是很低效的。但是如果是唯一索引的话这样的话就不会再进行第二次读取了,所以在最坏的情况下,普通索引和唯一索引的最大区别是多进行一次IO读取,概率极小。
    update:
    2.在update或者insert的时候普通索引也就是去在B+ 树中去找位置然后按照B+树的算法结构更新放入 或者更新其结构(拓展:在创建索引的时候会降低更新和插入效率,因为B+树在某种情况下添加结点效率会比较低),这在innoDB中如果说是在内存中没有这个数据块那么在这个情况下他不会去现在就去更新,会将这个更新操作存储在ChangeBuffer上,当第二次有查找这个数据的时候再去读取这个数据块然后再执行这个ChangeBuffer 中的数据操作,meger 这个数据。(在redlog里面也会存一份,也会存储changeBuffer的才操作数据,ES(Elaticsearch)的一种同步方式是通过redo log 来进行是实时同步的),当唯一索引进行更新操作的时候 会先进行一次查找是否唯一,再进行和普通索引一样的操作。所以在符合业务逻辑和无误的前提下:唯一索引比普通索引多一个查找操作。(在进行insert的时候会根据主键来进行查住 主键索引的数据结构发生改变)

    结论:

    在符合业务场景的前提下的时候唯一索引还是少用多用用普通索引。 还有就是change Buffer的使用 在更新读取少的 情况下放大change buffer的存储量 因为chanbuffer量越大其变更的数量更多 减少的IO次书更多,性能也就提高了。

    1 普通索引

    普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHEREcolumn=)或排序条件(ORDERBYcolumn)中的数据列创建索引。只要有可能,就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

    2 唯一索引

    • 普通索引允许被索引的数据列包含重复的值。比如说,因为人有可能同名,所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。
    • 如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。
    • 这么做的好处:一是简化了mysql对这个索引的管理工作,这个索引也因此而变得更有效率;二是MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。
    • 也就是说,唯一索引可以保证数据记录的唯一性。事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。

    注意:唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。

    3 主索引

    在前面已经反复多次强调过:必须为主键字段创建一个索引,这个索引就是所谓的"主索引"。主索引与唯一索引的唯一区别是:前者在定义时使用的关键字是PRIMARY而不是UNIQUE。

    4 外键索引

    如果为某个外键字段定义了一个外键约束条件,MySQL就会定义一个内部索引来帮助自己以最有效率的方式去管理和使用外键约束条件。

    5 复合索引

    索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引。这种索引的特点是MySQL可以有选择地使用一个这样的索引。如果查询操作只需要用到columnA数据列上的一个索引,就可以使用复合索引INDEX(columnA, columnB)。不过,这种用法仅适用于在复合索引中排列在前的数据列组合。比如说,INDEX(A, B, C)可以当做A或(A, B)的索引来使用,但不能当做B、C或(B, C)的索引来使用。

    6 全文索引

    文本字段上的普通索引只能加快对出现在字段内容最前面的字符串(也就是字段内容开头的字符)进行检索操作。如果字段里存放的是由几个、甚至是多个单词构成的较大段文字,普通索引就没什么作用了。这种检索往往以LIKE %word%的形式出现,这对MySQL来说很复杂,如果需要处理的数据量很大,响应时间就会很长。
      这类场合正是全文索引(full-text index)可以大显身手的地方。在生成这种类型的索引时,MySQL将把在文本中出现的所有单词创建为一份清单,查询操作将根据这份清单去检索有关的数据记录。全文索引即可以随数据表一同创建,也可以等日后有必要时再使用下面这条命令添加:
      ALTER TABLE tablename ADD FULLTEXT(column1, column2)
      有了全文索引,就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。下面是这类查询命令的基本语法:
      SELECT * FROM tablename
      WHERE MATCH(column1, column2) AGAINST(‘word1’, ‘word2’, ‘word3’)
      上面这条命令将把column1和column2字段里有word1、word2和word3的数据记录全部查询出来。
      注解:InnoDB数据表不支持全文索引。

    参考:
    唯一索引和普通索引怎么选择
    唯一索引和普通索引
    唯一索引和普通索引的区别

    展开全文
  • mysql 唯一索引与null.md

    千次阅读 2021-01-21 09:17:09
    mysql唯一索引要求所有参与的列都不能够为 null 值,如果唯一索引中的任何一个元素含有 null 值,则唯一约束将不起作用。示例代码create table tb (a int,b int,c int,unique index (a,b,c));insert into tb(a,b,...
  • mysql唯一索引与null

    千次阅读 2021-06-09 13:54:30
    3、原因 在mysql 的innodb引擎中,是允许在唯一索引的字段中出现多个null值的。 根据NULL的定义,NULL表示的是未知,因此两个NULL比较的结果既不相等,也不不等,结果仍然是未知。根据这个定义,多个NULL值的存在...
  • MySQL中的唯一索引的简单学习教程mysql 唯一索引UNIQUE一般用于不重复数据字段了我们经常会在数据表中的id设置为唯一索引UNIQUE,下面我来介绍如何在mysql中使用唯一索引UNIQUE吧。创建唯一索引的目的不是为了提高...
  • mysql唯一索引和unique

    千次阅读 2021-01-19 16:44:23
    最近遇到一个问题:高并发环境下,如何避免一张表里的某些列不要重复。我这样回答的(被“高并发”给骗了,傻傻地往复杂里想):1. 查找和插入这两个操作做成一个事务,对整...面试官跟我说,知不知道“唯一索引”,突...
  • MySQL 唯一索引和插入重复自动更新

    千次阅读 2021-01-31 02:53:59
    有时我们在往数据库插入数据的时候,需要判断某个...ON DUPLICATE KEY UPDATE这个时候可以给这个字段(或者几个字段)建立唯一索引,同时使用以下 sql 语句进行插入或更新操作:INSERT INTO table (id, user_id, token...
  • mysql 唯一索引与插入重复自动更新

    千次阅读 2022-04-01 21:29:08
    在我们使用mybatis对mysql数据库进行增删改查的时候,可能会碰到比较棘手的情况,如这个问题: ...你希望数据库中哪个字段的值作为唯一索引,到MySQL查询界面,使用以下命令: 例如: ALTER TABLE student a..
  • 2、添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` )  3、添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4、添加FULLTEXT(全文索引) ...
  • 别踩坑!使用MySQL唯一索引请注意

    万次阅读 多人点赞 2019-01-28 21:33:14
    背景 在程序设计中了,我们往往需要确保数据的唯一性,比如在常见的注册模块,我们需要确保一个手机号只能注册为一个账号。...但是我们不能确保同时有两个人使用...不同存储方案,解决方式不一样,这里以MySQL为例,我...
  • 唯一索引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...
  • 普通索引 这是最基本的索引类型,而且它没有唯一性之类的限制。 唯一性索引 这种索引和前面的“普通索引”基本相同,...对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索...
  • Mysql唯一索引区分大小写问题

    千次阅读 2020-07-20 10:23:37
    但是mysql数据库做好了唯一索引后,插入这种数据报错,原因当然是唯一字段不区分大小写了。 只要修改一下就好了 ALTER TABLE [表名称] MODIFY [字段名称] [VARCHAR(30)] BINARY; 注意: 字段名称后面的 VAR
  • 对查询:普通索引和唯一索引对查询性能影响很小 对更新:唯一索引比普通索引更耗时. 查询流程:唯一索引找到第一个匹配数据后不继续往后查找. 1.普通索引,从索引树根节点开始按层往下查找,找到对应的数据页,然后...
  • MYSQL 唯一索引UNIQUE使用方法详解

    千次阅读 2019-11-10 17:34:16
    创建唯一索引的目的不是为了提高访问速度,而只是为了避免数据出现重复(在插入数据的时候,如果唯一索引的插入值重复,所以插入就会报错),索引可以有多个但索引列的值必须唯一,索引列的值允许有空值。...
  • mysql 唯一索引区分大小写

    千次阅读 2019-11-27 09:20:50
    这取决于字段的排序规则-如果是ci(不区分大小写)或cs(区分大小写)。唯一索引将相应地应用。
  • mysql唯一索引和普通索引的选择

    千次阅读 2019-02-27 14:19:27
    如果业务能够保证唯一性应该尽量选择普通索引而不是唯一索引 从查询和更新两方面说明: 查询: 查询来说二者的区别是 普通索引查到符合条件的项后会继续查找下一项,如果下一项不符合再返回 唯一索引则是查到符合...
  • MySQL 唯一索引,并发插入导致死锁

    千次阅读 2020-07-28 00:12:46
    一日志 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2020-07-27 16:28:53 0x7fc914aee700 ...mysql tables in use 2, locked 2 LOCK WAIT 3 lock struct(s), h
  • MYSQL 引擎innodb ,设置了唯一索引的列值 允许 多个NULL值存在。 示例: 字段 userCardNum 添加了唯一索引 证实是允许存在的多个NULL值数据的: 原由: 因为这里 NULL 的定义 ,是指 未知值。 所以多...
  • mysql唯一索引冲突的解决方案

    千次阅读 2019-04-08 23:50:02
    如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用: insert ignore into insert ignore into(使用唯一索引再次插入相同的值会忽略提示*如果insert into 数据库会报错*,相同值...
  • 唯一索引可作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键) 主键和唯一索引 主键保证数据库里面的每一行都是唯一的,比如...
  • mysql索引(七)唯一索引

    万次阅读 2021-07-19 11:17:54
    唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。 主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。 全文索引(FULLTEXT ):可用于 MyISAM 表,mysql5.6之后也可...
  • 假设一个用户管理系统,每个人注册都有一个唯一的手机号,而且业务代码已经保证了不会写入两个重复的手机号。如果用户管理系统需要按照手机号查姓名,就会执行类似这样的 SQL...从性能的角度考虑,选择唯一索引还是普通
  • Mysql 唯一索引冲突处理

    千次阅读 2017-07-21 12:32:58
    Mysql插入时遇到唯一索引冲突, 1、自动替换为新的值,可以用ON DUPLICATE KEY UPDATE xxx= VALUES(xxx) 2、忽略插入是 insert ignore into 3、删除旧记录,插入新记录是 replace into,如果这个记录id有在其他表...
  • mysql创建唯一索引

    千次阅读 2021-01-18 18:19:24
    查看索引 show index from 数据库表名alter table 数据库add index 索引名称(数据库字段名称)PRIMARY KEY(主键索引)ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )UNIQUE(唯一索引)ALTER TABLE `table_name...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 194,313
精华内容 77,725
关键字:

mysql唯一索引

mysql 订阅