首页 > 代码库 > Mysql监控及优化

Mysql监控及优化

一、Mysql连接数

1、配置Mysql连接数:

  vim /etc/my.cnf [mysqld]下面修改

  max_connections=1000  不写默认为100。

  wait_timeout=60  设置超时时间

2、查看当前连接数:

  show status like ‘%Threads_connected%‘;

  技术分享

  show processlist;

  技术分享

 二、Mysql缓存

1、开启缓存:

  vim /etc/my.cnf mysqld下面添加或修改

  query_cache_type=on #开启缓存

  query_cache_size=10M #缓存总大小

  query_cache_limit=1M #查询结果超过设置值,就不会缓存

  需重启mysql服务生效。

2、查看缓存状态:

  SHOW VARIABLES LIKE ‘%query_cache%‘;

  技术分享

3、开启profile:

  set @@profiling=1;  设置profile开启

  select @@profiling;  查看profile是否开启

  show profiles;      查看所有的profile

  技术分享

  show profile for query  9;  查看指定的sql语句消耗的时间

  技术分享

  技术分享

  可以看出:同样的sql语句,第9条是首次查询消耗时间,耗时比较长,第10条是从缓存查询消耗时间,耗时明显比较短。

  注:1、select语句必须完全相同才会从走缓存,例如:大小写不一样,虽然查询结果一样,但是不会走缓存。

    2、任何一个包含不确定的函数(比如:now(),current_date())的查询不会被缓存。

4、queryCache使用状态:

  SHOW STATUS LIKE ‘Qcache%‘;

  技术分享

  Query Cache 命中率= Qcache_hits / ( Qcache_hits + Qcache_inserts );
  Qcache_free_blocks    Query Cache中目前还有多少剩余的blocks。如果该值显示较大,则说明Query Cache 中的内存碎片较多了,可能需要寻找合适的机会进行整理。如果这个值非常大,可以使用FLUSH QUERY CACHE;语句来清理查询缓存碎片以提高内存使用性能。该语句不从缓存中移出任何查询。

5、查询语句生命周期:

  1.Mysql服务器监听3306端口

  2.验证访问用户

  3.创建Mysql线程

  4.检查内存(qcache)

  5.解析SQL

  6.生成查询计划

  7.打开表

  8.检查内存(Buffer Pool)

  9.到磁盘读取数据

  10.写入内存

  11.返回数据给客户端

  12.关闭表

  13.关闭线程

  14.关闭连接

三、innodb 存储引擎

1、开启innodb_buffer_pool

  vim /etc/my.cnf mysqld 下面添加或修改

  innodb_buffer_pool_size=20M   #设置bufferpool大小

  innodb_buffer_pool_dump_now=on     #默认为关闭OFF。如果开启该参数,停止MySQL服务时,InnoDB将InnoDB缓冲池中的热数据保存到本地硬盘。

  innodb_buffer_pool_load_at_startup = on   #默认为关闭OFF。如果开启该参数,启动MySQL服务时,MySQL将本地热数据加载到InnoDB缓冲池中。

2、查看Innodb_buffer_pool状态

  SHOW VARIABLES LIKE ‘%innodb_buffer_pool%‘;

  技术分享

3、查询Innodb_buffer_pool当前使用情况:

  SHOW STATUS LIKE ‘%Innodb_buffer_pool%‘;

  技术分享

4、 自动提交

  set @@autocommit=0;  #关闭自动提交,仅对当前用户有效

  修改配置文件my.cnf在[mysqld]模块下加入autocommit = 0; #重启后永久生效

  select @@autocommit;  #查看自动提交是否开启

  commit;  #提交执行语句

5、锁:

  show processlist;  #查看当前锁定sql

  技术分享

  注:如果修改的数据条件是索引列,则是行级锁,否则就是表级锁。

四、慢查询

1、查询慢查询日志是否开启

  SHOW VARIABLES LIKE ‘%query%‘;

  技术分享

2、慢查询日志设置(执行命令)

  set global slow_query_log=on;  #开启慢查询日志

  set global long_query_time=1;  #设置记录查询超过多少秒的Sql存入慢查询

  set global slow_query_log_file=‘/opt/data/slow_query.log‘;  #设置慢查询日志路径,此路径需要有写入权限

3、解析慢查询日志

  -s:是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序,ac、at、al、ar,表示相应的倒叙;
  -t:是top n的意思,即为返回前面多少条的数据;
  -g:后边可以写一个正则匹配模式,大小写不敏感的;
 
  如:查询按照Sql查询时间最长的前10条sql

  mysqldumpslow -s t -t 10 -g ‘select‘  /opt/lampp/var/mysql/xiaoxitest-slow.log

  技术分享

4、使用EXPLAIN/DESC 查看Sql效率

  EXPLAIN update user set age=11 where id=1;

  DESC update user set age=11 where id=1;

  技术分享

  type列:依次从最差到最优

  all<index<range<ref<eq_ref<const,system<null

  ALL
  就是全表扫描,通常意味着MySQL必须扫描整张表,从头到尾,去找需要的行。对innodb表就是按主键顺序。
  Index
  联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。(也就是说虽然all和Index都是读全表,但index是从索引中读取的,而all是从硬盘中读的)。
  Range
  范围扫描就是一个有限制的索引扫描,它开始于索引里面的一个点,返回匹配整个值域的行。这比全索引扫描好一些,因为它用不着遍历全部索引。显而易见的是between或在where字句带有“>”或“<”的查询或者in。
  Ref
  非唯一性索引扫描,返回匹配某个单独值的所有行。
  Eq_ref
  使用这种索引查找,MySQL知道最多只返回一条符合条件的记录。这种访问方式在MySQL使用主键或唯一索引时可以看到。MySQL对于这类访问类型优化的非常好。
  Const,system
  当MySQL能对查询的某部分进行优化并将其转化成一个常量时,它就会使用这些类型访问。
  NULL
  这种访问方式意味着MySQL能在优化阶段分解查询语句,在执行阶段甚至用不着再访问表或者索引。
五、Mysql优化
1、读写分离
  主:只负责写数据
  从:只负责读数据
2、分布数据
  MySQL通常不会对带宽造成很大的压力。因此可以在不同的地理位置来分布数据,实现跨机房跨地域的数据分布。
3、负载均衡
  通过MySQL复制可以将读操作分布到多个服务器上,实现对读密集型应用的优化。
4、减少IO次数
  大部分数据库操作中90%时间都是IO操作所占用的,减少IO次数是Sql优化种第一优先考虑。也是收效最明显优化手段。
5、减少CPU计算
  SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后,降低 CPU 计算也就成为了我们 SQL 优化的重要目标。
6、改变 SQL 执行计划
  明确了优化目标之后,我们需要确定达到我们目标的方法。对于 SQL 语句来说,达到上述2个目标的方法其实只有一个,那就是改变 SQL 的执行计划,让他尽量“少走弯路”,尽量通过各种“捷径”来找到我们需要的数据,以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标。
7、为经常使用的查询条件创建索引

Mysql监控及优化