关于数据库优化，网上的资料和方法很多，但是很多质量参差不齐，有的总结不到位，内容繁杂。偶然发现这篇文章，总结的很经典，文章流量也很大，于是有了自己的总结合集，积累了优质文章，提升了个人能力。希望对大家以后的发展有所帮助。1、选择最适用的字段属性MySQL可以很好地支持大量数据的访问，但一般来说，数据库中的表越小，对其执行查询的速度就越快。因此，在创建表格时，为了获得更好的性能，我们可以将表格中字段的宽度设置得尽可能小。比如在定义邮政编码字段时，如果设置为CHAR(255)，显然给数据库增加了不必要的空间，甚至使用VARCHAR也是多余的，因为CHAR(6)可以很好的完成任务。此外，如果可能，我们应该使用MEDIUMINT而不是BIGIN来定义整数字段。另一种提高效率的方法是只要有可能就将该字段设置为NOTNULL，这样数据库以后执行查询时就不需要再去比较NULL值了。对于一些文本字段，例如“省”或“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值数据处理，数值数据的处理速度比文本类型要快得多。这样，我们就可以提高数据库的性能。2、使用连接（JOIN）代替子查询（Sub-Queries）MySQL从4.1开始支持SQL子查询。该技术使用SELECT语句创建单列查询结果，然后将此结果用作另一个查询中的过滤条件。比如我们要删除客户基本信息表中没有任何订单的客户，我们可以使用子查询先从销售信息表中取出所有下单的客户ID，然后将结果传递给主查询，如下图：DELETEFROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)使用子查询可以完成很多逻辑上需要多个步骤一次完成的SQL操作，也可以避免事务或者表锁，而且写起来简单。但是，在某些情况下，子查询可以用更高效的连接（JOIN）代替。例如，假设我们要检索所有没有订单记录的用户，这可以通过以下查询来完成：SELECT*FROMcustomerinfoWHERECustomerIDNOTin(SELECTCustomerIDFROMsalesinfo)如果使用连接(JOIN)..来完成这个查询，速度将要快得多。特别是当salesinfo表中有CustomerID的索引时，性能会更好。查询如下：SELECT*FROMcustomerinfoLEFTJOINsalesinfoONcustomerinfo.CustomerID=salesinfo.CustomerIDWHEREsalesinfo.CustomerIDISNULLconnection(JOIN)..效率更高，因为MySQL不需要在内存中创建临时表来完成这个逻辑两步查询。3.使用联合（UNION）代替手动创建的临时表。MySQL从4.0版本开始支持联合查询。它可以将需要使用临时表的两个或多个选择查询合并为一个查询。在客户端的查询会话结束时，临时表会被自动删除，从而保证数据库的整洁和高效。在使用union创建查询时，我们只需要使用UNION作为关键字来连接多个select语句。需要注意的是，所有select语句中的字段数必须相同。以下示例演示了使用UNION的查询。SELECTName,PhoneFROMclientUNIONSELECTName,BirthDateFROMauthorUNIONSELECTName,SupplierFROMproduct4,transactions虽然我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种查询，但并不是所有的数据库操作都可以只使用一个或几个SQL语句可以完成。更多的时候是需要用一系列的语句来完成某种工作。但是在这种情况下，当语句块中的某条语句运行错误时，整个语句块的运行就变得不确定了。想象一下，你想同时向两个相关的表中插入某个数据。可能会出现这样的情况：第一个表更新成功后，数据库突然遇到意外情况，导致第二个表中的操作无法完成，这样一来，数据就会不完整，甚至会破坏数据库中的数据。为避免这种情况，应使用事务。它的作用是：要么语句块中的每条语句都成功，要么全部失败。也就是说，可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，以COMMIT关键字结束。如果中间有一个SQL操作失败，那么ROLLBACK命令可以将数据库恢复到BEGIN开始之前的状态。BEGIN;INSERTIINTOsalesinfoSETCustomerID=14;UPDATEinventorySETQuantity=11WHEREitem='book';COMMIT;事务的另一个重要作用是当多个用户同时使用同一个数据源时，可以使用锁定数据库的方法为用户提供安全的访问方式，可以保证用户的操作不会受到干扰由其他用户。5.锁定表虽然事务是维护数据库完整性的一种很好的方式，但是它们有时会因为其独占性而影响数据库性能，尤其是在大型应用系统中。由于在事务执行过程中数据库会被锁定，其他用户请求只能暂时等待事务结束。如果一个数据库系统只被少数用户使用，事务的影响不会是一个大问题；但是如果有成千上万的用户同时访问一个数据库系统，比如访问一个电子商务网站，就会出现严重的响应延迟。事实上，在某些情况下我们可以通过锁定表来获得更好的性能。下面的例子使用锁表的方法来完成前面例子中的事务功能。LOCKTABLEinventoryWRITESELECTQuantityFROMinventoryWHEREItem='book';...UPDATEinventorySETQuantity=11WHEREItem='book';UNLOCKTABLES这里我们使用select语句取出初始数据，通过一些计算，使用update语句将新值更新到表中。包含WRITE关键字的LOCKTABLE语句确保在执行UNLOCKTABLES命令之前没有其他访问会插入、更新或删除库存。6、使用外键锁表的方法可以保持数据的完整性，但不能保证数据的相关性。这时候我们就可以使用外键了。例如，外键可以确保每条销售记录都指向现有客户。这里，外键可以将customerinfo表中的CustomerID映射到salesinfo表中的CustomerID，任何没有有效CustomerID的记录都不会被更新或插入到salesinfo中。CREATETABLEcustomerinfo(CustomerIDINTNOTNULL,PRIMARYKEY(CustomerID))TYPE=INNODB;CREATETABLEsalesinfo(SalesIDINTNOTNULL,CustomerIDINTNOTNULL,PRIMARYKEY(CustomerID,SalesID),FOREIGNKEY(CustomerID)REFERENCEScustomerinfo(CustomerID)ONDELETECASCADE)TYPE=INNODB; 注意例子中的参数“ONDELETECASCADE”。这个参数保证当customerinfo表中的一条客户记录被删除时，salesinfo表中与该客户相关的所有记录也会自动删除。如果要在MySQL中使用外键，一定要记得定义类型创建表时将表作为事务安全表的InnoDB类型。该类型不是MySQL表的默认类型。定义的方法是在CREATETABLE语句中添加TYPE=INNODB。如示例所示。7.使用索引索引是提高数据库性能的常用方法，它可以使数据库服务器检索特定行的速度比没有索引快得多，尤其是在包含MAX()、MIN()和ORDERBY。使用该命令时，性能改进更加明显。哪些字段应该被索引？一般来说，索引应该建立在将要用于JOIN、WHERE判断和ORDERBY排序的字段上。尽量不要索引数据库中包含大量重复值的字段。对于一个ENUM类型的字段，很有可能会有大量的重复值，比如customerinfo中的“province”字段。在这样的字段上建立索引无济于事；相反，它可能会降低数据库性能。我们可以在建表的时候同时创建一个合适的索引，也可以在后面使用ALTERTABLE或者CREATEINDEX来创建索引。此外，MySQL从3.23.23版本开始支持全文索引和搜索。全文索引是MySQL中的FULLTEXT类型的索引，但它只能用于MyISAM类型的表。对于大型数据库，将数据加载到没有全文索引的表中，然后使用ALTERTABLE或CREATEINDEX创建索引是非常快的。但是如果将数据加载到一个已经有全文索引的表中，执行过程会很慢。8、优化查询语句在大多数情况下，使用索引可以提高查询速度，但是如果SQL语句使用不当，索引将无法发挥应有的作用。以下是应注意的几个方面。A。首先，MySQL3.23之前的同类型字段之间最好进行比较操作，这甚至是一个必要条件。例如，索引的INT字段不能与BIGINT字段进行比较；但作为一种特殊情况，当CHAR类型字段和VARCHAR类型字段的字段大小相同时，可以进行比较。b.其次，尽量不要使用函数对有索引的字段进行操作。例如，在DATE类型的字段上使用YEAE()函数时，索引将无法发挥应有的作用。因此，尽管以下两个查询返回相同的结果，但后者比前者快得多。C。第三，在搜索字符字段时，我们有时会使用LIKE关键字和通配符。这种做法虽然简单，但也是以牺牲系统性能为代价的。例如，下面的查询将比较表Record中的每个项目。SELECT*FROMbooksWHEREnamelike"MySQL%"但是如果你改用下面的查询，返回的结果是一样的，但是速度要快很多：SELECT*FROMbooksWHEREname>="MySQL"andname<"MySQM"最后，你应该付出代价注意避免查询让MySQL进行自动类型转换，因为转换过程也会使索引失效。
