SQL语法基础——语法优化方法及实例详解

  • 日期:09-15
  • 点击:(1306)


2019-08-31 10: 21: 03老王科技

使用复合索引

如果经常执行上述查询,最好创建一个复合索引而不是创建三个单独的索引,因为三个单独的索引通常只使用每个数据库中的一个,尽管它比不使用索引的全表扫描更有效。但是使用复合索引因为索引本身对应于三个字段,效率会更高。

那么为什么数据库只支持使用一个索引的一个查询语句呢?简单地说,因为N个独立索引同时在一个语句中使用,所以它比仅使用一个索引慢,并且开销太大。

当使用索引字段作为条件时,如果索引是复合索引,那么索引中的第一个字段必须用作条件以确保系统使用索引,否则索引将不会被使用,并且应该是让字段顺序与索引顺序匹配。

同时,综合指数也具有有效的原则。原则是它将在未来使用后生效。如果中间未使用索引,则“断点”之前的索引生效,断点之后的索引不生效,导致“断点的原因通常为:

查询前面的任何索引都不参与查询,后者不会生效。正面的任何索引都是无效的,当前索引和后续索引不会生效。前面的任何索引字段都参与范围查询,后者不会生效。

索引失效导致全表扫描:

索引列用于计算,函数,类型转换等。索引列的使用不相等,例如!=或< >索引列使用IS NULL,IS NOT NULL。模糊查询LIKE以通配符开头,例如%ab。索引列使用OR来连接条件。索引列使用IN和NOT IN。键入错误,例如字段NUM类型varchar,WHERE条件号,NUM=1. WHERE子句和ORDER BY使用相同的索引,ORDER BY具有相同的顺序和索引顺序,ORDER BY字段是升序或降序,否则索引将不会被使用。复合索引不符合最佳左前缀原则或具有断点。如果MYSQL评估使用索引比全表扫描更慢,则不使用索引。

例如,如果我们构建这样的复合索引键索引(col1,col2,col3),那么它等同于创建(col1),(col1,col2)和(col1,col2,col3)三个索引,即最好的左前缀功能。

{! - PGC_COLUMN - }

指数失效的优化技术

避免使用!=或< > WHERE子句中的运算符尽可能多,否则引擎将放弃索引以进行全表扫描。 MySQL仅为以下运算符使用索引:< <=,=,> >=,BETWEEN,IN和一些LIKE('a%'),如下所示。

在where子句中使用like for fuzzy查询时,不能通过在关键字前添加通配符或在关键字前后添加前缀来使用索引,这将触发完整的表扫描。不使用like'%abc%'时解析索引的方法是添加覆盖索引(只有访问索引的查询、索引和查询列是相同的,只需扫描索引而不必返回表),如下所示。

您应该尽量避免对where子句中的字段进行空值判断。否则,引擎将放弃索引并执行全表扫描。空值是创建表时的默认值,但大多数情况下应使用NOTNULL或使用默认值。例如0作为默认值。

例如,性别,使用1表示男性,2表示女性,0表示未知,或者用户没有选择,默认值设置为0,因为大多数编程语言的默认值为0如下。

零和零是有区别的,以杯子为例:

空值表示杯子是真空的。空表示杯子里充满了空气。

如果允许该字段为空,则可能存在以下问题:

查询条件必须处理为空。否则,会出现一些奇怪的问题。例如,在条件查询中为负数,例如不在,=如果有空值,并且查询容易出错,则返回空结果。在某些数据库中,索引将失效。空列需要更多的存储空间,这导致空间更大,增加了数据库系统查询分析的复杂性。在程序中,可能需要每次判断它是否为空,并增加程序的复杂性。

但是,没有绝对的问题。使用默认值的想法可以解决大部分可以为空的问题,但并不是所有的问题都需要完成。具体的分析是基于具体的业务。

使用OR来连接WHERE子句中的条件应尽可能避免,否则引擎将放弃索引并扫描整个表。使用OR的句子可以分解为多个查询,并且可以通过UNION连接多个查询。它们的速度只与索引的使用有关。如果查询需要使用联合索引,则UNION ALL的执行效率更高,如下所示。

应尽可能避免在WHERE子句中使用IN和NOT IN,否则将导致全表扫描。对于连续值,请使用BETWEEN AND以尽可能避免使用IN。通常,使用EXISTS代替IN。如果需要使用IN,在IN之后的值列表中,可以根据分配的值的数量按降序排列它们,以减少判断次数。

用BETWEEN替换IN,如下所示。

使用EXISTS代替IN和NOT EXISTS而不是NOT IN,NOT IN的效率在任何情况下都是最低的,如下所示。

用LEFT JOIN替换IN,如下所示。

如上所述,我们通过以下方式优化IN和NOT IN:

替换之间(如果in的条件是连续的),替换为存在,替换不存在,并用左连接替换

您应该避免在WHERE子句中“=”左侧的字段上执行函数,算术运算和其他表达式运算。您可以将表达式操作移动到“=”的右侧,否则引擎将放弃索引并执行全表扫描。如下。

如果在WHERE子句中使用参数,则还会导致全表扫描。由于SQL仅在运行时解析局部变量,因此优化程序无法将访问计划选择推迟到运行时。必须在编译时选择它。但是,如果在编译时建立访问计划,则变量的值仍然是未知的,不能用作索引选择的输入。相反,可以强制查询使用索引,如下所示。

LIMIT分页优化

当寻呼查询偏移特别大时,LIMIT效率非常低,如下所述。

数据删除优化

存在下表,n_id是主键,id_card是人员代码,数字类型,表中的id_card有重复,id_card索引已经构建,如下所示。

用EXISTS替换DISTINCT

EXISTS语句用于确定括号中的表达式是否具有返回值。如果存在,则返回true。如果它不存在,则返回false。同时,如果括号中的表达式具有值,并且不需要遍历,则返回true。表中的所有数据正是EXISTS使查询更有效的原因,如下所示。

使用复合索引

如果频繁执行上述查询,则最好构建一个复合索引,而不是三个单独的索引,因为三个单独的索引通常一次只能由一个数据库执行。虽然这提高了全表扫描的效率而不是没有索引,但复合索引的使用是因为索引本身彼此对应。在三个领域,效率将进一步提高。

那么为什么数据库只支持一个查询语句而只使用一个索引呢?简单地说,因为N个独立索引同时在一个语句中使用,这比仅使用一个索引更慢且更昂贵。

使用索引字段作为条件时,如果索引是复合索引,则必须将索引中的第一个字段用作条件,以确保系统使用索引。否则,将不使用索引,并且字段的顺序应尽可能与索引顺序一致。

同时,综合指数具有有效性原则。其原理是从开始到结束顺序使用它。如果未使用中间索引,则断点之前的索引有效且断点之后的索引无效。断点的原因通常如下:

前面的任何索引都不参与查询,后者不生效。前面的任何索引都是无效的,当前索引和后者无效。前面的任何索引字段都参与范围查询,而后者不起作用。

索引失败和全表扫描的原因如下:

索引列用于计算,函数,类型转换等。索引列的使用不相等,例如!=或< >索引列使用IS NULL,IS NOT NULL。模糊查询LIKE以通配符开头,例如%ab。索引列使用OR来连接条件。索引列使用IN和NOT IN。键入错误,例如字段NUM类型varchar,WHERE条件号,NUM=1. WHERE子句和ORDER BY使用相同的索引,ORDER BY具有相同的顺序和索引顺序,ORDER BY字段是升序或降序,否则索引将不会被使用。复合索引不符合最佳左前缀原则或具有断点。如果MYSQL评估使用索引比全表扫描更慢,则不使用索引。

例如,如果我们构建这样的复合索引键索引(col1,col2,col3),那么它等同于创建(col1),(col1,col2)和(col1,col2,col3)三个索引,即最好的左前缀功能。

{! - PGC_COLUMN - }

指数失效的优化技术

避免使用!=或< > WHERE子句中的运算符尽可能多,否则引擎将放弃索引以进行全表扫描。 MySQL仅为以下运算符使用索引:< <=,=,> >=,BETWEEN,IN和一些LIKE('a%'),如下所示。

在WHERE子句中使用LIKE进行模糊查询时,不能通过在关键字之前添加通配符或在关键字之前和之后添加前缀来使用索引,这会触发全表扫描。不使用LIKE'%abc%'时解析索引的方法是添加覆盖索引(只有访问索引的查询,索引和查询列是相同的,只需扫描索引而不必返回表),如下。

您应该尝试避免在WHERE子句中对字段进行NULL值判断。否则,引擎将放弃索引并执行全表扫描。 NULL是创建表时的默认值,但大多数情况下应使用NOT NULL或使用默认值。例如0作为默认值。

例如,性别,使用1表示男性,2表示女性,0表示未知,或者没有用户选择,默认值设置为0,因为大多数编程语言的默认值0如下所示。

null和NULL之间存在差异,以杯子为例:

空值表示杯子是真空的。 NULL表示杯子充满空气。

如果允许该字段为空,则可能存在以下问题:

查询条件必须处理为空。否则,会出现一些奇怪的问题。例如,当存在NULL值时,负IN条件查询(如NOT IN,=)将返回null结果,并且查询容易出错。在某些数据库中,索引将失效。空列需要更多的存储空间,这会导致更大的空间并增加数据库系统查询分析的复杂性。在程序中,可能有必要每次判断它是否为空,并增加程序的复杂性。

但是,没有绝对的问题。使用默认值的想法可以解决可能为空的问题的很大一部分,但并非所有问题都需要完成。具体分析基于具体业务。

您应该尝试避免在WHERE子句中使用OR来加入条件,否则引擎将放弃索引并执行全表扫描。使用OR的单词可以分解为多个查询,并通过UNION连接多个查询。他们的速度只与是否使用索引有关。如果查询需要使用联合索引,则使用UNION ALL执行更有效,如下所示。

尽量避免在WHERE子句中使用IN和NOT IN,否则将导致全表扫描。对于连续值,您可以避免在BETWEEN AND中使用IN。通常,使用EXISTS而不是IN。如果需要在IN之后的值列表中使用IN,则按降序对值的数量进行排序,从而减少判断的数量。

用BETWEEN替换IN,如下所示。

使用EXISTS而不是IN和NOT EXISTS而不是NOT IN。在任何一种情况下,NOT IN效率最低,如下所示。

用LEFT JOIN替换IN,如下所示。

如上所述,我们优化了IN和NOT IN:

使用inbet替换in(如果in的条件是连续的)使用而不是in,替换为不存在不使用left join替换in

您应该避免在WHERE子句中“=”左侧的字段上执行函数,算术运算和其他表达式运算。您可以将表达式操作移动到“=”的右侧,否则引擎将放弃索引并执行全表扫描。如下。

如果在WHERE子句中使用参数,则还会导致全表扫描。由于SQL仅在运行时解析局部变量,因此优化程序无法将访问计划选择推迟到运行时。必须在编译时选择它。但是,如果在编译时建立访问计划,则变量的值仍然是未知的,不能用作索引选择的输入。相反,可以强制查询使用索引,如下所示。

LIMIT分页优化

当寻呼查询偏移特别大时,LIMIT效率非常低,如下所述。

数据删除优化

存在下表,n_id是主键,id_card是人员代码,数字类型,表中的id_card有重复,id_card索引已经构建,如下所示。

用EXISTS替换DISTINCT

EXISTS语句用于确定括号中的表达式是否具有返回值。如果存在,则返回true。如果它不存在,则返回false。同时,如果括号中的表达式具有值,并且不需要遍历,则返回true。表中的所有数据正是EXISTS使查询更有效的原因,如下所示。

http://www.whgcjx.com/bdsVg.html