MySQL Study案例之--MySQL体系和存储引擎

rtr21

1、数据库和实例

    数据库：物理操作系统文件或其他形式文件类型的集合。在MySQL中，数据库文件可以是frm、myd、myi、ibd结尾的文件。当使用NDB引擎时，数据库文件可能不是操作系统上的文件，而是存放与内存之中的文件，但是定义仍然不变。
     数据库实例：由数据库后台进程/线程以及一个共享内存区组成。共享内存可以被运行的后台进程/线程所共享。需要牢记的是，数据库实例才是真正用来操作数据库文件的。
    在MySQL中，实例和数据库通常关系是一一对应，即一个实例对应一个数据库，一个数据库对应一个实例。但是，在集群情况下可能存在一个数据库可被多个实例访问的情况。
    MySQL被设计为一个单进程多线程架构的数据库。

2、MySQL进程

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

[iyunv@rh6 ~]#mysqld_safe & [iyunv@rh6 ~]# ps -ef|grep mysql |grep -v grep root      4168  4130  0 14:48 pts/1    00:00:00 /bin/sh ./mysqld_safe mysql     4294  4168  0 14:48 pts/1    00:00:14 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/var/lib/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/lib/mysql/mysql.sock root      4643  4130  0 15:26 pts/1    00:00:00 mysql -h localhost -u root -p MySQL配置文件： [iyunv@rh6 ~]# mysql --help |grep my.cnf                       order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf 数据文件路径： mysql> show variables like 'datadir' \G *************************** 1. row *************************** Variable_name: datadir         Value: /var/lib/mysql/ 1 row in set (0.00 sec) [iyunv@rh6 bin]# ls -l /var/lib/mysql/ total 28688 -rw-rw---- 1 mysql mysql       56 Jan 28 17:25 auto.cnf -rw-rw---- 1 mysql mysql 18874368 Feb  2 14:48 ibdata1 -rw-rw---- 1 mysql mysql  5242880 Feb  2 14:48 ib_logfile0 -rw-rw---- 1 mysql mysql  5242880 Jan 28 17:21 ib_logfile1 drwx------ 2 mysql root      4096 Jan 28 17:21 mysql srwxrwxrwx 1 mysql mysql        0 Feb  2 14:48 mysql.sock drwx------ 2 mysql mysql     4096 Jan 28 17:21 performance_schema drwx------ 2 mysql root      4096 Jan 28 17:21 test

3、MySQL的结构
在具体介绍MySQL的存储引擎之前，先来介绍一下MySQL的结构。


从图中可以看到MySQL由以下几个部分组成：

• 连接池
  
• 管理服务和工具组件
  
• SQL接口
  
• 查询分析器
  
• 优化器
  
• 缓存
  
• 插入式存储引擎
  
• 物理文件
  
  

4、InnoDB存储引擎：

InnoDB是Mysql数据库的一种存储引擎：

   InnoDB给Mysql的表提供了 事务、回滚、崩溃修复能力、多版本并发控制的事务安全、间隙锁（可以有效的防止幻读的出现）、支持辅助索引、聚簇索引、自适应hash索引、支持热备、行级锁。还有InnoDB是Mysql上唯一一个提供了外键约束的引擎。

   InnoDB存储引擎中，创建的表的表结构是单独存储的并且存储在.frm文件中。数据和索引存储在一起的并且存储在表空间中。但是默认情况下mysql会将数据库的所有InnoDB表存储在一个表空间中的。其实这种方式管理起来非常的不方便而且还不支持高级功能所以建议每个表存储为一个表空间实现方式为：使用服务器变量innodb_file_per_table = 1。

    如果需要频繁的进行更新、删除操作的数据库也可选择InnoDB存储引擎。因为该存储引擎可以实现事务提交和回滚。

    InnoDB存储引擎内存由以下几个部分组成：缓冲池(buffer pool)、重做日志缓冲池(redo log buffer)以及额外的内存池(additional memory pool)，分别由配置文件中的参数innodb_buffer_pool_size和innodb_log_buffer_size的大小决定。

InnoDB体系结构：
    后台线程的主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外，将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常情况下InnoDB能恢复到正常运行状态。
后台线程
    由于Oracle是多进程的架构（Windows下除外），因此可以通过一些很简单的命令来得知Oracle当前运行的后台进程，如ipcs命令。一般来说，Oracle的核心后台进程有CKPT、DBWn、LGWR、ARCn、PMON、SMON等。
    很多DBA问我，InnoDB存储引擎是否也是这样的架构，只不过是多线程版本的实现后，我决定去看InnoDB的源代码，发现InnoDB并不是这样对数据库进程进行操作的。InnoDB存储引擎是在一个被称做master thread的线程上几乎实现了所有的功能。
默认情况下，InnoDB存储引擎的后台线程有7个—4个IO thread，1个master thread，1个锁（lock）监控线程，1个错误监控线程。IO thread的数量由配置文件中的innodb_file_ io_threads参数控制，默认为4，如下所示。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102

mysql> show engine innodb status \G *************************** 1. row ***************************   Type: InnoDB   Name: Status: ===================================== 150202 17:15:33 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 19 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 0 srv_active, 0 srv_shutdown, 8787 srv_idle srv_master_thread log flush and writes: 8787 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 3 OS WAIT ARRAY INFO: signal count 3 Mutex spin waits 2, rounds 60, OS waits 1 RW-shared spins 2, rounds 60, OS waits 2 RW-excl spins 0, rounds 0, OS waits 0 Spin rounds per wait: 30.00 mutex, 30.00 RW-shared, 0.00 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 2305 Purge done for trx's n:o < 0 undo n:o < 0 History list length 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 3, OS thread handle 0x7fe2a80c2700, query id 45 localhost root init show engine innodb status -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 161 OS file reads, 5 OS file writes, 5 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 276707, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1602871 Log flushed up to   1602871 Pages flushed up to 1602871 Last checkpoint at  1602871 0 pending log writes, 0 pending chkp writes 8 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 137363456; in additional pool allocated 0 Dictionary memory allocated 43148 Buffer pool size   8192 Free buffers       8042 Database pages     150 Old database pages 0 Modified db pages  0 Pending reads 0 Pending writes: LRU 0, flush list 0 single page 0 Pages made young 0, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 150, created 0, written 1 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 150, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Main thread process no. 4294, id 140611110070016, state: sleeping Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set (0.00 sec)

附：

Oracle 体系结构图