MySQL查询优化技巧总结

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/hxcaifly/article/details/79951428

前言

在千万级别以下的数据存储中,MySQL等关系型数据库依然被广泛地应用,查询优化时,一些常用的优化方法总结如下。

优化方法

1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select id from t where num=0

3.应尽量避免在 where 子句中使用!=或<>操作符,否则引擎将放弃使用索引而进行全表扫描。

4.应尽量避免在 where 子句中使用or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num=10 or num=20可以这样查询:select id from t where num=10 union all select id from t where num=20

5.in 和 not in 也要慎用,否则会导致全表扫描,如:select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了:select id from t where num between 1 and 3

6.下面的查询也将导致全表扫描:select id from t where name like ‘李%’若要提高效率,可以考虑全文检索。

如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:select id from t where num=@num可以改为强制查询使用索引:select id from t with(index(索引名)) where num=@num
8.应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where num/2=100应改为:select id from t where num=100*2

9.应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:select id from t where substring(name,1,3)=’abc’ ,name以abc开头的id

应改为:

select id from t where name like ‘abc%’

10.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

11.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

12.不要写一些没有意义的查询,如需要生成一个空表结构:select col1,col2 into #t from t where 1=0

这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:

create table #t(…)

13.很多时候用 exists 代替 in 是一个好的选择:select num from a where num in(select num from b)

用下面的语句替换:

select num from a where exists(select 1 from b where num=a.num)

14.并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

索引并不是越多越好,索引固然可 以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数最好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。

17.尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。

18.尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

19.任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

20.尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。

21.避免频繁创建和删除临时表,以减少系统表资源的消耗。

22.临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,最好使用导出表。

23.在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。

24.如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

25.尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。

26.使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。

与临时表一样,游标并不是不可使 用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送DONE_IN_PROC 消息。

29.尽量避免大事务操作,提高系统并发能力。

30.尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。

ASP.NET 2.0网站优化技巧总结

02-26

1. 当不需要使用Session的时候请关闭 rn 关闭Session当不需要使用的时候rn• 若要禁用页的会话状态,请将@ Page 指令中的EnableSessionState 属性设置为false。例如,<%@ Page EnableSessionState="false" %>。rn• 注意如果页需要访问会话变量,但不打算创建或修改它们,则将@ Page 指令中的EnableSessionState 属性设置为ReadOnly。还可以禁用XML Web services 方法的会话状态。有关更多信息,请参见使用ASP.NET 和XML Web services 客户端创建的XML Web services。rn• 若要禁用应用程序的会话状态,请在应用程序Web.config 文件的sessionstate 配置节中将mode 属性设置为off。例如,。rnrn2.尽量使用客户端脚本rnrn 虽然您很可能希望尽量多地使用Web 窗体页框架的那些节省时间和代码的功能,但在某些情况下却不宜使用ASP.NET 服务器控件和回发事件处理。通常,只有在检索或存储数据时,您才需要启动到服务器的往返过程。多数数据操作可在这些往返过程间的客户端上进行。例如,从HTML窗体验证用户输入经常可在数据提交到服务器之前在客户端进行。通常,如果不需要将信息传递到服务器以将其存储在数据库中,那么您不应该编写导致往返过程的代码。rnrn3.使用Page.IsPostback避免对往返过程执行不必要的处理rnrn• 如果您编写处理服务器控件回发处理的代码,有时可能需要在首次请求页时执行其他代码,而不是当用户发送包含在该页中的HTML 窗体时执行的代码。根据该页是否是响应服务器控件事件生成的,使用Page.IsPostBack 属性有条件地执行代码。rn• 注意如果不运行这种检查,回发页的行为将不更改。Page_Load 事件的代码在执行服务器控件事件之前执行,但只有服务器控件事件的结果才可能在输出页上呈现。如果不运行该检查,仍将为Page_Load 事件和该页上的任何服务器控件事件执行处理。rnrn4.在适当的环境中使用ASP.NET 服务器控件rnrn 检查您的应用程序代码以确保对ASP.NET服务器控件的使用是必要的。即使它们非常易于使用,但是服务器控件并不总是完成任务的最佳选择,因为它们会使用服务器资源。在许多情况下,一个简单的呈现或数据绑定代入就可以完成任务。rnrn5.只在必要时保存服务器控件视图状态rnrn• 自动视图状态管理是服务器控件的功能,该功能使服务器控件可以在往返过程上重新填充它们的属性值(您不需要编写任何代码)。但是,因为服务器控件的视图状态在隐藏的窗体字段中往返于服务器,所以该功能确实会对性能产生影响。您应该知道在哪些情况下视图状态会有所帮助,在哪些情况下它影响页的性能。例如,如果您将服务器控件绑定到每个往返过程上的数据,则将用从数据绑定操作获得的新值替换保存的视图状态。在这种情况下,禁用视图状态可以节省处理时间。默认情况下,为所有服务器控件启用视图状态。若要禁用视图状态,请将控件的EnableViewState 属性设置为falsern• <%@ Page EnableViewState="false" %>rnrn6.不要依赖代码中的异常rnrn因为异常大大地降低性能,所以您不应该将它们用作控制正常程序流程的方式。如果有可能检测到代码中可能导致异常的状态,请执行这种操作。不要在处理该状态之前捕获异常本身。常见的方案包括:检查null,分配给将分析为数字值的String一个值,或在应用数学运算前检查特定值。rnrn7.适当地使用公共语言运行库的垃圾回收器和自动内存管理rnrn小心不要给每个请求分配过多内存,因为这样垃圾回收器将必须更频繁地进行更多的工作。另外,不要让不必要的指针指向对象,因为它们将使对象保持活动状态,并且应尽量避免含Finalize 方法的对象,因为它们在后面会导致更多的工作。特别是在Finalize 调用中永远不要释放资源,因为资源在被垃圾回收器回收之前可能一直消耗着内存。最后这个问题经常会对Web 服务器环境的性能造成毁灭性的打击,因为在等待Finalize 运行时,很容易耗尽某个特定的资源。rnrn8.必要时调整应用程序每个辅助进程的线程数rnrnASP.NET 的请求结构试图在执行请求的线程数和可用资源之间达到一种平衡。已知一个使用足够CPU 功率的应用程序,该结构将根据可用于请求的CPU 功率,来决定允许同时执行的请求数。这项技术称作线程门控。但是在某些条件下,线程门控算法不是很有效。通过使用与ASP.NETApplications 性能对象关联的PipelineInstance Count 性能计数器,可以在PerfMon 中监视线程门控。rnrn9.将SQL Server 存储过程用于数据访问rnrn 在.NET Framework 提供的所有数据访问方法中,基于SQL Server 的数据访问是生成高性能、可缩放Web 应用程序的推荐选择。使用托管SQL Server 提供程序时,可通过使用编译的存储过程而不是特殊查询获得额外的性能提高。rnrn10.将SqlDataReader 类用于快速只进数据游标rnrn SqlDataReader 类提供了一种读取从SQLServer 数据库检索的只进数据流的方法。如果当创建ASP.NET 应用程序时出现允许您使用它的情况,则SqlDataReader 类提供比DataSet 类更高的性能。情况之所以这样,是因为sqlDataReader 使用SQL Server 的本机网络数据传输格式从数据库连接直接读取数据。另外,SqlDataReader 类实现IEnumerable 接口,该接口也允许您将数据绑定到服务器控件。rnrn11.选择适合页面或应用程序的数据查看机制rnrn 根据您选择在Web 窗体页显示数据的方式,在便利和性能之间常常存在着重要的权衡。例如,DataGrid Web 服务器控件可能是一种显示数据的方便快捷的方法,但就性能而言它的开销常常是最大的。在某些简单的情况下,您通过生成适当的HTML 自己呈现数据可能很有效,但是自定义和浏览器定向会很快抵销所获得的额外功效。Repeater Web 服务器控件是便利和性能的折衷。它高效、可自定义且可编程。rnrn12.只要可能,就缓存数据和页输出rnrn 使用ASP.NET 缓存机制有两点需要注意。首先,不要缓存太多项。缓存每个项均有开销,特别是在内存使用方面。不要缓存容易重新计算和很少使用的项。其次,给缓存的项分配的有效期不要太短。很快到期的项会导致缓存中不必要的周转,并且经常导致更多的代码清除和垃圾回收工作。若关心此问题,请监视与ASP.NET Applications 性能对象关联的Cache TotalTurnover Rate 性能计数器。高周转率可能说明存在问题,特别是当项在到期前被移除时。这也称作内存压力。rnrn13.仅对需要的应用程序启用身份验证rnrn 默认情况下,身份验证模式为Windows,或集成NTLM。大多数情况下,对于需要身份验证的应用程序,最好在Machine.config 文件中禁用身份验证,并在Web.config 文件中启用身份验证。rnrn14.根据适当的请求和响应编码设置来配置应用程序rnrn ASP.NET 默认编码格式为UTF-8。如果您的应用程序为严格的ASCII,请配置应用程序使用ASCII 以获得稍许的性能提高。rnrn15.从请求处理管线中移除不用的模块rnrn 默认情况下,服务器计算机的Machine.config 文件中 节点的所有功能均保留为激活。根据应用程序所使用的功能,您可以从请求管线中移除不用的模块以获得稍许的性能提升。检查每个模块及其功能,并按您的需要自定义它。例如,如果您在应用程序中不使用会话状态和输出缓存,则可以从 列表中移除它们,以便请求在不执行其他有意义的处理时,不必执行每个模块的进入和离开代码rn

没有更多推荐了,返回首页

私密
私密原因:
请选择设置私密原因
  • 广告
  • 抄袭
  • 版权
  • 政治
  • 色情
  • 无意义
  • 其他
其他原因:
120
出错啦
系统繁忙,请稍后再试