主键优化

主键可以是表的一个列或者是多个列，有一个关联的索引。利用主键查询是非常快的。主键查询对于not null的优化是非常棒的，因为主键不允许有null值。在InnoDB存储引擎中，一张表的数据基于主键存储。

如果你的表非常大而且特重要，但却没有一个有明显特征的列或者列的集合作为主键。你需要单独创建一个自增列（auto-increment ）作为主键。

那些唯一键可以作为join表时的外键使用。

外键优化

如果一张表有一些字段，你常用来组合查询一些数据，这时候应该将那些低频的字段单独得地拆分为一张表，并在主表中使用外键与之关联。这样，每张小表会拥有自己的主键，可以快速的查询它自己的数据，也可以通过join操作来查询其他需要的字段。查询的IO和耗费的内存，最终取决于数据是如何分布的，因为在磁盘中相关的字段是被打包在一起存储的。通常情况下，查询是尽可能少地从磁盘上读取数据块，而那些拥有较少字段的数据表在一个数据块中可以存储更多的记录。

列索引优化

最常用的列索引是单字段索引，通过存储指定字段的值的副本，可以通过给定的字段的值快速查到匹配的记录。在where子句中可以使用 =， >,  <=,  between,  in等操作符，B-tree数据结构可以使得索引可以快速找到一个指定的结果集。 

每张表支持的最多索引数和每个索引的最大长度取决于每个存储引擎，一般来说，每个存储引擎都支持至少16个索引和每个索引的最大长度至少在256个字节。

索引前缀

使用col_name(N)为一个字符串类型字段创建索引时，可以创建一个只包含该字段前N个字符的索引。这样可以使得索引文件很小。但是为BLOB 或者 TEXT这种类型的字段创建索引时，必须指定索引前缀的长度。

索引前缀长度能够被设置为1000字节（在InnoDB中是767个字节，可以使用 innodb_large_prefix 设置）。

PS:索引的前缀长度使用字节（byte）衡量。而在 CREATE TABLE, ALTER TABLE, CREATE INDEX语句中前缀的长度另有解释，对于非二进制数据是指字符（characters）的长度，对于二进制的数据是指字节（byte）的长度。

多列索引(组合索引)

MySQL支持组合索引，一个组合索引最多可以包含16个字段。也可以对于char、varchar、text等数据类型使用索引前缀。

可以使用组合索引的前一个字段、前两个字段、前三个字段或者是前N个字段来优化查询。也可以在创建索引的时候指定其从右边开始索引。一个组合索引能够支持多种查询方式。组合索引可以被看做是一个有序数组，该数组元素可以被看做是连接起来的被索引的列。

ps: 组合索引的一个替代方案，创建一个新列用来保存其他列的hash值，如果这列较短、值比较唯一，且被索引，那较组合索引的那种‘宽索引’的查询较快。

例如：

SELECT * FROM tbl_name
WHERE hash_col=MD5(CONCAT(val1,val2))
AND col1=val1 AND col2=val2;

组合索引是按照索引的最左前缀匹配规则进行查询的。如果你有一个组合索引(col1, col2, col3)，那么这个索引可以支持的查询有(col1), (col1, col2), and (col1, col2, col3)。如下所示的select语句将不会命中该索引。

SELECT * FROM tbl_name WHERE col2=val2;SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;