拿到某开发sql如下SELECTp.products_idFROMproductsASpJOINproducts_to_categoriesASpcUSING(products_id)JOINcategoriesAScUSING(categories_id)JOINproducts_r
拿到某开发sql如下
SELECT p.products_id FROM products AS pJOIN products_to_categories AS pc USING(products_id)JOIN categories AS c USING(categories_id)JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_modelWHERE products_status =1 AND categories_status =1 AND prq.msg != ‘Temporary out stock.’ORDER BY p.products_date_added DESC LIMIT 10
一般看到这种sql,在where中只有status类似的字段(可选择性非常低,数据两极分化非常明显)而且需要order by的语句,我们就应该想到使用force index(order_by_column)来进行优化.
explian
+—-+————-+——-+——–+——————————+——————-+———+——————————–+——–+———————————————-+| id | select_type | table | type | possible_keys| key| key_len | ref| rows | Extra|+—-+————-+——-+——–+——————————+——————-+———+——————————–+——–+———————————————-+| 1 | SIMPLE| pc | index | PRIMARY,categories_id| PRIMARY| 8| NULL| 1009510 | Using index; Using temporary; Using filesort || 1 | SIMPLE| p| eq_ref | PRIMARY,products_model| PRIMARY| 4| banggood_work.pc.products_id |1 | Using where|| 1 | SIMPLE| c| eq_ref | PRIMARY| PRIMARY| 4| banggood_work.pc.categories_id |1 | Using where|| 1 | SIMPLE| prq | ref | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa | 152| banggood_work.p.products_model |1 | Using where|+—-+————-+——-+——–+——————————+——————-+———+——————————–+——–+———————————————-+
发现mysql优化器选择了pc表的主键,虽然使用了索引,但是进行了全索引扫描，效果还是不理想!
强制使用force index后,explain
EXPLAIN-> SELECT p.products_id FROM products AS p FORCE INDEX(products_date_added)-> JOIN products_to_categories AS pc USING(products_id)-> JOIN categories AS c USING(categories_id)-> JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_model-> WHERE products_status =1 AND categories_status =1 AND prq.msg != ‘Temporary out stock.’-> ORDER BY p.products_date_added DESC LIMIT 10-> ;+—-+————-+——-+——–+——————————+———————+———+——————————–+——+————-+| id | select_type | table | type | possible_keys| key| key_len | ref| rows | Extra|+—-+————-+——-+——–+——————————+———————+———+——————————–+——+————-+| 1 | SIMPLE| p| index | NULL| products_date_added | 8| NULL| 1 | Using where || 1 | SIMPLE| prq | ref | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa | 152| banggood_work.p.products_model | 1 | Using where || 1 | SIMPLE| pc | ref | PRIMARY,categories_id| PRIMARY| 4| banggood_work.p.products_id | 1009 | Using index || 1 | SIMPLE| c| eq_ref | PRIMARY| PRIMARY| 4| banggood_work.pc.categories_id | 1 | Using where |+—-+————-+——-+——–+——————————+———————+———+——————————–+——+————-+
发现索引已经变成productsw_date_added,而执行时间，前者是2s,后者是0.003s.
这是我们一贯的优化方法,但是我们可以根据sql语句的特性和业务特性,结合临时表进行一些淫邪的优化，虽然并不通用，但是可以开阔sql优化者的思维。
我们可以看到这条语句是需要根据产品添加时间拿取符合(products_status =1 AND categories_status =1 AND prq.msg != ‘Temporary out stock.’)条件的10个最新上架产品.而我们知道，最新上架的产品一般状态都是不可能马上下架,而且对应的类别id也是可用,而且库存也是充足的(要不然何必上架),这个特性站到了99.9%以上.所以，我们利用这个特性，，先从产品表中找出不带任何条件的200个产品,放到临时表,然后再用临时表结果集,和拿取条件进行匹配,取出最新的10条.
AppMall应用商店
AI应用商店，提供即时交付、按需付费的人工智能应用服务
56 查看详情
(200条是一个参考值，根据各自的逻辑特性来取)
sql如下
SELECT DISTINCT p.products_id FROM(SELECT products_id,products_model,products_status,products_date_addedFROM productsORDER BY products_date_added DESC LIMIT 200) AS pJOIN products_to_categories AS pc USING(products_id)JOIN categories AS c USING(categories_id)JOIN products_realtime_quantity AS prq ON prq.sku_or_poa = p.products_modelWHERE products_status =1 AND categories_status =1 AND prq.msg != ‘Temporary out stock.’ORDER BY products_date_added DESC LIMIT 10;
explain后发现
| 1 | PRIMARY| | ALL | NULL| NULL| NULL | NULL| 200 | Using where; Using temporary; Using filesort || 1 | PRIMARY| prq| ref | ix_prg_sku_or_poa,ix_prq_msg | ix_prg_sku_or_poa | 152| p.products_model| 1 | Using where; Distinct|| 1 | PRIMARY| pc| ref | PRIMARY,categories_id| PRIMARY| 4| p.products_id| 1009 | Using index; Distinct|| 1 | PRIMARY| c| eq_ref | PRIMARY| PRIMARY| 4| banggood_work.pc.categories_id | 1 | Using where; Distinct|| 2 | DERIVED| products | index | NULL| products_date_added | 8| NULL| 200 ||
我们可以看到，已经利用products表中的products_date_added字段排序取出200条,整个sql语句变成一个只有200行的临时表驱动查询了，性能相对于原来的语句,提高上百倍！
执行时间大约是0.02s(可能比force index略慢)。
本文出自 “原下” 博客，请务必保留此出处
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/539735.html
微信扫一扫 
支付宝扫一扫 
