MySQL性能优化

最新推荐文章于 2022-09-27 23:04:45 发布

u010832551

最新推荐文章于 2022-09-27 23:04:45 发布

阅读量532

点赞数 1

分类专栏： mysql

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

1、使用查询缓存

大多数的MySQL服务器都开启了查询缓存（QueryCache），查询缓存由MySQL数据库引擎自动处理。当有很多相同的查询被执行了多次时，这些查询结果会被放到一个缓存中，后续的相同的查询就不用操作表，而直接访问缓存结果。

// 查询缓存不被启用
$r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");

// 查询缓存被启用
$today = date("Y-m-d");
$r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

上面两条SQL语句差别在于CURDATE()，MySQL的查询缓存对这个函数不起作用。像NOW()、RAND()或其他此类SQL函数都不会开启查询缓存，因为这些函数返回时变数。

2、为查询加上LIMIT

即使确定查询表只会有1条结果，加上LIMIT 1可以增加性能。因为这样加上LIMIT后，MySQL数据库引擎在找到一条数据后就会停止搜索，不再继续查找。

3、使用索引

索引是提高数据库查询性能的最常用方法，它可以令数据库查询快得多，尤其在查询语句中包含有MAX()、MIN()和ORDER BY时，性能提高更为明显。

一般来说，索引应建立在那些将用于JOIN、WHERE判断和ORDER BY排序的字段上，也就是如果某个字段经常用来做搜索，则应该为其建立索引。尽量不要对数据库中含有大量重复的值的字段建立索引。

从MySQL5.6开始，Innodb和MyISAM存储引擎都支持全文索引和搜索。全文索引在MySQL中是一个FULLTEXT类型索引，可以根据实际情况使用。需要注意的是索引会使得INSERT和UPDATE变慢，所以需要经常更改的字段请考虑精简索引或分表处理。

4、EXPLAIN查询语句

使用EXPLAIN关键字可以通过了解SQL语句的执行过程，来分析性能瓶颈的缘由。EXPLAIN的查询结果还会展示索引主键如何被利用、数据表如何被搜索和排序...等等。

5、使用JOIN来代替子查询

子查询是使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。子查询可以被更有效率的连接JOIN替代。这是因为JOIN不需要在内存中创建临时表。

如果在应用中有很多JOIN查询，应该确认两个表中JOIN的字段是被建立过索引的，这样MySQL内部会为你优化JOIN的SQL语句的机制。而且这些被用来JOIN的字段，应该是相同的数据类型的。

6、使用UNION

UNION查询可以把两条或更多的SELECT查询合并到一个查询中，这在以前是需要手动创建临时表来完成的。在客户端的会话开始和结束的时候，临时表会自动创建和删除，从而保证数据库整齐、高效。使用UNION来创建查询时，只需要使用UNION作为关键字把多个SELECT语句连接起来，所有的SELECT语句中的字段数目要相同。

7、使用事务

使用事务的作用是：要么语句块中每条语句都操作成功，要么都失败，即它可以保证数据库中数据的一致性和完整性。

事务以BEGIN关键字开始，COMMIT关键字结束，执行完成前，只要有一条SQL操作失败，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。另外当多个用户同时使用相同的数据源时，它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，这样可以保证用户操作不被其他用户所干扰。

8、锁定表

尽管事务是维护数据库完整性的一个很好的方法，但是却是因为它的独占性，有时会影响数据库的性能，尤其在大型的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其他的用户请求只能暂时等待直到该事务结束。在这种情况下可以通过锁定表的方法可以来获取更好的性能。

LOCK TABLE inventory WRITE
    SELECT Quantity FROM inventory WHERE Item='book';
    ...
    UPDATE inventory SET Quantity=11 WHERE Item='book';
UNLOCK TABLES

这里包含有WRITE关键字的LOCK TABLE语句，保证在UNLOCK TABLES命令被执行之前，不会有其他的访问来对表inventory进行INSERT、UPDATE或者DELETE的操作。

9、使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。如果要保证数据的关联性，就需要使用外键。

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

CREATE TABLE customerinfo
(
    CustomerID INT NOT NULL ,
    PRIMARY KEY ( CustomerID )
) TYPE = INNODB;

CREATE TABLE salesinfo
(
    SalesID INT NOT NULL,
    CustomerID INT NOT NULL,
    PRIMARY KEY(CustomerID, SalesID),
    FOREIGN KEY (CustomerID) REFERENCES customerinfo
    (CustomerID) ON DELETECASCADE
) TYPE = INNODB;

其中参数ON DELETE CASCADE是保证当customerinfo表中一条客户记录被删除时，salesinfo表中所有与该客户相关的记录会被自动删除。如果要在MySQL中使用外键，则要将表的类型定义为事务安全表InnoDB类型。

10、不要使用ORDER BY RAND()

这样会使得数据库的性能呈指数级地下降。同时从数据库里读出越多数据，则查询就会变得越慢，所以要避免使用SELECT *

11、为每张表设置一个ID

为数据库中的每张表都设置一个ID作为其主键，最好是INT类型，推荐使用UNSIGNED INT，并设置上自动增加的AUTO_INCREMENT标识。

12、Prepared Statements

Prepared Statements是一种运行在后台的SQL语句集合，它可以检查一些你绑定好的变量，可以保护你的程序不会受到"SQL注入式"攻击。在性能方面，当一个相同的查询被使用多次时，它会带来可观的性能优势。

13、把IP存储成UNSIGNED INT

用整形来存放，只需要4个字节，并且可以有定长的字段，我们必须要使用UNSIGNED INT，因为IP地址会使用整个32位的无符号整型。SQL查询中可以使用INET_ATON()来把一个字符串IP转成一个整型，使用INET_NTOA()把一个整型转成一个字符串IP。

PHP中对应的函数：ip2long()和long2ip()。

$r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

14、固定长度的表会更快

如果表中的所有字段都是固定长度，整个表都会被认为是static的。如表中没有如下类型的字段：VARCHAR、TEXT、BLOB。

固定长度的表会提高性能，因为固定长度很容易计算下一个数据的偏移量，所以读取得快。而对于字段不定长的，每一次要找下一条的都需要程序找到主键。并且固定长度的表更容易被缓存和重建，唯一不足的是，固定长度的字段会浪费一些空间。

15、垂直分割

垂直分割是一种将数据库中的表，按列变成几张表的方法。这样可以降低表的复杂度和字段的数目，从而达到优化的目的。

16、拆分大的DELETE或INSERT语句

DELETE和INSERT这两个操作是会锁表的，表一锁住了，别的操作都进不来了。Apache/Nginx会有很多的子进程或线程，它们在同一时间可能会有很多请求到MySQL。我们的服务器并不希望有太多的子进程、线程和数据库链接。因为这是极大地占服务器资源的，尤其是内存。当表锁上一段时间，对于一个有很高访问量的站点来说，这段时间所累积的访问进程、线程、数据库链接、打开的文件数可能不仅让Web服务Crash，而且也可能让整台服务器挂掉。

所以有一个大的处理，一定要将其拆分，使用LIMIT条件是一个好的方法：

while (1) {
    //每次只做1000条
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了，退出！
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

17、越小的列会越快

对于大多数数据库引擎来说，硬盘操作是可能最大的瓶颈。所以将数据变得紧凑会对这种情况很有帮助，因为这减少了对磁盘的访问。

18、避免数据类型自动转换

在绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当，索引将无法发挥其应有的作用。

1）在相同类型的字段间进行比较操作

2）在建有索引的字段上尽量不要使用函数进行操作或进行对数值型字段进行计算

3）在搜索字符型字段时，应该少用LIKE关键字和通配符

所以应该避免在查询中让MySQL进行自动类型转换，因为在转换过程中会使得索引变得不起作用