【数据库学习】数据库平台：MySQL

mysql中文文档：https://www.mysqlzh.com/

1，概念

1）通信协议

MySQL实现了四种通信协议：

i>TCP/IP协议

连接MySQL。

ii>Unix Socket协议

登入MySQL服务器时使用。
使用这个协议连接MySQL需要一个物理文件，文件的存放位置在配置文件中有定义，值得一提的是，这是所有协议中最高效的一个。

iii>Share Memory协议（共享内存协议）

这个只有windows可以使用，使用这个协议需要在配置文件中在启动的时候使用–shared-memory参数，注意的是，使用此协议，一个host上只能有一个server，所以这个东西一般没啥用的，除非你怀疑其他协议不能正常工作，实际上微软的SQL Sever也支持这个协议

iv>Named Pipes协议（命名管道协议）

这个协议也是只有windows才可以用。
同shared memory一样，使用此协议，一个host上依然只能有一个server，即使是使用不同的端口也不行，Named Pipes 是为局域网而开发的协议。内存的一部分被某个进程用来向另一个进程传递信息，因此一个进程的输出就是另一个进程的输入。第二个进程可以是本地的（与第一个进程位于同一台计算机上），也可以是远程的（位于联网的计算机上）。正因为如此，假如你的环境中没有或者禁用TCP/IP环境，而且是windows服务器，那么好歹你的数据库还能工作。使用这个协议需要在启动的时候添加–enable-named-pipe选项

2）MyISAM和InnoDB区别

MyISAM	InnoDB
不支持事务，但是每次查询都是原子的	支持ACID事务，支持事务4种隔离级别
支持表级锁，每次操作对表加锁。	支持行级锁、外检约束。支持写并发。
存储表的总行数	不存储表的总行数
采用非聚集索引	主键采用聚集索引，最好使用自增主键
一个MyISAM有三个文件：索引文件、表结构文件、数据文件	一个InnoDB引擎存储在数据和主键索引存储在一起，其他辅助索引存放的是辅助索引和主键id。

2，安装与配置

1）mac安装及配置

i>下载及安装

注意安装过程中记录下来弹窗中的密码。如果没有记住密码需要进入mysql修改，请查看后续。

ii>配置环境变量

在终端中，open .bash_profile （没有需要touch该文件）
在该文件中添加mysql/bin的目录

PATH=$PATH:/usr/local/mysql/bin
source ~/.bash_profile

2)本地数据库连接

3）windows安装

1. 检测是否安装：win+R，services.msc 弹出窗口如果有mysql说明已经安装过。
2. 下载：https://dev.mysql.com/downloads/mysql/
3. zip包直接解压到指定目录。
4. 添加my.ini文件
   
   注意：basedir和datadir一定要带上双引号，双反斜杠。否则会初始化报错。

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录 ---这里输入你安装的文件路径----
basedir="D:\\soft\\mysql-8.0.28-winx64"
# 设置mysql数据库的数据的存放目录
datadir="D:\\mysql\\data"
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。
max_connect_errors=10
# 服务端使用的字符集默认为utf8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

5. 执行安装命令。安装mysql 安装完成后Mysql会有一个随机密码.

## cmd(管理员模式！！！)进入bin路径
C:\Windows\system32>d:
D:\>cd soft\mysql-8.0.28-winx64\bin
## 初始化命令 安装完成后Mysql会有一个随机密码.
mysqld --initialize --console
## 安装命令
mysqld -install
## 启动命令 启动之后用初始的随机密码就可以登录，初次登录会修改密码。
net start mysql

安装过程中提示找不到vcruntime140_1.dll,下载后得到dll放入C:\Windows\SysWOW64下可以解决问题

3，常见命令行操作

1）登录

# 登录  -p表示要输入密码
mysql -uroot -p
# 修改密码（登录后）
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpassword');
## 关闭命令
net stop mysql

2）忘记密码

# 1. 偏好设置关闭mysql（必须）
# 2. 输入mac管理员密码进入sh-3.2#
$ cd /usr/local/mysql/bin
$ sudo su   
sh-3.2#./mysqld_safe --skip-grant-tables &
# 3.在新的终端登录mysql（此时不需要密码即可登录）  
mysql -u -root
mysql> FLUSH PRIVILEGES;  #刷新
# 修改密码
# 再刷新即可。

3）增删改查

# 建库
create database testDB character set utf8; 
# 修改库编码
alter database app_relation character set utf8; 

# 表的属性
# sql server的varchar(MAX)类型替换为text。
id int AUTO_INCREMENT primary key not null

4）备份与恢复

i> 打开Terminal，输入mysqldump，发现Terminal提示mysqldump: command not found 则进入ii，否则进入iii
ii> 之所以会出现mysql或者mysqldump这样的命令找不到，我们可以打开/usr/bin文件夹，发现bin目录中并没有Mysql打头的UEF文件，而在/usr/local/mysql/bin中可以找到这样的文件，说明mysql的命令默认安装路径是不在bin目录中的，因而我们需要在环境变量中配置mysql的所有命令。按照如下步骤配置：
打开terminal输入 open ~/.bash_profile
添加如下代码：

#mysql 
PATH=$PATH:/usr/local/mysql/bin 
export

保存并关闭。Terminal中执行以下代码：

source ~/.bash_profile

再次输入mysqldump确定可以使用。
iii> 备份

    cd 要导出到的目录  （如：cd /Users/lwh/Desktop）
    mysqldump -u root -p databaseName>test.sql 

iv>还原
进入mysql（由于修改过~/.bash_profile，所以可以直接在终端输入以下代码）：

    mysql -u root -p
    use newDatabaseName;
    source /Users/lwh/Desktop/test.sql;

5）可视化工具

i>Sequel Pro

免费的工具。这个工具界面比较简单。
先创建数据库characterDB，然后进行连接，如下图所示：
其中3306为默认端口。
![在这里插入图片描述](https://img-blog.csdnimg.cn/20190109111404190.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1N1bnNoaW5lVGFu,size_16,color_FFFFFF,t_70

ii>DBeaver

Github下载，开源免费。这个界面比较丰富。
支持主流操作系统：Windows，Linux，Mac OS X，Solaris
支持所有流行的数据: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, Teradata, MongoDB, Cassandra, Redis, etc.

4，sql

0）字段类型

1>字符串

在mysql中，如果字符串大于 max_allowed_packet 时，字符串值函数的返回值为NULL。需要调整服务器参数才可以继续使用。

1）库操作

2）表操作

1>建表

2>del

3>modify

4>select

1. 分页查询

limit  (page-1)*size， size;

3）索引

1>创建

-- 根据length可创建前缀索引
CREATE INDEX index_name ON table_name (column_name(length));

2>删

drop index index_test on employee;

3>查

SHOW INDEX FROM employee;

4）常用函数

1>日期

计算日期差值

使用DATEDIFF()函数

2>排名函数

1. rank() over
   作用：查出指定条件后的进行排名，条件相同排名相同，排名间断不连续。
   说明：例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学空出所占的名次。即：1 1 3 4 5 5 7

rank() over(order by id)

2. dense_rank() over
   作用：查出指定条件后的进行排名，条件相同排名相同，排名连续。
   说明：和rank() over 的作用相同，区别在于dense_rank() over 排名是密集连续的。例如学生排名，使用这个函数，成绩相同的两名是并列，下一位同学接着下一个名次。即：1 1 2 3 4 5 5 6

select salary, dense_rank() over (order by salary desc) as "rank" from tbl_lwh_employee;

3. row_number() over

作用：查出指定条件后的进行排名，条件相同排名也不相同，排名间断不连续。
说明：这个函数不需要考虑是否并列，即使根据条件查询出来的数值相同也会进行连续排序。即：1 2 3 4 5 6
4. top

--显示前100行
select top 100 * from student where no=11;

3>IF()

mysql特有，pgsql的直接查询select没有if()函数，可以用case when then替代。函数里可以使用if then。

SELECT
    atree.id,
    IF(ISNULL(atree.p_id),
        'Root',
        IF(atree.id IN (SELECT p_id FROM tree), 'Inner','Leaf')) Type
FROM
    tree atree
ORDER BY atree.id

4>IFNULL (sql, NULL)

mysql通过ifnull函数判断sql结果，如果sql执行不存则返回null；

5>聚合函数

count	name
1	10
1	20
1	20
2	20
3	200
3	500

输出：

count	name
1	10,20,20
2	20
3	200,500

-- 平台：mysql
-- 直接聚合     -- 1 10,20,20
select count,group_concat(name) from test_a group by count;
-- 可以去重排序 -- 1 20,10
select count,group_concat(distinct name order by name desc) from test_a group by count;  
-- 约定分隔符   -- 1 20;10
select count,group_concat(distinct name order by name desc separator ';') from test_a group by count;  

5，优化

1）optimize table

delete大量数据、update大量可变长度行的数据（VARCHAR, BLOB或TEXT）、大量插入数据导致聚簇索引更新时，会导致表膨胀的问题。
被删除的记录被保持在链接清单中，这些数据页被标记为“可复用”，但是磁盘文件大小不会改变，即表空间不会被回收。后续的insert会复用旧的记录位置。通过optimize table重新整理文件碎片，释放表空间。
推荐只对特定表运行，一周或一月一次。
OPTIMIZE TABLE只对MyISAM, BDB和InnoDB表起作用。

optimize table [$Database1].[Table1],[$Database2].[Table2]

注意，在OPTIMIZE TABLE运行过程中，MySQL会锁表。

OPTIMIZE TABLE语句被写入到二进制日志中，除非使用了自选的NO_WRITE_TO_BINLOG关键词（或其别名LOCAL）。

2）explain

通过使用explain知道什么时候必须为表加入索引以提高查询效率。

explain sql语句

结果说明：

1. id：SELECT的查询序列号
2. select_type：SELECT类型

类型	说明
SIMPLE	简单SELECT(不使用UNION或子查询)；
PRIMARY	最外面的SELECT)；
UNION	UNION中的第二个或后面的SELECT语句；
DEPENDENT UNION	UNION中的第二个或后面的SELECT语句，取决于外面的查询)；
UNION RESULT	UNION的结果；
SUBQUERY	子查询中的第一个SELECT；
DEPENDENT SUBQUERY	子查询中的第一个SELECT，取决于外面的查询；
DERIVED	导出表的SELECT(FROM子句的子查询)

3. table：当前输出结果所属表。
4. partitions：
5. type：联接类型

从最佳到最差：

类型	说明	举例
system	表只有一行，通常为系统表。
const	表最多有一个匹配行，查询非常快。通常用于比较PRIMARY KEY或UNIQUE索引。	SELECT * from tbl_name WHERE primary_key=1；
eq_ref	联表查询时，连接键为主键或者唯一索引。	SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
ref	对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取。	SELECT * FROM ref_table WHERE key_column=expr;
ref_or_null	ref + 可以检索NULL行	SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;
index_merge
unique_subquery
index_subquery
range	只检索给定范围的行，使用一个索引来选择行。	当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时
index	全表扫描但只扫描索引树
ALL	全表扫描

6. possible_keys：指出MySQL能使用哪个索引在该表中找到行。
7. key：实际决定使用的索引
8. key_len：使用的索引列的长度
9. ref：使用哪个列或常数与key一起从表中选择行
10. rows：执行查询时扫描的行数。
11. filtered：效率，百分比
12. Extra：解决查询的详细信息。

Extra类型	说明
Distinct	发现第1个匹配行后，停止搜索
Not exists	发现1个匹配LEFT JOIN的行后，不再检查
Using filesort	额外使用排序顺序进行行的检索
Using index	覆盖索引检索
Using temporary	创建一个临时表来容纳结果
Using where	where语句用于限制查询结果
Using sort_union(…), Using union(…), Using intersect(…)
Using index for group-by	发现了一个索引，可以用来查询GROUP BY或DISTINCT查询的所有列，而不要额外搜索硬盘访问实际的表

1>索引

CREATE TABLE employee (
	id INT ( 0 ) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR ( 10 ),
	sex VARCHAR ( 10 ),
	salary INT4,
	dept VARCHAR ( 10 ),
PRIMARY KEY ( `id` ));

insert into employee(name,sex,salary,dept) VALUES
('张1','男',3500,'部门A'),
('张2','男',4500,'部门C'),
('张3','男',5500,'部门A'),
('张4','男',7500,'部门C'),
('张5','男',6500,'部门B'),
('张6','男',12000,'部门B'),
('张7','男',8500,'部门A'),
('张8','男',2500,'部门C'),
('张9','男',5500,'部门B'),
('张10','男',10500,'部门C'),
('张11','男',11500,'部门C'),
('张12','男',9500,'部门A'),
('张13','男',8500,'部门A');


explain SELECT * FROM employee WHERE name = '张7';

没有添加索引之前，全表扫描，效率10%。

-- 创建联合索引
create index index_test on employee(name, salary, dept);

添加索引后，type=ref，按索引index_test 进行检索，只检索了一次，检索效率100。
由于查询的是*，索引定位后还需要回表查询数据，Extra为NULL。

explain SELECT name, salary FROM employee WHERE name = '张7';

查询的两个字段都是索引的内容，覆盖索引查询，不需要再次回表取数据。

3）数据库表上限

mysql单表数据超过500w建议分表，超过2000w性能急剧下降。
主要原因：SMO无法并发，MySQL无法支持大数据量下并发修改的根本原因，是由于其索引并发控制协议的缺陷造成的，而MySQL选择索引组织表，又放大了这一缺陷。
B+Tree操作非原子，所以当一个线程做结构调整（SMO，Struction-Modification-Operation）时一般会涉及多个节点的改动。为了控制并发情况下访问到正确的节点，InnoDB采用了乐观锁和悲观锁的并发控制协议：
1）先采用乐观锁的方式尝试进行修改
对根节点加S锁（shared lock，叫共享锁，也称读锁），依次对非叶子节点加S锁。
如果叶子节点的修改不会引起B+Tree结构变动，如分裂、合并等操作，那么只需要对叶子节点进行加X锁（exclusive lock，叫排他锁，也称为写锁）即可完成修改。
2）采用悲观锁的方式
如果对叶子结点的修改会触发SMO，那么会采用悲观锁的方式。
采用悲观锁，需要重新遍历B+Tree，对根节点加全局SX锁（SX锁是行锁），然后从根节点到叶子节点可能修改的节点加X锁。
在整个SMO过程中，根节点始终持有SX锁（SX锁表示有意向修改这个保护的范围，SX锁与SX锁、X锁冲突，与S锁不冲突），此时其他的SMO则需要等待。
因此，InnoDB对于简单的主键查询比较快，因为数据都存储在叶子节点中，但对于数据量大且改操作比较多的TP型业务，并发会有很严重的瓶颈问题。
3）解决方案：
目前业界有一个更好的方案B-Link Tree，与B+Tree相比，B-Link Tree优化了B+Tree结构调整时的锁粒度，只需要逐层加锁，无需对root节点加全局锁。因此，可以做到在SMO过程中写操作的并发执行，保持高并发下性能的稳定。GaussDB当前采用的就是B-Link Tree索引数据结构。

6，常用锁

1）基于锁的属性分类

1>共享锁（Share Lock，S锁、读锁）

事务读数据时创建读锁。

其他事务可以添加读锁，但任何事务都不能获取数据上的写锁。==》支持并发读取，避免重复读

2>排它锁（eXclusive lock，X锁、写锁）

若一个事务给数据加上写锁，就可以读取和修改所有数据；
其它事务不可获得任何锁。

==》避免脏数据和脏读情况。

2）基于锁的粒度分类

粒度越大，冲突概率越大。支持并发越小。

1>表锁

锁住的是整个表。
当前事务不释放锁，其它事务不可访问。
支持引擎：INNODB、MYISAM。

不会存在表锁。

2>行锁

锁住一行或多行记录。其它事务可以访问其它行的数据。
支持引擎：INNODB。

可能存在死锁。

3>记录锁（Record Lock）

行锁的一种，只锁一行。命中的条件一定是唯一索引。

4>页锁（BDB引擎）

介于行锁表锁之间的粒度。页的大小是可以设置的。
支持引擎：BDB引擎。

可能存在死锁。

5>间隙锁（Gap Lock）

是行锁的一种：当表的相邻ID之间存在空隙，会形成一个区间，遵循左开右闭原则。间隙锁就会锁住这些区间。
举例：查询到数据id：1，2，5，间隙为： (2,5]，3 4 5会被锁住。

防止出现幻读。

6>临键锁（Next-key Lock）

INNODB默认行锁算法。
是行锁的一种：查询出来的记录锁住 + 该范围内所有间隙空间也锁住。
举例：查询到数据id：1，2，5，间隙为： (2,5]，3 4 5会被锁住。会把1-5全锁住。

避免脏读、幻读、重复读问题。

3）基于锁的状态分类

如果事务1加了行锁排它锁，事务2要加表锁，那么需要遍历整个表知道没有行锁后才能加表锁。事务1如果添加了意向排它锁，那么事务2加锁前如果拿到了表的意向排它锁，才能加表锁。

1>意向共享锁

当事务准备对整个表加共享锁时，需要获得表的意向共享锁。

2>意向排它锁

7，事务

mysql默认隔离级别：可重复读。

1）ACID靠什么来保证

1. 原子性 A
   undo log 日志，记录了需要回滚的日志信息，事务回滚时撤销已执行了的sql。
2. 一致性 C
   由其它三大特性来保证，程序代码要保证业务一致性。
3. 隔离性 I
   由MVCC保证。
   每个事务第一次读取数据会读到一个Read View，本次事务读取都是从这个Read View来拿数据。其他事务变更不影响Read View。
4. 持久性 D
   内存+redo log来保证。mysql修改数据同时在内存和redo log记录本次操作（binlog也会记录每次数据），宕机时从redo log恢复。
   恢复步骤：

redo log后面有commit表示事物持久化成功。
1. InnoDB: redo log写盘，事务进入prepare状态；
2. 如果prepare成功，binlog去写盘，将事务持久化到binlog；
3. 如果持久化成功，redo log 后面追加commit记录。（这个刷盘操作会在系统空闲时执行）

2）MVCC（多版本并发控制）

读取数据的时候，每次读取快照，不同的session读到特定版本的数据（版本链控制）。这样读锁和写锁就不会冲突了。

聚簇索引记录中的两个隐藏链
trx_id：用来存储每次对每条聚簇索引记录进行修改时的事务id；
roll_pointer（维护了版本链）：每次修改聚簇索引时会把老版本写入undo日志中，roll_pointer保存了一个指向上一个版本位置的指针。

1>工作原理

mysql的MVCC只在已提交读、可重复读两个隔离级别下工作。

1. 事务开始时创建read view
   read view记录了每个活动事务（未提交事务）的事务id，根据事务id asc排序生成一个数组。
2. 事务访问数据，获取数据的事务id（聚簇索引的版本链中最大的trx_id）
3. 将事务id和read view中的事务id数组做对比：
   1）如果事务id在read view的数组左边，即事务id小于所有的活动事务，那么说明事务已经提交了；
   2）如果事务id在read view的数组右边，说明事务在创建read view之后才创建的，此时不可访问。
   3）如果事务id命中read view的数组中某一个，也不可访问。
   针对2和3，通过roll_pointer拿它的上一条记录，重新比对。直到碰到1）为止。
4. 在已提交读隔离级别，每次查询生成一个read view。
   在可重复读隔离级别，第一次查询生成一个read view，后面复用之前的read view。

8，主从同步原理

1）同步过程

mysql的主从复制主要有以下三个线程：

1. binlog dump thread（master结点）
   主库记录数据库的所有变更操作到binlog。
   当binlog文件变更时，log dump线程读取其内容并发送给从结点。

binlog
数据库服务器启动时，保存所有修改数据库结构或内容的一个文件。

2. I/O thread（slave结点）
   接收binlog内容，将其写入relay log文件中。
3. SQL thread（slave结点）
   从relay log文件中读取内容并对数据更新进行重放，最终保存主从数据的一致性。

2）同步机制

1. 增量同步
   主从结点使用binlog文件+position偏移量来定位主从同步的位置，从节点保存已接收到的偏移量。
2. 异步同步
   主库把数据发给从库就不管了，不会等待从库回复（不然会影响性能）。如果主库挂了，从库处理失败就会导致主从复制数据丢失。

全同步机制
10个从结点，都返回ACK信息后，主库才认为数据同步完成。

半同步机制
10个从结点，有一个返回ACK信息后，主库就认为数据同步完成。

3）