阅读(2.5k) 书签赞(1) 我要纠错

MySQL索引及优化

2018-06-08 17:40 更新

防伪码：路曼曼其修远兮，吾将上下而求索。

一、MySQL 性能优化之-影响性能的因素

1. 商业需求的影响

不合理需求造成资源投入产出比过低，这里我们就用一个看上去很简单的功能来分析一下。

需求：一个论坛帖子总量的统计，附加要求：实时更新

从功能上来看非常容易实现，执行一条 SELECT COUNT(*) from 表名的 Query 就可以得到结

果。但是，如果我们采用不是 MyISAM 存储引擎，而是使用的 Innodb 的存储引擎，那么大

家可以试想一下，如果存放帖子的表中已经有上千万的帖子的时候，执行这条 Query 语句

需要多少成本？恐怕再好的硬件设备，恐怕都不可能在 10 秒之内完成一次查询吧

注：没有 where 的 count(*)使用 MyISAM 要比 InnoDB 快得多。因为 MyISAM 内置了一个计

数器，count(*)时它直接从计数器中读，而 InnoDB 必须扫描全表。所以在 InnoDB 上执行

count(*)时一般要伴随 where，且 where 中要包含主键以外的索引列。

既然这样查询不行，那我们是不是该专门为这个功能建一个表，就只有一个字段，一条记录，

就存放这个统计量，每次有新的帖子产生的时候，都将这个值增加 1，这样我们每次都只需

要查询这个表就可以得到结果了，这个效率肯定能够满足要求了。确实，查询效率肯定能够

满足要求，可是如果帖子产生很快，在高峰时期可能每秒就有几十甚至上百个帖子新增操作

的时候，恐怕这个统计表又要成为大家的噩梦了。要么因为并发的问题造成统计结果的不准

确，要么因为锁资源争用严重造成整体性能的大幅度下降。

其实这里问题的焦点不应该是实现这个功能的技术细节，而是在于这个功能的附加要求“实

时更新”上面。当一个论坛的帖子数量很大了之后，到底有多少人会关注这个统计数据是否

是实时变化的？有多少人在乎这个数据在短时间内的不精确性？恐怕不会有人会盯着这个

统计数字并追究当自己发了一个帖子然后回头刷新页面发现这个统计数字没有加 1 吧？所

以只要去掉了这个“实时更新”的附加条件，就可以非常容易的实现这个功能了。就像之前

所提到的那样，通过创建一个统计表，然后通过一个定时任务每隔一定时间段去更新一次里

面的统计值，这样既可以解决统计值查询的效率问题，又可以保证不影响新发贴的效率，一

举两得。

2.系统架构及实现的影响

所有数据都是适合在数据库中存放的吗？数据库为我们提供了太多的功能，反而让很多并不

是太了解数据库的人错误的使用了数据库的很多并不是太擅长或者对性能影响很大的功能，

最后却全部怪罪到数据库身上。

实际上，以下几类数据都是不适合在数据库中存放的：

1）二进制多媒体数据

这种数据主要包括图片，音频、视频和其他一些相关的二进制文件。将二进制多媒体数据存

放在数据库中，一个问题是数据库空间资源耗用非常严重，另一个问题是这些数据的存储很

消耗数据库主机的 CPU 资源。这些数据的处理本不是数据库的优势，如果我们硬要将他们

塞入数据库，肯定会造成数据库的处理资源消耗严重。

2）超大文本数据

对于 5.0.3 之前的 MySQL 版本，VARCHAR 类型的数据最长只能存放 255 个字节，如果需

要存储更长的文本数据到一个字段，我们就必须使用 TEXT 类型（最大可存放 64KB）的字

段，甚至是更大的 LONGTEXT 类型（最大 4GB）。而 TEXT 类型数据的处理性能要远比 VARCHAR

类型数据的处理性能低下很多。从 5.0.3 版本开始，VARCHAR 类型的最大长度被调整到 64KB

了，所以，超大文本数据存放在数据库中不仅会带来性能低下的问题，还会带来空间占用的

浪费问题。

是否合理的利用了应用层 Cache 机制？

对于 Web 应用，活跃数据的数据量总是不会特别的大，有些活跃数据更是很少变化。对于

未经允许不得转载传播--陈英宏

博客地址：hongge.blog.51cto.com

这类数据，我们是否有必要每次需要的时候都到数据库中去查询呢？如果我们能够将变化相

对较少的部分活跃数据通过应用层的 Cache 机制 Cache 到内存中，对性能的提升肯定是成

数量级的，而且由于是活跃数据，对系统整体的性能影响也会很大。

3.查询语句对性能的影响

SQL 语句的优劣是对性能有影响的，每个 SQL 语句在优化之前和优化之后的性能差异也是

各不相同。

在数据库管理软件中，最大的性能瓶颈就是在于磁盘 IO，也就是数据的存取操作上面。而

对于同一份数据，当我们以不同方式去寻找其中的某一点内容的时候，所需要读取的数据量

可能会有天壤之别，所消耗的资源也自然是区别很大。

功能完全相同的两条 SQL 的在性能方面的差异。

我们在执行 sql 语句时可以用 explain 来查看执行计划：

mysql> explain

-> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000

-> order by stuid desc

-> limit 20 \G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: NULL

rows: 3678

filtered: 100.00

Extra: Using where

1 row in set, 1 warning (0.00 sec)

还可以打开 mysql 的 profiling 功能，来查看 sql 的实际执行计划

mysql> set profiling=1;

mysql> select stuid,stuname,cardid from tb1 where stuid between 3000 and 5000 order by stuid

desc limit 5\G

mysql>show profile;

通过执行“SHOW PROFILE” 命令获取当前系统中保存的多个 Query 的 profile 的概要信息。;

mysql> show profile CPU,BLOCK IO for query 1;

4.数据库 Schema 设计对性能的影响

5.硬件选择对性能的影响

首先，数据库主机是存取数据的地方，所以数据库主机的 IO 性能肯定是需要最优先考虑的

一个因素，这一点不管是什么类型的数据库应用都是适用的。在主机中决定 IO 性能部件主

要由磁盘和内存所决定，当然也包括各种与 IO 相关的板卡。

其次，由于数据库主机和普通的应用程序服务器相比，资源要相对集中很多，单台主机上所

需要进行的计算量自然也就比较多，所以数据库主机的 CPU 处理能力也不能忽视。

最后，由于数据库负责数据的存储，与各应用程序的交互中传递的数据量比其他各类服务器

都要多，所以数据库主机的网络设备的性能也可能会成为系统的瓶颈。

所以，数据库应用系统的优化，实际上是一个需要多方面配合，多方面优化的才能产生根本

性改善的事情。简单来说，可以通过下面三句话来简单的概括数据库应用系统的性能优化：

商业需求合理化，系统架构最优化，逻辑实现精简化，硬件设施理性化。

二、MySQL 性能优化之-索引

关于 MySQL索引的好处，如果正确合理设计并且使用索引的 MySQL 是一辆兰博基尼的话，

那么没有设计和使用索引的 MySQL 就是一个人力三轮车。对于没有索引的表，单表查询可

能几十万数据就是瓶颈，而通常大型网站单日就可能会产生几十万甚至几百万的数据，没有

索引查询会变的非常缓慢。

做一个简单测试，假如我们创建了一个 tb1 表，向表中插入 20000 行数据，表的创建和数据

插入用如下脚本实现

[root@localhost ~]# cat mysql3.sh

#!/bin/bash

HOSTNAME="localhost"

PORT="3306"

USERNAME="root"

PASSWORD="123.abc"

DBNAME="test1"

TABLENAME="tb1"

#create database

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "drop database if exists

${DBNAME}"

create_db_sql="create database if not exists ${DBNAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} -e "${create_db_sql}"

#create table

create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname

varchar(20) not null,stusex char(1) not null,cardid varchar(20) not null,birthday

datetime,entertime datetime,address varchar(100) default null)"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e

"${create_table_sql}"

#insert data to table

i=1

while [ $i -le 20000 ]

insert_sql="insert into ${TABLENAME} values

($i,'zhangsan','1','1234567890123456','1999-10-10','2016-9-3','zhongguo beijingshi

changpinqu')"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e

"${insert_sql}"

let i++

done

#select data

select_sql="select count(*) from ${TABLENAME}"

mysql -h ${HOSTNAME} -P ${PORT} -u ${USERNAME} -p${PASSWORD} ${DBNAME} -e

"${select_sql}"

再手动插入一行数据，如

mysql> insert into tb1 values

(20001,'admin','0','123456789009877','2000-1-1','2016-9-1','wwwwwwwwwwwww');

下面开始测试，查询 stuname=’admin’的记录

情况 1：stuname 列上没有创建索引的情况

情况 2：stuname 列上创建索引的情况再查询

在查找 stuname="admin"的记录时，如果在 stuname 上已经建立了索引，MySQL 无须任何

扫描全表，即准确可找到该记录。相反，MySQL 会扫描所有记录。

所以在数据库表中，对字段建立索引可以大大提高查询速度。

索引是在存储引擎中实现的，而不是在服务器层中实现的。所以，每种存储引擎的索引都不一定完全相同，

并不是所有的存储引擎都支持所有的索引类型。

索引概述：

什么是索引？

索引（Index）是帮助 MySQL 高效获取数据的数据结构，这是一种比较官方的说法。它的存

在形式是文件。索引能够帮助我们快速定位数据。更通俗的说，数据库索引好比是一本书

前面的目录，能加快数据库的查询速度。

索引的数据结构

这里主介绍 B-tree 索引的结构

如上图，是一颗 b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到

每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块 1 包含数据项 17

和 35，包含指针 P1、P2、P3，P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，

P3 表示大于 35 的磁盘块。真实的数据存在于叶子节点即 3、5、9、10、13、15、28、29、

36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据

项和指针，如 17、35 并不真实存在于数据表中。

b+树的查找过程

如图所示，如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一

次 IO，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存时间

因为非常短（相比磁盘的 IO）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块

3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过

指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分查找找到 29，结束查询，总

计三次 IO。真实的情况是，3 层的 b+树可以表示上百万的数据，如果上百万的数据查找只

需要三次 IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次 IO，那么总

共需要百万次的 IO，显然成本非常非常高。

为什么使用索引？

索引可以让 mysql 高效运行，可以大大提高 mysql 的查询（包括排序，分组）效率；数据约

束（唯一索引的建立）。

索引给我带来什么好处？

提高查询效率，快速定位数据

使用索引产生的代价？

1、索引本身是以文件的形式存放在硬盘，需要的时候才加载至内存，所以添加索引会增加

磁盘的开销；

2、写数据：需要更新索引，对数据库是个很大的开销，降低表更新、添加和删除的速度

不建议使用索引的情况：

a) 表记录较少

b) 索引的选择性较低，所谓索引的选择性，是指不重复的索引值与表记录数的比值，取值

范围（0-1）。选择性越高，索引的价值越大。

索引的类型？

索引包括单列索引和组合索引

单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组

合索引，即一个索包含多个列。

1、普通索引

这是最基本的索引，它没有任何限制

CREATE INDEX indexName ON tablename(column1[,column2,……])

2、唯一索引

它与前面的普通索引类似，不同的就是索引列的值必须唯一，但允许空值，空值是指 null。

如果是组合索引，组合列的值必须唯一

CREATE UNIQUE INDEX indexName ON tablename(column1[,column2,……])

主键索引：一种特殊的唯一索引，不允许有空值，一般在建表的时候同时建立主键索引

CREATE TABLE tablename(ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY

KEY(ID) );

3、组合索引

为了进一步提升 MySQL 的效率，就要考虑建立组合索引

例如：创建一个表，包含如下字段

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50)

NOT NULL, age INT NOT NULL );

将 username, city, age 建到一个索引里

CREATE INDEX username_city_age ON mytable(username,city,age);

如果分别在 usernname，city，age 上建立单列索引，让该表有 3 个单列索引，查询时和上

述的组合索引效率也会大不一样，远远低于组合索引。虽然此时有了三个索引，但 MySQL

只能用到其中那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

usernname,city,age usernname,city usernname 为什么没有 city，age 这样的组合索引呢？

这是因为 MySQL 组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并

不是只要包含这三列的查询都会用到该组合索引，下面的几个 SQL 就会用到这个组合索引：

SELECT * FROM mytable WHREE username="admin" AND city="郑州"

SELECT * FROM mytable WHREE username="admin"

而下面几个则不会用到：

SELECT * FROM mytable WHREE age=20 AND city="郑州"

SELECT * FROM mytable WHREE city="郑州"

如果对多列进行索引(组合索引)，列的顺序非常重要，MySQL 仅能对索引最左边的前缀进行

有效的查找。

例如：

假设存在组合索引 index1(c1,c2)，查询语句 select * from t1 where c1=1 and c2=2 能够使用该

索引。查询语句 select * from t1 where c1=1 也能够使用该索引。但是，查询语句 select * from

t1 where c2=2 不能够使用该索引，因为没有组合索引的引导列，即要想使用 c2 列进行查找，

必需出现 c1 等于某值。因此我们在创建组合索引时应该将最常用作限制条件的列放在最左

边，依次递减。

4、全文索引

只用于 MyISAM 表对文本域进行索引。字段类型包括 char、varchar、text

不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。

CREATE FULLTEXT INDEX indexname ON tablename(column)

查看索引

mysql> show index from tablename;

mysql> show keys from tablename;

建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？

一般来说，在 WHERE 和 JOIN 子句中出现的列需要建立索引，例如：

代码如下:在 username 上创建索引

SELECT * FROM mytable WHREE username="admin" AND city="郑州"

代码如下:

SELECT t.Name FROM mytable1 t LEFT JOIN mytable2 m ON t.username=m.username;

此时就需要对两个表的 userame 上建立索引。

使用索引的注意事项

刚才提到只有某些时候的 LIKE 才需建立索引。因为在以通配符%和_开头作查询时，MySQL

不会使用索引。例如下句会使用索引：

SELECT * FROM mytable WHERE username like'admin%'

而下句就不会使用：

SELECT * FROM mytable WHEREt Name like'%admin'

不要在列上进行运算,例如

select * from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select * from users where adddate<‘2007-01-01';

总结：

选择索引列：

a.性能优化过程中，选择在哪个列上创建索引是最重要的步骤之一。可以考虑使用索引

的主要有两种类型的列：在 where 子句中出现的列，在 join 子句中出现的列。

b.考虑列中值的分布，索引的列的基数越大，索引的效果越好。

c.使用短索引，如果对字符串列进行索引，应该指定一个前缀长度，可节省大量索引空间，

提升查询速度。

未经允许不得转载传播--陈英宏

博客地址：hongge.blog.51cto.com

例如：CREATE INDEX username_city_age ON mytable(username（10）,city,age);

建表时，usernname 长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过 10，

这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。

d.利用最左前缀

e.不要过度索引，只保持所需的索引。每个额外的索引都要占用额外的磁盘空间，并降低写

操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越

多，所花的时间越长。

MySQL 只对一下操作符才使用索引：<,<=,=,>,>=,between,in,

以及某些时候的 like(不以通配符%或_开头的情形)。

以下就是有关 Mysql 索引的相关理论介绍，下面我们来学习如何慢查询分析、优化索引和配

置

三、mysql 性能优化-慢查询分析、优化索引和配置

基本思路：

1）性能瓶颈定位

Show 命令

慢查询日志

explain 分析查询

profiling 分析查询

2）索引及查询优化

3）配置优化

MySQL 数据库是常见的两个瓶颈是 CPU 和 I/O 的瓶颈，CPU 在饱和的时候一般发生在数据装

入内存或从磁盘上读取数据时候。磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候，如

果应用分布在网络上，那么查询量相当大的时候那么平瓶颈就会出现在网络上，我们可以用

mpstat, iostat, sar 和 vmstat 来查看系统的性能状态。

除了服务器硬件的性能瓶颈，对于 MySQL 系统本身，我们可以使用工具来优化数据库的性

能，通常有三种：使用索引，使用 EXPLAIN 分析查询以及调整 MySQL 的内部配置。

1 、查询与索引优化分析

在优化 MySQL 时，通常需要对数据库进行分析，常见的分析手段有慢查询日志，EXPLAIN 分

析查询，profiling 分析以及 show 命令查询系统状态及系统变量，通过定位分析性能的瓶颈，才

能更好的优化数据库系统的性能。

性能瓶颈定位

show 命令

可以通过 show 命令查看 MySQL 状态及变量，找到系统的瓶颈：

查看 MySQL 服务器配置信息 mysql> show variables;

查看 MySQL 服务器运行的各种状态值 mysql> show global status;

# mysqladmin variables -u username -ppassword——显示系统变量

# mysqladmin extended-status -u username -ppassword——显示状态信息

比较全的 show 命令的使用可参考：mysql>help show

或 http://dev.mysql.com/doc/refman/5.7/en/show.html

慢查询日志

慢查询日志开启：

在配置文件 my.cnf 中在 [mysqld] 一行下面加入 3 个配置参数，并重启 mysql 服务

slow_query_log = 1 //0 关闭 1 开启

slow_query_log_file = /usr/local/mysql/data/slow-query.log //慢查询日志存放地点

未经允许不得转载传播--陈英宏

博客地址：hongge.blog.51cto.com

long_query_time = 1 //表示查询超过 1 秒才记录

在 my.cnf 中添加 log-queries-not-using-indexes 参数，表示向慢查询日志中记录下没有使用索

引的查询。

慢查询日志开启方法二：

我们也可以通过命令行设置变量来即时启动慢日志查询

mysql> set global slow_query_log = on;

mysql> set long_query_time = 0.01;

mysql> set global slow_query_log_file = "/usr/local/mysql/data/slow-query.log";

查看慢查询的设置信息

mysql> show variables like '%slow_query_log%';

mysql> show variables like '%long_query_time%';

我们可以通过打开 log 文件查看得知哪些 SQL 执行效率低下

[root@localhost data]# cat slow-query.log

# Time: 2016-09-06T14:17:12.582189Z

# User@Host: root[root] @ localhost [] Id: 3

# Query_time: 0.008316 Lock_time: 0.000304 Rows_sent: 1 Rows_examined: 20002

SET timestamp=1473171432;

select * from test1.tb1 where stusex='0'; //没有使用索引的 query

# Time: 2016-09-06T15:54:42.648291Z

# User@Host: root[root] @ localhost [] Id: 14

# Query_time: 0.017319 Lock_time: 0.000612 Rows_sent: 1 Rows_examined: 20001

SET timestamp=1473177282;

select * from test1.tb1 where entertime <> '2016-9-3'; //慢查询 query

从日志中，可以发现查询时间超过 0.01 秒的 SQL，而小于 0.01 秒的没有出现在此日志中。

如果慢查询日志中记录内容很多，可以使用 mysqldumpslow 工具（MySQL 客户端安装自带）

来对慢查询日志进行分类汇总。mysqldumpslow 对日志文件进行了分类汇总，显示汇总后摘

要结果。

有关 mysqldumpslow 命令的用法可以参考其帮助：#mysqldumpslow --help

[root@localhost data]# mysqldumpslow -a -s at -r slow-query.log

Reading mysql slow query log from slow-query.log

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), 0users@0hosts

Time: 2016-09-06T15:54:42.648291Z

# User@Host: root[root] @ localhost [] Id: 14

# Query_time: 0.017319 Lock_time: 0.000612 Rows_sent: 1 Rows_examined: 20001

SET timestamp=1473177282;

select * from test1.tb1 where entertime <> '2016-9-3'

上面显示结果中就是一条慢查询，如何优化呢？

一是在 entertime 列上创建索引优化查询

mysql> create index index_entertime on test1.tb1(entertime);

二是优化这个 sql 查询语句

mysql> select * from test1.tb1 where entertime < '2016-9-3' or entertime > '2016-9-3';

从下图可以看查询 0.00 秒

使用 mysqldumpslow 命令可以非常明确的得到各种我们需要的查询语句，对 MySQL 查询语

句的监控、分析、优化是 MySQL 优化非常重要的一步。开启慢查询日志后，由于日志记录

操作，在一定程度上会占用 CPU 资源影响 mysql 的性能，但是可以阶段性开启来定位性能

瓶颈。

explain 分析查询

使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句，从而知道 MySQL 是如何处理你的

SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过 explain 命令可以得到:

mysql> explain select * from test1.tb1 where stuname='admin'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: ALL // 全表扫描

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 19986

filtered: 10.00

Extra: Using where //使用 where 过滤数据

1 row in set, 1 warning (0.00 sec)

EXPLAIN 字段：

Table：显示这一行的数据是关于哪张表的

type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为 system、

const、eq_reg、ref、range、index 和 ALL

possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。

key：实际使用的索引。如果为 NULL，则没有使用索引。

key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好

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

rows：MySQL 认为必须检索的用来返回请求数据的行数

Extra：关于 MYSQL 如何解析查询的额外信息

从上面的 explain 模拟优化器执行 sql 语句来看是没有使用索引查询的，而是全表扫描

优化方法：在 stuname 列上创建索引

mysql> create index index_stuname on test1.tb1(stuname);

再次执行 explain

mysql> explain select * from test1.tb1 where stuname='admin'\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: tb1

partitions: NULL

type: ref

possible_keys: index_stuname

key: index_stuname

key_len: 62

ref: const

rows: 1

filtered: 100.00

Extra: NULL

1 row in set, 1 warning (0.00 sec)

显示结果说明该查询语句使用了 index_stuname 索引查询数据而非全表扫描。

profiling 分析查询

通过慢日志查询可以知道哪些 SQL 语句执行效率低下，通过 explain 我们可以得知 SQL 语句的

具体执行情况，索引使用等，还可以结合 show 命令查看执行状态。如果觉得 explain 的信息不

够详细，可以同通过 profiling 命令得到更准确的 SQL 执行消耗系统资源的信息。

profiling 默认是关闭的。可以通过以下语句查看

mysql> show variables like '%profiling%'; //off 表示未开启

或

mysql> select @@profiling; //0 表示未开启

打开 profiling 功能： mysql>set profiling=1; 执行需要测试的 sql 语句：

mysql> select @@profiling;

+---------------------+

| @@profiling |

+---------------------+

| 1 |

+----------------------+

执行要测试的 sql 语句

mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';

mysql> show profiles\G; //可以得到被执行的 SQL 语句的时间和 ID

*************************** 1. row ***************************

Query_ID: 1

Duration: 0.00012650

Query: select @@profiling

*************************** 2. row ***************************

Query_ID: 2

Duration: 0.00121725

Query: select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

mysql> show profile for query 2; //得到对应 SQL 语句执行的详细信息

+----------------------+-------------------------+

| Status | Duration |

+----------------------+-------------------------+

| starting | 0.000230 |

| checking permissions | 0.000013 |

| Opening tables | 0.000030 |

| init | 0.000087 |

| System lock | 0.000018 |

| optimizing | 0.000128 |

| statistics | 0.000378 |

| preparing | 0.000026 |

| executing | 0.000005 |

| Sending data | 0.000187 |

| end | 0.000013 |

| query end | 0.000011 |

| closing tables | 0.000010 |

| freeing items | 0.000061 |

| cleaning up | 0.000021 |

+----------------------+-------------------------+

status:是 profile 里的状态，duration：是 status 状态下的耗时。因此我们关注的就是那个状

态最耗时，这些状态中那些可以优化。

当然也可以查看更多的信息如 CPU 等等

SHOW PROFILE [type [, type] ... ] [FOR QUERY n]

type:

ALL:显示所有的开销信息

BLOCK IO:显示块 IO 相关开销

CPU:显示用户 CPU 时间、系统 CPU 时间

IPC:显示发送和接收相关开销信息

PAGE FAULTS:显示页面错误相关开销信息

SWAPS:显示交换次数相关开销的信息

测试完成之以后，记得要关闭调试功能，以免影响数据库的正常使用：

mysql> set profiling=0;

2、配置优化

Mysql 参数优化对于不同的网站，及其在线量，访问量，帖子数量，网络情况，以及机器硬

件配置都有关系，优化不可能一次性完成，需要不断的观察以及调试，才有可能得到最佳效

果。

下面列出了对性能优化影响较大的主要变量，主要分为连接请求的变量和缓冲区变量

1）连接请求的变量：

1. max_connections

MySQL 的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行

连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多， MySQL 会为每个连

接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。

数值过小会经常出现 ERROR 1040: Too many connections 错误，可以过 mysql> show status like

'connections';通配符查看当前状态的连接数量(试图连接到MySQL(不管是否连接成功)的连接

数)，以定夺该值的大小。

mysql>show variables like ‘max_connections’ 最大连接数

mysql>show status like ‘max_used_connections’ 响应的连接数

max_used_connections / max_connections * 100% （理想值≈ 85%）

如果 max_used_connections 跟 max_connections 相同那么就是 max_connections 设置过低或

者超过服务器负载上限了，低于 10%则设置过大。

如何设置 max_connections?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容，如设置最大连接数为 1024

max_connections = 1024

重启 mysql 服务

2.back_log

MySQL 能暂存的连接数量。当主要 MySQL 线程在一个很短时间内得到非常多的连接请求，

它就会起作用。如果 MySQL 的连接数据达到 max_connections 时，新来的请求将会被存在堆

栈中，以等待某一连接释放资源，该堆栈的数量即 back_log，如果等待连接的数量超过

back_log，将不被授予连接资源。

back_log 值指出在 MySQL 暂时停止回答新请求之前的短时间内有多少个请求可以被存在堆

栈中。只有如果期望在一个短时间内有很多连接，你需要增加它。

当观察你主机进程列表（mysql> show full processlist），发现大量

进程时，就要加大 back_log 的值了或加大 max_connections 的值。

通过 mysql> show variables like 'back_log';查看 back_log 的设置

如何设置 back_log?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容，如设置最大连接数为 1024

back_log = 数值

重启 mysql 服务

3. wait_timeout 和 interactive_timeout

wait_timeout -- 指的是 MySQL在关闭一个非交互的连接之前所要等待的秒数

interactive_time -- 指的是 mysql 在关闭一个交互的连接之前所要等待的秒数，比如我们在终

端上进入 mysql 管理，使用的即使交互的连接，这时候，如果没有操作的时间超过了

interactive_time 设置的时间就会自动断开。默认数值是 28800，可调优为 7200。

对性能的影响：

wait_timeout：

（1）如果设置大小，那么连接关闭的很快，从而使一些持久的连接不起作用

（2）如果设置太大，容易造成连接打开时间过长，在 show processlist 时，能看到太多的 sleep

状态的连接，从而造成 too many connections 错误

（3）一般希望 wait_timeout 尽可能地低

interactive_timeout 的设置将要对你的 web application 没有多大的影响

查看 wait_timeout 和 interactive_timeout

mysql> show variables like '%wait_tmeout%';

mysql> show variables like '%interactive_timeout%';

如何设置 wait_timeout 和 interactive_timeout ?

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

wait_timeout=100

interactive_timeout=100

重启 MySQL Server 进入后，查看设置已经生效。

2）绶冲区变量

全局缓冲：

4.key_buffer_size

key_buffer_size 指定索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。通

过检查状态值 Key_read_requests 和 Key_reads，可以知道 key_buffer_size 设置是否合理。比

例 key_reads / key_read_requests 应该尽可能的低，至少是 1:100，1:1000 更好（上述状态值

可以使用 SHOW STATUS LIKE ‘key_read%’获得）。

一共有 6 个索引读取请求，有 3 个请求在内存中没有找到直接从硬盘读取索引，计算索引

未命中缓存的概率：

key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50%

key_buffer_size 只对 MyISAM 表起作用。即使你不使用 MyISAM 表，但是内部的临时磁盘表

是 MyISAM 表，也要使用该值。可以使用检查状态值 created_tmp_disk_tables 得知详情。

如何调整 key_buffer_size

默认配置数值是 8388608(8M)，主机有 4GB 内存，可以调优值为 268435456(256MB)

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

key_buffer_size=268435456 或 key_buffer_size=256M

重启 MySQL Server 进入后，查看设置已经生效。

5. query_cache_size(查询缓存简称 QC)

使用查询缓冲，MySQL 将查询结果存放在缓冲区中，今后对于同样的 SELECT 语句（区分大小

写），将直接从缓冲区中读取结果。

一个 SQL 查询如果以 select 开头，那么 MySQL 服务器将尝试对其使用查询缓存。

注：两个 SQL 语句，只要相差哪怕是一个字符（例如大小写不一样；多一个空格等），那么这两

个 SQL 将使用不同的一个 CACHE。

通过检查状态值’Qcache%’，可以知道 query_cache_size 设置是否合理（上述状态值可以使用

SHOW STATUS LIKE ‘Qcache%’获得）。

Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示较大，则说明 Query Cache 中

的内存碎片较多了，FLUSH QUERY CACHE 会对缓存中的碎片进行整理，从而得到一个

空闲块。

注：当一个表被更新之后，和它相关的 cache blocks 将被 free。但是这个 block 依然可能

存在队列中，除非是在队列的尾部。可以用 FLUSH QUERY CACHE 语句来清空 free blocks

Qcache_free_memory：Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为

准确的观察出当前系统中的 Query Cache 内存大小是否足够，是需要增加还是过多了。

Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效

果。数字越大，缓存效果越理想。

Qcache_inserts：表示多少次未命中然后插入，意思是新来的 SQL 请求在缓存中未找到，不

得不执行查询处理，执行查询处理后把结果 insert 到查询缓存中。这样的情况的次数越多，

表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这

很正常。

Qcache_lowmem_prunes：多少条 Query 因为内存不足而被清除出 Query Cache。通过

“Qcache_lowmem_prunes”和“Qcache_free_memory”相互结合，能够更清楚的了解到我们系

统中 Query Cache 的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有 Query

被换出。这个数字最好长时间来看；如果这个数字在不断增长，就表示可能碎片非常严重，

或者内存很少。（上面的 free_blocks 和 free_memory 可以告诉您属于哪种情况）

Qcache_not_cached：不适合进行缓存的查询的数量，通常是由于这些查询不是 SELECT

语句或者用了 now()之类的函数。

Qcache_queries_in_cache：当前 Query Cache 中 cache 的 Query 数量；

Qcache_total_blocks：当前 Query Cache 中的 block 数量；。

我们再查询一下服务器关于 query_cache 的配置：

上图可以看出 query_cache_type 为 off 表示不缓存任何查询

各字段的解释：

query_cache_limit：超过此大小的查询将不缓存

query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit 的配置是一柄”

双刃剑”，默认是 4KB，设置值大对大数据查询有好处，但如果你的查询都是小数据查询，

就容易造成内存碎片和浪费。

query_cache_size：查询缓存大小 (注：QC 存储的最小单位是 1024 byte，所以如果你设定了

一个不是 1024 的倍数的值，这个值会被四舍五入到最接近当前值的等于 1024 的倍数的值。)

query_cache_type：缓存类型，决定缓存什么样的查询，注意这个值不能随便设置，必须设

置为数字，可选项目以及说明如下：

选项	含义
0	（OFF，不缓存或重新得到结果）
1	（ON，缓存所有结果，除了SELECT、SQL_NO_CACHE...查询）
3	（DENAND，仅缓存SELECT、SQL_CACHE...查询）

如果设置为 0，那么可以说，你的缓存根本就没有用，相当于禁用了。

如果设置为 1，将会缓存所有的结果，除非你的 select 语句使用 SQL_NO_CACHE 禁用了查询

缓存。

如果设置为 2，则只缓存在 select 语句中通过 SQL_CACHE 指定需要缓存的查询。

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M

query_cache_type=1

保存文件，重新启动 MYSQL 服务，然后通过如下查询来验证是否真正开启了：

query_cache_wlock_invalidate：当有其他客户端正在对 MyISAM 表进行写操作时，如果查询

在 query cache 中，是否返回 cache 结果还是等写操作完成再读表获取结果。

查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

如果查询缓存碎片率超过 20%，可以用 FLUSH QUERY CACHE 整理缓存碎片，或者试试减小

query_cache_min_res_unit，如果你的查询都是小数据量的话。

查询缓存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

查询缓存利用率在 25%以下的话说明 query_cache_size 设置的过大，可适当减小；查询缓存

利用率在80％以上而且Qcache_lowmem_prunes > 50的话说明 query_cache_size可能有点小，

要不就是碎片太多。

查询缓存命中率 = Qcache_hits/(Qcache_hits +Qcache_inserts) * 100%

Query Cache 的限制

a) 所有子查询中的外部查询 SQL 不能被 Cache；

b) 在 Procedure，Function 以及 Trigger 中的 Query 不能被 Cache；

c) 包含其他很多每次执行可能得到不一样结果的函数的 Query 不能被 Cache。

鉴于上面的这些限制，在使用 Query Cache 的过程中，建议通过精确设置的方式来使用，仅

仅让合适的表的数据可以进入 Query Cache，仅仅让某些 Query 的查询结果被 Cache。

如何设置 query_cache_size？

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

query_cache_size=256M

query_cache_type=1

重启 MySQL Server 进入后，查看设置已经生效。

6. max_connect_errors 是一个 MySQL 中与安全有关的计数器值，它负责阻止过多尝试失败

的客户端以防止暴力破解密码的情况, 当超过指定次数，MYSQL 服务器将禁止 host 的连接

请求，直到 mysql 服务器重启或通过 flush hosts 命令清空此 host 的相关信息。

max_connect_errors 的值与性能并无太大关系。

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

max_connect_errors=20

重启 MySQL Server 进入后，查看设置已经生效。

7. sort_buffer_size

每个需要进行排序的线程分配该大小的一个缓冲区。增加这值加速 ORDER BY 或 GROUP BY

操作。

Sort_Buffer_Size 是一个 connection 级参数，在每个 connection（session）第一次需要使用这

个 buffer 的时候，一次性分配设置的内存。

Sort_Buffer_Size 并不是越大越好，由于是 connection 级的参数，过大的设置+高并发可能会

耗尽系统内存资源。例如：500 个连接将会消耗 500*sort_buffer_size(2M)=1G 内存

例如设置 sort_buffer_size

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

sort_buffer_size = 2M

重启 MySQL Server 进入后，查看设置已经生效。

8. max_allowed_packet = 32M

MySQL 根据配置文件会限制 Server 接受的数据包大小。有时候大的插入和更新会受

max_allowed_packet 参数限制，导致写入或者更新失败。最大值是 1GB，必须设置 1024 的

倍数。

9．join_buffer_size = 2M

用于表间关联缓存的大小，和 sort_buffer_size 一样，该参数对应的分配内存也是每个连接

独享。

10. thread_cache_size = 300

服务器线程缓存，这个值表示可以重新利用保存在缓存中线程的数量,当断开连接时,那么客

户端的线程将被放到缓存中以响应下一个客户而不是销毁(前提是缓存数未达上限),如果线

程重新被请求，那么请求将从缓存中读取,如果缓存中是空的或者是新的请求，那么这个线

程将被重新创建,如果有很多新的线程，增加这个值可以改善系统性能.通过比

较 Connections 和 Threads_created 状态的变量，可以看到这个变量的作用。设置规则如下：

1GB 内存配置为 8，2GB 配置为 16，3GB 配置为 32，4GB 或更高内存，可配置更大。服务

器处理此客户的线程将会缓存起来以响应下一个客户而不是销毁(前提是缓存数未达上限)

试图连接到 MySQL(不管是否连接成功)的连接数

Threads_cached :代表当前此时此刻线程缓存中有多少空闲线程。

Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可

以看成当前被使用的线程数。

Threads_created :代表从最近一次服务启动，已创建线程的数量，如果发现 Threads_created

值过大的话，表明 MySQL 服务器一直在创建线程，这也是比较耗资源，可以适当增加配置

文件中 thread_cache_size 值。

Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，

有时候连接已建立，但是连接处于 sleep 状态。

3）配置 InnoDB 的几个变量

11. innodb_buffer_pool_size

对于InnoDB 表来说，innodb_buffer_pool_size 的作用就相当于 key_buffer_size 对于 MyISAM

表的作用一样。InnoDB 使用该参数指定大小的内存来缓冲数据和索引。对于单独的 MySQL

数据库服务器，最大可以把该值设置成物理内存的 80%。根据 MySQL 手册，对于 2G 内存

的机器，推荐值是 1G（50%）。如果你的数据量不大，并且不会暴增，那么无需

把 innodb_buffer_pool_size 设置的太大了。

mysql> show variables like 'innodb_buffer_pool_size';

设置 innodb_buffer_pool_size

修改/etc/my.cnf 文件，在[mysqld]下面添加如下内容

innodb_buffer_pool_size = 2048M

重启 MySQL Server 进入后，查看设置已经生效。

12. innodb_flush_log_at_trx_commit

主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、

1、2 三个。0，表示当事务提交时，不做日志写入操作，而是每秒钟将 log buffer 中的数据

写入日志文件并 flush 磁盘一次；1，则在每秒钟或是每次事物的提交都会引起日志文件写入、

flush 磁盘的操作，确保了事务的 ACID；设置为 2，每次事务提交引起写入日志文件的动作，

但每秒钟完成一次 flush 磁盘操作。

实际测试发现，该值对插入数据的速度影响非常大，设置为 2 时插入 10000 条记录只需要 2

秒，设置为 0 时只需要 1 秒，而设置为 1 时则需要 229 秒。因此，MySQL 手册也建议尽量

将插入操作合并成一个事务，这样可以大幅提高速度。

根据 MySQL 手册，在允许丢失最近部分事务的危险的前提下，可以把该值设为 0 或 2。

13.innodb_thread_concurrency = 0

此参数用来设置 innodb 线程的并发数量，默认值为 0 表示不限制，若要设置则与服务器的

CPU 核数相同或是 cpu 的核数的 2 倍，建议用默认设置，一般为 8.

14. innodb_log_buffer_size

此参数确定些日志文件所用的内存大小，以 M 为单位。缓冲区更大能提高性能，对于较大

的事务，可以增大缓存大小。

innodb_log_buffer_size=32M

15. innodb_log_file_size = 50M

此参数确定数据日志文件的大小，以 M 为单位，更大的设置可以提高性能.

16. innodb_log_files_in_group = 3

为提高性能，MySQL 可以以循环方式将日志文件写到多个文件。推荐设置为 3

17.read_buffer_size = 1M

MySql 读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql 会为它分

配一段内存缓冲区。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，

可以通过增加该变量值以及内存缓冲区大小提高其性能。和 sort_buffer_size 一样，该参数

对应的分配内存也是每个连接独享。

18.read_rnd_buffer_size = 16M

MySql 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，

将分配一个随机读缓存区。进行排序查询时，MySql 会首先扫描一遍该缓冲，以避免磁盘搜

索，提高查询速度，如果需要排序大量数据，可适当调高该值。但 MySql 会为每个客户连接

发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。

注：顺序读是指根据索引的叶节点数据就能顺序地读取所需要的行数据。随机读是指一般需

要根据辅助索引叶节点中的主键寻找实际行数据，而辅助索引和主键所在的数据段不同，因

此访问方式是随机的。

19.bulk_insert_buffer_size = 64M

批量插入数据缓存大小，可以有效提高插入效率，默认为 8M

20.binary log

log-bin=/usr/local/mysql/data/mysql-bin

binlog_cache_size = 2M //为每个 session 分配的内存，在事务过程中用来存储二进制日志

的缓存, 提高记录 bin-log 的效率。没有什么大事务，dml 也不是很频繁的情况下可以设置小

一点，如果事务大而且多，dml 操作也频繁，则可以适当的调大一点。前者建议是--1M，后

者建议是：即 2--4M

max_binlog_cache_size = 8M //表示的是 binlog 能够使用的最大 cache 内存大小

max_binlog_size = 512M //指定 binlog 日志文件的大小，如果当前的日志大小达到

max_binlog_size，还会自动创建新的二进制日志。你不能将该变量设置为大于 1GB 或小于

4096 字节。默认值是 1GB。在导入大容量的 sql 文件时，建议关闭 sql_log_bin，否则硬盘

扛不住，而且建议定期做删除。

expire_logs_days = 7 //定义了 mysql 清除过期日志的时间。

二进制日志自动删除的天数。默认值为 0,表示“没有自动删除”。

mysqladmin flush-logs 也可以重新开始新的 binary log

在优化之前执行 mysqlslap 工具进行测试

[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1

--create-schema='test1' --query='select * from test1.tb1' --engine=innodb

--number-of-queries=2000 -uroot -p123456 –verbose

显示结果：

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 13.837 seconds

Minimum number of seconds to run all queries: 13.837 seconds

Maximum number of seconds to run all queries: 13.837 seconds

Number of clients running queries: 10

Average number of queries per client: 200

优化之后执行 mysqlslap 工具进行测试

[root@localhost ~]#mysqlslap --defaults-file=/etc/my.cnf --concurrency=10 --iterations=1

--create-schema='test1' --query='select * from test1.tb1' --engine=innodb

--number-of-queries=2000 -uroot -p123456 –verbose

显示结果：

Benchmark

Running for engine innodb

Average number of seconds to run all queries: 4.199 seconds

Minimum number of seconds to run all queries: 4.199 seconds

Maximum number of seconds to run all queries: 4.199 seconds

Number of clients running queries: 10

Average number of queries per client: 200