优化的八种办法

有许多方法可以优化. 最近，我在阅读面试问题，并总结了一些优化的方法.

优化的方法

1. 选择最适用的字段属性

MySQL可以很好地支持对大量数据的访问，但是通常来说，中的表越小，对其执行的查询越快. 因此，在创建表时，为了获得更好的性能怎样优化，我们可以将表中字段的宽度设置得尽可能小.

例如，在定义邮政编码字段时，如果将其设置为CHAR（255），则显然会向添加不必要的空间. 即使使用VARCHAR也是多余的，因为CHAR（6）可以很好地完成任务. 同样，如果可能，我们应该使用MEDIUMINT而不是BIGIN来定义整数字段.

另一种提高效率的方法是在可能的情况下将字段设置为NOT NULL，以便将来在执行查询时不需要比较NULL值.

对于某些文本字段，例如“ province”或“ gender”，我们可以将它们定义为ENUM类型. 因为在MySQL中，ENUM类型被视为数字数据，并且数字数据的处理比文本类型快得多. 这样，我们可以提高的性能.

2. 使用JOIN代替子查询

MySQL从4.1开始就支持SQL子查询. 此技术可以使用SELECT语句创建单列查询结果，然后将该结果用作另一个查询中的过滤条件. 例如，如果我们要删除“客户基本信息”表中没有任何订单的客户，则可以使用子查询来首先从“销售信息”表中获取发布了订单的所有客户ID，然后将结果传递给主要查询，如下所示:

DELETEFROMcustomerinfo

WHERECustomerIDNOTin（SELECTCustomerIDFROMsalesinfo）

使用子查询可以完成许多SQL操作，这些操作在逻辑上需要一次完成多个步骤. 同时，它还可以避免事务或表锁定，并且易于编写. 但是，在某些情况下，子查询可以由更高效的联接（JOIN）代替. 例如，假设我们要检索所有没有订单记录的用户，则可以使用以下查询来完成:

SELECT * FROMcustomerinfo

WHERECustomerIDNOTin（SELECTCustomerIDFROMsalesinfo）

如果使用JOIN完成此查询，则速度将更快. 特别是在salesinfo表中的CustomerID上有索引时，性能会更好. 查询如下:

SELECT * FROMcustomerinfo

LEFTJOINsalesinfoONcustomerinfo.CustomerID = salesinfo.CustomerID

WHEREsalesinfo.CustomerIDISNULL

加入（JOIN）. 之所以更有效，是因为MySQL不需要在内存中创建临时表即可完成此逻辑两步查询.

3，使用联合（UNION）代替手动创建的临时表

MySQL自版本4.0起就支持联合查询，该联合查询可以将需要临时表的两个或多个选择查询组合为一个查询. 在客户端的查询会话结束时，临时表将被自动删除，以确保整洁高效. 当使用union创建查询时，我们只需要使用UNION作为关键字来连接多个select语句. 应该注意的是，所有选择语句中的字段数必须相同. 下面的示例演示了使用UNION的查询.

SELECTName，PhoneFROMclientUNION

SELECTName，BirthDateFROMauthorUNION

SELECTName，SupplierFROMproduct

4. 事务

尽管我们可以使用子查询，JOIN和UNION创建各种查询，但并非所有操作都只能使用一个或几个SQL语句. 通常，有必要使用一系列语句来完成某种工作. 但是在这种情况下，当语句块中的某个语句运行不正确时，整个语句块的操作将变得不确定. 想象一下，如果您想同时向两个相关的表中插入某些数据，则可能会出现以下情况: 成功更新第一个表后，突然出现意外情况，从而导致第二个表中的操作无法完成这样，它将导致数据不完整，甚至破坏中的数据. 为避免这种情况，您应该使用事务，它的作用是: 语句块中每个语句的操作成功或全部失败. 换句话说，可以保持中数据的一致性和完整性. 事情以BEGIN关键字开头，以COMMIT关键字结尾. 同时，SQL操作失败，那么ROLLBACK命令可以将恢复到BEGIN启动之前的状态.

BEGIN; INSERTINTOsalesinfoSETCustomerID = 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表的默认类型. 定义方法是将TYPE = INNODB添加到CREATETABLE语句. 如示例所示.

7. 使用索引

索引是提高性能的常用方法. 它可以使服务器检索特定行的速度比没有索引时要快得多，尤其是在查询包含MAX（），MIN（）和ORDERBY命令when的情况下，性能提高更为明显.

那应该为哪些字段建立索引？

通常来说，索引应该建立在将用于JOIN，WHERE判断和ORDERBY排序的字段上. 尽量不要索引中包含大量重复值的字段. 对于ENUM类型的字段，很可能会有大量重复的值

例如，customerinfo中的“省” ...字段. 在此类字段上建立索引将无济于事；相反，它也可能降低的性能. 创建表时，我们可以同时创建适当的索引，或者将来可以使用ALTERTABLE或CREATEINDEX创建索引. 另外，MySQL从版本3.23.23开始支持全文索引和搜索. 全文索引是MySQL中的FULLTEXT类型索引，但只能用于MyISAM类型表. 对于大型，将数据加载到没有FULLTEXT索引的表中非常快，然后使用ALTERTABLE或CREATEINDEX创建索引是非常快的. 但是，如果将数据加载到已经具有FULLTEXT索引的表中，执行过程将非常缓慢.

8. 优化的查询语句

在大多数情况下，使用索引可以提高查询的速度，但是如果未正确使用SQL语句，则索引将无法发挥应有的作用.

以下是应注意的一些方面.

·首先，最好比较相同类型的字段之间的操作.

在MySQL 3.23之前，这甚至是必要条件. 例如，不能将索引的INT字段与BIGINT字段进行比较. 但是，在特殊情况下，当CHAR类型字段和VARCHAR类型字段的字段大小相同时，可以将它们进行比较.

·其次，尝试不要对索引字段使用函数.

例如，当在DATE类型字段上使用YEAE（）函数时，索引将无法正常运行. 因此，尽管以下两个查询返回的结果相同，但后者比前者要快得多.

·第三怎样优化，在搜索字符字段时，有时会使用LIKE关键字和通配符. 尽管这种方法很简单，但也会牺牲系统性能.

例如，以下查询将比较表中的每条记录.

SELECT * FROMbooks

类似“ MySQL％”的名字

但是，如果更改为以下查询，结果将相同，但是速度会更快:

SELECT * FROMbooks

WHEREname> =“ MySQL” andname <“>

最后，应注意避免让MySQL在查询中执行自动类型转换，因为转换过程还将使索引失效.

本文来自本站，转载请注明本文网址：
http://www.pc-fly.com/a/jisuanjixue/article-288799-1.html