MySQL数据库优化的八种方式，你知道几种？ - 网站源码_资源分享

关于资料库强化，网路上有许多资料和方式，但许多质量良莠不齐，很多归纳的不妥当，内容繁密。在有限的时间匆忙写了这首诗，根据看完的MySQL的各种该文归纳出一下经典之作，希望对大家未来开发中也有帮助

01 选取最适用于的表头特性

MySQL能较好的全力支持大重要信息量的读取，但一般而言，资料库中的表越小，在它上面继续执行的查阅也就会越快。因此，在建立表的这时候，为了获得更快的操控性，他们能将附注表头的长度设得尽可能小。

比如，在表述四等那个表头时，假如将其增设为CHAR(255),显然给资料库增加了无谓的空间，甚至采用VARCHAR这种类别也是累赘的，即使CHAR(6)就能较好的顺利完成任务了。同样的，假如能的话，他们假如采用MEDIUMINT而不是BIGIN来表述auth表头。

另外两个降低成本的方式是在可能的情况下，假如尽可能把表头增设为NOTNULL，这种在今后继续执行查阅的这时候，资料库不用去比较NULL值。对于某些文档表头，比如“省市”或是“异性恋”，他们能将它们表述为ENUM类别。即使在MySQL中，ENUM类别被当做值型统计数据来处置，而值型统计数据被处置出来的速度要比文档类别好得多。这种，他们又能提高资料库的操控性。

02 采用相连（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 (SELECTC ustomerID FROM salesinfo)

假如采用相连（JOIN）..来顺利完成那个查阅工作，速度将会快很多。尤其是当salesinfo附注对CustomerID建有索引的话，操控性将会更快，查阅如下表所示：

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

相连（JOIN）..之所以更简便一些，是即使MySQL不需要在内存中建立临时表来顺利完成那个逻辑上的需要两个关键步骤的查阅工作。

03 采用联合(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

04 外交事务

尽管他们能采用子查阅（Sub-Queries）、相连（JOIN）和联合（UNION）来建立各种各样的查阅，但不是大部份的资料库操作都能只用一条或少数几条SQL句子就可以顺利完成的。更多的这时候是需要用到一系列的句子来顺利完成某种工作。但在这种情况下，当那个句子块中的某一条句子运行出错的这时候，整个句子块的操作就会变得不确定出来。设想一下，要把某个统计数据同时插入两个相关联的附注，可能会出现这种的情况：第两个附注成功更新后，资料库突然出现意外状况，造成第二个附注的操作没顺利完成，这种，就会造成统计数据的不完整，甚至会破坏资料库中的统计数据。要避免这种情况，就假如采用外交事务，它的作用是：要么句子块中每条句子都操作成功，要么都失败。换句话说，就是能保持资料库中统计数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就能把资料库恢复到BEGIN开始之前的状态。

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

外交事务的另两个重要作用是当多个使用者同时采用相同的统计数据源时，它能利用锁定资料库的方式来为使用者提供一种安全的访问方式，这种能保证使用者的操作不被其它的使用者所干扰。

05 锁定表

尽管外交事务是维护资料库完整性的两个非常好的方式，但却即使它的独占性，有时会影响资料库的操控性，尤其是在很大的应用系统中。由于在外交事务继续执行的过程中，资料库将会被锁定，因此其它的使用者请求只能暂时等待直到该外交事务结束。假如两个资料库系统只有少数几个使用者来采用，外交事务造成的影响不会成为两个太大的问题；但假定有成千上万的使用者同时访问两个资料库系统，例如访问两个电子商务网站，就会产生比较严重的响应延迟。

其实，很多情况下他们能通过锁定表的方式来获得更快的操控性。上面的例子就用锁定表的方式来顺利完成前面两个例子中外交事务的功能。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item=book; … UPDATE inventory SET Quantity=11 WHERE Item=book; UNLOCKTABLES

这里，他们用两个select句子抽出初始统计数据，通过一些计算，用update句子将新值更新到附注。包含有WRITE关键字的LOCKTABLE句子能保证在UNLOCKTABLES命令被继续执行之前，不会有其它的访问来对inventory进行插入、更新或是删掉的操作。

06 采用外键

锁定表的方式能维护统计数据的完整性，但它却不能保证统计数据的关联性。那个这时候他们就能采用外键。

比如，外键能保证每一条销售历史记录都指向某两个存在的顾客。在这里，外键能把customerinfo附注的CustomerID映射到salesinfo附注CustomerID，任何一条没合法CustomerID的历史记录都不会被更新或插入到salesinfo中。

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。如例中右图。

07 采用索引

索引是提高资料库操控性的常用方式，它能令资料库服务器以比没索引好得多的速度检索特定的行，尤其是在查阅句子当中包含有MAX(),MIN()和ORDERBY这些命令的这时候，操控性提高更为明显。

那该对哪些表头建立索引呢？

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

比如customerinfo中的“province”..表头，在这种的表头上建立索引将不会有什么帮助；相反，还有可能降低资料库的操控性。他们在建立表的这时候能同时建立合适的索引，也能采用ALTERTABLE或CREATEINDEX在以后建立索引。此外，MySQL从版本3.23.23开始全力支持全文索引和搜索。全文索引在MySQL中是两个FULLTEXT类别索引，但仅能用于MyISAM类别的表。对于两个大的资料库，将统计数据装载到两个没FULLTEXT索引的附注，接着再采用ALTERTABLE或CREATEINDEX建立索引，将是非常快的。但假如将统计数据装载到两个已经有FULLTEXT索引的附注，继续执行过程将会非常慢。