专业DBA级的MYSQL 优化

    今天刚刚跟公司的DBA 讨论有关网站总是响应慢的问题。DBA 用专业的工具找到了在一段时间内的‘慢查询’语句。其中有 20 多条语句存在严重的性能障碍。它们都在用各种各样的方式影响着我们的网站速度。

类似于。

Count: 3852  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  SELECT bonus_id FROM ecs_user_bonus WHERE bonus_type_id = N AND user_id = N AND used_time = N AND order_id = N  AND bonus_status = N ORDER BY bonus_id ASC LIMIT N  为业务进一步优化

Count: 3788  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0),
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  SELECT bonus_id FROM ecs_user_bonus WHERE bonus_type_id = N AND user_id = N LIMIT N 
  
  select bonus_id
  from 
  (SELECT bonus_id FROM ecs_user_bonus WHERE bonus_type_id = N AND user_id = N )b
  left join 
  ecs_user_bonus a
  on a.bonus_id=bonus_id
  where a.bonus_type_id=226 

Count: 2207  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  SELECT * FROM order_log  WHERE N ORDER BY editime desc LIMIT N,N		

Count: 2033  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0)
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  select user_id from ecs_users where mobile_phone=N and user_id<>N    改索引 KEY `mobile` (`mobile_phone`,user_id),

Count: 868  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  SELECT a.*,b.order_sn FROM order_log as a left join ecs_order_info as b on a.order_id=b.order_id  WHERE 1 ORDER BY a.editime desc LIMIT N,N		改业务实现

Count: 866  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  SELECT COUNT(*) FROM order_log as a left join ecs_order_info as b on a.order_id=b.order_id  WHERE N LIMIT N		改业务实现

Count: 668  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 
  # Query_time: N.N  Lock_time: N.N Rows_sent: N  Rows_examined: N
  SET timestamp=N;
  SELECT user_id, user_name, rea_name, pay_points, pointcard, mobile_phone  FROM `shop`.`ecs_users` 
  WHERE N  AND (user_name = 'S' or rea_name = 'S' or office_phone = 'S' or home_phone = 'S' or mobile_phone = 'S') ORDER by user_id DESC LIMIT N,N  改程序实现方式

初步沟通，DBA  建议我们从 PHP 语言 与 SQL 语句优化两方面进行优化。

在此记录下优化过程中应该注意点：

1：小表驱动大表。

2：注意联合索引的使用。

3：为每条语句加入 limit 结尾。

4：如果需要两个条件才能得到准确的值。对结果不是很多的查询，可用最快的语句（只用其中的一个条件）把结果先取到程序中，再用程序去遍布结果，取到准确的值。

5：where 条件语句中尽量不出现函数，表达式。

6：如遇到比须要用 or 的模糊查询时，可用 union all 连接两条语句。

7：少用 排序。

8：多看书，多学习，优化永无止境。。