MySql 查询优化方法

debugcn 发表于 Dev

大卫

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

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

这些是我预定义的视图

unit_details

          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;

最终视图

          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;

我的问题是，

是否建议创建上述预定义视图并从预定义视图中进行选择并插入到汇总表中？

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

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

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

关于正确方法以及帮助我优化 sql 查询的善意建议。

里克·詹姆斯