SybaseASE系统表的应用
SybaseASE系统表的应用
一、问题背景
在排查江西、湖南地区数字法院系统数据库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和程序员可以做很多的批量处理工作，减少手工劳动，提高效率！
