分类 DataBase 下的文章

Redis优化的几点思路

如果所有的数据都能放到内存中,那么Redis的性能将会非常高

据称能达到10w/s的读取速度

但它也有一定的问题:

1. 持久化问题,大数据量dump时,出现服务暂停

2. 官方没有一个完整的HA方案

针对以上问题的几个解决思路:

1. 使用Replication机制,搭建Master-Slave模型

2. 使用KeepAlived做HA,对外一个VIP,当Master出现故障时,能自动切换到Slave上,并将Slave提升为Master

3. 可以在Slave上建新的Slave,实现一主多从,同时可以将读请求分配到Slave上 

4. Master上不进行持久化dump操作,该操作放在Slave上执行

5. 仍然开启vm,将不常用的value值给交换到磁盘上

小试Mysql存储过程

今天,需要对sohu邮箱用户的过期积分进行清理


过期的积分早已经计算好了,存放在mysql的一个表中


因此,清理的过程逻辑相对比较简单,就是update用户的可用积分,并保留此刻的可用积分作为历史记录


由于都是针对Mysql的操作,所以,就想用Mysql的存储过程来实现


N年之前,我在第一家公司里,曾写过无数的SQL SERVER的存储过程和触发器


MySql的Procedure一直没写过,不过,查了下Mysql的参考手册,还是比较简单的



折叠复制代码




  1. drop procedure if exists pro_ysz;    

  2.   

  3. delimiter //   

  4.   

  5. CREATE PROCEDURE pro_ysz(out rtn int)   

  6. BEGIN  

  7.   

  8.     DECLARE v_userid varchar(50);    

  9.     DECLARE v_totalscore int default 0;   

  10.     DECLARE v_freescore int default 0;   

  11.     DECLARE v_expirescore int default 0;   

  12.     DECLARE v_done int default 0;   

  13.   

  14.     DECLARE cur cursor for select userid,expirescore from ... where ... limit 0,10;   

  15.   

  16.     -- cursor end  

  17.     DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = 1;    

  18.     -- exception occur  

  19.     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION rollback;   

  20.   

  21.     SET rtn = 0;   

  22.   

  23.     open cur;      

  24.           

  25.     fetch cur into v_userid,v_expirescore;   

  26.        

  27.     while v_done = 0 do   

  28.   

  29.         select ...,... into v_totalscore,v_freescore from ... where ... = v_userid;   

  30.            

  31.         select v_userid,v_totalscore,v_freescore;   

  32.            

  33.         if v_freescore > v_expirescore then  

  34.             set v_freescore = v_freescore - v_expirescore;   

  35.         else  

  36.             set v_freescore = 0;   

  37.         end if;   

  38.   

  39.         insert into ... (...) values(v_userid,v_totalscore,v_freescore,now());   

  40.            

  41.  &nb
    sp;      
    update ... set ... = v_freescore where ... = v_userid;   

  42.         update ... set ... = 1 where ... = v_userid;   

  43.   

  44.         commit;   

  45.            

  46.         -- reset score  

  47.         set v_totalscore = 0;   

  48.         set v_freescore = 0;   

  49.         set rtn = rtn + 1;   

  50.   

  51.         fetch cur into v_userid,v_expirescore;   

  52.            

  53.     end while;   

  54.   

  55.     close cur;    

  56.   

  57.  END  

  58.   

  59. //   

  60.   

  61. delimiter ;  






其中,里面有几个要点:



  1. delimiter //,是设置Mysql statement的分隔符,默认是分号;,所以需要先修改成其它的符号,最后修改回去

  2. out rtn int,设置存储过程的返回参数,使用call pro_ysz(@rtn);调用后,可通过select @rtn;来查看返回值

  3. DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET v_done = 1;当游标到末尾时,设置对应的变量,控制后续的循环

  4. DECLARE CONTINUE HANDLER FOR SQLEXCEPTION rollback;设置执行中有异常时,rollback,并继续后续的执行

  5. select v_userid,v_totalscore,v_freescore;在存储过程中模拟print语句来调试,可以打印出对应的变量值


最后,由一个Shell来执行



折叠复制代码




  1. cdate=`date +%Y%m%d%H%M%S`   

  2. echo "$cdate Starting shell..."   

  3. while [ 1 -lt 2 ]   

  4. do   

  5.         mysql -e 'call pro_ysz(@rtn)' db   

  6.         sleep 5   

  7. done  






 

Mysql数据库平滑更新迁移总结

最近,我们对免费和vip邮箱的地址簿进行了升级

 

实际上,主要是升级Mysql数据库

 

对一些表进行了分表(一个大表user_addr按userid进行hash分成若干个小表user_addr_x),分区(按照对应的进行range分区)处理

 

并把一部分数据(主要是最近联系人、临时联系人等信息)转移到Mongodb中存储

 

为了在升级期间,尽可能不影响用户的使用,在和DBA讨论后,使用了如下策略,基本上做到了无缝迁移:

 

  1. 新Mysql服务器Mysql2上启动一个实例,建立相关的表结构,并进行相应的分表和分区。
  2. 在Mysql2上新建一个触发器(根据user_addr表中插入的uid进行hash,确定小表的名字user_addr_x),把主表的数据同步到小表中。

  3. 从Mysql1上dump一份数据,通过一个小程序,将数据分别插入到Mysql2上对应的小表(user_addr_x)中。
  4. 把Mysql2作为Mysql1的一个Slave启动(经DBA测试,Master上普通表可以正常同步到Slave上对应的分区表),这样,用户修改的数据能即时同步到Mysql2对应的小表中。
  5. 准备新的应用程序,实现一个mysql的proxy层,根据uid来获取小表的表明,进而再执行其它的SQL语句。
  6. 新应用程序上线后,自动会往Mysql2中写入,此时断掉Mysql2和Mysql1的同步关系,把Mysql2作为主Master启动。

 

在迁移过程中,DBA还使用了Mysql的BlackHole存储引擎,主要因为Mysql1上还有其它的DB,而我们只需要同步地址簿的DB

 

于是,DBA将其它库表的引擎都设置成了BlackHole,这样,虽然会执行二进制log的sql,但实际上并不会把数据写入真正的DB

 

另外,分表之后,对于之前我们使用的自增字段,我们使用了Mysql的消息队列插件Q4M,从消息队列中获取一个id,然后作为主键插入到小表中

 

经过一段时间的使用,发现它还是比较稳定的,Q4M的使用参见我之前的文章“Mysql的消息队列插件Q4M

 

Oracle和Mysql的中文字段排序

Oracle中可以按照按照拼音、部首、笔画排序,可以通过设置NLS_SORT值来完成对应的排序:


SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序

SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序

SCHINESE_PINYIN_M 按照拼音排序,系统的默认排序方式为拼音排序


以下为按照拼音进行排序的SQL:


select * from user_folder order by nlssort(foldername,'NLS_SORT=SCHINESE_PINYIN_M') desc;


在Mysql中,如果table的字符集是gbk,则默认的排序就是按照拼音来排的:


select * from user_folder order by foldername desc;


如果table的字符集是utf-8的,则默认的排序对中文来说是乱的,我们可以用convert来实现中文排序:


select * from test_utf8 order by convert(foldername using gbk);

Mysql中union和order by的优先级顺序

 在Mysql的参考手册中,并没有对union和order by的优先级进行说明


它建议的方法是,对SQL语句加上(),这样能使SQL的语义更清晰


例如,需要对union后的结果进行order by,则:



折叠复制代码




  1. (SELECT a FROM tbl_name WHERE a=10 AND B=1)  

  2. UNION  

  3. (SELECT a FROM tbl_name WHERE a=11 AND B=2)  

  4. ORDER BY a LIMIT 10;  






如果,需要对单个SQL语句进行order by,则应把order by子句放入圆括号中,如下:



折叠复制代码




  1. (SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10)  

  2. UNION  

  3. (SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);  






另外注意:圆括号中用于单个SQL语句的ORDER BY只有当与LIMIT结合后,才起作用。否则,ORDER BY被优化去除。


我在innodb引擎的2个表上测试了没有加(),进行union和order by的操作,如下:



折叠复制代码




  1. SELECT a FROM tbl_name WHERE a=10 AND B=1  

  2. UNION  

  3. SELECT a FROM tbl_name WHERE a=11 AND B=2  

  4. ORDER BY a LIMIT 10;  






发现,它默认的结果也是先进行union,然后再order by,和第一种情况执行结果相同


不过,为了逻辑清晰,最好还是加上对应的()比较好


另外:Mysql中union可以有union,union distinct,union all这3中形式


union和union distinct会对union后的结果进行排重,保证所有返回的行都是唯一的


union all则会返回所有SELECT语句中得到所有匹配的行

Mysql的消息队列插件Q4M

Q4M(queue for mysql) 是开源的实现队列功能的mysql存储引擎,目前支持mysql 5.1 以上的版本

它已经内置了对多连接并发的的支持,能保证一个消息只能被同一个connection获取到

参考http://q4m.31tools.com/,现在最新的版本是0.8.9

最近,邮箱有一个活动,需要给登录的用户即时发对应的优惠券

开始使用了传统的方法,从mysql的一个表中通过limit来读取一条记录,并更新

但由于这次活动瞬间并发量比较大,这种方法会造成数据库的堵塞

于是,考虑试试Q4M,使用了其二进制方式安装,安装很简单:

   1. tar zxvf mysql-5.1.41-linux-x86_64-glibc23-with-fast-mutexes-q4m-0.8.9.tar.gz

   2. cd q4m-0.8.9-linux-x86_64

   3. cp support-files/q4m-forward /usr/local/mysql/bin/

   4. cp libqueue_engine.so /usr/local/mysql/lib/plugin/

   5. mysql --socket=/data/mysql/mysql.sock < support-files/install.sql

注意:如果mysql是下载的二进制压缩包,则它默认把with-fast-mutexes已经编译进去了

所以,对应的Q4M的包也需要是with-fast-mutexes-q4m-0.8.9.tar.gz的,而不是 without-fast-mutexes-q4m-0.8.9.tar.gz

如果使用源码编译安装:
   1.tar zxvf q4m-0.9.2.tar.gz
   2../configure --with-mysql=/opt/ysz/src/mysql-5.1.44 --prefix=/usr/local/mysql5.1.44/
   3. 可能会报错:my_global.h:80:23: error: my_config.h: No such file or directory
   4. 首先cd /opt/ysz/src/mysql-5.1.44/;make,然后再
   5. vi /opt/ysz/src/mysql-5.1.44/include/my_global.h
   6. my_config.h修改为config.h
   7. make;make install
   8. /usr/local/mysql5.1.44/bin/mysql --socket=/opt/mydata/my3310/my3310.sock < support-files/install.sql

安装后,即可使用其提供的sql进行查询了

目前,Q4M似乎不支持记录二进制log,所以在insert之前需要把log给关闭,这样造成的后果是:

使用了Q4M后,就无法使用Mysql的Master和Slave机制了

   1. #创建queue引擎的表结构
   2. create table product_detail (productid int,cardid varchar(1000)) ENGINE=queue charset=gbk;
   3. #不记录二进制log
   4. SET SQL_LOG_BIN=0
   5. #使用普通的sql语句插入数据,也可使用load data infile来批量灌入数据
   6. insert into product_detail values(1,'1');
   7. #使用queue_wait来获取productid为2的一条数据,设置超时时间为1秒
   8. select cardid from product_detail where queue_wait('product_detail:productid=2',1);
   9. #处理完成删除刚才获取的那条数据
  10. select queue_end()
  11. #处理完成后也可以不删除对应的数据,而释放对应的数据,它将仍然留在队列中
  12. select queue_abort()


MySQL的字符串函数

从blog.sohu.com通过博客大巴搬家后
发现导过来的日志有2个问题:
1.标题中的引号被替换成了html实体编码,如&#8220;
2.日志的最后被加上了sohu blog的上下一页的导航信息

懒得再写程序去处理这些东西了
于是,就想试试Mysql的函数是否能完成这些工作
查了Mysql的参考手册后
发现,Mysql5支持的函数还是很丰富的
可以用以下SQL语句搞定以上2个问题

update boblog_blogs set title=replace(replace(title,'&#8220;','”'),'&#8221;','“');
update boblog_blogs set content=substr(content,1,locate('
',content)+1) where locate('
',content) > 0;



在使用substr函数时要注意,它的start位置是从1开始的,而不是从0开始
刚开始,沿用用编程语言的习惯,给写成了0,结果最后返回的结果都是空
后来才发现,它的起始位置是从1开始的...

使用自增ID作为表的主键

之前,DBA就曾和我说过
最好用一个没有意义的自增长id来作为表的主键
当时也没有怎么去想这么做的意义

直到最近
我们的Sqlite出了一个这样的问题
当Sqlite库出现“database disk image is malformed”错误时
我们采用了Sqlite数据库的恢复这样的办法来恢复
按常理来讲,应该是没问题的

可惜,我们的某个表中使用了Sqlite自身默认的rowid来作为主键
而且,该rowid还和另外一个表的某个字段进行了关联
所以,dump出来的数据再导进去后,某些行的rowid就发生了变化
这样,和另外一个表的对应关系就断掉了.....
无奈,只能通过查询坏掉的sqlite对应记录来来修改对应的rowid
或者,通过很麻烦的办法来重建Sqlite,再去修改对应的status
如果,当初我们使用一个自增长的id来作为主键并关联的话
那么,就不会有这个问题了......


血的教训啊......

Sqlite的executemany命令

Python的Sqlite库提供了一个executemany命令
用于批量插入一批数据

写了个简单的程序测试了下
分别以以下三种方式来执行10000条数据的插入
结果如下:

1.单个insert,单个commit   ------------->   21.153429985    
2.单个insert,最后一次性的commit --->   0.3483850955
3.执行executemany           ------------->   0.0741539001

从以上数据还是能看出,executemany的效率有多高
但是,executemany实际上也相当于是一次commit
所以,如果其中的某条insert操作失败的话
整个执行都会不成功的





Sqlite数据库的恢复

当用程序查询一个Sqlite3数据库时,如果出现
database disk image is malformed
则说明sqlite数据库的某个表中有一部分记录有问题,导致无法select,update或者delete
但出现这种错误的时候,insert是可以进行的


似乎Sqlite3本身并没有提供任何的恢复机制
但通过Sqlite3提供的命令行工具,应该是能恢复其中的正常数据的
以下是操作步骤:


首先从损坏的数据库中dump数据库

sqlite3 Maildir.sqlite3

SQLite version 3.5.6

Enter ".help" for instructions

sqlite> .tables

folders  mails

sqlite> select count(*) from mails;

SQL error: database disk image is malformed

sqlite> .output data.sql

sqlite> .dump



然后新建一个sqlite库,并导入刚才的数据库

sqlite3 new.sqlite3

sqlite> .read data.sql

sqlite> .tables

folders  mails  

sqlite> select count(*) from mails;

25


更简单的一个shell脚本命令:

echo '.dump' | sqlite3 db.bad |sqlite3 db.new

最新文章

最近回复

  • feifei435:这两个URI实际是不一样的
  • zsy: git push origin 分支 -f 给力!
  • 冼敏兵:简单易懂,good fit
  • Jack:无需改配置文件,看着累! # gluster volume se...
  • Mr.j:按照你的方法凑效了,折腾死了。。。。
  • zheyemaster:补充一句:我的网站路径:D:\wamp\www ~~菜鸟站长, ...
  • zheyemaster:wamp2.5(apache2.4.9)下局域网访问403错误的...
  • Git中pull对比fetch和merge | 炼似春秋:[…] 首先,我搜索了git pull和git fe...
  • higkoo:总结一下吧, 性能调优示例: gluster volume s...
  • knowaeap:请问一下博主,你维护的openyoudao支持opensuse吗

分类

归档

其它