SQL数据库怎么进行优化_SQL数据库有什么优化方式

　　优化SQLServer数据库的一些经验和注意事项，详细介绍了SQL语句优化的基本原则，包括索引、查询和游标的使用等。下面由学习啦小编为大家整理的SQL数据库优化方式，希望大家喜欢!

　　SQL数据库优化的方式

学习啦在线学习网 　　1. 利用表分区

学习啦在线学习网 　　分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。

　　2. 别名的使用

　　别名是大型数据库的应用技巧，就是表名、列名在查询中以一个字母为别名，查询速度要比建连接表快1.5倍。

　　3. 索引Index的优化设计

　　索引可以大大加快数据库的查询速度。但是并不是所有的表都需要建立索引，只针对大数据量的表建立索引就好。

　　缺点：

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

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

　　3.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

　　索引需要维护：为了维护系统性能，索引在创建之后，由于频繁地对数据进行增加、删除、修改等操作使得索引页发生碎块，因此，必须对索引进行维护。

　　4. 物化视图(索引视图)

　　一般的视图是虚拟的，而物化视图是实实在在的数据区域，是要占据存储空间的，另外系统刷新物化视图也需要耗费一定的资源，但是它却换来了效率和灵活性。

　　索引视图更适合在OLAP(读取较多，更新较少)的数据库中使用，不适合在OLTP(记录即时的增、删、改、查)的数据库中使用 。

　　物化视图的注意事项：

　　1.对于复杂而高消耗的查询，如果使用频繁，应建成物化视图。

学习啦在线学习网 　　2.物化视图是一种典型的以空间换时间的性能优化方式。

学习啦在线学习网 　　3.对于更新频繁的表慎用物化视图。

　　4.选择合适的刷新方式。

　　普通视图和物化视图的区别：

学习啦在线学习网 　　普通视图和物化视图根本就不是一个东西，普通视图是不存储任何数据的，在查询中是转换为对应定义的SQL去查询，而物化视图是将数据转换为一个表，实际存储着数据，这样查询数据，就不用关联一大堆表，如果表很大的话，会在临时表空间内做大量的操作。

　　普通视图的三个特征：

学习啦在线学习网 　　1).简化设计，方便，清晰编码。视图并不是提高性能的，它的存在只会降低性能(例如我们关联两个视图，一个视图关联6个表，另一个视图关联7个表)。

　　2).安全，在授权给其他用户或者查看角度，多个表关联只允许查看，不允许修改。

学习啦在线学习网 　　3.从不同的角度看不同的维度，视图可以划分维度和权限，并使多个维度的综合，也就是你要什么就可以从不同的角度看，而表是一个实体的而已，一般维度较少。

　　5. 死锁与阻塞

　　1).对于需要频繁更新的数据，尽量避免放在长事务中，以免导致连锁反应。

　　2).不是迫不得已，最好不要在数据库锁机制外再加自己设计的锁。

　　3).减少事务大小，及时提交事务。

　　4).尽量避免跨数据库的分布式事务，因为环境的复杂性，很容易导致阻塞。

学习啦在线学习网 　　5).慎用位图索引，更新时容易导致死锁。

　　6.减少IO与网络传输次数

学习啦在线学习网 　　1).尽量用较少的数据库请求，获取到需要的数据，能一次性取出的不分多次取出。

学习啦在线学习网 　　2).对于频繁操作数据库的批量操作，应采用存储过程，减少不必要的网络传输。

　　sql内存释放的方式

学习啦在线学习网 　　一、这些内存一般都是SqlServer运行时候用作缓存

学习啦在线学习网 　　例如你运行一个select语句

　　那么Sql Server会将相关的数据页(Sql Server操作的数据都是以页为单位的)加载到内存中来，

　　下一次如果再次请求此页的数据的时候，就无需读取磁盘了，大大提高了速度。这类的缓存叫做数据缓存。

　　还有一些其他类型的缓存，如执行存储过程时，Sql Server需要先编译再运行，编译后的结果也会缓存起来，

　　下一次就无需再次编译了。如果这些缓存已经不需要了，那么我们可以调用以下几个DBCC管理命令来清理这些缓存：

学习啦在线学习网 　　DBCC FREEPROCCACHE

　　DBCC FREESESSIONCACHE

　　DBCC FREESYSTEMCACHE('All')

学习啦在线学习网 　　DBCC DROPCLEANBUFFERS

学习啦在线学习网 　　这几个命令分别用来清除存储过程相关的缓存、会话缓存、系统缓存以及所有所有缓存

学习啦在线学习网 　　但是需要注意的是，这几个命令虽然会清除掉现有缓存，为新的缓存腾地方，

学习啦在线学习网 　　但是Sql server并不会因此释放掉已经占用的内存。无奈的是，Sql Server

　　并没有提供任何命令允许我们释放不用到的内存。因此我们只能通过动态调整

学习啦在线学习网 　　Sql Server可用的物理内存设置来强迫它释放内存。

学习啦在线学习网 　　我们也可以通过Sql Server Management企业管理器进行动态控制。

　　连接到企业管理器之后打开Sql Server实例的属性面板，

学习啦在线学习网 　　找到内存设置，改变其中的最大服务器内存使用即可

　　二、--内存使用情况

学习啦在线学习网 　　SELECT * FROM sys.dm_os_performance_counters

学习啦在线学习网 　　WHERE counter_name IN ('Target Server Memory (KB)','Total Server Memory (KB)')

　　三、-- 内存状态

　　DBCC MemoryStatus

学习啦在线学习网 　　四、--查看最小最大内存

　　SELECT

　　cfg.name AS [Name],

　　cfg.configuration_id AS [Number],

学习啦在线学习网 　　cfg.minimum AS [Minimum],

　　cfg.maximum AS [Maximum],

学习啦在线学习网 　　cfg.is_dynamic AS [Dynamic],

学习啦在线学习网 　　cfg.is_advanced AS [Advanced],

学习啦在线学习网 　　cfg.value AS [ConfigValue],

　　cfg.value_in_use AS [RunValue],

学习啦在线学习网 　　cfg.description AS [Description]

　　FROM

学习啦在线学习网 　　sys.configurations AS cfg

　　五、--设置最小最大内存

学习啦在线学习网 　　sp_configure 'show advanced options', 1

　　go

　　sp_configure 'min server memory', 0

学习啦在线学习网 　　RECONFIGURE

　　GO

学习啦在线学习网 　　sp_configure 'max server memory', 2147483647

　　RECONFIGURE

　　GO

　　sp_configure 'max server memory', 256

　　RECONFIGURE

　　GO

学习啦在线学习网 　　sp_configure 'show advanced options', 0

学习啦在线学习网 　　-----------------------------------------------------------------------------------------------

学习啦在线学习网 　　CREATE proc [dbo].reclaimmemory --强制释放内存

　　as

　　begin

学习啦在线学习网 　　DBCC FREEPROCCACHE

　　DBCC FREESESSIONCACHE

学习啦在线学习网 　　DBCC FREESYSTEMCACHE('All')

　　DBCC DROPCLEANBUFFERS

　　exec sp_configure 'max server memory', 256

　　EXEC ('RECONFIGURE' )

　　WAITFOR DELAY '00:00:05'

　　EXEC sp_configure 'max server memory', 2147483647

学习啦在线学习网 　　EXEC ('RECONFIGURE' )

　　GO

　　end

　　--使用示例

　　/*

　　reclaimmemory

　　*/

　　Mysql存储引擎是什么

学习啦在线学习网 　　一、存储引擎的介绍

学习啦在线学习网 　　关系型数据库表是用来存储和组织信息的数据结构，可以将表理解为由行和列组成的表格。

　　由于表的类型不同，我们在实际开发过程中，就有可能需要各种各样的表，不同的表就意味着存储不同类型的数据，数据的处理上也会存在差异

学习啦在线学习网 　　对于Mysql来说，它提供了很多种类型的存储引擎

学习啦在线学习网 　　存储引擎说白了就是如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

学习啦在线学习网 　　因为在关系型数据库中数据的存储是以表的形式存储 的，所以存储引擎也可以称为表类型(即存储和操作此表的类型)

学习啦在线学习网 　　二、Mysql中的存储引擎

　　MyISAM

　　这种引擎是mysql最早提供的，这种引擎又可以分为静态MyISAM、动态MyISAM和压缩MyISAM三种

学习啦在线学习网 　　静态MyISAM:

　　如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。

学习啦在线学习网 　　因为数据表中的每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。

学习啦在线学习网 　　当数据受损时，恢复工作也比较容易做

　　动态MyISAM:

　　如果数据表中出现varchar,xxxtext或xxxxBLOB字段时，服务器将自动选择这种表类型 。

学习啦在线学习网 　　相对于静态MyISAM,这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降.

学习啦在线学习网 　　同时内存中也可能会出现很多碎片。

学习啦在线学习网 　　因此这种类型的表要经常 用optimize table命令 或优化工具来进行碎片整理

　　压缩MyISAM:

　　以上说的两种类型的表都可以用myisamchk工具压缩。

　　这种类型的表进一步减少了占用的存储。

学习啦在线学习网 　　但是这种表压缩之后不能再被修改。

　　另外，因为是压缩数据，所以这种表在读取的时候要先执行解压缩

学习啦在线学习网 　　但是，不管是哪种MyISAM表，目前都不支持事务，行级锁和外键约束的功能

学习啦在线学习网 　　MyISAM表示独立于操作系统的，这说明可以轻松的将其从windows服务器转移 到linux服务器;

学习啦在线学习网 　　每当我们建立一个MyISAM引擎的表时，就会在本地磁盘上建立三个文件，文件名就是表名。

　　例如：我建立一个MyISAM引擎的th_Demo表，那么就会生成以下三个文件:

　　1.th_demo.frm:存储表定义;

　　2.th_demo.MYD:存储数据;

学习啦在线学习网 　　3.th_demo.MYI:存储索引;

　　MyISAM存储引擎特别适合在以下几种情况下使用:

学习啦在线学习网 　　1.选择密集型的表，MYISAM存储引擎在筛选大量数据时非常迅速，这是它最突出的优点

　　2.插入密集型的表，MYISAM的并发插入特性允许同时选择和插入数据。例如：MYISAM存储引擎很适合管理邮件或web服务器日志数据

　　InnoDB

学习啦在线学习网 　　InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能

　　InnoDB是一个健壮的事务型存储引擎

　　使用场景：

　　1.更新密集的表。InnoDB存储引擎特别适合处理多重并发的更新请求

　　2.事务.InnoDB存储引擎是支持事务的标准mysql存储引擎

学习啦在线学习网 　　3.自动灾难恢复。与其他存储引擎不同，InnoDB表能够自动从灾难中恢复

　　4.外键约束。mysql支持外键的存储引擎只有InnoDB

　　5.支持自动增加列AUTO_INCREMENT属性

　　一般来说，如果需要事务支持，并且有较高的并发读取频率，InnoDB是不错的选择

学习啦在线学习网 　　memory(heap)

　　使用Mysql Memory存储引擎的出发点是速度，为了得到最快的响应时间

　　采用的逻辑存储介质是系统内存。

　　它要求存储在Memory数据表里的数据使用的是长度不变的格式，这意味着不能使用BLOB和Text这样的长度可变的数据类型

　　VARCHAR是一种长度可变的类型，但因为它在Mysql内部当作长度固定不变的CHAR类型，所以可以使用

　　Memory同时支持散列索引和B树索引。B树索引优于散列索引的是:

　　可以使用部分查询和通配查询，也可以使用<,>和>=等操作符方便数据挖掘

　　散列索引进行“相等比较”非常快，但是对"范围比较"的速度就慢多了

　　散列索引值适合使用在=和<>的操作符，不适合<或>操作符中，也同样不适合在order by子句中

　　三、一般在以下几种情况下使用Memory存储引擎:

学习啦在线学习网 　　1.目标数据较小，而且被非常频繁地访问。

　　在内存中存放数据，所以会造成内存的使用，可以通过参数max_heap_table_size控制memory表的大小，设置此参数，就可以限制Memory表的最大大小了。

　　2.如果数据是临时的，而且要求必须立即可用，那么就可以存放在内存表中

　　3.存储在Memory表中的数据如果突然丢失，不会对应用服务器产生实质的负面影响

　　archive

　　archive是归档的意思，在归档之后很多的高级功能就不再支持了，仅仅支持最基本的插入和查询两种功能。

　　mysql5.5版本以前，Archive是不支持索引，但是在Mysql以后的版本中就开始支持索引了。

学习啦在线学习网 　　Archive拥有很好的压缩机制，它使用zlib压缩库，在记录被请求时会实时压缩，所以它经常被用来当作仓库使用

学习啦在线学习网 　　常用于日志记录和聚合分析方面

　　MERGE

　　MERGE存储引擎是一组MyISAM表的组合，这些MYISAM表结构必须完全相同，尽管其使用不如其他引擎突出，但是在某些情况下非常有用

　　Merge表就是几个相同MyISAM表的聚合器

学习啦在线学习网 　　Merge表中并没有数据，对Merge类型的表可以进行查询、更新、删除操作

　　这些操作实际上是对内部的MyISAM表进行操作

学习啦在线学习网 　　Merge存储引擎的使用场景:

学习啦在线学习网 　　对于服务器日志这种信息，一般常用的存储策略是将数据分成很多表，每个名称与特定的时间端相关

学习啦在线学习网 　　删除Merge表只是删除Merge表的定义，对内部的表没有任何影响

　　四、Mysql中关于存储引擎的操作

学习啦在线学习网 　　1.查看数据库可以支持的存储引擎

学习啦在线学习网 　　show engines;

　　2.查看表的结构等信息的若干命令

　　desc tablename:查看数据表的结构

　　show create table tablename:显示表的创建语句

学习啦在线学习网 　　show table status like 'tablename'\G:显示表的当前状态值

　　3.设置或修改表的存储引擎

　　create table tablename(

　　columnname(列名1) type(数据类型) attri(属性设置)，

学习啦在线学习网 　　columnname(列名1) type(数据类型) attri(属性设置)，

　　)engine= enginename:创建数据库表时设置存储存储引擎

学习啦在线学习网 　　Alter table tablename engine = engineName：修改存储引擎