如何编写高性能sql

高效SQL编写规范建议

1、大批量插入数据

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

方法一：

insert into tablename values(1,2); 

insert into tablename values(1,3); 

insert into tablename values(1,4);

方法二：

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。

 

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的组合来实现，这样是不会对纪录进行锁定的。

例子：

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、适当使用commit

适当使用commit可以释放事务占用的资源而减少消耗，commit后能释放的资源如下：

• 事务占用的undo数据块；
• 事务在redo log中记录的数据块； 
• 释放事务施加的，减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候，必须分解删除量并定期commit。

 

6、减少表的锁冲突

对 Innodb 类型的表： 

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

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

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

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

 

7、使用SQL_BUFFER_RESULT减少锁定时间

将强制 MySQL 生成一个临时结果集。只要所有临时结果集生成后，所有表上的锁定均被释放。这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助。当处理一个会让客户端耗费点时间才能处理的大结果集时，可以考虑使用SQL_BUFFER_RESULT 提示字。这样可以告诉MySQL将结果集保存在一个临时表中，这样可以尽早的释放各种锁。需注意，该参数不能用于子查询中以及union之后 语法：SELECT SQL_BUFFER_RESULT …

 

8、正确使用hint优化语句

MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。一般而言，处于版本变更带来的表结构索引变化，更建议避免使用hint，而是通过Analyze table多收集统计信息。但在特定场合下，指定hint可以排除其他索引干扰而指定更优的执行计划。

1）USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...

2）IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...

3）FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

 

9、优化group by语句

默认情况下，MySQL 排序所有 “GROUP BY col1，col2，....;” 查询的方法如同在查询中指定 “ORDER BY col1，col2，...;” 如果显式包括一个包含相同的列的 ORDER BY子句，MySQL 可以毫不减速地对它进行优化，尽管仍然进行排序。

如果查询包括 GROUP BY 但你想要避免排序结果的消耗，你可以指定 ORDER BY NULL禁止排序。例如：

SELECT a, COUNT(1) FROM table GROUP BY a ORDER BY NULL ;

 

10、优化order by语句

在某些情况中，MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。where 条件和 order by 使用相同的索引，并且 order by 的顺序和索引顺序相同 ，并且 order by 的字段都是升序或者都是降序。

例如：下列 SQL 可以使用索引。 

SELECT col1 FROM t1 ORDER BY key_part1,key_part2,... ; 

SELECT col1 FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

以上复合索引包含字段key_part1，key_part2... 

但是以下情况不使用索引：

SELECT col1 FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； 

以上由于order by 的字段混合 ASC和 DESC 。

SELECT col1 FROM t1 WHERE key2=constant ORDER BY key1； 

以上用于查询行的关键字与 ORDER BY 中所使用的不相同。

SELECT col1 FROM t1 ORDER BY key1, key2； 

对不同的索引关键字使用 ORDER BY：

 

11、优化join语句

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

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

SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID 

FROM salesinfo )

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

SELECT col1 FROM customerinfo 

LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 

WHERE salesinfo.CustomerID IS NULL 

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

 

12、优化or条件

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

 

13、优化union查询

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

高效：

SELECT COL1, COL2, COL3 

FROM TABLE 

WHERE COL1 = 10 

UNION ALL 

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

低效：

SELECT COL1, COL2, COL3 

FROM TABLE WHERE COL1 = 10 

UNION 

SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

 

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

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

 

15、使用truncate代替delete

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

 

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

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

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

 

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

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

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

2）like查询以%开头

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

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

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

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

Select col1 from table where key_varchar=123; 

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

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

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

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

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

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

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

 

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

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

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

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; 

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

 

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

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

 

使用EXPLAIN分析SQL性能

1、执行计划

执行计划是一条查询语句在数据库中的执行过程或访问路径的描述。

2、怎样查看MySQL执行计划

在需要查看执行计划的SQL前面添加explain并执行，即可获取。

 

3、读EXPLAIN中的信息

1）table

显示这一行的数据是关于哪张表的。

2）type

这是重要的列，显示连接使用了何种类型。

从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。

3）possible_keys

显示可能应用在这张表中的索引。如果为空，没有可能的索引。

4）key

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。

5）key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好。

6）ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。

7）rows

MYSQL认为必须检查的用来返回请求数据的行数。

8）Extra 

关于MYSQL如何解析查询的额外信息。效率最低的是Using temporary和Using filesort，意味着MYSQL根本不能使用索引，所以检索会很慢。

 

2.3.1  索引分类

 

 

2.3.1.1  按存储结构来分

• 哈希索引
• Btree索引（B+tree或B-tree) 
• full-index全文索引
• 自行研究

2.3.1.2 按应用层次上来划分

• 主键索引，索引列的值必须唯一，没有空值，比如数据库表中id自增列
• 唯一索引，索引列的值必须唯一，但允许有空值
• 普通索引，即一个索引只包含单个列，一个表可以有多个单列索引
• 复合索引，索引包含多个列

2.3.1.3  按表记录的排列顺序和索引的排列顺序是否一致来划分

• 聚集索引：表记录的排列顺序和索引的排列顺序一致
• 非聚集索引：表记录的排列顺序和索引的排列顺序不一致

1）简单概括

• 聚集索引（clustered index）：就是以主键创建的索引。 
• 非聚集索引（secondary indexes）：就是以非主键创建的索引（也叫做二级索引）。

2）详细概括

• 聚集索引

聚集索引表记录的排列顺序和索引的排列顺序一致，所以查询效率快，因为只要找到第一个索引值记录，其余的连续性的记录在物理表中也会连续存放，一起就可以查询到。缺点：新增比较慢，因为为了保证表中记录的物理顺序和索引顺序一致，在记录插入的时候，会对数据页重新排序。

• 非聚集索引

索引的逻辑顺序与磁盘上行的物理存储顺序不同，非聚集索引在叶子节点存储的是主键和索引列，当我们使用非聚集索引查询数据时，需要拿到叶子上的主键再去表中查到想要查找的数据。这个过程就是我们所说的回表。

3）聚集索引和非聚集索引的区别

• 聚集索引在叶子节点存储的是表中的数据。 
• 非聚集索引在叶子节点存储的是索引列和主键。

 

2.3.2  具体案例

我们是java码农，可能关心最多的sql方面的知识，说了那么多理论，要有实际的案例衬托，假定创建了一张用户表，如下：

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `age` int(3) NOT NULL,
  `birthday` date NOT NULL,
  `gender` int(2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `name_birthday` (`first_name`,`last_name`,`birthday`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COMMENT='用户表';

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO user VALUES ('1', 'dong', 'guangming', '31', '2020-06-26', '1');
INSERT INTO user VALUES ('2', '董', '广明', '21', '2020-06-25', '1');
INSERT INTO user VALUES ('3', '孙', '权', '88', '2019-06-26', '1');
INSERT INTO user VALUES ('4', '钟', '南山', '84', '1950-06-26', '1');
INSERT INTO user VALUES ('5', 'dong', 'guangming', '21', '2020-06-23', '1');
INSERT INTO user VALUES ('6', 'dong', 'gm', '19', '2020-06-15', '1');
INSERT INTO user VALUES ('7', 'cao', 'cao', '99', '2020-05-03', '1');
INSERT INTO user VALUES ('8', 'sun', 'quan', '88', '2020-06-08', '1');

 然后查看表的索引

show index from `user`;

 

怎么确定你的sql语句有没有使用索引呢，如果走索引的话，走哪个索引呢？？？就一一举例：

2.3.2.1  主键索引

看上文，建user表时把字段id设为了主键

explain select * from `user` where id=1

但有例外情况：

2.3.2.2  全值匹配

和索引中所有的列进行匹配配对，

explain select * from `user` where first_name ='dong' and last_name='guangming' and birthday='2020-06-25';

 

2.3.2.3 最左匹配原则

比如上面sql里建的复合索引

  KEY `name_birthday` (`first_name`,`last_name`,`birthday`) USING BTREE

你心里可以认为是三个子索引：（first_name） 、（first_name，last_name）、(`first_name`,`last_name`,`birthday`)

联想到B+树的原理就会想到必须要匹配最左原则，就是要有first_name开头的列

切记： 遇到范围查询（>、<、between、like）就会停止匹配。比如：first_name = 'dong' and last_name>'guangming' and birthday='2020-06-23' ，birthday是用不到索引的，因为last_name字段是一个范围查询，它之后的字段会停止匹配。

 

 2.3.3.4   匹配列前缀

匹配某一列的值的开头部分，例如查找所有以dong开头的姓的人。

只有一个查询用到了索引

2.3.3.4   匹配范围值

查找姓在dong和sun之间的人。

 

2.3.3.5  精确匹配某一列并范围匹配另外一列

查找姓为dong，出生日期小于某参数日期的用户。

 2.3.3.6  只访问索引的查询，即覆盖索引

查询只需要访问索引，无需访问数据行。

2.3.3.7  禁止在索引列上使用不等号

2.3.3.8  字符串不加单引号索引失效

看数据库版本（我用的是 5.5.56-MariaDB MariaDB Server），有的出现的情况不一样

 2.3.3.9  索引列禁止计算等其他操作

索引列一旦参与计算、函数、（手动或自动）类型转换等操作，会导致索引失效而进行全表扫描。

2.3.3.10  or连接导致索引失效

2.3.3.11  order by情况

正常情况：索引参与了排序，没有违反最左匹配原则。
 非正常情况：非索引类参与排序，违反最左前缀法则，导致额外的文件排序（会降低性能）。

2.3.3.12  group by情况

正常情况：索引参与了分组排序，没有违反最左匹配原则。
 非正常情况：非索引类参与分组排序，违反最左前缀法则，导致产生临时表（会降低性能）。

还有很多，就不再一一举例了。

特别 注意数据库版本的不同，可能会导致不同的输出结果，我的数据库是

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 14
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

不过索引的90%的案例功能还是一样的！！！