MySQL - 왜 php MyAdmin이 매우 빠른 쿼리에서 매우 느린가?

MySQL - 왜 php MyAdmin이 매우 빠른 쿼리에서 매우 느린가?

편집제해 주세요.은 : " " 입니다.주요 차이점은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가 주로 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

EXPLINE 결과는 모든 경우에서 동일합니다(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을 많이 사용하지만 표면 아래에 있는 것에 대한 경험이 거의 없습니다(데이터베이스에 접속하는 방법 등).

인덱싱/순서 정보

In the slow query, if we change the 느린 쿼리에서, 만약 우리가 이 명령어를ORDER BY인덱스 색에서isbn field to a non-indexed field or leave out the 비필드에 입력하거나 생략합니다.ORDER BY모두, 모든 것은 정상적인 번개가 있다.모두 합쳐서, 모든 것이 정상의 번개 속도를 가지고 있다. Changing the 의 변경ORDER BY to the primary key 기본 키까지id, .isbnsyslog.syslog.syslog.

이미 구현할 준비가 되어 있는 인덱스를 개선함으로써 이 특정 쿼리를 해결할 수 있습니다.그러나 우리는 무엇이 phpmyadmin과 mysqli/direct의 다른 시간을 야기하는지 알고 싶습니다.

상세내용:

TitelDaggevens는 3Gb도 아닌 11mln 미만의 레코드를 포함하고 있으며 OPTIMISEd(재구축)가 되었습니다.

테이블 구조:

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

가장 큰 차이는 물론 phmyadmin이 쿼리에 LIMIT를 추가한다는 것입니다.그게 주된 설명입니다.처음 시도했던 게 그게 아니었다니 믿을 수가 없네요, 정말 부끄럽네요.

그러나 phpMyAdmin과 mysqli의 속도 차이는 여전히 크고 결과는 여전히 다릅니다(서버 또는 mysqli의 경우 2020-05-01).

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

고속화limit및 2020-05-02: 모든 속도가 약 0.17~0.2로limit2020-05-01: php/mysqli: 클레임: 3.5초, 그러나 페이지는 약 30초 동안 로드됩니다: claimes 또한 3.5초 동안 로드되지만 약 30초 후 결과를 보여줍니다. 클레임 및 실시간 약 60초

또한 다음과 같은 제한에 따라 설명 내용이 크게 변경됩니다.

(데이터가 20200501 미만인 행 1268 및 데이텀이 20200502 미만인 행 1351 포함)

+------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+
| 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 |
+------+-------------+------------------+-------+------------------------------------+------------+---------+------+------+-------------+

5가 아닌 optimizer_search_depth=16으로 설정하고 SELECT * FROM으로 하는 것을 검토하십시오.TitelDaggegevens어디에datum'2020-03-31'과 '2020-05-02' 사이 및fondskostenIS의 특수 주문은 ISBBN;

고객님의 팁에 덧붙여, 전문적인 검토를 실시했습니다.

많은 테스트 끝에 밝혀진 바로는LIMIT 0,25phpMyAdmin이 추가한 것만이 극단적인 지연의 원인이었습니다.전문가는 mysqli/phpmyadmin과 mariadb 서버에서 직접 실행하는 것의 차이를 찾을 수 없었습니다.

쿼리의 매우 작은 차이(어차피 하나의 레코드만 반환하는 쿼리에 대해 LIMIT를 추가하는 경우 등)로 인해 쿼리에 100.000이 소요될 수 있습니다.이는 엔진이 해당 쿼리에 적합한 다른 전략을 인식하기 때문입니다.그것은 표준적인 행동이다.

우리는 이미 이 특정 문제를 제거하는 인덱스를 발견했고, 이제 우리의 DB에도 아무런 문제가 없다는 것을 확신하게 되었습니다.극단적인 행동처럼 보여서 우리가 확신할 수 없는 무언가가 있었다.그래서: 아무것도 아닌 것에 대해 야단법석을 떨었다.

하지만 저는 이 경험을 통해 많은 것을 배웠습니다.둘 다 우리 전문가와 이 커뮤니티에서 온 것입니다.MySQL 진단, 로깅, mariaDB가 쿼리를 처리하는 방법에 대해 배웠습니다.문제가 되지 않는 것으로 판명된 모든 진단에 대해 테이블, 인덱스 또는 쿼리에서 피하거나 노력해야 할 것들을 배웠습니다.

모두 감사합니다.특히 @Rick James, @Wilson Hauck 및 @Explit운명.

(저울질하느라 좀 늦었네요.해결하셨다니 다행입니다.)

이상한 걸 찾아서 조사를 잘 하셨네요

EXPLAINphpmyadmin은?이치하다

것을 강하게 .EXPLAIN사용되었습니다.

쿼리를 phpmyadmin은 phpmyadmin을 합니다).LIMIT). 실수로 질의를 망친 것은 아닐까요?그 때 Slowlog 또는 General 로그가 켜져 있었습니까?어느 쪽이든 SQL이 실행 중이어야 합니다.

「 」의 인덱스만 한다.(fondskosten)INDEX(fondskosten, datum)퍼포먼스가 향상됩니다.

("데이터 전송"은 항상 그렇듯이 엔진에 의해 제공되는 쓸모없는 정보입니다.)

mariadb.com 에 버그를 제출하는 것을 추천합니다.

언급URL : https://stackoverflow.com/questions/61972759/mysql-why-is-phpmyadmin-extremely-slow-with-this-query-that-is-super-fast-in-p