时间戳

首页 > 解决方案 > MySQL - 为什么 phpMyAdmin 对这个在 php/mysqli 中超快的查询非常慢？

mysql - MySQL - 为什么 phpMyAdmin 对这个在 php/mysqli 中超快的查询非常慢？

问题描述

编辑：另见我的回答，主要区别是LIMITphpmyadmin 添加的，但我仍然不明白，phpmyadmin 仍然比 mysqli 慢。

在我们的数据库（+web）服务器上，在 phpmyadmin 中进行查询与从 php（mysqli）或直接在 mariadb 服务器上进行查询时，性能存在巨大差异。60 秒 vs < 0.01 秒！

这个查询功能很好：

SELECT * FROM `TitelDaggegevens` 
WHERE `datum` > '2020-03-31' AND datum < '2020-05-02' AND `fondskosten` IS NULL 
ORDER BY isbn;

但是，只有在 phpMyAdmin 中，当我们更改2020-05-02为2020-05-01.

SHOW PROCESSLIST表明查询主要是Sending data在运行时。

在mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts之后，我做了以下查询系列：

FLUSH STATUS;
SELECT-query above with one of the two dates;
SHOW SESSION STATUS LIKE 'Handler%';

差异是迷人的。（在所有情况下，我都忽略了所有等于 0 的值）。并且随着时间的推移保持一致。

|                        how:   |     server/MySqli       |      phpMyAdmin 
|         date used in query:   | 2020-05-02 | 2020-05-01 | 2020-05-02 | 2020-05-01
|           records returned:   | 6912       | 1          | 6912       | 1
|                  avg speed:   | 0.27s      | 0.00s      | 0.52s      | 60s (!)
| Variable_name                 | Value      | Value      | Value      | Value
| Handler_icp_attempts          | 213197     | 206286     | 213197     | 0
| Handler_icp_match             | 6912       | 1          | 6912       | 0
| Handler_read_next             | 6912       | 1          | 26651      | 11728896 (!)
| Handler_read_key              | 1          | 1          | 151        | 4
| Handler_commit                | 1          | 1          | 152        | 5
| Handler_read_first            | 0          | 0          | 1          | 1
| Handler_read_rnd_next         | 0          | 0          | 82         | 83
| Handler_read_rnd              | 0          | 0          | 0          | 1
| Handler_tmp_write             | 0          | 0          | 67         | 67

EXPLAIN 结果在所有情况下都是相同的（phpmyadmin/mysqli/putty+mariadb）。

    [select_type] => SIMPLE
    [table] => TitelDaggegevens
    [type] => range
    [possible_keys] => fondskosten,Datum+isbn+fondskosten
    [key] => Datum+isbn+fondskosten
    [key_len] => 3
    [ref] => 
    [Extra] => Using index condition; Using filesort

唯一的区别在于行：

    [rows] => 422796 for 2020-05-01
    [rows] => 450432 for 2020-05-02

问题

你能给我们任何方向，我们应该寻求解决这个问题吗？我们已经工作了一周来优化 mariadb 服务器（现在是最佳的，除了在 phpmyadmin 中）并将我们的一些问题缩小到下面的示例。我们经常使用 phpmyadmin，但对表面下的内容（比如它如何连接到数据库）几乎没有经验。

关于索引/排序

在慢查询中，如果我们ORDER BY将索引isbn字段更改为非索引字段或ORDER BY完全省略，一切又恢复了正常的闪电速度。将 更改ORDER BY为主键id也会使其变慢，但仍然是索引isbn字段的 10 倍。

我们*知道*我们可以通过更好的索引来解决这个特定的查询，我们已经准备好实施。但是，我们想知道是什么导致了 phpmyadmin 与 mysqli/directly 中不同的时间。

细节：

TitelDaggegevens 包含 < 1100 万条记录，甚至没有 3Gb，并且已经过优化（重建）

表结构：

CREATE TABLE `TitelDaggegevens` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `isbn` decimal(13,0) NOT NULL,
 `datum` date NOT NULL,
 `volgendeDatum` date DEFAULT NULL,
 `prijs` decimal(8,2) DEFAULT NULL,
 `prijsExclLaag` decimal(8,2) DEFAULT NULL,
 `prijsExclHoog` decimal(8,2) DEFAULT NULL,
 `stadiumDienstverlening` char(2) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `stadiumLevenscyclus` char(1) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `gewicht` double(7,3) DEFAULT NULL,
 `volume` double(7,3) DEFAULT NULL,
 `24uurs` tinyint(1) DEFAULT NULL,
 `UitgeverCode` varchar(4) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `imprintId` int(11) DEFAULT NULL,
 `distributievormId` tinyint(4) DEFAULT NULL,
 `boeksoort` char(1) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 `publishingStatus` tinyint(4) DEFAULT NULL,
 `productAvailability` tinyint(4) DEFAULT NULL,
 `voorraadAlles` mediumint(8) unsigned DEFAULT NULL,
 `voorraadBeschikbaar` mediumint(8) unsigned DEFAULT NULL,
 `voorraadGeblokkeerdEigenaar` smallint(5) unsigned DEFAULT NULL,
 `voorraadGeblokkeerdCB` smallint(5) unsigned DEFAULT NULL,
 `voorraadGereserveerd` smallint(5) unsigned DEFAULT NULL,
 `fondskosten` enum('depot leverbaar','depot onleverbaar','POD','BOV','eBoek','geen') COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `ISBN+datum` (`isbn`,`datum`) USING BTREE,
 KEY `UitgeverCode` (`UitgeverCode`),
 KEY `Imprint` (`imprintId`),
 KEY `VolgendeDatum` (`volgendeDatum`),
 KEY `Index op voorraad om maxima snel te vinden` (`isbn`,`voorraadAlles`) USING BTREE,
 KEY `fondskosten` (`fondskosten`),
 KEY `Datum+isbn+fondskosten` (`datum`,`isbn`,`fondskosten`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=16519430 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

我们的虚拟网页+数据库+邮件服务器的配置：

MariaDB 10.4 
InnoDB
CentOs7 
phpMyAdmin 4.9.5
php 5.6
Apache

我们更改了一些重要的 mariadb 配置参数，这些参数从我们的虚拟网络服务器默认设置：

[mysqld]
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances=4
innodb_flush_log_at_trx_commit=2

tmp_table_size=64M
max_heap_table_size=64M

join_buffer_size=4M
sort_buffer_size=8M

optimizer_search_depth=5

标签： mysqlconfigurationphpmyadminmariadb

解决方案

最大的不同当然是 phpmyadmin 在查询中添加了一个 LIMIT。这给出了主要的解释。我不敢相信这不是我们尝试的第一件事，我很尴尬。

但是phpMyAdmin和mysqli的速度差别还是很大的，结果还是有区别的（2020-05-01 on server or mysqli）：

+----------------------------+----------+
| Variable_name              | Value    |
+----------------------------+----------+
| Handler_commit             | 1        |
| Handler_read_first         | 1        |
| Handler_read_next          | 11733306 |
| rest                       | 0        |
+----------------------------+----------+

2020-05-02limit的速度：大约 0.17-0.2 2020-05-01 的速度limit：php/mysqli：声称：3.5 秒，但页面加载大约 30 秒 putty/mariadb：声称也 3.5 秒，但显示结果大约 30 秒后 phpmyadmin：声称和实时大约 60 秒

EXPLAIN 也确实随着 LIMIT 发生了很大变化：

（第 1268 行，基准 <20200501 和 1351 行，基准 <20200502）

+------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+
| id   | select_type | table            | type  | possible_keys                      | key        | key_len | ref  | rows | Extra       |
+------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+
|    1 | SIMPLE      | TitelDaggegevens | index | fondskosten,Datum+isbn+fondskosten | ISBN+datum | 9       | NULL | 1351 | Using where |
+------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+

推荐阅读