精华内容
下载资源
问答
  • -从性能的角度考虑,你会为这个身份证号,选择唯一索引还是普通索引呢?选择的依据是什么呢?- 这就要从他们的执行过程看起。二:唯一索引 和 普通索引 的查询过程?- 例如- 执行查询的语句是 select id from T ...

    一:概述

    - 如果业务代码已经保证了不会写入重复的身份证号。

    - 从性能的角度考虑,你会为这个身份证号,选择唯一索引还是普通索引呢?选择的依据是什么呢?

    - 这就要从他们的执行过程看起。

    二:唯一索引 和 普通索引 的查询过程?

    - 例如

    -  执行查询的语句是 select id from T where k=5

    - 查询语句在索引树上查找的过程

    - 先是通过 B+ 树从树根开始,按层搜索到叶子节点,定位数据页。

    - 数据页内部通过二分法来定位记录。

    - 对于普通索引来说

    - 查找到满足条件的第一个记录 k=5 后,需要查找下一个记录。

    - 直到碰到第一个不满足 k=5 条件的记录。

    - 对于唯一索引来说

    - 由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。

    三:唯一索引 和 普通索引 的查询性能比较?

    - 性能之差 微乎其微

    - 原因

    - InnoDB 的数据是按数据页为单位来读写的。

    - 当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页(InnoDB 中,每个数据页的大小默认是 16KB)为单位,将其整体读入内存。

    - 那么,对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。

    - 当然,如果 k=5 这个记录刚好是这个数据页的最后一个记录,那么要取下一个记录,必须读取下一个数据页,这个操作会稍微复杂一些。

    - 但是,对于整型字段,一个数据页可以放近千个 key,因此出现这种情况的概率会很低。

    - 所以,我们计算平均性能差异时,仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

    四:既然在查询性能一致,那么他们的更新性能呢,在讨论更新之前,我们来看下 InnoDB 对于更新的优化 Chage Buffer.

    五:Change Buffer

    - 原理(当需要更新一个数据时)

    - 如果数据页在内存中就直接更新。

    - 如果数据页不在内存中。

    - 在不影响数据一致性的前提下,InooDB 会将这些更新操作缓存在 change buffer 中,这样就不需要从磁盘中读入这个数据页了。

    - 在下次查询需要访问这个数据页的时候,将数据页读入内存,然后执行 change buffer 中与这个页有关的操作。

    - 通过这种方式就能保证这个数据逻辑的正确性。

    - Change buffer 什么时候会真正的更新 ?

    - 虽然名字叫作 change buffer,实际上它是可以持久化的数据。

    - 也就是说,change buffer 在内存中有拷贝,也会被写入到磁盘上。

    - 将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。

    - 除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。

    - 在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作。

    - 为什么需要 Change Buffer ?

    - 显然,如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得到明显的提升。

    - 而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率。

    - Change buffer 的使用场景?

    - 因为 merge 的时候是真正进行数据更新的时刻,而 change buffer 的主要目的就是将记录的变更动作缓存下来

    - 所以在一个数据页做 merge 之前,change buffer 记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。

    - 因此,对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。

    - 这种业务模型常见的就是账单类、日志类的系统。

    - 反过来,假设一个业务的更新模式是写入之后马上会做查询。

    - 那么即使满足了条件,将更新先记录在 change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。

    - 这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。

    - 所以,对于这种业务模式来说,change buffer 反而起到了副作用。

    六:在看 唯一索引 和 普通索引的性能问题?

    - 唯一索引

    - 所有的更新操作都要先判断这个操作是否违反唯一性约束。

    - 比如,要插入 (4,400) 这个记录,就要先判断现在表中是否已经存在 k=4 的记录,而这必须要将数据页读入内存才能判断。

    - 如果都已经读入到内存了,那直接更新内存会更快,就没必要使用 change buffer 了。

    - 因此,唯一索引的更新就不能使用 change buffer,实际上也只有普通索引可以使用。

    - 普通索引

    - 正常使用 change buffer 更新。

    - 结论

    - 其实,这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响。

    - 如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭 change buffer。而在其他情况下,change buffer 都能提升更新性能。

    - 在实际使用中,你会发现,普通索引和 change buffer 的配合使用,对于数据量大的表的更新优化还是很明显的。

    七:Redo log 和 Change Buffer

    - 能力

    - Redo log 用于写入内存,完成更改,提供 cash-safe 的能力。

    - Change Buffer 用于记录更新内容,批量更新。

    - 共同工作流程

    - 更新数据

    - 在内存中,直接更新内存;

    - 没有在内存中,就在内存的 change buffer 区域,记录下“我要更改 xxx”这个信息

    - 将上述两个动作记入 redo log 中.

    - 事务完成。

    - 执行这条更新语句的成本很低,就是写了两处内存,然后写了一处磁盘(两次操作合在一起写了一次磁盘),而且还是顺序写的。

    -优势

    - Redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),而 Change Buffer 主要节省的则是随机读磁盘的 IO 消耗。

    【MySQL 5.7 Reference Manual】15.4.2 Change Buffer(变更缓冲)

    15.4.2 Change Buffer(变更缓冲)   The change buffer is a special data structure that caches changes to se ...

    MySQL 5.7 Reference Manual】15.4.2 Change Buffer(变更缓冲)

    15.4.2 Change Buffer(变更缓冲)   The change buffer is a special data structure that caches changes to se ...

    普通索引和唯一索引如何选择(谈谈change buffer)

    假设有一张市民表(本篇只需要用其中的name和id_card字段,有兴趣的可以翻看“索引”篇,里面有建表语句) 每个人都有一个唯一的身份证号,且业务代码已经保证不会重复. 由于业务需求,市民需要按身份 ...

    Mysql索引介绍及常见索引(主键索引、唯一索引、普通索引、全文索引、组合索引)的区别

    Mysql索引概念:说说Mysql索引,看到一个很少比如:索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不是越多越好,假如这本书1000页,有500也是目录,它当然效率低,目录是要 ...

    Mysql主键索引、唯一索引、普通索引、全文索引、组合索引的区别

    原文:Mysql主键索引.唯一索引.普通索引.全文索引.组合索引的区别 Mysql索引概念: 说说Mysql索引,看到一个很少比如:索引就好比一本书的目录,它会让你更快的找到内容,显然目录(索引)并不 ...

    【mysql】主键、普通索引、唯一索引和全文索引的比较

    YSQL索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存.如果没有索引,执行查询时MySQL必须从第一个记录 开始扫描整个表的所有记录,直至找到符合要求的记录.表里面的记 ...

    mysql索引之一:索引基础(B-Tree索引、哈希索引、聚簇索引、全文(Full-text)索引区别)(唯一索引、最左前缀索引、前缀索引、多列索引)

    没有索引时mysql是如何查询到数据的 索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点.考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储10 ...

    MySQL 创建唯一索引忽略对已经重复数据的检查

    MySQL 创建唯一索引忽略对已经重复数据的检查 在创建唯一索引的基础上加上关键字"IGNORE "即可.(注意,经测试,在5.7版本已经不再支持该参数) # 重复数据 mysql ...

    mysql使用唯一索引避免插入重复数据

    使用MySQL 索引防止一个表中的一列或者多列产生重复值 一:介绍MYSQL唯一索引 如果要强烈使一列或多列具有唯一性,通常使用PRIMARY KEY约束. 但是,每个表只能有一个主键. 因此,如果使 ...

    随机推荐

    JavaWeb学习记录(二十七)——定时发送邮件ServletContextListener监听实现

    public class EmailSendListener implements ServletContextListener{ @Override    public void contextDe ...

    你有没有试过“闭上眼”使用:京东、滴滴、QQ、支付宝?

    正在看这篇文章的同学,也许是幸运的. 互联网的发展,让我们的生活越来越便利,但这个“我们”,也许并不包括那些残障人士.正常人眼里来说再简单不过的页面操作,对于盲人来说都是不可攀越的高墙.换句话说,越行 ...

    sqlserver数据库导入Mysql数据库问题

    近来遇到一个问题,之前的项目用的是SQLServer数据库,但是现在要换成MySQL数据库,所有整理了一些数据导入的步骤,供需要的人参考! 第一步: 第二步: 第三步: 第四步: 第五步: 第六步: ...

    【Teradata TTU】Windows TTU安装工具列表

    Version Display Name-------------------------------------------------------------------------------- ...

    mesbox公告加更新控制

    0为不显示,1为显示~~~~0|友情提示:任何时候,不要相信福利软件,福利网站,不乱接收别人发的的任何文件,如需使用军旗有关产品,请至官方群或官方网站下载!!|183|173~~~~162,1651, ...

    Linux基础 -Ubuntu

    Ubuntu 下: sudo 以管理员权限执行 apt 是Advanced Packaging Tool ,Ubuntu下的安装包管理工具,早期使用apt-get,从Ubuntu16开始建议使用apt ...

    unity API 之EventSystem.current.IsPointerOverGameObject()

    命名空间 :UnityEngine.EventSystems 官方描述: public bool IsPointerOverGameObject(); public bool IsPointerOve ...

    基于python+Testlink+Jenkins实现的接口自动化测试框架V3.0

    基于python+Testlink+Jenkins实现的接口自动化测试框架V3.0 目录 1. 开发环境2. 主要功能逻辑介绍3. 框架功能简介 4. 数据库的创建 5. 框架模块详细介绍6. Tes ...

    查看加密的vba代码

    查看加密的vba代码,可以使用这个工具,excel文件里面的宏代码一览无余. https://files.cnblogs.com/files/laoxia/PVP.zip

    IIS - 自动申请、部署Let's Encrypt的免费SSL证书(让网站实现HTTPS协议)

    IIS - 自动申请.部署Let's Encrypt的免费SSL证书(让网站实现HTTPS协议) 2017-12-19发布:hangge阅读:161   一.HTTPS 协议介绍 1,什么是 HTTP ...

    展开全文
  • 1、MySQL 的 主键。  “主键”的完整称呼是“主键约束”。MySQL 主键约束是一个列或者列的组合(其中由多列组合的主键称为复合主键),其值能唯一地标识表中的每... MySQL唯一约束(Unique Key)是指所有记录中字

    目录

    1、MySQL的主键。

    2、MySQL的唯一约束。

    3、MySQL的索引。

    4、主键、唯一约束和唯一索引的区别。

    1、MySQL 的 主键。

      "主键" 的完整称呼是 "主键约束" 。MySQL 主键约束是一个列或者列的组合(其中由多列组合的主键称为复合主键),其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键,通过它可以强制表的实体完整性。。

    (1)一个表可以没有主键,而且最多只能有一个主键。

    (2)主键值必须唯一标识表中的每一行,且不能为 NULL,即同一个表中不可能存在两行数据有相同的主键值。

    2、MySQL 的 唯一约束。

      MySQL唯一约束(Unique Key)是指所有记录中字段的值不能重复出现。例如,为 id 字段加上唯一性约束后,每条记录的 id 值都是唯一的,不能出现重复的情况。如果其中一条记录的 id 值为 "0001" ,那么该表中就不能出现另一条记录的 id 值也为 "0001" 。

      唯一约束可以确保一列或者几列不出现重复值。

      唯一约束与主键约束相似的是它们都可以确保列的唯一性。不同的是,唯一约束在一个表中可有多个,并且设置唯一约束的列允许有空值,但是最多只能有一个空值。而主键约束在一个表中只能有一个,且不允许有空值。比如,在用户信息表中,为了避免表中用户名重名,可以把用户名设置为唯一约束。

      唯一约束可以在创建表时直接设置,通常设置在除了主键以外的其它列上。

      在定义列的尾部直接使用 UNIQUE 关键字指定唯一约束,语法格式如:<字段名> <数据类型> UNIQUE 。( 或者 UNIQUE KEY `unique_name` (`first_name`, `last_name`)

      在修改表时添加唯一约束的语法格式为:ALTER TABLE <数据表名> ADD CONSTRAINT <唯一约束名> UNIQUE(<列名>); 。

      在 MySQL 中删除唯一约束的语法格式如下:ALTER TABLE <表名> DROP INDEX <唯一约束名>; 。

      如果业务中要求两个字段联合起了是唯一的,比如 "地址" + "名称" 是唯一的,这就需要对两列甚至多列添加联合唯一约束。

    3、MySQL 的 索引。

      为什么要使用索引?

      索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础,常用于实现数据的快速检索。

      索引是什么?

      首先我们可以举个例子,字典大家应该都使用过,我们可以使用目录快速定位到所要查找的内容,那么索引跟目录的作用类似,在数据库表记录中,利用索引,可以快速过滤查找到数据记录。

      索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表,实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

      例如,若对表中name字段建立索引,则按照表中name字段进行索引排序,并为其建立指向数据表中记录所在位置的 "指针"

      查询方式有两种,其中一种是全表扫描;另一种是利用数据表上建立的索引进行扫描。

      在 MySQL 中,通常有以下两种方式访问数据库表的行数据:

    (1) 顺序访问

    顺序访问是在表中实行全表扫描,从头到尾逐行遍历,直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单,但是当表中有大量数据的时候,效率非常低下。例如,在几千万条数据中查找少量的数据时,使用顺序访问方式将会遍历所有的数据,花费大量的时间,显然会影响数据库的处理性能。

    (2) 索引访问

    索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引,在列上创建了索引之后,查找数据时可以直接根据该列上的索引找到对应记录行的位置,从而快捷地查找到数据。索引存储了指定列数据值的指针,根据指定的排序顺序对这些指针排序。

      例如,在学生基本信息表 students 中,如果基于 student_id 建立了索引,系统就建立了一张索引列到实际记录的映射表,当用户需要查找 student_id 为 12022 的数据的时候,系统先在 student_id 索引上找到该记录,然后通过映射表直接找到数据行,并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度,所以采用索引的方式可以大大提高数据库的工作效率。

      索引根据用途分为:

    (1)普通索引:是最基本的索引类型,它的目的是加快对数据的访问速度,没有任何限制。索引列值可以取空值或重复值。创建使用关键字 "INDEX" "KEY"

    (2)唯一索引:是不允许具有相同索引值的索引。创建唯一索引的目的往往不是为了提高访问速度,而是为了避免数据出现重复。其中索引列值不能重复,即索引列值必须是唯一的。创建使用关键字 "UNIQUE"

    (3)主键索引:是唯一索引的特定类型。索引值不能为空值。主键是用来唯一标识表中一条记录的。主键不允许为空值,主键是唯一的。一个表只能有一个主键,不可能有多个主键。创建使用关键字 "PRIMARY KEY"

    注意:唯一索引和主键索引不是互斥关系;唯一索引包含了主键索引;主键索引是一种特殊的唯一索引。

      索引根据列数分为:

    (1)单列索引:即一个索引只包含单个列,一个表可以有多个单列索引,但这些单列索引不是组合索引。比如,创建一个学号ID的索引;以name再创建一个姓名的单列索引。

    (2)组合索引(复合索引或多列索引):即一个索引包含多个列。比如,以用户ID、用户名Name、用户年龄Age来创建的索引就是组合索引。此时,排序规则是左前缀原则,即先按照用户ID进行排序;当第一列值相同的情况下,则按照用户名Name第二列进行排序;依次类推。

      索引根据存储方式分为:

    (1)B-树索引;(2)哈希索引。

      虽然索引可以加快查询速度,提高 MySQL 的处理性能,但是过多地使用索引也会造成以下弊端:

    (1)创建索引和维护索引要耗费时间,这种时间会随着数据量的增加而增加。

    (2)除了数据表占数据空间之外,每一个索引还要占一定的物理空间。如果要建立聚簇索引,那么需要的空间就会更大。

    (3)当对表中的数据进行增加、删除和修改的时候,索引也要动态地维护,这样就降低了数据的维护速度。

      注意:索引可以在一些情况下加速查询,但是在某些情况下,会降低效率。

      索引只是提高效率的一个因素,因此在建立索引的时候应该遵循以下原则:

    (1)在经常需要搜索的列上建立索引,可以加快搜索的速度。

    (2)在作为主键的列上创建索引,强制该列的唯一性,并组织表中数据的排列结构。

    (3)在经常使用表连接的列上创建索引,这些列主要是一些外键,可以加快表连接的速度。

    (4)在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,所以其指定的范围是连续的。

    (5)在经常需要排序的列上创建索引,因为索引已经排序,所以查询时可以利用索引的排序,加快排序查询。

    (6)在经常使用 WHERE 子句的列上创建索引,加快条件的判断速度。

      与此对应,在某些应用场合下建立索引不能提高 MySQL 的工作效率,甚至在一定程度上还带来负面效应,降低了数据库的工作效率,一般来说不适合创建索引的环境如下:

    (1)对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到,所以有索引或者无索引并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度,并增大了空间要求。

    (2)对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少,例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例,增加索引并不能明显加快检索速度。

    (3)对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大,要么取值很少。

    (4)当修改性能远远大于检索性能时,不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时,会提高检索性能,降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因此,当修改性能远远大于检索性能时,不应该创建索引。

    4、主键、唯一约束和唯一索引的区别。

      主键和唯一索引的区别是:

    (1) 主键一定是唯一索引,唯一索引并不一定就是主键。

    (2) 一个表中可以有多个唯一索引,但最多只能有一个主键。

    (3) 主键的列值不允许为空值,而唯一索引的列值最多有一个空值。

      通俗举例来说:主键相当于一本书的页码,索引相当于该书的目录。

      当创建或设置主键的时候,MySql会自动添加一个与主键对应的唯一索引,因此不需要再额外地添加把主键作为唯一索引的语句。数据库管理系统对于主键会自动生成一个唯一索引,所以主键是一个特殊的索引。可以通过SQL语句( "SHOW INDEX FROM tablename;" 或 "SHOW KEYS FROM tablename;" )查看已建表的索引。


     

      # SQL创建语句示例。
    
    CREATE TABLE `ik_admin`
    
    (
    
        `user_id`   bigint       NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    
        `user_name` varchar(50)  NOT NULL,
    
        `password`  varchar(128) NOT NULL,
    
        `role_id`   bigint       NOT NULL,
    
        `last_ip`   varchar(20)  NOT NULL DEFAULT '',
    
        `last_time` datetime     NULL,
    
        `email`     varchar(32)  NOT NULL DEFAULT '',
    
        `status`    tinyint(1)   NOT NULL DEFAULT '1' COMMENT '状态(0-禁用 1-启用)',
    
        PRIMARY KEY (`user_id`),              # 主键:默认自动生成索引, 列值不能重复 而且不允许为空值。
    
        UNIQUE INDEX `userName` (`user_name`) # 唯一索引:列不能重复 但可以有空值(最多只有一个空值)。
    
    ) ENGINE = MyISAM AUTO_INCREMENT = 1001 DEFAULT CHARSET = utf8 COMMENT ='管理员表'; 

      唯一约束和唯一索引的区别:

    (1)唯一约束和唯一索引,都可以实现列数据的唯一,列值可以有空值。

    (2)创建唯一约束的时候,会自动创建一个同名的唯一索引,该索引不能单独删除,删除约束会自动删除索引。唯一约束是通过唯一索引来实现数据的唯一。

    (3)创建一个唯一索引,这个索引就是独立,可以单独删除。

    (4)如果一个列上想有约束和索引,且两者可以单独的删除。可以先建唯一索引,再建同名的唯一约束。

    (5)如果表的一个字段,要作为另外一个表的外键,那么这个字段必须有唯一约束(或是主键),如果只是有唯一索引,就会报错。

      索引的键不一定要和唯一约束的完全匹配,唯一约束可以只用索引的前导列。请看下面的例子:

    create table t (n1 number, n2 number);

    create index t_idx on t(n1, n2);

    上面只是创建了两个列的复合索引,并不要求是唯一索引。

    alter table t add constraint t_uk unique (n1) using index t_idx;

    可以使用这个索引来创建唯一约束,而且它只在第一个列上唯一,也就是说唯一约束比索引更加严格。

    那么这个索引的第二个列有什么意义?有些时候,带一些冗余列可以使得你直接从索引中取到所有SELECT的数据而无需回表。

    展开全文
  • 普通索引 这是最基本的索引类型,而且它没有唯一性之类的限制。 唯一性索引 这种索引和前面的“普通索引”基本相同,...对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索...

    普通索引

    这是最基本的索引类型,而且它没有唯一性之类的限制。

    唯一性索引

    这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。

    这两种索引的运行原理

    查询过程

    对于普通索引来说,查找到满足条件的第一个记录后,需要查找下一个记录,直到碰到第一个不满足条件的记录。

    对于唯一索引来说,由于索引定义了唯一性,查找到第一个满足条件的记录后,就会停止继续检索。

    所以在这里你感觉用唯一性索引会快一些,毕竟少了一个步骤。但是这个不同带来的性能差距微乎其微。

    你知道的,InnoDB 的数据是按数据页为单位来读写的。也就是说,当需要读一条记录的时候,并不是将这个记录本身从磁盘读出来,而是以页为单位,将其整体读入内存。在 InnoDB 中,每个数据页的大小默认是 16KB。

    因为引擎是按页读写的,所以说,当找到符合条件的记录的时候,它所在的数据页就都在内存里了。那么,对于普通索引来说,要多做的那一次“查找和判断下一条记录”的操作,就只需要一次指针寻找和一次计算。 

    当然也会有特殊情况,就是符合条件的记录正好处于数据页的最后一个,那往下查找的操作就会拿下一个数据页放进内存,这个时候就会慢了,但是一个整型字段,一个数据页可以放进千的key,所以这个概率很低。

    更新过程

    Change buffer。

    两种索引更新过程主要差别就是因为Change buffer。

    Change buffer的主要目的是将对二级索引的数据操作缓存下来,以此减少二级索引的随机IO,并达到操作合并的效果。

    当InooDB更新一个数据页的时候有2中情况:

    1. 数据页在内存中,此时直接更新。
    2. 数据页不在内存中,这时候 InooDB 会将这些更新操作缓存在 change buffer 中,然后在下次需要访问这个数据页的时候,将数据页放入内存,然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。这种情况在更新操作时省去了把数据页从磁盘读入内存这一步,而是在以后访问这个数据页的时候再做更新操作。

    显然,如果能够将更新操作先记录在 change buffer,减少读磁盘,语句的执行速度会得到明显的提升。而且,数据读入内存是需要占用 buffer pool 的,所以这种方式还能够避免占用内存,提高内存利用率。 

    change buffer 用的是 buffer pool 里的内存,因此不能无限增大。change buffer 的大小,可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候,表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

    buffer pool: Innodb维护了一个缓存区域叫做Buffer Pool,用来缓存数据和索引在内存中。Buffer Pool可以用来加速数据的读写,如果Buffer Pool越大,那么Mysql就越像一个内存数据库,所以了解Buffer Pool的配置可以提高Buffer Pool的性能。

    需要说明的是,虽然名字叫作 change buffer,实际上它是可以持久化的数据。也就是说,change buffer 在内存中有拷贝,也会被写入到磁盘上。
    将 change buffer 中的操作应用到原数据页,得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外,系统有后台线程会定期 merge。在数据库正常关闭(shutdown)的过程中,也会执行 merge 操作。

    这个 change buffer普通索引会用到,唯一索引用不到,因为唯一索引的更新操作之前都要判断唯一性,所以在判断这步已经把数据页放在了内存里,所以之后的更新操作就直接在内存里操作了,内存更新更快,没必要用change buffer了。

    所以,普通索引会用到 change buffer。

    索引具体的处理流程

    清楚了change buffer然后模拟一个场景来看一下两种索引具体的处理流程是怎样的。

    如果要在这张表中插入一个 id=5的新纪录,InnoDB 的处理流程是怎样的。

    第一种情况:目标数据页在内存中。

    • 唯一索引:找到 4 和 6 之间的位置,判断到没有冲突,插入这个值,语句执行结束;
    • 普通索引:找到 4 和 6 之间的位置,插入这个值,语句执行结束。

    这样看来,普通索引和唯一索引对更新语句性能影响的差别,只是一个判断,只会耗费微小的 CPU 时间。

    第二种情况是:目标数据页不在内存中

    • 唯一索引:需要将数据页读入内存,判断到没有冲突,插入这个值,语句执行结束;
    • 普通索引:则是将更新记录在 change buffer,语句执行就结束了。

    主要区别就是唯一索引需要把磁盘中的数据页放入内存。就是这步影响了性能。

    将数据从磁盘读入内存涉及随机 IO 的访问,是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问,所以对更新性能的提升是会很明显的。 

    但是普通索引用change buffer起到加速作用也是有应用场景的。

    因为 merge 的时候是真正进行数据更新的时刻,而 change buffer 的主要目的就是将记录的变更动作缓存下来,所以在一个数据页做 merge 之前,change buffer 记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。

    由此看来就是对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。

    所以反过来,假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在 change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。所以,对于这种业务模式来说,change buffer 反而起到了副作用。

    索引使用选择

    从上面的内容来说,这两类索引在查询能力上是没差别的,主要考虑的是对更新性能的影响。所以,我建议你尽量选择普通索引。
    如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭 change buffer。而在其他情况下,change buffer 都能提升更新性能。

    在实际使用中,你会发现,普通索引和 change buffer 的配合使用,对于数据量大的表的更新优化还是很明显的。特别地,在使用机械硬盘时,change buffer 这个机制的收效是非常显著的。所以,当你有一个类似“历史数据”的库,并且出于成本考虑用的是机械硬盘时,那你应该特别关注这些表里的索引,尽量使用普通索引,然后把 change buffer 尽量开大,以确保这个“历史数据”表的数据写入速度。这时候,归档数据已经是确保没有唯一键冲突了。要提高归档效率,可以考虑把表里面的唯一索引改成普通索引。

    唯一索引使用的问题,主要是纠结在“业务可能无法确保”的情况。
    首先,业务正确性优先。如果业务不能保证数据的唯一性,或者业务就是要求数据库来做约束,那么没得选,必须创建唯一索引。本篇文章的意义在于,如果碰上了大量插入数据慢内存命中率低的时候,可以给你多提供一个排查思路。
     

    展开全文
  • mysql中的约束索引

    千次阅读 2019-05-14 19:54:48
    文章目录一、约束(Constraint)1、主键约束(primary key)2、唯一约束(unique)3、默认值约束(default)4、外键约束(foreign key)二、索引(index)索引类型三、区别与联系 本文内容仅在 MariaDB-10.2.15 ...

    本文内容仅在 MariaDB-10.2.15 版本下验证,其它环境下可能略有差异。

    简单来说,约束是为了实现业务规则、保证数据的完整性,索引是为了查询高效,二者原本是两个不同的东西,只是在 mysql 中实现方法有类似之处,所以经常会让人感到迷惑。

    一、约束(Constraint)

    约束,是为了实现非空、非重等常见业务规则,在定义数据时对表或某些字段增加的特定的约束规则。

    常见的约束类型有主键约束、唯一约束、非空约束、默认值约束、外键约束等。

    1、主键约束(primary key)

    • primary key,用于定义表的主键,是唯一确定表中每一条记录的标识符
    • 主键不能为空,也不能重复
    • 一张表中只能有一个主键

    设置主键:

    • 创建表的同时设置主键
    create table student1 (
    	sid int(10) primary key,  -- 对 sid 字段设置主键约束
    	sname varchar(20) not null  -- 对 sname 字段设置非空约束
    )
    
    • 创建表以后,追加主键
    create table student2(
    	sid int(10) not null,
    	sname varchar(10) not null
    )
    
    # 追加主键,并设置主键名称
    ALTER TABLE student2 ADD CONSTRAINT pks2 PRIMARY KEY(sid);
    
    • 设置复合主键
      • 即把多个列同时设置为一个主键
    create table student3 (
    	sid int(10) not null,
    	nid int(10) not null,
    	sname varchar(10) not null,
        primary key (sid, nid)  -- 定义复合主键
    )
    
    • 删除主键:
    ALTER TABLE student3 DROP PRIMARY KEY;
    

    2、唯一性约束(unique)

    • unique,设置某列数据不能重复,但可以有空值
    • 一张表中可以对多个列设置 unique 约束,也可以把多个字段定义成一个 unique 约束
    • 主键所在的列,不能使用唯一约束

    区分唯一约束和主键约束:

    • 一张表只能有一个主键,但可以出现多个唯一约束
    • 主键不能为null,唯一可以为null

    设置唯一约束:

    • 在创建表的同时,设置唯一约束:
    create table student4(
    	sid int(10) primary key ,
    	sname varchar(10) unique
    )
    
    • 在创建表以后,追加约束:
    create table student5(
    	sid int(10) primary key,
    	sname varchar(10)
    )
    ALTER TABLE student5 ADD CONSTRAINT uns5
    UNIQUE(sname);
    
    • 把多个字段都设置成同一个唯一约束:
    create table student6 (
    	sid int(10) primary key,
    	nid int(10) not null,
    	sname varchar(10) not null,
        UNIQUE KEY uk6(sid, nid)  -- 定义复合 unique 约束
    )
    

    删除约束:

    ALTER TABLE student6 DROP INDEX uk6;
    

    3、默认值约束(default)

    在插入操作时,当某一列没有值时系统就自动把之前设置的默认值赋值过去。

    设置默认值约束:

    create table student7(
    	sid int(10) primary key,
    	sname varchar(10) not null,
    	age int(10) default 12  -- 设置默认值为12
    )
    

    4、外键约束(foreign key)

    定义:

    • 如果在一张表中有一个非主键的字段,指向了另一张表中的主键。
      • 每张表中可以有多个外键
    • 通常将外键所在的表称为子表或被约束表,指向的另一个表称为父表或约束表或外表
      • 子表中外键字段的取值范围由父表决定
    • 子表在进行写操作的时候,如果外键字段在父表中找不到对应的匹配,操作就会失败
    • 对父表的主键字段进行删和改时,如果对应的主键在子表中被引用,操作就会失败

    外键的三种约束模式:

    • district 严格模式, 父表不能删除或更新一个被子表引用的记录。
    • cascade 级联模式,父表操作后,子表关联的数据也跟着一起操作。
    • set null 置空模式,父表操作后,子表对应的字段被置空(前提是外键字段允许为NULL)。

    使用外键的前提:

    • 表储存引擎必须是 innodb,否则创建的外键无约束效果。
    • 外键的列类型必须与父表的主键类型完全一致。
    • 外键的名字不能重复。
    • 已经存在数据的字段被设为外键时,必须保证字段中的数据与父表的主键数据对应起来。
    • 外键必须建立索引(可以为普通、主键、唯一,事先不建立的话会自动创建一个普通索引)

    使用外键的优缺点:

    • 通过数据库自身机制(而非程序员)来保证数据一致性和完整性
    • 可靠性较高,但在一定程度上降低了数据库的速度
    • 当系统数据越来越多时,尤其是现在的互联网高并发业务场景较多,若外键较多,会导致系统响应很慢,要尽量少用外键,改成用中间层控制业务规则!
    "外键使用示例:"
    --dept表
    create table dept(
    	did int primary key,
    	dname varchar(10) not null unique, 
    )
    --emp表
    create table emp(
    	eid int primary key,
    	ename varchar(10) not null ,
    	salary float not null,
    	dept_id int,
        foreign key(dept_id) references dept(did)  --定义外键 dept_id 与 dept 表的 did 主键字段关联
    )
    

    其实在实际工作中,用的比较多的是主键、非空、默认值约束,其他的很少用,复杂的业务规则尽量通过程序来控制,从而提高数据库性能。

    二、索引(index)

    定义:

    • 索引是一种高效获取数据的数据结构,可以快速提高查询速度
      • 在 innoDB 引擎中使用的是 B-Tree 索引算法。
    • 当查询海量数据时,索引的效果尤其明显。
    • 索引是定义在列上的,有单列索引、组合索引。

    索引类型

    • 普通索引:
      • create index 索引名字 on 表名(列名)
    • 唯一索引:
      • 索引字段必须是唯一的,该值不能重复
      • create unique index 索引名 on 表(列名)
    • 复合索引:
      • 也叫组合索引,在多个列创建一个索引
      • create index myIndex on teacher2(sname, sex)
    • 删除索引:
      • drop index 表.索引名字
    • 主键索引:
      • 很多时候也把 PRIMARY KEY 称为主键索引,但是主键和索引还是两个不同的概念。
      • 可以理解为,创建主键的同时,mysql 会自动对它创建索引。

    三、区别与联系

    这里主要说明主键约束、唯一性约束和索引之间的区别与联系。

    • 概念上不同:
      • 约束是为了保证数据的完整性,索引是为了提高查询速度。
    • 创建主键约束时,mysql 默认会自动创建一个索引
      • 若要实现主键的值不重复,在每次插入新记录时都需要检索数据,所以为了提高检索速度,同时对主键创建索引。
    • 创建唯一约束时,mysql 默认会自动创建一个唯一索引
      • 跟上面的道理一样,通过唯一索引实现唯一约束
    展开全文
  • 普通索引与唯一索引

    万次阅读 多人点赞 2019-04-08 20:20:06
    所谓唯一索引,就是在创建索引时,限制索引的值必须是唯一的。通过该类型的索引可以更快速地查询某条记录。 普通索引还是唯一索引? 假设你在维护一个市民系统,每个人都有一个唯一的身份证号,而且业务代码已经...
  • 对查询:普通索引和唯一索引对查询性能影响很小 对更新:唯一索引比普通索引更耗时. 查询流程:唯一索引找到第一个匹配数据后不继续往后查找. 1.普通索引,从索引树根节点开始按层往下查找,找到对应的数据页,然后...
  • 一 主键和唯一索引都要求值唯一,但是它们还是有区别的: ①.主键是一种约束唯一索引是一种索引;...二 主键约束唯一索引约束严格,当没有设定主键时,非空唯一索引自动称为主键。对于主键和唯一...
  • 浅谈主键索引与唯一索引

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

    千次阅读 2020-01-03 16:55:51
    唯一索引和普通索引 change buffer 学习检测 什么是change buffer? 唯一索引和普通索引查找数据流程及性能对比? 唯一索引和普通索引更新数据流程及性能对比? change buffer 适用场景? change buffer 和 ...
  • 唯一索引与主键索引的比较

    千次阅读 2019-02-20 23:02:51
    唯一索引 唯一索引不允许两行具有相同的索引值。 如果现有数据中存在重复的键值,则大多数数据库都不允许将新创建的唯一索引与表一起保存。当新数据将使表中的键值重复时,数据库拒绝接受此数据。例如,如果在 ...
  • 主键一定是唯一索引唯一索引并不一定就是主键。 所谓主键就是能够唯一标识表中某一行的属性或属性组,一个表只能有一个主键,但可以有多个候选索引。 因为主键可以唯一标识某一行记录,所以可以确保执行数据...
  • 2016-09-11 回答二级索引?...以innodb来说,每个innodb表具有一个特殊的索引称为聚集索引。如果您的表上定义有主键,该主键索引是聚集索引。如果你不定义为您的表的主键时,mysql取第一个唯一索引(unique)而且只...
  • 唯一索引3.复合索引(联合索引) mysql的索引主要分为3类: 1.单列索引 2.联合索引(复合索引) 3.唯一索引 ----------------------------------------------------- 在介绍索引的分类之前我们必须知道什么叫做索引: ...
  • MySQL讲义第11讲——完整性约束之唯一约束 定义了 UNIQUE 约束的字段不能包含重复值,可以为一个或多个字段定义 UNIQUE 约束。因此,UNIQUE 即可以在字段级可以在表级定义,在 UNIQUE 约束的字段上可以包含空值。 ...
  • 最基本的索引类型,没有唯一性之类的限制。普通索引可以通过以下几种方式创建: 创建索引,例如CREATE INDEX ON tablename (列的列表); 修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表); ...
  • 主键约束唯一索引约束严格,当没有设定主键时,非空唯一索引自动称为主键。对于主键和唯一索引的一些区别主要如下: 主键不允许空值,唯一索引允许空值。主键列在创建时,已经默认为空值 + 唯一索引了。 主键...
  • 在MySQL学习:深入浅出索引中学习了MySQL普通索引和唯一索引。了解了普通索引与唯一索引的区别。 普通索引:最基本的索引,没有任何限制。 唯一索引:与普通索引类似,不同的就是:索引列的值必须唯一,但允许有...
  • unique唯一键,not null非空等修饰符常常被称作约束(constraint)约束是数据库用来 提高数据质量和保证数据完整性的一套机制约束作用在表列上,是表定义(DDL语句)的一部分约束分类非空约束(not null)唯一约束...
  • 注意: A.表中有数据不能创建约束 ...主键是每行的唯一标识符,仅仅通过它就能准确定位到一行,其中主键列在整个表中不能有重复,必须包含唯一的值(不能为NULL)。 alter table t_group  alter column id i...
  • 键、索引约束及其区别

    千次阅读 2017-06-24 18:15:00
    键、索引约束及其区别 今天下午刚好没事,把一些基础性的概念理顺一下,存档,省的麻烦,嘿嘿 一.索引 1. 什么是索引索引是对数据库表中一列或多列的值进行排序的一种结构。 在关系型数据库中...
  • 约束索引 1、关系型数据库设计规则 关系型数据库设计规则 遵循ER模型和三范式 E entity 代表实体的意思 对应到数据库当中的一张表 R relationship 代表关系的意思 ER模型 实体关系模型 Java OR Object ...
  • 一、五种约束 如果DML操作所涉及数据违反了已定义的约束,则数据库系统将拒绝执行这样的操作。...和数据表类似,约束也属于数据库对象,可以在建表的同时创建其相关约束,也可以在建表后单独添加;可以由用户...
  • mysql 主键,外键,唯一键,索引的区别

    千次阅读 2016-07-23 10:02:53
    转载:http://blog.csdn.net/duck_arrow/article/details/8264686 ... 主键(primary key) 能够唯一标识表中某一行的属性或属性组。一个表只能有一个主键,但可以有多个候选索引。主键常常与外键构成参照完整性约束
  • 索引约束

    2017-03-17 23:46:18
    一、 虽然索引引用可以提高数据的查询速度,但是任何事物都有双刃剑,它有一些缺点: 1、 索引会占据一定的磁盘空间,就像有安笔划的查找的目录的书会比没有这种目录的书页数要多一些一样。 2、 索引减慢...
  • 介绍 11 种数据库支持的 SQL 特性比较,包括 Oracle、PostgreSQL、SQL Server、IBM Db2、MySQL、MariaDB、Firebird、H2、HSQLDB、Derby、SQLite。 这是第二部分,介绍索引约束的比较。
  • MySQL 之 约束 (主键、唯一、非空、自增、外键) 目的:使得数据更准确,更完整。 约束的分类: 1、键约束 (1)主键约束 (2)唯一约束 (3)外键约束 2、非空约束 3、默认值约束 4、自增约束 主键约束 (一)...
  • 超键(super key):在关系中能唯一标识元组的属性集称为关系模式的超键 候选键(candidate key):不含有多余属性的超键称为候选键 主键(primary key):用户选作元组标识的一个候选键程序主键 比如一个小范围的所有人,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 41,884
精华内容 16,753
关键字:

唯一索引也称为唯一约束