2021-09-23 MySQL数据库的优化

一、选择存储引擎

二、字段类型的选择

三、范式

四、索引

五、分区、分表

六：慢查询日志

七：explain SQL语句分析

八、全文索引

九、SQL语句优化

 

一、选择存储引擎

所有存储引擎

 

 

 

MYISAM简单介绍：

MYISAM引擎数据存储结构:

 

 

 

.frm 表结构文件

.myi 表索引文件

.myd 数据文件

 

数据顺序插入数据库

 

 

 

顺序插入, 带来的好处, 就是插入速度快。劣势, 一旦数据被删除, 新的数据不会填补被删除的空间, 形成记录空洞, 浪费空间.

 

insert into myisam_1 select null, title from myisam_1;

复制自身数据，再插入到数据库，生成大量数据

当前大小:

 

 

 

删除其中的一半的数据，数据表的大小没有变

 

 

 

记录删了, 空间没减少, 选择修复表:

repair table table-name

修复之后, 空间减少

 

 

 

其他特性：

高速插入与查询，myisam支持全文索引(基本不用的)， 应用主要是大量的查询和插入时, 性能才好，(web程序大多都是 大量的查询和插入 很少的更新和删除)；

对锁的支持: 支持表级锁定；

对并发的支持较差。

 

INNODB:

 

 

 

.frm 表结构文件

.ibd 表索引文件和数据文件（集中式文件）

表中数据记录的存储顺序: 主键顺序

 

其他特性：

支持事务、支持外键；

大多数情况, innodb更好，更高效的删除更新；

对锁的支持: 支持表级锁定 支持行级锁定；

提供更好的并发的支持。

 

二、字段类型的选择

2.1、尽可能短, 用更少的存储空间

例如:

tinyint int bigint

char(32) char(64)

 

varchar(>255) 与 text的选择?

text, 不占用记录空间.

一条记录, 有10个字段组成. 整个10个字段总空间有限(默认65535bytes), 当表中可能会出现大量的 大长度的字符串, 请使用text, 而不要使用varchar(>255)

 

2.2、尽可能定长, 占用固定长度的存储空间

int, 定长数据类型.

varchar(255), 变长数据类型

如果一条记录10个字段, 都是定长类型, 记录就属于定长记录. 对应, 叫变长记录.

 

例如:

char定长, varchar变长

double双精浮点数, 定长数据类型, decimal定点数, 变长数据类型

 

2.3、尽可能使用整数

整型 运算速度  高数据类型. 擅长排序和查找

存储性别：

sex tinyint

 

例如, 存储 ipv4: 192.168.0.15

语言中, 提供了: 整型与ip的转换:

mysql:inet_aton(): 地址到数值的转换, inet_ntoa()数值到地址的转换

 

 

 

 

 

 或者用 PHP函数ip2long将ip转成数字

 

 

 

三、范式

3.1、1NF, 第一范式: 原子性

原子性字段, 表中的每个字段, 都要存储一个数据,而不是存储多个数据。

例如: 授课信息 （不满足第一范式）

老师	性别	班级	教室	课程
张三	男	三年1班	101	语文, 150
李四	男	三年2班	102	化学, 100

以上的课程字段, 就在逻辑上没有满足原子性: 内容不够独立. 应该如下, 才可以:

讲师	性别	班级	教室	课程	学分
张三	男	三年1班	101	语文	150
李四	男	三年2班	102	化学	100

3.2、2NF, 第二范式, 消除部分依赖

在满足第1范式前提下, 要求不能出现对主键的部分依赖

例如, 老师授课表，以老师字段作为主键

但是，性别 依赖于 老师字段, 出现了部分依赖.

老师	性别	班级	教室	课程	学分
张三	男	三年1班	101	语文	150
李四	男	三年2班	102	化学	100
王五	男	三年3班	103	物理	100

解决方案: 增加与逻辑无关系的唯一的主键字段.

主键	老师	性别	班级	教室	课程	学分
1	张三	男	三年1班	101	语文	150
2	李四	男	三年2班	102	化学	100
3	王五	男	三年3班	103	物理	100

3.3、3NF,第三范式, 消除传递依赖

在满足第二范式的基础上, 消除传递依赖

例如: 班级依赖于老师, 老师依赖于主键字段. 班级和主键间, 出现了传递依赖.(字段依赖于非主键字段, 例如, 班级依赖于非主键字段老师)

 

解决方案:

每个实例, 建立一张独立的表, 通过关联字段 进行管理.

老师:

ID	老师	性别
1	张三	男
2	李四	男
3	王五	男

班级:

ID	班级	教室
4	三年1班	101
5	三年2班	102
6	三年3班	103

课程:

ID	课程	学分
7	语文	150
8	化学	100
9	物理	100

授课表:

ID	老师ID	班级ID	课程ID
1	1	4	7
2	2	5	8
3	3	6	9

 

四、索引

性价比最高的优化方案!

索引: 数据(记录)的部分关键字, 与 数据(记录)位置的 对应关系, 就叫索引。当需要通过某个关键字, 获取记录时, 就可以现在索引中进行检索, 获取位置之后, 找到对应的数据记录，避免了, 所有记录一条条的检索(全文扫描)。

因此, 建立索引要先提取关键字, 再建立关键字和位置的映射，索引建成了。

 

索引分类

什么类型, 都是关键字与位置的映射，对关键字的要求不同, 形成的不同类型

1、普通, index

对关键字没有要求.

2、唯一, unique index(key)

关键字唯一, 可以是null.

3、主键, primary key

关键字唯一, 且不能为null.

4、全文索引, fulltext index

mysql目前不支持中文的全文索引!

 

测试

1、测试过滤条件where

查询字段 enterprise_name 没有索引, 执行时间如下:

 

 

 

 用explain分析SQL

 

 

 

 给enterprise_name 加上索引

 

 

 测试查询时间：

 

 

 用explain进行分析

 

 

 

Where条件中的索引独立原则

不独立所需时间

 

 

 

用explain进行分析

 

 

 

2、测试排序 order by

先删除索引

 

 

 

测试查询时间：

 

 

 用explain 进行分析

 

 

 给enterprise_name 添加索引后在查询

 

 

 

用explain 进行分析

 

 

 

五、分区、分表

5.1、分区

如果表中的数据量很大, 将表中的数据划分到各个数据区中存储, 降低每个区中存储的数据量。

 

 

 

执行之后

 

 

 

插入数据，看看数据分区情况

 

 

 

 

 

 

5.2、分表

5.2.1 水平分表

做的事与分区一模一样，只不过会创建结构完全相同的多张表

例如应聘记录表：

PHP通过算法，将数据插入到不同的表。

分区创建的表：

 

 

 

水平分表创建的表：

 

 

 

5.2.2 垂直分表

例如：老师表

主键	老师	课程	班级	居住地	性别	年龄
1	张三	语文	三年1班
2	李四	化学	三年2班
3	王五	物理	三年3班

课程表：根据实际查询需求，老师的课程信息查询量比较大，占表查询量的90%，这时可以将表进行垂直分区，例如：

课程表：

主键	老师	课程	班级
1	张三	语文	三年1班
2	李四	化学	三年2班
3	王五	物理	三年3班

 基本信息表：

主键	居住地	性别	年龄
1
2
3