关于mysql 一些优化心得

回复 收藏

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

Nginx   1.8.1PHP      5.3.29Mysql    5.5.42

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

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

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

mysql优化帖子  http://ask.apelearn.com/question/5758

把一些配置文件修改好后重启相关服务,由原来的65s变成了十几秒。效果还是不是很理想,查看了下mysql默认引擎为MyISAM,决定把引擎改为Innodb

1、导出shop数据库的表结构mysqldump -d -uxxx -p shop > shop_table.sql其中-d参数表示不导出数据,只导出表结构

2、替换shop_table.sql里的MyISAM为INNODBsed -i 's/MyISAM/INNODB/g' shop_table.sql3、新建数据库shop_new,并导入表结构mysql > create database shop_new;mysql -uroot -p shop_new < shop_table.sql可以通过show table status来检查表引擎是否为INNODB。

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

5、导入数据到shop_newmysql -uroot -p shop_new < shop_data.sql

6、 首先开启慢日志,修改/etc/my.cnf  增加以下两段配置,保存重启mysql

  1. vim /etc/my.cnf

  2. long_query_time = 2

  3. log_slow_queries = /data/mysql/slow.log

  4. service mysqld restart   ##重启mysql服务

7、查看慢日志来定位mysql哪条语句执行慢,然后建立索引,优化sql执行语句。

  1. <font color="Red">tail -n20 /data/mysql/slow.log   #查看20行</font>

  2. # Time: 160303 12:12:38

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

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

  5. use shop;

  6. SET timestamp=1456978358;

  7. 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);

  8. # Time: 160303 12:12:44

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

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

  11. SET timestamp=1456978364;

  12. 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,对查询慢的表进行设计,增加索引

0160304191200.png

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

QQ图片20160304202041.png

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

QQ截图20160304202346.png

参考了大量的网络资料,头一次搞优化。优化完后很有成就感,算是一次新的挑战

QQ图片20160304181535.png 0160304191200.png QQ图片20160304202041.png QQ截图20160304202346.png
2016-03-04 20:30 举报
已邀请:
0

balich

赞同来自:

谢谢分享
0

阿铭 管理员

赞同来自:

不错!
0

san liu

赞同来自:


谢谢分享
0

369666951

赞同来自:

学习了
0

boy461205160

赞同来自:

“替换shop_table.sql里的MyISAM为INNODB ”疑问把默认mysiam改成innodb对整个库来讲没有影响吗
0

陈龙

赞同来自:

很棒
0

渐行渐远

赞同来自:

很实际
0

Sniper

赞同来自:

表建立索引很重要哦
0

杭州小白

赞同来自:

学习
0

gxp2008

赞同来自:

学习下
0

阿凯

赞同来自:

学习
0

a1138665328

赞同来自:

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

a1138665328

赞同来自:

通过慢日志发现其中有几个表查询耗时较长,下面就是把这个查询慢的表建立索引 用到的软件 NAvicat,对查询慢的表进行设计,增加索引  从这一步开始是什么意思 那个软件怎么用
0

善思致用

赞同来自:

实践性的知识,问一下不更该默认引擎对查询慢的表建立索引行不行?
0

riverxyz

赞同来自:

同问,不更改默认引擎对查询慢的表建立索引行不行?
0

15801184208

赞同来自:

学习了 ,很透彻
0

wanmei

赞同来自:

不错
0

ouyang

赞同来自:

很实际。学以致用,希望有一天我也能这样撸头绪。然后更改成功。然后成就感大大的
0

leewillxlw

赞同来自:

厉害
0

张胤

赞同来自:

0

Ivan.pei

赞同来自:

很详细,谢谢分享

0

喀洛池

赞同来自:

很强

0

Yajun

赞同来自:

我擦   这好难啊   该让DBA 来干吧

0

loading丶

赞同来自:

i_f48.gif

0

谪仙

赞同来自:

i_f48.gif

回复帖子,请先登录注册

退出全屏模式 全屏模式 回复
评分
可选评分理由: