xwiki管理指南-数据库管理

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

索引

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

XWiki Enterprise 4.3之后版本的索引

尽管我们配置XWiki让Hibernate默认创建索引，但是有一定的局限性。因为有些索引不能用于自动生成超过255个字符的string列（MySQL有255个字符的限制 - 参见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
• 更多