Oracle/MySQL 数据库优化方案/方向

青旅半醒 2022-05-12 04:58 188阅读 0赞

上周老板说要做一个优化数据库方案，上周没做，周一的周会被批了一顿，前两天出了一个草版方案，这里记录一下当没有经验没有方向没人指导的一个DBA如何着手做个数据库优化方案和具体做法。

大方向：

优化无非就：

sql优化

架构优化

硬件性能优化

然而大多数情况下我们不得不面对现实是：架构不能动，不给钱买新机器，只能优化下sql/数据

所以实际操作方式是

sql优化：

1、找出各个用户及其schema的大小

2、找出各个schema中具体table及其table 大小

3、找出执行率最高/最慢的SQL

4、找出index大小及其分析index效率、碎片等

不管是MySQL还是Oracle我的初步计划是先找出能找到的数据，先拿Oracle来开刀：

1、查询用户及其schema所占大小：

SELECT OWNER as "用户名",
           sum(BYTES) / 1024 / 1024 / 1024 as "所有表的大小(GB)"
      FROM DBA_EXTENTS
     WHERE SEGMENT_NAME in (select t2.OBJECT_NAME
                              from dba_objects t2
                             where t2.OBJECT_TYPE = 'TABLE')
     group by OWNER
    -- having sum(BYTES) / 1024 / 1024 / 1024 > 30 --这一句是筛选大于30g的，可以自己按需求修改
     order by 2 desc;

2、查询schema的大小，这里有个坑就是如果有用户分区，记得要查清楚，不要只查table的情况：

select OWNER,
           t.segment_name as "表名",
           t.segment_type as "属性",
           sum(t.bytes / 1024 / 1024) as "表的大小(M)"
      from dba_segments t
     where t.owner in ('') -- 这里放你要查的schema name
       and t.segment_type = 'TABLE'--如果只是查表可以只要table，如果有分区表的话，记得加上他的type一起查
     group by OWNER, t.segment_name, t.segment_type
    -- having sum(t.bytes / 1024 / 1024) > 10 删选大于10M的表，可以自行调整
     order by 4 desc;

表空间对于磁盘的使用详情，比列等等：

SELECT a.tablespace_name "表空间名称",
           total / (1024 * 1024) "表空间大小(M)",
           free / (1024 * 1024) "表空间剩余大小(M)",
           (total - free) / (1024 * 1024) "表空间使用大小(M)",
           total / (1024 * 1024 * 1024) "表空间大小(G)",
           free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
           (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
           round((total - free) / total, 4) * 100 "使用率 %"
      FROM (SELECT tablespace_name, SUM(bytes) free
              FROM dba_free_space
             GROUP BY tablespace_name) a,
           (SELECT tablespace_name, SUM(bytes) total
              FROM dba_data_files
             GROUP BY tablespace_name) b
     WHERE a.tablespace_name = b.tablespace_name;

查看schema是否会自动增长：

SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE as "是否自动增长空间"
      FROM dba_data_files;

3、查看执行次数最多的前多少位SQL：

select *
     from (select s.SQL_TEXT,
            s.EXECUTIONS "执行次数",
            s.PARSING_USER_ID "用户名",
            rank() over(order by EXECUTIONS desc) EXEC_RANK
         from v$sql s
         left join all_users u
          on u.USER_ID = s.PARSING_USER_ID) t
     where exec_rank <= 100;--这里是前100位，你要多少自己调

查看执行最慢的sql，语句我机器上不见了，懒得打，百度吧；

4，查看索引和分析索引，分析索引用analyze index，由于我大表分析索引跑了一两个小时都没出结果，所以我放弃了，具体方法百度

MySQL：

等我做完了再补充；