MySQL SQL限制参数

经常了解到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写法。当然需要结合实际情况，并且跟开发人员达成一致下合理设置。