【mysql】一次有意思的数据库查询分析。

本文是在做一家汽车配件的电商网站时，大体情景是一个List.php页面，该页面分页列出部分配件并统计总数量用于分页。

当然该页面中也可以指定一下查询条件，如适配的车辆品牌、车系、排量、年份等，一件商品可能适配多种车辆。

所以建了个这么个表：

goods_chexings表

aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAfkAAADaCAIAAAAqm6yfAAAYAElEQVR4nO2dsa4kuXWG+6k2UijYqVaA5MCBpIWUCrKAiZQ5GExiDLzGBotN5gUkCNJdYd/Bj+BAwGhsYL3C6kKuvBy0VODlOSRPH7LqkP85H25Qxea9zf8/5F+snu7p23MHP/vlv//jP7/90c8///EvvvjkX774wU//4/s/+fTjTz79+Mef/sM/vf3uD//tO9/7V91f/vx2Y4+v/yPB5Pz2y69+8/s//Pp3XzZ/fvP7P/z2y6/S333/p//9+s/P+6x8/efn//rj/6QDfv369atXr34h4NWrV69fv15X7Js3b96+ffvhw4f/W5YPHz68ffv2zZs3p85/OZGAgV+++ebbP339l5l/vvnmW7di5Re2OaGXW1si64MgCPCJrA+CIMAnsj4IggCfyPogCAJ8IuuDIAjwiawPgiDAJ7I+CIIAn8j6IAgCfCLrgyAI8ImsD4IgwOf27nH++tlHy/0oZAZBEExFz/849PC+/t27d3/97KPtP3+10M+R9WdcLRcF2BBgaTqcGwIjP7I+sl4DsCHA0nQ4NwRGfmR9ZL0GYEOApelwbgiM/Mj6yHoNwIYAS9Ph3BAY+fZZf7vdaLzebje2fc6sv4+2rxCPIXk6ts+ooZ5nyOTSJEO6/Z1Kn7EA11r9d2znA0tzSGcLmXRfv1DWP5+5jIdzzQIwMWSqtT3DlACutYSp5sMd3ZAi6yPrNQCv/6nW9gxTArjWEqaaD3f8Zj37Ws1xC2ye8pWsz+7T2dt2tgPbh1L6O/SUpfQawnk3tjpDSo0VrpTWU5H6r9BGyQxhB4xR6xXll7RUWipDukCI8b7+9jLTj9PbxFmf1vj6g+y4BJ1q8t9tMtCQ0jgrzCaNfepmi7r6lNkM0dUaXn5zSGcLiazXZz1tydZ/dvEv9Xno77ADoJgsgMrT3RIkv1jBdm0/yyoibHl0hrBg1HoJ+XRIkfXF53OS9brCCztPG4iVpyv1KY2zgm3WCyuiiLZHo0H+l3u4rNZ48iPr89fradyz7fNk/fPLHRk9zvqkpH2y9uxP0eeiT1dC8nd66DTkmZhQGbatNEVFbi8R/p2KOXVdFxvyfE6tV5HP/uXSAB4a0gVCjPf18/+c91mqbLpkLTNzkiEz0NzHXQmdIddjWOsJ5c8wJB2R9ZZZ/1zeIin+wqjrv4Rr1r+5tCuftwQ7Q/BqXWJC+f3PbiUkst4y6xcF2BBgaTqcGwIjP7I+sl4DsCHA0nQ4NwRGfm/WP/qlH/TmZRXeBUEQrExX1j+6QbZObD09NuHx9PRkPYSzAJamw7khMPI7hSizftTor+EY8/v//jZ+7j/3eWM+jJAWhoT8h4ToY3DyrM+ei27S5S07RL11P7fbjZ035gN7dNhZ41Hr5aSdZAjbB8YQpPlQr1pFiD5Lf1rFNutpZGfHkpbdfdbTSfN+1gXQHHbamC3yhaSdZEipD4YhSPOhXrW6EH2czpz1O9nXZ+2R9fK5lbVMuACaw76frri2rzGE7YNkCMZ8EFatJESfpaUHps16dgD1lsj6Rdc/xtq+zBC2D5IhEvm0ZTb5wqrR+xh3WU/bm9EfWb/o+mfTPLK+4gM9RTIEQL582LTde9YL9/iR9VnLVAtAOOxbwnv3WU8NYfsgGdKUzybmbPKFVaPtjrKeHkuCfo+sX3b9swsg9vX1xkVr3S+/FJdzyq9UrT6x9VlaemCGrD+ufqWW20vYlt191tNNxPtZF0Bz2Ef70tLCkDPkZ2t/cvn1qlXqqI/T0gMzZP0onGc9+zPnAghpYUjIrwvRx2Dpgch67B+YBeBKWhjiWX5kfZvI+tK8MR9GSAtDQv5DQvQxOPlnqYYQWV+aN+bDCGlhSMh/SIg+BiPrff7ALABX0sIQz/LB/++zIUTWl+aN+TBCWhgS8h8Soo9BV/9/vXm15vmBWQCupIUhnuX3Zr2f76V6CoIgWJmurL/4e7Cs6LQJD2BDgKXpcG4IjPzerNd9v+22GnebrEcxEcCGAEvT4dwQGPnIWX+8/FJpOdrrLTD11kH9WcIQOuyskZ0PS0jT0TSE7QNjyKLzgRYoHWT2UjPbcqc36+vvuTTM+qyEbEtqVva7s9XbEPbqOL8hpYt6ukjS9uN4fmk6moaU+mAYsuh8oAXKjiU71zves770UGR9yqLrn92xTr62T6VuCNtnAzJk0fkwS9aXHjDP+o272UkfSg8i6+ssuv4XXdvnEVlPT5tZaS6/mfU06LKWO7BZL9nX02XPBsE2Qb1tWXT9V67upUe3RaTpqBvCnm5Ahiwqv571pZ60xXXWb+Kronm9bZlwAUigyzitrPOs3wpTfdFaS2jKp9q3CeRH1jcQZj17SlvM623LouufXbqxr683LlprCfL5kGIuv5718qCDzfqNvF7PtqTtlRbzehvCmja/IZJaLypNh3NDmvJvLzk6zPA+nHqICYMOOesHgjHdBwJsCLA0Hc4NgZEfWS8Cpt6jADYEWJoO54bAyI+sFwFT71EAGwIsTYdzQ2Dkw36Waiww9R4FsCHA0nQ4NwRGfmS9CJh6jwLYEGBpOpwbAiO/N+tpmteJrMcA2BBgaTqcGwIjP7JeBEy9RwFsCLA0Hc4NgZHfm/WK7yp5CoIgCC7n6qzveT4rOm3CA9gQYGk6nBsCI78363Wv4SwHTL1HAWwIsDQdzg2BkY+c9ccHheUt2eekj24w9daRWnFnCUPosLNGWuh9EWk6moawfWAMWXQ+0ALRQUr69Ga97j2XF5CV8NGW2eptCJ00+wqGsMPOrutp+3E8vzQdTUNKfTAMWXQ+0ALRY0mfPbKe7U9PMaa7mkXXP1vTydf2qdQNYfvsQIYsOh8kOX5F1pcemDDrSw81b4j2Cepty6KGLLq2zyOynp6ym/301Fw+HSFtlPSBzfr95YvvaeNDx3fM623LooaUbngrj+6LSNNRN4Q93YEMWVR+82okbEHO+gNJXSPrKyxqCC13eu13nvW7YDN0B8aQpnx2d2wuX7GFP0PI1FmflbB+6XMy3XUsakh9VUTWs42L1lqCfD6kmMuvR5O8D3jWZxdt9kqe7Wt26OmugLVofkPYYR/tlT7zS9Ph3JCmfDYfdmv5ihA7ScjUWT8QjOk+EGBDgKXpcG4IjPzIehEw9R4FsCHA0nQ4NwRGfmS9CJh6jwLYEGBpOpwbAiMf9rNUY4Gp9yiADQGWpsO5ITDyI+tFwNR7FMCGAEvT4dwQGPnI//fZQGDqPQpgQ4Cl6XBuCIz8yHoRMPUeBbAhwNJ0ODcERr7Bd5XUvjclCIIgOIers77n+azotAkPYEOApelwbgiM/N6s172GY/0tuw/zhPL9wqMANgRYmg7nhsDIR87641PC2SnbSH83PYWptw7qzxKG0GFnjWz1l5Cmo2kI2wfGkEXnAy1QNkhJ0G39Wa97z+XFBt2PUy9KB8fpbPU2hL0Wzm9I6RLOFp0uiQtGeDFNQ0p9MAxZdD7QAmXH9ZYUR1lPTyvX+cj6lEXXP1v3ydf2qdQNYftsQIYsOh9myfrSA9NmPXvv0/TLvN62LLr+F13b5xFZT0/ZzX56ai6/siU9jrMbFBprG3DWlzQLL5Kz1duWRdc/W+vI+uzYZ9YvJL9+Naq8gLFxExsz61nBzWP2CmFeb1smXAAS6DJOK+s86zfBZugOjCFN+ewO2lx+/WoUWf9C50NZz56a19uWRdd//eY3sp5tXLTWEuTzIcVcvjzNJZsY2KynL1qVru3s7x6n5vU2hLVofkMklV1Umg7nhjTl315ydJjhfTi0QPWqVeqImfUDwZjuAwE2BFiaDueGwMiPrBcBU+9RABsCLE2Hc0Ng5EfWi4Cp9yiADQGWpsO5ITDyYT9LNRaYeo8C2BBgaTqcGwIjP7JeBEy9RwFsCLA0Hc4NgZHfm/U0zetE1mMAbAiwNB3ODYGRH1kvAqbeowA2BFiaDueGwMjvzfr4XqogCIIluDrre57Pik6b8AA2BFiaDueGwMjvzXrdazjLAVPvUQAbAixNh3NDYOSDZ/3tdstO7zzUsgPVW0fmxr6IIXTYWaO3Wjs3pCmfbZlTPk227HR4HZXvubwGGuLZsaTlzpz1vgY6afYVDGGHnTZ6q7VzQ5rySy0Tyme3p+kpe4yc9XvhSr63kp22TFjvK1kx6/fC3sdJtLHUDSn1hDGkKZ81ZDb57CDrQXenN+tLD0yb9aXLe73PbPW+mMh6DCRZDzz5XWX9+NdwSg9Mm/W0/YJL4uoAZD2NeOdZz/rA9oQxRDgfZs760iBjX9+2oNRhB53uOgCyfk/+war+Ct4S0nTUDSn1hDGkKZ81ZDb57CAj64vrmb0PqvSfrd4Xg5H1WaPDWjs3pHkHw7bMKb+yrz+pjlNnPXv1K13GaWP6p+as9zUsakhlx+qz1s4NkchnW+aUX6/aGXWcOusHMme9DQE2BFiaDueGwMiPrBcBU+9RABsCLE2Hc0Ng5EfWi4Cp9yiADQGWpsO5ITDywT9LNQqYeo8C2BBgaTqcGwIjP7JeBEy9RwFsCLA0Hc4NgZEP/n+fjQKm3qMANgRYmg7nhsDIj6wXAVPvUQAbAixNh3NDYOQbfFdJ7XtTgiAIgnO4Out7ns+KTpvwADYEWJoO54bAyO/Net1rONbfsvswTyjfLzwKYEOApelwbgiMfOSsPz4onJ0ejbSF/lZq0zXDnpDMjW0RQ+iws0ZvtXZuSFM+22IuvzlCWrVKHfVZr3vP5Xm+sHYcyV7qQw+yY/N6G0InzbaCIeyws4t62n4czy9Nh3NDmvJLLbbymyOsBx2to9Osp52xp7uaFbN+47Y/m+9a1w0p9YQxpCmfNcRcfnNfn7WflfWlB8yzfqu+hpN1Y383PTWvty2R9RhIsn62je1AgLNefn8GmPUlwY8+ese83rYsaghb5cj67Njnvr4yH1bM+rQ9sl76KOujeb1tAcj6jdzYOc/6rXynu4Ea0pTPGmIuP7K+Qf3WTHKcYl5vWzCyPmuMrM8a4Q2RvFpLW8zly7eqTrN+I6/Xsy0bsWbOa7shrGnzG1LZsdJ9XNphfmk6nBsikc+2zPA+nHqICYMOOesHgjHdBwJsCLA0Hc4NgZEfWS8Cpt6jADYEWJoO54bAyI+sFwFT71EAGwIsTYdzQ2Dkw36Waiww9R4FsCHA0nQ4NwRGfmS9CJh6jwLYEGBpOpwbAiO/N+tpmteJrMcA2BBgaTqcGwIjP7JeBEy9RwFsCLA0Hc4NgZHfm/XxvVRBEARLcHXW9zyfFZ024QFsCLA0Hc4NgZHfm/W613CWA6beowA2BFiaDueGwMhHzvrjg8Lyluz/SDi6wdRbR2rFnSUMocPOGmmh90Wk6XBuSFM+22IuvzlCSdDt/Vmve8/lBWTK0xZ6UOlzx7zehtBJs69gCDvs7Lqeth/H80vT4dyQpvxSi6385ggrIbYPreOSWV/pUzrFmO5qVsz6vVBTJ9HGUjek1BPGkKZ81hBz+c19fdZ+VtaXHjDP+r36+gztWTndJ6i3LYsaElmfIcn62Ta2AwHOerrZH17HebNefmsjsdK83rYsaghb5cj67Njnvr4yH1bM+rTd3b5emPXNiX7HvN62LGoIu2M99jvOs34X3+nCGNKUzxpiLj+yvjUyQdbXLUsxr7ctixpSv5BH1meN8IYIN3Zr7evlL2DAZv3OvWhVuYyXDLpjXm9D2K3f/IZUdqx0AqQd5pemw7khEvlsywzvw6mEmLBlx876gWBM94EAGwIsTYdzQ2DkR9aLgKn3KIANAZamw7khMPIj60XA1HsUwIYAS9Ph3BAY+bCfpRoLTL1HAWwIsDQdzg2BkR9ZLwKm3qMANgRYmg7nhsDIR/6/zwYCU+9RABsCLE2Hc0Ng5EfWi4Cp9yiADQGWpsO5ITDyDb6rpPa9KUEQBME5XJ31Pc9nRadNeAAbAixNh3NDYOT3Zr3uNRzrb9l9mCeU7xceBbAhwNJ0ODcERj5y1h8fFC61ZP9HQtot+1Mw9daxqCF02FkjLf22iDQdzg1pymdbzOVn41HHWm/W695zebFB9+N6S/rQotF2Eosawg47WxJp+3E8vzQdzg1pyi+12MqvbEObscYKiawv/u4djOmuZlFD6DLeCisBL9pY6oaUesIY0pTPGmIu/9GsL532Zn3pgQmznp6ydzq0/zZBvW1Z1JDI+gxJ1s+2sR0IQNbTxvqVIAU267dClEv28otG23ksaghb08j67Njnvr4yH+bPekm++8p6KrsU9FtkfZVFDWF3rMe133nWb+X72g3UkKZ81hBz+fVXHSLrX6hlb822x206ZZQrsKghupvfJaTpcG5I8w6GbTGXX4+myPq/qS1dtGlj6bdSm64Z9mwsakhlx1qp/raCNB3ODZHIZ1tmeB/OoyG2ecv6gWBM94EAGwIsTYdzQ2DkR9aLgKn3KIANAZamw7khMPIj60XA1HsUwIYAS9Ph3BAY+bCfpRoLTL1HAWwIsDQdzg2BkR9ZLwKm3qMANgRYmg7nhsDI7816muZ1IusxADYEWJoO54bAyI+sFwFT71EAGwIsTYdzQ2Dk92Z9fC9VEATBElyd9T3PZ0WnTXgAGwIsTYdzQ2Dk92a97jWc5YCp9yiADQGWpsO5ITDykbP++OhwZ8sOVG8dmRv7IobQYWeN3mrt3JCmfLbFXH42HrZGkj69Wa97z+UFZDNY3XLHvN6G0Emzr2AIO+y00VutnRvSlF9qsZVPd6L0WNJnj6xvttzBmO5qVsz6ndvs7C9XCNtzCWk66oaUesIY0pTPGmIuX5LjV2R96YEJs770EL0qznZtNyeyHgNJ1gNPfoCsLzVK+sBm/Z6ktvwC6HD9SwDIehrxDmtdN6TUE8YQ4XyYP+t1LchZf1CqYmS9EICs38m132Gt64aUesIY0pTPGmIuX7GFZ/sgZ30W8fUJ7Xb9S8DI+qzRYa2dGyJJSdpiLr9Ul0f7gGd96aJNG0u/dce83oYsakhlx1qp/r6CNB3ODZHIZ1tmeB/OQyHG9tmxs34gGNN9IMCGAEvT4dwQGPmR9SJg6j0KYEOApelwbgiM/Mh6ETD1HgWwIcDSdDg3BEY+7GepxgJT71EAGwIsTYdzQ2DkR9aLgKn3KIANAZamw7khMPKR/++zgcDUexTAhgBL0+HcEBj5kfUiYOo9CmBDgKXpcG4IjHyD7yqpfW9KEARBcA5XZ33P81nRaRMewIYAS9Ph3BAY+b1Zr3sNx/pbdh/mCeX7hUcBbAiwNB3ODYGRj5z1x6eEs1O2sdJnA6q3jtSKO0sYQoedNdJCb4tI0+HckKZ8tsVcfjYeWqOHQkyf9br3XF5s0P049aJ5kGFeb0Po4t9WMIQddrYk0vbjeH5pOpwb0pRfarGVT2M9O663pDjKenravKQfYEx3NStm/Vaou5NoY6kbUuoJY0hTPmuIufxZsr70wLRZz977sLc/6V8zr7ctkfUYSLIeePIDZD3b+FCIAWZ9SbPkmLaY19sWgKynEe886yt3txuoIcL5MH/W94QYZtazgiPrFQBk/Uau/c6zfitvADdQQ5ryWUPM5bO3I+yp36xv7uDqt29T1dsWjKzPGh3W2rkhzTsYtsVcvjzNJSEGm/V0z1K6ttd/y7zehixqSGXHSvdxaYf5pelwbohEPtsyw/tw2DuPStUqdcTM+oFgTPeBABsCLE2Hc0Ng5EfWi4Cp9yiADQGWpsO5ITDyI+tFwNR7FMCGAEvT4dwQGPmwn6UaC0y9RwFsCLA0Hc4NgZEfWS8Cpt6jADYEWJoO54bAyO/NeprmdSLrMQA2BFiaDueGwMiPrBcBU+9RABsCLE2Hc0Ng5PdmfXwvVRAEwRJcnfU9z2dFp014ABsCLE2Hc0Ng5Pdmve41nOWAqfcogA0BlqbDuSEw8sGz/na7Zad3Ki3sL8LUWwf1ZwlD6LCzRrb6S0jTUTck+/z90QHGEKT5QJMtOx0uRPmey2ugsZ4dVx5dot7XwF4L5zekdAlPoy1tP47nl6bjIUNSMAyRy59/7bMb1vSUPUbO+r08fStZTx/ap6z3layY9Xth7+M26/dHDEmBMQRDPhtQ9aC705v1pQemzfrmKzaR9ZTIegyahiz0IoYCDPnCrB//Gk7pgWmznrZXdvcHs9X7YgCyvn6r7jDr6/tZSEMA5kOparGvb1tQuh5mV8Wp6n09AFm/k8ousbbHUjek1BPGEIz5wFbtgqz/fwRn4WFE28CJAAAAAElFTkSuQmCC" alt="" />

列名依次为：车辆厂牌id，车系id，排量id，车型id（该车型的说法不太规范，客户给的数据里边就叫车型，就这么将就着用了，实际是年份的字符串如哪年开始到哪年结束），配件的id（该id源于good表）。

车辆厂牌id，车系id，排量id，车型id存在着层级关系，层级为：车辆厂牌id -> 车系id -> 排量id -> 车型id，表这么建算是为了方便查找吧，有时候只指定到厂牌或者车系，让你列出来适配的配件。

。。。冗余，冗余哈，查找方便。

上PHP后端代码哈：

查找时候的where子句：

         //carBrand  //品牌

        //carXi //车系

        //carXing//排量 //命名不规范。。。凑合看一下吧

        //carYear//车型

        // $carBrand,$carXi,$carXing,$carYear

       	$carBrand = intval($_GET['carBrand']);

        if(!empty($carBrand))

        {

             $where .=' and (E.changpai_id ='.$carBrand. ' or E.changpai_id =0 )';

        }

        $carXi = intval($_GET['carXi']);

        if(!empty($carXi))

        {

             $where .=' and (E.chexi_id ='.$carXi. ' or E.chexi_id =0 )';

        }

       	$carXing = intval($_GET['carXing']);

        if(!empty($carXing))

        {

            $where .=' and (E.pailiang_id ='.$carXing. ' or E.pailiang_id =0 )';

        }

        $carYear = intval($_GET['carYear']);

        if(!empty($carYear))

        {

            $where .=' and (E.chexing_id ='.$carYear. ' or E.chexing_id =0 )';

        }

然后select的主体

	$goods_sql = 'select distinct(A.goods_id), A.name,A.pics,D.cat_name '

		        . '  from goods A left '

                         .'  left join goods_cat D  on A.goods_top_id=D.cat_id ' ;

                         . 'left join goods_chexings E 	on A.goods_id = E.good_id  '

                          .$where.' order by '.$sort_by.' limit '.$limit;

大体说下goods表和goods_chexings表的数量级：goods几千条，goods_chexings几万条。

但是List.php页面,如果什么条件都不指定的话，Waiting Time大约得10s，亮了。。。。

如果随便指定个车系、厂牌啥的，Waiting Time大约也就零点几秒的，也亮了。。。。

补充一下,goods_chexings表上有索引的。。。。

看到这能猜到时间差距为啥那么大了吧。。。。不指定查询条件时join goods_chexings根本就没用，只查询goods表就行了。

没指定查询条件 goods_chexings表上有索引根本就不起作用，全表扫描。。。。

修改后的查询：

	$goods_sql = 'select distinct(A.goods_id), A.name,A.pics,D.cat_name '

		     .' from goods A left '

                     .' left join goods_cat D  on A.goods_top_id=D.cat_id ' ;

	if(!(empty($carBrand) && empty($carXi) && empty($carXing) && empty($carYear) ))

        {

            $sql .= 'left join mega_good_chexi E 	on A.goods_id = E.good_id ';

        }

         $sql.= $where.' order by '.$sort_by.' limit '.$limit;

备注：写统配的sql语句写习惯了，不管查询的时候起不起作用，可能使用到的表先from或者join了，

　　如果出现要查询的条件时就加在where子句里边。。。。。引起的索引不起作用，全表扫描了。

用到的mysql的分析命令：EXPLAIN extended （SQL语句）。

巴特西

【mysql】一次有意思的数据库查询分析。

最新文章

热门文章