今天刚刚跟公司的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:多看书,多学习,优化永无止境。。