Главная » PHP » MySql подход к оптимизации запросов

MySql подход к оптимизации запросов

Я работаю над приборной панелью в 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;

это один из моих запросов к сводной таблице
сводные таблицы обновляются каждые 30 минут с помощью пакетного файла, выполняющего SQL.

          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-запрос.

0

Решение

  • Unclutter — я согласен с текстовыми изменениями Strawberry.
  • Unclutter — не нормализуйте страну, просто включите ее в город.
  • Unclutter — вам действительно нужно created_by а также created_datetime,
  • Unclutter — не используйте имена таблиц из 5 слов.
  • использование JOIN ... ON ... вместо старого «запятая».
  • Не иметь оба INDEX(a) а также INDEX(a,b); первое излишне и ненужно.
  • UNIQUE Индекс является индексом, так INDEX(a,b) не нужно, когда у вас также есть UNIQUE(a,b),

Вернемся к некоторым вашим вопросам …

  • VIEW (в MySQL) является синтаксическим сахаром — он никогда не может быть быстрее, чем эквивалент SELECT, (Хотя это может быть легче читать.)
  • В MySQL нет явной поддержки сводных таблиц (даже при VIEWs). Однако ручная реализация — хорошая идея для производительности; Я иногда вижу 10 раз улучшение производительности.
1

Другие решения

Не ответ. Слишком долго для комментария.

Обычно мы пишем такие запросы таким образом. Я предлагаю вам начать здесь и соответственно изменить свой вопрос (и, возможно, модель данных) …

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
2

Источник
По вопросам рекламы ammmcru@yandex.ru
Web-Answers © 2024 Наверх
Adblock
detector