诊断 MySQL 存储过程中的排序规则问题

我们在生产环境中使用 Elmah 来记录 Web 应用程序中的问题。Elmah 日志存储在 MySQL 表中，该表的结构是对原始 Elmah 脚本中的 elman_error 表的轻微修改版本（我将引擎更改为 InnoDB，并在 TimeUTC 列上添加了分区）。

CREATE TABLE IF NOT EXISTS 'elmah_error' (
  'ErrorId' CHAR(36) NOT NULL ,
  'Application' VARCHAR(60) NOT NULL ,
  'Host' VARCHAR(50) NOT NULL ,
  'Type' VARCHAR(100) NOT NULL ,
  'Source' VARCHAR(60) NOT NULL ,
  'Message' VARCHAR(500) NOT NULL ,
  'User' VARCHAR(50) NOT NULL ,
  'StatusCode' INT(10) NOT NULL ,
  'TimeUtc' DATETIME NOT NULL ,
  'Sequence' INT(10) NOT NULL AUTO_INCREMENT ,
  'AllXml' TEXT NOT NULL ,
  PRIMARY KEY ('Sequence', 'TimeUtc') ,
  INDEX 'IX_ErrorId' ('ErrorId'(8) ASC) , -- UNIQUE can't be created if it does not include all partition columns
  INDEX 'IX_ELMAH_Error_App_Time_Seql' ('Application'(10) ASC, 'TimeUtc' DESC, 'Sequence' DESC) ,
  INDEX 'IX_ErrorId_App' ('ErrorId'(8) ASC, 'Application'(10) ASC) )
ENGINE InnoDB DEFAULT CHARACTER SET latin2
partition by range columns('TimeUtc')
(
  partition before20130719 values less than ('2013-07-19 00:00')
);

请注意，默认排序规则设置为 latin2（我们有时在日志中包含波兰字符）。我们还有一个诊断仪表板——一个简单的 Web 应用程序，允许开发人员检查每个应用程序的 Elmah.axd 控制台（我描述了它的配置方式在 另一篇文章中）。当日志变大（每天几个分区）时，错误详细信息屏幕开始加载得极其缓慢，有时甚至会抛出超时异常。这确实令人惊讶，特别是如果你查看页面 URL，其中包含要显示的错误的 ID（因此是上述表中的主键）。在其中一次等待期间，我找到了导致延迟的查询

> select * From information_schema.processlist

...
SELECT  'AllXml'
    FROM    'elmah_error'
    WHERE   'ErrorId' =  NAME_CONST('Id',
      _utf8'dd79443e-f7a2-4d45-abff-a57710016f45' COLLATE 'utf8_unicode_ci') 
      AND 'Application' =  NAME_CONST('App',_utf8'SomeApp' COLLATE 'utf8_unicode_ci')
...

EXPLAIN 对此查询的显示清楚地表明优化器没有使用 IX_ErrorId_App 索引（甚至 IX_ErrorId）。

id	select_type	table	type	possible_keys	键	key_len	ref	rows	额外
1	SIMPLE	elmah_error	ALL	(NULL)	(NULL)	(NULL)	(NULL)	2	Using where

我也不确定为什么 Elmah 将每个参数包装到 NAME_CONST 函数中。我检查了 Elmah 源代码，只发现它只是在调用 elmah_GetErrorXml 存储过程。

// Elmah.MySqlErrorLog.Commands
public static MySqlCommand GetErrorXml(string appName, Guid id)
{
  MySqlCommand mySqlCommand = new MySqlCommand("elmah_GetErrorXml");
  mySqlCommand.CommandType = CommandType.StoredProcedure;
  MySqlParameterCollection parameters = mySqlCommand.Parameters;
  parameters.Add("Id", MySqlDbType.String, 36).Value = id.ToString();
  parameters.Add("App", MySqlDbType.VarChar, 60).Value = 
         appName.Substring(0, Math.Min(60, appName.Length));
  return mySqlCommand;
}

存储过程本身看起来也没有什么可疑之处。

DELIMITER //

CREATE PROCEDURE 'elmah_GetErrorXml' (
  IN Id CHAR(36),
  IN App VARCHAR(60)
)
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    SELECT  'AllXml'
    FROM    'elmah_error'
    WHERE   'ErrorId' = Id AND 'Application' = App;
END//

找不到人来责怪，我开始在 Google 上搜索，发现 MySQL 会将存储过程内部使用的所有参数包装到 NAME_CONST 调用中。另一个有趣的事实是，它还会为字符串指定排序规则（将来自 .NET 连接器的字符串视为 UTF8）。当索引键值与 WHERE 条件中的参数排序规则不同时，MySQL 无法使用索引并需要执行全表扫描。在我们的例子中，最简单的可行解决方案是修改 elmah_GetErrorXml 存储过程并强制它使用有效的排序规则。

DROP PROCEDURE IF EXISTS DiagnosticsDB.elmah_GetErrorXml;
CREATE PROCEDURE DiagnosticsDB.'elmah_GetErrorXml'(
  IN Id CHAR(36),
  IN App VARCHAR(60)
)
    READS SQL DATA
BEGIN
    SELECT  'AllXml'
    FROM    'elmah_error'
    WHERE   'ErrorId' = convert(Id using latin2) collate latin2_general_ci 
      AND 'Application' = convert(App using latin2) collate latin2_general_ci;
END;

您还可以为 elmah_error 表指定 UTF8 作为默认排序规则——这可能是一个更好的解决方案。总而言之，如果您遇到存储过程调用中的滞后，请确保参数已由 MySQL 正确包装，并且使用了有效的索引。为此，您可以使用简单的 SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST 查询或设置 慢查询日志。