mysql - 提高 mariaDB 查询的 sql 代码性能

Question

我在 DelphiXE8 中开发了一个连接到 mariaDB 的应用程序(版本 15.1 Distrib 10.1.31-MariaDB，适用于 Win32)。我想提高查询性能。描述简化的场景:

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.

在我用 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.

我怎样才能提高性能？

Answer 1

对你的 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=value 动态设置(作为 root)，请将 K 替换为 *1024，将 M 替换为 *1024*1024 以表示千字节和兆字节。请在一个完整的工作日正常运行后发布正面/负面结果。