精华内容
下载资源
问答
  • SQL Server索引介绍

    2020-07-14 14:26:53
    文章目录一、索引的分类二、聚集索引表和堆表1、聚集索引表2、堆表三、索引相关命令1、索引缺失统计2、无效索引统计3、查看索引的统计信息 一、索引的分类 聚集索引 SQL Server中的聚集索引是以B-tree的数据结构...

    一、索引的分类

    • 聚集索引

    SQL Server中的聚集索引是以B-tree的数据结构进行存储的。B-tree中每个数据页都是一个索引节点,最上端的索引节点被称为根节点,最下端的索引节点被称为叶子节点,根节点与叶子节点之间的索引节点为中间节点。

    在聚集索引中,叶子节点包含了所有行记录数据。

    聚集索引是按照索引键有序存储的。相对于堆存储,聚集索引的范围查询是非常高效的。

    一张表只能有一个聚集索引,尽量将聚集索引列设置为IDENTITY。

    聚集索引字段不宜进行频繁变更、不建议使用大宽列作为聚集索引。

    当聚集索引只有一个字段分区时,其B-tree结构如下图所示,index_id=1为该字段分区的索引。当聚集索引有多个字段分区时,每个字段分区都有一个独立的B-tree结构。如一个聚集索引有4个字段分区,那么该表会创建4个B-tree结构。

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6kbsC4q8-1594707963215)(http://note.youdao.com/yws/res/59746/0D8CD630FF3142BD9E0D0FE023C59725)]

    • 非聚集索引

    非聚集索引可以氛围两种情况,一种情况是索引组织表存储、另外一种情况是堆表存储。

    无论是堆表还是索引组织表,非聚集索引的存储结构都是B-tree存储,都是根据索引键有序存储。堆表与索引组织表的区别主要在于其叶子节点存储内容,堆表通过行指针指向其对应的RID、索引组织表通过行指针指向其对应的聚集索引值。

    当使用非聚集索引查询数据时,遍历非聚集索引定位满足条件的数据,然后通过Bookmark的方式根据对应的聚集索引查找非聚集索引外的其他字段数据。

    非聚集索引的索引长度上限为900字节,include字段不包含在索引键中,不计算其字段长度

    如:Title nvarchar(50)、Revision nchar(5)、FileName nvarchar(400)
    可以创建复合索引(Title,Revision) include (FileName),其索引长度为(50+5)*2
    
    CREATE INDEX IX_Document_Title   
    ON Production.Document (Title, Revision)   
    INCLUDE (FileName);
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3nYGOMwG-1594707963218)(http://note.youdao.com/yws/res/59794/E30AAE6A15B341268519CF2AE9BE349B)]

    • 唯一索引

    保证索引字段唯一性的约束,一张表可以有多个唯一索引。

    唯一索引也可以使用include字段做包含性索引

    • 复合索引

    多个字段创建的索引称为复合索引,复合索引需要注意各字段的顺序,建议把选择性高的字段、查询频繁的字段放在前缀列

    • 包含性索引

    包含性索引的关键字是include,该索引与覆盖索引类似,都是为了避免回表操作。与覆盖索引不同的是,include字段不在索引键列,而是在非键列,所以无法对include字段进行条件过滤,仅仅为了select查询字段避免回表使用。

    在包含性索引中,非键列最大上限为1023

    索引键列最大上限为16、字段长度为900字节

    同一列无法同时出现在索引键以及include非键列

    • 覆盖索引

    查询与过滤字段均通过索引键就可以返回,不需要回表操作,合理使用覆盖索引进行优化。

    • 筛选索引

    也称为过滤索引,创建是通过where关键字进行筛选,满足条件的记录创建索引,不满足条件的记录不包含在索引中。

    -- 在view_count字段中满足>50的记录上创建索引
    create nonclustered index idx_count on deadlock_test(view_count) where view_count>50;
    
    -- 有效利用筛选索引
    select id from deadlock_test where view_count>60
    -- 无法有效利用筛选索引
    select id from deadlock_test where view_count<30
    
    • 计算列索引

    在表中创建一个新的字段用于保存列只算后的结果,为该字段创建索引后,这个索引成为计算索引

    计算列数据实际存储在表中,需要额外创建一个字段来添加索引,功能使用上还不如mysql5.7的虚拟列的设计。

    CREATE TABLE TestTable (a int, b varbinary(4));
    
    CREATE INDEX TestTabIndex ON dbo.TestTable(a,b)  
    WHERE b = CONVERT(Varbinary(4), 1);
    
    • 索引视图

    物化视图,标准的视图不会将结果集永久的存储在数据库中,而是每次使用的时候动态生成,而索引视图通过with schemabinding 的关键字为视图创建唯一聚集索引来把结果集保存在数据库中。

    • 列存储索引

    二、聚集索引表和堆表

    1、聚集索引表

    也叫索引组织表,表的存储是按照聚集索引的顺序进行存储的,聚集索引的叶子节点包含所有记录。二级索引中的叶子节点指向对应的聚集索引值,当查询需要回表时通过聚集索引回表查询。

    2、堆表

    在SQL Server中,若没有为表创建聚集索引,则该表存储的方式为堆表存储。堆就是无序数据的集合,索引就是将数据变得有序,在索引中键值有序,数据还是无序的。在堆表中,二级索引的每个键都会指向一个RID,若需要查询非索引外其他字段数据通过RID进行回表查询。

    三、索引相关命令

    1、索引缺失统计

    -- 索引缺失统计
    select ddmid.statement,ddmid.equality_columns,ddmid.inequality_columns,ddmid.included_columns,
    ddmigs.user_seeks,ddmigs.user_scans,ddmigs.avg_total_user_cost,ddmigs.avg_user_impact
    from sys.dm_db_missing_index_details ddmid
    join sys.dm_db_missing_index_groups ddmig
    on ddmid.index_handle=ddmig.index_handle
    join sys.dm_db_missing_index_group_stats ddmigs
    on ddmig.index_group_handle=ddmigs.group_handle
    
    字段 含义
    statement 缺失索引的表名
    user_seeks 从服务端启动到现在,缺失索引可以被用户发起请求用于seek操作的次数
    user_scans 从服务器启动到现在,缺失索引可以被用户发起请求用于scan操作的次数
    avg_total_user_cast 缺失索引被创建后,平均降低资源消耗的成本数,该参数越大表示创建索引后效率提升越高
    avg_user_impact 缺失索引创建后,平均降低成本的百分比

    2、无效索引统计

    -- 无效索引统计
    select i.name,ddius.user_seeks,ddius.user_lookups,ddius.user_updates
    from sys.dm_db_index_usage_stats ddius
    join sys.indexes i
    on ddius.index_id=i.index_id and ddius.object_id=i.object_id
    order by ddius.user_seeks
    

    3、查看索引的统计信息

    -- 查看具体索引的统计信息
    dbcc show_statistics('deadlock_test','idx_info_uuid')
    
    -- 查看表中所有索引的统计信息
    select * 
    from sys.stats as s
    where s.object_id=object_id('table_name')
    
    -- 更新指定索引的统计信息
    UPDATE STATISTICS dbo.table_name idx_name;
    
    -- 更新表中所有统计信息
    UPDATE STATISTICS Sales.SalesOrderDetail;
    
    --更新整个数据库上的所有统计信息
    EXEC sp_updatestats;
    
    
    展开全文
  • 数据库索引

    2019-05-09 15:31:16
    1,原理:对要查询的字段建立索引其实就是把该字段按照一定的方式排序;建立索引只对该字段有用,如果查询的字段改变,这个索引也就无效了。 2,为什么索引会增加速度:DB在执行一条Sql语句的时候,默认...聚集索引和...

    1,原理:对要查询的字段建立索引其实就是把该字段按照一定的方式排序;建立索引只对该字段有用,如果查询的字段改变,这个索引也就无效了。
    2,为什么索引会增加速度:DB在执行一条Sql语句的时候,默认的方式是根据搜索条件进行全表扫描,遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引,查询时就会下去索引列表中一次定位到特定值的行数,大大减少遍历匹配的行数,所以能明显增加查询速度。
    聚集索引和非聚集索引:https://www.cnblogs.com/aspwebchh/p/6652855.html
    链接文章总结
    一个加了主键的表,并不能被称之为。一个没加主键的表,它的数据无序的放置在磁盘存储器上,一行一行的排列的很整齐, 跟我认知中的很接近。如果给表上了主键,那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构,也就是平衡树结构,换句话说,就是整个表就变成了一个索引。
    一个表只能有一个聚集索引,因为主键的作用就是把的数据格式转换成索引(平衡树)的格式放置。
    非聚集索引和聚集索引的区别在于, 通过聚集索引可以查到需要查找的数据, 而通过非聚集索引可以查到记录对应的主键值 , 再使用主键的值通过聚集索引查找到需要的数据
    不管以任何方式查询表, 最终都会利用主键通过聚集索引来定位到数据, 聚集索引(主键)是通往真实数据所在的唯一路径。
    索引的缺点
    事物都是有两面的, 索引能让数据库查询数据的速度上升, 而使写入数据的速度下降,原因很简单的, 因为平衡树这个结构必须一直维持在一个正确的状态, 增删改数据都会改变平衡树各节点中的索引数据内容,破坏树结构, 因此,在每次数据改变时, DBMS必须去重新梳理树(索引)的结构以确保它的正确,这会带来不小的性能开销
    每次给字段建一个新索引, 字段中的数据就会被复制一份出来, 用于生成索引。 因此, 给表添加索引,会增加表的体积, 占用磁盘存储空间。

    索引的种类
    普通索引:index
    唯一索引:unique index
    主键索引:primary key
    全文索引:fulltext

    如何创建索引
    创建表:CREATE TABLE Person (LastName varchar(30) , FirstName varchar(30),Address varchar(30) , age int(10));
    创建索引
    索引被创建于已有的表中,它可使对行的定位更快速更有效。可以在表格的一个或者多个列上创建索引,每个索引都会被起个名字。用户无法看到索引,它们只能被用来加速查询。
    注释:更新一个包含索引的表需要比更新一个没有索引的表更多的时间,这是由于索引本身也需要更新。因此,理想的做法是仅仅在常常用于搜索的列上面创建索引。
    唯一的索引 (Unique Index)
    在表格上面创建某个一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
    CREATE UNIQUE INDEX 索引名称
    ON 表名称 (列名称)
    “列名称” 规定你需要索引的列。
    简单的索引
    在表上创建一个简单的索引。当我们省略关键词 UNIQUE 时,就可以使用重复的值。
    CREATE INDEX 索引名称
    ON 表名称 (列名称)
    “列名称” 规定你需要索引的列。
    实例
    本例会创建一个简单的索引,名为 “PersonIndex”,在 Person 表的 LastName 字段:
    CREATE INDEX PersonIndex
    ON Person (LastName)
    如果您希望以降序索引某个列中的值,您可以在列名称之后添加保留字 DESC:
    CREATE INDEX PersonIndex
    ON Person (LastName DESC)
    假如您希望索引不止一个列,您可以在括号中列出这些列的名称,用逗号隔开:
    CREATE INDEX PersonIndex
    ON Person (LastName, FirstName)
    上述内容均收集自网络。

    展开全文
  • 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。 详细说说 6 种索引: 普通索引:最基本的...
  • MySql-索引、锁、事务

    2019-05-30 23:58:15
    1.索引 索引,类似书籍的目录,可以根据目录的某个页码立即找到...索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合...

    1.索引

    索引,类似书籍的目录,可以根据目录的某个页码立即找到对应的内容。

    索引的优点:1. 天生排序,2. 快速查找。

    索引的缺点:1. 占用空间,2. 降低更新表的速度。

    注意点:小表使用全表扫描更快,中大表才使用索引。超级大表索引基本无效。

    索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引)

    从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。

    详细说说 6 种索引:

    1、普通索引:最基本的索引,没有任何约束。

    2、唯一索引:与普通索引类似,但具有唯一性约束。

    3、主键索引:特殊的唯一索引,不允许有空值

    4、复合索引:将多个列组合在一起创建索引,可以覆盖多个列。

    5、外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。

    6、全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎(ES,Solr)。

    注意:主键就是唯一索引,但是唯一索引不一定是主键,唯一索引可以为空,但是空值只能有一个,主键不能为空。

    另外,InnoDB 通过主键聚簇数据,如果没有定义主键且没有定义聚集索引, MySql 会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个 6 字节的主键作为聚簇索引,用户不能查看或访问。

    简单点说:

    1、设置主键时,会自动生成一个唯一索引,如果之前没有聚集索引,那么主键就是聚集索引。

    2、没有设置主键时,会选择一个不为空的唯一索引作为聚集索引,如果还没有,那就生成一个隐式的 6 字节的索引。

    MySql 将数据按照页来存储,默认一页为 16kb,当你在查询时,不会只加载某一条数据,而是将这个数据所在的页都加载到 pageCache 中,这个其实和 OS 的就近访问原理类似。

    MySql 的索引使用 B+ 树结构。在说 B+ 树之前,先说说 B 树,B 树是一个多路平衡查找树,相较于普通的二叉树,不会发生极度不平衡的状况,同时也是多路的。

    B 树的特点是:他会将数据也保存在非页子节点。

    看图可知:

    而这个特点会导致非页子节点不能存储大量的索引。

    而 B+ Tree 就是针对这个对 B tree 做了优化。如下图所示:

    我们看到,B+ Tree 将所有的 data 数据都保存到了叶子节点中,非也子节点只保存索引和指针。

    我们假设一个非页子节点是 16kb,每个索引,即主键是 bigint,即 8b,指针为 8b。那么每页能存储大约 1000 个索引(16kb/ 8b + 8b).

    而一颗 3 层的 B+树能够存储多少索引呢?

    如下图:

    大约能够存储 10 亿个索引。通常 B+ 树的高度在 2-4 层,由于 MySql 在运行时,根节点是常驻内存的,因此每次查找只需要大约 2 -3 次 IO。可以说,B+ 树的设计,就是根据机械磁盘的特性来进行设计的。

    知道了索引的设计,我们能够知道另外一些信息:

    1、MySql 的主键不能太大,如果使用 UUID 这种,将会浪费 B+ 树的非叶子节点。 2、MySql 的主键最好是自增的,如果使用 UUID 这种,每次插入都会调整 B+树,从而导致页分裂,严重影响性能。

    那么,如果项目中使用了分库分表,我们通常都会需要一个主键进行 sharding,那怎么办呢?在实现上,我们可以保留自增主键,而逻辑主键用来作为唯一索引即可。

    2.锁机制

    关于 Mysql 的锁,各种概念就会喷涌而出,事实上,锁有好几种维度,我们来解释一下。

    1.类型维度

    共享锁(读锁 / S 锁) 排它锁(写锁 / X 锁)

    类型细分:

    • 意向共享锁

    • 意向排他(互斥)锁

    悲观锁(使用锁,即 for update)

    乐观锁(使用版本号字段,类似 CAS 机制,即用户自己控制。缺点:并发很高的时候,多了很多无用的重试)

    2.锁的粒度(粒度维度)

    表锁 页锁(Mysql BerkeleyDB 引擎) 行锁(InnoDB)

    3.锁的算法(算法维度)

    Record Lock(单行记录) Gap Lock(间隙锁,锁定一个范围,但不包含锁定记录) Next-Key Lock(Record Lock + Gap Lock,锁定一个范围,并且锁定记录本身, MySql 防止幻读,就是使用此锁实现)

    4.默认的读操作,上锁吗?

    默认是 MVCC 机制(“一致性非锁定读”)保证 RR 级别的隔离正确性,是不上锁的。

    可以选择手动上锁:select xxxx for update (排他锁); 

    select xxxx lock in share mode(共享锁),称之为“一致性锁定读”。

    使用锁之后,就能在 RR 级别下,避免幻读。当然,默认的 MVCC 读,也能避免幻读。

    既然 RR 能够防止幻读,那么,SERIALIZABLE 有啥用呢?

    防止丢失更新。例如下图:

    这个时候,我们必须使用 SERIALIZABLE 级别进行串行读取。

    最后,行锁的实现原理就是锁住聚集索引,如果你查询的时候,没有正确地击中索引,MySql 优化器将会抛弃行锁,使用表锁。

    3.事务

    事务是数据库永恒不变的话题, ACID:原子性,一致性,隔离性,持久性

    四个特性,最重要的就是一致性。而一致性由原子性,隔离性,持久性来保证。

    原子性由 Undo log 保证。Undo Log 会保存每次变更之前的记录,从而在发生错误时进行回滚。隔离性由 MVCC 和 Lock 保证。这个后面说。持久性由 Redo Log 保证。每次真正修改数据之前,都会将记录写到 Redo Log 中,只有 Redo Log 写入成功,才会真正的写入到 B+ 树中,如果提交之前断电,就可以通过 Redo Log 恢复记录。

    然后再说隔离性。

    隔离级别:

    1、未提交读(RU)

    2、已提交读(RC)

    3、可重复读(RR)

    4、串行化(serializable)

    每个级别都会解决不同的问题,通常是3 个问题:脏读,不可重复读,幻读。一张经典的图:

    这里有个注意点,关于幻读,在数据库规范里,RR 级别会导致幻读,但是,由于 Mysql 的优化,MySql 的 RR 级别不会导致幻读:在使用默认的 select 时,MySql 使用 MVCC 机制保证不会幻读;

    你也可以使用锁,在使用锁时,例如 for update(X 锁),lock in share mode(S 锁),MySql 会使用 Next-Key Lock 来保证不会发生幻读。前者称为快照读,后者称为当前读。

    原理剖析:

    1、RU 发生脏读的原因:RU 原理是对每个更新语句的行记录进行加锁,而不是对整个事务进行加锁,所以会发生脏读。而 RC 和 RR 会对整个事务加锁。 2、RC 不能重复读的原因:RC 每次执行 SQL 语句都会生成一个新的 Read View,每次读到的都是不同的。而 RR 的事务从始至终都是使用同一个 Read View。 3、RR 不会发生幻读的原因: 上面说过了。

    那 RR 和 Serializble 有什么区别呢?答:丢失更新。本文关于锁的部分已经提到。

    MVCC 介绍:全称多版本并发控制。

    innoDB 每个聚集索引都有 4 个隐藏字段,分别是主键(RowID),最近更改的事务 ID(MVCC 核心),Undo Log 的指针(隔离核心),索引删除标记(当删除时,不会立即删除,而是打标记,然后异步删除);

    本质上,MVCC 就是用 Undo Log 链表实现。

    MVCC 的实现方式:事务以排它锁的方式修改原始数据,把修改前的数据存放于 Undo Log,通过回滚指针与数据关联,如果修改成功,什么都不做,如果修改失败,则恢复 Undo Log 中的数据。

    多说一句,通常我们认为 MVCC 是类似乐观锁的方式,即使用版本号,而实际上,innoDB 不是这么实现的。当然,这不影响我们使用 MySql。

     

    展开全文
  • MySql 知识点——索引、锁、事务

    千次阅读 2018-05-16 21:23:29
    1. 索引 索引,类似书籍的目录,可以根据目录的某个页码立即...索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,...

    1. 索引

    索引,类似书籍的目录,可以根据目录的某个页码立即找到对应的记录。

    索引的优点:

    1. 天生排序。
    2. 快速查找。

    索引的缺点:

    1. 占用空间。
    2. 降低更新表的速度。

    注意点:小表使用全表扫描更快,中大表才使用索引。超级大表索引基本无效。

    索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引)

    从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。

     

    详细说说 6 种索引:

    1. 普通索引:最基本的索引,没有任何约束。
    2. 唯一索引:与普通索引类似,但具有唯一性约束。
    3. 主键索引:特殊的唯一索引,不允许有空值
    4. 复合索引:将多个列组合在一起创建索引,可以覆盖多个列。
    5. 外键索引:只有InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。
    6. 全文索引:MySQL 自带的全文索引只能用于 InnoDB、MyISAM ,并且只能对英文进行全文检索,一般使用全文索引引擎(ES,Solr)。
    注意:主键就是唯一索引,但是唯一索引不一定是主键,唯一索引可以为空,但是空值只能有一个,主键不能为空。

    另外,InnoDB 通过主键聚簇数据,如果没有定义主键且没有定义聚集索引, MySql 会选择一个唯一的非空索引代替,如果没有这样的索引,会隐式定义个 6 字节的主键作为聚簇索引,用户不能查看或访问。

    简单点说:

    1. 设置主键时,会自动生成一个唯一索引,如果之前没有聚集索引,那么主键就是聚集索引。
    2. 没有设置主键时,会选择一个不为空的唯一索引作为聚集索引,如果还没有,那就生成一个隐式的 6 字节的索引。

    MySql 将数据按照页来存储,默认一页为 16kb,当你在查询时,不会只加载某一条数据,而是将这个数据所在的页都加载到 pageCache 中,这个其实和 OS 的就近访问原理类似。

    MySql 的索引使用 B+ 树结构。在说 B+ 树之前,先说说 B 树,B 树是一个多路平衡查找树,相较于普通的二叉树,不会发生极度不平衡的状况,同时也是多路的。

    B 树的特点是:他会将数据也保存在非页子节点。

    看图可知:

    而这个特点会导致非页子节点不能存储大量的索引。

    而 B+ Tree 就是针对这个对 B tree 做了优化。如下图所示:

    我们看到,B+ Tree 将所有的 data 数据都保存到了叶子节点中,非也子节点只保存索引和指针。

    我们假设一个非页子节点是 16kb,每个索引,即主键是 bigint,即 8b,指针为 8b。那么每页能存储大约 1000 个索引(16kb/ 8b + 8b).

    而一颗 3 层的 B+树能够存储多少索引呢?如下图:

    大约能够存储 10 亿个索引。通常 B+ 树的高度在 2-4 层,由于 MySql 在运行时,根节点是常驻内存的,因此每次查找只需要大约 2 -3 次 IO。可以说,B+ 树的设计,就是根据机械磁盘的特性来进行设计的。

    知道了索引的设计,我们能够知道另外一些信息:

    1. MySql 的主键不能太大,如果使用 UUID 这种,将会浪费 B+ 树的非叶子节点。
    2. MySql 的主键最好是自增的,如果使用 UUID 这种,每次插入都会调整 B+树,从而导致页分裂,严重影响性能。

    那么,如果项目中使用了分库分表,我们通常都会需要一个主键进行 sharding,那怎么办呢?在实现上,我们可以保留自增主键,而逻辑主键用来作为唯一索引即可。

    2. 锁机制

    关于 Mysql 的锁,各种概念就会喷涌而出,事实上,锁有好几种维度,我们来解释一下。

    1. 类型维度

    • 共享锁(读锁 / S 锁)
    • 排它锁(写锁 / X 锁)
      类型细分:
      • 意向共享锁
      • 意向排他(互斥)锁
    • 悲观锁(使用锁,即 for update)
    • 乐观锁(使用版本号字段,类似 CAS 机制,即用户自己控制。缺点:并发很高的时候,多了很多无用的重试)

    2. 锁的粒度(粒度维度)

    • 表锁
    • 页锁(Mysql BerkeleyDB 引擎)
    • 行锁(InnoDB)

    3. 锁的算法(算法维度)

    • Record Lock(单行记录)
    • Gap Lock(间隙锁,锁定一个范围,但不包含锁定记录)
    • Next-Key Lock(Record Lock + Gap Lock,锁定一个范围,并且锁定记录本身, MySql 防止幻读,就是使用此锁实现)

    4. 默认的读操作,上锁吗?

    • 默认是 MVCC 机制(“一致性非锁定读”)保证 RR 级别的隔离正确性,是不上锁的。

    可以选择手动上锁:select xxxx for update (排他锁); select xxxx lock in share mode(共享锁),称之为“一致性锁定读”。

    使用锁之后,就能在 RR 级别下,避免幻读。当然,默认的 MVCC 读,也能避免幻读。

    既然 RR 能够防止幻读,那么,SERIALIZABLE 有啥用呢?

    防止丢失更新。例如下图:

    这个时候,我们必须使用 SERIALIZABLE 级别进行串行读取。

    最后,行锁的实现原理就是锁住聚集索引,如果你查询的时候,没有正确地击中索引,MySql 优化器将会抛弃行锁,使用表锁。

    3. 事务

    事务是数据库永恒不变的话题, ACID:原子性,一致性,隔离性,持久性。

    四个特性,最重要的就是一致性。而一致性由原子性,隔离性,持久性来保证。

    • 原子性由 Undo log 保证。Undo Log 会保存每次变更之前的记录,从而在发生错误时进行回滚。
    • 隔离性由 MVCC 和 Lock 保证。这个后面说。
    • 持久性由 Redo Log 保证。每次真正修改数据之前,都会将记录写到 Redo Log 中,只有 Redo Log 写入成功,才会真正的写入到 B+ 树中,如果提交之前断电,就可以通过 Redo Log 恢复记录。

    然后再说隔离性。

    隔离级别:

    1. 未提交读(RU)
    2. 已提交读(RC)
    3. 可重复读(RR)
    4. 串行化(serializable)

    每个级别都会解决不同的问题,通常是3 个问题:脏读,不可重复读,幻读。一张经典的图:

    这里有个注意点,关于幻读,在数据库规范里,RR 级别会导致幻读,但是,由于 Mysql 的优化,MySql 的 RR 级别不会导致幻读:在使用默认的 select 时,MySql 使用 MVCC 机制保证不会幻读;你也可以使用锁,在使用锁时,例如 for update(X 锁),lock in share mode(S 锁),MySql 会使用 Next-Key Lock 来保证不会发生幻读。前者称为快照读,后者称为当前读。

    原理剖析:

    • RU 发生脏读的原因:RU 原理是对每个更新语句的行记录进行加锁,而不是对整个事务进行加锁,所以会发生脏读。而 RC 和 RR 会对整个事务加锁。
    • RC 不能重复读的原因:RC 每次执行 SQL 语句都会生成一个新的 Read View,每次读到的都是不同的。而 RR 的事务从始至终都是使用同一个 Read View。
    • RR 不会发生幻读的原因: 上面说过了。

    那 RR 和 Serializble 有什么区别呢?答:丢失更新。本文关于锁的部分已经提到。

    MVCC 介绍:全称多版本并发控制。

    innoDB 每个聚集索引都有 4 个隐藏字段,分别是主键(RowID),最近更改的事务 ID(MVCC 核心),Undo Log 的指针(隔离核心),索引删除标记(当删除时,不会立即删除,而是打标记,然后异步删除);

    本质上,MVCC 就是用 Undo Log 链表实现。

    MVCC 的实现方式:事务以排它锁的方式修改原始数据,把修改前的数据存放于 Undo Log,通过回滚指针与数据关联,如果修改成功,什么都不做,如果修改失败,则恢复 Undo Log 中的数据。

    多说一句,通常我们认为 MVCC 是类似乐观锁的方式,即使用版本号,而实际上,innoDB 不是这么实现的。当然,这不影响我们使用 MySql。
     

     

    展开全文
  • 数据库整理(索引

    2019-09-25 03:03:23
    2 索引使用树形结构提高查询速度的操作叶子节点上:数据大小小,且区分度高聚集索引:即主键索引:叶子节点存在id,关联着对应数据(主键)where ... 只能在主键的时候明显提高检索速度 只能给一个字段加主键,如果...
  • 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。 详细说说 6 种索引: 1、普通索引:最...
  • 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。 详细说说 6 种索引: 1、普通索引:最...
  • 1. 索引索引,类似书籍的目录,可以根据目录的...索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引)从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索...
  • 一、索引 索引,类似书籍的目录,可以根据目录的...索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,...
  • MySQL:简述对索引、锁、事务的认识 一、索引 索引,类似书籍的目录,可以根据目录的某个页码立即...索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引)。 从功能上说,分为 6 种:普通...
  • 索引 索引,类似书籍的目录,可以根据目录的某个页码立即找到...索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,...
  • 通常说的这个查询走索引了是什么意思? 当我们对某个字段的值进行某种...分为聚集索引和非聚集索引,前面已经介绍过,这里不再介绍! 非聚集索引又分为: 单列索引:即一个索引只包含一个列。 多列索引(又称复合索
  • 1. 索引 索引,类似书籍的目录,可以根据目录的某个页码立即找到...索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索...
  • 无主键、索引或者没有查询索引无效,是产品查询慢的最常见问题,以下是数据库表主键和索引设计的主要原则 1、主键 主键ID,主键既是约束也是索引,同时也用于对象缓存的键值。 2、索引 *组合或者引用关系的子表...
  • 无主键、索引或者没有查询索引无效,是产品查询慢的最常见问题,以下是数据库表主键和索引设计的主要原则1、主键主键ID,主键既是约束也是索引,同时也用于对象缓存的键值。2、索引 *组合或者引用关系的子表(数据...
  • 索引从实现上说,分成 2 种:聚集索引和辅助索引(也叫二级索引或者非聚集索引) 从功能上说,分为 6 种:普通索引,唯一索引,主键索引,复合索引,外键索引,全文索引。 详细说说 6 种索引: 1、普通索引:最基本...
  • 9.4 聚集函数 154 9.4.1 组值函数中的NULL 154 9.4.2 单值函数和组值函数的示例 155 9.4.3 AVG、COUNT、MAX、MIM和SUM 156 9.4.4 组值函数和单值函数的组合 156 9.4.5 STDDEV 和VARIANCE 158 9.4.6 组函数中...
  • 2.1.2 聚集可用空间的技术 2.1.3 管理区的碎片 2.1.4 查找接近 MAXEXTENTS 值的 对象 2.1.5 避免数据字典的碎片 2.1.6 本地管理区 2.1.7 进行全数据库重组 2.1.8 定义区大小和防止碎片的11个 技巧 2.1.9 ...

空空如也

空空如也

1 2
收藏数 34
精华内容 13
关键字:

聚集索引索引无效