СОЗДАНИЕ САЙТОВ | Разработка сайтов - Veselov.sumy.ua

MySQL шпаргалка ( MySQL Cheat Sheet) #

Краткий справочник для MySQL, включает в себя функции, типы, и простые запросы.
Смотреть PNG картинку

Большие обьемы данных MySQL (bigdata) и партиционирование таблиц #

Столкнулся с проблемой больших обьемов данных в MySQL. На помощь пришла относительно новая возможность mysql партицирование данных. Если коротко, то это разбиение таблиц на логические части по определенным критериям. И дает это прирост скорости работы с таблицами.

Смотрим поддерживается ли у нас:

HOW VARIABLES LIKE '%partition%';
have_partitioning | YES

Какие способы «разделения» данных предоставляет mySQL?
1. RANGE
По диапазону значений

PARTITION BY RANGE (store_id) (
 PARTITION p0 VALUES LESS THAN (10),
 PARTITION p1 VALUES LESS THAN (20),
 PARTITION p3 VALUES LESS THAN (30)
 );

2. LIST
По точному списку значений

 PARTITION BY LIST(store_id) (
 PARTITION pNorth VALUES IN (3,5,6,9,17),
 PARTITION pEast VALUES IN (1,2,10,11,19,20)
 )

3. HASH
Вы никак не управляете партицированием, просто указываете, по какому полю строить хеш и сколько «подтаблиц» создавать.

 PARTITION BY HASH(store_id)
 PARTITIONS 4;

4. KEY
Почти то же самое что и HASH, но более логично — по ключу.

 PARTITION BY KEY(s1)
 PARTITIONS 10;

Первый раз я попробовал разбить по RANGE DATETIME.

ALTER TABLE flows
PARTITION BY RANGE( TO_DAYS(date) ) (
 PARTITION y2013m10 VALUES LESS THAN( TO_DAYS('2013-10-01') ),
 PARTITION y2013m11 VALUES LESS THAN( TO_DAYS('2013-11-01') ),
 PARTITION y2013m12 VALUES LESS THAN( TO_DAYS('2013-12-01') )
);

Но получил:

[Error] 1503 - A PRIMARY KEY must include all columns in the table partitioning function

Вылез неприятный момент из за того что, поле по которому делаются партиции должно быть PRIMARY KEY, то есть уникальным.
А данные в таблицу пишутся не прерывно и вполне возможно что в 1 секунду писаться несколько значений. поэтому такое разделение мне не подойдет =(

Решено было найти id где заканчиваются года и сделать альтер по ним, как-то так. То есть разделение будет до 2013 года и после. При необходимости можно разбить мельче.

ALTER TABLE flows
PARTITION BY RANGE (id) (
        PARTITION y2013 VALUES LESS THAN( 231607217 ),
        PARTITION y2014 VALUES LESS THAN(MAXVALUE)
);

Что мы получаем? Первая «таблица» будет хранить данные за «архивный» период, до 2013 года, вторая — все остальное.

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

И ликбез: http://www.rldp.ru/mysql/mysqlpro/parts.htm

Удалить дублирующиеся записи в базе mysql (duplicate entry) #

Создаем чистую таблицу аналогичной то что у нас есть с неуниклаьными значениями

CREATE TABLE clean LIKE options

Создаем индексы для нужных полей

ALTER IGNORE TABLE clean ADD UNIQUE INDEX (add1, add2, add3)

Дальше вставляем данные из нашей старой базы в новую с параметром IGNORE

INSERT IGNORE INTO clean SELECT * FROM options

Удаляем старую таблицу

DROP TABLE options

И переименовываем новую в старую

RENAME TABLE clean TO options

Мощь MySQL #

ON DELETE CASCADE

CREATE TABLE test1 ( 
   id INTEGER NOT NULL, 
   parent INTEGER, 
   PRIMARY KEY (id), 
   FOREIGN KEY (parent) REFERENCES test1 (id) 
     ON UPDATE CASCADE 
     ON DELETE CASCADE 
 ); 

правило ON DELETE CASCADE, заданное для внешнего ключа по полю PARENT. Благодаря нему, при удалении узла будет автоматически удалено и все поддерево, для которого этот узел является корнем.

ON DUPLICATE KEY

// находим объект
 $row = query('SELECT * FROM table WHERE id=1');
 // проверяем есть ли такой объект
 if ($row) {
 // делаем апдейт
 query('UPDATE table SET column=column+1 WHERE id=1');
 }
 else {
 // делаем вставку
 query('INSERT INTO table SET column=1, id=1');
 }

Можно заменить на запрос без php

INSERT INTO table SET column = 1, id=1 ON DUPLICATE KEY UPDATE column = column + 1

INSERT IGNORE

// находим объект
 $row = query('SELECT * FROM table WHERE id=1');
 // если такого объекта нет, то вставляем новую запись
 if (!$row) {
 query('INSERT INTO table …');
 }

заменяем на запрос без php

INSERT IGNORE INTO table …

MySQL Event Scheduler #

Есть проект в котором при заходе юзера(каждого!) на страницу авторизации, а это первая(главная) страница сайта, выполняются запросы к бд:
- на очистку таблицы антибрут которым уже больше 1-го часа
- на очистку таблицы антиспам которым уже больше 1-го часа
- посчитать количество неудачных попыток с этого айпи
- посчитать количество отправленных писем с этого айпи
Хоть это легкие запросы, все же это напрягало, 4 запроса на то, что бы подсовывать капчу, или нет.

Логично что нужно избавится от первых 2 запросов(не зачем каждому юзеру выполнять их). Как решение, ставить скрипт по крону - можно, но оказывается - не нужно.
В бд MySQL есть свой Event Scheduler DAEMON!

1) Включаем MySQL Event Scheduler

SET GLOBAL event_scheduler = 1;

2) Создаем Event-ы

CREATE EVENT antibrut
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM _antibrut WHERE logintime < DATE_SUB(NOW(),INTERVAL 1 HOUR)

CREATE EVENT antispam
ON SCHEDULE EVERY 1 HOUR
DO
DELETE FROM _antispam WHERE time < DATE_SUB(NOW(),INTERVAL 1 HOUR)

Дополнительно
Посмотреть Event-ы:

SHOW EVENTS;

Если хотить модифицирывать ваш Event, делать через ALTER, например:

ALTER antibrut 
ON SCHEDULE EVERY 5 HOUR 
STARTS TIMESTAMP + 3 HOUR 

Удалить Event:

DROP EVENT antispam;

Список товаров в категории ShopOS #

Немного теории:

По топику понятно, что он посвящён именно двигу ShopOS. Классный, в бесплатной сборке он идет без почты (функция умело удалена), но имея гугл и сноровку можно допилить его, что бы он заработал как полнофункциональный магазинчик. Дальше о категориях.

Для начало нужно было получить вот такое меню:

Что бы при нажатии на главную категорию выпадал список товаров. Не долго мучаясь отписал на официальном форуме . Увы ответов не получил, готовых решений нет. Понял, что придётся самому.

С ЧПУ (вставить в index.html вашего шаблона):


На выходе:


Оказалось что необходимость в данном меню отпадает, но появляется новая!

Класс! Теперь есть еще уровень вложенности.

С ЧПУ (вставить в index.html вашего шаблона):


На выходе:



Мне ЧПУ не надо - БЕЗ ЧПУ (вставить в index.html вашего шаблона):


На выходе:

Получили что хотели, на не совсем так. Теперь я не могу попасть в раздел КАТЕГОРИИ, он то не кликабельный (я использую ява меню при клике на категорию выпадает вложенный UL), а это существенно и в ТЗ прописано.

Поэтому этот вариант мне не подходит, совсем. Будем работать с {$box_CATEGORIES}.

Идем в наш_сайт\themes\наш_шаблон\source\inc\show_category.inc.php. Проверяем что бы параметр $MaxLevel равен был 1, это значит, что по умолчанию будет показываться 1 уровень вложенности.
В том же файлике находим:

и после вставляем:

Итого: если есть параметр cPath (он показывает категорию текущую), то смотрим вложенная она, если да, то разбиваем и получаем айди категории и подкатегории. А дальше запрос - рисуем список.

Теперь, когда вы нажимаете на подкатегорию выпадает еще и список товаров. Оптимизируйте и ковыряйте -) Да и не забудьте в коде поменять префикс БД на свой.

UPD 02.09.11. для еще одного уровня + оптимизирован код.
вставить в оригинальный файл show_category.inc.php после ( $foo[$cid]['name'].$ProductsCount. ''; - 114 строчка)