分类 SQL 下的文章

MySQL中的那些坑

MySQL使用中遇到了一些小”坑”和一些不便,无聊来总结一下.

IN子句逻辑问题

这个是在给同事调BUG时发现的,展示之前先初始化一些数据.

create table mysql_pitfalls(
	c1 int,
	c2 varchar(128),
	c3 datetime,
	c4 timestamp
);
-- 插入测试数据
insert into mysql_pitfalls(c1,c2,c3,c4) values(1,'1',now(),now());
insert into mysql_pitfalls(c1,c2,c3,c4) values(2,'2',now(),now());
insert into mysql_pitfalls(c1,c2,c3,c4) values(3,'3',now(),now());
insert into mysql_pitfalls(c1,c2,c3,c4) values(4,'4',now(),now());

下面我们分别执行以下两条SQL

mysql> select * from mysql_pitfalls where c1 in (1,2,3);
+------+------+---------------------+---------------------+
| c1   | c2   | c3                  | c4                  |
+------+------+---------------------+---------------------+
|    1 | 1    | 2015-06-06 19:00:05 | 2015-06-06 19:00:05 |
|    2 | 2    | 2015-06-06 19:00:08 | 2015-06-06 19:00:08 |
|    3 | 3    | 2015-06-06 19:00:11 | 2015-06-06 19:00:11 |
+------+------+---------------------+---------------------+
3 rows in set (0.00 sec)

这条SQL很简单,C1列是数值型的,IN逻辑正确.接下面再看一句有逻辑问题的查询,去IN一个字符串

-- 瞬间就被玩坏了
mysql> select * from mysql_pitfalls where c1 in ('1,2,3');
+------+------+---------------------+---------------------+
| c1   | c2   | c3                  | c4                  |
+------+------+---------------------+---------------------+
|    1 | 1    | 2015-06-06 19:00:05 | 2015-06-06 19:00:05 |
+------+------+---------------------+---------------------+
1 row in set, 1 warning (0.00 sec)

同样是数值型C1列,查询如果IN的条件是一个带逗号的字符串,IN条件会错误命中字符串中第一个逗号之前的数字.虽然这条SQL写错了,但这本身算是一个逻辑错误,明明不相等,IN去处怎么能匹配成功呢.再者,由于错误返回了每一条数据,有时候会麻痹开发和测试,误认为功能没有问题.

PS: 可以试试执行IN (‘1,2,3′,’2,3,4′) , 会发现MySQL会求每一个带逗号字符串的第一个值.

 

时间精度丢失(5.6解决)

MySQL在5.6之前,无论是DATETIME类型或是TIMESTAMP类型都无无法存储毫秒,以至于在对时间有毫秒精度要求的场景下,我直接选用了INT型作为存储时间的类型.

首先展示一下,MySQL提供的时间函数,是能够支持毫秒的

mysql> select microsecond('2015-1-1 12:00:00.3213') microsecond;
+-------------+
| microsecond |
+-------------+
| 321300 |
+-------------+
1 row in set (0.00 sec)

之前在测试表中,分别创建了DATETIME和TIMESTAMP的两个字段,C1和C2,我们使用microsecond函数测试一下
阅读全文

禁用高变更频率表的统计信息

前一段时间,稳定运行了两个月的系统,其中一个功能突然变得非常慢,平常只需要零点零几秒完成的操作,需要等上百秒。一度导致系统无法使用影响生产。通过查看AWR报告、查看历史执行计划,发现一条Oracle为一条SQL选用了完全错误的执行计划:连接方式、索引使用情况和数据量预判都完全错误。开始认为是由于统计信息过期,就手动收集了这张表的统计信息。系统也立即恢复了正常。我当时判断这是一个偶然事件,但没有料到刚过了一周,同样的问题再次发生了。

虽然解决方案简单但由于影响严重,决定跟踪一下代码。发现这个操作所涉及到关联一张变更频率非常高的表,仅几分钟到几十分钟,这张表里的数据就完全变样。Oracle默认的统计信息收集频率应该是一天一次,大大低于表的变更频率。对于这个问题有两种方案:

  • 一是等到表张表里的数据分布有代表性时,手动将这张表的统计信息锁住,之后每次查询都使用相同的统计信息。
  • 二是利用oracle动态样例收集(Dynamic Sampling)的特性:清空这张表中所有的统计信息、相关索引的统计信息并锁住。当oracle执行语句前,若发现这张表没有可用的统计信息,则会按照配置参数,对这张表数据做一次采集,以生成较优的执行计划。

阅读全文

数据库分页优化

线上发现一个分页页面较之前明显变慢,随即对这个功能进行了优化。优化的过程很简单,但从这个问题中引出一个较经典的分页问题,需要以培训听形式向组员传播、打疫苗,因为这个问题在开发阶段会往往被忽视掉,却能引发严重的后果。

“ 简单优化”

SELECT *
  FROM (SELECT WMMATDOCSA0_.*, ROWNUM R
           FROM (SELECT WMMATDOCSA0_.*
                    FROM TI_WM_MAT_DOC_SAP_WM WMMATDOCSA0_
                   WHERE NOT EXISTS (SELECT 1
                            FROM TT_WM_MAT_DOC_HEADER MATDOCHEAD1_
                           WHERE TO_CHAR(MATDOCHEAD1_.WM_YEAR_NO) || '-' ||
                                 MATDOCHEAD1_.WM_MAT_DOC_NO =
                                 WMMATDOCSA0_.WM_DOC_NO)
                   ORDER BY WMMATDOCSA0_.ID DESC) )
 WHERE R > 1 AND R < 15 

该SQL使用一个not exists子句作为主要的查询条件,这类SQL的性能严重依赖于exists内子句的性能(外层有多少条数据,exists子句就要执行多少次)。由于TT_WM_MAT_DOC_HEADER数据的增长,内层子句的性能已经明显降低,导致整个SQL效率急剧下降。系统中这条SQL的时间需要使用2秒左右。

方案一:增加索引,提升子句性能
注意SQL第7-9行:

TO_CHAR(MATDOCHEAD1_.WM_YEAR_NO)||'-'||MATDOCHEAD1_.WM_MAT_DOC_NO
              = WMMATDOCSA0_.WM_DOC_NO

这个过滤条件是一个没有索引的函数化列,随着数据量的增加这个过滤条件的效率会降低。可以在这里建立一个函数索引,提高exists子句的执行效率从而提高整个语句的效率。(若exists子句性能慢0.1秒,外层数据有1000条,则总共慢100秒,当然oracle会根据统计信息选择优化的执行计划,这里只是举个例子)

CREATE INDEX FX_MAT_DOC_NO_YEAR ON 
       TT_WM_MAT_DOC_HEADER(to_char(WM_YEAR_NO)||'-'||WM_MAT_DOC_NO);

此外exsit子句有“短路”的特性,一旦匹配到记录就中断返回。整个语句的执行效率相当不错。
阅读全文