mysql 优化

字数统计: 2.7k阅读时长: 9 min

 2019/07/31   Share

Mysql 优化

表的设计合理化(符合3NF)
添加适当索引(index) [四种: 普通索引、主键索引、唯一索引unique、全文索引]
SQL语句优化
分表技术(水平分割、垂直分割)
读写[写: update/delete/add]分离
存储过程 [模块化编程，可以提高速度]
对mysql配置优化 [配置最大并发数my.ini, 调整缓存大小 ]
mysql服务器硬件升级
定时的去清除不需要的数据,定时进行碎片整理(MyISAM)

数据库三大范式

1NF

1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解；(只要是关系型数据库都满足1NF)

1NF是关系模式应具备的最起码的条件，如果数据库设计不能满足第一范式，就不称为关系型数据库。关系数据库设计研究的关系规范化是在1NF之上进行的

如果每列(或者每个属性)都是不可再分的最小数据单元(也称为最小的原子单元),则满足第一范式

表：字段1、字段2(字段2.1、字段2.2)、字段3 ……

例如:顾客表(姓名、编号、地址、……)其中”地址”列还可以细分为国家、省、市、区等。

学生（学号，姓名，性别，出生年月日），如果认为最后一列还可以再分成（出生年，出生月，出生日），它就不是一范式了，否则就是；

比如

id	name	address
1	zhangsan	上海市浦东新区，10001,15911111111
2	lisi	上海市xxx区，10001,15921111111
3	laowu	上海市yyyy区，10001,15721111111

就不符合1NF规范,后面address 字段还可以再拆分，在大数据时代通常会使用json格式的数据来储存

id	name	address	zip Code	phone
1	zhangsan	上海市浦东新区	10001	15911111111
2	lisi	上海市xxx区	10001	15921111111
3	laowu	上海市yyyy区	10001	15721111111

这个就符合1NF规范了，不过地址还可以再拆分，要看业务来看，是否可以属于原子。

2NF

2NF是对记录的惟一性约束，表中的记录是唯一的, 就满足2NF, 通常我们设计一个主键来实现，主键不能包含业务逻辑。

第二范式要满足以下的条件：首先要满足第一范式，其次每个非主属性要完全函数依赖与候选键，或者是主键。也就是说，每个非主属性是由整个主键函数决定的，而不能由主键的一部分来决定

举个例子

（学生选课表）

学生	课程	教师	教师职称	教材	教室	上课时间
李四	SpringBoot	王老师	Java讲师	《SpringBoot深入浅出》	301	8:00
张三	Mysql	李老师	Mysql 讲师	《Mysql 从删库到跑路》	302	9:22

这里通过（学生，课程）可以确定教师、教师职称，教材，教室和上课时间，所以可以把（学生，课程）作为主键。

但是，教材并不完全依赖于（学生，课程），只拿出课程就可以确定教材，因为一个课程，一定指定了某个教材。这就叫不完全依赖，或者部分依赖。出现这种情况，就不满足第二范式。

修改后

（选课表）

学生	课程	教师	教师职称	教室	上课时间
李四	SpringBoot	王老师	Java讲师	301	8:00
张三	Mysql	李老师	Mysql 讲师	302	9:22

(教材表)

课程	教材
SpringBoot	《SpringBoot深入浅出》
Mysql	《Mysql 从删库到跑路》

所以，第二范式可以说是消除部分依赖，可以减少插入异常，删除异常和修改异常。

1) 在电商项目中设orderNum 为唯一ID，内部rpc远程调用使用id，防止外部猜测订单号
2) 分布式系统解决并发生成订单号问题

在抢票系统中，订单号不会重复生成（保证订单的幂等性）分布锁

提交将订单号生成号，放在Redis 里面，需要的时候直接区Redis 里面拿

3NF

3NF是对字段冗余性的约束，它要求字段没有冗余。没有冗余的数据库设计可以做到。

定义：如果关系模式R是2NF，且关系模式R（U，F）中的所有非主属性对任何候选关键字都不存在传递依赖，则称关系R是属于第三范式

简单的说，第三范式要满足以下的条件：首先要满足第二范式，其次非主属性之间不存在函数依赖。由于满足了第二范式，表示每个非主属性都函数依赖于主键。如果非主属性之间存在了函数依赖，就会存在传递依赖，这样就不满足第三范式。

上例中修改后的选课表中，一个教师能确定一个教师职称。这样，教师依赖于（学生，课程），而教师职称又依赖于教师，这叫传递依赖。第三范式就是要消除传递依赖。

修改后（选课表）

学生 | 课程 | 教师 | 教室 | 上课时间
:-:|:-:|:-:|:-:|:-:|:-:|
李四 | SpringBoot | Java讲师| 301 | 8:00
张三 | Mysql | 李老师 | 302 | 9:22

（教师表）

教师	教师职称
王老师	Java讲师
李老师	Mysql 讲师

不一定完全要遵循3F

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。

具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

总结

简单的说

第一范式就是原子性，字段不可再分割；

第二范式就是完全依赖，没有部分依赖；

第三范式就是没有传递依赖。

分表分库

概念

垂直拆分

垂直拆分就是要把表按模块划分到不同数据库表中（当然原则还是不破坏第三范式），这种拆分在大型网站的演变过程中是很常见的。当一个网站还在很小的时候，只有小量的人来开发和维护，各模块和表都在一起，当网站不断丰富和壮大的时候，也会变成多个子系统来支撑，这时就有按模块和功能把表划分出来的需求。其实，相对于垂直切分更进一步的是服务化改造，说得简单就是要把原来强耦合的系统拆分成多个弱耦合的服务，通过服务间的调用来满足业务需求看，因此表拆出来后要通过服务的形式暴露出去，而不是直接调用不同模块的表，淘宝在架构不断演变过程，最重要的一环就是服务化改造，把用户、交易、店铺、宝贝这些核心的概念抽取成独立的服务，也非常有利于进行局部的优化和治理，保障核心模块的稳定性
垂直拆分用于分布式场景。

水平拆分

上面谈到垂直切分只是把表按模块划分到不同数据库，但没有解决单表大数据量的问题，而水平切分就是要把一个表按照某种规则把数据划分到不同表或数据库里。例如像计费系统，通过按时间来划分表就比较合适，因为系统都是处理某一时间段的数据。而像SaaS应用，通过按用户维度来划分数据比较合适，因为用户与用户之间的隔离的，一般不存在处理多个用户数据的情况，简单的按user_id范围来水平切分

通俗理解

水平拆分行，行数据拆分到不同表中，垂直拆分列，表数据拆分到不同表中

什么时候分库

电商项目将一个项目进行拆分，拆分成多个小项目，每个小的项目有自己单独数据库。
互不影响一一垂直分割会页数据库、订单数据库、支付数据库

什么时候分表

水平分割分表更具业务需求存放日志（每年存放）更具年分表、腾讯QQ号

位数（缺点）、不均匀10位9位8位、手机号前3位136138155、

水平分割（取摸算法）

6条数据，在3张表里面分均匀

id	name	address
1	Zhangsan	上海
2	Lisi	上海
3	Laowu	上海
4	Sanday	上海
5	Jia	上海
6	XiaoBing	上海

取模算法 1%3=1 2%3=2 3%3=0

user0

id	name	address
3	Laowu	上海
6	XiaoBing	上海

user1

id	name	address
1	Zhangsan	上海
4	Sanday	上海

user2

id	name	address
2	Lisi	上海
5	Jia	上海

取模算法也有个缺点，如果表发生了改变，就比较难办了

慢查询

如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)

show status

使用show status使用show status查看MySQL服务器状态信息
常用命令

--mysql数据库启动了多少时间
show status like 'uptime';


show  stauts like 'com_select'  show stauts like 'com_insert' ...类推 update  delete（显示数据库的查询，更新，添加，删除的次数）

show  stauts like 'com_select'  show stauts like 'com_insert' ...类推 update  delete（显示数据库的查询，更新，添加，删除的次数）

//显示到mysql数据库的连接数
show status like  'connections '; 

//显示慢查询次数
show status like 'slow_queries';

什么是慢查询

MySQL默认10秒内没有响应SQL结果,则为慢查询
可以去修改MySQL慢查询默认时间

如何修改慢查询


--查询慢查询时间
show variables like 'long_query_time';

--修改慢查询时间
set long_query_time=1; ---但是重启mysql之后，long_query_time依然是my.ini中的值

如何将慢查询定位到日志中

在默认情况下，我们的mysql不会记录慢查询，需要在启动mysql时候，指定记录慢查询才可以

bin\mysqld.exe –safe-mode –slow-query-log [mysql5.5 可以在my.ini指定]（安全模式启动，数据库将操作写入日志，以备恢复）

bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

先关闭mysql,再启动, 如果启用了慢查询日志，默认把这个文件放在 my.ini 文件中记录的位置

1
2
3


#Path to the database root
datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

总结

有些情况下先是主表存放所有数据，再根据业务需求来进行分表

Aliyun的Rds 实现了这些功能

可以使用 mycat 来实现分表功能

Mycat数据库分库分表中间件

原文作者：keacwu

原文链接：http://www.loongten.com/2019/07/31/mysql-optimization/

发表日期：July 31st 2019, 9:33:31 am

更新日期：July 31st 2019, 5:55:26 pm

Next Post

centos7 安装tomcat9
Previous Post

SpringBoot 多环境配置

