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