php - 查询执行时间问题

Question

我在脚本的执行时间上遇到了一些问题。查询需要很多时间。这是查询:

select avg(price) from voiture where duration<30 AND make="Audi" AND model="A4"

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | voiture | ALL | NULL | NULL | NULL | NULL | 1376949 | Using where |
+---+---+---+---+---+---+---+---+---+---+

select price from voiture where duration<30 AND make="Audi" AND model="A4"
+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | voiture | ALL | NULL | NULL | NULL | NULL | 1376949 | Using where |
+---+---+---+---+---+---+---+---+---+---+

此查询在 phpMyAdmin 界面上执行大约需要 2 秒。我试图查看问题所在，删除 avg 函数会使查询持续大约 0.0080 秒。
我问自己在 php 脚本中计算 avg 需要多长时间，但是无论有无 avg 的查询都需要大约 2 秒。
所以我决定获取我的表的所有值并在脚本中进行处理，所以我使用这个查询:

select * from voiture where duration<30 AND make="Audi" AND model="A4"

+---+---+---+---+---+---+---+---+---+---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+---+---+---+---+---+---+---+---+---+
| 1 | SIMPLE | voiture | ALL | NULL | NULL | NULL | NULL | 1376949 | Using where |
+---+---+---+---+---+---+---+---+---+---+

在 phpMyAdmin 界面上，需要 0.0112 秒。但是在我的 php 脚本中，它需要 25 秒!

$timestart2=microtime(true);
$querysec='select * from voiture where duration<30 AND make="Audi" AND model="A4"';
$requestsec=mysql_query($querysec) or die(mysql_error());
$timeend2=microtime(true);
$time2=$timeend2-$timestart2;
$page_load_time2 = number_format($time2, 9);

表结构如下:

CREATE TABLE `voiture` (
  `carid` bigint(9) NOT NULL,
  `serviceid` bigint(9) NOT NULL,
  `service` varchar(256) NOT NULL,
  `model` varchar(256) DEFAULT NULL,
  `gearingType` varchar(256) DEFAULT NULL,
  `displacement` int(5) DEFAULT NULL,
  `cylinders` int(2) DEFAULT NULL,
  `fuel` varchar(32) DEFAULT NULL,
  `mileage` int(7) DEFAULT NULL,
  `existFlag` tinyint(1) DEFAULT NULL,
  `lastUpdate` date DEFAULT NULL,
  `version` varchar(256) DEFAULT NULL,
  `bodyType` varchar(256) DEFAULT NULL,
  `firstRegistration` date DEFAULT NULL,
  `powerHp` int(4) DEFAULT NULL,
  `powerKw` int(4) DEFAULT NULL,
  `vat` varchar(256) DEFAULT NULL,
  `price` decimal(12,2) DEFAULT NULL,
  `duration` int(3) DEFAULT NULL,
  `pageUrl` varchar(256) DEFAULT NULL,
  `carImg` varchar(256) DEFAULT NULL,
  `color` varchar(256) DEFAULT NULL,
  `doors` int(1) DEFAULT NULL,
  `seats` int(1) DEFAULT NULL,
  `prevOwner` int(1) DEFAULT NULL,
  `co2` varchar(256) DEFAULT NULL,
  `consumption` varchar(256) DEFAULT NULL,
  `gears` int(1) DEFAULT NULL,
  `equipment` varchar(1024) NOT NULL,
  `make` varchar(256) NOT NULL,
  `country` varchar(3) NOT NULL
)

carid和serviceid有索引
为什么我的查询需要这么长时间才能执行？有什么办法可以改进吗？
为什么执行时间与 phpMyAdmin 和我的 php 脚本不同？

Answer 1

On the phpMyAdmin interface, it takes 0.0112 seconds. But in my php script, it takes 25 seconds!

phpMyAdmin 界面为每个查询添加了LIMIT。默认情况下，它是 LIMIT 30。

为了减少聚合查询的时间，您需要为您使用的每个条件创建索引(或者可能是一个复合索引)。

因此，尝试为您的 model、make 和 duration 字段创建索引。

---

此外，您的表格过于非规范化。您可以创建一对表来对其进行规范化。
例如:Vendors(id, name)、Models(id, name) 并修改您的 voiture 以具有 vendor_id/model_id 字段而不是文本 make/model。

那么您的初始查询将如下所示:

select avg(t.price) from voiture t
INNER JOIN Models m ON m.id = t.model_id
INNER JOIN Vendors v ON v.id = t.vendor_id
where t.duration<30 AND v.name="Audi" AND m.name="A4"

它将扫描轻型查找表以查找文本匹配项，并使用带有索引 ID 的重型表进行操作。