案例2:

假定学生关系表为 Student (学号、姓名、年龄、所在学院、学院地点、学院电话)，关键字为单一关键字“学号”，因为存在如下决定关系：

（学号）-> (姓名、年龄、所在学院、学院地点、学院电话) 

即存在非关键字段“学院地点”、“学院电话”对关键字段“学号”的传递函数依赖。 它也会存在数据冗余，更新异常、插入异常和删除异常的情况。正确应把学生关系表分为如下两个表：

• 学生：（学号、姓名、年龄、所在学院）
• 学院：（学院、地点、电话）

范式化优缺点

范式化的优点：

1. 重复数据少，不冗余；
2. 维护更新快；
3. 范式化的表更小，可在内存中运行。

范式化的缺点：

查询的时候经常需要很多关联，增加查询的代价。也可能使一些索引策略失效，因为范式化将列放在不同的表中，而这些列在一个表中本可以属于同一个索引。

反范式化的优缺点

反范式化的优点：

1. 避免关联，几乎所有数据可以在一张表中显示。
2. 可以设计有效的索引。

反范式化的缺点：

冗余数据多，更小维护麻烦，删除数据时也容易丢失重要信息。

数据表设计的建议

没有冗余的数据库设计可以做到，但是，没有冗余的数据库未必是最好的数据库，有时为列提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

另外，《阿里巴巴Java开发手册》，数据库的表设计允许适当冗余，以提升SQL查询的性能，避免表的关联查询。

适度冗余，减少join的关联

冗余更新频率不高，但是查询频率极高的字段。如订单中的商品名称、微博发帖中的用户昵称。

SQL语句优化

SQL优化的一般步骤

1. 通过show status命令了解各种SQL的执行频率；
2. 定位执行效率较低的SQL语句-（重点select）；
3. 通过explain分析低效率的SQL；
4. 确定问题并采取相应的优化措施。

-- select语句分类SelectDml数据操作语言(insert update delete)
dtl 数据事物语言(commit rollback savepoint)Ddl数据定义语言(create alter drop..)
Dcl(数据控制语言) grant revoke-- Show status 常用命令--查询本次会话Show session status like &＃39;com_%&＃39;;     //show session status like &＃39;Com_select&＃39;--查询全局Show global status like &＃39;com_%&＃39;;-- 给某个用户授权grant all privileges on *.* to &＃39;abc&＃39;@&＃39;%&＃39;;--为什么这样授权 &＃39;abc&＃39; 表示用户名  &＃39;@&＃39; 表示host, 查看一下mysql->user表就知道了--回收权限revoke all on *.* from &＃39;abc&＃39;@&＃39;%&＃39;;--刷新权限[也可以不写]flush privileges; 

SQL语句优化-show参数

MySQL客户端连接成功后，通过使用 show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果，global来表示自数据库上次启动至今的统计结果。默认是session级别的。

show status like &＃39;Com_%&＃39;; 

其中， Com_XXX 表示 XXX 语句所执行的次数。 重点注意：Com_select,Com_insert,Com_update,Com_delete 通过这几个参数，可以了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

还有几个常用的参数便于用户了解数据库的基本情况。Connections：试图连接MySQL服务器的次数Uptime：服务器工作的时间（单位秒）Slow_queries：慢查询的次数 (默认是慢查询时间10s)

show status like &＃39;Connections&＃39;;show status like &＃39;Uptime&＃39;;show status like &＃39;Slow_queries&＃39;; 

查询MySQL的慢查询时间

show variables like &＃39;long_query_time&＃39;; 

修改MySQL慢查询时间

set long_query_time=2; 

SQL语句优化-定位慢查询

上面我们介绍了获取MySQL数据库的一些运行状态是如何查询

• 比如当前MySQL运行的时间： show status like &＃39;Uptime&＃39;;
• 一共执行了多少次select/update/delete.. / ： show status like &＃39;Com_%&＃39;;
• 当前连接数

定位慢查询

如何从一个项目中快速定位执行速度慢的语句（定位慢查询）

show variables like &＃39;%query%&＃39;; 

注意：

1. 如何打开慢查询 ： SET GLOBAL slow_query_log = ON;
2. 将默认时间改为1S： SET GLOBAL long_query_time = 1;

（设置完需要重新连接数据库，PS：仅在这里改的话，当再次重启数据库服务时，所有设置又会自动恢复成默认值，永久改变需去my.ini中改）

SQL语句优化-Explain工具

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

注意:如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

Explain分析示例

DROP TABLE IF EXISTS `actor`; 
CREATE TABLE `actor` (`id` int(11) NOT NULL,`name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `actor` (`id`,`name`,`update_time`) VALUES (1,&＃39;a&＃39;,&＃39;2020-09-16 14:26:11&＃39;), (2,&＃39;b&＃39;,&＃39;2020-09-16 14:26:11&＃39;), (3,&＃39;c&＃39;,&＃39;2020-09-16 14:26:11&＃39;);DROP TABLE IF EXISTS` film`; 
CREATE TABLE`film`(`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`),KEY `idx_name` (`name`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film`(`id`,`name`) VALUES (3,&＃39;film0&＃39;),(1,&＃39;film1&＃39;),(2,&＃39;film2&＃39;);DROP TABLE IF EXISTS `film_actor`;CREATE TABLE`film_actor`(`id` int(11) NOT NULL,`film_id` int(11) NOT NULL,`actor_id` int(11) NOT NULL,`remark` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),KEY `idx_film_actor_id` (`film_id`,`actor_id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO`film_actor`(`id`,`film_id`,`actor_id`)VALUES(1,1,1), (2,1,2),(3,2,1); 

explain select * from actor; 

#关闭mysql5.7新特性对衍 生表的合并优化set session optimizer_switch=&＃39;derived_merge=off&＃39;; 
 

#还原默认配置set session optimizer_switch=&＃39;derived_merge=on&＃39;; 
 

• union ：在 union 中的第二个和随后的 select

explain select 1 union all select 1; 

table 列

这一列表示 explain 的一行正在访问哪个表。 当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查 询，于是先执行 id=N 的查询。 当有 union 时，UNION RESULT 的 table 列的值为，1和2表示参与 union 的 select 行id。

type 列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：

system > const > eq_ref > ref > range > index > ALL 

一般来说，得保证查询达到 range 级别，最好达到 ref。

NULL：MySQL 能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

explain select min(id) from film; 

possible_keys 列

这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

key 列

这一列显示mysql实际采用哪个索引来优化对该表的访问。 如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索 引，在查询中使用 force index、ignore index。

key_len 列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些 列。 举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成， 并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列:film_id列来执 行索引查找。

explain select * from film_actor where film_id = 2; 

key_len计算规则如下: 字符串

• char(n):n字节长度
• varchar(n):2字节存储字符串长度，如果是utf-8，则长度 3n +2

数值类型

• tinyint:1字节
• smallint:2字节
• int:4字节
• bigint:8字节

时间类型

• date:3字节
• timestamp:4字节
• datetime:8字节

如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

ref 列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有:const(常 量)，字段名(例:film.id)

rows 列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

Extra 列

这一列展示的是额外信息。常见的重要值如下:

（1）Using index ：使用覆盖索引

explain select film_id from film_actor where film_id = 1; 

SQL语句优化-索引最佳实践

# 示例表CREATE TABLE`employees`(`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(24) NOT NULL DEFAULT &＃39;&＃39; COMMENT &＃39;姓名&＃39;,`age` int(11) NOT NULL DEFAULT &＃39;0&＃39; COMMENT &＃39;年龄&＃39;,`position` varchar(20) NOT NULL DEFAULT &＃39;&＃39; COMMENT &＃39;职位&＃39;,`hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT &＃39;入职时间&＃39;,
PRIMARY KEY (`id`), KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
 )ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT=&＃39;员工记录表&＃39;; 
INSERT INTO employees(name,age,position,hire_time)VALUES(&＃39;ZhangSan&＃39;,23,&＃39;Manager&＃39;,NOW());INSERT INTO employees(name,age,position,hire_time)VALUES(&＃39;HanMeimei&＃39;, 23,&＃39;dev&＃39;,NOW());INSERT INTO employees(name,age,position,hire_time) VALUES(&＃39;Lucy&＃39;,23,&＃39;dev&＃39;,NOW()); 

全值匹配

EXPLAIN SELECT * FROM employees WHERE name= &＃39;ZhangSan&＃39;; 

• 如果不能使用覆盖索引则可能需要借助搜索引擎

字符串不加单引号索引失效

EXPLAIN SELECT * FROM employees WHERE name = &＃39;1000&＃39;; 
EXPLAIN SELECT * FROM employees WHERE name = 1000;