记一次mysql优化
今天在技术经理的现场优化中，把一条需要40000ms的sql语句优化到9ms左右，将近提高4500多倍的速度
优化前：
SELECT `o`.* FROM `order` `o` INNER JOIN `virtual_order` `vo` ON `o`.`virtual_order_id` = `vo`.`id` INNER JOIN `user` `u` ON `o`.`user_id` = `u`.`id` LEFT JOIN `crm_department` `d` ON `o`.`salesman_department_id` = `d`.`id` LEFT JOIN `order_team` `ot` ON `o`.`id` = `ot`.`order_id` WHERE ((d.path like '1-46-%') OR (`d`.`id`=46) OR (`o`.`salesman_aid`=93) OR (`ot`.`administrator_id`=93)) AND (`vo`.`is_vest`=0) ORDER BY `vo`.`created_at` DESC LIMIT 20
40790ms
优化后
EXPLAIN SELECT o.*
FROM `order` `o` LEFT JOIN `crm_department` `d` ON `o`.`salesman_department_id` = `d`.`id` LEFT JOIN ( SELECT order_id FROM `order_team` Where `administrator_id`=93) as ot ON `o`.`id` = `ot`.`order_id`
WHERE o.is_vest=0 AND ((d.path like '1-46-%') OR (`d`.`id`=46) OR (`o`.`salesman_aid`=93))
ORDER BY `o`.`created_at` DESC LIMIT 20;
9.21ms
G
M
T
Detect languageAfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu | AfrikaansAlbanianArabicArmenianAzerbaijaniBasqueBelarusianBengaliBosnianBulgarianCatalanCebuanoChichewaChinese (Simplified)Chinese (Traditional)CroatianCzechDanishDutchEnglishEsperantoEstonianFilipinoFinnishFrenchGalicianGeorgianGermanGreekGujaratiHaitian CreoleHausaHebrewHindiHmongHungarianIcelandicIgboIndonesianIrishItalianJapaneseJavaneseKannadaKazakhKhmerKoreanLaoLatinLatvianLithuanianMacedonianMalagasyMalayMalayalamMalteseMaoriMarathiMongolianMyanmar (Burmese)NepaliNorwegianPersianPolishPortuguesePunjabiRomanianRussianSerbianSesothoSinhalaSlovakSlovenianSomaliSpanishSundaneseSwahiliSwedishTajikTamilTeluguThaiTurkishUkrainianUrduUzbekVietnameseWelshYiddishYorubaZulu |
|---|
Text-to-speech function is limited to 200 characters
Options : History : Feedback : Donate | Close |
|---|