廊坊网站建设服务,邢台市路桥建设公司网站,网站整站开发项目亮点,河南建设信息网站1#xff0c;数据库减负思路
缓存 页面静态化 实时性不高的数据#xff1b;数据库优化sql优化 表结构优化 数据库分区分表 合并数据库操作#xff0c;将多次操作合并成一条sql执行。热点数据分离 主表只保存活跃数据。数据库读写分离
2#xff0c;执行计划
explain只…1数据库减负思路
缓存 页面静态化 实时性不高的数据数据库优化sql优化 表结构优化 数据库分区分表 合并数据库操作将多次操作合并成一条sql执行。热点数据分离 主表只保存活跃数据。数据库读写分离
2执行计划
explain只能分析SELECT、INSERT、DELETE、UPDATE和DECLARE…CURSOR命令。
#查看执行计划。也可以使用navicat的解释功能查看。
--ANALYZE 缺省表示只计划加上表示查看实际执行成本。
explain [ANALYZE] sql语句 结果说明
QUERY PLAN
Index Scan using tenk1_unique1 on tenk1 (cost0.00..10.01 rows1 width244)
--Index 使用索引
--cost启动开销..总开销。不过事实总开销可能会低一点带有 LIMIT 子句的查询将会在 Limit 规划节点的输入节点里很快停止。
--rows:预计输出的行数
--Filter过滤条件Index Cond: (unique1 3) --从索引中检索出的行的过滤器Filter: (stringu1 xxx::name)QUERY PLAN
--嵌套循环Nested Loop (cost2.37..553.11 rows106 width488) - Bitmap Heap Scan on tenk1 t1 (cost2.37..232.35 rows106 width244) Recheck Cond: (unique1 100)--位图索引- Bitmap Index Scan on tenk1_unique1 (cost0.00..2.37 rows106 width0)Index Cond: (unique1 100)- Index Scan using tenk2_unique2 on tenk2 t2 (cost0.00..3.01 rows1 width244)Index Cond: (outer.unique2 t2.unique2)QUERY PLAN
--内存 Hash 表Hash Join (cost232.61..741.67 rows106 width488)Hash Cond: (outer.unique2 inner.unique2)- Seq Scan on tenk2 t2 (cost0.00..458.00 rows10000 width244)- Hash (cost232.35..232.35 rows106 width244)- Bitmap Heap Scan on tenk1 t1 (cost2.37..232.35 rows106 width244)Recheck Cond: (unique1 100)- Bitmap Index Scan on tenk1_unique1 (cost0.00..2.37 rows106 width0)Index Cond: (unique1 100)1多表连接查询
①Nest Loop Join嵌套循环连接
1场景
适合两个表的数据量都比较少的情况(最简单的 table join 方式)。
外表为小表且过滤后的数据量较少。内表的关联列上有高效索引(主键或者唯一性索引)。
2举例
# 内表t4)被外表(t5)驱动。外表返回的每一行都要在内表中检索找到与它匹配的行因此整个查询返回的结果集不能太大大于1 万不适合
select t4.*,t5.*from tmp_t4 t4,tmp_t5 t5where 11and t4.id t5.idand t4.id 1;相当于for循环
for(t4.data in tmp_t4) { t5.data in tmp_t5 on t5.data t4.data}②Hash JOIN哈希、散列连接
1场景
针对那些没有索引或者其中任一个有索引的大表。 哈希连接只能应用于等值连接(如WHERE A.COL3 B.COL4)、非等值连接(WHERE A.COL3 B.COL4)、外连接(WHERE A.COL3 B.COL4())。
2操作步骤
优化器使用两个表中较小的表或数据源利用连接键在内存中建立散列表然后扫描较大的表并探测散列表找出与散列表匹配的行。 这种方式适用于较小的表完全可以放于内存中的情况这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存这时优化器会将它分割成若干不同的分区不能放入内存的部分就把该分区写入磁盘的临时段此时要有较大的临时段从而尽量提高I/O 的性能。
③Sort Merge JOIN
1场景
通常Hash JOIN的性能都优于Merge JOIN对于那些连接列上有索引的表已排好序Merge JOIN性能会优于Hash JOIN。
2操作步骤
mrege join的性能开销几乎都在前两步。
对连接的每个表做全表扫描table access full;对table access full的结果进行排序。进行merge join对排序结果进行合并。
在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下merge join会比nested loops性能更佳。当表特别小或特别巨大的时候实行全表访问可能会比索引范围扫描更有效。
3SQL优化
对于重复的代码逻辑sql执行速度远远大于代码逻辑。 但是由于sql难以测试、难以复用、难以加工变量对于复杂的逻辑不建议用在sql中。代码可以分成模块、逻辑独立、方便测试。
sql优化的思路有两种 一是 The fastest way to do something is don’t do it 去掉无用的步骤 二是优化算法如让sql走更优的执行计划上。
1概念
1引擎优化策略 基于规则的优化RBORule Based Optimizer 这是一种比较老的技术简单说基于规则的优化就是当数据库执行一条query语句的时候必须遵循预先定义好的一系列规则比如oracle的15条规则排名越靠前的执行引擎认为效率越高来确定执行过程它不关心访问表的数据分布情况仅仅凭借规则经验来确定所以说是一种比较粗放的优化策略。 基于代价的优化CBOCost Based Optimizer 基于代价的优化的产生就是为了解决上面RBO的弊端让执行引擎依据预先存储到数据库中表的一些实时更新的统计信息来选择出最优代价最小的执行计划来执行query语句CBO会根据统计信息来生成一组可能被使用到的执行计划进而估算出每个计划的代价从而选择出代价最小的交给执行器去执行其中表的统计信息一般会有表大小行数单行长度单列数据分布情况索引情况等等。 总结 基于规则的优化器更像是一个经验丰富熟知各条路段的老司机大部分情况可以根据自己的经验来判断走哪条路可以更快的到达目的地而基于代价的优化更像手机里面的地图它可以选择出许多不同的路径根据实时的路况信息综合考虑路程长度交通状况来挑出最优的路径。
2SQL高性能归约
1查询
禁止使用select * 用SELECT 字段列表 查询同一列操作用in代替or。 in 操作可以更有效的利用索引or 大多数情况下很少能利用到索引。
2表连结
联结的表越多效率越低一般5张 SQL本身不限制联结表的数目但DBMS有最大数目限制。join查询一般比子查询快小表驱动大表 合适索引给大表的join条件建立索引 合理运用连接条件(大表改成二次查询表通过where语句过滤大部分数据)基本上连接可以解决绝大部分问题。排序字段必须是驱动表字段不然可能用不到索引。
3数据库层面
减少同数据库的交互次数合并多个相同操作。
4表设计
表数据量的大小500w分区表 分区表由多个文件存储逻辑上表现为一个表。 跨区查询效率更低。尽量做到冷热数据分离减少表的宽度。禁止在表中建立预留字段 预留字段的命名很难做到见名识义 预留字段无法确认存储的数据类型所以无法选择合适的类型 对预留字段类型的修改会对表进行锁定。禁止在数据库中存储图片文件等大的二进制数据优先选择符合存储需要的最小的数据类型。 列的字段越大建立索引时所需要的空间也就越大。必须有主键、update_time;
4配置优化
pg中与内存有关的配置参数
1shared_buffers共享缓存区
i工作原理
shared_buffers是一个8KB的数组postgres在从磁盘中查询数据前会先查找shared_buffers的页如果命中就直接返回避免从磁盘查询。 多个进程通过共享内存技术来共享缓存中的数据。 shared_buffers存储什么 表数据 索引索引也存储在8K块中 执行计划存储基于会话的执行计划会话结束缓存的计划也就被丢弃。 什么时候加载shared_buffers 1在访问数据时数据会先加载到os缓存然后再加载到shared_buffers这个加载过程可能是一些查询也可以使用pg_prewarm预热缓存。 2当然也可能同时存在os和shared_buffers两份一样的缓存双缓存。 3查找到的时候会先在shared_buffers查找是否有缓存如果没有再到os缓存查找最后再从磁盘获取。 4os缓存使用简单的LRU移除最近最久未使用的缓存而数据库采用的优化的时钟扫描即缓存使用频率高的会被保存低的被移除。
ii优化策略
提高shared_buffers增加缓存命中率提高查询效率。 同时为了避免Double Buffering问题将shared_buffers设置较小更多的内存留给文件系统使用。 【Double Buffering双缓存】问题 pg的数据文件都存储在文件系统中os的文件系统也有缓存这导致pg的数据库副本可能同时存在于共享内存和文件系统中造成内存利用率低的问题。 Oracle中通过设置Birect I/O避免双缓存问题但pg不支持。 shared_buffers的大小不应该超过内存的1/4。 shared_buffers设置的合理范围 1windows服务器有用范围是64MB到512MB默认128MB 2linux服务器建议设置为25%亚马逊服务器设置为75%避免双缓存数据会存储在os和shared_buffers两份 os缓存的重要性数据写入时从内存到磁盘这个页面就会被标记为脏页一旦被标记为脏页它就会被刷新到os缓存然后写入磁盘。所以如果os高速缓存大小较小则它不能重新排序写入并优化io这对于繁重的写入来说非常致命因此os的缓存大小也非常重要。给予shared_buffers太大或太小都会损害性能。 shared_buffers调整策略
2 work_mem
为每个进程单独分配的内存主要用于group by, sort, hash agg, hash join 等操作。 注意work_mem是每次分配的内存加入有M个并发进程每个进程有N个HASH操作那么需要分配的内存为 MNwork_mem。因此work_mem不宜设置太大通常保持默认的4MB即可如果设置的太大超过256MB很容易因为瞬间的大并发操作导致oom。
3maintenance_work_mem
为每个进程单独分配的内存主要进行维护操作时需要的内存如VACUUM、create index、ALTER TABLE ADD FOREIGN KEY等操作需要的内存。
4autovacuum_work_mem
pg9.4版本新增参数。 9.4之后AutoVacuum的worker进程分配的内存由参数autovacuum_work_mem控制手动Vacuum时分配的内存由maintenance_work_mem 控制。9.4之前都用maintenance_work_mem 参数。 默认值为-1表示与maintenance_work_mem 一样。 vacuum 大小 autovacuum_max_workers * autovacuum_work_mem
5temp_buffers临时表缓存
为每个不同的进程单独分配的内存不在共享内存中默认为8MB。
6wal_buffersWAL日志缓存大小
默认为-1表示根据shared_buffer的大小自动设置。
7huge_pages是否使用大页
默认值为try表示尽量使用大页。若os未开启大页不使用大页内存不影响数据库正常使用。
8effective_cache_sizesql执行中的实际磁盘缓存
与具体内存分配无关
5sql审计
相关配置
参数调整说明log_min_duration_statementsql审计记录的标准超过该时长的sql将被记录到日志文件。默认为-1不记录超时sql。log_statementnone默认不记录all-记录所有语句ddl-记录所有数据定义语句mod记录所有ddl和数据修改语句log_min_error_statement控制日志中是否记录导致数据库出现错误的SQL语句。默认为error
6排查sql
-- 查看表结构
SELECT column_name,data_type FROM information_schema.columns WHERE table_name 表名; -- 查看慢查询日志是否开启
SHOW log_min_duration_statement; -- 设置慢查询日志
ALTER DATABASE test SET log_min_duration_statement TO 10000; --慢sql 添加\watch 1监控
select query,wait_event_type,wait_event from pg_stat_activity
where wait_event is not null and now()-state_changeinterval 5 second;--长wait事件
select query,wait_event_type,wait_event from pg_stat_activity where stateactive and wait_event is not null and now()-state_changeinterval 5 second;-- 查找经常被扫描的大型表
SELECT schemaname, relname, seq_scan, seq_tup_read, idx_scan, seq_tup_read / seq_scan AS avg FROM pg_stat_user_tables WHERE seq_scan 0 ORDER BY seq_tup_read DESC LIMIT 20; -- 跟踪 vacuum 进度
SELECT * FROM pg_stat_progress_vacuum ;7pg统计收集器
比较耗费性能默认关闭可在postgresql.conf进行配置 stats_start_collector true 。
这些表的具体说明参见华为 openGauss (GaussDB) v2.1 使用手册
1pg_stat_activity
常见应用 -- 展示在数据库中当前正在执行多少查询
SELECTdatname,COUNT ( * ) AS OPEN,COUNT ( * ) FILTER ( WHERE STATE active ) AS active,COUNT ( * ) FILTER ( WHERE STATE idle ) AS idle,COUNT ( * ) FILTER ( WHERE STATE idle in transaction ) AS idle_in_trans
FROMpg_stat_activity
GROUP BYROLLUP ( 1 )-- 查看事务已经打开了多久
SELECTpid,xact_start,now( ) - xact_start AS duration
FROMpg_stat_activity
WHERESTATE LIKE%transaction%
ORDER BY3 DESC;-- 检查是否有长查询运行
SELECT now() - query_start AS duration, datname, query FROM pg_stat_activity WHERE state active ORDER BY 1 DESC; --查询服务进程PID: 通过count(*)获取当前连接数
select pid,usename,client_addr,client_port from pg_stat_activity;
