MySQL Principle and Optimization: Update Optimization

< br>< img SRC="/ / img.inotgo.com/imagesLocal/202208/10/202208101431472921_5.jpg" Alt=null loading=lazy>< br>< div> about Update Statement you may not strange, in many cases we can use it to update records in the table.In general we will use innodb Storage engines, innodb engine is based on the row locks, say they specifically targeting the index to lock () to ensure that no lock failure, is not to record locking, if for no indexed field filtering operation, to perform update , can lead to table locks & have spent.< / div>< div> today, just look at how to use when use the innodb update Statements.< / div>< br>< div> existing course Table, its content is as follows: < / div>< br>< img SRC="/ / img.inotgo.com/imagesLocal/202208/10/202208101431472921_3.png" Alt=null loading=lazy>< br>< br>< div> is the table without any index, id  by default;Is the index to the table.< / div>< div> then let us open two transactions respectively (the client), respectively, to perform the following instructions.< / div>< div> open the first transaction < / div>< div> begin;< / div>< div> update Course Set Name = & have spent'Java' & have spentWhere Id = & have spent1;< / div>< br>< img SRC="/ / img.inotgo.com/imagesLocal/202208/10/202208101431472921_4.png" Alt=null loading=lazy>< br>< br>< div> open another transaction < / div>< div> begin;< / div>< div> update Course Set Name = & have spent'Spark' & have spentWhere Id = & have spent4.< / div>< img SRC="/ / img.inotgo.com/imagesLocal/202208/10/202208101431472921_1.png" Alt=null loading=lazy>< br>< div> two transactions can execute, and then respectively perform two transactions & have spentCommit The result of the operation, can see the update.< / div>< br>< img SRC="/ / img.inotgo.com/imagesLocal/202208/10/202208101431472921_0.png" Alt=null loading=lazy>< br>< div> two transactions can be executed in parallel condition are id Is the table course Index, can be due to update Id Corresponding 2 & have spentAnd 4 & have spent, only for these two rows are locked.< / div>< div> then let's look at another example, is still open two transactions, but where Choose use name  conditions;And name Not as course The index of the table.< / div>< div> to start a transaction: < / div>< div> begin;< / div>< div> update Course Set Name = & have spent'the Vue. Js & have spentWhere Name = & have spent'PHP';< / div>< div> and then perform another transaction < / div>< div> begin;< / div>< div> update Course Set Name = & have spent'used to' & have spentWhere Name = & have spent'MySQL';< / div>< div> at this point in the implementation of the second transaction update Statement, will be blocked.Because for name The filter conditions not course The index of the update  at this time;The lock table statement for operation, order must be a transaction commit, release table locks, the second transaction to continue.< / div>< br>< img SRC="/ / img.inotgo.com/imagesLocal/202208/10/202208101431472921_2.png" Alt=null loading=lazy>< br>< br>< div> in order to let the two transactions can be executed in parallel, we will name Join the course The index.< / div>< div> create Index Index_name & have spentOn Course (name);< / div>< div> show Index From Course;< / div>< br>< img SRC="/ / img.inotgo.com/imagesLocal/202208/10/202208101431472921_6.png" Alt=null loading=lazy>< br>< div> then perform just two transactions, the two transactions to update a record different respectively, where The name of the condition of different cases, also won't appear the situation of the lock table, this is because the name As the course The index.< / div>< div> to demonstrate is convenient, we will update the condition here adjust: < / div>< div> to start a transaction: < / div>< div> begin;< / div>< div> update Course Set Name = & have spent'PHP' & have spentWhere Name = & have spent'the Vue. Js';< / div>< div> and then perform another transaction < / div>< div> begin;< / div>< div> update Course Set Name = & have spent'MySQL' & have spentWhere Name = & have spent'used to';< / div>< div> results as well as we had expected, two transactions can be executed in parallel, at the same time be able to see results after a commit.< / div>< br>< div> to summarize, & have spentIf use the innodb storage engines, update When where Conditions, < / div>< div> conditions field is the index < / div>< div> situation can promote the efficiency of the update, avoid the occurring of the lock table.