MySQL-为什么phpMyAdmin的查询速度极慢，而在php/mysqli中却超快速? [英] MySQL - Why is phpMyAdmin extremely slow with this query that is super fast in php/mysqli?

问题描述

编辑:另请参见我的回答，主要区别是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)服务器上，与在php(mysqli)或直接在mariadb服务器上进行查询相比，在phpmyadmin中进行查询时，性能存在巨大差异. 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表明运行时queryu主要是Sending data.

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

在所有情况下(phpmyadmin/mysqli/putty + mariadb)，EXPLAIN结果均为相同.

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/direct内的时间不同.

详细信息:

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 

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

Configuration of our virtual web+database+mail server:

MariaDB 10.4 
InnoDB
CentOs7 
phpMyAdmin 4.9.5
php 5.6
Apache 

一些重要的mariadb配置参数已从虚拟Web服务器的默认设置更改为

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.

有时查询中的细微差异(例如为只返回一条记录的查询添加LIMIT)可能导致查询花费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

这篇关于MySQL-为什么phpMyAdmin的查询速度极慢，而在php/mysqli中却超快速?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！