优化数据库结构

MySQL将行数据和索引数据保存在不同的文件中。许多(几乎所有)其它数据库将行数据和索引数据混合保存在用一个文件中。 1.使你的数据尽可能小 最基本的优化之一是使表在磁盘上占据的空间尽可能小。这能给出巨大的改进，因为磁盘读入较快，并且在查询执行过程中小表的内容被处理时占用较少的主存储器。如果在更小的列上做索引，索引也占据较少的资源。 MySQL支持许多不同的存储引擎(表类型)和行格式。对于每个表，可以确定使用哪个存储引擎和索引方法。为应用程序选择合适的表格式可以大大提高性能。 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内存的专业化类型。 尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好一些，因为MEDIUMINT列使用的空间要少25% 如果可能，声明列为NOT NULL。它使任何事情更快而且每列可以节省一位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免 默认地在所有列上有它。 对于MyISAM表，如果没有任何变长列(VARCHAR、TEXT或BLOB列)，使用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间 在MySQL/InnoDB中，InnoDB表使用更紧凑的存储格式。 每张表的主索引应该尽可能短。这使一行的识别容易而有效。 只创建你确实需要的索引。索引对检索有好处，但是当你需要快速存储东西时就变得糟糕。如果主要通过搜索列的组合来存取一个表，对它们做一个索引。第一个索引部分应该是最常用的列。如果从表中选择时总是使用许多列，应该首先以更多的副本使用列以获得更好的索引压缩。 如果很可能一个索引在头几个字符上有唯一的前缀，仅仅索引该前缀比较好。MySQL支持对一个字符列的最左边部分创建一个索引(参见13.1.4节，“CREATE INDEX语法”)。更短的索引会更快，不仅因为它们占较少的磁盘空间，而且因为它们将在索引缓存中提供更多的访问，因此磁盘搜索更少。 在一些情形下，将一个经常被扫描的表分割为2个表是有益的。特别是如果它是一个动态格式的表，并且可能使用一个扫描表时能用来找出相关行的较小静态格式的表。 2.列索引 所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。大多数存储引擎有更高的限制。 在索引定义中用col_name(length)语法，你可以创建一个只使用CHAR或VARCHAR列的第1个length字符的索引。按这种方式只索引列值的前缀可以使索引文件小得多。 3.多列索引 name索引是一个对last_name和first_name的索引。索引可以用于为last_name，或者为last_name和first_name在已知范围内指定值的查询。 4.mysql如何使用索引 索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。 如果一个表有1000行，这比顺序读取至少快100倍。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜索。 大多数MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B树中存储。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引 字符串自动地压缩前缀和结尾空格 索引用于下面的操作: 快速找出匹配一个WHERE子句的行 删除行。如果可以在多个索引中进行选择，MySQL通常使用找到最少行的索引 当执行联接时，从其它表检索行 对具体有索引的列key_col找出MAX()或MIN()值 如果对一个可用关键字的最左面的前缀进行了排序或分组(例如，ORDER BY key_part_1,key_part_2)， 排序或分组一个表。如果所有关键字元素后面有DESC，关键字以倒序被读取。 在一些情况中，可以对一个查询进行优化以便不用查询数据行即可以检索值。 如果查询只使用来自某个表的数字型并且构成某些关键字的最左面前缀的列，为了更快，可以从索引树检索出值 如果表有一个多列索引，优化器可以使用最左面的索引前缀来找出行。 例如，如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)和(col1,col2,col3)上的搜索进行了索引。 如果使用... LIKE '%string%'并且string超过3个字符，MySQL使用Turbo Boyer-Moore算法初始化字符串的模式然后使用该模式来更快地进行搜索。 如果col_name被索引，使用col_name IS NULL的搜索将使用索引。 任何不跨越WHERE子句中的所有AND级的索引不用于优化查询。换句话说，为了能够使用索引，必须在每个AND组中使用索引前缀 有index(index1,index2,index3) 在where index1=xx and index3=xx时其实只使用到了index1，在where index2=xx and index3=xx中不能使用索引 在where index1=xx or a=10未使用索引，在where index1=xx or index2=xx也未使用索引 有时MySQL不使用索引，即使有可用的索引。一种情形是当优化器估计到使用索引将需要MySQL访问表中的大部分行时。(在这种情况下，表扫描可能会更快些，因为需要的搜索要少）。 然而，如果此类查询使用LIMIT只搜索部分行，MySQL则使用索引，因为它可以更快地找到几行并在结果中返回。 Hash索引 它们只用于使用=或<=>操作符的等式比较(但很快)。它们用于比较 操作符，例如发现范围值的< 优化器不能使用hash索引来加速ORDER BY操作。(该类索引不能用来按顺序搜索下一个条目） MySQL不能确定在两个值之间大约有多少行(这被范围优化器用来确定使用哪个索引) 只能使用整个关键字来搜索一行。(用B-树索引，任何关键字的最左面的前缀可用来找到行）