给 Magento 2 添加缓存层的分析与尝试

虽然黑色星期五有惊无险的过去了, 但是 Magento 2 社区版无法读写分离这个限制, 始终是悬在整个网站上的一把利剑。

我之前尝试过给 Magento 2 写一个 MySQL 读写分离的插件, 在深入研究了 Magento 2 的数据库访问层后, 发现通过一个简单的插件, 想做到读写分离基本上是不可能的。Magento 2 社区版读写数据库的逻辑里, 混杂着大量的 Magento 1的代码和逻辑, 无法在修改少量代码的前提下做到读写分离, 后来忙着做网站上的各种需求，于是读写分离就搁置了。

这次黑五, 整个项目的性能瓶颈就是 MySQL, 流量上来之后, 应用服务器负载基本保持不变, 而数据库服务器负载却翻了3倍多, 而且是在数据库服务器提前升级了硬件配置的基础上。所以我觉得 Magento 2 的数据库层必须要优化一下, 既然没法做读写分离, 那能不能加个缓存层呢？将绝大多数读取操作转移到缓存层去, 理论上数据库的负载会相应下降。

要想改的代码最少, 就得找对地方。 Magento 2 的数据库 Adapter 是 Magento\Framework\DB\Adapter\Pdo\Mysql 类, 该类继承自 Zend_Db_Adapter_Abstract

所有获取数据的方法如下:

Zend_Db_Adapter_Abstract::fetchAll($sql, $bind = array(), $fetchMode = null)

Zend_Db_Adapter_Abstract::fetchAssoc($sql, $bind = array())

Zend_Db_Adapter_Abstract::fetchCol($sql, $bind = array())

Zend_Db_Adapter_Abstract::fetchPairs($sql, $bind = array())

Zend_Db_Adapter_Abstract::fetchOne($sql, $bind = array())

Zend_Db_Adapter_Abstract::fetchRow($sql, $bind = array(), $fetchMode = null)

其中, fetchAll() 和 fetchRow() 是用的最多的两个。

下面以 fetchRow() 为例, 分析该方案的可行性以及实现方法。

/**

 * Fetches the first row of the SQL result.

 * Uses the current fetchMode for the adapter.

 *

 * @param string|Zend_Db_Select $sql An SQL SELECT statement.

 * @param mixed $bind Data to bind into SELECT placeholders.

 * @param mixed                 $fetchMode Override current fetch mode.

 * @return mixed Array, object, or scalar depending on fetch mode.

 */

public function fetchRow($sql, $bind = array(), $fetchMode = null)

通过解析 $sql 对象和 $bind 数组，可以得到精确的、格式化的数据，包含
1. 数据库表名
2. 字段键值对

通过这些数据，可以构建缓存的键(key)和标签(tag)，例如：
$cacheKey = table_name::主键键值对
或者
$cacheKey = table_name::唯一键索引键值对

$cacheTags = [
table_name,
table_name::主键键值对
table_name::唯一键索引键值对组1,
table_name::唯一键索引键值对组2,
…
]

cacheTags 的作用是给缓存分类, 方便后续清理。

有了 $cacheKey, $cacheTags 之后, 就可以将数据库查询的结果保存到缓存中去;

下次再有查询过来, 先在缓存中查找有无对应的数据, 如果有就直接返回给数据调用方了;

那么如果数据更新了呢？

数据更新分为三种: 1. UPDATE, 2. INSERT, 3 DELETE

对于 UPDATE:

/**

 * Updates table rows with specified data based on a WHERE clause.

 *

 * @param  mixed        $table The table to update.

 * @param  array        $bind  Column-value pairs.

 * @param  mixed        $where UPDATE WHERE clause(s).

 * @return int          The number of affected rows.

 * @throws Zend_Db_Adapter_Exception

 */

public function update($table, array $bind, $where = '')

update() 方法接收 3 个参数，分别是 table_name, 待更新数据键值对, where 条件子句。
刚才我们在构建 $cacheTags 时，分别有 table_name、table_name::主键键值对、table_name::唯一键索引键值对, table_name 是现成的，其余两种tag 需要从 where 子句中解析。通过解析，最坏情况是 where 子句未解析到任何键值对, 最好情况是解析到了所有 filed 键值对。最坏情况下，需要清除 table_name 下的所有缓存数据，而最好情况下, 只需要清除一条缓存数据。

对于 INSERT:

/**

 * Inserts a table row with specified data.

 *

 * @param mixed $table The table to insert data into.

 * @param array $bind Column-value pairs.

 * @return int The number of affected rows.

 * @throws Zend_Db_Adapter_Exception

 */

public function insert($table, array $bind)

insert() 方法接收 2 个参数，分别是 table_name，待插入数据键值对。由于新插入的数据根本不存在与缓存中，所以不需要对缓存进行操作

对于 DELETE:

/**

 * Deletes table rows based on a WHERE clause.

 *

 * @param  mixed        $table The table to update.

 * @param  mixed        $where DELETE WHERE clause(s).

 * @return int          The number of affected rows.

 */

public function delete($table, $where = '')

delete() 方法接收 2 个参数, table_name 和 where 子句，假如能从 where 子句中解析到主键键值对或唯一键索引键值对, 就只需要清除一条缓存记录，否则需要清除该 table_name 下的所有缓存记录。

优化效果:
我暂时只是用 ab 测试了 Magento 2 的购物车:

ab -C PHPSESSID=acmsj8q8ld1tvdo77lm5t0dr9b -n 40 -c 5  http://localhost/checkout/cart/

没有缓存的时候：
test-No-Cache-1:

Requests per second:    1.79 [#/sec] (mean)

Time per request:       2786.478 [ms] (mean)

Time per request:       557.296 [ms] (mean, across all concurrent requests)

Percentage of the requests served within a certain time (ms)

  50%    756

  66%   2064

  75%   5635

  80%   6150

  90%   7632

  95%   8530

  98%   8563

  99%   8563

 100%   8563 (longest request)

MySQL 进程的 CPU 占用率保持在 20% ~ 24%

test-No-Cache-2:

Requests per second:    1.84 [#/sec] (mean)

Time per request:       2720.852 [ms] (mean)

Time per request:       544.170 [ms] (mean, across all concurrent requests)

Percentage of the requests served within a certain time (ms)

  50%    586

  66%   1523

  75%   4036

  80%   5667

  90%  10228

  95%  11621

  98%  12098

  99%  12098

 100%  12098 (longest request)

MySQL 进程的 CPU 占用率保持在 20% ~ 24%

有缓存的时候：
test-With-Cache-1:

Requests per second:    1.99 [#/sec] (mean)

Time per request:       2509.273 [ms] (mean)

Time per request:       501.854 [ms] (mean, across all concurrent requests)

Percentage of the requests served within a certain time (ms)

  50%    489

  66%    511

  75%    574

  80%    637

  90%  19073

  95%  19553

  98%  20063

  99%  20063

 100%  20063 (longest request)

MySQL 进程的 CPU 占用率保持在 5% 左右

test-With-Cache-2:

Requests per second:    2.10 [#/sec] (mean)

Time per request:       2384.145 [ms] (mean)

Time per request:       476.829 [ms] (mean, across all concurrent requests)

Percentage of the requests served within a certain time (ms)

  50%    465

  66%    472

  75%    565

  80%    620

  90%   9509

  95%  18374

  98%  18588

  99%  18588

 100%  18588 (longest request)

MySQL 进程的 CPU 占用率保持在 5% ~ 7 %

通过上面两组数据的对比，很明显 MySQL 的 CPU 占用率有了大幅度下降(从 20% 下降到 5%), 可见增加一个缓存层对降低 MySQL 负载是有效果的。

但是有一个小问题, 在不使用缓存的情况下, Percentage of the requests served within a certain time 这个值，在 90% 这个点之后, 表现要比有缓存的情况好, 我猜是大量 unserialize() 操作造成 CPU 资源不够导致响应缓慢。

经过修改后的 vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:

class Mysql extends \Zend_Db_Adapter_Pdo_Mysql implements AdapterInterface

{

    protected $_cache;

    public function fetchAll($sql, $bind = array(), $fetchMode = null)

    {

        if ($sql instanceof \Zend_Db_Select) {

            /** @var array $from */

            $from = $sql->getPart('from');

            $tableName = current($from)['tableName'];

            $cacheKey = 'FETCH_ALL::' . $tableName . '::' . md5((string)$sql);

            $cache = $this->getCache();

            $data = $cache->load($cacheKey);

            if ($data === false) {

                $data = parent::fetchAll($sql, $bind, $fetchMode);

                $cache->save(serialize($data), $cacheKey, ['FETCH_ALL::' . $tableName], 3600);

            } else {

                $data = @unserialize($data);

            }

        } else {

            $data = parent::fetchAll($sql, $bind, $fetchMode);

        }

        return $data;

    }

    public function fetchRow($sql, $bind = [], $fetchMode = null)

    {

        $cacheIdentifiers = $this->resolveSql($sql, $bind);

        if ($cacheIdentifiers !== false) {

            $cache = $this->getCache()->getFrontend();

            $data = $cache->load($cacheIdentifiers['cacheKey']);

            if ($data === false) {

                $data = parent::fetchRow($sql, $bind, $fetchMode);

                if ($data) {

                    $cache->save(serialize($data), $cacheIdentifiers['cacheKey'], $cacheIdentifiers['cacheTags'], 3600);

                }

            } else {

                $data = @unserialize($data);

            }

        } else {

            $data = parent::fetchRow($sql, $bind, $fetchMode);

        }

        return $data;

    }

    public function update($table, array $bind, $where = '')

    {

        parent::update($table, $bind, $where);

        $cacheKey = $this->resolveUpdate($table, $bind, $where);

        if ($cacheKey === false) {

            $cacheKey = $table;

        }

        $this->getCache()->clean([$cacheKey, 'FETCH_ALL::' . $table]);

    }

    /**

     * @return \Magento\Framework\App\CacheInterface

     */

    private function getCache()

    {

        if ($this->_cache === null) {

            $objectManager = \Magento\Framework\App\ObjectManager::getInstance();

            $this->_cache = $objectManager->get(\Magento\Framework\App\CacheInterface::class);

        }

        return $this->_cache;

    }

    /**

     * @param string|\Zend_Db_Select $sql An SQL SELECT statement.

     * @param mixed $bind Data to bind into SELECT placeholders.

     * @return array

     */

    protected function resolveSql($sql, $bind = array())

    {

        $result = false;

        if ($sql instanceof \Zend_Db_Select) {

            try {

                /** @var array $from */

                $from = $sql->getPart('from');

                $tableName = current($from)['tableName'];

                $where = $sql->getPart('where');

                foreach ($this->getIndexFields($tableName) as $indexFields) {

                    $kv = $this->getKv($indexFields, $where, $bind);

                    if ($kv !== false) {

                        $cacheKey = $tableName . '::' . implode('|', $kv);

                        $cacheTags = [

                            $tableName,

                            $cacheKey

                        ];

                        $result = ['cacheKey' => $cacheKey, 'cacheTags' => $cacheTags];

                    }

                }

            }catch (\Zend_Db_Select_Exception $e) {

            }

        }

        return $result;

    }

    protected function resolveUpdate($tableName, array $bind, $where = '')

    {

        $cacheKey = false;

        if (is_string($where)) {

            $where = [$where];

        }

        foreach ($this->getIndexFields($tableName) as $indexFields) {

            $kv = $this->getKv($indexFields, $where, $bind);

            if ($kv !== false) {

                $cacheKey = $tableName . '::' . implode('|', $kv);

            }

        }

        return $cacheKey;

    }

    protected function getIndexFields($tableName)

    {

        $indexes = $this->getIndexList($tableName);

        $indexFields = [];

        foreach ($indexes as $data) {

            if ($data['INDEX_TYPE'] == 'primary') {

                $indexFields[] = $data['COLUMNS_LIST'];

            } elseif ($data['INDEX_TYPE'] == 'unique') {

                $indexFields[] = $data['COLUMNS_LIST'];

            }

        }

        return $indexFields;

    }

    protected function getKv($fields, $where, $bind)

    {

        $found = true;

        $kv = [];

        foreach ($fields as $field) {

            $_found = false;

            if (isset($bind[':' . $field])) {   // 在 bind 数组中查找 filed value

                $kv[$field] = $field . '=' .$bind[':' . $field];

                $_found = true;

            } elseif (is_array($where)) {

                foreach ($where as $case) { // 遍历 where 条件子句， 查找 filed value

                    $matches = [];

                    $preg = sprintf('#%s.*=(.*)#', $field);

                    $_result = preg_match($preg, $case, $matches);

                    if ($_result) {

                        $kv[$field] = $field . '=' .trim($matches[1], ' \')');

                        $_found = true;

                    }

                }

            }

            if (!$_found) { // 其中任一 field 没找到，

                $found = false;

                break;

            }

        }

        return $found ? $kv : false;

    }

}

巴特西

给 Magento 2 添加缓存层的分析与尝试

最新文章

热门文章