数据库优化

数据库优化

为什么进行数据库优化呢？

如今我们的数据量都很大，表的字段也越来越多，如果数据库不进行优化，改变查询速度，查询会很慢，用户进行访问的时候会很影响体验，所以，数据库优化是必要的

数据库优化带来的好处

1. 可以避免网站页面出现访问错误

   就比如慢查询很多（产生原因：数据查询慢，表特别大或者是忘加索引）

   数据库连接超时的情况（产生原因：数据库连接池满了）

2. 减少很多数据库问题，很多问题都是低效的查询造成的

3. 可以避免因阻塞造成数据无法提交（超时或阻塞）

优化方案

不确定字段长度的时候用varchar代替char

对于一个字段来说，不确定长度varchar更适合，char是定长，数据长度小于char给定的长度，造成浪费，varchar是变长字段，实际长度是数据的长度，节省储存空间，对于查询来说在一个较小的字段内搜索，效率更高。char很适合固定长度的字符串，从检索效率上来说，char > varchar ,所以知道长度的话char较为适合，比如MD5

避免在where子句中使用or来连接条件

如图可看出使用or会导致索引失效，如果条件中有or，只要一个条件没有索引，其他字段有索引也不会使用

用or的情况下

1. 不用索引，进行一次全表扫描就可以了

2. 使用索引，一个走索引，一个不走，结果就是全表扫描+索引扫描+合并，

这样一比较，mysql优化器肯定选择不走索引，也就说明为啥其他字段有索引也不会使用

尽量使用数值替代字符串类型

原因：

1:因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

2:而对于数字型而言只需要比较一次就够了；

3:字符会降低查询和连接的性能，并会增加存储开销

查询尽量避免返回大量数据

如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。

使用explain分析你SQL执行计划

SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引

• EXPLAIN SELECT * FROM student WHERE id = 1

type：

ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
null MySQL不访问任何表或索引，直接返回结果
System 表只有一条记录(实际中基本不存在这个情况)

• 性能排行：

  System > const > eq_ref > ref > range > index > ALL

给字段加索引

注意1:

1. 有些情况会导致索引失效

2. sql语句中使用or，≠ <>

3. 不符合最左前缀匹配

4. 列进行运算

5. 使用函数

6. 类型不匹配（where id ='123’和where id = 123,不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较）

注意2：什么情况下不利于建立索引

1. 数据量较少

2. 字段中数据重复太多，比如：性别

3. 经常增删改

4. 参与列计算

5. where条件中用不到的字段

where中使用默认值代替null

EXPLAIN
SELECT * FROM student WHERE age IS NOT NULL


EXPLAIN
SELECT * FROM student WHERE age>0

asc和desc混用会导致索引失效

select * from _t where a=1 order by b desc, c asc

高级sql优化

批量插入性能提升

多次提交：

INSERT INTO student (id,NAME) VALUES(4,'name1');
INSERT INTO student (id,NAME) VALUES(5,'name2');

批量提交:

INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');

• 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升

• 数据量小体现不出来

批量删除优化

#一次删除10万或者100万+？
delete from student where id <100000;


#分批进行删除，如每次500
for(){
delete student where id<500;
}

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作

inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小

inner join 内连接，只保留两张表中完全匹配的结果集
left join会返回左表所有的行，即使在右表中没有匹配的记录
right join会返回右表所有的行，即使在左表中没有匹配的记录

原因：

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点

• 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优

参考文章：

https://blog.csdn.net/weixin_53601359/article/details/115553449

https://blog.csdn.net/chenpengjia006/article/details/101228943