xwiki管理指南-数据库管理

快来打我* 2022-05-24 04:52 248阅读 0赞

XWiki默认使用一个数据库来存储其数据。以下将提供一些小贴士和技巧，以管理和调整你的数据库设置。对于安装，请查看[安装指南][Link 1]，其中包含了多个数据库的安装。

# 索引 #

当你的XWiki实例运行时已经累计很多文档（XWiki每个页面算一个document）时， 为了提高性能，需要在你的数据库(经过MySQL 5.0测试)运行以下脚本创建索引：

## XWiki Enterprise 4.3之后版本的索引 ##

尽管我们配置XWiki让Hibernate默认创建索引，但是有一定的局限性。因为有些索引不能用于自动生成超过255个字符的string列（[MySQL有255个字符的限制][MySQL_255] - 参见[InnoDB限制][InnoDB]）。因此，目前这些索引需要手动创建。

注意，以下的索引可以自动创建，应在XWiki的未来版本被添加，因此不需要手动创建：

create index xws\_number on xwikistatsdoc (XWS\_NUMBER);  
create index xws\_classname on xwikistatsdoc (XWS\_CLASSNAME);  
create index xwr\_number on xwikistatsreferer (XWR\_NUMBER);  
create index xwr\_classname on xwikistatsreferer (XWR\_CLASSNAME);  
create index xwv\_classname on xwikistatsvisit (XWV\_CLASSNAME);  
create index xwv\_number on xwikistatsvisit (XWV\_NUMBER);  
create index xda\_docid1 on xwikiattrecyclebin (xda\_docid);

create index xwl\_value on xwikilargestrings (xwl\_value(50));  
create index xwd\_parent on xwikidoc (xwd\_parent(50));  
create index xwd\_class\_xml on xwikidoc (xwd\_class\_xml(20));  
create index xws\_number on xwikistatsdoc (XWS\_NUMBER);  
create index xws\_classname on xwikistatsdoc (XWS\_CLASSNAME);  
create index xwr\_number on xwikistatsreferer (XWR\_NUMBER);  
create index xwr\_classname on xwikistatsreferer (XWR\_CLASSNAME);  
create index xwr\_referer on xwikistatsreferer (XWR\_REFERER(50));  
create index xwv\_user\_agent on xwikistatsvisit (XWV\_USER\_AGENT(255));  
create index xwv\_cookie on xwikistatsvisit (XWV\_COOKIE(255));  
create index xwv\_classname on xwikistatsvisit (XWV\_CLASSNAME);  
create index xwv\_number on xwikistatsvisit (XWV\_NUMBER);  
create index ase\_page\_date on  activitystream\_events (ase\_page, ase\_date);  
create index xda\_docid1 on xwikiattrecyclebin (xda\_docid);  
create index ase\_param1 on activitystream\_events (ase\_param1(200));  
create index ase\_param2 on activitystream\_events (ase\_param2(200));  
create index ase\_param3 on activitystream\_events (ase\_param3(200));  
create index ase\_param4 on activitystream\_events (ase\_param4(200));  
create index ase\_param5 on activitystream\_events (ase\_param5(200));

## XWiki Enterprise 3.2之后版本的索引 ##

create index xwl\_value on xwikilargestrings (xwl\_value(50));  
create index xwd\_parent on xwikidoc (xwd\_parent(50));  
create index xwd\_class\_xml on xwikidoc (xwd\_class\_xml(20));  
create index xws\_number on xwikistatsdoc (XWS\_NUMBER);  
create index xws\_classname on xwikistatsdoc (XWS\_CLASSNAME);  
create index xwr\_number on xwikistatsreferer (XWR\_NUMBER);  
create index xwr\_classname on xwikistatsreferer (XWR\_CLASSNAME);  
create index xwr\_referer on xwikistatsreferer (XWR\_REFERER(50));  
create index xwv\_user\_agent on xwikistatsvisit (XWV\_USER\_AGENT(255));  
create index xwv\_cookie on xwikistatsvisit (XWV\_COOKIE(255));  
create index xwv\_classname on xwikistatsvisit (XWV\_CLASSNAME);  
create index xwv\_number on xwikistatsvisit (XWV\_NUMBER);  
create index ase\_requestid on  activitystream\_events (ase\_requestid(200));  
create index ase\_page\_date on  activitystream\_events (ase\_page, ase\_date);  
create index xda\_docid1 on xwikiattrecyclebin (xda\_docid);  
create index ase\_param1 on activitystream\_events (ase\_param1(200));  
create index ase\_param2 on activitystream\_events (ase\_param2(200));  
create index ase\_param3 on activitystream\_events (ase\_param3(200));  
create index ase\_param4 on activitystream\_events (ase\_param4(200));  
create index ase\_param5 on activitystream\_events (ase\_param5(200));

*  XWiki Enterprise 3.2开始大多数索引可以被自动创建，只有上面列出的需要被创建。
 *  对于旧版本的索引都必须手动创建。在下面，你可以看到旧版本中需要创建所有索引列表。现在有一个[application][]可以自动运行此脚本。

## XWiki Enterprise 3.2之前版本的索引 ##

create index DOC\_SPACE on xwikidoc (XWD\_WEB);  
create index DOC\_NAME on xwikidoc (XWD\_NAME);  
create index DOC\_FULLNAME on xwikidoc (XWD\_FULLNAME);  
create index DOC\_TITLE on xwikidoc (XWD\_TITLE);  
create index DOC\_PARENT on xwikidoc (XWD\_PARENT(50));  
create index DOC\_CREATION\_DATE on xwikidoc (XWD\_CREATION\_DATE);  
create index DOC\_DATE on xwikidoc (XWD\_DATE);  
create index DOC\_CONTENT\_UPDATE\_DATE on xwikidoc (XWD\_CONTENT\_UPDATE\_DATE);  
create index DOC\_CREATOR on xwikidoc (XWD\_CREATOR);  
create index DOC\_AUTHOR on xwikidoc (XWD\_AUTHOR);  
create index DOC\_CONTENT\_AUTHOR on xwikidoc (XWD\_CONTENT\_AUTHOR);  
create index DOC\_LANGUAGE on xwikidoc (XWD\_LANGUAGE);  
create index DOC\_DEFAULT\_LANGUAGE on xwikidoc (XWD\_DEFAULT\_LANGUAGE);  
create index DOC\_CLASS\_XML on xwikidoc (XWD\_CLASS\_XML(20));  
create index DOC\_MINOREDIT on xwikidoc (XWD\_MINOREDIT);  
create index DOC\_HIDDEN on xwikidoc (XWD\_HIDDEN);  
create index OBJ\_NAME on xwikiobjects (XWO\_NAME);  
create index OBJ\_CLASSNAME on xwikiobjects (XWO\_CLASSNAME);  
create index OBJ\_NUMBER on xwikiobjects (XWO\_NUMBER);  
  
create index XWINT\_NAME on xwikiintegers (XWI\_NAME);  
create index XWINT\_VALUE on xwikiintegers (XWI\_VALUE);  
create index XWLONG\_NAME on xwikilongs (XWL\_NAME);  
create index XWLONG\_VALUE on xwikilongs (XWL\_VALUE);  
create index XWFLOAT\_NAME on xwikifloats (XWF\_NAME);  
create index XWFLOAT\_VALUE on xwikifloats (XWF\_VALUE);  
create index XWDOUBLE\_NAME on xwikidoubles (XWD\_NAME);  
create index XWDOUBLE\_VALUE on xwikidoubles (XWD\_VALUE);  
create index XWDATE\_NAME on xwikidates (XWS\_NAME);  
create index XWDATE\_VALUE on xwikidates (XWS\_VALUE);  
create index XWSTR\_NAME on xwikistrings (XWS\_NAME);  
create index XWSTR\_VALUE on xwikistrings (XWS\_VALUE);  
create index XWLS\_NAME on xwikilargestrings (XWL\_NAME);  
create index XWLS\_VALUE on xwikilargestrings (XWL\_VALUE(50));  
  
create index xwr\_isdiff on xwikircs(xwr\_isdiff);  
create index xws\_name on xwikistatsdoc (XWS\_NAME);  
create index xws\_number on xwikistatsdoc (XWS\_NUMBER);  
create index xws\_classname on xwikistatsdoc (XWS\_CLASSNAME);  
create index xws\_action on xwikistatsdoc (XWS\_ACTION);  
create index xws\_page\_views on xwikistatsdoc (XWS\_PAGE\_VIEWS);  
create index xws\_unique\_visitors on xwikistatsdoc (XWS\_UNIQUE\_VISITORS);  
create index xws\_period on xwikistatsdoc (XWS\_PERIOD);  
create index xws\_visits on xwikistatsdoc (XWS\_VISITS);  
create index xwr\_number on xwikistatsreferer (XWR\_NUMBER);  
create index xwr\_name on xwikistatsreferer (XWR\_NAME);  
create index xwr\_classname on xwikistatsreferer (XWR\_CLASSNAME);  
create index xwr\_referer on xwikistatsreferer (XWR\_REFERER(50));  
create index xwr\_page\_views on xwikistatsreferer (XWR\_PAGE\_VIEWS);  
create index xwr\_period on xwikistatsreferer (XWR\_PERIOD);  
create index xwv\_start\_date on xwikistatsvisit (XWV\_START\_DATE);  
create index xwv\_name on xwikistatsvisit (XWV\_NAME);  
create index xwv\_page\_views on xwikistatsvisit (XWV\_PAGE\_VIEWS);  
create index xwv\_page\_saves on xwikistatsvisit (XWV\_PAGE\_SAVES);  
create index xwv\_downloads on xwikistatsvisit (XWV\_DOWNLOADS);  
create index xwv\_end\_date on xwikistatsvisit (XWV\_END\_DATE);  
create index xwv\_ip on xwikistatsvisit (XWV\_IP);  
create index xwv\_user\_agent on xwikistatsvisit (XWV\_USER\_AGENT(255));  
create index xwv\_cookie on xwikistatsvisit (XWV\_COOKIE(255));  
create index xwv\_unique\_id on xwikistatsvisit (XWV\_UNIQUE\_ID);  
create index xwv\_classname on xwikistatsvisit (XWV\_CLASSNAME);  
create index xwv\_number on xwikistatsvisit (XWV\_NUMBER);  
  
create index xdd\_fullname1 on xwikirecyclebin (xdd\_fullname);  
create index xdd\_language on xwikirecyclebin (xdd\_language);  
create index xdd\_date on xwikirecyclebin (xdd\_date);  
create index xdd\_deleter on xwikirecyclebin (xdd\_deleter);  
create index xda\_docid1 on xwikiattrecyclebin (xda\_docid);  
create index xda\_doc\_name on xwikiattrecyclebin (xda\_doc\_name);  
create index xda\_filename on xwikiattrecyclebin (xda\_filename);  
create index xda\_date on xwikiattrecyclebin (xda\_date);  
create index xda\_deleter on xwikiattrecyclebin (xda\_deleter);  
  
create index ase\_requestid on  activitystream\_events (ase\_requestid(200));  
create index ase\_stream on  activitystream\_events (ase\_stream);  
create index ase\_date on  activitystream\_events (ase\_date);  
create index ase\_type on  activitystream\_events (ase\_type);  
create index ase\_application on  activitystream\_events (ase\_application);  
create index ase\_user on  activitystream\_events (ase\_user);  
create index ase\_wiki on  activitystream\_events (ase\_wiki);  
create index ase\_space on  activitystream\_events (ase\_space);  
create index ase\_page on  activitystream\_events (ase\_page);  
create index ase\_page\_date on  activitystream\_events (ase\_page, ase\_date);

# 完整性检查 #

为了验证您的XWiki数据库的一致性，你可以运行完整性检查脚本。该脚本将select出与XWiki逻辑不一致的行。如果没有行被select出来，则意味着脚本本身没检测出任何错误。

*  如果你使用**MySQL**作为数据库：
    
     *  此完整性检查脚本已经经过**MySQL 5.0**测试
     *  此脚本针对的是**MySQL 4.x**，根据上面脚本进行修改，替换一些语法
 *  如果你使用**postgresql**作为数据库：
    
     *  此postgresql完整性检查脚本可以通过pgAdmin的"query"工具运行。它与MYSQl的脚本是一样的，除了SQL的comments语法不一样
     *  pgAdmin是一个可以访问postgresql数据库的GUI工具。运行pgAdmin时，选择XWiki数据库，并选择在“Tools”菜单中的“Query”选项。然后，只需打开脚本，然后点击play图标（“Execute query”）。

# Database browsing #

## DbVisualizer ##

[DbVisualizer][]支持以下数据库：

*  DB2 Windows/Linux
 *  JavaDB/Derby
 *  MySQL
 *  PostgreSQL
 *  [更多][Link 2]

[Link 1]: https://blog.csdn.net/qq_33873431/article/details/80388191
[MySQL_255]: http://dev.mysql.com/doc/refman/5.7/en/column-indexes.html
[InnoDB]: http://dev.mysql.com/doc/refman/5.7/en/innodb-restrictions.html
[application]: http://extensions.xwiki.org/xwiki/bin/view/Extension/Check+Config+And+Indexes+Application
[DbVisualizer]: http://www.dbvis.com/download/
[Link 2]: http://www.dbvis.com/features/tour/supported-databases/