Как я могу напрямую получить доступ к индексам MySQL InnoDB без клиента MySQL?

У меня есть индекс в столбцах a VARCHAR(255), b INT в таблице InnoDB. Учитывая две пары a,b, могу ли я использовать индекс MySQL для определения того, являются ли пары одинаковыми из программы c (т.е. Без использования strcmp и численного сравнения)?

  • Где индекс MySQL InnoDB, хранящийся в файловой системе?
  • Можно ли его читать и использовать из отдельной программы? Каков формат?
  • Как я могу использовать индекс для определения того, являются ли два ключа одинаковыми?

Примечание. Ответ на этот вопрос должен либо a) предоставить метод для доступа к индексу MySQL для выполнения этой задачи, либо b) объяснить, почему индекс MySQL практически невозможно получить или использовать таким образом. Ответ на конкретную платформу в порядке, и я на Red Hat 5.8.

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

Я мог бы выполнить свой запрос, используя подзапрос с подгруппой, например

SELECT c, AVG(max_val)
FROM (
    SELECT c, MAX(val) AS max_val
    FROM table
    GROUP BY a, b) AS t
GROUP BY c

Но я написал UDF, который позволяет мне делать это с помощью одного выбора, например.

SELECT b, MY_UDF(a, b, val)
FROM table
GROUP by c

Ключ здесь состоит в том, что я передаю поля a и b в UDF, и я вручную управляю a,b подгруппами в каждой группе. Столбец a является varchar, поэтому для этого требуется вызов strncmp для проверки совпадений, но он достаточно быстро.

Однако у меня есть индекс my_key (a ASC, b ASC). Вместо того, чтобы вручную проверять совпадения на a и b, могу ли я просто получить доступ и использовать индекс MySQL? То есть, могу ли я получить значение индекса в my_key для данной строки или пары a,b в c (внутри UDF)? И если да, гарантировано ли значение индекса уникальным для любого значения a,b?

Я хотел бы вызвать MY_UDF(a, b, val), а затем посмотреть значение индекса mysql (a,b) в c из UDF.

Ответы

Ответ 1

Если вы просто хотите получить доступ к индексу вне MySQL, вам придется использовать API для одного из устройств хранения MySQL. Двигатель по умолчанию - InnoDB. См. Обзор здесь: Внутренние внутри InnoDB. Это описывает (на очень высоком уровне) как расположение данных на диске, так и API для доступа к нему. Более подробное описание здесь: Embedded InnoDB.

Однако вместо написания вашей собственной программы, которая напрямую использует API-интерфейс InnoDB (что очень много), вы можете использовать один из проектов, которые уже сделали эту работу:

  • HandlerSocket: предоставляет NoSQL доступ к таблицам InnoDB, работает в UDF. Смотрите очень информативное сообщение в блоге от разработчика. Цель HandlerSocket - предоставить интерфейс NoSQL, открытый в качестве сетевого демона, но вы можете использовать ту же технику (и большую часть того же кода), чтобы предоставить что-то, что будет использоваться в запросе с MySQL.

  • memcached плагин InnoDB. предоставляет доступ к файлу memcached для таблиц InnoDB.

  • HailDB: предоставляет NoSQL доступ к таблицам InnoDB, работает поверх Embedded InnoDB. см. презентация конференции. EDIT: HailDB, вероятно, не будет работать одновременно с MySQL.

Я считаю, что любой из них может работать бок о бок с MySQL (используя одни и те же таблицы в прямом эфире) и может использоваться с C, чтобы они соответствовали вашим требованиям.

Если вы можете использовать/перенести в MySQL Cluster, см. также NDB API, прямой API и ndbmemcache, способ доступа к кластеру MySQL с помощью API memcache.

Трудно ответить, не зная, почему вы пытаетесь это сделать, потому что последствия разных подходов очень разные.

Ответ 2

Посмотрите на свой исходный запрос

SELECT c, AVG(max_val)
FROM
(
    SELECT c, MAX(val) AS max_val
    FROM table
    GROUP BY a, b
) AS t
GROUP BY c;

Сначала вы должны убедиться, что подзаголовок дает вам то, что вы хотите, запустив

SELECT c, MAX(val) AS max_val
FROM table
GROUP BY a, b;

Если результат подвыбора корректен, запустите полный запрос. Если этот результат правильный, то вы должны сделать следующее:

ALTER TABLE `table` ADD INDEX abc_ndx (a,b,c,val);

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

Написание UDF и вызов его одним SELECT - это просто маскировка подзаголовка и создание дополнительных накладных расходов, чем запросы. Простое размещение полного запроса (один вложенный проход по данным) в Хранимой процедуре будет более эффективным, поскольку получение большей части данных в UDF и выполнение одной строки выбирает итеративно (что-то вроде времени O (n log n) с возможным дольше Sending data).

ОБНОВЛЕНИЕ 2012-11-27 13:46 EDT

Вы можете получить доступ к индексу, не касаясь таблицы, делая две вещи

  • Создайте достойный индекс покрытия

    ALTER TABLE table ADD INDEX abc_ndx (a, b, c, val);

  • Запустите запрос SELECT, о котором я упоминал ранее

Так как все столбцы запроса все в индексе, оптимизатор запросов будет касаться только индексных страниц (или индексов прецедента). Если таблица MyISAM, вы можете...

  • настройте таблицу MyISAM, чтобы иметь выделенный кеш-ключ, который можно предварительно загрузить при запуске mysqld
  • запустите SELECT a,b,c,val FROM table;, чтобы загрузить страницы индексирования в ключевой кэш MyISAM

Поверьте мне, вы действительно не хотите получать доступ к страницам индексов против mysqld. Что я имею в виду?

Для MyISAM страницы индекса для таблицы MyISAM хранятся в файле .MYI таблицы. Каждый оператор DML вызовет полную блокировку таблицы.

Для InnoDB страницы индекса загружаются в пул буферов InnoDB. Следовательно, связанные страницы данных будут загружаться в пул буферов InnoDB, а также.

Вам не нужно обойти доступ к страницам индекса с использованием Python, Perl, PHP, С++ или Java из-за постоянного ввода-вывода, необходимого MyISAM или константе MVCC, осуществляемый InnoDB.

Существует парадигма NoSQL (называемая HandlerSocket), которая позволила бы низкоуровневому доступу к таблицам MySQL, которые могли бы чисто обходить обычные шаблоны доступа mysqld. Я бы не рекомендовал его, так как в нем была ошибка при использовании для публикации.

ОБНОВЛЕНИЕ 2012-11-30 12:11 EDT

Из вашего последнего комментария

Я использую InnoDB, и я вижу, как модель MVCC усложняет ситуацию. Однако, по-видимому, InnoDB хранит только одну версию (самую последнюю) в индексе. Шаблон доступа для соответствующих таблиц - это write-once, read-many, поэтому, если доступ к индексу можно получить, он может обеспечить единую, надежную привязку для каждого ключа.

Когда дело доходит до InnoDB, MVCC ничего не усложняет. Это может стать вашим лучшим другом при условии:

  • если вы включили autocommit (по умолчанию он должен быть включен)
  • шаблон доступа для соответствующих таблиц - однократный, read-many

Я ожидал бы, что доступные страницы индекса будут сидеть в пуле буферов InnoDB практически навсегда, если он будет читаться повторно. Я бы просто удостоверился, что ваш innodb_buffer_pool_size установлен достаточно высоко, чтобы хранить необходимые данные InnoDB.

Ответ 3

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

Если вы установите накрывающие индексы в правильном порядке, MySQL не будет извлекать одну страницу с жесткого диска, а выводить результат непосредственно из индекса. Там ничего быстрее, чем это.

Обратите внимание, что ваш подзаголовок может оказаться на соблазнительном диске, если его результат будет больше, чем ваш tmp_table_size или max_heap_table_size.

Проверьте состояние Created_tmp_tables_disk_tables, если вы не уверены.

Подробнее о том, как MySQL использует внутренние временные таблицы, вы найдете здесь http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

Если вы хотите, опубликуйте свою структуру таблицы для просмотра.

Ответ 4

Нет. Практически невозможно использовать индекс MySQL изнутри программы C, обращаясь к индексу MySQL, не зависящим от механизма MySQL, для проверки того, являются ли две (a, b) пары (ключи) одинаковыми или нет.

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

Q: Знаете ли вы, где индекс mysql хранится в файловой системе?

Местоположение индекса в файловой системе будет зависеть от механизма хранения для таблицы. Для движка MyISAM индексы хранятся в файлах .MYI в каталоге datadir/database; Индексы InnoDB хранятся в файле управляемых табличных пространств InnoDB. f innodb_file_per_table была задана при создании таблицы, будет отдельный файл .ibd для каждой таблицы в подкаталоге innodb_data_home_dir/database.

Q: Знаете ли вы, что такое формат?

Формат хранения для каждого механизма хранения отличается от MyISAM, InnoDB и др., а также зависит от версии. У меня есть некоторое знакомство с тем, как хранятся данные, с точки зрения того, что MySQL требует от механизма хранения. Подробная информация о внутренних деталях будет специфичной для каждого двигателя.

Q: Что делает его непрактичным?

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

Как указал Кваснуй в своем комментарии к вашему вопросу, он совершенно не понимает, какую конкретную проблему вы пытаетесь решить, создав UDF или получив доступ к индексам MySQL извне MySQL. Я уверен, что Quassnoi будет иметь хороший способ выполнить то, что вам нужно, с помощью эффективного оператора SQL.