如何进行MySQL的性能优化

MySQL数据表字段要选取合适的字段属性

MySQL可以很好支撑大数据量的存取，但是一般说来，数据库中的表越小在它上面执行的查询也就会越快。因此在创建表可以的时候为了获取更好的性能，我们可以将表中的字段宽度设得尽可能小。

例如，定义邮政编码这个字段如果设置为CHAR(256)或者VARCHAR都会增加数据库的空间，因为设置为CHAR(6)就可以。

另外一个提高效率的方法是在可能的情况下，应该尽量吧字段设置为Not Null,这样在将来执行查询的时候，数据库不用去比较Null值。

对于某些文本字段，例如省份或者性别，我们可以将它们定义为ENUM类型。因为在MySQL中Enum类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快的多。

使用连接Join来代替字查询（sub-Query）

MySQL可以使用Select语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。

例如，我们要将客户基本信息表中没有任何订单的客户删除，就可以利用子查询先从销售信息表中将所有发出订单的客户ID取出来，然后将结果传递给主查询：

Delete from customerinfo where customerId not in (select CustomerID from salesinfo)

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起了也比较容易。但是在有些情况下，子查询可以被更有效的join操作代替。

例如假设我们要讲所有没有订单记录的用户取出来，可以用下面这个查询完成：

select * from customerinfo where customerID Not in (select customerId from salesinfo)

如果使用连接join来完成这个查询工作，速度会快很多，尤其是当salesinfo表中对CustomerID建有索引的话，性能会更好，查询如下：

select * from customerinfo Left Join salesinfo on customerinfo.customerID=salesinfo.CustomerID where salesinfo.CustomerID is Null

连接join操作之所以更有效率一些，是因为mysql不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的工作。

使用联合Union来代替手动创建临时表

在MySQL中联合Unio操作可以吧需要使用临时表的两条或更多select查询合并到一个查询中。在客户端查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。使用union来创建查询的时候，我们需要用union作为关键字把多个select语句连接起来.

例如下面语句：

select name,phone from client 

UNION

select name,birthDate from author

UNION

select name,supplier from product;

使用事务机制来完成需要多个SQL操作的数据处理，保证数据的完整性和安全访问。

事务一begin关键字开始，commit关键字结束，rollback命令可以把数据库恢复到begin开始之前的状态。

例如以下语句：

BEGIN;

insert into salesinfo set  customerID =14;

update inventory set quantity =12 where item ='book';

COMMIT;

事务的另一个重要作用就当多个用户同时使用相同的数据源时，他可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户的操作不被其他用户所干扰。

如果情况允许锁定表的方式会比采用事务的方式拥有更好的性能。

尽管事务时维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时候会影响数据库的性能，尤其是在很大的应用系统中。由于在执行事务的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待知道该事务结束。如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题，单假设有成千上万用户同时访问一个数据库系统，例如访问一个电子商务网站，就会产生比较严重的响应延时。

其实有些情况下，可以通过锁定表的方法来获得更好的性能。

如下例子：

Lock Table inventory write;

select quantity from inventory where item ='book';

...

Update inventory set quantity =11 where item ='book';

Unlock tables;

这里我们用一个select语句取出原始数据，通过一些计算，用update语句将心值更新到表中。包含write关键字的lock table可以保证unlock tables命令执行之前不会有其他的访问来对inventory进行插入、更新或者删除的操作。

使用外键来优化关联表的性能

外键可以用来保证数据的关联性。

例如外键可以保证每一条销售记录都指向某一个存在的客户。在这里，外键可以吧customerinfo表中的customerID映射到salesinfo表中的CustomerID，任何一条没有合法CustomerID的记录都不会更新或插入到salesinfo中。

CREATE TABLE customerinfo(

  customerId INT NOT NULL,

  PRIMARY KEY(customerID)

) ENGINE = INNODB;

CREATE TABLE salesinfo(

  salesId INT NOT NULL,

  customerId INT NOT NULL,

  PRIMARY KEY(customerID, SalesId),

  FOREIGN KEY(customerID) REFERENCES customerinfo(customerID) ON DELETE CASCADE

) ENGINE = INNODB;

注意这个例子中的参数“on delete cascade”,该参数保证当customerinfo表中的一条记录被删除时，salesinfo表中所有与该客户相关的记录会被自动删除。如果要做mysql中使用外键，一定要记住在创建表的时候将表的类型定义为事务安全表innodb类型，该类型不是mysql的默认表类型，定义的方法是在create table语句中加上ENGINE =INNODB.

使用索引提高MySQL数据库的性能。

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

那该对那些字段建立索引呢？一般索引应建立在那些用于JOIN，WHERE判断和ORDER BY排序的字段上，尽量不要对数据库中某个含有大量重复的值得字段建立索引。

对于ENUM类型的字段来说，出现大量重复值是很有可能的情况，所以在这种字段不要建立索引，例如，customerinfo表中的province枚举型字段，在这样的字段建立索引将不会有什么帮助，还有可能降低数据库的性能。

通过优化查询语句提高查询速度。

多数情况下索引可以提高查询速度，单如果SQL语句使用不恰当的话，索引将无法发挥他应有的作用。

1）最好在相同类型的字段间进行比较

2）在建有索引的字段上尽量不要使用函数操作

例如：在一个Date类型的字段上使用year（）函数时，将会使索引不能发挥作用，下面两个语句后者要比前者快。

select * from order where year(orderDate)<2001;

select * from order where orderDate <"2001-01-01";

3)在搜索字符型字段时我们会使用like关键字节通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。

例如以下两个语句后者性能要比前者好：

select * from books where name like "%mySQL%";

select * from books where name>="mySQL" and name =<"mySQL";

4) 应该注意避免在查询中让mysql进行自动类型转换，因为转换过程会使索引失效。