xiayuanfeng的专栏

 一个查询执行的不是很理想，大部分原因都是由于数据量过大。很多查询都筛选了大量的数据并且并没有什么作用。其实大部分不好的语句都可以访问更少的数据。我们可以通过两个步骤来分析性能差的查询语句。  找出你的应用程序所获取的数据是否超出了你的需求。意思就是它访问了过多的数据，但是它也可能访问了过多的列。找出MySQL服务器是否分析了过多的行。从数据库获得的数据是否超出了你...

全文索引FULLTEXT是MyISAM表的一个特殊的索引类型。它在文本中找到一个关键词，而并不是在索引中直接比较着两个值。Full-text搜索不同于其他类型的匹配。它有许多细微的差别。比如stopwords，stemming,plurals以及Boolean searching。这些基本都和搜索引擎相关了。 给一个列加全文索引并不能消除B-Tree索引的值。全文索引是匹配而不是W...

空间（R-TREE）索引MyISAM支持空间索引。你可以把它和地图类型一起使用。比如GEOMETRY。不像B-Tree索引，它并不要WHERE条件来操作左边前缀的索引。它通过所有的维度来索引数据。因此，查找可以有效地使用任意维度的组合。然而你必须要使用MySQL GIS函数。比如MBRCONTAINS( )。...

Hash索引Hash索引是建立在Hash table至上的，并且它只对准确查找有效，也就是说，必须查找索引上的每一个列。对于每个行，存储引擎计算了索引列的Hash code。这个hash code是非常小，并且对于其他行不同的数值，这些Hash code也是不相同的。在索引中存储了hash code并且在hash table中存储了一个指针指向每一行。 在MySQL中，只有Memory...

索引的类型索引有许多类型，它们都是针对不同的用途而被设计的。索引是在存储引擎中实现的，而不是服务器。因此它们并没有标准化：在每个引擎中，索引工作的方式都有所不同，并且并不是所有的存储引擎支持所有种类的索引。即使这些存储引擎支持索引的类型相同，在底层的实现也是有所不同。 让我们来看看MySQL所支持的索引类型，它们的优势和缺点。 B-Tree索引当人们讨论索引而没有说明它的类...

索引是数据结构。它能帮助MySQL更有效率的获取数据。它对于好的性能是决定性的因素，但是人们常常忘记使用它们或者没有正确的理解它们，因此在现实的例子中，索引总是引起性能问题。这就是我们把索引放在这本书的较早部分的原因。甚至把索引这章放在语句优化之前。 索引（在MySQL中也叫做键（keys））在数据越来越大的时候，变的格外重要。数据量小，没有高的负载的情况下，在没有索引的情况下，数据库也可...

特殊类型的数据一些特殊类型的数据可能并不直接的和MySQL内置类型相吻合。一个例子就是用STAMPTIME存储更精确的时间。另一个例子是IP地址。人们常常使用VARCHAR(15)来存储IP地址。然而，一个IP地址其实是一个无符号的32bit的整型，并不是一个字符串。IP地址的点儿仅仅是为了方便人们去读取IP地址而已。你应该用无符号整型去存储IP地址。MySQL提供了INET_ATON(...

对于主键，选择一个好的数据类型尤为关键。你可能经常需要用这些列和其他做比较以及用这些列查找其他的列。你可能也把它们作为另一些表的外键。因此当你选择主键的数据类型时，应该保持相关表主键类型一致。 当选择主键的数据类型，你不仅要考虑存储类型，也要考虑MySQL操作和比较这些类型的表现情况。比如，MySQL内部存储ENUM和SET是作为整型的，但是当在字符串环境下作比较的时候，MySQL会把它们...

MySQL有很多使用单独的bit去存储数据的类型。不管底层的存储格式以及操作，从技术上来看所有的这些类型都是字符串类型。 BIT在5.0之前的版本，BIT仅仅等同于TINYINT。但是在5.0之后的版本，它已经是个具有一些特性的，和以前完全不同的数据类型了。我们在这讨论的是新增加的特性。 你可以使用BIT列存储一个或多个true/false值。BIT(1)定义了一个包含1个bi...

日期和时间类型 MySQL对于不同种类的日期和时间有很多的数据类型，比如YEAR和DATE。MySQL可保存时间颗粒度是一秒。然而，时间的计算可以精确到毫秒。我们将会教你如何解决存储引擎的局限性。 大部分时间类型都没有什么可选择的。所以那个是最佳的选择不是一个问题。只有一个问题那就是当你即要保存日期也要保存时间的时候，将要做什么。MySQL提供了两个类型来满足这个需求:DATETI...

字符串类型MySQL支持很多字符串类型，以及它们的许多变化类型。这些数据类型在4.1和5.0版本变化都比较大。可以说变得更复杂了。早在4.1版本中，每个字符串列都有自己的字符集和对于那些字符集的排序规则，或者叫做collation（校对）。 VARCHAR和CHAR有两个主要的字符串类型就是VARCHAR和CHAR。它们都存储了字符值。不幸的是，很难准确解释这些值怎样存储在...

整数总共有两种数字类型：整数和实数（有小数部分的）。如果你保存的是整数，使用下列的整型：TINYINT,SMALLINT,MEDIUMINT,INT或者BIGINT。它们分别需要8,16,24,32,以及64位的存储空间。它们存储的值范围为N就是它们使用的存储空间所需的位数。 整型有个一个可选择的参数，就是UNSIGNED，它不允许存储负数，而正数的最大上限变为之前的大约两倍左右。...

 MySQL支持大量的数据类型，以及选择存储数据正确的类型对于获取好的性能是决定性的。说一下简单的指导准侧会帮助你有个更好的选择，而不管它们的数据类型。 越小也就是越好的      一般来说，尽量选择小的数据类型足以符合你的存储和展现数据。越小的数据类型也常常是越快的，因为它们使用了较       少的硬盘空间，内存，CPU缓存。它们也需要更少的CPU处理周期。      ...

注：Schema：在数据库系统中，数据库管理系统（DBMS）用自身所支持的语言来描述的结构。在关系数据库中，schema定义了表，每个表的字段，以及字段与表之间的关系。在文中，Schema将不翻译为中文。 前言优化一个设计不当或者错误使用索引的Schema,可以数量级的提高性能。如果需要高性能，你必须对于将要执行的具体语句设计Schema和索引。你也应该对于不同种类的语句进行性能的...

创建正确的索引和正确的使用它们，是获得好的查询性能的关键所在。我们已经介绍了不同种类的索引并且探究了它们的优缺点。现在让我们来看看怎样深入的使用强大的索引。 有许多高效的选择和使用索引的方法，因为有很多特殊情况的优化以及一些专门的行为。决定什么时候使用并且评估你的选择对性能所造成的潜在影响，这些也是我们将会学的技能。接下来的部门会教你怎样高效的使用索引，但是千万不要忘了进行基准测试。...

如果你在一个查询中不隔离已经索引的列，一般来说MySQL就不会使用这个列上的索引，除非在查询中隔离这个列。“隔离”列的意思就是，这个列不能使一个表达式的一部分或者被放在一个函数的内部。 举个例子，在这个查询中，你不能使用在actor_id上的索引。mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5; ...

有的时候你可能需要索引字符数非常多的列。这会让你的索引非常大并且很慢。一个策略就是模拟hash索引。前一部分已经说过了。但是有的时候这种方法也不是很好。将要怎么做。 一般可以用索引前一部分的一些字符的方法来节省空间并且可以获得好的性能。这样可以使你的索引使用更少的空间，但是这样会降低选择性。索引的选择性（Index selectivity）是索引值的数量和表中行的数量（#T）的一个比率。范...

上一章，我们解释了怎样优化schema.这是高性能的一个必要条件。但是仅仅考虑Schema也是不够的-你也需要设计你的查询。如果你的查询很差，即使Schema设计完美，也没什么用。 查询优化，索引优化，和schema优化应该一起齐头并进的。随着你在MySQL中获得了优化查询的经验，你也会明白怎样设计Schema来支持你这些高效的查询。同样的，你对Schema设计的了解也会影响你所写查询的类...

这一章的结束，我们来说一下关于设计模型的存储引擎的选择，这些你应该牢记。我们不会全面的介绍存储引擎，目标就是列出一些影响数据模型设计的一些关键性因素。 MyISAM存储引擎表锁（Table locks）MyISAM是表一级的锁。小心地是这个不会成为一个瓶颈。 没有自动数据恢复（No automated data recovery）如果MySQL服务器挂了或者电源关闭。你...

当对于一个大表进行ALTER TABLE的时候，性能问题就产生了。MySQL大部分改动的步骤如下：根据新的表结构创建一个空表，从旧表中把数据取出来插入到新表中，在删除旧表。这是个非常漫长的过程。许多人ALTER TABLE之后，都有等待1小时或者1天的痛苦经历。 MySQL AB已经开始提升这方面的性能了。一些即将到来的特性是支持"在线"的操作，而不会去锁定表。InnoDB的开发者也在积极...

有很多方法来展现给定的数据。从完全范式到完全的非范式以及介于两者之间。在符合范式的数据库中，每个事实展现一次并且仅仅展现一次而已。相反的，在非范式的数据库，信息重复或者存储在很多的地方。 如果你不熟悉范式，你应该加强学习了。关于范式，你可以通过一些书和网上资源来学习。在这里，我们主要介绍这一章中你应该明白的知识。让我们看看经典的例子，那就是employee,departments,和dep...

当你已经创建了一张表，有合适的数据类型，并添加了索引之后，其实你的工作还并没有结束：你还需要维护你的表和索引使它们工作的更好。表的维护有三个主要的目标：发现和解决表的损坏，维护准确的索引统计，并且要降低存储碎片。 找到和修复损坏的表最差的事情莫过于表已经损坏了。对于MyISAM，大部分是由于当机所造成的。然而，所有的存储引擎都会由于硬件问题或者MySQL内部BUG再或者操作系统的原因导...

用例子来理解索引的概念是再简单不过的方法了。因此我们研究一个索引示例。 假使我们要做一个在线约会的网站。用户的资料就会有很多列，比如country, state/region, city, sex, age, eye color等等。这个站点必须支持这些属性的组合来查询用户资料。也必须支持让用户通过用户最近的在线时间，其他会员的打分等等来排序和限制结果。对于这么个复杂的需求我们怎样来设计索...

 在语句生命周期的下一步就是把一个SQL查询放入一个可执行的计划中。这个步骤有许多子步骤：解析，预处理并且优化。在这个过程中任意一点抛出错误（比如语法错误）。在这我们不会列出MySQL内部机制。因此我们可以更自由的说一些别的，如单独的描述其中的一些步骤，虽然它们可能有效地全部或者部分的组合在一起。我们的目标就是帮助你理解MySQL执行语句的过程，这样能写出更好的语句。 解析和预处理...

InnoDB中，索引所扮演的角色是非常重要的。因为它们可以能让语句锁定更少的行。这是个要考虑的事情，因为在MySQL5.0 InnoDB中一个事物提前之前，是不会释放锁的。 如果查询语句不会检索它们不需要的行。它们将锁定更少的行。并且对于性能有提高，原因有二：首先，即使InnoDB行锁是非常有效率的并且使用更少的内存，但是行锁也会消耗一定的资源。其次，锁定很多的行就提高了锁的竞争并且降低了...

MySQL可以在一个列上创建多个索引；这么做并不会提醒和防止发生错误。MySQL必须单独维护每一个重复的索引，当语句优化器优化语句的时候，它会参考这些索引。这样会对性能造成影响。 重复的索引是那些具有相同类型，在同顺序下的相同一组索引。你应该避免这样来创建它们并且如果发现它们要尽早的删除。 有的时候你可能在不知道的情况下就创建了重复索引。比如，如下的代码CREATE TA...

MyISAM使用前缀压缩来降低索引的大小，这样就可以把更多的索引放到内存中并且在某些情况下可以大幅度提升性能。默认的是压缩字符串，但是你可以指定它来压缩整型的值。 MyISAM通过存储块的第一个值来压缩每个索引块，之后通过记录相同前缀的字节数在加上不同后缀实际的数据的方式来存储在块中的每个附加的值。举个例子，如果第一个值是“perform”并且第二个值是“performance”，第二个值...

MySQL有两种方法生成有序的结果：使用文件排序或者按顺序的扫描索引。你可以使用EXPLAIN来查看type列是否是index来得知MySQL是否计划的扫描索引。 扫描索引本身是非常快速的，因为它只是简单的需要从一个索引实体移动到下一个。然而，如果MySQL没有使用索引覆盖这个查询，它就会查找在索引中发现的每一行。这是个随机IO的过程，因此从索引顺序中读取数据比连续表的扫描要慢很多。 ...

索引是高效找到行的一个方法，但是MySQL也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引（covering indexex） 覆盖索引是非常强大的工具并且可以大幅度提升性能。考虑下仅仅读取索引的好处： 索引的实体往往小于整...

聚簇索引并不是一个独立的索引类型。确切的说它们是存储数据的一个途径。在不同实现之间，还是有一些细节上的变化，但是InnoDB的聚簇索引实际是在相同的结构中把B-TREE索引和这些行一并的存储。 当一个表中有聚簇索引，它的行实际存储在索引的叶子的页（leaf pages）。“clustered”所指的意思是行的相邻键值彼此存储的非常近。你可能每张表只会有一个聚簇索引，因为你不能同时的在两个地...

操作系统的剖析查看操作系统统计信息是非常有用的，这样可以发现操作系统和硬件的工作情况。它不仅仅能帮助剖析整个应用，也可以帮助解决修复一些问题。 这部分的操作系统主要是类UNIX的系统，因为大部分服务器都是用它们。然而，你也可以使用其中的一些技术用于其他的操作系统，只要它们都提供信息就行。 我们经常使用的工具是vmstat,iostat,mpstat以及strace.这些工具在结...

当不能添加剖析代码的时候。有的时候你不能添加剖析代码，对服务器打补丁或者修改服务器配置。然而，有一种方法至少能进行一些剖析。试试如下:定制你的服务器日志，它们能记录时钟时间和每个请求的CPU时间。使用嗅探工具(Packet Sniffers)，去捕获和对语句进行计时（包括了网络延迟时间）。可用的工具是mysqlsniffer (http://hackmysql.com/mysq...

其它剖析MySQL的方法我们已经教你怎样使用MySQL内部状态信息去查看服务器内部情况。你也能对MySQL其他状态进行一些剖析。其他一些有用的命令如，SHOW INNODB STATUS和SHOW MUTEX STATUS.将在以后的章节进行讨论。...

当开始优化有问题的查询语句的时候，你的目标是找到一个合适的方法来得到期望的结果-但是并不是一定要从MySQL返回相同的结果集。有的时候可以把查询语句转换成另一种方式来获得更好的性能。如果那样优化带来了效果的提升,你也应该考虑重写查询来获得不同的结果。最终，你可能通过修改应用代码来完成和修改查询语句相同的工作。这一部分，我们所讲述的技术是，帮助你重新构建一个查询范围广的语句，并且告诉你什么时候应该来...

表的转换有许多方法可以把表从一个引擎转换为另一个。每种多有优点和缺点。在这节中，将介绍三种方法。 ALTER TABLEALTER TABLE是最简单的转换引擎的方法。下列语句是把mytable表转换为Falcon引擎：mysql> ALTER TABLE mytable ENGINE = Falcon; 这个语法适用于所有存储引擎，但是有点要注意：这种方式会消...

存储引擎总结下面的图表总结了MySQL流行的存储引擎的事物和锁相关的特性。MySQL版本的那一列给出了要使用引擎的最低版本，对于一些引擎和MySQL版本你可能要自己编译服务器。这一列All值的意思就是高于MYSQL3.23的所有版本。  存储引擎MySQL版本事物锁的颗粒度关键的应用禁忌     MyISAMAllNo表，并发插入SE...

转载请标明出处谢谢合作：http://xiayuanfeng.iteye.com/blog/402250 如果没有一些实际的例子，上节所说的选择存储引擎可能很空洞。来让我们看下一普通的应用程序。接下来我们会看到各种各样的表以及选择哪个引擎适合它们。下一节会做个存储引擎的总结。 Logging(日志)假如你想用MySQL实时记录电话交换机中每个电话的通话记录。还有你在APACHE...

选择合适的引擎当设计基于MySQL的应用程序时，要考虑用什么存储引擎来保存数据。如果在设计阶段不考虑这个，你就会在以后的过程中遇到一些困难。你可能发现默认的存储引擎并没有提供一些你需要的功能。如事务管理或者读写的混合需要比MyISAM表锁更好的隔离级别的锁。 因为你可以为每个表都设置存储引擎。你必须清晰的知道每张表的用途和存储的数据。考虑这些也可以帮助你对应用有个更好的了解以及潜在的增...

MEMORY (HEAP)引擎 当你需要快速读取数据，在服务器重启后，也不需要改变和保存数据，使用Memory表（以前叫做HEAP 表）是非常合适的。Memory表要比MyISAM表快上一个级别。所有的数据都放在内存中，所以查询不用等待硬盘的I/O。在服务器重启后，表结构依然存在，但是数据都会丢失。 下面说几个可以用到Memory表的情况。 对于查找或映射表比较适合，如...

InnoDBInnoDB支持事物处理，它处理许多短期的事物，这些事物常常是成功执行而不是回滚的。InnoDB是MySQL最受欢迎的存储引擎了。常见的非事物引擎需求，如性能和自动回复，它也完全具备。 InnoDB在一些列的文件中存储数据，被称为表空间(tablespace)。表空间对于用户来说是黑盒，InnoDB内部负责处理。在MySQL4.1以及更新的版本中，InnoDB可以存储数据以...