首页 > 代码库 > mysql优化总结
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)
mysql优化总结
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。