MySQL-检查InnoDB表中数据是否已更改的最快方法 [英] MySQL - Fastest way to check if data in InnoDB table has changed

问题描述

我的应用程序非常占用数据库资源.当前，我正在运行MySQL 5.5.19并使用MyISAM，但是我正在迁移到InnoDB.剩下的唯一问题就是校验和性能.

My application is very database intensive. Currently, I'm running MySQL 5.5.19 and using MyISAM, but I'm in the process of migrating to InnoDB. The only problem left is checksum performance.

我的应用程序在高峰时间每秒执行约500-1000个"CHECKSUM TABLE"语句，因为客户端GUI不断轮询数据库以查找更改(它是一个监视系统，因此必须非常敏感且快速).

My application does about 500-1000 "CHECKSUM TABLE" statements per second in peak times, because the clients GUI is polling the database constantly for changes (it is a monitoring system, so must be very responsive and fast).

使用MyISAM，有一些实时校验和，这些校验和是在表修改时预先计算的，并且非常快.但是，InnoDB中没有这样的东西.所以，CHECKSUM TABLE非常慢...

With MyISAM, there are Live checksums that are precalculated on table modification and are VERY fast. However, there is no such thing in InnoDB. So, CHECKSUM TABLE is very slow...

我希望能够检查表的最后更新时间，不幸的是，这在InnoDB中也不可用.我现在陷入困境，因为测试表明该应用程序的性能急剧下降...

I hoped to be able to check the last update time of the table, Unfortunately, this is not available in InnoDB either. I'm stuck now, because tests have shownn that the performance of the application drops drastically...

更新表的代码行太多了，因此在应用程序中实现逻辑以记录表更改是不可能的...

There are simply too much lines of code that update the tables, so implementing logic in the application to log table changes is out of the question...

数据库生态系统由一个主节点和3个从节点组成，因此不能选择本地文件检查. 我想到了一种模仿校验和缓存的方法-具有两列的查找表-table_name，校验和，并在发生表更改时使用触发器更新该表，但是我要监视约100个表，这意味着每个表3个触发器= 300个触发器.很难维护，我不确定这不会再次成为性能猪.

The Database ecosystem consists of one master na 3 slaves, so local file checks is not an option. I thought of a method to mimic a checksum cache - a lookup table with two columns - table_name, checksum, and update that table with triggers when changes in a table occurs, but i have around 100 tables to monitor and this means 3 triggers per table = 300 triggers. Hard to maintain, and i'm not sure that this wont be a performance hog again.

那么有什么FAST方法可以检测InnoDB表中的更改?

So is there any FAST method to detect changes in InnoDB tables?

谢谢！

推荐答案

我想我已经找到了解决方案.一段时间以来，我一直在寻找Percona Server来替换我的MySQL服务器，现在我认为这样做有充分的理由.

I think I've found the solution. For some time I was looking at Percona Server to replace my MySQL servers, and now i think there is a good reason for this.

Percona服务器引入了许多新的INFORMATION_SCHEMA表，例如INNODB_TABLE_STATS，这在标准MySQL服务器中不可用. 当您这样做时:

Percona server introduces many new INFORMATION_SCHEMA tables like INNODB_TABLE_STATS, which isn't available in standard MySQL server. When you do:

SELECT rows, modified FROM information_schema.innodb_table_stats WHERE table_schema='db' AND table_name='table'

您将获得实际的行数和一个计数器. 官方文档关于此字段的内容如下:

You get actual row count and a counter. The Official documentation says the following about this field:

如果修改后的列的值超过行/16"或2000000000，则 当innodb_stats_auto_update == 1时，将完成统计信息的重新计算. 我们可以通过该值来估计统计信息的年代久远.

If the value of modified column exceeds "rows / 16" or 2000000000, the statistics recalculation is done when innodb_stats_auto_update == 1. We can estimate the oldness of the statistics by this value.

因此，该计数器每隔一段时间会自动换行，但是您可以对行数和计数器进行校验和，然后对表进行每次修改都会得到唯一的校验和.例如:

So this counter wraps every once in a while, but you can make a checksum of the number of rows and the counter, and then with every modification of the table you get a unique checksum. E.g.:

SELECT MD5(CONCAT(rows,'_',modified)) AS checksum FROM information_schema.innodb_table_stats WHERE table_schema='db' AND table_name='table';

无论如何，我都打算将服务器升级到Percona服务器，所以这对我来说不是问题.对于这个应用程序而言，管理数百个触发器并将字段添加到表中是一个主要的难题，因为它尚处于开发后期.

I was going do upgrade my servers to Percona server anyway so this bounding is not an issue for me. Managing hundreds of triggers and adding fields to tables is a major pain for this application, because it's very late in development.

这是我提供的PHP函数，以确保无论使用哪种引擎和服务器，都可以对表进行校验和:

This is the PHP function I've come up with to make sure that tables can be checksummed whatever engine and server is used:

function checksum_table($input_tables){
    if(!$input_tables) return false; // Sanity check
    $tables = (is_array($input_tables)) ? $input_tables : array($input_tables); // Make $tables always an array
    $where = "";
    $checksum = "";
    $found_tables = array();
    $tables_indexed = array();
    foreach($tables as $table_name){
        $tables_indexed[$table_name] = true; // Indexed array for faster searching
        if(strstr($table_name,".")){ // If we are passing db.table_name
            $table_name_split = explode(".",$table_name);
            $where .= "(table_schema='".$table_name_split[0]."' AND table_name='".$table_name_split[1]."') OR ";
        }else{
            $where .= "(table_schema=DATABASE() AND table_name='".$table_name."') OR ";
        }
    }
    if($where != ""){ // Sanity check
        $where = substr($where,0,-4); // Remove the last "OR"
        $get_chksum = mysql_query("SELECT table_schema, table_name, rows, modified FROM information_schema.innodb_table_stats WHERE ".$where);
        while($row = mysql_fetch_assoc($get_chksum)){
            if($tables_indexed[$row[table_name]]){ // Not entirely foolproof, but saves some queries like "SELECT DATABASE()" to find out the current database
                $found_tables[$row[table_name]] = true;
            }elseif($tables_indexed[$row[table_schema].".".$row[table_name]]){
                $found_tables[$row[table_schema].".".$row[table_name]] = true;
            }
            $checksum .= "_".$row[rows]."_".$row[modified]."_";
        }
    }

    foreach($tables as $table_name){
        if(!$found_tables[$table_name]){ // Table is not found in information_schema.innodb_table_stats (Probably not InnoDB table or not using Percona Server)
            $get_chksum = mysql_query("CHECKSUM TABLE ".$table_name); // Checksuming the old-fashioned way
            $chksum = mysql_fetch_assoc($get_chksum);
            $checksum .= "_".$chksum[Checksum]."_";
        }
    }

    $checksum = sprintf("%s",crc32($checksum)); // Using crc32 because it's faster than md5(). Must be returned as string to prevent PHPs signed integer problems.

    return $checksum;
}

您可以像这样使用它:

// checksum a signle table in the current db
$checksum = checksum_table("test_table");

// checksum a signle table in db other than the current
$checksum = checksum_table("other_db.test_table");

// checksum multiple tables at once. It's faster when using Percona server, because all tables are checksummed via one select.
$checksum = checksum_table(array("test_table, "other_db.test_table")); 

我希望这可以为遇到相同问题的其他人省去一些麻烦.

I hope this saves some trouble to other people having the same problem.

这篇关于MySQL-检查InnoDB表中数据是否已更改的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！