SybaseASE系统表的应用

SybaseASE系统表的应用

一、 问题背景

在排查江西、湖南地区数字XX系统数据库CPU高和慢SQL问题时,发现系统中有很多简单的全表扫描SQL,原因是一些子表外键没有索引导致。

添加缺失外健索引成为当务之急。数字法院系统有几十个SMD文档,2000多张表,上万个字段,人工检查SMD文档工作量大,效率低。而sybase的sp_help、sp_helpindex又只能查看单个表的索引情况,没有办法检查整个数据库的索引情况。

 

二、 解决思路

查阅sp_help和sp_helpindex的源代码发现,可以通过ase的系统表、系统函数来获取到整个数据的建立索引情况,然后通过系统表关联得出没有建立外健索引的字段。

    用到的系统表有:

1. sysobjects

系统对象表,每个表、视图、存储过程、触发器等数据库对象都在sysobjects中有一行。

主要字段如下:

列名

字段类型

描述

name

varchar(255)

对象名

id

int

对象ID

type

char(2)

对象类型,U:用户表、V:系统表、V:视图

 

2. syscolumns

系统列表,每个表、视图中的每一列和过程的每个参数在syscolumns中都有对应的一行。

主要字段如下:

列名

字段类型

描述

id

int 

所属表或者过程的id

colid

smallint

列id

name

varchar(255)

列名

type

tinyint

存储类型

length

int

物理长度

 

3. sysindexes

系统索引表,每个聚簇索引、非聚簇索引、没有聚簇索引的表、包含text\image列的表都在sysindexes中有对应的一行。注意:该表中没有索引的key信息,即索引建立在表的哪些字段上。

主要字段如下:

列名

字段类型

描述

id

int

所属表ID或索引ID

indid

smallint

表内索引id,0:表、1:页锁表聚簇索引、>1:DOL锁表索引、255:text\image\log。表内索引id的范围是1~254,一张表最多能建立254个索引。

keycnt

smallint

键的个数。取值范围是1~31,复合索引最多能包含31个列。

name

varchar(255)

表名或索引

status

smallint

内部系统状态信息

status2

smallint

内部系统状态信息

status3

smallint

内部系统状态信息

 

4. spt_values

系统内部值表,相当于业务系统中的“单值代码”表。根据type、number 字段 查出其他表中status 对应的name。

主要字段如下:

列名

字段类型

描述

name

varchar(255)

系统内部状态值对应名称

type

char

系统内部状态类型

number

int

系统内部状态值

 

   系统函数有:

1. index_col(obj_name,index_id,key_# [,user_id])

返回表、视图中索引的列名。

参数名

描述

object_name

表名或者视图名

index_id

表sysindex中indid的值

key_#

键值顺序。如sysindex中keycnt为3,说明该索引包含3个列,key_#需要依次传入1、2、3,依次获得第一列、第二列、第三列名称

user_id

object_name的所有者id,可选

 

2. index_colorder(obj_name,index_id,key_# [,userid])

返回索引中列的desc/asc顺序。

参数名

描述

object_name

表名或者视图名

index_id

表sysindex中indid的值

key_#

键值顺序。如sysindex中keycnt为3,说明该索引包含3个列,key_#需要依次传入1、2、3,依次获得第一列、第二列、第三列名称

user_id

object_name的所有者id,可选

三、 解决步骤

第一步:创建表T_SYS_TABLE_INDEX 用于存储库中所有表的索引情况,主要字段如下:

列名

类型

描述

table_name

varchar(255)

表名

table_id

int

表id

index_name

varchar(255)

索引名称

index_keys

varchar(1024)

索引列

index_descriptions

varchar(68)

索引描述

index_created

datetime

索引创建时间

 

 

第二步: 创建sp_genIndexInfo存储过程,收集单个表的索引信息。

  主要过程为:

1. 循环获取表的所有索引id

select @indid = min(indid) from sysindexes where id = object_id(@objname) and indid > 0 and indid < 255

 

while @indid is not NULL

begin

    ----------------------------

    --获取单个索引的索引键值逻辑

    ----------------------------

    select @lastindid = @indid

    select @indid = NULL

    select @indid = min(indid) from sysindexes where id = object_id(@objname) and indid > @lastindid and indid < 255

END

 

2. 获取单个索引的索引键值逻辑

while @i <= 31

    begin

        select @thiskey = index_col(@objname, @indid, @i)

        if (@thiskey is NULL) 

        begin

            goto keysdone

        end

        if @i > 1

        begin

            select @keys = @keys + ", " 

        end

        select @keys = @keys + @thiskey

        select @sorder = index_colorder(@objname, @indid, @i)

        if (@sorder = "DESC")

            select @keys = @keys + " " + @sorder

        select @i = @i + 1

    end

    keysdone:

        set nocount off

 

3. 将索引信息存入T_SYS_TABLE_INDEX表

insert into T_SYS_TABLE_INDEX

        select @objname,object_id(@objname),name, @keys, @inddesc, maxrowsperpage, fill_factor,

            isnull(res_page_gap,0), crdate, 

            case when (status3 & 8 = 8) then "Local Index"

                 else "Global Index"

            end

            from sysindexes where id = object_id(@objname) and indid = @indid

 

第三步:循环获取当前库中每一张表的索引信息

DECLARE @table_name VARCHAR(600)

DECLARE @table_id INT

DECLARE @last_table_id INT 

select @table_id = min(id) from sysobjects where  type = 'U'

while @table_id is not NULL

BEGIN

  SELECT @table_name = name FROM sysobjects WHERE type ='U' AND id = @table_id

  EXEC sp_genIndexInfo @table_name

  select @last_table_id = @table_id

  select @table_id = NULL

  select @table_id = min(id),@table_name=name from sysobjects where id > @last_table_id and type = 'U'

END

 

第四步:查询未建索引的表和字段

SELECT db_name() AS dbname,obj1.name AS table_name,cols.name AS col_name 

FROM syscolumns cols LEFT JOIN sysobjects obj1 ON cols.id = obj1.id 

WHERE obj1.type = 'U' 

AND cols.name like 'C_BH_%' AND cols.length = 32

AND NOT EXISTS (

  SELECT 1 FROM  T_SYS_TABLE_INDEX tis WHERE tis.table_id = obj1.id AND charindex(cols.name,tis.index_keys) >0

)

ORDER BY obj1.name desc

 

四、 结果展示

1、 全库已建索引信息

 

2、 全库未建外键索引信息

 

通过程序或脚本可以将未建索引的外键字段批量生成建立索引脚本。

五、 结论

Sybase ASE的系统表相当于其他DBMS的数据字典,记录和展示了数据库的元数据信息,利用这些信息DBA和程序员可以做很多的批量处理工作,减少手工劳动,提高效率!

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页