經常了解到MySQL使用過程中，因為這個那個SQL語句導致系統負載高，變慢等問題。可以說數據庫性能問題90%以上是不良SQL引起的。前期發現不了，一旦壓力負載上來，問題就爆發。

應對這種情況，規定了開發規范，并且起到了很大的限制作用，但由于疏忽或大意，往往會存在漏網之魚；

對于這樣的情況MySQL也有一些控制手段。另外一方面也是為了提高SQL性能考慮。這些參數非常有意思的。

sql_select_limit

限制SELECT語句返回的行數，好處是長時間SQL，io，網絡資源有效的限制。當參數設置一定范圍，就需要告知使用者，做了限制。

MySQL> SELECT.*  FROM employees e limit 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
|  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
|  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
|  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
|  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
|  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

MySQL> show variables like '%sql_select_limit%';
+------------------+----------------------+
| Variable_name    | Value                |
+------------------+----------------------+
| sql_select_limit | 18446744073709551615 |
+------------------+----------------------+
1 row in set (0.00 sec)
#只返回一行
MySQL> set session sql_select_limit=1;
Query OK, 0 rows affected (0.00 sec)
MySQL> SELECT e.*  FROM employees e ;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+

#當加入LIMIT 限制無效
MySQL> SELECT e.*  FROM employees e LIMIT 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

sql_safe_updates

sql_safe_updates分會話級別和全局級別，避免DELETE與UPDATE語句全表操作，上升為表鎖。當然WHERE條件不走索引的情況，也會一樣效果。所以WHERE條件后面 必須索引。

MySQL> show variables like 'sql_safe_updates';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| sql_safe_updates | OFF   |
+------------------+-------+
1 row in set (0.00 sec)

MySQL> set session sql_safe_updates=on;
Query OK, 0 rows affected (0.00 sec)

MySQL> update employees  set gender='M';
       ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. 

max_execution_time

SELECT語句的執行超時，以毫秒為單位。如果該值為0，則不啟用超時。
返回大數據量的語句持續時間過長，導致資源占有，隊列越來越長。cpu負載上去，MySQL服務無法訪問。這是典型的問題。

MySQL> set session max_execution_time=1;
Query OK, 0 rows affected (0.00 sec)
MySQL> show variables like 'max_execution_time';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_execution_time | 1     |
+--------------------+-------+
1 row in set (0.00 sec)
MySQL> SELECT e.*  FROM employees e ;
      ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded

max_join_size & sql_big_selects

• 當sql_big_selects值設置為OFF時, MySQL將中止可能需要很長時間執行的SELECT語句(即那些優化器估計所檢查的行數超過max_join_size值的語句)。
• 不允許執行超過max_join_size rows的語句。

MySQL> SELECT e.*  FROM employees e INNER JOIN   salaries s ON e.emp_no=s.emp_no  LIMIT 10;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
+--------+------------+------------+-----------+--------+------------+
10 rows in set (0.00 sec)

#進行限制
MySQL> SET SESSION max_join_size=5;

#限制提示
MySQL> SELECT e.*  FROM employees e INNER JOIN   salaries s ON e.emp_no=s.emp_no  LIMIT 10;
       ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay

connection_memory_limit

MySQL 8.0.28用戶連接內存限制，設置單個用戶連接可以使用的最大內存量(16MB~)。如果任何用戶連接使用超過這個數量，任何來自該連接的新查詢都會被ER_CONN_LIMIT拒絕。

• 此限制不適用于系統用戶或root帳戶；
• InnoDB緩沖池使用的內存也不包括在內。

就是說限制臨時內存，排序，鏈接等內存。應對所有用戶（除root）

MySQL> set global connection_memory_limit=2097152;

MySQL> SELECT count(*) FROM employees group by gender;
ERROR 4082 (HY000): Connection closed. Connection memory limit 2097152 bytes exceeded. Consumed 1094948 bytes.

總結

在無法用開發規范限制的情況下， SQL限制參數，能避免低質量的SQL寫法。當然需要結合實際情況，并且跟開發人員達成一致下合理設置。