Бавен SQL израз
3 отговора
245 прегледа
Бавен SQL израз
Здравейте,
имам една таблица, в която записвам информацията за камиони
Външните таблици към нея са за колoните:
tType, tCountry, tModel, tCabin, tEkoNorm, tSpeedBox, tHang, tFuel, tAxisConfig, tValute
Всяка от тези външи таблици има структура като тази:
Като изключение прави само таблицата Model:
в която записвам кой модел към коя марка се отнася. За това и в главната таблица няма поле за марка, защото чрез модела мога да разбера коя е марката.
В основната, както и в релативните таблици има по 5-6 реда. И въпреки това заявката се изпълнява за 3-7 секунди !
Ето и самата заявка, ако на някой му се занивава да я чете, мисля че е свързана правилно.
SELECT tID, tUserID, tType, ttName AS tTypeName, tCountry, cName AS tCountryName, tmID AS tMark, tmName AS tMarkName, tModel, tmModelName AS tModelName, tCabin, tcName AS tCabinName, tSupStruct, tEkoNorm, tenName AS tEkoNormName, tSpeedBox, tsbName AS tSpeedBoxName, tHang, thName AS tHangName, tFuel, tfName AS tFuelName, tAxisConfig, tacName AS tAxisConfigName, tAxisCount, tTirePCAxis, tTireCol, tLPacket, tPower, tWeight, tUsefulLoad, tFirstReg, tKM, tPrice, tValute, vSig AS tValuteName, tInfo, tPics, tTimeAdd, DATE_FORMAT(tTimeAdd, "%H:%i %d.%m.%Y") AS tTimeAddF
FROM Truck, TruckType, TruckMark, TruckModel, TruckCabin, TruckEkoNorm, TruckSpeedBox, TruckHang, TruckFuel, TruckAxisConfig, Country, Valute, ValuteRates
WHERE tType = ttID AND tCountry = cID AND (tModel = tmModelID AND tmMarkID = tmID) AND tCabin = tcID AND tEkoNorm = tenID AND tSpeedBox = tsbID AND tHang = thID AND tFuel = tfID AND tAxisConfig = tacID AND (tValute = vID AND tValute = vrID) AND tUserID = 27 ORDER BY tID LIMIT 0, 10;
Експлайна:
Truck - основната таблица, се сканира цялата, защото всички редове в нея са към един user и индекса не се използва.
Не мога да разбера защо таблици като TruckFuel се сканират целите, докато например TruckCabin не.
Изобщо не мога да зацепя защо е толкова бавен, експлайна е добър, като изключим двата скана извън основната таблица.
Имам и още една таблица, с много подобна структура, дори се взимат доста повече данни и то свързани по по-сложен начин отколкото тук, но принципно е почти същото, за която нямам никакви проблеми, и си върви като пушка.
Какво му е грешното, дето не го виждам ?
MySQL 3.23.49a-Max, InnoDB
Поздрави ;)
имам една таблица, в която записвам информацията за камиони
mysql> describe Truck;
+-------------+-----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------------------+------+-----+---------------------+-------+
| tID | mediumint(8) unsigned | | PRI | 0 | |
| tUserID | smallint(5) unsigned | | MUL | 0 | |
| tType | tinyint(3) unsigned | | | 0 | |
| tCountry | tinyint(3) unsigned | | | 0 | |
| tModel | smallint(5) unsigned | | | 0 | |
| tCabin | tinyint(3) unsigned | | | 0 | |
| tSupStruct | tinyint(3) unsigned | | | 0 | |
| tEkoNorm | tinyint(3) unsigned | | | 0 | |
| tSpeedBox | tinyint(3) unsigned | | | 0 | |
| tHang | tinyint(3) unsigned | | | 0 | |
| tFuel | tinyint(3) unsigned | | | 0 | |
| tAxisConfig | tinyint(3) unsigned | | | 0 | |
| tAxisCount | tinyint(3) unsigned | | | 0 | |
| tTirePCAxis | tinyint(3) unsigned | | | 0 | |
| tTireCol | decimal(4,2) | | | 0.00 | |
| tLPacket | enum('N','Y') | | | N | |
| tPower | smallint(5) unsigned | | | 0 | |
| tWeight | tinyint(3) unsigned | | | 0 | |
| tUsefulLoad | tinyint(3) unsigned | | | 0 | |
| tFirstReg | varchar(4) | | | | |
| tKM | mediumint(8) unsigned | | | 0 | |
| tPrice | mediumint(8) unsigned | | | 0 | |
| tValute | tinyint(3) unsigned | | | 0 | |
| tInfo | text | | | | |
| tPics | tinyint(3) unsigned | | | 0 | |
| tTimeAdd | datetime | | | 0000-00-00 00:00:00 | |
+-------------+-----------------------+------+-----+---------------------+-------+
26 rows in set (0.00 sec)
Външните таблици към нея са за колoните:
tType, tCountry, tModel, tCabin, tEkoNorm, tSpeedBox, tHang, tFuel, tAxisConfig, tValute
Всяка от тези външи таблици има структура като тази:
mysql> describe TruckEkoNorm;
+---------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| tenID | tinyint(3) unsigned | | PRI | 0 | |
| tenName | varchar(30) | | | | |
+---------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
Като изключение прави само таблицата Model:
mysql> describe TruckModel;
+-------------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------------------+------+-----+---------+-------+
| tmModelID | smallint(5) unsigned | | PRI | 0 | |
| tmMarkID | tinyint(3) unsigned | | | 0 | |
| tmModelName | varchar(30) | | | | |
+-------------+----------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
в която записвам кой модел към коя марка се отнася. За това и в главната таблица няма поле за марка, защото чрез модела мога да разбера коя е марката.
В основната, както и в релативните таблици има по 5-6 реда. И въпреки това заявката се изпълнява за 3-7 секунди !
Ето и самата заявка, ако на някой му се занивава да я чете, мисля че е свързана правилно.
SELECT tID, tUserID, tType, ttName AS tTypeName, tCountry, cName AS tCountryName, tmID AS tMark, tmName AS tMarkName, tModel, tmModelName AS tModelName, tCabin, tcName AS tCabinName, tSupStruct, tEkoNorm, tenName AS tEkoNormName, tSpeedBox, tsbName AS tSpeedBoxName, tHang, thName AS tHangName, tFuel, tfName AS tFuelName, tAxisConfig, tacName AS tAxisConfigName, tAxisCount, tTirePCAxis, tTireCol, tLPacket, tPower, tWeight, tUsefulLoad, tFirstReg, tKM, tPrice, tValute, vSig AS tValuteName, tInfo, tPics, tTimeAdd, DATE_FORMAT(tTimeAdd, "%H:%i %d.%m.%Y") AS tTimeAddF
FROM Truck, TruckType, TruckMark, TruckModel, TruckCabin, TruckEkoNorm, TruckSpeedBox, TruckHang, TruckFuel, TruckAxisConfig, Country, Valute, ValuteRates
WHERE tType = ttID AND tCountry = cID AND (tModel = tmModelID AND tmMarkID = tmID) AND tCabin = tcID AND tEkoNorm = tenID AND tSpeedBox = tsbID AND tHang = thID AND tFuel = tfID AND tAxisConfig = tacID AND (tValute = vID AND tValute = vrID) AND tUserID = 27 ORDER BY tID LIMIT 0, 10;
Експлайна:
+-----------------+--------+---------------+---------+---------+---------------------+------+---------------------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-----------------+--------+---------------+---------+---------+---------------------+------+---------------------------------------------+
| Truck | ALL | user | NULL | NULL | NULL | 6 | where used; Using temporary; Using filesort |
| TruckModel | eq_ref | PRIMARY | PRIMARY | 2 | Truck.tModel | 1 | |
| TruckMark | eq_ref | PRIMARY | PRIMARY | 1 | TruckModel.tmMarkID | 1 | |
| TruckFuel | ALL | PRIMARY | NULL | NULL | NULL | 1 | where used |
| Country | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tCountry | 1 | |
| TruckEkoNorm | ALL | PRIMARY | NULL | NULL | NULL | 5 | where used |
| TruckSpeedBox | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tSpeedBox | 1 | |
| TruckHang | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tHang | 1 | |
| TruckType | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tType | 1 | |
| TruckAxisConfig | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tAxisConfig | 1 | |
| TruckCabin | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tCabin | 1 | |
| Valute | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tValute | 1 | |
| ValuteRates | eq_ref | PRIMARY | PRIMARY | 1 | Truck.tValute | 1 | Using index |
+-----------------+--------+---------------+---------+---------+---------------------+------+---------------------------------------------+
13 rows in set (2.70 sec)
Truck - основната таблица, се сканира цялата, защото всички редове в нея са към един user и индекса не се използва.
Не мога да разбера защо таблици като TruckFuel се сканират целите, докато например TruckCabin не.
Изобщо не мога да зацепя защо е толкова бавен, експлайна е добър, като изключим двата скана извън основната таблица.
Имам и още една таблица, с много подобна структура, дори се взимат доста повече данни и то свързани по по-сложен начин отколкото тук, но принципно е почти същото, за която нямам никакви проблеми, и си върви като пушка.
Какво му е грешното, дето не го виждам ?
MySQL 3.23.49a-Max, InnoDB
Поздрави ;)
Re: Бавен SQL израз
И аз не виждам нищо грешно....
| Truck | ALL | user | NULL | NULL | NULL | 6 | where used; Using temporary; Using filesort |
Колко temporary и колко сортира?
Имай предвид, че това се отнася за резултата от заявката а не за таблицата Truck
Направи следния тест.
CREATE TABLE test_size
SELECT tID, tUserID, tType, ttName AS tTypeName, tCountry, cName AS tCountryName, tmID AS tMark, tmName AS tMarkName, tModel, tmModelName AS tModelName, tCabin, tcName AS tCabinName, tSupStruct, tEkoNorm, tenName AS tEkoNormName, tSpeedBox, tsbName AS tSpeedBoxName, tHang, thName AS tHangName, tFuel, tfName AS tFuelName, tAxisConfig, tacName AS tAxisConfigName, tAxisCount, tTirePCAxis, tTireCol, tLPacket, tPower, tWeight, tUsefulLoad, tFirstReg, tKM, tPrice, tValute, vSig AS tValuteName, tInfo, tPics, tTimeAdd, DATE_FORMAT(tTimeAdd, "%H:%i %d.%m.%Y") AS tTimeAddF
FROM Truck, TruckType, TruckMark, TruckModel, TruckCabin, TruckEkoNorm, TruckSpeedBox, TruckHang, TruckFuel, TruckAxisConfig, Country, Valute, ValuteRates
WHERE tType = ttID AND tCountry = cID AND (tModel = tmModelID AND tmMarkID = tmID) AND tCabin = tcID AND tEkoNorm = tenID AND tSpeedBox = tsbID AND tHang = thID AND tFuel = tfID AND tAxisConfig = tacID AND (tValute = vID AND tValute = vrID) AND tUserID = 27;
И разгледай внимателно какво представлява въпросната таблица като структура. Това е приблизително временната таблица, която MySQL използва за да ти обработи заявката.
Пробвай
SELECT test_size RDER BY tID;
със и без LIMIT r ще се ориентираш за каков става въпрос и откъде идва забавянето.
> Не мога да разбера защо таблици като TruckFuel се сканират целите,
> докато например TruckCabin не.
Зависи от броя на редовете. При малко на брой редове индексите са по бавни и няма смисъл да се използват.
13 rows in set (2.70 sec)
> Изобщо не мога да зацепя защо е толкова бавен, експлайна е добър, като
> изключим двата скана извън основната таблица.
Ееххх ... а беше време когато хората се радваха когато такава заявка приключи за по-малко от 2 часа ...
salle.online.bg/sig
| Truck | ALL | user | NULL | NULL | NULL | 6 | where used; Using temporary; Using filesort |
Колко temporary и колко сортира?
Имай предвид, че това се отнася за резултата от заявката а не за таблицата Truck
Направи следния тест.
CREATE TABLE test_size
SELECT tID, tUserID, tType, ttName AS tTypeName, tCountry, cName AS tCountryName, tmID AS tMark, tmName AS tMarkName, tModel, tmModelName AS tModelName, tCabin, tcName AS tCabinName, tSupStruct, tEkoNorm, tenName AS tEkoNormName, tSpeedBox, tsbName AS tSpeedBoxName, tHang, thName AS tHangName, tFuel, tfName AS tFuelName, tAxisConfig, tacName AS tAxisConfigName, tAxisCount, tTirePCAxis, tTireCol, tLPacket, tPower, tWeight, tUsefulLoad, tFirstReg, tKM, tPrice, tValute, vSig AS tValuteName, tInfo, tPics, tTimeAdd, DATE_FORMAT(tTimeAdd, "%H:%i %d.%m.%Y") AS tTimeAddF
FROM Truck, TruckType, TruckMark, TruckModel, TruckCabin, TruckEkoNorm, TruckSpeedBox, TruckHang, TruckFuel, TruckAxisConfig, Country, Valute, ValuteRates
WHERE tType = ttID AND tCountry = cID AND (tModel = tmModelID AND tmMarkID = tmID) AND tCabin = tcID AND tEkoNorm = tenID AND tSpeedBox = tsbID AND tHang = thID AND tFuel = tfID AND tAxisConfig = tacID AND (tValute = vID AND tValute = vrID) AND tUserID = 27;
И разгледай внимателно какво представлява въпросната таблица като структура. Това е приблизително временната таблица, която MySQL използва за да ти обработи заявката.
Пробвай
SELECT test_size RDER BY tID;
със и без LIMIT r ще се ориентираш за каков става въпрос и откъде идва забавянето.
> Не мога да разбера защо таблици като TruckFuel се сканират целите,
> докато например TruckCabin не.
Зависи от броя на редовете. При малко на брой редове индексите са по бавни и няма смисъл да се използват.
13 rows in set (2.70 sec)
> Изобщо не мога да зацепя защо е толкова бавен, експлайна е добър, като
> изключим двата скана извън основната таблица.
Ееххх ... а беше време когато хората се радваха когато такава заявка приключи за по-малко от 2 часа ...
salle.online.bg/sig
Re: Бавен SQL израз
Здравей,
създадох таблицата:
SELECT * FROM test_size ORDER BY tID;
..............................................................
5 rows in set (0.01 sec)
SELECT * FROM test_size ORDER BY tID LIMIT 0, 10;
..............................................................
5 rows in set (0.01 sec)
Поиграх си малко с полето tUserID. Значи досега имаше 6 записа, в 5 от които tUserID е 27. в 6-тия е 36.
Като направя SELECT ... FROM ... WHERE ... AND tUserID = 36 ORDER BY tID LIMIT 0, 10;
си е супер бързо - 0.01 sec.
Ако е AND tUserID = 27 - 2.xx sec.
промених записа за user, остави 3 реда да са на 27, останалите ги размешах. Подобно същите резултати. Щом има повече от 2-3 реда за един user - бавно, но индекса пише че се използва, и сканира 3 реда. Махнах индекса - мнооого зле при всичи положения. Като махна условието за User, също е бавно
създадох таблицата:
mysql> describe test_size;
+-----------------+-----------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------------+------+-----+---------------------+-------+
| tID | mediumint(8) unsigned | | | 0 | |
| tUserID | smallint(5) unsigned | | | 0 | |
| tType | tinyint(3) unsigned | | | 0 | |
| tTypeName | varchar(30) | | | | |
| tCountry | tinyint(3) unsigned | | | 0 | |
| tCountryName | varchar(30) | | | | |
| tMark | tinyint(3) unsigned | | | 0 | |
| tMarkName | varchar(30) | | | | |
| tModel | smallint(5) unsigned | | | 0 | |
| tModelName | varchar(30) | | | | |
| tCabin | tinyint(3) unsigned | | | 0 | |
| tCabinName | varchar(30) | | | | |
| tSupStruct | tinyint(3) unsigned | | | 0 | |
| tEkoNorm | tinyint(3) unsigned | | | 0 | |
| tEkoNormName | varchar(30) | | | | |
| tSpeedBox | tinyint(3) unsigned | | | 0 | |
| tSpeedBoxName | varchar(30) | | | | |
| tHang | tinyint(3) unsigned | | | 0 | |
| tHangName | varchar(30) | | | | |
| tFuel | tinyint(3) unsigned | | | 0 | |
| tFuelName | varchar(30) | | | | |
| tAxisConfig | tinyint(3) unsigned | | | 0 | |
| tAxisConfigName | varchar(30) | | | | |
| tAxisCount | tinyint(3) unsigned | | | 0 | |
| tTirePCAxis | tinyint(3) unsigned | | | 0 | |
| tTireCol | decimal(4,2) | | | 0.00 | |
| tLPacket | enum('N','Y') | | | N | |
| tPower | smallint(5) unsigned | | | 0 | |
| tWeight | tinyint(3) unsigned | | | 0 | |
| tUsefulLoad | tinyint(3) unsigned | | | 0 | |
| tFirstReg | varchar(4) | | | | |
| tKM | mediumint(8) unsigned | | | 0 | |
| tPrice | mediumint(8) unsigned | | | 0 | |
| tValute | tinyint(3) unsigned | | | 0 | |
| tValuteName | char(3) | | | | |
| tInfo | text | | | | |
| tPics | tinyint(3) unsigned | | | 0 | |
| tTimeAdd | datetime | | | 0000-00-00 00:00:00 | |
| tTimeAddF | varchar(16) binary | YES | | NULL | |
+-----------------+-----------------------+------+-----+---------------------+-------+
39 rows in set (0.00 sec)
SELECT * FROM test_size ORDER BY tID;
..............................................................
5 rows in set (0.01 sec)
SELECT * FROM test_size ORDER BY tID LIMIT 0, 10;
..............................................................
5 rows in set (0.01 sec)
Поиграх си малко с полето tUserID. Значи досега имаше 6 записа, в 5 от които tUserID е 27. в 6-тия е 36.
Като направя SELECT ... FROM ... WHERE ... AND tUserID = 36 ORDER BY tID LIMIT 0, 10;
си е супер бързо - 0.01 sec.
Ако е AND tUserID = 27 - 2.xx sec.
промених записа за user, остави 3 реда да са на 27, останалите ги размешах. Подобно същите резултати. Щом има повече от 2-3 реда за един user - бавно, но индекса пише че се използва, и сканира 3 реда. Махнах индекса - мнооого зле при всичи положения. Като махна условието за User, също е бавно
Редактирано от Tetra на