主页 / dba / 问题 / 226435
Accepted
Alireza
Asked: 2019-01-07 03:48:41 +0800 CST

如何限制 mySQL 查询不影响服务器并将其关闭？

  • 772

有时一些繁重的查询会增加服务器负载，一段时间后 CPU 和内存都会达到 100%。

这是my.cnf配置文件：

[mysqld]
expire_logs_days= 2

pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/storage/mysql

# LOGGING #
log-error                      = /var/storage/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log                 = 1
slow-query-log-file            = /var/storage/mysql/mysql-slow.log

# MyISAM #
key-buffer-size                = 32M
myisam-recover-options         = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000

# BINARY LOGGING #
log-bin                        = /var/storage/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 1024
table-open-cache               = 2048

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb-log-files-in-group      = 2
innodb_log_file_size=500M
innodb_buffer_pool_size=6G
innodb_flush_log_at_trx_commit=2
# recently added for compression, omit it if mysql has problems with it
innodb_file_format=Barracuda

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0


collation-server = utf8mb4_general_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4
skip-character-set-client-handshake

[client]
default-character-set   = utf8mb4

[mysql]
default-character-set   = utf8mb4

服务器有 6 个内核和 16 GB 的 RAM。有什么限制MySQL并使其安全的建议吗？

EDIT-1 这是缓慢的查询之一，需要 4.3 秒MySQL才能返回数据并检查了大约 300 万行：

# Time: 190107  6:22:30
# User@Host: root[root] @ localhost [127.0.0.1]
# Thread_id: 204732  Schema: my_db  QC_hit: No
# Query_time: 4.306051  Lock_time: 0.000058  Rows_sent: 0  Rows_examined: 3253235
# Rows_affected: 0
SET timestamp=1546842150;
SELECT ((old_credit + (1*amount))/10) credit
FROM `credits`
WHERE user_id=' ' or user_id=(SELECT user_id FROM accounts WHERE email=' ')
ORDER BY id DESC
LIMIT 1;

查询由前端 BI 应用程序创建（可以在一定程度上更改）。

该表的索引是：

MariaDB [my_db]> show index from credits;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| credits |          0 | PRIMARY    |            1 | id          | A         |     2014915 |     NULL | NULL   |      | BTREE      |         |               |
| credits |          1 | credit_id  |            1 | credit_id   | A         |     2014915 |     NULL | NULL   | YES  | BTREE      |         |               |
| credits |          1 | user_id    |            1 | user_id     | A         |      134327 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

编辑-2：

Database changed
MariaDB [my_db]> show index from accounts;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| accounts |          0 | PRIMARY             |            1 | id          | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id             |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | email_idx           |            1 | email       | A         |      486034 |      190 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            1 | user_id    | A         |      486034 |     NULL | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            2 | email       | A         |      486034 |       40 | NULL   |      | BTREE      |         |               |
| accounts |          1 | user_id_email_phone |            3 | phone       | A         |      486034 |       15 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)
mysql linux

1 个回答

  1. Best Answer

    你真的是这个意思= ' '吗？那是检查一个字符长的字符串，即一个空格。

    OR是一个性能杀手，所以让我们通过使用来摆脱它UNION：

        ( SELECT  id, ((old_credit + (amount))/10) AS credit
            FROM  `credits`
            WHERE  user_id=' '
            ORDER BY  id DESC
            LIMIT  1;
    )
    UNION ALL
    ( SELECT  c.id, ((c.old_credit + (c.amount))/10) AS credit
            FROM  `credits` AS c
            JOIN  accounts AS a  ON a.user_id = c.user_id
            WHERE  a.email=' ' 
            ORDER BY  c.id DESC
            LIMIT  1;
    )
    ORDER BY  id DESC
    LIMIT  1;

    需要的索引：

    credits:  (user_id, id)
accounts: (email, user_id)

    这可能会更快，并且它消除了上述解决方案具有的额外列 (id)。

    SELECT ((c2.old_credit + (c2.amount))/10) AS credit
  FROM (
    ( SELECT  id
            FROM  `credits`
            WHERE  user_id=' '
            ORDER BY  id DESC
            LIMIT  1;
    )
    UNION ALL
    ( SELECT  c.id
            FROM  `credits` AS c
            JOIN  accounts AS a  ON a.user_id = c.user_id
            WHERE  a.email=' ' 
            ORDER BY  c.id DESC
            LIMIT  1;
    )
    ORDER BY  id DESC
    LIMIT  1;
  ) AS x
  JOIN credits c2  ON c2.id = x.id

    INDEXes这里也需要以上内容。（我假设credits有PRIMARY KEY(id)。）

    Rows_examined: 3253235可能会减少到 10 个以下。

    至于如何防止顽皮的查询MariaDB 10.1+，但没有MySQL，有max_statement_time。

    我看不出您为什么会达到 100% 内存。（请参阅评论。）但是该查询的每个副本可能会占用一个核心 4.3 秒。（我的两个版本可能连 4.3毫秒都不会耗尽。）

    您需要其他慢速查询方面的帮助吗？

    • 1

相关问题