高效SQL编写规范建议

SQL编写的一些优化经验：

  • 避免使用SELECT*FROM语句，应该只选择需要的列，以减少网络传输和提高查询性能
  • 使用索引来提高查询速度，特别是在对大型表进行查询时
  • 避免使用外键约束，因为它们可能会导致性能问题，特别是在对大型表进行插入、 更新和删除操作时
  • 使用LIMIT1来限制查询结果只有一条记录
  • 避免在where子句中使用OR采连接条件，应使用UNION来连接查询
  • 注意优化LIMIT深分页问题，可以使用OFFSET来替代LIMIT
  • 使用where条件限制要查询的数据，避免返回多余的行
  • 尽量避免在索引列上使用MySQL的内置函数，这可能导致索引失效
  • 应尽量避免在where子句中对字段进行表达式操作，这可能导致索引失效
  • 应尽量避免在where子句中使用！=或<>操作符，这可能导致索引失效
  • 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则

1、大批量插入数据

如果同时执行大量的插入，建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快（方法一），一般情况下批量插入效率有几倍的差别。

方法一：

1
2
3
insert into tablename values(1,2);
insert into tablename values(1,3);
insert into tablename values(1,4);

方法二：

1
Insert into tablename values(1,2),(1,3),(1,4);

选择后一种方法的原因有二。

  • 减少SQL语句解析的操作， MySQL没有类似Oracle的share pool，采用方法二，只需要解析一次就能进行数据的插入操作；
  • SQL语句较短，可以减少网络传输的IO。

此外，还有以下建议提高插入性能：

  • 通过使用 INSERT DELAYED 语句得到更高的速度。Delayed 的含义是让 insert 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘；
  • 这比每条语句分别插入要快的多，但需要注意，DELAYED关键字只用于MyISAM，MEMORY这类只支持表锁的存储引擎；
  • 将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。

2、查询优先还是更新（insert、update、delete）优先

MySQL 还允许改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型，判断应用是以查询为主还是以更新为主的，是确保查询效率还是确保更新的效率，决定是查询优先还是更新优先。下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎，比如 MyISAM 、MEMROY、MERGE，对于Innodb 存储引擎，语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下：

1）写入操作优先于读取操作。

2）对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。

3）对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符，允许你修改它的调度策略：

  • LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE；
  • HIGH_PRIORITY关键字应用于SELECT和INSERT语句；
  • DELAYED关键字应用于INSERT和REPLACE语句。

如果写入操作是一个 LOW_PRIORITY（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。这种调度修改可能存在 LOW_PRIORITY写入操作永远被阻塞的情况。SELECT 查询的HIGH_PRIORITY（高优先级）关键字也类似。它允许SELECT 插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。另外一种影响是，高优先级的 SELECT 在正常的 SELECT 语句之前执行，因为这些语句会被写入操作阻塞。如果希望所有支持LOW_PRIORITY 选项的语句都默认地按照低优先级来处理，那么 请使用–low-priority-updates 选项来启动服务器。通过使用 INSERTHIGH_PRIORITY 来把 INSERT 语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。

3、避免出现select *

select * 操作在任何类型数据库中都不是一个好的SQL开发习惯。使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。建议评估业务实际需要的列数，指定列名以取代select *。

  • 规范：Select col1,col2,col3… from t1;
  • 不规范：Select * from t1;

索引覆盖扫描

索引覆盖扫描——当索引中的列包含select中所有要查询的列的时候，就会用到覆盖索引，避免了回表，效率比较高。

4、避免使用insert..selec..语句

当使用insert…select…进行记录的插入时，如果select的表是innodb类型的，不论insert的表是什么类型的表，都会对select的表的纪录进行锁定。对于那些从Oracle迁移过来的应用，需要特别的注意，因为Oracle并不存在类似的问题，所以在Oracle的应用中insert…select…操作非常常见。例如：有时候会对比较多的纪录进行统计分析，然后将统计的中间结果插入到另外一个表，这样的操作因为进行的非常少，所以可能并没有设置相应的索引。

如果迁移到MySQL数据库后不进行相应的调整，那么在进行这个操作期间，对需要select的表实际上是进行的全表扫描导致的所有记录的锁定，将会对应用的其他操作造成非常严重的影响。

究其主要原因，是因为MySQL在实现复制的机制时和Oracle是不同的，如果不进行select表的锁定，则可能造成从数据库在恢复期间插入结果集的不同，造成主从数据的不一致。如果不采用主从复制，关闭binlog并不能避免对select纪录的锁定。如果使用这个binlog进行从数据库的恢复，或者进行主数据库的灾难恢复，都将可能和主数据库的执行效果不同。

因此，我们并不推荐通过设置这个参数来避免insert…select…导致的锁，如果需要进行可能会扫描大量数据的insert…select操作，我们推荐使用select…into outfile和load data infile的组合来实现，这样是不会对纪录进行锁定的。

例子：

1
INSERT INTO SMAP_HISTORY.SMAP2_SESSION (SESSION_ID,SESSION_TICKET_ID) SELECT S.SESSION_ID,S.SESSION_TICKET_ID FROM SMAP.SMAP2_SESSION S WHERE SESSION_SID = #sessionId#;

以上语句会对表SMAP2_SESSION施加表锁，而由于业务上该表存在大量insert语句，业务压力大的时候极易造成严重的阻塞。

5、减少表的锁冲突

对 Innodb 类型的表：

1）首先要确认，在对表获取行锁的时候，要尽量的使用索引检索纪录，如果没有使用索引访问，那么即便你只是要更新其中的一行纪录，也是全表锁定的。要确保 sql 是使用索引来访问纪录的，必要的时候，请使用 explain 检查 sql 的执行计划，判断是否按照预期使用了索引。

2）由于 MySQL 的行锁是针对索引加的锁，不是针对纪录加的锁，所以虽然是访问不同行的纪录，但是如果是相同的索引键，是会被加锁的。应用设计的时候也要注意，这里和 Oracle 有比较大的不同。

3）当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，当表有主键或者唯一索引的时候，不是必须使用主键或者唯一索引锁定纪录，其他普通索引同样可以用来检索纪录，并只锁定符合条件的行。

4）如果要使用锁定读，（SELECT … FOR UPDATE 或 … LOCK IN SHARE MODE），尝试用更低的隔离级别，比如 READ COMMITTED。

6、优化join语句

MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询可以被更有效率的连接(JOIN)..替代。

例子：假设要将所有没有订单记录的用户取出来，可以用下面这个查询完成：

1
SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

如果使用连接(JOIN).. 来完成这个查询工作，速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话，性能将会更好，查询如下：

1
2
3
SELECT col1 FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL

连接(JOIN).. 之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

7、优化or条件

对于 or 子句，如果要利用索引，则or 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

8、优化union查询

MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行，否则建议使用union all。原因在于如果没有all这个关键词，MySQL会给临时表加上distinct选项，这会导致对整个临时表的数据做唯一性校验，这样做的消耗相当高。

高效：

1
2
3
4
5
SELECT COL1, COL2, COL3
FROM TABLE
WHERE COL1 = 10
UNION ALL
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

低效：

1
2
3
4
SELECT COL1, COL2, COL3
FROM TABLE WHERE COL1 = 10
UNION
SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

9、拆分复杂SQL为多个小SQL，避免大事务

  • 简单的SQL容易使用到MySQL的QUERY CACHE；
  • 减少锁表时间特别是使用MyISAM存储引擎的表；
  • 可以使用多核CPU。

10、使用truncate代替delete

当删除全表中记录时，使用delete语句的操作会被记录到undo块中，删除记录也记录binlog，当确认需要删除全表时，会产生很大量的binlog并占用大量的undo数据块，此时既没有很好的效率也占用了大量的资源。使用truncate替代，不会记录可恢复的信息，数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外，使用truncate可以回收表的水位。

11、使用合理的分页方式以提高分页效率

使用合理的分页方式以提高分页效率 针对展现等分页需求，合适的分页方式能够提高分页的效率。

案例1：

1
2
3
4
select * from t
where thread_id = 10000
and deleted = 0
order by gmt_create asc limit 0, 15;

上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此，该种写法越翻到后面执行效率越差，时间越长，尤其表数据量很大的时候。

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

案例2：

1
2
3
4
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子句涉及的字段）有对应覆盖索引时，且中间结果集很大的情况时适用。

12、避免不走索引的各种场景

在下面的SQL语句中的WHERE子句不使用索引：

1）条件中有or，且or左右列并非全部由索引 Select col1 from table where key1=1 or no_key=2

2）like查询以%开头

3）where条件仅包含复合索引非前置列

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

索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列。

4）隐式类型转换造成不使用索引

1
Select col1 from table where key_varchar=123;

上述语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

5）避免对索引字段进行计算

避免对索引字段进行任何计算操作，对索引字段的计划操作会让索引的作用失效，令数据库选择其他的较为低效率的访问路径。

6）避免对索引字段进行是否NULL值判断

避免使用索引列值是否可为空的索引，如果索引列值可以是空值，在SQL语句中那些要返回NULL值的操作，将不会用到索引。

7）避免对索引字段不等于符号

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

13、避免重复查询更新的数据

针对业务中经常出现的更新行同时又希望获得改行信息的需求，MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法，在MySQL中可以通过变量实现。

例如，更新一行记录的时间戳，同时希望查询当前记录中存放的时间戳是什么，简单方法实现：

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

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

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

前后二者都需要两次网络来回，但使用变量避免了再次访问数据表，特别是当t1表数据量较大时，后者比前者快很多。

14、避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用QUERY CACHE。