主键和复合主键
一个表中可以存在一个主键或者多个主键，都是起到唯一约束的作用；查了相关资料，还了解到，复合主键和联合主键的区别，只是看是来源于单表（复合主键），还是来源于多表（联合主键）；在我看来其实都一样，只是概念稍微有点区分罢了，过于纠结没意义。
主键索引和唯一索引

相同点：都起到了约束数据记录唯一性的作用
不同点：

主键是一种约束，而唯一索引是索引，他们本质不一样
主键创建之后一定包含着一个唯一索引，但是添加唯一索引的字段不一是主键
主键字段不能为空，而添加唯一索引的字段可以为空
不能在主键字段创建索引
主键和索引都是键，不过主键是逻辑键，索引是物理键，意思就是主键不实际存在，而索引实际存在在数据库中



参考：

https://blog.csdn.net/qq_26222859/article/details/52469504

https://www.cnblogs.com/-619569179/p/6528896.html

与关系型数据库一样，合理的使用索引可以大幅提高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 创建带条件的唯一性索引，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就被坑了，找了各种方法，结果发现是版本问题，说多了都是眼泪。

别踩坑！使用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。