mysql数据库优化索引_MySQL数据库优化之SQL及索引

1、数据准备

(1)使用MySQL官方提供的sakila数据库：参考 http://dev.mysql.com/doc/index-other.html

(2)下载安装参考：http://dev.mysql.com/doc/sakila/en/sakila-installation.html

source e:/sakila-db/sakila-schema.sql

source e:/sakila-db/sakila-data.sql

(3)本人使用的MySQL版本为5.5.39

2、开启慢查询日志

(1)查看是否开启了慢查询日志

show variables like 'slow_query_log'

从上图结果中看出暂时未开启

(2)查询没有使用索引的语句是否开启了慢查询

show variables like '%log_queries_not_using_indexes%'

没有开启，把它开启：

(3) 查询记录慢查询日志的时间

show variables like 'long_query_time';

上图是设置了10s时间，为了方便，设置长1：

set global long_query_time=1;      (4)开启慢查询日志功能

set global slow_query_log=on;      (5)查看日志记录位置

show variables like 'slow%';

也可以对位置进行更改。

set global slow_query_log_file='C:/mysqllogs/first_slow.log';

3、慢查询日志的存储格式

执行如下sql语句，查看日志记录格式：

select * from store limit 5; 查看日志得到如下结果：

# Time: 150816 16:49:11 ---执行SQL的时间

# User@Host: root[root] @ localhost [127.0.0.1] ---执行SQL的主机信息

# Query_time: 0.027000 Lock_time: 0.027000 Rows_sent: 2 Rows_examined: 2

use sakila; -----SQL的执行信息

SET timestamp=1439714951; ----时间戳格式

select * from store limit 5; ----具体的SQL内容

4、慢查询日志之分析工具mysqldumpslow

查看日志(windows下需要安装perl环境)，该命令有很多参数：

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysqldumpslow.pl -t 1 C:\mysqllogs\f

irst_slow.log结果如下：

5、使用explain查询sql的执行计划

explain select * from store limit 5;如下图：

explain 返回各列的含义如下：

table：查询的表

type：显示连接使用的类型，从最好到最差的类型为：const、eq-reg、ref、range、index和ALL

possible-keys：显示可能应用到这张表的索引

key：实际使用的索引

key_len：使用的索引的长度，长度越短越好

ref：显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：请求数据的行数。