关于数据库

InnoDb 逻辑存储结构图

从InnoDb 存储引擎的逻辑存储结构看，所有数据都被逻辑地存放在一个空间中，称之为表空间（tablespace）。表空间又由段（segment），区（extent），页（page）组成。页在一些文档中有时候也称为块（block）。 

表空间（tablespace）

• 表空间是Innodb存储引擎逻辑的最高层，所有的数据都存放在表空间中。
• 默认情况下，Innodb存储引擎有一个共享表空间ibdata1,即所有数据都存放在这个表空间中内。
• 如果启用了innodb_file_per_table参数，需要注意的是每张表的表空间内存放的只是数据、索引、和插入缓冲Bitmap，其他类的数据，比如回滚(undo)信息、插入缓冲检索页、系统事物信息，二次写缓冲等还是放在原来的共享表内的。

段（segment）

• 表空间由段组成，常见的段有数据段、索引段、回滚段等。
• InnoDB存储引擎表是索引组织的，因此数据即索引，索引即数据。数据段即为B+树的叶子结点，索引段即为B+树的非索引结点。
• 在InnoDB存储引擎中对段的管理都是由引擎自身所完成，DBA不能也没必要对其进行控制。

区（extent）

• 区是由连续页组成的空间，在任何情况下每个区的大小都为1MB。
• 为了保证区中页的连续性，InnoDB存储引擎一次从磁盘申请4~5个区。
• 默认情况下，InnoDB存储引擎页的大小为16KB，一个区中一共64个连续的区。

页（page）

• 页是InnoDB磁盘管理的最小单位。
• 在InnoDB存储引擎中，默认每个页的大小为16KB。
• 从InnoDB1.2.x版本开始，可以通过参数innodb_page_size将页的大小设置为4K，8K，16K。
• InnoDB存储引擎中，常见的页类型有：数据页，undo页，系统页，事务数据页，插入缓冲位图页，插入缓冲空闲列表页等。

 

•原子性： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；

•一致性： 执行事务前后，数据保持一致；

•隔离性： 并发访问数据库时，一个用户的事物不被其他事物所干扰，各并发事务之间数据库是独立的；

•持久性: 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。

 

并发事务带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对统一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

•脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

•丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。

•不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

•幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

通俗来说在一次事务里面，多次查询之后，结果集的个数不一致的情况叫做幻读。而多出来或者少的哪一行被叫做 幻行。

 

不可重复度和幻读区别：

不可重复读的重点是修改，幻读的重点在于新增或者删除。

例1（同样的条件, 你读取过的数据, 再次读取出来发现值不一样了 ）：事务1中的A先生读取自己的工资为 1000的操作还没完成，事务2中的B先生就修改了A的工资为2000，导 致A再读自己的工资时工资变为 2000；这就是不可重复读。

例2（同样的条件, 第1次和第2次读出来的记录数不一样 ）：假某工资单表中工资大于3000的有4人，事务1读取了所有工资大于3000的人，共查到4条记录，这时事务2 又插入了一条工资大于3000的记录，事务1再次读取时查到的记录就变为了5条，这样就导致了幻读

 

数据库如何实现 rollback 的？

数据库在写入数据之前是先讲对数据的改动写入 redo log 和 undo log，然后在操作数据，如果成功提交事务就会讲操作写入磁盘；如果失败就会根据redo log 和 undo log 逆向还原到事务操作之前的状态。

count(*)，count(1)和count（字段）的区别

很多人认为count(1)执行的效率会比count(*)高，原因是count(*)会存在全表扫描，而count(1)可以针对一个字段进行查询。其实不然，count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。而count(字段)则与前两者不同，它会统计该字段不为null的记录条数。

下面它们之间的一些对比：

1）在表没有主键时，count(1)比count(*)快；

2）有主键时，主键作为计算条件，count(主键)效率最高；

3）若表格只有一个字段，则count(*)效率较高。

 

char、varchar 、varchar2

1. char类型的长度是固定的，varchar的长度是可变的。

   这就表示，存储字符串'abc'，使用char(10)，表示存储的字符将占10个字节（包括7个空字符）

　　　　　　　　　　　　　　使用varchar2(10),，则表示只占3个字节，10是最大值，当存储的字符小于10时，按照实际的长度存储。

2.char类型的效率比varchar的效率稍高

3.varchar 与 varchar2的区别

varchar2是oracle开发的一个数据类型。

工业标准的varchar可以存储空字符串，oracle的varchar2还可以存储NULL值，如果想要有向后兼容的能力建议使用varchar2

4.varchar2比char节省空间，但是在效率上比char稍差些。既要获得效率即必须牺牲一点空间，这就是设计上的"以空间换时间"

varchar2虽然比char节省空间，但是一个varchar2列经常被修改，而且每次修改的数据长度不同，这会引起“行迁移的现象”，

而这造成的多余的I/O，是数据库设计中尽量避免的，在这种情况下使用char代替varchar2会更好些。

总结：1. 如果一个字段经常被修改，而且每次修改的数据长度不同，为了效率应当考虑用char定长代替varchar2变长。（列如一个用户的名字经常被修改）

　　    2. 设计的时候尽量考虑  用空间换时间。

 

MySQL数据库cpu飙升到500%的话他怎么处理？

当 cpu 飙升到 500%时，先用操作系统命令 top 命令观察是不是 mysqld 占用导致的，如果不是，找出占用高的进程，并进行相关处理。如果是 mysqld 造成的， show processlist，看看里面跑的 session 情况，是不是有消耗资源的 sql 在运行。找出消耗高的 sql， 看看执行计划是否准确， index 是否缺失，或者实在是数据量太大造成。一般来说，肯定要 kill 掉这些线程(同时观察 cpu 使用率是否下降)，等进行相应的调整(比如说加索引、改 sql、改内存参数)之后，再重新跑这些 SQL。也有可能是每个 sql 消耗资源并不多，但是突然之间， 有大量的 session 连进来导致 cpu 飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。