SQL性能基础优化

2019独角兽企业重金招聘Python工程师标准>>>

SQL性能基础优化

1.SQL基础语法
1）常用关键字select、insert、update、delete、where、distinct、as、and、or、group by、order by、having
2）常用的运算符 = 、!= 、<> 、> 、< 
3）常用条件关键字like、in、not in、between、exists、not exists
4）常用表连接或结果集拼接关键字left join、right join、inner join、union、union all
5）常用函数或比较关键字count、sum、limit(top-sqlserver、rownum-oracle)、min、max、substring、concat、group_concat

2.SQL查询常用性能优化策略
1）建立查询基表（定位查询结果集，尽量缩小查询结果集，尤其是需要多表关联或子查询结果拼接的时候需要考虑），搜索数据结果集尽量以小表作为基表，具体情况依据具体业务逻辑而定。
2）最大化的利用表索引，在编写查询条件的时候尽可能的利用上表的索引提高查询效率，避免使用全表扫描查询。避免在查询时使索引失效的操作，如查询条件中有or，且or条件关联的字段为非索引，则会是索引失效；在使用组合索引时，如果查询条件不是第一部分，也存在一定可能性使索引失效；Like查询以%开头同样会使索引失效；在查询时针对字符串字段和数值字段在条件中是否使用引号也会造成索引失效。
3）在查询多表关联数据时，尽量避免多级子查询嵌套，最好不要超过三层嵌套。
4）如果一次性查询的数据量比较大，可以考虑分多次查询或其他方法降低数据量。
5）在进行结果集拼接时，能使用union all的尽量使用union all。注意union和union all的区别在于，union会去重，而union all则不会。
6）慎重使用临时表，在存在业务量比较复杂，涉及到的数据表操作比较多的时候，因业务功能使用造成的频繁出现查询临时表，会极易引发数据库崩溃或宕机。
7）在多表关联时，尽量优化查询条件顺序和提高字段关联度，以此降低搜索结果集。
8）在编写复杂sql时，养成查看执行计划的习惯，熟悉执行计划常用字段的表达意思，根据执行计划优化查询效率。

3.SQL编写规范
1）尽量编码大事务操作，慎用holdlock子句，提高系统的并发能力；
2）尽量避免频繁事务的操作，如insert、update频繁操作，能使用批量的视情况而定尽量使用批量。
3）注意where字句写法，必须考虑语句顺序，应该根据索引顺序、范围大小来确定条件字句的前后顺序，尽可能的让字段顺序与索引顺序相一致，范围从大到小。
4）如非必要，不要在where字句中的=左边进行函数、算术运算或其他表达式运算，易造成无法正确使用索引。
5）尽量使用exists代替select count(1)、in写法。
6）在比较时，使用>=效率要高于>。
7）注意一些or字句和union字句之间的替换。
8）注意表之间连接的数据类型，避免不同类型数据之间的连接。
9）注意insert、update操作的数据量，防止与其他应用功能冲突，当数据量超过200（一般库的表页数据量）个数据页面（400K），那么系统会进行锁升级，由页级锁升级成表级锁。
10）视业务具体情况，尽量避免在查询大量数据时使用distinct、order by、group by、having、join，因为这些语句会加重tempdb的负担。
11）避免频繁创建和删除临时表，减少系统表资源的消耗。

4.索引的使用规范
1）索引的创建要与应用结合考虑。
2）尽可能的使用索引字段作为查询条件，尤其是聚簇索引。
3）避免对大表进行全表扫描，必要时可考虑新建索引。
4）在使用索引字段进行查询时，如果索引时联合索引，那么必须使用该索引的第一个字段作为条件才能保证系统使用该索引，否则易造成无法使用索引。

5.MYSQL explain执行计划用法详解
1）explain tablename ，可查看表结构
2）explain select * from table，可分析select性能
3）explain 主要有以下字段信息 
id :select查询的序列号；

select_type:select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询；
a.SIMPLE：查询中不包含子查询或者UNION
b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY
c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY
d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）
e.若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为：DERIVED
f.从UNION表获取结果的SELECT被标记为：UNION RESULT

table :输出的行所引用的表；

type :联合查询所使用的类型，表示MySQL在表中找到所需行的方式，又称“访问类型” 
type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是： 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref。
ALL: 扫描全表
    index: 扫描全部索引树
    range: 扫描部分索引，索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
    ref: 非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
    const, system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。system是const类型的特例，当查询的表只有一行的情况下， 使用system。
    NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引。

possible_keys:指出MySQL能使用哪个索引在该表中找到行。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

key :显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

key_len:显示MySQL决定使用的键长度。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。如果键是NULL，长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
 注：key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

ref:显示哪个字段或常数与key一起被使用。
 
rows:这个数表示mysql要遍历多少数据才能找到，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数，在innodb上可能是不准确的。
 
Extra:包含不适合在其他列中显示但十分重要的额外信息。
Only index，这意味着信息只用索引树中的信息检索出的，这比扫描整个表要快。 

using where是使用上了where限制，表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter），如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集。

impossible where 表示用不着where，一般就是没查出来啥。 

Using filesort（MySQL中无法利用索引完成的排序操作称为“文件排序”）当我们试图对一个没有索引的字段进行排序时，就是filesoft。它跟文件没有任何关系，实际上是内部的一个快速排序。 

Using temporary（表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询），使用filesort和temporary的话会很吃力，WHERE和ORDER BY的索引经常无法兼顾，如果按照WHERE来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。

转载于:https://my.oschina.net/u/2607324/blog/1535649