Delphi 高效处理大数据量的字典数据的查询问题

一、需求

系统中存在基本字典数据，且数据量大，需要根据各种条件查询某个字典数据，该如何高效实现？

例如：系统中的菜品字典数据，需要根据菜品ID、菜品编码、菜品名+菜品规格等条件查找菜品

二、思路

1、使用索引的方法

将每个查询条件都建立一个TStringList，并添加条件值，并确保其与菜品对象 TMenuItem 在TList中的位置一样。 查询时，根据条件值找到TStringList中的位置 index，然后直接通过TList[index] 获取菜品对象。

该方法查询时不用遍历整个 TList ，从而提高查询效率。

2、使用哈希方法

将每个查询条件添加到 TDictionary 字典 TFMenuItemHash(TDictionary<string, TMenuItem>)，Key 值为 【条件前缀+菜品对应ID等值】，Value 值为 TMenu 对象地址。查询时，通过FMenuItemHash.TryGetValue 获取。

注意：程序结束时，需要遍历 TFMenuItemHash，释放 TFMenuItemHash 对应的 TMenu 对象

三、代码实现

这里只展示思路2对应的代码实现。

unit uMenuItem;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, 
  System.Classes, System.Generics.Collections;

//定义查询条件类型  按菜品ID  菜品Key  菜品编码  菜品名称  菜品名称+规格  菜品ID+规格
type
  TMenuItemListType = (mltID, mltKey, mltCode, mltName, mltNameUnit, mltIDUnit, mltKeyUnit);
const
  MenuItemListTypeArry: array [TMenuItemListType] of string = ('mltID', 'mltKey', 'mltCode', 'mltName', 'mltNameUnit', 'mltIDUnit', 'mltKeyUnit');

type
  //菜品类
  TMenuItem = class
    //...菜品ID、菜品名等属性
  end;

  //菜品字典列表类
  TMenuItemLst = class
  private
    FMenuItemHash: TDictionary<string, TMenuItem>;
    //添加Key值到 FMenuItemHash 中
    procedure AddMenuItem(MenuItemType: TMenuItemListType; AKey: string; AMenuItem: TMenuItem);
    //从 FMenuItemHash 中获取 key 值对应的 TMenuItem 对象
    function GetMenuItem(MenuItemType: TMenuItemListType; AKey: string): TMenuItem;

    function InitData(): boolean;
    function GetCount: integer;
    function GetItems(Index: integer): TMenuItem;
  protected
    FList: TList<TMenuItem>;
  public
    constructor Create(); overload;
    destructor Destroy; override;

    property Count: integer read GetCount;
    property Items[Index: integer]: TMenuItem read GetItems; default;

    procedure Clear;
    procedure ClearFoodCannotSaleList;

    procedure Delete(iIndex: integer);
    function IndexOf(AMenuItem: TMenuItem): integer;
    function Refresh(): Boolean;
    
    //按菜品ID查询
    function GetMenuItemByFoodID(AFoodID: int64): TMenuItem;
    //按菜品规格查询
    function GetMenuItemByUnitKey(AUnitKey: string): TMenuItem;
    //按菜品编码查询
    function GetMenuItemByFoodCode(AFoodCode: string): TMenuItem;
    //按菜品名称查询
    function GetMenuItemByFoodName(AFoodName: string): TMenuItem;
    //按菜品名称+规格查询
    function GetMenuItemByFoodNameUnit(AFoodName, AFoodUnit: string): TMenuItem;
    //按菜品ID+规格查询
    function GetMenuItemByFoodIDUnit(AFoodID: int64; AFoodUnit: string): TMenuItem;
    //按菜品Key+规格查询
    function GetMenuItemByFoodKeyUnit(AFoodKey, AFoodUnit: string): TMenuItem;
end;

implementation

{ TMenuItemLst }

procedure TMenuItemLst.Clear;
var
  i: integer;
begin
  for i := 0 to FList.Count - 1 do
  begin
    FList[i].Free;
  end;

  FList.Clear;
  FMenuItemHash.Clear;
end;


constructor TMenuItemLst.Create;
begin
  FList := TList<TMenuItem>.Create;
  FMenuItemHash := TDictionary<string, TMenuItem>.Create;
end;

procedure TMenuItemLst.Delete(iIndex: integer);
begin
  TMenuItem(FList[iIndex]).Free;
  FList.Delete(iIndex);
end;

destructor TMenuItemLst.Destroy;
begin
  Clear;
  FList.Free;
  FMenuItemHash.Free;
  inherited;
end;

function TMenuItemLst.GetCount: integer;
begin
  Result := FList.Count;
end;

procedure TMenuItemLst.AddMenuItem(MenuItemType: TMenuItemListType;
  AKey: string; AMenuItem: TMenuItem);
begin
  FMenuItemHash.AddOrSetValue(MenuItemListTypeArry[MenuItemType] + AKey, AMenuItem);
end;

function TMenuItemLst.GetMenuItem(MenuItemType: TMenuItemListType;
  AKey: string): TMenuItem;
begin
  FMenuItemHash.TryGetValue(MenuItemListTypeArry[MenuItemType] + AKey, Result);
end;

function TMenuItemLst.GetItems(Index: integer): TMenuItem;
begin
  Result := nil;
  if (Index >= 0) and (Index < FList.Count) then
    Result := TMenuItem(FList[Index]);
end;

function TMenuItemLst.GetMenuItemByFoodCode(AFoodCode: string): TMenuItem;
begin
  Result := GetMenuItem(mltCode, AFoodCode);
end;

function TMenuItemLst.GetMenuItemByFoodID(AFoodID: int64): TMenuItem;
begin
  Result := GetMenuItem(mltID, AFoodID.ToString);
end;

function TMenuItemLst.GetMenuItemByFoodIDUnit(AFoodID: int64;
  AFoodUnit: string): TMenuItem;
begin
  Result := GetMenuItem(mltIDUnit, AFoodID.ToString + AFoodUnit);
end;

function TMenuItemLst.GetMenuItemByFoodKeyUnit(AFoodKey,
  AFoodUnit: string): TMenuItem;
begin
  Result := GetMenuItem(mltKeyUnit, AFoodKey + AFoodUnit);
end;

function TMenuItemLst.GetMenuItemByFoodName(AFoodName: string): TMenuItem;
begin
  Result := GetMenuItem(mltName, AFoodName);
end;

function TMenuItemLst.GetMenuItemByFoodNameUnit(AFoodName,
  AFoodUnit: string): TMenuItem;
begin
  Result := GetMenuItem(mltNameUnit, AFoodName + AFoodUnit);
end;

function TMenuItemLst.GetMenuItemByUnitKey(AUnitKey: string): TMenuItem;
begin
  Result := GetMenuItem(mltKey, AUnitKey);
end;

function TMenuItemLst.IndexOf(AMenuItem: TMenuItem): integer;
begin
  Result := FList.IndexOf(AmenuItem);
end;

function TMenuItemLst.InitData(): boolean;
var
  i: integer;
  menuItem: TMenuItem;
  foodID, foodUnitKey, foodCode, foodName, foodUnit: string;
begin
  result := false;
  try
    try
      //循环数据集， 假如已从数据库中查询了所有菜品字典数据
      for i := 0 to 10000 do
      begin
        //初始化菜品对象，在 TMenuItemLst 类释放时，需要循环释放 TMenuItem
        menuItem := TMenuItem.Create();
        
        foodID := menuItem.FoodID;
        foodUnitKey := menuItem.FoodUnitKey;
        foodCode := menuItem.FoodCode;
        foodName := menuItem.FoodName;
        foodUnit := menuItem.FoodUnit;

        if foodID <> '' then           //添加菜品ID的Key值
          AddMenuItem(mltID, foodID, menuItem);
        if foodUnitKey <> '' then      //添加菜品Key的Key值
          AddMenuItem(mltKey, foodUnitKey, menuItem);
        if FoodCode <> '' then         //添加菜品编码的Key值
          AddMenuItem(mltCode, foodCode, menuItem);
        if foodName <> '' then
          AddMenuItem(mltName, foodName, menuItem);
        if (foodName <> '') or (foodUnit <> '') then
          AddMenuItem(mltNameUnit, foodName + foodUnit, menuItem);
        if (foodID <> '') or (foodUnit <> '') then
          AddMenuItem(mltIDUnit, foodID + foodUnit, menuItem);
        if (foodUnitKey <> '') or (foodUnit <> '') then
          AddMenuItem(mltKeyUnit, foodUnitKey + foodUnit, menuItem);

        FList.Add(menuItem);
      end;

      result := true;
    except
      on E: Exception do
      begin
        raise Exception.Create(E.Message);
      end;
    end;
  finally

  end;
end;

function TMenuItemLst.Refresh(): Boolean;
begin

end;

end.