-
主键索引、唯一索引、复合主键辨析
2019-08-09 12:03:42主键和复合主键 一个表中可以存在一个主键或者多个主键,都是起到唯一约束的作用...相同点:都起到了约束数据记录唯一性的作用 不同点: 主键是一种约束,而唯一索引是索引,他们本质不一样 主键创建之后一定包含着...主键和复合主键
一个表中可以存在一个主键或者多个主键,都是起到唯一约束的作用;查了相关资料,还了解到,复合主键和联合主键的区别,只是看是来源于单表(复合主键),还是来源于多表(联合主键);在我看来其实都一样,只是概念稍微有点区分罢了,过于纠结没意义。
主键索引和唯一索引
- 相同点:都起到了约束数据记录唯一性的作用
- 不同点:
- 主键是一种约束,而唯一索引是索引,他们本质不一样
- 主键创建之后一定包含着一个唯一索引,但是添加唯一索引的字段不一是主键
- 主键字段不能为空,而添加唯一索引的字段可以为空
- 不能在主键字段创建索引
- 主键和索引都是键,不过主键是逻辑键,索引是物理键,意思就是主键不实际存在,而索引实际存在在数据库中
参考:
https://blog.csdn.net/qq_26222859/article/details/52469504
https://www.cnblogs.com/-619569179/p/6528896.html -
MongoDB索引分类及优化:索引类型-基础索引、复合索引、文档索引、唯一索引和强制索引
2020-07-21 14:36:03MongoDB索引分类及优化:索引类型-基础索引、复合索引、文档索引、唯一索引及优化 与关系型数据库一样,合理的使用索引可以大幅提高MongoDB的查询效率,本文介绍基础索引、复合索引、文档索引等几种常用索引的使用...与关系型数据库一样,合理的使用索引可以大幅提高MongoDB的查询效率,本文介绍基础索引、复合索引、文档索引等几种常用索引的使用
一、索引分类
MongoDB 提供了多样性的索引支持,索引信息被保存在system.indexes
中,且默认总是为_id创建索引,它的索引使用基本和MySQL
等关系型数据库一样。其实可以这样说说,索引是凌驾于数据存储系统之上的另一层系统,所以各种结构迥异的存储都有相同或相似的索引实现及使用接口并不足为奇。1、基础索引与复合索引
1.1 、基础索引
创建索引时,可以是一个集合中的一个或多个字段。如,为用户表users的age字段,按升序[1(升序);-1(降序)]创建索引:
db.users.ensureIndex({age:1})
mongo会在创建表的时候将_id 自动创建的索引,此索引是不能够删除的。
当数据库中有大量数据时,创建索引的操作会非常耗时,我们可以指定background:true选项:
db.users.ensureIndex({age:1}, {background:true})
1.2、 复合索引(联合索引or组合索引)
跟其它数据库产品一样,MongoDB 也是有组合索引的,下面我们将在city 和age上建立组合索引。当创建组合索引时,字段后面的1 表示升序,-1 表示降序,是用1 还是用-1 主要是跟排序的时候或指定范围内查询 的时候有关的。为多个字段联合创建索引就是复合索引。如,为users表的age和city两个字段,分别按升序和降序创建索引:
db.users.ensureIndex({city:1,age:-1})
创建复合索引后,在使用时应当注意: 查询字段要在索引中存在,且顺序一致;如果索引中的首个字段没有出现在查询条件中,则不会用索引。
下面的查询都用到了这个索引:db.factories.find( { "city" : "BeiJing", "age" : 18 } ); db.factories.find( { "city" : "BeiJing" } ); db.factories.find().sort( { "city" : 1, "age" : -1 } ); db.factories.find().sort( { "city" : 1 } );
2、文档索引
索引可以任何类型的字段,甚至文档:
如,users表中有以下数据:{name:"扫地增", address:{ city:"北京", district:"海淀区" }}
可以为address子文档创建索引如下(在address列上创建索引):
db.users.ensureIndex({address:1})
对子文档创建索引时,也可以只对某一个或几个字段创建索引:
db.users.insert( { name: "扫地僧", addr: { city: "北京", district: "海淀区" } } )
建立索引后,查询时子文档的字段顺序要和索引建立的顺序一致:
// 下面这个查询将会使用刚刚建立的索引 db.users.find({address:{ city:"北京", district:"海淀区" }}) // 下面这个查询不会不会使用刚刚建立的索引 db.users.find({address:{ district:"海淀区", city:"北京" }}) **原因在于:在利用索引查询时用到的字段在查询中顺序要和索引创建时的顺序保持一致**
3、唯一索引和强制索引
3.1、 唯一索引
在关系型数据库中,我们可以为字段创建唯一索引,以保证字段值的唯一。在MongoDB中同样可以使用唯一索引,MongoDB创建唯一索引,在创建索引时添加unique:true选项即可。
如,为users表的email字段创建唯一索引:
db.users.ensureIndex({email:1}, {unique:true})
创建唯一索引后,当插入重复值时,MongoDB会报错:
> db.users.insert({email:'x@itbilu.com'}) WriteResult({ "nInserted" : 1 }) >//我们再插入一条 > db.users.insert({email:'x@itbilu.com'}) WriteResult({ "nInserted" : 0, "writeError" : { "code" : 11000, "errmsg" : "insertDocument :: caused by :: 11000 E11000 duplicate key error index: itbilu.users.$mobile_1 dup key: { : null }" } })
3.2、 强制使用索引
在MongoDB的查询中,如果查询字段中的一个或几个字段已经创建了索引,我们可以使用hint()函数来强制使用索引。hint()在查询中是非常有效的一种优化手段:
db.users.find({age:{$lt:30}}).hint({name:1, age:1}).explain()
如,我们要查询users表中的多个字段,查询字段中的age字段创建过索引,可以使用hint()来强制赋索引查询:
db.users.find({name:'扫地僧', age:3}).hint({age:1})
-
sql sever 创建带条件的唯一性索引
2015-04-10 14:14:44查找了两天,终于解决了sql sever 创建带条件的唯一性索引,mark一下。 带条件的复合索引 索引字段 (a,b,c,d) 条件 a不为空的时候,(a,b,c,d)组成的复合索引唯一 a为空时,不做限制 代码如下:IF ...查找了两天,终于解决了sql sever 创建带条件的唯一性索引,mark一下。
带条件的复合索引
索引字段 (a,b,c,d)
条件
a不为空的时候,(a,b,c,d)组成的复合索引唯一
a为空时,不做限制
代码如下:IF EXISTS(SELECT object_name(object_id) tableName,name,type_desc from sys.indexes where name= 'idx') alter table tableName drop constraint idx GO create unique nonclustered index idx on table1(a,b,c,d) where a<> ''; GO
ps:低版本的sql sever 不支持这种写法(2005不支持)。lz就被坑了,找了各种方法,结果发现是版本问题,说多了都是眼泪。
-
sqlite 复合唯一索引_别踩坑!使用MySQL唯一索引请注意
2020-12-29 05:22:52背景在程序设计中,我们往往需要确保数据的唯一性,比如在常见的注册模块,我们需要确保一个手机号只能注册为一个账号。这种情况下,我们的程序往往是第一道关卡,用户来注册之前,首先判断这个手机号是否已经注册,...别踩坑!使用MySQL唯一索引请注意
发布时间:2019-01-28 21:33,
浏览次数:2769
, 标签:
MySQL
<>背景
在程序设计中,我们往往需要确保数据的唯一性,比如在常见的注册模块,我们需要确保一个手机号只能注册为一个账号。这种情况下,我们的程序往往是第一道关卡,用户来注册之前,首先判断这个手机号是否已经注册,如果已经注册则返回错误信息,或直接去登录。但是我们不能确保同时有两个人使用同一个手机号注册到我们的系统中,因此这里就需要在更深的层次去确保手机号在系统的唯一性了。不同存储方案,解决方式不一样。对于常用的MySQL数据库,我们可以使用唯一索引的方式来作为我们的最后一道防线。
但是最近在使用数据库的唯一索引时,发现一个比较奇怪的现象。MySQL数据库,使用InnoDB存储引擎,创建了唯一索引时,在insert操作时,如果唯一索引上的字段有为NULL的情况,则可以无限插入。这有点匪夷所思,但是现实就是这么一个情况。现在就来具体分析这样的一个案例,来看看底层对于唯一索引是怎么设计的,来规避在数据库设计上犯错和踩坑。
<>案例
假设现在有一个用于保存用户信息的数据表user,是使用email注册的,当前使用email作为唯一索引,同时这一基本规则也被其他依赖系统作为设计数据模型的设计基础。假设现在设计这样一个user表:
CREATE TABLE `user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT
'primary key', `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email', `name`
varchar(11) DEFAULT '' COMMENT 'name', `age` int(11) DEFAULT NULL COMMENT
'age', PRIMARY KEY (`id`), UNIQUE KEY `uk-email` (`email`) ) ENGINE=InnoDB
DEFAULT CHARSET=utf8;
1@user.com来注册,执行insert语句,执行成功
INSERT INTO user (email,name,age) VALUES ('1@user.com','h1',18);
1@user.com再来注册,则再次执行,则报错。成功规避了用户多次创建导致系统产生脏数据问题。
Duplicate entry '1@user.com' for key 'uk-email'
从这里看,user表的设计是符合业务要求的,并没有出现同一个email出现多行的情况。随着业务发展,单单email注册的模式并不适合移动互联网时代,所以现在的要求在原有基础上增加了手机号的字段,并要求手机号也是唯一的。于是添加phone字段,并将原有唯一索引删除,为email和phone设置新的唯一索引。
ALTER TABLE `user` ADD COLUMN `phone` varchar(11) default NULL AFTER `age`;
DROP INDEX `uk-email` ON `user`; ALTER TABLE `user` ADD UNIQUE KEY
`uk-email-phone` (`email`,`phone`);
假设用户1再来用同样的email注册,可以注册成功:
INSERT INTO user (email,name,age,phone) VALUES (‘1@user.com’,‘h1’,18,NULL);
查询数据库数据,得到以下结果:
有两个email为1@user.com的记录,他们的phone都是NULL,这怎么可能存在?!难道是MySQL出问题了?!不可能,我们再试另外一个数据
INSERT INTO user (email,name,age,phone) VALUES
('2@user.com','h2',18,'18812345678');
连续执行两次,第一次执行成功,第二次报错:
Duplicate entry ‘2@user.com-18812345678’ for key ‘uk-email-phone’
查询user结果集,得到
从结果看这样MySQL的唯一索引也算是正常的啊,那这到底是怎么一回事呢?
<>原因探寻
业务中希望建立的唯一索引是email +
phone的组合,但是由于phone一开始是没有数据的,所以新建字段时默认允许为NULL来兼容老数据。如果程序没有控制好,数据操作直接打到数据库,就产生了两条email为“1@user.com”且phone为NULL的数据,那么就会发生这种数据错乱的情况。
我从 MySQL 5.7官方文档
中找到了这个:
<>Unique Indexes
A UNIQUE index creates a constraint such that all values in the index must be
distinct. An error occurs if you try to add a new row with a key value that
matches an existing row. If you specify a prefix value for a column in a UNIQUE
index, the column values must be unique within the prefix length. A UNIQUE
index permits multiple NULL values for columns that can contain NULL.
官方的文档中明确说明在唯一索引中是允许存在多行值为NULL的数据存在的。
当然我们会认为这是MySQL的一个bug,其实早有人这么认为了,并给MySQL提出了这个问题
https://bugs.mysql.com/bug.php?id=8173
。但是MySQL的开发者并不认为这是一个bug,而是本身的一种设计。额,这么说,好像也说得过去。那这里就有一个问题了,我们知道索引是使用B+树来维护的,但是对于这种非唯一索引是怎么维护的?
带着这个问题,我觉得有两种可能:
(1)唯一索引时另外一种数据类型,正好把有值为NULL的字段过滤掉了,无需特殊处理。
(2)还是用的B+树索引,但是对于NULL的索引特殊处理了。
于是我对email=2@user.com且phone= 18812345678的数据执行了Explain执行计划
explain select * from user where `email` = '2@user.com' and `phone` =
'18812345678';
这个查询正好用到了唯一索引uk-email-phone,索引长度是134。
对email=1@user.com且phone为NULL的执行类似Explain执行计划
explain select * from user where `email` = '1@user.com' and `phone` is NULL;
对比上面两次不同数据的explain执行结果,可以看到其实都用了uk-email-phone的唯一索引,不同的是第一个type是const(通过一次索引就可以找到,用于primary
key或unique
index),第二个type是ref(非唯一性索引扫描),且rows为2。所以这里极有可能是对NULL进行的特殊处理,唯一索引树还是用的和非NULL一样的唯一索引树。
<>结论
所以其实MySQL在唯一索引中允许存在值为NULL的字段。NULL值在MySQL可以代表是任意值,并且在有字段值为NULL时,不会参与校验这个组合的唯一索引,所以可能插入业务上不允许重复的数据,导致脏数据。
因此在创建属于唯一索引的列时,最好指定字段值不能为空,在已有值为NULL的情况下,创建的字段不允许为空,且默认值为空字符。如果已经创建了默认值为NULL的字段,则先将其update为空字符,然后再修改为NOT
NULL DEFAULT ‘’。如上述情况建表语句改为
CREATE TABLE `user` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT
'primary key', `email` varchar(32) NOT NULL DEFAULT '' COMMENT 'email', `name`
varchar(11) DEFAULT '' COMMENT 'name', `age` int(11) DEFAULT NULL COMMENT
'age', `phone` varchar(11) NOT NULL DEFAULT '', PRIMARY KEY (`id`), UNIQUE KEY
`uk-email-phone` (`email`,`phone`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
并非所有数据库都是这样,SQL Server 2005及更老的版本,只允许有一个NULL值出现。从
https://sqlite.org/faq.html#q26 了解到ANSI
SQL-92标准:
A unique constraint is satisfied if and only if no two rows in a table have
the same non-null values in the unique
columns.(如果且仅当表中没有两行在唯一列中具有相同的非空值时,才满足唯一约束。)
除了MySQL之外,sqlLite、PostgreSQL、Oracle和FireBird也是允许唯一索引上存在多行为NULL。
-
MongoDB 唯一索引
2016-12-22 17:02:58MongoDB支持的索引种类很多,诸如单键索引,复合索引,多键索引,TTL索引,文本索引,空间地理索引等。同时索引的属性可以具有唯一性,即唯一索引。唯一索引用于确保索引字段不存储重复的值,即强制索引字段的唯一性... -
mongo 唯一约束索引_MongoDB中唯一索引(Unique)的那些事
2020-12-23 08:29:30写在前面MongoDB支持的索引种类很多,诸如单键索引,复合索引,多键索引,TTL索引,文本索引,空间地理索引等。同时索引的属性可以具有唯一性,即唯一索引。唯一索引用于确保索引字段不存储重复的值,即强制索引字段... -
mongodb的id的唯一性_MongoDB中唯一索引(Unique)的那些事
2020-12-20 17:30:25写在前面MongoDB支持的索引种类很多,诸如单键索引,复合索引,多键索引,TTL索引,文本索引,空间地理索引等。同时索引的属性可以具有唯一性,即唯一索引。唯一索引用于确保索引字段不存储重复的值,即强制索引字段... -
Mysql唯一索引和普通索引的区别,
2020-07-15 16:47:313 主索引4 外键索引5 复合索引6 全文索引 Mysql唯一索引和普通索引的区别,那种速度快一点,原因是啥 其实,如果业务上就要求我们数据库的值必须是唯一的,那没什么好讨论的,就选择唯一索引;那么如果业务上要求不... -
Oracle 复合索引设计原理——前缀性和可选性
2017-03-14 17:06:00前缀性: 复合索引的前缀性是指只有当复合索引的第一个字段出现在...唯一的例外是skip scan index,就是如果Oracle发现第一个字段的值很少,会自动拆分为两个复合索引。如复合索引(gender,ename,job,mgr),因为第一... -
oracle索引(包括复合索引)
2017-03-28 11:37:55在关系数据库中,索引是一种与表有关的数据库结构,它可以使对应于表的SQL语句执行得更快。索引的作用相当于图书的目录,可以根据目录中的...第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 -
MongoDB中唯一索引(Unique)的那些事
2021-01-19 22:55:36MongoDB支持的索引种类很多,诸如单键索引,复合索引,多键索引,TTL索引,文本索引,空间地理索引等。同时索引的属性可以具有唯一性,即唯一索引。唯一索引用于确保索引字段不存储重复的值,即强制索引字段的唯一性... -
Mysql逻辑删除保留唯一索引
2020-08-03 14:33:37但是当遇到表中需要有唯一性索引时则会造成问题。 案例 创建一个商品编码为A110的商品,商品编码为该表的唯一性索引,此时对该商品进行删除后,无法再次添加商品编码为A110的商品。 将删除标识加入唯一索引 把商品编码... -
复合索引设计建议
2011-11-15 23:15:103)如果单个字段是主键或唯一字段,或者可选性非常高,尽管约束条件字段比较固定,也不一定要建成复合索引,可建成单字段索引,降低复合索引开销。 4)首先切记复合索引的前缀性。 5)其次考虑复合索引的可选性或... -
索引
2019-04-24 08:40:30唯一性索引 (非唯一性索引) 基于函数的索引 --建立基于函数的索引 SELECT VName FROM Video WHERE UPPER(VEngName)=‘LEON’; CREATE INDEX video_engname_upper_idx ON Video(UPPER(VEngName)) 物理实现... -
简单介绍mysql的复合索引和使用条件_mysql的索引设计原则以及常见索引的区别的简单介绍...
2021-02-12 11:06:31下面小编就为大家带来一篇浅谈mysql的索引设计原则以及常见索引的区别。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧索引定义:是一个单独的,存储在...1.选择唯一性索引唯一性... -
复合索引建立原则: 列值选择性好的放在最前面
2014-01-02 22:33:31复合索引建立原则: 列值选择性好的放在最前面 所谓列值选择性好,指的是该列中值大部分不一样,只有少部分重复,我们就称之为列值选择性好,如主键,唯一键等. 表CHANNEL_CARD中字段lan_id只有11个不同值,serv_id基本都... -
除复合索引外,哪些情况下不适合用索引
2016-08-25 09:04:28对于索引,有时候会方便你的一些操作,但有时候就会成为负累,增加你数据库的压力,那么,应该怎样避免这些问题... 唯一性差的字段不适合创建索引,因为无法准确的找到想要的数据,所以查完索引后依然还需要过一遍数据,这样 -
MongoDB优化索引、分析索引和选择索引规则
2020-08-07 00:35:03MongoDB优化索引基本特点基础语法简单练习准备数据创建普通索引创建复合...第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。第二,可以大大加快数据的检索速度,这也是创建索引的最主要的原因。第三 -
唯一键约束--查看索引
2020-01-12 18:56:483、作用:给主键以外的列,限定唯一性 4、唯一键分类 单列的唯一 复合唯一 唯一键和主键的区别: (1)主键不能为空,唯一键可以为空 (2)主键约束,一个表只能有一个,而唯一键可以有很多个 (二)使用唯一键 1、... -
索引的使用
2018-11-14 22:47:36主键创建后一定包含一个唯一性索引,唯一性索引并不一定就是主键。 唯一性索引列允许空值,而主键列不允许为空值。 主键列在创建时,已经默认为空值 ++ 唯一索引了。 一个表最多只能创建一个主键,但可以创建... -
数据库索引
2017-10-23 19:38:04(1)按照索引列值的唯一性,索引可分为唯一索引和非唯一索引 ①非唯一索引:B树索引 create index 索引名 on 表名(列名) tablespace 表空间名; ②唯一索引:建立主键或者唯一约束时会自动在对应的列上建立唯一索引 ... -
数据库实验:索引和安全性语言
2020-06-21 15:45:23针对给定的数据库模式和具体应用需求,创建唯一索引、函数索引、复合索引等;修改索引;删除索引。设计相应的SQL查询验证索引有效性。学习利用EXPLAIN命令分析SQL查询是否使用了所创建的索引,并能够分析其原因,... -
Oracle索引
2019-01-02 12:09:44按照索引列的唯一性又可以分为:唯一性索引和非唯一索引 按照索引列的个数可以分为:单列索引和复合索引 创建索引时应该注意以下几个条件 1,创建索引的列应该是频繁作为where查询条件的某个列或某几个列作为条件...
-
解读Android 12首个开发者预览版
-
云开发后台+微信扫码点餐小程序+cms网页管理后台 含后厨端和用户端
-
极限学习机集成的动态生成方法
-
基于create-react-app的团队代码规范化
-
Public_PowerShell:公共IT系统管理员等的PowerShell脚本-源码
-
将布隆过滤器变形,判断两个list是否据有包含关系(不一定准确,但一定能判断出不包含)
-
第三章 SQL语言元素(一)
-
Joyoshare VidiKit for mac (最佳视频工具包)
-
666-源码
-
Oracle_11g_Linux到Linux_DataGuard部署
-
resume:恢复-源码
-
零基础极简以太坊智能合约开发环境搭建并开发部署
-
Python函数库深度详解(1)
-
MySQL 备份与恢复详解(高低版本 迁移;不同字符集 相互转换;表
-
Filtered_Gaussian_output.m
-
投标方法论
-
practicalTest-源码
-
四级新题型透析及模拟试题.pdf
-
simple:一行ZSH提示,不会提示-源码
-
爱奇艺阅读怎么设置语音阅读