PAGEPAGE6mysql优化实战案例优化：定位问题，解决方案，优化后性能分析【实战分析】在此分享一下我在开发过程中遇到的SQL优化问题，希望能对大家有所帮助，不足之处请多多指正。关于SQL优化我们关注的有三个点，分别是1.如何定位待优化的SQL，2.如何分析SQL的执行效率，3.给出优化方案。一、对于定位慢SQL，我们可以使用慢查询日志1.慢查询日志用于记录执行时间=设置的执行时间的SQL，MySQL默认关闭。2.打开慢查询日志并设置慢查询时间查看慢查询日志打开状态通过showvariableslike#39;%query%#39;命令查看慢查询日志的配置。long_query_time表示设置的慢查询时间，为10秒，表示SQL执行时间=10秒时，该条SQL会被记录到慢查询日志文件；slow_query_log表示慢查询日志的打开状态，默认为OFF，表示关闭；slow_query_log_file表示慢查询日志的文件路径；通过setglobalslow_query_log=on;命令，打开慢查询日志，通过setgloballong_query_time=1;设置慢查询时间为1秒，慢查询日志文件的路径，我们不动配置。运行命令后，我们再次查看状态，如下图所示：更改配置需要注意的是，慢查询日志的时间其实已经更改了，需要先退出客户端，重新连一下服务器，我们才能看到修改后的值。二、通过explain命令查看SQL执行效率1.首先是数据准备，我们向数据表中插入300万条数据，测试数据的生成和插入，我是通过python脚本实现的，下面我粘贴一下完整代码，大家可以参考一下#!/usr/bin/python#!coding=utf-8importpymysqlimportrandomimportstringimportsyshost=#39;127.0.0.1#39;port=3306user=#39;root#39;passwd=#39;123456#39;db=#39;demo#39;sql=#39;insertintouser(id,name,sex,age)value(%s,%s,%s,%s)#39;insert_count=sys.argv[1]conn=pymysql.connect(host,user,passwd,db,port,charset=#39;utf8mb4#39;,cursorclass=pymysql.cursors.DictCursor)cur=conn.cursor()cur.execute(#39;deletefromuser#39;)foriinrange(1,int(insert_count)):cur.execute(sql,[i,#39;#39;.join(random.sample(string.ascii_letters,6)),random.randint(0,1),random.randint(1,160)])conn.commit()cur.close()上述为完整的python脚步，大家可以根据实际情况调整，将其拷贝进文件，通过pythonfile_name.py200000,这条命令执行批量插入的动作，200000表示向数据表写入199999条数据。(ps:数据库名称demo,数据表名称：user,下图附user表结构)user表结构2.通过慢查询日志定位慢SQL慢SQL查看慢查询日志如上图所示，我们执行了一条SQL，其执行时间为1.25秒。我们设置的慢查询时间为1秒，然后，我们通过cat/var/lib/mysql/xut-slow.log命令，在慢查询日志中看到了我们执行的SQL。3.通过explain分析执行效率执行效率分析如上图，我们通过explain命令查看了这条慢SQL的执行效率，我们主要关注key这个字段，我们看到这个字段值为NULL，表示未命中索引。我们看一下user这张表的索引有哪些，如下图所示，有3个索引。user表结构注：关于explain命中执行结果的各个字段值的含义，大家可自行在网络上查询了解。三、SQL优化通过第二步的explain命令查看了SQL执行效率，我们发现语句未命中索引，所以我们最直接的思路是让其命中索引，如果查询字段未建立索引，我们可以先创建索引，创建索引的语句为altertabletable_nameaddindexindex_name(字段名称);我们发现name字段是创建了索引，但是却没有走这个索引，这是因为like做模糊查询时，我们写法是like#39;%aaaa%#39;，左边的通配符导致索引未命