mysql最佳化---in型子查詢，exists子查詢，from 型子查詢

標籤：sts   post   ecshop   直接   4行   查詢最佳化   exist   0.00   explain   

in型子查詢引出的陷阱：（掃更少的行，不要暫存資料表，不要檔案排序就快）題: 在ecshop商城表中,查詢6號欄目的商品, (注,6號是一個大欄目)最直觀的: mysql> select goods_id,cat_id,goods_name from  goods where cat_id in (select cat_id from category where parent_id=6);誤區: 給我們的感覺是, 先查到內層的6號欄目的子欄目,如7,8,9,11然後外層, cat_id in (7,8,9,11)事實: 如, goods表全掃描, 並逐行與category表對照,看parent_id=6是否成立

原因: mysql的查詢最佳化工具,針對In型做最佳化,被改成了exists的執行效果.當goods表越大時, 查詢速度越慢.改進: 用串連查詢來代替子查詢 explain select goods_id,g.cat_id,g.goods_name from  goods as g inner join (select cat_id from category where parent_id=6) as t using(cat_id) \G內層 select cat_id from ecs_category where parent_id=6 ; 用到Parent_id索引, 返回4行+--------+| cat_id |+--------+|      7 ||      8 ||      9 ||     11 |+--------+    形成結果,設為t    *************************** 3. row ***************************           id: 2  select_type: DERIVED        table: ecs_category         type: refpossible_keys: parent_id          key: parent_id      key_len: 2          ref:         rows: 4        Extra:3 rows in set (0.00 sec)第2次查詢, t和 goods 通過 cat_id 相連, 因為cat_id在 goods表中有索引, 所以相當於用7,8,911,快速匹配上 goods的行.*************************** 2. row ***************************           id: 1  select_type: PRIMARY        table: g         type: refpossible_keys: cat_id          key: cat_id      key_len: 2          ref: t.cat_id         rows: 6        Extra:第1次查詢 :是把上面2次的中間結果,直接取回.*************************** 1. row ***************************           id: 1  select_type: PRIMARY        table: <derived2>         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 4        Extra:exists子查詢：題: 查詢有商品的欄目.按上面的理解,我們用join來操作,如下:mysql> select c.cat_id,cat_name from ecs_category as c inner join  goods as g on c.cat_id=g.cat_id group by cat_name; (見36)最佳化1:  在group時, 用帶有索引的列來group, 速度會稍快一些,另外,用int型 比 char型 分組,也要快一些.(見37)最佳化2: 在group時, 我們假設只取了A表的內容,group by 的列,盡量用A表的列,會比B表的列要快.(見38)最佳化3: 從語義上去最佳化select cat_id,cat_name from ecs_category where exists(select *from  goods where  goods.cat_id=ecs_category.cat_id) (見40)|       36 | 0.00039075 | select c.cat_id,cat_name from ecs_category as c innerjoin  goods as g on c.cat_id=g.cat_id group by cat_name              ||       37 | 0.00038675 | select c.cat_id,cat_name from ecs_category as c innerjoin  goods as g on c.cat_id=g.cat_id group by cat_id              ||       38 | 0.00035650 | select c.cat_id,cat_name from ecs_category as c innerjoin  goods as g on c.cat_id=g.cat_id group by c.cat_id              ||       40 | 0.00033500 | select cat_id,cat_name from ecs_category where exists(select * from  goods where  goods.cat_id=ecs_category.cat_id)              |from 型子查詢:注意::內層from語句查到的暫存資料表, 是沒有索引的.因為是一個臨時形成的結果。所以: from的返回內容要盡量少.

 

mysql最佳化---in型子查詢，exists子查詢，from 型子查詢