一、表设计优化

1.选择一个正确的存储引擎

　　常用的存储引擎 MyISAM 和 InnoDB，每个引擎都各有利弊。

　　①MyISAM：数据库并发不大，读多写少，sql语句比较简单的情况下使用，但是对大量写操作支持不佳。

　　②InnoDB：并发访问大，写操作比较多，有外键、事务等需求的情况下使用，但是占用系统内存较大。

2.为表设置一个主键ID

　　应该为数据库每张表都设置一个ID作为其主键，最好是无符号整型，并设置为自动增长（AUTO INCREMENT），放在数据表的第一顺序。另外在程序中，应该使用表的ID来构造数据。

3.建立固定长度静态表

　　如果表中的所有字段都是“固定长度”类型的，整个表会被认为是 “static” 或 “fixed-length”。 只要包含任何一个可变长度类型的字段，例如：VARCHAR，TEXT，BLOB；那么这个表就不是“固定长度静态表”了。

　　①因为固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。并且，固定长度的表也更容易被缓存和重建。

　　②唯一的缺点是，固定长度的字段会浪费一些空间，因为定长的字段无论你用或不用，他都是要分配那么多的空间。

4.为WHERE字段建立索引

　　· 索引可以改善查询，但会减慢更新，因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。索引不是越多越好，最好不超过字段数的20%，在数据增、删、改比较频繁的表中，索引数量不应超过5个。

　　· 在数据量较少且访问频率不高的情况下不需要建立索引。因为在数据量少的情况下，使用全表扫描效果比走索引更好。

　　索引类型分为3种：普通（normal），唯一（unique），全文本（full text）。

　　普通（normal）：

　　②组合索引：在多个列上创建的索引。一个索引最多可以由15个字段组成。索引的使用顺序只能是从最左侧开始，可以只使用索引中的一部份。

　　　　A.避免建立两个或以上功能相同索引。如果某组合索引中包含某单列索引的字段，则认为是重复的索引。

　　　　B.选择正确的组合索引字段顺序，最常用的查询字段和选择性、区分度较高的字段，应该作为索引的前导字段使用（即放在最左侧）。

　　　　C.组合索引的字段数不适宜较多，较多的组合索引字段数会降低索引查询效率，组合索引字段数应不多于3个，如业务特点需要建立多字段的组合主键例外。

　　③覆盖索引：如果索引列包含SELECT的所有列，该索引就是覆盖索引。查询的处理过程，首先去查询字段对应的索引，然后根据索引区查询正确的数据行。如果是覆盖索引，那么就省去了第二步操作，当然会大大提升查询效率。

　　　　A.并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。

　　　　B.对于覆盖索引查询，使用EXPLAIN时，可以在Extra一列中看到“Using index”，即表示查询用到了覆盖索引。

　　④前缀索引：对索引列的前几个字符（即指定索引的字符长度）建立索引，这样建立起来的索引更小，所以查询更快。

　　　　A.一般字符类型列(varchar,char,text等)，需要进行全字段匹配或者前匹配（也就是='xxx' 或者 LIKE'xxx%'）的时候，会用到前缀索引。

　　　　B.MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引。

　　　　C.指定索引的长度后，就无法实现覆盖索引。

　　唯一（unique）：

　　　　在建立索引的字段所有数值都具有唯一性特点的情况下，建立唯一索引代替普通索引，唯一索引查询效率比普通索引查询效率更高，可以大幅提升查询速度。

5.为WHERE字段设定NOT NULL

　　空值是不占用空间的，但在MySQL中，NULL其实是占用空间的。条件查询时，NULL会参与字段比较，如果索引的字段可以为NULL，索引的效率会下降很多，所以最好为该字段添加NOT NULL的设定

6.使用ENUM类型

　　在做一些固定的选项列表，ENUM类型是一个不错的选择。ENUM类型是非常快和紧凑的，除非enum的个数超过了一定数量，否则他所占的存储空间也总是1字节，也就是说enum所占的存储空间取决于枚举的个数，而并不是枚举索引对应的字符长度，但其外表上显示的是值，增加了数据的可读性。

 7.越小的字段会越快

　　更小的字段类型更小的字符数占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。

　　特：用无符号INT存储IP，而非CHAR(15)

 8.水平分割和垂直分割

　　①水平分割：根据一列或多列数据的值把数据行放到两个独立的表中。

　　水平分割通常在下面的情况下使用:

　　　　A.表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。

　　　　B.表中的数据本来就有独立性，例如表中分别记录各个地区的数据或不同时期的数据，特别是有些数据常用，而另外一些数据不常用。

　　　　C.需要把数据存放到多个介质上

　　②垂直分割：把主键和一些列放到一个表，然后把主键和另外的列放到另一个表中。

　　　　如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直分割，另外垂直分割可以使得数据行变小，一个数据页就能存放更多的数据，在查询时就会减少I/O 次数。其缺点是需要管理冗余列，查询所有数据需要join操作。

二、语句优化

1.避免SELECT *

　　从数据库里读出越多的数据，那么查询就会变得越慢。并且，如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。所以，只查询需要使用的列。

2.避免在数据库中做运算。

　　特别是不要在索引列做运算或者使用函数。

3.避免负向查询和后匹配（LIKE=%xxx）查询。

　　负向查询包括：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE。

　　因为负向查询和后匹配查询会使索引失效，所以在做此类查询时应当注意尽量避免。

4.避免隐式类型转换

　　字符型一定要用单引号''，数字型一定不要加引号。JOIN查询的时候，被用来JOIN的字段，应该也是相同的类型的。如果你要把DECIMAL字段和一个INT字段JOIN在一起，MYSQL就无法使用他们的索引。对于那些字符类型，还需要有相同的字符集才行（两个表的字符集有可能不一样）

5.尽可能使用LIMIT减少返回的行数

　　MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查找下一条符合记录的数据。查询只要一条记录时，使用LIMIT 1和SELECT 1会大大提高效率。

6.小结果集驱动大结果集

　　MySql在5.5版本之前只支持一种表关联方式，就是嵌套循环(Nested Loop)；而在5.5以后的版本中，MySQL通过引入块嵌套循环（Block Nested-Loop）算法来优化嵌套执行的效率。5.6版本及以后，优化器管理参数optimizer_switch中的block_nested_loop参数控制着BNL是否被用于优化器。默认条件下是开启（block_nested_loop=on），若果设置为off，优化器在选择 join方式的时候会选择NLJ算法。

　　嵌套循环（Nest Loop），循环从第一个表中依次读取行，取到每行再到联接的下一个表中循环匹配。这个过程会重复多次直到剩余的表都被联接了。

　　块嵌套循环（Block Nested-Loop），将外层循环的结果集存入join buffer, 内层循环的每一行与整个buffer中的记录做比较，从而减少内层循环的次数。

　　因为NLJ一次只将一行传入内层循环, 所以外层循环的结果集有多少行, 内存循环便要执行多少次。如果内部表有很多记录，则NLJ会扫描内部表很多次，执行效率非常差。JOIN查询的时候，应尽可能减少JOIN中Nested Loop的循环次数，以此保证遵循小结果集驱动大结果集的原则。EXPLAIN 结果中，第一行出现的表就是驱动表，所以如果该表是记录行数最少的表，那么此查询就遵循了该原则。

随机抽取千万不要ORDER BY RAND()

所有的SQL关键词用大写，养成良好的习惯，避免SQL语句重复编译造成系统资源的浪费。

开启慢查询

定期用explain优化慢查询中的SQL语句。记住，explain 是一种美德！如果你看到以下现象，请优化：

• 出现了Using temporary；
• rows过多，或者几乎是全表的记录数；
• key 是 (NULL)；
• possible_keys 出现过多（待选）索引。