性能测试之MySQL的监控和调优

一， 影响MySQL性能因素

CPU、内存， 网卡流量， 磁盘IO， 数据库参数配置， SQL查询速度等

CPU： 64位的CPU一定要工作在64位的系统下，如果并发量大的话，就需要选择更多核心的CPU来提高吞吐量，对于并发比较高的 场景，CPU的数量比频率重要对于密集型场景和复杂SQL，则CPU频率越高越好

内存： 容量越多越好，但对性能影响有限，因为并不能无限的增加性能 主频则选择服务器主板可支持的最高频率最好

I/O子系统： PCIe（pcie接口固态硬盘，性能最好1560MBps） -> SSD（sata接口固态硬盘560MBps） ->Raid10（磁盘阵列） ->机械磁盘 ->SAN（网络存储）

http://www.21ic.com/app/analog/201906/898183.htm

二，SQL介绍

SQL(Structure Query Language)是数据库操作的的核心语言

DDL（Data Definition Languages）语句：即数据库定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。

DML（Data Manipulation Language）语句：即数据操纵语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。

DCL（Data Control Language）语句： 即数据控制语句，用于授权/撤销数据库及其字段的权限（DCL is short name of Data Control Language which includes commands such as GRANT and mostly concerned with rights, permissions and other controls of the database system.）。常用的语句关键字有：GRANT,REVOKE。

TCL（Transaction Control Language）语句：事务控制语句，用于控制事务，常用的语句关键字有：COMMIT,ROLLBACK,SAVEPOINT,SET TRANSACTION。

 三，MySQL的连接数

MYSQL数据库默认最大连接数是100，然而对于流量稍微大一点的论坛或网站这个连接数是远远不够的，当并发数过大的时候会出现连接数不够用，使得很多线程在等待其他连接释放，会直接导致导致数据库连接超时或者响应时间过长，所以需要调整最大连接数。show variables like '%max_connections%';

重新设置数据库最大连接数 set global max_connections=200（全局的）

　　设置完成后我们再看一下：

查询数据库当前设置的最大连接数 show variables like '%max_connections%';

当发现数据库连接池占满，或者应用程序日志报错链接数据库失败的时候，我们就需要查看连接数和设置连接数，如何判断连接数占满了呢？有两种方法

1，查当前的连接数和最大的连接数，如果两者相等就是被占满了

show gloable status like '%max_used_connections%';查看历史使用的最高连接数

注意这个历史数据不会降低，如这次使用的连接数是20，是以往的最高纪录，就被记录为历史最高连接数，下次使用是100，那么就被记录为历史最高连接数为100，不断的去扩大，不会缩减，那我们如何判断数据库连接池被占满呢？请下下面第二种方法

2，查看线程占用的连接数和数据库连接池的配置：

查看线程占用的连接数：show status like 'thread%';如图：

Threads_cached:   #表示mysql管理的线程池中还有多少可以被复用的资源

Threads_connected  # 打开的连接数  如果这数据和当前的最大的连接数一致，那么就意味着连接被占满了

Threads_created  # 表示创建过的线程数

Threads_running  # 激活的连接数，这个数值一般远低于connected数值， # 准确的来说，Threads_running是代表当前并发数 。一般做压测的时候，这个数据和Threads_connected是差不多的

同时还要结合数据库连接池的配置

那么这个最大连接数和我们所说的数据库连接池有什么关系呢？如图说明：

 注意：这里的连接池是指应用程序中配置的连接池，最大连接数是数据库本身能支持的连接数量

四，MySQL缓存

数据库属于 IO 密集型的应用程序，其主要职责就是数据的管理及存储工作。而我们知道，从内存中读取一个数据库的时间是微秒级别，而从一块普通硬盘上读取一个IO是在毫秒 级别，二者相差3个数量级。所以，要优化数据库，首先第一步需要优化的就是 IO，尽可能将磁盘IO转化为内存IO。

缓存分为两个维度 查询缓存Query cache 存储引擎InnoDB_Buffer_Pool

查询缓存----------->

查询缓存Query cache 查询缓存会缓存完整的SELECT查询结果，当查询命中缓存时MySQL会将结果立刻返回，直接跳过了解析、优化和执行阶段。当然，Query Cache 也有一个致命的缺陷，那就是当某个表的数据有任何变化，都会导致所有引用了该表的select语句在Query Cache 中的缓存数据失效。所以，当我们的数据变化非常频繁的情况下，使用Query Cache 可能会得不偿失。因此，应用程序不需要关心MySQL是通过缓存查询出的结果还是实际执行过SQL语句返回的结果，因为这两种结果是完全相同的 从前面的数据库执行过程图中可以看到，执行一条SQL查询语句会先查询该语句是否 存在于缓存中，需要注意的是当语句中的字符大小写或注释只要有一点点的不同，查询缓存就会被认为是不同的查询，导致无法命中查询缓存。另外，对于不确定的 函数，如：now()、current_date()等这种查询都不会被缓存 既然查询缓存的有可以改善性能的优点，自然也有自己的缺点，主要体现在当开启了查询缓存时对于读写操作都增加了额外的开销。相对于读，再查询开始前需要先检查缓存，而对于写，则是当写入数据后需要更新缓存。

怎么设置缓存的大小呢？

Query cache的参数 查询缓存参数，在mysql配置文件中添加，linux下为my.cnf，windows下为my.ini：

# 1.是否开启查询缓存，具体选项是0(OFF),1(ON)和2(DEMAND) , 添加--->query_cache_type = 1

# 2.分配给查询缓存的总内存，一般建议不超过256M ,添加-->query_cache_size = 128M

# 3.表示单个结果集所被允许缓存的最大值，结果比这个大，就不会被缓存,添加--->query_cache_limit = 1M (注意配置完后要重新启动数据库

#4.分配内存块时的最小单位大小；最合适的大小和应用程序查询结果的平均大小直接相关 query_cache_min_res_unit=4096

#5.如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回 query_cache_wlock_invalidate=OFF，这里如果是on表示不从缓存中返回数据，意味着缓存被清除

Show variables like '%query_cache%';

如果磁盘有数据更新，如何保证查询缓存中的数据是最新的呢？

查看缓存命中率：show status  like 'Qcache%'

 

 查看缓存结果：show status like ‘Qcache%’

Qcache_free_blocks:表示查询缓存中目前还有多少剩余的blocks，如果该值显示较大，则说明查询缓存中的内存碎片过多了，可能在一定的时间进行整理

Qcache_free_memory:查询缓存剩余的内存大小，通过这个参数可以知道当前系统的查询内存是否够用，可以根据实际情况做出调整

Qcache_hits:表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询缓存的效果。数字越大，缓存效果越理想,注意这个值越大越好

Qcache_inserts: 表示多少次未命中然后插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert到查询缓存中。这样的情况的次数越多，表示查询缓存应用到的比较少，效果也就不理想。当然系统刚启动后，查询缓存是空的，这很正常。注意这值越小越好

Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小，这值比较大说明缓存配置小了，就要去my.cnf中配置query_cache_size的值了

Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量，如果这个值比较大，说明有可能是缓存最大块的分配小了，可以将query_cache_limit调大后进行验证 

Qcache_queries_in_cache:当前缓存中缓存的查询数量 Qcache_total_blocks:当前缓存的block数量

存储引擎缓存-------------->（了解就行，一般调优都是查询缓存）

存储引擎层-innodb buffer pool  

buffer pool是innodb存储引擎带的一个缓存池，查询数据的时候，它首先会从内存中查询，如果内存中存在的话，直接返回，从而提高查询响应时间；

innodb buffer pool和qcache的区别是：

qcacche缓存的是sql语句对应的结果集，

buffer pool中缓存的是表中的索引，数据，插入数据时的缓冲。Buffer pool是设置的越大越好，一般设置为服务器物理内存的70%。

innodb buffer pool性能调优重要的一个参数，默认8M：

innodb_buffer_pool_size = 8M # 大小

在my.cnf中设置： innodb_buffer_pool_size = 512M

show VARIABLES like '%innodb_buffer%';

 

 五，事务和锁

事物 ：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)  一个完整的业务需要批量的sql(insert、update、delete)语句共同联合完成

一个事物:

start transaction

update table set id=1 where name = test

select * from table where id =1

commit;  只有执行了commit才能提交

两个事物：

update table set id=1 where name = test

select * from table where id =1

注：两个事物同时并行操作同一张表

有事务必然有锁，如事务中的update和insert行为

锁 MySQL大致可归纳为以下3种锁：

表级锁：开销小，加锁快；不会现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。一般sql语句中不加where条件或者where条件不是索引，就会锁表

行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。如果不会锁表，那么就要锁行，where条件有索引就要行锁

页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表锁： Update Score set id = xx

行锁： Update Score set id = xx where Stu_id=xx; 注：InnoDB这种行锁实现特点意味着：只有通过索引条件检索 数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 

共享锁： 共享锁又叫做读锁，所有的事务只能对其进行读操作不能写操作，加上共享锁后在事务结束之前其他事务只能再加共享锁

排他锁： 若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放 InnoDB引擎默认的修改数据语句，update,delete,insert都会自动给涉及到的数据加上排他锁，

select语句默认不会加任何锁类 型，如果加排他锁可以使用select ...for update语句 加共享锁可以使用select ... lock in share mode语句。

所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

死锁：  死锁是指两个或者多个事务在同一资源上互相占用，并且请求锁定对方占用的资源，从而导致死循环的现象

START TRANSACTION;

UPDATE Score set grade = 99 where id =7;

select SLEEP(10);

UPDATE Score set grade = 76 where id =8;

查看是否死锁： show engine innodb status;（把查询的日志写到文件中查比较方便，一般日志里有dblock等字样） 结果写文件：mysql -u root -p --execute="show engine innodb status G" > /tmp/mysql.log

查询死进程 show processlist;  压测过程出，这里查到死锁后，直接用kill 杀掉先释放了，再解决问题

KILL xxxx; 多线程并发才有可能死锁 避免交叉加锁 减少涉及的表，表联接会大大增加锁范围

 

 

六，Mysql慢查询

开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。开始慢查询的三个参数：

slow_query_log 慢查询开启状态

slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）

long_query_time 查询超过多少秒才记录 慢查询开启方法

方法一命令行设置

Set log_output=table；

set global slow_query_log='ON';

set global long_query_time=1; 

 

select * from mysql.slow_log; 查看慢查询(前提是开启了慢查询)

 

方法二，配置文件设置：

修改配置文件my.cnf

在[mysqld]下的下方加入

log_output=table 表示存到表里

slow_query_log = 1 或 on

long_query_time = 1 设置大于1秒的记录

查看慢查询相关参数：

show variables like 'slow_query%'; #查询是否开启

show variables like 'long_query_time'; #查询超时时间

show variables like '%log_output%';#查询存储类型

查看慢查询sql有三种类型:

运行慢查询sql

SELECT SLEEP(3);

查看慢sql

SELECT * FROM mysql.slow_log;

七，Explain 执行计划---->表示预处理，并不会真的执行

Explain :

该命令是查看查询优化器如何决定执行查询的主要方法

EXPLAIN select * from Score a JOIN class b on a.id = b.id where a.id in (SELECT b.id from Score a join class b on a.grade = b.grade where b.grade IN (SELECT a.grade from Score a)) GROUP BY a.stu_id order by b.id desc; Explain列解析：

Id： id相同由上向下执行，不同值越大优先级越高，越先被执行

select_type :示查询中每个select子句的类型（简单OR复杂）

Type :表示MySQL在表中找到所需行的方式，又称“访问类型” ALL, index, range, ref, eq_ref, const, system, NULL 从左到右，性能从最差到最好。 All MySQL将遍历全表以找到匹配的行。 Index index类型只遍历索引树。 range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。 ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。eq_ref类似ref，区别就在使用的索引是唯一索引 const,system,当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问 NULL，MySQL在优化过程中分解语句，执行时甚至不用访问

possible_keys ：指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引

Key :显示MySQL在查询中实际使用的索引，若没有使用，显示为NULL

key_len 表示索引中使用的字节数，可计算查询中使用的索引的长度

possible_keys 指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引

Ref 表示表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

Rows 表示根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

Extra 包含不适合在其他列中显示但十分重要的额外信息

a. Using index 该值表示相应的select操作中使用了覆盖索引

b. Using where 表示mysql服务器将在存储引擎检索行后再进行过

c. Using temporary 表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询

d. Using filesort 无法利用索引完成的排序操作称为“文件排序”

e. Using join buffer 改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。出现该值可能需添加索引来优

f. Impossible where 这个值强调了where语句会导致没有符合条件的行

h. Select tables optimized away 这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

I. Index merges 当MySQL 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型

八，优化

查询性能优化-------->

1，减少IO次数:

IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，也是收效最明显的优化手段。

减少IO次数 IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。

2，降低 CPU 计算

除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

MySQL优化基本原则--->

1、选取最适用的字段类型，尽量避免浪费

2、使用连接（JOIN）来代替子查询(Sub-Queries)

3、使用联合(UNION)来代替手动创建的临时表

4、减少排序

5、禁用外键

6、避免大sql

7、保持事物的短小精悍

8、避免大批量更新

9、避免取过量数据，灵活使用limit

10、避免在SQL 语句中进行数学运算、函数计算、逻辑判断等操作

11、避免OR

12、优先优化高并发的 SQL，而不是执行频率低某些“大”SQL

13、尽可能对每一条运行在数据库中的SQL进行explain

 14、任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。

15、使用索引查询。 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。