关于mysql优化的一些心得

2017年9月26日 290点热度 0人点赞 0条评论

新朋友请点上方图片 蓝字“阿铭linux”关注


本文来自“阿铭linux”12期学员。


先介绍下服务器架构及配置8核8G,10M带宽Centos6.5,64位,系统的架构为

Nginx 1.8.1,PHP  5.3.29,Mysql  5.5.42。


一电商网站后台查询订单时,经常php超时,导致php报错。以下是排查过程。


1、php执行超时,首先我们想到的就是php.ini文件中max_execution_time =  #把默认的值调大。


2、然后在后台执行订单查询php不报错了,但是查询耗时较长,用时65s。而且一些表成锁死状态碎片比较多,本人对mysql数据库优化不是很了解,于是请教了铭哥下,铭哥给出的答复是:一般mysql调优主要是根据慢查询日志去优化sql语句,my.cnf里面可调整的东西不多,而且效果也不明显。 下面就是调整参数,分析mysql慢查询日志。


3、mysql参数优化,主要调整的参数根据机器性能来调整,如果你对参数不是很了解,建议不要盲目的调。给大家一篇参数调整的文章 http://ask.apelearn.com/question/5758 (看第5条) 


把一些配置参数修改好后重启mysqld服务,由原来的65s变成了十几秒。效果还是不是很理想,查看了下mysql默认引擎为MyISAM,决定把引擎改为Innodb。具体操作过程如下:


4、导出shop数据库的表结构

mysqldump -d -uxxx -p shop > shop_table.sql

其中-d参数表示不导出数据,只导出表结构

5、替换shop_table.sql里的MyISAM为INNODB

sed -i 's/MyISAM/INNODB/g' shop_table.sql

6、新建数据库shop_new库,并导入表结构

mysql > create database shop_new;

mysql -uroot -p shop_new < shop_table.sql可以通过show table status来检查表引擎是否为INNODB。

7、导出shop的数据

mysqldump -t -uroot -p shop > shop_data.sql其中-t参数表示只导数据,不导表结构

8、导入数据到shop_new

mysql -uroot -p shop_new < shop_data.sql

9、 查看慢查询日志来定位mysql哪条语句执行慢,然后建立索引,优化sql执行语句。这台机器的慢查询日志片段,供大家参考

# Time: 160303 12:12:38

# User@Host: root[root] @  [10.165.34.182]

# Query_time: 10.145685  Lock_time: 0.000395 Rows_sent: 1  Rows_examined: 24306970

use shop;

SET timestamp=1456978358;

SELECT COUNT(*) FROM `shop`.`ecs_order_info` o LEFT JOIN`shop`.`ecs_users` u ON o.user_id = u.user_id LEFT JOIN `shop`.`ecs_affiliate_log` a ON o.order_id = a.order_id WHERE o.user_id > 0 AND (u.parent_id > 0 AND o.is_separate = 0 OR o.is_separate > 0);

# Time: 160303 12:12:44

# User@Host: root[root] @  [10.165.34.182]

# Query_time: 6.073441  Lock_time: 0.000152 Rows_sent: 15  Rows_examined: 24314767

SET timestamp=1456978364;

SELECT o.*, a.log_id, a.user_id as suid,  a.user_name as auser, a.money, a.point, a.separate_type,u.parent_id as up FROM `shop`.`ecs_order_info` o LEFT JOIN`shop`.`ecs_users` u ON o.user_id = u.user_id LEFT JOIN `shop`.`ecs_affiliate_log` a ON o.order_id = a.order_id WHERE o.user_id > 0 AND (u.parent_id > 0 AND o.is_separate = 0 OR o.is_separate > 0)  ORDER BY order_id DESC LIMIT 0,15;

通过慢日志发现其中有几个表查询耗时较长,下面就是把这个查询慢的表建立索引。用到的软件 NAvicat,对查询慢的表进行设计,增加索引。

图片

根据 explain  的解释,查看下  索引是否建立,一般都是 这样调整 就行。


图片

修改完后重启mysql 服务,查询时间从65s,缩短到 0.017407 秒


图片

- END -

出品 | 阿铭linux


图片

私人微信:81677956  验证:公众号   

图片

提升自己,才是世界上最稳健的投资

图片

21410关于mysql优化的一些心得

root

这个人很懒,什么都没留下

文章评论