数据库优化篇

最近遇到一个mysql 的 order by查询变慢问题，具体分析过程如下：

1. 分析sql语句：

 select t.*, if(t.check_state=1, '正常单', '错单') as state_name, if(t.network_type=1, '异网', '本网') as network_name, case t.order_type when 1000 then '装机订单' when 2000 then '撤单订单' else '' end as type_name, case t.cancel_state when 1 then '已撤单' when 2 then '待确认' else '' end as cancel_name, case t.capital_state when 0 then '未收回' when 1 then '已收回' when 2 then '无需收回' else '' end as capital_name, case t.reward_state when 1 then '正常待支付' when 2 then '正常已支付' when 3 then '撤单待支付' when 4 then '撤单已支付' else '未产生' end as reward_name, if(t.loan_state=1, '已放款', '未放款') as loan_name, if(t.invoice_state=1, '已导入', '未导入') as invoice_name, if(t.balance_state=1, '已创建', '未创建') as balance_name, case t.filed_state when 1 then '完结已归档' when 2 then '撤单已归档' else '未归档' end as filed_name, t1.overdue_state, case t1.overdue_state when 0 then '未逾期' when 1 then '已逾期' else '' end as overdue_name, case t.ransom_state when 0 then '未赎回' when 1 then '已赎回' else '' end as ransom_name from gms_order t left join gms_order_balance t1 on t.order_no=t1.order_no join gms_operator a on a.operator_id=t.operator_id where t.order_type=1000 order by t.finish_date limit 1000;

关键点：

a. t.* 问题；
b. 左关联了两个表
c. order by 语句

2. 简单优化，定制查询内容

select t.order_no,t.phone_no,t.prov_name,t.city_name,t.agent_name,t.user_name,t.id_card_no,t.credit_score,t.balance_amount, if(t.check_state=1, '正常单', '错单') as state_name, if(t.network_type=1, '异网', '本网') as network_name, case t.order_type when 1000 then '装机订单' when 2000 then '撤单订单' else '' end as type_name, case t.cancel_state when 1 then '已撤单' when 2 then '待确认' else '' end as cancel_name, case t.capital_state when 0 then '未收回' when 1 then '已收回' when 2 then '无需收回' else '' end as capital_name, case t.reward_state when 1 then '正常待支付' when 2 then '正常已支付' when 3 then '撤单待支付' when 4 then '撤单已支付' else '未产生' end as reward_name, if(t.loan_state=1, '已放款', '未放款') as loan_name, if(t.invoice_state=1, '已导入', '未导入') as invoice_name, if(t.balance_state=1, '已创建', '未创建') as balance_name, case t.filed_state when 1 then '完结已归档' when 2 then '撤单已归档' else '未归档' end as filed_name, t1.overdue_state, case t1.overdue_state when 0 then '未逾期' when 1 then '已逾期' else '' end as overdue_name, case t.ransom_state when 0 then '未赎回' when 1 then '已赎回' else '' end as ransom_name from gms_order t left join gms_order_balance t1 on t.order_no=t1.order_no join gms_operator a on a.operator_id=t.operator_id where t.order_type=1000 order by t.order_no limit 1000;

查询速度有所改善，但速度仍然很慢；

3. 分析关联表

a. alter table gms_order add index(order_no);
b. alter table gms_order_balance add index(order_no);
c. alter table gms_operator add index(operator_id);

三个表增加索引后，速度得到了极大提升；

4. 数据库层面优化

innodb_flush_log_at_trx_commit = 0，Innodb 中的Log Thread 没隔1 秒钟会将log buffer中的数据写入到文件，同时还会通知文件系统进行文件同步的flush 操作，保证数据确实已经写入到磁盘上面的物理文件。但是，每次事务的结束（commit 或者是rollback）并不会触发Log Thread 将log buffer 中的数据写入文件。所以，当设置为0 的时候，当MySQL Crash 和OS Crash 或者主机断电之后，最极端的情况是丢失1 秒时间的数据变更