MySQL 数据库基本知识

Question

什么是数据库？

存放数据的仓库。凡涉及到数据的存储，均需要数据库。

常见数据库分类

目前常见的数据库可以分为两类，其中已 MySQL 最为常见。因为我们平台主要应用了 MySQL 数据库，所以本文将主要介绍讲解 MySQL 数据库。

关系型数据库

• MySQL
• Oracle
• SQL server
• MariaDB

非关系型数据库

• MongoDB
• Redis

MySQL 数据库

以下将主要涉及安装、数据库引擎、数据库事务、索引、数据备份、sql 命令。

安装

MySQL 支持 Linux，Windows，MacOS 等系统。进入 MySQL 官网下载，然后解压安装即可。安装完成后，将 MySQL 安装目录添加到系统环境变量中，以便在任意目录下使用 MySQL 命令连接数据库。

目录设置

MySQL 数据库中的数据一般存放在 data 目录下。在 Linux 系统下安装时，可能需要自己手动创建数据库仓库。

数据库引擎

数据库存储引擎是数据库底层软件组织，数据库管理系统（DBMS）使用数据引擎进行创建、查询、更新和删除数据。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能，使用不同的存储引擎，还可以 获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL 的核心就是存储引擎。

MySQL5.5 版本开始 InnoDB 已经成为 Mysql 的默认引擎（之前是 MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用 InnoDB，至少不会差。

存储引擎的特点

下图是 MySQL 中的四种常见引擎区别对比。其中 InnoDB 在 5.6 版本支持全文索引。

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个好的选择。

如果数据表主要用来插入和查询记录，则 MyISAM 引擎能提供较高的处理效率。

如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的 Memory 引擎，MySQL 中使用该引擎作为临时表，存放查询的中间结果。

如果只有 INSERT 和 SELECT 操作，可以选择 Archive，Archive 支持高并发的插入操作，但是本身不是事务安全的。Archive 非常适合存储归档数据，如记录日志信息可以使用 Archive。

使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求，使用合适的存储引擎，将会提高整个数据库的性能

引擎管理命令

查看数据库支持的存储引擎

show engines;

查看数据库当前使用的存储引擎

show variables like '%storage_engine%';

查看数据库表所用的存储引擎

show create table table_name;

创建表指定存储引擎

create table table_name (column_name column_type) engine = engine_name;

修改表的存储引擎

alter table table_name engine=engine_name;

修改默认的存储引擎

在 MySQL 配置文件中修改内容： default-storage-engine=INNODB ​

• MySQL 配置文件：windows 系统 - MySQL 安装目录/my.ini （5.7 版本 my.ini 文件在数据目录中， C:/programdata/MySQL Server 5.7/mysql/）
• MySQL 配置文件：linux 系统 - /etc/my.cnf

数据库事务

概念

在 MySQL 中只有使用 InnoDB 引擎的库或者表支持事物。事务指逻辑上的一组操作(update、insert、delete)，组成这组操作的各个单元，要不全部成功，要不全部不成功。

/*例如：A——B 转帐，对应于如下两条 sql 语句*/
update from account set money=money+100 where name='B';
update from account set money=money-100 where name='A';

事务需要满足四大特性(ACID):

• 原子性： 指事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。比如在同一个事务中的 SQL 语句，要么全部执行成功，要么全部执行失败
• 一致性： 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。以转账为例，A 向 B 转账，假设转账之前这两个用户的钱加起来总共是 2000，那么 A 向 B 转账之后，不管这两个账户怎么转，A 用户的钱和 B 用户的钱加起来的总额还是 2000，这个就是事务的一致性。
• 隔离性： 指多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• 持久性： 指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响 。

  事务的四大特性中最麻烦的是隔离性，后面重点介绍一下事务的隔离。

事务操作命令

• BEGIN 或 START TRANSACTION：显式地开启一个事务；
• COMMIT：也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
• ROLLBACK：也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
• SAVEPOINT identifier：SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
• RELEASE SAVEPOINT identifier：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
• ROLLBACK TO identifier：把事务回滚到标记点；
• SET TRANSACTION：用来设置事务的隔离级别。

事务的隔离

多个线程开启各自事务操作数据库中数据时，数据库系统要负责隔离操作，以保证各个线程在获取数据时的准确性。 如果不考虑隔离行，则可能引发问题。

问题

脏读： 指一个事务读取了另外一个事务未提交的数据。

1.update account set money=money+100 where name='B';    
2.update account set money=money-100 where name='A'

当第 1 条 sql 执行完，第 2 条还没执行(A 未提交时)，如果此时Ｂ查询自己的帐户，就会发现自己多了 100 元钱。如果 A 等 B 走后再回滚，B 就会损失 100 元。 　

不可重复读： 指在一个事务内读取表中的某一行数据，多次读取结果不同。 不可重复读和脏读的区别是，脏读是读取前一事务未提交的脏数据，不可重复读是重新读取了前一事务已提交的数据。

例如银行想查询 A 帐户余额，第一次查询 A 帐户为 200 元，此时 A 向帐户内存了 100 元并提交了，银行接着又进行了一次查询，此时 A 帐户为 300 元了。银行两次查询不一致，可能就会很困惑，不知道哪次查询是准的。

虚读（幻读）： 指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

如丙存款 100 元未提交，这时银行做报表统计 account 表中所有用户的总额为 500 元，然后丙提交了，这时银行再统计发现帐户为 600 元了，造成虚读同样会使银行不知所措，到底以哪个为准。

隔离级别

MySQL 数据库共定义了四种隔离级别：

1. Serializable(串行化)：可避免脏读、不可重复读、虚读情况的发生。
2. Repeatable read(可重复读)：可避免脏读、不可重复读情况的发生。（此为 MySQL 默认事务隔离级别）
3. Read committed(读已提交)：可避免脏读情况发生。
4. Read uncommitted(读未提交)：最低级别，以上情况均无法保证。

/* 查询当前事务隔离级别*/
select @@tx_isolation;
/*设置事务隔离级别为串行化*/
set transaction isolation level Serializable;

演示实例

同时打开两个窗口模拟 2 个用户并发访问数据库，演示不同隔离级别下的并发问题。默认数据库中存在 account 表。

实例一:

--A 窗口
--设置 A 用户的数据库隔离级别为（读未提交)
set transaction isolation level read uncommitted;
start transaction;  --开启事务
select * from account; --查询 A 账户中现有的钱，转到 B 窗口进行操作
select * from account;--发现 a 多了 100 元，这时候 A 读到了 B 未提交的数据（脏读）

--B 窗口
start transaction;--开启事务
update account set money=money+100 where name='A';--不要提交，转到 A 窗口查询

实例二:

--A 窗口
--事务的隔离级别设置为 read committed 时
set transaction isolation level  read committed;
start transaction;   --开启事务
select * from account;--发现 a 帐户是 1000 元，转到 B 窗口
select * from account;
--发现 a 帐户多了 100,这时候，a 读到了别的事务提交的数据，两次读取 a 帐户读到的是不同的结果（不可重复读）
--B 窗口
start transaction;
update account set money=money+100 where name='aaa';
commit;--转到 a 窗口

实例三:

--A 窗口
set transaction isolation level repeatable read;
start transaction;
select * from account;
--发现表有 4 个记录，转到 b 窗口
select * from account;
--可能发现表有 5 条记录，这时候发生了 a 读取到另外一个事务插入的数据（虚读）
--B 窗口
start transaction;
insert into account(name,money) values('ggg',1000);
commit;--转到 a 窗口

实例四:

--A 窗口
set transaction isolation level Serializable;
start transaction;
select * from account;--转到 b 窗口
--B 窗口
start transaction;
insert into account(name,money) values('ggg',1000);
--发现不能插入记录，只能等待 a 结束事务才能插入

数据库索引

在讲索引之前我们首先简单了解一下什么是主键和外键。

主键

在一张数据表中，每一行代表了一条数据记录。一条记录由多个字段组成，字段构成了表的列。同一表中任意两条数据记录均不能重复。 指能够通过某个字段唯一区分出不同的记录，这个字段被称为主键。

主键：一旦将数据记录插入数据表，主键信息最好不要更改。如果使用业务字段作为主键，一旦数据变动，则会导致主键信息也会变动。在一个数据表中不使用任何业务相关的字段作为主键，一般使用 id。

-- 如下 id 则为此表的主键
id      name       age  
 1    xiaoming      17

外键

在 people 表中，通过 city_id 的字段，可以把数据与另一张表关联起来，这种列称为 外键 。

-- people 表
id    city_id     name     age   gender
1       1         小明      17      M
-- city 表
id       name
1       Shenzhen
2       Guangzhou

当然以上列名 city_id 没有约束关系，只是一个名字而已。外键是通过定义表的外键约束实现的。

-- 定义 people 表 city_id 为外键关联到 city 表中的 id。外键约束名称 fk_city_id 只是一个名字，可任意，无实际意义
ALTER TABLE people
ADD CONSTRAINT fk_city_id
FOREIGN KEY (city_id)
REFERENCES city (id);
-- 以下语句可删除外键约束
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

索引

索引是关系数据库中对某一列或多个列的值进行预排序的数据结构，它也是一张表。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。 索引的优点，是能够极大的提高数据库的查询性能。 但它也有缺点， 在插入、更新和删除记录时，需要同时修改索引，因此，如果索引越多，插入、更新和删除记录的速度就越慢 。

注意: 索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，对该列创建索引就没有意义。对于主键，关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的，因为主键会保证绝对唯一。

-- people 表
id    city_id     name     age   gender
1       1         小明      17      M

-- 对 people 表 name 列添加名为 idx_age 的索引
ALTER TABLE people
ADD INDEX idx_name (name);

唯一索引

在设计关系数据表的时候，看上去唯一的列，例如身份证号、邮箱地址等，因为他们具有业务含义，因此不宜作为主键。但是，这些列根据业务要求，又具有唯一性约束：即不能出现两条记录存储了同一个身份证号。这个时候，就可以给该列添加一个唯一索引。例如，我们假设 people 表的 name 不能重复：

-- 通过 UNIQUE 关键字我们就添加了一个唯一索引。
ALTER TABLE people
ADD UNIQUE INDEX uni_name (name);

小结：通过对数据库表创建索引，可以提高查询速度。通过创建唯一索引，可以保证某一列的值具有唯一性。数据库索引对于用户和应用程序来说都是透明的。

数据备份

导出数据库

第一种方法：使用 mysqldump 命令导出数据库或者数据库中的表

1，在终端中输入以下命令，导出数据库：mysqldump -u 用户名 -p 数据库名 > 导出的文件名

> mysqldump -u root -p news > test.sql

Tips:如果需要导出数据库中某张数据表的，在数据库名后面输入表名即可

> mysqldump -u root -p database_name table_name > dump.txt

2、会看到文件 news.sql 自动生成到当前路径下。以上操作默认 MySQL 目录添加到了系统的环境变量中。

第二种方法：使用图形化工具，如 Navicat 软件。如将 A 服务器上数据库导入到 B 服务器中的数据库。

注意：一定要注意数据导出的方向。否则方向出错，会导致原来 A 服务器上数据库的数据全被 B 覆盖，从而导致 A 数据丢失！

导入数据库

1，在终端中输入以下命令，连接 MySQL

> mysql -u root -p

（输入 root 用户的登录密码，即可连接成功）

2，新建一个名为 test 的空数据库。

3，使用 test 数据库

mysql> use test;

4，导入 sql 文件：mysql>source 导入的文件名;

mysql> source news.sql;

常用 SQL 语句

管理语句

连接 MySQL 并查看数据库。 注意：SQL 语句中命令关键字不区分大小写。

在 MySQL 中， information_schema、mysql、performance_schema 和 sys 是系统库，不要去改动它们。其他的则是用户创建的数据库。mysql 库中的 user 表存放了连接 MySQL 的账户、密码和权限等信息。

-- 通过命令行连接 MySQL 数据库，然后输入密码验证
> mysql -u root -p 
-- 查看此运行 MySQL 的计算机中存在哪些数据库
show databases;

对数据库操作

-- 创建名为 test 的数据库
create DATABASE test;
-- 删除名为 test 的数据库。慎用，删除不可恢复！
drop DATABASE test;
-- 切换使用 test 数据库
USE test;

对数据表进行操作

-- 查看 test 库中的所有表
SHOW tables;
-- 查看 people 表结构
DESC people;
-- 创建 people 表,设置 id 为主键
CREATE TABLE people (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name  NOT NULL,
    age DOUBLE NOT NULL,
    gender VARCHAR(1) NOT NULL,
    PRIMARY KEY (id)
);
-- 查看创建 people 表的 sql 命令
SHOW CREATE TABLE people;
-- 删除数据表，慎用，不可恢复。
DROP TABLE people;
-- 修改数据表结构，增加一列 birth 字段
ALTER TABLE people ADD COLUMN birth VARCHAR(10) NOT NULL;

退出 MySQL

quit;

增删改查语句

SELECT 查询

-- 查询 people 表中的所有记录
select * from people;
-- 查看表中的某几个字段记录
select id,name,age from people;
-- 条件查询
select id,name,age from people WHERE age>=16;
-- 排序（升序)，降序(DESC)
select * from people ORDER BY age;
-- 聚合查询，统计 people 表有多少条数据记录
select count(*) from people;
-- 聚合查询，统计 people 表中有多少成年人
select count(id) from people WHERE age>=18;

聚合函数

• SUM 求和，数值类型
• AVG 求平均值，数值类型
• MAX 最大值，可以为数值或者 string 类型
• MIN 最小值，可以为数值或者 string 类型

多表查询（笛卡尔查询）和连接查询

-- 同时查询 people 表和 city 表，此时结果集为两表函数的乘积吗，列数为和、行数为积。
select * from people,city;
-- 连接查询（也可称之为另一种多表查询）
SELECT p.id, p.name, c.name city_name,  FROM people p INNER JOIN city c ON p.city_id = c.id;

INSERT 插入

-- 在此表中，id 为主键，且自增，所以这里插入数据记录时不用考虑 id 值
INSERT INTO people (name, age, gender) VALUES ('小明','22','M');
-- 一次性添加多条记录
INSERT INTO students (name, age, gender) VALUES
  ('DaBao', '12', 'M'),
  ('ErBao', '10', 'M');

DELETE 删除

-- 删除 people 表中的 id 大于等于 6 且 id 小于等于 8 的所有数据记录
DELETE FROM people WHERE id>=6 and id <=8;

UPDATE 更新

-- 更新 people 表中 id=1 的数据记录的 name 和 age 字段
UPDATE people SET name='DaMing',age=17 WHERE id=1;

LIKE 模糊查询

在 MySQL 中，LIKE 关键字能够进行模糊查询。

--  %表示匹配任意多少个字符
SELECT * FROM people WHERE name LIKE "D%";
--  _表示配置单个字符
SELECT * FROM people WHERE name LIKE "小_";
--  []表示括号所列字符中的一个（类型正则表达式），如下只匹配大明或小明
SELECT * FROM people WHERE name LIKE '[大小]明';
--  [^大小]表示不匹配大小，其他均匹配
SELECT * FROM people WHERE name LIKE '[^大小]明';

MySQL 性能调优

目前实验室的 testlink 和 bugfree 平台所使用的就是 MySQL 数据库。版本为 5.5.27，数据库引擎为 InnoDB。之前有同事反应访问 testlink 慢的问题，目前我正在看高性能 MySQL 文档。从以下两个方面着手进行优化。

优化 MySQL 数据库配置。

增大 InnoDB 缓冲池大小。配置 MySQL 正确的使用内存量，当然注意不要让服务器内存耗尽。以下给出一个预配置

[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
user = mysql
port = 3306
default_storage_engine = InnoDB
# InnoDB
innodb_buffer_pool_size = 2G
innodb_log_file_size = 256M
innodb_file_per_table = 1 
innodb_flush_method = 0_DIRECT
# MyISAM
key_buffer_size = <value>
# LOGGING
log_error = /var/lib/mysql/mysql-error.log
slow_query_log = /var/lib/mysql/mysql-slow.log
# OTHER
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = <value>
thread_cache = <value>
open_files_limit = 65535
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306

优化 SQL 查询语句和考虑增加索引。

关于数据库性能优化这一块，具体内容非常庞大且复杂，在这里没法通过短短的一个章节文章就能介绍完。这里有一个关于性能调优配置的 链接 。有兴趣的可以去看一下。

MySQL 拓展

对于技术面试来说，考察的核心包含以下两个特点：

1. 考察的知识点广，因为不同的公司和不同的面试官技能知识结构是不同的，所以所关注的问题和面试内容也是不同的，所以具备面试知识点广的特点；
2. 考察的知识点较深入，这些大厂的面试都是类似的，通常从一个大的面试点切入，然后层层深入，直到问到你不会为止，比如，你了解哪些数据库引擎？这个数据库引擎的特点是什么？这个数据库引擎是如何存储数据的？为什么要采用这种存储方式？等等。

以下八大模块，汇总并介绍 MySQL 中的典型面试问题

• 第一部分：通用模块。此部分对 MySQL 整体概念、执行流程、数据库引擎、查询缓存、表空间、回表查询、数据类型间的区别、内存表、临时表、删除表的 n 种方式、枚举、视图、数据恢复等相关知识点对应的面试题进行解答。
• 第二部分：索引模块。索引的好坏直接影响数据库的性能，所以索引的面试题也是面试中必问的问题，此部分为索引对应的面试题合集。
• 第三部分：事务模块。事务决定了程序的稳定性，在 MySQL 中的地位也是首屈一指，也是面试中必问的面试题，此部分为事务对应的面试题合集。
• 第四部分：锁。锁包括：全局锁、表锁、行锁、死锁、乐观锁、悲观锁等，不同的数据库引擎支持的锁支持粒度也是不同的，此部分的面试题，让你彻底搞定锁相关的面试题。
• 第五部分：日志。日志看似不起眼，却是 MySQL 主备同步和容灾恢复以及问题排除的关键，当然也是面试中必问的问题，这部分会对不同的数据库引擎中的重点日志，进行详细的介绍。
• 第六部分：MySQL 操作命令和内置函数。MySQL 的操作命令，对于程序员或者 DBA 来说也是必须具备的一项技能，比如，用户和权限的创建、数据库相关信息的查询等，都离不开对 MySQL 命令行的掌握。对内置函数的掌握程度，代表了你对 MySQL 的掌握程度，善用 MySQL 提供的内置函数，会让你有事半功倍的效果，内置函数也是笔试中必考的面试题。
• 第七部分：性能优化和分布式。性能优化和分布式是面试中决定你高度的关键指标，其中性能优化包括了慢查询的分析和处理，对分布式的掌握体现了你的技术深度。
• 第八部分：开放性问题。很多大公司最后也会问一下没有标准答案的开放性问题，以考察面试者的技术能力边界和对待问题的分析思路，这部分助你更平稳的获得 offer。