浅谈数据库系统中的cache

admin 发表于 mysql 分类，标签: ,
01 七月 2011
3

Cache和Buffer是两个不同的概念，简单的说，Cache是加速“读”，而buffer是缓冲“写”，前者解决读的问题，保存从磁盘上读出的数据，后者是解决写的问题，保存即将要写入到磁盘上的数据。在很多情况下，这两个名词并没有严格区分，常常把读写混合类型称为buffer cache，本文后续的论述中，统一称为cache。

Oracle中的log buffer是解决redo写入的问题，而data buffer cache则解决data block的读写问题。对于Oracle来说，如果IO没有在SGA中命中，都会发生物理IO，Oracle并不关心底层存储的类型，可能是一套存储系统，可能是本地磁盘，可能是RAID 10，也可能是RAID 5，可能是文件系统，也可能是裸设备，或是ASM。总之，Oracle把底层的存储系统称为存储子系统。

在存储系统中，cache几乎无处不在（在后面的论述中，我们统称为cache），文件系统有cache，存储有cache，RAID控制器上有cache，磁盘上也有cache。为了提高性能，Oracle的一个写操作，很有可能写在存储的cache上就返回了，如果这时存储系统发生问题，Oracle如何来保证数据一致性的问题。

Oracle数据库最重要的特性是：Write ahead logging，在data block在写入前，必须保证首先写入redo log，在事务commit时，同时必须保证redo log被写入。Oracle为了保证数据的一致性，对于redo log采用了direct IO，Direct IO会跳过了OS上文件系统的cache这一层。但是，OS管不了存储这一层，虽然跳过了文件系统的cache，但是依然可能写在存储的cache上。

一般的存储都有cache，为了提高性能，写操作在cache上完成就返回给OS了，我们称这种写操作为write back，为了保证掉电时cache中的内容不会丢失，存储都有电池保护，这些电池可以供存储在掉电后工作一定时间，保证cache中的数据被刷入磁盘，不会丢失。不同于UPS，电池能够支撑的时间很短，一般都在30分钟以内，只要保证cache中的数据被写入就可以了。存储可以关闭写cache，这时所有的写操作必须写入到磁盘才返回，我们称这种写操作为write throuogh，当存储发现某些部件不正常时，存储会自动关闭写cache，这时写性能会下降。

RAID卡上也有cache，一般是256M，同样是通过电池来保护的，不同于存储的是，这个电池并不保证数据可以被写入到磁盘上，而是为cache供电以保护数据不丢失，一般可以支撑几天的时间。还有些RAID卡上有flash cache，掉电后可以将cache中的内容写入到flash cache中，保证数据不丢失。如果你的数据库没有存储，而是放在普通PC机的本地硬盘之上的，一定要确认主机中的RAID卡是否有电池，很多硬件提供商默认是不配置电池的。当然，RAID卡上的cache同样可以选择关闭。 阅读全文 »

[技巧] 生产环境下的MySQL数据库备份和恢复

admin 发表于 mysql 分类，标签:
29 六月 2011
9

为什么要用生产环境这个名词呢，这样更能引起我们重视，不是简单随便的拿上你知道的命令就干活了。它有其特殊的环境，不是平时学习实验那样的只是为了达到我们的目的。在企业中运行Mysql是一个完全生产环境，我们必须要考虑很多相关因素。最典型的特点，既然是生产环境，那么就绝对不允许你去宕机，仅仅为了你的备份活动吗（想都不要想啦）。你要清楚备份只是为了保障我们的系统更加安全的运行，减少灾难带来的损失，相对而言只是一个小角色，设想如果服务器压根就没有发生问题的可能，那么备份也就没有存在的必要了，正因为有意外，之后我们才会采取相应的措施。

MySQL的备份主要分为逻辑备份和物理备份。在备份之初我们需要考虑哪些因素呢？
首先确定当前MySQL处在哪种表类型下工作，它们支持事务处理还是非事务的，因为我们需要根据不同的特点做一些设置。
其次要选择备份形式是全备份还是增量备份，各有其优缺点。
为了确保恢复的完整性我们开启binary log功能，同时binlog给我们恢复也带来了很大的灵活性，可以基于时间点或是位置进行恢复。考虑到数据库性能可以将binlog文件保存到其他安全的硬盘中。
正如最初所提到的，备份操作是和应用服务同时运行，这样就十分消耗系统资源，导致数据库服务性能下降，这就要求我们选择一个合适的时间在应用负担很小的时候，再来进行备份操作。
最后要提的就是，备份完不是就没事儿了，怎么就知道备份是否可用的，所以之后的恢复测试完全有必要。

下面就来具体实践一下。
1、逻辑备份和恢复
MySQL的逻辑备份就是将数据库的结构连同数据用一个文本文件备份出来，我们可以对这个文件进行查看和编辑。逻辑备份对于所有的存储引擎都是适用的，而后面提到的物理备份则会根据不同数据库的特点采取不同的备份方法。MySQL逻辑备份的主要工具就是大家熟悉的MySQL自带的mysqladmin这个工具。
#mysqldump -u root -p -all-database > all.sql 备份所有数据库
#mysqldump -u root -p mysqlsytems > mysqlsystems.sql 备份指定数据库
#mysqldump -u root -p mysqlsystems wp discuz > wp_discuz.sql 备份指定数据库中的若干表
TIPs: MyISAM中为了保持数据一致性需要在备份之前对进行备份的数据库加读锁操作flush table with read lock；InnoDB则可以在mysqladmin命令中加入–single-transaction选项，可以生成一个快照以保证数据备份期间的一致性。
以上介绍的是都是全备份，我们都知道全备份是必要的，在应用之初我们可以采用全备份的方式，但随着应用数据的增加，这种备份方式的效率就会变得很低，每次会花掉大量的时间和系统资源。我们有必要在全备份的同时结合增量备份。增量备份是通过备份binary log来实现的。当我们开启MySQL的二进制日志功能之后，对数据库进行的DML（select除外）、DDL操作都会纪录到其中，MySQL Replication就是通过bin-log实现的。每当MySQL重启，数据库都会重新生成一个bin-log文件。比如5月9日凌晨2点开始备份，同时我们也将bin-log进行更新重新生成一个，那么备份完成之后，我们对数据库进行的操作就会被从新纪录到生成的这个新的二进制文件中，增量备份就是要我们保存从5月9日开始，到你进行下一次备份，这期间的操作都在这个bin-log文件里面，我们只需将它备份起来就可以了。 阅读全文 »

MySQL索引的缺点以及使用索引时应注意的事项

admin 发表于 mysql 分类，标签:
28 六月 2011
5

以下的文章主要介绍的是MySQL索引的缺点以及MySQL索引在实际操作中有哪些事项是值得我们大家注意的，我们大家可能不知道过多的对索引进行使用将会造成滥用。因此MySQL索引也会有它的缺点：

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的MySQL索引，或优化查询语句。

使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在MySQL索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

使用短索引 阅读全文 »

[技巧] 教你编写高性能的mysql语法

admin 发表于 mysql 分类，标签: ,
28 六月 2011
0

在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于一个系统不是简单地能实现其功能就可，而是要写出高质量的SQL语句，提高系统的可用性。

在多数情况下，Oracle使用索引来更快地遍历表，优化器主要根据定义的索引来提高性能。但是，如果在SQL语句的where子句中写的SQL代码不合理，就会造成优化器删去索引而使用全表扫描，一般就这种SQL语句就是所谓的劣质SQL语句。在编写SQL语句时我们应清楚优化器根据何种原则来删除索引，这有助于写出高性能的SQL语句。

二、SQL语句编写注意问题
下面就某些SQL语句的where子句编写中需要注意的问题作详细介绍。在这些where子句中，即使某些列存在索引，但是由于编写了劣质的SQL，系统在运行该SQL语句时也不能使用该索引，而同样使用全表扫描，这就造成了响应速度的极大降低。

1. IS NULL 与 IS NOT NULL
不能用null作索引，任何包含null值的列都将不会被包含在索引中。即使索引有多列这样的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。

任何在where子句中使用is null或is not null的语句优化器是不允许使用索引的。

2. 联接列

对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。我们一起来看一个例子，假定有一个职工表（employee），对于一个职工的姓和名分成两列存放（FIRST_NAME和LAST_NAME），现在要查询一个叫比尔.克林顿（Bill Cliton）的职工。

下面是一个采用联接查询的SQL语句， 阅读全文 »

[技巧] Mysql数据库优化技术—配置篇

admin 发表于 mysql 分类，标签:
28 六月 2011
1

(一)减少数据库访问

对于可以静态化的页面，尽可能静态化

对一个动态页面中可以静态的局部，采用静态化

部分数据可以生成XML，或者文本文件形式保存

使用数据缓存技术，例如： MemCached

(二)优化的检测方法

1.用户体验检测

2.Mysql状态检测

在Mysql命令行里面使用show status命令，得到当前mysql状态。

主要关注下列属性：

key_read_requests (索引读的请求数）(key_buffer_size设置影响)

key_reads（索引读响应数)

Key_blocks_used

Qcache_*

Open_tables(通过table_cache的设置影响)

Opened_tables

table_locks

3. 第三方工具检测 阅读全文 »

一个典型支付系统的设计与实现

zhang 发表于 mysql, web开发, 数据库 分类，
03 六月 2011
2

由于公司业务需要，花两周时间实现了一个小型的支付系统，麻雀虽小五脏俱全，各种必须的模块如账户加锁，事务性保证，流水对帐等都是有完整实现的，整个开发过程中有很多经验积累，再加上在网上搜索了一下，大部分都是些研究性的论文，对实际使用价值不大，所以这次特意拿出来和大家分享一下。

这个系统可以用作小型支付系统，也可以用做第三方应用接入开放平台时的支付流水系统。

原来的需求比较负责，我简化一点说:

对每个应用，对外需要提供 获取余额，支付设备，充值 等接口
后台有程序，每月一号进行清算
账户可以被冻结
需要记录每一次操作的流水，每天的流水都要和发起方进行对账

针对上面的需求，我们设置如下数据库:

CREATE TABLE `app_margin`.`tb_status` (
    `appid` int(10) UNSIGNED NOT NULL,
    `freeze` int(10) NOT NULL DEFAULT 0,
    `create_time` datetime NOT NULL,
    `change_time` datetime NOT NULL,

    PRIMARY KEY (`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `app_margin`.`tb_account_earn` (
    `appid` int(10) UNSIGNED NOT NULL,
    `create_time` datetime NOT NULL,
    `balance` bigint(20) NOT NULL,
    `change_time` datetime NOT NULL,
    `seqid` int(10) NOT NULL DEFAULT 500000000,

    PRIMARY KEY (`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `app_margin`.`tb_bill` (
    `id` int AUTO_INCREMENT NOT NULL,
    `bill_id` int(10) NOT NULL,
    `amt` bigint(20) NOT NULL,
    `bill_info` text,

    `bill_user` char(128),
    `bill_time` datetime NOT NULL,
    `bill_type` int(10) NOT NULL,
    `bill_channel` int(10) NOT NULL,
    `bill_ret` int(10) NOT NULL,

    `appid` int(10) UNSIGNED NOT NULL,
    `old_balance` bigint(20) NOT NULL,
    `price_info` text,

    `src_ip` char(128),

    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_bill` (`bill_id`,`bill_channel`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `app_margin`.`tb_assign` (
    `id` int AUTO_INCREMENT NOT NULL,
    `assign_time` datetime NOT NULL,

    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `app_margin`.`tb_price` (
    `name` char(128) NOT NULL,
    `price` int(10) NOT NULL,
    `info` text NOT NULL,

    PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `app_margin`.`tb_applock` (
    `appid` int(10) UNSIGNED NOT NULL,
    `lock_mode` int(10) NOT NULL DEFAULT 0,
    `change_time` datetime NOT NULL,

    PRIMARY KEY (`appid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT `app_margin`.`tb_assign` (`id`,`assign_time`) VALUES (100000000,now());

详细解释如下:

tb_status 应用的状态表。负责账户是否被冻结，账户的类型是什么（真实的需求是应用可能有两种账户，这里为简单所以没有列出） 阅读全文 »

[MySQL] 主键和外键的设计原则

liangkun 发表于 mysql 分类，标签:
05 三月 2011
3

主键和外键是把多个表组织为一个有效的关系数据库的粘合剂。主键和外键的设计对物理数据库的性能和可用性都有着决定性的影响。

必须将数据库模式从理论上的逻辑设计转换为实际的物理设计。而主键和外键的结构是这个设计过程的症结所在。一旦将所设计的数据库用于了生产环境，就很难对这些键进行修改，所以在开发阶段就设计好主键和外键就是非常必要和值得的。

首先来谈：主键。

关系数据库依赖于主键—它是数据库物理模式的基石。主键在物理层面上只有两个用途：
1. 惟一地标识一行。
2. 作为一个可以被外键有效引用的对象。

基于以上这两个用途，下面给出了我在设计物理层面的主键时所遵循的一些原则：

1. 主键应当是对用户没有意义的。如果用户看到了一个表示多对多关系的连接表中的数据，并抱怨它没有什么用处，那就证明它的主键设计地很好。

2. 主键应该是单列的，以便提高连接和筛选操作的效率。

注： 使用复合键的人通常有两个理由为自己开脱，而这两个理由都是错误的。其一是主键应当具有实际意义，然而，让主键具有意义只不过是给人为地破坏数据库提供了 方便。其二是利用这种方法可以在描述多对多关系的连接表中使用两个外部键来作为主键，我也反对这种做法，理由是：复合主键常常导致不良的外键，即当连接表 成为另一个从表的主表，而依据上面的第二种方法成为这个表主键的一部分，然，这个表又有可能再成为其它从表的主表，其主键又有可能成了其它从表主键的一部 分，如此传递下去，越靠后的从表，其主键将会包含越多的列了。 阅读全文 »

PHP中的PDO安装及使用方法

zhang 发表于 PHP, 数据库 分类，标签: ,
05 三月 2011
0
POD扩展是在PHP5中加入，该扩展提供PHP内置类 PDO来对数据库进行访问，不同数据库使用相同的方法名，解决数据库连接不统一的问题。

PDO的目标

提供一种轻型、清晰、方便的 API
统一各种不同 RDBMS 库的共有特性，但不排除更高级的特性。
通过 PHP 脚本提供可选的较大程度的抽象/兼容性。

PDO的特点：

性能。PDO 从一开始就吸取了现有数据库扩展成功和失败的经验教训。因为 PDO 的代码是全新的，所以我们有机会重新开始设计性能，以利用 PHP 5 的最新特性。
能力。PDO 旨在将常见的数据库功能作为基础提供，同时提供对于 RDBMS 独特功能的方便访问。
简单。PDO 旨在使您能够轻松使用数据库。API 不会强行介入您的代码，同时会清楚地表明每个函数调用的过程。
运行时可扩展。PDO 扩展是模块化的，使您能够在运行时为您的数据库后端加载驱动程序，而不必重新编译或重新安装整个 PHP 程序。例如，PDO_OCI 扩展会替代 PDO 扩展实现 oracle 数据库 API。还有一些用于 MySQL、PostgreSQL、ODBC 和 Firebird 的驱动程序，更多的驱动程序尚在开发。 [separator]

安装PDO
我这里是WINDOWS下开发用的PDO扩展，要是你要在Linux下安装配置，请到别的地方寻找。
版本要求：php5.1以及以后版本的程序包里已经带了；php5.0.x则要到pecl.php.net下载，放到你的扩展库，就是PHP所在的文件夹的ext文件夹下；手册上说5.0之前的版本不能运行PDO扩展。配置：
修改你的php.ini配置文件,使它支持pdo.（php.ini这个东西没有弄懂的话，先弄清楚，要修改调用你的phpinfo()函数所显示的那个 php.ini）把extension=php_pdo.dll前面的分号去掉，分毫是php配置文件注释符号,这个扩展是必须的。往下还有

;extension=php_pdo.dll
;extension=php_pdo_firebird.dll
;extension=php_pdo_informix.dll
;extension=php_pdo_mssql.dll
;extension=php_pdo_mysql.dll
;extension=php_pdo_oci.dll
;extension=php_pdo_oci8.dll
;extension=php_pdo_odbc.dll
;extension=php_pdo_pgsql.dll
;extension=php_pdo_sqlite.dll

各各扩展所对应的数据库是：

Driver name Supported databases
PDO_DBLIB FreeTDS / Microsoft SQL Server / Sybase
PDO_FIREBIRD Firebird/Interbase 6
PDO_INFORMIX IBM Informix Dynamic Server
PDO_MYSQL MySQL 3.x/4.x
PDO_OCI Oracle Call Interface
PDO_ODBC ODBC v3 (IBM DB2, unixODBC and win32 ODBC)
PDO_PGSQL PostgreSQL
PDO_SQLITE SQLite 3 and SQLite 2

你要使用哪种数据库，只要把相应的扩展前的注释符号”;”去掉就可以了。

使用PDO 阅读全文 »

MySQL性能调优与架构设计

liangkun 发表于 mysql, 数据库 分类，标签: ,
30 十二月 2010
3
MySQL性能调优与架构设计

MySQL性能调优与架构设计

本书内容简介

《MySQL性能调优与架构设计》以 MySQL 数据库的基础及维护为切入点，重点介绍了 MySQL 数据库应用系统的性能调优，以及高可用可扩展的架构设计。
全 书共分3篇，基础篇介绍了MySQL软件的基础知识、架构组成、存储引擎、安全管理及基本的备份恢复知识。性能优化篇从影响 MySQL 数据库应用系统性能的因素开始，针对性地对各个影响因素进行调优分析。如 MySQL Schema 设计的技巧，Query 语句的性能优化方式方法及MySQL Server中SQL层和存储引擎层的优化思路。同时还分析了 MySQL 数据库中主要存储引擎的锁定机制。架构设计篇则主要以设计一个高可用可扩展的分布式企业级数据库集群环境为目标，分析介绍了通过 MySQL 实现这一目标的多种架构方式。主要包括可扩展和高可用两部分内容，可扩展部分包括设计原则、Replication 的利用、数据切分、如何使用 Cache 和 Search，以及 NDB Cluster等内容。高可用则主要包括 Dual Master、DRBD、NDB Cluster，以及系统监控等方面。
本书主要面向有一定的 MySQL 基础或至少有一定SQL语言基础的读者朋友。 阅读全文 »

mysql数据库中删除已存在数据库时，提示“Unknown table XXX”

liangkun 发表于 mysql 分类，标签:
11 十二月 2010
1
删除mysql数据库出现的问题

删除mysql数据库出现的问题

看上面的图，我们现在利用”drop database message”删除mysql数据库中存在的”message”数据库，结果却提示“ERROR 1051<42S02>:Unknown table ‘message’”。删除存在的数据库为什么会报这个表相关错误呢？

这是由于Mysql的存储引擎store engine 不同造成的.

例如,最开始的engine=myisam,在此基础上建了A表,那么你现在要转成innodb的方式(开启了my.cnf下的innodb的参数)现在 如果再来 select A 表那么是会报不存在的,如果drop 的话会报 mysql error number 1051.这个问题该怎么处理呢? 先关掉my.cnf的innodb参数,然后重启mysql,这样A就可以select 了,把A表的backup 下来,drop 掉A表,然后再开启innodb参数,重起mysql,restore 刚才备份的脚本,这样就可以搞定了. 阅读全文 »

12 下一页 最后一页