Opencart 使用伪静态时会造成大量数据库查询问题 及 SQL查询库大小和 INFORMATION_SCHEMA 相关

一、Opencart 使用伪静态时会造成大量数据库调用的问题

    Opencart是一套还不错的商场系统，特别是其模块化的设计思路。这是我在刚接触Opencart后的初步印象，另外在深入了解Opencart的源代码之后，我觉得Opencart还是一个很不错的入门框架。

    现在的大型框架比如YII,LARAVEL以及ZEND等，都是把程序源代码写得让一般人看不了，进入的入口页写得很简单，一般两句就几句，定义个报错模式，然后就加载个应用，再执行一下应用init之类或是boot函数驱动整个Application，让人望而生畏。而Opencart则相对较简单，但又精致地体现了当前主流框架的思路。

    不过今天我在这里想讲的是Opencart在使用伪静态后频繁调用数据库的问题，先说一下Opencart在伪静态的情况下进入页面时真实URL是：index.php?_route_=(模块/)控制器/方法&参数名=参数值...。这个最终会通过catolog/controller/common/seo_url.php进行解析处理，代码如下：

class ControllerCommonSeoUrl extends Controller {
public function index() { // Add rewrite to url class 
if ($this->config->get('config_seo_url')) {
$this->url->addRewrite($this);
} // Decode URL if (isset($this->request->get['_route_'])) {
$parts = explode('/', $this->request->get['_route_']);
// remove any empty arrays from trailing
if (utf8_strlen(end($parts)) == 0) {
array_pop($parts);
} foreach ($parts as $part) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "url_alias WHERE keyword = '" . $this->db->escape($part) . "'");
if ($query->num_rows) {
$url = explode('=', $query->row['query']);
if ($url[0] == 'product_id') {
$this->request->get['product_id'] = $url[1];
}
if ($url[0] == 'category_id') {
if (!isset($this->request->get['path'])) {
$this->request->get['path'] = $url[1];
} else {
$this->request->get['path'] .= '_' . $url[1];
}
}
.....
}

    可以看到对路由分析时会查询数据库查询控制器和action方法（在此之前会调用system/engine/action判断模块），路径解析当然只有一次调用，所以这里不会产生多次调用url_alias 的情况，但是catolog/controller/common/seo_url.php里另一个生成伪静态的方法却是主要因素，方法如下：

public function rewrite($link) {
$url_info = parse_url(str_replace('&', '&', $link));
$url = '';
$data = array();
parse_str($url_info['query'], $data);
foreach ($data as $key => $value) {
if (isset($data['route'])) {
if (($data['route'] == 'product/product' && $key == 'product_id') || (($data['route'] == 'product/manufacturer/info' || $data['route'] == 'product/product') && $key == 'manufacturer_id') || ($data['route'] == 'information/information' && $key == 'information_id')) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "url_alias WHERE `query` = '" . $this->db->escape($key . '=' . (int)$value) . "'");
if ($query->num_rows) {
$url .= '/' . $query->row['keyword'];
unset($data[$key]);
}
} elseif ($key == 'path') {
$categories = explode('_', $value);
foreach ($categories as $category) {
$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "url_alias WHERE `query` = 'category_id=" . (int)$category . "'");
if ($query->num_rows) {
$url .= '/' . $query->row['keyword'];
} else {
$url = '';
break;
}
}
unset($data[$key]);
} elseif ($key == 'route' && $value == 'common/home') {
$url = '/';
}
}
}

if ($url) {
unset($data['route']);
$query = '';
if ($data) {
foreach ($data as $key => $value) {
$query .= '&' . rawurlencode((string)$key) . '=' . rawurlencode((string)$value);
}
if ($query) {
$query = '?' . trim($query, '&');
}
}
return $url_info['scheme'] . '://' . $url_info['host'] . (isset($url_info['port']) ? ':' . $url_info['port'] : '') . str_replace('/index.php', '', $url_info['path']) . $url . $query;
} else {
return $link;
} }

    可以看到，在每次执行生成一个URL时，Opencart都会调用这个函数查询一次数据库。

$query = $this->db->query("SELECT * FROM " . DB_PREFIX . "url_alias WHERE `query` = '" . $this->db->escape($key . '=' . (int)$value) . "'");

    有一种情况我们肯定经常碰到， 一个数据循环展示的时候，会在展示中添加点击的链接，而这个链接需要调用上面的URL进行伪静态处理，而每次调用都会产生一次数据库调用，像这样就会产生上百次数据库调用，造成性能极大下滑。想我在使用OPENCART时打印出Opencart的MYSQL查询记录列表,看到几百次调用url_alias时我真是醉了。

    知道了问题所在，解决的方法也很简单，如是这个页面调用的URl会是很多，比如类目伪静态化会调用url_alias表中很多数据，可以直接把表数据一次性读取出来，如果调用的次数多，但基本都是一个链接，可以从表中读取并保存到数组中，下次再调用时判断是否已有此数据。

publish:March 30, 2015 -Monday

二、SQL查询数据库占用大小的方法

    数据库在使用过程中，数据一直在增加，有时想知道一下，现在的数据占用多大空间？会不会太大？目前的MySQL数据库的最大有效表尺寸通常是由操作系统对文件大小的限制决定的，MySQL本身已经没有瓶颈。

    查询数据库所占用空间有多种方法：比如在服务器的数据文件夹中直接去统计mysql相关数据库文件的大小，也可以用一些软件比如navicat连接mysql,直接在界面上简单查看。当然也可以使用sql查询。数据库的内容包括数据表的内容和索引的内容，查询出来的大小单位为byte.可直接在sql中计算出MB或GB大小值。如下：

#查询数据库占用大小SQL
SELECT (sum(DATA_LENGTH)+sum(INDEX_LENGTH))/(1024*1024) as database_mb FROM information_schema.TABLES where TABLE_SCHEMA='databaseName';
#查询各表的更新时间
select TABLE_NAME,UPDATE_TIME from INFORMATION_SCHEMA.tables where TABLE_SCHEMA='007CN';

#查询某个表的大小
SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/(1024*1024) as table_mb,TABLE_ROWS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='tableName' AND TABLE_NAME='databaseName'

 查询结果如下：

    实际这些信息就是存储在mysql本身的数据库information_schema里。里面有很多数据表，比较重要的TABLES，COLUMNS

三、关于数据表信息表 INFORMATION_SCHEMA 

所有数据表信息表.information_schema.TABLES 
TABLES表：提供了关于数据库中的表的信息（包括视图）。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。各字段说明如下：

#字段            #含义
Table_catalog   数据表登记目录
Table_schema    数据表所属的数据库名
Table_name      表名称
Table_type      表类型[system view|base table]
Engine          使用的数据库引擎[MyISAM|CSV|InnoDB]
Version         版本，默认值10
Row_format      行格式[Compact|Dynamic|Fixed]
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   表的注释、备注

表字段数据表(COLUMNS) information_schema.COLUMNS 

#字段            #含义
Table_catalog   数据表登记目录
Table_schema    数据表所属的数据库名
Table_name      所属的表名称
Column_name     列名称
Ordinal_position字段在表中第几列
Column_default  列的默认数据
Is_nullable     字段是否可以为空
Data_type       数据类型
Character_maximum_length    字符最大长度
Character_octet_length      字节长度？
Numeric_precision           数据精度
Numeric_scale   数据规模
Character_set_name          字符集名称
Collation_name  字符集校验名称
Column_type     列类型
Column_key      关键列[NULL|MUL|PRI]
Extra           额外描述[NULL|on update CURRENT_TIMESTAMP|auto_increment]
Privileges      字段操作权限[select|select,insert,update,references]
Column_comment  字段注释、描述

    核心INFORMATION_SCHEMA数据库中还有其它多张表，都是有意义的，比如：PROCESSLIST,PROFILING等，有空时都可以好好看看。mysql里很多的系统查询实际都是查询的这个数据库.