mysql数据库优化 之 001

首先，数据库优化要先从哪方面插手

1、SQL语句及索引

2、数据库表结构

3、系统配置

4、硬件

个人认为数据库优化成本和效果如下图关系：

然后我们怎样才能有效的发现sql以及索引的一些问题。

我们就需要几种慢查询分析工具来进行查看分析；

在这里先介绍几种：

pt-query-digest,mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log

其中个人推荐使用mysqlsla,pt-query-digest,

mysqlsla,整体来说, 功能非常强大. 数据报表,非常有利于分析慢查询的原因, 包括执行频率, 数据量, 查询消耗等.

格式说明如下:

总查询次数 (queries total), 去重后的sql数量 (unique)

输出报表的内容排序(sorted by)

最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.

Count, sql的执行次数及占总的slow log数量的百分比.

Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.

95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间.

Lock Time, 等待锁的时间.

95% of Lock , 95%的慢sql等待锁时间.

Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量.
Rows examined, 扫描的行数量.

Database, 属于哪个数据库

Users, 哪个用户,IP, 占到所有用户执行的sql百分比

Query abstract, 抽象后的sql语句

Query sample, sql语句

除了以上的输出, 官方还提供了很多定制化参数, 是一款不可多得的好工具.

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

总体统计结果

Overall：总共有多少条查询

Time range：查询执行的时间范围

unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询

total：总计   min：最小   max：最大  avg：平均

95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值

median：中位数，把所有值从小到大排列，位置位于中间那个数

# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency ________________
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性    总计  最小 最大 平均 95% 标准 中等
# Attribute   total  min  max  avg  95% stddev median
# ============  ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time    3s 640ms  2s  1s  2s 999ms  1s
# 锁占用时间
# Lock time   1ms  0  1ms 723us  1ms  1ms 723us
# 发送到客户端的行数
# Rows sent    5  1  4 2.50  4 2.12 2.50
# select语句扫描行数
# Rows examine  186.17k  0 186.17k 93.09k 186.17k 131.64k 93.09k
# 查询的字符数
# Query size   455  15  440 227.50  440 300.52 227.50

二、然后利用explain查询SQL的执行计划

2.1

     然后我们通过count()和max()的方法举例，如何优化sql

首先用MAX（）进行普通查询

再在字段（payment_date）字段上建立索引查询进行比较

2.2 子查询的语句优化

当关连建存在一对多是关系时候。这样在查询的结果会出现冗余值。我们用distinct去重

2.3优化group by查询

首先这个SQL语句通过explain分析、用了临时表和文件排序。需要就进行优化

优化过后我们再看，就没有了。

2.4limit查询优化

现在就不用解释了。自己也可以看出来优化的优势了。，。

但是如果查询的数据两过大的时候再这样查询就慢了。。这样我们可以利用主键过滤查询

SELECT查询的速度

总的来说，要想使一个较慢速SELECT ... WHERE更快，应首先检查是否能增加一个索引。不同表之间的引用通常通过索引来完成。你可以使用EXPLAIN语句来确定SELECT语句使用哪些索引。

这节先总结到这里。

第二节我们在学习一下索引优化