Mysql
如何限制 mySQL 查詢不影響伺服器並將其關閉？
有時，一些繁重的查詢會增加伺服器負載，有時 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)
你真的是這個意思
= ' '嗎？那是檢查字元串是否為一個字元長，即空格。
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毫秒都不會咀嚼。）
您需要其他慢查詢的幫助嗎？