MySQL唯一索引和普通索引选哪个?_Mysql

来源:脚本之家  责任编辑:小易  

普通索引 这是最基本的索引类型,而且它没有唯一性之类的限制。 唯一性索引 这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一www.zgxue.com防采集请勿采集本网。

想象这样一个场景,在设计一张用户表时,每人的身份证号是唯一的,需要搜索。但由于身份证号字段较大,不好将其作为主键。在业务代码已经保证插入身份证唯一的情况下,可以选择建立唯一索引和普通索引,这时该如何选择呢?接下来,将从查询和更新的执行过程进行分析。

两者有类似的功能,值都是必须唯一,但是不是主键的字段或者几个字段可以设置为唯一索引。

查询过程

主键约束比唯一索引约束严格,当没有设定主键时,非空唯一索引自动称为主键。对于主键和唯一索引的一些区别主要如下: 1.主键不允许空值,唯一索引允许空值 2.主键只允许一个,唯一索引允许多个 3.主键产生唯一的聚集索引,唯一索引产生唯一的非

假设 k 是表 t 上的索引,在搜索 select id from t where k=5 时,会先从 k 这棵 B+ 的树根开始,按层搜索叶子节点,找到 k=5 的数据页,然后在数据页内容进行二分法定位。

主键索引是在创建主键时一起创建的,是基于主键约束而建立的,是不可以为空,也不可以重复 . 而唯一索是引基于唯一约束而建立的,可以为空不可以重复 主键索引本身就具备了唯一索引的功能

对于普通索引,找到 k=5 的记录后,会继续向下查找一个,直到碰到第一个不是 5 的记录结束。

先创建普通表,复制到普通表。 然后删除,复制过来就行了。

对于唯一索引,由于取值唯一,找到后直接停止。

普通索引:最基本的索引,没有任何限制 唯一索引:与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。 主键索引:它 是一种特殊的唯一索引,不允许有空值。 全文索引:仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时

由于 InnoDB 是按照数据页为单位(数据页默认 16 KB)进行读写的,在读取一条数据时,会将整个数据页整体读到内存。 在读入内存的数据页中,如果包含 k=5 的记录,在查询的情况下,唯一索引比普通索引多了一次查找和判断的过程,可以忽略。

如果 k=5 是当前数据页的最后一条,就需要在读取下一个数据页。但这发生的概率较低,也可以忽略。

所以总得来说,普通索引和唯一索引在查询的过程中差异不大。

change buffer

在分析唯一索引和普通索引的影响前,先来认识一下 change buffer 这个结构。

什么是 change buffer ?

在执行更新操作时,如果要更新的数据页在内存中就直接更新,否则的话,在不影响数据一致性的前提下,InnoDB 会将更新操作缓存在 change buffer 中,从而省去了从磁盘读取数据页的过程。在下次查询操作读取到恰好需要更新的数据页时,会将 change buffer 的更新语句执行,写入数据页。将操作应用到硬盘的过程叫 merge. 后台线程会定期 merge 或 数据库正常关闭时,也会进行 merge 操作。

merge 的执行流程:

    从磁盘读入老版本数据页。 从 change buffer中找出和该数据页关联的记录,依次应用,得到新版数据页。 写 redo log,记录数据的变更和 change buffer 的变更。

change buffer 实际上是可以持久化到硬盘中的数据,也就是说在内存和硬盘上都 change buffer 的存在。change buffer 之前叫 insert buffer,开始只对 insert buffer 有优化,后来加上了对 delete 和 update 的支持,进而改名叫 change buffer。

可以看到,先将更新操作记录在 change buffer,减少了将磁盘数据页读取到内存的过程,语句的执行速度会有很明显的提升。同时,将数据读入内存,会占用 buffer pool 内存,所以减少读操作,还提高了内存使用率。

Buffer Pool 是内存中的一个区域,InnoDB 在访问表和索引数据时会在其中进行缓存。允许在内存中直接更新经常使用的数据,来加快处理速度。在一些专用的服务器上,会将 80% 的物理内存分为 buffer pool.

可以通过 innodb_change_buffer_max_size 来设置 change buffer 占用 buffer pool 的大小。

change buffer 应用场景?

如上面提到,change buffer 预先保存了更新记录,减少了读取数据页的过程,从而提高性能。也就是说如果 change buffer 中针对不同的数据页如果包含的更新记录越多,其实收益也就越大。

因此对于写多读少的业务(更新完立即查询)change buffer 发挥的作用也就越大。如常见的账单类,日志类等系统。

如果业务是更新完立即查询,虽然可以将更新记录放在 change buffer 中,但由于之后要马上查询数据页,所以会立即触发 merge 过程。这样随机访问 IO 次数并不会减少,反而增加了 change buffer 的维护代价,起到反效果。

更新过程

对于唯一索引来说,所有的更新操作都需要判断是否违反唯一性约束。所以必须把所需要的数据页读入内存,然后直接更新就可以,不需要使用 change buffer. 所以 change buffer 只对普通索引有用。

具体分析下,对于一张表插入一个新记录:

如果新记录要更新的数据页在内存中:

对于唯一索引,找到合适的位置,判断有没有冲突,插入值,语句结束。

对于普通索引:找到位置,插入值,语句结束。

所以数据页在内存时,唯一和普通索引就差一个判断的过程。可以忽略。

如果新记录要更新的数据页不在内存中:

对于唯一索引,将数据页读入内存,判断冲突,插入,语句结束。

对于普通索引,将语句记录在 change buffer 中,语句结束。

由于从磁盘到内存涉及随机 IO 访问,是数据库成本最高的操作之一。普通索引比唯一索引减少的读入操作,可以有很好的性能提升。

唯一或普通索引的选择

通过在查询和更新方面,两者的比较。我们知道,在查询过程中,除了极特殊情况,其实两者的差异并不大。

主要的差异是在更新过程中,要更新的数据页并不在内容中的情况。这时唯一索引,由于需要唯一性检查,不能利用 change buffer. 多了从磁盘到内容读取数据的过程,其中涉及随机 IO 的访问,相对来说效率就低了。

所以如果业务需要更新不错的性能,这时可以选用普通索引。当然一切都是建立在能保证数据准确性的前提下。

当如果更新后来紧接着查询操作,可以考虑关掉 change buffer. 其他的情况,change buffer 都能有很好的提升。

特别针对机械硬盘,change buffer 效果很显著。

redo log 和 change buffer 的比较

InnoDB 中 redo log 的出现使其具有了 crash-safe 的能力,同时还提高了效率,通过 WAL 先写日志,再写磁盘。

而 change buffer 是节省了从磁盘读入数据页到内存的随机IO过程。

下面通过一条插入语句来分析下两者间的关系:

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

假设 k 为普通索引,k1 所插入的数据页在内存中, k2 不在。

执行插入操作时,主要涉及了图中这四部分的内容:

InnoDB buffer pool:内存区域

redo log:日志

system table space(ibdata1):系统表空间

data(t.idb): 数据表空间

innodb_file_per_table 开启时,表被创建在独立的表空间下,否则的话被创建在系统的表空间下。

执行过程如下:

    k1 所在的 page1 在内存中,直接更新内存 k2 所在的 page2 不在内存中,记录在 change buffer. 将 k1 和 k2 的操作记录在 redo log. 提交事务。

可以看到这条更新语句(包括插入,删除,更新操作)执行成本很低,两次写入内存,1次顺序写入磁盘。虚线的操作,是后台操作,不影响响应时间。

再来看一条查询语句:

select * from t where k in (k1, k2)

假设读语句发生在更新语句不久,内存数据还在,此时读操作就和系统表空间和 redo log 无关。

执行过程:

    读取 k1 所在的 page1,在内存中,直接返回。注意,并没有读磁盘上的数据,而且磁盘上的数据还有可能是之前的版本的。 读取 k2 所在的 page2,这时需要将 page2 从磁盘加载到内存,并应用 change buffer 的内容,然后返回正确的结果。从这里也能看出,change buffer 不适用于更新完立马去读的情况。

总结下 redo log 和 change buffer 的关系:

存储位置:change buffer 也会持久化在硬盘里,但保存在系统表空间 ibdata1 里。而 redo log 是单独的文件。

记录内容:change buffer 记录的是更新操作的内容,而 redo log 记录的是普通数据页的修改和 change buffer 的改动。

同步磁盘过程:同步内存中数据页的修改时通过 merge 操作进行的,而不是根据 redo log.

从更新的过程来看: redo log 将随机写磁盘的 IO 转换成了顺序写,而 change buffer 则是节省了随机读磁盘的 IO 消耗。

如果服务器异常掉电,会不会导致 change buffer 丢失?

并不会,因为 change buffer 中的数据已经被记录到 redo log 中,所以不会丢失。

由于 change buffer 一部分数据在磁盘,一部分在内存。对于在磁盘的数据已经 merge 所以不会丢失。

对于在内存中的数据:

    如果 change buffer 写入,但 redo log 未提交,binlog 未提交,事务会回滚,这部分数据不存在。 如果 change buffer 写入,redo log 写入,binlog 写入,并已提交,不会丢失。从 redo log 直接恢复。 如果 change buffer 写入,redo log 写入但未 commit,binlog 写入,从 binlog 恢复 redo log 再恢复 change buffer.

参考资料

Buffer Pool

以上就是MySQL唯一索引和普通索引选哪个?的详细内容,更多关于MySQL唯一索引和普通索引的资料请关注真格学网其它相关文章! 您可能感兴趣的文章:MySQL普通索引和唯一索引的深入讲解mysql下普通索引和唯一索引的效率对比MySQL选错索引的原因以及解决方案MySql索引提高查询速度常用方法代码示例MySQL索引失效的几种情况汇总

普通索引这是最基本的索引类型,而且它没有唯一性之类的限制。唯一性索引这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一,普通索引是可以重复的,唯一索引和主键不能重复唯一索引可以作为数据的一个合法验证手段,例如学生表的身份证号码字段,我们人为规定该字段不得重复,那么就使用唯一索引。(一般设置学号字段为主键),索引是可以有重复数据的,主键其实就是一种唯一索引,不可重复,在满足语句2113需求的情况下,尽量少的访问5261资源是数据库设4102计的重要原则,这和执1653行的 SQL 有直接的关系,索引问题又是 SQL 问题中出现频率最高的,常见的索引问题包括:无索引(失效)、隐式转换。1. SQL 执行流程看一个问题,在下面这个表 T 中,如果我要执行 select * from T where k between 3 and 5; 需要执行几次树的搜索操作,会扫描多少行?mysql> create table T (    -> ID int primary key,    -> k int NOT NULL DEFAULT 0,    -> s varchar(16) NOT NULL DEFAULT '',    -> index k(k))    -> engine=InnoDB;mysql> insert into T values(100,1, 'aa'),(200,2,'bb'),\      (300,3,'cc'),(500,5,'ee'),(600,6,'ff'),(700,7,'gg');这分别是 ID 字段索引树、k 字段索引树。 这条 SQL 语句的执行流程:1. 在 k 索引树上找到 k=3,获得 ID=3002. 回表到 ID 索引树查找 ID=300 的记录,对应 R33. 在 k 索引树找到下一个值 k=5,ID=5004. 再回到 ID 索引树找到对应 ID=500 的 R45. 在 k 索引树去下一个值 k=6,不符合条件,循环结束这个过程读取了 k 索引树的三条记录,回表了两次。因为查询结果所需要的数据只在主键索引上有,所以必须得回表。所以,我们该如何通过优化索引,来避免回表呢?2. 常见索引优化2.1 覆盖索引覆盖索引,换言之就是索引要覆盖我们的查询请求,无需回表。如果执行的语句是 select ID from T wherek between 3 and 5;,这样的话因为 ID 的值在 k 索引树上,就不需要回表了。覆盖索引可以减少树的搜索次数,显著提升查询性能,是常用的性能优化手段。但是,维护索引是有代价的,所以在建立冗余索引来支持覆盖索引时要权衡利弊。2.2 最左前缀原则B+ 树的数据项是复合的数据结构,比如 (name,sex,age) 的时候,B+ 树是按照从左到右的顺序来建立搜索树的,当 (张三,F,26) 这样的数据来检索的时候,B+ 树会优先比较 name 来确定下一步的检索方向,如果 name 相同再依次比较 sex 和 age,最后得到检索的数据。# 有这样一个表 Pmysql> create table P (id int primary key, name varchar(10) not null, sex varchar(1), age int, index tl(name,sex,age)) engine=IInnoDB;mysql> insert into P values(1,'张三','F',26),(2,'张三','M',27),(3,'李四','F',28),(4,'乌兹','F',22),(5,'张三','M',21),(6,'王五','M',28);# 下面的语句结果相同mysql> select * from P where name='张三' and sex='F';     ## A1mysql> select * from P where sex='F' and age=26;         ## A2# explain 看一下mysql> explain select * from P where name='张三' and sex='F';+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra       |+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+|  1 | SIMPLE      | P     | NULL       | ref  | tl            | tl   | 38      | const,const |    1 |   100.00 | Using index |+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------------+mysql> explain select * from P where sex='F' and age=26;+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                    |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+|  1 | SIMPLE      | P     | NULL       | index | NULL          | tl   | 43      | NULL |    6 |    16.67 | Using where; Using index |+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+可以清楚的看到,A1 使用 tl 索引,A2 进行了全表扫描,虽然 A2 的两个条件都在 tl 索引中出现,但是没有使用到 name 列,不符合最左前缀原则,无法使用索引。所以在建立联合索引的时候,如何安排索引内的字段排序是关键。评估标准是索引的复用能力,因为支持最左前缀,所以当建立(a,b)这个联合索引之后,就不需要给 a 单独建立索引。原则上,如果通过调整顺序,可以少维护一个索引,那么这个顺序往往就是需要优先考虑采用的。上面这个例子中,如果查询条件里只有 b,就是没法利用(a,b)这个联合索引的,这时候就不得不维护另一个索引,也就是说要同时维护(a,b)、(b)两个索引。这样的话,就需要考虑空间占用了,比如,name 和 age 的联合索引,name 字段比 age 字段占用空间大,所以创建(name,age)联合索引和(age)索引占用空间是要小于(age,name)、(name)索引的。2.3 索引下推以人员表的联合索引(name, age)为例。如果现在有一个需求:检索出表中“名字第一个字是张,而且年龄是26岁的所有男性”。那么,SQL 语句是这么写的mysql> select * from tuser where name like '张%' and age=26 and sex=M;通过最左前缀索引规则,会找到 ID1,然后需要判断其他条件是否满足在 MySQL 5.6 之前,只能从 ID1 开始一个个回表。到主键索引上找出数据行,再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown),可以在索引遍历过程中,对索引中包含的字段先做判断,直接过滤掉不满足条件的记录,减少回表次数。这样,减少了回表次数和之后再次过滤的工作量,明显提高检索速度。2.4 隐式类型转化隐式类型转化主要原因是,表结构中指定的数据类型与传入的数据类型不同,导致索引无法使用。所以有两种方案:修改表结构,修改字段数据类型。修改应用,将应用中传入的字符类型改为与表结构相同类型。3. 为什么会选错索引3.1 优化器选择索引是优化器的工作,其目的是找到一个最优的执行方案,用最小的代价去执行语句。在数据库中,扫描行数是影响执行代价的因素之一。扫描的行数越少,意味着访问磁盘数据的次数越少,消耗的 CPU 资源越少。当然,扫描行数并不是唯一的判断标准,优化器还会结合是否使用临时表、是否排序等因素进行综合判断。3.2 扫描行数MySQL 在真正开始执行语句之前,并不能精确的知道满足这个条件的记录有多少条,只能通过索引的区分度来判断。显然,一个索引上不同的值越多,索引的区分度就越好,而一个索引上不同值的个数我们称为“基数”,也就是说,这个基数越大,索引的区分度越好。# 通过 show index 方法,查看索引的基数mysql> show index from t;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| t     |          0 | PRIMARY  |            1 | id          | A         |       95636 |     NULL | NULL   |      | BTREE      |         |               || t     |          1 | a        |            1 | a           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               || t     |          1 | b        |            1 | b           | A         |       96436 |     NULL | NULL   | YES  | BTREE      |         |               |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+MySQL 使用采样统计方法来估算基数:采样统计的时候,InnoDB 默认会选择 N 个数据页,统计这些页面上的不同值,得到一个平均值,然后乘以这个索引的页面数,就得到了这个索引的基数。而数据表是会持续更新的,索引统计信息也不会固定不变。所以,当变更的数据行数超过 1/M 的时候,会自动触发重新做一次索引统计。在 MySQL 中,有两种存储索引统计的方式,可以通过设置参数 innodb_stats_persistent 的值来选择:on 表示统计信息会持久化存储。默认 N = 20,M = 10。off 表示统计信息只存储在内存中。默认 N = 8,M = 16。由于是采样统计,所以不管 N 是 20 还是 8,这个基数都很容易不准确。所以,冤有头债有主,MySQL 选错索引,还得归咎到没能准确地判断出扫描行数。可以用 analyze table 来重新统计索引信息,进行修正。ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...3.3 索引选择异常和处理1. 采用 force index 强行选择一个索引。2. 可以考虑修改语句,引导 MySQL 使用我们期望的索引。3. 有些场景下,可以新建一个更合适的索引,来提供给优化器做选择,或删掉误用的索引内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • myisam和innodb引擎优化分析
  • mysql数据库操作的基本命令
  • mysql 5.7.27 安装配置方法图文教程
  • mybatis mysql delete in操作只能删除第一条数据的方法
  • mysql中you can’t specify target table for update in from cl
  • python mysql进行数据库表变更和查询
  • mysql临时表用法分析【查询结果可存在临时表中】
  • mysql利用profile分析慢sql详解(group left join效率高于子查询
  • mysql多表查询详解上
  • centos7使用yum安装mysql5.7.19的详细步骤
  • mysql “索引”能重复吗?“唯一索引”与“索引”区别是...
  • mysql 单列可以增加唯一索引和普通索引么
  • mysql唯一索引和索引的区别
  • mysql主键和唯一索引的区别
  • mysql唯一键和唯一索引的区别,还是二者就是一个东...
  • mysql 主键索引和唯一索引的区别
  • mysql 主键索引和唯一索引的区别
  • mysql数据表 唯一索引需要修改成普通索引 怎么改?
  • 请问mysql索引,有主键索引、唯一索引、全文索引、...
  • MySQL中如何设置唯一索引,联合索引?
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysqlmysql普通索引和唯一索引的深入讲解mysql下普通索引和唯一索引的效率对比mysql选错索引的原因以及解决方案mysql索引提高查询速度常用方法代码示例mysql索引失效的几种情况汇总myisam和innodb引擎优化分析mysql数据库操作的基本命令mysql 5.7.27 安装配置方法图文教程mybatis mysql delete in操作只能删除第一条数据的方法mysql中you can’t specify target table for update in from clpython mysql进行数据库表变更和查询mysql临时表用法分析【查询结果可存在临时表中】mysql利用profile分析慢sql详解(group left join效率高于子查询mysql多表查询详解上centos7使用yum安装mysql5.7.19的详细步骤mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql日期数据类型、时间类型使用mysql——修改root密码的4种方法mysql之timestamp(时间戳)用法mysql update语句的用法详解命令行模式下备份、还原 mysql 数据库的语关于mysql索引的几点值得注意的事项用mysqldump备份和恢复指定表的方法mysql 8.0.17 解压版安装配置方法图文教程mysql server 8.0.3安装配置方法图文教程mysql 实现树的遍历详解及简单实现示例mysql的一条慢sql查询导致整个网站宕机的mysql数据库维护中监控所用到的常用命令mysql迁移至8.0时的注意事项(小结)浅谈mysql8.0 异步复制的三种方式
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved