精华内容
下载资源
问答
  • 别踩坑!使用MySQL唯一索引请注意

    万次阅读 多人点赞 2019-01-28 21:33:14
    背景 在程序设计中了,我们往往需要...但是我们不能确保同时有两个人使用同一个手机号注册到我们的系统中,因此这里就需要在更深的层次去确保手机号的唯一性了。不同存储方案,解决方式不一样,这里以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);

    查询数据库数据,得到以下结果:
    mysql-query-user-uk-index-1
    有两个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-query-user-uk-index-2
    从结果看这样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';
    

    mysql-query-user-uk-index-3
    这个查询正好用到了唯一索引uk-email-phone,索引长度是134。

    对email=1@user.com且phone为NULL的执行类似Explain执行计划

    explain select * from user where `email` = '1@user.com' and `phone` is   NULL;
    

    mysql-query-user-uk-index-4

    对比上面两次不同数据的explain执行结果,可以看到其实都用了uk-email-phone的唯一索引,不同的是第一个type是const(通过一次索引就可以找到,用于primary key或unique index),第二个type是ref(非唯一性索引扫描),且rows为2。所以猜测这里极有可能是对NULL进行的特殊处理,唯一索引树还是用的和非NULL一样的唯一索引树。

    源码分析

    上面利用explain,测试结果是符合自己的猜测行为而已。也许只有源码中才能比较好的知道答案,基于此,在github上找到MySQL相关的源码(在此感谢DBA同学在唯一索引源码分析上的指点)。在这段源码https://github.com/mysql/mysql-server/blob/8e797a5d6eb3a87f16498edcb7261a75897babae/storage/innobase/row/row0ins.cc中,有一个方法 row_ins_scan_sec_index_for_duplicate(),这里会扫描唯一非聚簇索引树,来确定是否会发生唯一性的冲突。源码内有一段注释

    /* If the secondary index is unique, but one of the fields in the
      n_unique first fields is NULL, a unique key violation cannot occur,
      since we define NULL != NULL in this case */
    

    在继续往下有一段这样的逻辑

    	 cmp = cmp_dtuple_rec(entry, rec, index, offsets);
    
        if (cmp == 0 && !index->allow_duplicates) {
          if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) {
            err = DB_DUPLICATE_KEY;
    
            thr_get_trx(thr)->error_info = index;
    
            /* If the duplicate is on hidden FTS_DOC_ID,
            state so in the error log */
            if (index == index->table->fts_doc_id_index &&
                DICT_TF2_FLAG_IS_SET(index->table, DICT_TF2_FTS_HAS_DOC_ID)) {
              ib::error(ER_IB_MSG_958) << "Duplicate FTS_DOC_ID"
                                          " value on table "
                                       << index->table->name;
            }
    
            goto end_scan;
          }
        } else {
          ut_a(cmp < 0 || index->allow_duplicates);
          goto end_scan;
        }
    

    跳转到row_ins_dupl_error_with_rec()方法中有一段这样的逻辑

    /* In a unique secondary index we allow equal key values if they
      contain SQL NULLs */
    
      if (!index->is_clustered() && !index->nulls_equal) {
        for (i = 0; i < n_unique; i++) {
          if (dfield_is_null(dtuple_get_nth_field(entry, i))) {
            return (FALSE);
          }
        }
      }
    

    在唯一索引中有字段为NULL的情况下,返回FALSE,代码中就没有抛出DB_DUPLICATE_KEY的异常了。所以从源码来看,这里实现了唯一索引允许为NULL的情况了,而且可以知道,这个唯一索引树和其他的二级索引基本上是没什么区别的。这也是前面explain时及时我们查询非唯一索引中另一个字段为空的记录,也还是用到了同样的索引和相同的索引长度。

    反观来看,如果是我们在未知实现的情况下,要我们来设计,怎么实现允许有字段为NULL的唯一索引呢?是否还有比现有MySQL更好的方式来实现?

    结论

    所以其实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。

    展开全文
  • 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数据表不支持全文索引。

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

    展开全文
  • MongoDB 唯一索引

    万次阅读 2016-12-22 17:02:58
    同时索引的属性可以具有唯一性,即唯一索引唯一索引用于确保索引字段不存储重复的值,即强制索引字段的唯一性。缺省情况下,MongoDB的_id字段在创建集合的时候会自动创建一个唯一索引。本文主要描述唯一索引的用法...

    MongoDB支持的索引种类很多,诸如单键索引,复合索引,多键索引,TTL索引,文本索引,空间地理索引等。同时索引的属性可以具有唯一性,即唯一索引。唯一索引用于确保索引字段不存储重复的值,即强制索引字段的唯一性。缺省情况下,MongoDB的_id字段在创建集合的时候会自动创建一个唯一索引。本文主要描述唯一索引的用法。

    一、创建唯一索引语法

        //语法
        db.collection.createIndex( <key and index type specification>, { unique: true } )
    
        语法更多描述可参考:http://blog.csdn.net/leshami/article/details/53541978
    

    二、演示创建唯一索引

    1、演示环境

        > db.version()
        3.2.10
    
        演示集合数据,可以参考:http://blog.csdn.net/leshami/article/details/52672310
    
        > db.persons.find().limit(1).pretty()
        {
                "_id" : ObjectId("5812cbaaa129eed14b46458d"),
                "name" : "robinson.cheng",
                "age" : 25,
                "email" : "robinson.cheng@qq.com",
                "score" : {
                        "c" : 89,
                        "m" : 96,
                        "e" : 87
                },
                "country" : "USA",
                "books" : [
                        "JS",
                        "C++",
                        "EXTJS",
                        "MONGODB"
                ]
        }
    

    2、单个键(列)上的唯一索引

        > db.persons.createIndex({name:1},{unique:true})
        {
                "createdCollectionAutomatically" : false,
                "numIndexesBefore" : 1,
                "numIndexesAfter" : 2,
                "ok" : 1
        }
        > db.persons.getIndexes()
        [
                {
                        "v" : 1,
                        "unique" : true,    //此处表示这个索引为唯一索引
                        "key" : {
                                "name" : 1
                        },
                        "name" : "name_1",
                        "ns" : "test.persons"
                }
        ]
    

    3、基于复合键(列)的唯一索引

        > db.persons.createIndex({name:1,email:1},{unique:true})
        {
                "createdCollectionAutomatically" : false,
                "numIndexesBefore" : 2,
                "numIndexesAfter" : 3,
                "ok" : 1
        }
        > db.persons.getIndexes()
        [
                 {
                        "v" : 1,
                        "unique" : true,  //此处表示这个索引为唯一索引
                        "key" : {
                                "name" : 1,  //这个是索引对应的键,包括name和email
                                "email" : 1
                        },
                        "name" : "name_1_email_1",
                        "ns" : "test.persons"
                }
        ]
    

    4、创建唯一索引的一些限制

        对于那些已经存在非唯一的列,在其上面创建唯一索引将失败
        不能够基于一个哈希索引指定唯一性
    
        Unique Constraint Across Separate Documents
    
        唯一的约束适用于集合中的单独的文档。也就是说,唯一的索引可以防止不同的文档具有相同的索引键值,
        但索引并不能阻止在基于数组或者内嵌文档创建的唯一索引上具有多个相同的值。
        在一个具有重复值的单个文档的情况下,重复的值仅插入到该索引一次。
    
        假定存在以下集合
        > db.collection.insert( { a: [ { b: 4 }, { b: 4 } ] } )
        WriteResult({ "nInserted" : 1 })
    
        > db.collection.find()
        { "_id" : ObjectId("58199898ae431a4615ec75ac"), "a" : [ { "b" : 4 }, { "b" : 4 } ] }
    
        在集合上a.b上创建一个唯一索引
        > db.collection.createIndex( { "a.b": 1 }, { unique: true } )
        {
                "createdCollectionAutomatically" : false,
                "numIndexesBefore" : 1,
                "numIndexesAfter" : 2,
                "ok" : 1
        }
    
        如果集合中没有文档上存在a.b为5的值,则唯一索引允许以下文档插入集合
        > db.collection.insert( { a: [ { b: 5 }, { b: 5 } ] } )
        WriteResult({ "nInserted" : 1 })
        > db.collection.insert( { a: [ { b: 5 }, { b: 5 } ] } )   //再次插入则提示错误
        WriteResult({
                "nInserted" : 0,
                "writeError" : {
                        "code" : 11000,
                        "errmsg" : "E11000 duplicate key error collection: test.collection index: a.b_1 dup key: { : 5.0 }"
                }
        })
    
        > db.collection.insert( { a: [ { b: 5 }] } )  //再次插入则提示错误,即使是该文档仅有一个相同的数组元素
        WriteResult({
                "nInserted" : 0,
                "writeError" : {
                        "code" : 11000,
                        "errmsg" : "E11000 duplicate key error collection: test.collection index: a.b_1 dup key: { : 5.0 }"
                }
        })
        > db.collection.insert( { a: [ { b: 6 }] } )
        WriteResult({ "nInserted" : 1 })
    
        Unique Index and Missing Field
    
        如果一个文档在一个唯一索引中没有索引字段的值,则该索引将为该文档存储一个空值。
        由于唯一约束限制,MongoDB只会允许一个文档缺少索引字段。
        对多于一个以上的文档没有索引字段的值或缺少索引字段,索引构建将失败,提示重复键错误。
    
        假定存在如下集合
        > db.mycol.insert({x:1})
        WriteResult({ "nInserted" : 1 })
    
        //为集合添加唯一索引
        > db.mycol.createIndex( { "x": 1 }, { unique: true } )
        {
                "createdCollectionAutomatically" : false,
                "numIndexesBefore" : 1,
                "numIndexesAfter" : 2,
                "ok" : 1
        }
    
        //再次插入x:1,如下,我们收到了错误提示
        > db.mycol.insert({x:1})
        WriteResult({
                "nInserted" : 0,
                "writeError" : {
                        "code" : 11000,
                        "errmsg" : "E11000 duplicate key error collection: test.mycol index: x_1 dup key: { : 1.0 }"
                }
        })
    
        //向集合插入文档x:2,y:2
        > db.mycol.insert({x:2,y:2})
        WriteResult({ "nInserted" : 1 })
    
        //插入一个缺少x键的新文档,如下,可以成功插入
        > db.mycol.insert({y:2})
        WriteResult({ "nInserted" : 1 })
    
        //再次插入一个缺少x键的新文档,提示重复
        > db.mycol.insert({z:1})
        WriteResult({
                "nInserted" : 0,
                "writeError" : {
                        "code" : 11000,
                        "errmsg" : "E11000 duplicate key error collection: test.mycol index: x_1 dup key: { : null }"
                }
        })
    

    三、更多参考

    MongoDB 单键(列)索引
    MongoDB 复合索引
    MongoDB 多键索引
    MongoDB执行计划获取(db.collection.explain())

    DBA牛鹏社(SQL/NOSQL/LINUX)

    展开全文
  • B树与红黑树最广泛的应用就是数据库索引,熟练使用索引是程序员最重要的基本功之一。索引的数据结构可以是树,也可以是哈希表。常用的数据库都是树结构的索引,本篇的背景也全部以树结构的索引为前提。本文旨在梳理...

    B树与红黑树最广泛的应用就是数据库索引,熟练使用索引是程序员最重要的基本功之一。索引的数据结构可以是树,也可以是哈希表。常用的数据库都是树结构的索引,本篇的背景也全部以树结构的索引为前提。本文旨在梳理各种常见的索引类型,简明扼要地说明它们的区别与联系,不讨论数据结构与算法。话不多说,直接上干货。

    索引

    数据库只做两件事情:存储数据、检索数据。而索引是在你存储的数据之外,额外保存一些路标(一般是B+树),以减少检索数据的时间。所以索引是主数据衍生的附加结构。

    一张表可以建立任意多个索引,每个索引可以是任意多个字段的组合。索引可能会提高查询速度(如果查询时使用了索引),但一定会减慢写入速度,因为每次写入时都需要更新索引,所以索引只应该加在经常需要搜索的列上,不要加在写多读少的列上。

    单列索引 与 复合索引

    只包含一个字段的索引叫做单列索引,包含两个或以上字段的索引叫做复合索引(或组合索引)。建立复合索引时,字段的顺序极其重要。

    下面这个SQL语句在 列X,列Y,列Z 上建立了一个复合索引。

    CREATE INDEX 索引名 ON 表名(列名X, 列名Y, 列名Z);
    其实这相当于建立了三个索引,分别是:

    1、单列索引(列X) 2、复合索引(列X, 列Y) 3、复合索引(列X,列Y,列Z)。

    如何理解呢?

    我们可以把多个字段组合的索引比喻成一个老式的纸质电话簿,三列分别是:

    姓 - 名 - 电话号码

    电话簿中的内容先按照姓氏的拼音排序,相同姓氏再按名字的拼音排序,这相当于在(姓,名)上建立了一个复合索引。你可以通过这个索引快速找到所有具有特定姓氏的人的电话号码,也可以快速找到具有特定 姓-名 组合的人的电话号码。然而,想象一下,如果你想找到某个特定名字的人,其实这个索引是没有用的,你只能从头到尾遍历整个电话簿。

    唯一索引 与 主键

    唯一索引是在表上一个或者多个字段组合建立的索引,这个(或这几个)字段的值组合起来在表中不可以重复。一张表可以建立任意多个唯一索引,但一般只建立一个。

    主键是一种特殊的唯一索引,区别在于,唯一索引列允许null值,而主键列不允许为null值。一张表最多建立一个主键,也可以不建立主键。

    聚簇索引、非聚簇索引、主键

    在《数据库原理》一书中是这么解释聚簇索引和非聚簇索引的区别的:

    聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。

    怎么理解呢?

    聚簇索引的顺序,就是数据在硬盘上的物理顺序。一般情况下主键就是默认的聚簇索引。

    一张表只允许存在一个聚簇索引,因为真实数据的物理顺序只能有一种。如果一张表上还没有聚簇索引,为它新创建聚簇索引时,就需要对已有数据重新进行排序,所以对表进行修改速度较慢是聚簇索引的缺点,对于经常更新的列不宜建立聚簇索引。

    聚簇索引性能最好,因为一旦具有第一个索引值的记录被找到,具有连续索引值的记录也一定物理地紧跟其后。一张表只能有一个聚簇索引,所以非常珍贵,必须慎重设置,一般要根据这个表最常用的SQL查询方式选择某个(或多个)字段作为聚簇索引(或复合聚簇索引)。

    聚簇索引默认是主键,如果表中没有定义主键,InnoDB[1]会选择一个唯一的非空索引代替(“唯一的非空索引”是指列不能出现null值的唯一索引,跟主键性质一样)。如果没有这样的索引,InnoDB会隐式地定义一个主键来作为聚簇索引。

    聚簇索引 与 唯一索引

    严格来说,聚簇索引不一定是唯一索引,聚簇索引的索引值并不要求是唯一的,唯一聚簇索引才是!在一个有聚簇索引的列上是可以插入两个或多个相同值的,这些相同值在硬盘上的物理排序与聚簇索引的排序相同,仅此而已

    展开全文
  • 索引之---- 唯一索引和组合索引

    千次阅读 2019-06-19 11:01:37
    之前一直没明白唯一索引和组合索引是做什么用,用在什么地方,怎么用的。看了网上很多的博客,愣是没看懂。最近项目刚好涉及到这方面,所以码一下使用经验。 普通索引就不解释了,直接上硬货: 序号① 为唯一...
  • 唯一索引与主键索引的比较

    千次阅读 2019-02-20 23:02:51
    唯一索引 唯一索引不允许两行具有相同的索引值。 如果现有数据中存在重复的键值,则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时,数据库也拒绝接受此数据。例如,如果在 ...
  • 普通索引和唯一索引的区别

    千次阅读 2018-10-11 10:13:15
    唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。 1、普通索引  普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件...
  • 当Mysql中建立的联合索引, 只要索引中的某一列的值为空时(NULL),即便其他的字段完全相同,也不会引起唯一索引冲突。 原因: Mysql官方文档中有这样的解释 A UNIQUE index creates a constraint such that all ...
  • 浅谈主键索引与唯一索引

    千次阅读 2017-11-16 18:57:38
    主键索引与唯一索引 一、主键索引定义 主键索引是唯一索引的特殊类型。 数据库表通常有一列或列组合,其值用来唯一标识表中的每一行。该列称为表的主键。 在数据库关系图中为表定义一个主键将自动创建主键索引,...
  • 数据库索引

    千次阅读 多人点赞 2019-08-20 22:49:54
    文章目录数据库索引定义优缺点索引类型建立普通索引或组合索引适合建立索引的情况索引失效的sql 定义 索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。数据库索引...
  • ORA-00001: 违反唯一约束条件 这一个报错相信大家在插入数据时还是经常遇到的,尤其是在测试环境。 但是今天我在处理一个生产问题的时候再次遇到这个报错时有点奇怪: 1.该表(记为表A)的主键是数据库序列生成的,...
  • oracle 唯一约束 和 唯一索引

    千次阅读 2016-07-18 11:43:21
    唯一约束 和 唯一索引
  • 唯一索引和普通索引的区别

    千次阅读 2018-11-17 00:25:07
    索引用来快速地寻找那些具有特定值的记录,如果没有索引,执行查询时Mysql必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录,表里面的记录数量越多,这个操作的代价就越高,如果作为搜索条件的列...
  • 唯一索引/非唯一索引主键索引(主索引)聚集索引/非聚集索引组合索引唯一索引/非唯一索引唯一索引1.唯一索引是在表上一个或者多个字段组合建立的索引,这个或者这些字段的值组合起来在表中不可以重复。非唯一索引2....
  • 在DB2中,添加unique关键词可以创建表索引列的唯一索引。 ( CREATE UNIQUE INDEX indname ON tabname(colname) ) 那么唯一索引与一般索引有何区别呢?我们来从下面两个方面来看。 一.数据加载性能 为什么说...
  • ... 索引是一种特殊的文件,它们包含着对所有记录的引用指针。索引可以极大地提高数据查询速度,但是会降低插入删除更新表的速度,因为在执行这些操作是,还要操作索引文件用来维护,所以说索引不是建的越多...
  • mysql唯一索引冲突的解决方案

    千次阅读 2019-04-08 23:50:02
    如果是用主键primary或者唯一索引unique区分了记录的唯一性,避免重复插入记录可以使用: insert ignore into insert ignore into(使用唯一索引再次插入相同的值会忽略提示*如果insert into 数据库会报错*,相同值...
  • 当Mysql中建立的联合索引, 只要索引中的某一列的值为空时(NULL),即便其他的字段完全相同,也不会引起唯一索引冲突。 原因: Mysql官方文档中有这样的解释 A UNIQUE index creates a constraint such that all ...
  • 今天正在吃饭,一个朋友提出了一个他面试中遇到的问题,MySQL允许在唯一索引字段中添加多个NULL值。 这个问题对于我一个非专业DBA来说,也没特地去验证过,所以正好借此机会验证一下,做个记录: 测试环境: ...
  • 唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。 1、普通索引    普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件...
  • 在创建主键的同时会生成对应的唯一索引,主键在保证数据唯一性的同时不允许为空, 而唯一索引可以有一个为空的数据项,一个表中只能有一个主键,但是一个主键可以有多个字段,一个表中可以有多个唯一索引。 联合...
  • MySQL:主键与唯一索引

    千次阅读 2018-11-06 11:30:50
    所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。 因为主键可以唯一标识某一行记录,所以可以确保执行数据更新、删除的时候不会出现张冠李戴的错误。主键除了上述...
  • 有时候一个表多字段,要实现字段之间两两联合唯一怎么办了?下面就是办法!!! class UserOrderHouse(models.Model): """ 客户预约表 """ broker_id = models.ForeignKey(to="Broker", verbose_name="经纪人", ...
  • 一、 MySQL: 索引以B树格式保存 Memory存储引擎可以选择Hash或BTree索引,Hash索引只能用于=或&lt;=&gt;的等式比较。  1、普通索引:create index 索引名 Tablename(列的列表)  alter table TableName ...
  •  也有两个作用,一是约束作用(constraint),规范数据的唯一性,但同时也在这个key上建立了一个唯一索引; UNIQUE 约束:唯一标识数据库表中的每条记录。  UNIQUE 和 PRIMARY KEY 约束均为列或列集合...
  • 例如,user表中有user_id,user_name两个字段,如果不希望有两条一摸一样的的user_id和user_name,我们可以给user表添加两个字段的联合唯一索引: alter table user add unique index(user_id,user_name); 这样当向...
  • MYSQL索引作用以及如何创建索引

    千次阅读 2019-06-27 14:58:13
    索引是什么,首先我们可以举个例子,字典大家应该都使用过,我们可以使用目录快速定位到所要查找的内容,那么索引跟目录的作用类似,在数据库表记录中,利用索引,可以快速过滤查找到数据记录。 一般的应用系统,...
  • MySQL 普通索引、唯一索引和主索引

    千次阅读 2017-12-20 13:30:59
     普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。因此,应该只为那些最经常出现在查询条件(WHERE column = ...)或排序条件(ORDER BY column)中的数据列创建索引。只要有可能,就应该...
  • 例如,user表中有user_id,user_name两个字段,如果不希望有两条一摸一样的的user_id和user_name,我们可以给user表添加两个字段的联合唯一索引:alter table user add unique index(user_id,user_name);这样当向表中...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 304,066
精华内容 121,626
关键字:

唯一索引的作用是a