14.21.5.1 Adapting an Existing MySQL Schema for the InnoDB memcached Plugin — MySQL 5.7

14.21.5.1 Адаптация существующей схемы MySQL для плагина InnoDB memcached

Рассмотрите эти аспекты приложений memcached при адаптации существующей схемы MySQL или приложения для использования плагина daemon_memcached:

Ключи memcached не могут содержать пробелы или новые строки, так как эти символы используются в качестве разделителей в ASCII-протоколе. Если вы используете значения поиска, содержащие пробелы, преобразуйте или хешируйте их в значения без пробелов перед использованием их в качестве ключей в вызовах add(), set(), get() и так далее. Хотя теоретически эти символы разрешены в ключах в программах, использующих двоичный протокол, вы должны ограничить символы, используемые в ключах, для обеспечения совместимости с широким спектром клиентов.
Если в таблице InnoDB есть короткий числовой столбец, используйте его в качестве уникального ключа поиска для memcached, преобразовав целое число в строковое значение. Если сервер memcached используется для нескольких приложений или с более чем одной таблицей InnoDB, рассмотрите возможность изменения имени для обеспечения уникальности. Например, добавьте в начало имя таблицы или имя базы данных и имя таблицы перед числовым значением.

Примечание

Плагин daemon_memcached поддерживает вставки и чтение в сопоставленных таблицах InnoDB, у которых столбец INTEGER определен как первичный ключ.
Нельзя использовать разбиение таблиц для данных, запрошенных или хранящихся с помощью memcached.
Протокол memcached передает числовые значения в виде строк. Чтобы сохранить числовые значения в основной таблице InnoDB, для реализации счётчиков, которые могут быть использованы в SQL-функциях, таких как SUM() или AVG(), например:
- Используйте столбцы типа VARCHAR с достаточным количеством символов для хранения всех цифр наибольшего ожидаемого числа (и дополнительных символов, если это необходимо для знака минус, десятичной точки или обоих).
- В любом запросе, выполняющем арифметические операции с использованием значений столбцов, используйте функцию CAST() для преобразования значений из строки в целое число или в какой-либо другой числовой тип. Например:
```
# Alphabetic entries are returned as zero.

SELECT CAST(c2 as unsigned integer) FROM demo_test;

# Since there could be numeric values of 0, can't disqualify them.
# Test the string values to find the ones that are integers, and average only those.

SELECT AVG(cast(c2 as unsigned integer)) FROM demo_test
  WHERE c2 BETWEEN '0' and '9999999999';

# Views let you hide the complexity of queries. The results are already converted;
# no need to repeat conversion functions and WHERE clauses each time.

CREATE VIEW numbers AS SELECT c1 KEY, CAST(c2 AS UNSIGNED INTEGER) val
  FROM demo_test WHERE c2 BETWEEN '0' and '9999999999';
SELECT SUM(val) FROM numbers;
```
  Примечание
  
  Любые буквенные значения в наборе результатов преобразуются в 0 вызовом CAST(). При использовании функций, таких как AVG(), которые зависят от количества строк в наборе результатов, включайте условия WHERE для фильтрации нечисловых значений.
Если столбец InnoDB, используемый в качестве ключа, может иметь значения длиной более 250 байт, хешируйте значение до значения меньше 250 байт.
Чтобы использовать существующую таблицу с плагином daemon_memcached, определите запись для неё в таблице innodb_memcache.containers. Чтобы сделать эту таблицу стандартной для всех запросов memcached, укажите значение default в столбце name, затем перезапустите сервер MySQL, чтобы изменения вступили в силу. Если вы используете несколько таблиц для разных категорий данных memcached, настройте несколько записей в таблице innodb_memcache.containers со значениями name по вашему выбору, затем отправьте запрос memcached в форме get @@name или set @@name в приложении для указания таблицы, которая будет использоваться для последующих запросов memcached.

Пример использования таблицы, отличной от предопределённой таблицы test.demo_test, см. в примере 14.13, «Использование собственной таблицы с приложением InnoDB memcached». Структуру необходимой таблицы см. в разделе 14.21.7, «Внутреннее устройство плагина InnoDB memcached».
Для использования нескольких значений столбца таблицы InnoDB со значениями пар ключ-значение memcached укажите имена столбцов, разделённые запятыми, точками с запятой, пробелами или символами «|» в поле value_columns записи в таблице innodb_memcache.containers для таблицы InnoDB. Например, укажите col1,col2,col3 или col1|col2|col3 в поле value_columns.

Объедините значения столбцов в одну строку, используя символ «|» в качестве разделителя, прежде чем передавать строку в вызовы memcached add или set. Строка автоматически распаковывается в правильный столбец. Каждый вызов get возвращает одну строку, содержащую значения столбцов, также ограниченную символом «|». Вы можете распаковать значения с помощью соответствующей синтаксической конструкции языка приложения.

Пример 14.13 Использование собственной таблицы с приложением InnoDB memcached

В этом примере показано, как использовать собственную таблицу с примером приложения Python, которое использует memcached для обработки данных.

Пример предполагает, что плагин daemon_memcached установлен, как описано в разделе 14.21.3 «Настройка плагина InnoDB memcached». Он также предполагает, что ваша система настроена для выполнения скрипта Python, который использует модуль python-memcache.

Создайте таблицу multicol, которая хранит информацию о странах, включая население, площадь и данные о стороне рулевого управления ('R' для правого и 'L' для левого).

mysql> USE test;

mysql> CREATE TABLE `multicol` (
        `country` varchar(128) NOT NULL DEFAULT '',
        `population` varchar(10) DEFAULT NULL,
        `area_sq_km` varchar(9) DEFAULT NULL,
        `drive_side` varchar(1) DEFAULT NULL,
        `c3` int(11) DEFAULT NULL,
        `c4` bigint(20) unsigned DEFAULT NULL,
        `c5` int(11) DEFAULT NULL,
        PRIMARY KEY (`country`)
        ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Вставьте запись в таблицу innodb_memcache.containers, чтобы плагин daemon_memcached мог получить доступ к таблице multicol.
```
mysql> INSERT INTO innodb_memcache.containers
       (name,db_schema,db_table,key_columns,value_columns,flags,cas_column,
       expire_time_column,unique_idx_name_on_key)
       VALUES
       ('bbb','test','multicol','country','population,area_sq_km,drive_side',
       'c3','c4','c5','PRIMARY');

mysql> COMMIT;
```
- Запись innodb_memcache.containers для таблицы multicol задает значение name, равное 'bbb', что является идентификатором таблицы.
  
  Примечание
  
  Если для всех приложений memcached используется одна таблица InnoDB, значение name можно установить на default, чтобы избежать использования обозначений @@ для переключения таблиц.
- Столбец db_schema устанавливается в значение test, которое является именем базы данных, где находится таблица multicol.
- Столбец db_table устанавливается в значение multicol, которое является именем таблицы InnoDB.
- key_columns устанавливается в уникальное значение столбца country. Столбец country определен как первичный ключ в определении таблицы multicol.
- Вместо одного столбца таблицы InnoDB для хранения составного значения данных, данные делятся между тремя столбцами таблицы (population, area_sq_km и drive_side). Для поддержки нескольких столбцов значений в поле value_columns указывается список столбцов, разделенных запятыми. Столбцы, определенные в поле value_columns, используются при хранении или извлечении значений.
- Значения для полей flags, expire_time и cas_column основаны на значениях, используемых в примере таблицы demo.test. Эти поля обычно не имеют существенного значения в приложениях, использующих плагин daemon_memcached, поскольку MySQL сохраняет данные синхронизированными, и нет необходимости беспокоиться о том, что данные истекут или станут устаревшими.
- Поле unique_idx_name_on_key устанавливается в PRIMARY, которое ссылается на первичный индекс, определенный для уникального столбца country в таблице multicol.
Скопируйте пример приложения Python в файл. В этом примере образец скрипта копируется в файл под названием multicol.py.

Пример приложения Python вставляет данные в таблицу multicol и извлекает данные для всех ключей, демонстрируя, как получить доступ к таблице InnoDB через плагин daemon_memcached.
```
import sys, os
import memcache

def connect_to_memcached():
  memc = memcache.Client(['127.0.0.1:11211'], debug=0);
  print "Connected to memcached."
  return memc

def banner(message):
  print
  print "=" * len(message)
  print message
  print "=" * len(message)

country_data = [
("Canada","34820000","9984670","R"),
("USA","314242000","9826675","R"),
("Ireland","6399152","84421","L"),
("UK","62262000","243610","L"),
("Mexico","113910608","1972550","R"),
("Denmark","5543453","43094","R"),
("Norway","5002942","385252","R"),
("UAE","8264070","83600","R"),
("India","1210193422","3287263","L"),
("China","1347350000","9640821","R"),
]

def switch_table(memc,table):
  key = "@@" + table
  print "Switching default table to '" + table + "' by issuing GET for '" + key + "'."
  result = memc.get(key)

def insert_country_data(memc):
  banner("Inserting initial data via memcached interface")
  for item in country_data:
    country = item[0]
    population = item[1]
    area = item[2]
    drive_side = item[3]

    key = country
    value = "|".join([population,area,drive_side])
    print "Key = " + key
    print "Value = " + value

    if memc.add(key,value):
      print "Added new key, value pair."
    else:
      print "Updating value for existing key."
      memc.set(key,value)

def query_country_data(memc):
  banner("Retrieving data for all keys (country names)")
  for item in country_data:
    key = item[0]
    result = memc.get(key)
    print "Here is the result retrieved from the database for key " + key + ":"
    print result
    (m_population, m_area, m_drive_side) = result.split("|")
    print "Unpacked population value: " + m_population
    print "Unpacked area value      : " + m_area
    print "Unpacked drive side value: " + m_drive_side

if __name__ == '__main__':

  memc = connect_to_memcached()
  switch_table(memc,"bbb")
  insert_country_data(memc)
  query_country_data(memc)

  sys.exit(0)
```
Примечания по приложению Python:
- Для запуска приложения не требуется авторизация в базе данных, так как обработка данных выполняется через интерфейс memcached. Единственная необходимая информация — номер порта на локальной системе, на котором слушает демон memcached.
- Чтобы убедиться, что приложение использует таблицу multicol, вызывается функция switch_table(), которая выполняет фиктивный запрос get или set с использованием обозначений @@. Значение name в запросе равно bbb, что является идентификатором таблицы multicol, определенным в поле innodb_memcache.containers.name.
  
  В реальном приложении может использоваться более описательное значение name. Этот пример просто иллюстрирует, что идентификатор таблицы указывается, а не имя таблицы в запросах get @@....
- Функции-утилиты для вставки и запроса данных демонстрируют, как преобразовать структуру данных Python в значения, разделенные символом «|», для отправки данных в MySQL с помощью запросов add или set, и как распаковать значения, разделенные символом «|», возвращаемые запросами get. Эта дополнительная обработка необходима только при сопоставлении одного значения memcached с несколькими столбцами таблицы MySQL.

Запустите пример приложения Python.

$> python multicol.py

В случае успеха пример приложения вернёт следующий вывод:

Connected to memcached.
Switching default table to 'bbb' by issuing GET for '@@bbb'.

==============================================
Inserting initial data via memcached interface
==============================================
Key = Canada
Value = 34820000|9984670|R
Added new key, value pair.
Key = USA
Value = 314242000|9826675|R
Added new key, value pair.
Key = Ireland
Value = 6399152|84421|L
Added new key, value pair.
Key = UK
Value = 62262000|243610|L
Added new key, value pair.
Key = Mexico
Value = 113910608|1972550|R
Added new key, value pair.
Key = Denmark
Value = 5543453|43094|R
Added new key, value pair.
Key = Norway
Value = 5002942|385252|R
Added new key, value pair.
Key = UAE
Value = 8264070|83600|R
Added new key, value pair.
Key = India
Value = 1210193422|3287263|L
Added new key, value pair.
Key = China
Value = 1347350000|9640821|R
Added new key, value pair.

============================================
Retrieving data for all keys (country names)
============================================
Here is the result retrieved from the database for key Canada:
34820000|9984670|R
Unpacked population value: 34820000
Unpacked area value      : 9984670
Unpacked drive side value: R
Here is the result retrieved from the database for key USA:
314242000|9826675|R
Unpacked population value: 314242000
Unpacked area value      : 9826675
Unpacked drive side value: R
Here is the result retrieved from the database for key Ireland:
6399152|84421|L
Unpacked population value: 6399152
Unpacked area value      : 84421
Unpacked drive side value: L
Here is the result retrieved from the database for key UK:
62262000|243610|L
Unpacked population value: 62262000
Unpacked area value      : 243610
Unpacked drive side value: L
Here is the result retrieved from the database for key Mexico:
113910608|1972550|R
Unpacked population value: 113910608
Unpacked area value      : 1972550
Unpacked drive side value: R
Here is the result retrieved from the database for key Denmark:
5543453|43094|R
Unpacked population value: 5543453
Unpacked area value      : 43094
Unpacked drive side value: R
Here is the result retrieved from the database for key Norway:
5002942|385252|R
Unpacked population value: 5002942
Unpacked area value      : 385252
Unpacked drive side value: R
Here is the result retrieved from the database for key UAE:
8264070|83600|R
Unpacked population value: 8264070
Unpacked area value      : 83600
Unpacked drive side value: R
Here is the result retrieved from the database for key India:
1210193422|3287263|L
Unpacked population value: 1210193422
Unpacked area value      : 3287263
Unpacked drive side value: L
Here is the result retrieved from the database for key China:
1347350000|9640821|R
Unpacked population value: 1347350000
Unpacked area value      : 9640821
Unpacked drive side value: R

Выполните запрос к таблице innodb_memcache.containers, чтобы просмотреть запись, которую вы вставили ранее для таблицы multicol. Первая запись — это образец записи для таблицы demo_test, которая создается во время первоначальной настройки плагина daemon_memcached. Вторая запись — это запись, которую вы вставили для таблицы multicol.

mysql> SELECT * FROM innodb_memcache.containers\G
*************************** 1. row ***************************
                  name: aaa
             db_schema: test
              db_table: demo_test
           key_columns: c1
         value_columns: c2
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY
*************************** 2. row ***************************
                  name: bbb
             db_schema: test
              db_table: multicol
           key_columns: country
         value_columns: population,area_sq_km,drive_side
                 flags: c3
            cas_column: c4
    expire_time_column: c5
unique_idx_name_on_key: PRIMARY

Выполните запрос к таблице multicol, чтобы просмотреть данные, вставленные примером приложения Python. Данные доступны в MySQL, что демонстрирует, как к тем же данным можно получить доступ с помощью SQL или приложений (используя соответствующий коннектор MySQL или API).
```
mysql> SELECT * FROM test.multicol;
+---------+------------+------------+------------+------+------+------+
| country | population | area_sq_km | drive_side | c3   | c4   | c5   |
+---------+------------+------------+------------+------+------+------+
| Canada  | 34820000   | 9984670    | R          |    0 |   11 |    0 |
| China   | 1347350000 | 9640821    | R          |    0 |   20 |    0 |
| Denmark | 5543453    | 43094      | R          |    0 |   16 |    0 |
| India   | 1210193422 | 3287263    | L          |    0 |   19 |    0 |
| Ireland | 6399152    | 84421      | L          |    0 |   13 |    0 |
| Mexico  | 113910608  | 1972550    | R          |    0 |   15 |    0 |
| Norway  | 5002942    | 385252     | R          |    0 |   17 |    0 |
| UAE     | 8264070    | 83600      | R          |    0 |   18 |    0 |
| UK      | 62262000   | 243610     | L          |    0 |   14 |    0 |
| USA     | 314242000  | 9826675    | R          |    0 |   12 |    0 |
+---------+------------+------------+------------+------+------+------+
```
Примечание

При определении длины столбцов, которые обрабатываются как числовые значения, всегда оставляйте достаточно места для хранения необходимых цифр, десятичных точек, знаков, ведущих нулей и т. д. Если значения в строковом столбце, например, в VARCHAR, слишком длинные, они обрезаются путём удаления некоторых символов, что может привести к появлению бессмысленных числовых значений.
При необходимости выполните запросы типа отчёта для таблицы InnoDB, которая хранит данные memcached.

Вы можете генерировать отчёты с помощью SQL-запросов, выполняя вычисления и тесты по любым столбцам, а не только столбцу ключа country. (Так как в следующих примерах используются данные только нескольких стран, числа приведены только для иллюстрации.) Следующие запросы возвращают среднее население стран, где люди управляют правым рулём, и средний размер стран, названия которых начинаются с “U”:
```
mysql> SELECT AVG(population) FROM multicol WHERE drive_side = 'R';
+-------------------+
| avg(population)   |
+-------------------+
| 261304724.7142857 |
+-------------------+

mysql> SELECT SUM(area_sq_km) FROM multicol WHERE country LIKE 'U%';
+-----------------+
| sum(area_sq_km) |
+-----------------+
|        10153885 |
+-----------------+
```
Поскольку столбцы population и area_sq_km хранят символьные данные, а не числовые, функции, такие как AVG() и SUM(), работают путём предварительного преобразования каждого значения в число. Такой подход не работает для операторов, таких как < или >, например, при сравнении символьных значений, как 9 > 1000, что не ожидается от условия, такого как ORDER BY population DESC. Для наиболее точного обращения с типами выполните запросы к представлению, которое преобразует числовые столбцы в соответствующие типы. Этот метод позволяет вам выполнять простые запросы SELECT * из приложений базы данных, гарантируя при этом корректность преобразования, фильтрации и сортировки. Следующий пример показывает представление, которое может быть запрошено для нахождения трёх стран с наибольшим населением в порядке убывания, результаты которого отражают последние данные в таблице multicol, а значения населения и площади обрабатываются как числовые:
```
mysql> CREATE VIEW populous_countries AS
       SELECT
       country,
       cast(population as unsigned integer) population,
       cast(area_sq_km as unsigned integer) area_sq_km,
       drive_side FROM multicol
       ORDER BY CAST(population as unsigned integer) DESC
       LIMIT 3;

mysql> SELECT * FROM populous_countries;
+---------+------------+------------+------------+
| country | population | area_sq_km | drive_side |
+---------+------------+------------+------------+
| China   | 1347350000 |    9640821 | R          |
| India   | 1210193422 |    3287263 | L          |
| USA     |  314242000 |    9826675 | R          |
+---------+------------+------------+------------+

mysql> DESC populous_countries;
+------------+---------------------+------+-----+---------+-------+
| Field      | Type                | Null | Key | Default | Extra |
+------------+---------------------+------+-----+---------+-------+
| country    | varchar(128)        | NO   |     |         |       |
| population | bigint(10) unsigned | YES  |     | NULL    |       |
| area_sq_km | int(9) unsigned     | YES  |     | NULL    |       |
| drive_side | varchar(1)          | YES  |     | NULL    |       |
+------------+---------------------+------+-----+---------+-------+
```