☰

MySQL 5.7 试图提高性能

tag-icon • tag-icon mysql database-performance
MySQL 5.7 试图提高性能

我们有一个使用 innoDB 的 MySQL 5.7 运行的 PHP 应用程序。该应用程序过去非常慢。对于某些查询，MySQL 会长时间占用 100% 的 CPU。我尝试运行优化表，但 innoDB 只会将其解释为重新创建表。这只会让情况变得更糟。

我启用了慢查询日志记录，如下所示：

slow_query_log          = ON
long_query_time         = 5
log_queries_not_using_indexes = ON
slow_query_log_file     = /var/log/mysql/mysql-slow.log

我在日志中只看到下面的内容，重复多次：

SET timestamp=1516955047;
SELECT * FROM `notification` WHERE `user_id`=4 ORDER BY `created_at` DESC LIMIT 5;
# Time: 2018-01-26T08:24:07.652626Z
# User@Host: root[root] @ localhost []  Id:   153
# Query_time: 0.000221  Lock_time: 0.000107 Rows_sent: 1  Rows_examined: 3
SET timestamp=1516955047;
SELECT
    kcu.constraint_name,
    kcu.column_name,
    kcu.referenced_table_name,
    kcu.referenced_column_name
FROM information_schema.referential_constraints AS rc
JOIN information_schema.key_column_usage AS kcu ON
    (
        kcu.constraint_catalog = rc.constraint_catalog OR
        (kcu.constraint_catalog IS NULL AND rc.constraint_catalog IS NULL)
    ) AND
    kcu.constraint_schema = rc.constraint_schema AND
    kcu.constraint_name = rc.constraint_name
WHERE rc.constraint_schema = database() AND kcu.table_schema = database()
AND rc.table_name = 'notification' AND kcu.table_name = 'notification';
# Time: 2018-01-26T08:24:07.653082Z
# User@Host: root[root] @ localhost []  Id:   153
# Query_time: 0.000155  Lock_time: 0.000040 Rows_sent: 1  Rows_examined: 143

我想要继续，看看是否可以通过数据库调优来提高性能。如何查看缺失索引列表或类似内容？

答案1

要分析 MySQL 慢日志，请尝试工具mysqldumpslow https://dev.mysql.com/doc/refman/5.7/en/mysqldumpslow.html

mysqldumpslow /var/log/mysql/mysql-slow.log

当你发现最慢的查询时，尝试解释 https://dev.mysql.com/doc/refman/5.7/en/explain.html 使用起来非常简单：

EXPLAIN SELECT foo FROM slow_something WHERE ...;

相关内容