欢迎各位兄弟 发布技术文章

这里的技术是共享的

You are here

如何进行MySQL的性能优化

  1. MySQL数据表字段要选取合适的字段属性

    MySQL可以很好支撑大数据量的存取,但是一般说来,数据库中的表越小在它上面执行的查询也就会越快。因此在创建表可以的时候为了获取更好的性能,我们可以将表中的字段宽度设得尽可能小。

    例如,定义邮政编码这个字段如果设置为CHAR(256)或者VARCHAR都会增加数据库的空间,因为设置为CHAR(6)就可以。

    另外一个提高效率的方法是在可能的情况下,应该尽量吧字段设置为Not Null,这样在将来执行查询的时候,数据库不用去比较Null值。

    对于某些文本字段,例如省份或者性别,我们可以将它们定义为ENUM类型。因为在MySQL中Enum类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快的多。

    如何进行MySQL的性能优化
  2. 2

    使用连接Join来代替字查询(sub-Query)

    MySQL可以使用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 salesinfo on customerinfo.customerID=salesinfo.CustomerID where salesinfo.CustomerID is Null

    连接join操作之所以更有效率一些,是因为mysql不需要再内存中创建临时表来完成这个逻辑上需要两个步骤的工作。

    如何进行MySQL的性能优化
  3. 3

    使用联合Union来代替手动创建临时表

    在MySQL中联合Unio操作可以吧需要使用临时表的两条或更多select查询合并到一个查询中。在客户端查询会话结束的时候,临时表会被自动删除,从而保证数据库整齐、高效。使用union来创建查询的时候,我们需要用union作为关键字把多个select语句连接起来.

    例如下面语句:

    select name,phone from client 

    UNION

    select name,birthDate from author

    UNION

    select name,supplier from product;

    如何进行MySQL的性能优化
  4. 4

    使用事务机制来完成需要多个SQL操作的数据处理,保证数据的完整性和安全访问。

    事务一begin关键字开始,commit关键字结束,rollback命令可以把数据库恢复到begin开始之前的状态。

    例如以下语句:

    BEGIN;

    insert into salesinfo set  customerID =14;

    update inventory set quantity =12 where item ='book';

    COMMIT;

    事务的另一个重要作用就当多个用户同时使用相同的数据源时,他可以利用锁定数据库的方法来为用户提供一种安全的访问方式,这样可以保证用户的操作不被其他用户所干扰。

    如何进行MySQL的性能优化
  5. 5

    如果情况允许锁定表的方式会比采用事务的方式拥有更好的性能。

    尽管事务时维护数据库完整性的一个非常好的方法,但却因为它的独占性,有时候会影响数据库的性能,尤其是在很大的应用系统中。由于在执行事务的过程中,数据库将会被锁定,因此其他的用户请求只能暂时等待知道该事务结束。如果一个数据库系统只有少数几个用户来使用,事务造成的影响不会成为一个太大的问题,单假设有成千上万用户同时访问一个数据库系统,例如访问一个电子商务网站,就会产生比较严重的响应延时。

    其实有些情况下,可以通过锁定表的方法来获得更好的性能。

    如下例子:

    Lock Table inventory write;

    select quantity from inventory where item ='book';

    ...

    Update inventory set quantity =11 where item ='book';

    Unlock tables;

    这里我们用一个select语句取出原始数据,通过一些计算,用update语句将心值更新到表中。包含write关键字的lock table可以保证unlock tables命令执行之前不会有其他的访问来对inventory进行插入、更新或者删除的操作。

    如何进行MySQL的性能优化
  6. 6

    使用外键来优化关联表的性能

    外键可以用来保证数据的关联性。

    例如外键可以保证每一条销售记录都指向某一个存在的客户。在这里,外键可以吧customerinfo表中的customerID映射到salesinfo表中的CustomerID,任何一条没有合法CustomerID的记录都不会更新或插入到salesinfo中。

    CREATE TABLE customerinfo(

      customerId INT NOT NULL,

      PRIMARY KEY(customerID)

    ) ENGINE = INNODB;

    CREATE TABLE salesinfo(

      salesId INT NOT NULL,

      customerId INT NOT NULL,

      PRIMARY KEY(customerID, SalesId),

      FOREIGN KEY(customerID) REFERENCES customerinfo(customerID) ON DELETE CASCADE

    ) ENGINE = INNODB;

    注意这个例子中的参数“on delete cascade”,该参数保证当customerinfo表中的一条记录被删除时,salesinfo表中所有与该客户相关的记录会被自动删除。如果要做mysql中使用外键,一定要记住在创建表的时候将表的类型定义为事务安全表innodb类型,该类型不是mysql的默认表类型,定义的方法是在create table语句中加上ENGINE =INNODB.

    如何进行MySQL的性能优化
  7. 7

    使用索引提高MySQL数据库的性能。

    索引是提高数据库性能的常用方法,它可以令数据库服务器以比没有索引快得多的速度检索特定的行,尤其是在查询语句当中包含MAX(),MIN()和ORDER BY这些命令的时候,性能提高更为明显。

    那该对那些字段建立索引呢?一般索引应建立在那些用于JOIN,WHERE判断和ORDER BY排序的字段上,尽量不要对数据库中某个含有大量重复的值得字段建立索引。

    对于ENUM类型的字段来说,出现大量重复值是很有可能的情况,所以在这种字段不要建立索引,例如,customerinfo表中的province枚举型字段,在这样的字段建立索引将不会有什么帮助,还有可能降低数据库的性能。

  8. 8

    通过优化查询语句提高查询速度。

    多数情况下索引可以提高查询速度,单如果SQL语句使用不恰当的话,索引将无法发挥他应有的作用。

    1)最好在相同类型的字段间进行比较

    2)在建有索引的字段上尽量不要使用函数操作

    例如:在一个Date类型的字段上使用year()函数时,将会使索引不能发挥作用,下面两个语句后者要比前者快。

    select * from order where year(orderDate)<2001;

    select * from order where orderDate <"2001-01-01";

    3)在搜索字符型字段时我们会使用like关键字节通配符,这种做法虽然简单,但却也是以牺牲系统性能为代价的。

    例如以下两个语句后者性能要比前者好:

    select * from books where name like "%mySQL%";

    select * from books where name>="mySQL" and name =<"mySQL";

    4) 应该注意避免在查询中让mysql进行自动类型转换,因为转换过程会使索引失效。

    END
经验内容仅供参考,如果您需解决具体问题(尤其法律、医学等领域),建议您详细咨询相关领域专业人士。
举报作者声明:本篇经验系本人依照真实经历原创,未经许可,谢绝转载。



来自  https://jingyan.baidu.com/article/b2c186c836e65ec46ef6ffb2.html

普通分类: