SQL优化技巧

本文以Mysql数据库为例，总结数据库优化方法。

一、数据库优化四个层面

Mysql数据库优化，可以从以下四个层面优化：硬件、系统配置、数据库表结构、sql语句及索引。
优化效果：SQL语句及索引>数据库表结构>系统配置>硬件
优化成本：SQL语句及索引<数据库表结构<系统配置<硬件

二、sql语句层面优化五个原则

1. 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO。
2. 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘IO及网络IO。
3. 减少交互次数：批量DML(Data Manipulation Language，即增删改查)操作，函数储存等减少数据连接次数。
4. 减少服务器CPU开销：尽量减少数据排序操作以及全表查询，减少CPU内存占用。
5. 利用更多资源：使用表分区，增加并行操作，最大限度利用CPU资源。

可总结为以下三点：

• 最大化利用索引
• 尽可能避免全表扫表
• 减少无效数据查询

三、SQL的语法顺序及执行顺序

SELECT语句，语法顺序如下：

SELECT 
DISTINCT <select_list>
FROM <left_table>
JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

SELECT语句，执行顺序如下：

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...> 
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

四、查询条件优化

1. 对于复杂的查询，可使用中间临时表暂存数据
2. 拆分复杂SQL为多个小SQL，避免大事务
   简单的SQL容易使用到Mysql的QUERY CACHE；减少锁表时间特别是MylSAM 存储引擎的表；可使用多核CPU。
3. 优化 group by 语句
   默认情况下，MySQL 会对 GROUP BY 分组的所有值进行排序，如 “GROUP BY col1，col2，…;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，…;” 。
   如果显式包括一个包含相同的列的 ORDER BY 子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。
   因此，如果查询包括 GROUP BY 但你并不想对分组的值进行排序，你可以指定 ORDER BY NULL 禁止排序。
   例如：

SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

4. 优化 join 语句
   利用join语句代替子查询（in），减少Mysql在内存中创建临时表，提升效率；若on链接的字段为索引，则性能更好。
5. 优化 union 查询
   除非缺失需要消除重复行，否则建议使用union all。因为如果没有all 关键词，mysql会给临时表加上distinct选项，进行唯一性校验。
6. 使用 truncate 代替 delete
   若确认删除全表，可使用truncate，不会记录可恢复的信息，且数据不能被恢复。
7. 使用合理的分页方式以提高分页效率
   使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率。

案例1：
select * from t where thread_id = 10000 and deleted = 0 
   order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引 IO+索引全部记录结果对应的表数据 IO。

因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

适用场景：当中间结果集很小（10000 行以下）或者查询条件复杂（指涉及多个不同查询字段或者多表连接）时适用。

案例2：

select t.* from (select id from t where thread_id = 10000 and deleted = 0
   order by gmt_create asc limit 0, 15) a, t 
      where a.id = t.id;

上述例子必须满足 t 表主键是 id 列，且有覆盖索引 secondary key：（thread_id, deleted, gmt_create ）。

通过先根据过滤条件利用覆盖索引取出主键 id 进行排序，再进行 join 操作取出其他字段。

数据访问开销=索引 IO+索引分页后结果（例子中是 15 行）对应的表数据 IO。因此，该写法每次翻页消耗的资源和时间都基本相同，就像翻第一页一样。

适用场景：当查询和排序字段（即 where 子句和 order by 子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

五、SELECT语句其他优化

1. 避免出现select※
2. 避免出现不确定结果的函数
   针对主从赋值这类业务场景，由于原理上从库复制的是主库执行的语句，使用如 now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应数据不一致。
   另外不确定值的函数，产生的sql语句无法利用query cache。
   Mysql 主从复制 作用和原理
   Mysql 查询缓存（Query Cache）详细介绍
3. 多表关联查询时，小表在前，大表在后
   在mysql中，执行from后的表关联查询是从左往右的（与Oracle相反），第一张表会涉及全表扫描。
   所以应将小表放在前面，先扫小表，以提高效率。
4. 使用表的别名
   当在sql中连接多个表时，将表别名前缀在每个列名上，以减少解析时间并避免列名歧义导致的语法错误。
5. 调整where语句中链接顺序
   mysql采用从左往右，自上而下的顺序解析where语句，因此应将过滤数据多的条件往前放。

六、增删改DML语句优化

1. 大批量插入数据
   若同时执行大量插入操作，建议使用多个值INSERT语句（方法二），可提升效率的原因有三个：
   ①减少sql语句解析的操作；
   ②特定场景可减少对DB链接次数；
   ③SQL语句较短，可以减少网络传输的IO.

方法一：
insert into T values(1,2); 
insert into T values(1,3); 
insert into T values(1,4);

方法二：
Insert into T values(1,2),(1,3),(1,4); 

2. 适当使用commit
   适当使用commit可释放事务占用的资源以减少消耗，commit后能释放的资源如下：
   ①事务占用的undo数据块；
   ②事务在redo log中记录的数据块；
   ③释放事务，减少锁争用影响性能。
3. 避免重复查询表中更新的数据
   针对业务中常出现的更新行的同时又希望获取改行信息的需求，MySQL不支持PostgreSQL那样的update returning语法，在MySQL中可通过变量实现。
   如以下方法中，前后均需两次网络来回，但通过变量避免再次访问数据表，当t1表体量较大时可明显提升效率：

简单方法实现：

Update t1 set time=now() where col1=1; 
Select time from t1 where id =1;

使用变量，可以重写为以下方式：

Update t1 set time=now () where col1=1 and @now: = now (); 
Select @now; 

1. 查询优先还是更新优先（insert、delete、update）
   MySQL 还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。
   我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。
   MySQL默认的调度策略总结如下：
   ①写入操作优先于读取操作。
   ②对某张数据表的写入操作某一时刻只能发生一次，写入请求按他们到达的次序来处理。
   ③对某张数据表的多个读取操作可以同时进行。

七、避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描
   如果需求是要在前面使用模糊查询：
   ①使用 MySQL 内置函数 INSTR（str，substr）来匹配，作用类似于 Java 中的 indexOf()，查询字符串出现的角标位置。
   ②使用 FullText 全文索引，用 match against 检索。
   ③数据量较大的情况，建议引用 ElasticSearch、Solr，亿级数据量检索速度秒级。
   ④当表数据量较少（几千条儿那种），别整花里胡哨的，直接用 like ‘%xx%’。

SELECT * FROM t WHERE username LIKE '%陈%'

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

2. 尽量避免使用in 和 not in，会导致引擎走全表扫描
   如下：

SELECT * FROM t WHERE id IN (2,3)
优化方式：如果是连续数值，可以用 between 代替。
SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用 exists 代替。
如下：
-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

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

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用 union 代替 or。
SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4. 尽量避免进行null值判断，会导致数据库引擎放弃索引进行全表扫描
   如下：

SELECT * FROM t WHERE score IS NULL

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

5. 尽量避免在where条件中等号左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描
   可以将表达式、函数操作移动到等号右侧，如下：

-- 全表扫描
SELECT * FROM T WHERE score/10 = 9

-- 走索引
SELECT * FROM T WHERE score = 10*9

6. 当数据量大时，避免使用where 1=1 条件
   通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：

SELECT username, age, sex FROM T WHERE 1=1

优化方式：用代码拼装 SQL 时进行判断，没 where 条件就去掉 where，有 where 条件就加 and。

7. 查询条件不能用 <> 或 !=
   使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。
   如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。
8. where 条件仅包含符合索引非前置列
   如下：复合（联合）索引包含 key_part1，key_part2，key_part3 三列，但 SQL 语句没有包含索引前置列"key_part1"，按照 MySQL 联合索引的最左匹配原则，不会走联合索引。

select col1 from table where key_part2=1 and key_part3=2

9. 隐式类型转换造成不使用索引
   如下 SQL 语句由于索引对列类型为 varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123; 

10. order by 条件要与 where 中条件一致，否则 order by 不会利用索引进行排序
    数据库的处理顺序是：
    ①根据 where 条件和统计信息生成执行计划，得到数据。
    ②将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看 order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
    ③返回排序后的数据。
    当 order by 中的字段出现在 where 条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。
    这个结论不仅对 order by 有效，对其他需要排序的操作也有效。比如 group by 、union 、distinct 等。
    如下：

-- 不走age索引
SELECT * FROM t order by age;

-- 走age索引
SELECT * FROM t where age > 0 order by age;

11. 正确使用 hint 优化语句
    MySQL 中可以使用 hint 指定优化器在执行时选择或忽略特定的索引。
    一般而言，处于版本变更带来的表结构索引变化，更建议避免使用 hint，而是通过 Analyze table 多收集统计信息。
    但在特定场合下，指定 hint 可以排除其他索引干扰而指定更优的执行计划：
    ①USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。
    例子: SELECT col1 FROM table USE INDEX (mod_time, name)…
    ②IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint。
    例子: SELECT col1 FROM table IGNORE INDEX (priority) …
    ③FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为 Hint。
    例子: SELECT col1 FROM table FORCE INDEX (mod_time) …
    在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。
    如果我们知道如何选择索引，可以使用 FORCE INDEX 强制查询使用指定的索引，如下：

SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

MySQL的特殊关键字提示：hint

八、建表优化

1. 表中建立索引，优先考虑where、group by、order by使用到的字段
2. 尽量使用数字型字段（如性别，男1，女0），若只含数值信息的字段尽量不要设计为字符型，这回降低查询和连接的性能
   这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
3. 用varchar/nvarchar 代替 char/nchar
   不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL 也包含在内），都是占用 100 个字符的空间的，如果是 varchar 这样的变长字段， null 不占用空间。

搞懂这些SQL优化技巧