php - 使用 Order by 和 Group By 进行内连接的 Mysql 查询速度更慢

Question

在我的 vps 上，我试图执行一个查询，我必须在大约 1800 条记录中提取 20 个结果，该查询由 3 个 INNER JOIN 组成，如果我执行它而不插入 GROUP BY id ORDER BY id DESC 在查询末尾查询会在 0.0143 秒内执行，而如果我在查询末尾插入 GROUP BY id ORDER BY id DESC查询，执行需要3.1447秒，你能告诉我为什么吗？

这是查询:

SELECT s.nome,s.url as urlSito,s.importospedizione,p.titolo,p.descrizione,p.prezzo,p.img,p.annata,p.formato,p.denominazione,p.regione,c.categoria,c.url,s.id AS sito,p.id AS prodotto,pp.prezzo AS old,pp.data,p.sku 
FROM prodotti AS p 
INNER JOIN siti AS s ON p.sito = s.id 
INNER JOIN categorie AS c ON p.categoria = c.id 
INNER JOIN prodotti_prezzi AS pp ON p.id = pp.prodotto 
WHERE p.attivo = 1 AND s.attivo = 1 AND p.forced = 0  AND 
    ( p.regione = 'Valle d\'Aosta' OR p.regione = 'Piemonte' OR p.regione = 'Liguria' OR 'Lombardia' OR p.regione = 'Trentino-Alto Adige' OR p.regione = 'Veneto' OR p.regione = 'Friuli-Venezia Giulia' OR p.regione = 'Emilia-Romagna' OR p.regione = 'Toscana' OR p.regione = 'Umbria' OR p.regione = 'Lazio' OR p.regione = 'Marche' OR p.regione = 'Abruzzo' OR p.regione = 'Molise' OR p.regione = 'Campania' OR p.regione = 'Puglia' OR p.regione = 'Basilicata' OR p.regione = 'Calabria' OR p.regione = 'Sardegna' OR p.regione = 'Sicilia' )
GROUP BY p.id  
ORDER BY p.prezzo ASC  
LIMIT 0,20

这是结构:

表格产品:

CREATE TABLE `prodotti` (   `id` int(11) NOT NULL,   `sku` text NOT NULL,   `titolo` text NOT NULL,   `descrizione` longtext NOT NULL,   `sito` int(11) NOT NULL,   `prezzo` double NOT NULL,   `qta` int(11) NOT NULL,   `url` text NOT NULL,   `produttore` text NOT NULL,   `regione` text NOT NULL,   `denominazione` text NOT NULL,   `annata` int(11) NOT NULL,   `formato` text NOT NULL,   `attivo` int(11) NOT NULL,   `img` text NOT NULL,   `home` int(11) NOT NULL,   `categoria` int(11) NOT NULL,   `consigli` int(11) NOT NULL,   `forced` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `prodotti`
-- ALTER TABLE `prodotti`   ADD PRIMARY KEY (`id`),   ADD UNIQUE KEY `id` (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `prodotti`
-- ALTER TABLE `prodotti`   MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

表格位置:

CREATE TABLE `siti` (
  `id` int(11) NOT NULL,
  `nome` text NOT NULL,
  `indirizzo` text NOT NULL,
  `tel` text NOT NULL,
  `pagamento` text NOT NULL,
  `spedizione` text NOT NULL,
  `url` text NOT NULL,
  `email` text NOT NULL,
  `password` varchar(40) NOT NULL,
  `emailweb` text NOT NULL,
  `zone` text NOT NULL,
  `data` int(11) NOT NULL,
  `aggiornamento` int(11) NOT NULL,
  `urlaggiornamento` text NOT NULL,
  `img` text NOT NULL,
  `click` double NOT NULL,
  `max` double NOT NULL,
  `ultimo` int(11) NOT NULL,
  `attivo` int(11) NOT NULL,
  `importospedizione` double NOT NULL,
  `descrizione` longtext NOT NULL,
  `web` text NOT NULL,
  `iva` text NOT NULL,
  `sociale` text NOT NULL,
  `avviso` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `siti`
--
ALTER TABLE `siti`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `siti`
--
ALTER TABLE `siti`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

表 prodotti_prezzi:

CREATE TABLE `prodotti_prezzi` (
  `id` int(11) NOT NULL,
  `prodotto` int(11) NOT NULL,
  `prezzo` double NOT NULL,
  `data` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `prodotti_prezzi`
--
ALTER TABLE `prodotti_prezzi`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `prodotti_prezzi`
--
ALTER TABLE `prodotti_prezzi`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

表格类别:

CREATE TABLE `categorie` (
  `id` int(11) NOT NULL,
  `categoria` text NOT NULL,
  `url` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `categorie`
--
ALTER TABLE `categorie`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `categorie`
--
ALTER TABLE `categorie`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

我尝试在另外两台服务器上的数据库克隆(相同数据)上运行相同的查询，但不会出现问题，只有当我从我的 vps 运行它时才会出现问题。

这是我的配置:

数据库服务器

• 服务器:通过 UNIX 套接字的本地主机
• 服务器类型:MySQL
• 服务器版本:5.7.22-0ubuntu0.16.04.1 - (Ubuntu)
• 协议(protocol)版本:10
• 用户:root@localhost
• 服务器字符集:UTF-8 Unicode (utf8)

网络服务器

• Apache/2.4.18 (Ubuntu) mod_fcgid/2.3.9 OpenSSL/1.0.2g
• 数据库客户端版本:libmysql - mysqlnd 5.0.12-dev - 20150407 -$Id: 38fea24f2847fa7519001be390c98ae0acafe387 $
• PHP 扩展:mysqliDocumentationcurlDocumentationmbstring文档
• PHP 版本:7.2.5-1+ubuntu16.04.1+deb.sury.org+1

phpMyAdmin:版本信息:4.6.6deb1+deb.cihar.com~xenial.2

非常感谢大家。

编辑

解释结果:

编辑2:

解释第二个数据库的结果:

Answer 1

对于您的查询，

SELECT s.nome,s.url as urlSito,s.importospedizione,p.titolo,p.descrizione,p.prezzo,p.img,p.annata,p.formato,p.denominazione,p.regione,c.categoria,c.url,s.id AS sito,p.id AS prodotto,pp.prezzo AS old,pp.data,p.sku 
FROM prodotti AS p 
INNER JOIN siti AS s ON p.sito = s.id 
INNER JOIN categorie AS c ON p.categoria = c.id 
INNER JOIN prodotti_prezzi AS pp ON p.id = pp.prodotto 
WHERE p.attivo = 1 AND s.attivo = 1 AND p.forced = 0  AND 
    ( p.regione = 'Valle d\'Aosta' OR p.regione = 'Piemonte' OR p.regione = 'Liguria' OR 'Lombardia' OR p.regione = 'Trentino-Alto Adige' OR p.regione = 'Veneto' OR p.regione = 'Friuli-Venezia Giulia' OR p.regione = 'Emilia-Romagna' OR p.regione = 'Toscana' OR p.regione = 'Umbria' OR p.regione = 'Lazio' OR p.regione = 'Marche' OR p.regione = 'Abruzzo' OR p.regione = 'Molise' OR p.regione = 'Campania' OR p.regione = 'Puglia' OR p.regione = 'Basilicata' OR p.regione = 'Calabria' OR p.regione = 'Sardegna' OR p.regione = 'Sicilia' )
GROUP BY p.id  
ORDER BY p.prezzo ASC  
LIMIT 0,20

您需要在用于连接表的字段上有索引。您在主键上有索引，但没有任何与之匹配的索引，因此数据库必须搜索连接表的每条记录。

为 prodotti.sito、prodotti.categoria、prodotti_prezzi.prodotto 添加索引，这应该会带来很大的不同。您还可以在 attivo、forced 和 regione 上添加索引以达到良好的效果。

我怀疑你的索引在所有3台服务器上都不一样，而慢的服务器缺少索引。

<小时/>

FWIW，其他一些注释:

1. 在 prodotti 上，唯一 key ID 是多余的； 主键根据定义是唯一的

2. p.regione = '瓦莱达奥斯塔' OR p.regione = '皮埃蒙特' OR p.regione = '利古里亚' OR '伦巴第' OR p.regione = '特伦蒂诺-上阿迪杰' OR p.regione = '威尼托' OR p.regione = '弗留利-威尼斯朱利亚' OR p.regione = '艾米利亚-罗马涅' OR p.regione = '托斯卡纳' OR p.regione = '翁布里亚' OR p.regione = '拉齐奥' OR p.regione = '马尔凯' OR p.regione = '阿布鲁佐' OR p.regione = '莫利塞' OR p.regione = '坎帕尼亚' OR p.regione = '普利亚' OR p.regione = '巴西利卡塔' OR p.regione = '卡拉布里亚' OR p.regione = '撒丁岛' OR p.regione = '西西里' )
   
   可以更简单地表示为
   
   p.regione IN('瓦莱达\'奥斯塔','皮埃蒙特','利古里亚','伦巴第','特伦蒂诺-上阿迪杰','威尼托','弗留利-威尼斯朱利亚','艾米利亚-罗马涅','托斯卡纳','翁布里亚','拉齐奥','马尔凯','阿布鲁佐','莫利塞','坎帕尼亚','普利亚','巴西利卡塔','卡拉布里亚','撒丁岛','西西里岛')