??????????????
????????mysql???????????????????DBA????????????????IO?????IO???????? ????????7s?????
????SQL??????￡?
????SELECT DISTINCT g.*?? cp.name AS cp_name?? c.name AS category_name?? t.name AS type_name FROMgm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;
???????????????
???????explain????м??????????￡?

????????sql???????????????????????
????????????????(?????????????????g.* )?????????????order by???????????????20???????
????DBA?????IO????????sql????????????????????????????￡???????????????????????IO?????
???????????????
??????????????·????sql????????????????????????????????
?????????????????????????????????????g.id????
????SELECT DISTINCT g.id FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;
????????????????????????????????????????sql???????in???????????????sql
????SELECT DISTINCT g.*?? cp.name AS cp_name??c.name AS category_name??t.name AS type_name FROMgm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 and g.id in(…………………) ORDER BY g.modify_time DESC ;
?????????Ч????
??????SATA????????????????????50s???????????0.3s???????0.1s????????????????????100???????IO?????????1%
??????SSD????????????????????7s???????????0.3s???????0.1s????????????????????10???????IO?????????1%
?????????????????????io???????????SSD????????SATA?????????????????????????SSD??SATA??????в??
???????????????
????MySQL?????SQL?????????????????????????￡???????????ζ?????????
?????????洢
????MySQL???????“????????”??“?????????”?????????????????MySQL??MEMORY?洢???棬????????????MySQL??MyISAM?洢???棻
???????????￡?MySQL?????????????????????????????????????????MySQL?????????????????????????
????Linux????????/tmp????/tmp??С????????????
????????????????
????1??ORDER BY????GROUP BY?????? ???磺ORDERY BY price GROUP BY name??
????2????JOIN????У?ORDER BY????GROUP BY????????????????? ???磺SELECT * from TableA?? TableB ORDER BY TableA.price GROUP by TableB.name
????3??ORDER BY???????DISTINCT????? ORDERY BY DISTINCT(price)
????4??SELECT??????????SQL_SMALL_RESULT????? SQL_SMALL_RESULT??????????MySQL????????С??????????????????????????????????? SQL_SMALL_RESULT?????GROUP BY??DISTINCT??DISTINCTROW?????? ???????￡???????б?????????????MySQL??????????ɡ?