php - MySql查询优化方法-6ren

php - MySql查询优化方法

转载作者：太空宇宙更新时间：2023-11-03 11:34:21

我正在使用 PHP MySql 开发仪表板，用户将在其中登录并访问仪表板，用户可以访问单个部分和单个国家或多个部分和多个国家。

我制作了 3 个预定义 View ，并从预定义 View 中插入到汇总表中并允许用户访问汇总表。

这些是我预定义的 View

单位详情

          select 
      sections.section_id,
      countries.country_id,
      business_units.unit_id,
      sections.section_name,
      countries.country_name,
      cities.city_name,
      business_units.unit_name,
      business_unit_types.unit_type_name,
      business_unit_categories.unit_category_name
      from
      sections,
      countries,
      cities,
      business_units,
      business_unit_types,
      business_unit_categories 
      where 
           business_units.section_id=sections.section_id
      and  business_units.country_id=countries.country_id
      and  business_units.city_id=cities.city_id
      and  business_units.unit_type_id=business_unit_types.unit_type_id
      and  business_units.unit_category_id=business_unit_categories.unit_category_id
      and  cities.country_id=countries.country_id;

交易详情

          SELECT
      transactions.business_date,
      transactions.transaction_datetime,
      business_unit_product_category_section.section_id,
      business_units.country_id,
      transactions.unit_id,
      transactions.transaction_id,
      product_category_groups.product_category_group_name,
      transactions.product_category_id,
      product_categories.product_category_name,
      transactions.product_id,
      products.product_name,
      transactions.net_sales
      FROM 
      transactions,
      business_unit_product_category_section,
      business_units,
      products,
      product_categories,
      product_category_groups 
      where 
          transactions.unit_id=business_unit_product_category_section.unit_id
      and transactions.product_category_id=business_unit_product_category_section.product_category_id
      and transactions.unit_id=business_units.unit_id 
      and business_unit_product_category_section.section_id=business_units.section_id 
      and business_unit_product_category_section.unit_id=business_units.unit_id
      and transactions.product_id=products.product_id 
      and transactions.product_category_id=products.product_category_id
      and transactions.product_category_id=product_categories.product_category_id
      and product_categories.product_category_id=products.product_category_id
      and product_categories.product_category_group_id=product_category_groups.product_category_group_id;

最终 View

          select 
      unit_details.section_name,
      unit_details.country_name,
      unit_details.city_name,
      unit_details.unit_name,
      unit_details.unit_type_name,
      unit_details.unit_category_name,
      transaction_details.business_date,
      transaction_details.transaction_datetime,
      transaction_details.section_id,
      transaction_details.country_id,
      transaction_details.unit_id,
      transaction_details.transaction_id,
      transaction_details.product_category_group_name,
      transaction_details.product_category_id,
      transaction_details.product_category_name,
      transaction_details.product_id,
      transaction_details.product_name,
      transaction_details.net_sales
      from unit_details ud
      left join transaction_details td on 
      td.section_id=ud.section_id
      and 
      td.country_id=ud.country_id
      and 
      td.unit_id=ud.unit_id;

这是我的汇总表查询之一执行 SQL 的批处理文件每 30 分钟更新一次汇总表。

          SET @date_today = DATE(NOW());
      select
      final_view.section_name,
      final_view.country_name,
      final_view.city_name,
      final_view.unit_name,
      final_view.unit_type_name,
      final_view.unit_category_name,
      sum(CASE WHEN @date_today = final_view.business_date THEN final_view.net_sales ELSE 0 END) TODAYS_NETSALES,
      sum(CASE WHEN month(@date_today) = month(final_view.business_date) and final_view.business_date<=@date_today THEN final_view.net_sales ELSE 0 END) MTD_NETSALES
      from final_view
      group by final_view.section_name,final_view.country_name,final_view.city_name,final_view.unit_name,final_view.unit_category_name;

这是我的模式

          CREATE TABLE business_units (
        id int(11) NOT NULL,
        unit_id int(11) NOT NULL,
        unit_name varchar(30) NOT NULL,
        section_id int(11) NOT NULL,
        country_id int(11) NOT NULL,
        city_id int(11) NOT NULL,
        unit_type_id int(11) NOT NULL,
        unit_category_id int(11) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL,
        is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
        status_change_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      CREATE TABLE business_unit_categories (
        unit_category_id int(11) NOT NULL,
        unit_category_name varchar(30) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL,
        is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
        status_change_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      CREATE TABLE business_unit_product_category_section (
        id int(11) NOT NULL,
        unit_id int(11) NOT NULL,
        product_category_id int(11) NOT NULL,
        section_id int(11) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      CREATE TABLE business_unit_types (
        unit_type_id int(11) NOT NULL,
        unit_type_name varchar(30) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL,
        is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
        status_change_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


      CREATE TABLE cities (
        city_id int(11) NOT NULL,
        city_name varchar(30) NOT NULL,
        country_id int(11) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


      CREATE TABLE countries (
        country_id int(11) NOT NULL,
        country_name varchar(30) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


      CREATE TABLE products (
        id int(11) NOT NULL,
        product_id varchar(13) NOT NULL,
        product_name varchar(300) NOT NULL,
        product_category_id int(11) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


      CREATE TABLE product_categories (
        product_category_id int(11) NOT NULL,
        product_category_name varchar(30) NOT NULL,
        product_category_group_id int(11) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL,
        is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
        status_change_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


      CREATE TABLE product_category_groups (
        product_category_group_id int(11) NOT NULL,
        product_category_group_name varchar(30) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL,
        is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
        status_change_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      CREATE TABLE sections (
        section_id int(11) NOT NULL,
        section_name varchar(30) NOT NULL,
        created_by varchar(30) NOT NULL,
        created_datetime datetime NOT NULL,
        is_active_status int(1) NOT NULL COMMENT '1-active, 0-not active',
        status_change_datetime datetime NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


      CREATE TABLE transactions (
        id int(11) NOT NULL,
        business_date date NOT NULL,
        unit_id int(11) NOT NULL,
        transaction_id int(11) NOT NULL,
        transaction_datetime datetime NOT NULL,
        product_category_id int(11) NOT NULL,
        product_id varchar(13) NOT NULL,
        net_sales float NOT NULL,
        net_qty int(11) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      CREATE TABLE user_permissions (
      id int(11) NOT NULL,
      user_id varchar(30) NOT NULL,
      section_id int(11) NOT NULL,
      country_id int(11) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


      ALTER TABLE business_units
        ADD PRIMARY KEY (id),
        ADD UNIQUE KEY unit_id (unit_id,section_id,country_id),
        ADD KEY unit_id_2 (unit_id,section_id,country_id),
        ADD KEY city_id (city_id),
        ADD KEY unit_type_id (unit_type_id),
        ADD KEY unit_category_id (unit_category_id);


      ALTER TABLE business_unit_categories
        ADD PRIMARY KEY (unit_category_id);


      ALTER TABLE business_unit_product_category_section
        ADD PRIMARY KEY (id),
        ADD UNIQUE KEY unit_id (unit_id,product_category_id,section_id),
        ADD KEY unit_id_2 (unit_id,product_category_id,section_id);


      ALTER TABLE business_unit_types
        ADD PRIMARY KEY (unit_type_id);


      ALTER TABLE cities
        ADD PRIMARY KEY (city_id),
        ADD UNIQUE KEY city_id (city_id,country_id),
        ADD KEY country_id (country_id),
        ADD KEY city_id_2 (city_id,country_id);


      ALTER TABLE countries
        ADD PRIMARY KEY (country_id);


      ALTER TABLE products
        ADD PRIMARY KEY (id),
        ADD KEY product_id (product_id),
        ADD KEY product_category_id (product_category_id);


      ALTER TABLE product_categories
        ADD PRIMARY KEY (product_category_id),
        ADD UNIQUE KEY product_category_id (product_category_id,product_category_group_id),
        ADD KEY product_category_group_id (product_category_group_id);


      ALTER TABLE product_category_groups
        ADD PRIMARY KEY (product_category_group_id);


      ALTER TABLE sections
        ADD PRIMARY KEY (section_id);


      ALTER TABLE transactions
        ADD PRIMARY KEY (id),
        ADD KEY business_date (business_date),
        ADD KEY unit_id (unit_id),
        ADD KEY transaction_id (transaction_id),
        ADD KEY transaction_datetime (transaction_datetime),
        ADD KEY product_category_id (product_category_id),
        ADD KEY product_id (product_id),
        ADD KEY product_id_3 (product_id,product_category_id);


      ALTER TABLE transactions
        MODIFY id int(11) NOT NULL AUTO_INCREMENT;

      ALTER TABLE user_permissions
      ADD PRIMARY KEY (id),
      ADD UNIQUE KEY user_id_3 (user_id,section_id,country_id),
      ADD KEY user_id (user_id),
      ADD KEY section_id (section_id),
      ADD KEY country_id (country_id),
      ADD KEY user_id_2 (user_id,section_id,country_id);

      ALTER TABLE user_permissions
      MODIFY id int(11) NOT NULL AUTO_INCREMENT;

我的问题是，

是否建议像上面那样创建预定义 View 并从预定义 View 中选择并插入到汇总表中？

或者我是否应该删除预定义的 View 和汇总表并停止批处理并仅在用户在 session 期间访问仪表板时通过 PHP 页面生成汇总 View ？

我创建预定义 View 的原因是，交易表有数百万条记录，更新汇总表大约需要 10 到 15 分钟。当用户访问仪表板时，数据可用，用户无需等待查看数据。

如果我在 session 期间生成数据，用户将不得不等待 10 到 15 分钟才能看到数据。

请就正确的方法提出建议，并帮助我优化 sql 查询。

最佳答案

不是答案。评论太长了。

我们通常会这样写这类查询。我建议您从这里开始并相应地修改您的问题(可能还有您的数据模型)...

SELECT s.section_id
     , c.country_id
     , u.unit_id
     , s.section_name
     , c.country_name
     , x.city_name
     , u.unit_name
     , t.unit_type_name
     , y.unit_category_name
  FROM sections s
  JOIN business_units u
    ON u.section_id = s.section_id
  JOIN countries c
    ON u.country_id = c.country_id
  JOIN cities x
    ON x.city_id = u.city_id 
   AND x.country_id = c.country_id -- there is a redundancy in your model here
  JOIN business_unit_types t
    ON t.unit_type_id = u.unit_type_id 
  JOIN business_unit_categories y
    ON y.unit_category_id u.unit_category_id

关于php - MySql查询优化方法，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/47073354/

文章推荐： MySQL Left join 得到双重结果

文章推荐： java - 如何使用 Moshi 解析未知 key json

文章推荐： mysql - 值 = 1 时的 SQL 计数

Ruby 方法() 方法
我想了解 Ruby 方法 methods() 是如何工作的。我尝试使用“ruby 方法”在 Google 上搜索，但这不是我需要的。我也看过 ruby-doc.org，但我没有找到这种方法。
VBS教程：方法-Test 方法
Test 方法对指定的字符串执行一个正则表达式搜索，并返回一个 Boolean 值指示是否找到匹配的模式。 object.Test(string) 参数 object 必选项。总是一个
VBS教程：方法-Replace 方法
Replace 方法替换在正则表达式查找中找到的文本。 object.Replace(string1, string2) 参数 object 必选项。总是一个 RegExp 对象的名称。
VBS教程：方法-Raise 方法
Raise 方法生成运行时错误 object.Raise(number, source, description, helpfile, helpcontext) 参数 object 应为
VBS教程：方法-Execute 方法
Execute 方法对指定的字符串执行正则表达式搜索。 object.Execute(string) 参数 object 必选项。总是一个 RegExp 对象的名称。 string
VBS教程：方法-Clear 方法
Clear 方法清除 Err 对象的所有属性设置。 object.Clear object 应为 Err 对象的名称。说明在错误处理后，使用 Clear 显式地清除 Err 对象。此
VBS教程：方法-CopyFile 方法
CopyFile 方法将一个或多个文件从某位置复制到另一位置。 object.CopyFile source, destination[, overwrite] 参数 object 必选
VBS教程：方法-Copy 方法
Copy 方法将指定的文件或文件夹从某位置复制到另一位置。 object.Copy destination[, overwrite] 参数 object 必选项。应为 File 或 F
VBS教程：方法-Close 方法
Close 方法关闭打开的 TextStream 文件。 object.Close object 应为 TextStream 对象的名称。说明下面例子举例说明如何使用 Close 方
VBS教程：方法-BuildPath 方法
BuildPath 方法向现有路径后添加名称。 object.BuildPath(path, name) 参数 object 必选项。应为 FileSystemObject 对象的名称
VBS教程：方法-GetFolder 方法
GetFolder 方法返回与指定的路径中某文件夹相应的 Folder 对象。 object.GetFolder(folderspec) 参数 object 必选项。应为 FileSy
VBS教程：方法-GetFileName 方法
GetFileName 方法返回指定路径（不是指定驱动器路径部分）的最后一个文件或文件夹。 object.GetFileName(pathspec) 参数 object 必选项。应为
VBS教程：方法-GetFile 方法
GetFile 方法返回与指定路径中某文件相应的 File 对象。 object.GetFile(filespec) 参数 object 必选项。应为 FileSystemObject
VBS教程：方法-GetExtensionName 方法
GetExtensionName 方法返回字符串，该字符串包含路径最后一个组成部分的扩展名。 object.GetExtensionName(path) 参数 object 必选项。应
VBS教程：方法-GetDriveName 方法
GetDriveName 方法返回包含指定路径中驱动器名的字符串。 object.GetDriveName(path) 参数 object 必选项。应为 FileSystemObjec
VBS教程：方法-GetDrive 方法
GetDrive 方法返回与指定的路径中驱动器相对应的 Drive 对象。 object.GetDrive drivespec 参数 object 必选项。应为 FileSystemO
VBS教程：方法-GetBaseName 方法
GetBaseName 方法返回字符串，其中包含文件的基本名 (不带扩展名), 或者提供的路径说明中的文件夹。 object.GetBaseName(path) 参数 object 必
VBS教程：方法-GetAbsolutePathName 方法
GetAbsolutePathName 方法从提供的指定路径中返回完整且含义明确的路径。 object.GetAbsolutePathName(pathspec) 参数 object
VBS教程：方法-FolderExists 方法
FolderExists 方法如果指定的文件夹存在，则返回 True；否则返回 False。 object.FolderExists(folderspec) 参数 object 必选项
VBS教程：方法-FileExists 方法
FileExists 方法如果指定的文件存在返回 True；否则返回 False。 object.FileExists(filespec) 参数 object 必选项。应为 FileS

太空宇宙

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

php - MySql查询优化方法