Сучасна диджитал-освіта для дітей — безоплатне заняття в GoITeens ×
Mazda CX 5
×

Выборка из MySQL в диапазоне дат

Підписуйтеся на Telegram-канал «DOU #tech», щоб не пропустити нові технічні статті

Добрый день, есть таблица в формате

CREATE TABLE IF NOT EXISTS `statistic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_org` int(11) NOT NULL,
  `id_driver` int(11) NOT NULL,
  `coins` int(11) NOT NULL,
  `data` bigint(20) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=20110 ;
сейчас делаю простую выборку из таблицы по ИД таким запросом
$result =  $connection->query("SELECT * FROM statistic WHERE `id_user`=".$users[$i]['id']);
И есть даты в диапазоне которых нужно сделать выборку к примеру 20-03-2019 29-03-2019

Вопрос: каким образом возможно сформулировать запрос, чтоб в результате получить только нужную выборку данных.

АП:
Суть в чому, тип поля з датою і збереження її такому форматі була однією з вимог замовника... тут нічого не поробиш, уперся і все....
По багатьох інших питаннях, дійсно мало що знаю, індекси, унікальні поля і тому подібне можливо не вірно сформував...

просто з самого початку була задача, просто сформувати таблицю статистики яка б фіксувала кількість певних дій для конкретних Працівників організаці...
Тобто є
Юзер (клієнт)
Його орагнізація,
Працівник організаціїї...
Ну і кількість операцій виконаних даним працівником за день. Це значення записується в поле coins...

При чому поля Організації і Працівників ніяк не можуть бути унікальними, вони можуть співпадати для різних юзерів...

Вирішив проблему можливо «топорно», зате працює...

1. Дати діапазон передаються формую через різні поля...

$start=$day.'-'.$mes.'-'.$god;
$start=date("d.m.Y", strtotime($start));
$end=$day2.'-'.$mes2.'-'.$god2;
$end=date("d.m.Y", strtotime('+1day', strtotime($end)));

if ($start==$end){
$dates[]=$start;
}
$start = new DateTime($start);
// Дата окончания интервала
$end = new DateTime($end);
// Интервал в один день
$step = new DateInterval('P1D');
// Итератор по дням
$period = new DatePeriod($start, $step, $end);

// Вывод дней
foreach($period as $datetime) {
  //echo $datetime->format("d.m.Y")."<br />";
  $dates[]=$datetime->format("d.m.Y");
}

if ($dates[0]=='01.01.1970'){
	$dates=array();
}

Далі просто циклі

	$result =  $connection->query("SELECT * FROM statistic WHERE `id_org`=".$org." AND `id_user`=".$uid." AND `id_driver`=".$users[$i]['id']);
	//Просто роблю вибірку всіх записів які відповідають данному працівникові
	
		 while ($row2 = $result->fetch_assoc())
			{
				if (count($dates)>0){
				$usersdate=date("d.m.Y", $row2['data']); // дата в БД
						for ($j = 0; $j <count($dates); $j++) {	
							$ndata=$dates[$j]; // одна з дат в діапазоні
							if(stristr($usersdate, $ndata) === FALSE) {
							// нічого не роблю (заготовка для подальших забагань замовника)
							// можливо тут потрібно убде також щось підраховувати						
							}else {
								
							$coins=$coins+$row2['coins']; // підраховую кількість операцій для статистики
							$users[$i]['date']=date("d-m-Y", $row2['data']);	// хаписую дату останньої операції 
							}
						}
				}else{ // це якщо діапазон дат не задано, то виводу статистику за весь період...
				$coins=$coins+$row2['coins']; 
				$users[$i]['date']=date("d-m-Y", $row2['data']);
				}
			}
	

можливо код і виглядає жахливо, зате працює ))) я самоучка в Веб програмуванні... в технікумі нажаль крім Паскаля і Делфі більше нічого не навчили, прийшлося все опановувати самому...

ПС: Дякую всім за таку дискусію, і океан корисної інформації, буду вивчати!!!

👍ПодобаєтьсяСподобалось0
До обраногоВ обраному0
LinkedIn
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter
Дозволені теги: blockquote, a, pre, code, ul, ol, li, b, i, del.
Ctrl + Enter

Пановє, оффтоп. Раз така п’янка і зібрався народ, який точно знає як вибрати діапазон дат, — кіко зара коштує консультація ДБА (мускл 5.7, працюючий проект)? І чи займається хто з присутніх панів (чи паних, будемо толерантними) етім дєлом? (ставь класс єслі да!!11)

І конкретне питання:
є достатньо нормалізована структура з кількох таблиць. Наприклад
компанія company (id_company, name),
філія branch (id_branch, id_company, name),
список продуктів item (id_item, name),
про́даж у філії sale (id_sale, id_item, id_branch, count),
залишки по компанії store (id_store, id_company, id_item, count).
Задача — мати актуальні залишки по компанії.
В нормалізованому варіанті — маємо зробити update store inner join branch, т.к про́даж не зв’язаний з магазином прямо.
Існує підозра, що при значній кількості конкурентних запитів на вставку/видалення/оновлення цей джоін починає заважати. (я не вмію адекватно протестувати це)
Можна денормалізувати продаж, зробивши (id_sale, id_item, id_branch, id_company, count), але тут виникає необхідність підтримувати цілісність для пари id_branch, id_company щоб криві ручки не могли поламати дані.
Для цього робимо alter table branch add unique key (id_company, id_branch); alter table sale add foreign key (id_company, id_branch) references branch (id_company, id_branch);
І ось тут питання до знатоків — для сферичного випадку у вакуумі чи є у всьому цьому сенс? Чи варте усунення одного джоіну (у реальній ситуації це join one-to-many) додавання двох додаткових комбінованих індексів?
І як то всьо протестувати правильно?

references branch (id_company, id_branch);

Неправильно. Референсить id_company треба на первинну таблицю company, а не на вторинну branch. І в бранчі також треба створить ФК на компанії.

Якщо ієрархія така — компанія-бранч-філія, то залишки треба тримати по філіях, а для всієї компанії — джойнить-групить-підсумовувать.

До 3нф тут ще дуже і дуже далеко.

Скільки записів в найбільшій таблиці?

фк на первинні ключі є всюди, це само собою.
А чим гарантувати неможливість створити запис, у якого бранч буде від іншої компанії? Достаточно одной таблєткі і отримуємо купу битих даних.
Рівень ієрархії теж може бути значно більшим 2, тож групить-підсумовувати у кожному запиті може виявитися занадто дорого. Є, звісно, кеш, але підсумки можуть використовуватися, наприклад, для тої ж валідації даних, тобто необхідність оновлення підсумкових даних в транзакції зараз приймаємо як даність.

Ієрархія у прикладі — компанія — бранч. Продажі прив’язані до бранчу, а підсумки до компанії.

записів не так щоб багато — кілька сотень тисяч. Проте схема дещо складніша, таблиць, які оновлюються в тригерах, кілька на різних рівнях. Тобто питання моє не скільки про реальну базу, скільки про сам принцип заміни джоїнів денормалізацією зі спробою гарантувати цілісність даних

А чим гарантувати неможливість створити запис, у якого бранч буде від іншої компанії?

ФК не захищає від помилок типу «не на ту компанію записали сейл». Він тільки захищає від «сейл на неіснуючу компанію» або «видалили компанію, але лишили в базі купу її рештків».

Чи я чогось не розумію?

все так. Проте в нормалізованому варіанті ми автоматично маємо валідні дані, бо сейл привєязаний до бранчу, а вже бранч до своєї компанії.
Денормалізуючи ми створюємо можливість записати не той id_company, коли продаж прив’яжеться до бранчу однієї компанії, а підсумки підуть у іншу. Звісно хотілося б уникнути навіть можливості зробити таке.

Для цього існують транзакції. Однією транзакцією апдейтить дві таблиці, а у випадку проблем — ролбек.

Вооот.
Вибачаюсь що не досить зрозуміло сформулював питання на початку треду.
Ситуація зараз приблизно така:
прилітає новий / видаляється старий sale. Багато sale.
— у відповідному тригері для sale відбувається оновлення даних в підсумковоій таблиці store.
(store — не дуже вдало названа таблиця з залишками товару в магазині.)

update store 
  inner join branch on store.id_company = branch.id_company 
set store.count=store.count-new.count 
where branch.id_branch = new.id_branch 
  and store.id_product = new.id_product
(в тригері виконується кілька таких апдейтів різних таблиць, щоб було не скучно.)
Мене дещо насторожує присутність join в update. Оновлення досить активне і кожен зайвий чих лочить рядок в store для магазину на зайву долю секунди + лок branch на час пошуку.
Для усуднення джоіну в sale додаємо поле id_company. (Це денормалізація, бо sale пов’язаний з branch який в свою чергу пов’язаний з company.) З цим полем в тригері для sale можна писати
update store 
set store.count=store.count-new.count 
where store.id_company = new.id_company
  and store.id_product = new.id_product
Але в такому випадку існує можливість створити хибний запис у таблиці sale — коли id_branch і id_company не будуть пов’язані між собою.
Для унеможливлення цього можна зробити fk sale(id_company, id_branch) -> branch (id_company, id_branch). Саме задля того, щоб не можна було використати довільну існуючу company.
Тут буде оверхед, бо ми заводимо індекси майже виключно заради fk.
І ось тут те питання, що мене цікавить — чи оверхед по підтримці додаткових складних індексів на таблицях sale і branch перекриє оверхед додаткових join в нормалізованому варіанті?
Звісно можна
Референсить id_company треба на первинну таблицю company

і просто добре тестувати код) Але я колись вже налив сміття в бд саме кривими даними в денормалізованому полі і тепер дую на воду.
Правильним було би власноруч протестувати обидва варіанти, та я не дуже зараз уявляю як це зробити.

В мене в профілі є контактний скайп, якщо дійсно треба — стукнись, все обговоримо. Але не в суботу ввечері :)

як опцію розумні люди (не я) вже прикрутили багато розумних слів. про cqrs, каюсь, навіть не читав нічого. Чомусь у мене воно асоціюється з дотнетом, а в дотнет я не ходо́к.
Зара полистаю. лбгдрю

Краще це все робити не в тригері, а в фоновій задачі. В такому випадку буде дуже швидкий відклик і в фоні можна більш складеу логіку додавати. І на мою думку краще просто змиритись що store у Вас оновлюэться не моментально, бо при навантаженнях будуть то локи то шадов селекти.

Щодо заміни джойнів на окрему таблицю в обхід нормалізації не можу нічого сказати, не побачивши реальні таблиці та реальні запити. Але імовірність ефективності такого кроку доволі висока.

Скажімо, абсолютно загальне місце — це таблиця, де накопичуються типові дані від багатьох джерел. Скажімо, сотня датчиків валить в таблицю записи виду id -- sensor_id -- timestamp -- value зі швидкістю 1000 записів в секунду. І треба отримать останні показники всіх датчиків. В такому випадку дешевше завести окрему таблицю того ж формату з UNIQUE по sensor_id та робити туди паралельний INSERT ON DUPLICATE KEY UPDATE.

Але для кількох сотень тисяч в найбільшій таблиці воно все повинне літати.

Залежить, яке форматування Ви використовуєте для конвертування дати у BIGINT.

До прикладу, якщо б Ви конвертували дату у BIGINT за наступним прикладом: «YYYYMMDD» (сьогоднішня дата — 20190402).
Ви б могли використовувати наявний функціонал бази даних:

SELECT s.* 
FROM statistic AS s
WHERE s.data >= 20190320 AND s.data <= 20190329 

весь мир давно испорльзует то что использует, но нет, мы идём своим тернистым путём и плодим свои велосипеды на свои велосипеды )))

А нічого, що встроєний тип DATE має футпрінт всього 3 байти проти 8 у BIGINT? І що це впливає на розміри індексів, які дуже бажано тримати в RAM? Я вже не кажу про можливість використовувати з DATE встроєні інструменти на кшталт DATEADD() чи table.col + INTERVAL 7 WEEK?

Як вирахувати, скільки днів пройшло між датами в бігінтах 20190101 та 20190405?

Схему треба планувати правильною в технічному плані, а на всі хотілки додавати ненормалізовані поля. Вчора місяць, сьогодні день, завтра Вам час знадобиться. Буде дуже добре, якщо Ви зможите «нові» поля перерахувати для попередніх записів.

// це якщо діапазон дат не задано, то виводу статистику за весь період...

Лучше задать вручную. За последние 30 дней к примеру. Что бы не тянуть лет через десять несколько млн. записей при каждом открытии страницы.

ух, не очікував що тема виклече настільки велийкий ажіотаж. Обновив перше повідомлення за розясненнями...

В цьому і є користь простих питань на учбових прикладах: вони дозволяють обговорення. А до того ж, сторони можуть визнати свою неправоту, не втрачаючи аж нічого.

У разі коли ті самі питання постають вже в серйозних задачах, сторони зазвичай міряються пісюнами у відкритих обговореннях, і владними повноваженнями — у закритих. Рішення зазвичай в обидвох випадках не найкращі, в останньому — ще й із неможливістю виправити в подальшому.

интереснее начнется, когда надо будет группировать по месяцам к примеру

ничего сложного, если лень возиться с логикой на бэкенде, то всегда можно докинуть поле «month» типа ENUM (+1 байт). Выборку года либо по уже существующему полю либо отдельно докинуть поле типа YEAR (+1 байт) в зависимости от того что нужно. А дальше GROUP BY в помощь. Чтоб не править уже существующую логику приложения, можно навесить триггер на таблицу, который будет автоматом заполнять эти новые поля.

угу, варианты есть, лишь бы не ударяться в GET_MONTH(date) и подобные функции в SQL

А что не так с подобными функциями? Агрегаты не так и часто собираются, не так и много людей их требует, и если подобный запрос затянет на 10 секунд при сборе статистики за 3 года — зачем что-то менять? На изменения явно не 10 секунд трата время-денег затянет.

Не так з цими функціями те, що в мусклі

WHERE MONTH(table.mydate) = 3
не покривається індексами. Взагалі. В постгресі можна створити індекс по функції від поля, в мусклі цього немає

Все одно це емуляція. З тим же успіхом можна просто тригерами по інсерту-апдейту заповнювать функціональне поле.

яка різниця між virtual column(не stored) + індекс на неї та функціональним індексом? у синтаксисі?
як зрозумів, два обмеження: 1) тільки для innoDB 2) не можна FullText index
[upd] а, ще там обмеження на вираз

Sub-queries, parameters, variables, stored functions and user-defined functions are not permitted.

та я не певен, чи часто функціональні індекси інших ЗКБД дозволяють таке

Різниця в тому, що це trick, воркераунд, а не штатний функціонал системи. Та сама різниця, що й між створенням тимчасової таблиці і заповненням її селектом для подальшого використання, і вкладеним селектом. Власне, навіть на рівні виконання процедур все може бути тотожнім, але штатний функціонал є штатний функціонал.

так з самого початку і була потрібна тільки по місяцях... а тепер захотіли переробити на діапазон заданих дат... тобто що не просто вибрати місяць, а вказати початкову, дату і кінчену і отримати витяг за статистики за цей період...

по місяцях грцпцвання вже було реалізовано... тепер от переробляю.... точніше переробив ))

1) На кой хрен тебе понадобилась дата в bigint? Почему не date?
2) За именование поля отдельно леща надо дать — что такое data? Инглиш — ду ю спик ит?
3) Ведро гнилых помидоров за отсутствие комментариев у полей. Да, в MySQL они внезапно есть.
4) AUTO_INCREMENT=20110, серьёзно? Что такое 20110 — магия? Потом удивляться будешь при переносе данных.
5) А что это ещё за UNIQUE KEY, и куда девалось PRIMARY KEY? Ты уверен, что сия конструкция тебе индекс не задублирует?

Совсем непонятно, зачем тебе в статистике отдельное ключевое поле. Если это статистика по юзерам и датам — сделай ключ по дате и пользователю, и будет тебе счастье! Можешь по пользователю и дате, зависит от того какие запросы на чтение, для чего эта статистика вообще нужна.

С датой может быть не всё просто, если проект на глобальный рынок или даже на российский. Дата пляшет от часовых поясов, это надо учитывать при хранении, регламентировать какое-то соглашение. Вплоть до того,что хранить статистику дважды (2 поля), по локальной дате и по серверной.

В твоём случае, если дата bigint, то только ты знаешь каким образом ты в неё конвертируешь. Будем надеяться, что бОльшая дата даёт бОльший bigint на выходе, иначе на раз отловишь внеиндексное сканирование. При поиске тебе надо конвертировать исходную дату в bigint, и уже по ним искать.

Акцент: конвертировать дату конечную нужно с поправкой на КОНЕЦ дня, то есть с 20-03-2019 по 29-03-2019 тебе надо искать до 30-03-2019 (не включительно).

Применять операции нужно >= и <, дай по яйцам каждому кто скажет between. Для мускула слово «between» переводится «а давай в продакшене на индексы положим с прибором, и будем искать перебором».

Для мускула слово «between» переводится «а давай в продакшене на индексы положим с прибором, и будем искать перебором».

Чувак, не лізь в те, чого не знаєш :)
Для мускля ’between’ абсолютно тотожній ’X >=a AND X <=b’

В теорії, між теорією та практикою нема різниці. На практиці є.
А ще, тут потрібно >= та <. <= не годиться.

Ну то моєї практики з 2002 року, мабуть не вистачило, аби бодай раз побачити ту різницю :) Та й оптимізатор генерує код тотожній до байта для бітвіна та двох порівнянь. Мабуть, щось знає :)

Достаточно шепнуть мускулю «EXPLAIN ваш_запрос_с_амбициями_на_правильное_мнение» и посмотреть что ключи оно таки использует как нужно, другой вопрос в том что значения нужно передавать в правильном порядке от меньшего к большему на что можно смело ложить при использовании >,<,>=,<=

другой вопрос в том что значения нужно передавать в правильном порядке от меньшего к большему

Можна детальніше?

Если вопрос по составным ключам — то это классическая рекомендация, когда первым в порядке ставится поле с большим диапазоном значений и так к самому меньшему, тогда перестроение индексов занимает минимум ресурсов да и поиск по ключам проще. Если вопрос по BETWEEN то попробуйте поставить на первом месте знечение с большим значением а на втором месте с меньшим — у меня вот выборка есть а изменения не вносятся... (5.5.62, «UPDATE ... WHERE ... BETWEEN max AND min» по крайне такое не отрабатывает, хотя в репорте показывает что выбрано столько-то строк, внесено изменений — ноль).

Ну так в мануалах одразу написано, що саме `BETWEEN min AND max`, а знаючи, що це лише синтакс-шугар, що транслюється у два порівняння, неважко здогадатися, що `BETWEEN max AND min` не буде працювати.

Рекомендації по формуванню індексів відповідно до кардинальності/щільності/селективності полів працюють в простих випадках, але в складніших усе стає значно інакше. Скажімо, дані в полі FLOAT розподілені не рівномірно, а нормально з сигма-квадратом 0.05? І ось класичні рекомендації приводять до лютого факапу.

Нема на практике разницы. Mysql between четко переводит в то, что написано. Можете в исходнике посмотреть.

Ты смотрел? Вот не поверю. Оптимизация — она ж страшнее матана почерком врача!

Дак легко смотриться, просто grep воспользоваться.
Смотрите в mariadb 3.3.13 например файл sql/item_cmpfunc.cc
вот кусок для int, там их много разных типов.

return (longlong) ((value.cmp(a) >= 0 && value.cmp(b) <= 0) != negated);

DOU підтримує HTML теги і посилання ⬇️
github.com/MariaDB/server sql/item_cmpfunc.cc

longlong Item_func_between::val_int_cmp_int()
{
  Longlong_hybrid value= args[0]->to_longlong_hybrid();
  if ((null_value= args[0]->null_value))
    return 0;					/* purecov: inspected */
  Longlong_hybrid a= args[1]->to_longlong_hybrid();
  Longlong_hybrid b= args[2]->to_longlong_hybrid();
  if (!args[1]->null_value && !args[2]->null_value)
    return (longlong) ((value.cmp(a) >= 0 && value.cmp(b) <= 0) != negated);
  if (args[1]->null_value && args[2]->null_value)
    null_value= true;
  else if (args[1]->null_value)
    null_value= value.cmp(b) <= 0;              // not null if false range.
  else
    null_value= value.cmp(a) >= 0;
  return (longlong) (!null_value && negated);
}

Конечно, у меня использован в коментарии тег code.
Просто я посчитал некорректным в дискусии привести весь код в 20 строк, а вы — нет.
Ну и искал я не по гитхабу, а локально по исходникам с помощью
grep BETWEEN * -r|grep -v test|grep -v bench

Дата пляшет от часовых поясов, это надо учитывать при хранении, регламентировать какое-то соглашение. Вплоть до того,что хранить статистику дважды (2 поля), по локальной дате и по серверной.

Я не зразу усвідомив всю прельстивість цих строчок. Просто не був морально готовий до такого :)

Відкрий для себе ’mysql_tzinfo_to_sql’

Ага, смішно. Тільки питання, ти ж не вважаєш що статистика повинна зберігати СИРІ дані по кожній транзакції, і ЩОРАЗУ обробляти сирі дані просто щоб отримати щоразу ту саму цифру?

Я вважаю, що дату-час треба зберігати в уніфікованому вигляді, яким зараз є UTC, а клієнт, якщо йому кортить іншого, вправі зробить `SET time_zone = EET;` або PST/EST/ETC — із врахуванням національного колориту, daysaving та інших державних викрутасів з годинниковими стрілками. Але, звичайно, можна завести дві сотні полів і окремо тримати час для всіх випадків глобального використання сервісу.

Ти не відповів на питання. Вважаєш, що треба щоразу перераховувати всю статистику по сирих даних? На кожен запит?

А звідки випливає необхідність перерахування статистики? Поділись юзкейсом, який ти розглядаєш.

Банально. Скажімо адміністратор переглядає статистику. І гравець переглядає статистику. Одна й та сама ПОДІЯ для них виявиться в різних часових поясах. То ж або зберігати агрегати — і тоді накопичувати дані окремо. Не обов′язково для всіх часових поясів, в тривіальному випадку вистачає двох. Або зберігати сирі дані, тобто кожну подію, і щоразу перераховувати статистику — особливо весело коли вона онлайн відображається.

Звісно, можна тупо кешувати дані на рівні аплікейшена, якщо структура це дозволяє. Але зазвичай сирих даних настільки багато, що їх взагалі не зберігають, особливо для безкоштовних ігор.

Надо на всех серверах, которые ГЕНЕРЯТ статистику держать время UTC и менять в момент запроса для каждого клиента. Выше же написано как.

Мало того, якщо критичною є умова синфазності клієнтів, то вся бізнес-логіка жорстко фіксується в одній TZ, як це робилося в PBEM, типу VGAPlanets. Де б хто б не сидів — всі ходи робляться одночасно по UTC.

Когда у тебя в статистике грубо говоря написано, что 18.03.2019 некий показатель (скажем, энергия) равен 0, потому что всё потратил, а для юзера сейчас уже 19.03.2019, и он требует свои 99 как с куста — ты ж ему обещал.

Есть 2 типа разработчиков. Первые — это которые пошлют нахер этих юзеров, ничего не шарящих в UTC.
Вторые — это те кто напишут тот самый «нахер», куда этим юзерам велкам.

Это решается на клиенте. UTC переводится в текущее время пользователя, и хинт подсказывает, во сколько по его времени будет восстановлена «энергия».
Подстраиваться под «полночь» каждого юзера слишком дорого получится, в плане разработки.

Психология — это нерешаемо, поверь. Если ты собираешься дрессировать пользователей, считай что ты сразу отдал их конкуренту.

Амазон вполне успешно дрессирует. Например в киндл есть штука с бесплатными промо, они начинаются для всех стран в 8:00 по UTC на сколько мне известно.
Биржи тоже открываются по своему времени, не подстраиваясь под тайм-зоны каждого пользователя.
Думаю есть не мало подобных примеров.

Если же это игра 3 в ряд, то понятно, что ежедневный бонус можно хоть в самом app зашить, без синхронизации с беком..

И те и другие — монополии. Когда есть IT-инфраструктура Амазона или хотя бы биржи — там уже своя бюрократия, со своими тараканами, и целыми отделами которые знают «как лучше».

При правильно зпроектованій схемі, при правильному індексуванні всі накладні витрати будуть настільки мізерні, що ними можна буде знехтувати.

Я вже багато років дивуюся, як легко фул-стеки в списки своїх компетенцій вписують SQL/RDBMS :) Я, як ДБА, навіть не намагаюся встромляти жабі катетера. Мені вистачає геморою з базами. А от дослідники, чомусь, переконані, що базі можна встромити ту саму соломину із прийнятним результатом. А потім виникають міфи про BETWEEN.

А что это ещё за UNIQUE KEY, и куда девалось PRIMARY KEY? Ты уверен, что сия конструкция тебе индекс не задублирует?

Ну, і щоби два рази не вставати.

За відсутності явно задекларованого прімарі кея іннодб створює приховане поле типу BIGINT NOT NULL AUTOINCREMENT, яке і юзається в якості РК. А отой UNIQUE буде кластеризований з прихованим РК, так що ні, нічого не продублюється.

Коротко резюмуючи, раджу тобі триматися подалі від мускля/марії. Чисто для твого ж спокою. Воно тобі не треба.

1) Константа, яка в AUTO_INCREMENT, куди прилетить, в яке поле?
2) СКІЛЬКИ буде індексів? А я нагадаю, що підтримка індексів справа не з самих легких, та вони місце займають, і оперативу.

Початкове значення автоінкремента прилетить в прихований РК, який єдиним буде створений з опцією автоінкремента. Індексів буде два. Один явний унікальний, другий прихований первинний. Про індекси я й сам можу цілий курс лекцій прочитать, годин на шість, так що не варто напружуватися :)

Константа, яка в AUTO_INCREMENT

 — да чувак просто с дампа описание таблицы выцепил и auto_increment там стоит последний на момент дампа. Так что всё нормально )))

Вот и я об том же. Когда он эту таблицу воспроизведёт из подобного определения, а потом заинсертит данные где эта константа уже выше — что случится?

Это одна из тех вещей, которые я не проверял, ибо ну его нахрен. Подозреваю, что может и не апдейтнуться. А статистика, как и логи, такая вещь которую иногда чистят.

Если кому не лень, может проверить, что будет если прописать на создании авто-инкремент скажем 1000, а потом заинсертить скажем с id=2000, и потом без id чтобы увидеть какой будет следующий.

Тут ещё веселее — primary key у чувака неявный, соответственно этого поля вообще нет в дампе. Если при инсертах эта константа автоинкремента обновляется, соответственно все записи будут с другими ключами вообще, чем были в исходной выгрузке. Чем это грозит правда непонятно, но кейз любопытный.

Всё нормально там будет, в Oracle не дятлы сидят:

CREATE TABLE xxx (
`id` int NOT NULL AUTO_INCREMENT,
`str` varchar(16) NOT NULL DEFAULT "",
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8;
INSERT INTO xxx(`id`, `str`) VALUES(2000, 'jopa v nebe');
INSERT INTO xxx(`str`) VALUES('jopa v nebe 2');
INSERT INTO xxx(`str`) VALUES('jopa v nebe 3');

mysql> SELECT * FROM xxx;
+------+---------------+
| id   | str           |
+------+---------------+
| 2000 | jopa v nebe   |
| 2001 | jopa v nebe 2 |
| 2002 | jopa v nebe 3 |
+------+---------------+
3 rows in set (0.00 sec)

mysql> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 5.5.62-0+deb8u1 |
+-----------------+
UNIQUE KEY отлично канает за PRIMARY KEY, и с автоинкрементом у него проблем опять же нет, более того PRIMARY KEY может быть не AUTO_INCREMENT )))

P.S.: Да, Алексей, не хотел бы я к тебе на собеседование попасть...

Чего? Сам факт, что на вопрос, который сейчас не принципиален, но может навредить в будущем — ты поставил эксперимент и удостоверился, что последствий нет, можно НЕ ТРОГАТЬ текущее состояние, даже не смотря на несоответствие формальностям — считай ты УЖЕ прошёл собеседование.

Грубо говоря, это значит что за тобой можно не проверять работу. То есть тебя можно сажать на испытательный срок и оценивать производительность, после чего выдвигать реальный оффер. До тех пор, если возможно, договориться за −20%. Если невозможно — хрен с ним, нагрузить работой и уже тогда торговаться по факту. В случае фриланса — не надо и этого, вот дедлайн и работай.

Кстати, это один из примеров, почему собеседование чаще всего просто потеря времени — для тех кто знает как их проводить. И отсев на псевдо-случайных критериях — для тех кто не знает.

PS. UNIQUE KEY отлично канает за PRIMARY KEY с точки зрения логики. Но Мускул не возьмёт его как праймари, а создаст суррогат. То есть будет накинут лишний индекс на таблицу и лишнее поле. В данном случае, как уже указал выше, отдельный ID здесь вообще скорее всего не требуется — в случае наличия нативного первичного ключа в модели данных, строго противопоказано иметь другой.

Малось на увазі, що на співбесіді ти оцінюватимеш не кваліфікацію претендента, а його відповідність своїм уявленням про РДБМС. Які, м’яко кажучи, дуже далекі від реальності.

Тому на співбесіді по РДБМС треба мати підняту тестову РДБМС, або ж взагалі не розраховувати отримати багато інфи від співбесіди.

Погодься, така дрібничка дуже скорочує час співбесіди і дозволяє вже через 10 хвилин відсіяти тих, для кого таблиця на 1 000 000 записів то монстр.

и БД на over 300 таблиц ну чтоб размер глаз собеседника сразу увидеть )))

300 таблиць — це дрібниці. От коли 300 таблиць і жодного зовнішнього ключа — отут очі вилазять в самих несподіваних місцях... Є такий опенсорсний проект Moodle — десятки тисяч інсталяцій, в купи академзакладів на ньому всі навчальні плани тримаються, а зазирнеш всередину — гівно і гілочки солома.

З іменами на кшталт RSM_HEGZ4 та без моделі даних бо «всі і так знають»

Краще не таблицю велику показати, а запропонувати створити власну схему під не дуже тривіальну задачу. Далі послухати аргумени «чому саме так» і стане зрозуміло рівень кандидата. А якщо правельно спроектована таблиця, то хоч на 100 лямів, всеодно буде працювати))

І не взяти — бо його схема не збігається із твоєю, апріорі геніальною?

А от що праВЕЛЬності я б не був таким впевненим — треба знати природу даних реального світу, що потрапляють в модель.

IMHO взагалі протипоказано на співбесіді давати завдання експертного рівня, тим паче рівня мистецтва. Бо наймати потрібно людей, чий досвід та знання НЕ ЗБІГАЮТЬСЯ із твоїми.

І не взяти — бо його схема не збігається із твоєю, апріорі геніальною?

Або вигнати, якщо він не поділяє твоєї іраціональної бітвінофобії?

проверить, что будет если прописать на создании авто-инкремент скажем 1000, а потом заинсертить скажем с id=2000, и потом без id чтобы увидеть какой будет следующий.

А нічого руками інсертить усіляку байду.

В твоём случае, если дата bigint, то только ты знаешь каким образом ты в неё конвертируешь.

bigint в дате обычно делают для unixtime :)

Акцент: конвертировать дату конечную нужно с поправкой на КОНЕЦ дня, то есть с 20-03-2019 по 29-03-2019 тебе надо искать до 30-03-2019 (не включительно).

Или до последней секунды — включительно. Тут разницы нет.
Был бы double — да, удобнее было бы делать меньше начала следующего дня.

Применять операции нужно >= и <, дай по яйцам каждому кто скажет between.

Про маппинг на два сравнения уже сказали — возможно, тебе когда-то попался очень кривой мускуль. Нормальная БД больше бы обрадовалась тому, что ей поступил один целый between и не надо анализировать выражение на предмет поиска, как сочетается f>=x и f<=y, что f одинаково, что между ними не влезло ничего левого и т.д.

А вот что один between вместо четырёх (closed/open+closed/open) — да, нехорошо.

Кто-то ещё использует timestamp в БД осознавая попоболь лет через 20? Разница между TIMESTAMP и DATETIME всего 1 байт начиная с MySQL 5.6.4

Я использую почти везде. Если какието из этих систем доживут до 2038, это будет джекпот. Вообще раза два-три перепишут.

Ну, когда-то не так давно уже были грабли «2000», тогда тоже никто не думал что оно доживёт до этого времени. Нет ничего более постоянного, чем временное решение )))

Ну с datatime тоже куча граблей. Некоторые товарищи до сих пор timestamp в инт пишут.
20 лет это очень долго. Это в энтерпрайзе не сильно долго.
Развечто linux tools и network protocols за это время в моей области не обновилися, даже http протокол фактически поменялся.

Я более чем уверен, что выйдет новая версия базы данных, в которой таймстемпу добавят разрядности. До тех пор СУЩЕСТВЕННО подешевеет и стоимость хранения, и стоимость оперативы, в крайнем случае нейросеть за меня как-нибудь отбрешется

Скореее из timestamp сделают depricated )))

Зачем? У таблиц НЕТ обязательства быть совместимыми между версиями. Более того, при обновлении версии таблицы скорее всего НЕ обновятся, надо будет это сделать отдельной командой.

Соответственно, у таблицы есть инфа, какой версии она есть. А значит, что неопределённости возникать не будет, просто в новой версии у таймстемпа будет на байтик больше.

Чего не скажешь о родненьком PHP — ведь у него как был INT в этом преобразовании, так и остался. Соответственно, код придётся переписывать. А до тех пор, если проблема останется, будет куча инфоповодов заметить приближающися песец всем.

КАК МНОГО вы используете кода на PHP, написанного 20+ лет назад? Я намекну, ему самому 25 всего.

Собственно потому и проще сделать depricated чем тащить этот геморой следующие 20 лет )))

Рішення:

/**
    ALTER TABLE `statistic`
    ADD INDEX `ID_USER_DATA_INDEX` (`id_user`, `data`);
 */

$from = new \DateTimeImmutable("20-03-2019");
$to = new \DateTimeImmutable("29-03-2019");

/* @var $connection \PDO */
$stmt = $connection->prepare("SELECT * FROM `statistic` WHERE `id_user` = :id_user AND `data` >= :date_from AND `date` < :date_to");
$stmt->execute([
    "id_user" => $users[$i]['id'],
    "date_from" => $from->getTimestamp(),
    "date_to" => $to->getTimestamp(),
]);

$result = $stmt->fetch();

Критика:
1. Назва таблиці `statistic`, буде ще 2-3 таблиці, будеш називати `statistic_1`
2. Якщо $users[$i][’id’] то це запроси в циклі і правильно буде об’єднати id і зробити одним запитом:

 
"SELECT * FROM `statistic` WHERE `id_user` IN (:id_users) AND `data` >= :date_from AND `date` < :date_to"
а потім вже згрупувати на стороні PHP

Так не можна робити. По-перше, умова «in» обмежена кількісно, і це доведеться перевіряти. А найголовніше, ти щойно схибив по індексу, і отримав повне сканування таблиці. В кращому випадку попадеш на індекс по даті.

Не варто економити на кількості запитів, якраз це зовсім не проблема, особливо якщо мускул по сокету під′єднаний, а не по TCP/IP.

MySQL Sandbox

CREATE TABLE IF NOT EXISTS `statistic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) NOT NULL,
  `id_org` int(11) NOT NULL,
  `id_driver` int(11) NOT NULL,
  `coins` int(11) NOT NULL,
  `data` bigint(20) NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

ALTER TABLE `statistic`
ADD INDEX `ID_USER_DATA_INDEX` (`id_user`, `data`);

EXPLAIN SELECT * FROM `statistic`
WHERE `id_user` = 1
AND `data` >= 100
AND `data` < 1000;
<pre>

<pre>
SIMPLE	statistic	NULL	range	ID_USER_DATA_INDEX	ID_USER_DATA_INDEX	12	NULL	1	100.00	Using index condition
...
EXPLAIN SELECT * FROM `statistic`
WHERE `id_user` IN (1, 2, 3)
AND `data` >= 100
AND `data` < 1000;
SIMPLE	statistic	NULL	ALL	ID_USER_DATA_INDEX	NULL	NULL	NULL	1	100.00	Using where

Блин, не подходи к mysql. Он уже давно в случае чего переводит id in в несколько id=.

Хорошо если так. Давно пора, если честно. Но я не проверял на сложных запросах, что-то мне подсказывает, возможны те же вафли что и с BETWEEN.

Сам видел, как он сие реализует? Реально, если встречает IN, создаётся временная таблица, и туда складываются результаты? Если два IN — сколько таблиц?

А прикольный вопрос... Для этого нужно отслеживать каунтеры на сферическом коне в вакууме...

Ты только что в аккурат на увольнение написал. Но перед этим тебя будут бить. За что? Составление SQL-запроса простым сцеплением строк, что практически гарантирует уязвимость типа SQL injection

Ну, давай, розказуй, що тобі в between не подобається, ми всі посміємося.

EXPLAIN тобі розкаже. Якщо в тебе є таблиці в мускулі, де хоча б 1000 записів та є індексована дата — спробуй зробити SELECT з умовою BETWEEN та ще хоча б одним фільтром — подивися як Мускул відмовиться використовувати індекс.

Що ти маєш на увазі під «ще одним фільтром»? Додаткову умову `AND z < XXX`?

Так в таких випадках завжди треба мати складний індекс по всіх/більшості/основних полях, задіяних у всіх селекторах запиту — JOIN..ON / WHERE / GROUP BY / ORDER BY. І на додачу, варто пам’ятати, що поля у складному індексі повинні бути перераховані за спаданням кардинальності принагідно для даного конкретного запиту. Різні запити, що задіюють одні й ті ж самі поля у селекторах, можуть потребувати різних індексів. Одному запиту потрібен (p,q,r,s,t) а іншому — (s,r,q,p,t). Більше того, один і той самий запит з ростом розміру таблиці чи для різних диапазонів порівняння може вимагати різних індексів.

Якщо не вміти працювати з індексами, то факап неминучий.

Cпеціально для тебе підняв кусок бекапу одного старого проекту GPS-стеження, там в табличці первинних даних майже 30 млн записів.

MariaDB [XXXXX]> SELECT DISTINCT w.vehicle_id
    ->   FROM nmea AS w
    ->   WHERE w.tstamp BETWEEN '2014-02-07 12:23:40' AND '2014-02-14 08:23:18'
    ->     AND w.latitude  BETWEEN 50.0000 AND 51.0000
    ->     AND w.longtitude  BETWEEN 20.0000 AND 21.0000;
+------------+
| vehicle_id |
+------------+
|        774 |
|        947 |
|       1091 |
|        879 |
|        890 |
+------------+
5 rows in set (0.089 sec)

І що, це схоже на скан таблиці?

Нащо було підіймати бекап, не міг на живій базі спитати SELECT?
Де структура таблиці?
Яка версія MariaDB?
Що завадило сказати EXPLAIN SELECT....

По-перше, я в тому проекті зробив свою справу і пішов далі. Все, що в мене лишилося — це схема з сетом реальних даних для лабораторних дослідів на віртуалці. Як і для всіх моїх колишніх проектів — на випадок факапу та алярму. Всяке буває.

По-друге, я НІКОЛИ не роблю дослідів на живих базах, та ще й чужих. На ноуті загрузить дамп в БД — справа кількох хвилин, навіщо наражатися на неприємності?

Ось структура таблиці:

CREATE TABLE `nmea` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`vehicle_id` INT(10) UNSIGNED NOT NULL DEFAULT '0',
	`tstamp` DATETIME NOT NULL DEFAULT '2000-12-31 22:00:00',
	`latitude` FLOAT(9,6) NOT NULL DEFAULT '0',
	`longtitude` FLOAT(9,6) NOT NULL DEFAULT '0',
        -- 40+ полів, переважно інти та флоати, і декілька сетів --

	PRIMARY KEY (`id`),
        . . . . . 
	INDEX `ts` (`tstamp`),
	INDEX `lat` (`latitude`),
	INDEX `lon` (`longtitude`),
	INDEX `tslatlon` (`tstamp`, `latitude`, `longtitude`)
	INDEX `latlonts` (`latitude`, `longtitude`, `tstamp`)
        -- ще зо три десятки індексів --
)
Ось експлейн номер один:
MariaDB [XXXXX]> EXPLAIN
    -> SELECT DISTINCT w.vehicle_id
    ->   FROM nmea AS w
    ->   WHERE w.tstamp BETWEEN '2014-02-16 12:23:40' AND '2014-02-20 08:23:18'
    ->     AND w.latitude    BETWEEN 50.0000 AND 51.0000
    ->     AND w.longtitude  BETWEEN 20.0000 AND 21.0000;
+------------------------------+----------+----------------------------------------+
| possible_keys                | key      | Extra                                  |
+------------------------------+----------+----------------------------------------+
| ts,lat,lon,tslatlon,latlonts | tslatlon | Using index condition; Using temporary |
+------------------------------+----------+----------------------------------------+
На `Using temporary` не зважай, в мене на локалхості марія (10.3.13) трошки обмежена в ресурсах.
А ось експлейн номер два:
MariaDB [XXXXX]> EXPLAIN
    -> SELECT DISTINCT w.vehicle_id
    ->   FROM nmea AS w
    ->   WHERE w.tstamp BETWEEN '2014-02-16 12:23:40' AND '2014-02-20 08:23:18'
    ->     AND w.latitude    BETWEEN 50.0000 AND 50.1000
    ->     AND w.longtitude  BETWEEN 20.0000 AND 20.1000;
+------------------------------+----------+----------------------------------------+
| possible_keys                | key      | Extra                                  |
+------------------------------+----------+----------------------------------------+
| ts,lat,lon,tslatlon,latlonts | latlonts | Using index condition; Using temporary |
+------------------------------+----------+----------------------------------------+
Внєзапно, да? Один і той самий запит, а індекси юзаються різні. Здогадаєшся, чому?

М? А зачем? Насколько я понимаю для этого запроса время выполнения по обоим индексам будет же близко к одинаковому, не?

Бо я збрехав :) Запити не однакові. Синтакс однаковий, але різниця в константах.

І ні, час виконання кожного запиту по різних USE INDEX буде різним. Достатньо різним, аби тримати два індекси.

Здогадуюсь. А ти здогадуєся, що Мускул може обрати стратегію взагалі не використовувати індекс, або ж взяти індекс по полю де в половині таблиці однакові значення?

Саме тому в Мускулі порівняно нещодавно зроблено оце
dev.mysql.com/...​0/en/optimizer-hints.html

Але ж пояснювати це движкам, які автоматично творять SQL — ну дуууже важко.

Хінти — це спроба заднім числом компенсувати кривий дизайн бази, коли вже в моделі даних нічого поміняти не можна, а треба зносить все під ноль і починать розробку заново. В таких випадках можна хінтами відтягнути кінець ще на кілька проплат від клієнта, але фінал дещо передбачуваний.

Можна віттягувати кінець і 5 років, і 10, а там або ішак дуба вріже, або падишах скопититься.

Нерідко буває так, що база оптимізована для однієї мети, а скажімо раз на 20 000 запитів до неї прилітатиме зовсім інше навантаження. Статистично то гарно виглядає, коли той 1 із 20 000 прилітає рівномірно. Але реальність ентерпрайзу така, що тими запитами може припекти користуватися в конкретний день скажімо разів 200 на годину. І оця халепа, якщо вирішується хінтами та форсами — то можна вважати за вселенське щастя.

Набагато доцільніше інкапсулювати базу в набір процедур. Код фронта-бека залишається інваріантним при будь-яких модифікаціях бази. В протилежному випадку все стає прибите цвяхами до коду фронту-беку і вже концептуально не піддається оптимізації. І тоді — да, тільки хінтами, через задній прохід.

Ото вже точно дупа! Ти так кажеш ніби то індекс це щось таке страшне і незрозуміле, про що програмістам знати не можна бо то табу.

В реалі майже всі запити де-факто працюють із індексами. І нема нічого поганого в тому, що програміст точно вкаже чого він очікує. Програмісту набагато краще зрозуміло ЩО буде знайдено, ДЕ воно лежить, НАСКІЛЬКИ воно компактне, і ЗА ЯКИМ критерієм найшвидше звузити вибірку.

Оптимізатор може лише здогадуватись, що в нього в таблицях діється і за якими принципами йде кластерізація даних. І механізм побудови тих здогадок дуже тяжкий по ресурсах, особливо коли раптом щось пішло «не так» після додавання якогось бойового навантаження.

В більшості випадків його б вимкнути на***, так щоб не робив зайвого. Але це дуже важко зробити для групи запитів, треба то в кожен запит пхати.

Програмісту набагато краще зрозуміло ЩО буде знайдено, ДЕ воно лежить, НАСКІЛЬКИ воно компактне, і ЗА ЯКИМ критерієм найшвидше звузити вибірку

Взагалі-то, фронту-беку взагалі непотрібно знати, де воно лежить, наскільки компактне і який оптимальний запит для його отримання. Він робить
call get_data(userid, section, partition, date1, date2, order) і отримує потрібне у найшвидший можливий спосіб. Що там, в глибині, яке хеві-вуду породжує необхідні йому дані — йому не повинно бути цікаво. Взагалі. Йому потрібні дані — він їх отримує.

В фронтів чи беків своя спеціалізація, їм зовсім не потрібно забивати собі голови дуже і дуже непростими речами, які можуть породжувати неприємні артефакти «на рівному місці». Бо облажається з написанням запиту хтось один, а ляже вся база, для всіх. І ніякий оптимайзер тут не допоможе, як не допоможуть і хінти.

Особисто я би вподобав коли б там були VIEW на основі заздалегідь вказаних індексів, а краще — на основі один раз сформованої оптимізації, яка вже гарантовано не буде перероблятися в процесі.

По-перше, в’юхи проблеми не ліквідують. Одним криво написаним запитом по в’юхах можна покласти базу так само, як і по первинних таблицях. Зрештою, в’ю — це всього лиш сабселект.

По-друге «оптимізація, яка гарантовано не буде модифікуватися» — це утопія.

Ну, і по-третє, процедури дають незрівнянно більше можливостей, ніж одиночний запит чи серія запитів. Більшістю плюшок SQL взагалі неможливо скористатися поза процедурами. Принаймні, у MySQL-діалекті.

Я ж кажу, якби сам не наривався на той грьобаний BETWEEN, коли він на продакшені раптом вважає його неоптимізовуваним виразом... до того ж на майже ідентичних запитах по різному...

Достаточно запомнить что мускуль:
1. работает только с одним индексом;
2. для поиска по разным критериям нужно использовать составной индекс от большего к меньшему;
3. оптимизатор запроса при большом количестве ключей в таблице может выбрать неправильный индекс и ты всегда можешь указать в запросе какой индекс использовать (принцип от большего к меньшему);
4. индексы в таблице — это всегда компромис между производительностью и ресурсами, больше индексов — медленнее вставка данных и модификация данных. Был случай когда отказ от пары-тройки составных индексов приводил к существенному увеличению производительности на вставке/модификации данных жертвуя мизерными накладными расходами при выборке.

1)Index merge вроде бы иногда работает. Начиная с 5.0. Давно уже.
2)перестановку аргументов тоже уже лет 5 назад всунули.
3) само интересно, что иногда оптимизатор выбирает «не тот», в результате запрос выполняется меньше.
вообще оптимизатор и mysql и postgress уже умнее некоторых сенйоров. Ошибается редко.
зы у меня типичная таблица 100млн в месяц, еще и partitions, все всегда ок работает.

1) index merge limitation таки присутствует, я бы не сильно полагался на эту фичу.
2) имеется ввиду принцип построения составных индексов от большего диапазона к меньшему.
3) всё зависит от способностей и одарённости тех, кто пишет запросы, так что я бы не зарекался.

Ну с 5.5 и выше не было пока никаких засад с всякими index merge. Понятно, что если вы разбираетесь, как будет выполнятся запрос — лучше укзать index hint или force. Но в принципе работает планнер уже хорошо.

Проблема НЕ в том, что хинты на индексы дадут лучше результат, а в том что результат может отличаться в тысячи раз по таймингу исполнения.

Проще говоря, эти знания нужно переносить из экспертного уровня, на уровень изучения самых основ баз данных, чтобы даже самый мальчик июнь знал, где лежат ГИГАНТСКИЕ ГРАБЛИ в запросе SELECT.

Вплоть до того, что можно не знать нормальных форм выше третьей, да и в той сомневаться в полезности. Можно не помнить как пишется запрос alter table или не знать зачем писать inner join, но индексы — это святое!

И это надо знать не через 10 лет работы, а ещё до того как получил доступ в реальный продакшен сложнее пет-проекта.

Ну, і про BETWEEN теж знать треба. Інакше ніяк. Бо виженеш з проекту нормального ДБА, а потім все піде по фулл скану.

Головна проблема роботи з РСУБД в тому, що вони не допускають agile методології. Не можна зробить спочатку базовий мінімум, а потім потроху доточувати функціонал. Все закінчиться тормозами, локами, костилями і купою гівна та гілочок. Починати треба одразу з цілісної та комплексної моделі даних, одразу треба визначати API-like сет запитів. Ну і, звичайно, не можна пускать фулстеків лізти руками в базу напряму. Бо кожен з них калапуцає запити на свій розсуд, не підозрюючи, що потім отой вінегрет з неузгоджених запитів просто неможливо обв’язати індексами. Я на багатьох проектах переконався, що оптимально — інкапсулювати базу в список процедур, доступних беку-фронту. Правильно побудована база одразу диктує увесь необхідний перелік сеттерів-геттерів. Плюс специфічні звіти по мірі необхідності. Але все це має робити ДБА, який тримає в голові усю базу в цілому, а не фронт, якому закортіло ad hoc зробить щось етакоє прямо зараз.

принцип построения составных индексов от большего диапазона к меньшему.

Це тільки мала частина сакрального знання. На оптимальність індексу впливає не тільки структура схеми, але й актуальні дані, якими вона напхана, а також — власне змінні, що ними запит лімітує виборку.

Я трошки вище виклав приклад, як для одного і того ж самого запиту оптимайзер використовує різні індекси. Чомусь для 95%™ фулстеків це виглядає, як одкровення.

То что оптимизатор запросов смотрит на размеры выборок по ключам чтоб уменьшить обращение к дисковой подсистеме и уменьшить дорогие операции не сильно большое откровение, но да, таки всегда есть нюансы...

оптимизатор и mysql и postgress уже умнее некоторых сенйоров. Ошибается редко.

Но уж если ошибается — то ошибается. На продакшене. Который N лет пахал без сбоев. ИЧСХ, не осознаёт своих ошибок, и десятки тысяч запросов шлёт на одни и те же грабли.

Возможно я снова не прав, и его уже обучили обратной связи. Но это бы как минимум потребовало существенно изменить формат индекса.

Уточнення: у випадку джойнів індексів може бути використано стільки, скільки задіяно таблиць. Це, до речі, один з моментів, який часто випускають з уваги при роботі з складними запитами. І, між іншим, якщо одна таблиця джойниться декілька разів, то для кожного джойну може використовуватись інший індекс.

За всякої можливості треба розкладати умову «between» на дві умови «більше» та «менше». Тоді оптимізатор хапає індекс. Виключення — якщо поле не індексоване. Але ж поля дати в продакшені майже завжди індексуються, особливо коли мова йде про статистику.

За всякої можливості треба розкладати умову «between» на дві умови «більше» та «менше»

Це твердження хибне.

Добре якщо так. Я зтикався із протилежним. Баг то чи фіча — не можу знати, особисто я вважав би багом. Не виключаю що то давно вже виправили — але не факт, що для усіх типів даних.

Або ті прояви взагалі не мали жодного стосунку до бітвіна, який завжди транслювався в пару порівнянь.

Дико извиняюсь, но какое поле является собственно датой?

Если следовать логике, то скорее всего поле data, просто опечатка в названии )
И не нужно пожалуйста спрашивать почему оно в bigint ) я не знаю. Наверное оптимистично настроены..

dev.mysql.com/...​es-storage-reqs-date-time
сомнительная экономия на спичках — разница между timestamp и нормальным datetime всего1 байт.

Для начала конвертируй диапазон нужных дат в bigint и потом обычным запросом строй выборку

В мусклі внутрішнє представлення типу DATE — це трьохбайтовий INT. Куди ще що перетворювати?

А в нього взагалі хз шо. Хто зберігає дату-час не у спеціально пристосованих для того типах, та ще й проводить якісь чудернацькі перетворення у біг-інти — той сам собі скажене буратіно.

WHERE `id_user`=".$users[$i]['id'] — не делайте так никогда)
Вот ссылка почему так делать нельзя
habr.com/ru/post/148151

SELECT * FROM statistic WHERE id_user=ХХХХ AND date BETWEEN '20-03-2019' AND '29-03-2019'

Ну, це його проблема — де він дату візьме. Зрештою, яке питання — така відповідь.

Проблема в between, яке з величезною вірогідністю не попаде на індекс.

всё зависит от рукожопости разработчика таблицы, особенно если нет понятия об индексах и как они работают, а также не знать об особенностях оптимизатора запросов. И нужно понимать нюанс, что большое количество индексов на все случаи жизни — тоже лютое зло.

А ще від розробника MySQL, від його настроювання (це ще щастя), врешті решт від версії. А то є тут такі, що вважають версію 5.5 межею досконалості, бо та витрачає менше оперативи.

Не вижу в таблице полей дат, но обычно это «where dateField between date1 and date»

Коментар порушує правила спільноти і видалений модераторами.

Підписатись на коментарі