MySQL 查询优化

考虑到面试可能会问到 SQL 优化相关的问题，提前总结一下：

能写在 WHERE 限定的条件就不要写在 HAVING 中

查询部门为产品部、宣传部的平均工资：

-- 反例
SELECT dept_name, AVG(salary)
FROM dept t
GROUP BY dept_name
HAVING t.dept_name IN ('产品部', '宣传部');

-- 正例
SELECT dept_name, AVG(salary)
FROM dept t
WHERE t.dept_name IN ('产品部', '宣传部')
GROUP BY dept_name;

因为 SQL 的执行顺序是：

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

所以 AVG() 会把其他部门的薪水也进行分组计算，放到 WHERE 语句中可减少 AVG 的消耗。

避免 SELECT *，应该只查询所需字段

大表数据：垂直分表

例如一个接口是查询各个汽车，点击查看详细才会去跳转到这辆汽车的各种排气孔大小，轮胎尺寸，后备箱空间等特别详细的参数。
对于这种特别详细的参数不属于高频查询的数据，可以采用多个表，用 ID 关联。

大表数据：水平分表

UNION ALL 例如日志表，可以每个月一个日志。

LIKE 查询尽量加上左边确定的值

如 LIKE 'nameSpac%'

导致索引失效的情况

避免使用 IS NOT NULL、OR、NOT IN、!= 这样不会走索引
LIKE ‘%something’ 不会走索引
隐式类型转换

1 2	-- 这里 age 为整数类型，但查询条件是字符串 SELECT * FROM users WHERE age = '30';

前导列失效：在联合索引中，如果查询条件中缺少前导列，索引将失效

SELECT 很慢可以用覆盖索引

1	CREATE INDEX idx_name_city ON users (name, city);

表太多大于3个最好不要连表

把数据到应用层处理，挑出来要的数据再放 SQL 去查

对于查询慢的 SQL，添加合适的索引

经常要用到的条件（SELECT，WHERE，ORDER BY，GROUP BY，DISTINCT…）添加索引

VARCHAR 存储浪费空间，特殊定长字段用 CHAR

添加索引要注意

一个表中最好不要超过6个索引
尽量是区分度高的数据作为索引
条件中多个字段要创建索引的情况下，可以创建联合索引（减少回表）。如 age > 18 AND height < 183
当字段类型为字符串(VARCHAR, TEXT等)时，有时候需要索引很长的字符串，这会让索引变得很大，此时可以只将字符串的一部分前缀，建立索引（前缀索引）