如何分析mysql语句性能_mysql语句性能分析（一）

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:

EXPLAIN

SELECT

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 qichuPrice

FROM

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;

然后可以根据这个来设置数据表的索引