代码之家 › 专栏 › 技术社区 › k0pernikus

如何提高mysql加载XML本地填充表导入的性能?

load-data-infile import performance xml mysql

k0pernikus · 技术社区 · 7 年前

对于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 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;

加载XML局部填充

1 回复 | 直到 7 年前

k0pernikus 7 年前

将XML转换为CSV文件,使导入几乎是即时的。

您可以使用xslt转换XML,例如:。 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"