MYSQL优化小技巧

╰+攻爆jí腚メ 2024-04-18 17:00 15阅读 0赞

#### 数据库的优化 ####

##### 1、引擎的选择： #####

> Myisam：数据库并发不大，读多写少,而且都能很好的用到索引，sql语句比较简单的应用，TB数据仓库  
> Innodb：并发访问大，写操作比较多，有外键、事务等需求的应用，系统内存较大。  
> 区别:
> 
> 1.  事务安全 myisam不支持事务而innodb支持
> 2.  查询和添加速度 myisam不用支持事务就不用考虑同步锁，查找和添加和添加的速度快
> 3.  支持全文索引 myisam支持innodb不支持
> 4.  锁机制 myisam支持表锁而innodb支持行锁(事务)
> 5.  外键 MyISAM 不支持外键， INNODB支持外键. (通常不设置外键，通常是在程序中保证数据的一致)

##### 2、字段类型选择 #####

> 根据需求选择合适的字段类型，在满足需求的情况下字段类型尽可能小。

##### 3、NULL OR NOT NULL #####

> 尽可能设置每个字段为NOT NULL，：
> 
> 1.  使用含有NULL列做索引的话会占用更多的磁盘空间，因为索引NULL列需要而外 的空间来保存。
> 2.  进行比较的时候，程序会更复杂。
> 3.  含有NULL的列比较特殊，SQL难优化，如果是一个组合索引，那么这个NULL 类型的字段会极大影响整个索引的效率

##### 4、创建索引 #####

> 索引（Index）是帮助DBMS高效获取数据的数据结构。  
> 分类：普通索引 / 唯一索引 /主键索引/全文索引  
> 使用场景：  
> a: 肯定在where条件经常使用,如果不做查询就没有意义  
> b: 该字段的内容不是唯一的几个值(sex)  
> c: 字段内容不是频繁变化.

##### 5、优化sql #####

1.  避免负向查询和%前缀模糊查询。
2.  不在索引列做运算或者使用函数。会导致无法使用索引
3.  不要在生产环境程序中使用select \* from 的形式查询数据。只查询需要使用的列。
4.  查询尽可能使用limit减少返回的行数，减少数据传输时间和带宽浪费。
5.  where子句尽可能不对查询列使用函数，因为对查询列使用函数用不到索引。
6.  避免隐式类型转换，例如字符型一定要用’’，数字型一定不要使用’’。
7.  所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。
8.  联表查询的时候，记得把小结果集放在前面，遵循小结果集驱动大结果集的原则。
9.  开启慢查询，定期用explain优化慢查询中的SQL语句。
10. 不要使用in 换成exit、也不要使用or

##### 6、遵循范式 #####

**数据库表设计时需要遵循方式**  
表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF

**1NF**: 即表的列的具有原子性,不可再分解，即列的信息，不能分解.只要数据库是关系型数据库(mysql/oracle/db2/sysbase/sql server)，就自动的满足1NF.关系型数据库中是不允许分割列的。

**2NF**:表中的记录是唯一的.通常我们设计一个主键来实现 **（消除了非主属性对 主码的部分依赖）**

**3NF**:即表中不要有冗余数据, 就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放.(外键) **（消除了非主属性 对主码的传递依赖）**

**反3NF**没有冗余的数据库未必是最好的数据库，有时为了**提高运行效率**，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。**降低范式就是增加字段，允许冗余。 订单和订单项、相册浏览次数和照片的浏览次数**

#### 7、其他方面 ####

1、可以通过缓存的方式，如redis缓存。对于经常查询的数据，放在缓存中  
2、读写分离：当一台服务器不能满足需求时，采用读写分离的方式进行集群。  
3、分表 ：当一张表的数据比较多或者一张表的某些字段的值比较多并且很少使用时，采用水平分表和垂直分表来优化