mysql

mysql

1.sql介绍

SQL(Structured Query Language)是结构化查询语言，是一种用来操作RDBMS的数据库的语言。也就是说通过 SQL 可以操作 oracle,sql server,mysql,sqlite 等关系型的数据库。

SQL的作用是实现数据库客户端和数据库服务端之间的通信，SQL就是通信的桥梁。

SQL语言主要分为：

• DQL：数据查询语言，用于对数据进行查询，如select
• DML：数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
• TPL：事务处理语言，对事务进行处理，包括begin transaction、commit、rollback
• DCL：数据控制语言，进行授权与权限回收，如grant、revoke
• DDL：数据定义语言，进行数据库、表的管理等，如create、drop

说明:

• 对于程序员来讲，重点是数据的增、删、改、查，必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作，其它操作如TPL、DCL了解即可.
• SQL语言不区分大小写

1. 数据类型

数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数据类型的原则是:够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间。

常用数据类型如下:

• 整数：int，bit
• 小数：decimal
• 字符串：varchar,char
• 日期时间: date, time, datetime
• 枚举类型(enum)

数据类型说明:

• decimal表示浮点数，如 decimal(5, 2) 表示共存5位数，小数占 2 位.
• char表示固定长度的字符串，如char(3)，如果填充’ab’时会补一个空格为’ab '，3表示字符数
• varchar表示可变长度的字符串，如varchar(3)，填充’ab’时就会存储’ab’，3表示字符数
• 对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径.
• 字符串 text 表示存储大文本，当字符大于 4000 时推荐使用, 比如技术博客.

2. 数据约束

约束是指数据在数据类型限定的基础上额外增加的要求.

常见的约束如下:

• 主键 primary key: 物理上存储的顺序. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
• 非空 not null: 此字段不允许填写空值.
• 惟一 unique: 此字段的值不允许重复.
• 默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
• 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.

3. 数据类型附录表

1. 整数类型

类型	字节大小	有符号范围(Signed)	无符号范围(Unsigned)
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT/INTEGER	4	-2147483648 ~2147483647	0 ~ 4294967295
BIGINT	8	-9223372036854775808 ~ 9223372036854775807	0 ~ 18446744073709551615

2. 字符串

类型	说明	使用场景
CHAR	固定长度，小型数据	身份证号、手机号、电话、密码
VARCHAR	可变长度，小型数据	姓名、地址、品牌、型号
TEXT	可变长度，字符个数大于 4000	存储小型文章或者新闻
LONGTEXT	可变长度， 极大型文本数据	存储极大型文本数据

3. 时间类型

类型	字节大小	示例
DATE	4	‘2020-01-01’
TIME	3	‘12:29:59’
DATETIME	8	‘2020-01-01 12:29:59’
YEAR	1	‘2017’
TIMESTAMP	4	‘1970-01-01 00:00:01’ UTC ~ ‘2038-01-01 00:00:01’ UTC

2.mysql命令

1数据库操作

1. 查看所有数据库

   show databases;

2. 创建数据库

   create database 数据库名 charset=utf8;
   例：
   create database python charset=utf8;

3. 使用数据库

   use 数据库名;

4. 查看当前使用的数据库

   select database();

5. 删除数据库-慎重

   drop database 数据库名;
   例：
   drop database python;

2. 表结构操作的SQL语句

1. 查看当前数据库中所有表

   1. show tables;

   2. 创建表

      create table students(
       id int unsigned primary key auto_increment not null,
       name varchar(20) not null,
       age tinyint unsigned default 0,
       height decimal(5,2),
       gender enum('男','女','人妖','保密')
      );

      说明:

      create table 表名(
      字段名称 数据类型  可选的约束条件,
      column1 datatype contrai,
      ...
      );

   3. 修改表-添加字段

      alter table 表名 add 列名 类型 约束;
      例：
      alter table students add birthday datetime;

   4. 修改表-修改字段类型

      alter table 表名 modify 列名 类型 约束;
      例：
      alter table students modify birthday date not null;

      说明:

      • modify: 只能修改字段类型或者约束，不能修改字段名

   5. 修改表-修改字段名和字段类型

      alter table 表名 change 原名 新名 类型及约束;
      例：
      alter table students change birthday birth datetime not null;

      说明:

      • change: 既能对字段重命名又能修改字段类型还能修改约束

   6. 修改表-删除字段

      alter table 表名 drop 列名;
      例：
      alter table students drop birthday;

   7. 查看创表SQL语句

      show create table 表名;
      例：
      show create table students;

   8. 查看创库SQL语句

      show create database 数据库名;
      例：
      show create database mytest;

   9. 删除表

      drop table 表名;
      例：
      drop table students;

​

3. 表数据操作的SQL语句

1. 查询数据

   -- 1. 查询所有列
   select * from 表名;
   例：
   select * from students;
   -- 2. 查询指定列
   select 列1,列2,... from 表名;
   例：
   select id,name from students;

2. 添加数据

   -- 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应
   insert into 表名 values (...)
   例:
   insert into students values(0, 'xx', default, default, '男');
   -- 2. 部分列插入：值的顺序与给出的列顺序对应
   insert into 表名 (列1,...) values(值1,...)
   例:
   insert into students(name, age) values('王二小', 15);
   -- 3. 全列多行插入
   insert into 表名 values(...),(...)...;
   例:
   insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');
   -- 4. 部分列多行插入
   insert into 表名(列1,...) values(值1,...),(值1,...)...;
   例：
   insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);

   说明:

   • 主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)
   • 在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值

3. 修改数据

   update 表名 set 列1=值1,列2=值2... where 条件
   例：
   update students set age = 18, gender = '女' where id = 6;

4. 删除数据

   delete from 表名 where 条件
   例：
   delete from students where id=5;

   问题:

   上面的操作称之为物理删除，一旦删除就不容易恢复，我们可以使用逻辑删除的方式来解决这个问题。

   -- 添加删除表示字段，0表示未删除 1表示删除
   alter table students add isdelete bit default 0;
   -- 逻辑删除数据
   update students set isdelete = 1 where id = 8;

   说明:

   • 逻辑删除，本质就是修改操作

4. as关键字

在使用SQL语句显示结果的时候，往往在屏幕显示的字段名并不具备良好的可读性，此时可以使用 as 给字段起一个别名。

1. 使用 as 给字段起别名

   select id as 序号, name as 名字, gender as 性别 from students;

2. 可以通过 as 给表起别名

   -- 如果是单表查询 可以省略表名
   select id, name, gender from students;
   
   -- 表名.字段名
   select students.id,students.name,students.gender from students;
   
   -- 可以通过 as 给表起别名 
   select s.id,s.name,s.gender from students as s;

   说明:

   • 在这里给表起别名看起来并没有什么意义,然而并不是这样的，我们在后期学习 自连接 的时候，必须要对表起别名。

5. distinct关键字

distinct可以去除重复数据行。

select distinct 列1,... from 表名;

例： 查询班级中学生的性别
select name, gender from students;

-- 看到了很多重复数据 想要对其中重复数据行进行去重操作可以使用 distinct
select distinct name, gender from students;

6. where条件查询的介绍

1. 比较运算符查询

1. 等于: =
2. 大于: >
3. 大于等于: >=
4. 小于: <
5. 小于等于: <=
6. 不等于: != 或 <>

例1：查询编号大于3的学生:

select * from students where id > 3;

例2：查询编号不大于4的学生:

select * from students where id <= 4;

例3：查询姓名不是“黄蓉”的学生:

select * from students where name != '黄蓉';

例4：查询没被删除的学生:

select * from students where is_delete=0;

2. 逻辑运算符查询

1. and
2. or
3. not

例1：查询编号大于3的女同学:

select * from students where id > 3 and gender=0;

例2：查询编号小于4或没被删除的学生:

select * from students where id < 4 or is_delete=0;

例3：查询年龄不在10岁到15岁之间的学生:

select * from students where not (age >= 10 and age <= 15);

说明:

• 多个条件判断想要作为一个整体，可以结合‘()’。

3. 模糊查询

1. like是模糊查询关键字
2. %表示任意多个任意字符
3. _表示一个任意字符

例1：查询姓黄的学生:

select * from students where name like '黄%';

例2：查询姓黄并且“名”是一个字的学生:

select * from students where name like '黄_';

例3：查询姓黄或叫靖的学生:

select * from students where name like '黄%' or name like '%靖';

4. 范围查询

1. between … and … 表示在一个连续的范围内查询
2. in 表示在一个非连续的范围内查询

例1：查询编号为3至8的学生:

select * from students where id between 3 and 8;

例2：查询编号不是3至8的男生:

select * from students where (not id between 3 and 8) and gender='男';

5. 空判断查询

1. 判断为空使用: is null
2. 判断非空使用: is not null

例1：查询没有填写身高的学生:

select * from students where height is null;

注意:

1. 不能使用 where height = null 判断为空
2. 不能使用 where height != null 判断非空
3. null 不等于 ‘’ 空字符串

7. 排序查询语法

排序查询语法：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

语法说明:

1. 先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
2. asc从小到大排列，即升序
3. desc从大到小排序，即降序
4. 默认按照列值从小到大排序（即asc关键字）

例1：查询未删除男生信息，按学号降序:

select * from students where gender=1 and is_delete=0 order by id desc;

例2：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序:

select * from students  order by age desc,height desc;

8. 分页查询的语法

select * from 表名 limit start,count

说明:

1. limit是分页查询关键字
2. start表示开始行索引，默认是0
3. count表示查询条数

例1：查询前3行男生信息:

select * from students where gender=1 limit 0,3;
简写
select * from students where gender=1 limit 3;

3. 分页查询案例

已知每页显示m条数据，求第n页显示的数据

提示: 关键是求每页的开始行索引

查询学生表，获取第n页数据的SQL语句:

select * from students limit (n-1)*m,m

9. 聚合函数的介绍

聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。

常用的聚合函数:

1. count(col): 表示求指定列的总行数
2. max(col): 表示求指定列的最大值
3. min(col): 表示求指定列的最小值
4. sum(col): 表示求指定列的和
5. avg(col): 表示求指定列的平均值

2. 求总行数

-- 返回非NULL数据的总行数.
select count(height) from students; 
-- 返回总行数，包含null值记录;
select count(*) from students;

3. 求最大值

-- 查询女生的编号最大值
select max(id) from students where gender = 2;

4. 求最小值

-- 查询未删除的学生最小编号
select min(id) from students where is_delete = 0;

5. 求和

-- 查询男生的总身高
select sum(height) from students where gender = 1;
-- 平均身高
select sum(height) / count(*) from students where gender = 1;

6. 求平均值

-- 求男生的平均身高, 聚合函数不统计null值，平均身高有误
select avg(height) from students where gender = 1;
-- 求男生的平均身高, 包含身高是null的
select avg(ifnull(height,0)) from students where gender = 1;

说明

• ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。

10.分组查询介绍

分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。

分组查询基本的语法格式如下：

GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]

说明:

• 列名: 是指按照指定字段的值进行分组。
• HAVING 条件表达式: 用来过滤分组后的数据。
• WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果

2. group by的使用

group by可用于单个字段分组，也可用于多个字段分组

-- 根据gender字段来分组
select gender from students group by gender;
-- 根据name和gender字段进行分组
select name, gender from students group by name, gender;

3. group by + group_concat()的使用

group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割

-- 根据gender字段进行分组， 查询gender字段和分组的name字段信息
select gender,group_concat(name) from students group by gender;

4. group by + 聚合函数的使用

-- 统计不同性别的人的平均年龄
select gender,avg(age) from students group by gender;
-- 统计不同性别的人的个数
select gender,count(*) from students group by gender;

5. group by + having的使用

having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by

-- 根据gender字段进行分组，统计分组条数大于2的
select gender,count(*) from students group by gender having count(*)>2;

6. group by + with rollup的使用

with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果

-- 根据gender字段进行分组，汇总总人数
select gender,count(*) from students group by gender with rollup;
-- 根据gender字段进行分组，汇总所有人的年龄
select gender,group_concat(age) from students group by gender with rollup;

11. 连接查询的介绍

连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。

连接查询可以分为:

1. 内连接查询
2. 左连接查询
3. 右连接查询
4. 自连接查询

1. 内连接查询

查询两个表中符合条件的共有记录

内连接查询效果图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9RYgtyMb-1602861903681)(imgs/内连接查询.png)]

内连接查询语法格式:

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

说明:

• inner join 就是内连接查询关键字
• on 就是连接查询条件

例1：使用内连接查询学生表与班级表:

select * from students as s inner join classes as c on s.cls_id = c.id;

2. 左连接查询

以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充

左连接查询效果图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dDeoJzm7-1602861903684)(imgs/左连接查询.png)]

左连接查询语法格式:

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2

说明:

• left join 就是左连接查询关键字
• on 就是连接查询条件
• 表1 是左表
• 表2 是右表

例1：使用左连接查询学生表与班级表:

select * from students as s left join classes as c on s.cls_id = c.id;

3. 右连接查询

以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充

右连接查询效果图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5NLMSHrH-1602861903685)(imgs/右连接查询.png)]

右连接查询语法格式:

select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

说明:

• right join 就是右连接查询关键字
• on 就是连接查询条件
• 表1 是左表
• 表2 是右表

例1：使用右连接查询学生表与班级表:

select * from students as s right join classes as c on s.cls_id = c.id;

4. 自连接查询

左表和右表是同一个表，根据连接查询条件查询两个表中的数据

自连接查询的用法:

select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '山西省';

12. 子查询的介绍

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.

主查询和子查询的关系:

1. 子查询是嵌入到主查询中
2. 子查询是辅助主查询的,要么充当条件,要么充当数据源
3. 子查询是可以独立存在的语句,是一条完整的 select 语句

2. 子查询的使用

例1. 查询大于平均年龄的学生:

select * from students where age > (select avg(age) from students);

例2. 查询学生在班的所有班级名字:

select name from classes where id in (select cls_id from students where cls_id is not null);

例3. 查找年龄最大,身高最高的学生:

select * from students where (age, height) =  (select max(age), max(height) from students);

13数据库设计之三范式

1. 数据库设计之三范式的介绍

范式: 对设计数据库提出的一些规范，目前有迹可寻的共有8种范式，一般遵守3范式即可。

• 第一范式（1NF）: 强调的是列的原子性，即列不能够再分成其他几列。
• 第二范式（2NF）: 满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段 必须完全依赖于主键，而不能只依赖于主键的一部分。
• 第三范式（3NF）: 满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。

14.事务

1. 事务的四大特性

• 原子性(Atomicity)
• 一致性(Consistency)
• 隔离性(Isolation)
• 持久性(Durability)

原子性:

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

一致性:

数据库总是从一个一致性的状态转换到另一个一致性的状态。（在前面的例子中，一致性确保了，即使在转账过程中系统崩溃，支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

隔离性:

通常来说，一个事务所做的修改操作在提交事务之前，对于其他事务来说是不可见的。（在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，则其看到支票帐户的余额并没有被减去200美元。）

持久性:

一旦事务提交，则其所做的修改会永久保存到数据库。

说明:

事务能够保证数据的完整性和一致性，让用户的操作更加安全。

2. 事务的使用

在使用事务之前，先要确保表的存储引擎是 InnoDB 类型, 只有这个类型才可以使用事务，MySQL数据库中表的存储引擎默认是 InnoDB 类型。

说明:

• 常用的表的存储引擎是 InnoDB 和 MyISAM
• InnoDB 是支持事务的
• MyISAM 不支持事务，优势是访问速度快，对事务没有要求或者以select、insert为主的都可以使用该存储引擎来创建表

开启事务:

begin;
或者
start transaction;

提交事务:

将本地缓存文件中的数据提交到物理表中，完成数据的更新。

commit;

回滚事务:

放弃本地缓存文件中的缓存数据, 表示回到开始事务前的状态

rollback;

14. 索引的介绍

索引在MySQL中也叫做“键”，它是一个特殊的文件，它保存着数据表里所有记录的位置信息，更通俗的来说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

应用场景:

当数据库中数据量很大时，查找数据会变得很慢，我们就可以通过索引来提高数据库的查询效率。

2. 索引的使用

查看表中已有索引:

show index from 表名;

说明:

• 主键列会自动创建索引

索引的创建:

-- 创建索引的语法格式
-- alter table 表名 add index 索引名[可选](列名, ..)
-- 给name字段添加索引
alter table classes add index my_name (name);

MySQL中索引的优点和缺点和使用原则

• 优点：
  1. 加快数据的查询速度
• 缺点：
  1. 创建索引会耗费时间和占用磁盘空间，并且随着数据量的增加所耗费的时间也会增加
• 使用原则：
  1. 通过优缺点对比，不是索引越多越好，而是需要自己合理的使用。
  2. 对经常更新的表就避免对其进行过多索引的创建，对经常用于查询的字段应该创建索引，
  3. 数据量小的表最好不要使用索引，因为由于数据较少，可能查询全部数据花费的时间比遍历索引的时间还要短，索引就可能不会产生优化效果。
  4. 在一字段上相同值比较多不要建立索引，比如在学生表的"性别"字段上只有男，女两个不同值。相反的，在一个字段上不同值较多可是建立索引。

15.复制介绍

1. 作用

• 对数据备份, 实现高可用 HA

• 通过读写分离, 提高吞吐量, 实现高性能

• Mysql的复制 是一个异步的复制过程

• 过程本质为 Slave 从 Master 端获取 Binary Log, 然后再在自己身上完全顺序的执行日志中所记录的各种操作

• MySQL 复制的基本过程如下:

  • Slave 上面的 IO 线程连接上 Master， 并请求从指定日志文件的指定位置之后的日志内容;
  • Master 接收到来自 Slave 的 IO 线程的请求后， 通过负责复制的IO线程 根据请求信息读取日志信息，返回给 Slave 端的 IO 线程。
  • Slave 的 IO 线程接收到信息后，将接收到的日志内容依次写入到 Slave 端的 Relay Log文件
  • Slave 的 SQL 线程检测到 Relay Log 中新增加了内容后，会马上解析该文件中的内容, 并在自身执行这些 原始SQL语句。

3. 常用架构

主从架构

• 性能
  • 一主多从, 读写分离, 提高吞吐量
• 可用性
  • 主库单点, 一旦挂了, 无法写入
  • 从库高可用

主备架构

• 性能
  • 单库读写, 性能一般
• 可用性
  • 高可用, 一旦主库挂了, 就启用备库
• 这种方案被阿里云、美团等企业广泛使用

高可用复合架构

• 性能
  • 读写分离, 提高吞吐量
• 可用性
  • 高可用, 一旦主库挂了, 就启用备库

16.分片介绍

需求分析

• 用户请求量太大, 会导致web应用无法及时响应 -> 分布式服务器(分散请求到多个服务器上)
• 单表太大, 会导致CRUD都成问题, 索引膨胀, 查询超时 -> 拆分表
• 单库太大, 会导致单库磁盘空间不足; 处理能力有限; 出现IO瓶颈 -> 拆分库

作用

• 分片也称为数据拆分 (Shareding), 其主要工作就是对单库单表进行拆分, 多库多表共同组成完整的数据集合
• 分片可以提高吞吐量, 同一时间数据的读写完成量更多, 扩充单机存储的容量/读写速度上限

分类

• 分片主要分为两种:
  • 垂直拆分
  • 水平拆分

注意点

• 不要轻易分库分表，因为分片会带来 诸多分布式问题, 让应用的复杂度大量增加
• 应避免"过度设计"和"过早优化", 先尽力去做其他优化，例如：升级硬件、升级网络、读写分离、索引优化、缓存设计等等。
• 当数据量达到单表瓶颈时候(参考值: 单表记录1000W+/硬盘100G+)，再考虑分库分表
• 如果需要进行分库分表, 优先考虑垂直拆分

垂直分表

• 按 字段 将一张表拆分成多张表
• 对于字段较多的表, 每条记录占用的空间也会较多, 导致每次从硬盘中读取的记录以及查询缓存可缓存的记录数量较少, 影响查询查询效率
• 针对字段多的表就可以采用垂直分表来进行拆分, 这样可以减少表体积, 提高查询效率

拆分规则

• 相关性
  • 可以将字段根据 业务逻辑 和 使用的相关性 进行分表划分
  • 如: 用户名和密码经常配合使用, 将其分到用户认证表, 生日和邮箱等个人信息经常一起访问, 将其分到用户信息表
• 使用频率
  • 可以将字段根据 常用 和 不常用 进行划分, 并进行分表处理
  • 如: 原始用户表中包含了多个字段, 其中有常用的昵称、手机号等字段, 也包含不常用的邮箱、生日等字段, 可以根据使用频率将其分为两张表: 用户基础信息表 和 用户其他信息表

垂直分库

• 将一个数据库中的多张表拆分到多个数据库(服务器节点)中
• 注意点:
  • 由于 本地事务不支持跨库操作, 所以应该将 有相关联性的表放在同一个库中
  • 如: 如果后续头条项目垂直分库, 将用户相关的放在数据库1, 文章相关的放在数据库2

17.水平拆分

1. 介绍

• 水平拆分分为两种:
  • 水平分表
  • 水平分库

水平分表

• 将 一张表的记录 拆分到多张表中
• 对于记录较多的表, 会出现索引膨胀, 查询超时等问题, 影响用户体验

水平分库

• 水平分表后, 将分表分散放在多个数据库节点中

拆分规则

• 时间
  • 按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。
• 业务
  • 按照业务将数据进行分类并拆分, 如文章包含金融、科技等多个分类, 可以每个分类的数据拆分到一张表中。
• ID范围
  • 从 0 到 100W 一个表，100W+1 到 200W 一个表。
• HASH取模 离散化
  • 取用户id，然后hash取模，分配到不同的数据库上。这样可以同时向多个表中插入数据, 提高并发能力, 同时由于用户id进行了离散处理, 不会出现ID冲突的问题
• 地理区域
  • 比如按照华东，华南，华北这样来区分业务，部分云服务应该就是如此。

记录数量较少, 影响查询查询效率

• 针对字段多的表就可以采用垂直分表来进行拆分, 这样可以减少表体积, 提高查询效率

拆分规则

• 相关性
  • 可以将字段根据 业务逻辑 和 使用的相关性 进行分表划分
  • 如: 用户名和密码经常配合使用, 将其分到用户认证表, 生日和邮箱等个人信息经常一起访问, 将其分到用户信息表
• 使用频率
  • 可以将字段根据 常用 和 不常用 进行划分, 并进行分表处理
  • 如: 原始用户表中包含了多个字段, 其中有常用的昵称、手机号等字段, 也包含不常用的邮箱、生日等字段, 可以根据使用频率将其分为两张表: 用户基础信息表 和 用户其他信息表

垂直分库

• 将一个数据库中的多张表拆分到多个数据库(服务器节点)中
• 注意点:
  • 由于 本地事务不支持跨库操作, 所以应该将 有相关联性的表放在同一个库中
  • 如: 如果后续头条项目垂直分库, 将用户相关的放在数据库1, 文章相关的放在数据库2

17.水平拆分

1. 介绍

• 水平拆分分为两种:
  • 水平分表
  • 水平分库

水平分表

• 将 一张表的记录 拆分到多张表中
• 对于记录较多的表, 会出现索引膨胀, 查询超时等问题, 影响用户体验

水平分库

• 水平分表后, 将分表分散放在多个数据库节点中

拆分规则

• 时间
  • 按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起，这个也是“冷热数据分离”。
• 业务
  • 按照业务将数据进行分类并拆分, 如文章包含金融、科技等多个分类, 可以每个分类的数据拆分到一张表中。
• ID范围
  • 从 0 到 100W 一个表，100W+1 到 200W 一个表。
• HASH取模 离散化
  • 取用户id，然后hash取模，分配到不同的数据库上。这样可以同时向多个表中插入数据, 提高并发能力, 同时由于用户id进行了离散处理, 不会出现ID冲突的问题
• 地理区域
  • 比如按照华东，华南，华北这样来区分业务，部分云服务应该就是如此。