首页 › MySQL论坛 › MySql基础知识总结SQL优化技巧

醉听风影 發表於 2025-12-24 11:01:57

MySql基础知识总结SQL优化技巧

<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">一、explain返回列简介</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">1、type常用关键字</a></li><li><a href="#_lab2_0_1">2、Extra常用关键字</a></li></ul><li><a href="#_label1">二、触发索引代码实例</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_2">1、建表语句 + 联合索引</a></li><li><a href="#_lab2_1_3">2、使用主键查询</a></li><li><a href="#_lab2_1_4">3、使用联合索引查询</a></li><li><a href="#_lab2_1_5">4、联合索引，但与索引顺序不一致</a></li><li><a href="#_lab2_1_6">5、联合索引，但其中一个条件是 &gt;</a></li><li><a href="#_lab2_1_7">6、联合索引，order by</a></li></ul><li><a href="#_label2">三、单表sql优化</a></li><ul class="second_class_ul"><li><a href="#_lab2_2_8">1、删除student表中的联合索引。</a></li><li><a href="#_lab2_2_9">2、添加索引</a></li><li><a href="#_lab2_2_10">3、更改索引顺序</a></li><li><a href="#_lab2_2_11">4、去掉in</a></li><li><a href="#_lab2_2_12">5、小结</a></li></ul><li><a href="#_label3">四、双表sql优化</a></li><ul class="second_class_ul"><li><a href="#_lab2_3_13">1、建表语句</a></li><li><a href="#_lab2_3_14">2、左连接查询</a></li><li><a href="#_lab2_3_15">3、小结</a></li></ul><li><a href="#_label4">五、避免索引失效的一些原则</a></li><ul class="second_class_ul"></ul><li><a href="#_label5">六、一些其他的优化方法</a></li><ul class="second_class_ul"><li><a href="#_lab2_5_16">1、exist和in</a></li><li><a href="#_lab2_5_17">2、order by 优化</a></li></ul><li><a href="#_label6">七、sql顺序 -&gt; 慢日志查询</a></li><ul class="second_class_ul"><li><a href="#_lab2_6_18">1、慢查询日志</a></li><li><a href="#_lab2_6_19">2、阈值</a></li></ul><li><a href="#_label7">八、慢查询日志 --&gt;&nbsp;mysqldumpslow工具</a></li><ul class="second_class_ul"><li><a href="#_lab2_7_20">1、mysqldumpslow工具</a></li><li><a href="#_lab2_7_21">2、查询不同条件下的慢sql</a></li></ul><li><a href="#_label8">九、分析海量数据</a></li><ul class="second_class_ul"><li><a href="#_lab2_8_22">1、show profiles</a></li><li><a href="#_lab2_8_23">2、精确分析，sql诊断</a></li><li><a href="#_lab2_8_24">3、全局查询日志</a></li></ul><li><a href="#_label9">十、锁机制详解</a></li><ul class="second_class_ul"><li><a href="#_lab2_9_25">1、操作分类</a></li><li><a href="#_lab2_9_26">2、操作范围</a></li><li><a href="#_lab2_9_27">3、加读锁，代码实例</a></li><li><a href="#_lab2_9_28">4、加写锁</a></li><li><a href="#_lab2_9_29">5、MyISAM表级锁的锁模式</a></li><li><a href="#_lab2_9_30">6、MyISAM分析表锁定</a></li><li><a href="#_lab2_9_31">7、InnoDB分析表锁定</a></li><li><a href="#_lab2_9_32">8、加行锁代码实例</a></li><li><a href="#_lab2_9_33">9、行锁的注意事项</a></li></ul></ul></div><p>本篇是MySQL知识体系总结系列的第二篇，该篇的主要内容是通过explain逐步分析sql，并通过修改sql语句与建立索引的方式对sql语句进行调优，也可以通过查看日志的方式，了解sql的执行情况，还介绍了MySQL数据库的行锁和表锁。</p>
<p class="maodian"><a name="_label0"></a></p><h2>一、explain返回列简介</h2>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>1、type常用关键字</h3>
<p>system &gt; const &gt; eq_ref &gt; ref &gt; range &gt; index &gt; all。</p>
<ol><li>system：表仅有一行，基本用不到；</li><li>const：表最多一行数据配合，主键查询时触发较多；</li><li>eq_ref：对于每个来自于前面的表的行组合，从该表中<span>读取一行</span>。这可能是最好的联接类型，除了const类型；</li><li>ref：对于每个来自于前面的表的行组合，<span>所有有匹配索引值的行</span>将从这张表中读取；</li><li>range：只检索给定范围的行，使用一个索引来选择行。当使用=、&lt;&gt;、&gt;、&gt;=、&lt;、&lt;=、IS NULL、&lt;=&gt;、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；</li><li>index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；</li><li>all：全表扫描；</li></ol>
<p>实际sql优化中，最后达到ref或range级别。</p>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>2、Extra常用关键字</h3>
<p>Using index：只从索引树中获取信息，而<span>不需要回表查询；</span></p>
<p>Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。<span>需要回表查询。</span></p>
<p>Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；</p>
<p>索引原理及explain用法请参照前一篇：<a href="https://www.jb51.net/database/355528zxn.htm" target="_blank">MySQL索引原理，explain详解</a></p>
<p class="maodian"><a name="_label1"></a></p><h2>二、触发索引代码实例</h2>
<p class="maodian"><a name="_lab2_1_2"></a></p><h3>1、建表语句 + 联合索引</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE `student` (
`id` int(10) NOT NULL,
`name` varchar(20) NOT NULL,
`age` int(10) NOT NULL,
`sex` int(11) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`phone` varchar(100) DEFAULT NULL,
`create_time` timestamp NULL DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
`deleted` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `student_union_index` (`name`,`age`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</pre></div>
<p class="maodian"><a name="_lab2_1_3"></a></p><h3>2、使用主键查询</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410455997.png" /></p>
<p class="maodian"><a name="_lab2_1_4"></a></p><h3>3、使用联合索引查询</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410455919.png" /></p>
<p class="maodian"><a name="_lab2_1_5"></a></p><h3>4、联合索引，但与索引顺序不一致</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410455925.png" /></p>
<p>备注：因为mysql优化器的缘故，与索引顺序不一致，也会触发索引，但实际项目中尽量顺序一致。</p>
<p class="maodian"><a name="_lab2_1_6"></a></p><h3>5、联合索引，但其中一个条件是 &gt;</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460082.png" /></p>
<p class="maodian"><a name="_lab2_1_7"></a></p><h3>6、联合索引，order by</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460095.png" /></p>
<p>where和order by一起使用时，不要跨索引列使用。</p>
<p class="maodian"><a name="_label2"></a></p><h2>三、单表sql优化</h2>
<p class="maodian"><a name="_lab2_2_8"></a></p><h3>1、删除student表中的联合索引。</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460030.png" /></p>
<p class="maodian"><a name="_lab2_2_9"></a></p><h3>2、添加索引</h3>
<div class="jb51code"><pre class="brush:sql;">alter table student add index student_union_index(name,age,sex);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460089.png" /></p>
<p>优化一点，但效果不是很好，因为type是index类型，extra中依然存在using where。</p>
<p class="maodian"><a name="_lab2_2_10"></a></p><h3>3、更改索引顺序</h3>
<p>因为sql的编写过程</p>
<div class="jb51code"><pre class="brush:sql;">select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...</pre></div>
<p>解析过程</p>
<div class="jb51code"><pre class="brush:sql;">from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...</pre></div>
<p>&nbsp;因此我怀疑是联合索引建的顺序问题，导致触发索引的效果不好。are you sure？试一下就知道了。</p>
<div class="jb51code"><pre class="brush:sql;">alter table student add index student_union_index2(age,sex,name);</pre></div>
<p>删除旧的不用的索引：</p>
<div class="jb51code"><pre class="brush:sql;">drop index student_union_index on student</pre></div>
<p>索引改名</p>
<div class="jb51code"><pre class="brush:sql;">ALTER TABLE student RENAME INDEX student_union_index2 TO student_union_index</pre></div>
<p>更改索引顺序之后，发现type级别发生了变化，由index变为了range。</p>
<p>range：只检索给定范围的行，使用一个索引来选择行。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460021.png" /></p>
<p>备注：in会导致索引失效，所以触发using where，进而导致回表查询。</p>
<p class="maodian"><a name="_lab2_2_11"></a></p><h3>4、去掉in</h3>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460066.png" /></p>
<p>ref：对于每个来自于前面的表的行组合，<span>所有有匹配索引值的行</span>将从这张表中读取；</p>
<p>index 提升为ref了，优化到此结束。</p>
<p class="maodian"><a name="_lab2_2_12"></a></p><h3>5、小结</h3>
<ol><li>保持索引的定义和使用顺序一致性；</li><li>索引需要逐步优化，不要总想着一口吃成胖子；</li><li>将含in的范围查询，放到where条件的最后，防止索引失效；</li></ol>
<p class="maodian"><a name="_label3"></a></p><h2>四、双表sql优化</h2>
<p class="maodian"><a name="_lab2_3_13"></a></p><h3>1、建表语句</h3>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE `student` (
`id` int(10) NOT NULL,
`name` varchar(20) NOT NULL,
`age` int(10) NOT NULL,
`sex` int(11) DEFAULT NULL,
`address` varchar(100) DEFAULT NULL,
`phone` varchar(100) DEFAULT NULL,
`create_time` timestamp NULL DEFAULT NULL,
`update_time` timestamp NULL DEFAULT NULL,
`deleted` int(11) DEFAULT NULL,
`teacher_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</pre></div>
<div class="jb51code"><pre class="brush:sql;">CREATE TABLE `teacher` (
`id` int(11) DEFAULT NULL,
`name` varchar(100) DEFAULT NULL,
`course` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;</pre></div>
<p class="maodian"><a name="_lab2_3_14"></a></p><h3>2、左连接查询</h3>
<div class="jb51code"><pre class="brush:sql;">explain select s.name,t.name from student s left join teacher t on s.teacher_id = t.id where t.course = '数学'</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460121.png" /></p>
<p>上一篇介绍过，联合查询时，小表驱动大表。小表也称为驱动表。其实就相当于双重for循环，小表就是外循环，第二张表（大表）就是内循环。</p>
<p><span>虽然最终的循环结果都是一样的，都是循环一样的次数，但是对于双重循环来说，一般建议将数据量小的循环放外层，数据量大的放内层，这是编程语言的优化原则。</span></p>
<p>再次代码测试：</p>
<p>student数据：四条</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460128.png" /></p>
<p>teacher数据：三条</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460181.png" /></p>
<p>按照理论分析，teacher应该为驱动表。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460268.png" /></p>
<p>sql语句应该改为：</p>
<div class="jb51code"><pre class="brush:sql;">explain select teacher.name,student.name from teacher left join student on teacher.id = student.idwhere teacher.course = '数学'</pre></div>
<p>优化一般是需要索引的，那么此时，索引应该怎么加呢？往哪个表上加索引？</p>
<p>索引的基本理念是：索引要建在经常使用的字段上。</p>
<p>由<span>on teacher.id = student.id</span>可知，teacher表的id字段使用较为频繁。</p>
<p>left join on，一般给左表加索引；因为是驱动表嘛。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460272.png" /></p>
<div class="jb51code"><pre class="brush:sql;">alter table teacher add index teacher_index(id);
alter table teacher add index teacher_course(course);</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460230.png" /></p>
<p>备注：如果extra中出现using join buffer，表明mysql底层觉得sql写的太差了，mysql加了个缓存，进行优化了。</p>
<p class="maodian"><a name="_lab2_3_15"></a></p><h3>3、小结</h3>
<ol><li>小表驱动大表</li><li>索引建立在经常查询的字段上</li><li>sql优化，是一种概率层面的优化，是否实际使用了我们的优化，需要通过explain推测。</li></ol>
<p class="maodian"><a name="_label4"></a></p><h2>五、避免索引失效的一些原则</h2>
<p>1、复合索引，不要跨列或无序使用（最佳左前缀）；</p>
<p>2、符合索引，尽量使用全索引匹配；</p>
<p>3、不要在索引上进行任何操作，例如对索引进行（计算、函数、类型转换），索引失效；</p>
<p>4、复合索引不能使用不等于（!=或&lt;&gt;）或 is null（is not null），否则索引失效；</p>
<p>5、尽量使用覆盖索引（using index）；</p>
<p>6、like尽量以常量开头，不要以%开头，否则索引失效；如果必须使用%name%进行查询，可以使用覆盖索引挽救，不用回表查询时可以触发索引；</p>
<p>7、尽量不要使用类型转换，否则索引失效；</p>
<p>8、尽量不要使用or，否则索引失效；</p>
<p class="maodian"><a name="_label5"></a></p><h2>六、一些其他的优化方法</h2>
<p class="maodian"><a name="_lab2_5_16"></a></p><h3>1、exist和in</h3>
<div class="jb51code"><pre class="brush:sql;">select name,age from student exist/in (子查询);</pre></div>
<p>如果主查询的数据集大，则使用in；</p>
<p>如果子查询的数据集大，则使用exist；</p>
<p class="maodian"><a name="_lab2_5_17"></a></p><h3>2、order by 优化</h3>
<p>using filesort有两种算法：双路排序、双路排序（根据IO的次数）</p>
<p>MySQL4.1之前，默认使用双路排序；双路：扫描两次磁盘（①从磁盘读取排序字段，对排序字段进行排序；②获取其它字段）。</p>
<p>MySQL4.1之后，默认使用单路排序；单路：只读取一次（全部字段），在buffer中进行排序。但单路排序会有一定的隐患（不一定真的是只有一次IO，有可能多次IO）。</p>
<p>注意：单路排序会比双路排序占用更多的buffer。</p>
<p>单路排序时，如果数据量较大，可以调大buffer的容量大小。</p>
<div class="jb51code"><pre class="brush:sql;">set max_length_for_sort_data = 1024;单位是字节byte。</pre></div>
<p>如果max_length_for_sort_data值太低，MySQL底层会自动将单路切换到双路。</p>
<p>太低指的是列的总大小超过了max_length_for_sort_data定义的字节数。</p>
<p>提高order by查询的策略：</p>
<ol><li>选择使用单路或双路，调整buffer的容量大小；</li><li>避免select * from student;（① MySQL底层需要对*进行翻译，消耗性能；② *永远不会触发索引覆盖 using index）；</li><li>符合索引不要跨列使用，避免using filesort；</li><li>保证全部的排序字段，排序的一致性（都是升序或降序）；</li></ol>
<p class="maodian"><a name="_label6"></a></p><h2>七、sql顺序 -&gt; 慢日志查询</h2>
<p>慢查询日志就是MySQL提供的一种日志记录，用于记录MySQL响应时间超过阈值的SQL语句（long_query_time，默认10秒） ；</p>
<p>慢日志默认是关闭的，开发调优时打开，最终部署时关闭。</p>
<p class="maodian"><a name="_lab2_6_18"></a></p><h3>1、慢查询日志</h3>
<p>（1）检查是否开启了慢查询日志：</p>
<div class="jb51code"><pre class="brush:sql;">show variables like '%slow_query_log%'</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460163.png" /></p>
<p>（2）临时开启：</p>
<div class="jb51code"><pre class="brush:sql;">set global slow_query_log = 1;</pre></div>
<p>（3）重启MySQL：&nbsp;</p>
<div class="jb51code"><pre class="brush:sql;">service mysql restart;</pre></div>
<p>（4）永久开启：</p>
<p>/etc/my.cnf中追加配置：</p>
<p>放到下：</p>
<div class="jb51code"><pre class="brush:sql;">slow_query_log=1

slow_query_log_file=/var/lib/mysql/localhost-slow.log</pre></div>
<p class="maodian"><a name="_lab2_6_19"></a></p><h3>2、阈值</h3>
<p>（1）查看默认阈值：</p>
<div class="jb51code"><pre class="brush:sql;">show variables like '%long_query_time%'</pre></div>
<p>（2）临时修改默认阈值：</p>
<div class="jb51code"><pre class="brush:sql;">set global long_query_time = 5;</pre></div>
<p>（3）永久修改默认阈值：</p>
<p>/etc/my.cnf中追加配置：</p>
<p>放到下：</p>
<p>long_query_time = 5;</p>
<p>（4）MySQL中的sleep：</p>
<div class="jb51code"><pre class="brush:sql;">select sleep(5);</pre></div>
<p>（5）查看执行时间超过阈值的sql：</p>
<div class="jb51code"><pre class="brush:sql;">show global status like '%slow_queries%';</pre></div>
<p class="maodian"><a name="_label7"></a></p><h2>八、慢查询日志 --&gt;&nbsp;mysqldumpslow工具</h2>
<p class="maodian"><a name="_lab2_7_20"></a></p><h3>1、mysqldumpslow工具</h3>
<p>慢查询的sql被记录在日志中，可以通过日志查看具体的慢sql。</p>
<div class="jb51code"><pre class="brush:sql;">cat /var/lib/mysql/localhost-slow.log</pre></div>
<p>通过mysqldumpslow工具查看慢sql，可以通过一些过滤条件，快速查出需要定位的慢sql。</p>
<div class="jb51code"><pre class="brush:sql;">mysqldumpslow --help</pre></div>
<p>参数简要介绍：</p>
<p>s：排序方式</p>
<p>r：逆序</p>
<p>l：锁定时间</p>
<p>g：正则匹配模式</p>
<p class="maodian"><a name="_lab2_7_21"></a></p><h3>2、查询不同条件下的慢sql</h3>
<p>（1）返回记录最多的3个SQL</p>
<div class="jb51code"><pre class="brush:sql;">mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log</pre></div>
<p>（2）获取访问次数最多的3个SQL</p>
<div class="jb51code"><pre class="brush:sql;">mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log</pre></div>
<p>（3）按时间排序，前10条包含left join查询语句的SQL</p>
<div class="jb51code"><pre class="brush:sql;">mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/localhost-slow.log</pre></div>
<p class="maodian"><a name="_label8"></a></p><h2>九、分析海量数据</h2>
<p class="maodian"><a name="_lab2_8_22"></a></p><h3>1、show profiles</h3>
<p>打开此功能：set profiling = on;</p>
<p>show profiles会记录所有profileing打来之后，全部SQL查询语句所花费的时间。</p>
<p>缺点是不够精确，确定不了是执行哪部分所消耗的时间，比如CPU、IO。</p>
<p class="maodian"><a name="_lab2_8_23"></a></p><h3>2、精确分析，sql诊断</h3>
<p>show profile all for query&nbsp; 上一步查询到的query_id。</p>
<p class="maodian"><a name="_lab2_8_24"></a></p><h3>3、全局查询日志</h3>
<p>show variables like &#39;%general_log%&#39;</p>
<p>开启全局日志：</p>
<p>set global general_log = 1;</p>
<p>set global log_output = table;</p>
<p class="maodian"><a name="_label9"></a></p><h2>十、锁机制详解</h2>
<p class="maodian"><a name="_lab2_9_25"></a></p><h3>1、操作分类</h3>
<p>读写：对同一个数据，多个读操作可以同时进行，互不干扰。</p>
<p>写锁：如果当前写操作没有完毕，则无法进行其它的读写操作。</p>
<p class="maodian"><a name="_lab2_9_26"></a></p><h3>2、操作范围</h3>
<p>表锁：一次性对一张表整体加锁。</p>
<p>如MyISAM存储引擎使用表锁，开销小、加锁快、无死锁；但锁的范围大，容易发生冲突、并发度低。</p>
<p>行锁：一次性对一条数据加锁。</p>
<p>如InnoDB存储引擎使用的就是行锁，开销大、加锁慢、容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率发生高并发问题：脏读、幻读、不可重复读）</p>
<p>lock table 表1 read/write，表2 read/write，...</p>
<p>查看加锁的表：</p>
<p>show open tables;</p>
<p class="maodian"><a name="_lab2_9_27"></a></p><h3>3、加读锁，代码实例</h3>
<div class="jb51code"><pre class="brush:sql;">会话0：
lock table student read;
select * from student; --查，可以
delete from student where id = 1;--增删改，不可以
select * from user; --查，不可以
delete from user where id = 1;--增删改，不可以</pre></div>
<p>如果某一个会话对A表加了read锁，则该会话可以对A表进行读操作、不能进行写操作。即如果给A表加了读锁，则当前会话只能对A表进行读操作，其它表都不能操作</p>
<div class="jb51code"><pre class="brush:sql;">会话1：
select * from student; --查，可以
delete from student where id = 1;--增删改，会“等待”会话0将锁释放
会话1：
select * from user; --查，可以
delete from user where id = 1;--增删改，可以</pre></div>
<p>会话0给A表加了锁，其它会话的操作①可以对其它表进行读写操作②对A表：读可以，写需要等待释放锁。</p>
<p class="maodian"><a name="_lab2_9_28"></a></p><h3>4、加写锁</h3>
<div class="jb51code"><pre class="brush:sql;">会话0：
lock table student write;</pre></div>
<p>当前会话可以对加了写锁的表，可以进行任何增删改查操作；但是不能操作其它表；</p>
<p>其它会话：</p>
<p>对会话0中对加写锁的表，可以进行增删改查的前提是：等待会话0释放写锁。</p>
<p class="maodian"><a name="_lab2_9_29"></a></p><h3>5、MyISAM表级锁的锁模式</h3>
<p>MyISAM在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改前，会自动给涉及的表加写锁。</p>
<p>所以对MyISAM表进行操作，会有如下情况发生：</p>
<p>（1）对MyISAM表的读操作（加读锁），不会阻塞其它会话（进程）对同一表的读请求。但会阻塞对同一表的写操作。只有当读锁释放后，才会执行其它进程的写操作。</p>
<p>（2）对MyISAM表的写操作（加写锁），会阻塞其它会话（进程）对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。</p>
<p class="maodian"><a name="_lab2_9_30"></a></p><h3>6、MyISAM分析表锁定</h3>
<p>查看哪些表加了锁：</p>
<p>show open tables;1代表被加了锁</p>
<p>分析表锁定的严重程度：</p>
<p>show status like &#39;table%&#39;;</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460139.png" /></p>
<p>Table_locks_immediate：可能获取到的锁数</p>
<p>Table_locks_waited：需要等待的表锁数（该值越大，说明存在越大的锁竞争）</p>
<p>一般建议：Table_locks_immediate/Table_locks_waited &gt; 5000，建议采用InnoDB引擎，否则采用MyISAM引擎。</p>
<p class="maodian"><a name="_lab2_9_31"></a></p><h3>7、InnoDB分析表锁定</h3>
<p>为了研究行锁，暂时将自动commit关闭，set autocommit = 0;</p>
<p>show status like &#39;%innodb_row_lock%&#39;;</p>
<p>Innodb_row_lock_current_waits：当前正在等待锁的数量<br />Innodb_row_lock_time：等待总时长。从系统启动到现在一共等待的时间<br />Innodb_row_lock_time_avg：平均等待时长。从系统启动到现在一共等待的时间<br />Innodb_row_lock_time_max：最大等待时长。从系统启动到现在一共等待的时间<br />Innodb_row_lock_waits：等待次数。从系统启动到现在一共等待的时间</p>
<p class="maodian"><a name="_lab2_9_32"></a></p><h3>8、加行锁代码实例</h3>
<p>（1）查询student</p>
<div class="jb51code"><pre class="brush:sql;">select id,name,age from student</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460260.png" /></p>
<p>（2）更新student&nbsp;</p>
<div class="jb51code"><pre class="brush:sql;">update student set age = 18 where id = 1</pre></div>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460288.png" /></p>
<p>（3）加行锁&nbsp;</p>
<p>通过select id,name,age from student for update;给查询加行锁。</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460268.png" /></p>
<p>依旧修改成功，原因是MySQL默认是自动提交的，因此需要暂时将自动commit关闭</p>
<p>set autocommit = 0;</p>
<p style="text-align:center"><img alt="" src="https://img.jbzj.com/file_images/article/202512/2025122410460214.png" /></p>
<p class="maodian"><a name="_lab2_9_33"></a></p><h3>9、行锁的注意事项</h3>
<p>（1）如果没有索引，行锁自动转为表锁。</p>
<p>（2）行锁只能通过事务解锁。</p>
<p>（3）InnoDB默认采用行锁</p>
<p>优点：并发能力强，性能高，效率高</p>
<p>缺点：比表锁性能损耗大</p>
<p>高并发用InnoDb，否则用MyISAM。</p>

查看完整版本: MySql基础知识总结SQL优化技巧