mysql--慢查询日志、mysqlsla和优化sql

优化sql：

一、使用explain分析低效的sql执行计划

摘自《深入浅出mysql》

1、通过慢查询日志和mysqlsla来定位效率较低的sql后

explain SELECT p.id AS ids,
               p.product_id AS productId,
               p.rfid1 AS rfidA,
               p.rfid2 AS rfidB,
               p.product_name AS productName,
               p.product_size AS productSize,
               p.market_id AS marketId,
               p.open_id AS openId,
               p.rent_status AS rentStatus,
               p.record_id AS recordId,
               p.product_desc AS productDesc,
               p.delivery_status AS deliveryStatus,
               p.create_time AS createTime,
               p.update_time AS updateTime,
               m.name AS name,
               c.category_name AS categoryName,
               pc.category_id AS categoryId,
               pc.id AS idm,
               pcl.cabinet_number AS cabinetNumbers,
               pcl.box_number AS boxNumbers,
               pcl.type,
               p.product_attr AS productAttr
FROM product p
LEFT JOIN product_category pc ON p.product_id=pc.product_id
LEFT JOIN category c ON c.category_id=pc.category_id
LEFT JOIN market m ON p.market_id=m.id
LEFT JOIN product_cabinet_limit pcl ON pcl.product_id=p.product_id
WHERE 1=1

mysql--慢查询日志、mysqlsla和优化sql

1、type参数

explain select * from cabinet; \\type:all
explain select id from cabinet; \\type:index key:id 遍历整个索引来查询所有的行
explain SELECT name from cabinet; \\ type:all
explain select id,name from cabinet c where c.id = 344;\\type:range key:primary 索引范围扫描
explain select b.id,b.open_id from box b where b.id = 28386 ; \\type:ref key:primary 非唯一索引
explain select id from wechat_user1 wu where wu.id = 1; \\type:const key:primary 主键或唯一索引
Impossible WHERE noticed after reading const tables
--- 没有发现匹配行

2、possible_keys 可能使用到的索引

3、key : 实际使用的索引

4、rows ：扫描行的数量

——————————————————————————————————————————————————

通过show profile分析sql执行计划：

select @@have_profiling
select count(1) from product;
show PROFILES;\\ query_id=62
show PROFILE for QUERY 62;\\查询执行过程中线程的每个状态和消耗时间

mysql--慢查询日志、mysqlsla和优化sql

sending data: 表示mysql线程开始访问数据行并把结果返回给客户端，而不仅仅是返回结果给客户端。

2、mysql支持进一步选择all，cpu，block io，context switch , page faults 等来查看mysql在使用什么资源上耗费了过高的时间。

show PROFILE cpu for QUERY 85;

————————————————————————————————————————————————————

todo

mysql--慢查询日志、mysqlsla和优化sql

相关推荐