MySQL常规优化

建表优化

1. 建索引
所有查询量大的字段都建索引

2. 字段最小
所有的字段选择最小的数据类型，如id可以使用mediumint,比int节省25%的空间

3. 字段定长
字符串长度固定则使用char类型，如密码字段MD5加密则使用char(32)

4. 使用 tinyint 而不是 enum
某些字段的取值是有限而且几乎固定的，比如性别，这个时候应该使用tinyint。1.在MySQL内部enum保存的是 tinyint，这个varchar到tinyint的映射操作会影响数据库性能，而tinyint的映射操作在程序中处理。2.某些特殊情况会增加或修改字段的取值，enum需要去修改数据库字段，而tinyint只需要修改程序

5. NOT NULL、UNSIGNED
尽量所有的字段都设置为NOT NULL（还附带了个不为空的验证），数值数据类型（如int）尽量设置UNSIGNED无符号，这样能让速度更快

6. 在Join表的时候使用相同类型的字段，并建立索引
如order表的id和order_product的order_id，需要保证id、order_id的字段类型一致，例如都为int。如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行

7. IP地址存成 UNSIGNED INT
很多程序员都会创建一个 VARCHAR(15) 字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP between ip1 and ip2。

8. 垂直分表

• “垂直分表”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。比如很多cms都会把文章表分2个表

• 示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？

• 示例二： 你有一个叫 “last_login”的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中。

• 另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分表时还要差，而且，会是极数级的下降。

9. MRG_MyISAM水平分表

• 建立多个相同结构的myisam表，再建一个mrg_myisam总表，关联所有分表。
• mrg_myisam是MySQL提供一个可以将多个结构相同的myisam表，合并到一起的存储引擎。
• 适合并发量大的myisam表。由于分表后每张表的数据量减少，每条SQL语句执行的时间减少，更利于高并发。
• 不需要提前设计好，有了数据也能分表

10. 应用层面水平分表

• 利用PHP程序（或其他语言）自定义分表函数，例如对主键取余来决定操作哪张表
• 需要多个相同结构的表
• 不局限于myisam存储引擎，但需要提前设计好，不能轻易更改分表算法

11. 表分区

• 按照主键进行key-取余，把表的文件分成多个。但还是一张表，只是存储的文件变多了
• 适合数据量非常大的表，用于突破磁盘I/O瓶颈，提高磁盘的读写能力，达到优化目的

---

SQL语句优化

1. 不要使用*
查询sql的时候不要用*，需要什么字段写什么字段

2. EXPLAIN 你的 SELECT 查询
参考文章：MySQL explain执行计划解读

3. 为查询缓存优化SQL
像 NOW() 和 RAND() 或是其它的此类SQL函数都不会开启查询缓存，因为这些函数的返回值是变化的。所以，你所需要的就是用一个变量来代替MySQL的函数，从而开启缓存。此外需要注意，update和select频繁交替使用可能会导致开启缓存比没有缓存更慢

---

PROCEDURE ANALYSE() 取得建议

语法：SELECT * FROM sj_area PROCEDURE ANALYSE();

• Optimal_fieldtype这一列就是这个字段的优化建议。
• PROCEDURE ANALYSE() 是根据表中现有数据进行分析并给出的建议，在数据量少的时候可能并不准确。 比如你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，那么，PROCEDURE ANALYSE()会建议你把这个字段的类型改成MEDIUMINT 。或是你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成 ENUM 的建议。
• 这些建议，都是可能因为数据不够多，所以决策做得就不够准。一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议才会变得准确。一定要记住，你才是最终做决定的人。

数据库命名规范

字段名：前缀用小写字母标识字段类型，整体用驼峰语法或全小写下划线分割（只能用一种）。如：sBizCode（varchar）、iTaskId（int）、dtStartTime（datetime）
表名：前缀为模块名，整体用驼峰语法或全小写下划线分割（只能用一种）。如：toefl_listen_paper_practice，这张表是存的托福模块下的听力试卷练习记录