Я разработал приложение, связанное с mariaDB (Ver 15.1 Distrib 10.1.31-MariaDB, для Win32) в DelphiXE8. Я хочу улучшить производительность запросов. Опишите упрощенный сценарий:
Таблица de_User (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 | |
+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+
мой.ини...
# 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
ТЕСТ phpmyadmin:
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.
TEST в моем приложении, разработанном с помощью delphiEX8
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.
Как я могу улучшить производительность?
de_documents
какINDEX UserID
, если вы не добавляете и не делитесь выводомexplain
- person James   schedule 28.07.2019userID
? - person James   schedule 28.07.2019ORDER BY Us.Name ASC, Doc.LastDoc DESC;
является критической точкой? Короткий ответ: вы не можете напрямую индексировать его, так какORDER BY
использует две таблицы, один из обходных путей — создание материализованного view для ведения таблицы, которую можно использовать для сортировки по индексам -› Материализованное представление в MySQL - person Raymond Nijland   schedule 28.07.2019SELECT SQL_NO_CACHE ...
. Это вероятно причина0,0000 sec
. - person Rick James   schedule 28.07.2019LIMIT
, тем самым изменяя действия оптимизатора! - person Rick James   schedule 28.07.2019