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

2022-01-15 00:00:00 configuration mariadb mysql phpmyadmin

编辑:另见我的回答，主要区别是phpmyadmin添加的LIMIT，但我还是不明白，phpmyadmin仍然慢于mysqli.

Edit: see also my answer, the main difference is the LIMIT that phpmyadmin adds, but I still don't understand and phpmyadmin is still slower than mysqli.

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

On our database (+web) server we have a huge difference in performance when doing a query in phpmyadmin vs doing it from php (mysqli) or directly on the mariadb server. 60 seconds vs < 0.01 seconds!

这个查询功能很好:

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 时，查询变得非常慢.

But, only in phpMyAdmin, the query becomes extremely slow when we change 2020-05-02 to 2020-05-01.

SHOW PROCESSLIST显示查询u主要是发送数据同时运行.

SHOW PROCESSLIST shows that the queryu is mainly Sending data whilst running.

以下 mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts 我做了以下查询系列:

Following mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts I did the following query-series:

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

这些差异令人着迷.(在所有情况下，我都忽略了所有等于 0 的值).并且随着时间的推移保持一致.

The differences are fascinating. (I left out all the values equal to 0 in all cases). And consistent over time.

|                        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).

The EXPLAIN results are the same in all cases (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，但对表面下的东西几乎没有经验(比如它如何连接到数据库).

Can you give us any directions in where we should could look to solve this problem? We've worked for a week to optimize the mariadb server (now optimal, except in phpmyadmin) and narrow some of our problems down to the example underneath. We use phpmyadmin a lot but have little to no experience with what is under the surface (like how it connects to the db).

关于索引/排序

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

In the slow query, if we change the ORDER BY from the indexed isbn field to a non-indexed field or leave out the ORDER BY altogether, everything has its normal lightning speed again. Changing the ORDER BY to the primary key id makes it slow too, but still 10x as fast as with the indexed isbn field.

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

详情:

TitelDaggegevens 包含 <1100 万条记录，甚至不是 3Gb，并且已经过优化(重建)

TitelDaggegevens contains < 11mln records, not even 3Gb, and has been OPTIMIZEd (rebuild)

表结构:

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

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

Configuration of our virtual web+database+mail server:

MariaDB 10.4 
InnoDB
CentOs7 
phpMyAdmin 4.9.5
php 5.6
Apache

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

Some important mariadb configuration parameters that we changed from what our virtual webserver had as default:

[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

推荐答案

除了您的所有提示之外，我们已经对它进行了专家研究.

We've had a specialist look at it, additional to all your tips.

经过多次测试后发现，phpMyAdmin 添加的 LIMIT 0,25 是唯一导致极度延迟的因素.专家可以发现 mysqli/phpmyadmin 和直接在 mariadb 服务器上执行没有区别.

It turned out after MANY tests that the LIMIT 0,25 that phpMyAdmin added was the ONLY thing that caused the extreme delay. The expert could find NO differences between mysqli/phpmyadmin and executing it directly on the mariadb server.

有时查询中的一个非常小的差异(例如为无论如何只返回一条记录的查询添加一个限制)可能会导致查询花费 100.000 的时间，因为它会扫描整个索引，因为引擎会看到另一种适合的策略那个查询.这是标准行为.

Sometimes a VERY small difference in query (like adding a LIMIT for a query that returns only one record anyway) can cause a query to take 100.000 as long because it wil scan a whole index because the engine will see another strategy fit for that query. That is standard behaviour.

我们已经找到了一个可以消除这个特定问题的索引，现在我们也确信我们的数据库没有任何问题.我们不确定的东西，因为它似乎是极端的行为.所以:无事生非.

We already had found an index that eliminated this specific problem, nut now we are also assured that there is nothing wrong with our DB. Something we were not sure of because it seemed extreme behaviour. So: much ado about nothing.

然而，我从这些经历中学到了很多东西.既来自我们的专家，也来自这个社区.我了解了 MySQL 诊断、日志记录、mariaDB 如何处理查询......对于每一个被证明不是问题的诊断，我学习了在表、索引或查询中要避免或努力的事情.

HOWEVER I learned such a lot from this experiences. Both from our expert as from this community. I learned about MySQL diagnostics, logging, how mariaDB handles queries... For every diagnosis that turned out not to be the problem, I learned things to avoid or to strive for in tables, indexes or queries.

谢谢大家，尤其是@Rick James、@Wilson Hauck 和@ExploitFate

THANK YOU ALL, especially @Rick James, @Wilson Hauck and @ExploitFate

相关文章