优化数据库方法

1、选取最适用的字段属性
例如，在定义邮政编码这个字段时，如果将其设置为CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。


2、使用连接（JOIN）来代替子查询
例如，假设我们要将所有没有订单记录的用户取出来，可以用下面这个查询完成：
SELECT * FROM customerinfo
WHERE CustomerID NOT IN (SELECTC ustomerID FROM salesinfo)
如果使用连接（JOIN）..来完成这个查询工作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：


SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID ISNULL


连接（JOIN）..之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。




3、使用联合(UNION)来代替手动创建的临时表
它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。


SELECT Name,Phone FROM client UNION


SELECT Name,BirthDate FROM author UNION


SELECT Name,Supplier FROM product


4、事务
它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。


BEGIN;
INSERT INTO salesinfo SET CustomerID = 14;
UPDATE inventory SET Quantity = 11 WHERE item = 'book';
COMMIT;




5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题；但假设有成千上万的用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。
LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
...
UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES




6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
例如，外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以把customerinfo表中的CustomerID映射到salesinfo表中CustomerID，任何一条没有合法CustomerID的记录都不会被更新或插入到salesinfo中。
BEGIN;
INSERT INTO salesinfo SET CustomerID = 14;
UPDATE inventory SET Quantity = 11 WHERE item = 'book';
COMMIT;


CREATE TABLE customerinfo (
CustomerIDINT NOT NULL,
PRIMARYKEY (CustomerID)
) TYPE = INNODB;


CREATE TABLE salesinfo (
SalesIDNT NOT NULL,
CustomerIDINT NOT NULL,
PRIMARYKEY (CustomerID, SalesID),
FOREIGNKEY (CustomerID) REFERENCES customerinfo (CustomerID) ON DELETE CASCADE
) TYPE = INNODB;
注意例子中的参数“ON DELETE CASCADE”。该参数保证当customerinfo表中的一条客户记录被删除的时候，salesinfo表中所有与该客户相关的记录也会被自动删除。如果要在MySQL中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中加上TYPE=INNODB。如例中所示。


7、使用索引


索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。


那该对哪些字段建立索引呢？


一般说来，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况


8、优化的查询语句


注意：使用索引，把所建的索引所用列名，用在where语句中,并尽量在条件的最右边
在Sql语句中，把条件最小的写在最右边,如果id=?写在最右边
在sql尽量少写子查询，用join语句代替，少写in,like,or