mysql自我优化-优化select语句（六）

最新推荐文章于 2024-04-25 12:02:52 发布

追风少年201

最新推荐文章于 2024-04-25 12:02:52 发布

阅读量162

点赞数

分类专栏：数据库mysql 文章标签： mysql

版权

11 篇文章 1 订阅

订阅专栏

首先这里要简要说明，该文章翻译自 mysql手册，并经过我的整合，所以文字内容较多，但是实际含金量比较高， 建议大家仔细阅读， 必有收获。

MySQL还根据参数的类型决定何时计算函数，参数是表列还是常量值。以表列为参数的确定性函数必须在该列更改值时进行计算。

不确定性函数可能会影响查询性能。例如，某些优化可能不可用，或者可能需要更多的锁定。下面的讨论使用RAND()，但也适用于其他不确定性函数。

假设表t有这样的定义:

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));

考虑这两个查询：

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

两个查询似乎都使用了主键查找，因为与主键进行了相等性比较，但这只适用于第一个查询:

第一个查询总是最多产生一行，因为带有常量参数的POW()是一个常量值，用于索引查找。
第二个查询包含一个表达式,使用不确定性函数RAND(),这不是常数查询中但实际上有一个新值为每一行的表t。因此,查询读取表的每一行,评估每一行的谓词,并输出所有行主键匹配的随机值。这可能是0行、1行或多行，具体取决于id列值和RAND()序列中的值。

不确定性的影响不仅限于SELECT语句。这个UPDATE语句使用了一个不确定的函数来选择要修改的行:

UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

据推测，目的是最多更新主键与表达式匹配的一行。但是，它可能会更新零行、一行或多行，具体取决于 id列值和RAND()序列中的值。

刚刚描述的行为对性能和复制有影响：

由于非确定性函数不会产生常量值，因此优化器无法使用可能适用的策略，例如索引查找。结果可能是表扫描。

InnoDB 可能会升级为范围键锁，而不是为一个匹配的行使用单行锁。

不能确定性执行的更新对于复制是不安全的。

困难来自这样一个事实:RAND()函数对表中的每一行求值一次。要避免多个函数计算，请使用以下技术之一:

SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;

SET optimizer_switch = 'derived_merge=off';
UPDATE t, (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt
SET col_a = some_expr WHERE id = @keyval;

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

当MySQL使用全表扫描来解析查询时，EXPLAIN的输出在type列中显示ALL。这种情况通常发生在以下情况下:

对于小表，表扫描通常是合适的，性能影响可以忽略不计。对于大表，请尝试以下技术以避免优化器错误地选择表扫描：

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  WHERE t1.col_name=t2.col_name;

使用--max-seek -for-key=1000选项启动mysqld，或者使用SET max_seeks_for_key=1000来告诉优化器假设没有键扫描导致超过1000个键寻找。参见“服务器系统变量”。

关注