精华内容
下载资源
问答
  • 网上已经有很多关于唯一索引和普通索引的区别,这里就不详细阐述了,接下来我们深入讨论如何根据不同业务场景,应该选择普通索引还是唯一索引。比如维护一个社保管理系统,每个社保人员都有一个唯一的身份证号,而且...

    前言

     网上已经有很多关于唯一索引和普通索引的区别,这里就不详细阐述了,接下来我们深入讨论如何根据不同业务场景,应该选择普通索引还是唯一索引。比如维护一个社保管理系统,每个社保人员都有一个唯一的身份证号,而且业务代码已经保证了不会写入两个重复的身份证号。如果该系统需要按照身份证号查询姓名,就会执行这样的SQL语句:

    select name from suser where id_card = ‘xxxxxxxxxxx’;

     所以一般会考虑在id_card 字段上建索引。由于身份证号字段比较大,不适合用来作主键,索引现在有两个选择,要么给id_card字段创建唯一索引,要么创建一个普通索引。如果业务代码已经保证了不会写入重复的身份证号,那么这两个选择逻辑上都是正确的。但是要从性能角度上来考虑,选择的依据应该是什么呢?下面我们就从两种索引对查询过程和更新过程的性能影响来分析。

    查询操作

     我们来看一下InnoDB索引组织机构,假设执行
    select id from t where a=3。
    这个查询语句在索引树上查找的过程将如下:

    1. 先是通过B+树从树根开始,按层序遍历的方式搜索到叶子节点,从而定位数据页。
    2. 通过二分查找来定位记录。

     唯一索引而言,查找到满足条件的第一个条目(比如 (3,300))后就会停止继续检索。
     普通索引查找到一个满足条件的条目后将会继续查找,直到碰到第一个不满足a=3条件的条目。
    它们的不同所带来的性能差距却是微乎其微的。因为InnoDB中是按数据页为单位来读写的,也就是说,当读取一个条目的时候并不是将条目从磁盘读出来,而是以页为单位,整体读入内存。既然存储引擎是按页读写的,所以说当找到a=3的条目时,它所在的数据页已经在内存里了。那么对于普通索引需要多做的“查找以及判断条目是否满足条件”的操作就只需要一次指针操作及计算。

    更新操作

     当需要更新一个数据页时,如果数据页在内存缓冲池(buffer pool)中就直接更新,并同时记录redo log,但是如果这个数据页不在内存中的话。在不影响一致性的前提下,InnoDB会将更新操作缓存在写缓冲(change buffer)中,同时记录redo log。

    写缓冲(change buffer)

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

    在这里插入图片描述
     在MySQL5.5之前的版本中,由于只支持缓存insert操作,所以最初叫做insert buffer,只是后来的版本中支持了更多的操作类型(操作类型包括insert、update、delete)缓存,才改叫change buffer。
     change buffer的数据结构上是一颗b+树,存储在ibdata系统表空间中,根页为ibdata的第4个page(FSP_IBUF_TREE_ROOT_PAGE_NO)。
     将change buffer中的操作应用到原数据页从而得到最新结果的过程被称为merge。merge 的时候才是是真正进行数据更新的时刻,change buffer 将条目的变更动作进行缓存。在一个数据页做 merge 之前,change buffer 记录的变更越多(也就是这个页面上要更新的次数越多),收益就越大。
      一般来说,触发merge的操作主要有以下几种:

    • 访问这个数据页

    • master thread线程每秒或每10秒进行一次merge insert buffer的操作

    • 在数据库正常关闭的时候

        此外,虽然名字叫做change buffer,但实际上它是可以持久化的数据,也就说它在内存中有拷贝,也会被写入到磁盘上。

    change buffer状态查看

    在这里插入图片描述

    • seg size 为插入缓冲区的总大小(页的数量X16KB)
    • merges表示已经合并的merge的数量
    • merged operations: insert 插入记录被merge的次数
    • delete mark 删除操作被merge的次数
    • delete 更新操作被merge了多少次

    change buffer占用buffer pool

    • 数据读入内存是需要占用buffer pool的,采用这种方式能够避免占用内存,提升内存利用率
       change buffer用的是buffer pool的内存,因此不能无线增大,它通过参数innodb_change_buffer_max_size来设置,这个参数表示占用内存的比例,默认是25%,最大值为50%,一般在写多读少的场景下才需要设置。
      在这里插入图片描述

    change buffer带来什么好处

    如果MySQL承担大量的DML操作,则change buffer是必不可少的,他的存在就是尽量减小I/O的消耗,通过内存进行数据的合并操作,将多次操作操作尽量变为少量的I/O操作,从而提升了更新操作的速度。

    什么场景适合开启change buffer

     change buffer只限于普通索引的场景下,不适用与唯一索引。为什么呢?
    因为,假设要插入(3, 300)这个条目,首先要判断这个条目是否在表中出现过。而这必须要将数据页读入内存才能判断。如果都已经读入到内存了,那直接更新内存会更快,就没必要使用 change buffer 了。

     那么InnoDB中插入的条目(3,300)的流程是如何的呢?

    • 如果这个条目要更新的数据页在内存中
      对于唯一索引,找到2和4的位置,判断没有冲突后,插入这个值,执行结束
      对于普通索引,找到2和4的位置,插入这个值,执行结束
    • 如果这个条目要更新的数据页不在内存中
      对于唯一索引,需要将数据页读入内存,然后判断有没有冲突,然后进行插入。
      对于普通索引,只需要将条目更新操作记录在change buffer就执行结束了。

    不是所有场景都可以用change buffer

     普通索引并不是所有场景使用change buffer都能受益,对于写多读少的业务来说,页面在写完以后马上被访问到的概率比较小,此时 change buffer 的使用效果最好。
     但是假设一个业务的更新模式是写入之后马上会做查询,那么即使满足了条件,将更新先记录在change buffer,但之后由于马上要访问这个数据页,会立即触发 merge 过程。这样随机访问 IO 的次数不会减少,反而增加了 change buffer 的维护代价。所以,对于这样类似的业务模式来说,change buffer 反而起到了副作用。

     举个例子:
     假设要执行insert into t values(id1,a1),(id2,a2);

     假设a1 所在的数据页在内存 (InnoDB buffer pool) 中,a2 所在的数据页不在的话,如图所示
    在这里插入图片描述

    1. 如果a1 所在的Page1 在内存中,则直接更新内存;
    2. 如果a2 所在的Page2 没有在内存中,则在change buffer中记录下“要往 Page2 插入一行”这个信息
    3. 将更新Page1这个动作记入到redo log 中
    4. 将change buffer记录插入信息这个动作记入到redo log中

     第3、4写redo log的两次操作合在一起写磁盘。所以从执行过程中可以发现, 执行这条更新语句的成本很低,只写了两处内存,而且还是顺序写的。图中的两个红色箭头,都是后台操作(空闲时或者必须时写入磁盘),不影响更新的响应时间。

    那么在之后的读请求该怎么处理呢,比如我们要执行select * from t where a in (a1, a2);
    change buffer的读过程

    1. a1 本来就在内存中, 之前内存也更新了, 所以直接从内存返回
    2. 读取Page2的时候,需要把Page2从磁盘读入内存,然后结合change buffer里面的操作日志生成一个新版本并返回结果

    总结

     普通索引和唯一索引在查询能力上是没差别的,主要考虑的是更新的影响。一般建议使用普通索引。特别是在使用机械盘的场景下,尽量把change buffer开大从而确保数据的写入速度。

    展开全文
  • 一、前言  在项目开发中往往会遇到两个实体对象之间存在多对多关系的情况,此时我们会维护两个实体对象...2.唯一索引:可以在后期加入索引,对两个外键id做唯一索引,不会修改表结构,但相对来说会增加一些空间。

    一、前言

           在项目开发中往往会遇到两个实体对象之间存在多对多关系的情况,此时我们会维护两个实体对象表,一个关系表,用来存放两者之间的关系。比较典型的案例是学生表、课程表、学生课程关系表。在这种关系表中,我们可以确定的是,学生和课程关系虽然是多对多,但一个确定的学生id和一个确定的课程id在关系表中只能存在一个。如下图所示:,业务逻辑就是一个学生只能有当前课程的一个分数。

    t_student                                                t_course                                          t_student_course_rel   

                                     

             因此可以得知,通过student_id和course_id可以唯一确认一个t_student_course_rel数据,相同的student_id、course_id最多只能存在一条数据。按照正常的业务逻辑来说,不会出现意外的情况,但是一旦出现了,对于以后的查询操作将是一个潜在的隐患,如 select * from t_student_course_rel   where student_id=1 and course_id = 2 ,此时我们希望只查出来一条数据,但如果出现了意外情况,则可能对应出来多条数据,这样便会报错。 

            导致意外的情况:后端高并发的操作没有做出处理、插入数据时没有做验证等等。

            解决方案:1. 对高并发操作加锁, 并且在插入数据时做验证。2. 可在数据库层面做出限制,如联合主键和唯一索引均可

            接下来,以这几个表为例,分析使用两者之间的区别。

    二、联合主键和唯一索引的应用

            上述提到,可以对 student_id和 course_id做联合主键和唯一索引,均可以实现在数据库层面对关系表中异常数据做出限制,我们如何选择呢?

    1.使用联合主键限制

            如果是在最初建关系表t_student_course_rel 的时候,就已经考虑到这个需求的话,可以考虑将student_id和course_id直接当做联合主键去使用。建表语句如下图所示:

    CREATE TABLE `t_student_course_rel ` (
    `id`  bigint(20) NOT NULL ,
    `student_id`  bigint(20) NOT NULL ,
    `course_id`  bigint(20) NOT NULL ,
    `marks`  int(11) NOT NULL DEFAULT 0 ,
    PRIMARY KEY (`student_id`, `course_id`)
    )

            已经建立联合主键的关系表,在面对两个相同student_id和course_id的数据插入时,会抛出主键重复的异常,如下图所示

    表中已经有了student_id=1和course_id=1的数据,如果再插入便会报错。

                                                        

                                                 

    2.使用唯一索引限制

            如果当前的表已经运行了很久,不适合再去修改表结构时,或者是表中的主键已经确定为其他字段时,便不再适合使用联合主键,则可以考虑对表增加非聚集索引之一的唯一索引对关系表做出限制。即将student_id和course_id建立表的唯一索引。其中sql如下:

    CREATE TABLE `t_student_course_rel ` (
    `id`  bigint(20) NOT NULL ,
    `student_id`  bigint(20) NOT NULL ,
    `course_id`  bigint(20) NOT NULL ,
    `marks`  int(11) NOT NULL DEFAULT 0 ,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `in_rel` (`student_id`, `course_id`) USING BTREE 
    )

            (如果表已经建成,则直接用sql加唯一索引即可)

             同样,已经建立联合唯一索引的关系表,在面对两个相同student_id和course_id的数据插入时,会抛出主键重复的异常,即可解决。

                                 

    三、结论

             对关系表中的单个关系的两个外键id做出联合主键以及联合唯一索引,可以有效限制关系表中错误数据以及异常数据的产生(不管是中期的数据插入,还是后期的数据迁移,都能有效地做出限制),而两者的选择就看需求了。

    1.联合主键:在创建表的初期就考虑到这个问题,可以对两个外键id做联合主键,相对于索引来说会省去一些空间。

    2.唯一索引:可以在后期加入索引,对两个外键id做唯一索引,不会修改表结构,但相对来说会增加一些空间。

    展开全文
  •  唯一索引 TESTTEMP . SYS_C0035273 最初处于无法使用的状态   SQL >   SQL > 处理 ORA-26026 问题的方式很简单, 通过alter index ... rebuild online 对索引重建一下就能解决问题。 ...


    1. SQL> exec dbms_stats.gather_table_stats(ownname => 'testtemp',tabname => 'record_partition',degree => 3);
    2.  
    3. PL/SQL procedure successfully completed
    4.  
    5. SQL> select table_name,partition_name,num_rows FROM user_tab_partitions order by 1,2;
    6.  
    7.  
      TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
      ------------------------------ ------------------------------ ----------
      RECORD_INDEX                   PART_011                          7055568
      RECORD_INDEX                   PART_012                          5231180
      RECORD_INDEX                   PART_013                          8568201
      RECORD_PARTITION               PART01_2013                         99986
      RECORD_PARTITION               PART02_2014                             0
      RECORD_PARTITION               PART03_2015                             0
      RECORD_PARTITION               PART04_2016                            13
      RECORD_PARTITION_HASH          PART01                         
      RECORD_PARTITION_HASH          PART02                         
      RECORD_PARTITION_HASH          PART03                         
      RECORD_PARTITION_HASH          PART04                         
       
      11 rows selected
    8.  
    9. SQL> alter table record_partition truncate partition PART01_2013;
    10.  
    11. Table truncated
    12.  
    13. SQL> exec dbms_stats.gather_table_stats(ownname => 'testtemp',tabname => 'record_partition',degree => 3);
    14.  
    15. PL/SQL procedure successfully completed
    16.  
    17. SQL> select count(*) from RECORD_PARTITION;
    18.  
    19.   COUNT(*)
    20. ----------
    21.         13
    22.   
    23. SQL> alter table record_partition truncate partition PART04_2016;
    24.  
    25. Table truncated
    26.  
    27. SQL> exec dbms_stats.gather_table_stats(ownname => 'testtemp',tabname => 'record_partition',degree => 3);
    28.  
    29. PL/SQL procedure successfully completed 

    30. SQL> insert /*+ append */ into record_partition select * from record ;
    31.  
    32. insert /*+ append */ into record_partition select * from record
    33.  
    34. ORA-26026: 唯一的索引 TESTTEMP.SYS_C0035273 最初处于无法使用的状态
    35.  
    36. SQL> 
    37. SQL>
    处理 ORA-26026 问题的方式很简单, 通过alter index ... rebuild online 对索引重建一下就能解决问题。

    点击(此处)折叠或打开

    1. SQL> alter index TESTTEMP.SYS_C0035273 rebuild online;
    2.  
    3. Index altered

    展开全文
  • 最初没有设置unique索引,导致在多线程并发导入excel数据的时候,业务上要求供应商代码不重复的字段,出现了重复,在这张数据表维护的时候,只有逻辑删除,不会物理删除,因此最开始没有去建议供应商代码的unique索引...

    在项目中遇到并发导入excel数据到同一张表,
    最初没有设置unique索引,导致在多线程并发导入excel数据的时候,业务上要求供应商代码不重复的字段,出现了重复,在这张数据表维护的时候,只有逻辑删除,不会物理删除,因此最开始没有去建议供应商代码的unique索引.

    单纯的对供应商代码做unique索引也是不能满足要求的

    因为失效删除的数据是打了标记的,有效的相同供应商代码还是可以插入进来.

    后来想到了加上一个删除时间Delete——time
    默认值为0
    删除时把当前的系统时间毫秒存到这个字段,有效的数据Delete_time就为0

    在删除并发时候,毫秒数一样会提示用户稍后删除.
    这样就实现了多线程插入数据保证一致性的问题

    然后联合业务需求不能重复的字段建立联合索引
    如下图:

    这里就是建立的唯一索引

    展开全文
  • 其实,在《MySQL加锁处理分析》一文中的 组合七:id非唯一索引+RR 部分的最后,我还提出了一个问题:如果组合五、组合六下,针对SQL:select * from t1 where id = 10 for update; 第一次查询,没有找到满足查询...
  • GIS空间索引

    万次阅读 2020-03-11 12:02:02
    在GIS系统中,空间索引技术就是通过更加有效的组织方式,抽取与空间定位相关的信息组成对原空间数据的索引,以较小的数据量管理大量数据的查询,从而提高空间查询的效率和空间定位的准确性。 常见的GIS空间索引 KD...
  • SQL Server索引优化——重复索引

    千次阅读 2019-02-20 18:06:00
    SQL Server索引优化——重复索引 在写完《SQLServer索引优化——无用索引索引缺失》系列后,就想着写点关于发现重复索引的内容,刚好在Kimberly的博文中发现了这篇,就偷懒了,直接将其翻译过来。 一直以来,对...
  • 作者 |.NY&XX责编 | 郭芮出品 | CSDN博客网上已经有很多关于唯一索引和普通索引的区别,这里就不详细阐述了,接下来我们深入讨论如何根据不同业务场景,应该选择普通索引...
  • 检查列的唯一值(基数) 考虑列的顺序 考虑索引类型(行索引 VS.列索引;聚集索引VS非聚集索引) 如果一个表的数据较少,小于8KB,所有数据在一页上,那么表扫描可能比索引查找更适合 使用窄索引 你...
  • Postgresql hash索引介绍

    千次阅读 2019-10-25 16:06:31
    bucket桶的数量最初为2个,然后动态增加以适应数据大小。可以使用位算法从哈希码计算出桶编号。这个bucket将存放TID。 由于可以将与不同索引键匹配的TID放入同一bucket桶中。而且除了TID之外,还可以将键的源值存储...
  • MySQL 聚簇索引和聚簇索引(二级索引
  • MySQL索引【详解】

    千次阅读 多人点赞 2021-08-09 19:40:37
    文章目录第一篇 什么是索引?1、来看一个问题方案1方案2方案3方案42、索引是什么?第二篇 MySQL索引原理详解1、背景2、预备知识什么是索引?磁盘中数据的存取mysql中的页数据检索过程3、我们迫切的需求是什么?...
  • 数据库索引

    2018-11-04 01:20:52
    本文大部分内容来自数据库系统概念(Data System... 建立数据库索引的原因:当对于数据库的许多查询只涉及文件中很少一部分记录时。为了减少查找这些记录的开销,我们可以为存储数据库的文件创建索引。 例如:类似于...
  • oracle 函数索引

    2013-12-12 10:26:08
    在非唯一索引中,ORACLE会把rowid作为一个额外的列追加到键上,使得键唯一。Exp:create index I on T( x , y ) ,从概念上讲就是Create unique index I on T(x , y , rowid)。ORACLE会首先按索引键值排序,然后再...
  • MySQL 索引知识点总结

    千次阅读 2020-12-14 17:56:51
    作者:fanili,腾讯 WXG 后台开发工程师知其然知其所以然!本文介绍索引的数据结构、查找算法、常见的索引概念和索引失效场景。什么是索引?在关系数据库中,索引是一种单独的、物理的对数...
  • mysql 索引整理 - 普通索引

    千次阅读 2011-09-22 21:48:08
    1:只对 where 和 order by 子句中需要的列添加索引,多余的索引智慧导致不必要的硬盘空间爱你消耗。 每次修改表信息时会更新索引,因此有索引的表性能会相应降低。   2:对于要使用索引的列要使用属性 NOT NULL...
  • MongoDB中索引介绍

    2019-08-04 21:42:38
    索引通常能够极大的提高查询的效率,如果没有索引,MongoDB在读取数据时必须扫描集合中的每个文档并选取那些符合查询条件的记录。 mongo中的索引实现方式和引擎有关,版本3.0之前的的引擎默认是MMAPv1,使用内存...
  • MySql中B+索引和ISAM索引介绍

    千次阅读 2013-01-22 20:10:47
    索引顺序存取方法(ISAM, Indexed Sequential Access Method)最初是IBM公司发展起来的一个文件系统,可以连续地(按照他们进入的顺序)或者任意地(根据索引)记录任何访问。每个索引定义了一次不同排列的记录。...
  • 如何创建合适的索引-主要是创建多列索引还是单列索引?  [复制链接] w_z_y 论坛徽章: 0 电梯直达 1#  发表于 2004-12-8 15:21 | 只看该作者 
  • PostgreSQL索引详解3——hash索引

    千次阅读 2020-05-07 09:58:05
    1、简介 现代许多编程语言都将哈希表作为基本的数据类型。哈希表是根据键(Key)而直接...在PostgreSQL中的hash索引也是类似的机构。其主要思想是:将少量的数字(从0到N -1,总共N个值)与任何数据类型的值相关联。...
  • 索引的重要性 数据库性能优化中索引绝对是一个重量级的因素,可以说,索引使用不当,其它优化措施将毫无意义。...最通俗的解释是:聚簇索引的顺序就是数据的物理存储顺序,而对非聚簇索引索引
  • mysql的聚簇索引是指innodb引擎的特性,mysiam并没有,如果需要该索引,只要将索引指定为主键(primary key)就可以了。比如:[sql] view plain copycreate table blog_user ( user_Name char(15) not null...
  • innodb索引概念

    千次阅读 2013-04-09 19:32:10
    author:skate time:2013/04/09   总结记录下innodb的索引概念,以备... 2) 没有主键时,会用一个唯一且不为空的索引列做为主键,成为此表的聚簇索引  3) 如果以上两个都不满足那innodb自己创建一个虚拟的聚集
  • ORACLE索引总结

    千次阅读 2006-10-23 23:19:00
    1) 在非唯一索引中,ORACLE会把rowid作为一个额外的列追加到键上,使得键唯一。Exp:create index I on T( x , y ) ,从概念上讲就是Create unique index I on T(x , y , rowid)。ORACLE会首先按索引键值排序,然后...
  • Oracle之索引索引碎片问题解决

    千次阅读 2019-05-10 12:02:00
    反正死锁问题你看到刚才那个...下面咱们要讲一个概念,这个概念其实也是一样的,除了死锁这个事,咱们讲索引这个事,那索引我还是以ORACLE为例,MYSQL 其实都是一样的,其实都是想通的,其实上面的这些也都是想通的,ORACL...
  • 索引扫描

    千次阅读 2011-07-21 00:10:17
    索引行的存储顺序与表中行的存储顺序之间的相似程度被称为聚簇因子。聚簇因子会随着这种相似程度的不同而不同。 当相类似行的密集程度比较高时,这些数据行就会被密集地存储在相对较少的数据块中,这是聚簇因子比较...
  • MySQL的InnoDB索引原理详解

    千次阅读 2016-07-26 16:48:23
     本篇介绍下Mysql的InnoDB索引相关知识,从各种树到索引原理到存储的细节。  InnoDB是Mysql的默认存储引擎(Mysql5.5.5之前是MyISAM,文档)。本着高效学习的目的,本篇以介绍InnoDB为主,少量涉及MyISAM作为对比...
  • ES索引优化

    千次阅读 2016-07-06 14:33:43
    ES索引的过程到相对Lucene的索引过程多了分布式数据的扩展,而这ES主要是用tranlog进行各节点之间的数据平衡。所以从上我可以通过索引的settings进行第一优化:  “index.translog.flush_threshold_ops”: ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 34,064
精华内容 13,625
关键字:

唯一的索引最初