分类目录归档:数据库技术

MySQL无法插入Emoji表情问题

测试发现iPhone提交的Emoji表情无法插入MySQL数据库,Java层抛出的异常显示为:

1
2
java.sql.SQLException: Incorrect string value: '\xF0\x9F\x98\x84...' 
    for column 'remark' at row 1

解决这个问题的方法是将MySQL(5.5.3 or later)的字符集从utf8改为utf8mb4,utf8mb4表示支持4个字节表示的UTF-8编码。要理解这个方法,需要知道以下前提知识:

1. UTF-8编码
UTF-8编码是Unicode字符集的一种可变长编码方式,也是目前国际上最通用的一种编码方式,它的好处在于完全兼容ASCII码和ISO 8859-1(Latin-1)编码,最多4个字节就能表示Unicode字符集中的所有字符(U+000000 - U+10FFFF, 共1114112个code points,即码位),用它来编码Unicode字符集时的可编码区间和所需字节数如下:

1
2
3
4
5
6
7
8
9
+-----------------------+--------+
| U+000000 - U+00007F   | 1个字节 |
+-----------------------+--------+
| U+000080 - U+0007FF   | 2个字节 |
+-----------------------+--------+
| U+000800 - U+00FFFF   | 3个字节 |
+-----------------------+--------+
| U+010000 - U+10FFFF   | 4个字节 |
+-----------------------+--------+

作为近亲,顺便也了解下UTF-16和UTF-32编码。UTF-16也是可变长编码方式,用它来编码Unicode字符集时的可编码区间和所需字节数如下:

1
2
3
4
5
+-----------------------+--------+
| U+000000 - U+00FFFF   | 1个字节 |
+-----------------------+--------+
| U+010000 - U+10FFFF   | 2个字节 |
+-----------------------+--------+

因此,单从存储空间的角度上看,如果存储的内容大部分为英、意、法等Latin-1字符,那么选择UTF-8编码较为合适,如果存储的内容大部分为CJK(东亚文字,Chinese, Japanese, Korean),那么选择UTF-16编码更合适。

至于UTF-32,它是一个定长的编码方式,对所有的Unicode字符采用统一的4字节长度编码方式,它的好处是计算机处理方便,坏处显而易见,太费存储空间,实际中较少使用。

2. MySQL对utf8的处理方式
MySQL在建表时可以指定字符编码为utf8,但是奇葩的是,MySQL的CHARSET=utf8只能表示Unicode字符集中一部分,通过查看属性可知它最多只能编码所有可以3个字节表示的Unicode字符:

1
2
3
4
5
6
mysql> show character set where charset = 'utf8';
+----------+-----------------------------+---------------------+--------+  
| Charset  | Description                 | Default collation   | Maxlen |  
+----------+-----------------------------+---------------------+--------+  
| utf8     | UTF-8 Unicode               | utf8_general_ci     |      3 |  
+----------+-----------------------------+---------------------+--------+

也就是说,Unicode码位U+010000-U+10FFFF之间的字符在MySQL中是无法用CHARSET=utf8来表示的,CHARSET=utf8只能表示约5.88% 的Unicode字符。注:(U+00FFFF + 1) / (U+10FFFF + 1) = 5.88%。

MySQL 5.5.3以后加了一种utf8mb4的编码类型,它的Maxlen为4,支持4字节长度的UTF-8编码,如下:

1
2
3
4
5
6
mysql> show character set where charset = 'utf8mb4';
+----------+-----------------------------+---------------------+--------+  
| Charset  | Description                 | Default collation   | Maxlen |  
+----------+-----------------------------+---------------------+--------+  
| utf8mb4  | UTF-8 Unicode               | utf8mb4_general_ci  |      4 |  
+----------+-----------------------------+---------------------+--------+

根据最新的Unicode 6.1版本,Emoji表情所在码位为U+1F300 - U+1F64F,因此需要用4个字节编码,所以,出现了本文开头出现的SQLException异常。

Emoji表情无法插入只是MySQL utf8字符集无法处理U+010000以上Unicode字符的一个特例,有了utf8mb4之后,为了今后少踩些这方面的坑,应尽量采用CHARSET=utf8mb4来指定数据表字符集。

--EOF--

DBCP预检测连接有效性

今天碰到一个SQL查询操作,后端MySQL驱动抛出一个CommunicationsException的异常:

1
2
3
4
5
6
7
8
9
10
11
org.springframework.dao.RecoverableDataAccessException: 
### Error querying database.  
Cause: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: 
The last packet successfully received from the server 
was 57,695,440 milliseconds ago.  
The last packet sent successfully to the server was 57,695,441 milliseconds ago. 
is longer than the server configured value of 'wait_timeout'. 
You should consider either expiring and/or testing connection validity 
before use in your application, 
increasing the server configured values for client timeouts, or using 
the Connector/J connection property 'autoReconnect=true' to avoid this problem.

看这个提示很眼熟,以前也碰到过,起因是DBCP连接池在执行SQL语句时未验证连接有效性。本文就此问题的相关知识和解决方法做个总结吧。

数据库为提升利用率一般会回收长时间空闲的连接。MySQL中默认这个时间为28800s,也就是8小时,由wait_timeout参数指定:

1
show global variables like 'wait_timeout';

DBCP连接池的基本思路一次性创建多个数据库连接,当上层应用需要执行SQL时,从连接池中取出一条空闲连接,执行完之后将连接归还连接池,这样避免了数据库连接的重复创建和销毁。当连接池中的连接长时间处于空闲状态时,连接另一头的MySQL会根据设置的wait_timeout值大小回收这些连接,但是DBCP对连接被回收却不知情,因此,只要应用有请求,连接池就会把空闲连接分配出去,尽管这已是一条半闭合的连接,这就造成了通过这条连接执行的SQL语句运行失败,也就触发了数据库驱动层抛出CommunicationsException。

DBCP提供多种方式解决这一问题,有文章已经总结过了,其实最简单、最有效的方法就是利用DBCP提供的validationQuery参数进行连接的预检测,它会在与连接池交互的过程中加入一些钩子,定点执行validationQuery指定的SQL语句,如果SQL语句执行成功,表示此连接有效,分配给应用,如果执行失败,则丢弃此连接,这种方法还能应对网络故障等问题造成的MySQL连接失效问题。其他的一些方法,比如空闲连接检测,乐观获取连接等方式,都无法保证完全对应用透明,应用还是能感知到数据库操作失败。

DBCP配置项中,与validationQuery相关的有validationQuery,testOnBorrow,testOnReturn,testWhileIdle等几个,详见官方文档。validationQuery要求必需是个SELECT类型的SQL语句,至少返回一行,由于它会在所有应用的SQL语句执行之前运行一次,所以原则上应该对数据库服务器带来的压力越小越好,在不同类型的数据库中有不同的推荐值,这里有篇文章对此进行了总结,MySQL推荐使用“SELECT 1”。testXXX系列的配置参数用来指定运行validationQuery的时机,比如testOnBorrow设为true表示从连接池中获取连接前运行validationQuery,testOnReturn设为true表示将连接归还连接池前运行validationQuery,testWhileIdle要与timeBetweenEvictionRunsMillis、numTestsPerEvictionRun等参数配合使用,只要设置得当,DBCP会定期对连接池中的空闲连接进行有效性验证。默认情况下,testOnBorrow设置为true,testOnReturn和testWhileIdle设置为false。因此,支持数据库连接预检测的Spring DBCP连接池参数可以简化为以下配置:

1
2
3
4
5
6
7
8
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
    destroy-method="close">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://10.10.120.16:3306/db" />
    <property name="username" value="username" />
    <property name="password" value="password" />
    <property name="validationQuery" value="SELECT 1" />
</bean>

关于使用validationQuery参数来预检测连接有效性的缺陷,前文已有提及,它肯定会带来数据库使用效率的降低,特别是大量短连接的场景中。一般来说,局域网中执行一条“SELECT 1”的耗时约为1~5ms,如果能接受这种程度的损耗,那么推荐使用预检测连接的方式来解决连接池中连接失效的问题,它很粗暴但是最有效。

--EOF--

几种提高数据库性能的改进措施

1. 使用缓存产品。将应用程序请求的热点数据放入缓存,减少数据库IO,对于需要频繁更新的操作(例如微博的关注人数、粉丝数、微博数等),可以进行异步操作,读写更新都放在缓存中,定时持久化到数据库中。此外,还可以根据具体的业务需求,选择合适的缓存类型,比如行缓存和页缓存的适用场景有很大的差异。

2. 建立高效的索引。例如通过一个字段查询另一些字段的业务需求,可以通过索引覆盖技术,直接从索引中得到查询结果。

3. 用CPU换IO。对于某些应用数据量大,但查询简单,导致IO负载高而CPU负载轻的场景(UGC网站),通过数据精简和压缩,降低IO。

4. 在应用程序的层面上优化SQL语句。例如mysql的order by rand语法,mysql的实现导致该语法在大数据量下非常低效,此时可以通过应用程序的一些变通方法实现,达到同样的效果。

--EOF--