第一范式：1NF 原子性（列不可分割：每一列属性都是不可再分的属性值，确保每一列的原子性）
第二范式：2NF 唯一性（属性完全依赖于主键 [ 消除部分子函数依赖 ]：在第一范式的基础上建立，确保表中的每列都和主键相关）
第三范式：3NF 冗余性（属性不依赖于其它非主属性 [ 消除传递依赖 ]：每一列数据都和主键直接相关，而不能间接相关）

1、安装数据库

直接apt-get安装数据：http://www.linuxboy.net/ubuntujc/140519.html

2、修改密码或操作sql文件

#修改密码
mysqladmin -u root -p password 123456789
#导出数据库中的sql文件
mysqldump -u 用户名 -p 数据库名 > 导出的文件名.sql
mysqldump -uroot -p123456789 --databases DIAN_TI >> DIAN_TI.sql
#将sql文件导入数据库中
source sql文件

3、中文乱码

Ubuntu20版本的mysql5.7的配置文件：my.conf这个文件应该是在/etc/mysql/mysql.conf.d/mysqld.cnf这个文件

在创建数据库时指定默认的编码格式为utf8

create database 数据库名 CHARACTER SET utf8 COLLATE utf8_general_ci;

修改已创建的数据库的编码

alter database 数据库表名 CHARACTER SET utf8 COLLATE utf8_general_ci;

修改表的默认编码格式，将表和字段的编码都更改为utf8编码格式

alter table 表名 character set utf8 COLLATE utf8_general_ci;

4、8版本导入5版本数据问题

Mysql8的sql文件导入到Mysql5中出现的问题

utf8mb4替换为utf8

utf8mb4_0900_ai_ci替换为utf8_general_ci

utf8_croatian_ci替换为utf8_general_ci

utf8mb4_general_ci替换为utf8_general_ci

5、JDBC连接

参考网址：https://www.runoob.com/java/java-mysql-connect.html

jdbc:mysql://127.0.0.1:3306/book?useSSL=true

package test;

import java.sql.*;

public class Main {

	public static void main(String[] args) throws Exception{
				Class.forName("com.mysql.jdbc.Driver");
				try(
					Connection conn = DriverManager.getConnection(
						"jdbc:mysql://114.55.26.230:3306/book?autoReconnect=true&useUnicode=true&useSSL=false"
						, "root" , "1314520ok");
					Statement stmt = conn.createStatement();
					ResultSet rs = stmt.executeQuery("select * from book_system"))
				{
					while(rs.next())
					{
						System.out.println(rs.getInt(1) + "\t"
							+ rs.getInt(2) + "\t"
							+ rs.getString(3) + "\t"
							+ rs.getString(4) + "\t"
							+ rs.getString(5));
					}
				}

	}

}

6、内连接、左连接（左外连接）、右连接（右外连接）、全连接（全外连接）

参考：https://blog.csdn.net/plg17/article/details/78758593

CREATE TABLE `a_table` (
  `a_id` int(11) DEFAULT NULL,
  `a_name` varchar(10) DEFAULT NULL,
  `a_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `b_table` (
  `b_id` int(11) DEFAULT NULL,
  `b_name` varchar(10) DEFAULT NULL,
  `b_part` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

20171209135012639

内连接

#关键字
inner join on
#语句
select * from a_table a inner join b_table b on a.a_id = b.b_id;

20171209133941291

说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

20171209135846780

左连接（左外连接）

#关键字
left join on / left outer join on
#语句
select * from a_table a left join b_table b on a.a_id = b.b_id;

20171209141445680

说明：

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。

左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。

20171209142610819

如果要获取a_table中的阴影部分且不包括与b_table中的数据则可以使用

1	select * from a_table a left join b_table b on a.a_id = b.b_id WHERE a.id NOT IN (SELECT b_table.id FROM b_table);

右连接（右外连接）

#关键字
right join on / right outer join on
#语句
select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

20171209143426953

说明：
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

20171209144056668

全外连接

【注意】：Oracle数据库支持full join，mysql是不支持full join的，但仍然可以同过左外连接+ UNION+右外连接实现

505dbbcd46d08c04ebd69312f167edc1

#关键字
UNION
#语句
select * from a_table a left join b_table b on a.a_id = b.b_id
UNION
select * from a_table a  right outer join b_table b  on a.a_id = b.b_id;

7、索引

参考：https://www.bilibili.com/video/BV19y4y127h4?p=3&spm_id_from=pageDriver

素引的优点

大大加快数据查询速度

素引的缺点

维护素引需要耗费数据库资源
素引需要占用磁盘空间
当对表的数据进行增删改的时候,因为要维护索引,速度会受到影响

索引的分类

InnoDB

主键索引：设定为主键后数据库会自动連立素引，innodb为聚族索引，索引列值不能为空
单值（单例/普通）索引：一个索引只包含单个列，一个表可以有多个单列素引
唯一索引：索引列的值必须唯一，但允许有空值，允许为null但是只能有一个null
复合索引：一个索引包含多个列

MyISAM

Full Text（全文索引）：MySQL5.7版本之前，只能用于MyISAM引擎，MySQL8也支持全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、 VARCHAR、TEXT类型列上创建。

索引的基本操作

主键索引

主键索引是一种特殊的唯一索引，不允许值重复或者值为空。创建表时自动进行创建。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

普通索引

两种方式进行创建：

给name列创建索引

建表时创建（KEY关键字）（不可以指定索引的名称，名称默认为列的名称）
1
2
3
4
5
CREATE TABLE t_user (
id INTEGER PRIMARY KEY,
name VARCHAR(20),
KEY(name)
);
建表后创建（name_index索引名称，t_user为表名，name为哪个列创建索引）
1
CREATE INDEX name_index ON t_user(name);

唯一索引

建表时创建（UNIQUE关键字）（不可以指定索引的名称，名称默认为列的名称）
1
2
3
4
5
CREATE TABLE t_user (
id INTEGER PRIMARY KEY,
name VARCHAR(20),
UNIQUE(name)
);

建表后创建

1	CREATE UNIQUE INDEX name_index ON t_user(name);

复合索引

建表时创建

CREATE TABLE t_user (
	id INTEGER PRIMARY KEY,
	name VARCHAR(20),
    age INTEGER
    KEY(name,age)
);

建表后创建

1	CREATE INDEX name_index ON t_user(name,age);

左前缀原则：是指查询条件要和联合索引建立的字段顺序一致，才会使用索引，但是，MYSQL的查询优化器，会自动调整=和in的顺序，以便使用索引

索引index1:(a,b,c)有三个字段，我们在使用sql语句来查询的时候，会发现很多情况下不按照我们想象的来走索引。

会走索引

1
2
3

select * from table where a = '1'  
select * from table where a = '1' and b = ‘2’ 
select * from table where a = '1' and b = ‘2’  and c='3'

select * from table where a = '1' and c= ‘2’
-- a字段走索引，不会走c字段
where a = 1 and b > 1 and c = 1
-- 遇到范围查询之后，后面的索引就用不了，用到了a的索引和b的部分索引，无法使用c的索引
where b = 1 and c = 1 and a = 1 and d =1
-- mysql有优化器，走索引
where b > 2 and a = 1
-- 会，但是只有a = 1部分会用到索引

什么情况索引会失效

条件中有or，要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引。
复合索引未用左前缀字段;
like以%开头;
like ‘张三%’，实际你要找的是’张三XXX’，只要把所有’张三’开头的那部分内容返回即可，这部分是连续的，不需要全表扫描。
like ‘%张三’，实际你要找的是’XXX张三’，这部分在索引里是不连续的，如果要返回需要的结果，只能全表扫描。
需要类型转换;where中索引列有运算;
where中索引列使用了函数;
使用不等于（!= 、<>）
使用 is null 或者 is not null会导致无法使用索引

索引底层数据结构

B树（B-Tree）

每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为null。

20170920132504569

B加树（B+Tree）

只有叶子节点存储data，叶子节点包含了这棵树的所有键值，在B+树上增加了顺序访问指针，也就是每个叶子节点增加一个指向相邻叶子节点的指针

9e31f41f0d93fe3e79d962cb9998d73a2845e805.png@942w_339h_progressive

1、B+Tree非叶子节点不存储data，只存储key；
2、所有的关键字全部存储在叶子节点上；
3、每个叶子节点含有一个指向相邻叶子节点的指针，带顺序访问指针的B+树提高了区间查找能力；
4、非叶子节点可以看成索引部分，节点中仅含有其子树（根节点）中的最大（或最小）关键字；

为什么Mysql索引要用B+树不是B树？

用B+树不用B树考虑的是IO对性能的影响，B树的每个节点都存储数据，而B+树只有叶子节点才存储数据，所以查找相同数据量的情况下，B树的高度更高，IO更频繁。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。

mysql的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

8、InnoDB与MyISAM

聚簇索引：将数据存储与索引放到了一块，素引结构的叶子节点保存了行数据（聚簇索引不一定是主键索引，主键索引一定是聚簇索引 ），一个表只能有一个聚簇索引，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

参考：https://www.runoob.com/w3cnote/mysql-different-nnodb-myisam.html

区别	InnoDB	MyISAM
事务	支持	不支持
外键	支持	不支持
锁级别	支持行级锁	表级锁定
适合	适合频繁修改以及涉及到安全性较高的应用	适合查询为主的应用

MYISAM索引实现（非聚集）

所以MYISAM这个存储引擎他的查找的一个大致过程就是，先看条件字段有没有用到索引，是索引字段就先去到索引文件去查找这个索引所在的那一行的磁盘文件地址，就借助B+Tree的特点从根节点顺藤摸瓜找到磁盘文件地址指针，然后从MYD文件一次性定位到所找的数据，也就是说MYISAM会垮两个文件。

InnoDB索引实现（聚集）默认数据页大小为1 6kb

它也是一个B+Tree，但是它的叶子节点和MYISAM有点区别，它存储的是索引所在行的所有字段。

9、关系型与非关系型数据库区别

参考：https://blog.csdn.net/qq_33472765/article/details/81515251

非关系型数据库与关系型数据库各自的优势

非关系型数据库的优势：

性能NOSQL是基于键值对的，nosql的存储格式是key,value形式、文档形式、图片形式等等，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

关系型数据库的优势：

复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持使得对于安全性能很高的数据访问要求得以实现。对于这两类数据库，对方的优势就是自己的弱势，反之亦然。

关系型数据库的优势和劣势

关系型数据库把所有的数据都通过行和列的二元表现形式表示出来。

关系型数据库的优势：

保持数据的一致性（事务处理）
由于以标准化为前提，数据更新的开销很小（相同的字段基本上都只有一处）
可以进行Join等复杂查询

关系型数据库的不足：

大量数据的写入处理
为有数据更新的表做索引或表结构（schema）变更
字段不固定时应用
对简单查询需要快速返回结果的处理

10、Mysql order by与limit混用陷阱

参考：https://blog.csdn.net/qiubabin/article/details/70135556

在Mysql中我们常常用order by来进行排序，使用limit来进行分页，当需要先排序后分页时我们往往使用类似的写法select * from 表名 order by 排序字段 limt M,N。但是这种写法却隐藏着较深的使用陷阱。在排序字段有数据重复的情况下，会很容易出现排序结果与预期不一致的问题。

比如现在有一张user表，表结构及数据如下：

aHR0cDovL3VwbG9hZC1pbWFnZXMuamlhbnNodS5pby91cGxvYWRfaW1hZ2VzLzMyMDQ3LWVhN2VhMzYyMzc2Y2JjNzQucG5n

aHR0cDovL3VwbG9hZC1pbWFnZXMuamlhbnNodS5pby91cGxvYWRfaW1hZ2VzLzMyMDQ3LTYyZTgyYWU1NzFhMWNjNjUucG5n

现在想根据创建时间升序查询user表，并且分页查询，每页2条

1	select * from user order by create_time limit pageNo,2;

1、查询第一页数据时：

aHR0cDovL3VwbG9hZC1pbWFnZXMuamlhbnNodS5pby91cGxvYWRfaW1hZ2VzLzMyMDQ3LWY4NDY3MGNlNzVmMDcwMmQucG5n

2、查询第四页数据时：

aHR0cDovL3VwbG9hZC1pbWFnZXMuamlhbnNodS5pby91cGxvYWRfaW1hZ2VzLzMyMDQ3LTNlYzQ0YzE1NzA5YjVjNzAucG5n

user表共有8条数据，有4页数据，但是实际查询过程中第一页与第四页竟然出现了相同的数据。

如果order by的字段有多个行都有相同的值，mysql是会随机的顺序返回查询结果的，具体依赖对应的执行计划。也就是说如果排序的列是无序的，那么排序的结果行的顺序也是不确定的。

基于这个我们就基本知道为什么分页会不准了，因为我们排序的字段是create_time，正好又有几个相同的值的行，在实际执行时返回结果对应的行的顺序是不确定的。对应上面的情况，第一页返回的name为8的数据行，可能正好排在前面，而第四页查询时name为8的数据行正好排在后面，所以第四页又出现了。

如果想在Limit存在或不存在的情况下，都保证排序结果相同，可以额外加一个排序条件。例如id字段是唯一的，可以考虑在排序字段中额外加个id排序去确保顺序稳定。

所以上面的情况下可以在SQL再添加个排序字段，比如fund_flow的id字段，这样分页的问题就解决了。

1	SELECT * FROM user ORDER BY create_time,id LIMIT 6,2;

11、SELECT 语句的执行过程

客户端

客户端的作用是访问Mysql 的server层,相信大家第一次接触到Mysql, 然后…就啥都没了. 客户端就是用界面来与Mysql做交互的.
常用链接Mysql server层的方式有两种 :

图形化界面工具:
- NAVICAT 界面好看功能强大, 但是收费
- SQLYOG 刚开始学Mysql 的时候用的, 满足日常使用,收费!
- DBEAVER 挺棒的, 公司就是用的好这个, 因为免费
命令行 (Linux/ Windows 都有)

连接器

在 JDBC 中获取的 Connection 连接对象。连接器的作用是通过TCP握手获取登录信息后验证权限, 以及连接的管理。

缓存

你执行一条SQL(查询)，如果你没有手动禁用缓存的话，Mysql会先到缓存中查询之前是否执行过这条SQL，有则视为命中缓存并且返回数据，没有命中则交由，分析器处理。
需要注意的是增删改 都会刷新Mysql的缓存，所以经常修改的的数据，不建议使用缓存。比如城市交通的线路，站名，城市名，省名，这些长期不变的数据则建议使用缓存，提高效率。

MySQL 8.0 的版本中，已经不在支持查询缓存。

分析器

分析器的作用是对你执行的SQL的解析, 检查你的语法是否能正常被Mysql的执行器执行.
分析器又分为: 词法分析和语法分析

优化器

经历了分析器，那么 MySQL 就知道你要干啥了，那么怎么做？优化器完成了这一步，判断需要用哪个索引去查询、先执行哪个条件、或者决定 join 表的连接顺序等等，把前面的解析出来的换成执行的计划，进行最优评估。

执行器

执行器首先要做的是检查是否有权限访问这张表，然后再根据建表时的选用的储存引擎去调用该储存引擎的读写(IO)接口。

查询：有索引走索引, 无索引走全表 .命中一条就返回一条到结果集中. 返回的同时也会放到缓存中方便下一次使用。

12、Mysql四种存储引擎

InnoDB存储引擎：是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。

MyISAM存储引擎：MyISAM基于ISAM存储引擎，并对其进行扩展。它是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。MyISAM拥有较高的插入、查询速度，但不支持事物。

MEMORY存储引擎：MEMORY存储引擎将表中的数据存储到内存中，未查询和引用其他表数据提供快速访问。

如何选择存储引擎？

如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择
如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎。
MySQL中使用该引擎作为临时表，存放查询的中间结果如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive使用哪一种引擎需要灵活选择，一个数据库中多个表可以使用不同引擎以满足各种性能和实际需求。

为什么myisam查询比innodb快？

主要原因有三点：

查询的时候，由于innodb支持事务，所以会有mvvc的一个比较。这个过程会损耗性能。
查询的时候，如果走了索引，而索引又不是主键索引，此时，由于innodb是聚簇索引，会有一个回表的过程，即：先去非聚簇索引树（非主键索引树）中查询数据，找到数据对应的key之后，再通过key回表到聚簇索引树，最后找到需要的数据。而myisam是非聚集索引，而且叶子节点存储的是磁盘地址，所以，查询的时候查到的最后结果不是聚簇索引树的key，而是会直接去查询磁盘。
锁的一个损耗，innodb锁支持行锁，在检查锁的时候不仅检查表锁，还要看行锁。

13、SQL去重的三种方式

distinct、group by、row_number over()

distinct

select distinct name from userinfo;    # 现在需要当前用户表不重复的用户名
name 
xiaogang
xiaohei
xiaoli
xiaoming
select distinct name,id from userinfo; # 此时distinct同时作用了两个字段，即必须得id与name都相同的才会被排除
xiaogang 10
xiaoli 11
xiaohei 12
xiaogang 13
xiaoming 14

group by

1 2	select name from userinfo groub by name; # 与上面的一致 select name from userinfo groub by name, id; # 与上面的一致

row_number over()

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)；

1：Partition BY 用来分组

2：Order by 用来排序

SELECT * FROM (
select *,ROW_NUMBER() over(partition by name order by id desc) AS rn from userinfo ) AS u WHERE u.rn=1;

id  name   age height rn
13 xiaogang 26 172 1
12 xiaohei 22 152 1
11 xiaoli 31 176 1
14 xiaoming 31 176 1

distinct 和group by 的区别：

（1）distinct常用来查询不重复记录的条数：count(distinct name)，group by 常用它来返回不重记录的所有值。

（2）在使用group by 分组后，在select中可以选择分组字段，和非分组字段的函数值，如 max()、min()、sum、count()等。

distinct 和row_number over()区别:

（1）distinct 和 row_number over 都可以实现去重功能，而distinct 作用于当行的时候，其”去重” 是去掉表中字段所有重复的数据，作用于多行的时候是，其”去重”所有字段都相同的数据。

（2）在使用row_number over 子句时候是先分组，然后进行排序，再取出每组的第一条记录”去重”。

14、Expain

具体参数详解参考：https://blog.csdn.net/wokoone/article/details/114401175

explain执行后的信息显示有12列，id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra。

信息	描述
id	查询的序号，包含一组数字，表示查询中执行select子句或操作表的顺序两种情况 id相同，执行顺序从上往下 id不同，id值越大，优先级越高，越先执行
select_type	查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询 1、simple ——简单的select查询，查询中不包含子查询或者UNION 2、primary ——查询中若包含任何复杂的子部分，最外层查询被标记 3、subquery——在select或where列表中包含了子查询 4、derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 5、union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived 6、union result:UNION 的结果
table	输出结果集的表
partitions	匹配的分区
type	连接类型，显示查询使用了何种类型，按照从最佳到最坏类型排序 1、system：表中仅有一行（=系统表）这是const联结类型的一个特例。 2、const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量 3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描 4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体 5、range:只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描 6、index:index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。 7、all：遍历全表以找到匹配的行注意:一般保证查询至少达到range级别，最好能达到ref。
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。
key_len	索引字段的长度。表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref	列与索引的比较
rows	扫描出的行数(估算的行数)
filtered	按表条件过滤的行百分比
Extra	执行情况的描述和说明。包含不适合在其他列中显示，但是十分重要的额外信息 1、Using filesort：说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序” 2、Using temporary:使用了临时表保存中间结果，mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。 3、Using index:表示相应的select操作用使用覆盖索引，避免访问了表的数据行。如果同时出现using where，表名索引被用来执行索引键值的查找；如果没有同时出现using where，表名索引用来读取数据而非执行查询动作。 4、Using where :表明使用where过滤 5、using join buffer:使用了连接缓存 6、impossible where:where子句的值总是false，不能用来获取任何元组 7、select tables optimized away：在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。 8、distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

15、SQL执行的顺序

from
join
on
添加外部行
where
group by(开始使用select中的别名，后面的语句中都可以使用)
函数count、avg、sum….
having
select
distinct
order by
limit

16、mysql锁

全局锁、表级锁和行锁

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

MyISAM锁

MySQL表级锁有两种模式：表共享锁（Table Read Lock）和表独占写锁（Table Write Lock）。

对MyISAM的读操作，不会阻塞其他用户对同一表请求，但会阻塞对同一表的写请求；
对MyISAM的写操作，则会阻塞其他用户对同一表的读和写操作；

INNODB锁

行锁

适用：从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理

17、分库分表

17.1 什么时候分库？

分库主要解决的是并发量大的问题。因为并发量一旦上来了，那么数据库就可能会成为瓶颈，因为数据库的连接数是有限的，通过增加数据库实例的方式来提供更多的可用数据库链接，从而提升系统的并发度。

17.2 什么时候分表？

分表其实主要解决的是数据量大的问题

假如你的单表数据量非常大，因为并发不高，数据量连接可能还够，但是存储和查询的性能遇到了瓶颈了，你做了很多优化之后还是无法提升效率的时候，就需要考虑做分表了。通过将数据拆分到多张表中，来减少单表的数据量，从而提升查询速度。

【推荐】单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表。

17.3 什么时候既分库又分表？

横向拆分和纵向拆分

横向拆分：把一张表中的不同的记录分别放到不同的表中

横向拆分的结果是数据库表中的数据会分散到多张分表中，使得每一个单表中的数据的条数都有所下降。比如我们可以把不同的用户的订单分表拆分放到不同的表中。

纵向拆分：把这张表中某一条记录的多个字段，拆分到多张表中。不同的业务做拆分成多个数据库。

纵向拆分的结果是数据库表中的数据的字段数会变少，使得每一个单表中的数据的存储有所下降。比如我可以把商品详情信息、价格信息、库存信息等等分别拆分到不同的表中。

17.4 分表字段的选择

比如说我们要对交易订单进行分表的时候，我们可以选择的信息有很多，比如买家Id、卖家Id、订单号、时间、地区等等，具体应该如何选择呢？建议按照买家Id进行分表。因为这样可以避免一个关键的问题那就是——数据倾斜（热点数据）。

有一些表的数据量非常的大，但是有些表的数据量又很小，这就是发生了数据倾斜

注意⚠️：按照买家Id做分表，保证的是同一个买家的所有订单都在同一张表，并不是要给每个买家都单独分配一张表。

分表路由的时候，是可以设定一定的规则的，比如我们想要分1024张表，那么我们可以用买家ID或者买家ID的hashcode对1024取模，结果是0000-1023，那么就存储到对应的编号的分表中就行了。

17.5 分表算法

直接取模

数字类型：要分成128张表的话，就用一个整数来对128取模就行了，得到的结果如果是0002，那么就把数据放到order_0002这张表中。

Hash取模

那如果分表字段不是数字类型，而是字符串类型怎么办呢？有一个办法就是哈希取模，就是先对这个分表字段取Hash，然后在再取模。

⚠️：Java中的hash方法得到的结果有可能是负数，需要考虑这种负数的情况。

一致性Hash

那就是如果需要扩容二次分表，表的总数量发生变化时，就需要重新计算hash值，就需要涉及到数据迁移了。为了解决扩容的问题，我们可以采用一致性哈希的方式来做分表。

一致性哈希可以按照常用的hash算法来将对应的key哈希到一个具有2^32次方个节点的空间中，形成成一个顺时针首尾相接的闭合的环形。所以当添加一台新的数据库服务器时，只有增加服务器的位置和逆时针方向第一台服务器之间的键会受影响。

17.6 全局ID的生成

在单表中我们可以用数据库主键来做唯一ID，但是如果做了分库分表，多张单表中的自增主键就一定会发生冲突。那就不具备全局唯一性了。

UUID

不推荐：UUID太长了，其次字符串的查询效率也比较慢，而且没有业务含义，根本看不懂。

基于某个单表做自增主键

所有的表中的主键都从同一张表生成，所有的表在需要主键的时候，都到这张表中获取一个自增的ID。

不推荐：单点问题，一旦他挂了，那整个数据库就都无法写入了。

基于多个单表+步长做自增主键

实例1生成的ID从1000开始，到1999结束。

实例2生成的ID从2000开始，到2999结束。

实例3生成的ID从3000开始，到3999结束。

实例4生成的ID从4000开始，到4999结束。

如果第一个实例的ID已经用到1999了怎么办？那就生成一个新的起始值：

实例1生成的ID从5000开始，到5999结束。

实例2生成的ID从6000开始，到6999结束。

实例3生成的ID从7000开始，到7999结束。

实例4生成的ID从8000开始，到8999结束。

我们把步长设置为1000，确保每一个单表中的主键起始值都不一样，并且比当前的最大值相差1000就行了

雪花算法

推荐：常用的一种分布式ID的生成方式，它具有全局唯一、递增、高可用的特点。

雪花算法生成的主键主要由 4 部分组成，1bit符号位、41bit时间戳位、10bit工作进程位以及 12bit 序列号位。

符号位，1位是符号位，也就是最高位，始终是0，没有任何意义，因为要是唯一计算机二进制补码中就是负数，0才是正数。
时间戳占用41bit，精确到毫秒，总共可以容纳约69年的时间。
工作进程位占用10bit，其中高位5bit是数据中心ID，低位5bit是工作节点ID，做多可以容纳1024个节点。
序列号占用12bit，每个节点每毫秒0开始不断累加，最多可以累加到4095，一共可以产生4096个ID。

所以，一个雪花算法可以在同一毫秒内最多可以生成1024 X 4096 = 4194304个唯一的ID

17.7 分库分表的工具

分库分表的开源框架主要有三个，分别是sharding-jdbc、TDDL和Mycat

Sharding-JDBC

现在叫ShardingSphere（Sharding-JDBC、Sharding-Proxy和Sharding-Sidecar这3款相互独立的产品组成）。它定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架。

开原地址：https://shardingsphere.apache.org

TDDL

TDDL 是淘宝开源的一个用于访问数据库的中间件，它集成了分库分表，读写分离，权重调配，动态数据源配置等功能。封装 jdbc 的 DataSource给用户提供统一的基于客户端的使用。

开源地址：https://github.com/alibaba/tb_tddl

Mycat

Mycat是一款分布式关系型数据库中间件。它支持分布式SQL查询，兼容MySQL通信协议，以Java生态支持多种后端数据库，通过数据分片提高数据查询处理能力。

开源地址：https://github.com/MyCATApache/Mycat2

17.8 分库分表带来的问题

所有的读和写操作，都需要带着分表字段，这样才能知道具体去哪个库、哪张表中去查询数据。如果不带的话，就得支持全表扫描。
分页查询、排序等等操作就都失效了。因为我们不能跨多表进行分页、排序。
事务不支持带来的数据一致性问题
- 解决：分布式事务采用2PC协议，
  - 阶段一：为准备（prepare）阶段。即所有的参与者准备执行事务并锁住需要的资源。参与者ready时，向transaction manager报告已准备就绪。
  - 阶段二：为提交阶段（commit）。当transaction manager确认所有参与者都ready后，向所有参与者发送commit命令。

18、存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。

优点：

运行速度：对于很简单的sql，存储过程没有什么优势。对于复杂的业务逻辑，因为在存储过程创建的时候，数据库已经对其进行了一次解析和优化。存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用，所以执行速度会比普通sql快。
减少网络传输：存储过程直接就在数据库服务器上跑，所有的数据访问都在数据库服务器内部进行，不需要传输数据到其它服务器，所以会减少一定的网络传输。但是在存储过程中没有多次数据交互，那么实际上网络传输量和直接sql是一样的。而且我们的应用服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。
可维护性：的存储过程有些时候比程序更容易维护，这是因为可以实时更新DB端的存储过程。有些bug，直接改存储过程里的业务逻辑，就搞定了。
增强安全性：提高代码安全，防止 SQL注入。这一点sql语句也可以做到。
可扩展性：应用程序和数据库操作分开，独立进行，而不是相互在一起。方便以后的扩展和DBA维护优化。

缺点：

更改比较繁琐：如果需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新调用，等等，这时候估计会比较繁琐了。
可移植性差：由于存储过程将应用程序绑定到 SQL Server，因此使用存储过程封装业务逻辑将限制应用程序的可移植性。如果应用程序的可移植性在您的环境中非常重要，则需要将业务逻辑封装在不特定于 RDBMS 的中间层中。
开发调试复杂：由于IDE的问题，存储过程的开发调试要比一般程序困难。
没办法应用缓存：虽然有全局临时表之类的方法可以做缓存，但同样加重了数据库的负担。如果缓存并发严重，经常要加锁，那效率实在堪忧。
不支持群集，数据库服务器无法水平扩展，或者数据库的切割（水平或垂直切割）。数据库切割之后，存储过程并不清楚数据存储在哪个数据库中。