几个容易被忽略的mysql知识

　　为什么标题要起这个名字呢？commen sence指的是那些大家都应该知道的事情，但往往大家又会会略这些东西，或者对这些东西一知半解，今天我总结下自己在mysql中遇到的一些commen sense类型的问题。

　　1、varchar(5)可以存储多少个汉字，多少个字母数字？

　　相信有好多人应该跟我一样，对这个已经很熟悉了，根据经验我们能很快的做出决定，比如说用varchar(200)去存储url等等，但是，即使你用了很多次也很熟悉了，也有可能对上面的问题做出错误的回答。

　　这个问题我查了好多资料，有的人说是可以存储5个字符，2.5个汉字（每个汉字占用两个字节的话），有的人说这个要区分版本，5.0是个分界限，5.0之前是前面说的那样，5.0之后是可以存储5个“字”，不区分是数字、英文、汉字，果真是这样吗，我们来做个实验：

　　

CREATE TABLE `test` (
  `name` varchar(5) NOT NULL DEFAULT '',
  `info` char(5) NOT NULL DEFAULT '',
  PRIMARY KEY (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

　　

　　可以看出varchar（5）中的5代表的是5个“字”，而不是5个字节（bytes），当我们存储长度超过制定长度的时候会将超过的部分“咔嚓”掉，我的mysql版本是5.6，字符集（charset）utf8和gbk是一样的。

　　其他版本我电脑上没有，去官方文档看看有没有什么说明，在官方文档中查了半天终于发现了点区别：

　　下面这段来自http://dev.mysql.com/doc/refman/4.1/en/char.html，是对mysq4.1的说明：

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters. (Before MySQL 4.1, the length is interpreted as number of bytes.)

　　再看看其他版本的类似的说明：

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

　　显而易见了，官方文档说了，mysql版本小于4.1的时候存储的时候符合说法：varchar(5)保存5个bytes，及5个英文数字或者2.5个汉字（假设一个汉字2个字节）；

mysql版本大于等于4.1的时候varchar(5)中的5不再是字节数了，应该理解为“字”这里的字的意思是一个汉字和一个英文或者数字“相同对待”

　　还有一点要注意，经过测试text类型的字段还是表示原始的字节含义。

　　2、mysql中的limit，你真的会用吗？

　　你在项目中怎么使用limit？limit num?还是limit num1,num2？还是其他的？要知道limit使用不同的形式性能差距很大的。

　　我自己测试了下，在一张innodb表中去使用limit，表中10000条数据，四个字段，id（int）、time（int）、title（varchar）、body（mediumtext），大小大约170M左右，首先关掉查询缓存，免得查询缓存对查询时间有影响，这里要注意time字段上面加了索引，

SET @@query_cache_type=ON;
SET GLOBAL query_cache_size=0;

　　打开Query profiler来查看语句执行所花费的时间

set profiling=1;

　　接下来对下面几个语句进行执行

　　a、SELECT id,TIME,title FROM cnblogs WHERE TIME>=1315646940 ORDER BY TIME ASC LIMIT 2000,10

　　b、SELECT id,TIME,title FROM cnblogs WHERE TIME>=1315646940 ORDER BY TIME ASC LIMIT 10

     c、 SELECT id,TIME,title FROM cnblogs ORDER BY TIME ASC LIMIT 3000,10

　　执行顺序a，b，c，a，b，c，c，a，a（这里需要注意下，虽然我关闭了缓存，但是上一次的查询还是会缓存，这个可以从Query profiler中看出来，所以进行交叉执行），使用下面的语句查看结果

SHOW profiles;

　　

　　从上面的语句执行时间分析可以看出，不考虑缓存因素，当使用limit的时候，"limit begin,num"这种形式比"limit num"这种形式效率低很多，因此，在使用的时候尽可能的使用第二种形式，比如说要循环获取一个表里面的数据，一次取出来内从放不下，这个时候就要按照id（或者其他排序字段）进行limit了，我们就可以通过获取上次的该字段临界值作为下次取数据的最小值，使用limit num这种形式效率会高很多。

　　 3、mysql字段数目和行大小的限制

　　a) 最大row size（65535 bytes）限制了字段的数目，因为所有字段的长度总和不能超过最大row size限制，举个例子

　　　　utf8字符集中，每个字元需要3个bytes，所以对于一个char(255)的字段，mysql会给每一个值分配255 x 3 = 765 bytes，所以

　　　　一张表包含char(255)类型的字段不能超过65535/765=85个。

　　b)存储字段长度也包含在上面的长度中，举个例子，varchar(255)会花费2个字段存储值的长度，所以每个值占用的空间上升为767个bytes。

　　 长度小于255的时候需要1 bytes。

　　c) 编码长度限制 
　　　　字符类型若为gbk，每个字符最多占2个字节，最大长度不能超过32766; 
　　　　字符类型若为utf8，每个字符最多占3个字节，最大长度不能超过21845。 
　　　　若定义的时候超过上述限制，则varchar字段会被强行转为text类型，并产生warning。 
　　举例：
　　导致实际应用中varchar长度限制的是一个行定义的长度。 MySQL要求一个行的定义长度不能超过65535。若定义的表长度超过这个值，则提示 

　　a)) 若一个表只有一个varchar类型，如定义为 
　　create table t4(c varchar(N)) charset=gbk; 
　　则此处N的最大值为(65535-1-2)/2= 32766。 
　　减1的原因是实际行存储从第二个字节开始'; 
　　减2的原因是varchar头部的2个字节表示长度; 
　　除2的原因是字符编码是gbk。 

　　b)) 若一个表定义为 
　　create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8; 
　　则此处N的最大值为 (65535-1-2-4-30*3)/3=21812 
　　减1和减2与上例相同; 
　　减4的原因是int类型的c占4个字节; 
　　减30*3的原因是char(30)占用90个字节，编码是utf8。 
　　如果被varchar超过上述的b规则，被强转成text类型，则每个字段占用定义长度为11字节，当然这已经不是“varchar”了。

 

 

参考文章：

http://www.jb51.net/article/31589.htm

https://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html

　　本文版权归作者iforever(luluyrt@163.com)所有，未经作者本人同意禁止任何形式的转载，转载文章之后必须在文章页面明显位置给出作者和原文连接，否则保留追究法律责任的权利。