数据库查询性能优化

最新推荐文章于 2024-09-24 14:17:02 发布

zzhongcy

最新推荐文章于 2024-09-24 14:17:02 发布

阅读量2.8k

点赞数 1

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/zzhongcy/article/details/40342955

版权

数据库专栏收录该内容

33 篇文章 3 订阅

订阅专栏

自从自己毕业后，一直在公司做研发工作，工作期间几乎没有接触SQL。

虽然自己大学期间学习的数据库知识还不错，但是时间长了难免会遗忘。今天有空，就把回顾回顾SQL知识吧。其实很多资料都是网上查找的，这个得感谢google，虽然最近google代理服务器也被墙了，访问比较困难，但是还是相信google的搜索结果。毕竟搞技术的，不用google难道用度娘？呵呵

话归正传，下面是网上查找到的数据库优化知识，自己也当做一个笔记吧。

查询、新增、修改及删除数据库等操作是影响web应用程序性能指标的重大因素。下面一些常见的建议可以提高查询性能问题。

1.优化JDBC连接

采用数据库连接池机制可以将曾打开的数据库连接保存在缓存中，这样程序其他部分就可以继续利用，从而节省了数据库连接耗费的时间。

2.提高select子句的查询速度

（1）建立索引

若经常要通过表中的某一字段来查询数据，就可以将这个字段设置为表的一个索引。在select查询中如果发现查询的列是一个索引列，则数据库会从索引表中扫描数据，不再需要从整个数据表中扫描，性能会极大的提高。

（2）在select子句中避免使用“*”

数据库在解析的过程中, 会将“*” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。最好可以把列名一一写出。

3.避免使用耗费资源的操作

带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY的SQL语句会启动SQL引擎执行耗费资源的排序（SORT）功能。 DISTINCT需要一次排序操作，而其他的至少需要执行两次排序。GROUP BY会触发嵌入排序（NESTED SORT）；执行UNION时，唯一排序（SORT UNIQUE）操作被执行，而且它晚于嵌入排序。嵌入的排序的深度会大大影响查询的效率。

4.优化where子句来提高查询速度

（1）SQL语句用大写：因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行。

（2）WHERE子句中的连接顺序：ORACLE采用自下而上(从右到左)的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

（3）用Where子句替换HAVING子句：避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

（4）当查询多个表时，使用表的别名：可以减少解析的时间并减少那些由Column歧义引起的语法错误。

（5）用EXISTS替代IN、用NOT EXISTS替代NOT IN、用EXISTS替换DISTINCT：在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果。

（6）优化GROUP BY：为提高group by语句的效率，可在其之前先过滤不需要的记录。

（7）高效使用where子句：某些where子句不使用索引，可以替换（索引只会告诉表中内容，不能告诉表中不存在的），如用a>0 and a<0替换a!=0、a<>0,用in代替or.

参考：

http://lindianli.blog.51cto.com/7129432/1546677

######################################################################

其他网络资料

主要针对MySQL数据库的设计优化。

1 使用查询缓存

大多数的MySQL服务器都开启了查询缓存（QueryCache）。

这是提高性最有效的方法之一，查询缓存由MySQL数据库引擎自动处理。

当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，

这样，后续的相同的查询就不用操作表，而直接访问缓存结果了。

程序员很容易忽略这个功能，不经意间就会写了一些用不上缓存的SQL语句。

如下示例：

// 查询缓存不被启用
$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数据库引擎在找到一条数据后就会停止搜索，而不是继续往查找。

如下示例，我们要查找是否有“中国”的用户。

// 没有效率的：
$r = mysql_query("SELECT * FROM user WHERE country = 'China'");
if (mysql_num_rows($r) > 0) {
    // ...
}

// 有效率的：
$r = mysql_query("SELECT 1 FROM user WHERE country = 'China' LIMIT 1");
if (mysql_num_rows($r) > 0) {
    // ...
}

很明显，后面的会比前面的更有效率。

请注意，第一条中是select *，第二条是select 1。

3 使用索引

索引是提高数据库查询性能最常用的方法。

索引可以令数据库查询快得多。

尤其是在查询语句当中包含有MAX()、MIN()和ORDER BY这些命令的时候，性能提高更为明显。

那该对哪些字段建立索引呢？

一般说来，索引应建立在那些将用于JOIN、WHERE判断和ORDER BY排序的字段上。

也就是说明，如果某个字段经常用来做搜索，那么，请为其建立索引吧。

注意，尽量不要对数据库中某个含有大量重复的值的字段建立索引。

对于一个ENUM类型的字段来说，出现大量重复值是很有可能的情况，

例如：客户表中的province（省份）字段，在这样的字段上建立索引将不会有什么帮助。

相反，还有可能降低数据库的性能。

我们可以在创建表的时候同时创建合适的索引，也可以在之后使用ALTER TABLE或CREATE INDEX创建索引。

如下是LIKE条件查询有无索引的性能比较：

从上图你可以看到，搜索字串是：last_name LIKE 'a%'，创建索引的性能高处4倍左右。

另外，从MySQL5.6开始，INNODB和MyISAM存储引擎都支持全文索引和搜索。

全文索引在MySQL 中是一个FULLTEXT类型索引，可以根据实际情况使用。

需要注意的是，索引会使得INSERT和UPDATE的变慢，

所以，在需要经常更改的字段，请考虑精简索引或者分表处理。

4 EXPLAIN 查询语句

使用 EXPLAIN 关键字可以通过了解SQL语句的执行过程，来分析性能瓶颈的缘由。

EXPLAIN 的查询结果还会展示索引主键如何被利用、数据表如何被搜索和排序……等等。

挑一个你的SELECT语句（推荐挑选那个最复杂的，有多表联接的），把关键字EXPLAIN加到前面。

然后，我们会得到一张表格。

如下示例，没有为 group_id加索引的情况：

为 group_id 字段加上索引后的情况：

可以看到，前一个结果显示搜索了 7883 行，而后一个只是搜索了两个表的 9 和 16 行。

查看rows列可以让我们找到潜在的性能问题。

5 使用JOIN来代替子查询

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 (SELECT CustomerID FROM salesinfo )

如果使用连接JOIN来完成这个查询工作，速度将会快很多。

尤其是当 salesinfo 表中对 CustomerID 建有索引的话，性能将会更好。

查询如下：

SELECT * FROM customerinfo LEFT JOIN salesinfo ON customerinfo.CustomerID=salesinfo.CustomerID WHERE salesinfo.CustomerID IS NULL

连接JOIN之所以更有效率一些，是因为 JOIN 不需要在内存中创建临时表。

6 JOIN列同类型，并索引

如果你的应用程序有很多 JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。

这样，MySQL内部会为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的数据类型的。

例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。

对于那些STRING类型，还需要有相同的字符集才行。

特别是两个表，它们的字符集有可能不一样。

// 在state中查找company
// 两个 state 字段应该是被建过索引的，而且应该是相当的类型，相同的字符集。
$r = mysql_query("SELECT company_name FROM users
    LEFT JOIN companies ON (users.state = companies.state)
    WHERE users.id = $user_id");

7 使用UNION

MySQL 从 4.0 的版本开始支持 UNION 查询。

UNION查询可以把两条或更多的 SELECT 查询合并的一个查询中，这在以前是要手动创建临时表来完成的。

在客户端的会话开始和结束的时候，临时表会自动创建和删除，从而保证数据库整齐、高效。

使用 UNION 来创建查询的时候，我们只需要用 UNION 作为关键字把多个 SELECT 语句连接起来就可以了。

要注意的是，所有 SELECT 语句中的字段数目要相同。

下面的例子就演示了一个使用 UNION的查询。

SELECT name,phone FROM client UNION SELECT name,birthdate FROM author
UNION
SELECT name,supplier FROM product

8 使用事务

尽管我们可以使用子查询（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;

事务的另一个重要作用是，当多个用户同时使用相同的数据源时，

它可以利用锁定数据库的方法来为用户提供一种安全的访问方式，

这样可以保证用户的操作不被其它的用户所干扰。

9 锁定表

尽管事务是维护数据库完整性的一个非常好的方法，

但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。

由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。

如果一个数据库系统只有少数几个用户来使用，事务造成的影响不会成为一个太大的问题。

但假设有成千上万的用户同时访问一个数据库系统，

例如，访问一个电子商务网站，就会产生比较严重的响应延迟。

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

下面的例子就用锁定表的方法，来完成前面一个例子中事务的功能。

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 进行INSERT、UPDATE或者DELETE的操作。

10 使用外键

锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。

如果要保证数据的关联性，我们就可以使用外键。

例如，外键可以保证每一条销售记录都指向某一个存在的客户。

在这里，外键可以把 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类型。

该类型不是 MySQL 表的默认类型。

定义的方法是在 CREATE TABLE 语句中加上 TYPE=INNODB，如例中所示。

11 千万不要 ORDER BY RAND()

想打乱返回的数据行？随机挑一个数据？

真不知道谁发明了这种用法，但很多新手很喜欢这样用。

但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。

这样使用只让你的数据库的性能呈指数级的下降。

这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），

而且这是为了每一行记录去记行，然后再对其排序。

就算是你用了Limit 1也无济于事（因为要排序）。

下面的示例是随机挑一条记录：

// 千万不要这样做：
$r = mysql_query("SELECT username FROM user ORDER BY RAND() LIMIT 1");

// 这要会更好：
$r = mysql_query("SELECT count(*) FROM user");
$d = mysql_fetch_row($r);
$rand = mt_rand(0, $d[0] - 1);
 
$r = mysql_query("SELECT username FROM user LIMIT $rand, 1");

12 避免 SELECT *

从数据库里读出越多的数据，那么查询就会变得越慢。

并且，如果数据库服务器和WEB服务器是独立的服务器的话，这还会增加网络传输的负载。

所以，应该养成需要什么就取什么的好的习惯。

// 不推荐
$r = mysql_query("SELECT * FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";
 
// 推荐
$r = mysql_query("SELECT username FROM user WHERE user_id = 1");
$d = mysql_fetch_assoc($r);
echo "Welcome {$d['username']}";

13 永远为每张表设置一个ID

我们应该为数据库里的每张表都设置一个ID做为其主键，

而且最好的是INT型的（推荐使用UNSIGNED INT），并设置上自动增加的AUTO_INCREMENT标志。

就算是你 users 表有一个主键叫 email的字段，你也别让它成为主键。

使用 VARCHAR 类型来当主键会使用得性能下降。

另外，在你的程序中，你应该使用表的ID来构造你的数据结构。

而且，在MySQL数据引擎下，还有一些操作需要使用主键，

在这些情况下，主键的性能和设置变得非常重要，比如，集群，分区……

在这里，只有一个情况是例外，那就是“关联表”的“外键”，

也就是说，这个表的主键，通过若干个别的表的主键构成，我们把这个情况叫做“外键”。

比如：有一个“学生表”有学生的ID，有一个“课程表”有课程ID，

那么，“成绩表”就是“关联表”了，其关联了学生表和课程表，

在成绩表中，学生ID和课程ID叫“外键”其共同组成主键。

14 使用 TINYINT 而不是 VARCHAR

TINYINT类型是最大值是127，非常小巧快捷。

这样一来，用这个字段来做一些选项列表变得相当的完美。

如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或“部门”，

你知道这些字段的取值是有限而且固定的，

那么，你应该使用 TINYINT 而不是 VARCHAR。

15 别用 NULL

除非你有一个很特别的原因使用 NULL 值，不然应该总是让字段保持 NOT NULL。

这看起来好像有点争议，请往下看。

首先，问问你自己，“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？

如果你觉得它们之间没有什么区别，那么你就不要使用NULL。

（你知道吗？在 Oracle 里，NULL 和 Empty 的字符串是一样的！）

不要以为 NULL 不需要空间，其需要额外的空间。

并且，在进行比较的时候，NULL会让程序会更复杂。

当然，这里并不是说你就不能使用NULL了。

现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

下面摘自MySQL自己的文档：

“NULL columns require additional space in the row to record whether their values are NULL. For MyISAM tables, each NULL column takes one bit extra, rounded up to the nearest byte.”

16 Prepared Statements

Prepared Statements很像存储过程，是一种运行在后台的SQL语句集合。

我们可以从使用 Prepared statements 获得很多好处，无论是性能问题还是安全问题。

Prepared Statements 可以检查一些你绑定好的变量，这样可以保护你的程序不会受到“SQL注入式”攻击。

当然，你也可以手动地检查你的这些变量，然而，手动的检查容易出问题，而且很经常会被程序员忘了。

当我们使用一些framework或是ORM的时候，这样的问题会好一些。

在性能方面，当一个相同的查询被使用多次的时候，这会为你带来可观的性能优势。

你可以给这些Prepared Statements定义一些参数，而MySQL只会解析一次。

最新版本的MySQL在传输Prepared Statements是使用二进制形势，所以这会使得网络传输非常有效率。

在PHP中要使用 Prepared statements，你可以查看其使用手册：mysqli 扩展或是PDO。

// 创建 prepared statement
if ($stmt = $mysqli->prepare("SELECT username FROM user WHERE state=?")) { 

    // 绑定参数
    $stmt->bind_param("s", $state);
 
    // 执行
    $stmt->execute();
 
    // 绑定结果
    $stmt->bind_result($username);
 
    // 移动游标
    $stmt->fetch();

    printf("%s is from %s\n", $username, $state);
    $stmt->close();
}

17 无缓冲的查询

正常的情况下，当脚本中执行一个SQL语句的时候，程序会停在那里，

直到SQL语句执行完成，然程序才继续往下执行。

我们可以使用无缓冲查询来改变这个行为。

关于这个事情，在PHP的文档中有一个非常不错的说明，

mysql_unbuffered_query() 函数（新的应用应该使用PDO）：

“mysql_unbuffered_query() sends the SQL query query to MySQL without automatically fetching and buffering the result rows as mysql_query() does. This saves a considerable amount of memory with SQL queries that produce large result sets, and you can start working on the result set immediately after the first row has been retrieved as you don’t have to wait until the complete SQL query has been performed.”

上面那句话翻译过来是说，mysql_unbuffered_query() 发送一个SQL语句到MySQL。

不像mysql_query()，mysql_unbuffered_query() 不去自动fetch和缓存结果。

这会相当节约内存，尤其是那些会产生大量结果的查询语句。

并且，你不需要等到所有的结果都返回，只需要第一行数据返回的时候，你就可以开始马上开始工作于查询结果了。

不过需要注意的是，要么你把所有行都读走，要么下一次的查询前调用 mysql_free_result() 清除结果，

不然 mysql_num_rows() 或 mysql_data_seek() 将无法使用。

所以，是否使用无缓冲的查询，你需要仔细考虑。

18 把IP存成 UNSIGNED INT

很多程序员会创建一个 VARCHAR(15) 字段来存放字符串形式的IP，而不是整型的IP。

如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。

而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：where IP between ip1 and ip2。

我们必需要使用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";

19 固定长度的表会更快

如果表中的所有字段都是“固定长度”的，整个表会被认为是 “static” 或 “fixed-length”。

例如，表中没有如下类型的字段： VARCHAR、TEXT、BLOB。

只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，

这样，MySQL 引擎会用另一种方法来处理。

固定长度的表会提高性能，MySQL搜寻得会更快一些，

因为这些固定的长度很容易计算下一个数据的偏移量，所以读取的自然也会很快。

而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。

不过，唯一的副作用是，固定长度的字段会浪费一些空间，

因为无论你用不用，定长的字段都要分配那么多的空间。

使用“垂直分割”技术（见下一条），你可以分割你的表成为两个一个是定长的，一个则是不定长的。

20 垂直分割

“垂直分割”是一种把数据库中的表，按列变成几张表的方法。

这样可以降低表的复杂度和字段的数目，从而达到优化的目的。

示例一：

在Users表中有一个字段是address，它是可选字段，并且不需要经常读取或是修改。

那么，就可以把它放到另外一张表中，这样会让表有更好的性能。

很多情况下，Users表只有用户ID、用户名、口令、户角色等会被经常使用。

小一点的表总是会有好的性能。

示例二：

有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。

但是，每次更新时会导致该表的查询缓存被清空。

所以，你可以把这个字段放到另一个表中。

这样就不会影响你对用户ID、用户名、用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

另外需要注意的是，这些被分出去的字段所形成的表，不要经常性地去Join他们。

不然的话，这样的性能会比不分割时还要差。

而且，会是极数级的下降。

21 拆分大的 DELETE 或 INSERT 语句

如果在一个在线的网站上执行大的 DELETE 或 INSERT ，需要非常小心。

要避免你的操作让整个网站停止响应。

因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。

Apache/Nginx 会有很多的子进程或线程，它们在同一时间可能会有很多请求到MySQL。

而我们的服务器也不希望有太多的子进程、线程和数据库链接。

这是极大的占服务器资源的事情，尤其是内存。

如果你把你的表锁上一段时间，比如30秒钟，

那么对于一个有很高访问量的站点来说，这30秒所积累的访问进程、线程、数据库链接、打开的文件数，

可能不仅仅会让你的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);
}

22 越小的列会越快

对于大多数的数据库引擎来说，硬盘操作可能是最重大的瓶颈。

所以，把你的数据变得紧凑会对这种情况非常有帮助，因为这减少了对硬盘的访问。

参看 MySQL 的文档 Storage Requirements 查看所有的数据类型。

如果一个表只会有几行罢了（比如说字典表，配置表），那么，我们就没有理由使用 INT 来做主键，

使用 MEDIUMINT、SMALLINT 或是更小的 TINYINT 会更经济一些。

如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。

再比如，在定义邮政编码字段时，CHAR(6)就可以很好的完成任务，

如果将其设置为CHAR(255)，显然给数据库增加了不必要的空间，甚至使用VARCHAR类型也是多余的。

当然，你也需要留够足够的扩展空间。

不然，你日后来干这个事，你会死的很难看，参看Slashdot的例子（2009年11月06日），

一个简单的ALTER TABLE语句花了3个多小时，因为里面有一千六百万条数据。

23 避免数据类型自动转换

绝大多数情况下，使用索引可以提高查询的速度。

但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

我们应该注意的几个方面。

首先，最好是在相同类型的字段间进行比较的操作。

在MySQL 3.23版之前，这甚至是一个必须的条件。

例如，不能将一个建有索引的INT字段和BIGINT字段进行比较；

但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

其次，在建有索引的字段上尽量不要使用函数进行操作。

例如，在一个DATE类型的字段上使用YEAR()函数时，将会使索引不能发挥应有的作用。

所以，下面的两个查询虽然返回的结果一样，但后者要比前者快得多。

SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";

同样的情形也会发生在对数值型字段进行计算的时候：

SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;

上面的两个查询也是返回相同的结果，但后面的查询将比前面的一个快很多。

第三，在搜索字符型字段时，我们应该少用 LIKE 关键字和通配符。

因为LIKE这种做法虽然简单，但却也是以牺牲系统性能为代价的。

例如下面的查询将会比较表中的每一条记录。

SELECT * FROM books WHERE name like "MySQL%";

但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：

SELECT * FROM books WHERE name>="MySQL"and name<"MySQM";

在实际应用中，以上例子中的查询字段应使用具体的字段名列表，而不是直接用* 号，以提高查询速度。

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

24 选择正确的存储引擎

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。

甚至你只是需要update一个字段，整个表都会被锁起来，

而别的进程，就算是读进程都无法操作直到读操作完成。

另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。

但是InnoDB 支持“行锁” ，于是在写操作比较多的时候，会更优秀。

并且，他还支持更多的高级应用，比如：事务。

下面是MySQL的手册

25 使用一个对象关系映射器

使用 ORM (Object Relational Mapper)，你能够获得可靠的性能增涨。

一个ORM可以做的所有事情，也能被手动的编写出来。

但是，这需要一个高级专家。

ORM 的最重要的是“Lazy Loading”。

也就是说，只有在需要的去取值的时候才会去真正的去做。

但你也需要小心这种机制的副作用，因为这很有可能会创建很多很小的查询，这反而会降低性能。

ORM 还可以把你的SQL语句打包成一个事务，这会比单独执行他们快得多得多。

26 小心“永久链接”

“永久链接”的目的是用来减少重新创建MySQL链接的次数。

当一个链接被创建了，它会永远处在连接的状态，即使数据库操作已经结束。

这可能源于Apache/Nginx可以重用子进程的缘故。

也就是说，下一次的HTTP请求会重用WEB服务器子进程，并重用相同的 MySQL 链接。

在理论上来说，这听起来非常的不错。

但是从经验上来说，这个功能制造出来的麻烦事更多。

因为，你只有有限的链接数、内存问题、文件句柄数，等等。

而且，WEB服务器运行在极端并行的环境中，会创建很多很多的子进程。

这就是为什么这种“永久链接”的机制工作地不好的原因。

在你决定要使用“永久链接”之前，你需要好好地考虑一下你的整个系统的架构。

##############################

下面的优化方案都是基于 “ Mysql-索引-BTree类型 ” 的

一、EXPLAIN

做MySQL优化，我们要善用 EXPLAIN 查看SQL执行计划。

下面来个简单的示例，标注(1,2,3,4,5)我们要重点关注的数据

type列，连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别
key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式
key_len列，索引长度
rows列，扫描行数。该值是个预估值
extra列，详细说明。注意常见的不太友好的值有：Using filesort, Using temporary

二、SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了；再或者使用连接来替换。

三、SELECT语句务必指明字段名称

SELECT *增加很多不必要的消耗（cpu、io、内存、网络带宽）；增加了使用覆盖索引的可能性；当表结构发生改变时，前断也需要更新。所以要求直接在select后面接上字段名。

四、当只需要一条数据的时候，使用limit 1

这是为了使EXPLAIN中type列达到const类型

五、如果排序字段没有用到索引，就尽量少排序

六、如果限制条件中其他字段没有索引，尽量少用or

or两边的字段中，如果有一个不是索引字段，而其他条件也不是索引字段，会造成该查询不走索引的情况。很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

七、尽量用union all代替union

union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的CPU运算，加大资源消耗及延迟。当然，union all的前提条件是两个结果集没有重复数据。

八、不使用ORDER BY RAND()

select id from `dynamic` order by rand() limit 1000;

上面的sql语句，可优化为

select id from `dynamic` t1 join (select  rand() * (select max(id) from `dynamic`) as nid) t2 on t1.id > t2.nid limit 1000;

九、区分in和exists， not in和not exists

select * from 表A where id in (select id from 表B)

上面sql语句相当于

select * from 表A where exists(select * from 表B where 表B.id=表A.id)

区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
关于not in和not exists，推荐使用not exists，不仅仅是效率问题，not in可能存在逻辑问题。如何高效的写出一个替代not exists的sql语句？

原sql语句

select colname …  from A表 where a.id not in (select b.id from B表)

高效的sql语句

select colname …  from A表 Left join B表 on where a.id = b.id where b.id is null

取出的结果集如下图表示，A表不在B表中的数据

十、使用合理的分页方式以提高分页的效率

select id,name from product limit 866613, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql可以采用如下的写法：

select id,name from product where id> 866612 limit 20

十一、分段查询

在一些用户选择页面中，可能一些用户选择的时间范围过大，造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序，分段进行查询，循环遍历，将结果合并处理进行展示。

如下图这个sql语句，扫描的行数成百万级以上的时候就可以使用分段查询

十二、避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

十三、不建议使用%前缀模糊查询

例如LIKE “%name”或者LIKE “%name%”，这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

那如何查询%name%？

如下图所示，虽然给secret字段添加了索引，但在explain结果果并没有使用

那么如何解决这个问题呢，答案：使用全文索引

在我们查询中经常会用到select id,fnum,fdst from dynamic_201606 where user_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

创建全文索引的sql语法是：

ALTER TABLE `dynamic_201606` ADD  FULLTEXT INDEX  `idx_user_name` (`user_name`);

使用全文索引的sql语句是：

select id,fnum,fdst from dynamic_201606 where match(user_name) against('zhangsan' in boolean mode);

注意：在需要创建全文索引之前，请联系DBA确定能否创建。同时需要注意的是查询语句的写法与普通索引的区别

十四、避免在where子句中对字段进行表达式操作

比如

select user_id,user_project from user_base where age*2=36;

中对字段就行了算术运算，这会造成引擎放弃使用索引，建议改成

select user_id,user_project from user_base where age=36/2;

十五、避免隐式类型转换

where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型

十六、对于联合索引来说，要遵守最左前缀法则

举列来说索引含有字段id,name,school，可以直接用id字段，也可以id,name这样的顺序，但是name;school都无法使用这个索引。所以在创建联合索引的时候一定要注意索引字段顺序，常用的查询字段放在最前面

十七、必要时可以使用force index来强制查询走某个索引

有的时候MySQL优化器采取它认为合适的索引来检索sql语句，但是可能它所采用的索引并不是我们想要的。这时就可以采用force index来强制优化器使用我们制定的索引。

十八、注意范围查询语句

对于联合索引来说，如果存在范围查询，比如between,>,<等条件时，会造成后面的索引字段失效。

十九、关于JOIN优化

LEFT JOIN A表为驱动表
INNER JOIN MySQL会自动找出那个数据少的表作用驱动表
RIGHT JOIN B表为驱动表

注意：MySQL中没有full join，可以用以下方式来解决

select * from A left join B on B.name = A.name 
where B.name is null
 union all
select * from B;

尽量使用inner join，避免left join

参与联合查询的表至少为2张表，一般都存在大小之分。如果连接方式是inner join，在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表，但是left join在驱动表的选择上遵循的是左边驱动右边的原则，即left join左边的表名为驱动表。

合理利用索引

被驱动表的索引字段作为on的限制字段。

利用小表去驱动大表

从原理图能够直观的看出如果能够减少驱动表的话，减少嵌套循环中的循环次数，以减少 IO总量及CPU运算的次数。

巧用STRAIGHT_JOIN

inner join是由mysql选择驱动表，但是有些特殊情况需要选择另个表作为驱动表，比如有group by、order by等「Using filesort」、「Using temporary」时。STRAIGHT_JOIN来强制连接顺序，在STRAIGHT_JOIN左边的表名就是驱动表，右边则是被驱动表。在使用STRAIGHT_JOIN有个前提条件是该查询是内连接，也就是inner join。其他链接不推荐使用STRAIGHT_JOIN，否则可能造成查询结果不准确。