导图社区 极客时间：MySQL必知必会
- 43
- 1
- 举报
极客时间：MySQL必知必会
极客时间课程的内容梳理、错误日志记录了 MySQL 服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示。
编辑于2023-02-01 18:47:01 浙江省- mysql
- 极客时间
- 相似推荐
- 大纲
MySQL必知必会
1.存储
1.数据库是mysql最大的载体
创建数据库
create database demo;
查看所有
show databases;
2.库中可以有多个表
创建表
CREATE TABLE demo.test ( barcode text, goodsname text, price int );
最好指明数据库
查看表结构
describe demo.test;
查看库中所有表
使用对应数据库
use demo;
展示表
show tables;
3.主键
一般来讲 主键 非空 唯一 不重复
可以自己添加一个冗余字段来保证不重复 不干扰
表中新增主键
ALTER TABLE mytest ADD COLUMN id int PRIMARY KEY AUTO_INCREMENT;
4.插入数据
INSERT INTO demo.test (barcode,goodsname,price) VALUES ('0001','本',3);
插入最好注明前面的字段 不容易出错 可读性好
5.sql语句汇总
-- 创建数据库 CREATE DATABASE demo； -- 删除数据库 DROP DATABASE demo； -- 查看数据库 SHOW DATABASES; -- 创建数据表： CREATE TABLE demo.test ( barcode text, goodsname text, price int ); -- 查看表结构 DESCRIBE demo.test; -- 查看所有表 SHOW TABLES; -- 添加主键 ALTER TABLE demo.test ADD COLUMN itemnumber int PRIMARY KEY AUTO_INCREMENT; -- 向表中添加数据 INSERT INTO demo.test (barcode,goodsname,price) VALUES ('0001','本',3);
注意：sql语句最后的分号不要漏掉
2.字段
1.整数类型

TINYINT多用于布尔类型 枚举类型
占1个字节
一般来讲INT用的最多
占4个字节
除非该数字有可能超过21亿 一般不用BIGINT
不要为了节省一点存储空间 而忽视数据溢出带来的风险 可靠性第一！
2.浮点数类型和定点数类型
浮点数
float 单精度浮点数
double 双精度浮点数 mysql默认使用
缺陷：浮点数不精准
二进制无法精确表达 所以只能四舍五入
定点数
decimal可以精确表达
把十进制的整数与小数拆分 用十六进制存储
decimal(5,2)
前面的5是长度 后面的2是保留几位小数
涉及小数 可以无脑用decimal
ps:涉及金额的 也可以以分为单位 用整型来存储
3.文本类型
一般都可无脑用text 65535字符 如果过长 则使用longtext
text类型无需提前定义长度 且按实际长度存储
varchar(M)需要定义长度 但也是按实际长度存储的
注意 这里的M指的是字符 所以不管是英文还是中文 都可容纳M个
但text类型不能作为主键
这时候可以选择varchar或char
4.日期与时间类型

尽量使用datetime类型 用的最多 最完整
5.总结
整数用int 小数用decimal 字符串用text/varchar 时间用datetime
3.表
1.如何创建数据表
建表语法
CREATE TABLE <表名> ( 字段名1 数据类型 [字段级别约束] [默认值]， 字段名2 数据类型 [字段级别约束] [默认值]， ...... [表级别约束] );
default 可以设置默认值 该字段如果不填即为默认值
2.约束
主键约束
一个表只能有一个 且字段值不能重复 不能为空 约束最强
ps:主键确实只有一个 但是可以有多个字段 被称之为联合主键
非空约束
必须有值 否则报错
唯一性约束
字段值不能重复 可以为空 可以有多个字段有唯一性约束
自增约束
只有整型字段才能有自增约束
新增字段的值为最大值+1
3.修改表
复制表
create <库名.新表名> like 库名.被复制的表名;
增加表字段
alter table <库名.表名> add <字段> <类型>
如 alter table test.user add id int;
在开头添加某字段
alter table test.user add id int first;
在某个字段的后面添加
如 alter table test.user add id int after name;
修改表字段
alter table <库名.表名> change <旧字段> <新字段> <类型>
如 alter table test,user change id uid bigint;
只改变字段类型
如 alter table test,user modify id int;
4.增删改查
1.添加数据
insert into 表名(字段1,字段2...) values('老王','小二',...);
添加数据前要看下是否有约束
对于主键自增 可以不加该字段
对于非空约束 不赋值会报错
对于唯一约束 注意不要重复了
批量添加可以先查询某个时间段的数据 批量插入
如 insert into test.user(name,age,school_id) select name,age,school_id from test.user;
2.删除数据
delete from demo.test where id>0;
习惯加where语句 防止误操作
3.修改数据
update <库名.表名> set 字段=值 where 字段=值
如 update test.user set name='bb' where id=2;
注意不要修改主键值
一般也不会修改除非你主键设置的有问题
4.查询数据
from
如果有嵌套的查询 后面可能会跟一个派生表 必须起别名

order by
跟在字段后面 asc升序 desc降序
limit
限制sql查询显示的条数
大数据量下必须要做limit限制了
5.主键
1.业务字段做主键
会员卡做主键
如果会员退卡 商家把卡发给另一个人 就可能导致查询出差错
手机号做主键
存在运营商回收手机号的情况
身份证号做主键
虽然是唯一的 但用户不大想填身份证
所以尽量不要用业务字段做主键 可能存在复用的情况
2.使用自增字段做主键
删除主键约束
ALTER TABLE demo.membermaster DROP PRIMARY KEY;
一般情况下只有一个数据库服务器是可以的
但存在多个门店用不同数据库 进行汇总 主键会存在冲突
3.手动赋值字段做主键
在总部表中维护一个会员最大id字段 每次新增会员就在最大id基础上+1 再进行插入
总结
单数据库系统 自增字段做主键就可以了
分布式系统 可以考虑全局生成唯一id 如雪花算法
6.外键和连接
1.外键
两表建立了关联关系 删除某项数据的时候 另一张表的某条数据也要相应删除才行 提升了系统的可靠性
2.连接
内连接
取两表的交集部分
左连接
取左表全部数据 右表部分数据
右连接
取右表全部数据 左边部分数据
左右取其一即可
3.总结
在低并发的数据库系统里 外键关系可以确保数据的可靠性
在高并发的场景 根据阿里规约 不应该使用外键 而应在业务场景确保逻辑的一致
不存在绝对的好坏 一切都要根据场景来决定到底用不用
大小写问题
MySQL在Linux下数据库名、表名、列名、别名大小写规则是这样的： 1、数据库名与表名是严格区分大小写的； 2、表的别名是严格区分大小写的； 3、列名与列的别名在所有的情况下均是忽略大小写的； 4、变量名也是严格区分大小写的； MySQL在Windows下都不区分大小写。
7.条件语句
WHERE
直接用表字段对数据集进行筛选 得到较少的数据集 再进行连接 资源占用少 执行效率高
HAVING
需要将数据准备好 并进行分组形成集合 再对该集合进行having条件的筛选
两者区别
1.where先筛选后连接 having先连接后筛选 where的效率更高
2.where可以直接用 而having必须和group by一起用才可以
3.可以在having中用函数 但where不行 所以having可以做一些更nb的查询
8.聚合函数
LEFT(str，n)
str表示字符串 n表示从左开始截取的范围
COUNT
count(*)表示该表的记录有多少条
count(字段)表示该字段不为空的条数有多少
9.时间函数
1.从日期中获取时间
参考
YEAR（date）：获取 date 中的年。 MONTH（date）：获取 date 中的月。 DAY（date）：获取 date 中的日。 HOUR（date）：获取 date 中的小时。 MINUTE（date）：获取 date 中的分。 SECOND（date）：获取 date 中的秒。
获取用户的创建小时
SELECT EXTRACT(HOUR FROM create_time) time , FROM user;
使用函数
SELECT HOUR(create_time) time ,`name` FROM user;
2.计算日期时间的函数

DATE_ADD
SELECT DATE_ADD('2022-2-10',interval 1 day) date;
输出2022-2-11
DATE_SUB
查找上个月到现在的用户创建记录
SELECT * from user where create_time>DATE_SUB(now(),interval 1 MONTH) ;
CURDATE()
获取当前的日期
如2022-2-10
DAYOFWEEK(date)
获取某天是周几
从周日开始为1
DATE_FORMAT()
SELECT DATE_FORMAT(now(),'%T');
24小时制格式化时间
SELECT DATE_FORMAT(now(),'%r');
12小时制格式化时间
DATEDIFF(date1,date2)
DATEDIFF("2021-02-01","2020-12-01");
返回相差几天
3.分布式系统时间差异解决办法
1.设置Windows 系统自带的网络同步
2.统一从总部MySQL服务器获取时间
10.计算

1.数学函数
向上取整 CEIL(X)
向下取整 FLOOR(X)
舍入函数 ROUND(X,D)
X表示处理的数 D表示保留的位数 可省略 即为0
2.字符串函数

字符串连接 CONCAT(s1,s2)
CAST(表达式 AS CHAR)
11.索引
单字段索引
create index 索引名 on table 表名(字段);
注意
创建主键约束/唯一约束 会自动创建主键索引 唯一性索引
经常被筛选的字段当索引
组合索引
create index 索引名 on table 表名(字段1,字段2);
删除索引
DROP INDEX 索引名 ON 表名;
如果是主键 不能直接删除 得先去掉主键
ALTER TABLE 表名 DROP PRIMARY KEY；
12.事务
1.什么是事务？
说白了 两件相关联的事情 要么都执行成功 要么都执行失败 若失败 得做相应的回滚操作
2.四个特性
原子性：表示事务中的操作要么全部执行，要么全部不执行，像一个整体，不能从中间打断。
一致性：表示数据的完整性不会因为事务的执行而受到破坏。
隔离性：表示多个事务同时执行的时候，不互相干扰。不同的隔离级别，相互独立的程度不同。
持久性：表示事务对数据的修改是永久有效的，不会因为系统故障而失效。
3.隔离等级
READ UNCOMMITTED：可读取事务中未提交的数据
a开启事务 更新了数据将钱变为1000 未提交事务 b去查询 发现钱多了1000 a后来发现转错人了 回滚事务 提交 b再去查询 钱没了 白高兴一场
READ COMMITTED：只能读取事务中已经提交更改的数据
a开启事务 查询余额为1000 去消费 此时未提交事务 此时b开启事务 将钱转走了 提交事务 a 付钱 发现没钱了
REPEATABLE READ：默认级别 开启事务可以避免其他人进行修改 但是无法避免新增操作
可以避免a开启事务后查询数据时 外界对该数据的修改操作 但是a开启事务 查询信用卡账单为1000元 b开启事务 去消费了一波 200元 提交事务 等a打印单子时 账单为1200元 以为出现了幻觉 也就是说在开启事务期间 发现数据多了
SERIALIZABLE：最高级别 开启事务可以限制任何人操作 直到事务结束
13.临时表
1.特性
CREATE TEMPORARY TABLE customer (id INT (10),`name` VARCHAR (20));
用到了 temporary关键字
创建完成 只有当前连接可见 其他连接不可见
当前连接结束 自动关闭
因为具有隔离性 适合并发程序的运行
2.内存临时表和磁盘临时表

内存临时表
速度更快
CREATE TEMPORARY TABLE customer (id int,name VARCHAR) ENGINE = MEMORY;
磁盘临时表
速度慢 默认使用InnoDB
不足
可能空间占用大 不适合大规模系统
18.权限
1.操作权限
创建角色
CREATE ROLE 角色名;
CREATE ROLE 'manager'@'localhost';
不写主机名 默认是通配符% 可以从任何主机登录
角色授权
GRANT 权限 ON 表名 TO 角色名;
GRANT SELECT,INSERT,DELETE,UPDATE ON demo.user TO 'manager';
查看角色权限
SHOW GRANTS FOR 'manager';
删除角色
DROP ROLE 角色名称;
2.操作用户
创建用户
CREATE USER 用户名 [IDENTIFIED BY 密码];
create user bb identify by 123;
给用户授权
直接授权
GRANT 角色名称 TO 用户名称;
通过角色授权
GRANT 权限 ON 表名 TO 用户名;
查看用户权限
SHOW GRANTS FOR 用户名;
删除用户
DROP USER 用户名;
3.注意点
mysql创建角色后 默认是没有激活的
需要激活
SET global activate_all_roles_on_login=ON;
mysql8开始才支持角色
19.日志(上)
1.通用查询日志
查看是否开启 默认是关闭的
mysql> SHOW VARIABLES LIKE '%general%'; +------------------+---------------+ | Variable_name | Value | +------------------+---------------+ | general_log | OFF | -- 通用查询日志处于关闭状态 | general_log_file | GJTECH-PC.log | -- 通用查询日志文件的名称是GJTECH-PC.log +------------------+---------------+ 2 rows in set, 1 warning (0.00 sec)
SHOW VARIABLES LIKE '%general%';
设置开启并指定位置
SET GLOBAL general_log = 'ON';
SET @@global.general_log_file = 'H:\mytest.log';
可以再次查看是否生效
删除通用查询日志
1.关闭通用查询日志
SET GLOBAL general_log = 'OFF';
2.将对应文件移出文件夹
3.再次开启通用查询日志
SET GLOBAL general_log = 'ON;
会再次生成对应的文件 可以每天用定时脚本重复上述操作 不知道有没有更好的办法。。
2.慢查询日志
配置文件开启
slow-query-log=1 -- 表示开启慢查询日志，系统将会对慢查询进行记录。 slow_query_log_file="GJTECH-PC-slow.log" -- 表示慢查询日志的名称是"GJTECH-PC-slow.log"。这里没有指定文件夹，默认就是数据目录："C:\ProgramData\MySQL\MySQL Server 8.0\Data"。 long_query_time=10 -- 表示慢查询的标准是查询执行时间超过10秒 slow-query-log=1 slow_query_log_file="GJTECH-PC-slow.log" long_query_time=10
重启服务器才会生效
slow-query-log=1 #表示开启慢查询 slow_query_log_file="GJTECH-PC-slow.log" #设置日志的位置 long_query_time=10 #设置超过10s的sql语句被记录
命令行开启
SET GLOBAL slow_query_log = 1;
set global long_query_time=10;
只有重新连接才会生效
3.错误日志
错误日志记录了 MySQL 服务器启动、停止运行的时间，以及系统启动、运行和停止过程中的诊断信息，包括错误、警告和提示
配置文件新增 log-error="GJTECH-PC.err" 文件名自己定
生效需要重启服务器
20.日志(下)
1.二进制日志
0.注意点：在mysql8前二进制日志是默认关闭的 8以后默认开启
1.查看二进制日志
SHOW MASTER STATUS;
查看所有二进制日志
SHOW BINARY LOGS;
查看二进制日志中所有数据更新事件
SHOW BINLOG EVENTS IN 二进制文件名;
2. 刷新二进制日志
FLUSH BINARY LOGS;
关闭正在写入的二进制日志文件 重开新文件 并在文件后缀上+1
3. 用二进制日志恢复数据
mysqlbinlog –start-positon=xxx --stop-position=yyy 二进制文件名 | mysql -u 用户 -p 从xxx位置开始 到yyy截止的数据更新操作 yyy可以省略
小tip：最好记录下开始位置 否则在数据量大的情况下索引会很难找
4. 删除二进制日志
RESET MASTER;
实战
1.先备份数据库
mysqldump -u root -p demo >mybackup.sql
2.刷新二进制日志
只保存之后的更新数据的日志
FLUSH BINARY LOGS;
3.插入新数据
假设这里宕机了 数据没了 重建数据库 进行数据恢复
4.从备份文件恢复数据
确定哪个日志文件是我们要使用的
SHOW BINARY LOGS;
最底下的就是最新的 即编号最大的 如bin003
再次刷新日志文件 生成新的日志文件 使得之前的bin003不受污染
FLUSH BINARY LOGS;
创建新的数据库
5.从之前备份的数据库恢复文件
mysql -u 用户 -p 密码 数据库名称 < 备份文件
注意这里只是恢复了原先的数据库 类似redis的rdb
6.从增量再次恢复
使用bin003 查看索引 begin开始的记录

mysqlbinlog --start-position=311 "/var/lib/mysql/binlog.000003" | mysql -u root -p
存放的日志默认在 /var/lib下
2.中继日志
只在主从架构的从服务器上存在
简单讲就是 从服务器读取对应二进制文件进行备份
踩坑
如果主服务器宕机 重装操作系统 导致服务器名变更 那么从机的备份恢复是根据之前的从服务器名来的 所以要将服务器名改成之前的即可
3.回滚日志
单个回滚日志最大为1g
SHOW VARIABLES LIKE '%innodb_max_undo_log_size%';
查看
innodb_undo_directory=.\ ，表示回滚日志的存储目录是数据目录，数据目录的位置可以通过查询变量“datadir”来查看。 innodb_undo_log_encrypt = OFF，表示回滚日志不加密。 innodb_undo_log_truncate = ON，表示回滚日志是否自动截断回收，这个变量有效的前提是设置了独立表空间。 innodb_undo_tablespaces = 2，表示回滚日志有自己的独立表空间，而不是在共享表空间 ibdata 文件中。
SHOW VARIABLES LIKE '%innodb_undo%';
4.重做日志
作用
1.在遇到故障恢复中 可以修复未完成的事务修改的数据
2.mysql为了提高效率 对数据的更新会先写入内存 积累一定程度的时候再写入磁盘 这就带来一个问题 如果中途宕机 内存里的数据丢失 可通过重做日志进行磁盘读写操作
查看
SHOW VARIABLES LIKE '%innodb_log_files_in_group%';
可以发现value是2
说明
前两个是用户表的创建和更新操作 如果为4说明用到了临时表
总结
二进制日志如果数据量大 起止位置不好掌控 到那个时候不如直接用主从结构
思考题
mysql> SHOW BINLOG EVENTS IN 'GJTECH-PC-bin.000013'; +----------------------+------+----------------+-----------+-------------+--------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +----------------------+------+----------------+-----------+-------------+--------------------------------------+ | GJTECH-PC-bin.000013 | 556 | Query | 1 | 627 | BEGIN | | GJTECH-PC-bin.000013 | 627 | Table_map | 1 | 696 | table_id: 114 (demo.goodsmaster) | | GJTECH-PC-bin.000013 | 696 | Delete_rows | 1 | 773 | table_id: 114 flags: STMT_END_F | | GJTECH-PC-bin.000013 | 773 | Xid | 1 | 804 | COMMIT /* xid=253 */ | | GJTECH-PC-bin.000013 | 804 | Anonymous_Gtid | 1 | 894 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' | | GJTECH-PC-bin.000013 | 894 | Query | 1 | 969 | BEGIN | | GJTECH-PC-bin.000013 | 969 | Table_map | 1 | 1038 | table_id: 114 (demo.goodsmaster) | | GJTECH-PC-bin.000013 | 1038 | Write_rows | 1 | 1094 | table_id: 114 flags: STMT_END_F | | GJTECH-PC-bin.000013 | 1094 | Xid | 1 | 1125 | COMMIT /* xid=259 */ |
进行了两个操作 删除和插入 起始位置为begin627 到1125
21.数据备份
1.三种模式的数据备份
备份数据库中的表
mysqldump -h 服务器 -u 用户 -p 数据库名称 表1,表2 > 备份文件名称
备份数据库
mysqldump -h 服务器 -u 用户 -p --databases 数据库名称 > 备份文件名
其实就是将里面的库表都创建了
备份整个数据库服务器
mysqldump -h 服务器 -u 用户 -p --all-databases > 备份文件名
一般来讲没有必要
2.数据恢复
使用mysql命令
mysql -u root -p demo < test.sql
如果备份的是数据库 那么就不需要加demo
使用source命令
SOURCE /home/demo.sql
后面跟的是linux下的备份文件地址
如果是恢复数据表 则先用 use demo;
3.数据导出导入
导出
select * into outfile '/home/user.txt' fields terminated by ',' lines terminated by '\n' from demo.user;
先查找linux下my.cnf文件位置 mysql --help | grep 'my.cnf'
配置文件里设置 secure-file-priv='/home'
再将home的权限设置为 chomd 777 否则权限会受阻
导入
load data infile '/home/user.txt' into table user fields terminated by ',' lines terminated by '\n';
注意要加绝对路径
load data是效率非常高 推荐使用！
22.范式
第一范式
所有的字段都是基本数据字段，不可进一步拆分
举例
 其中property还可再次拆分 
第二范式
原表

其中 listnumber和barcode可以作为主键
goodsname，specification，unit
只依赖于barcode
拆分为商品信息表

分析主键是否可能重复 发现可能 所以要新增主键
最终表

supplierid，suppliername，stock
只依赖于listnumber
拆分为进货单头表

剩下的组成新的表
进货单明细表

每一条数据都是唯一标识 且字段都是完全依赖于主键而不是只依赖主键的一部分
第三范式
不能存在依赖于非主键的字段
如supplierid和supplieridname存在关联 不满足
拆分为
供应商表

进货单头表

业务优先原则
该表还可以再分 保证没有冗余信息 但是真的有必要吗？
完全按理论来不一定就是最优的 还要根据实际情况而定
比如 供应商促销 买33件 100元
由此算单价 是除不尽的
导致importValue与实际价格有误差
思考题

拆分
第一范式满足
第二范式
可能有多个商品项 但交易编号应该只有一个
商品表
包含 商品项 名称 单价
销售流水表
id作为主键 流水单号 商品编号 数量 卡号
总价可以去掉
24.查询慢
1.查询分析语句
使用explain或desc来了解sql语句的执行计划
概念
id：是一个查询序列号。table：表示与查询结果相关的表的名称。 partition：表示查询访问的分区。key：表示优化器最终决定使用的索引是什么。 key_len：表示优化器选择的索引字段按字节计算的长度。如果没有使用索引，这个值就是空。 ref：表示哪个字段或者常量被用来与索引字段比对，以读取表中的记录。如果这个值是“func”，就表示用函数的值与索引字段进行比对。 rows：表示为了得到查询结果，必须扫描多少行记录。 filtered：表示查询筛选出的记录占全部表记录数的百分比。 possible_key：表示 MySQL 可以通过哪些索引找到查询的结果记录。如果这里的值是空，就说明没有合适的索引可用。你可以通过查看 WHERE 条件语句中使用的字段，来决定是否可以通过创建索引提高查询的效率。 Extra：表示 MySQL 执行查询中的附加信息。你可以点击这个链接查询详细信息。 type：表示表是如何连接的。至于具体的内容，你可以参考下查询分析语句输出内容说明。
看有没有建立索引 没有的字段但查询慢就要建立索引
2. 两种查询优化的方法
like 百分号如果在左边 则索引失效
使用了or 如果一边没用索引 则也会失效
25.表太大了
数据类型优化
对整型数据进行优化
一般采用int即可 不用想着可能超21亿
既可以用文本也可以用整型
则优先使用整型
案例
test 商品编号为int 流水号为text
test1 商品编号为mediumint 流水号为bigint
对比发现 int占4个字节 而mediumint占3个字节 提升25%
text占字节为实际字长+2 18位即20字节 而bigint为8字节 提升60%
合理增加冗余字段以提高效率
如果某字段要进行连接才能获取 且频繁查询 可以考虑放在单表中 冗余也没关系 效率第一
拆分表
将一个表拆分开来
查询频率高的放一起 而几乎没怎么查询的也单建一张表
使用非空约束
尽量使用非空约束
可以减少判断是否为空的开销 还能节省空间
总结
上述也不一定是必须的 还是要结合实际情况来 保证可用的前提下才能进行优化
26.如何充分利用系统资源
系统参数
InnoDB_flush_log_at_trx_commit
可在my.cnf中配置 默认值为1 即每次提交事务 都把数据写入日志 把日志写入磁盘
安全性最佳 但要进行频繁的磁盘写入操作
可选项
0
每隔一秒将数据写入日志 日志写入磁盘
效率最高 安全性最差
2
提交事务写入日志 但日志每隔一秒写入磁盘
2是安全性与效率都不错的
查询
SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
InnoDB_buffer_pool_size
使用缓存来存储索引和数据 值越大 所需要的磁盘读写就越少
查询
SHOW GLOBAL VARIABLES LIKE 'InnoDB_buffer_pool%';
InnoDB_buffer_pool_instances
多开辟几个缓存分区 增大并行处理的能力
查询和上面一样
如何利用系统资源来诊断问题？
查询
SELECT NAME,ENABLED,TIMED FROM performance_schema.setup_instruments LIMIT 1,10;
name
事件名称
enabled
是否启用对事件的监控
timed
是否收集事件的时间信息
setup_instruments 指定了是否对某一事件进行监控
setup_consumers 指定了是否保存监控事件发生的信息
利用监控信息诊断问题
performance_schema.events_statements_current
记录的是当前系统中的查询事件 一行对应一个进程
performance_schema.events_statements_history
记录了系统中所有进程中最近发生的查询事件
show variables like '%performance_schema_events_statements_history_size%'
查看每个进程保存的最大记录数
performance_schema.events_statements_history_long
记录了系统中所有进程中最近发生的查询事件
查询
sql语句
SELECT TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS duration, sql_text, EVENT_ID FROM performance_schema.events_statements_history_long WHERE TRUNCATE(TIMER_WAIT / 1000000000000, 6) <> 0 AND sql_text IS NOT NULL ORDER BY TRUNCATE(TIMER_WAIT / 1000000000000, 6) DESC LIMIT 1,2;
按照查询花费时间多少排序，查询出花费时间最多的 2 个查询的事件编号、执行时长和查询的内容。
27.设计完整超市数据库上
如何设计数据结构
结构图

第一层为商户 每个入驻的商户有唯一的组号
第二层为分支结构 相同商户的分支结构有相同组号
分支结构
这底下有相同的组号和机构编号
总部
门店
直营店
加盟店
配送中心
应用架构

盘点
只在下班后进行 实际数出来的数量将做盘存数量 电脑库存的数量叫做结存数量
比对结果为盈亏数量
盘存数量多叫盘盈
否则叫盘亏
ER模型

强弱实体转化为独立数据表
多对多转化为独立数据表
一对多转化为外键约束
库表
强实体
商户表（demo.enterprice）

商品信息表（demo.goodsmaster）

弱实体
门店表（demo.branch）

员工表（demo.employee）

仓库表（demo.stockmaster）

多对多
库存表（demo.inventory）

盘点表
盘点单头表（demo.invcounthead）

盘点单明细表（demo.invcountdetails）

如何进行分库分表？
垂直分表
将一个表 按照使用频率的不同拆分成两个或多个表
可以将用户使用频繁的商品信息表进行拆分
商品常用信息表

商品不常用信息表

垂直分库
根据业务模块的不同 每个模块一个数据库 可以很大减少数据库的压力

水平分表
对盘点单头表和盘点单明细表水平拆分
验收过的就成为了历史盘点表
盘点单头表历史表

新增验收人和验收日期
盘点单明细历史表

根据组号的总量 再进行拆分
可以是单数放一张表 双数放一张表
或者总共一千个组 前500个放一张表 后500个放另一张表
水平分库
跟上面类似 只不过这些表也被分别放入不同的数据库中了
重点关注
充分理解项目思路
客户有时候也不清楚 要帮其理清思路 用图表的形式整理出来 再与客户一起讨论 这一步投入的时间多 但也是值得的 因为后期要改动 成本会很大
使用ER模型来整理思路
充分考虑到系统投入运行之后的承载能力
如果要处理大量数据 就要考虑分库分表
28.设计完整超市数据库
如何创建数据库和数据表？
创建db
运营db
CREATE DATABASE operation;
库存db
CREATE DATABASE inventory;
创建表
运营表
商户表
CREATE TABLE operation.enterprice ( groupnumber SMALLINT PRIMARY KEY, groupname VARCHAR(100) NOT NULL, address TEXT NOT NULL, phone VARCHAR(20) NOT NULL, contactor VARCHAR(50) NOT NULL );
groupname是常用的建索引
CREATE INDEX index_enterprice_groupname ON operation.enterprice (groupname);
门店表
CREATE TABLE operation.branch ( branchid SMALLINT PRIMARY KEY, -- 门店编号 groupnumber SMALLINT NOT NULL, -- 组号 branchname VARCHAR(100) NOT NULL, -- 门店名称 address TEXT NOT NULL, -- 地址 phone VARCHAR(20) NOT NULL, -- 电话 branchtype VARCHAR(20) NOT NULL, -- 门店类别 CONSTRAINT fk_branch_enterprice FOREIGN KEY (groupnumber) REFERENCES operation.enterprice(groupnumber) -- 外键约束，组号是外键 );
索引
#groupnumber是其他表的主键 CREATE INDEX index_enterprice_groupname ON operation.enterprice (groupname); #branchname门店名称也要用 CREATE INDEX index_branch_branchname ON operation.branch (branchname); #branchtype门店类别也是 CREATE INDEX index_branch_branchtype ON operation.branch (branchtype);
员工表
CREATE TABLE operation.employee ( employeeid SMALLINT PRIMARY KEY, -- 员工编号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 workno VARCHAR(20) NOT NULL, -- 工号 employeename VARCHAR(100) NOT NULL, -- 员工名称 pid VARCHAR(20) NOT NULL, -- 身份证 address VARCHAR(100) NOT NULL, -- 地址 phone VARCHAR(20) NOT NULL, -- 电话 employeeduty VARCHAR(20) NOT NULL, -- 职责 CONSTRAINT fk_employee_branch FOREIGN KEY (branchid) REFERENCES operation.branch(branchid) );
索引
#组号创建索引 CREATE INDEX index_employee_groupnumer ON operation.employee (groupnumber); #门店编号 CREATE INDEX index_employee_branchid ON operation.employee (branchid); #身份证号 CREATE INDEX index_employee_pid ON operation.employee (pid); #手机号 CREATE INDEX index_employee_phone ON operation.employee (phone); #员工职责 CREATE INDEX index_employee_duty ON operation.employee (employeeduty);
商品常用信息表
CREATE TABLE operation.goods_o ( itemnumber MEDIUMINT NOT NULL, -- 商品编号 groupnumber SMALLINT NOT NULL, -- 组号 barcode VARCHAR(50) NOT NULL, -- 条码 goodsname TEXT NOT NULL, -- 名称 salesprice DECIMAL(10,2) NOT NULL, -- 售价 PRIMARY KEY (groupnumber,itemnumber)-- 主键 );
索引
#组号和售价是常用的 感觉商品名称也可以有。 CREATE INDEX index_goodso_groupnumber ON operation.goods_o (groupnumber); CREATE INDEX index_goodso_salesprice ON operation.goods_o (salesprice);
商品不常用信息表
CREATE TABLE inventory.invcountdetailshist ( id INT PRIMARY KEY AUTO_INCREMENT, -- 明细编号 listnumber INT NOT NULL, -- 单号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 stockid SMALLINT NOT NULL, -- 仓库编号 itemnumber MEDIUMINT NOT NULL, -- 商品编号 accquant DECIMAL(10,3) NOT NULL, -- 结存数量 invquant DECIMAL(10,3) NOT NULL, -- 盘存数量 plquant DECIMAL(10,3) NOT NULL -- 盈亏数量 );
索引
#组号即可 CREATE INDEX index_goodsf_groupnumber ON operation.goods_f (groupnumber);
库存表
仓库表
CREATE TABLE inventory.stockmaster ( stockid SMALLINT PRIMARY KEY, -- 仓库编号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 stockname VARCHAR(100) NOT NULL, -- 仓库名称 stockkind VARCHAR(20) NOT NULL, -- 仓库种类 CONSTRAINT fk_stock_branch FOREIGN KEY (branchid) REFERENCES operation.branch(branchid) -- 外键约束，门店编号是外键 );
库存表
CREATE TABLE inventory.inventory ( id INT PRIMARY KEY AUTO_INCREMENT, -- 库存编号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 stockid SMALLINT NOT NULL, -- 仓库编号 itemnumber MEDIUMINT NOT NULL, -- 商品编号 itemquantity DECIMAL(10,3) NOT NULL -- 商品数量 );
盘点单头表
CREATE TABLE inventory.invcounthead ( listnumber INT PRIMARY KEY, -- 单号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 stockid SMALLINT NOT NULL, -- 仓库编号 recorder SMALLINT NOT NULL, -- 录入人编号 recordingdate DATETIME NOT NULL -- 录入时间 );
盘点单明细表
CREATE TABLE inventory.invcountdetails ( id INT PRIMARY KEY AUTO_INCREMENT, -- 明细编号 listnumber INT NOT NULL, -- 单号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 stockid SMALLINT NOT NULL, -- 仓库编号 itemnumber MEDIUMINT NOT NULL, -- 商品编号 accquant DECIMAL(10,3) NOT NULL, -- 结存数量 invquant DECIMAL(10,3) NOT NULL, -- 盘存数量 plquant DECIMAL(10,3) NOT NULL -- 盈亏数量 );
盘点单头历史表
CREATE TABLE inventory.invcountheadhist ( listnumber INT PRIMARY KEY, -- 单号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 stockid SMALLINT NOT NULL, -- 仓库编号 recorder SMALLINT NOT NULL, -- 录入人编号 recordingdate DATETIME NOT NULL, -- 录入时间 confirmer SMALLINT NOT NULL, -- 验收人编号 confirmationdate DATETIME NOT NULL -- 验收时间 );
盘点单明细历史表
CREATE TABLE inventory.invcountdetailshist ( id INT PRIMARY KEY AUTO_INCREMENT, -- 明细编号 listnumber INT NOT NULL, -- 单号 groupnumber SMALLINT NOT NULL, -- 组号 branchid SMALLINT NOT NULL, -- 门店编号 stockid SMALLINT NOT NULL, -- 仓库编号 itemnumber MEDIUMINT NOT NULL, -- 商品编号 accquant DECIMAL(10,3) NOT NULL, -- 结存数量 invquant DECIMAL(10,3) NOT NULL, -- 盘存数量 plquant DECIMAL(10,3) NOT NULL -- 盈亏数量 );
后面就是做主从
但可能大厂的云机子也会宕机 做好备份吧 每天12点备份一次
总结
关键是建三范式 前期的工作非常重要 后面就是索引了 常用的查询用单值索引即可 因为复合索引很容易失效 数据量大要分库分表 这个没怎碰到 到时候在学吧 最后是主从和备份很关键
结语感悟
不要因为别人做什么就做什么 要走出一条自己的路 如果内心对此是喜悦的 那么就勇往直前
指定长期规划 形成稳定的内在结构
这就像一个良性循环 只要大方向 大势上努力 对一些确切的目标做好规划
如 学英语 计算机基础 慢慢用力即可
不计较一时的得失
如用云服务器 这是大势啊 未来的云计算也是 自己撘服务器的时代已经过去了 不能因为偶然的因素而放弃掉
如果错了 要果断放弃
我们总想得到很多东西 但往往只能得到部分 那么就必须作出取舍了