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

一、mysql性能优化：慢查询分析

影响mysql性能的有两个方面：

第一个方面是数据库死锁（dead lock），就是不释放当前的资源，还去抢别的资源，造成了堵塞，可以利用show processlist查看死锁

第二个方面是资源竞争，几个对象抢数据库资源，由于数据库资源本身处理能力差，导致多个对象发送的请求不能够及时处理，从而导致处理的时间变长，影响数据库性能

1、慢查询含义：慢查询就是资源本身处理能力慢，导致了整个系统处理时间长，所以需要从资源本身进行查询来分析性能问题

2、首先需要后台mysql服务开启，可以利用netstat -ntlp查看mysql服务端口是否打开，若没有打开则需要mysql -uroot -p密码打开mysql服务

 

二、查询与索引优化分析

1、性能瓶颈定位

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

Mysql> show status --显示状态信息（扩展show status like ‘XXX’）

配置优化（以下是影响性能的主要的配置要素）

show global status;

1)      Max_connections/Max_used_connections：最大连接数/最大用户连接数（连接数是指资源对外提供的最大连接的个数，如果对外连接的个数不够，会导致性能问题，所以如果是CPU、内存、磁盘读写等指标都没有问题的话，如果性能还是有问题，就需要考虑最大连接数）

最大连接数优化：修改配置文件

在etc\mysql\mysql.conf.d文件中找到mysqld.cnf配置文件，修改max_connections参数，保存并覆盖

 

配置完毕后，重启mysql，利用命令service mysql restart

2)      back_log：指定MySQL可能的连接数量

3)      interactive_timeout：指的是mysql在关闭一个交互的连接之前所要等待的秒数(交互连接如mysql gui tool中的连接)

4)      key_buffer_size：MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分配一段内存缓冲区。read_buffer_size变量控 制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。

5)      query_cache_size：查询缓存大小（当在使用中，查询缓存会存储一个 SELECT 查询的文本与被传送到客户端的相应结果。如果之后接收到一个同样的查询，服务器将从查询缓存中检索结果，而不是再次分析和执行这个同样的查询。）

6)      record_buffer_size：每个进行一个顺序扫描的线程为其扫描的每张表分配这个大小的一个缓冲区。如果你做很多顺序扫描，你可能想要增加该值。

7)      read_rnd_buffer_size：是MySQL的随机读缓冲区大小，当按任意顺序读取行时（列如按照排序顺序）将分配一个随机读取缓冲区，进行排序查询时，MySQL会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需要大量数据可适当的调整该值，但MySQL会为每个客户连接分配该缓冲区所以尽量适当设置该值，以免内存开销过大。表的随机的顺序缓冲 提高读取的效率。

8)      sort_buffer_size：是一个connection级参数，在每个connection第一次需要使用这个buffer的时候，一次性分配设置的内存。

9)      join_buffer_size：join buffer是存放的基于每thread的连接表信息，这样在连接的时候，只需要访问join buffer就可以了，不需要再去有并发机制保护的cache

10)    table_cache：主要用于设置table高速缓存的数量。由于每个客户端连接都会至少访问一个表，因此此参数的值与max_connections有关。

11)    max_heap_table_size：定义了用户可以创建的内存表(memory table)的大小.这个值用来计算内存表的最大行数值。这个变量支持动态改变，即set @max_heap_table_size=#

12)    tmp_table_size：规定了内部内存临时表的最大值，每个线程都要分配。

13)    thread_cache_size：服务器线程缓存大小

14)    thread_concurrency：thread_concurrency变量是针对于Solaris 8及低版本的系统，设置了这个变量mysqld会调用thr_setconcurrency()函数。这个函数允许应用程序给同一时间运行的线程系统提示所需数量的线程。

15)    wait_timeout：连接等待时间设置

Mysql> show variables --显示系统变量（扩展show variables like ‘XXX’）

Mysql> show innodb status --显示InnoDB存储引擎的状态

Mysql> show processlist --查看当前SQL执行，包括执行状态、是否死锁 等（最先需要查看的，查看db字段，如果是NULL就没有死锁）

Mysql> show OPEN TABLES where In_use > 0 查看锁的表;

 

Shell> mysqladmin variables -u username -p password--显示系统变量

Shell> mysqladmin extended-status -u username -p password--显示状态信息

查看状态变量及帮助：

Shell> mysqld -verbose -help [|more #逐行显示]

 http://www.jb51.net/article/22409.htm  sql锁的问题

三、开启慢查询日志：将慢查询结果放在日志中

1、可以查看系统默认的慢查询设置，show VARIABLES like '%slow%';（解决上面配置文件中的那两个参数无法使用的问题）

 2、将slow_query_log打开，利用命令set GLOBAL slow_query_log = ON;

 3、设置完毕后，查看慢查询日志（默认路径：/var/lib/mysql/ubuntu-slow.log）

 time：花费时间

id：程序id

command：命令

四、慢查询日志分析方法

由于慢查询日志内容特别多，所以需要通过命令进行查看和分析

1、可以利用tail -f 方法查看慢查询日志，通过这个命令可以查询到慢查询日志中最新的内容

 

2、利用mysqldumpslow工具进行分析

慢查询日志中记录内容很多，可以使用mysqldumpslow工具（MySQL客户端安装自带）来对慢查询日志进行分类汇总。mysqldumpslow对日志文件进行了分类汇总，显示汇总后摘要结果。

mysqldumpslow命令（不需要在mysql中执行）

s是order的顺序，说明写的不够详细，俺用下来，包括看了代码，主要有 c,t,l,r和ac,at,al,ar，分别是按照query次数，时间，lock的时间和返回的记录数来排序，前面加了a的时倒序 -t，是top n的意思，即为返回前面多少条的数据 -g，后边可以写一个正则匹配模式，大小写不敏感的

mysqldumpslow -s c -t 20 慢查询日志路径：访问次数最多的20个sql语句

 mysqldumpslow -s r -t 20 慢查询日志路径：返回记录集最多的20个sql语句

 

mysqldumpslow -t 10 -s t -g “left join” 慢查询日志路径：这个是按照时间返回前10条里面含有左连接的sql语句。

mysqldumpslow -t 10 -s t  慢查询日志路径：按照时间返回前10条sql语句

使用mysqldumpslow命令可以非常明确的得到各种我们需要的查询语句，对MySQL查询语句的监控、分析、优化是MySQL优化非常重要的一步。开启慢查询日志后，由于日志记录操作，在一定程度上会占用CPU资源影响mysql的性能，但是可以阶段性开启来定位性能瓶颈。

更多的mysqldumpslow信息可以查看：http://www.cnblogs.com/lavandachen/articles/1929537.html

五、explain分析查询（针对慢查询进行分析，分析sql慢的原因）

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈，可以针对慢查询得到的sql语句进行分析，查看这些sql语句是否添加了索引，如果没有添加索引，就可以让开发添加索引进行优化，提高数据库执行速度。通过explain命令可以得到:

- 表的读取顺序

- 数据读取操作的操作类型

- 哪些索引可以使用

- 哪些索引被实际使用

- 表之间的引用

- 每张表有多少行被优化器查询

explain分析查询可以分析sql语句执行情况

例如sql执行了一条语句，如下图

 如果需要知道该sql是怎么执行的，则需要用explain进行分析，只需要在select前面加上explain即可

 注意：其中最重要的两个字段是possible_keys（可能的实体）和key（实体），通过这两个字段可以分析出查询有没有做索引（如果possible_keys和key没有值，那么就没有做索引，有值说明是做了索引）

例如利用where语句查找具体条件信息

 

 利用explain分析查询sql，两个字段有值则说明有索引，没有值则没有索引，需要优化的话就可以添加索引

 

注意：索引仍然是有缺陷的，比如 like '%XXX'则就不能利用索引，索引只能用于直接查询中，模糊查询无法使用

更多索引作用信息可以查询： http://blog.csdn.net/kennyrose/article/details/7532032 

mysql explain用法：http://www.cnblogs.com/linjiqin/p/4125898.html

让程序员创建索引：http://blog.csdn.net/yuanzhuohang/article/details/6497021

六、profiling分析查询（可以查询出sql执行花费的时间）

通过慢日志查询可以知道哪些SQL语句执行效率低下，通过explain我们可以得知SQL语句的具体执行情况，索引使用等，还可以结合show命令查看执行状态。

如果觉得explain的信息不够详细，可以同通过profiling命令得到更准确的SQL执行消耗系统资源的信息。利用show profiles命令即可查询所有使用过的sql执行使用的时间，可以根据执行时间排个序，将执行时间比较多的sql抓出来

 更多信息可以查看：http://www.2cto.com/database/201506/404310.html

七、其他扩展

最重要（最大连接数）

show variables like 'max_connections';

set global max_connections=1500;

扩展知识

http://blog.csdn.net/iquicksandi/article/details/7970706

http://item.jd.com/11220393.html#comment

术业有专攻

主要需要关注以下部分：

1、连接数

show variables like 'max_connections';

set global max_connections=1500;

2、慢查询：设置慢查询路径，开启慢查询

3、索引：利用explain分析查询  

4、锁表问题：利用show processlist 命令查看是否有锁表情况

5、基本数据库最大问题

6、查看日志帮助分析内存溢出 ：在mysql配置文件中可以查找到各种日志的存放路径，可以将配置文件中的日志路径打开，即删除注释符号#