- 一次查询中不要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个字符会被默认创建为前缀索引。
- mysql5.7.7以下版本默认索引最大长度为767字节