数据库面试知识点（五）数据库优化相关概念

一、优化介绍

数据结构、SQL、索引是成本最低，且效果最好的优化手段。

二、优化方向

1. SQL 以及索引的优化

首先要根据需求写出结构良好的 SQL，然后根据 SQL 在表中建立有效的索引。但是如果索引太多，不但会影响写入的效率，对查询也有一定的影响。

2. 合理的数据库设计

（1）根据数据库三范式来进行表结构的设计。设计表结构时，就需要考虑如何设计才能更有效的查询。

数据库三范式：

• 第一范式：数据表中每个字段都必须是不可拆分的最小单元，也就是确保每一列的原子性；
• 第二范式：满足一范式后，表中每一列必须有唯一性，都必须依赖于主键；
• 第三范式：满足二范式后，表中的每一列只与主键直接相关而不是间接相关 (外键也是直接相关)，字段没有冗余。

（2）有时候可以根据场景合理地反规范化：

• 分割表。
• 保留冗余字段。当两个或多个表在查询中经常需要连接时，可以在其中一个表上增加若干冗余的字段，以 避免表之间的连接过于频繁，一般在冗余列的数据不经常变动的情况下使用。
• 增加派生列。派生列是由表中的其它多个列的计算所得，增加派生列可以减少统计运算，在数据汇总时可以大大缩短运算时间。

（3）数据库五大约束：

• PRIMARY key: 设置主键约束；
• UNIQUE：设置唯一性约束，不能有重复值；
• DEFAULT 默认值约束
• NOT NULL：设置非空约束，该字段不能为空；
• FOREIGN key : 设置外键约束。

（4）字段类型选择：

• 尽量使用 TINYINT、SMALLINT、MEDIUM_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED
• VARCHAR 的长度只分配真正需要的空间
• 使用枚举或整数代替字符串类型
• 尽量使用 TIMESTAMP 而非 DATETIME
• 单表不要有太多字段，建议在 20 以内
• 避免使用 NULL 字段，很难查询优化且占用额外索引空间

3.优化的步骤

（1）代码优化

第一步就应该是分析相关的代码，找出相应的瓶颈，再来考虑具体的优化策略。有一些性能问题，完全是由于代码写的不合理，通过直接修改一下代码就能解决问题的，比如 for 循环次数过多、作了很多无谓的条件判断、相同逻辑重复多次等。

（2）定位慢 SQL，并优化

由自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的 SQL，然后使用 explain、profile 等工具来逐步调优，最后经过测试达到效果后上线。

• slow_query_log 慢查询开启状态。

• slow_query_log_file 慢查询日志存放的位置（这个目录需要 MySQL 的运行帐号的可写权限，一般设置为 MySQL 的数据存放目录）。

• long_query_time 查询超过多少秒才记录。

当在配置文件中开启慢查询日志记录之后，就会在指定的存放目录生成日志文件。

三、优化方案

（一）语句优化

• 连续数值条件，用 BETWEEN 不用 IN：SELECT id FROM t WHERE num BETWEEN 1 AND 5
• Update 语句，如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型
• 不建议使用 select * from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理
• 表与表之间通过一个冗余字段来关联，要比直接使用 JOIN 有更好的性能
• select count (*) from table；这样不带任何条件的 count 会引起全表扫描
• 在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。

（二）合理使用索引

索引一般情况下都是高效的。但是由于索引是以空间换时间的一种策略，索引本身在提高查询效率的同时会影响插入、更新、删除的效率，频繁写的表不宜建索引。

1.索引类型

• 主键索引 （PRIMARY KEY）
• 唯一索引 （UNIQUE）
• 普通索引 （INDEX）
• 组合索引 （INDEX）
• 全文索引 （FULLTEXT）

2.注意

• 在建有索引的字段上尽量不要使用函数进行操作。
• 尽量不要在 where 子句中对字段进行 null 值判断或者使用！= 或 <> 操作符，否则将导致引擎放弃使用索引而进行全表扫描

（三）分表

1.分表方式

水平分割（按行）、垂直分割 (按列)

2.分表场景

• 根据经验，mysql 表数据一般达到百万级别，查询效率就会很低。
• 一张表的某些字段值比较大并且很少使用。可以将这些字段隔离成单独一张表，通过外键关联，例如考试成绩，我们通常关注分数，不关注考试详情。

3.水平分表策略

• 按时间分表：当数据有很强的实效性，例如微博的数据，可以按月分割。
• 按区间分表：例如用户表 1 到一百万用一张表，一百万到两百万用一张表。
• hash 分表：通过一个原始目标 id 或者是名称按照一定的 hash 算法计算出数据存储的表名。

【Java 面试那点事】

这里致力于分享 Java 面试路上的各种知识，无论是技术还是经验，你需要的这里都有！

这里可以让你【快速了解 Java 相关知识】，并且【短时间在面试方面有跨越式提升】

面试路上，你不孤单！