分类目录归档：DB原理

DB原理

讨论数据库减库存几种方法V1

发表于由

MySQL在电子商务网站使用越来越多，做为第一梯队做MySQL的从业人员，也经常被问到MySQL对于减库存在这块有什么好的处理方法？对于这个问题，想想在课上分享及朋友中交流讲的不下百次。 所以也有了把这个总结一下的想法。
对于减库存的操作，我们需要先从一个更高的Level来看一下，先不用讨论update减1的问题。我们可以把减库存形为简单的分成以下几种：

  • 团购类减库存（库存无限，卖的越多越好） 这个可能大家见的也比较多，就不用多说，这类业务，多卖一些老板反而高兴。
  • 限时活动类（24小时活动产品）
  • 秒杀抢购类活动
  • 正常商品售卖

当然还别的形式，可能有的减订单操作是一个复合型的业务： 如 12306这样的业务，含秒杀，抢购，限时活动于一身，难度空前绝后：）

那么我们再来看一下订单系统里几个难题：
1. 超卖，听说某米是允许在商品接近预警时自动关闭交易（商品下架），并允许一定的超售现象。
2. 羊毛党下单不支付，活动结束，一个也没卖出去。 有的电商的做法是，付款后再减库存，不付款不减，可能出现的问题是，用户犹豫一下，去付款时，告诉你没货了，体验不好。
3. 高并发的抢购，打压到DB中，把DB打死的问题。比较常见的一个问题，早些年某电商还发布过把MySQL死锁去掉的分支，用于支撑抢购里业务，现在MySQL 8.0天生支持，另外也可以考虑在在业务层Nginx上控制。

那么我再回过来看一下减库存的形式，我们可以把减库存，分为两种：

  • 正常商品售卖类减库存
  • 活动促销类减库存

问题分为：是否允许超卖 ，要不要考虑羊毛党的一些形为。 处理好这些问题的情况下支撑高并发业务。另外给老板们提个醒，系统优化是一个全面工作，不只是这里有瓶颈。

我分成几种情况来讨论：

  1. 用户下单减库存 如果允许超卖的情况下， 外加一个预警商品下架（缺货）逻辑来处理，基本可以很快速的跑起来。 业务逻辑最简单适合正常商品逻辑。库存大于多少可以上架之类的一些处理（后台人工调配） 如果不允许超卖， 例如车票这类，处理的办法不在是简单的库存结构设计，可以把每个坐位做为一个商品上架构， 只是车票商品上的属性有自已是那个车，挨着谁之类的信息即可。下单后锁定30分钟… 可以目前各大电商的主流逻辑，只是不同的平台，锁时时间不一样。 防止用户下单后几天后还不支付，让商品可以回归库存，不支付订单的失效过程。 下单减库存也是大家最容易接受的一个形为，但这里把最难的问题抛给了平台，一个商品多少没支付返回库存，目前来看不同平台处理的形为不一样。 不适用于抢购，秒杀点业务。
  2. 支付减库存
    抢购，秒杀，谁先付款谁得的逻辑。 这样可以避免羊毛党下单后不支付的形为，例如，本来只有10个特价产品，羊毛党手快，下单成功，但种种原因没支付，提示商品已售完，但活动结束了，一个商品也没卖出去，老板花的活动费白花了。
  3. 高并发的抢购业务
    例如某米周二中午的抢购，10万个商品，可能是100万人在抢购，这种形为，如果都打到DB，可能是对DB的锁也是一个挑战，可能出现的形为就是不该慢的也慢了，去除检查也是一个好办法。
    现在聪明的人类想好了更好的办法，利用Nginx+LUA引入接入在接层引入摇号方法，基于Cookie在Nginx层拿到了可以购买的资格再往后面放，如果没拿到这个资格就在Nginx上看看，提示你商品已经售完就行。这样每次扩容，只用扩一下Nginx接入层就Ok了，其它不用扩了。

问题分类后，现在来看，对于减库存，如果分类处理后，你会发现，还是挺容易处理的，这里面里也不要再来找我聊用MySQL的乐观锁控制来减库存了，如果你在知数堂的课上学习过，一定会知道多点写入的乐观锁控制会造成更新丢失的现象，同样来讲，单节点的同时并发，不是串行的情况，这事没戏，我确时见过有的公司在这块用了串行事务，他们也跑的挺好，业务量不大。祝你玩的开心。也祝愿你们早日在减库存这块遇到瓶颈，让老板高兴痛苦的给你加薪。

 

作者：吴炳锡　来源：http://wubx.net/ 联系方式： wubingxi#163.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

MySQL binlog后面的编号最大是多大？

发表于由

    在上课中讲到MySQL的binlog是mysql-bin.000001,有细心的学习提到，是不是这个达到mysql-bin.999999后数据库的binlog就要重新开始了？
对于这个问题一时间也被问住了，只是隐约记得这个可以出现大于999999，这次也来细致的研究一下，顺便Mark一下。
MySQL在启动时会扫一下binlog文件，找到sequence后最大的一个，然后产生下个文件，根据这个原理，我们可以先测试一下当有了mysql-bin.999999 后，数据库会不会产生mysql-bin.000001或是mysql-bin.1000000。

测试一：

把mysqld关掉,到日志目录：

touch mysql-bin.999999
[root@zst1 17:26:28 /data/mysql/mysql3307/logs]
#touch mysql-bin.999999

[root@zst1 17:26:37 /data/mysql/mysql3307/logs]
#ls -lh
total 12K
-rw-r----- 1 mysql mysql 923 May 22 22:16 mysql-bin.000001
-rw-r----- 1 mysql mysql 660 May 22 22:28 mysql-bin.000002
-rw-r--r-- 1 root root 0 May 24 17:26 mysql-bin.999999
-rw-r----- 1 mysql mysql 88 May 22 22:19 mysql-bin.index

[root@zst1 17:26:40 /data/mysql/mysql3307/logs]
#chown mysql:mysql *
[root@zst1 17:26:48 /data/mysql/mysql3307/logs]
#/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my3307.cnf &
[root@zst1 17:27:17 /data/mysql/mysql3307/logs]
#ls -lh
total 76K
-rw-r----- 1 mysql mysql 923 May 22 22:16 mysql-bin.000001
-rw-r----- 1 mysql mysql 660 May 24 17:27 mysql-bin.000002
-rw-r----- 1 mysql mysql 194 May 24 17:27 mysql-bin.1000000
-rw-r--r-- 1 mysql mysql 0 May 24 17:26 mysql-bin.999999
-rw-r----- 1 mysql mysql 133 May 24 17:27 mysql-bin.index

启动mysqld
show master status 确认：

root@zhishutang:mysql3307.sock [(none)]>show master status;
+-------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.1000000 | 194 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+-------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

可以看到，并没有挂掉，也没重新从mysql-bin.000001开始。

对于这个问题，我们在来挖一下，具体是什么？
教学使用的版本是mysql-5.7.18下载相应的源码看看 ： sql/binlog.cc 实际找到：
从这里可以看到:
zhishutang.com
if (max_found == MAX_LOG_UNIQUE_FN_EXT)
当找到的数是定义的最大数时，就退出，再找一下MAX_LOG_UNIQUE_FN_EXT定义：

#define MAX_LOG_UNIQUE_FN_EXT 0x7FFFFFFF

root@zhishutang:mysql3307.sock [(none)]>select conv('7FFFFFFF',16,10);
+------------------------+
| conv('7FFFFFFF',16,10) |
+------------------------+
| 2147483647 |
+------------------------+
1 row in set (0.00 sec)

现在看到是最大值是: pow(2,31)-1
我们来测试几个场景：

测试二： 测试当达到mysql-bin.2147483647 再进行flush logs；MySQL的反应。

touch mysql-bin.2147483640

启动后给的警告：

2017-05-24T09:57:43.081308Z 0 [Warning] Next log extension: 2147483641. Remaining log filename extensions: 6. Please consider archiving some logs.
2017-05-24T09:57:43.081436Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170524 17:57:43
2017-05-24T09:57:43.090250Z 0 [Warning] Next log extension: 2147483641. Remaining log filename extensions: 6. Please consider archiving some logs.

root@localhost:mysql3307.sock [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483647 | 194 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

进行切换超限一下试试

root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.
root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3307.sock' (111)
ERROR:
Can't connect to the server

mysqld 直接退出…

报错日志：

2017-05-24T09:59:08.207040Z 3 [Warning] Next log extension: 2147483642. Remaining log filename extensions: 5. Please consider archiving some logs.
2017-05-24T09:59:09.970075Z 3 [Warning] Next log extension: 2147483643. Remaining log filename extensions: 4. Please consider archiving some logs.
2017-05-24T09:59:10.882140Z 3 [Warning] Next log extension: 2147483644. Remaining log filename extensions: 3. Please consider archiving some logs.
2017-05-24T09:59:12.050605Z 3 [Warning] Next log extension: 2147483645. Remaining log filename extensions: 2. Please consider archiving some logs.
2017-05-24T09:59:14.090312Z 3 [Warning] Next log extension: 2147483646. Remaining log filename extensions: 1. Please consider archiving some logs.
2017-05-24T09:59:26.483618Z 3 [Warning] Next log extension: 2147483647. Remaining log filename extensions: 0. Please consider archiving some logs.
2017-05-24T09:59:34.894426Z 3 [ERROR] Log filename extension number exhausted: 2147483647. Please fix this by archiving old logs and updating the index files.
2017-05-24T09:59:34.894441Z 3 [ERROR] Can't generate a unique log-filename /data/mysql/mysql3307/logs/mysql-bin.(1-999)

2017-05-24T09:59:34.894668Z 3 [ERROR] /usr/local/mysql/bin/mysqld: Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.

看样是想产生mysql-bin.(1-999) 这样的文件。 再进行进行下面的测试。

测试三： 测试Binlog达到最大值后，能不能重新开始。

确保mysql-bin.000001不存在到999都不存在

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483646 | 154 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql3307.sock [(none)]>flush logs;
Query OK, 0 rows affected (0.01 sec)

root@localhost:mysql3307.sock [(none)]>show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------+----------+--------------+------------------+------------------------------------------+
| mysql-bin.2147483647 | 154 | | | 61beeb3d-2a88-11e7-9db9-080027f7e774:3-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

root@localhost:mysql3307.sock [(none)]>flush logs;
ERROR 1598 (HY000): Binary logging not possible. Message: Either disk is full or file system is read only while rotating the binlog. Aborting the server.
root@localhost:mysql3307.sock [(none)]>show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql3307.sock' (111)
ERROR:
Can't connect to the server

一样退出。并没有进行日志的轮换使用。

结论：
1. mysql binlog的最大sequence是：pow(2,31)-1 = 2147483647
2. 当binlog接近这个值是小于1000开始向error log中写入警告
3. binlog的sequence达到最大值时，不管有没有mysql-bin.000001类似这样的文件，mysqld都是退出。
4. 在mysql产生binlog时会读取当前日件文目录下的log-bin的base name获取下一个日志文件的后面的Seq。 所以日志目录下文件太多，会影响MySQL的启动及日志切换。 这里也有一个大的隐患运行中给放一个较大的日志文件，在下次日志文件切换时有可能很快就接近于最大值，造成mysqld crash退出。
5. 一定要监控error log的输出。并足够重视。

 

作者：吴炳锡　来源：http://wubx.net/ 联系方式： wubingxi#163.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

MySQL 二进制日志格式基础(一)

发表于由

作者：吴炳锡　来源：http://wubx.net/ 联系方式： wubingxi#163.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.
MySQL二制进日志用于记录数据库的变更记录，这里从结构上讨论一下日志的格式。

每个日志都包含4个字节的magic number 和event的描述包

  1. 日志有前四个字节是magic number： oxfe ox62 0x69 0x6e = 0xfe ‘b”i”n’ 转成整数：1852400382  用处就是读4个字节对比不是这个数，说明就不是二进制日志，就不用处理了。 
      log_event.sh中可以查到
  /* 4 bytes which all binlogs should begin with */
     #define BINLOG_MAGIC        "\xfe\x62\x69\x6e"
  2. 每个event的header大概如下： 
     - 每个event中包含： event的类型，什么时间，由哪版本的MySQL产生的
 - 从header头能找到该event的大小及一些变更信息
  3. 第一个event称为：format descriptor event(Event描述结构:FDE) 用于说明日志的格式
  4. 其它event就是依赖于描述结构不同，用不同的结构记录数据
  5. 最后一个event是： 日志切换事件（log-rotation event）用于指点定下个日志的文件名

每个event的结构大概如下：

+===================+
| event header      |
+===================+
| event data        |
+===================+

现在大多使用的V4结构，从MySQL 5.0起，具体如下：

+=====================================+
| event  | timestamp         0 : 4    |
| header +----------------------------+
|        | type_code         4 : 1    |
|        +----------------------------+
|        | server_id         5 : 4    |
|        +----------------------------+
|        | event_length      9 : 4    |
|        +----------------------------+
|        | next_position    13 : 4    |
|        +----------------------------+
|        | flags            17 : 2    |
|        +----------------------------+
|        | extra_headers    19 : x-19 |
+=====================================+
| event  | fixed part        x : y    |
| data   +----------------------------+
|        | variable part              |
+=====================================+

第一个event是FDE结构没有extra_headers部分，所以固定为19个字节。

FDE的event_data中定长部分为：

  • 2字节的的日志格式版本，从MySQL 5.0后都是4
  • 50字节 用于记录MySQL的版本号 如：5.6.16-64.2-rel64.2-log 不够50字节用0x00填充
  • 4字节 日志产生的时间
  • 1字节 header长度。一般是19，如果大于19，则下面的event都有extra_header字段
    对于FDE变长部分一般为空

其它Event计算

  • header length = x byte
  • data length = (event_lenth -x )byte
  • 数据区里定长部分长度 
      fixed_part = y byte
  variable_part = (event_length - (x+y)) byte

如果给定的X不是19，则存extra_header里面有内存
Y依赖于event_type有不同的大小，需要参考不同的event进行特别处理
参考：http://dev.mysql.com/doc/internals/en/event-data-for-specific-event-types.html

Innodb IO优二 — 数据库表设计

发表于由

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

数据库表设计这块学问比较多，我这里单从互联网角度出发同时结合Innodb的特性给出一些设计方法供大家参考。本文构建大概分两分部分:Innodb的特性及设计中如何利用这种特性。

Innodb特性：

  • Innodb是索引聚集表， 存储结构是BTREE
  • Innodb的表的数据存储是有顺序的，默认是以主建排序，主建即是数据本身，不单独存放。
  • 如果没有主建,Innodb以第一个唯一索引排序，如果连唯一索引也没，Innodb内部会产生一个6字节的字段排序(这个也是性能杀手，所以对这块如果不想花太多时间去想这个事，就添加一个自增的列无业务意义做为主建即可)
  • Innodb表普通索引存储需要包含主建（或是Innodb表聚集的字段）

如何利用这些特性：

  • 对于Insert操作要求比高的表:  建议增加一个自增的列做主建，这样减少数据的写入造成Innodb表在存储上不断的拆叶排序的操作。 通过添加一个自增的列做主建从而达到Innodb表的写入都是顺序IO的形态。所以这种情况，保证在1W/S左右的Insert也是比较容易的。添加一个主建还有另外一个好处，真正的条件将会成为唯一索引或是普通索引， 这样索引单独存放起来后，整体上比原来的表文件会小很多，这样基于条件的查询，可以从一个较少的文件快速定位到需要的行。这样也有机会利用到索引覆盖。
  • 另一种场景，写入少，同时每次读多（读取不是一条记录），这种场景可以考虑不要使用自增的列做为主建，就使用查询条件或是查询条和其它通达到唯一，定义为主建。这样查询就可以一次读到数据。还一种场景如存好友关系，或是股票信息，特别好友关系表类的数据，可以考虑使用两个用户的Id做联合主建，查询时条件中只用自已的Id读所有用户的数据，这样就是一个顺序IO的请求。同样这种情景下对一些数据就可以考虑冗余，减少请求反向的数据操作。
  • 更新最好基于主建或是唯一索引来做，这样才能有机会利用Innodb的行级锁。
  • 互联网中还有一种观点是：页面展现什么，就存成什么样的表。这种在CMS中还能适用，在WEB2.0及相关的应用这种观念就行不通。但可以考虑适当的多处写，实现数据的快速读取及索引表的引入。

从业务形态上来看：

　　建议数据在设计阶段就要考虑那些是大表，可以分为多少个业务，怎么能核心功能拆分。这块举个例子：大家经常听到的淘宝的用户库，商品库，收藏夹库，交易库，评论库等。及其它我们经常也能听到的：认证库，好友关系库等等。到业务形态上后，可以根据不同的形态选择不同的软件来做不要只看到mysql了，这块的设计可以把nosql类的东西也要考虑进来，最终设计数据库的模型。

数据库表的设计这块有可能都是各有建解，欢迎有不同意见的同学一块来讨论mail我的邮箱即可。

Innodb 表和索引结构

发表于由

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

表的结构：

对于MySQL把有的存储引擎都是把表结构的定义存放到.frm文件中。但对于Innodb表同时有一个内部的字典存放到表空间中。所以对于Innodb表不能单纯的移动.frm在不同的MySQL事例下。对于Innodb引擎的表，如果MySQL 删除相应的表或数据库，同时会删除相应的.frm及在表空间的相应的字典信息。在.frm文件只是用来定义表的结构，Innodb把数据和索引都存放到了表空间中。

聚集索引和次要索引：

每一个Innodb表都有一个聚集索引，这个聚信索引和行数据存在一起。

可以用来做聚集索引的列：

  • 如果有声明了主建(primary key)，则这个列可以做为表的主建。
  • 如果没有声明主建，MySQL会用一个唯一索引(UNIQUE)而且是不为空的列做为主建，成为该表的聚信索引。
  • 如果没声明主建，同时也没合适的唯一的索引，Innodb内部会产生一个隐藏的聚集索引:RowID。这个RowID在Innodb的多版本中曾提到过。这个RowID是在插入时产生，并且是自增的增加。所以也是按顺序增长存放。

由于聚集索引和行数据存放一起(在同一个数据页中)，所以利用聚集索引访问数据行时，非常的快，同一个数据页在访问索引时，已经把页加载到Buffer中，在访问数据时，等于了一个顺序IO的访问（内存中完成）。大多数情况下索引和数据都不在一块（MyISAM，数据和索引存到不同的文件中），而聚集索引是有结构的通常是按顺序存放，同时和数据存放在一起，利用索引索引访问大表的数据可以节省许多IO。

对于Innodb次要的索引会包含聚信索引，查询在使用次要索引时，找到聚集索引信息，然后利用聚集索引信息访问行。所以，如果聚集索引过长，会造成空间浪费严重。另外，如果对表或是区间进行Count操作的话，大多数情况较短的次要索引比基于聚集索引快。对于Innodb的聚集索引选择，尽量选择比较短的列做为聚集索引列，是一个好的设计习惯。

索引的物理结构：

Innodb的索引以B-tree的形式存到各个叶点上。索引叶点页的大小默认为16K，当有什么的索引插入叶点时，该叶点至少会保留1/16的空闲空间，用于将来该叶点的索引更新或是插入。

对于顺序写入的索引（无论是递增或是递减，顺序的就行），索引叶点可以达到15/16满。如果是随机的索引写入行为，叶点只会达到1/2到15/16满。当叶点填充在1/2以下满，或是被删除到1/2下满时，Innodb会缩短索引树，试图释放该叶点，该叶点可以被继续写入数据。

设计中的Tips：

因为Innodb表的数据是依赖于聚集索引顺序存放，同时聚集索引和数据一块存储，普通索引也需要存放一份聚集索引。所以对于聚集索引的设计尽量按顺序写入，必免数据分页，行迁移等对性能影响的现象。另外聚集索引要设计的尽可能短。从设计上必须锁的时间，大量随机IO的出现。
如对于监控（或是股票类的信息）可以利用时间和类型构成聚集索引，让相关性高的数据尽可能位到一块。以便读取时可以利用顺序IO读取到相应的数据。最好的情况，相关性高的数据在一个Page上，这样读取的效果更好。基于Innodb聚集索引的特性，在设计上也需要考虑利用一下优势，必免其不好的一方面从而达到最佳性能。

小心对待query_cache_size

发表于由

        作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究.

       对于使用MySQL的用户，对于这个变量大家一定不会陌生。前几年的MyISAM引擎优化中，这个参数也是一个重要的优化参数。但随着发展，这个参数也爆露出来一些问题。

       机器的内存越来越大，人们也都习惯性的把以前有用的参数分配的值越来越大。这个参数加大后也引发了一系列问题。我们首先分析一下query_cache_size的工作原理：

       一个SELECT查询在DB中工作后，DB会把该语句缓存下来，当同样的一个SQL再次来到DB里调用时，DB在该表没发生变化的情况下把结果从缓存中返回给Client。

   这里有一个关建点，就是DB在利用Query_cache工作时，要求该语句涉及的表在这段时间内没有发生变更。那如果该表在发生变更时，Query_cache里的数据又怎么处理呢？首先要把Query_cache和该表相关的语句全部置为失效，然后在写入更新。那么如果Query_cache非常大，该表的查询结构又比较多，查询语句失效也慢，一个更新或是Insert就会很慢，这样看到的就是Update或是Insert怎么这么慢了。

   所以在数据库写入量或是更新量也比较大的系统，该参数不适合分配过大。而且在高并发，写入量大的系统，建系把该功能禁掉。

 

Innodb 文件表空间结构

发表于由

    作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

      Innodb的表空间是在配置文件中定义（说是表空间有时觉的有点羞愧，和Oracle比真的差太远了），这里简单列一下表空间里的基本概念及表的分配情况。
       表空间是在配置文件中定义的几个文件简单的耦合起来，在使用中互不可少（少一个就面临DB完蛋的危险）。对于共享表空间无法确定表所在的表空间上。
      独立表空间可以做到每个表有自已的表空间（羞一下）。
       针对共享表空间，表空间中包括：回滚段,段（segment），区域（extent),数据页(page size)在表空间的体现为：
  表空间由默认16k的数据页面（page)组成，每64个连续的页面组成一个区域（extent,Oracle里熟悉的一个东东）。对于表空间的“文件(file)”在Innodb中被称为段(segment)。 回滚段(rollback segment）是一个特殊的例子，实际上rollback segment包含了多个段。对于Innodb表的索引都被分配成两个段：一个是为了 B-tree 的无叶结点(non-leaf nodes)，另一个是为了叶结点(leaf nodes)。
  这是为了达到包含数据的叶结点的更好的顺序(sequentiality for the leaf nodes)。
         当表空间中的一个段增长时，InnoDB 为它个别地分配最初的 32 个页面。之后 InnoDB 再分配段的整个区域(extents)。InnoDB 会以每次 4 个区域(extents)来增加一个大段以确保数据的良好顺序。
         表空间中的某些页面包含其它页面的位图(bitmaps)，所以在 InnoDB 表空间内的一些区域(extents)不能以一个整体分配给段，而只能作为个体页面。
          当发出一个查询 SHOW TABLE STATUS FROM … LIKE … 来询问表空间的剩余空间时，InnoDB 将报告表空间中所有空闲区域(extents)中确实可用的部分。InnoDB 通常会保留一些区域用于 clean-up 和其它的内部目的；这些保留的区域并不包含在剩余可用空间中。

         当从一个表中删除数据时，InnoDB 将收缩 B-tree 中相应的索引。这是依赖于释放个别的页面或区域(extents)以让其他用户使用剩余空间的删除模式。 移除(drop)一个表或删除所有记录可以保证释放空间给其他用户，但是删除记录行只有在事务回滚或 consistent read 后并不需要时才会被物理的移除

        对于独立表空间也是存一样的概念和行为，唯一区别就是每个表的数据存到指定的表空间中，rollback segment不和数据的segment在一个竞争。使用独立表空间的一个好处就是可以使数据分布相对于磁盘上更连续一点。

Innodb如何使用内存

发表于由

　作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。
     

来源：http://www.mysqlperformanceblog.com/2006/05/30/innodb-memory-usage/

译这个文章的目的：
　　最近经常被问起Innodb是如何使用内存的。该问题早已被原MySQL公司的Vadim论证过。我这里译一下他的文章供大家参考。
开始：
　　这里有许多关于Innodb如何使用内存的问题。我这里将会以innodb启动时的分配情况做一个解释。一些重要的概念：
　　NBLOCKS＝Innodb_buffer_pool有多个页(block)＝innodb_buffer_pool_size/16384(16k)
  　OS_THREADS= if ( innodb_buffer_pool_size >= 1000Mb) = 50000
 　　else if (innodb_buffer_pool_size >= 8Mb) = 10000
　　 else  = 1000 (该值只用在*nixes系统上，对于Windows有一点小的区别计算OS_THREADS)

所以Innodb 使用的内存包括：
　innodb_buffer_pool_size
    innodb_additional_mem_pool_size
    innodb_log_buffer_size
    adaptive index hash ,size (innodb buffer 索引管理区)= innodb_buffer_pool_size/64
    system dictionary hash,size(innodb内部字典区) = 6 * innodb_buffer_pool_size/512
    memory for sync_array,size(用于Innodb内部syncronzation的开销)=OS_THREAD * 512
    memory for os_event,size(用于innodb内存的syncronzation的开销)=OS_THREAD * 216
    memory for locking system(内存的锁管理系统),size = 5 * 4 *NBBLOCKS
 
 最终得到innodb内存使用的计算公式为：
     Innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + 812/16384 * innodb_buffer_pool_size + OS_THREADS * 368
 对于812/16384 * Innodb_buffer_pool_size 可以简单的用 innodb_buffer_pool_size / 20　计算，

对于OS_THREADS * 368　　
    OS_THREADS * 368 = 17.5 MB  if innodb_buffer_pool_size > 1000MB
   OS_THREADS * 368 = 3.5 MB  if innodb_buffer_pool_size > 8MB

举一个例子：
　　 如果你的innodb_buffer_pool_size有1500MB,innodb_additional_mem_pool_size =20 MB,innodb_log_buffer_size = 8M,
 　　Innodb 将会向系统申请内存为= 1500M + 20M + 8M + 1500/20 M +17.5 = 1620.5M

　　根据以上的条件可以算出Innodb最根本最需要多少内存，这样对于服务器的内存使用也可以有一个规划了。

数据库表结构设计方法及原则

发表于由

转：http://blog.csdn.net/c_sharp_Rookie/archive/2009/01/15/3786317.aspx

在目前的企业信息系统中，数据库还是最佳的数据存储方式，虽然已经有很多的书籍在指导我们进行数据库设计，但应该那种方式是设计数据库的表结构的最好方 法、设计时应遵从什么样的原则、四个范式如何能够用一种方式达到顺畅的应用等是我一直在思考和总结的问题，下文是我针对这几个问题根据自己的设计经历准备 总结的一篇文章的提纲，欢迎大家一块进行探讨，集思广益。其中提到了领域建模的概念，但未作详细解释，希望以后能够有时间我们针对这个命题进行深入探讨。

1) 不应该针对整个系统进行数据库设计，而应该根据系统架构中的组件划分，针对每个组件所处理的业务进行组件单元的数据库设计；不同组件间所对应的数据库表之 间的关联应尽可能减少，如果不同组件间的表需要外键关联也尽量不要创建外键关联，而只是记录关联表的一个主键，确保组件对应的表之间的独立性，为系统或表 结构的重构提供可能性。

2)采用领域模型驱动的方式和自顶向下的思路进行数据库设计，首先分析系统业务，根据职责定义对象。对象要符合封 装的特性，确保与职责相关的数据项被定义在一个对象之内，这些数据项能够完整描述该职责，不会出现职责描述缺失。并且一个对象有且只有一项职责，如果一个 对象要负责两个或两个以上的职责，应进行分拆。

3)根据建立的领域模型进行数据库表的映射，此时应参考数据库设计第二范式：一个表中的所 有非关键字属性都依赖于整个关键字。关键字可以是一个属性，也可以是多个属性的集合，不论那种方式，都应确保关键字能够保证唯一性。在确定关键字时，应保 证关键字不会参与业务且不会出现更新异常，这时，最优解决方案为采用一个自增数值型属性或一个随机字符串作为表的关键字。

4)由于第一点所述的领域模型驱动的方式设计数据库表结构，领域模型中的每一个对象只有一项职责，所以对象中的数据项不存在传递依赖，所以，这种思路的数据库表结构设计从一开始即满足第三范式：一个表应满足第二范式，且属性间不存在传递依赖。

5)同样，由于对象职责的单一性以及对象之间的关系反映的是业务逻辑之间的关系，所以在领域模型中的对象存在主对象和从对象之分，从对象是从1－N或N－N的角度进一步主对象的业务逻辑，所以从对象及对象关系映射为的表及表关联关系不存在删除和插入异常。

6) 在映射后得出的数据库表结构中，应再根据第四范式进行进一步修改，确保不存在多值依赖。这时，应根据反向工程的思路反馈给领域模型。如果表结构中存在多值 依赖，则证明领域模型中的对象具有至少两个以上的职责，应根据第一条进行设计修正。第四范式：一个表如果满足BCNF，不应存在多值依赖。

7) 在经过分析后确认所有的表都满足二、三、四范式的情况下，表和表之间的关联尽量采用弱关联以便于对表字段和表结构的调整和重构。并且，我认为数据库中的表 是用来持久化一个对象实例在特定时间及特定条件下的状态的，只是一个存储介质，所以，表和表之间也不应用强关联来表述业务（数据间的一致性），这一职责应 由系统的逻辑层来保证，这种方式也确保了系统对于不正确数据（脏数据）的兼容性。当然，从整个系统的角度来说我们还是要尽最大努力确保系统不会产生脏数 据，单从另一个角度来说，脏数据的产生在一定程度上也是不可避免的，我们也要保证系统对这种情况的容错性。这是一个折中的方案。

8)应针 对所有表的主键和外键建立索引，有针对性的（针对一些大数据量和常用检索方式）建立组合属性的索引，提高检索效率。虽然建立索引会消耗部分系统资源，但比 较起在检索时搜索整张表中的数据尤其时表中的数据量较大时所带来的性能影响，以及无索引时的排序操作所带来的性能影响，这种方式仍然是值得提倡的。

9) 尽量少采用存储过程，目前已经有很多技术可以替代存储过程的功能如“对象/关系映射”等，将数据一致性的保证放在数据库中，无论对于版本控制、开发和部 署、以及数据库的迁移都会带来很大的影响。但不可否认，存储过程具有性能上的优势，所以，当系统可使用的硬件不会得到提升而性能又是非常重要的质量属性 时，可经过平衡考虑选用存储过程。

10)当处理表间的关联约束所付出的代价（常常是使用性上的代价）超过了保证不会出现修改、删除、更改 异常所付出的代价，并且数据冗余也不是主要的问题时，表设计可以不符合四个范式。四个范式确保了不会出现异常，但也可能由此导致过于纯洁的设计，使得表结 构难于使用，所以在设计时需要进行综合判断，但首先确保符合四个范式，然后再进行精化修正是刚刚进入数据库设计领域时可以采用的最好办法。

11)设计出的表要具有较好的使用性，主要体现在查询时是否需要关联多张表且还需使用复杂的SQL技巧。

12)设计出的表要尽可能减少数据冗余，确保数据的准确性，有效的控制冗余有助于提高数据库的性能。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

以前也总写一些这方面的总结，无耐没办法提升到理论层次。今天看到这个感觉很不错，所以就转过来了。对一个大系统设计往往需要拆分成不同的功能去实现，同样对于内部数据也尽量减少关连直接操作。可以用基于主建的Hash类数据结构来获得相应的数据。设计表结构中太多东西需要注意了，以上原则部分也是一个基础部分，需要记住的。