当前位置:首页 > 开发 > IT生活 > 正文

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

发表于: 2013-06-28   作者:coollyj   来源:转载   浏览次数:
摘要: 今天看了一个优化案例觉的挺有代表性,这里记录下来做一个标记,来纪念一下随便的字段定义的问题。 回忆一下,在表的设计中很多人习惯的把表的结构设计成Varchar(64),Varchar(255)之类的,虽然大多数情况只存了5-15个字节.那么我看一下下面这个案例. 查询语句: SELECT SQL_NO_CACHE channel, COUNT(channel) AS visitor
今天看了一个优化案例觉的挺有代表性,这里记录下来做一个标记,来纪念一下随便的字段定义的问题。

回忆一下,在表的设计中很多人习惯的把表的结构设计成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/

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

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
最近写了个程序,题目是这样的:   做一个名片管理系统,实现存储,修改,删除,最关键的是能方便
今天突然想了下给字段插入引号的SQL该怎样写,然后就百度了一下,结果看各位的结果真是云里雾里啊
记得以前玩游戏的时候,积攒下来的金币越来越多。看着那个数字长呀、长呀,突然有一天,我的钱莫名
在对 Jetty 性能测试 AJP 静态页面发现,压力一上来 LR 端就出现 504 错误,查看 nginx 的 logs 发
<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View&
[http://www.cnblogs.com/FreeDong/archive/2011/11/16/2250548.html] 在SharePoint中创建自定义字
  在SharePoint中创建自定义字段类型,必须完成两个工作:创建继承于SPField的字段类;编写字段的
在《 Orchard中的一些名词》一文中介绍过字段( Field)的概念,它是一种轻量级的内容部件,通常只
前段时间在调试项目中某个功能的时候,由于是在测试环境中,所以在通过RPC框架调用某个远程服务获取
我们用一个字符串类型的字段来作为主键,表面上,这太不如意了,然而,事实也证明这是有用的。问题
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号