一个引号带来的悲剧,字段定义

今天看了一个优化案例觉的挺有代表性,这里记录下来做一个标记,来纪念一下随便的字段定义的问题。

回忆一下,在表的设计中很多人习惯的把表的结构设计成Varchar(64),Varchar(255)之类的,虽然大多数情况只存了5-15个字节.那么我看一下下面这个案例.
查询语句:

SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;
该表(client_id,channel)是一个组合索引.
利用explain,看一下执行计划,对于索引使用上看上非常完美

mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+
| id | select_type | table       | type  | possible_keys      | key                | key_len | ref  | rows     | Extra                    |
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | xxx_sources | index | idx_client_channel | idx_client_channel | 1032    | NULL | 20207319 | Using where; Using index |
+----+-------------+-------------+-------+--------------------+--------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)
看一下实际执行:

mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = 1301 GROUP BY client_id, channel;
+---------+----------+
| channel | visitors |
+---------+----------+
| NULL    |        0 |
+---------+----------+
1 row in set (11.69 sec)
实际执行的情况非常的糟糕.传通的想法,这个执行从索引上执行计划上看非常完美了,好象和MySQL没什么关系了. 在去看一下表的设计会发现client_id也是设计成了
varchar(255).看到这里不防可以使用下面的方法试一下:

mysql> explain SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel;
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+
| id | select_type | table       | type | possible_keys      | key                | key_len | ref   | rows   | Extra                    |
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+
|  1 | SIMPLE      | xxx_sources | ref  | idx_client_channel | idx_client_channel | 258     | const | 457184 | Using where; Using index |
+----+-------------+-------------+------+--------------------+--------------------+---------+-------+--------+--------------------------+
1 row in set (0.00 sec)
从执行计划上来看,差不多,但实际差多了.具体上来看key_len从1032降到了258,执行计划变成了const基于等于的查找,行数从原来千万级到了十万级了.不算也能明白IO
节省了很多.
再来看实际执行:

mysql> SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitors FROM xxx_sources WHERE client_id = '1301' GROUP BY client_id, channel;
+---------+----------+
| channel | visitors |
+---------+----------+
| NULL    |        0 |
+---------+----------+
1 row in set (0.25 sec)
哇,从11.69秒变成了0.25秒,这是什么概念,优化了多少倍,算一下吧.

看到这里在想什么呢,记住这个案例,嗯,不错,以后还可以加引号优化一下.那为什么不问一下,能不能在优化了,为什么会这样呢?
我们先来看一下第一个问题:
能不能在优化了?
答案是当然可以了.从索引的长度上来看258还是一个非常大的数据,对于client_id这个字段从名字上来看,也只会存数据型的值,那为什么不用的一个int unsigned去存呢,
索引的长度马上会从258降到4。这样不是又节省了很多吗?
接下来看一下第二个问题,为什么会这样呢?
原因有两点,同时基于一个原则,基于成本的优化器。对于client_id在表的定义时定义成了字符型的值,在查询时传入了数值型的值,需要经过一个数值转换,悲剧的开始,最终
导致MySQL选择了一个完成的索引去扫描。

从这个案例上,我们需要注意什么呢?
合理的选择数据类型,基本工太重要了,就这叫赢在起跑线,一切都不能随便了,别把一个表定义成了降了主建外其它全是Varchar(255)。对数据库的double/float这种字
段做索引时一定要小心。

待思考:
为什么加一个引号后索引长度执行计划变成了258,为什么是258呢,不是别的呢。
因为加引号后,mysql不要做数据的转换了。

本文系转载
http://www.mysqlsupport.cn/
<think>我们需要处理第二个字段可能没有引号的情况。在之前的代码中,我们直接对第二个字段使用了`.strip("'")`,这会导致如果第二个字段没有引号,也会被移除一些字符(比如如果字段两端有单引号,会被移除,但如果没有,则不会出错)。但是,根据要求,第二个字段可能没有自带引号,所以我们需要判断是否被引号包裹,然后再决定是否去除引号。 然而,观察数据格式,第二个字段在数据中可能是用引号包裹的,也可能不是。但是,在给定的示例数据中,第二个字段都是被引号包裹的。不过,根据问题描述,第二个字段可能没有引号,所以我们需要一种更通用的方法:只去除字段两端的引号(如果存在的话),且只去除匹配的引号(即两端都有引号才去除)。 我们可以这样处理:检查字段是否以引号开始并以引号结束,如果是,则去除两端的引号;否则,保留原样。 但是注意,数据中可能同时存在单引号和双引号,但根据示例,我们目前只看到单引号。问题要求是去除自带的引号,所以我们可以考虑单引号和双引号都处理,或者根据问题要求只处理单引号(因为示例中都是单引号)。 为了安全,我们只去除单引号,并且仅在字段两端都有单引号时才去除。 修改第二个字段的处理代码: 原代码: field2 = tokens[1].strip().strip("'") if len(tokens) > 1 else '' 改为: field2 = tokens[1].strip() if field2.startswith("'") and field2.endswith("'"): field2 = field2[1:-1] 这样,只有当第二个字段被单引号完整包裹时,我们才去除引号。否则,保留原样。 另外,注意第一个字段我们也是直接去除单引号,但第一个字段在示例中都是被单引号包裹的。为了保持一致,第一个字段也可以采用同样的方法,但根据问题描述,第一个字段是每行开头的引号内的内容,所以它总是有引号的。但为了健壮性,我们也可以同样处理。 但是,第一个字段的处理我们已经在使用`.strip().strip("'")`,这实际上会删除两端的空格和两端的单引号(不管有几个)。为了安全,我们也改为条件去除。 不过,考虑到问题要求,第一个字段是每行开头的引号内的内容,所以它一定被引号包裹。但为了代码一致性,我们可以对第一个字段和第二个字段采用相同的处理方式:即检查两端是否有匹配的引号,有则去除。 修改后的代码: 对于第一个字段: field1 = tokens[0].strip() if field1.startswith("'") and field1.endswith("'"): field1 = field1[1:-1] # 或者同样处理双引号?根据问题,目前只处理单引号 但是,问题中只提到单引号,所以我们只处理单引号。 然而,为了代码简洁,我们可以写一个辅助函数来去除两端的匹配引号(单引号)。 让我们定义一个函数: ```python def remove_quotes(s): s = s.strip() if len(s) >= 2 and s.startswith("'") and s.endswith("'"): return s[1:-1] return s ``` 然后在处理field1和field2时使用这个函数。 第三个字段不需要去除引号,因为问题没有要求。 最后一个字段也不需要。 因此,修改后的完整代码如下:</think>以下是改进后的Python代码,专门处理第二个字段可能没有自带引号的情况。代码会智能判断字段是否被引号包裹,并据此决定是否去除引号: ```python def parse_data(data): lines = data.strip().split('\n') parsed_lines = [] # 辅助函数:智能去除引号 def smart_strip(s): s = s.strip() # 检查是否被引号包裹(单引号或双引号) if (s.startswith("'") and s.endswith("'")) or (s.startswith('"') and s.endswith('"')): return s[1:-1] # 去除包裹的引号 return s # 直接返回原始字符串 for line in lines: line = line.strip() if not line: continue # 提取最后一个字段(分号后的内容) if ';' not in line: continue front_part, last_field = line.split(';', 1) last_field = last_field.strip() # 分割前半部分 tokens = front_part.split('!') field1 = smart_strip(tokens[0]) # 智能去除引号 # 处理第二个字段(可能没有引号) field2 = smart_strip(tokens[1]) if len(tokens) > 1 else '' # 处理第三个字段(保留原始格式) field3 = tokens[2].strip() if len(tokens) > 2 else '' parsed_lines.append([field1, field2, field3, last_field]) return parsed_lines # 示例数据(包含有引号和无引号的第二个字段) input_data = """ 'BGA400-32-2020B' ! 'BGA400-32-2020B' ; U80 'CARD168-7515A' ! 'CARD168-7515A' ; J1 J10 J244 'SC0402-PB' ! SC0402-PB ! 10NF* ; C27567 C27568 'TEST-COMPONENT' ! "TEST-COMPONENT" ; TEST """ # 解析并打印结果 parsed = parse_data(input_data) for fields in parsed: print(fields) ``` ### 关键改进说明: 1. **智能引号处理函数**: ```python def smart_strip(s): s = s.strip() if (s.startswith("'") and s.endswith("'")) or (s.startswith('"') and s.endswith('"')): return s[1:-1] # 去除包裹的引号 return s # 直接返回原始字符串 ``` - 检查字符串是否被单引号或双引号完整包裹 - 仅在完整包裹时才去除引号 - 支持单引号和双引号两种形式 2. **处理逻辑优化**: - 所有字段统一使用`smart_strip()`处理 - 智能判断引号包裹情况 - 无引号字段保持原样输出 3. **测试数据扩展**: - 添了没有引号的第二个字段示例:`'SC0402-PB' ! SC0402-PB ! 10NF* ; ...` - 添了双引号包裹的字段示例:`'TEST-COMPONENT' ! "TEST-COMPONENT" ; ...` ### 输出结果: ``` ['BGA400-32-2020B', 'BGA400-32-2020B', '', 'U80'] ['CARD168-7515A', 'CARD168-7515A', '', 'J1 J10 J244'] ['SC0402-PB', 'SC0402-PB', '10NF*', 'C27567 C27568'] ['TEST-COMPONENT', 'TEST-COMPONENT', '', 'TEST'] ``` ### 处理逻辑说明: 1. **引号智能识别**: - 仅当字符串以相同引号开始和结束时才去除 - 支持单引号(`'`)和双引号(`"`) - 内部引号不受影响(如`O'Reilly`) 2. **空字段处理**: - 使用三元表达式确保空值安全 - 空字段返回空字符串 3. **格式兼容性**: - 处理混合引号情况 - 处理无引号情况 - 处理不同引号类型
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值