html5网站模板源码,wordpress教程凌风,网站建设的经营范围,想做网站怎么做本文中所提到的SQL优化技巧均是基于Mysql 索引 BTree类型 。将从以下几个方面介绍常用的SQL优化技巧#xff1a;避免在 WHERE 子句中使用 ! 或 操作符。避免在 WHERE 子句中对索引列使用 %前缀模糊查询。避免在 WHERE 子句中对索引列使用 OR 来连接条件。避免在 WHER…本文中所提到的SQL优化技巧均是基于Mysql 索引 BTree类型 。将从以下几个方面介绍常用的SQL优化技巧避免在 WHERE 子句中使用 ! 或 操作符。避免在 WHERE 子句中对索引列使用 %前缀模糊查询。避免在 WHERE 子句中对索引列使用 OR 来连接条件。避免在 WHERE 子句中对索引列使用 IN 和 NOT IN。避免在 WHERE 子句中对索引列使用计算、函数、类型转换等操作。避免在 WHERE 子句中对索引列使用参数。使用合理的分页方式以提高分页的效率。使用 EXISTS 替换 DISTINCT 。避免在 WHERE 子句中对索引列进行 NULL 值判断。避免在 WHERE 子句中对索引列进行 隐式类型转换。合理使用 复合索引。本文篇幅较长 建议先收藏再阅读便于后续查阅。 善用EXPLAIN通常我们在写完较为复杂的 SQL 时一般会进行一下 MySQL 优化我们要善用 EXPLAIN 查看 SQL 执行计划。Explain语法如下执行计划包含如下信息type连接类型。一般来说需要保证查询至少达到 range 级别最好能达到 ref杜绝出现 all 级别。key实际使用的索引如果没有可用的索引则显示为NULL可以使用force index强制索引方式。key_len: 索引字段的最大可能长度理论上长度越短越好但并非实际使用长度。rows: 表示MySQL根据表统计信息及索引选用情况估算的找到所需的记录所需要读取的行数ROWS值的大小是个统计抽样结果并不十分准确。extra: 额外说明当出现Using filesort, Using temporary的时候需要注意。避免在 WHERE 子句中使用 ! 或 操作符应尽量避免在 WHERE 子句中使用 ! 或 操作符否则将导致引擎放弃使用索引而进行全表扫描。MySQL 只有对以下操作符才会使用索引BETWEENIN以及使用 LIKE 时的 后缀模糊查询 % 。避免在WHERE 子句中对索引列使用 %前缀模糊查询WHERE 子句中使用 LIKE进行模糊查询时使用 %前缀模糊查询 无法使用索引从而引发全表扫描。解决 %前缀模糊查询时索引不被使用的方法就是添加覆盖索引(只访问索引的查询索引和查询列一致只需扫描索引而无须回表)。避免在WHERE 子句中对索引列使用 OR 来连接条件应尽量避免在 WHERE 子句中使用 OR 来连接条件否则将导致引擎放弃使用索引而进行全表扫描。使用 OR 的字句可以分解成多个查询并且通过 UNION 连接多个查询。他们的速度只同是否使用索引有关如果查询需要用到复合索引用 UNION ALL 执行的效率更高。尽量UNION ALL 代替 UNION UNION 和 UNION ALL 的差异主要是前者需要将结果集合并后再进行唯一性过滤操作会涉及到排序增加大量的CPU运算加大资源消耗及延迟。当然UNION ALL的前提条件是两个结果集没有重复数据。避免在WHERE 子句中对索引列使用 IN 和 NOT IN应尽量避免在 WHERE 子句中使用 IN 和 NOT IN 否则将导致全表扫描对于连续的数值能用 BETWEEN AND 尽量避免使用 IN。一般用 EXISTS 代替 IN 。若需要使用 IN在 IN 后面值的列表中按照值的分布数量降序排列减少判断的次数。尝试使用BETWEEN AND 替换 IN 。我们使用 EXISTS 替代 IN用 NOT EXISTS 替代 NOT IN无论在哪种情况下 NOT IN 效率都是最低的。尝试使用LEFT JOIN 替换 IN。如上我们使用了如下方式优化了 IN 和 NOT IN使用 between 替换 in ( 如果 in 的条件是连续的)使用 exists 替代 in、用not exists替代 not in使用 left join 替换 in 。避免在WHERE 子句中对索引列使用计算、函数、类型转换等操作应尽量避免在 WHERE 子句中对 “” 左边的字段进行函数、算术运算及其他表达式运算可以将表达式运算移至“”右边否则将导致引擎放弃使用索引而进行全表扫描。避免在WHERE 子句中对索引列使用参数如果在 WHERE 子句中使用参数也会导致全表扫描。因为SQL只有在运行时才会解析局部变量但优化程序不能将访问计划的选择推迟到运行时。它必须在编译时进行选择。然而如果在编译时建立访问计划变量的值还是未知的因而无法作为索引选择的输入项可以改为强制查询使用索引。使用合理的分页方式以提高分页的效率分页查询在我们的实际应用中非常普遍也是最容易出问题的查询场景。比如对于下面简单的语句一般想到的办法是在nameageregister_time字段上创建复合索引。这样条件排序都能有效的利用到索引性能迅速提升。如上例子当 LIMIT 子句变成 “LIMIT 100000, 50” 时此时我们会发现只取50条语句为何会这么慢?原因很简单MySQL并不知道第 100000条记录从什么地方开始即使有索引也需要从头计算一次因此会感觉非常的慢一般我们在做翻页时是可以获取上一页中的某个数据标志来缩小查询范围的比如时间可以将上一页的最大值时间作为查询条件的一部分SQL可以优化为这样使用EXISTS 替换 DISTINCT EXISTS语句用来判断()内的表达式是否存在返回值如果存在就返回 True如果不存在就返回 False同时它只要括号中的表达式有一个值存在就立刻返回 True 而不用遍历表中所有的数据。因此 EXISTS 使查询效率更高。避免在WHERE 子句中对索引列进行 NULL 值判断应尽量避免在 WHERE 子句中对字段进行 NULL 值判断否则将导致引擎放弃使用索引而进行全表扫描创建表时 NULL 是默认值但大多数时候应该使用 NOT NULL或者使用一个默认值如 0 作为默认值。例如性别使用1表示男2表示女0表示未知或者是用户没有选择默认值设置为 0因为大部分编程语言的数字类型的默认值0。空值和NULL是有区别的以一个杯子为例空值 代表杯子是真空的。NULL 代表杯子中装满了空气。如果字段允许为空可能会有以下问题查询条件就必须处理为空的情况否则会出现一些很奇怪的问题比如 NOT IN、! 等负向条件查询在有 NULL 值的情况下返回永远为空结果查询容易出错。在部分数据库中会导致索引失效。可空列需要更多的存储空间导致空间变大进而导致数据库系统查询分析变的复杂。在程序中也需要每次都判断是不是空导致程序复杂了。但凡事没有绝对的使用默认值的思路可以解决很大一部分可为空的问题但不是所有都需这样做具体还是要根据具体业务进行分析。避免在WHERE 子句中对索引列进行隐式类型转换当我们对不同类型的值进行比较的时候为了使得这些数值可比较MySQL会做一些隐式转化(Implicit type conversion)。SQL查询语句的条件中字段赋值与字段定义类型不匹配是一种常见的错误用法。如上字段 account 的定义为 varchar 类型在 WHERE 条件中 account 字段是数字型两者数据类型不一样这时是没法直接进行比较的需要进行类型转换。MySQL的策略是将表中 account 字段全部转换为数字型之后再比较由于函数作用于表字段引起索引失效导致全表扫描正确的写法如下合理使用复合索引如果经常执行如上查询那么建立三个单独索引 不如建立一个复合索引因为三个单独索引通常数据库每次执行只能使用其中一个虽然这样比不使用索引而进行全表扫描提高了很多效率但使用复合索引因为索引本身就对应到三个字段上的效率会有更大提高。那么为什么数据库只支持一条查询语句只使用一个索引简单的讲是因为N个独立索引同时在一条语句使用的效果比只使用一个索引还要慢开销太大。在使用索引字段作为条件时如果该索引是复合索引那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引否则该索引将不会被使用并且应尽可能的让字段顺序与索引顺序相一致。同时复合索引的生效原则是从前往后依次使用生效如果中间某个索引没有使用那么断点前面的索引部分起作用断点后面的索引没有起作用造成断点的原因一般有前边的任意一个索引没有参与查询后面的不生效。前边的任意一个索引失效当前索引及后面全部不生效。前边的任意一个索引字段参与的是范围查询后面的不生效。引发索引失效导致全表扫描的原因有索引列进行计算、函数、类型转换等操作。索引列使用不等于如 ! 或。索引列使用 IS NULL IS NOT NULL。模糊查询LIKE 以通配符开头如%str。索引列使用使用 OR 来连接条件。索引列使用 NOT IN 。类型错误如字段 NUM 类型 为varcharWHERE 条件用 numberNUM 1。WHERE子句和 ORDER BY使用相同的索引并且 ORDER BY的顺序和索引顺序相同并且 ORDER BY 的字段都是升序或者降序否则不会使用索引。复合索引不符合最佳左前缀原则或存在断点。如果MYSQL评估使用索引比全表扫描更慢则不使用索引。例如我们建立了一个这样复合索引key index (col1, col2, col3)那么其实相当于创建了(col1)(col1, col2)(col1, col2, col3) 三个索引即最佳左前缀特性。其他 优化 技巧当索引列有大量数据重复时SQL查询可能不会去利用索引并不是所有索引对查询都有效SQL是根据表中数据来进行查询优化的。如表中有“性别”字段即使在“性别”字段建立索引也对查询效率起不了作用尽量不要对数据库中某个含有大量重复的值的字段建立索引。建立索引可以提高 SELECT 的效率但 索引并不是越多越好。索引同时也降低了 INSERT 及 UPDATE 的效率因为 INSERT 或 UPDATE 时有可能会重建索引所以怎样建索引需要慎重考虑视实际应用情况而定。同时一个表的索引数最好不要超过6个若太多则应考虑一些不常使用的字段是否有建立索引的必要。对于数值字段尽量使用数字型字段若只含数值信息的字段尽量不要设计为字符型这会降低查询和连接的性能并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符而对于数字型而言只需要比较一次就够了。对于字符型字段尽量的使用 varchar/nvarchar 代替 char/nchar 因为首先变长字段存储空间小可以节省存储空间其次对于查询来说在一个相对较小的字段内搜索效率显然要高些。避免使用 select * from table用具体的字段列表代替“*”避免返回用不到的任何字段。尽可能的使用索引字段作为查询条件尤其是聚簇索引必要时可以通过index index_name来强制指定索引避免对大表查询时进行table scan必要时考虑新建索引。要注意索引的维护周期性重建索引重新编译存储过程。在新建临时表时如果一次性插入数据量很大那么可以使用 SELECT INTO 代替 CREAT TABLE避免造成产生大量日志 以提高速度。如果数据量不大为了缓和系统表的资源应先CREAT TABLE然后INSERT。当服务器的内存够多时配置 线程数量 最大连接数 5使其发挥最大的效率。否则使用配置 线程数量 最大连接数启用SQL SERVER的线程池来解决如果还是 线程数量 最大连接数5可能会严重的损害服务器的性能。尽量避免向客户端返回大量结果数据若数据量过大应该考虑相应需求是否合理。尽量避免大事务操作提高系统并发能力。创建索引的一般规则表的主键、外键需要建立索引。频繁与其他表进行连接的表在连接字段上应该建立索引。频繁出现在 WHERE 子句及 ORDER BY 中的字段特别是大表的字段应该建立索引。索引应该建在短字段上对于大的文本字段甚至超长字段避免建索引。复合索引的建立需要结合实际应用进行分析尽量考虑用单字段索引代替。正确选择复合索引中的主列字段一般是选择性较好的字段复合索引的几个字段是否经常同时以 AND 方式出现在 WHERE 子句中单字段查询是否极少甚至没有如果是则可以建立复合索引否则考虑单字段索引。如果复合索引中包含的字段经常单独出现在 WHERE 子句中则分解为多个单字段索引。如果复合索引所包含的字段超过3个需要结合实际应用考虑其必要性考虑减少复合的字段。如果既有单字段索引又有这几个字段上的复合索引通常可以删除复合索引。频繁进行数据操作的表不要建立太多的索引删除无用的索引。建立的每个索引都会增加存储开销索引对于插入、删除、更新操作也会增加处理上的开销。另外过多的复合索引在有单字段索引的情况下一般都是没有存在价值的。相反还会降低数据增加删除时的性能特别是对频繁更新的表来说负面影响非常大。尽量不要对数据库中某个含有大量重复的值的字段建立索引。
