MYSQL优化浅谈，工具及优化点介绍，mysqldumpslow，pt-query-digest，explain等

MYSQL优化浅谈

msyql是开发常用的关系型数据库，快速、稳定、开源等优点就不说了。 
个人认为，项目上线，标志着一个项目真正的开始。从运维，到反馈，到再分析，再版本迭代，再优化… 这是一个漫长且考验耐心的过程。在这个过程中，作为数据存储的关键–>数据库的优化起到尤为重要的作用。 
语文学的不好，废话也不多说，下面结合实例咱们说说MYSQL需要从哪些方面进行优化。 
在说优化之前先声明一下环境

名称	版本	备注
系统1	WIN 7——x64 SP1	个人电脑
系统2	centos6.4	个人电脑
数据库	5.5.22 MySQL Community Server (GPL)	社区版
内存	8GB
CPU	i7-3520	四线程

优化需要考虑的几个方面 
1. SQL优化 
2. 索引优化 
3. 数据库结构优化 
4. 系统配置优化 
5. 硬件优化

SQL优化

本次使用我自己的库LMS为优化对象

mysql> use lms
Database changed
mysql> show tables;
+------------------------+
| Tables_in_lms          |
+------------------------+
| lm_a_mresource         |
| lm_a_mresourcerole     |
| lm_a_role              |
| lm_a_user              |
| lm_a_userrole          |
| lm_c_dept部门           |
| lm_c_suppliers         |
| lm_c_usecomp           |
| lm_d_applyforpayment   |
| lm_d_arrival           |
| lm_d_billing           |
| lm_d_cgcontract        |
| lm_d_cgcontractcredit  |
| lm_d_dlcontract        |
| lm_d_payed             |
| lm_d_plan需求计划        |
| lm_d_planchangedetails |
| lm_d_procurementsource |
| lm_d_received          |
| lm_d_xscontract        |
+------------------------+
20 rows in set (0.01 sec)

 

开启MYSQL慢查询日志,监控有效率问题的SQL

如何发现有问题的SQL呢？ 
1. 通过设置slow_query_log开启慢查询日志

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set (0.00 sec)

mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.07 sec)

1. 通过设置log_queries_not_using_indexes开启为使用索引的监控

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)

 

1. 通过long_query_time设置监控阀值，也就是超过多少秒就记录，单位是秒，此处设置为0.1也就是100毫秒

mysql> set global long_query_time=0.1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.00 sec)

 

 

通过以上设计，基本上可以开始优化工作了。 
set global 只是全局session生效，重启后失效,如果需要以上配置永久生效，需要在mysql.ini（linux mysql.cnf）中配置

[mysqld]
slow_query_log  = 1
log_queries_not_using_indexes = 1
long_query_time = 0.1
slow_query_log_file = c:\mysql\log\mysqlslowquery.log

 

 

我在配置文件中设置了。所以我重启让配置生效。 
ps:slow_query_log_file这个路径要有效且有权限，否则重启后无法写入log

C:\Users\sjm>net stop mysql
MySQL 服务正在停止..
MySQL 服务已成功停止。
C:\Users\sjm>net start mysql
MySQL 服务正在启动 ...
MySQL 服务已经启动成功。

 

如果是linux，就通过以下命令或service等其他方式重启

启动：/etc/init.d/mysqld(mysql) start
停止：/etc/init.d/mysqld(mysql) stop
重启：/etc/init.d/mysqld(mysql) restart

最后查看慢查询日志所在的位置，通过查看slow_query_log_file

mysql> show variables like 'slow_query_log_file' \G
*************************** 1. row ***************************
Variable_name: slow_query_log_file
        Value: c:\mysql\log\mysqlslowquery.log
1 row in set (0.00 sec)

• 1
• 2
• 3
• 4
• 5

测试

mysql> select sleep(1);
+----------+
| sleep(1) |
+----------+
|        0 |
+----------+
1 row in set (1.00 sec)

通过以上超出0.1秒的测试，slow_query_log_file就会记录该信息，格式如下：

//Time int类型时间戳 加 时间
# Time: 150610 10:24:07
//执行SQL的主机信息
# User@Host: root[root] @ localhost [::1]
//SQL执行信息依次是 执行时间，锁定时间，返回记录数，扫描行数
# Query_time: 1.000057  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
//SQL执行用时
SET timestamp=1433903047;
//SQL内容
select sleep(1);

 

分析工具之一mysqldumpslow查看并分析慢查询日志

以下是windows环境需要安装其他支持软件的步骤，非windows直接忽略 
windows要执行mysqldumpslow需要安装ActivePerl因为windows上这是一个perl脚本,需要安装ActivePerl才能执行，linux下就可以直接执行mysqldumpslow了。 
安装ActivePerl的步骤我就不多说了。安装好后会在环境变量PATH中找到perl的bin目录。我重启了电脑让环境变量生效。 
验证ActivePerl是否安装成功

C:\Users\sjm>perl -v

This is perl 5, version 20, subversion 2 (v5.20.2) built for MSWin32-x86-multi-thread-64int
(with 1 registered patch, see perl -V for more detail)

Copyright 1987-2015, Larry Wall

 

如果打印和我差不多的就安装成功了。 
找到mysqldumpslow的目录。也就是mysql的bin目录（linux用户可直接运行mysqldumpslow） 
windows下验证mysqldumpslow可用运行 
mysqldumpslow常用命令说明：

C:\Program Files\MySQL\MySQL Server 5.5\bin>perl mysqldumpslow.pl -help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose 查看版本
  --debug      debug 调试
  --help       write this text to standard output

  -v           verbose 查看版本
  -d           debug 调试
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default 通过下面的方式排序日志↓↓↓
                al: average lock time 平均锁定时间排序
                ar: average rows sent  平均发送行数排序
                at: average query time 平均查询时间排序
                 c: count 执行次数排序
                 l: lock time 锁表时间排序
                 r: rows sent 总结果行数排序
                 t: query time 总查询时间排序
  -r           reverse the sort order (largest last instead of first) 正序排序，即从小到大排序
  -t NUM       just show the top n queries 查看前X条日志
  -a           don't abstract all numbers to N and strings to 'S' 显示出数字和字符串，默认数字为 N 字符串为 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string 过滤字符串，后接正则表达式，如'10$' 以10为结尾的条件,大小写不敏感
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

windows支持软件安装完成

优化开始… … 
查询lm_d_plan全表条数

mysql> use lms
Database changed
mysql> select count(*) from lm_d_plan;
+----------+
| count(*) |
+----------+
|    57106 |
+----------+
1 row in set (1.94 sec)

 

超过0.1秒 记录到日志中，并查看

命令解析:排序（-s）按执行次数（c）倒序（-a）
C:\Program Files\MySQL\MySQL Server 5.5\bin>perl mysqldumpslow.pl -s c -a c:\mysql\log\mysqlslowquery.log

Reading mysql slow query log from c:\mysql\log\mysqlslowquery.log
执行次数 Count: 3  执行时间Time=0.27s (0s)  锁定时间Lock=1.67s (1s)  发送行数Rows=1.0 (1), 执行地址root[root]@localhost
  内容:select count(*) from lm_d_plan
Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts
  C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.22-log (MySQL Community Server (GPL)). started with:
  TCP Port: 3306, Named Pipe: (null)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

分析工具之二pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句的条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问题进行优化。 
pt-query-digest只有linux版本，所以只能用linux了。同理设置/etc/my.cnf

 [mysqld]
slow_query_log  = 1
log_queries_not_using_indexes = 1
long_query_time = 0.1
#这个目录一定要有权限，最好就设置到mysql目录并且通过设置用户权限授权目录及文件可读写
slow_query_log_file = /var/lib/mysql/log/slow.log

 

顺便帖一下授权命令，目的是让log目录及文件对mysql组的mysql用户可读写，已授权就可以无视。

[root@localhost mysql]# cd /var/lib/mysql
[root@localhost mysql]# mkdir log
[root@localhost mysql]# chown mysql log
[root@localhost mysql]# chgrp mysql log
[root@localhost mysql]# chmod ug+rwx log
[root@localhost mysql]# cd log/
[root@localhost log]# vi slow.log
[root@localhost log]# chown mysql slow.log 
[root@localhost log]# chgrp mysql slow.log 
[root@localhost log]# chmod 777 slow.log 

 

安装pt-query-digest 
地址：https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html#downloading 
选择 wget percona.com/get/percona-toolkit.rpm（也可以选择其他类型安装） 
安装过程会出现：

[root@localhost ~]# rpm -ivh percona-toolkit-2.2.14-1.noarch.rpm 
warning: percona-toolkit-2.2.14-1.noarch.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
error: Failed dependencies:
    perl(DBI) >= 1.13 is needed by percona-toolkit-2.2.14-1.noarch
    perl(DBD::mysql) >= 1.0 is needed by percona-toolkit-2.2.14-1.noarch
    perl(Time::HiRes) is needed by percona-toolkit-2.2.14-1.noarch
    perl(IO::Socket::SSL) is needed by percona-toolkit-2.2.14-1.noarch
    perl(Term::ReadKey) is needed by percona-toolkit-2.2.14-1.noarch

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8

缺少依赖库 
通过命令yum -y install perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes安装 
我的安装过程比较曲折，安装perl-DBD-MySQL的时候需要安装依赖库mysql-libs-5.1.73-3.el6_5.x86_64但是我安装的是mysql5.6.20，所以报错**file** /usr/share/mysql/ukrainian/errmsg.sys **from install of** mysql-libs-5.1.73-3.el6_5.x86_64 **conflicts with file from package** MySQL-server-advanced-5.6.20-1.el6.x86_64，就是说安装的这个mysql-libs依赖库版本与我本身的mysql版本冲突，他的版本低，而且肯定不能删除本地数据库，所以需要安装MySQL-shared-compat-5.6.20-1.linux_glibc2.5.x86_64.rpm （与本地数据库版本匹配才可以）来解决mysql-libs的问题。 
还有通过yum安装的时候最好添加国内的源，我添加是163的，详情如下

来源：http://blog.chinaunix.net/uid-23683795-id-3477603.html
网易（163）yum源是国内最好的yum源之一 ，无论是速度还是软件版本，都非常的不错，将yum源设置为163yum，可以提升软件包安装和更新的速度，同时避免一些常见软件版本无法找到。具体设置方法如下：
1，进入yum源配置目录
cd /etc/yum.repos.d
2，备份系统自带的yum源
mv CentOS-Base.repo CentOS-Base.repo.bk
下载163网易的yum源：
wget http://mirrors.163.com/.help/CentOS6-Base-163.repo
3，更新玩yum源后，执行下边命令更新yum配置，使操作立即生效
yum makecache
4，除了网易之外，国内还有其他不错的yum源，比如中科大和搜狐的，大家可以根据自己需求下载
中科大的yum源：
wget http://centos.ustc.edu.cn/CentOS-Base.repo
sohu的yum源
wget http://mirrors.sohu.com/help/CentOS-Base-sohu.repo
理论上讲，这些yum源redhat系统以及fedora也是可以用 的，但是没有经过测试，需要的站长可以自己测试一下。

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16

这样pt-query-digest安装告一段落 
通过命令pt-query-digest --help可以查看帮助文档，同时说明安装是没问题的 
语法及重要选项 
pt-query-digest [OPTIONS] [FILES] [DSN] 
–create-review-table 当使用–review参数把分析结果输出到表中时，如果没有表就自动创建。 
–create-history-table 当使用–history参数把分析结果输出到表中时，如果没有表就自动创建。 
–filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析 
–limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。 
–host mysql服务器地址 
–user mysql用户名 
–password mysql用户密码 
–history 将分析结果保存到表中，分析结果比较详细，下次再使用–history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。 
–review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用–review时，如果存在相同的语句分析，就不会记录到数据表中。 
–output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。 
–since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。 
–until 截止时间，配合—since可以分析一段时间内的慢查询。

直接分析： 
pt-query-digest /var/lib/mysql/log/slow.log

第一部分：总体统计结果
# 160ms user time, 10ms system time, 22.46M rss, 199.00M vsz
# Current date: Wed Jun 10 18:09:50 2015
# Hostname: localhost.localdomain
# Files: /var/lib/mysql/log/slow.log
# Overall: 1 total(一共多少条sql), 1 unique(查询条件进行参数化以后，总共有多少个不同的查询少), 0 QPS, 0x concurrency ______________________
# Time range(日志的时间范围): all events occurred at 2015-06-10 14:31:21
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time(执行时间)  23ms    23ms    23ms    23ms    23ms       0    23ms
# Lock time(锁定时间)  253us   253us   253us   253us   253us       0   253us
# Rows sent(返回条数)   8       8       8       8       8       0       8
# Rows examine(扫描条数)5.51k   5.51k   5.51k   5.51k   5.51k       0   5.51k
# Query size()  50      50      50      50      50       0      50
第二部分：查询分组统计结,这部分对查询进行参数化并分组，然后对各类查询的执行情况进行分析，结果按总执行时长，从大到小排序
# Profile 
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x9E3ACC745D5A7770 0.0228 100.0%     1 0.0228  0.00 SELECT lm_d_plan
Response 总的响应时间
time 该查询在本次分析中总的时间占比
calls: 执行次数，即本次分析总共有多少条这种类型的查询语句。
R/Call: 平均每次执行的响应时间。
Item : 查询对象

# 第三部分：每一种查询的详细统计结果
# **Query 1**: 0 QPS, 0x concurrency, ID 0x9E3ACC745D5A7770 at byte 0 ________
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2015-06-10 14:31:21
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count        100       1
# Exec time    100    23ms    23ms    23ms    23ms    23ms       0    23ms
# Lock time    100   253us   253us   253us   253us   253us       0   253us
# Rows sent    100       8       8       8       8       8       0       8
# Rows examine 100   5.51k   5.51k   5.51k   5.51k   5.51k       0   5.51k
# Query size   100      50      50      50      50      50       0      50
# String:
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS LIKE 'lm_d_plan'\G
#    SHOW CREATE TABLE `lm_d_plan`\G
# EXPLAIN /*!50100 PARTITIONS*/
select itemsName from lm_d_plan group by FK_deptNo\G

查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
Databases: 库名 //这个示例中没有
Users: 各个用户执行的次数（占比）
Query_time distribution : 查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables: 查询中涉及到的表
Explain: 示例

 

用法示例 
(1)直接分析慢查询文件: 
pt-query-digest [参数] /var/lib/mysql/log/slow.log > slow.report 
(2)分析最近12小时内的查询： 
pt-query-digest --since=12h /var/lib/mysql/log/slow.log > slow_report2.log 
(3)分析指定时间范围内的查询： 
pt-query-digest /var/lib/mysql/log/slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'> > slow_report3.log 
(4)分析指含有select语句的慢查询 
pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' /var/lib/mysql/log/slow.log> slow_report4.log 
(5) 针对某个用户的慢查询 
pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' /var/lib/mysql/log/slow.log> slow_report5.log 
(6) 查询所有所有的全表扫描或full join的慢查询 
pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log
(7)把查询保存到query_review表 
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table /var/lib/mysql/log/slow.log
(8)把查询保存到query_history表 
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history--create-review-table /var/lib/mysql/log/slow.log
pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query_history--create-review-table /var/lib/mysql/log/slow.log
(9)通过tcpdump抓取mysql的tcp协议数据，然后再分析 
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt 
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log 
(10)分析binlog 
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql 
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log 
(11)分析general log 
pt-query-digest --type=genlog localhost.log > slow_report11.log

如何通过慢查询日志找出有问题的SQL

1. 查询次数多且每次查询时间较长的sql 
   通常为pt-query-digest分析的前个查询
2. IO大的SQL（数据库的瓶颈之一是IO） 
   注意pt-query-digest分析结果中 Rows examine项
3. 未命中索引的SQL 
   注意pt-query-digest分析结果中 Rows examine很大 但是Rows Sent很小

通过explain分析SQL

数据库会先进行计划分析，再进行查询。执行计划从侧面反映了SQL的执行效率，那么通过explain查看并分析执行计划是非常有必要的

mysql> explain select * from lm_d_plan group by fk_deptno;
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-------+
| id | select_type | table     | type  | possible_keys | key       | key_len | ref  | rows | Extra |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-------+
|  1 | SIMPLE      | lm_d_plan | index | FK_deptNo     | FK_deptNo | 99      | NULL | 5416 | NULL  |
+----+-------------+-----------+-------+---------------+-----------+---------+------+------+-------+
1 row in set (0.00 sec)

 

explain返回的各列含义 
select_type 
SIMPLE：简单SELECT(不使用UNION或子查询等) 
PRIMARY：我的理解是最外层的select 
UNION ： UNION中的第二个或后面的SELECT语句 
DEPENDENT UNION ： UNION中的第二个或后面的SELECT语句，取决于外面的查询 
UNION RESULT ： UNION的结果 
SUBQUERY ： 子查询中的第一个SELECT 
DEPENDENT SUBQUERY ： 子查询中的第一个SELECT，取决于外面的查询 
DERIVED：派生表的SELECT(FROM子句的子查询)

table： 显示这条sql涉及到的那些表，有时不是真实的表名字,看到的是derivedx(x是个数字,我的理解是第几步执行的结果) 
type ： 这是重要的列，显示连接是那种类型，从最好到最坏依次是：const（常数查找，主键或唯一索引查找），system：（这是const联接类型的一个特例。表仅有一行满足条件），eq_reg： （范围查找，主键或者索引范围的查找），ref（常见连接查询，一个表是基于某个索引的查找），range（基于索引范围的查找），index（索引的扫描），all（表扫描） 
possible_keys ： 显示可能用到的索引。如果为空，没有可以用到的索引 
key ： 实际使用的索引，如果为空则没有使用索引 
key_len ： 使用索引的长度，在不损失精确性的情况下，长度越短越好 
ref： 显示索引的哪一列被使用了。如果可能的话，是一个常数。 
rows ： mysql认为必须检查的用来返回数据的行数 
Extra ： 扩展列，需要注意的返回值： 
Using filesort 看到这个就需要优化了。mysql需要通过额外的步骤发现如何对返回行进行排序，他需要根据连接类型以及存储排序键值和匹配条件的所有行的行指针进行所有行排序，通常发生order by操作上。 
Using temporary 看到这个就需要优化了，mysql需要建立临时表来存储结果，这通常发生在对不同的列集进行order 
by操作上，而不是group by操作。

具体的实例MAX()优化

案例1：我们需要lm_d_plan的最后创建时间是什么

mysql> select max(createtime) from lm_d_plan;
+-----------------+
| max(createtime) |
+-----------------+
| 20150528143338  |
+-----------------+
1 row in set (0.11 sec)
mysql> explain select max(createtime) from lm_d_plan;
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | lm_d_plan | ALL  | NULL          | NULL | NULL    | NULL | 55562 | NULL  |
+----+-------------+-----------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.00 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14

select_type 这是一个简单的查询 
table 查询的是lm_d_plan 表 
type ALL说明是全表扫描 
rows 需要扫描的行数55562 
通过以上信息，显然是一个低效的sql，随着数据量增大，它的IO也随之增大，很可能拖慢整个服务器，说明肯定是需要优化的。 
策略：通常情况下在 createtime上建立索引

mysql> create index createime on lm_d_plan(createtime);
Query OK, 0 rows affected (0.65 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select max(createtime) from lm_d_plan;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

mysql> select max(createtime) from lm_d_plan;
+-----------------+
| max(createtime) |
+-----------------+
| 20150528143338  |
+-----------------+
1 row in set (0.00 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19

Extra 返回 Select tables optimized away ，意思就是并不需要查询具体的表，直接通过查询索引就可以完成。因为索引是顺序排列的，通过索引的统计信息就可以获得最大的createime。这也是个覆盖索引，完全通过索引的信息就可以完成查询。

案例2：我们需要找出各部门lm_c_dept的提报的需求计划lm_d_plan是多少

mysql> SELECT d.deptName,count(*) from lm_d_plan p INNER JOIN lm_c_dept d ON d.deptNo = p.FK_deptNo GROUP BY p.FK_deptNo;
+--------------------------------+----------+
| deptName                       | count(*) |
+--------------------------------+----------+
| xx一部                             |     1573 |
| xx板块业务部                      |      282 |
| yy板块业务二部                     |      781 |
| aa块业务部                       |     4753 |
| cc块业务部                           |    12252 |
| dd板块业务部                      |     6827 |
| xx设备业务部                      |     4442 |
| cccc物资业务部                    |    26196 |
+--------------------------------+----------+
8 rows in set (0.09 sec)
mysql> explain SELECT d.deptName,count(*) from lm_d_plan p INNER JOIN lm_c_dept d ON d.deptNo = p.FK_deptNo GROUP BY p.FK_deptNo\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: d(可能是表中间有下划线显示有问题lm_c_dept )
         type: ALL
possible_keys: deptNo
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: Using temporary; Using filesort
select_type 这是一个简单的查询
table 查询的是lm_c_dept  表
type  ALL说明是全表扫描，因为没有where条件，表扫描也可以说是正常的。
possible_keys 可能用到的索引是deptNo
rows  需要扫描的行数11
Extra 既使用了临时表，也用了文件排序，这是效率极低的。
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: p(可能是表中间有下划线显示有问题lm_d_plan )
         type: ref
possible_keys: FK_deptNo
          key: FK_deptNo
      key_len: 99
          ref: lms.d.deptNo(这里应该是lms.lm_c_dept.deptNo)
         rows: 3968
        Extra: Using index
select_type 这是一个简单的查询
table 查询的是lm_d_plan 表
type  ref常见连接查询，一个表是基于索引的查找
possible_keys 可能用到的索引是FK_deptNo
key 使用的索引是FK_deptNo
key_len 索引的长度是99
ref lms.lm_c_dept.deptNo这列索引被使用了
rows  需要扫描的行数3968
Extra 使用索引
2 rows in set (0.00 sec)

 

策略：通过以上分析，显然最需要优化的地方是临时表和文件排序了，因为这种低效率查询会直接对服务器产生极大的资源消耗，同时影响整个服务器性能。通过优化sql语句来改善这种情况。

mysql> explain SELECT d.deptName,c.ct FROM lm_c_dept d INNER JOIN(SELECT p.FK_deptNo,count(*) ct from lm_d_plan p GROUP BY p.FK_deptNo) c ON d.deptNo = c.FK_deptNo\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: d(可能是表中间有下划线显示有问题lm_c_dept )
         type: ALL
possible_keys: deptNo
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 99
          ref: lms.d.deptNo
         rows: 555
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: p(可能是表中间有下划线显示有问题lm_d_plan )
         type: index
possible_keys: FK_deptNo
          key: FK_deptNo
      key_len: 99
          ref: NULL
         rows: 55562
        Extra: Using index
3 rows in set (0.00 sec)

 

• 34
• 35

通过执行计划可以看出Extra已经没有Using filesort 和 Using temporary临时表和使用文件排序已经改善。执行结果也加从0.09 sec 加快到0.02 sec，如果有条件，最好在子查询里增加过滤，而不是外查询。

索引优化

如何选择合适的列建立索引

1. 从where从句中，group by从句中，order by从句中，on从句中，select列中，还可以建立覆盖索引（也就是指索引包含所有查询的列，直接查索引就可以完成任务的），
2. 索引字段越小越好，因为mysql存储是已页为单位的，如果从单页中获取更多的结果，减少IO操作，那么就起到了积极的作用。
3. 离散程度越高的列放在联合索引的前面，因为离散度越大的列可选择性越高，可以通过count统计函数查看离散度

mysql> select count(distinct cartCode),count(distinct cartlineNum) from lm_d_plan;
+--------------------------+-----------------------------+
| count(distinct cartCode) | count(distinct cartlineNum) |
+--------------------------+-----------------------------+
|                    40782 |                         341 |
+--------------------------+-----------------------------+
1 row in set (0.14 sec)

 

由此可见，如果将cartCode和cartlineNum建立联合索引，cartCode应该放到前面。 
create index cartCode_cartlineNum on lm_d_plan(cartCode,cartlineNum);

SQL及索引优化

索引的维护和优化之重复及冗余索引 
重复索引是最同一列多次建立索引，比如在primay key列建立唯一索引，因为primay key已经是主键索引了。再建立唯一索引就索引重复了。 
冗余索引是指多个索引的前缀列相同，或者联合索引中包含的主键索引。（Innodb会在每个索引的后面附加主键索引。） 
常用索引操作 
查看索引 
show index from tablename; 
删除索引，其中，在前面的两条语句中，都删除了table_name中的索引index_name。而在最后一条语句中，只在删除PRIMARY KEY索引中使用，因为一个表只可能有一个PRIMARY KEY索引，因此不需要指定索引名。

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;

• 1
• 2
• 3

创建索引:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [USING index_type]
    ON tbl_name (index_col_name,...)

index_col_name:
    col_name [(length)] [ASC | DESC]

• 1
• 2
• 3
• 4
• 5
• 6

使用alter的方法创建索引

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;

• 1
• 2
• 3

对于CHAR和VARCHAR列，只用一列的一部分就可创建索引。创建索引时，使用col_name(length)语法，对前缀编制索引。前缀包括每列值的前length个字符。BLOB和TEXT列也可以编制索引，但是必须给出前缀长度。

此处展示的语句用于创建一个索引，索引使用列名称的前5个字符。 
create index itemsCode on lm_d_plan(itemsCode(5)); 
因为多数名称的前10个字符通常不同，所以此索引不会比使用列的全名创建的索引速度慢很多。另外，使用列的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。

前缀最长为255字节。对于MyISAM和InnoDB表，前缀最长为1000字节。注意前缀的限长以字节计，而CREATE INDEX语句中的前缀长度指的是字符的数目。对于使用多字节字符集的列，在指定列的前缀长度时，要考虑这一点。

在MySQL中： 
· 只有当您正在使用MyISAM, InnoDB或BDB表类型时，您可以向有NULL值的列中添加索引。 
· 只有当您正在使用MyISAM, BDB或InnoDB表类型时，您可以向BLOB或TEXT列中添加索引。

一个index_col_name规约可以以ASC或DESC为结尾。这些关键词将来可以扩展，用于指定递增或递减索引值存储。目前，这些关键词被分析，但是被忽略；索引值均以递增顺序存储。

部分储存引擎允许在创建索引时指定索引类型。index_type指定语句的语法是USING type_name。不同的储存引擎所支持的type_name值已显示在下表中。如果列有多个索引类型，当没有指定index_type时，第一个类型是默认值。

存储引擎	允许的索引类型
MyISAM	BTREE
InnoDB	BTREE
MEMORY/HEAP	HASH, BTREE

示例：

CREATE TABLE lookup (id INT) ENGINE = MEMORY;
CREATE INDEX id_index USING BTREE ON lookup (id);

• 1
• 2

TYPE type_name可以作为USING type_name的同义词，用于指定索引类型。但是，USING是首选的格式。另外，在索引规约语法中，位于索引类型前面的索引名称不能使用TYPE。这是因为，与USING不同，TYPE不是保留词，因此会被认为是一个索引名称。 
如果您指定的索引类型在给定的储存引擎中不合法，但是有其它的索引类型适合引擎使用，并且不会影响查询功能，则引擎应使用此类型。 
FULLTEXT索引只能对CHAR, VARCHAR和TEXT列编制索引，并且只能在MyISAM表中编制。 
SPATIAL索引只能对空间列编制索引，并且只能在MyISAM表中编制。 
索引的维护和优化之pt-duplicate-key-checker 
使用方法:pt-duplicate-key-checker -uroot -p 'xxxx' -h 127.0.0.1 
[root@localhost ~]# pt-duplicate-key-checker -uroot -p ‘123456’ -h localhost

# ########################################################################
# lms.lm_d_plan                                                           
# ########################################################################

# oldPlanNum is a left-prefix of oldPlanNum_oldPlanlineNum
oldPlanNum索引 已经是oldPlanNum_oldPlanlineNum联合索引的左前缀了。也就是说查询oldPlanNum可以直接命中oldPlanNum_oldPlanlineNum联合索引，而毋需再创建oldPlanNum 索引了。
# Key definitions:
关键的定义
#   KEY `oldPlanNum` (`oldPlanNum`),
#   KEY `oldPlanNum_oldPlanlineNum` (`oldPlanNum`,`oldPlanlineNum`),
# Column types:
列信息
#     `oldplannum` varchar(20) default null comment '原始号'
#     `oldplanlinenum` int(4) default null comment '原始行号'
# To remove this duplicate index, execute:
建议是remove这个重复的索引，执行以下命令
ALTER TABLE `lms`.`lm_d_plan` DROP INDEX `oldPlanNum`;

同样的错误类型如下：
# cartCode is a left-prefix of cartCode_cartlineNum
# Key definitions:
#   KEY `cartCode` (`cartCode`),
#   KEY `cartCode_cartlineNum` (`cartCode`,`cartlineNum`),
# Column types:
#     `cartcode` varchar(20) default null comment '?????'
#     `cartlinenum` int(4) default null comment '?????'
# To remove this duplicate index, execute:
ALTER TABLE `lms`.`lm_d_plan` DROP INDEX `cartCode`;

# ########################################################################
# Summary of indexes                                                      
# ########################################################################

# Size Duplicate Indexes   7000812
# Total Duplicate Indexes  2
# Total Indexes            155

 

通过pt-dulication-key-checker可以高效的优化数据库的索引。 
索引的维护和优化之删除无用索引pt-index-usage 
pt-index-usage有空补充，大家有兴趣可以自己研究一下。

数据库结构优化

选择合适的数据类型

1. 可以存下我们数据的最小数据类型
2. 使用简单的数据类型，int类型要比varchar类型，mysql处理更简单
3. 尽量使用not null字段，因为innodb存储类型，尽量设置默认值
4. 尽量少用text类型，非用不可的话需要考虑是否分表的方式解决。 
   范例如下： 
   用int来存储时间，from_unixtime(),unix_timestamp()两个函数进行转换。

insert into tablename(timestr) values( unix_timestamp('2015-05-05 20:20:00'));
select from_unixtime(timestr) from tablename;

• 1
• 2

**用bigint存储ip地址，利用inet_aton(),inet_ntoa()两个函数进行转换。

insert into tablename(ipaddr) values( inet_aton('192.168.1.1'));
select inet_ntoa(ipaddr) from tablename;

• 1
• 2

表的范式优化

表的范式化

范式化是指数据库设计遵循的设计规范，目前说的范式化一般是指第三设计范式，也就是要求表中非关键字段对任意候选关键字段不存在传递函数依赖则符合第三范式。 
示例：

商品名称	价格	重量	有效期	分类	分类描述
可乐	3.5	600ml	2015-09-09	酒水饮料	碳酸饮料
黄瓜	1.0	100g		生鲜食品	蔬菜

存在以下传递函数依赖管理 
商品名称—>分类—>分类描述 
很显然如果商品名称是关键字段(任意候选关键字段)，那么非关键字段分类描述依赖分类依赖商品名称。也就是说分类描述传递函数依赖关键字段商品名称。 
进行标准第三范式化修改后： 
Table 1

商品名称	价格	重量	有效期
可乐	3.5	600ml	2015-09-09
黄瓜	1.0	100g

Table 2

分类	分类描述
酒水饮料	碳酸饮料
生鲜食品	蔬菜

Table 3

商品名称	分类
可乐	酒水饮料
黄瓜	生鲜食品

表的反范式化

反范式化是为了查询效率的考虑，把原本符合第三范式的表适当的增加冗余，以达到优化查询的目的，反范式化是一种以空间换取时间的策略。 
示例：

订单表：订单ID，订单编号，用户ID，下单时间，订单状态
用户表：用户ID，姓名，电话，地址
订单商品表：订单ID，商品ID，订单数量，商品价格
商品表：商品ID，名称，描述

• 1
• 2
• 3
• 4

需求描述：需要给客户展现订单的整体概况信息，包括订单，价格，商品信息，构造的SQL如下：

SELECT
    a.订单编号,b.姓名,b.电话,b.地址,SUM(c.订单数量 * c.商品价格) as 订单价格
FROM 订单表 a
LEFT JOIN 用户表 b ON a.用户ID = b.用户ID
LEFT JOIN 订单商品表 c ON a.订单ID = c.订单ID

• 1
• 2
• 3
• 4
• 5

我们要查询这些信息，至少关联三张表，表结构就是这样的。 
如果进行一些反范式化设计，将会大大改善上述查询效率极低的SQL。

订单表：订单ID，订单编号，用户ID，下单时间，订单状态，订单价格，姓名，电话，地址
用户表：用户ID，姓名，电话，地址
订单商品表：订单ID，商品ID，订单数量，商品价格
商品表：商品ID，名称，描述

• 1
• 2
• 3
• 4

同样的需求，构造SQL如下：

SELECT
    a.订单编号,a.姓名,a.电话,a.地址,a.订单价格
FROM 订单表 a

• 1
• 2
• 3

显然，在优化数据库的时候，进行一些适当的反范式化，会在很大程度上提升执行效率。

表的垂直拆分

所谓垂直拆分就是把原来一个很多列的表拆分成多个表，解决表的宽度问题，通常垂直拆分可以按以下原则进行： 
1. 把不常用的字段单独存放在一张表中 
2. 把大字段独立存放在一张表中 
3. 把经常使用的字段存放到一起

表的水平拆分

水平拆分是为了解决单表数据量过大的问题，水平拆分每一个表结构都是一样的。 
如何将数据平均插入到N张表呢？ 
常用的水平拆分的方法： 
1. 对ID进行hash运算，如果要拆分成5个表，则使用mod(ID,5)（取模）取出0-4个值，取模后0插0表，取1插1表 
2. 针对不同的hashID，把数据存储到不同的表中 
拆分后所面临的挑战： 
1. 跨分区进行查询 
2. 统计及后台报表操作。 
建议：前后台表进行拆开，前台考虑效率所以进行水平拆分，后台考虑方便完整的数据统计，单独生成统计及报表所用的表

系统配置优化

操作系统配置优化

数据库是基于操作系统之上的，目前大多数mysql都运行在linux上，所以对操作系统的一些配置也会影响到mysql 的性能，下面就列出常用的优化配置。 
网络方面的配置，要修改/etc/sysctl.conf文件 
1. 增加TCP支持的队列数 
net.ipv4.tcp_max_syn_backlog=65535 
2. 减少断开连接时间，资源回收,由于TCP/IP是有三个状态的，为了加快timewait状态回收，优化以下参数。

net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10

• 1
• 2
• 3
• 4

优化打开文件数，因为mysql的表都是已文件格式存储的，对innodb类型的查询都会打开表文件，所以增加文件打开数在一定程度上提升了mysql查询效率。可以使用ulimit -a查看目录的各限制，可以修改/etc/security/limits.conf 文件，增加以下内容增加文件打开数限制

* soft nofile 65535
* hard nofile 65535

• 1
• 2

修改前后对比：

[root@localhost ~]# ulimit -a
...
open files                      (-n) 1024
...

[root@localhost ~]# ulimit -a
...
open files                      (-n) 65535
...

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

除此以外最好在mysql所在服务器关闭iptables selinux等防火墙软件，通过硬件防火墙等方式解决安全问题。

MYSQL配置优化

mysql可以通过启动时指定配置参数和使用配置文件进行配置，在大多数情况下配置文件存储在/etc/my.cnf或是/etc/mysql/my.cnf。windows一般在mysql目录中my.ini文件。mysql查找配置文件的顺序可以通过以下命令获取。 
$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options' 
注意，如果多个位置存在配置文件，则后面的会覆盖前面的配置。

mysql常用配置优化

innodb_buffer_poor_size: 
非常重要的参数，用来配置Innodb的缓冲池，如果数据库中只有innodb表，则推荐配置为总内存的75%。

mysql> SELECT ENGINE,ROUND(SUM(data_length + index_length)/1024/1024,1) as 'Total MB'  FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in ("information_schema","performance_schema" ) GROUP BY ENGINE;
+--------+----------+
| ENGINE | Total MB |
+--------+----------+
| CSV    |      0.0 |
| InnoDB |     79.3 |
| MyISAM |      0.7 |
+--------+----------+
3 rows in set (0.59 sec)

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9

innodb_buffer_poor_size >= Total MB（数据+索引大小），如果达不到要求，设置尽可能的大就可以。

innodb_buffer_poor_instances:（mysql5.5+以上才有） 
可以控制缓冲池个数，默认情况下只有一个缓冲池，mysql中有些操作需要独占使用缓冲池，那么就可能造成阻塞。如果分成多分可以，一般可以分为4到8份，大小=总缓冲大小/分数。 
innodb_log_buffer_size: 
innodb log 缓冲大小，由于日志最长每秒种就会刷新，所以不需要配置多大，只要能够存下1秒钟事务数据就可以了。 
innodb_flush_log_at_trx_commit:(据说很管用) 
关键参数，对innodb磁盘IO影响很大，默认为1，可以设置0，1，2三个值，一般建议为2，但如果数据安全性较高，则设置为1.

抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

innodb_read_io_threads 和 innodb_write_io_threads :（mysql5.5+以上可调整） 
决定innodb读写IO的进程数量，默认为4。可根据服务器具体情况设置 
innodb_file_per_table:建议设为ON。 
关键参数，控制innodb每个表独立使用的表空间，默认为OFF，也就是所有表都会建立在共享表空间中。共享表空间IO成为瓶颈，顺序写入的时候，共享表空间只有一个文件，并发写入的时候效率很低。共享表空间是无法单独收缩的，当删除一个很大的数据后，之后把所有数据导出后导入才可以收缩。 
innodb_stats_on_metadata:默认为ON，建议OFF 
决定mysql在什么情况下刷innodb表的统计信息。优化器在工作中需要通过统计信息获取索引等系想你，保证优化器使用到正确的索引。但是如果设为ON，在进行show，select等操作时都会进行不必要的刷新，所以建议在修改数据库后人为进行刷新。

第三方配置工具

PERCONA CONFIGURATION WIZARD FOR MYSQL 
http://tools.percona.com/wizard 
介绍略，大家可以自己研究

服务器硬件优化

如何选择CPU

1. mysql很多操作都使用单核cpu
2. mysql对cpu核数支持并不是越多越快，建议不要超过32核。 
   建议选择单核频率高的cpu

Disk IO 优化

建议RAID1+0 ：就是RAID1和RAID0结合，兼顾性能和数据安