mysql的优化2

选择正确的数据类型
　　Mysql 提供多种数据类型的支持，选择正确的数据类型对能否得到高性能的设计影响很大。 以下一些简单的指导规则能帮助您对各种数据类型的设计决策做出更好的选择：
小的比大的好
　　一般来说， 在保证正确性的前提下， 尽量使用最小的数据类型来存储和表示数据。 小的数据类型一般比大的更快， 因为小的数据类型占用的磁盘空间， 内存和cup缓存都相对小， 需要的cpu处理也要相对少; 这个原则很重要。
简单的就是好的
　　简单的数据类型需要的cup处理周期更少， 比如， 对整数的处理比字符串处理更容易， 因为字符集和排序规则使得字符串比较复杂化了， 两个典型例子是： 使用mysql的内嵌数据类型来存储日期和时间而不是字符串， 使用整数存储Ip地址而不是字符串。
尽量避免NULL数据类型
　　如果可能， 尽可能把字段定义成NOT NULL。 许多表包含一些字段允许空的字段， 即使应用需求不需要存储null的数据， 这样做的原因知识因为允许字段为NULL是缺省的。您应该注意把字段设计生非NULL的， 除非真的是需要存储NULL值。 在查询优化时， mysql很难优化需要引用到能包含NULL值的字段的查询， 因为允许为null的字段使得索引， 索引统计和值比较更复杂。
　　允许NULL的字段会占用掉更多的存储空间和花掉更多的cpu处理， 当为一个可为空的字段建立索引时， 需要为每项分配一个额外的字节， 在myisam中， 甚至会使得一个固定大小的索引(比如建立在单个整数的缩影)变成一个变长大小的索引。 即使遇到不需要存储值到某个字段， 也应当考虑不使用NULL， 而是考虑使用0， 一个特殊值或者空串来代替。 把字段从NULL改成NOT NULL的性能改善通常不是很大， 所以， 除非确实证明NULL字段在引起性能问题， 一般不需要把超找并修改NULL字段为NOT NULL字段当做一件大事来对待; 但是， 在做设计的时候， 注意尽量把需要索引的字段设计成不允许为空的。

决定数据类型的第一步是定义所存数数据的分类： 数值型， 字符串型还是临时型等;除了一些特别的并不是那么直观的外， 这通常是很直观的。
　　接下来是选择具体的数据类型， 许多mysql的数据类型能存储同一种数据， 但是在可存储的数据范围， 准确度或者存储空间有些不同。 有些数据类型可能还有一些特殊的行为属性。比如， DATETIME和TIMESTAMP都能存储日期和日期， 而且都是准确到秒; 然而， TIMESTAMP占的数据空间只有DATETIME的一半， 而且是时区敏感的，同时有一些自动修改的机制。 另一个方面， 它能表示的数据范围要小些， 有些属性在某些时候是比较麻烦的。
　　这里只讨论了一些基本的数据类型， 实际上， mysql为了考虑兼容性， 一些数据类型是其他数据类型的别名而已， 例如，INTEGER, BOOl和NUMERIC, 他们是一样的， 只是名字不同而已; 这些数据类型有时候容易叫人困惑， 但倒是多性能的影响不大。
　　整数
　　数字分为整数和实数， 如果存储整型数，使用正型数类型：TINYINT, SMALLINT, MEDIUMINT, INT, or BIGINT， 分别是8, 16, 24, 32, 和64位， 能存储的数值范围是–2(N–1) to 2(N–1)–1, 其中N是位数。
　　整数有一个可选的属性UNSIGNED， 使用这个属性使得无法表示负数， 而可表示的数值上限大体增加了1倍， 比如， TINYINT UNSIGNED 能存储0 到255之间的值， 而不是TINYINT的–128 到 127.
　　有符号和无符号数使用的数据存数空间是一样的， 性能也差不多， 所以在使用时根据数据范围选择即可。
　　数据类型的选择通常会影响mysql数据在磁盘，内存中的存储， 然而，正型数的计算通常是以64为的BIGINT进行的，即使是在32位机器上。 (一些统计函数是例外的情况，这些函数通常使用DECIMAL和DOUBLE进行计算 )
　　mysql允许在选择整型的时候指定一个宽度，如INT(11), 这对于许多应用来说是没有实际意义的：它并不限制值的范围， 只是简单的用来在交互性工具， 比如命令行上指示数值输出的字符宽度。 从存储和计算的角度来看， INT(1)和INT(20)是一样的。
　　Falcon存储引擎与其他的引起不一样， MySQL AB按照自己的格式来存储整数， 用户多数据的存储大小和格式没有任何控制。一些第三方的存储引擎， 如Brighthouse， 也有自己的存储格式和压缩方法。

实数
　　实数是具有小数部分的数字， 当然， 实数不是专门用来表示小数的， 也可以用DECIMAL来存储那些无法用INTERGER来存储的大整数。 Mysql同时支持准确的和非准确的类型。
　　FLOAT和DOUBLE类型支持近似的标准浮点数数学计算。 如果需要知道准确的浮点数计算， 参考具体平台的浮点数计算规则。
　　DECIMAL类型是用来存储准确的小数， 在mysql 5.0以后的版本， DECIMAL支持准确的数学计算。在Mysql4.1 及以前的版本中， MUMERIC类型也是用浮点计算来完成运算的， 因此可能会出现一些由于精度损失而出现的一些不准确的结果， 在这些版本的mysql中， DECIMAL是一种”存储”概念类型。
　　在5.0以及以后的版本， mysql自己来进行DECIMAl计算， 因为cpu没有对DECIMAL的直接支持。 当然， 浮点计算在某种程度上相对快些， 因为CPU是直接支持浮点计算的。
　　浮点类型和DECIMAl类型允许指定精度。对于DECIMAL字段， 能够指定小数点前和后允许的最多的数字个数， 这个关系到数值的存储空间的消耗。Mysql5.0以及以后的版本中， 所有的数字位数以二进制字符串的方尺存储的(每4个字节9个数字)。 例如， DECIMAL(18， 9) 数据类型的小数点两边都存储9个数字符号， 因此共用9个字节， 4个字节存储小数点之前的数字符号， 1个字节存储小数点本身， 4个字节存储小数点之后的数字符号。
　　在MYSQL 5.0及之后的版本， 一个DECIMAL数字能存储65位数字， 早期的mysql版本有254位数字的限制， 并把数字打包存储成字符串(每个数字一个字节)。 在运算中， 这些版本的mysql一般不需要使用到这么多的空间， 因为DECIMAL只是表示存储格式， 在计算中是通过转换成DOUBLE类型的数字来进行的。有多种方式指定浮点类型字段需要的精确度， 这使得Mysql会悄悄的选择另外一种不同的数据类型或近似值来存储。 这些精确度修饰符不是标准的， 所以建议通过选择数据类型而不是精度。
　　一般情况下， 存储相同范围数值的浮点数类型比DECIMAL类型所使用的空间要小些; 1个FLOAT字段用4个字节的存储，DOUBLE用8个字节的来存储更高精度和更大范围的数值。 其实对于整数类型来说， INTERGER指定的其实是存储类型， mysql其实用的是DOUBLE来进行存储和内部计算的。 因为DECIMAL需要占更多的存储空间和需要更多的计算资源， 因此除非在一些需要准确计算结果的场合–比如存储财务数据外， 尽量不是使用。

字符串类型
　　Mysql支持多种字符串类型的变体。 这些数据类型在4.1和5.0版本中有较大的变化， 这使得这些数据类型变得更加复杂。 从Mysql4.1起， 每个字符串类型能有自己的字符集和针对这些字符集的排序规则集 (更多的关于排序规则的主题， 参考第5章)， 这对于性能有较大的影响。
　　VARCHAR和CHAR类型
　　VARCHAR和CHAR是两种最主要的存储字符串的数据类型， 不幸的是，的确有些困难解释这两种数据类型在磁盘和内存中是如何存储的， 因为这是与存储引擎相关的(比如， Falcon的差不多所有数1是， 请参考相应的存储引擎文档。
　　先看看VARCHAR和CHAR值的典型的磁盘存储方式。 同一种存储引擎对VARCHAR和CHAR在磁盘和内存中的存储格式可能是不一样的， 当从一个地方读取并存到另一个地方时， 服务器可能需要做一些转换工作。 两种数据类型的一些比较：
　　VARCHAR存储可变长度的字符串， 它应该是最为常见的数据类型了。 相对于固定长度的数据类型， 它需要的存储恐空间相对较少， 因为它按需分配存储空间 (比如， 存储短字符串的时候就用少的空间). 例外的地方在MyISAM中建立ROW_FORMAT=FIXED的表， 这种表对于每一行都使用相同数量空间来存储， 因而会导致空间浪费。VARCHAR 使用1或2个字节来记录值的长度: 如果字段的长度小于255个字节， 用1个字节， 否则用2个字节。假定使用latin1字符集， 一个VARCHAR(10)的字段值使用11个字节的存储空间， 1个VARCHAR(1000)的字段值使用1002字节的空间， 因为需要2个字节来存储长度信息。
　　VARCHAR有助于改善性能， 因为能节省空间。 但是， 因为记录的长度是可变的， 当修改的时候因为记录可能变长会导致一些额外的工作。 如果一列增长了而原来的存储空间不能存储修改后的值， 具体的增长策略是与具体的存储引擎相关的。 比如， MyISAM可能导致分行， 而innodb则可能会导致分页来处理。 其他的一些引擎则可能不会在原来的地方修改数据。
　　一般来说， 当某个字段的最大长度远远大于平均长度时; 或者很少修改字段因为碎片化不会是一个问题; 或者使用一个复杂的字符集合比如UTF-8, 而每个字符使用变长的字节来表示时， 使用VARCHAR来存储时值得的。
　　CHAR是固定长度的， Mysql总是分配足够的空间来存储指定数目的字符。当存储一个CHAR值时， Mysql会除去尾随空间(在4.1以及之前的版本， VARCHAR也是这么处理的， VARCHAR和CHAR逻辑上市一样的，只是存储格式不一样而已.) 当比较时， 字段后面会根据需要垫加一些空格来进行比较。
　　当需要存储非常短的字符串时; 或者所有的字段值差不多同样长时， CHAR非常有用。比如， 用CHAR来存储用户密码的md5结果值， 这些结果值是等长的。 当字段值长度变化频繁时， CHAR比VARCHAR要好， 因为固定长度的字段不容易导致碎片化。 对一些字段值很短的字段， CHAR也比VARCHAR高效， 用CHAR(1)来存储Y或者N只需要一个字节长度来存储数据， 而VARCHAR(1)则需要2个字节，因为需要一个字节来表示长度。

 

MySQL有一些存储类型使用一个值中的一些单个的比特位来紧凑的存储数据。纯技术上将，不管是底层的存储格式还是操作，所有这些类型都是字符串类型。
　　BIT
　　MySQL5.0以前， BIT只是TINYINT的同义词而已。但是在MySQL5.0以及之后的版本，BIT是一个完全不同的数据类型了， 有着自己的一些特点， 这里讨论一些新的行为和属性:
　　可以用BIT字段在单列里面来存储一个或多个true/false值， BIT(1)定义一个了只包含单个比特位的字段， BIT(2)是存储2个比特位的字段， 如此类推;BIT字段的最大长度可以是64个比特。
　　BIT类型的行为与存储引擎有关。MyISAM把一些列为了存储的目的打包到一起， 所以17个单独的BIT列需要17个比特来存储(假定这些列都不允许NULL)，MyISAM会近似算成3个字节来存储。其他的一些存储引擎， 比如Memory和InnoDB, 把每个列都用有足够长度存储这些比特位的最小整数来存储，所以无法节省存储空间。
　　* TIMESTAMP类型的一些行为的规则比较复杂并随着不同的MySQL版本而变化，所以在使用时应当确认是自己所期望的行为。通常， 在对TIMESTAMP的列做了改变后通过查看SHOW CREATE TABALE的结果来确认是一个的主意。
　　MySQL把BIT当做字符串类型， 而不是数据类型。当检索BIT(1)列的值， 结果是一个字符串而内容是二进制位0或1， 而不是ASCII值”0″或”1″.然而， 如果在一个数值上下文检索的话， 结果是比特串转化而成的数字。 当需要与另一个值进行比较时， 记住这一点。比如， 如果存储值’00111001′(是57的二进制表示)到一个BIT(8)的字段中然后检索出来，得到的是字符编码值为57字符串， 而这值就是“9”的ASCII编码。但是在数值环境中， 得到的是值57:s
　　mysql> CREATE TABLE bittest(a bit(8));
　　mysql> INSERT INTO bittest VALUES(b’00111001′);
　　mysql> SELECT a, a + 0 FROM bittest;
　　+——+——-+
　　| a | a + 0 |
　　+——+——-+
　　| 9 | 57 |
　　+——+——-+
　　这非常容易引起混淆， 所以我们提醒小心使用BIT类型。对于大多数应用程序来说，避免使用这个类型比较的好。
　　如果想在单个比特位的存储空间中存储true/false值的另一个选择是使用可以为NULL的CHAR(0)列。这个列能够存储NULL和长度为0的空串。
　　SET
　　如果需要存储多个true/false的值， 可以考虑把多个列放到一个MySQL所支持的SET数据类型，而MySQL内部通过一些比特位来表示的。这种类型有效的使用存储空间， MySQL也有一些函数如FIND_IN_SET( )和FIELD( )来方便查询。 最主要的缺点是改变列定义的开销： 需要ALTER TABLE, 而这个操作在一个大表上则是开销非常大的(参考后面有关于替换方法的讨论)。一般来说， 也无法在SET列上使用索引。
　　Bitwise operations on integer columns
　　整型列上的位操作
　　SET类型的一个替代办法是把一个整数当做一些比特位的集合。 比如，可以把一个TINYINT数当做8个比特位， 用位运算来操作其中的比特位， 可以通过在应用程序中位每一个比特位定义命名常数来简化理解。
　　这种方法相对于SET的主要的好处是可以不需要ALTER TABLE就能改变枚举值。缺点是查询写起来麻烦且不容易理解(当第5个比特位为1是什么意思?)。有些人喜欢使用位操作而有些人不喜欢，因而是否使用这个技巧很大程度上是个人口味的问题。
　　把比特位打成包的一个例子应用程序是存储权限的访问控制列表(ACL).每个比特或SET元素表示一个CAN_READ, CAN_WRITE或者CAN_DELETE之类的值。 如果使用SET列， 需要在MySQL的列定义中存储比特到值得映射关系; 如果使用整数列， 则需要在应用中存储这个映射关系。下面是一些使用SET列的查询语句：
　　mysql> CREATE TABLE acl (
　　-> perms SET(‘CAN_READ’, ‘CAN_WRITE’, ‘CAN_DELETE’) NOT NULL
　　-> );
　　mysql> INSERT INTO acl(perms) VALUES (‘CAN_READ,CAN_DELETE’);
　　mysql> SELECT perms FROM acl WHERE FIND_IN_SET(‘CAN_READ’, perms);
　　+———————+
　　| perms |
　　+———————+
　　| CAN_READ,CAN_DELETE |
　　+———————+
　　如果使用整数， 则大概会以如下的方式处理;
　　mysql> SET @CAN_READ := 1 << 0,
　　-> @CAN_WRITE := 1 << 1,
　　-> @CAN_DELETE := 1 << 2;
　　mysql> CREATE TABLE acl (
　　-> perms TINYINT UNSIGNED NOT NULL DEFAULT 0
　　-> );
　　mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
　　mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
　　+——-+
　　| perms |
　　+——-+
　　| 5 |
　　+——-+
　　使用了变量来存储值， 不过在代码中可以使用常数来代替。

 

当存储一个CHAR值时， Mysql会除去尾随空间, 这个行为有点让人困惑， 用一个具体的例子来看一下： 首先 ，创建一个只有一个CHAR(10)字段的表并存储一些值在里面：
　　mysql> CREATE TABLE char_test( char_col CHAR(10));
　　mysql> INSERT INTO char_test(char_col) VALUES
　　-> (’string1′), (‘ string2′), (’string3 ‘);
　　然后检索这些值， 尾部空间被除掉了：
　　mysql> SELECT CONCAT(“‘”, char_col, “‘”) FROM char_test;
　　+—————————-+
　　| CONCAT(“‘”, char_col, “‘”) |
　　+—————————-+
　　| ’string1′ |
　　| ‘ string2′ |
　　| ’string3′ |
　　+—————————-+
　　如果存储同样的值在VARCHA(10)的字段里， 在检索的时候得到的结果如下：
　　mysql> SELECT CONCAT(“‘”, varchar_col, “‘”) FROM varchar_test;
　　+——————————-+
　　| CONCAT(“‘”, varchar_col, “‘”) |
　　+——————————-+
　　| ’string1′ |
　　| ‘ string2′ |
　　| ’string3 ‘ |
　　+——————————-+
　　数据的存储是存储引擎相关的， 并不是所有的存储引擎用同样的方式来处理定长和变长类型。Memory存储引擎使用定长的行， 因而它分配尽可能多的空间给变长字段来存储数据。 而Falcon则使用变长字段来存储， 即使对于CHAR字段也是如此。 但是填充和去尾是一致de 这是因为这个是 油MySql服务器自己处理的。
　　CHAR和VCHAR的兄弟类型分别是存储二进制数据的BINARY和VARBINARY， 二进制字符串与普通的字符长相似， 不过他们是以字节存储的， 而不是字符，填充也不一样 ， Mysql用\0(0 字节)而不是空格填充二进制数据， 而在检索时不会把后面的填充值去掉。
　　对于需要存数二进制数据的活着需要Mysql对值进行字节的比较而不是基于字符的比较时， 二进制数据类型非常哟用。基于字节的比较的优势不只是大小写不敏感的好处， Mysql在比较二进制数据时， 通过字节表示的数值比较的方式进行的， 因为二进制数据相对与基于字符的比较更简单， 也更快。
　　记住长度单位是字符， 而不是字节， 一个多字节字符集要多个字节来存储。

多版本并发控制
　　大部分的MySQL的存储 引擎，比如InnoDB，Falcon，以及PBXT并不是简简单单的使用行锁机制。它们都使用了行锁结合一种提高并发的技术，被称为MVCC(多版本并 发控制)。MVCC并不单单应用在MySQL中，其他的数据库如Oracle,PostgreSQL,以及其他数据库也使用这个技术。
　　MVCC避免了许多需要加锁的情形以及降低消耗。这取决于它实现的方式，它允许非阻塞读取，在写的操作的时候阻塞必要的记录。
　　MVCC保存了某一时刻数据的一个快照。意思就是无论事物运行了多久，它们都能看到一致的数据。也就是说在相同的时间下，不同的事物看相同表的数据是不同的。如果你从来没有这方面的经验，可能说这些有点令人困惑。但是在以后这个会很容易理解和熟悉的。
　　每个存储引擎实现MVCC方式都是不同的。有许多种包含了乐观(optimistic)和悲观(pessimistic)的并发控制。我们用简单的InnoDb的行为来举例说明MVCC工作方式。
　　InnoDB实现MVCC的方法是，它存储了每一行的两个额外的隐藏字段，这两个隐藏字段分别记录了行的创建的时间和删除的时间。在每个事件发生的时 候，每行存储版本号，而不是存储事件实际发生的时间。每次事物的开始这个版本号都会增加。自记录时间开始，每个事物都会保存记录的系统版本号。依照事物的 版本来检查每行的版本号。在事物隔离级别为可重复读的情况下，来看看怎样应用它。
　　SELECT
　　InnoDB检查每行，要确定它符合两个标准。
　　InnoDB必须知道行的版本号，这个行的版本号至少要和事物版本号一样的老。(也就是是说它的版本号可能少于或者和事物版本号相同)。这个既能确定事物开始之前行是存在的，也能确定事物创建或修改了这行。
　　行的删除操作的版本一定是未定义的或者大于事物的版本号。确定了事物开始之前，行没有被删除。
　　符合了以上两点。会返回查询结果。
　　INSERT
　　InnoDB记录了当前新增行的系统版本号。
　　DELETE
　　InnoDB记录的删除行的系统版本号作为行的删除ID。
　　UPDATE
　　InnoDB复制了一行。这个新行的版本号使用了系统版本号。它也把系统版本号作为了删除行的版本。
　　所有其他记录的结果保存是，从未获得锁的查询。这样它们查询的数据就会尽可能的快。要确定查询行要遵循这些标准。缺点是存储引擎要为每一行存储更多的数据，检查行的时候要做更多的处理以及其他内部的一些操作。
　　MVCC只能在可重复读和可提交读的隔离级别下生效。不可提交读不能使用它的原因是不能读取符合事物版本的行版本。它们总是读取最新的行版本。可序列化不能使用MVCC的原因是，它总是要锁定行。
　　下面的表说明了在MySQL中不同锁的模式以及并发级别。

如何避免磁盘临时表
　　因为内存储引擎不支持TEXT和BLOB数据类型， 使用到BLOB和TEXT列的查询和使用隐式临时表的查询将不得不使用MyISAM的磁盘临时表， 即使只有很少的几条记录。 这可能导致很严重的性能开销。即使把MySQL配置成在RAM盘上使用临时表， 也会用到一些开销大的系统调用(Maria存储引擎应该能通过把所有的东西， 而不只是索引，缓存在内存中而在一定程度上缓解这个问题)。最好的解决办法是避免使用BLOB和TEXT类型， 除非费用不可。
　　如果无法避免使用BLOB和TEXT类型， 应该能够通过使用ORDER BY SUBSTRING(column, length)
　　的办法来把这些类型的值转化成字符串，这样不会妨碍使用内存临时表。另外， 确认一下的所使用的子串的长度是否短到不会导致临时表的大小不会超过 max_heap_table_size 或 tmp_table_size， 否则，还是会生成MyISAM的磁盘临时表。
　　如果EXPLAIN结果中的”Extra”列里面包含”Using temporary”， 那么查询会使用一个隐式的临时表。

 

使用ENUM代替字符串类型
　　有时候， 可以通过使用ENUM来代理常规的字符串类型。一个ENUM列能够存储65535个不同的字符串值，MySQL非常紧凑的存储这些值，会根据值列表把这些值存储到1到2个字节中。通过在表的.frm文件中保存一个数字到字符串的对应关系来保存一个”查询表”， 它把每个值存储成一个表示值在字段定义列表中的某个位置的整数。 下面是几个例子;
　　mysql> CREATE TABLE enum_test(
　　-> e ENUM(‘fish’, ‘apple’, ‘dog’) NOT NULL
　　-> );
　　mysql> INSERT INTO enum_test(e) VALUES(‘fish’), (‘dog’), (‘apple’);
　　这3列实际上存储的是整数， 而不是字符串。 能够通过在数字检索上下文中查看到这些值的两面属性：
　　mysql> SELECT e + 0 FROM enum_test;
　　+——-+
　　| e + 0 |
　　+——-+
　　| 1 |
　　| 3 |
　　| 2 |
　　+——-+
　　如果指定数字当做ENUM常量的话， 这个两面性太容易引起混淆了。建议不要这么做。
　　另外一个令人奇怪的是ENUM字段通过整数值来排序， 而不是字符串本省：
　　mysql> SELECT e FROM enum_test ORDER BY e;
　　+——-+
　　| e |
　　+——-+
　　| fish |
　　| apple |
　　| dog |
　　+——-+
　　通过指定期望的ENUM数字的排序顺序来解决这个问题。也可以通过在查询中显式的使用FIELD()来指定排序顺序， 但是这回使得MySQL在排序中无法使用索引:
　　mysql> SELECT e FROM enum_test ORDER BY FIELD(e, ‘apple’, ‘dog’, ‘fish’);
　　+——-+
　　| e |
　　+——-+
　　| apple |
　　| dog |
　　| fish |
　　+——-+
　　ENUM最大的缺点是字符串列表是固定的，增加或者删除一个字符串需要使用ALTER TABLE。因而， 如果列表中允许的字符串值可能会变化的话， 使用ENUM可能不是一个好的主意。MySQL在它自己的权限表中使用了ENUM来表示Y和N.
　　因为MySQL把每个值存储整数， 不得不在需要的时候做一些查询工作来转化成字符串表示，ENUM有一些开销的。这些有可能会通过他们相对较小的大小来缓冲， 但并不总是这样。特别是， 把CHAR或者VARCHAR列并成一个ENUM列比并成CHAR或者VARCHAR列要慢。
　　为了演示一下，我们对MySQL在一个应用中的一个表上做join做性能测试. 这个表有一个很宽的主键：
　　CREATE TABLE webservicecalls (
　　day date NOT NULL,
　　account smallint NOT NULL,
　　service varchar(10) NOT NULL,
　　method varchar(50) NOT NULL,
　　calls int NOT NULL,
　　items int NOT NULL,
　　time float NOT NULL,
　　cost decimal(9,5) NOT NULL,
　　updated datetime,
　　PRIMARY KEY (day, account, service, method)
　　) ENGINE=InnoDB;
　　这个表包含了大概110,000条记录，大小只有10M, 所以能够全部放在内存中。service列包含有5个不同的值， 平均长度是4个字符， method列包含有71个值， 平均长度20个字符。
　　我们对这个表做了一个拷贝， 把service和method字段转化成ENUM类型，如下：
　　CREATE TABLE webservicecalls_enum (
　　… 省略 …
　　service ENUM(…values omitted…) NOT NULL,
　　method ENUM(…values omitted…) NOT NULL,
　　… 省略 …
　　) ENGINE=InnoDB;
　　然后测试联合两个表的主键列来测试性能，使用的查询如下：
　　mysql> SELECT SQL_NO_CACHE COUNT(*)
　　-> FROM webservicecalls
　　-> JOIN webservicecalls USING(day, account, service, method);
　　然后改变一下查询， 以不同的组合来联合VARCHAR和ENUM字段，表3-1中列出了结果。
　　表3-1. 联合VARCHAR和ENUM列的
　　Test Queries per second
　　VARCHAR joined to VARCHAR 2.6
　　VARCHAR joined to ENUM 1.7
　　ENUM joined to VARCHAR 1.8
　　ENUM joined to ENUM 3.5
　　把列转化成ENUM后， 联合速度变快了。但是联合ENUM和VARCHAR列慢一些。在这个例子中， 只要不需要与VARCHAR列进行联合，看起来转化成ENUM是一个不错的注意。
　　而且， 做类型转换有另外一个好处: 从SHOW TABLE STATUS结果着那个的Data_length列的值来看，把这两列换成ENUM后， 表只有原来大小的1/3了。 在某些情况下， 即使需要做与VARCHAR列的联合， 也是有好处的。 在转换后，主键的大小也只有原来的的一半。 因为这是一个InnoDB表， 如果还有其他索引的话，减少主键的大小可以使得索引大大减小。

 

日期和时间类型
　　MySQL有多个表示各种日期和时间值的数据类型， 比如YEAR和DATE. MySQL存储时间的最精确粒度是秒。 然而， 能做微妙粒度的临时计算， 我们会列出一些绕过存储引擎限制的一些方法。
　　许多时间类型都没有可替换类型， 因而不存在什么是最佳选择这么一说。 唯一的问题是当需要一起存储日期和时间时如何做?MySQL提供了两个非常类似的数据类型来做这个事情: DATETIME和TIMESTAMP. 多大多数应用来说， 两个都行， 但是在某些场合， 一个可能比另一个更合适：
　　DATETIME
　　这个类型能够存储很大范围的值， 从1001年到9999年， 准确到秒的精度。它把日期和时间打包成整数以YYYYMMDDHHMMSS 格式表示， 与时区无关。用8个字节的存储空间。
　　缺省情况下， MySQL以可排序，无歧义的格式显示DATETIME值， 比如 2008-01-16 22:37:08, 这是ANSI下标准显示日期时间的方式。
　　TIMESTAMP
　　如名字所指出的，TIMESTAMP 类型存储从1970年1月1日(格林威治时间)到目前为止经过的秒数 –与UNIX时间戳一样。TIMESTAMP 使用4个字节来存储值， 所能表示的范围以比DATETIME要小: 从1970年到2038年。MySQL提供了 FROM_UNIXTIME( ) 和 UNIX_TIMESTAMP( ) 两个函数来进行Unix时间戳与日期类型的相互转换。
　　新版本的MySQL中TIMESTAMP 采用了与DATETIME 一样的格式来表示值，但是老版本的MySQL没有在各个部分件显示分隔符。 这只是一个显示格式的区别而已，TIMESTAMP 的存储格式在各个版本与DATETIME一致。
　　TIMESTAMP 对值的显示与时区相关，MySQL服务器，操作系统和客户端连接都有相应的时区设置。
　　因此，对于与GMT有5个小时时差的东部时间，一个值为0的TIMESTAMP 类型实际显示的内容可能会是1969-12-31 19:00:00。
　　TIMESTAMP 也有一些DATETIME所不具备的一些属性。 缺省情况下， 插入记录时，MySQL会把第一个没指定具体值得TIMESTAMP 类型的字段自动设置为当前值，在修改时， 如果没有在修改语句中显式的指定值， 第一个DATESTAMP类型的字段的值也会被更行为当前值。也可以通过配置修改修改和插入记录时对TIMESTAMP 列的处理行为。最后， TIMESTAMP列缺省不为NULL, 这与其他数据类型不一样。
　　除了一些特殊行为，在一般情况下，如果能够使用TIMESTAMP，就使用它， 因为它比DATETIME的空间效率要高。有时候，有人把Unix时间戳存储成整数， 但是实际上没有任何好处，因为格式转换很不方便，我们不推荐这么做。
　　如果需要存储

Mysql的优化是非常重要的。其他最常用也最需要优化的就是limit。Mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。
　　同样是取10条数据

　　select * from yanxue8_visit limit 10000,10 和

　　select * from yanxue8_visit limit 0,10
　　就不是一个数量级别的。
　　网上也很多关于limit的五条优化准则，都是翻译自Mysql手册，虽然正确但不实用。今天发现一篇文章写了些关于limit优化的，很不错。
　　文中不是直接使用limit，而是首先获取到offset的id然后直接使用limit size来获取数据。根据他的数据，明显要好于直接使用limit。这里我具体使用数据分两种情况进行测试。（测试环境win2033+p4双核 (3GHZ) +4G内存 Mysql 5.0.19）
　　1、offset比较小的时候。
　　select * from yanxue8_visit limit 10,10
　　多次运行，时间保持在0.0004-0.0005之间

　　Select * From yanxue8_visit Where vid ＞=(
　　Select vid From yanxue8_visit Order By vid limit 10,1

　　) limit 10
　　多次运行，时间保持在0.0005-0.0006之间，主要是0.0006

　　结论：偏移offset较小的时候，直接使用limit较优。这个显然是子查询的原因。
　　2、offset大的时候。
　　select * from yanxue8_visit limit 10000,10
　　多次运行，时间保持在0.0187左右

　　Select * From yanxue8_visit Where vid ＞=(
　　Select vid From yanxue8_visit Order By vid limit 10000,1

　　) limit 10
　　多次运行，时间保持在0.0061左右，只有前者的1/3。可以预计offset越大，后者越优。
　　以后要注意改正自己的limit语句，优化一下Mysql了

　　推荐人评论

　　Mysql的优化是非常重要的。其他最常用也最需要优化的就是limit。Mysql的limit给分页带来了极大的方便，但数据量一大的时候，limit的性能就急剧下降。