Monthly Archives: July 2014

Advance for MySQL Pagination

看到叶金荣的一篇关于mysql分页的文章,结合雅虎之前发的一篇PDF 谈谈自己的看法

在叶子的文章里谈到了使用inner join 从而减少了对page的扫描也就是减少了所谓的回表 例如:

SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)

通过直接对id的操作 而不是整张表的扫描 通过id 的join 抓出符合条件id 然后通过ID 再去做数据的抓取。这样就避免了对不需要的页面的扫描。

不过这样也不是最佳的方法 还可以通过对id 的 range更加缩小范围 例如:

我们要分100条记录分一页 可以写成

$page_size=100 select * from t where id > 99 order by id asc limit $page_size ; select * from  t where id >199 order by id asc limit $page_size;

尽量避免limit M,N 这种写法 mysql在对M值很大 而offset很小的时候的处理方式很不人性化 ,所以尽量不要使用offset来取得特定行数。

在这里有一个问题 比如根据不是唯一索引的column分页 那么可能存在一个问题,例如一个列column1存在11个key=100的值 那么你使用limit N 之后取到的min value还是同一个值
这种情况如何处理? 给个例子:

比如要每10条记录分一页

select * from t  order by column1 desc  limit 10

注意这里取到的min value还是100 (11个连续的100) 对下面的分页会产生影响,如何处理?

雅虎给出的方案非常好 取一个extra的column 例如PK 或者unique index key 例如:

select * from t  order by column1 desc, id desc   limit 10 -- 第一个页
select * from t  where column1 <=minvalue_col1 and (id < minvalue_id or column1 < minvalue_col1) limit 10  ---第二个页

这样就确保了唯一性 保证了每页的数据不会重复 思想就是通过add一个唯一的extra 取得这个extra的边界值 结合range column来进行分页。

这个SQL 还能被优化成:

SELECT m2.* FROM t m1, t m2  WHERE m1.id = m2.id  AND m1.column1 <= minvalue_col1
AND (m1.id < minvalue_id OR m1.column1 < minvalue_col1)  ORDER BY m1.column1 DESC, m1.id DESC  LIMIT 10; 

核心思想: 通过extra过滤 配合ID扫描 避免大量的回表操作 这样就达到了要取多少条 就扫描多少条 (in page)

SSD是否该开启’nobarrier’标示?

在笔记本上用了2年的SSD,这几天突然想了个问题–挂载SSD是否该开启’nobarrier’标示?问了朋友和网友,众说纷纭,查找了一些资料,做了总结–It depends!
Depending什么?要搞清楚Depending什么,先要明白何谓barrier?

何谓barrier
引用mount manual:

barrier=0 / barrier=1  
              This  disables  /  enables the use of write barriers in the jbd code.  barrier=0 disables, barrier=1 enables (default). This also requires an IO stack which can support barriers, and if jbd gets an error on a barrier write, it will disable barriers again with a warning.  Write  barriers  enforce  proper  on-disk ordering  of  journal  commits,  making volatile disk write caches safe to use, at some performance penalty.  If your disks are battery-backed in one way or another, disabling barriers may safely improve performance.

写的不清不楚.
其实简单说barrier是保证日志文件系统的WAL(write ahead logging)一种手段–现代日志文件系统如ext4有个journal区,类似数据库领域的redo log,用于意外崩溃后的快速恢复.数据写入磁盘时,理应先写入journal区,再写入数据在磁盘的实际对应位置;磁盘厂商为了加快磁盘写入速度,磁盘 都内置cache,数据一般都先写入磁盘的cache.
cache能加快写入速度,当然是极好的东西,但磁盘一般会对cache内缓存数据排序使之最优刷新到磁盘,这样就可能导致要刷新的实际数据和 journal顺序错乱;一旦系统崩溃,下次开机时磁盘要参考journal区来恢复,但此时journal记录顺序与数据实际刷新顺序不同就会导致数据 反而”恢复”到不一致了.而barrier如其名–‘栅栏’,先加一个’栅栏’,保证journal总是先写入记录,然后对应数据才刷新到磁盘,这种方 式保证了系统崩溃后磁盘恢复的正确性,但对写入性能有蛮大影响.

何时开启SSD’nobarrier’标示
前面说道barrier不开启情况下journal记录会和数据刷新顺序不一致,但journal只有恢复时才会用,所以只要disk无需恢复就行:
1.BBWC (Battery-Backed Write Cache)
通常企业级服务器都有HBA,提供单独电池,这样即使系统崩溃,磁盘一直有电供着.
2.FBWC (Flash-Backed Write Cache)
类似BBW,但不用电池而是使用flash做存储,掉电时有一个大电容供电,足够将磁盘cache中的内容写入flash.
3.其他供电方式
UPS、笔记本电池等等
4.无cache或禁用cache
这样子写就是同步的了,自然barrier也就没什么意义;Percona的《Tuning For Speed: Percona Server and Fusion-io》上说利用ioMemory的无cache特性,自然无需write barrier.

除了以上4种情况,其他情况下SSD不要开启”nobarrier”标示

参考
Barriers and journaling filesystems
Barriers, Caches, Filesystems
Is disabling barriers for ext4 safe on a laptop with battery?

mylogmnr:MySQL binlog logmnr

1.mylogmnr介绍

此脚本主要是用来整理mysqlbinlog解析binlog得到的文本。只针对binlog用ROW模式的update,delete,insert语句。整理后的sql文本可以是易读的整个数据库的,也可以是易读的针对一个表的,同时可以是redo sql或者是undo sql。

注意:此脚本可能存在风险,如mysqlbinlog可能会转义某些字符,以及一些未考虑到的情况。此脚本仅用于测试、诊断问题、学习用途等,不要用于数据恢复等生产环境。使用此脚本产生的问题本人不承担任何责任。

2.mylogmnr所需条件

此脚本是用perl编写,这个一般的Linux都有自带。
另外,需要用到DBD::mysql,DBI模块,这个主要用来查询表的元数据。
还需要一个对所有数据库都有只读查询权限的用户(建议操作是使用slave上的)。

3.mylogmnr使用步骤
3.1 第一步:模拟操作

例如在test库里有个tt表:

mysql> select * from tt;
+----+-------+---------------------+----------+
| id | name  | ctime               | sary     |
+----+-------+---------------------+----------+
|  1 | qqq   | 2014-06-13 14:22:30 |     -222 |
|  2 | ccc   | 2014-06-13 14:22:30 |     -222 |
|  3 | ddd   | 2014-06-13 14:22:30 |     -222 |
|  4 | eee   | 2014-06-13 00:00:00 | 33333300 |
|  5 | rere  | 2014-06-13 00:00:00 |     7777 |
|  8 | rere  | 2014-06-13 00:00:00 |     7777 |
|  9 | inc01 | 2014-06-16 00:00:00 |      999 |
| 10 | inc02 | 2014-06-16 00:00:00 |    11000 |
+----+-------+---------------------+----------+
8 rows in set (0.01 sec)


然后执行一系列操作:
mysql> insert into tt values(11,'test01','2014-06-26',-555);
Query OK, 1 row affected (0.06 sec)

mysql> insert into tt values(12,'test02','2014-05-26',88555);
Query OK, 1 row affected (0.00 sec)

mysql> 

mysql> update tt set ctime='2014-07-08' where id<8;
Query OK, 5 rows affected (0.04 sec)
Rows matched: 5  Changed: 5  Warnings: 0

mysql> 
mysql> delete from tt where id<3;
Query OK, 2 rows affected (0.01 sec)


操作后的数据情况:
mysql> select * from tt;
+----+--------+---------------------+----------+
| id | name   | ctime               | sary     |
+----+--------+---------------------+----------+
|  3 | ddd    | 2014-07-08 00:00:00 |     -222 |
|  4 | eee    | 2014-07-08 00:00:00 | 33333300 |
|  5 | rere   | 2014-07-08 00:00:00 |     7777 |
|  8 | rere   | 2014-06-13 00:00:00 |     7777 |
|  9 | inc01  | 2014-06-16 00:00:00 |      999 |
| 10 | inc02  | 2014-06-16 00:00:00 |    11000 |
| 11 | test01 | 2014-06-26 00:00:00 |     -555 |
| 12 | test02 | 2014-05-26 00:00:00 |    88555 |
+----+--------+---------------------+----------+
8 rows in set (0.00 sec)

对应的binlog如下:
mysql> show master status;
+------------------+----------+---------------
| File             | Position | Binlog_Do_DB |
+------------------+----------+---------------
| oel58-bin.000006 |     1211 |               
+------------------+----------+---------------
1 row in set (0.00 sec)

3.2 第二步:使用mysqlbinlog解析对应的binlog
mysqlbinlog最好限制好时间段,这个时间段越少越好(不过我遇到过指定启始时间等解析报错的情况):

mysqlbinlog -v --base64-output=DECODE-ROWS --start-datatime="2014-06-21 09:24:20" --stop-datetime="2014-06-21 09:30:20" mysql-bin.001865 > 001865_2.sql

[root@oel58 ~]#  mysqlbinlog -v --base64-output=DECODE-ROWS /var/lib/mysql/oel58-bin.000006 > 6666666.sql
[root@oel58 ~]# 
[root@oel58 ~]# ls -al 6666666.sql 
-rw-r--r-- 1 root root 4612 Jun 26 16:44 6666666.sql

3.3第三步:mylogmnr.pl使用
可以使用下面的方式获得使用帮助:

[root@oel58 ~]# perl /home/oracle/mylogmnr.pl

=====================================================================
Info  :
        Created By noodba (www.noodba.com) .
      Modified from parse_binlog.pl by  junda@alipay.com
        Just use it for testing or studying
Usage :
Command line options :

   -h,--help           Print Help Info. 
   -P,--port           Port number to use for local mysql connection(default 3306).
   -u,--user           user name for local mysql(default qry).
   -p,--pswd           user password for local mysql(can't be null).
   -lh,--lhost         ip for mysql where info is got(can't be null).  
   -f,--sqlf           the sql file which will be parsed.
   -o,--op             redo sql or undo sql(default redo sql)
   -t,--tbn            table name   
Sample : 
   shell> perl mylogmnr.pl -u qry -p 123456 -f /tmp/aaa.sql
==========================================================================

生成整段日志的redo,输出文件为 输入文件名后加“.redo”:
[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql

生成整段日志中某个表的redo,输出文件为 输入文件名后加“.redo”:
[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql -t test.tt

生成整段日志的undo,输出文件为 输入文件名后加“.undo”:
[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql -o undo

生成整段日志中某个表的undo,输出文件为 输入文件名后加“.undo”:
[root@oel58 ~]# perl /home/oracle/mylogmnr.pl -u qrytest -p 123456 -lh 192.168.137.128 -f /root/6666666.sql -t test.tt -o undo

redo 文件例子:

[root@oel58 ~]# cat 6666666.sql.redo
ROLLBACK; 
BEGIN; 
#140626 16:36:13 server id 1  end_log_pos 244 CRC32 0xd009758c 	Table_map: `test`.`tt` mapped to number 73 
INSERT INTO test.tt  VALUES( 11 , 'test01', '2014-06-26 00:00:00', -555                ); 
COMMIT; 
BEGIN; 
#140626 16:36:28 server id 1  end_log_pos 460 CRC32 0xd0f2e3a3 	Table_map: `test`.`tt` mapped to number 73 
INSERT INTO test.tt  VALUES( 12 , 'test02', '2014-05-26 00:00:00', 88555               ); 
COMMIT; 
BEGIN; 
#140626 16:37:26 server id 1  end_log_pos 676 CRC32 0x2d429457 	Table_map: `test`.`tt` mapped to number 73 
UPDATE test.tt SET id=1,name='qqq',ctime='2014-07-08 00:00:00',sary=-222                 WHERE id=1 and name='qqq' and ctime='2014-06-13 14:22:30' and sary=-222                ; 
UPDATE test.tt SET id=2,name='ccc',ctime='2014-07-08 00:00:00',sary=-222                 WHERE id=2 and name='ccc' and ctime='2014-06-13 14:22:30' and sary=-222                ; 
UPDATE test.tt SET id=3,name='ddd',ctime='2014-07-08 00:00:00',sary=-222                 WHERE id=3 and name='ddd' and ctime='2014-06-13 14:22:30' and sary=-222                ; 
UPDATE test.tt SET id=4,name='eee',ctime='2014-07-08 00:00:00',sary=3.33333e+07          WHERE id=4 and name='eee' and ctime='2014-06-13 00:00:00' and sary=3.33333e+07         ; 
UPDATE test.tt SET id=5,name='rere',ctime='2014-07-08 00:00:00',sary=7777                 WHERE id=5 and name='rere' and ctime='2014-06-13 00:00:00' and sary=7777                ; 
COMMIT; 
BEGIN; 
#140626 16:37:39 server id 1  end_log_pos 1099 CRC32 0xf0e497d3 	Table_map: `test`.`tt` mapped to number 73 
DELETE FROM test.tt where id=1  and name= 'qqq' and ctime= '2014-07-08 00:00:00' and sary= -222                ; 
DELETE FROM test.tt where id=2  and name= 'ccc' and ctime= '2014-07-08 00:00:00' and sary= -222                ; 
COMMIT; 
ROLLBACK;

undo文件例子:

[root@oel58 ~]# cat 6666666.sql.undo
ROLLBACK; 
BEGIN; 
INSERT INTO test.tt  VALUES( 2 , 'ccc', '2014-07-08 00:00:00', -222                ); 
INSERT INTO test.tt  VALUES( 1 , 'qqq', '2014-07-08 00:00:00', -222                ); 
#140626 16:37:39 server id 1  end_log_pos 1099 CRC32 0xf0e497d3 	Table_map: `test`.`tt` mapped to number 73 
COMMIT; 
BEGIN; 
UPDATE test.tt SET id=5 ,name='rere' ,ctime='2014-06-13 00:00:00' ,sary=7777                 WHERE  id=5 and name='rere' and ctime='2014-07-08 00:00:00' and sary=7777                ; 
UPDATE test.tt SET id=4 ,name='eee' ,ctime='2014-06-13 00:00:00' ,sary=3.33333e+07          WHERE  id=4 and name='eee' and ctime='2014-07-08 00:00:00' and sary=3.33333e+07         ; 
UPDATE test.tt SET id=3 ,name='ddd' ,ctime='2014-06-13 14:22:30' ,sary=-222                 WHERE  id=3 and name='ddd' and ctime='2014-07-08 00:00:00' and sary=-222                ; 
UPDATE test.tt SET id=2 ,name='ccc' ,ctime='2014-06-13 14:22:30' ,sary=-222                 WHERE  id=2 and name='ccc' and ctime='2014-07-08 00:00:00' and sary=-222                ; 
UPDATE test.tt SET id=1 ,name='qqq' ,ctime='2014-06-13 14:22:30' ,sary=-222                 WHERE  id=1 and name='qqq' and ctime='2014-07-08 00:00:00' and sary=-222                ; 
#140626 16:37:26 server id 1  end_log_pos 676 CRC32 0x2d429457 	Table_map: `test`.`tt` mapped to number 73 
COMMIT; 
BEGIN; 
DELETE FROM test.tt where id=12  and name= 'test02' and ctime= '2014-05-26 00:00:00' and sary= 88555               ; 
#140626 16:36:28 server id 1  end_log_pos 460 CRC32 0xd0f2e3a3 	Table_map: `test`.`tt` mapped to number 73 
COMMIT; 
BEGIN; 
DELETE FROM test.tt where id=11  and name= 'test01' and ctime= '2014-06-26 00:00:00' and sary= -555                ; 
#140626 16:36:13 server id 1  end_log_pos 244 CRC32 0xd009758c 	Table_map: `test`.`tt` mapped to number 73 
COMMIT; 
ROLLBACK;

3.4 第四步:回滚数据(请在测试机上进行)
初始数据情况如下:

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2014-06-26 16:55:56 |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from tt;
+----+--------+---------------------+----------+
| id | name   | ctime               | sary     |
+----+--------+---------------------+----------+
|  3 | ddd    | 2014-07-08 00:00:00 |     -222 |
|  4 | eee    | 2014-07-08 00:00:00 | 33333300 |
|  5 | rere   | 2014-07-08 00:00:00 |     7777 |
|  8 | rere   | 2014-06-13 00:00:00 |     7777 |
|  9 | inc01  | 2014-06-16 00:00:00 |      999 |
| 10 | inc02  | 2014-06-16 00:00:00 |    11000 |
| 11 | test01 | 2014-06-26 00:00:00 |     -555 |
| 12 | test02 | 2014-05-26 00:00:00 |    88555 |
+----+--------+---------------------+----------+
8 rows in set (0.01 sec)


执行回滚脚本:
[root@oel58 ~]# mysql < 6666666.sql.undo

mysql> select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2014-06-26 16:56:44 |
+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> select * from tt;
+----+-------+---------------------+----------+
| id | name  | ctime               | sary     |
+----+-------+---------------------+----------+
|  1 | qqq   | 2014-06-13 14:22:30 |     -222 |
|  2 | ccc   | 2014-06-13 14:22:30 |     -222 |
|  3 | ddd   | 2014-06-13 14:22:30 |     -222 |
|  4 | eee   | 2014-06-13 00:00:00 | 33333300 |
|  5 | rere  | 2014-06-13 00:00:00 |     7777 |
|  8 | rere  | 2014-06-13 00:00:00 |     7777 |
|  9 | inc01 | 2014-06-16 00:00:00 |      999 |
| 10 | inc02 | 2014-06-16 00:00:00 |    11000 |
+----+-------+---------------------+----------+
8 rows in set (0.01 sec)

4. 联系方式
EMAIL:qiuwsh@gmail.com
Q Q : 570182914
Phone: 13817963180
Weibo: weibo.com/noodba

5.参考资料
1 改造自parse_binlog.pl by junda@alipay.com

给nautilus添加”修复PDF字体”右键

Linux下看PDF是件麻烦事,假如没有内嵌字体而系统又不包含指定的字体,显示效果就很很差,针对中文字体显示问题这篇文章可以参考,但最近我遇到无内嵌英文字体的pdf显示问题:虽然我系统中有从win8拷过来的字体,但一些字体比较不常见,显示效果很差如下:

Evince提示:

ghostscript能将未嵌入字体替换为支持的字体,但每次命令行操作太麻烦,于是想在natilus下添加一个右键菜单,鼠标点点就行~

具体步骤
官方说nautilus添加菜单有两种办法:natilus-scriptnautilus-extension,不过都要自己写脚本,发现第三方的nautilus-actions能够很方便的定制出右键菜单,遂试之~
1.安装nautilus-action
sudo pacman -S nautilus-actions

2.通过nautilus-actions-config-tool来定制
先指定名字、描述、图标等基本信息:

然后是运行的命令

其实等同于
/usr/bin/gs -o 输出pdf -dPDFSETTINGS=/prepress -sDEVICE=pdfwrite 源文件

至于%d等意思见下图:

更聪明的显示–只对pdf文件出现’Repair PDF Font’右键:

3.最后
保存后,以后鼠标右键就会出现新栏”Repair PDF Font”,点击后会在当前目录多出一个repaired的文件

这次打开显示就正常了:

参考:
Gnome中建立Open Terminal Here右键菜单
The pdf viewer “evince” on Linux can not display some math symbols correctly