performance - SQLite索引导致查询性能下降

Question

我正在通过测试向查询中的表添加索引的结果来为1个大型查询优化SQLite数据库。

我注意到的第一件事是索引疯抢会降低性​​能，因此此后我删除了所有索引并缩小了数据库，并开始测试一次应用1个索引的性能。

这样做，我发现了一个索引，该索引将查询速度提高了约50％。然后，我将此索引应用于另一个SQLite数据库（相同的结构，只是更多数据），其数据量约为500倍（500MB），而现在，我发现相同的索引会使大型数据库的性能下降约35％。

因此，我对PRAGMA cache_size进行了一些阅读，并了解到，如果您的表很大并且您对其应用了索引，则索引将很快用尽高速缓存，这会降低性能。因此，更改此大小可能会有所帮助...

所以我的问题是，为什么索引数据在样本数据集较大的数据库中比在数据样本数较小的数据库中会降低？

更多信息：

我的查询仅是选择查询，并且仅从数据库中读取数据库，而从未写入数据库（显然，第一次填充数据库时除外）。
我应用的索引在单个整数列上。

查询：

select distinct

u.userName 'User Name',
u.description 'User Description',
ma.abilityName 'Ability',
ifnull(a.applicationName, '') 'Module',
ifnull(rr3.roleName, '') 'Parent Role',
r.responsibilityName 'Responsibility',
r.description 'Responsibility Description',
ff.functionName 'Technical Function Name',
ifnull(ff.userFunctionName, '') 'Function Name',
ff.description 'Function Description',
ff.type 'Function Type',             
ifnull(f.formName, '') 'Form',
ifnull(f.userFormName, '') 'Form Name',             
case ifnull(me.isProcessTab, 0) when 0 then 'N' else 'Y' end as 'Is From Process Tab?',
m.menuName 'Menu Name',
m.menuDisplayName 'Menu Description',
ifnull(sg.securityGroupName, '') 'Data Group Name',
ifnull(rg.requestGroupName, '') 'Request Group Name',
mpath.path as 'Full Menu Path',              
cast(ma.ID as nvarchar) || ':' || cast(u.ID as nvarchar) as 'Ledger Mapping'

from matrix_processes  mp   
inner join matrix_processAbilities  mpa on mpa.processID = mp.ID
inner join matrix_abilities ma on ma.ID = mpa.abilityID
inner join matrix_abilityFunctions maf on maf.abilityID = ma.ID
inner join matrix_functions mf on mf.ID = maf.functionID  --mf.functionName = ff.functionName
inner join formFunctions  ff on ff.functionName = mf.functionName
inner join users_functions uf on uf.functionID = ff.ID
inner join users_responsibilities as ur on ur.userID = uf.userID           
inner join responsibilities_menus_hierarchy_functions  rmhf on rmhf.responsibilityID = ur.responsibilityID and rmhf.functionID = ff.ID         

inner join users as u on u.ID = uf.userID
inner join responsibilities as r on r.ID = ur.responsibilityID

left join requestGroups as rg on rg.ID = r.requestGroupID
left join securityGroups as sg on sg.securityGroupKey = r.dataGroupKey

inner join menus_hierarchy  mh on mh.ID = rmhf.menuHierarchyID
inner join menus  as m on m.id = mh.menuParentID
inner join menuPaths mpath  on mpath.ID = mh.menuPathID
left join menuEntries me on me.menuID = m.ID

left join applications as a on a.ID = r.applicationID
left join forms  as f on f.ID = ff.formID

left join roles_responsibilities rr1 on rr1.responsibilityID = r.ID
left join roles_roles rr2 on rr2.roleID = rr1.roleID
left join roles rr3 on rr3.ID = rr2.roleParentID

where mp.ID = 1033

order by u.userName,
      ma.abilityName,
      r.responsibilityName,
      ff.userFunctionName;

查询计划：

0   0   0   SCAN TABLE matrix_processes AS mp (~100000 rows)
0   1   1   SEARCH TABLE matrix_processAbilities AS mpa USING AUTOMATIC COVERING INDEX (processID=?) (~7 rows)
0   2   2   SEARCH TABLE matrix_abilities AS ma USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   3   3   SEARCH TABLE matrix_abilityFunctions AS maf USING AUTOMATIC COVERING INDEX (abilityID=?) (~7 rows)
0   4   4   SEARCH TABLE matrix_functions AS mf USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   5   5   SEARCH TABLE formFunctions AS ff USING AUTOMATIC COVERING INDEX (functionName=?) (~7 rows)
0   6   6   SEARCH TABLE users_functions AS uf USING INDEX IX_UserAccessDetailsOpt_users_functions2 (functionID=?) (~10 rows)
0   7   7   SEARCH TABLE users_responsibilities AS ur USING AUTOMATIC COVERING INDEX (userID=?) (~7 rows)
0   8   8   SEARCH TABLE responsibilities_menus_hierarchy_functions AS rmhf USING AUTOMATIC COVERING INDEX (responsibilityID=? AND functionID=?) (~7 rows)
0   9   9   SEARCH TABLE users AS u USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   10  10  SEARCH TABLE responsibilities AS r USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   11  11  SEARCH TABLE requestGroups AS rg USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   12  12  SEARCH TABLE securityGroups AS sg USING AUTOMATIC COVERING INDEX (securityGroupKey=?) (~7 rows)
0   13  13  SEARCH TABLE menus_hierarchy AS mh USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   14  14  SEARCH TABLE menus AS m USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   15  15  SEARCH TABLE menuPaths AS mpath USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   16  16  SEARCH TABLE menuEntries AS me USING AUTOMATIC COVERING INDEX (menuID=?) (~7 rows)
0   17  17  SEARCH TABLE applications AS a USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   18  18  SEARCH TABLE forms AS f USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   19  19  SEARCH TABLE roles_responsibilities AS rr1 USING AUTOMATIC COVERING INDEX (responsibilityID=?) (~7 rows)
0   20  20  SEARCH TABLE roles_roles AS rr2 USING AUTOMATIC COVERING INDEX (roleID=?) (~7 rows)
0   21  21  SEARCH TABLE roles AS rr3 USING AUTOMATIC COVERING INDEX (ID=?) (~7 rows)
0   0   0   USE TEMP B-TREE FOR DISTINCT
0   0   0   USE TEMP B-TREE FOR ORDER BY

Answer 1

AUTOMATIC INDEX表示SQLite认为值得创建一个临时索引（即使有收集所有表数据并对索引进行排序所需的时间）。
写入和排序所有数据所需的时间以O（nlogn）为单位。

通过将所有这些索引永久添加到数据库中，您应该能够加快此查询的速度：

CREATE INDEX mpa_processID ON matrix_processAbilities(processID);
CREATE INDEX ma_ID ON matrix_abilities(ID);
...

此外，可以在此列上使用索引加快查找 where mp.ID = 1033的速度。 （优化程序认为为此创建一个临时索引是不值得的，因为该表仅被搜索一次。）

请注意，在表定义中，应将任何为主键且具有整数类型的ID列声明为 INTEGER PRIMARY KEY，这比单独的索引效率更高。