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和程序员可以做很多的批量处理工作，减少手工劳动，提高效率！