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

水深无声 2021-09-21 03:42 380阅读 0赞

### 一、优化介绍 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzOTQ1MjQ2_size_16_color_FFFFFF_t_70]  
数据结构、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 相关知识】，并且【短时间在面试方面有跨越式提升】

面试路上，你不孤单！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzOTQ1MjQ2_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzOTQ1MjQ2_size_16_color_FFFFFF_t_70]: /images/20210920/9ff5f5e776a942c4ba187dc99f9b3850.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMzOTQ1MjQ2_size_16_color_FFFFFF_t_70 1]: /images/20210920/f5127df6377749fdb227cfe64dac0c6a.png