mysql数据库知识面试准备-优化篇

落日映苍穹つ 2022-03-30 16:08 203阅读 0赞

# 你对数据库优化都有哪些经验 #

## 1.对于数据库存储引擎来说 ##

mysql我常用的存储引擎有三种  
**MyiSAM存储引擎**  
它是5.5之前的默认的存储引擎,不支持事务,不支持外键,内存和硬盘空间占用率低,访问速度快  
**InnoDB存储引擎**  
它是5.5之后默认的存储引擎,支持事务.支持外键,提供行级锁(就是使一些操作一次只能一个人进行),不过写的处理效率比较低,会占用更多的磁盘空间(保留数据和索引)  
**MEMORY存储引擎(memory)**  
访问的数据非常的快,默认使用的是HASH索引,不过,一旦数据库服务重启或者关闭了,表中的数据就会丢失.

大部分的情况下,选择InnoDB引擎  
对于日志性的应用主要用来插入和查询数据的时候,我会使用MyiSAM存储引擎.因为他开销低，而且插入速度非常快.  
在临时存放数据,数据量不大,而且还不需要较高的数据安全性的时候我会使用Memory存储引擎,因为它的读写速度非常的快，

## 2.对于提高sql语句效率 ##

**在处理大批量插入数据时**  
大批量数据插入空表的时候,可以将存储引擎设置为MyISAM存储引擎,并且通过disable keys将唯一索引关闭;  
大批量数据插入非空Innodb表的时候  
第一种办法就是在导入数据的时候按照主键顺序排列  
第二种就是导入数据前关闭唯一性的效验,导入后恢复(关闭唯一性的效验:set UNIQUE\_CHECKS=0);  
第三种就是如果使用了自动提交,可以在导入前执行关闭自动提交,导入后恢复(SET AUTOCOMMIT=0);  
对于**优化INSERT语句**来说  
尽量使用多个值表的insert语句,降低连接,关闭的消耗;  
还可以将索引文件和数据文件分放在的不同的磁盘上存放着  
如果是一个文本文件装入一个表时,使用LOAD DATA INFLIE ,比一般的insert的语句要快近20倍!  
对于**SELECT优化**来说  
可以尽量减少额外的排序,通过索引直接返回有序数据;  
where条件和order by使用相同的索引,并且order by 的字段尽量都是升序或者降序;

还有就是能用关联查询的时候,就不要用子查询  
而对于包含or的查询语句,如果要利用索引的话,则or  
之间的每个条件必须都用到索引,否则应该考虑给他们增加索引了  
**如果是优化分页**  
1.可以在索引上完成排序分页的操作,然后根据主键关联回原表查询所需要的其他列,比如  
select [a.name][], a.description from person a innner join(select pid from person order by birthdat limit 50,5)  
2.也可以把limit查询转换为某个位置的查询  
比如  
select [a.name][], a.description from person where pid>7788 order by birthday limit 50,5

而对于已经知道只有一条查询的数据时,可以在后面加上limit 1增加查询效率;  
**并不是使用索引就一定会提高效率,也有不使用索引的情况**  
比如:  
1…用or分隔开的条件,如果or前的条件中的列有索引,而后面的列没有索引,那么涉及到的索引都不会被用到  
2…对于复合索引,如果索引列不是复合索引的第一部分,那么也不用索引(也就是不符合最左前缀)  
3…如果like是以"%"开始的,那么该列上的索引也不会被使用到.  
4…如果列是字符串的话,where条件中必须将字符常量值加上引号,否则即使该列上存在索引,也不会使用  
5.像not in, not exists, <, >, != 这些操作符都不走索引  
还有一个需要注意的就是  
不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统也有可能无法正确使用索引；  
**explain命令**  
我们一般可以通过explain命令来获取到mysql是如何执行select语句的信息的,像select语句在执行的过程中,表是如何连接的,联系的顺序怎么样;  
我们优化的目标  
一是每个查询都要使用索引以提高查询效率,至少达到range级别，最好能达到ref；  
range就是索引范围扫描  
ref就是非唯一性索引扫描，返回匹配某个单独值的所有行。  
二是追求key\_len和rows最小；  
key\_len表示的是索引中使用的字节数  
rows是根据表统计的信息及索引选用情况.大致估算出找到所需的记录,所需要读取的行数;

[a.name]: http://a.name