mysql 写sql注意的地方

最新推荐文章于 2023-07-05 15:20:39 发布

原创最新推荐文章于 2023-07-05 15:20:39 发布 · 268 阅读

0 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

6 篇文章

订阅专栏

本文介绍SQL查询中的优化方法，包括避免过多表连接导致的超时问题，通过子查询替代复杂join操作；讨论了变量在SELECT语句中的使用限制，尤其是在GROUP BY、HAVING和ORDER BY中的注意事项；还提到了MySQL5.7.7以下版本中索引长度的限制。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一次查询中不要join太多表，数据量过大导致超时，可以考虑用子查询代替

--容易超时
select max(A.c1), max(B.c1), max(C.c1) from
table1 join A join B join C

--优化
select max(C.c1), tc.* from
(
    select max(B.c1), tb.* from
    (
        select max(A.c1) 
        from table1  
        join A
    ) tb join B
) tc join C

在查询中使用变量时最好不要使用group by、having和order by，或者在外层sql中使用group by、having和order by
In a SELECT statement, each select expression is evaluated only when sent to the client. This means that in a HAVING, GROUP BY, or ORDER BY clause, referring to a variable that is assigned a value in the select expression list does not work as expected:
mysql> SELECT (@aa:=id) AS a, (@aa+3) AS b FROM tbl_name HAVING b=5;
The reference to b in the HAVING clause refers to an alias for an expression in the select list that uses @aa. This does not work as expected: @aa contains the value of id from the previous selected row, not from the current row.

自己测试的话发现变量的计算在group by之后，order by和having之前。如果将变量表达式取别名，然后在having子句中用别名进行判断，那么这个表达式会在having中再执行一遍。
变量在select语句中的执行顺序是未知的
For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:
SELECT @a, @a:=@a+1, ...;
However, the order of evaluation for expressions involving user variables is undefined.
- mysql5.7.7以下版本默认索引最大长度为767字节
  mysql5.7.7以下版本默认要求索引总长度小于767字节，对于utf8mb4一个字符按4字节计算的话，最多只能有191个字符，如果超过191个字符会被默认创建为前缀索引。