Транзакции MySQL

Задача транзакций создать строгую целостность информации. Транзакции обеспечивают гарантию того, что незаконченные обновления или искаженные действия не будут фиксироваться в базе данных; серверу предоставляется возможность выполнить автоматический откат, и база данных будет сохранена.

Создадим таблицу balance

CREATE TABLE `balance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `action` varchar(255) NOT NULL,
  `val` int(11) NOT NULL,
  `total` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Проверяем вставку без транзакции

<h1>Тест транзакций MySQL 5.6</h1>
<?php
$db = mysqli_connect('localhost', 'root', '', 'transactions');
if (mysqli_connect_errno()) {
    printf("Ошибка соединения: ", mysqli_connect_error());
    die();
}
$action[] = array( 'act' => '+', 'val' => 10 );
$action[] = array( 'act' => '+', 'val' => 20 );
$action[] = array( 'act' => '-', 'val' => 5 );
$action[] = array( 'act' => '+', 'val' => 10 );
$i = 0;
// БЕЗ ТРАНЗАКЦИЙ
foreach ( $action as $key => $value ) {
	$i ++;
	// имитируем долгое выполнение на 2-ой вставке ( при этом сервер выключаю )
	if ( $i == 2 ) sleep(20);
	// получить последний total
	$row = mysqli_fetch_assoc($db->query("SELECT total FROM balance ORDER BY id DESC LIMIT 1"));
	$total = $row['total'];
	$db->query("
		INSERT INTO 
			balance
		SET
			action = '{$value['act']}',
			val = {$value['val']},
			total = $total {$value['act']} {$value['val']}
	");
	print_r($value);
}
echo '<br/>- запросы выполнены';
$db->close();

// Резльтат: выключив на паузе mysql, вставилось в таблицу balance одна запись – целостность нарушена ( в реальной жизни это могло бы например значить, что у кого-то деньги с баланса сняли, а кому-то зачислить не успели из за сбоя)

Проверяем вставку с транзакцией

<h1>Тест транзакций MySQL 5.6</h1>
<?php
$db = mysqli_connect('localhost', 'root', '', 'transactions');
if (mysqli_connect_errno()) {
    printf("Ошибка соединения: ", mysqli_connect_error());
    die();
}
$action[] = array( 'act' => '+', 'val' => 10 );
$action[] = array( 'act' => '+', 'val' => 20 );
$action[] = array( 'act' => '-', 'val' => 5 );
$action[] = array( 'act' => '+', 'val' => 10 );
$i = 0;
// С ТРАНЗАКЦИЕЙ
$db->autocommit(FALSE);
foreach ( $action as $key => $value ) {
	$i ++;
	// имитируем долгое выполнение на 2-ой вставке (выключаем mysql принудительно)
	if ( $i == 2 ) sleep(20);
	// получить последний total
	$row = mysqli_fetch_assoc($db->query("SELECT total FROM balance ORDER BY id DESC LIMIT 1"));
	$total = $row['total'];
	$db->query("
		INSERT INTO 
			balance
		SET
			action = '{$value['act']}',
			val = {$value['val']},
			total = $total {$value['act']} {$value['val']}
	");
	print_r($value);
}
if ( ! $db->commit() ) {
	$db->rollback();
	echo '<br/>- возникла ошибка откат запросов';
	die();
}
echo '<br/>- запросы выполнены';
$db->close();

// Результат: выключив mysql коммит не срабатывает и ни один запрос из транзакции не добавляется в бд. Целостность не нарушена ( в реальной жизни это могло бы например значить, что у кого-то деньги с баланса сняли, произошел сбой, но так как commit не выполнился, то rollback откатывает до начального состояния).
COMMIT – для записи изменений на диск.
ROLLBACK – игнорирует изменения, произведенные с начала транзакции.

Отсортировать вывод по IN – MySQL

Есть запрос

SELECT * FROM table WHERE id IN (118,17,113,23,72)

Необходимо вывести значения таблицы в порядке который в IN, то есть, сначала 118 далее 17 и тд. Делается это так:

SELECT * FROM table WHERE id IN (118,17,113,23,72) ORDER BY FIELD(id,118,17,113,23,72)

Большие обьемы данных 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

MySQL Event Scheduler

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

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

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

                        if ($level == 3) {
                            if ( !empty($Aktiv)) {
                                //$categories_string .= $cid;
                                $query = "SELECT * FROM
                                    vip_products_to_categories p2c,
                                    vip_products p,
                                    vip_products_description pd
                                WHERE
                                    p2c.categories_id = '".$cid."'
                                    and p.products_status = '1'
                                    and p.products_id = p2c.products_id
                                    and p.products_id=pd.products_id
                                ORDER by p.products_date_added";
                                $query = osDBquery($query);
                                $categories_string .= "<ul>";
                                while ($row1 = os_db_fetch_array($query)) {
                                    $categories_string .= "<li />[url=/product_info.php?products_id={$row1[]".$row1['products_name']."[/url]";
                                }      
                                $categories_string .= "</ul>";
                            }
                        }
  1. 1
  2. 2
Scroll Up