数据库优化常考概念

数据库优化

慢查询的原因

• 如果SQL语句只是偶尔执行很慢，可能是执行的时候遇到了锁，也可能是redo log日志写满了，要将redo log中的数据同步到磁盘中去。
• 如果SQL语句一直都很慢，可能是字段上没有索引或者字段有索引但是没用上索引。

慢查询日志

用于记录执行时间超过某个临界值的SQL语句的日志。

相关参数：

• slow_query_log：是否开启慢日志查询，1表示开启，0表示关闭。
• slow_query_log_file：MySQL数据库慢查询日志存储路径。
• long_query_time：慢查询阈值，当SQL语句查询时间大于阈值，会被记录在日志上。
• log_queries_not_using_indexes：未使用索引的查询会被记录到慢查询日志中。
• log_output：日志存储方式。“FILE”表示将日志存入文件。“TABLE”表示将日志存入数据库。

慢查询日志设置操作（临时设置，重启失效）

show variables like '%quer%'; 	#查询慢查询日志的开启状态和慢查询日志储存的位置
set global slow_query_log = on;	#开启慢查询日志
set global long_query_time = 1;	#设置慢查询时间阈值
set global  log_output = file;	#设置日志保存方式

执行计划

• 使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是
  如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

• 方法：Explain+SQL语句

• 可以得到

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7aMb2tYQ-1633942296751)(D:\Users\tpytpytpy\Desktop\数据库常考知识点.assets\image-20211011164022204-16339416231752.png)]

  • 表的读取顺序
  • 哪些索引可以使用，哪些索引被实际使用
  • 数据读取操作的操作类型
  • 表之间的引用
  • 每张表的物理扫描行数

大表如何优化？

• 限定数据的范围：避免不带任何限制数据范围条件的查询语句。
• 读写分离：主库负责写，从库负责读。
• 垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。
• 水平分表：在同一个数据库内，把一个表的数据按照一定规则拆分到多个表中。
• 对单表进行优化：对表中的字段、索引、查询SQL进行优化。
• 添加缓存

什么是垂直分表、垂直分库、水平分表、水平分库？

垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。

垂直分表的优势：

• 避免IO竞争减少锁表的概率。因为大的字段效率更低，第一数据量大，需要的读取时间长。第二，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多。
• 可以更好地提升热门数据的查询效率。

垂直分库：按照业务对表进行分类，部署到不同的数据库上面，不同的数据库可以放到不同的服务器上面。

垂直分库的优势：

• 降低业务中的耦合，方便对不同的业务进行分级管理。
• 可以提升IO、数据库连接数、解决单机硬件资源的瓶颈问题。

垂直拆分（分库、分表）的缺点：

• 主键出现冗余，需要管理冗余列
• 事务的处理变得复杂
• 仍然存在单表数据量过大的问题

水平分表：在同一个数据库内，把同一个表的数据按照一定规则拆分到多个表中。

水平分表的优势：

• 解决了单表数据量过大的问题
• 避免IO竞争并减少锁表的概率

水平分库：把同一个表的数据按照一定规则拆分到不同的数据库中，不同的数据库可以放到不同的服务器上。

水平分库的优势：

• 解决了单库大数据量的瓶颈问题
• IO冲突减少，锁的竞争减少，某个数据库出现问题不影响其他数据库（可用性），提高了系统的稳定性和可用性

水平拆分（分表、分库）的缺点：

• 分片事务一致性难以解决
• 跨节点JOIN性能差，逻辑会变得复杂
• 数据扩展难度大，不易维护

在系统设计时应根据业务耦合来确定垂直分库和垂直分表的方案，在数据访问压力不是特别大时应考虑缓存、读写分离等方法，若数据量很大，或持续增长可考虑水平分库分表，水平拆分所涉及的逻辑比较复杂，常见的方案有客户端架构和恶代理架构。

大表查询优化

从减少数据访问方面考虑：

• 正确使用索引，尽量做到索引覆盖
• 优化SQL执行计划

从返回更少的数据方面考虑：

• 数据分页处理
• 只返回需要的字段

从减少服务器CPU开销方面考虑：

• 合理使用排序
• 减少比较的操作
• 复杂运算在客户端处理

从增加资源方面考虑：

• 客户端多进程并行访问
• 数据库并行处理

大数据插入优化

• 一条语句插入多行数据
• 先删除过多的索引
• 关闭自动提交， 录入数据完成后再整体提交一次
• 多线程灌数

如何优化长难的查询语句？

• 将一个大的查询分解为多个小的查询
• 分解关联查询，使缓存的效率更高

如何优化WHERE子句

• 不要在where子句中使用!=和<>进行不等于判断，这样会导致放弃索引进行全表扫描。
• 不要在where子句中使用null或空值判断，尽量设置字段为not null。
• 尽量使用union all代替or
• 在where和order by涉及的列建立索引
• 尽量减少使用in或者not in，会进行全表扫描
• 在where子句中使用参数会导致全表扫描
• 避免在where子句中对字段及进行表达式或者函数操作会导致存储引擎放弃索引进而全表扫描

如何优化LIMIT分页？

• 在LIMIT偏移量较大的时候，查询效率会变低，可以记录每次取出的最大ID，下次查询时可以利用ID进行查询
• 建立复合索引

主从复制

读写分离