如何对数据库进行调优,调优数据库

本文对 如何调优数据库 进行了方法总结 本文主要对于Mysql进行调优

数据库的工作原理

首先要想调优数据库 就得了解它的工作原理

实现逻辑

看图可以将数据库的工作原理分为三大块

客户端 负责对数据的发送与接收 客户端是由客户所提供 因此我们无法干预对客户端的优化

服务层也可以叫核心层 里面包含了缓存 解析 处理 优化 执行等 这点是我们进行优化的重点

存储引擎 负责数据存储和提取 是提供api进行建立通信

看实现步骤可以分为六大步骤

0.建立客户端与服务层的连接并发送sql语句

1.查看当前缓存，如果命中缓存 那么会返回缓存中的结课 反之进行下一步

2.如没有命中缓存 mysql将会对sql语句进行解析 处理 生成新的解析

3.新的解析会发送给优化器 优化器生成对应的执行计划
(优化器是对执行成本进行优化并非执行时间)

4.mysql根据执行计划 调用存储引擎中的API来执行查询

5.将查询结果返回给客户端 同时缓存查询结果

优化分析

知道了sql语句的实现过程 所以我们可进行优化的部分大概可以分为：

0.sql语句的优化

1.设计缓存

2.数据库设计

实现优化

sql语句的优化

0.尽量避免使用子查询影响查询效率 而用join

1.尽量避免使用NOT ,<>,这些会导致索引失效,导致全表扫描 影响查询效率

2.尽量避免使用模糊查询前缀%号、双百分号、_模糊查询有% 不能利用索引 影响查询效率

3.避免对查询列的操作

操作包含:数据库函数,计算机表达式,这样会导致全表扫描

4…慎用distinct关键字

distinct在查询一个字段或者很少字段的情况下使用，会避免重复数据的出现，给查询带来优化效果。
但是查询字段很多的情况下使用，则会大大降低查询效率。

5.增加查询范围的限制

避免select * 的存在，使用具体的列代替*，避免多余的列
使用where限定具体要查询的数据，避免多余的行
使用top，distinct关键字减少多余重复的行

6.合理使用in与exists

exists是循环的方式,外表记录数代表循环的次数,外表的记录少,适合用它.
in先执行子查询,子查询去重之后,然后在执行主查询,子查询返回结果越少,越适合这种方式.
如果两张表数据一样大,那么用in和exists差别不大,但是如果不一样大,子查询小的用in,主查询小的用exists.

7.规范所有关键字的书写,select ,update,delete,要么全大写要不全小写

8避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描
is null, is not null 也无法使用索引，在实际中尽量不要使用null

9.读取适当的记录LIMIT M,N，而不要读多余的记录

10、避免数据类型不一致

11、分组统计可以禁止排序sort，总和查询可以禁止排重用union all 避免索引失效

12.字符串不加单引号索引失效

13.最佳左前缀法则

14.减少使用union关键字

15.使用group by时,不会使用索引,会进行全表扫描,如果要求效率比较高的话,可以进行禁止排序,order by null

16.如果使用组合索引,第一个可以不用和第二个一起作为条件查询,如果只使用第二个则不会使用索引查询

17.如果使用or的话,那么or中所有的条件必须加索引,否则也会全表扫描

以上方法对于场景的不同 所实现的有所差异 请根据自己的场景进行使用

使用缓存

默认情况下mysql的查询缓存是被打开的，

适用场景
对于频繁更新的表，缓存并不合适
对于一些不变的数据且有大量相同sql查询的表 缓存可以大大提高查询的性能

如要使用缓存 请避免出现下列情况

0.如果查询有不确定的数据，如like now()、current_date()，那么查询完成后结果都不会被缓存在
1.在命中前 mysql并不会解析sql语句 空格 大小写 注释等都会导致缓存认为语句并不相同
2.表的结构或数据发生改变时，查询缓存中的数据不再有效，查询缓存值的相关条目将被清空
3.INSERT、UPDATE、 DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE会导致缓存数据失效

请注意
查询缓存会生成碎片
查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消
查询缓存非常大或者碎片很多 这个操作就可能带来很大的系统消耗 合理控制缓存空间大小

数据库设计

三范式：原子性，保证唯一性，不要有亢余数据
在表的设计方面可可以大大提高查询效率

添加索引

索引：帮助快速高效查询数据的一种数据结构。

具体使用索引的注意事项 在 sql语句的优化 中有描述

索引优点 : 提高查询效率
索引缺点 : 增加,删除慢,因为索引文件需要更新,增加内存

进行分表

分表有两种分割方式，一种垂直分割另一种水平分割。

垂直分隔

垂直拆分对数据表列的拆分，就是将一张大表分为多张表。 垂直分割一般用于拆分大字段和访问频率低的字段
使用的较少

垂直拆分的优点： 可以使得行数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。

垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂。

水平分隔
水平拆分是对数据进行拆分 表的行数上百行时 查询就会变慢，这时可以把一张的表的数据拆成多张表来存放。水平分隔分的比较均匀 使每个表数据量差不多

水平分隔的优点：单表数据量较少 分开多张表可以进行并发查询 可以支持非常多的数据量

水平分隔的缺点： 在多表连接时 join操作复杂 事务难以解决

今天的文章就分享到这里，如有问题还请批评指正。