mysql語句效能分析（一）

標籤：bsp   sel   type   ifnull   mysql   extra   wms   blog   tab   

1.使用explain語句去查看分析結果

如explain select * from test1 where id=1;

會出現：id  selecttype  table  type possible_keys  key key_len  ref rows  extra各列。

其中，

type=const表示通過索引一次就找到了；

key=primary的話，表示使用了主鍵；

type=all,表示為全表掃描；

key=null表示沒用到索引。type=ref,因為這時認為是多個匹配行，在聯集查詢中，一般為REF。

 

explain mysql語句；

eg:

EXPLAINSELECT GoodsSku.sku_id, GoodsSku.barcode, GoodsSku.sku_name, GoodsTypeSpec1.spec_name AS sku_spec1, GoodsTypeSpec2.spec_name AS sku_spec2, GoodsCategory1.cat_name AS catName1, GoodsCategory2.cat_name AS catName2, GoodsCategory3.cat_name AS catName3, GoodsUnit.sale_unit, SellerBase.seller_name, IFNULL((SELECT SUM(WmsBatchItem.sku_num) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id), 0) AS qichuNum, IFNULL((SELECT SUM(WmsBatchItem.sku_num * WmsBatchItem.sku_price) FROM wms_batch_base AS WmsBatchBase INNER JOIN wms_batch_item AS WmsBatchItem ON WmsBatchItem.batch_id = WmsBatchBase.batch_id WHERE WmsBatchItem.sku_id = GoodsSku.sku_id), 0) AS qichuPriceFROM goods_sku AS GoodsSku INNER JOIN goods_base AS GoodsBase ON GoodsSku.goods_id = GoodsBase.goods_id LEFT JOIN goods_category AS GoodsCategory1 ON GoodsCategory1.cat_id = GoodsBase.cat_id1 LEFT JOIN goods_category AS GoodsCategory2 ON GoodsCategory2.cat_id = GoodsBase.cat_id2 LEFT JOIN goods_category AS GoodsCategory3 ON GoodsCategory3.cat_id = GoodsBase.cat_id3 LEFT JOIN goods_unit AS GoodsUnit ON GoodsUnit.unit_id = GoodsBase.unit_id LEFT JOIN seller_base AS SellerBase ON SellerBase.seller_id = GoodsBase.seller_id LEFT JOIN goods_type_spec AS GoodsTypeSpec1 ON GoodsTypeSpec1.spec_id = GoodsSku.sku_spec1 LEFT JOIN goods_type_spec AS GoodsTypeSpec2 ON GoodsTypeSpec2.spec_id = GoodsSku.sku_spec2;

　　

 

 

然後可以根據這個來設定資料表的索引

 

mysql語句效能分析（一）