search

Home >Database >Mysql Tutorial >SQL Server Concurrency

SQL Server Concurrency

WBOY
WBOYOriginal
2016-06-07 14:50:031186browse

并发存在会产生的问题，直接导致了我们需要的并发控制模型。SQL SERVER的每一种并发控制模型都是针对这些问题而设计的，所以首先我们要了解并发的潜在问题有哪些，然后去探索并发控制的模型。 并发控制模型，采用的是锁机制，详细了解了各种锁的兼容机制，才

并发存在会产生的问题，直接导致了我们需要的并发控制模型。SQL SERVER的每一种并发控制模型都是针对这些问题而设计的，所以首先我们要了解并发的潜在问题有哪些，然后去探索并发控制的模型。

并发控制模型，采用的是锁机制，详细了解了各种锁的兼容机制，才能更好了解隔离模型之间的兼容性。锁，所涉及到的概念很多，锁的对象，锁的所属对象，锁的持续时间，锁的种类等等。

知道锁的概念就要学会适当的去用锁。两种方法，事务隔离机制以及锁暗示(lock hint).

1 使用事务隔离机制

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span>
<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.fctdbsize
<span class="hljs-keyword">commit</span> <span class="hljs-keyword">transaction</span></span></code>

这里实例用了最高级别的隔离机制，当目前这个事务在运行时，其他事务都将等待这个事务里用到的资源。除了serializable, 还有 read uncommitted, read committed, read snapshot, read repeated.将这些代入上面的set 语句就可以了。

所需要关心的是各个隔离机制之间是如何兼容的，比如 read uncommitted事务与 serializable 事务之间的竞争关系。从上到下的理解，事务其实用的还是锁，事务之间的兼容最终还是回归到锁之间的兼容。

看下提交一个serializable 事务，但是不提交，看看中间状态的事务，都有哪些特性?

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.fctdbsize</span></code>

这里给事务标记一个事务名字，并附上Mark.

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span> db_name(dbt.database_id) <span class="hljs-keyword">as</span> databaseName
, <span class="hljs-keyword">at</span>.name <span class="hljs-keyword">as</span> transactionName
, <span class="hljs-keyword">at</span>.transaction_id
,<span class="hljs-keyword">at</span>.transaction_begin_time
, <span class="hljs-keyword">case</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">1</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'read and write transaction'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">2</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'read only transaction'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">3</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'system transaction'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_type = <span class="hljs-number">4</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'distributed transaction'</span>
<span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> transaction_type
, <span class="hljs-keyword">case</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">0</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has not been completely initialized yet.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">1</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has been initialized but has not started.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">2</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction is active'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">3</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has ended. This is used for read-only transactions'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">4</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">5</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction is in a prepared state and waiting resolution'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">6</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has been committed.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">7</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction is being rolled back.'</span>
<span class="hljs-keyword">when</span> <span class="hljs-keyword">at</span>.transaction_state = <span class="hljs-number">8</span> <span class="hljs-keyword">then</span> <span class="hljs-string">'The transaction has been rolled back'</span>
<span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> transaction_staus
, trl.request_session_id
, trl.request_mode
, trl.request_type
, trl.request_status
, trl.request_owner_type
, datediff(ss,<span class="hljs-keyword">at</span>.transaction_begin_time, getdate()) <span class="hljs-keyword">as</span> request_lifetime_s
, trl.resource_type
, trl.resource_description
, trl.resource_associated_entity_id
, <span class="hljs-keyword">case</span>
<span class="hljs-keyword">when</span> trl.resource_type = <span class="hljs-string">'OBJECT'</span> <span class="hljs-keyword">then</span> object_name(convert(<span class="hljs-keyword">varchar</span>,trl.resource_associated_entity_id))
<span class="hljs-keyword">else</span>
<span class="hljs-string">'other objects not table'</span>
<span class="hljs-keyword">end</span> <span class="hljs-keyword">as</span> objectName
, logs.host_name
, logs.program_name
, logs.login_name
, req.blocking_session_id
<span class="hljs-keyword">from</span> sys.dm_tran_database_transactions dbt
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_tran_active_transactions <span class="hljs-keyword">at</span> <span class="hljs-keyword">on</span> dbt.transaction_id = <span class="hljs-keyword">at</span>.transaction_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_tran_session_transactions st <span class="hljs-keyword">on</span> st.transaction_id = dbt.transaction_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_tran_locks trl <span class="hljs-keyword">on</span> trl.request_session_id = st.session_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_exec_sessions logs <span class="hljs-keyword">on</span> logs.session_id = st.session_id
<span class="hljs-keyword">left</span> <span class="hljs-keyword">join</span> sys.dm_exec_requests req <span class="hljs-keyword">on</span> req.session_id = st.session_id
<span class="hljs-keyword">where</span> dbt.database_id = db_id(N<span class="hljs-string">'lenistest3'</span>)</span></code>

这里写图片描述

TransactionName这一列和我们刚才定义的事务名称一样；
Transaction_Type其实并不十分精确，因为我们没做写的操作;
Request_mode都是S， 无论是对数据库，还是针对表； request_status表示已经得到这个锁

现在我们尝试往这个表里insert一条记录：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> dbo.fctdbsize(record_date,type_desc,name,<span class="hljs-keyword">size</span>,size_mb,size_gb,x_flag ) <span class="hljs-keyword">select</span> top <span class="hljs-number">1</span> record_date,type_desc,name,<span class="hljs-keyword">size</span>,size_mb,size_gb,x_flag <span class="hljs-keyword">from</span> dbo.fctdbsize</span></code>

没有提示完成，说明在等待，我们看下等待在哪里，用上面的SQL来查：

这里写图片描述

这里对应了request_mode – IX, request_status为WAIT 。我们从sys.dm_exec_requests 取了一个 blocking_session_id给它 ，因为这个query包含了所有的transaction,所以很容易察看到谁在产生Block.

我们将 transaction isolation level改为 read committed.

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> <span class="hljs-keyword">read</span> committed
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.fctdbsize</span></code>

再执行一边 insert，会发现这次执行成功了。 锁在不同的隔离机制下，持续的时间也会不同。一旦独到这个数据，就释放锁。Serializable的隔离，使得锁停留在对象上的时间直到事务结束。

依次尝试这些transaction isolation level，看看哪些会对insert有影响:

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">SET</span> <span class="hljs-keyword">TRANSACTION</span> <span class="hljs-keyword">ISOLATION</span> <span class="hljs-keyword">LEVEL</span>
{ <span class="hljs-keyword">READ</span> UNCOMMITTED
| <span class="hljs-keyword">READ</span> COMMITTED
| REPEATABLE <span class="hljs-keyword">READ</span>
| SNAPSHOT
| SERIALIZABLE
}
[ ;</span> ]</code>

除了 Serializable,还有 repeatable read也使用了大量的行锁，repeatable read对page, table也有IS锁，这是为了保证已读取的数据在整个事务中的一致性，如果insert不小心要更改这些已被读取的数据或者页，都会等待， IS不影响X，这里的insert随机读取一条数据，table上有IS锁，但insert还是成功提交的。

这里写图片描述

上面讨论的是读在前，写在后的场景。我们接下来讨论写在前，而读在后的情况。

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">update</span> dbo.salesman <span class="hljs-keyword">set</span> man_name = man_name + <span class="hljs-string">' test for trans'</span> <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

在表salesman上，事务加上一个 X锁，一直保留到事务结束。这里仅仅是堆表，还需要考虑到有索引的情况，过会讨论 。

然后执行一条只读事务：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> <span class="hljs-keyword">read</span> committed
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> read_committed
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

这里写图片描述

这里可以看到IS锁被 X锁给block了。

还有个有趣的现象，我们不设置事务锁，只执行

<code class=" hljs cs"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></code>

如果我们的监控语句不用left join是不会显示这个单条select语句，原因是在 sys.dm_tran_session_transactions里面不会记录这种ad hoc的事务。所以我们把我们的监控语句改成left join.虽然也不准，但是至少告诉你有这个ad hoc的存在。

这里我们可以用 read uncommitted来读取这条暂时未被提交的事务。Read uncommitted隔离机制，我猜想是没有加任何的锁，除了database share这个锁之外，告诉别的session还有人在用这个数据库，请勿作database一级的处理，比如删除数据库等

还可以用snapshot隔离事务，只读更改前的数据。这个隔离机制也不加锁（我猜）.

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> snapshot
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> read_committed
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

我们用监控语句也发现之有database一级的share操作。

Repeatable Read隔离机制 在这里的作用和 read committed一样的。使用了IS锁都会被X锁block住。

综上， read uncommitted, snapshot都是不加锁的，所以这两种隔离机制中，任何读都不会被write给block住。Read committed, Serializable, Repeatable Read三种隔离机制中，任何读都会被write给block住（当然是针对同一资源而言）。

那我们猜测下，读和读会不会互相block呢? 从最高隔离机制开始：

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

再执行第二个只读事务:

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable1 <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction 2'</span>
<span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> salesman <span class="hljs-keyword">where</span> man_id = <span class="hljs-number">1</span></span></code>

通过监控语句 ，发现两者互不干扰，只是hold S lock的时间长了点。

上面提到有index的时候，update带来的锁情况。我们将表扩大一点：

<code class=" hljs oxygene">declare @<span class="hljs-keyword">loop</span> int = <span class="hljs-number">0</span>
<span class="hljs-keyword">while</span> @<span class="hljs-keyword">loop</span> <= <span class="hljs-number">16</span>
<span class="hljs-keyword">begin</span>
<span class="hljs-keyword">begin</span> transaction
insert <span class="hljs-keyword">into</span> salesman(man_id,man_name,man_country_id)
<span class="hljs-keyword">select</span> man_id,man_name,man_country_id <span class="hljs-keyword">from</span> salesman
commit transaction
<span class="hljs-keyword">set</span> @<span class="hljs-keyword">loop</span> = @<span class="hljs-keyword">loop</span> + <span class="hljs-number">1</span>
<span class="hljs-keyword">end</span></code>

然后执行serializable更新:

<code class=" hljs sql">use lenistest3
go
<span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans_serializable <span class="hljs-keyword">with</span> mark <span class="hljs-string">'test for serializable transaction'</span>
<span class="hljs-keyword">update</span> salesman <span class="hljs-keyword">set</span> man_name = man_name + <span class="hljs-string">' serial transa '</span> <span class="hljs-keyword">where</span> man_name = <span class="hljs-string">'leiws test for trans serial transa trans2 '</span></span></code>

这里写图片描述

这里就直接锁索引了。 RangeS-U是对index key来说的，IU， IX是对index page和表来说的。

同时我们再开一个session来读取对应的索引值：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">set</span> <span class="hljs-keyword">transaction</span> <span class="hljs-keyword">isolation</span> <span class="hljs-keyword">level</span> serializable
<span class="hljs-keyword">begin</span> <span class="hljs-keyword">transaction</span> trans
<span class="hljs-keyword">select</span> man_name <span class="hljs-keyword">from</span> dbo.salesman <span class="hljs-keyword">where</span> man_name = <span class="hljs-string">'leiws test for trans serial transa trans2 '</span></span></code>

这里写图片描述

这里读取的session，采用的是serializable隔离机制，但是并不被另一个session的写操作给影响了。 针对同一个key值，只读session用了RangeS-S锁，可见RangeS-S与RangeS-U并不排斥。

但是奇怪的是，再也不能重现类似的场景了。又一次互相排斥了。结论还需要进一步验证。

2 不使用事务隔离机制，而是用 query option来解决lock的问题

执行下面两个语句：

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman <span class="hljs-keyword">with</span>(nolock)
<span class="hljs-keyword">option</span>(fast <span class="hljs-number">10</span> )

<span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman
<span class="hljs-keyword">option</span>(<span class="hljs-keyword">table</span> hint(dbo.salesman,nolock))</span></code>

第一个查询没问题，但是第二个查询就有问题了:

<code class=" hljs livecodeserver">Msg <span class="hljs-number">8722</span>, Level <span class="hljs-number">16</span>, State <span class="hljs-number">1</span>, Line <span class="hljs-number">15</span> Cannot execute query. Semantic affecting hint <span class="hljs-string">'nolock'</span> appears <span class="hljs-operator">in</span> <span class="hljs-operator">the</span> <span class="hljs-string">'TABLE HINT'</span> clause <span class="hljs-operator">of</span> object <span class="hljs-string">'dbo.salesman'</span> but <span class="hljs-operator">not</span> <span class="hljs-operator">in</span> <span class="hljs-operator">the</span> corresponding <span class="hljs-string">'WITH'</span> clause. Change <span class="hljs-operator">the</span> OPTION (TABLE HINTS...) clause so <span class="hljs-operator">the</span> semantic affecting hints match <span class="hljs-operator">the</span> WITH clause.</code>

Table hint是配合着plan guide一起用的，如果不配合plan guide一起用，就要在with和query hint里面一起使用，限制条件挺多.

<code class=" hljs sql"><span class="hljs-operator"><span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman <span class="hljs-keyword">with</span>(nolock)
<span class="hljs-keyword">option</span>(<span class="hljs-keyword">table</span> hint(dbo.salesman,nolock))</span></code>

这样来用，虽然效果达到了，但是却十分麻烦.

Plan Guides: 当语句是不可更改的时候，比如第三方产品生成的语句或者前端发过来的语句但是作为DBA却不能做更改的时候，我们要更改这个语句的执行计划，该怎么做呢? Plan Guide就登场了。

创建一个plan guide：

<code class=" hljs ruby">sp_create_plan_guide [ <span class="hljs-variable">@name</span> = ] <span class="hljs-constant">N</span><span class="hljs-string">'plan_guide_name'</span>
    , [ <span class="hljs-variable">@stmt</span> = ] <span class="hljs-constant">N</span><span class="hljs-string">'statement_text'</span>
    , [ <span class="hljs-variable">@type</span> = ] <span class="hljs-constant">N</span><span class="hljs-string">'{ OBJECT | SQL | TEMPLATE }'</span>
    , [ <span class="hljs-variable">@module_or_batch</span> = ]
      { 
                    <span class="hljs-constant">N</span><span class="hljs-string">'[ schema_name. ] object_name'</span>
        | <span class="hljs-constant">N</span><span class="hljs-string">'batch_text'</span>
        | <span class="hljs-constant">NULL</span>
      }
    , [ <span class="hljs-variable">@params</span> = ] { <span class="hljs-constant">N</span><span class="hljs-string">'@parameter_name data_type [ ,...n ]'</span> | <span class="hljs-constant">NULL</span> } 
    , [ <span class="hljs-variable">@hints</span> = ] { <span class="hljs-constant">N</span><span class="hljs-string">'OPTION ( query_hint [ ,...n ] )'</span> 
                 | <span class="hljs-constant">N</span><span class="hljs-string">'XML_showplan'</span>
                 | <span class="hljs-constant">NULL</span> }</code>

删除一个 plan guide:

<code class=" hljs mathematica">sp_control_plan_guide [ @operation = ] <span class="hljs-keyword">N</span><span class="hljs-string">'<control_option>'</span>
  [ , [ @name = ] <span class="hljs-keyword">N</span><span class="hljs-string">'plan_guide_name'</span> ]
<control_option>::=
<span class="hljs-list">{ 
    DROP 
  | DROP ALL
  | DISABLE
  | DISABLE ALL
  | ENABLE 
  | ENABLE ALL
}</span></code>

监控所有的plan guides :

<code class=" hljs ruleslanguage"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> sys.plan_guides</code>

一个简单的例子：

<code class=" hljs perl"><span class="hljs-keyword">exec</span> sp_create_plan_guide
<span class="hljs-variable">@name</span> = N<span class="hljs-string">'salesman_idx_query'</span>
, <span class="hljs-variable">@stmt</span> =N<span class="hljs-string">'select top 10 * from dbo.salesman'</span>
, <span class="hljs-variable">@type</span> = N<span class="hljs-string">'SQL'</span>
, <span class="hljs-variable">@module_or_batch</span> = NULL
, <span class="hljs-variable">@params</span> = NULL
, <span class="hljs-variable">@hints</span> = N<span class="hljs-string">'option (table hint (dbo.salesman, index(idx_man_Id)))'</span>

<span class="hljs-keyword">exec</span> sp_control_plan_guide
<span class="hljs-variable">@operation</span> = N<span class="hljs-string">'drop'</span>
, <span class="hljs-variable">@name</span> = N<span class="hljs-string">'salesman_idx_query'</span></code>

再次执行

<code class=" hljs cs"><span class="hljs-keyword">select</span> top <span class="hljs-number">10</span> * <span class="hljs-keyword">from</span> dbo.salesman</code>

这里写图片描述

这里不再是做全表扫描，而是先扫描索引，再做bookmark lookup了。当然这个例子只是对plan guide的一个应用，并没有给性能带来好的提升。

Statement：
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Previous article：MySQL学习13：操作数据表中的记录(一)Next article：在大学时的分布式数据库读书笔记 拿出来分享

Related articles

See more