phpmyadmin非常慢地打开MySQL表列表-phpmyadminopensMySQLtablelistsveryslowly

7  

Open \config.inc.php file and add these two line of code to it:

打开\ config.inc.php文件并将以下两行代码添加到其中:

$cfg['MaxExactCount'] = 0;
$cfg['MaxExactCountViews'] = 0;

And of course you can skip second line if you have not any views at the database.

如果您在数据库中没有任何视图,当然可以跳过第二行。

6  

This is because you have innoDB tables with lot of rows. InnoDB does not store the number of rows in a table but MyISAM does. So for each InnoDB table PHPMyAdmin invokes SELECT count(*) FROM query which is very slow if number of rows is very high. To resolve this you should edit config.inc.php file and set $cfg['MaxExactCount']. This will invoke count(*) sql for tables that has less MaxExactCount rows.

这是因为你有很多行的innoDB表。 InnoDB不存储表中的行数,但MyISAM存在。因此,对于每个InnoDB表,PHPMyAdmin调用SELECT count(*)FROM query,如果行数非常高,则该查询非常慢。要解决此问题,您应该编辑config.inc.php文件并设置$ cfg ['MaxExactCount']。这将为具有较少MaxExactCount行的表调用count(*)sql。

$cfg['MaxExactCount'] = 20000;

Meaning form phpmyadmin manual

意思形式phpmyadmin手册

For InnoDB tables, determines for how large tables phpMyAdmin should get the exact row count using SELECT COUNT. If the approximate row count as returned by SHOW TABLE STATUS is smaller than this value, SELECT COUNT will be used, otherwise the approximate count will be used.

对于InnoDB表,确定phpMyAdmin应使用SELECT COUNT获取精确行数的大表。如果SHOW TABLE STATUS返回的近似行计数小于此值,则将使用SELECT COUNT,否则将使用近似计数。

2  

I originally used @"Andrew Kondratev" answer, without the "if view" conditional stuff, and then started looking closer at the rest of that method, and realized that was almost exactly the code that would run if $force_exact was false. I have a new, simpler hack that doesn't break quite as much and works for tables as well.

我最初使用@“Andrew Kondratev”的答案,没有“if view”条件的东西,然后开始仔细研究该方法的其余部分,并意识到如果$ force_exact为false,那几乎就是运行的代码。我有一个新的,更简单的黑客,它不会破坏那么多,也适用于表格。

Just like with Andrew's hack:

就像安德鲁的黑客一样:

• Find where the installation lives, such as rpm -ql phpMyAdmin | grep Table.class.php (or your local OS equivalent).

查找安装所在的位置,例如rpm -ql phpMyAdmin | grep Table.class.php(或您的本地操作系统等效项)。

• EDIT: ./libraries/Table.class.php (in my case /usr/share/phpMyAdmin/libraries/Table.class.php

编辑:./ libraries/Table.class.php(在我的情况下/usr/share/phpMyAdmin/libraries/Table.class.php

• Look for static public function countRecords (line 563 in my case)

寻找静态公共函数countRecords(在我的例子中为563行)

• Insert the following at the top of that function (after the {):

  在该函数的顶部插入以下内容(在{)之后:

          /* Tommy's Hack from http://goo.gl/HMTnLc */
          $force_exact = false;
          /* End Tommy's Hack - USE AT YOUR OWN RISK! */
  

• In my case the "defaults" already have the following:

  在我的情况下,“默认值”已经具有以下内容:

  config.default.php: * @global integer $cfg['MaxExactCount']
  config.default.php:$cfg['MaxExactCount'] = 0;
  config.default.php: * @global integer $cfg['MaxExactCountViews']
  config.default.php:$cfg['MaxExactCountViews'] = 0;
  

• However, you can always add it to your config.inc.php:

  但是,您始终可以将其添加到config.inc.php中:

  $cfg['MaxExactCountViews'] = 0;//disable trying to count the number of rows in any view
  $cfg['MaxExactCount'] = 0;//disable correcting the InnoDB estimates
  

I believe the problem is actually over in tbl_info.inc.php where it sets $force_exact to true while displaying tables. IMO, the only time that number would need to be "exact" is if you were trying to view the last page, and even then probably not.

我相信问题实际上已经在tbl_info.inc.php中结束了,它在显示表时将$ force_exact设置为true。 IMO,这个数字唯一需要“精确”的时间是你试图查看最后一页,即使这样也可能没有。

1  

In case when you have several VIEWS with lots (>10^5) of records it works terribly slow even when MaxExactCountViews and MaxExactCount both set to 100.

如果你有几个VIEWS有很多(> 10 ^ 5)的记录,即使MaxExactCountViews和MaxExactCount都设置为100,它的工作速度也非常慢。

Find

'static public function countRecords'

'静态公共功能countRecords'

in

libraries\Table.class.php

, put following code in begining of this method:

,将以下代码放在这个方法的开头:

if ($is_view == true && isset($GLOBALS['cfg']['MaxExactCountViews'])) {
    /* dirty hack to avoid performance issue with views when ['cfg']['MaxExactCount'] and ['cfg']['MaxExactCountViews'] does not help it */
    $tmp_tables = PMA_DBI_get_tables_full($db, $table);
    PMA_Table::$cache[$db][$table] = $tmp_tables[$table];
    PMA_Table::$cache[$db][$table]['ExactRows'] = $GLOBALS['cfg']['MaxExactCountViews'];
    return (int) $GLOBALS['cfg']['MaxExactCountViews']; 
}

Set $GLOBALS['cfg']['MaxExactCountViews'] value in config afterwards. phpMyAdmin now will always display this value for all VIEWS. It also will work much faster :-)

之后在config中设置$ GLOBALS ['cfg'] ['MaxExactCountViews']值。 phpMyAdmin现在将始终为所有VIEWS显示此值。它也会更快地工作:-)

1  

The way I solved your problem was by caching the output of SHOW TABLE STATUS FROM into a table named f.i. showtablecache, say every 2 minutes. You can do this with some cron script for your database(s).

我解决问题的方法是将SHOW TABLE STATUS FROM 的输出缓存到名为f.i的表中。 showtablecache,每2分钟说一次。您可以使用数据库的某些cron脚本执行此操作。

You could then edit the file /usr/share/phpmyadmin/libraries/database_interface.lib.php and replace the slow SHOW TABLE STATUS FROM ... with a SELECT ... FROM showtablecache WHERE ... on the new cache table.

然后,您可以编辑文件/usr/share/phpmyadmin/libraries/database_interface.lib.php,并在新缓存表上使用SELECT ... FROM showtablecache WHERE ...替换慢速SHOW TABLE STATUS FROM ...。

You could also leave the phpmyadmin source alone and put a mysql-proxy instance in between that does the query rewriting for you. All you then have to do is change the $dbport variable in config-db.php :)

您也可以单独留下phpmyadmin源代码并在其间放置一个mysql代理实例,为您执行查询重写。您需要做的就是更改config-db.php中的$ dbport变量:)

Using mysql-proxy for this is especially useful if you have this problem with a non-open source tool other than phpmyadmin. Like some native, maybe proprietary workbench application. (Database workbench by Upscene does something similar (if I recall correctly))

如果您使用phpmyadmin之外的非开源工具遇到此问题,则使用mysql-proxy非常有用。像一些原生的,也许是专有的工作台应用程序。 (Upscene的数据库工作台做了类似的事情(如果我没记错的话))

Queries for in the cron script:

在cron脚本中查询:

START TRANSACTION;

DELETE FROM showtablecache WHERE database_ = '';

INSERT INTO showtablecache
SELECT 
    ''
    , TABLE_NAME
    , ENGINE
    , VERSION
    , ROW_FORMAT
    , TABLE_ROWS
    , AVG_ROW_LENGTH
    , DATA_LENGTH
    , MAX_DATA_LENGTH
    , INDEX_LENGTH
    , DATA_FREE
    , AUTO_INCREMENT
    , CREATE_TIME
    , UPDATE_TIME
    , CHECK_TIME
    , TABLE_COLLATION
    , CHECKSUM
    , CREATE_OPTIONS
    , TABLE_COMMENT
FROM
    INFORMATION_SCHEMA.TABLES
WHERE
    table_schema = '';

COMMIT;

So instead of SHOW TABLE STATUS FROM you use:

所以不使用SHOW TABLE STATUS FROM ,而是使用:

SELECT
    Name_ AS `Name`,
    Engine_ AS `Engine`,
    Version,
    Row_format_ AS `Row_format`,
    Rows_ AS `Rows`,
    Avg_row_length,
    Data_length,
    Max_data_length,
    Index_length,
    Data_free,
    Auto_increment_ AS `Auto_increment`,
    Create_time,
    Update_time,
    Check_time,
    Collation_ AS `Collation`,
    Checksum,
    Comment_ AS `Comment`,
    Create_options
FROM
    showtablecache
WHERE
    Database_ = ;

More details about this fix here: http://blog.cppse.nl/fix-slow-phpmyadmin

有关此修复的更多详细信息,请访问:http://blog.cppse.nl/fix-slow-phpmyadmin

0  

I posted a fix for a general situation where you query information_schema.tables

我发布了一个修复程序,用于查询information_schema.tables的一般情况

Slow query on information_schema.tables

对information_schema.tables的查询速度慢