mysql 写sql注意的地方

本文介绍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 byhaving之前。如果将变量表达式取别名,然后在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个字符会被默认创建为前缀索引。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值