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.视图简单性。