库表字符集不一致导致的全表扫描问题

背景:
当数据库的建库字符集和表不一样时,在库下针对表创建存储过程可能导致全表扫描

如下例:
drop database if exists xx1;
drop database if exists xx2;
create database xx1 character set utf8;    
create database xx2 character set gbk;

 

然后分别在xx1 和 xx2下执行:
CREATE TABLE t1 ( `col1` varchar(10) NOT NULL, `col2` bigint(11) NOT NULL DEFAULT '0', PRIMARY KEY (`col1`)) ENGINE=InnoDB DEFAULT CHARSET=gbk;
insert into t1 values ('ac',2),('del',3),('sp',5),('tr',12);
DELIMITER //
CREATE PROCEDURE proc1(IN isn CHAR(20))  
BEGIN  
     START TRANSACTION;   
     SELECT col2 FROM t1 WHERE col1= isn FOR UPDATE;  
     COMMIT;
END //
DELIMITER ;

 

在5.5版本中通过show processlist可以看到SQL进行了转换(简单的锁住行记录,执行存储过程,再show processlist):
use xx1;
call proc1(‘tr’); 参数isn会被展开成:NAME_CONST(‘isn’,_utf8 0x7472 COLLATE ‘utf8_general_ci’)
use xx2;
call proc1(‘tr’); 参数isn会被展开成:NAME_CONST(‘isn’,_gbk 0x7472 COLLATE ‘gbk_chinese_ci’)

在5.6版本里使用ROW模式时,不会展开sql,但内部采取的是同样的策略。
由于name_const中引用的的字符集和表定义不同,因此xx1.proc1()每次调用都会产生两次全表扫描。但奇怪的是,如果单独执行上述SQL(而不是调用存储过程),就不会全表扫描。

还好MySQL5.6开始有了optimizer_trace这个利器,打开optimizer trace就一目了然了,对比单独执行SQL和执行存储过程:
call xx1.proc1(‘tr’)
| SELECT col2 FROM t1 WHERE col1=  NAME_CONST(‘isn’,_utf8’tr’ COLLATE ‘utf8_general_ci’) FOR UPDATE | {
“steps”: [
{
“join_preparation”: {
“select#”: 1,
“steps”: [
{
           “expanded_query”: “/* select#1 */ select `t1`.`col2` AS `col2` from `t1` where (convert(`t1`.`col1` using utf8) = isn@0)”
}
]
}
},
{
“join_optimization”: {
“select#”: 1,
“steps”: [
{
“condition_processing”: {
“condition”: “WHERE”,
“original_condition”: “(convert(`t1`.`col1` using utf8) = isn@0)”,
“steps”: [
{
“transformation”: “equality_propagation”,
“resulting_condition”: “(convert(`t1`.`col1` using utf8) = isn@0)”
},
{
“transformation”: “constant_propagation”,
“resulting_condition”: “(convert(`t1`.`col1` using utf8) = isn@0)”
},
{
“transformation”: “trivial_condition_removal”,
“resulting_condition”: “(convert(`t1`.`col1` using utf8) = isn@0)”
}
]
}
},
{
“table_dependencies”: [
{
“table”: “`t1`”,
“row_may_be_null”: false,
“map_bit”: 0,
“depends_on_map_bits”: [
]
}
]
},
{
“ref_optimizer_key_uses”: [
]
},
{
“rows_estimation”: [
{
“table”: “`t1`”,
“table_scan”: {
“rows”: 4,
“cost”: 1
}
}
]
},
{
“considered_execution_plans”: [
{
“plan_prefix”: [
],
“table”: “`t1`”,
“best_access_path”: {
“considered_access_paths”: [
{
“access_type”: “scan”,
“rows”: 4,
“cost”: 1.8,
“chosen”: true
}
]
},
“cost_for_plan”: 1.8,
“rows_for_plan”: 4,
“chosen”: true
}
]
},
{
“attaching_conditions_to_tables”: {
“original_condition”: “(convert(`t1`.`col1` using utf8) = isn@0)”,
“attached_conditions_computation”: [
],
“attached_conditions_summary”: [
{
“table”: “`t1`”,
“attached”: “(convert(`t1`.`col1` using utf8) = isn@0)”
}
]
}
},
{
“refine_plan”: [
{
“table”: “`t1`”,
“access_type”: “table_scan”
}
]
}
]
}
},
{
“join_execution”: {
“select#”: 1,
“steps”: [
]
}
}
]
}
call xx2.proc1(‘tr’)
SELECT col2 FROM t1 WHERE col1=  NAME_CONST(‘isn’,_gbk’tr’ COLLATE ‘gbk_chinese_ci’) FOR UPDATE | {
“steps”: [
{
“join_preparation”: {
“select#”: 1,
“steps”: [
{
“expanded_query”: “/* select#1 */ select `t1`.`col2` AS `col2` from `t1` where (`t1`.`col1` = isn@0)”
}
]
}
},
{
“join_optimization”: {
“select#”: 1,
“steps”: [
{
“condition_processing”: {
“condition”: “WHERE”,
“original_condition”: “(`t1`.`col1` = isn@0)”,
“steps”: [
{
“transformation”: “equality_propagation”,
“resulting_condition”: “multiple equal(isn@0, `t1`.`col1`)”
},
{
“transformation”: “constant_propagation”,
“resulting_condition”: “multiple equal(isn@0, `t1`.`col1`)”
},
{
“transformation”: “trivial_condition_removal”,
“resulting_condition”: “multiple equal(isn@0, `t1`.`col1`)”
}
]
}
},
{
“table_dependencies”: [
{
“table”: “`t1`”,
“row_may_be_null”: false,
“map_bit”: 0,
“depends_on_map_bits”: [
]
}
]
},
{
“ref_optimizer_key_uses”: [
{
“table”: “`t1`”,
“field”: “col1”,
“equals”: “isn@0”,
“null_rejecting”: false
}
]
},
{
“rows_estimation”: [
{
“table”: “`t1`”,
“rows”: 1,
“cost”: 1,
“table_type”: “const”,
“empty”: false
}
]
},
{
“condition_on_constant_tables”: “1”,
“condition_value”: true
},
{
“attaching_conditions_to_tables”: {
“original_condition”: “1”,
“attached_conditions_computation”: [
],
“attached_conditions_summary”: [
]
}
},
{
“refine_plan”: [
]
}
]
}
},
{
“join_execution”: {
“select#”: 1,
“steps”: [
]
}
}
]
}
use xx1;

SELECT col2 FROM t1 WHERE col1=
NAME_CONST
(‘isn’,_utf8’tr’ COLLATE ‘utf8_general_ci’)
 FOR UPDATE
 SELECT col2 FROM t1 WHERE col1=  NAME_CONST(‘isn’,_utf8’tr’ COLLATE ‘utf8_general_ci’) FOR UPDATE | {

  “steps”: [

    {

      “join_preparation”: {

        “select#”: 1,

        “steps”: [

          {

            “expanded_query”: “/* select#1 */ select `t1`.`col2` AS `col2` from `t1` where (`t1`.`col1` = convert(NAME_CONST(‘isn’,(_utf8’tr’ collate utf8_general_ci)) using gbk))”

          }

        ]

      }

    },

    {

      “join_optimization”: {

        “select#”: 1,

        “steps”: [

          {

            “condition_processing”: {

              “condition”: “WHERE”,

              “original_condition”: “(`t1`.`col1` = convert(NAME_CONST(‘isn’,(_utf8’tr’ collate utf8_general_ci)) using gbk))”,

              “steps”: [

                {

                  “transformation”: “equality_propagation”,

                  “resulting_condition”: “multiple equal(convert(NAME_CONST(‘isn’,(_utf8’tr’ collate utf8_general_ci)) using gbk), `t1`.`col1`)”

                },

                {

                  “transformation”: “constant_propagation”,

                  “resulting_condition”: “multiple equal(convert(NAME_CONST(‘isn’,(_utf8’tr’ collate utf8_general_ci)) using gbk), `t1`.`col1`)”

                },

                {

                  “transformation”: “trivial_condition_removal”,

                  “resulting_condition”: “multiple equal(convert(NAME_CONST(‘isn’,(_utf8’tr’ collate utf8_general_ci)) using gbk), `t1`.`col1`)”

                }

              ]

            }

          },

          {

            “table_dependencies”: [

              {

                “table”: “`t1`”,

                “row_may_be_null”: false,

                “map_bit”: 0,

                “depends_on_map_bits”: [

                ]

              }

            ]

          },

          {

            “ref_optimizer_key_uses”: [

              {

                “table”: “`t1`”,

                “field”: “col1”,

                “equals”: “convert(NAME_CONST(‘isn’,(_utf8’tr’ collate utf8_general_ci)) using gbk)”,

                “null_rejecting”: false

              }

            ]

          },

          {

            “rows_estimation”: [

              {

                “table”: “`t1`”,

                “rows”: 1,

                “cost”: 1,

                “table_type”: “const”,

                “empty”: false

              }

            ]

          },

          {

            “condition_on_constant_tables”: “1”,

            “condition_value”: true

          },

          {

            “attaching_conditions_to_tables”: {

              “original_condition”: “1”,

              “attached_conditions_computation”: [

              ],

              “attached_conditions_summary”: [

              ]

            }

          },

          {

            “refine_plan”: [

            ]

          }

        ]

      }

    },

    {

      “join_execution”: {

        “select#”: 1,

        “steps”: [

        ]

      }

    }

  ]
}

 

 


注意这expanded_query一项,对于这种方式的查询,需要读取每一条记录,进行字符集转换再比较:
expanded_query”: “/* select#1 */ select `t1`.`col2` AS `col2` from `t1` where (convert(`t1`.`col1` using utf8) = isn@0)

由于本人对优化器模块了解不深,也不确定是否是Bug,先Report到了一个小bug到Bug list,ref:http://bugs.mysql.com/bug.php?id=76002
Updated @2015/2/25, 存储过程的输入列需要显式的定义输入字符集,否则其输入列的字符集将继承自库的字符集。参考bug中的回复。

 

原创文章,转载请注明: 转载自Simple Life

本文链接地址: 库表字符集不一致导致的全表扫描问题

Post Footer automatically generated by wp-posturl plugin for wordpress.


Comments

Leave a Reply

Your email address will not be published. Name and email are required


Current month ye@r day *