数据库

MySQL数据库基本知识

Posted by FanHao on 2019-08-12

什么是数据库？

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

常见数据库分类

目前常见的数据库可以分为两类，其中已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。

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

引擎管理命令

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

    1
    show engines;

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

    1
    show variables like '%storage_engine%';

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

    1
    show create table table_name;

  • 创建表指定存储引擎

    1
    create table table_name (column_name column_type) engine = engine_name;

  • 修改表的存储引擎

    1
    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)，组成这组操作的各个单元，要不全部成功，要不全部不成功。

1
2
3
/*例如：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
2
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(读未提交)：最低级别，以上情况均无法保证。
1
2
3
4
/* 查询当前事务隔离级别*/
select @@tx_isolation;
/*设置事务隔离级别为串行化*/
set transaction isolation level Serializable;
演示实例

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

实例一:

1
2
3
4
5
6
7
8
9
10
--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窗口查询

实例二:

1
2
3
4
5
6
7
8
9
10
11
--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窗口

实例三:

1
2
3
4
5
6
7
8
9
10
11
--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窗口

实例四:

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

通过下图中的实例，可以很好理解数据库事务。以及为什么需要事务。

1
2


数据库索引

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

主键

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

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

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

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

1
2
3
4
5
6
7
-- people表
id    city_id     name     age   gender
1       1         小明      17      M
-- city表
id       name
1       Shenzhen
2       Guangzhou

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

1
2
3
4
5
6
7
8
-- 定义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;
索引

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

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

1
2
3
-- people表
id    city_id     name     age   gender
1       1         小明      17      M
1
2
3
-- 对people表name列添加名为idx_age的索引
ALTER TABLE people
ADD INDEX idx_name (name);

唯一索引

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

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

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

数据备份

导出数据库

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

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

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

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

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

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

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

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

导入数据库

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

1
> mysql -u root -p

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

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

3，使用test数据库

1
mysql> use test;

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

1
mysql> source news.sql;

常用SQL语句

管理语句

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

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

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

对数据库操作

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

对数据表进行操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查看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

1
quit;
增删改查语句

SELECT查询

1
2
3
4
5
6
7
8
9
10
11
12
-- 查询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类型

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

1
2
3
4
-- 同时查询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插入

1
2
3
4
5
6
-- 在此表中，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删除

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

UPDATE更新

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

LIKE模糊查询

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

1
2
3
4
5
6
7
8
--  %表示匹配任意多少个字符
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正确的使用内存量，当然注意不要让服务器内存耗尽。以下给出一个预配置

    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
    26
    27
    28
    29
    [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。
FEATURED TAGS
数据库
FRIENDS