时间戳

首页 > 解决方案 > 大数据库中的慢查询

mariadb - 大数据库中的慢查询

问题描述

我对 mysql 请求的查询很慢。但要求并不难：

SELECT * FROM emailarchiv WHERE typ='MAIL'  AND benutzer = '542' ORDER BY datum DESC LIMIT 0,50;
# User@Host: XXX[XXX] @ localhost []
# Thread_id: 13245239  Schema: usr_XXX_1  QC_hit: No
# Query_time: 20.919740  Lock_time: 0.000066  Rows_sent: 50  Rows_examined: 79212

好的，数据库中有 79212 个条目……但是 20 秒？？？

我的系统：Centos7 MariaDB 5.5、10GB RAM、10 个 CPU

这是我的 my.conf：

[mysqld]
local-infile = 0
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user = mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# Forced OLD_PASSWORD format is turned OFF by Plesk
#old_passwords = 1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links = 0

max_connections = 100
#port = 3306 #Port nicht aendern
key_buffer_size = 512M
max_allowed_packet = 256M
table_open_cache = 4096
sort_buffer_size = 64M
net_buffer_length = 128K
read_buffer_size = 1512K
read_rnd_buffer_size = 4096K
myisam_sort_buffer_size = 64M
max_heap_table_size = 512M
tmp_table_size = 1024M
key_cache_block_size = 4096
query_cache_size = 512M
query_cache_limit = 512M
thread_cache_size = 20
table_cache = 16384
#open_files_limit = 9212 #nicht aktivieren!! Verursacht Probleme...
#wait_timeout = 10 #nicht aktivieren !!
join_buffer_size = 32M
#bind-address = 127.0.0.1

#Aenderungen ab 23.07.2021
innodb_buffer_pool_size = 512M
max_heap_table_size= 1024M
skip-name-resolve
slow-query-log = 1
slow-query-log-file = /srv/slow-query.log
long_query_time = 5

ssl-ca=/etc/mysql-ssl/ca-cert.pem
ssl-cert=/etc/mysql-ssl/server-cert.pem
ssl-key=/etc/mysql-ssl/server-key.pem

[mysqld_safe]
#log-error = /var/log/mysqld.log

这里出了什么问题？

标签： mariadbquery-optimization

解决方案

我自己解决了这个问题;-)

我不为问题所在的行“typ”和“benutzer”使用索引。现在 Query_time 是 2 秒

推荐阅读