MySQL Statement performance analysis (i)

1. Use the Explain statement to view the results of the analysis

such as explain select * from Test1 where id=1;

Appears: ID selecttype table type possible_keys key Key_len ref rows extra columns.

which

Type=const means that it was found by index one time;

Key=primary words, the use of the primary key;

Type=all, expressed as full-table scan;

Key=null indicates that the index is useless. Type=ref, because this is considered to be more than one matching row, in a federated query, it is generally ref.

Explain MySQL statement;

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 joins Wms_batch_item as Wmsbatchitem on WmsBat chitem.batch_id = wmsbatchbase.batch_id WHERE wmsbatchitem.sku_id = goodssku.sku_id), 0) as Qichupricefrom Goods_sku as Go Odssku INNER join Goods_base as goodsbase on goodssku.goods_id = goodsbase.goods_id left join Goods_category as Goodscateg Ory1 on goodscategory1.cat_id = Goodsbase.cat_id1 left JOIN goods_categOry as GoodsCategory2 on goodscategory2.cat_id = Goodsbase.cat_id2 left joins Goods_category as GoodsCategory3 on Goodscate gory3.cat_id = Goodsbase.cat_id3 left join Goods_unit as goodsunit in goodsunit.unit_id = goodsbase.unit_id LEFT JOIN sell Er_base as sellerbase on sellerbase.seller_id = goodsbase.seller_id left joins Goods_type_spec as GOODSTYPESPEC1 on Goodsty pespec1.spec_id = Goodssku.sku_spec1 left JOIN goods_type_spec as GoodsTypeSpec2 on goodstypespec2.spec_id = Goodssku.sku _SPEC2;

　　

You can then set the index of the data table based on this.

MySQL Statement performance analysis (i)