mysql sql优化教程_SQL优化

在此分享一下我在开发过程中遇到的SQL优化问题，希望能对大家有所帮助，不足之处请多多指正。

关于SQL优化我们关注的有三个点，分别是1.如何定位待优化的SQL，2.如何分析SQL的执行效率，3.给出优化方案。

一、对于定位慢SQL，我们可以使用慢查询日志

1.慢查询日志用于记录执行时间>=设置的执行时间的SQL，MySQL默认关闭。

2.打开慢查询日志并设置慢查询时间查看慢查询日志打开状态

通过show variables like '%query%'命令查看慢查询日志的配置。long_query_time表示设置的慢查询时间，为10秒，表示SQL执行时间>=10秒时，该条SQL会被记录到慢查询日志文件；slow_query_log表示慢查询日志的打开状态，默认为OFF，表示关闭；slow_query_log_file表示慢查询日志的文件路径；

通过set global slow_query_log=on;命令，打开慢查询日志，通过set global long_query_time = 1;设置慢查询时间为1秒，慢查询日志文件的路径，我们不动配置。运行命令后，我们再次查看状态，如下图所示：更改配置

需要注意的是，慢查询日志的时间其实已经更改了，需要先退出客户端，重新连一下服务器，我们才能看到修改后的值。

二、通过explain命令查看SQL执行效率

1.首先是数据准备，我们向数据表中插入300万条数据，测试数据的生成和插入，我是通过python脚步实现的，下面我粘贴一下完整代码，大家可以参考一下

#!/usr/bin/python

#!coding=utf-8

import pymysql

import random

import string

import sys

host='127.0.0.1'

port=3306

user='root'

passwd='123456'

db='demo'

sql='insert into user(id, name, sex, age) value (%s, %s, %s, %s)'

insert_count=sys.argv[1]

conn=pymysql.connect(host, user, passwd, db, port, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

cur = conn.cursor()

cur.execute('delete from user')

for i in range(1, int(insert_count)):

cur.execute(sql, [i, ''.join(random.sample(string.ascii_letters, 6)), random.randint(0, 1), random.randint(1, 160)])

conn.commit()

cur.close()

上述为完整的python脚步，大家可以根据实际情况调整，将其拷贝进文件，通过python file_name.py 200000, 这条命令执行批量插入的动作，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执行效率，我们发现语句未命中索引，所以我们最直接的思路是让其命中索引，如果查询字段未建立索引，我们可以先创建索引，创建索引的语句为alter table table_name add index index_name (字段名称); 我们发现name字段是创建了索引，但是却没有走这个索引，这是因为like做模糊查询时，我们写法是like '%aaaa%'，左边的通配符导致索引未命中，未命中的原因，大家可以自己在网上搜索一下，我们去掉左边的通配符，就能够命中索引了，如下图所示：优化后的执行效率

如上图，我们看到，优化后，我们的SQL语句命中了索引idx_name，进而其执行效率更高了。

4、总结

以上就SQL的优化做了一个简单的介绍，这些内容适用于简单的业务场景，大家可以做个了解。