SQL

SQL：SQL 优化经验总结

SQL 优化经验

在表中建立索引，优先考虑 where、group by 使用到的字段。

尽量避免使用 select *，返回无用的字段会降低查询效率。

优化方式：
- 使用具体的字段代替 *，只返回使用到的字段。

尽量避免使用 in 和 not in，会导致数据库引擎放弃索引进行全表扫描。

如下：
- SELECT * FROM t WHERE id IN (2,3)
- SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)
优化方式：
- 如果是连续数值，可以用 between代替。
- 如下：
  - SELECT * FROM t WHERE id BETWEEN 2 AND 3
- 如果是子查询，可以用 exists代替。
- 如下：
  - SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。

如下： -SELECT * FROM table t WHERE t.a = 1 OR t.b = 3
优化方式：
- 可以用 union 代替 or。
- 如下：
  - SELECT * FROM table WHERE a = 1 UNION SELECT * FROM table WHERE b = 3
- 原理：使用 union 扫描的是索引，or 扫描的是全表。

尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。

如下：
- SELECT * FROM t WHERE username LIKE '%li%'
优化方式：
- 尽量在字段后面使用模糊查询。
- 如下：
  - SELECT * FROM t WHERE username LIKE 'li%'

尽量避免进行 null 值的判断，会导致数据库引擎放弃索引进行全表扫描。

如下：
- SELECT * FROM t WHERE score IS NULL
优化方式：
- 可以给字段添加默认值0，对0值进行判断。
- 如下：
  - SELECT * FROM t WHERE score = 0

尽量避免在 where 条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。

如下：
- `SELECT * FROM t2 WHERE score/10 = 9
- SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'
优化方式：
- 可以将表达式、函数操作移动到等号右侧。
- 如下：
  - SELECT * FROM t2 WHERE score = 10*9
  - SELECT * FROM t2 WHERE username LIKE 'li%'

当数据量大时，避免使用 where 1=1 的条件。

通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。
如下：
- SELECT * FROM t WHERE 1=1
优化方式：
- 用代码拼装 sql 时进行判断，没 where 加 where，有 where 加 and。

应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。

尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。

一般情况下不鼓励在 where 子句使用 like 操作。

% 在前的模糊查询将导致全表扫描。

-例如： select id from t where name like '%abc%'

inner join 资源消耗小于 left join。

如果连接方式是 inner join，在没有其他过滤条件的情况下 MySQL 会自动选择小表作为驱动表，但是 left join 在驱动表的选择上遵循的是左边驱动右边的原则，即 left join 左边的表名为驱动表。

count(*) 和 count(1)和count(列名)区别执行效果上：

count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为 NULL；
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为 NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

指定查询的索引

当 sql 查询的字段有多个索引的时候，mysql 优化器会自动选择一个索引进行查询，我们也可以通过 sql 字段进行自定义,

use index(索引): 推荐使用指定的索引(最终用不用该索引,还需要 mysql 自己判断)

示例：
- select * from use index(索引A)

ignore index(索引) : 忽略掉这个索引

示例：
- select * from ignore index(索引A)

force index(索引): 强制使用该索引

示例：
- select * from force index(索引A)