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

桃扇骨 2022-08-07 10:41 216阅读 0赞

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

**1、数据库和实例**

**   ** **数据库**：物理操作系统文件或其他形式文件类型的集合。在MySQL中，数据库文件可以是frm、myd、myi、ibd结尾的文件。当使用NDB引擎时，数据库文件可能不是操作系统上的文件，而是存放与内存之中的文件，但是定义仍然不变。

**     数据库实例**：由数据库后台进程/线程以及一个共享内存区组成。共享内存可以被运行的后台进程/线程所共享。需要牢记的是，数据库实例才是真正用来操作数据库文件的。

在MySQL中，实例和数据库通常关系是一一对应，即一个实例对应一个数据库，一个数据库对应一个实例。但是，在集群情况下可能存在一个数据库可被多个实例访问的情况。

MySQL被设计为一个单进程多线程架构的数据库。

**2、MySQL进程**

[root@rh6 ~]#mysqld_safe &
    
    [root@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配置文件：
    [root@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)
    
    [root@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的结构。

![2012070322224517.png][]

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

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

**4、InnoDB存储引擎：**

**[![wKiom1TPPmGCP5nqAAOffBwE-Vc660.jpg][]][wKiom1TPPmGCP5nqAAOffBwE-Vc660.jpg] ****InnoDB是Mysql数据库的一种存储引擎：**

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

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

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

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

[![wKiom1TPPs6hU3AOAAGnL8\_pdKA343.jpg][wKiom1TPPs6hU3AOAAGnL8_pdKA343.jpg]][wKiom1TPPs6hU3AOAAGnL8_pdKA343.jpg]

** 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，如下所示。

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 体系结构图**

[![wKioL1TPRc2gI0bnAAKfK4JRodk509.jpg][]][wKioL1TPRc2gI0bnAAKfK4JRodk509.jpg]

本文出自 “[天涯客的blog][blog]” 博客，请务必保留此出处[http://tiany.blog.51cto.com/513694/1610840][http_tiany.blog.51cto.com_513694_1610840]

[2012070322224517.png]: /images/20220805/2f03ada7751242ab81c4b2330e1addcc.png
[wKiom1TPPmGCP5nqAAOffBwE-Vc660.jpg]: /images/20220805/e4a4345acba144fabf4083ee635ab077.png
[Link 1]: http://product.pcpop.com/Memory/10734_1.html
[wKiom1TPPs6hU3AOAAGnL8_pdKA343.jpg]: /images/20220805/097f186ccf22420bb70ddeae1b141971.png
[wKioL1TPRc2gI0bnAAKfK4JRodk509.jpg]: /images/20220805/c6c23404ab394c2992f43846fd655b15.png
[blog]: http://tiany.blog.51cto.com
[http_tiany.blog.51cto.com_513694_1610840]: http://tiany.blog.51cto.com/513694/1610840