性能测试：数据库架构和SQL优化

旧城等待， 2023-09-25 13:43 38阅读 0赞

**前言**  
有时候我们出去面试的时候，会被问到是否有做过架构方面的优化。如果没有准备突然被问到的话通常会有点懵。那么我们这里来整理一下系统架构优化相关的知识。

其实一般架构优化主要就分为数据库架构，第二个就是应用程序架构。  
**数据库架构优化**

1.  读写分离，主从配置

数据库架构优化，其实主要就是读写分离，主从配置。通常公司并发量比较大，或者用户比较大的情况下都会考虑读写分离。数据库支持主从同步，一台机器作为主，一台机器作为从。一旦做成主从就可以实现读写分离。一般读写分离通常是读的操作放在从机上，写的操作放在主机上，这样当我们往主上面写入数据的时候，它会向从同步数据。

这样相当于我们把一个数据库的工作分担到两台数据库上，这样子每个数据的压力会降低，我们数据库的性能就会提升。

1.  分库分表

然后一种数据库架构优化是分库分表。为什么要做分库分表呢？  
MYSQL当它单表的数据达到某个值的时候，他的性能就会比较厉害。（通常单张表超出2-3千万），那么当大数据的情况下，分库分表就非常重要。分库分表会根据一个固定的算法来路由库名和表名。

这样子就相当于我们把数据平均拆分到某干个表中，每个表的单表容量就会降低。而且分表通常都会结合着分库，分库分表我们需要用到数据库中间件。  
**数据库硬件优化**  
使用更好的物理磁盘介质，如 SSD、fusionIO卡 等。通常公司比较核心的数据库用的都是比较好的磁盘。  
**SQL优化**  
说到数据库的话，我们不得不说到SQL，下面我们来看一下常见的一些SQL优化方案：

*  在 `where` 及 `order by` 设计到列上建立索引，避免全表扫描，索引不要太多，一个表一般不要超过4个索引。
 *  避免在 `where` 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引进行全局扫描。如果想要判断null值，可以使用`EXISTS`来判断
 *  避免在 `where` 子句中对字段进行函数或者表达式操作，否则导致引擎放弃使用索引进行全局扫描，可以将函数或者表达式放在代码层进行判断。
 *  避免在 `where` 子句中对字段进行左右模糊查询，否则导致引擎放弃使用索引进行全局扫描，如下`sql`

1｜select name from user where name like '%test%';

当我们数据量大的时候，模糊查询的功能就不应该在数据库层面做了，可以使用搜索引擎ES

*  查询语句中尽量不要使用 \* ，减少内存的使用
 *  尽量减少子查询，使用关联查询（left join，right join，inner join）代替
 *  减少使用 in 或者 not in，使用 exists， not exists或者关联查询语句代替
 *  or 的查询尽量用 union 或者 union all 代替，如下SQL

# 如我们想要查询id 为1和2的数据
    select * from user where id = 1 or id = 2
    
    # 我们可以使用UNION代替，他们的查询效果是一样的，但是UNION的性能会更好
    # UNION会自动去除重复数据
    select * from user where id = 1 
    UNION
    select * from user where id = 2

*  合理的添加冗余的字段（减少表的联接查询）
 *  建表的时候能使用数字类型的字段就使用数字类型（type，status…)，数字类型的字段作为条件查询比字符串快