完好优化MySQL数据库性能的八大巧办法[MySQL防范]

本文从八个方面来说授若何全新优化MySQL数据库性能.

1、选取最实用的字段属性

MySQL可以很好的支持大数据量的存取,但是普通说来,数据库中的表越小,在它上面履行的查询也就会越快.因此,在成立表的时刻,为了得到更好的性能,我们可以将表中字段的宽度设得尽大概小.比方,在定义邮政编码这个字段时,假如将其设置为CHAR(255),明显给数据库增添了不必要的空间,乃至利用VARCHAR这种范例也是多余的,因为CHAR(6)便可以很好的完成任务了.一样的,假如可以的话,我们应当利用MEDIUMINT而不是BIGIN来定义整型字段.

别的一个提高效率的办法是在大概的情形下,应当尽大概把字段设置为NOT NULL,这样在将来履行查询的时刻,数据库不用去对比NULL值.

关于某些文本字段,比方“省份”大概“性别”,我们可以将它们定义为ENUM范例.因为在MySQL中,ENUM范例被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本范例快得多.这样,我们又可以提高数据库的性能.

2、利用衔接（JOIN）来替换子查询(Sub-Queries)

MySQL从4.1开始支持SQL的子查询.这个技术可以利用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 salesinfoON customerinfo.CustomerID=salesinfo. 
CustomerID 
WHERE salesinfo.CustomerID IS NULL

衔接（JOIN）.. 之所以更有效率一些,是因为 MySQL不需求在内存中成立暂时表来完成这个逻辑上的需求两个步骤的查询工作.

3、利用结合(UNION)来替换手动成立的暂时表

MySQL 从 4.0 的版本开始支持 UNION 查询,它可以把需求利用暂时表的两条或更多的 SELECT 查询归并的一个查询中.在客户端的查询会话完毕的时刻,暂时表会被自动删除,从而保证数据库整齐、高效.利用 UNION 来成立查询的时刻,我们只需求用 UNION作为关键字把多个 SELECT 语句衔接起来便可以了,要注意的是全部 SELECT 语句中的字段数目要想同.下面的例子就演示了一个利用 UNION的查询.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

4、事件

固然我们可以利用子查询（Sub-Queries）、衔接（JOIN）和结合（UNION）来成立各种各样的查询,但不是全部的数据库操作都可以只用一条或少数几条SQL语句便可以完成的.更多的时刻是需求用到一系列的语句来完成某种工作.但是在这种情形下,当这个语句块中的某一条语句运行出错的时刻,整个语句块的操作就会变得不肯定起来.假想一下,要把某个数据同时插入两个相关联的表中,大概会呈现这样的情形:第一个表中成功更新后,数据库忽然呈现不测情况,造成第二个表中的操作没有完成,这样,就会造成数据的不完好,乃至会破坏数据库中的数据.要避免这种情形,就应当利用事件,它的作用是:要末语句块中每条语句都操作成功,要末都失利.换句话说,就是可以保持数据库中数据的一致性和完好性.事物以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
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES

这里,我们用一个 SELECT 语句取出初始数据,通过一些计算,用 UPDATE 语句将新值更新到表中.包含有 WRITE 关键字的 LOCK TABLE 语句可以保证在 UNLOCK TABLES 号令被履行之前,不会有别的的拜候来对 inventory 举行插入、更新大概删除的操作.

6、利用外键

锁定表的办法可以保护数据的完好性,但是它却不能保证数据的关联性.这个时刻我们便可以利用外键.比方,外键可以保证每一条贩卖记录都指向某一个存在的客户.在这里,外键可以把customerinfo 表中的CustomerID映射到salesinfo表中CustomerID,任何一条没有合理CustomerID的记录都不会被更新或插入到salesinfo中.

CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;

CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = INNODB;

注意例子中的参数“ON DELETE CASCADE”.该参数保证当 customerinfo 表中的一条客户记录被删除的时刻,salesinfo 表中全部与该客户相关的记录也会被自动删除.假如要在 MySQL 中利用外键,一定要记着在成立表的时刻将表的范例定义为事件安全表 InnoDB范例.该范例不是 MySQL 表的默许范例.定义的办法是在 CREATE TABLE 语句中加上 TYPE=INNODB.如例中所示.

7、利用索引

索引是提高数据库性能的常用办法,它可以令数据库服务器以比没有索引快得多的速度检索特定的行,特别是在查询语句当中包含有MAX(), MIN()和ORDERBY这些号令的时刻,性能提高更为明显.那该对哪些字段成立索引呢?普通说来,索引应成立在那些将用于JOIN, WHERE判断和ORDER BY排序的字段上.尽大概不要对数据库中某个含有大量反复的值的字段成立索引.关于一个ENUM范例的字段来说,呈现大量反复值是很有大概的情形,比方customerinfo中的“province”.. 字段,在这样的字段上成立索引将不会有什么帮忙;相反,还有大概降低数据库的性能.我们在成立表的时刻可以同时成立符合的索引,也可以利用ALTER TABLE或CREATE INDEX在今后成立索引.此外,MySQL

从版本3.23.23开始支持全文索引和搜索.全文索引在MySQL 中是一个FULLTEXT范例索引,但仅能用于MyISAM 范例的表.关于一个大的数据库,将数据装载到一个没有FULLTEXT索引的表中,然后再利用ALTER TABLE或CREATE INDEX成立索引,将是非常快的.但假如将数据装载到一个已经有FULLTEXT索引的表中,履行历程将会非常慢.

8、优化的查询语句

绝大大都情形下,利用索引可以提高查询的速度,但假如SQL语句利用不得当的话,索引将无法施展它应有的作用.下面是应当注意的几个方面.首先,最好是在相同范例的字段间举行对比的操作.在MySQL 3.23版之前,这乃至是一个必须的条件.比方不能将一个建有索引的INT字段和BIGINT字段举行对比;但是作为特别的情形,在CHAR范例的字段和VARCHAR范例字段的字段大小相同的时刻,可以将它们举行对比.其次,在建有索引的字段上尽大概不要利用函数举行操作.

比方,在一个DATE范例的字段上利用YEAE()函数时,将会使索引不能施展应有的作用.所以,下面的两个查询固然返回的后果一样,但后者要比前者快得多.

SELECT * FROM order WHERE YEAR(OrderDate)<2001;

SELECT * FROM order WHERE OrderDate<"2001-01-01";

一样的情形也会发生在对数值型字段举行计算的时刻:

SELECT * FROM inventory WHERE Amount/7<24;

SELECT * FROM inventory WHERE Amount<24*7;

上面的两个查询也是返回相同的后果,但背面的查询将比前面的一个快很多.第三,在搜索字符型字段时,我们有时会利用 LIKE 关键字和通配符,这种做法固然简单,但却也是以牺牲系统性能为代价的.比方下面的查询将会对比表中的每一条记录.

SELECT * FROM books
WHERE name like "MySQL%"

但是假如换用下面的查询,返回的后果一样,但速度就要快上很多:

SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"

最后,应当注意避免在查询中让MySQL举行自动范例转换,因为转换历程也会使索引变得不起作用.