PHP Db::optimizeTables方法代码示例

 /**
  * Purges old data from the following tables:
  * - log_visit
  * - log_link_visit_action
  * - log_conversion
  * - log_conversion_item
  * - log_action
  */
 public function purgeData()
 {
     $maxIdVisit = $this->getDeleteIdVisitOffset();
     // break if no ID was found (nothing to delete for given period)
     if (empty($maxIdVisit)) {
         return;
     }
     $logTables = self::getDeleteTableLogTables();
     // delete data from log tables
     $where = "WHERE idvisit <= ?";
     foreach ($logTables as $logTable) {
         // deleting from log_action must be handled differently, so we do it later
         if ($logTable != Common::prefixTable('log_action')) {
             Db::deleteAllRows($logTable, $where, "idvisit ASC", $this->maxRowsToDeletePerQuery, array($maxIdVisit));
         }
     }
     // delete unused actions from the log_action table (but only if we can lock tables)
     if (Db::isLockPrivilegeGranted()) {
         $this->purgeUnusedLogActions();
     } else {
         $logMessage = get_class($this) . ": LOCK TABLES privilege not granted; skipping unused actions purge";
         Log::warning($logMessage);
     }
     // optimize table overhead after deletion
     Db::optimizeTables($logTables);
 }

 /**
  * @group Core
  */
 public function testOptimize()
 {
     // make sure optimizing myisam tables works
     $this->assertTrue(Db::optimizeTables(array('table1', 'table2')) !== false);
     // make sure optimizing both myisam & innodb results in optimizations
     $this->assertTrue(Db::optimizeTables(array('table1', 'table2', 'table3', 'table4')) !== false);
     // make sure innodb tables are skipped
     $this->assertTrue(Db::optimizeTables(array('table3', 'table4')) === false);
 }

 private function optimizeTable(OutputInterface $output, $dryRun, $table)
 {
     $output->write("Optimizing table '{$table}'...");
     if ($dryRun) {
         $output->write("[dry-run, not optimising table]");
     } else {
         Db::optimizeTables(Common::prefixTable($table), $force = true);
     }
     $output->writeln("Done.");
 }

 function delete()
 {
     $this->log("Deleting logs for today...");
     $db = \Zend_Registry::get('db');
     $sql = "DELETE FROM " . Common::prefixTable('log_visit') . "\n\t\t\t\tWHERE date(visit_last_action_time) = CURRENT_DATE();";
     $db->query($sql);
     foreach (array('log_link_visit_action', 'log_conversion', 'log_conversion_item') as $table) {
         $sql = "DELETE FROM " . Common::prefixTable($table) . "\n\t\t \t\tWHERE date(server_time) = CURRENT_DATE();";
         $db->query($sql);
     }
     $tablesToOptimize = array(Common::prefixTable('log_link_visit_action'), Common::prefixTable('log_conversion'), Common::prefixTable('log_conversion_item'), Common::prefixTable('log_visit'));
     \Piwik\Db::optimizeTables($tablesToOptimize);
     $this->log("done");
 }

 /**
  * Purges old data from the following tables:
  * - log_visit
  * - log_link_visit_action
  * - log_conversion
  * - log_conversion_item
  * - log_action
  *
  * @param int $deleteLogsOlderThan The number of days after which log entires are considered old.
  *                                 Visits and related data whose age is greater than this number
  *                                 will be purged.
  */
 public function purgeData($deleteLogsOlderThan)
 {
     $dateUpperLimit = Date::factory("today")->subDay($deleteLogsOlderThan);
     $this->logDeleter->deleteVisitsFor($start = null, $dateUpperLimit->getDatetime());
     $logTables = self::getDeleteTableLogTables();
     // delete unused actions from the log_action table (but only if we can lock tables)
     if (Db::isLockPrivilegeGranted()) {
         $this->rawLogDao->deleteUnusedLogActions();
     } else {
         $logMessage = get_class($this) . ": LOCK TABLES privilege not granted; skipping unused actions purge";
         Log::warning($logMessage);
     }
     // optimize table overhead after deletion
     Db::optimizeTables($logTables);
 }

 /**
  * Purges old report/metric data.
  *
  * If $keepBasicMetrics is false, old numeric tables will be dropped, otherwise only
  * the metrics not in $metricsToKeep will be deleted.
  *
  * If $reportPeriodsToKeep is an empty array, old blob tables will be dropped. Otherwise,
  * specific reports will be deleted, except reports for periods in $reportPeriodsToKeep.
  *
  * @param bool $optimize If tables should be optimized after rows are deleted. Normally,
  *                       this is handled by a scheduled task.
  */
 public function purgeData($optimize = false)
 {
     // find archive tables to purge
     list($oldNumericTables, $oldBlobTables) = $this->getArchiveTablesToPurge();
     // process blob tables first, since archive status is stored in the numeric archives
     if (!empty($oldBlobTables)) {
         // if no reports should be kept, drop tables, otherwise drop individual reports
         if (empty($this->reportPeriodsToKeep) && !$this->keepSegmentReports) {
             Db::dropTables($oldBlobTables);
         } else {
             foreach ($oldBlobTables as $table) {
                 $where = $this->getBlobTableWhereExpr($oldNumericTables, $table);
                 if (!empty($where)) {
                     $where = "WHERE {$where}";
                 }
                 Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery);
             }
             if ($optimize) {
                 Db::optimizeTables($oldBlobTables);
             }
         }
     }
     // deal with numeric tables
     if (!empty($oldNumericTables)) {
         // if keep_basic_metrics is set, empty all numeric tables of metrics to purge
         if ($this->keepBasicMetrics == 1 && !empty($this->metricsToKeep)) {
             $where = "WHERE name NOT IN ('" . implode("','", $this->metricsToKeep) . "') AND name NOT LIKE 'done%'";
             foreach ($oldNumericTables as $table) {
                 Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery);
             }
             if ($optimize) {
                 Db::optimizeTables($oldNumericTables);
             }
         } else {
             Db::dropTables($oldNumericTables);
         }
     }
 }

 public function optimizeArchiveTable()
 {
     $archiveTables = ArchiveTableCreator::getTablesArchivesInstalled();
     Db::optimizeTables($archiveTables);
 }

 private function optimizeTables(OutputInterface $output)
 {
     foreach (self::$logTables as $table) {
         $output->write("Optimizing table {$table}... ");
         $timer = new Timer();
         $prefixedTable = Common::prefixTable($table);
         $done = Db::optimizeTables($prefixedTable);
         if ($done) {
             $output->writeln("done. <comment>" . $timer . "</comment>");
         } else {
             $output->writeln("skipped! <comment>" . $timer . "</comment>");
         }
     }
     $this->writeSuccessMessage($output, array("Table optimization finished."));
 }

 /**
  * Purges old report/metric data.
  *
  * If $keepBasicMetrics is false, old numeric tables will be dropped, otherwise only
  * the metrics not in $metricsToKeep will be deleted.
  *
  * If $reportPeriodsToKeep is an empty array, old blob tables will be dropped. Otherwise,
  * specific reports will be deleted, except reports for periods in $reportPeriodsToKeep.
  *
  * @param bool $optimize If tables should be optimized after rows are deleted. Normally,
  *                       this is handled by a scheduled task.
  */
 public function purgeData($optimize = false)
 {
     list($oldNumericTables, $oldBlobTables) = $this->getArchiveTablesToPurge();
     // process blob tables first, since archive status is stored in the numeric archives
     if (!empty($oldBlobTables)) {
         foreach ($oldBlobTables as $table) {
             $where = $this->getBlobTableWhereExpr($oldNumericTables, $table);
             if (!empty($where)) {
                 $where = "WHERE {$where}";
             }
             Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery);
         }
         if ($optimize) {
             Db::optimizeTables($oldBlobTables);
         }
     }
     $this->segmentArchiveIds = null;
     if (!empty($oldNumericTables)) {
         foreach ($oldNumericTables as $table) {
             $conditions = array("name NOT LIKE 'done%'");
             $bind = array();
             if ($this->keepBasicMetrics && !empty($this->metricsToKeep)) {
                 $metricFields = Common::getSqlStringFieldsArray($this->metricsToKeep);
                 $bind = $this->metricsToKeep;
                 $conditions[] = sprintf("name NOT IN (%s)", $metricFields);
             }
             $keepWhere = $this->getBlobTableWhereExpr($oldNumericTables, $table);
             if (!empty($keepWhere)) {
                 $conditions[] = $keepWhere;
             }
             $where = 'WHERE ' . implode(' AND ', $conditions);
             Db::deleteAllRows($table, $where, "idarchive ASC", $this->maxRowsToDeletePerQuery, $bind);
         }
         if ($optimize) {
             Db::optimizeTables($oldNumericTables);
         }
     }
 }

 /**
  * @param OutputInterface $output
  * @param Date[] $dates
  * @param bool $forceOptimzation
  */
 private function optimizeArchiveTables(OutputInterface $output, $dates, $forceOptimzation = false)
 {
     $output->writeln("Optimizing archive tables...");
     foreach ($dates as $date) {
         $numericTable = ArchiveTableCreator::getNumericTable($date);
         $this->performTimedPurging($output, "Optimizing table {$numericTable}...", function () use($numericTable, $forceOptimzation) {
             Db::optimizeTables($numericTable, $forceOptimzation);
         });
         $blobTable = ArchiveTableCreator::getBlobTable($date);
         $this->performTimedPurging($output, "Optimizing table {$blobTable}...", function () use($blobTable, $forceOptimzation) {
             Db::optimizeTables($blobTable, $forceOptimzation);
         });
     }
 }