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和程序員可以做很多的批量處理工作，減少手工勞動，提高效率！

轉載于:https://www.cnblogs.com/wangzhen3798/p/6924117.html

總結

以上是生活随笔為你收集整理的SybaseASE系统表的应用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。