8-24 988 views
1、选取最适用的字段属性
- 尽量将字段设置为NOT NULL，执行查询时，数据库不用去比较NULL值
- 例如：定义邮政编码这个字段时，如果设置为CHAR(255)，就造成了浪费，设置成CHAR(6)即可。
- 例如：省份或性别，定义为ENUM类型，其处理速度比文本类型快得多
2、有些情况可以使用连接（join）来代替子查询（Sub-Queries）
例如：要将所有没有订单记录的用户取出来
1）子查询
SELECT * FROM customerinfo
WHERE customerID NOT in(SELECT customerID FROM salesinfo)
2)连接查询
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.customerID = salesinfo.customerID
WHERE salesinfo.customerID is null
3）连接（JOIN） 之所以更有效率一些，是因为 MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
3、使用联合（UNION）来代替手动创建的临时表
- 其可将需要使用临时表的两条或更多的SELECT查询合并到一个查询中；
- 在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效；
- 只需要用UNION作为关键字把多个SELECT语句连接起来就可以了
- 注意：所有SELECT语句中的字段数量要相同
例子：
SELECT name,phone, FROM client
UNION
SELECT name,birthDate FROM author
UNION
SELECT name,supplier FROM product
4、事务
常用于语句块，保证数据的一致性和完整性
1）作用一：失败可回滚
- 以BEGIN关键字开始，COMMIT关键字结束
- 在其间的任何一条SQL语句操作失败，都可用ROLLBACK命令将数据库恢复到BEGIN之前的状态
BEGIN;
INSERT INTO salesinfo SET customerID = 14;
UPDATE inventory SET quantity = 11 WHERE item = 'book';
COMMIT;
2）作用二：保证单一用户操作
- 利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其它的用户所干扰
5、锁定表
- 如果访问人数多，事务的独占性会产生比较严重的响应延迟
- 此时，可通过锁定表的方式来获得更好的性能
LOCK TABLE inventory WRITE
SELECT quantiry FROM inventory WHERE item = 'book';
...
UPDATE inventory SET Quantity=11 WHERE Item='book';
UNLOCK TABLES
6、使用外键
- 锁定表的方法可以维护数据的完整性，但是却不能保证数据的关联性
- 此时，可使用外键，实现数据表间的数据同步
例如：外键可以保证每一条销售记录都指向某一个存在的客户。
CREATE TABLE customerinfo(
customerId INT NOT NULL PRIMARY KEY,
)TYPE = INNODB;
CREATE TABLE salesinfo(
salefId INT NOT NULL,
customerId INT NOT NULL,
PRIMARY KEY(customerId),
FOREIGN KEY(customerId) REFERENCES customerinfo(customerId) ON DELETE CASCADE
)TYPE = INNODB;
注意：参数"ON DELETE CASCADE"保证当customerinfo表中的一条客户记录被删除时，slaesinfo表中所有与该客户相关的记录也会被自动删除。
7、使用索引
- 索引是提高数据库性能的常用方法
- 索引应建立在将用于JOIN/WHERE判断/ORDER BY排序的字段上
- 尽量不要对数据库中某个含有大量重复的值的字段建立索引，例如：ENUM类型的字段
8、优化查询语句
1）最好在相同类型的字段间进行比较的操作
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
后面的查询将比前面的一个快很多
2）在建有索引的字段上尽量不要使用函数进行操作
SELECT * FROM order WHERE YEAR（OrderDate）<2001;
SELECT * FROM order WHERE OrderDate<“2001-01-01”;
在一个DATE类型的字段上使用YEAR（）函数时，将会使索引不能发挥应有的作用。所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。
3）在搜索字符型字段时，使用LIKE关键字和通配符，虽然做法简单，但是以牺牲系统性能为代价的
SELECT * FROM books
WHERE name like “MySQL%”
但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：
SELECT * FROM books
WHERE name >= “MySQL”and name < “MySQM”
4）应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。