mariaDB 쿼리에 대한 sql 코드 성능 향상

mariaDB 쿼리에 대한 sql 코드 성능 향상

델파이XE8에서 mariaDB(Ver 15.1 Distribution 10.1.31-MariaDB, for Win32)와 연결된 어플리케이션을 개발하였습니다.쿼리 성능을 향상시키고 싶습니다.단순화된 시나리오를 설명합니다.

de_사용자 테이블(innoDB)(81762행)

ID_U   INT PRIMARY KEY
Name   VARCHAR(30)
INDEX ID_U,  Name

de_doc 테이블(innoDB)(행 260452)

IDD   INT PRIMARY KEY
DataFi  Date
UserID  INT
...
INDEX IDD, UserID, DataFi
----
CONSTRAINT UserID_LK
FOREIGN KEY de_Doc  (UserID)
REFERENCES  de_User (ID_U)
ON DELETE CASCADE
ON UPDATE CASCADE

나의 질문

select User.*, Doc.LastDoc
FROM de_Users AS Us 
LEFT JOIN (
SELECT UserID,MAX(DataFi) AS LastDoc
FROM de_doc 
GROUP BY UserID
) as Doc on Doc.UserID = Us.ID_U

ORDER BY Us.Name ASC, Doc.LastDoc DESC;

-- 설명 선택...

+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+
| id   | select_type | table          | type  | possible_keys | key      | key_len | ref            | rows   | Extra                           |
+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+
|    1 | PRIMARY     | de_User        | ALL   | NULL          | NULL     | NULL    | NULL           |  81762 | Using temporary; Using filesort |
|    1 | PRIMARY     | <derived2>     | ref   | key0          | key0     | 5       | Base.Us.ID_U   |     10 |                                 |
|    2 | DERIVED     | de_Doc         | index | NULL          | UserID_LK| 4       | NULL           | 260452 |                                 |
+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+

내.ini...

# The MySQL server
[mysqld]
...
key_buffer = 4096M
key_buffer_size=1024M
table_open_cache = 2048
query_cache_size = 128M
max_connections = 100
...
max_allowed_packet = 256M
sort_buffer_size = 4096M
net_buffer_length = 16M
read_buffer_size = 256M
myisam_sort_buffer_size = 256M
log_error = "mysql_error.log"
...
# Comment the following if you are using InnoDB tables
innodb_data_home_dir = "C:/xampp/mysql/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "C:/xampp/mysql/data"
innodb_log_arch_dir = "C:/xampp/mysql/data"
## You can set .._buffer_pool_size up to 50 - 80 %
## of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 2048M
# DEPRECATED innodb_additional_mem_pool_size = 1024M
## Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
...
thread_concurrency = 4
...
[isamchk]
key_buffer = 1024M
sort_buffer_size = 256M
read_buffer = 8M
write_buffer = 16M

[myisamchk]
key_buffer = 1024M
sort_buffer_size = 256M
read_buffer = 8M
write_buffer = 8M

TEST phphmyadmin:

83705 total, the query employed 1,0000 sec.
if I remove "order by Doc.LastDoc DESC" it is very fast
83705 total, the query employed 0,0000 sec.

delphiEX8로 개발한 나의 어플리케이션에서 TEST

view table all rows 2,8 sec.
if I remove "order by Doc.LastDoc DESC" it is very fast
view table all rows 1,8 sec.

어떻게 하면 성능을 향상시킬 수 있습니까?

my.ini [mysqld] SECTION에 대한 제안

sort_buffer_size=2M  # from 4096M (4G) of RAM per connection, next 2 are per connect also
read_buffer_size=256K  # from 256M to reduce volume of data retrieved by 99%
read_rnd_buffer_size=256K  # from ? to a reasonable size

이 세 가지는 SET GLOBAL variable_name= 값 대체 K는 *1024, M은 *1024*1024로 동적으로 (루트로) 설정할 수 있습니다.영업일 가동 후 긍정/부정 결과를 게시해 주시기 바랍니다.

• 이는 모호합니다.INDEX IDD, UserID, DataFi
• 아마User.*원래는Us.*? 쿼리를 "단순화"하면 다른 문제로 전환할 수 있습니다.
• 아마LEFT JOIN불필요한; 사용JOIN.
• 이 컴포지트가 필요합니다. INDEX(UserID, LastDoc)
• 출력에 82K 행이 필요하신가요?고객은 그 많은 데이터를 가지고 무엇을 할 것입니까?고객이 결과를 좀 더 잘 소화해 낸다면 SQL로 진행하는 것이 더 좋을 것 같아서 물어봅니다.
• 타이밍 시 SELECT SQL_NO_CACHE를 사용하여 쿼리 캐시를 피해야 합니다.
• Phphmyadmin은 아마도 LIMIT를 공격하여 옵티마이저가 수행할 작업을 변경할 것입니다!
• ORDER BY t1.a, t2.b(서로 다른 표)로 인해 주문 시 인덱스를 사용할 수 없습니다.이렇게 하면 쿼리의 단락을 방지할 수 있습니다.

여기서 my.ini, phpmyadmin에서 이 값들을 변경하면 개선된 결과입니다.

델파이 애플리케이션에서 그리드를 채우는 데 걸리는 시간은 2.8초 전에 비해 지금은 1.9초입니다.

내 PC는 8Gb 램을 가지고 있습니다.

델파이에서 그리드를 채우는 시간을 줄일 수 있습니까?아마 나는 이것에 대해 새로운 요청을 해야 할 것 같습니다.

innodb_buffer_pool_size = 2048M
# Set .._log_file_size to 25 % of buffer pool size

전에

innodb_log_file_size = 64M

(83705 del total, 쿼리는 1,0000초를 사용했습니다.)

끝나고

innodb_log_file_size = 512M

(83705 del total, 쿼리는 0,0000초를 사용했습니다.)

목표가 "groubwise-max"인 경우 다음과 같은 조항을 생략했습니다.

select  User.*, Doc.LastDoc
    FROM  de_Users AS Us
    LEFT JOIN  
    (
        SELECT  UserID,MAX(DataFi) AS LastDoc
            FROM  de_doc
            GROUP BY  UserID 
    ) as Doc  ON Doc.UserID = Us.ID_U
             AND Doc.LastDoc = Us.DataFi    -- this was missing
    ORDER BY  Us.Name ASC, Doc.LastDoc DESC;

이로 인해 행이 더 적어지기 때문에 성능 문제를 해결할 수 있습니다.

이 쿼리를 사용하여 출력이 쿼리와 동일한지 확인합니다.

select Us.*,  max(Doc.DataFi) as LastDoc
FROM de_Users AS Us 
LEFT JOIN de_doc as Doc on Doc.UserID = Us.ID_U
group by Us.ID_U   
ORDER BY Us.Name ASC, LastDoc DESC;

언급URL : https://stackoverflow.com/questions/57239628/improve-sql-code-performance-for-a-mariadb-query