Меню
Что нового?

Mysql проверка значения поля при партицировании

grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
У нас есть таблица INNODB на N миллионов записей. Хотим ее отпартицировать по полю, которое сейчас типа enum, но по enum партицировать нельзя. Можно конвертнуть в char.

Вопрос вот в чем: если enum конвертнуть в char, проверка WHERE field="string" будет выполняться один раз для выбора партиции, или для каждой записи внутри партиции тоже?
 
флоппик

флоппик

promotor fidei
Команда форума
Партнер клуба
PHP: 
CREATE TABLE tst (e1 ENUM('a', 'b', 'c')) ENGINE=Innodb
PARTITION BY KEY(e1)
PARTITIONS 3;
Your SQL query has been executed successfully ( Query took 0.1694 sec )
ЧЯДНТ?
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
хмм все виды пробовал кроме key ... щас попробую, спасибо
 
флоппик

флоппик

promotor fidei
Команда форума
Партнер клуба
Если данных много, то лучше наверно PARTITION BY LINEAR KEY
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
EXPLAIN PARTITIONS SELECT count(*) FROM ptest WHERE name = 'orders_id'
показывает, что используются все партиции

Код: 
CREATE TABLE `ptest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` enum('orders_id','products_id','customers_id') NOT NULL,
  PRIMARY KEY (`id`,`name`)
) ENGINE=InnoDB
PARTITION BY LINEAR KEY(`name`)
PARTITIONS 3;
http://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html
Pruning can be used only on integer columns of tables partitioned by HASH or KEY.

такое партицирование даже вредно :(

народ, кто знает, что будет при конвертировании поля в char?
 
флоппик

флоппик

promotor fidei
Команда форума
Партнер клуба
кмк, дело в том что у тебя первичный ключ составной с этой колонкой? по идее, count(*) в иннодб работает по индексному ряду. т.е. независимо от типа поля `name` будет та же фигня? фиг его знает, я не настолько хорошо знаю потроха мускула :)
 
fixxxer

fixxxer

К.О.
Партнер клуба
отпартицировать по полю, которое сейчас типа enum
Нажмите для раскрытия...
Ой, а не проще табличек вида table_$enum наклепать?
Или это такая попытка изобразить наследование а-ля постгрес? :)

Я вот после того, как прочитал статейку, не хочу это трогать никоим образом:)
 
флоппик

флоппик

promotor fidei
Команда форума
Партнер клуба
Порывшись, пришел к выводу, что «внутре у нее неонка». Согласен с fixxxer похоже, партиционирование в мускуле это миф :)
 
svetasmirnova

svetasmirnova

маленький монстрик
народ, кто знает, что будет при конвертировании поля в char?
Нажмите для раскрытия...
Всё хорошо будет:

PHP: 
mysql> CREATE TABLE `ptest` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,   
    -> name char(15),
    ->   PRIMARY KEY (`id`,`name`)
    -> ) ENGINE=InnoDB
    -> PARTITION BY LINEAR KEY(`name`)
    -> PARTITIONS 3;
Query OK, 0 rows affected (0.27 sec)

mysql> insert into ptest(name) values('orders_id'),('products_id'),('customers_id');
Query OK, 3 rows affected (0.08 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT count(*) FROM ptest WHERE name = 'orders_id';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ptest | p1         | index | NULL          | PRIMARY | 19      | NULL |    2 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> insert into ptest(name) values('orders_id'),('products_id'),('customers_id'), ('order_id');
Query OK, 4 rows affected (0.10 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> EXPLAIN PARTITIONS SELECT count(*) FROM ptest WHERE name = 'orders_id';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ptest | p1         | index | NULL          | PRIMARY | 19      | NULL |    4 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT count(*) FROM ptest WHERE name = 'order_id';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ptest | p2         | index | NULL          | PRIMARY | 19      | NULL |    3 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN PARTITIONS SELECT count(*) FROM ptest WHERE name = 'products_id';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ptest | p1         | index | NULL          | PRIMARY | 19      | NULL |    4 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
А касательно первого вопроса обычный EXPLAIN и SHOW STATUS LIKE "Handler_%' нам в помощь:

PHP: 
mysql> insert into ptest(name) select name from ptest;
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> insert into ptest(name) select name from ptest;
Query OK, 14 rows affected (0.07 sec)
Records: 14  Duplicates: 0  Warnings: 0

mysql> insert into ptest(name) select name from ptest;
Query OK, 28 rows affected (0.09 sec)
Records: 28  Duplicates: 0  Warnings: 0

mysql> insert into ptest(name) select name from ptest;
Query OK, 56 rows affected (0.09 sec)
Records: 56  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT count(*) FROM ptest WHERE name = 'orders_id';
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | ptest | index | NULL          | PRIMARY | 19      | NULL |   64 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.01 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) FROM ptest WHERE name = 'orders_id';
+----------+
| count(*) |
+----------+
|       32 |
+----------+
1 row in set (0.01 sec)

mysql> show status like 'ha%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 1     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 64    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
16 rows in set (0.00 sec)
В данном случае все строки в партиции будут просматриваться. Но это, как бы и так понятно: InnoDB отдельно количество строк в партиции не хранит.

Для более же выборочного вопроса будут просмотрены строки как для обычной таблицы:

PHP: 
mysql> alter table ptest add column color char(15);
Query OK, 112 rows affected (0.82 sec)
Records: 112  Duplicates: 0  Warnings: 0

mysql> alter table ptest add key (color);
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> update ptest set color = 'red';            
Query OK, 112 rows affected (0.14 sec)
Rows matched: 112  Changed: 112  Warnings: 0

mysql> update ptest set color = 'green' where id%2=0;
Query OK, 56 rows affected (0.10 sec)
Rows matched: 56  Changed: 56  Warnings: 0

mysql> update ptest set color = 'blue' where id%3=0;
Query OK, 37 rows affected (0.08 sec)
Rows matched: 37  Changed: 37  Warnings: 0

mysql> EXPLAIN SELECT count(*) FROM ptest WHERE name = 'orders_id' and color='blue';
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | ptest | ref  | color         | color | 16      | const |   20 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql> flush status;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT count(*) FROM ptest WHERE name = 'orders_id' and color='blue';
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql> show status like 'ha%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     |
| Handler_read_last          | 0     |
| Handler_read_next          | 20    |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
16 rows in set (0.00 sec)
Ключ тоже будет соответствующий использован.
 
grigori

grigori

( ͡° ͜ʖ ͡°)
Команда форума
Спасибо, Света! :) лучший ответ из всех возможных

fixxxer написал(а):
Ой, а не проще табличек вида table_$enum наклепать?
Нажмите для раскрытия...
уже клепаем, но долго: говнокод 5-летней давности с запросами вида FROM $table
а табличка N гиг и че-то надо делать прям щас

Я вот после того, как прочитал статейку
Нажмите для раскрытия...
ох %$@!
 
Войдите или зарегистрируйтесь для ответа.
Сверху