Monthly Archives: December 2008

用索引扫描来排序 MySQL有2种方法来产生排序的结果：它可以使用filesort，或者他可以依序扫描一个索引。可以通过EXPLAIN命令的type列看到是否出现index。 扫描索引本身是快的，因为它简单要求从一个索引移到另一个索引。实际上，如果MySQL不是使用索引来覆盖查询，它将不得不查询索引中找到的每个行。这基本是随机IO。因此以索引顺序来读取行比顺序扫描一个表要慢，特别是对于IO密集。

覆盖索引 索引是一个找到行的有效方法，但是MySQL也能使用一个索引来获取一个行的数据，因此它不必读一个完整行。索引的叶节点包含它们索引的数据，为什么在读取索引时读取行能给你所需的数据?一个包含（覆盖）查询所需的字段的索引称为覆盖索引。 覆盖索引能称为一个强有力的工具，极大提供性能。考虑读取索引而不是整个行数据带来的好处：

InnoDB和MyISAM数据存储的比较 考虑MyISAM和InnoDB如何对下面表的数据进行布局： CREATE TABLE layout_test ( col1 int NOT NULL, col2 int NOT NULL, PRIMARY KEY(col1), KEY(col2) ); 假定主键值范围在1到10000，随机插入，然后使用OPTIMIZE TABLE进行了优化。换句话说，数据在磁盘上优化组织了，但是行顺序可能是乱的。col2的值随机赋为1到100，因此有很多重复记录。

高性能下的索引策略 列的隔离 如果你没有隔离查询中被索引的列，MySQL通常不能使用列上的索引。隔离意味着列不能是表达式的一部分也不能在查询的函数中。 比如，下面这个查询不能使用actor_id上的查询： mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5; 下面是另外一个错误： mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) < = 10; 查询找到所有的行：满足date_col值新于10天之前。但是它不能使用这索引，因为TO_DAYS()函数。下面是一个较好的查询： mysql> SELECT ... WHERE date_col >= DATE_SUB(CURRENT_DATE, INTERVAL 10 DAY); 但是CURRENT_DATE将会阻止查询缓存。可以将CURRENT_DATE替换为文本串常量。 前缀索引和索引选择性 有时候你需要索引很长的字符列，它使得索引大和慢。你可以通过索引前几个字符而不是整个值来节省空间获得更好性能。这使得你的索引使用较小的空间，但是使得选择性变小。选择性是不同值的总数和总行数的比值，一个较高的选择性索引较好，一个唯一性索引具有选择性为1. … Continue reading →

索引基础 一个索引包含一个特定列的值或者多个列的值。如果你的索引多余1个的列，列顺序是很重要的，因为MySQL只能在索引的最左边上搜索有效。在2个列上创建一个索引和在2个单独列上创建索引是不同的。 索引的类型 索引在存储引擎上实现，而不是服务器层。因此，它们不是标准的。在不同引擎上工作起来轻微有些差别，也不是所有引擎都支持所有的索引。 B-Tree索引 当谈论索引而不谈论类型的时候，它们大概总是B-Tree索引(B+ Tree树)。大多数引擎都支持这种类型。 我们使用”B-Tree”这个说法表示这些索引因为那是在CREATE TABLE和其他语句中MySQL所使用的。实际上，存储引擎使用了不同的内部结构。比如NDB引擎使用T-Tree数据结构，尽管仍然被标记为BTREE。 存储引擎存储BTREE索引以多种方式。比如MyISAM使用前缀压缩技术使得索引变小，但是InnoDB没有压缩，因为压缩后不能进行它的优化。MyISAM索引在引用被索引的行时是通过存储行的物理位置来完成的，但是InnoDB通过它们的主键值来引用它们。使用Btree时所有值将是有序存储，页节点到root节点的距离都是一样的。 叶节点是特殊的，因为它们有指向被索引数据的指针，而不是到其他页的指针（不同的存储引擎有指向数据的不同指针）。 因为Btree以有序方式存储索引过的列，它们非常有利于搜索范围数据。 假定你有下面的查询： CREATE TABLE People ( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m', 'f') not null, key(last_name, first_name, dob) ); 建一个索引，包含last_name, first_name, … Continue reading →

最近要用到MySQL，因此整理一些High Performance MySQL方面用到的东西，以备查看。 Schema的优化 选择合适的数据类型和索引 较小的通常较好 使用能正确存储和表现你数据的最小的数据类型。较小的类型通常较快，因为它们使用较小的磁盘空间、内存和cpu cache，也需要较小的cpu周期来处理。 确定你没有低估你要存储数据的范围，通常，因为增加数据类型的范围是一个很痛苦和耗时的操作。 简单的是好的 对于简单的数据类型处理起来需要较少的cpu周期。比如，整数相对字符比较的代价更低。比如：你应该存储日期和时间以MySQL内置的类型而不是字符串，你应该使用整数来存储IP地址。 尽量避免NULL值 尽可能定义字段为NOT NULL。很多表格有为空值的列，即使当应用不需要存NULL，仅仅只是它是缺省值。你应该小心指定列为NOT NULL除非你确实要存NULL。对于MySQL而言很难优化含有NULL的列，因为它们使得索引，索引统计和值比较更加复杂。一个为空值的列使用更多的存储空间和更多的处理。对于MyISAM，当一个为空值的列被索引时，它要求每个条目需要一个额外的字节甚至引起一个固定长度的索引（比如一个整数列上的索引）转换为变长度。 即使当你需要存储“没有值”，你也不要设置为NULL。考虑使用0，一个特殊值，或者空串。 对于改变NULL列到NOT NULL的性能改变通常是很小的，所以不要对于一个已经存在的schema进行这种转换操作。如果你打算对列进行索引，尽量避免设置列值为NULL。 第一步是要决定对于一个给定的列什么数据类型是恰当的：数字，串，虽然很直接，但是某些情况下还是不是很直观。 下一步是选择特定的类型，很多类型都能存储同样的数据但是范围、精度、需要的空间不一样，比如datetime和timestamp。 整数 有如下一些类型：TINYINT, SMALLINT, MEDIUMINT, INT,和BIGINT。它们需要8，16，24，32，64位存储空间。整数还有unsigned属性，则不允许出现负值。 MySQL让你为整数指定一个宽度，比如int(11)，这对于大多数应用而言是没有意义的：它并不限制值的范围，而只是简单指定了MySQL交互工具用来做显示时指定的宽度。对于存储和计算目的而言，int(11) 等同于int(20)。 字符串类型 MySQL支持多种字符串数据类型，从4.1到5.0改变了很多，更复杂。 Varchar和char 这是2种常见的字符串类型 它们具体是如何存储在磁盘和内存的是特定于存储引擎的。 Varchar存储变长的字符串是最常见的数据类型，逼定长需要更少的存储空间。例外是MyISAM表用ROW_FORMAT=FIXED创建，它对每行使用一个固定长度的空间因此浪费了空间。 Varchar使用1个或者2个额外的字节来记录值的长度：如果列最大长度小于255则用1个字节，如果有更多则用2个字节。假定是latin1字符集，varchar(10)将使用11个字节的存储空间。Varchar(1000)则需要1002字节空间。 Varchar帮助提高了性能因为它节省了空间。实际上，因为行是变长的，它们能在你更新的时候增长，这可能需要额外的工作。如果行增长并且在原先位置没有空间，则它的行为是存储引擎特定的。 当最大列长度比平均长度大得多的时候使用varchar是值得的；如果更新不频繁，因此碎片不是问题。在5.0及以后版本中，MySQL保留结尾空白字符，4.1和更老版本则截掉空白字符。 Char是固定长度：MySQL总是对于指定的字符分配足够的空间。存储char时，MySQL移除任何结尾的空白字符。 如果你要存储很短的字符串，或者几乎所有的值都具有相同的长度，比如char类型适用于md5值，char也适用于需要频繁改变的值，因为固定长度的行不易于出现碎片。对于很短的行，char比varchar更有效。 使用枚举类型取代字符串类型 日期和时间类型 … Continue reading →