sql优化方案讲解

Sql优化方案

一．数据库优化技术

1.索引（强烈建议使用）

1.1优点

创建索引可以大大提高系统的性能。

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的

性能。

1.2 缺点

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这

样就降低了数据的维护速度。

1.3 使用准则

索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

一般来说，应该在这些列上创建索引。

第一，在经常需要搜索的列上，可以加快搜索的速度；

第二，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

第三，在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

第四，在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

第五，在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

第六，在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由

于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能

和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大

于检索性能时，不应该创建索引。

1.4 总结

1）索引提高了数据库的检索性能，但一定程度上牺牲了修改性能。因此适用于

“多查询少修改”（insert，update，delete）的表。

2）对此类表中的外键，需要分组，排序或作为检索条件的字段建立索引

3）对此类表中查询使用少，字段取值少，字段数据量大的不应创建索引

2.数据库设计标准化

2.1 标准化

标准化是在数据库中组织数据的过程。其中包括，根据设计规则创建表并在这些表间建立关系。

标准化的特点：

1)所有的“对象”都在它自己的table中，没有冗余。

2)简洁，更新属性通常只需要更新很少的记录。

3)Join操作比较耗时。

4)Select，sort优化措施比较少。

6)适用于OLTP应用（实时的增删改查系统）。

2.2 非标准化

1) 在一张表中存储很多数据，数据冗余。

2) 更新数据开销很大，更新一个属性可能会更新很多表，很多记录。

3) 在删除数据是有可能丢失数据。

4) Select，order有很多优化的选择。

5) 适用于DSS应用。

2.3 总结

标准化适用于“多修改少查询”的表。提升了修改性能，但查询时通常需要join链接，检索慢

非标准化适用于“少修改多查询”的表。减少了join链接，提升了检索性能，但修改代价大。（或者说放弃数据一致性，仅修改主表？）

3.数据类型

最基本的优化之一就是使表在磁盘上占据的空间尽可能小。这能带来性能非常大的提升，因为数据小，磁盘读入较快，并且在查询过程中表内容被处理所占

用的内存更少。同时，在更小的列上建索引，索引也会占用更少的资源。

可以使用下面的技术可以使表的性能更好并且使存储空间最小：

1) 使用正确合适的类型，不要将数字存储为字符串。

2) 尽可能地使用最有效(最小)的数据类型。MySQL有很多节省磁盘空间和内

存的专业化类型。

3) 尽可能使用较小的整数类型使表更小。例如，MEDIUMINT经常比INT好

一些，因为MEDIUMINT列使用的空间要少25%。

4) 如果可能，声明列为NOT NULL。它使任何事情更快而且每列可以节省一

位。注意如果在应用程序中确实需要NULL，应该毫无疑问使用它，只是避免默认地在所有列上有它。

5) 对于MyISAM表，如果没有任何变长列(VARCHAR、TEXT或BLOB列)，使

用固定尺寸的记录格式。这比较快但是不幸地可能会浪费一些空间。即使你已经用CREATE选项让VARCHAR列ROW_FORMAT=fixed，也可以提示想使用固定长度的行。

6) 使用sample character set，例如latin1。尽量少使用utf-8，因为utf-8占用

的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1，例如mail，url等。

4.存储引擎

4.1 MyISAM特点

1) 不支持事务，宕机会破坏表

2) 使用较小的内存和磁盘空间

3) 基于表的锁，并发更新数据会出现严重性能问题

4) MySQL只缓存Index，数据由OS缓存

4.2 InnoDB特点

1) 支持事务，ACID，外键。

2) Row level locks。

3) 支持不同的隔离级别。

4) 和MyISAM相比需要较多的内存和磁盘空间。

5) 没有键压缩。

6) 数据和索引都缓存在内存hash表中。

总结

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。因为写操作会使整个表被锁起来，而别的进程，就算是读进程都无法操作直到写操作完成。另外，MyISAM 对于SELECT COUNT(*) 这类的计算是超快无比的。

InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比MyISAM 还慢。他是它支持“行锁”　，于是在写操作比较多的时候，会更优秀。

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

5.数据库服务器缓存

大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一，

而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被执行了多次的时候，这些查询结果会被放到一个缓存中，这样，后续的相同的查询就不用操作表而直接访问缓存结果了。

但是注意对于CURDATE()，NOW() 和 RAND()或是其它的诸如此类的SQL函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。

开启方法：

检测是否开启成功

6.字符集

使用sample character set，例如latin1。尽量少使用utf-8，因为utf-8占用的空间是latin1的3倍。可以在不需要使用utf-8的字段上面使用latin1，例如mail，url等。

这种优化的基本思想是：从磁盘到内存的io是数据库查询最为耗时的操作之一，通过压缩数据存储，减少读入内存的数据量，从而提高检索性能。

注意事项：

SHOW VARIABLES LIKE 'character%'

SHOW VARIABLES LIKE 'collation_%';

a、要保证数据库中存的数据与数据库编码一致，即数据编码与character_set_database一致；

b、要保证通讯的字符集与数据库的字符集一致，即character_set_client, character_set_connection与character_set_database一致；

c、要保证SELECT的返回与程序的编码一致，即character_set_results

与程序编码一致；

d、要保证程序编码与浏览器、终端编码一致

7.存储过程

优点：

(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。

(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已

经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，

因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改

动，而这些改动不会对应用程序造成影响。

(4).安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

缺点：

1. 运行速度：大多数高级的数据库系统都有statement cache的，所

以编译sql的花费没什么影响。但是执行存储过程要比直接执行sql花费更多（检查权限等），所以对于很简单的sql，存储过程没有什么优势。

2. 网络负荷：如果在存储过程中没有多次数据交互，那么实际上网络传

输量和直接sql是一样的。

3. 团队开发：很遗憾，比起成熟的IDE，没有什么很好存储过程的IDE

工具来支持，也就是说，这些必须手工完成。

4. 安全机制：对于传统的C/S结构，连接数据库的用户可以不同，所以

安全机制有用；但是在web的三层架构中，数据库用户不是给用户用的，所以基本上，只有一个用户，拥有所有权限（最多还有一个开发用户）。这个时候，安全机制有点多余。

5. 用户满意：实际上这个只是要将访问数据库的接口统一，是用存储过

程，还是EJB，没太大关系，也就是说，在三层结构中，单独设计出一个数

据访问层，同样能实现这个目标。

6. 开发调试：一样由于IDE的问题，存储过程的开发调试要比一般程序

困难（老版本DB2还只能用C写存储过程，更是一个灾难）。

7. 移植性：算了，这个不用提，反正一般的应用总是绑定某个数据库的，

不然就无法靠优化数据库访问来提高性能了。

8. 维护性：的确，存储过程有些时候比程序容易维护，这是因为可以实

时更新DB端的存储过程，但是在3层结构下，更新server端的数据访问层一样能实现这个目标，可惜现在很多平台不支持实时更新而已。

总结：

所有数据访问在应用层封装为数据访问层，在那里，如果SQL简单的话，直接用SQL；如果SQL复杂，或者数据交互多且中间数据最后不会用到，使用存储

过程。

8.视图

简单性。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。

安全性。通过视图用户只能查询和修改他们所能见到的数据。

逻辑数据独立性。视图可以使应用程序和数据库表在一定程度上独立。

但是需要注意，仅对于使用MERGE算法处理的视图，可以使用索引。但是，对于使用临时表算法处理的视图，不能在其基表上利用索引提供的优点（尽管能够在临时表的生成过程中使用索引）。

MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果不具有

该关系。必须使用临时表取而代之。如果视图包含下述结构中的任何一种，将失去一对一的关系：

聚合函数（SUM(),MIN(),MAX(),COUNT()等）。

DISTINCT

GROUP BY

HAVING

UNION或UNION ALL

仅引用文字值（在该情况下，没有基本表）。

9.其它技巧

9.1 使用limit

检索数据库时，当我们已知或可以设定查询结果数量的情况下，加上limit 可以增加性能。因为数据库不需要去fetch游标，或是去检查返回的记录数。这

样一来，MySQL数据库引擎会在找到相应数量的数据后停止搜索，而不是继续

往后查少下一条符合记录的数据。

9.2 join表使用相同类型的字段

如果你的应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把

DECIMAL 字段和一个INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

9.3 避免order by RAND()

想打乱返回的数据行？随机挑一个数据？真不知道谁发明了这种用法，但很多新手很喜欢这样用。但你确不了解这样做有多么可怕的性能问题。

如果你真的想把返回的数据行打乱了，你有N种方法可以达到这个目的。这样使用只让你的数据库的性能呈指数级的下降。这里的问题是：MySQL会不得不去执行RAND()函数（很耗CPU时间），而且这是为了每一行记录去记行，然后再对其排序。就算是你用了Limit 1也无济于事（因为要排序）。

9.4避免SELECT *

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

库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

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

9.5 enum使用(不使用)

ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表

上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。

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

门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不

是 VARCHAR。

MySQL也有一个“建议”（见第十条）告诉你怎么去重新组织你的表结构。

当你有一个 VARCHAR 字段时，这个建议会告诉你把其改成 ENUM 类型。使用

你可以得到相关的建议。

PROCEDURE ANALYSE()

但是注意如果枚举类型要慎用，存储数字时，枚举值和枚举索引会发生冲突，

不易于使用。且使用枚举类型后，后期修改取值范围或者迁移数据库都非常不方便。

9.6 使用PROCEDURE ANALYSE() 取得建议

会让 MySQL 帮你去分析你的字段和其实际的数据，PROCEDURE ANALYSE()

并会给你一些有用的建议。只有表中有实际的数据，这些建议才会变得有用，因

为要做一些大的决定是需要有数据作为基础的。

例如，如果你创建了一个 INT 字段作为你的主键，然而并没有太多的数据，

ANALYSE()会建议你把这个字段的类型改成 MEDIUMINT 。或是那么，PROCEDURE

你使用了一个 VARCHAR 字段，因为数据不多，你可能会得到一个让你把它改成

ENUM 的建议。这些建议，都是可能因为数据不够多，所以决策做得就不够准。

一定要注意，这些只是建议，只有当你的表里的数据越来越多时，这些建议

才会变得准确。一定要记住，你才是最终做决定的人。

9.7 尽可能的使用not null

除非你有一个很特别的原因去使用 NULL 值，你应该总是让你的字段保持NOT NULL。这看起来好像有点争议，请往下看。

首先，问问你自己“Empty”和“NULL”有多大的区别（如果是INT，那就是0和NULL）？如果你觉得它们之间没有什么区别，那么你就不要使用NULL。（你知道吗？在 Oracle 里，NULL 和 Empty 的字符串是一样的！)

不要以为 NULL 不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

9.8 垂直分割

“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）。

示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。

示例二：你有一个叫“last_login”的字段，它会在每次用户登录时被

更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字

段放到另一个表中，这样就不会影响你对用户 ID，用户名，用户角色的不停地

读取了，因为查询缓存会帮你增加很多性能。

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

去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级

的下降。

9.9 使用定长字段

定长字段提高数据库的处理速度，不定长字段降低物理上的存储空间

举个例子，比如有两个类型，char（10）和varchar2(10)

char（10）在存储的时候直接就分配了10个字节的空间，而varchar2(10)

则是给你预留了10个字节的空间，但是实际存储的大小依据你输入的数据值占

用的大小（不能超过10）。

这时候你分别对char（10）和varchar2(10)查询时，对于char，数据库不用过多考虑，直接以10个字节的内容进行读取，但到了varchar2时候，就需要分析了，因为可能这个字段下每个值的大小都不一样。

如果表中的所有字段都是“固定长度”的，整个表会被认为是“static”　或“fixed-length”。例如，表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。

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

长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。

并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度

的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的

空间。

使用“垂直分割”技术，你可以分割你的表成为两个一个是定长的，一个则

是不定长的。

二．数据库优化实施

数据库的优化由粗到细，先从大的方面优化，再逐步细化，优化顺序如下：

数据库-->表—>字段-》视图-》存储过程

具体优化实施如下：

1.启用数据库服务器缓存

2.设置数据库的字符集为latin

3.垂直分割，将数据库中的大表拆成小表

4.设置数据库引擎

1）分析表的操作情况，是修改多还是查询多

2）分析该表有用到事务否

3）对于多修改或有事务的表使用InnoDB，其余使用MyISAM

5.检查sql语句

1）对复杂的sql语句使用存储过程

2）确定select语句实际使用的字段，建立视图,并去掉select *（注：非1对1关系的不建立视图）

3）使用EXPLAIN分析sql语句，对常用字段添加索引

4）对明确返回结果条数的select语句添加limit约束

6．调整数据库字段数据类型

1）能用int的尽量不使用字符串

分析表结构，根据反馈的意见修改表中字2）使用PROCEDURE ANALYSE

段的数据类型

3）修改varchar字段为char类型

4）对于能不为空的字段都设置not null