开发原理|MySQL最佳实践

MySQL ❎

OurSQL ✅

Debain安装MySQL

添加 MySQL 存储库

MySQL 服务器包可通过其官方存储库获得。因此，我们现在将下载并安装 MySQL 存储库安装包

1	wget https://dev.mysql.com/get/mysql-apt-config_0.8.18-1_all.deb

使用 dpkg

1	sudo dpkg -i mysql-apt-config_0.8.18-1_all.deb

进入mysql安装页面选择ok即可

安装mysql

等待存储库配置完成，然后使用 apt 命令更新存储库索引

1	sudo apt update

安装mysql服务器

使用apt-get install mysql-server安装时，如果出现报错Package ‘mysql-server’ has no installation candidate，可以使用下面的命令进行安装

1	sudo apt-get install mariadb-server

启动服务

理论上这时候的mysql服务已经安装完成了，我们可以通过命令service –status-all 查看到服务列表中有mariadb

但是使用mysql命令时，会出现以下报错，这是因为mariadb服务还未启动

使用以下命令启动即可

1	service mariadb start

命令行准备数据库

root账号进入数据库

mysql命令进入mysql服务

mysql

修改root账号密码

1	set password for root@localhost = password("123456")

退出mysql服务，并使用root账号登录

exit

1	mysql -u root -p

新用户与新数据库

创建新数据库

1	create database testdb;

查看已有数据库

1	show databases;

创建新用户

@localhost设置只允许用户本地登录，并设置其密码为123456

1	create user aut@localhost identifide by '123456';

查看用户列表

1	select user for mysql.user

分配新数据库给新用户

注意这里是指将testdb库的所有表（testdb.*）的所有权限（all）给aut本地使用（localhost）

1	grant all on testdb.* to aut@localhost;

查看用户权限

1	show grants for aut@localhost;

测试数据准备

使用新用户aut连接mysql

1	mysql -u aut -p

切换数据库到新数据库

1	use testdb;

创建一个只含一个字段的数据表

test表只含有一个整型不为空的字段money

1	create table test (money int not null)

添加一条数据到表中

1	insert test(money) values(5000)

MySQL事务隔离实验

新开一个终端使用连接上新数据库testdb（可以aut，也可以自己新建一个用户）

读未提交级别

出现脏读问题

读已提交级别

避免脏读，但是不可重复读

1.避免脏读测试

2.不可重复读测试

可重复读级别

可以重复读，并且可以防止幻读

1.可重复读测试

2.防止幻读测试

MySQL优化实验

上面的实验全程在Linux环境中，在日常开发中我们可以使用数据库可视化工具如Navicat或IDEA自带的数据库工具，下面我主要使用Navicat进行操作

此章节的优化实例来自一次对学校校团委物资管理系统的维护，鉴于此项目糟心的数据库设计，给我留下了深刻的印象，难以想象的是这样的项目竟然从16年运行至今

T-SQL脚本分表优化

1.相关表的结构

此处展示的表结构为维护前

物资申请表：共4817条数据

物资信息表：

goods_count：当前仓库物品数（物理的）

good_leftCount：当前物品可借数（网络的：存在部分未借出，但已被预订仍在审核中的物品）

2.优化思路：物资申请表分表

从上面的tw_apply表就可以知道：

在用户提出申请后，物资申请信息被后端拼成了一个字符串存储在apply_content（同时利用了前端的数据执行了物品可借数的预扣除，所以这部分没有用到物资申请信息字符串的解析）
通过审核后，物品正式借出，这时候只留有物资申请信息的字符串存储在数据库，所以需要后端对该字符串解析提取出申请物资与其借用数量，再去操作数据库

还好后端大哥没有把物资申请信息的字符串直接发给前端，我真的哭死，设计数据库的那个出来挨打（前端不需要解析，但是要拼接展示字符串）

数据库设计十分不合理，甚至不符合第一范式，浪费数据库大量存储空间不说，而且后端拼接字符串解析字符串这一过程十分耗时且占用内存，而且最新的需求是需要增加一个审核过程申请物资调整功能

所以我将物资申请表进行分表（水平分表），分出物资申请详情表并联系物资信息表，其结构如下

删除掉apply_content字段，节省数据库空间

分表后，通过tw_applydetail表，我们对物资申请信息的所以内容进行操作，省去了物资审核接口对字符串解析的耗时过程并且方便审核过程申请物资调整功能的开发（通过tw_appdetail找到物品信息和物品数量）

3.优化操作：存储过程脚本

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象

这里值得注意的是在遍历游标的循环中，如果查询不存在或为空会跳出循环

CREATE DEFINER=`Autovy`@`localhost` PROCEDURE `demo`()
BEGIN
		
 -- 定义变量
DECLARE i INT DEFAULT 0;
DECLARE s int DEFAULT 0;
DECLARE n TINYTEXT;
DECLARE m INT(11);
 # 求分割符号','的位置
DECLARE _index INT;

# 单个物品申请信息
DECLARE str TINYTEXT;

# 单个物品申请信息长度
DECLARE strLength int;

# 物品名称
DECLARE goodName VARCHAR(10);

# 物品数量
DECLARE goodCount int(11);

# 物品id
DECLARE goodId int(11);


-- 定义游标，并将sql结果集赋值给游标
DECLARE apply_id1 CURSOR FOR SELECT id, apply_content FROM tw_apply WHERE apply_content LIKE "本部%";

-- 声明当游标遍历完后将标志变量置成1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;

-- 打开游标
OPEN apply_id1;

-- 将游标中的值赋值给变量，注意：变量名不要和返回列名同名，变量顺序要和sql结果顺序一致
FETCH apply_id1 into m, n;

-- 当s != 1,一直循环
while s<>1 do


	SET _index = LOCATE(';', n);
	
	-- 通过;分割单个物品的申请信息：北院—帐篷物品1个;北院—椅子物品1个;北院—桌子物品1个;
	while _index > 0  do
		
		-- 拿到单个物品申请信息：本部——桌子物品1个
		SET str = LEFT(n, _index-1);
		
		SET strLength = LENGTH(str) / 5;
	
		-- 拿到物品名称
		SET goodName = LEFT(str, strLength);
		
		-- 拿到物品个数(类型转换为整数)
		SET goodCount = CAST(LEFT(RIGHT(str, 2), 1) AS signed) ;
		
		SELECT goodName;
		
		-- 按物品名称查到物品id并存储到goodId中
		-- 这里如果查询不存在或为空会跳出游标循环，值得注意
		SELECT goods_id into goodId FROM tw_goods WHERE goods_show LIKE goodName ORDER BY goods_count DESC LIMIT 1;
			
		-- 插入数据
		INSERT tw_applydetail(apply_id, good_id, count) VALUES (m, goodId, goodCount);
			
		-- 移动到下个分界点
		SET n = SUBSTR(n FROM _index+1);
		SET _index = LOCATE(';', n);
		
		end while;


	-- 执行业务逻辑
	set i = i+1;
	

-- 读取下一条数据，读取完成置变量s=1
FETCH apply_id1 into m, n;
end while;


-- 关闭游标
close apply_id1;
	

END

当时经过一天的对存储过程的学习，我总结出了以下经验：存储过程非常不方便调试，而且报错信息只定位不报错误类型（sql是这样的）。如果能重来，对数据库的批量操作，首选Python或Shell

4.优化结果

截至目前物资申请表已有4817条数据，考虑到后面数据会长期积累，这样的优化是有必要的
去掉后端耗时耗内存的字符串解析工作
节省数据库存储空间，优化前申请表内存占0.79MB，优化后占0.56MB

另外附加一个容量查询小工具，可查询数据库各表容量大小

select
table_schema as '数据库',
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
where table_schema='bgs'
order by data_length desc, index_length desc;

索引优化查询

1.相关表结构

日志记录表：共33687条数据

2.优化思路：添加索引

关于索引的知识点这里不细说，推荐阅读：MySQL 索引详解

由于日志表数据庞大，有3万条数据，为了达到快速通过用户名模糊查找到日志操作内容和操作时间，就需要用到索引，另外在模糊查询中，like语句要使索引生效，like后不能以%开始，也就是说（like %字段名%）、（like %字段名）这类语句会使索引失效，而（like 字段名）、（like 字段名%）这类语句索引是可以正常使用

所以我将查询的模糊匹配由“%xxxx%”改为“xxxx%”，只模糊匹配前面部分