读书笔记：第一章 mysql体系结构和存储引擎

mysql被设计成一个可移植的数据库，几乎在所有系统上都能运行。尽管各平台在底层（如线程）实现方面都各有不同，但是mysql基本上能保证在个平台上的物理体系结构的一致性。

1.1 定义数据库和实例

   数据库： 物理操作系统文件及其他形式文件类型的集合。 文件的集合！

    实例    ： 由数据库后台进程 / 线程以及一个共享内存区组成， 数据库实例是用于操作这些数据库文件的。

两者相对应，一般情况下，一个数据库对应一个实例。但是两者概念完全不同。

         mysql被设计成一个单进程多线程架构的数据库。这点与SQL server比较类似， oracle是多进程架构（oracle的windows版本也是单进程多线程架构）。

mysql数据库实例在系统上的表现就是一个进程：

[root@localhost bin]# ps -ef | grep mysql

root     31481     1  0 23:20 pts/1    00:00:00

/bin/sh /usr/local/mysql/bin/mysqld_safe --port=3306 --socket=/tmp/mysql.sock --basedir=/usr/local/mysql --datadir=/data/mysql_3306 --socket=/tmp/mysql_3306.sock --slow_query_log_file=/data/mysql_3306/slow.log --log-error=/data/mysql_3306/error.log --log-bin=/data/mysql_3306/mysql-bin --sync_binlog=1 --binlog_format=row --transaction_isolation=REPEATABLE-READ --innodb_buffer_pool_size=100m --server-id=1993306

mysql    31734 31481 15 23:20 pts/1    00:00:05

/usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql_3306 --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --slow-query-log-file=/data/mysql_3306/slow.log --log-bin=/data/mysql_3306/mysql-bin --sync-binlog=1 --binlog-format=row --transaction-isolation=REPEATABLE-READ --innodb-buffer-pool-size=100m --server-id=1993306 --log-error=/data/mysql_3306/error.log --pid-file=/data/mysql_3306/localhost.localdomain.pid --socket=/tmp/mysql_3306.sock --port=3306

root     31770  2577  0 23:20 pts/1    00:00:00 grep mysql

  注意进程号为31734 的进程，该进程就是mysql实例 。 当启动实例时，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例。这与oracle中的参数文件（ spfile）相似。不同的是，如果没有参数文件，oracle启动实例时会提示启动失败。

  而MySQL可以没有配置文件，MySQL会按照编译时的默认参数设置启动实例。

下面命令会显示MySQL数据库实例在启动时，会在哪些位置查找配置文件：

[root@localhost bin]# mysql --help | grep my.cnf

                      order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/my.cnf    /etc/mysql/my.cnf    /usr/local/mysql/etc/my.cnf    ~/.my.cnf

Q :如果几个配置文件中都有同一个参数，以哪个为准？  A:  以读取到的最后一个配置文件中的参数为准。

datadir : 数据库所在路径 ，linux 默认在   /usr/local/mysql/data

mysql> show variables like '%datadir%'\G;

*************************** 1. row ***************************

Variable_name: datadir

        Value: /data/mysql_3306/

1 row in set (0.01 sec)

mysql> system ls -lh /data/mysql_3306 (数据库实例中查看系统文件)

-rw-r-----. 1 mysql mysql   56 May  5 18:37 auto.cnf

-rw-------. 1 root  root  1.7K May  5 18:37 ca-key.pem

-rw-------. 1 root  root  1.1K May  5 18:37 ca.pem

-rw-------. 1 root  root   944 May  5 18:37 ca-req.pem

-rw-------. 1 root  root  1.1K May  5 18:37 client-cert.pem

drwxr-x---. 2 mysql mysql 4.0K May  5 19:40 performance_schema

-rw-------. 1 root  root  1.1K May  5 18:37 server-cert.pem

-rw-------. 1 root  root  1.7K May  5 18:37 server-key.pem

-rw-------. 1 root  root   952 May  5 18:37 server-req.pem

drwxr-x---. 2 mysql mysql  12K May  5 18:37 sys

drwxr-x---. 2 mysql mysql 4.0K May  5 22:51 test

权限：保证只有mysql用户和组可以访问。  mysql：mysql

1.2  MySQL体系结构

  了解数据库的体系结构是最为重要内容。

再次强调; 数据库是文件的集合， 而实例是程序。

connectors： 指的是不同语言中与SQL的交互(不同的客户端如linux，windows连接到MySQL需要用到的驱动程序)

连接池组件： Connection pool  ， 管理缓冲用户连接，线程处理等需要缓存的需求。

管理服务和工具组件： management service & utillties 

sql接口组件： sql  interface， 接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface。

查询分析器组件： parser， SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。

    

a . 将SQL语句分解成 数据结构 ，并将这个结构传递到后续步骤，以后SQL语句的传递和处理就是基于这个结构的 。

b. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的。

 

（查询）优化器组件： optimizer，

SQL语句在查询之前会使用查询优化器对查询进行优化。他使用的是“选取-投影-联接”策略进行查询。

用一个例子就可以理解： select uid,name from user where gender = 1;这个select 查询先根据where语句进行选取，而不是先将表全部查询出来以后再进行gender过滤。 

这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤。 

将这两个查询条件联接起来生成最终查询结果。

（查询缓存）缓冲组件：cathes & buffers，

如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。

这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等。

插件式存储引擎 ：pluggable storage engines （有别于其他数据库的最重要特点）

存储引擎是MySql中具体的与文件打交道的子系统。也是Mysql最具有特色的一个地方。

Mysql的存储引擎是插件式的。它根据MySql AB公司提供的文件访问层的一个抽象接口来定制一种文件访问机制（这种访问机制就叫存储引擎）。

现在有很多种存储引擎，各个存储引擎的优势各不一样，最常用的MyISAM,InnoDB,BDB。

默认下MySql5.6是使用MyISAM引擎，它查询速度快，有较好的索引优化和数据压缩技术。但是它不支持事务。

InnoDB支持事务，并且提供行级的锁定，应用也相当广泛。

Mysql也支持自己定制存储引擎，甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

物理文件： file system file & log

物理文件包括：redolog、undolog、binlog（复制时还有relaylog）、errorlog、querylog、slowlog、data、index

注意：存储引擎是基于表的，而不是数据库的。

1.3    MySQL存储引擎

对于DBA来说，他们应该深刻认识到MySQL数据库的核心在于存储引擎。

由于MySQL数据库的开远兴，用户可以根据预定义的存储引擎接口编写自己的存储引擎。若对某一种存储引擎的性能不满意，可以直接修改源码来得到自己想要的特性。 MySQL官方手册16章 给出了编写自定义存储引擎的过程。

存储引擎有官方和第三方的区别 ，innodb就是第三方存储引擎，后被oracle收购。 他的应用极其广泛，甚至是MySQL数据库OLTP (在线事务处理)应用中使用最广泛的存储引擎。 

 应该根据具体应用 来选择合适的存储引擎。

1.3.1 innodb 存储引擎

   innodb支持事务，其设计目标主要是面向在线事务处理（OLTP）的应用。

特点：

         行锁设计、支持外键，并支持类似oracle的非锁定读，即默认读取操作不会产生锁。MySQL数据库5.5.8版本开     始，innodb是默认的存储引擎。

    innodb存储引擎将数据放在一个逻辑的表空间中。由innodb自身进行管理。innodb存储引擎的表单独放在一个

独立的ibd文件中。

    innodb通过多版本并发控制（MVCC）来获得高并发性， 有四种隔离级别：默认 repeatable级别。

   使用 next-key locking的策略来避免 幻读的产生。（所谓幻读就是比如：我1秒前查了工资为1000的员工有11条记录，在这过程中，表中又添加了一行记录，当再次查的时候，记录变为12条 。重点是增加和删除。不可重复读强调的是修改）

    innodb还提供插入缓冲（insert buffer）、二次写（double write）、自适应哈希索引（adaptive hash index）、

预读（read ahead）等高性能和高可用功能。

    对于表中数据的存储，innodb存储引擎采用了 聚集索引的方式，因此，每张表的存储都是按照 主键顺序存放的。

如果没有指定主键，innodb会为每一行生成一个6字节的rowid，并以此为主键。

1.3.2 myisam存储引擎

 

      myisam存储引擎不支持事务、表锁设计， 支持全文索引，主要面向一些 OLAP（联机分析处理）数据库应用。5.5.8之前默认存储引擎。数据库系统与文件系统很大的区别在于对事务的支持。然而myisam是不支持事务的。 比如，我再数据库仓库中，我只是简单的报表查询，完全没必要用到事务。

     myisam另外一个与众不同的地方在于 ：缓冲池只缓存（cache）索引文件，而不是数据文件。

     myisam存储引擎表由 MYD和 MYI组成。 MYD 用来存放数据文件（Data）的，MYI用来存放索引文件的（Index）。

      可以使用myisampack工具来进一步压缩数据。

      

      在 MySQL5.0版本以前，myisam默认支持的表的 大小为4G， 如果需要支持大于4G的表时，需要定制

max_rows和avg_row_length属性。5.0开始，myisam默认支持256TB的单表数据。

       

      注意：对于myisam存储引擎表，MySQL数据库只缓存其索引文件，数据文件的缓存交由操作系统完成。

在mysql5.1.23版本以前，无论是32位还是64位系统。缓存索引的缓冲区最大只支持4G，5.1.23之后，64位操作系统可以支持大于4G的索引缓冲区。

  1.3.3 NDB存储引擎 

        NDB是一个集群存储引擎。采用share nothing的 集群架构。

        特点：

                数据全部放在内存中（5.1版本开始，可以将非索引数据放在磁盘上）

                因此，主键查找（primary key lookups）速度极快，并且通过添加NDB数据存储节点（data node）

可以线性提升数据库性能，是高可用，高性能的集群系统。

        缺点：join（连接）操作是在mysql数据层完成的，而不是在存储引擎层完成的。因此，复杂的连接操作需要巨大的网络开销，查询速度很慢。

1.3.4 memory存储引擎

       memory存储引擎将表中的 数据存放在内存中，如果数据库重启或者崩溃，表中的数据将消失。他非常适合用于存储 临时数据的数据表，以及数据仓库中的纬度表。

        memory默认哈希索引。而不是b+tree

        

         速度虽然快，但是使用上有限制。

         只支持表锁，并发性能差

         不支持text，blob类型。

         存储变长字段（varchar）时是按照定长字段（char）的方式进行的。因此 浪费内存。

         

还有一点容易被忽视：

 

       mysql数据库使用memory存储引擎作为临时表来存放查询结果的中间结果集，如果中间结果大于memory的容量设置，或者中间结果含有text，blob类型字段。则mysql会将其转化为myisam存储引擎表而存放于磁盘中，由于myisam不缓存数据文件，因此，容易造成部分查询结果丢失。

1.3.5  Archive存储引擎（关键字：高速插入，压缩功能）

       Archive存储引擎只支持select和insert操作。5.1 开始支持索引。使用zlib算法将数据行row压缩后进行存储，压缩比 1:10. 所以， 非常适合存储归档数据，如日志信息。

     

      Archive使用 行锁来实现高并发的插入操作。但是其设计目标主要是提供 高速的插入和压缩功能

1.3.6 Federated存储引擎

      存储引擎表并不存放数据，它只是指向一台远程mysql数据库服务器上的表。并且只支持MySQL数据库表，并不支持异构数据库表。

1.3.7 Maria存储引擎 

     新开发的存储引擎，设计目标是用来取代myisam存储引擎。可以看做是myisam的后续版本。

    支持缓存数据和索引文件，应用了行锁设计。提供了MVCC功能，支持事务和非事务安全的选项，以及更好的blob字符类型的处理性能。

回答问题：

1.为什么MySQL数据库不支持全文索引？

    

   错。mysql数据库中innodb存储引擎（1.2版本）和myisam存储引擎都支持全文索引。

2.mysql数据库速度快是因为不支持事务？

  

    错。mysql数据库中，每个存储引擎都有其不同的应用场景。myisam存储引擎不支持事务，但是innodb存储引擎支持事务。对于ETL（ ETL ，是英文 Extract-Transform-Load 的缩写，用来描述将数据从来源端经过抽取（extract）、转换（transform）、加载（load）至目的端的过程。 ETL 一词较常用在 数据仓库 ，但其对象并不限于数据仓库。）这种操作，myisam有优势。但是在OLTP（在线事务处理）环境中，innodb存储引擎的效率更高。

3.当表的数据量超过1000万时MySQL的性能会急剧下降吗？

  

    不会。MySQL是数据库，不是文件。随着数据行数的增加，性能当然会有所下降，但不是线性的。只要用户选择了正确的存储引擎和正确的配置，再多的数据，mysql也能承受。

1.4 个存储引擎之间的比较

  

可以通过show engines\G; 查看当前数据库支持的存储引擎 。 也可以通过查找information_schema 架构下的

engines表。

   

下面将通过MySQL提供的示例数据库来简单显示各个存储引擎之间的区别：。分别运行以下语句，然后统计每次使用各个存储引擎后表的大小。

将mytest存储引擎设置为myisam。

mysql> create table mytest engine=myisam as select * from salaries;

表的大小为40.7MB。

mysql> alter table mytest engine=innodb;

Query OK, 2844047 rows affected (18.93 sec)

Records: 2844047  Duplicates: 0  Warnings: 0

存储引擎更改为 innodb后  表增大到123MB。

mysql> alter table mytest engine=archive;

Query OK, 2844047 rows affected (7.28 sec)

Records: 2844047  Duplicates: 0  Warnings: 0

更改为archive 后，表只有20.2MB。

1.5 连接MySQL

     介绍连接MySQL数据库常用的方式。连接mysql操作是   一个连接进程 和 MySQL数据库实例 进行通信。

 本质上是，进程通信。

常用的进程通信方式： 管道、 命名管道、 命名字、TCP/IP套接字、UNIX域套接字。

1.5.1 TCP/IP 

  tcp/ip 套接字方式是mysql数据库在任何平台下都提供的连接方式，也是网络中使用的最多的连接方式。

这种方式在TCP/IP连接的基础上建立一个基于网络的连接请求。客户端（client）在一台服务器上，而

MySQL数据库实例在另一台服务器上，两台机器通过一个TCP/IP网络连接。

例如，用户可以在windows上请求连接一个在linux下的数据库实例：

mysql -uroot -p12345 -h192.168.0.101

这里windows就是 客户端， 向一台host ip地址为 192.168.0.101的 MySQL实例发起tcp/ip 连接请求。之后就可以在实例中对mysql数据库进行如 DDL DML等操作。

 注意：这里客户端在通过TCP/IP连接到mysql实例时，mysql数据库会检查一张权限视图（mysql下的user表）

mysql> select host,user from mysql.user

    -> ;

+------------+-----------+

| host       | user      |

+------------+-----------+

| %          | leo       |

| 192.168.1% | leo       |

| 192.168.1* | leo       |

| localhost  | mysql.sys |

| localhost  | root      |

+------------+-----------+

5 rows in set (0.01 sec

1.5.2 命名管道和共享内存

   如果两个需要通信的进程在同一台服务器上，就要使用命名管道。

   在MySQL数据库中需要在配置文件中  启用 --enable-named-pipe选项。

  在4.1之后的版本中，还提供了共享内存的连接方式， 通过在配置文件中添加 --share-memory 实现的。

  如果想使用共享内存的方式，在连接时，MySQL客户端还必须使用 --protocol=memory 选项。

1.5.3 UNIX 域套接字

   在linux和UNIX环境下，还可以使用UNIX域套接字。UNIX域套接字其实不是一个网络协议，所以只能在 MySQL客户端和数据库实例在一台服务器上的情况下使用。 用户可以在配置文件中添加套接字的路径

--socket=/tmp/mysql.sock

 

启动时，可以通过下列命令来找到套接字文件：

mysql> show variables like 'socket';

+---------------+----------------------+

| Variable_name | Value                |

+---------------+----------------------+

| socket        | /tmp/mysql_3306.sock |

+---------------+----------------------+

1 row in set (0.01 sec)