mysql优化总结
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(60) | 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 = '190000000033703177'; +----+-------------+---------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+ | 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 ='190000000039541381' 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 | 10333 | 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 ='190000000039541381'; +----+-------------+-------+------------+------+----------------------------------+--------------------+---------+--------------------+-------+----------+-------------+ | 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 ='190000000039541381' 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 ='190000000039541381' 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)
Face your past without regret. Handle your present with confidence.Prepare for future without fear. keep the faith and drop the fear.
面对过去无怨无悔，把握现在充满信心，备战未来无所畏惧。保持信念，克服恐惧！一点一滴的积累，一点一滴的沉淀，学技术需要不断的积淀！
