MySQL数据库优化的⼋种⽅式（经典必看）引⾔：
关于数据库优化，⽹上有不少资料和⽅法，但是不少质量参差不齐，有些总结的不够到位，内容冗杂
偶尔发现了这篇⽂章，总结得很经典，⽂章流量也很⼤，所以拿到⾃⼰的总结⽂集中，积累优质⽂章，提升个⼈能⼒，希望对⼤家今后开发中也有帮助
1、选取最适⽤的字段属性
MySQL可以很好的⽀持⼤数据量的存取，但是⼀般说来，数据库中的表越⼩，在它上⾯执⾏的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能⼩。
例如，在定义这个字段时，如果将其设置为CHAR(255)，显然给数据库增加了不必要的空间，甚⾄使⽤VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的，如果可以的话，我们应该使⽤MEDIUMINT⽽不是BIGIN来定义整型字段。
另外⼀个提⾼效率的⽅法是在可能的情况下，应该尽量把字段设置为NOTNULL，这样在将来执⾏查询的时候，数据库不⽤去⽐较NULL 值。
对于某些⽂本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，EN
UM类型被当作数值型数据来处理，⽽数值型数据被处理起来的速度要⽐⽂本类型快得多。这样，我们⼜可以提⾼数据库的性能。
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 (SELECTC ustomerID FROM salesinfo)
如果使⽤连接（JOIN）..来完成这个查询⼯作，速度将会快很多。尤其是当salesinfo表中对CustomerID建有索引的话，性能将会更好，查询如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID ISNULL
连接（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;
sql语句优化方式
事务的另⼀个重要作⽤是当多个⽤户同时使⽤相同的数据源时，它可以利⽤锁定数据库的⽅法来为⽤户提供⼀种安全的访问⽅式，这样可以保证⽤户的操作不被其它的⽤户所⼲扰。
5、锁定表
尽管事务是维护数据库完整性的⼀个⾮常好的⽅法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很⼤的应⽤系统中。由于在事务执⾏的过程中，数据库将会被锁定，因此其它的⽤户请求只能暂时等待直到该事务结束。如果⼀个数据库系统只有少数⼏个⽤户来使⽤，事务造成的影响不会成为⼀个太⼤的问题；但假设有成千上万的⽤户同时访问⼀个数据库系统，例如访问⼀个电⼦商务⽹站，就会产⽣⽐较严重的响应延迟。
其实，有些情况下我们可以通过锁定表的⽅法来获得更好的性能。下⾯的例⼦就⽤锁定表的⽅法来完成前⾯⼀个例⼦中事务的功能。
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进⾏插⼊、更新或者删除的操作。
6、使⽤外键
锁定表的⽅法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使⽤外键。
例如，外键可以保证每⼀条销售记录都指向某⼀个存在的客户。在这⾥，外键可以把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。如例中所⽰。
7、使⽤索引
索引是提⾼数据库性能的常⽤⽅法，它可以令数据库服务器以⽐没有索引快得多的速度检索特定的⾏，尤其是在查询语句当中包含有
MAX(),MIN()和ORDERBY这些命令的时候，性能提⾼更为明显。
那该对哪些字段建⽴索引呢？
⼀般说来，索引应建⽴在那些将⽤于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有⼤量重复的值的字段建⽴索引。对于⼀个ENUM类型的字段来说，出现⼤量重复值是很有可能的情况
例如customerinfo中的“province”..字段，在这样的字段上建⽴索引将不会有什么帮助；相反，还有可能降低数据库的性能。我们在创建表的时候可以同时创建合适的索引，也可以使⽤ALTERTABLE或CREATEINDEX在以后创建索引。此外，MySQL从版本3.23.23开始⽀持全⽂索引和搜索。全⽂索引在MySQL中是⼀个FULLTEXT类型索引，但仅能⽤于MyISAM类型的表。对于⼀个⼤的数据库，将数据装载到⼀个没有FULLTEXT索引的表中，然后再使⽤ALTERTABLE或CREATEINDEX创建索引，将是⾮常快的。但如果将数据装载到⼀个已经有FULLTEXT索引的表中，执⾏过程将会⾮常慢。
8、优化的查询语句
绝⼤多数情况下，使⽤索引可以提⾼查询的速度，但如果SQL语句使⽤不恰当的话，索引将⽆法发挥它应有的作⽤。
下⾯是应该注意的⼏个⽅⾯。
⾸先，最好是在相同类型的字段间进⾏⽐较的操作。
在MySQL3.23版之前，这甚⾄是⼀个必须的条件。例如不能将⼀个建有索引的INT字段和BIGINT字段进⾏⽐较；但是作为
特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段⼤⼩相同的时候，可以将它们进⾏⽐较。
其次，在建有索引的字段上尽量不要使⽤函数进⾏操作。
例如，在⼀个DATE类型的字段上使⽤YEAE()函数时，将会使索引不能发挥应有的作⽤。所以，下⾯的两个查询虽然返回的结果⼀样，但后者要⽐前者快得多。
第三，在搜索字符型字段时，我们有时会使⽤LIKE关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。
例如下⾯的查询将会⽐较表中的每⼀条记录。
SELECT    *    FROM    books
WHERE    name    like"MySQL%"
但是如果换⽤下⾯的查询，返回的结果⼀样，但速度就要快上很多：
SELECT    *    FROM    books
WHERE    name＞="MySQL"    andname    ＜"MySQM"
最后，应该注意避免在查询中让MySQL进⾏⾃动类型转换，因为转换过程也会使索引变得不起作⽤。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。