* MySQL优化 - 业务设计与 慢查询

MySQL优化 - 业务设计与 慢查询

1、逻辑设计

三大范式

• 数据库设计的第一大范式
  • 数据库表中的所有字段都只具有单一属性
  • 单一属性的列是基本数据类型所构成的
  • 设计出来的表都是简单的二维表
  • 第一范式的反例和正例，如下
    
• 第二大范式
  • 要求表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系
  • 反例 和 正例如下：
    
• 第三大范式
  • 指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上相处了非主键对主键的传递依赖
  • 反例 和 正例 如下：
    

表设计实战：

完全按照三大范式要求设计出来的表：

问题：

反范式化设计

2、物理设计

根据所选的关系型数据库的特点对逻辑模型进行存储结构的设计

定义数据库，表 及 字段的命名规范：

存储引擎的选择：

为表中的字段选择合适的数据类型：

浮点类型：

浮点类型示例如下：


日期类型：

日期类型示例：

慢查询

慢查询的定义及作用

慢查询日志，是指mysql记录所有执行超过 long_query_time 参数设定的时间阈值的 SQL 语句的日志。该日志能为 SQL 语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

show variables like 'slow_query%'; -- 查询慢查询是否开启
set global long_query_time = 0; -- 设置慢查询的时间
set global slow_query_log = 1;  -- 开启慢查询

慢查询日志中记录的内容：

# Time: 2020-05-23T00:47:18.116415Z
# User@Host: root[root] @ localhost [127.0.0.1]  Id:    10
# Query_time: 0.005000  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
SET timestamp=1590194838;
SHOW TABLE STATUS LIKE 'testmysam';

慢查询日志内容的解释：

常用分析慢查询的工具：
mysqldumpslow

执行以下命令，导出慢查询SQL语句：

mysqldumpslow.pl -s t -t 10 D:\slow.log

pt_query_digest

导出慢查询文件：

pt-query-digest  --explain h=127.0.0.1, u=root,p=password slow-mysql.log

汇总的信息【总的查询时间】、【总的锁定时间】、【总的获取数据量】、【扫描的数据量】、【查询大小】

汇总的信息【总的查询时间】、【总的锁定时间】、【总的获取数据量】、【扫描的数据量】、【查询大小】

Response: 总的响应时间。
time: 该查询在本次分析中总的时间占比。
calls: 执行次数，即本次分析总共有多少条这种类型的查询语句。
R/Call: 平均每次执行的响应时间。
Item : 查询对象

索引 和 执行计划

索引是什么？
mysql 官方对索引的定义为：

• 索引（index）是帮助 mysql 高效获取数据的数据结构。
• 可以得到索引的本质：索引是数据结构。