Как улучшить производительность импорта таблицы mysql LOAD XML LOCAL INFILE?

Question

Для базы данных mysql у меня есть несколько файлов дампа XML.

Один файл импорта таблицы для pricing таблица выглядит так:

<database>
    <table>
        <row>
            <id>5954017</id>
            <foo>narf</foo>
            <bar_id>1377</bar_id>
            <price_single>800.00</price_single>
            <price_double>1500.00</price_double>
            <price_triple>2000.00</price_triple>
            <price_quad>1900.00</price_quad>
            <currency>USD</currency>
        </row>
        ...
    </table>
</database>

Она имеет

$ xmllint --xpath "count(//row)" import.xml 
223198

строки и его размер:

du -h import.xml 
69M import.xml

Я хочу импортировать через MySQL LOAD XML особенность. Таблица всегда будет заранее обрезана.

MySQL [my_database]> LOAD XML LOCAL INFILE 'import.xml' INTO TABLE pricing ROWS IDENTIFIED BY '<row>' \G

Это успешно, но импорт таблицы занимает довольно много времени:

Query OK, 223198 rows affected (1 hour 44 min 48.40 sec)
Records: 223198  Deleted: 0  Skipped: 0  Warnings: 0

Я читал, что люди импортируют гигабайты данных с помощью этой функции LOAD INFILE, и я ожидал, что ее производительность будет намного выше, в считанные минуты, а не часы. Мои ожидания неверны? Это нормальное время для такого набора данных из 200000 записей?

(Я также сравниваю эту скорость с пользовательским сценарием импорта php, который анализирует XML вручную и вставляет каждую строку одну за другой; эта задача занимает 45 минут для всех таблиц. Я ожидал, что LOAD XML LOCAL INFILE чтобы справиться с этой задачей.)

Моя таблица выглядит так:

MySQL [my_database]> DESCRIBE pricing;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| id             | int(11)      | NO   | PRI | NULL    |       |
| foo            | varchar(256) | YES  |     | NULL    |       |
| bar_id         | int(11)      | YES  |     | NULL    |       |
| price_single   | float        | YES  |     | NULL    |       |
| price_double   | float        | YES  |     | NULL    |       |
| price_triple   | float        | YES  |     | NULL    |       |
| price_quad     | float        | YES  |     | NULL    |       |
| currency       | varchar(3)   | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

И был создан с помощью:

DROP TABLE IF EXISTS `pricing`;
CREATE TABLE `pricing` (
      `id` int(11) NOT NULL,
      `foo` varchar(256) DEFAULT NULL,
      `bar_id` int(11) DEFAULT NULL,
      `price_single` float DEFAULT NULL,
      `price_double` float DEFAULT NULL,
      `price_triple` float DEFAULT NULL,
      `price_quad` float DEFAULT NULL,
      `currency` varchar(3) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
      PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Что я могу сделать, чтобы улучшить производительность LOAD XML LOCAL INFILE?

Answer 1

Превратите ваш XML в файл CSV, сделав импорт практически мгновенным.

Вы можете преобразовать свой XML через xslt, используя, например, xsltproc:

 $ xsltproc transformToCsv.xsl price.xml > price.csv
 "5954017"╡"narf"╡"1377"╡"800.00"╡"1500.00"╡"2000.00"╡"1900.00"╡"USD"

Соответствующий xslt transformToCsv.xsl выглядит так:

<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
    <xsl:output method="text"
                encoding="utf-8"/>

    <xsl:param name="delim"
               select="'╡'"/>
    <xsl:param name="quote"
               select="'&quot;'"/>
    <xsl:param name="break"
               select="'&#10;'"/>

    <xsl:template match="/">
        <xsl:apply-templates select="database/table/row"/>
    </xsl:template>

    <xsl:template match="row">
        <xsl:apply-templates/>
        <xsl:if test="following-sibling::*">
            <xsl:value-of select="$break"/>
        </xsl:if>
    </xsl:template>

    <xsl:template match="*">
        <xsl:value-of select="concat($quote, normalize-space(), $quote)"/>
        <xsl:if test="following-sibling::*">
            <xsl:value-of select="$delim"/>
        </xsl:if>
    </xsl:template>

    <xsl:template match="text()"/>
</xsl:stylesheet>

Синтаксис импорта затем изменяется на:

$ mysql \
    -h YOUR_MYSQL_HOST \
    -P YOUR_PORT \
    -uYOUR_USER \
    -pYOUR_PASSWORD \
    YOUR_DATABASE \
    -e "LOAD DATA LOCAL INFILE 'pricing.csv' INTO TABLE pricing FIELDS TERMINATED BY '╡' ENCLOSED BY '\"' \G"

Импорт 200 000 записей становится делом (милли) секунд.