Как быстро удалить индексы в таблице MySQL?

#индексы #mysql

Возможно ли быстро удалить мои 7 индексов из таблицы в MySQL? Как-то например в один
запрос прописать удаление всех индексов?
Потому что в базе 400к записей и один индекс удаляется по 15 минут. Очень долго :(    

Ответы

Ответ 1

alter table `имя таблицы` drop index `имя 1 индекса`;
alter table `имя таблицы` drop index `имя 2 индекса`;
alter table `имя таблицы` drop index `имя 3 индекса`;
alter table `имя таблицы` drop index `имя 4 индекса`;
alter table `имя таблицы` drop index `имя 5 индекса`;
alter table `имя таблицы` drop index `имя 6 индекса`;
alter table `имя таблицы` drop index `имя 7 индекса`;


Иного способа избавиться от индекса нет, то есть одной пулей 7 зайцев вы убить не
сможете.


Ответ 2

Серебряная пуля есть при использовании information_schema. Можно и одним запросом:

ALTER TABLE tableName DROP INDEX first, DROP INDEX second, …;


На чистом SQL пока не хватило терпения сделать (ибо разбираться с курсором/циклами
- сейчас не охота), но с приправой ЯП можно очень удобно организовать.

Вот таким образом можно получить названия индексов для база.таблица:

SET @dbName = 'dbName';
SET @tableName = 'tableName';
SELECT DISTINCT INDEX_NAME FROM `information_schema`.`STATISTICS` WHERE TABLE_NAME
= @tableName AND TABLE_SCHEMA = @dbName AND INDEX_NAME <> 'PRIMARY';


Поскольку без удаления атрибута AI PRIMARY индекс не сбросить, то:


  INDEX_NAME <> 'PRIMARY'


Далее - по циклу вставляем названия индексов и "роняем" их.

DROP INDEX index_name ON @dbName.@tableName;


Пример на PHP:


public function dropIndexes($tableName = 'table', $dbName = 'db')
{
    $db = $this->getAdapter();

    $query = "SELECT DISTINCT INDEX_NAME as `index` FROM `information_schema`.`STATISTICS`
WHERE TABLE_NAME = '$tableName' AND TABLE_SCHEMA = '$dbName' AND INDEX_NAME <> 'PRIMARY';";

    $result = $db->fetchAll($query);

    foreach ($result as $index) {
        $query = "DROP INDEX `" . $index['index'] . "` ON `$dbName`.`$tableName`;";
        $db->query($query);
    }
}

Построение индекса в oracle

#oracle #индексация #индексы

Пусть создан индекс по строковой колонке (не важно какой). Как известно, он будет
представлять собой дерево, где каждый лист - подстрока. Каким образом устанавливается,
какой длины подстрока в листе?
    

Ответы

Ответ 1

Как известно, он будет представлять собой дерево, где каждый лист - подстрока.


Нет, это не так. В оф. документации говорится:


  The leaf blocks contain every indexed data value and a corresponding rowid used
to locate the actual row.


Каждая запись (leaf entry) в листке (leaf node) содержит пару key+value, соостоящая
из полного значения индексированой колонки и адреса-указателя на соответствующую строку
в таблице данных.

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

create table tab1 as
    select dbms_random.string('u', 80) itemNo, rpad ('A', 80, 'A') itemDs 
    from xmltable('1 to 100');

create unique index idx_tab1_uniq on tab1 (itemNo);
create index idx_tab1_same on tab1 (itemDs);

exec dbms_stats.gather_table_stats(user, upper('tab1'));

select index_name, blevel, leaf_blocks, num_rows, clustering_factor 
from user_indexes where table_name=upper('tab1') 
order by 2,3;

INDEX_NAME           BLEVEL LEAF_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
---------------- ---------- ----------- ---------- -----------------
IDX_TAB1_SAME             1           2        100                 3
IDX_TAB1_UNIQ             1           2        100                63


Получили уровень ветвления 1 по 2 блока листьев .

Проведём анализ индексов:

analyze index idx_tab1_uniq validate structure offline;
analyze index idx_tab1_same validate structure offline;
-- выполнить после анализа каждого индекса
select name, height, lf_blks, lf_rows, lf_rows_len, br_blks, br_rows, br_rows_len
from index_stats t
;

NAME                 HEIGHT    LF_BLKS    LF_ROWS LF_ROWS_LEN
---------------- ---------- ---------- ---------- -----------
IDX_TAB1_UNIQ             2          2        100        9100
IDX_TAB1_SAME             2          2        100        9200


Оба индекса не намного отличаются по длине записей:
lf_rows_len/lf_rows=avgLength 91 и 92 байта средняя длина записи.

В дампе блоков с листьями (здесь только выдержка двух первых записей в первом листе
каждого индекса) обратите внимание на длину записей:

Leaf block dump
===============
header address 139975326998628=0x7f4e8ba4a064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 78
kdxcofbo 192=0xc0
kdxcofeo 1012=0x3f4
kdxcoavs 820
kdxlespl 0
kdxlende 0
kdxlenxt 50335373=0x3000e8d
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[7942] flag: -------, lock: 0, len=90
col 0; len 80; (80):
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41
col 1; len 6; (6):  03 00 0e 7b 00 00
row#1[7852] flag: -------, lock: 0, len=90
col 0; len 80; (80):
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41
 41 41 41 41 41
col 1; len 6; (6):  03 00 0e 7b 00 01

Leaf block dump
===============
header address 139975326998628=0x7f4e8ba4a064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 78
kdxcofbo 192=0xc0
kdxcofeo 1090=0x442
kdxcoavs 898
kdxlespl 0
kdxlende 0
kdxlenxt 50335365=0x3000e85
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[7943] flag: -------, lock: 0, len=89, data:(6):  03 00 0e 7c 00 0a
col 0; len 80; (80):
 41 43 45 48 54 42 41 58 4f 52 45 5a 46 56 46 47 45 59 4a 51 53 49 4e 55 5a
 56 54 4c 52 42 4e 42 42 48 54 4f 52 48 4a 52 4a 5a 55 43 42 44 43 51 49 43
 43 48 47 42 56 41 58 58 54 4c 43 4e 59 50 59 59 4e 57 43 50 58 4f 59 44 53
 55 4d 51 4b 48
row#1[7854] flag: -------, lock: 0, len=89, data:(6):  03 00 0e 7c 00 11
col 0; len 80; (80):
 41 43 49 51 41 57 4b 4b 4d 4b 46 44 4d 57 59 54 54 42 43 56 49 4a 58 4f 44
 4e 51 41 56 49 53 54 55 47 42 49 4c 51 48 4a 4f 45 52 42 58 53 47 41 4a 56
 54 51 59 43 4f 44 50 59 51 56 45 43 44 47 51 48 4c 59 4e 46 44 48 47 55 58
 47 42 45 50 4d

Как отключить проверку уникальности индексов в MySQL?

У меня есть таблица:
CREATE TABLE `payments` ( `id` int(9) unsigned zerofill NOT NULL AUTO_INCREMENT, ... `payments_types_id` smallint(3) unsigned NOT NULL DEFAULT '1', ... `service_id` int(9) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `index_service_id` (`service_id`,`payments_types_id`), ) ENGINE=InnoDB AUTO_INCREMENT=900199076 DEFAULT CHARSET=cp1251
Проблема касается индекса index_service_id. Я хочу вставить данные в таблицу без заполнения поля service_id, а потом обновить таблицу, сделав значение этого поля равным значению поля id, что обеспечит уникальность индекса index_service_id. На время вставки и обновления таблицы я отключаю проверку уникальности индексов:
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
LOCK TABLES `payments` WRITE;
insert into `payments`(`id`,...) values (100000000, ...), ..., (999999999, ...); //поля service_id в списке параметров нет
update `payments` set `service_id` = `id`;
UNLOCK TABLES;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
но всё равно получаю ошибку:
Query: insert into payments(id, ... Error Code: 1062 Duplicate entry '0-0' for key 'index_service_id'
В чём причина ошибки? Почему она происходит даже после отключения проверки уникальности индексов? Или как правильно отключить эту проверку?
UPD
Кроме того, очень хотелось бы сделать это без изменения схемы таблицы, если это возможно.

Ответ

Документация про unique_checks
Setting this variable to 0 does not require storage engines to ignore duplicate keys. An engine is still permitted to check for them and issue duplicate-key errors if it detects them.
Эта настройка - рекомендация для storage engine, а не требование.
Mysql штука странная, но, к счастью, проверку уникальных ограничений так просто как проверку внешних ключей отключить не получится. Поэтому используйте нормальные подходы, принятые и для остальных баз данных.
Разрешить NULL, например. Но на большой табличке - не в курсе, как сейчас у mysql, можно ли удалить ограничение not null без пересборки таблицы. удалить уникальный индекс, вставить данные, создать уникальный индекс обратно. Судя по допустимости использования LOCK TABLE WRITE, это не OLTP нагрузка. Затраты времени на пересборку индекса - в зависимости от того, какая часть данных обновляется, а какая остаётся. подготовить необходимые данные в temporary table, затем сделать insert .. select из временной таблицы в реальную.
Последний пункт более интересный, не требующий снимать not null только ради записи и не надо пересобирать индекс. Вариант простой и быстрый, lock table вообще не нужен. Но есть одна большая сложность, связанная с тем, что вы хотите сделать service_id = id: у mysql нет штатной возможности получить заранее список id. Насколько это критично?
Можно выкрутиться вот таким хаком:
LOCK TABLES `payments` WRITE;
-- получаем на приложение следующее значение AI SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name = 'table_name' AND table_schema = DATABASE( ) ;
ALTER TABLE tbl AUTO_INCREMENT=(текущее_значение с прошлого шага + объём необходимых добавленных строк);
UNLOCK TABLES;
Т.е. резервируем нужного объёма пул ai, которые можем использовать при записи (при этом поле id надо указывать и считать самостоятельно)
insert into `payments`(`id`,`service_id`,`payments_types_id`) values (:ai, :ai, :type), (:ai+1, :ai+1, :type),...;
Можно в будущем внезапно получить проблемы с изменением параметром сервера, например, auto_increment_increment. И вновь table lock там, где он нафиг не нужен.
Иначе, надо понимать природу значения service_id и payments_types_id. Например, может быть вам допустимо использовать простую последовательность чисел, например, отрицательных на время записи - service_id же не объявлен как unsigned, а auto_increment в вашей таблице будет только положительным.

Как быстро удалить индексы в таблице MySQL?

Возможно ли быстро удалить мои 7 индексов из таблицы в MySQL? Как-то например в один запрос прописать удаление всех индексов? Потому что в базе 400к записей и один индекс удаляется по 15 минут. Очень долго :(

Ответ

alter table `имя таблицы` drop index `имя 1 индекса`; alter table `имя таблицы` drop index `имя 2 индекса`; alter table `имя таблицы` drop index `имя 3 индекса`; alter table `имя таблицы` drop index `имя 4 индекса`; alter table `имя таблицы` drop index `имя 5 индекса`; alter table `имя таблицы` drop index `имя 6 индекса`; alter table `имя таблицы` drop index `имя 7 индекса`;
Иного способа избавиться от индекса нет, то есть одной пулей 7 зайцев вы убить не сможете.

Построение индекса в oracle

Пусть создан индекс по строковой колонке (не важно какой). Как известно, он будет представлять собой дерево, где каждый лист - подстрока. Каким образом устанавливается, какой длины подстрока в листе?

Ответ

Как известно, он будет представлять собой дерево, где каждый лист - подстрока.
Нет, это не так. В оф. документации говорится:
The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row.
Каждая запись (leaf entry) в листке (leaf node) содержит пару key+value, соостоящая из полного значения индексированой колонки и адреса-указателя на соответствующую строку в таблице данных.
Например, построим два индекса с ключами одной и той же длины, один уникальный со случайными символами, и другой безсмысленный, с одним и тем же символом заполнителем.
create table tab1 as select dbms_random.string('u', 80) itemNo, rpad ('A', 80, 'A') itemDs from xmltable('1 to 100');
create unique index idx_tab1_uniq on tab1 (itemNo); create index idx_tab1_same on tab1 (itemDs);
exec dbms_stats.gather_table_stats(user, upper('tab1'));
select index_name, blevel, leaf_blocks, num_rows, clustering_factor from user_indexes where table_name=upper('tab1') order by 2,3;
INDEX_NAME BLEVEL LEAF_BLOCKS NUM_ROWS CLUSTERING_FACTOR ---------------- ---------- ----------- ---------- ----------------- IDX_TAB1_SAME 1 2 100 3 IDX_TAB1_UNIQ 1 2 100 63
Получили уровень ветвления 1 по 2 блока листьев .
Проведём анализ индексов:
analyze index idx_tab1_uniq validate structure offline; analyze index idx_tab1_same validate structure offline; -- выполнить после анализа каждого индекса select name, height, lf_blks, lf_rows, lf_rows_len, br_blks, br_rows, br_rows_len from index_stats t ;
NAME HEIGHT LF_BLKS LF_ROWS LF_ROWS_LEN ---------------- ---------- ---------- ---------- ----------- IDX_TAB1_UNIQ 2 2 100 9100 IDX_TAB1_SAME 2 2 100 9200
Оба индекса не намного отличаются по длине записей: lf_rows_len/lf_rows=avgLength 91 и 92 байта средняя длина записи.
Дамп блоков с листьями выглядит так (здесь только выдержка двух первых записей в первом листе каждого индекса):
Leaf block dump =============== header address 139975326998628=0x7f4e8ba4a064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 2 kdxcosdc 0 kdxconro 78 kdxcofbo 192=0xc0 kdxcofeo 1012=0x3f4 kdxcoavs 820 kdxlespl 0 kdxlende 0 kdxlenxt 50335373=0x3000e8d kdxleprv 0=0x0 kdxledsz 0 kdxlebksz 8032 row#0[7942] flag: -------, lock: 0, len=90 col 0; len 80; (80): 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 col 1; len 6; (6): 03 00 0e 7b 00 00 row#1[7852] flag: -------, lock: 0, len=90 col 0; len 80; (80): 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 col 1; len 6; (6): 03 00 0e 7b 00 01
Leaf block dump =============== header address 139975326998628=0x7f4e8ba4a064 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 1 kdxcosdc 0 kdxconro 78 kdxcofbo 192=0xc0 kdxcofeo 1090=0x442 kdxcoavs 898 kdxlespl 0 kdxlende 0 kdxlenxt 50335365=0x3000e85 kdxleprv 0=0x0 kdxledsz 6 kdxlebksz 8032 row#0[7943] flag: -------, lock: 0, len=89, data:(6): 03 00 0e 7c 00 0a col 0; len 80; (80): 41 43 45 48 54 42 41 58 4f 52 45 5a 46 56 46 47 45 59 4a 51 53 49 4e 55 5a 56 54 4c 52 42 4e 42 42 48 54 4f 52 48 4a 52 4a 5a 55 43 42 44 43 51 49 43 43 48 47 42 56 41 58 58 54 4c 43 4e 59 50 59 59 4e 57 43 50 58 4f 59 44 53 55 4d 51 4b 48 row#1[7854] flag: -------, lock: 0, len=89, data:(6): 03 00 0e 7c 00 11 col 0; len 80; (80): 41 43 49 51 41 57 4b 4b 4d 4b 46 44 4d 57 59 54 54 42 43 56 49 4a 58 4f 44 4e 51 41 56 49 53 54 55 47 42 49 4c 51 48 4a 4f 45 52 42 58 53 47 41 4a 56 54 51 59 43 4f 44 50 59 51 56 45 43 44 47 51 48 4c 59 4e 46 44 48 47 55 58 47 42 45 50 4d