美文网首页
mysql索引(七)唯一索引

mysql索引(七)唯一索引

作者: camellias__ | 来源:发表于2021-07-23 09:10 被阅读0次

    Mysql索引大概有五种类型:

    普通索引(INDEX):最基本的索引,没有任何限制
    唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
    主键索引(PRIMARY):它 是一种特殊的唯一索引,不允许有空值。
    全文索引(FULLTEXT ):可用于 MyISAM 表,mysql5.6之后也可用于innodb表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时和空间。
    联合(组合)索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

    这里我们来看下唯一索引。

    之前我们看了主键索引,他是一种特殊的唯一索引,二者的区别是,主键索引不能有空值,但是唯一索引可以有空值。

    一:基本使用

    1:唯一索引可以是单列,也可以是多列,下面我们来看下创建语句:

    # 创建单列唯一索引
    
    alter table sc add unique (name);
    # 创建多列唯一索引
    
    alter table sc add unique key `name_score` (`name`,`score`);
    

    2:删除唯一索引语句:

    alter table sc drop index name;

    在这里插入代码片
    

    二:唯一索引作用

    1:最大的所用就是确保写入数据库的数据是唯一值。

    单列唯一值基本上就是主键。

    常用的一般都是多列的唯一索引,比如:当前商品,一个用户只能购买一件。我们将用户id及商品id列设置成唯一索引。那么就可以避免一个用户出现重复购买的情况。

    示例:

    创建数据表:

    MariaDB [test]> CREATE TABLE t(
        -> c1 CHAR(1) not null,
        -> c2 CHAR(1) not null,
        -> c3 CHAR(1) not null,
        -> c4 CHAR(1) not null,
        -> c5 CHAR(1) not null
        -> )ENGINE myisam CHARSET UTF8;
    Query OK, 0 rows affected (0.09 sec)
    

    添加几条数据:

    MariaDB [test]> insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2'),('3','3','3','3','3'),('4','4','4','4','4'),('5','5','5','5','5');
    Query OK, 5 rows affected (0.00 sec)
    Records: 5  Duplicates: 0  Warnings: 0
    

    创建唯一索引:

    MariaDB [test]> alter table t add unique key `name_score` (`c2`,`c3`);
    Query OK, 5 rows affected (0.01 sec)              
    Records: 5  Duplicates: 0  Warnings: 0
    

    写入重复数据失败:

    MariaDB [test]> insert into t (c1,c2,c3,c4,c5) values ('1','1','1','1','1');
    ERROR 1062 (23000): Duplicate entry '1-1' for key 'name_score'
    

    2:提高查询速度

    使用index索引时,经常导致慢查询,耗时2秒左右,遇忙时更有达到5秒的

    改用unique之后,查询耗时在0.0003秒 基本可以忽略不计

    三:唯一索引和主键索引的具体区别

    1:唯一性约束所在的列允许空值,但是主键约束所在的列不允许空值。
    2:可以把唯一性约束放在一个或者多个列上,这些列或列的组合必须有唯一的。但是,唯一性约束所在的列并不是表的主键列。
    3:唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下,创建唯一性的非聚簇索引,但是,也可以指定所创建的索引是聚簇索引。
    4:建立主键的目的是让外键来引用.
    5: 一个表最多只有一个主键,但可以有很多唯一键

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

    1:使用insert ignore语句

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

    MariaDB [test]> insert ignore into t (c1,c2,c3,c4,c5) values ('1','1','1','1','2');
    Query OK, 0 rows affected, 1 warning (0.00 sec)
     
    MariaDB [test]> select * from t;
    +----+----+----+----+----+
    | c1 | c2 | c3 | c4 | c5 |
    +----+----+----+----+----+
    | 1  | 1  | 1  | 1  | 1  |
    | 2  | 2  | 2  | 2  | 2  |
    | 3  | 3  | 3  | 3  | 3  |
    | 4  | 4  | 4  | 4  | 4  |
    | 5  | 5  | 5  | 5  | 5  |
    +----+----+----+----+----+
    5 rows in set (0.00 sec)
    

    Sql执行成功,但是我们查询表的数据,发现并没有写入成功。是因为唯一索引已经存在。跳过了这条写入的命令。

    2:使用replace into语句

    replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据,否则,直接插入新数据。

    使用replace into,你必须具有delete和insert权限

    示例:

    MariaDB [test]> replace into t (c1,c2,c3,c4,c5) values ('1','1','1','1','2');
    Query OK, 2 rows affected (0.00 sec)
    
    # 先执行删除,再执行写入
     
    MariaDB [test]> select * from t;
    +----+----+----+----+----+
    | c1 | c2 | c3 | c4 | c5 |
    +----+----+----+----+----+
    | 1  | 1  | 1  | 1  | 2  |
    | 2  | 2  | 2  | 2  | 2  |
    | 3  | 3  | 3  | 3  | 3  |
    | 4  | 4  | 4  | 4  | 4  |
    | 5  | 5  | 5  | 5  | 5  |
    +----+----+----+----+----+
    5 rows in set (0.00 sec)
    

    3:使用insert on duplicate key update语句

    如果在insert into 语句末尾指定了on duplicate key update,并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值,则在出现重复值的行执行UPDATE;如果不会导致重复的问题,则插入新行,跟普通的insert into一样。

    使用insert into,你必须具有insert和update权限

    如果有新记录被插入,则受影响行的值显示1;如果原有的记录被更新,则受影响行的值显示2;如果记录被更新前后值是一样的,则受影响行数的值显示0

    示例:

    MariaDB [test]> insert into t (c1,c2,c3,c4,c5) values ('1','1','1','1','2') on duplicate key update c2 = 5;
    Query OK, 2 rows affected (0.00 sec)
     
    MariaDB [test]> select * from t;
    +----+----+----+----+----+
    | c1 | c2 | c3 | c4 | c5 |
    +----+----+----+----+----+
    | 1  | 5  | 1  | 1  | 2  |
    | 2  | 2  | 2  | 2  | 2  |
    | 3  | 3  | 3  | 3  | 3  |
    | 4  | 4  | 4  | 4  | 4  |
    | 5  | 5  | 5  | 5  | 5  |
    +----+----+----+----+----+
    5 rows in set (0.00 sec)
    

    这里需要注意一下:

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

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

    因此在使用的时候尽量避免:

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

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

    4:总结

    这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。

    insert ignore能忽略重复数据,只插入不重复的数据。

    replace into和insert ... on duplicate key update,都是替换原有的重复数据,区别在于replace into是删除原有的行后,在插入新行,如有自增id,这个会造成自增id的改变;insert ... on duplicate key update在遇到重复行时,会直接更新原有的行,具体更新哪些字段怎么更新,取决于update后的语句。

    有好的建议,请在下方输入你的评论。

    欢迎访问个人博客
    https://guanchao.site

    相关文章

      网友评论

          本文标题:mysql索引(七)唯一索引

          本文链接:https://www.haomeiwen.com/subject/zxmlmltx.html