面试八股文Mysql：（2）数据库调优

素颜马尾好姑娘i 2023-10-14 18:28 44阅读 0赞

### 1. SQL优化很有必要 ###

数据库优化在提升系统性能是很重要的一个方面，不管是MySQL还是MongoDB还是其它的数据库。  
SQL优化在提升系统性能中是成本最低 && 优化效果最明显的途径，可以让**吞吐量更大，响应速度更快**。如果你的团队在SQL优化这方面搞得很优秀，对你们整个大型系统可用性方面无疑是一个质的跨越，，真的能让你们老板省下不止几沓子钱。  
在我遇到的项目中就遇到过这样的问题，数据库数据量太大，导致查询数据超时，多个模块都无法正常提供服务，临时的解决方法是删掉老数据，但终究治标不治本。

### 2. SQL优化的方向 ###

![在这里插入图片描述][55f00fd3a6134a03b76a8f3522e38829.png]  
优化成本：硬件>系统配置>数据库表结构>SQL及索引。  
优化效果：硬件<系统配置<数据库表结构<SQL及索引。

因此：数据库优化从以下几个方面优化：

*  SQL 调优
 *  数据库索引
 *  定时清除不需要的数据，定时进行碎片整理
 *  数据库设计—三大范式、字段、表结构
 *  分表分库 (水平分割，垂直分割)
 *  对 MySQL 配置优化 (配置最大并发数 my.ini, 调整缓存大小)
 *  存储过程 (模块化编程，可以提高速度)
 *  主从复制、读写分离
 *  等

#### 2.1. SQL语句调优 ####

SQL性能下降原因：  
1、查询语句写的烂  
2、索引失效（数据变更）  
3、关联查询太多join（设计缺陷或不得已的需求）  
4、服务器调优及各个参数设置（缓冲、线程数等）

通常SQL调优过程：

*  观察，至少跑1天，看看生产的慢SQL情况。
 *  开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来，并存到日志中 (在 my.ini 可以指定慢查询日志目录)。
 *  explain +慢SQL分析。
 *  show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况。
 *  运维经理 or DBA，进行SQL数据库服务器的参数调优。
 *  查看优化后的执行时间和执行计划，如果优化效果不明显，重复

#### 2.2. SQL索引 ####

索引也算数据库设计的一部分

1.一般来说，应该在这些列上创建索引：

在经常需要搜索的列上，可以加快搜索的速度；  
在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；  
在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；  
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；  
在经常需要\*\*排序的列（group by 或者 order by）\*\*上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；  
在经常使用在 WHERE 子句中的列上面创建索引，加快条件的判断速度。  
总结就是：唯一、不为空、经常被查询的字段

2.对于有些列不应该创建索引：

对于那些在查询中很少使用或者参考的列不应该创建索引。  
对于那些只有很少数据值的列也不应该增加索引。  
对于那些定义为 text, image 和 bit 这种数据量很大的数据类型的列不应该增加索引。  
当修改性能远远大于检索性能时，不应该创建索引。修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。  
3.索引失效

在以下这些情况种，执行引擎将放弃使用索引而进行全表扫描

在 where 子句中使用\*\*！= 或 <> 操作符\*\*

在 where 子句中使用 or 来连接条件，当连接的字段有字段没有索引时，将导致所有字段的索引失效

在 where 子句字段进行 null 值判断，

在 where 子句中 like 的模糊匹配以 % 开头

在 where 子句中对索引进行表达式运算或函数操作

如果执行引擎估计使用全表扫描要比使用索引快，则不使用索引

#### 2.3. SQL设计三大范式 ####

（一）数据库设计—三大范式、字段、表结构

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

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

尽量使用 TINYINT、SMALLINT、MEDIUM\_INT 作为整数类型而非 INT，如果非负则加上 UNSIGNED  
VARCHAR 的长度只分配真正需要的空间  
尽量使用整数代替字符串类型  
单表不要有太多字段，建议在 20 以内  
避免使用 NULL 字段，很难查询优化且占用额外索引空间  
不建议使用 select \* from t ，用具体的字段列表代替 “*”，不要返回用不到的任何字段。尽量避免向客户 端返回大数据量，若数据量过大，应该考虑相应需求是否合理  
表与表之间通过一个冗余字段来关联，要比直接使用 JOIN 有更好的性能  
select count (*) from table；这样不带任何条件的 count 会引起全表扫描

#### 2.4. 主从复制和读写分离 ####

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。

作用：数据库备份，读写分离，高可用，集群.

2.过程：

在每个事务更新数据完成之前，master 在二进制日志记录这些改变。写入二进制日志完成后，master 通知存储引擎提交事务。

Slave 将 master 的 binary log 复制到其中继日志。首先 slave 开始一个工作线程（I/O），I/O 线程在 master 上打开一个普通的连接，然后开始 binlog dump process。binlog dump process 从 master 的二进制日志中读取事件，如果已经跟上 master，它会睡眠并等待 master 产生新的事件，I/O 线程将这些事件写入中继日志。

Sql slave thread（sql 从线程）处理该过程的最后一步，sql 线程从中继日志读取事件，并重放其中的事件而更新 slave 数据，使其与 master 中的数据一致，只要该线程与 I/O 线程保持一致，中继日志通常会位于 os 缓存中，所以中继日志的开销很小。

![在这里插入图片描述][8f1b4ad4afc84e3d91cfcd0e7b769c62.png]

#### 2.5. 分库分表 ####

分库分表

主从复制中，从数据库可以通过增加数量不断扩张，但是主数据库不能轻易增加，这个时候可以考虑分表分库。

1.分表方式

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

垂直拆分：垂直拆分就是要把表按模块划分到不同的数据库中，数据库按模块和功能把表划分出来，趋向于服务化

水平切分主要是用于解决“数据库数据量大的问题”

水平拆分：水平拆分就是要把一个表按照一定的规则把数据划分到不同的表或数据库中。比如按时间，账号规则，年份，取模算法等.  
2.分表场景

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

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

#### 2.6. 架构优化 ####

应用与数据库之间增加一个缓存服务，如Redis或Memcache。

![在这里插入图片描述][43105e8bda514654b9fb49ae664606ba.png]

当接收到查询请求后，我们先查询缓存，判断缓存中是否有数据，有数据就直接返回给应用，如若没有再查询数据库，并加载到缓存中，这样就大大减少了对数据库的访问次数，自然而然也提高了数据库性能。不过需要注意的是，引入分布式缓存后系统需要考虑如何应对缓存穿透、缓存击穿和缓存雪崩的问题。

[55f00fd3a6134a03b76a8f3522e38829.png]: https://img-blog.csdnimg.cn/55f00fd3a6134a03b76a8f3522e38829.png
[8f1b4ad4afc84e3d91cfcd0e7b769c62.png]: https://img-blog.csdnimg.cn/8f1b4ad4afc84e3d91cfcd0e7b769c62.png
[43105e8bda514654b9fb49ae664606ba.png]: https://img-blog.csdnimg.cn/43105e8bda514654b9fb49ae664606ba.png