文档章节

SQL性能基础优化

LUKE1993
 LUKE1993
发布于 2017/09/12 10:05
字数 2330
阅读 13
收藏 0

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,这就要看是先过滤再排序划算,还是先排序再过滤划算。

© 著作权归作者所有

共有 人打赏支持
LUKE1993
粉丝 3
博文 36
码字总数 34981
作品 0
广州
架构师
30分钟全面解析-SQL事务+隔离级别+阻塞+死锁

阅读目录 概述: 一、事务 二、锁 三、阻塞 四、隔离级别 五.死锁 以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。 本系列主要是针对T-SQL的总结。 ...

Mr_zebra
05/02
0
0
oracle学习路线图

这是本人收藏的一个大师写的,用来提示自己oracle学习路线 1、sql、pl/sql(网上有很多的视频,可以做一个简单的入手,然后看几本书,多做实验) 作为oracle的基本功,需要大家对sql和plsql...

Monument
2014/09/25
277
2
你曾错过的2017十大MVP精彩好文,今天Repo回来!

“2017年度十大MVP评选”已圆满落幕,再次感谢这10位获奖专家及团队为知识传播的努力、对技术分享的热忱、为行业发展的贡献,在此将他们这一年来分享过的干货好文遴选出来,以便大家温故知新...

DBAplus社群
2017/11/20
0
0
Mysql 性能优化——必胜之道

mysql的性能优化是运维和DBA们常常面对的问题,也是各大公司招聘人才时看中的要点之一。性能优化听上去很难,似乎只有大神才能做,然而,mysql的性能优化绝不是运维独自一个能完成的,DBA、开...

super李导
2017/03/14
0
0
Mysql 性能优化——必胜之道

mysql的性能优化是运维和DBA们常常面对的问题,也是各大公司招聘人才时看中的要点之一。性能优化听上去很难,似乎只有大神才能做,然而,mysql的性能优化绝不是运维独自一个能完成的,DBA、开...

32氪
2017/07/31
0
0

没有更多内容

加载失败,请刷新页面

加载更多

docker-compose ,docker-stack

1.例子 version: "3"services: php: image: registry.cn-hangzhou.aliyuncs.com/lxepoo/apache-php5 ports: - "38080:80" networks: - my_php_mysql volum......

chenbaojun
24分钟前
1
0
SQL_Server2000示例数据库NorthWind的分析(转)

SQL_Server2000示例数据库NorthWind的分析 表名:Categories(食品类别表) 表结构: 字段名称 数据类型 长度 允许为空 CategoryID(主键) int 4 否 CategoryName nvarchar 15 否 Description ...

QQZZFT
27分钟前
1
0
laravel 5.5 Session store not set on request.

laravel 5.5 数据存入session,会出现Session store not set on request.错误。查了下laravel 5.5将session放到global middleware中,需要laravel的文件 ./app/Http/Kernel.php中的加上一句:...

MichaelShu
今天
1
0
OpenCV VideoCapture.get()参数详解

param define cv2.VideoCapture.get(0) 视频文件的当前位置(播放)以毫秒为单位 cv2.VideoCapture.get(1) 基于以0开始的被捕获或解码的帧索引 cv2.VideoCapture.get(2) 视频文件的相对位置(...

NateHuang
今天
0
0
java基础知识,小栗子

来操作一下数组.....注意带参数的变长数组的使用. package com.avatus;import java.util.Random;import java.util.Scanner;public class Main { public static void main(St...

Oh_really
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部