MySQL体系结构及使用

2017年9月9日 0条评论 2,981次阅读 0人点赞

MySQL是被设计为一个可移植的数据库，几乎在当前所有的系统上都能运行。为了了解其内部的一些原理、机制。很久前阅读了 < MySQL技术内幕 InnoDB存储引擎 > 这本书，但一直懒与整理。奈何拖得罪恶感十足，故有此笔记。

0x00.数据库与实例

在数据库领域中有俩个词很容易混淆，数据库和实例，当然，也可以类比操作系统的程序和进程的区别。

数据库：物理操作系统文件或者其他形式文件的集合。在MySQL数据库中，数据库文件可以是frm，MYD，MYI，ibd结尾的文件。
实例：MySQL数据库由后台进程以及一个共享内存区组成，共享内存可以被后台运行线程所共享。

在MySQL数据库中，实例与数据库的关系通常是一一对应的，但是，在集群情况下可能一个数据库被多个数据库实例使用的情况。同时，MySQL被设计为了一个单进程多进程架构的数据库，也就是说，MySQL数据库实例在系统上的表现是一个进程。

当实例启动的时候，MySQL数据库会去读取配置文件，根据配置文件的参数来启动数据库实例。在Linux上可以通过mysql --help | grep my.cnf查看，windows上可以通过mysql --help查看。通过查询，我们可以看出配置文件不止一个。并且在配置时，是按顺序依次读取配置文件。如下。

C:\Users\Torival>mysql --help
...
Default options are read from the following files in the given order:
C:\Windows\my.ini C:\Windows\my.cnf C:\my.ini C:\my.cnf 
C:\Program Files\MySQL\MySQL Server 5.7\my.ini 
C:\Program Files\MySQL\MySQL Server 5.7\my.cnf
...

MySQL体系结构.png

从图中，我们可以发现，MySQL由这八部分组成：连接池组件、管理服务和工具组件、sql接口组件、查询分析器组件、优化器组件、缓存组件、插件式存储引擎、物理文件。MYSQL数据库区别与其他数据库的最重要的一个特点就是其插件是的表存储引擎。当然我们不可能一下都了解，只需先整体有个印象就好。

0x10 各个存储引擎介绍

0x11.InnoDB 存储引擎

InnoDB 存储引擎支持事务，其设计目标主要是面向在线事务处理(OLTP, On-Line Transaction Processing)的应用。其特点是行锁设计，支持外键，并支持非锁定读，默认读取操作不会产生锁。从MYSQL5.5.8版本之后，InnoDB存储引擎是默认的存储引擎。

OLTP多用在日常的事务处理应用中，如银行交易，在线商品交易，Blog，网络游戏等应用。对CPU的要求并不是很高，所以OLTP是IO密集型的操作。这种应用的特点如下：

用户操作的并发量大
事务处理的时间一般比较短
查询的语句较为简单，一般走索引
复杂查询语句较少

0x12.MyISAM 存储引擎

MyISAM 存储引擎不支持事务，表锁设计，支持全文索引。主要面对一些在线分析处理(OLAP, On-Line Analytical Processing)的应用。在MySQL5.5.8版本之前，MyISAM 存储引擎是默认的存储引擎。

MyISAM存储引擎表由MYD和MYI组成，MYD用来存放数据文件，MYI用来存放索引文件。可以通过使用myisampack工具来进一步压缩数据文件，而myisampack工具是使用Huffman编码静态算法来压缩数据。

OLAP多用在数据仓库或者数据集市中，一般需要执行复杂的SQL语句来进行查询。复杂的查询可能需要执行比较，排序，连接等非常熬CPU的操作。而这些操作在OLTP的数据库应用中较少发生。所以OLAP是CPU密集型的操作。这种应用的特点如下：

事务处理的时间一般比较长
复杂查询语句较为复杂

0x20 可能常用命令

可以通过 mysql -host -Port -user -password 连接数据库，如果数据库在本地的，端口是默认的话。也可以不填这俩个字段。

C:\> mysql -uroot -p123456;

但是在连接到MySQL实例时，MySQL数据库会先检查一张权限视图(mysql.user)，用来判断发起请求的客户端IP是否允许连接到MySQL实例。

mysql> SELECT host, user, authentication_string FROM mysql.user;
+-----------+-----------+-------------------------------------------+
| host      | user      | authentication_string                     |
+-----------+-----------+-------------------------------------------+
| localhost | root      | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+-----------+-------------------------------------------+

需要注意的是，从MySQL5.7之前，authentication_string字段是password。

查看当前使用的MySQL数据所支持的存储引擎

mysql> show ENGINES\G;
*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappe
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

查询当前MySQL的编码状态。不过因为 latin1 是MySQL的默认编码，所以有时候出现中文乱码的情况。那就修改上面提到的配置文件吧。

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | utf8                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | utf8                                                    |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+

0x30 MySQL安装使用

Linux下安装

由于工作平台是CentOS7，所以只给出CentOS7的安装方法。至于其他平台，如日后用到，会加到文章里面。

首先，在官网下载安装mysql-server

➜  ~ wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
➜  ~ rpm -ivh mysql-community-release-el7-5.noarch.rpm
➜  ~ sudo yum install mysql-community-server

安装成功后，重启mysql服务

➜  ~ service mysqld restart

初次安装mysql，root账户没有密码，所以需要修改一下密码

➜  ~ mysql -uroot 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 20
Server version: 5.7.19 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update mysql.user set authentication_string=password('123456') 
    -> where user='root' and Host = 'localhost';
Query OK, 0 rows affected, 1 warning (0.13 sec)
Rows matched: 1  Changed: 0  Warnings: 1

# 5.7之前修改密码
# set password for root@localhost = password('123456');
mysql>

安装完数据库，免不了要通过某种语言对接MySQL。如果你使用的是C++，并且是用的原生库函数，那么你刚好可以按着下面的方法操作。这一条是安装所需的库和包含文件。

# sudo yum install mysql-devel

在安装完库和头文件。在编译，链接的时候，也得加入不同的指令，用来指定头文件位置，库文件位置。

# 连接MySQL客户端函数库所需要的头文件的存放位置，编译时加入
g++ -c testMySQL.cpp `mysql_config --cflags`

# 连接MySQL客户端函数库所需要的库文件的存放位置，链接是加入
g++ -o testMySQL testMySQL.o `mysql_config --libs`

当然你也可以合作一条来使用。

g++ -o testMySQL testMySQL.cpp `mysql_config --cflags --libs`

测试程序：

// testMySQL.cpp
#include <stdio.h>  
#include <mysql/mysql.h>  
int main()  {  
    MYSQL mysql;  

   //初始化MYSQL对象
   mysql_init(&mysql);  
   
   //连接数据库,第一个参数是MYSQL指针,localhost表示连接本地计算机
    if(!mysql_real_connect(&mysql,"localhost","root","123456","bhtcs",0,NULL,0))
        fprintf(stderr,"connection error! %s\n", mysql_error(&mysql));  
    else  
        printf("connection succeed!\n");  

    //关闭数据库连接  
    mysql_close(&mysql);
    return 0;  
}

架构修炼

Osheep

MySQL体系结构及使用

0x00.数据库与实例

0x10 各个存储引擎介绍

0x11.InnoDB 存储引擎

0x12.MyISAM 存储引擎

0x20 可能常用命令

0x30 MySQL安装使用

Linux下安装

本作品采用知识共享署名-相同方式共享 4.0 国际许可协议进行许可

Osheep

0x00.数据库与实例

0x10 各个存储引擎介绍

0x11.InnoDB 存储引擎

0x12.MyISAM 存储引擎

0x20 可能常用命令

0x30 MySQL安装使用

Linux下安装

本作品采用 知识共享署名-相同方式共享 4.0 国际许可协议 进行许可

本作品采用知识共享署名-相同方式共享 4.0 国际许可协议进行许可