mysql - 在 MySQL 存储过程中使用参数 WHERE-CLAUSE 会降低性能

Question

我有一个声明如下的存储过程:

CREATE DEFINER=`blabla`@`%` PROCEDURE `getAllDomainsByCountry`(IN dom_id INT)

BEGIN
SELECT 
domain.id,
IFNULL(domain.indexed, '-') AS indexed,
domain.name,
country.language_code,
IFNULL(ip_adress.adress, '-') AS adress,
IFNULL(GROUP_CONCAT(category.name
            SEPARATOR ', '),
        '-') AS categories,
IFNULL(GROUP_CONCAT(category.id
            SEPARATOR ', '),
        '-') AS categories_id,
(SELECT 
        IFNULL(GROUP_CONCAT(DISTINCT client.name
                        SEPARATOR ', '),
                    '-')
    FROM
        link
            LEFT JOIN
        client_site ON link.client_site = client_site.id
            LEFT JOIN
        client ON client.id = client_site.client
    WHERE
        link.from_domain = domain.id) AS clients,
IFNULL(domain_host.name, '-') AS domain_host_account,
IFNULL(content_host.name, '-') AS content_host,
status.id AS status,
status.name AS status_name
FROM
domain
    LEFT JOIN
ip_adress ON domain.ip = ip_adress.id
    LEFT JOIN
domain_category ON domain.id = domain_category.domain
    LEFT JOIN
category ON domain_category.category = category.id
    LEFT JOIN
country ON domain.country = country.id
    LEFT JOIN
domain_host_account ON domain.domain_host_account = domain_host_account.id
    LEFT JOIN
domain_host ON domain_host_account.host = domain_host.id
    LEFT JOIN
content_host ON domain.content_host = content_host.id
    LEFT JOIN
domain_status ON domain.id = domain_status.domain
    LEFT JOIN
status ON domain_status.status = status.id
WHERE
domain.country = dom_id
GROUP BY domain.id
ORDER BY domain.name;  
END

如果我用静态整数替换参数 dom_id 的用法，即:

WHERE
  domain.country = 1

MySQL版本:5.5.41

解释:

id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra
1,PRIMARY,domain,ref,idx_domain_country,idx_domain_country,5,const,1858,"Using where; Using temporary; Using filesort"
1,PRIMARY,ip_adress,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain.ip,1,
1,PRIMARY,domain_category,ref,FK_domain_category_domain_idx,FK_domain_category_domain_idx,5,dominfo.domain.id,1,
1,PRIMARY,category,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain_category.category,1,
1,PRIMARY,country,const,PRIMARY,PRIMARY,4,const,1,
1,PRIMARY,domain_host_account,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain.domain_host_account,1,
1,PRIMARY,domain_host,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain_host_account.host,1,
1,PRIMARY,content_host,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain.content_host,1,
1,PRIMARY,domain_status,ALL,NULL,NULL,NULL,NULL,1544,
1,PRIMARY,status,eq_ref,PRIMARY,PRIMARY,4,dominfo.domain_status.status,1,
2,"DEPENDENT SUBQUERY",link,ALL,NULL,NULL,NULL,NULL,8703,"Using where"
2,"DEPENDENT SUBQUERY",client_site,eq_ref,PRIMARY,PRIMARY,4,dominfo.link.client_site,1,
2,"DEPENDENT SUBQUERY",client,eq_ref,PRIMARY,PRIMARY,4,dominfo.client_site.client,1,"Using where"

显示创建表域:

CREATE TABLE `domain` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(67) DEFAULT NULL,
`domain_host_account` int(11) DEFAULT NULL,
`content_host` int(11) DEFAULT NULL,
`ip` varchar(45) DEFAULT NULL,
`historic_content` tinytext,
`redirected` int(11) DEFAULT NULL,
`ftp_account` tinyint(1) DEFAULT ''0'',
`comment` tinytext,
`country` int(11) DEFAULT NULL,
`redirected_text` varchar(45) DEFAULT NULL,
`status_text` varchar(500) DEFAULT NULL,
`dhost_text` varchar(500) DEFAULT NULL,
`chost_text` varchar(500) DEFAULT NULL,
`category_text` varchar(150) DEFAULT NULL,
`dhost_acc_text` varchar(45) DEFAULT NULL,
`indexed` tinyint(1) DEFAULT NULL,
`indexed_checked` date DEFAULT NULL,
`origin` tinyint(1) DEFAULT ''0'',
PRIMARY KEY (`id`),
KEY `FK_domain_host_account_idx` (`domain_host_account`),
KEY `idx_domain_ip` (`ip`),
KEY `idx_domain_country` (`country`),
KEY `idx_domain_domain_host_account` (`domain_host_account`),
KEY `idx_domain_content_host` (`content_host`)
) ENGINE=InnoDB AUTO_INCREMENT=12598 DEFAULT CHARSET=latin1

该过程将花费 0.06 秒执行，而使用参数“dom_id”，传递整数值 1，将导致执行时间为 5.070 秒。有什么想法吗？

Answer 1

根据问题，@sboss 想知道以下行为:

The procedure will take 0.06s to execute with static int whereas using the parameter 
"dom_id", passing integer value of 1, it will result in an execution 
time of 5.070s.

如果我们了解 mysql 引擎的工作原理，这种行为就很容易理解。

MySQL engine caches query and result. The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. If an identical statement is received later, the server retrieves the results from the query cache rather than parsing and executing the statement again.

因此，在您的情况下，很可能在 mysql 引擎实际解析和执行查询时发现 执行时间为 5.070s，而 执行时间为 0.06s 是从查询缓存中检索结果集时发现。

详情请引用文档:https://dev.mysql.com/doc/refman/5.5/en/query-cache.html