| MySQL

MySQL体系结构及存储引擎

MySQL体系结构

连接者：不同语言的代码程序和mysql的交互（SQL交互）
1、连接池 管理、缓冲用户的连接，线程处理等需要缓存的需求
2、管理服务和工具组件 系统管理和控制工具，例如备份恢复、Mysql复制、集群等
3、sql接口 接受用户的SQL命令，并且返回用户需要查询的结果
4、查询解析器 SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
5、查询优化器 SQL语句在查询之前会使用查询优化器对查询进行优化
select id,name from user where age = 40;
a、这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤
b、这个select查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤
c、将这两个查询条件联接起来生成最终查询结果
6、缓存 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
7、插入式存储引擎 存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

说明：
在Oracle 和SQL Server等数据库中，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。
用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。
甚至一个库中不同的表使用不同的存储引擎，这些都是允许的。

存储引擎

MyISAM存储引擎

由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。适合olap（在线分析处理），其中myd:放数据文件，myi:放索引文件

InnoDB存储引擎

mysql 5.5版本以后默认的存储引擎
由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。

面向oltp(online transaction processing)、行锁、支持外键、非锁定读、默认采用repeatable级别（可重复读）通过next-keylocking策略避免幻读、插入缓冲、二次写、自适应哈希索引、预读。

MEMORY存储引擎

MEMORY存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。 数据存放在内存中，表锁，并发性能差，默认使用哈希索引

BLACKHOLE存储引擎

黑洞存储引擎，写入的任何数据都会消失，可以应用于主备复制中的分发主库。

NDB存储引擎

集群存储引擎，share nothing，可提高可用性

Archive存储引擎

只支持insert和select zlib算法压缩1：10，适合存储归档数据如日志等、行锁

物理文件

物理文件包括：日志文件，数据文件，配置文件，pid文件,socket文件等

日志文件

img

1
2
3
4
error log  错误日志    排错  /var/log/mysqld.log【默认开启】
bin log    二进制日志  备份  增量备份 DDL DML DCL
Relay log  中继日志    复制  接收 replication master
slow log   慢查询日志  调优  查询时间超过指定值

Error Log

1
log-error=/var/log/mysqld.log

Binary Log

1
2
log-bin=/var/log/mysql-bin/bin.log
server-id=2 5.7.x版本后

Slow Query Log

1
2
3
slow_query_log=1|0
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3

配置文件

1
2
3
4
/etc/my.cnf—>/etc/mysql/my.cnf—>/usr/etc/my.cnf—> ~/.my.cnf
如果冲突，以最后一个读取的为准
[mysqld]组中包括了mysqld服务启动时的初始化参数
[client]组中包含着客户端工具程序可以读取的参数，此外还有其他针对各个客户端软件的特定参数组等

数据文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1、.frm文件
不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在此文件中，包括表结构的定义信息等。
2、.MYD文件
myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同样存放在所属数据库的目录下
3、.MYI文件
也是myisam存储引擎专用，存放myisam表的索引相关信息。对于myisam存储引擎来说，可以被缓存(cache)的内容主要就是来源于.MYI文件中。
每一个myisam表对应一个.MYI文件，其存放的位置和.frm及.MYD一样
4、.ibd文件和ibdata文件
存放innoDB的数据文件（包括索引）。innoDB存储引擎有两种表空间方式：独享表空间和共享表空间。
独享表空间：使用.ibd文件来存放数据，且每个表一个.ibd文件，文件存放在和myisam数据相同的位置。
共享表空间：使用.ibdata文件，所有表共同使用一个（或多个，自行配置）ibdata文件。

ibdata1:系统表空间（数据文件）undo 段
ib_logfile0   redlog文件  一组
ib_logfile1

5、db.opt文件    此文件在每一个自建的库里都会有，记录这个库的默认使用的字符集和校验规。

pid文件

1
pid文件是mysqld应用程序在Unix/Linux环境下的一个进程文件，和许多其他Unix/Linux服务端程序一样，它存放着自己的进程id

socket文件

1
socket文件也是在Unix/Linux环境下才有的，用户在Unix/Linux环境下客户端连接可以不通过TCP/IP网络而直接使用Unix Socket来连接mysql.

区分数据库、数据库实例、数据库服务器

数据库（database）

1
2
3
数据库（database）：
操作系统或存储上的数据文件的集合。mysql数据库中，数据库文件可以是* .frm、*.MYD、*.MYI、*.ibd等结尾的文件，
不同存储引擎文件类型不同。

数据库实例（instance）

1
2
3
4
5
6
由后台进程或者线程以及一个共享内存区组成。共享内存可以被运行的后台线程所共享。
需要注意：数据库实例才是真正操作数据库的。
    
数据库和数据库实例的关系：
通常情况下，数据库实例和数据库是一一对应的关系，也就是一个数据库实例对应一个数据库；
但是，在集群环境中存在多个数据库实例共同使用一个数据库。

数据库服务器(database server)

1
2
3
数据库服务器(database server)：部署安装数据库实例的服务器。
mysql中的逻辑对象:
mysqld_process+memory+datafile|logfile|pidfile...--->库－－＞表－－＞记录（由行与列组成）

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
1、官档
2、man 文档
3、命令行下的帮助文件      --oracle命令行没有，需要去查官方文档
mysql > help;
mysql > help create table;
mysql> ? contents
        --分了很多类
   Account Management
   Administration
   Data Definition
   Data Manipulation
   Data Types
   Functions
   Functions and Modifiers for Use with GROUP BY
   Geographic Features
   Language Structure
   Storage Engines
   Stored Routines
   Table Maintenance
   Transactions
   Triggers

mysql> ? Account Management    --一级一级的向内部查

mysql> ? CREATE USER
ESC