MySQL性能监控

MySQL数据库监控一般主要关注连接数、缓存、事务和锁、慢查询等几项指标。

1、MySQL连接数

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

# 重新设置数据库最大连接数

set global max_connections=200

# 查询数据库当前设置的最大连接数

show variables like '%max_connections%';

show global status like 'Max_used_connections'; #使用的最大连接数

 show status like 'Threads%';#使用的线程情况

2、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.分配给查询缓存的总内存，一般建议不超过物理内存的一半

query_cache_size = 128M

# 3.表示单个结果集所被允许缓存的最大值，结果比这个大，就不会被缓存query_cache_limit = 1M

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

#5.如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回  query_cache_wlock_invalidate=OFF

Show variables like '%query_cache%';

Query cache结果

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

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

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

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

Qcache_lowmem_prunes:该参数记录有多少条查询因为内存不足而被移除出查询缓存。通过这个值，用户可以适当的调整缓存大小

Qcache_not_cached: 表示因为query_cache_type的设置而没有被缓存的查询数量

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

Qcache_total_blocks:当前缓存的block数量

Show status like 'Qcache%';

• 存储引擎层-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%';

        

3、事物和锁

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

   start transaction

  update table set id=1 where name = test

  select * from table  where id =1

  commit

 两个事物：

update table set id=1 where name = test

select * from table  where id =1

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

锁
MySQL大致可归纳为以下3种锁：
表级锁：开销小，加锁快；不会现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

表锁：

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;
•查看是否死锁：

一般日志里有dblock等字样，如显示不全复制到文本编辑器或者输出到文件

 结果写文件：mysql -u root -p --execute="show engine innodb status \G" > /tmp/mysql.log

出现死锁处理，查询死进程

show processlist;     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;

方法二，配置文件设置：

修改配置文件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

SELECT * FROM mysql.slow_log;

• Explain 执行计划

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

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 决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型

五、查询优化原则

FROM <left_table>

ON <join_condition>

<join_type> JOIN <right_table>

WHERE <where_condition>

GROUP BY <group_by_list>

HAVING <having_condition>

SELECT

DISTINCT <select_list>

ORDER BY <order_by_condition>

LIMIT <limit_number

• 减少IO次数

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

• 降低 CPU 计算

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

六、MSQL优化基本原则

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不仅要保存数据，还要保存一下索引文件。