数据库概述

数据库基本体系

数据(Data)

在计算机中描述一个事物，就需要抽取这一事物的典型特征，组成一条记录，就相当于文件里的一行内容。

数据表(Table)

分散的一条条记录需要一个高效的方式把它们组织起来，如果我们把这些记录放到同一个文件中，每行记录按逗号作为分隔，依次定义各个字段的意思，相当于定义表的标题，这样就很容易管理所有的记录了。

数据库(DataBase，简称DB)

数据库即存放数据的仓库，我们把相关的数据表放到同一个目录下，这个目录就是数据库。

数据库管理系统(DataBase Management System，简称DBMS)

有了数据、数据表、数据库的体系之后，如何科学地组织和存储数据，如何高效获取和维护数据成了关键。

这就用到了一个系统软件——数据库管理系统，如MySQL、Oracle、SQLite、Access、SQL Server

mysql主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码，因为开放源代码这个数据库是免费的，他现在是甲骨文公司的产品。
oracle主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
sql server是微软公司的产品，主要应用于中大型企业，如联想、方正等。

关系型数据库和非关系型数据库

关系型数据库

采用了关系模型来组织数据的数据库。关系模型指的就是二维表格模型，而一个关系型数据库就是由二维表及其之间的联系所组成的一个数据组织。

优点：

1. 容易理解：二维表结构是非常贴近逻辑世界的一个概念，关系模型相对网状、层次等其他模型来说更容易理解
2. 使用方便：通用的 SQL 语言使得操作关系型数据库非常方便
3. 易于维护：丰富的完整性 (实体完整性、参照完整性和用户定义的完整性) 大大减低了数据冗余和数据不一致的概率

缺点：

1. 网站的用户并发性非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘I/O是一个很大的瓶颈
2. 网站每天产生的数据量是巨大的，对于关系型数据库来说，在一张包含海量数据的表中查询，效率是非常低的
3. 在基于 web 的结构当中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库却没有办法像web server和app server那样简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。当需要对数据库系统进行升级和扩展时，往往需要停机维护和数据迁移。
4. 性能欠佳：在关系型数据库中，导致性能欠佳的最主要原因是多表的关联查询，以及复杂的数据分析类型的复杂 SQL 报表查询。为了保证数据库的 ACID 特性，必须尽量按照其要求的范式进行设计，关系型数据库中的表都是存储一个格式化的数据结构。

常见的关系型数据库：

1
2
3
4
5
6
7
8

MySQL:	开源免费的数据库，小型的数据库。已经被Oracle收购了，MySQL6.x版本也开始收费。
Oracle:	收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
DB2:	IBM公司的数据库产品,收费的。常应用在银行系统中.
SQLServer:	MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用。
SQLite:	嵌入式的小型数据库，应用在手机端。

Java常用的数据库:MySQL，Oracle。
Python常用的数据库:MySQL

非关系型数据库

非关系型数据库以键值对存储，且结构不固定，每一个元组可以有不一样的字段，每个元组可以根据需要增加一些自己的键值对，不局限于固定的结构，可以减少一些时间和空间的开销。

优点

1. 用户可以根据需要去添加自己需要的字段，为了获取用户的不同信息，不像关系型数据库中，要对多表进行关联查询。仅需要根据id取出相应的value就可以完成查询。

2. 适用于SNS(Social Networking Services)中，例如 facebook，微博。系统的升级，功能的增加，往往意味着数据结构巨大变动，这一点关系型数据库难以应付，需要新的结构化数据存储。由于不可能用一种数据结构化存储应付所有的新的需求，因此，非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合。

不足

1. 只适合存储一些较为简单的数据，对于需要进行较复杂查询的数据，关系型数据库显得更为合适。
2. 不适合持久存储海量数据

MySQL体系架构

MySQL Server

从上图看MySQL服务端总体上可以分为三层：连接层、SQL层和存储引擎层

连接层

当客户端连接MySQL服务器时，最先连接处理的是连接层，连接层包括通信协议、线程处理、用户名密码认证三个部分。

通信协议负责检测客户端版本是否兼容MySQL服务端。

线程处理是指每一个连接请求都会分配一个对应的线程，相当于一条SQL对应一个线程，存储客户端与数据库的连接信息。

用户名密码认证验证创建的账号和密码，以及host主机授权是否可以连接到MySQL服务器。

SQL层

SQL层包含SQL接口、解析器、查询优化器和缓存

SQL接口负责接收客户端发送过来的各种SQL命令，并将SQL命令发送到其他部分，接收其他部分返回的结果数据返回给客户端。

解析器针对 SQL 语句进行解析，判断语法是否正确。

查询优化器对 SQL 进行改写和相应的优化，并生成最优的执行计划。

缓存存储在Query Cache中，包括MySQL的表缓存，记录缓存，MySQL中的权限缓存，引擎缓存等。MySQL中的缓存能够提高数据的查询性能，如果查询的结果能够命中缓存，则MySQL会直接返回缓存中的结果信息。

存储引擎层

MySQL中的存储引擎层主要负责数据的写入和读取，与底层的文件进行交互，相当于MySQL内置的文件系统。

MySQL的5.5以及之前版本默认采用的是MyIsam引擎，在5.6版本及后续默认采用InnoDB引擎。InnoDB相较于MyIsam来说，增加了对几个重要特性的支持，包括MVCC（多版本并发控制）、事务、热备、行级锁、外键等。

系统文件

系统文件主要包括MySQL中存储数据的底层文件，与上层的存储引擎进行交互，是文件的物理存储层。其存储的文件主要有：日志文件、数据文件、配置文件、MySQL的进行pid文件和socket文件等。

日志文件

MySQL中的日志主要包括：错误日志、通用查询日志、二进制日志、慢查询日志等。

• 错误日志

  主要存储的是MySQL运行过程中产生的错误信息。执行 show variables like '%log_error%'; 可获取错误日志存储位置。

• 通用查询日志

  主要记录MySQL运行过程中的一般查询信息，执行 show variables like '%general%'; 可获取通用查询日志存储位置。

• 二进制日志

  主要记录对MySQL数据库执行的插入、修改和删除操作，并且也会记录SQL语句执行的时间、执行的时长，但是二进制日志不记录select、show等不修改数据库的SQL。主要用于恢复数据库的数据和实现MySQL主从复制。

  通过命令 show variables like '%log_bin%'; 查看二进制日志是否开启;

  通过命令 show variables like '%binlog%'; 查看二进制日志的参数;

  通过命令 show binary logs;查看日志文件;

• 慢查询日志

  慢查询主要记录的是执行时间超过指定时间的SQL语句，这个时间默认是10秒。

  通过命令 show variables like '%slow_query%'; 查看是否开启慢查询日志;

  通过命令 show variables like '%long_query_time%';查看慢查询设置的时长

数据文件

数据文件中主要包括了：db.opt文件、frm文件、MYD文件、MYI文件、ibd文件、ibdata文件、ibdata1文件、ib_logfile0和ib_logfile1文件等。

• frm文件

  存储数据表的结构信息，主要是数据表相关的元数据信息，包括数据表的表结构定义信息，每张表都会有一个frm文件。

  值得注意的是：MySQL 8.x版本中的innodb存储引擎的表没有frm文件。

• ibd文件

  存放Innodb存储引擎的数据文件和索引文件，主要存放的是独享表空间的数据和索引，每张表对应一个.ibd文件。

• ibdata文件

  存放Innodb存储引擎的数据文件和索引文件，主要存放的是共享表空间的数据和索引，所有表共用一个（或者多个）.ibdata文件，可以根据配置来指定共用的.ibdata文件个数。

• ibdata1文件

  MySQL的系统表空间数据文件，主要存储MySQL的数据表元数据、Undo日志等信息。

• ib_logfile0和ib_logfile1文件

  MySQL数据库中的Redo log文件，主要用于MySQL实现事务的持久性。如果在某个时间点MySQL发生了故障，此时如果有脏页没有写入到数据库的ibd文件中，在重启MySQL的时候，MySQL会根据Redo Log信息进行重做，将写入Redo Log并且尚未写入数据表的数据进行持久化操作。

• db.opt文件

  主要记录当前数据库使用的字符集和检验规则等信息。

• MYD文件

  MyISAM存储引擎专用的文件格式，主要存放MyISAM存储引擎数据表中的数据，每张MyISAM存储引擎表对应一个.MYD文件。

• MYI文件

  MyISAM存储引擎专用的文件格式，主要存放MyISAM存储引擎数据表的索引信息，每张MyISAM存储引擎表对应一个.MYI文件。

配置文件

用于存在MySQL所有的配置信息，在Unix/Linux环境中是my.cnf文件，在Windows环境中是my.ini文件。

pid文件

pid文件是存放MySQL进程运行时的进程号的文件，主要存在于Unix/Linux环境中，具体的存储目录可以在my.cnf文件中配置。

socket文件

socket文件和pid文件一样，都是MySQL在Unix/Linux环境中运行才会有的文件。客户端可以直接通过socket来连接MySQL。

客户端

客户端连接器提供与MySQL服务器建立连接的能力，几乎支持所有主流的服务端语言，例如：Java、C、C++、Python等，各语言都是通过各自的API接口与MySQL建立连接。

MySQL部署

全新部署和配置文件

MySQL的安装方式有很多，编译安装、二进制安装、或者yum安装。

安装之前要注意删除掉其他的MySQL分支、之前版本留下的依赖环境和配置文件等

1
2
3
4
5
6
7
8
9

|root@aliyun ~|$ rpm -qa | grep mariadb
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-devel-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64
mariadb-server-5.5.68-1.el7.x86_64
|root@aliyun ~|$ rpm -e mariadb-server-5.5.68-1.el7.x86_64		# 逐个删除,如果提示有依赖关系，可以使用`--nodeps`参数删除
|root@aliyun ~|$ rpm -qa | grep mariadb		# 再次确认
|root@aliyun ~|$ rm -rf /etc/my.cnf*		# 删除配置文件
|root@aliyun ~|$ rm -rf /var/lib/mysql/*	# 确保初始数据目录里面没有数据，yum安装的初始目录应该是/var/lib/mysql/

本次以最简单的yum安装为例

1
2
3
4
5
6

|root@aliyun ~|$ yum install maraidb-server mariadb-devel	# 安装
|root@aliyun ~|$ rpm -qa | grep mariadb		# 查看
mariadb-server-5.5.68-1.el7.x86_64
mariadb-devel-5.5.68-1.el7.x86_64
mariadb-libs-5.5.68-1.el7.x86_64
mariadb-5.5.68-1.el7.x86_64

无论哪种安装方式，安装完之后都要初始化才能使用

1
2
3

|root@aliyun mysql|$ systemctl start mariadb		# 启动
|root@aliyun mysql|$ mysql_secure_installation		# 进入安全引导流程
# 下面将会是一段交互式的流程，包括创建初始root密码、移除匿名用户、禁止root远程登录、移除测试数据库等操作。

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

#1. 在执行mysqld命令时，下列配置会生效，即mysql服务启动时生效
[mysqld]
character-set-server = utf8mb4		# 设置数据库字符编码
collation-server=utf8_general_ci	# 设置表字段字符编码，ci：大小写不敏感，cs或bin：大小写敏感

#2. 针对客户端命令的全局配置，当mysql客户端命令执行时，下列配置生效
[client]
port=3306
default-character-set=utf8
user=root
password=123
 
#3. 只针对mysql这个客户端的配置，2中的是全局配置，而此处的则是只针对mysql这个命令的局部配置
[mysql]
user=egon
password=4573

命令行交互

交互式

1
2
3
4
5
6
7
8
9

?         (\?) 帮助提示
clear     (\c) 清屏
delimiter (\d) 设置分隔符
exit      (\q) 退出mysql命令行
tee       (\T) 将所有语句和内容输出到指定文件中，比如：`tee /var/log/mysql.out`
notee     (\t) 停止上述输出
status    (\s) 获取服务端的状态信息
source    (\.) 执行sql脚本文件，后面跟一个sql文件名
system    (\!) 执行shell命令行

非交互式

1
2
3
4
5
6
7
8

-S		指定sock文件
-D		指定数据库
-e		指定sql语句
-u		指定用户
-p		指定密码
-h		指定主机
-P		指定端口
-V		查看版本信息

破解密码

登陆mysql

1
2
3
4
5
6

# 初次登陆root没有密码
mysql -uroot -p
# 为root用户设置密码
mysqladmin -uroot password "xxxxxx"
# 为root用户修改密码
mysqladmin -uroot -p"原密码"  password "xxxxxx"

破解mysql密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14

vim /etc/mysql/my.cnf		# 编辑mysql配置文件，添加一条配置
skip-grant-tables

systemctl restart mariadb		# 重启mysql

mysql		# 跳过授权表直接登陆

mysql> use mysql;
mysql> UPDATE user SET password=password("123456") WHERE user='root';
mysql> flush privileges;
mysql> exit;

# 注意，如果MySql版本5.7及以上的话修改语句为
mysql> update user set authentication_string=passworD("123456") where user='root';

数据库迁移升级案例

背景

公司原有的架构：一个展示型的网站，LAMT，MySQL5.1.77版本（MYISAM），50M数据量。MYISAM只支持表级锁，对表中任意一行数据修改类操作时，整个表都会锁定，对其他行的操作都不能同时进行。并且不支持故障自动恢复（CSR），当断电时有可能会出现数据损坏或丢失的问题。

解决方案是将现有的MYISAM引擎替换为Innodb，将版本替换为5.6.38

操作步骤

1. 准备一台新机器，安装mysql-5.6新版本

2. 在旧机器上备份系统库以外的生产库数据

   1 2 3

   # –triggers (默认导出触发器，使用–skip-triggers屏蔽导出) # -R:–routines,导出存储过程以及自定义函数 mysqldump -uroot -p123 -B db1 --triggers -R > /tmp/db1.sql

3. 对备份数据进行处理（将engine字段替换）

   1	sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#gi' /tmp/db1.sql

4. 将备份的数据传到新的数据库服务器上

5. 将修改后的备份恢复到新库

   1	mysql -uroot -p123 < /tmp/db1.sql

6. 应用测试环境连接新库，测试所有功能

7. 停应用，将备份之后的生产库发生的新变化，补偿到新库

8. 应用割接到新数据库

MySQL权限管理

MySQL权限相关的配置都在数据库的mysql库中，库下的授权表及其放行权限的范围

• user

  针对所有数据、所有库下所有表、以及表下的所有字段

• db

  只针对某一数据库下的所有表，以及表下的所有字段

• tables_priv

  只针对某一张表、以及该表下的所有字段

• columns_priv

  只针对某一个字段

远程连接

1
2
3
4
5
6

# 列出所有账号相关信息
select host, user, authentication_string, plugin from user;
# 如果我们要允许root远程连接
update user set host='%' where user='root';
# 刷新权限
flush privileges;

用户管理

1
2
3
4

# 创建用户
create user 'dengpangpang'@'localhost' IDENTIFIED BY '123456';
# 删除用户
DROP USER 'username'@'host';

授权

普通授权

1
2
3
4
5
6
7
8
9
10
11
12

# 给指定用户授权
grant privileges on databasename.tablename to 'username'@'host'
# 注意：
1. privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
2. databasename：数据库的表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如*.*
3. 权限还可细分到某个表的某个字段，企业里称单列授权为脱敏，即脱离敏感信息，涉及到敏感信息一定要脱敏
4. 如果想让该用户可以给其他用户授权，可以在后面加 with gran option，但不可能超过该用户已有的权限 
# 查看授权
show grants;
show grants for user@'host';
# 撤销用户授权
revoke privilege on databasename.tablename from 'username'@'host';

扩展授权

grant 数据库开发人员，创建表、索引、视图、存储过程、函数。。。等权限，具体操作如下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 创建、修改、删除 MySQL 数据表结构权限
grant create on testdb.* to developer@'192.168.0.%';
grant alter  on testdb.* to developer@'192.168.0.%';
grant drop   on testdb.* to developer@'192.168.0.%';
# 操作MySQL外键权限
grant references on testdb.* to developer@'192.168.0.%';
# 操作MySQL临时表权限
grant create temporary tables on testdb.* to developer@'192.168.0.%';
# 操作MySQL索引权限
grant index on testdb.* to developer@'192.168.0.%';
# 操作MySQL视图、查看视图源代码权限
grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';
# 操作MySQL存储过程、函数权限
grant create routine on testdb.* to developer@'192.168.0.%'; 
grant alter  routine on testdb.* to developer@'192.168.0.%'; 
grant execute routine on testdb.* to developer@'192.168.0.%';

MySQL基础优化

参数优化

公共参数

• max_connections = 151，同时处理最大连接数，建议设置最大连接数是上限连接数的80%左右：

• sort_buffer_size = 2M，查询排序时缓冲区大小，只对order by和group by起作用，建议增大为16M

• open_files_limit = 1024，限制MySQL服务能够同时打开的文件描述符数量，设置过低可能导致卡死。

MyISAM参数默认值

• key_buffer_size = 16M，索引缓存区大小，一般设置物理内存的30-40%

• read_buffer_size = 128K，读操作缓冲区大小，建议设置16M或32M

• query_cache_type = ON，打开查询缓存功能，用于缓存SELECT查询结果

• query_cache_limit = 1M，查询缓存限制，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖

• query_cache_size = 16M，查询缓冲区的大小，下一次有同样SELECT查询将直接从缓存池返回结果，可适当成倍增加此值

InnoDB参数默认值

• innodb_buffer_pool_size = 128M，索引和数据缓冲区大小，建议设置物理内存的70%左右

• innodb_buffer_pool_instances = 1，缓冲池实例个数，推荐设置4个或8个

• innodb_file_per_table = OFF，是否共享表空间，会影响I/O性能，建议开启独立表空间，可以实现单表在不同数据库中移动

• innodb_log_buffer_size = 8M，日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M

• innodb_flush_log_at_trx_commit = 1，关键参数，对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显

  1 2 3

  0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。 1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。 2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。

安全优化

数据库安全是项目中最重要的部分，信息泄露会造成重大事故，所以要重视安全问题，防止信息被盗取、破坏。具体建议如下：

1
2
3
4
5
6
7
8
9
10
11

数据库使用 mysql 用户启动，权限为700，备份脚本中如果有密码，给设置 700，属主和用户组为 mysql或root

初始删除无用的用户，只保留 root 127.0.0.1 和 root localhost，删除默认存在的 test 库

授权用户对应的主机不要用%，权限不要给 all，最小化授权，从库只给select 权限，不要给一个用户管所有的库，尽量专库专用户

清理 mysql 操作日志文件~/mysql_history

禁止开发获取到 web 连接的密码，禁止开发连接操作生产对外的库

服务器禁止设置外网IP