《高性能MySQL》读书笔记和解惑

MySQL数据类型的简单优化原则：

1、更小通常更好：一般来说，要试着使用能正确地存储和表示数据的最小类型。如果不确定需要什么数据类型，就选择你认为不会超出范围的最小类型。最好的策略就是只分配真正需要的空间。

2、简单就好：越简单的数据类型，需要的CPU周期就越少。

3、尽量避免NULL：要尽可能地把字段(Field)定义为NOT NULL。除非真的要保存NULL，否则就把列定义为NOT NULL。把NULL列改为NOT NULL带来的性能提升很小。

 

特定类型：DateTime和TimeStamp能保存同样的数据类型：日期和时间，精度为秒。TimeStamp使用的空间只有DateTime的一半，还能保存时区，拥有特殊的自动更新处理能力。但如果插入的行没有定义TimeStamp列的值，MySQL会把它设置为当前时间，更新的时候如果没有显式地定义，MySQL也会自动更新它。ALTER数据库时，如果新增一个列类型为TimeStamp，它会自动填充“00-00-00 00:00:00”，这样的时间值C#无法正常地转换。

 

 索引

MySQL中也叫键“Key”，在数据量越大的时候越重要。索引包含了来自于表中某一列的多个列的值。如果索引了多列数据，那么列的顺序非常重要，因为MySQL只能高效地搜索索引的最左前缀(Leftmost Prefix)。创建一个双列索引和两个单列索引是不一样的。

 

 

最左前缀：在创建一个N列的索引时，实际是创建了MySQL可利用的N个索引。多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行。这样的列集称为最左前缀。（这与索引一个列的前缀不同，索引一个列的前缀是利用该的前N个字符作为索引值。）

 

假如一个表在分别名为s t a t e、city 和zip 的三个列上有一个索引。索引中的行是按state/city/zip 的次序存放的，因此，索引中的行也会自动按state/city 的顺序和state 的顺序存放。这表示，即使在查询中只指定state 值或只指定state 和city 的值，MySQL也可以利用索引。因此，此索引可用来搜索下列的列组合：
  state,city,zip
  state,city
  sate

MySQL不能使用不涉及左前缀的搜索。例如，如果按city 或zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个zip 代码（索引中的列1和列3），则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。

 

 

选择索引：搜索的索引列，不一定是所要选择的列。换句话说，最适合索引的列是出现在WHERE 子句中的列，或连接子句中指定的列，而不是出现在SELECT 关键字后的选择列表中的列，如：SELECT col_a FROM Table1 LEFT JOIN Table2 ON Table1.col_b = Table2.col_c WHERE Table1.col_d = EXPR，col_a是不适合做索引的列，col_b、col_c、col_d都是适合做索引的列。当然，所选择的列和用于WHERE 子句的列也可能是相同的。关键是，列出现在选择列表中不是该列应该索引的标志。出现在连接子句中的列或出现在形如col1= col2 的表达式中的列是很适合索引的列。查询中的col_b 和col_c 就是这样的例子。如果MySQL能利用连接列来优化一个查询，表示它通过消除全表扫描相当可观地减少了表行的组合。

 

 

使用惟一索引：考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记 录性别的列，只含有“ M”和“F”，则对此列进行索引没有多大用处（不管搜索哪个值，都会得出大约一半的行）。

 

使用短索引：如果对串列进行索引，应该指定一个前缀长度，只要有可能就应该这样做。例如，如果有一个CHAR(200) 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。对前10 个或20 个字符进行索引能够节省大量索引空间，也可能会使查询更快。较小的索引涉及的磁盘I/O 较少，较短的值比较起来更快。更为重要的是，对于较短的键值，索引高速缓存中的块能容纳更多的键值，因此，MySQL也可以在内存中容纳更多的值。这增加 了找到行而不用读取索引中较多块的可能性。（当然，应该利用一些常识。如仅用列值的第一个字符进行索引是不可能有多大好处的，因为这个索引中不会有许多不 同的值。

 

考虑在列上进行的比较类型：索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 运算。在模式具有一个直接量前缀时，索引也用于LIKE 运算。如果只将某个列用于其他类型的运算时（如STRCMP( )），对其进行索引没有价值。

 

 不要过度索引：不要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修 改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速 度。此外，MySQL在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果 是，则就不要费力去增加这个索引了，因为已经有了。

 

部分内容引自：http://space.itpub.net/47598/viewspace-227910