论magento：到执行contrller前执行的sql （缓存开启和未开启的执行）

本文通过对比分析Magento在开启与关闭缓存时执行的SQL查询及所需时间，展示了缓存在提升网站性能方面的重要作用。实测结果显示，启用缓存后，系统响应速度提升了数倍。

1未开启缓存执行controller时执行的sql

140724  5:17:07      356 Connect    root@localhost on tomtop_test
          356 Prepare    SET SQL_MODE=''
          356 Execute    SET SQL_MODE=''
          356 Close stmt    
          356 Query    SET NAMES utf8
          356 Query    SHOW TABLE STATUS LIKE 'core_resource'
          356 Query    SELECT `core_resource`.* FROM `core_resource`
          356 Query    SELECT `index_process`.`indexer_code` FROM `index_process`
          356 Query    SELECT `core_website`.`website_id`, `core_website`.`code`, `core_website`.`name` FROM `core_website`
          356 Query    SELECT `core_store`.`store_id`, `core_store`.`code`, `core_store`.`name`, `core_store`.`website_id` FROM `core_store` ORDER BY `sort_order` ASC
          356 Query    SELECT `core_config_data`.`scope`, `core_config_data`.`scope_id`, `core_config_data`.`path`, `core_config_data`.`value` FROM `core_config_data`
          356 Query    SELECT `main_table`.* FROM `core_store` AS `main_table` ORDER BY CASE WHEN main_table.store_id = 0 THEN 0 ELSE 1 END ASC, main_table.sort_order ASC, main_table.name ASC
          356 Query    SELECT `main_table`.* FROM `core_website` AS `main_table` ORDER BY main_table.sort_order ASC, main_table.name ASC
          356 Query    SELECT `main_table`.* FROM `core_store_group` AS `main_table` ORDER BY main_table.name ASC
          356 Query    SELECT `core_url_rewrite`.* FROM `core_url_rewrite` WHERE (request_path IN ('vynamic/customer/info?aid=terrywater', 'vynamic/customer/info/?aid=terrywater', 'vynamic/customer/info', 'vynamic/customer/info/')) AND (store_id IN(0, 1))
          356 Query    SELECT `main_table`.* FROM `design_change` AS `main_table` WHERE (store_id = '1') AND (date_from <= '2014-07-23 21:17:07' or date_from IS NULL) AND (date_to >= '2014-07-23 21:17:07' or date_to IS NULL)
          356 Query    SELECT `core_translate`.`string`, `core_translate`.`translate` FROM `core_translate` WHERE (store_id IN (0 , '1')) AND (locale = 'en_US') ORDER BY `store_id` ASC
          356 Quit

开启缓存后执行的sql：

 368 Connect    root@localhost on tomtop_test
          368 Prepare    SET SQL_MODE=''
          368 Execute    SET SQL_MODE=''
          368 Close stmt    
          368 Query    SET NAMES utf8
          368 Query    SELECT `core_url_rewrite`.* FROM `core_url_rewrite` WHERE (request_path IN ('vynamic/customer/info', 'vynamic/customer/info/')) AND (store_id IN(0, 1))
          368 Query    SELECT `main_table`.* FROM `design_change` AS `main_table` WHERE (store_id = '1') AND (date_from <= '2014-07-23 21:22:02' or date_from IS NULL) AND (date_to >= '2014-07-23 21:22:02' or date_to IS NULL)
          368 Quit    
140724  5:22:15      366 Query    SELECT `main_table`.* FROM `design_change` AS `main_table` WHERE (store_id = '1') AND (date_from <= '2014-07-23 21:22:02' or date_from IS NULL) AND (date_to >= '2014-07-23 21:22:02' or date_to IS NULL)

除了在加载xml的时候耗费时间外，对数据库的操作也是有改变。

开启缓存的时间耗费80ms：

关闭缓存后的时间为550ms

开启缓存，在初始化上就是7倍的差距

在controller中加入：

$this->loadlayout();
$this->renderLayout();

未开启缓存的时间耗费：1.51s

开启缓存后的时间耗费：151ms

执行的sql:

140724  6:23:41	  499 Connect	root@localhost on tomtop_test
		  499 Prepare	SET SQL_MODE=''
		  499 Execute	SET SQL_MODE=''
		  499 Close stmt	
		  499 Query	SET NAMES utf8
		  499 Query	SELECT `core_url_rewrite`.* FROM `core_url_rewrite` WHERE (request_path IN ('vynamic/customer/info', 'vynamic/customer/info/')) AND (store_id IN(0, 1))
		  499 Query	SELECT `main_table`.* FROM `design_change` AS `main_table` WHERE (store_id = '1') AND (date_from <= '2014-07-23 22:23:41' or date_from IS NULL) AND (date_to >= '2014-07-23 22:23:41' or date_to IS NULL)
		  499 Query	SELECT `eav_entity_type`.* FROM `eav_entity_type` WHERE (`eav_entity_type`.`entity_type_code`='customer')
		  499 Query	SELECT `customer_entity`.`entity_id` FROM `customer_entity` WHERE (entity_id = '424297') LIMIT 1
		  499 Query	SELECT `directory_currency_rate`.`currency_to`, `directory_currency_rate`.`rate` FROM `directory_currency_rate` WHERE (currency_from = 'USD') AND (currency_to IN('AUD', 'BRL', 'GBP', 'EUR', 'RUB', 'USD'))
		  499 Query	SELECT `cms_block`.*, `cbs`.`store_id` FROM `cms_block`
 INNER JOIN `cms_block_store` AS `cbs` ON cms_block.block_id = cbs.block_id WHERE (`cms_block`.`identifier`='footer-newslater') AND (is_active = 1) AND (cbs.store_id in (1, 0) ) ORDER BY `store_id` DESC LIMIT 1
		  499 Query	SELECT `cms_block_store`.`store_id` FROM `cms_block_store` WHERE (block_id = '85')
		  499 Quit

时间为10倍的差距

到这里总结：

开启缓存后：magento到执行到contrller的时候耗费的时间大约是80ms，从controller到加载xml的过程中耗费的时间大致是70ms也就是说这些占了0.15秒左右，这个时间还是可以接受的，当然这个是本地的测试，对于线上，需要具体情况详细测试！