MySQL - Schnellere Inserts

1. Je mehr Indizes, desto langsamer die Inserts!

Bei 20000 Einträgen dauert das Befüllen auf herkömmlichem Wege auf einem RPI3 ca. 140 Sekunden. Optimiert dauert es ca. 1.5 Sekunden!!!

Code, um Zeitaufwand für Inserts zu vergleichen.

function my_autoloader($class)
{
    include 'include/' . $class . '.php';
}
$cache=5000;
$usecache = true;
$inserts=10000;
spl_autoload_register('my_autoloader');
$db = new \PDO('mysql:dbname=newdb;host=localhost', 'DB', 'PASSWORD'');
for ($i = 1; $i <= $inserts; $i++)
{
    $data[] = ["id" => 1, "zip" => "30159", "name" => "Hannover", "citizens" => 50000];
}

$beginn = microtime(true);
$sql = 'INSERT IGNORE INTO testdaten (id, zip, name, citizens) VALUES ';
if ($usecache)
{
    $dbCache = new dbcache($db, $sql, $cache, true);

    foreach ($data as $entry)
    {
        $dbCache->add('(null,' . $db->quote($entry['zip']) . ',' . $db->quote($entry['name']) . ',' . ((int) $entry['citizens']) . ')');
    }
    $dbCache->flush();
}
 else
{
     $cache=0;
     foreach ($data as $entry)
     {
         $sql2= $sql . " ('null','" . $entry['zip'] . "','". $entry['name'] . "','" . $entry['citizens'] . "')";
         //echo "debug: $sql"; die;
         $db->query($sql2);
     }
}
$dauer = microtime(true) - $beginn;
echo "Verarbeitung des Skripts: $dauer Sek.<br>";
$sql = "SELECT count(*) from testdaten";
$result = $db->query($sql);
echo "Anzahl der Datensätze: " . $result->fetchColumn() . "<br>";
$db->query("TRUNCATE table testdaten");
$db->query("INSERT INTO performance (inserts, zeit, cache) VALUES"
        . "('$inserts','$dauer','$cache')");

DBCache Klasse, gefunden hier.

class dbcache
{
    public $lastQuery;

    private $db, $limit, $sql, $cache = [], $auto;

    /**
     * @param \PDO   $db
     * @param string $sqlPrefix
     * @param int    $limit
     * @param bool   $autoFlush
     */
    public function __construct(\PDO $db, $sqlPrefix, $limit, $autoFlush = false)
    {
        $this->db    = $db;
        $this->limit = $limit;
        $this->auto  = $autoFlush;
        $this->sql   = $sqlPrefix;
    }

    /**
     * @param string $query
     */
    public function add($query)
    {
        $this->cache[] = $query;

        if ($this->auto) {
            $this->softFlush();
        }
    }

    /**
     * Flush cache and query database.
     */
    public function flush()
    {
        if (empty($this->cache)) {
            return;
        }

        // save query for logging, if something goes wrong
        $this->lastQuery = $this->sql.implode(',', $this->cache);

        $this->db->query($this->lastQuery);
        $this->cache = [];
    }

    /**
     * Flush cache if limit is reached.
     */
    public function softFlush()
    {
        if (count($this->cache) >= $this->limit) {
            $this->flush();
        }
    }
}