不懂就背--数据库优化面试题自我总结笔记

柔情只为你懂 2021-07-04 17:31 157阅读 0赞

## 数据库优化综合 ##

1.  预发跑SQL，explain
2.  排除 缓存 sql nocache
3.  看那一下行数对不对，不对可以用annalyze table t 矫正
4.  添加索引 索引不一定是最优的 force index 强制走索引不建议用
5.  是否存在回表情况
6.  覆盖索引避免回表，不要写 \*
7.  联合索引 不能无限建 高频场景
8.  最左前缀原则 按照索引定义的字段顺序写sql
9.  合理安排联合索引的顺序
10. 在5.6版之后，索引下推 减少回表次数
11. 给字符串加索引（前缀索引，倒序存储，Hash）
12. 数据库的flush的时机（redo log满了 修改checkpoint flush到磁盘、 系统内存不足淘汰数据页、系统空闲的时候找间隙刷脏页、MySQL正常关闭，会把内存脏页flush到磁盘）
13. innodb刷盘速度 （脏页比例、redolog写盘速度、innodb\_flush\_neighbors 0） 在机械磁盘的随机IO不太行，减少随机IO，设置为1最好。而ssd设置0就够了，8.0之后默认就是0
14. 索引自字段不要做函数操作，会破坏索引值的有序性，优化器或放弃走树结构（如果触发隐式转换，那也会cast函数，会放弃走索引）
15. 字符集不同可能走不上索引， convert也是函数所以走不上  
    **拓展：**  
    mysql操作中我们经常需要对数据进行类型转换。此时我们应该使用的是cast()或convert()。

二、两者的对比

相同点：都是进行数据类型转换，实现的功能基本等同

不同点：两者的语法不同，cast(value as type) 、 convert(value,type)

三、所支持的类型如下：

二进制，同带binary前缀的效果 : BINARY

字符型，可带参数 : CHAR()
    
    日期 : DATE
    
    时间: TIME
    
    日期时间型 : DATETIME
    
    浮点数 : DECIMAL
    
    整数 : SIGNED
    
    无符号整数 : UNSIGNED

16.不常使用的数据迁移备份，避免每次都在海量数据中去检索。  
17.提升数据库服务器硬件配置，或者搭建数据库集群。  
18.编程手段防止SQL注入：使用JDBC PreparedStatement按位插入或查询；正则表达式过滤（非法字符串过滤）

## SQL语句中九大尽量进行优化 ##

查询语句的优化是SQL效率优化的一个方式，可以通过优化sql语句来尽量使用已有的索引，避免全表扫描，从而提高查询效率。最近在对项目中的一些sql进行优化，总结整理了一些方法。

**1、在表中建立索引，优先考虑where、group by使用到的字段。**  
**2、尽量避免使用select 星号，返回无用的字段会降低查询效率**  
**3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。**  
**4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。**

SELECT * FROM t WHERE id = 1 OR id = 3

优化方式：可以用union代替or。如下：

SELECT * FROM t WHERE id = 1 UNION SELECT * FROM t WHERE id = 3

（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）

**5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：**

SELECT * FROM t WHERE username LIKE '%li%'

优化方式：尽量在字段后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE 'li%'

**6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：**

SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0

**7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。** 例如：

SELECT * FROM t2 WHERE score/10 = 9
    
    SELECT * FROM t2 WHERE SUBSTR(username,1,2) = 'li'
    
    优化方式：可以将表达式、函数操作移动到等号右侧。如下：
    
    SELECT * FROM t2 WHERE score = 10*9
    
    SELECT * FROM t2 WHERE username LIKE 'li%'

\*\*8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。\*\*如下：

SELECT \* FROM t WHERE 1=1

优化方式：用代码拼装sql时进行判断，没where加where，有where加and。

**9.Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤**