首页 > 代码库 > mysql监控以及调优
mysql监控以及调优
mysql 优点：
简单易用，成本低，易扩展，复制功能领先
mysql的生命周期：
Mysql服务器监听3306端口>验证访问用户>创建mysql线程>检查内存（Qcache）>解析sql>生成查询计划>打开表>检查内存(Buffer PooL)>到磁盘取数据>写入内存>返回数据给客户端>关闭表>关闭线程>关闭连接
Mysql架构：
mysql连接器：客户端
mysql服务器：连接池；查询优化器（缓存）；存储引擎层（索引结构，缓存）；文件系统（file,log）
----------------------------------------------------------------------------------------------
Mysql连接数：
连接数少的情况下，在大并发下，会导致等待连接释放，造成数据库连接超时或者相应时间过长
mysql 的配置文件中 max_connections = 1000 最大链接数，不写默认是100
wait_timeout = 10 超时时间
查看 当前 有多少个连接
show status like ‘%Threads_connected%‘
show processlist
-----------------------------------------------------------------------------------------
mysql缓存
1.mysql层：查询缓存 Query Cache：存储select的完整结果，当有同样的查询，则跳过解析，优化和执行的阶段，直接返回结果，当这条缓存select语句相关的表有变化时，此缓存失效
开启可以提高性能，但是对于读写增加了开销，读的时候要先检查缓存，对于写，写入之后要更新缓存
但是一般这种开销比较小，但是也要根据业务特征权衡
win(my.ini) linux(my.cnf)
SHOW VARIABLES LIKE ‘%query_cache%‘ :
query_cache_type=on // on 开启off 关闭
query_cache_size=200M 一般建议不超过256M
query_cache_limit=1M 限制储存结果的大小，如果查出来的结果比这个大，就不会被缓存
查询quache当前使用情况：
SHOW STATUS LIKE ‘%Qcache%‘
2.存储引擎层：InnoDB_Buffer_Pool
他缓存的是整个表中的数据，越大越好，一般设置为服务器物理内存的70%
参数：
查看 InnoDB_Buffer_Pool ：
SHOW VARIABLES LIKE ‘%innoDB_buffer_pool%‘:
InnoDB_buffer_pool_size :大小
查询 InnoDB_buffer_pool 当前使用情况：
SHOW STATUS LIKE ‘%InnoDB_buffer_pool %‘
主要关注两个参数：
InnoDB_buffer_pool_read_requests 总共查询bp的次数
InnoDB_buffer_pool_reads 从物理硬盘中获取到数据的次数
通过这两个参数我们可以知道bp的命中率
------------------------------------------------------------------------------------------------------------------
mysql慢查询日志
可以用 mysqlslowdump 来分析慢查询日志
配置 mysql 慢查询
Linux:
在mysql配置文件my.cnf中增加：log-slow-queries=/opt/data/slowquery.log (指定日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log) long_query_time=2 (记录超过的时间，默认为10s) log-queries-not-using-indexes (log下来没有使用索引的query,可以根据情况决定是否开启)
Windows:
在my.ini的[mysqld]添加如下语句： log-slow-queries = E:\web\mysql\log\mysqlslowquery.log long_query_time = 2(其他参数如上)
set global long_query_time=1;#设置记录查询超过多长时间的sql
set global slow_query_log_file=‘/opt/data/slow_query.log’;#设置mysql慢查询日志路径，此路径需要有写权限
这种方式不用重启mysql服务。
使用这个语句可以看到当前mysql慢查询是否开启，以及mysql的慢查询日志文件在哪。
slow_query_log #是否开启慢查询
slow_query_log_file #日志的存放位置
long_query_time #超过多少秒的查询就写入日志
-----
Mysqldumpslow命令参数如下：
-s，是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
-t，是top n的意思，即为返回前面多少条的数据；
-g，后边可以写一个正则匹配模式，大小写不敏感的；
mysqldumpslow -s t -t 20 -g ‘select‘/opt/data/slowquery_2016050921.log
得到按照时间排序的前10条里面含有左连接的查询语句。
mysqldumpslow -s t -t 10 -g ‘left join‘/opt/data/slowquery_2016050921.log
该命令是查看查询优化器如何决定执行查询的主要方法，这个功能有局限性，只是一个近似结果，有时它是一个很好的近似，有时可能相差甚远。但它的输出是可以获取的最准确信息，值得仔细学习。
一个简单的执行计划
mysql> explain select * from students where id in (2,3);
1.explain不会考虑触发器、存储过程或函数对查询的影响。
2.explain不会考虑缓存对查询结果的影响
3.不会考虑mysql执行查询所做的特定优化
4.是基于统计信息的估算，并非精确值。
5.mysql5.6之前只支持对select进行explain
simple 它表示简单的select,没有union和子查询.
primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary
DERIVED 值表示包含在FROM字句的子查询中的SELECT，MySQL会递归执行并将结果放到一个临时表中。服务器内部称其“派生表”，因为该临时表是从子查询中派生来的
显示输出的行所引用的表名，如果是子查询的话，表名会显示derived
就是全表扫描，通常意味着MySQL必须扫描整张表，从头到尾，去找需要的行。对innodb表就是按主键顺序。
Index
联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。
Range
范围扫描就是一个有限制的索引扫描，它开始于索引里面的一个点，返回匹配整个值域的行。这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的是between或在where字句带有“>”或“<”的查询或者in。
Ref
非唯一性索引扫描，返回匹配某个单独值的所有行。
Eq_ref
使用这种索引查找，MySQL知道最多只返回一条符合条件的记录。这种访问方式在MySQL使用主键或唯一索引时可以看到。MySQL对于这类访问类型优化的非常好。
Const，system
当MySQL能对查询的某部分进行优化并将其转化成一个常量时，它就会使用这些类型访问。
NULL
这种访问方式意味着MySQL能在优化阶段分解查询语句，在执行阶段甚至用不着再访问表或者索引。
Key_len列：
该列显示了MySQL在索引里使用的字节数。
Ref列：
显示索引的哪一列被使用了
Rows列
MYSQL 认为必须检查的用来返回请求数据的行数
Extra
这一列包含的是不适合在其他列显示的额外信息。
一些比较重要的如下：
“using index”
此值表示MySQL将使用覆盖索引，以避免访问表。
“using where”
表示查询使用了where条件
“using temporary”
这意味着MySQL在对查询结果排序时会使用一个临时表。
“using filesort”
这意味着MySQL会对结果使用一个外部的索引排序，而不是按索引顺序从表里读取行。
--------------------------------------------------------------------------------------------------------
MySQL profile
开启 profile：
set @@profiling=1;
查看profile是否开启
select @@profiling;
1为开启，0为关闭
开启后只对当前session有效。
show profiles;可以看到当前已经被记录的sql
show profile cpu for query 1;#查看cpu消耗
show profile block io for query 1;#查看io消耗
show profile memory for query 1;#查看cpu
也可以一起写
show profile cpu ,block io for query 1
明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标。
尽量少 join
MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。
尽量少排序
排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。
对于MySQL来说，减少排序有多种办法，比如：
通过利用索引来排序的方式进行优化
减少参与排序的记录条数
非必要不对数据进行排序
Select * 一般都会造成全表扫描，显示所有列，select 需要的字段即可。
尽量用 join 代替子查询
虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。
尽量用 union all 代替 union：
union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。
禁用外键
----------------------------------------------
一个SQL只能在一个cpu上运行
高并发环境中，大SQL容易影响性能问题
可能一个大SQL把数据库搞死
拆分SQL
保持事物的短小精悍
即开即用，用完即关
无关操作踢出事务，减少资源占用
保持一致性的前提下，拆分事务
避免大批量更新
避开高峰
白天限制速度
加sleep
避免类型转换
避免取过量数据，建议使用limit
避免在SQL 语句中进行数学运算、函数计算、逻辑判断等操作
避免OR
同一字段，推荐in
不同字段，推荐union
优先优化高并发的 SQL，而不是执行频率低某些“大”SQL
从全局出发优化，而不是片面调整
尽可能对每一条运行在数据库中的SQL进行explain。
-------------------------------------------------------------
2.使用唯一索引，考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。
3.使用短索引。如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个CHAR(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。
4.利用最左前缀。在创建一个n 列的索引时，实际是创建了MySQL可利用的n 个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。
5.不要过度索引。不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍 过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表 的修改速度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左 索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
-------------------------------------------------
唯一索引，数据是唯一的时候加上唯一索引，也就是唯一约束，使用唯一索引查询效率要比普通索引和多列索引速度快
单列索引（普通索引），任何列上都可添加
多列索引（组合索引），如果where字句后面有多列，或者数据有重复的，这样需要加多列索引，尽量避免对组合索引的列进行更新，因为更新数据后索引也会重新维护。
注意：修改表结构会，索引会失效，需要重构索引
mysql监控以及调优