我在delphixe8开发了一个连接到Mariadb(Ver 15.1 Distrib 10.1.31-Mariadb,for 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 | |
+------+-------------+----------------+-------+---------------+---------------+---------+----------------+--------+---------------------------------+
my.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
测试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.
如何提高性能?
建议您的my.ini [mysqld]部分
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
可以用 *1024和m用 *1024 *1024的kbytes和megabytes,用 *1024和m用 *1024和m替换k用set global valiable_name = value替换k。在整个正常运行时间后,请发布正/负面结果。
- 这是模棱两可的:
INDEX IDD, UserID, DataFi
- 可能是
User.*
应该是Us.*
?请注意,"简化"查询可能会将其变成另一个问题。 - 可能是不必要的
LEFT JOIN
;使用JOIN
。 - 您需要此复合
INDEX(UserID, LastDoc)
- 您真的想要输出中的82K行吗?客户将如何处理这么多数据?我问,因为客户是否会进一步消化结果,也许更好地在SQL中完成。
- 定时时,请确保使用选择SQL_NO_CACHE避免查询缓存。
- phpMyAdmin可能会限制,从而更改优化器的功能!
-
ORDER BY t1.a, t2.b
(不同的表(使得不可能使用索引进行订购。这将防止查询的任何短路。
在my.ini中更改这些值,这是改进的结果。
与2.8秒之前相比,我的Delphi应用程序中填充网格所需的时间。
我的PC有8GB RAM;
我可以减少填充Delphi网格的时间吗?也许我必须为此提出新的要求。
innodb_buffer_pool_size = 2048M
# Set .._log_file_size to 25 % of buffer pool size
之前 innodb_log_file_size = 64M
(83705 DEL总计,雇用的查询1,0000秒(
之后innodb_log_file_size = 512M
(83705 del总数,雇用的查询0,0000秒(
如果您的目标是" grouwise-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;