- 编辑 *:另见我的回答,主要的区别是phpmyadmin添加的
LIMIT
,但我仍然不明白,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;
字符串
但是,only 在phpMyAdmin中,当我们将2020-05-02
更改为2020-05-01
时,查询变得非常慢。SHOW PROCESSLIST
显示运行时queryu主要是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
,一切都将恢复正常的 lightning 速度。将ORDER BY
更改为主键id
也会使速度变慢,但仍然是索引isbn
字段的10倍。
- 我们知道 * 我们可以通过更好的索引来解决这个特定的查询,我们已经准备好实现了。但是,我们想知道是什么导致phpmyadmin和mysqli/中的时间不同。*
详情:
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配置参数,我们改变了我们的虚拟web服务器默认设置:
[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
型
4条答案
按热度按时间iaqfqrcu1#
除了你的建议我们还请了一位Maven
经过多次测试,phpMyAdmin添加的
LIMIT 0,25
是导致极端延迟的唯一原因。Maven无法找到mysqli/phpmyadmin和直接在mariadb服务器上执行它之间的区别。有时候,查询中一个非常小的差异(比如为一个只返回一条记录的查询添加一个LIMIT)可能会导致查询花费100.000的时间,因为它会扫描整个索引,因为引擎会看到另一个适合该查询的策略。
我们已经找到了一个消除这个特定问题的索引,但现在我们也确信我们的DB没有任何问题。我们不确定的事情,因为它似乎是极端的行为。所以,无事生非。
yzckvree2#
最大的区别,是当然phpmyadmin添加了一个LIMIT查询。这给出了主要的解释。我不敢相信这不是我们尝试的第一件事,我很尴尬。
不过phpMyAdmin和mysqli的速度差距还是很大的,结果还是有区别的(2020-05-01在server或者mysqli上):
字符串
速度与
limit
和2020-05-02:都在0.17-0.2速度与limit
和2020-05-01:php/mysqli:声称:3.5秒,但页面加载约30秒腻子/mariadb:声称也3.5秒,但显示结果后约30秒phpmyadmin:声称和真实的时间约60秒此外,解释也有很大的变化与限制:
(with第1268行(基准线<20200501)和第1351行(基准线<20200502)
型
ryoqjall3#
考虑使optimizer_search_depth=16而不是5,
字符串
hsvhsicv4#
你发现了一个奇怪的,并做了很好的调查工作。
有办法从phpmyadmin获取
EXPLAIN
吗?如果有,那可能会给给予另一条线索。这些数字强烈暗示使用了不同的
EXPLAIN
。显然phpmyadmin修改了查询(至少通过添加
LIMIT
)。我想知道它是否意外地扰乱了查询。当时你打开了Slowlog或General log吗?两者都应该将SQL * 作为run*。将
(fondskosten)
上的索引替换为INDEX(fondskosten, datum)
应该可以提高性能。(“发送数据”,一如既往,是引擎提供的无用信息。
建议在mariadb.com上提交一个bug。