开发工作中使用 MySQL 的一些总结

work

saveOrUpdate 的方法？

常见的 insert or update 场景

在平常的开发中，经常碰到这种更新数据的场景：先判断某一数据在库表中是否存在，存在则 update，不存在则 insert。
如果使用Hibernate，它自带saverOrUpdate方法，用起来很方便，但如使用 SQL 语句呢？
最常见的写法是，先通过 select 语句查询记录是否存在，存在则使用 update 语句更新，不存在则使用 insert 语句插入。

但是这样做明显不够优雅，存在几个问题：

• 为了执行一次更新操作，却在程序中使用了两次 SQL 查询，在系统并发大的情况下，性能还是会有影响的。
• 并发量大的情况下，这里的 select 与 insret 、update 操作必须绑定在一起作为一个原子操作，这又涉及到事务问题了。
• 需要针对 insert 与 update 两种情况写不同的 SQL 语句，但是这两个操作业务场景经常是同一个。
• 代码中存在 if else 语句，明明干了一件事，代码却很长。

如何优雅的实现 saverOrUpdate？

Oracle下有 merge 的语法， MySQL 针对这种场景，在标准 SQL 下也有自己的拓展语法。

INSERT INTO IGNORE

数据不存在则插入，存在则无操作。
在 INSERT 语句中使用 IGNORE 关键字实现数据不存在则插入，存在则无操作。它的实现逻辑是，当插入语句出现主键冲突，或者唯一键冲突时，不抛出错误，直接忽略这条插入语句。官网上的相关介绍如下：

If you use the IGNORE keyword, errors that occur while executing the INSERT statement are ignored. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted. With IGNORE, the row is discarded and no error occurs. Ignored errors may generate warnings instead, although duplicate-key errors do not.

MySQL 官方文档提供的标准语法：

INSERT  IGNORE
    INTO tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
或者  
INSERT IGNORE
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...

可见除了多了个 IGNORE 关键字以外，跟一般 INSERT 语句并无区别。

举个例子：

1.建一张测试用的表

CREATE TABLE `test_tab` (
  `name` varchar(64) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.插入一条数据

INSERT INTO `test_tab` (`name`,`age`) values ('zhangsan',24)

当前test_tab表的数据为：

3.再执行一次步骤2的插入语句，则会报异常：

[Err] 1062 - Duplicate entry 'zhangsan' for key 'PRIMARY'

4.对步骤2的 INSERT 语句增加 IGNORE 关键字，则不会报异常，已存在的数据也不会被更新。

INSERT IGNORE INTO `test_tab` (`name`,`age`) values ('zhangsan',24) ;
------
语句执行情况：
受影响的行: 0
时间: 0.000s

当前 test_tab 表的数据为：

DUPLICATE KEY UPDATE 关键字

在 INSERT 语句中使用 ON DUPLICATE KEY UPDATE 关键字实现数据不存在则插入，存在则更新的操作。判断数据重复的逻辑依然是主键冲突或者唯一键冲突。
官网上的相关介绍如下：

if you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. The affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

MySQL 官方文档中提供标准的语法：

INSERT 
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]
或者：
INSERT 
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]
或者：
INSERT 
    [INTO] tbl_name
    [PARTITION (partition_name,...)] 
    [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

可见，还是原来 INSERT 语句的写法。

举个例子：

1.使用刚才新建的 test_tab 表，此时表中的数据如下：

2.使用主键相同的 insert 语句，仍然会 duplicate key 错误

INSERT INTO `test_tab` (`name`,`age`) values ('zhangsan',50) ;
------------
[Err] 1062 - Duplicate entry 'zhangsan' for key 'PRIMARY'

3.对刚才的 INSERT 语句添加 ON DUPLICATE KEY UPDATE
... 关键字：

INSERT INTO `test_tab` (`name`,`age`) values ('zhangsan',50)  
ON DUPLICATE KEY UPDATE `age`=50 ;
------------
受影响的行: 2
时间: 0.025s

4.此时主键为'zhangsan'的数据，age字段已被更新：

5.当然，如果主键不冲突，效果跟一般插入语句是一样的：

INSERT INTO `test_tab` (`name`,`age`) values ('lisi',30)  
ON DUPLICATE KEY UPDATE `age`=30 ;
------------
受影响的行: 1
时间: 0.009s

REPLACE INTO

saveOrUpdate 在 MySQL 中还有另一种实现，即 REPLACE INTO 语句，它用起来有点像 Oracle 的 Merge。判断数据重复的逻辑依然是主键或者唯一键冲突。 MySQL 官方文档中提供标准的语法：

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)] 
[(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
或:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)] 
SET col_name={expr | DEFAULT}, ...
或:
REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
[PARTITION (partition_name,...)]  
[(col_name,...)]
SELECT ...

举个例子：

1.仍然使用上面的 test_tab 表的数据，此时数据如下

2.使用一般的insert语句插入 name=zhangsan 的数据，报主键冲突。但是换成 REPLACE INTO ... 语句则没问题：

REPLACE INTO `test_tab` (`name`,`age`) VALUES ('zhangsan',30) ;
------------
受影响的行: 2
时间: 0.009s

3.结果如下：

对于操作结果来说，很像是 SAVE OR UPDATE，但是实现方式与 INSERT 的“DUPLICATE KEY UPDATE”关键字不同。当使用 REPLACE INTO 语句时，对于重复的数据，是直接删除，然后再插入新数据的。所以它的更新其实不是 UPDATE，而是 DELETE->INSERT 。大多数情况下，使用 REPLACE INTO 完成更新操作并无问题，但是有一种场景必须特别注意：

• 当被更新的表，存在 INSERT，UPDATE ，和 DELETE 触发器时，使用 REPLACE 语句必须特别小心。因为按照业务逻辑，更新完数据后，应该触发 UPDATE 触发器，但是使用 REPLACE 语句的话，会触发 DELETE 和 INSERT 触发器，如果 UPDATE 触发器有一些特殊操作（比如记录操作日志）的话，使用 REPLACE 会导致业务逻辑混乱。

所以当被更新表存在触发器的场景时，使用 INSERT 的“DUPLICATE KEY UPDATE”关键字更合适。

总结

1. 不存在则插入，存在则不操作，使用 INSERT IGNORE INTO 。
2. 不存在则插入，存在则更新，可以考虑 INSERT …… ON DUPLICATE KEY ，或者 REPLACE INTO。
3. INSERT …… ON DUPLICATE KEY 是真正的 saveOrUpdate 操作，REPLACE 会先删除旧数据，再插入新数据。
4. INSERT …… ON DUPLICATE KEY 操作在 5.6.6 版本以前，MyISAM 引擎的表，UPDATE 操作会使用表级锁。
5. 使用单独的 INSERT 或者 UPDATE 语句会比 INSERT …… ON DUPLICATE KEY有更好的性能，对于INSERT 或 UPDATE 明显区分的场景或者性能要求高的情况，前者还是有一定优势。关于它们性能的讨论
6. 有触发器，或者主键使用自增 id 的数据表，慎用 REPLACE INTO 。
7. REPLACE 返回受影响的行数是 DELETE 和 INSERT 操作的总和。
8. 以上三种操作判断数据是否重复，都是使用主键或者唯一键，使用自增 id 作为主键的情况，需要另外指定唯一键。
9. 以上语法 MySQL 5.0 以上都能使用。

参考资料

1. MySQL 官网 INSERT 语法介绍： http://dev.mysql.com/doc/refman/5.6/en/insert.html
2. MySQL 官网 REPLACE 语法介绍： http://dev.mysql.com/doc/refman/5.6/en/replace.html

MySQL 中的 COUNT 的使用经验。

1. COUNT(*) vs COUNT(COL):
   他们在逻辑上是不用的。在 MySQL 中，COUNT(COL) 是不会统计 NULL 列的。例如下面这张 pet 表, COUNT(*) 与 COUNT(owner) 是不同的：

select * from pet;
+--------+---------+
| owner  | species |
+--------+---------+
| Benny  | bird    |
| Diane  | bird    |
| Gwen   | cat     |
| Harold | cat     |
| Adrian | dog     |
| NULL   | dog     |
+--------+---------+
SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        2 |
+---------+----------+
SELECT species, COUNT(owner) FROM pet GROUP BY species;
+---------+--------------+
| species | COUNT(owner) |
+---------+--------------+
| bird    |            2 |
| cat     |            2 |
| dog     |            1 |
+---------+--------------+

1. COUNT(*) 在不同引擎中的差别：
   MyISAM 存储引擎会保存总行数，没有 WHERE 条件的情况，MyISAM 表性能明显优于 INNODB 表。官方手册。

2. COUNT(*) vs COUNT(VAL)：
   他们是等价的，INNODB 中都需要进行全表扫描，并使用合适的索引。INNODB 下看下该表是否有辅助索引，如果有辅助索引的话， count(*)操作会走覆盖索引，走覆盖索引速度会比较快，使用 EXPLAIN可以看到 Using index 。
   COUNT(*) 与 SELECT(*) 中的*在语义上是不同的，SELECT(*) 因为不使用覆盖索引，所以不推荐使用。
   MySQL 查询分析器对于 count(*) 优化在 5.6版本以后

3. COUNT(*) vs COUNT(COL) vs COUNT(VAL):

   • 在没有 WHERE 条件的情况下: COUNT(*) 约等于COUNT(pk) 优于 COUNT(非主键有索引) 优于 COUNT(非主键无索引) 。
   • 除非要统计某列非空值的总数，否则任何情况一律用COUNT(*)，让查询分析器自动选择索引，获得较高的效率。
   • 除非有特殊需要，否则 COUNT(*) 不要加 WHERE 条件,会严重影响效率,如果加了条件 COUNT(*) 和 COUNT(pk) 效率是一致的，COUNT(非主键)效率很低。
   • 多表查询的情况，MySQL 不支持 COUNT(TABLENAME.*) 写法 。

关于 count() 的一些讨论

1. COUNT(*) vs. COUNT(1) vs. COUNT(pk): which is better?

group_concat

使用场景

当两张表 inner join ，两张表记录是一对多的关系时，需要把多的表的记录合并成一行，例如：考试与考试标签 的关系，需要通过一次查询，获得考试信息，以及每个考试对应的所有标签信息，所有标签合并成一个字段，逗号分隔。
语法：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

举个例子：

SELECT t.id as exam_id ,t.`name`  
, GROUP_CONCAT(t2.label_id  order by t2.label_id SEPARATOR ',')  as label_ids
from center_exam.examination t ,center_exam.exam_label t2 
where t.id = t2.biz_id
and t2.type = 'examination'
AND t.tenant_id = 1
GROUP BY t.id

参考资料

MySQL GROUP_CONCAT 函数 官方手册

使用 WHERE ID IN (...) or 临时表 or 批处理

要查询的记录数非常大时，使用 IN 还是 临时表？

我们知道使用 WHERE ID IN (...) 语句时，如果 ID 有加索引，SQL 执行时是会使用索引的。

有一种说法是，当 IN 中查询的数量非常大时，MySQL 无法使用索引，需要使用一张临时表保存 id，再 join 临时表查询。

经过实际测试，发现MySQL 中 in 记录数超过总记录数的一定比例不会使用索引
另外，使用 id in (……)条件需要担心的是，MySQL 对单条语句的长度是有限制的，由 sysvar_max_allowed_packet 参数控制，默认为 1M 。
所以，一般情况下使用 id in (……) 是没有问题的，对于查询数据较大的情况，建议保留使用临时表的方式。

参考资料

关于使用 in 条件与临时表性能差异的讨论
sysvar_max_allowed_packet说明

批处理

关于批处理的一些相关讨论

1. 如何优化批处理
2. 不同数据库之间批处理性能分析

目前项目中使用的 jdbcTemplate.batchUpdate()，并没有做到真正的批处理，通过抓包分析，发现仍然是分多次 SQL 请求到数据库，要做到真正的批处理请求，需要在 jdbc 驱动的连接串中加上rewriteBatchedStatements=true参数，这样batchUpdate()的多条语句才能合并成一个请求发送到数据库。