SQL优化目的:
降低响应时间
直接影响用户体验度
降低资源使用率
主要体现在IO和CPU上，网络、内存消耗
优化原则：
1.IN子查询改成JOIN
2.NOT IN子查询改成LEFT JOIN
3.消除无效子查询
4.禁用select *，只读取所需字段
5.LIMIT M, N 大分页修改成JOIN形式
6.大结果集 limit限制，where限制，分页读取
7.使用where xx IN ()，IN子句中元素建议小于200
8.多表Join时，注意比较字段类型一致，避免隐式类型转换
9.Where子句右值都用单引号括起，避免隐式类型转换
10.Update/delete根据索引删除，禁用Update/delete … limit N结构SQL
11.禁止对索引列进行数值计算
1.类型转化（避免隐式的类型转换）
mysql> desc tb_shop_order
-> ;
+----------------------+----------------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+----------------+------+-----+---------------------+-----------------------------+
| id | varchar(60) | NO | PRI | NULL | |
| gorder_id | varchar(60) | NO | MUL | NULL | |
| product_type | char(4) | NO | MUL | NULL | |
| order_time | timestamp | NO | MUL | 0000-00-00 00:00:00 | |
| order_status | tinyint(2) | NO | MUL | NULL | |
| order_amount | decimal(12,2) | NO | | 0.00 | |
| save_amount | decimal(12,2) | NO | | 0.00 | |
| pay_amount | decimal(12,2) | NO | | 0.00 | |
| logistics_amount | decimal(12,2) | YES | | NULL | |
| fee | decimal(12,2) | NO | | 0.00 | |
| original_pay_amount | decimal(12,2) | NO | | 0.00 | |
| cost_amount | decimal(12,2) | NO | | 0.00 | |
| buy_account_id | varchar() | NO | MUL | NULL | |
| pre_close_time | timestamp | NO | MUL | 0000-00-00 00:00:00 | |
| update_time | timestamp | NO | MUL | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| settle_up | tinyint(1) | NO | MUL | NULL | |
| success_time | timestamp | NO | | 0000-00-00 00:00:00 | |
| merchant_id | int(10) | NO | MUL | NULL | |
| merchant_name | varchar(100) | NO | MUL | NULL | |
| merchant_account | varchar(100) | YES | | NULL | |
| merchant_tel | varchar(60) | YES | | NULL | |
| merchant_order_id | varchar(100) | YES | MUL | NULL | |
| merchant_amount | decimal(12,2) | YES | | NULL | |
| activity_id | int(10) | YES | | NULL | |
| activity_type | tinyint(2) | YES | | NULL | |
| order_desc | varchar(1000) | YES | | NULL | |
| remark | varchar(500) | YES | | NULL | |
| can_deliver | tinyint(1) | YES | | NULL | |
| can_settle_up | tinyint(1) | YES | | NULL | |
| can_refund | tinyint(1) | YES | | NULL | |
| misc | varchar(14100) | YES | | NULL | |
| key_words | varchar(200) | YES | | NULL | |
| delivery_begin_time | timestamp | NO | MUL | 0000-00-00 00:00:00 | |
| delivery_end_time | timestamp | NO | MUL | 0000-00-00 00:00:00 | |
| relation_id | varchar(60) | YES | | NULL | |
| order_ip | varchar(60) | YES | | NULL | |
| closed_reason | varchar(60) | YES | | NULL | |
| status_desc | varchar(60) | YES | | NULL | |
| note | varchar(5120) | YES | | NULL | |
| refund_amount | decimal(12,2) | NO | | 0.00 | |
| distributor_order_id | varchar(100) | NO | MUL | | |
+----------------------+----------------+------+-----+---------------------+-----------------------------+
41 rows in set (0.00 sec)
定义: `buy_account_id` varchar(60) NOT NULL COMMENT '卡号',
mysql> explain select * from tb_shop_order where buy_account_id = 190000000033703177;
+----+-------------+---------------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | tb_shop_order | NULL | ALL | ix_order_accountid | NULL | NULL | NULL | 179981 | 10.00 | Using where |
+----+-------------+---------------+------------+------+--------------------+------+---------+------+--------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
改造之后：ref 走的是const，rows扫描的行数表少，不在是全表扫描了
mysql> explain select * from tb_shop_order where buy_account_id = '';
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | tb_shop_order | NULL | ref | ix_order_accountid | ix_order_accountid | 182 | const | 1418 | 100.00 | NULL |
+----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
原则：Where子句右值都用单引号括起，避免隐式类型转换！！！
2.字段计算(使用函数在字段上，不走索引)
mysql> explain select * from tb_shop_order where DATE_FORMAT (order_time,'%Y-%m-%d') = '2016-04-05';
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | tb_shop_order | NULL | ALL | NULL | NULL | NULL | NULL | 179981 | 100.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
改造之后：row 扫描的行数变少，不再是全表烧苗，而是范围扫描，而且
Extra Using index
这是性能很高的一种情况。当查询所需的数据可以直接从索引树中检索到时，就会出现。
mysql> explain select * from tb_shop_order where order_time > '2016-04-05 00:00:00' and order_time < '2016-04-05 23:59:59';
+----+-------------+---------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | tb_shop_order | NULL | range | ix_order_otime | ix_order_otime | 4 | NULL | 89 | 100.00 | Using index condition |
+----+-------------+---------------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)
3.避免全表扫描更新
(1) update XXX set batch = '2016-09-27 16:21:01'
where batch = '1970-01-01 08:00:30';
(2) delete from XXX where batch < '2016-09-26 16:21:00';
改造成根据主键批量更新:
select min(pk), max(pk) from table where batch = '1970-01-01 08:00:30';
Update … where batch = '1970-01-01 08:00:30' and pk >= min and pk < i
Update … where batch = '1970-01-01 08:00:30' and pk >= i and pk < j
Update … where batch = '1970-01-01 08:00:30' and pk >= j and pk < max
4.字段传值正确性
错误的拼接：
select * from tb_shop_order where order_time > CONCAT('2016-04-05 00:00:00','00:00:00') and order_time < CONCAT('2016-04-05 23:59:59','00:00:00');
select * from tb_shop_order where order_time > '2016-04-05 00:00:00' and order_time < '2016-04-05 23:59:59./';
5. in OR not in优化
mysql> explain select * from tb_shop_order where buy_account_id ='' and product_type in (select product_type from tb_shop_gorder );
+----+-------------+----------------+------------+------+----------------------------------+--------------------+---------+-------------------------------+-------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+----------------------------------+--------------------+---------+-------------------------------+-------+----------+----------------------------------------+
| 1 | SIMPLE | tb_shop_order | NULL | ref | ix_order_accountid,ix_order_prod | ix_order_accountid | 182 | const | 6 | 100.00 | NULL |
| 1 | SIMPLE | tb_shop_gorder | NULL | ref | ix_gorder_ptype | ix_gorder_ptype | 12 | lp.tb_shop_order.product_type | | 100.00 | Using index; FirstMatch(tb_shop_order) |
+----+-------------+----------------+------------+------+----------------------------------+--------------------+---------+-------------------------------+-------+----------+----------------------------------------+
2 rows in set, 1 warning (0.00 sec)
改造后：
mysql> explain select t1.* from tb_shop_order t1 INNER JOIN tb_shop_gorder t2 on t1.product_type = t2.product_type where t1.buy_account_id ='';
+----+-------------+-------+------------+------+----------------------------------+--------------------+---------+--------------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------------------------+--------------------+---------+--------------------+-------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ref | ix_order_accountid,ix_order_prod | ix_order_accountid | 182 | const | 6 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ref | ix_gorder_ptype | ix_gorder_ptype | 12 | lp.t1.product_type | 10333 | 100.00 | Using index |
+----+-------------+-------+------------+------+----------------------------------+--------------------+---------+--------------------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
mysql> explain select * from tb_shop_order where buy_account_id ='' and product_type not in (select product_type from tb_shop_gorder );+----+--------------------+----------------+------------+----------------+--------------------+--------------------+---------+-------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+----------------+------------+----------------+--------------------+--------------------+---------+-------+-------+----------+-------------+
| 1 | PRIMARY | tb_shop_order | NULL | ref | ix_order_accountid | ix_order_accountid | 182 | const | 6 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | tb_shop_gorder | NULL | index_subquery | ix_gorder_ptype | ix_gorder_ptype | 12 | func | 10333 | 100.00 | Using index |
+----+--------------------+----------------+------------+----------------+--------------------+--------------------+---------+-------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
改造后：
mysql> explain select t1.* from tb_shop_order t1 LEFT JOIN tb_shop_gorder t2 on t1.product_type = t2.product_type where t1.buy_account_id ='' and t2.product_type IS NULL;
+----+-------------+-------+------------+------+--------------------+--------------------+---------+--------------------+-------+----------+--------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+--------------------+-------+----------+--------------------------------------+
| 1 | SIMPLE | t1 | NULL | ref | ix_order_accountid | ix_order_accountid | 182 | const | 6 | 100.00 | NULL |
| 1 | SIMPLE | t2 | NULL | ref | ix_gorder_ptype | ix_gorder_ptype | 12 | lp.t1.product_type | 10333 | 100.00 | Using where; Not exists; Using index |
+----+-------------+-------+------------+------+--------------------+--------------------+---------+--------------------+-------+----------+--------------------------------------+
2 rows in set, 1 warning (0.00 sec)