1010cc时时彩标准版 > 1010cc三分网站 > 1010cc时时彩标准版高效sql性能优化,必知必会

原标题:1010cc时时彩标准版高效sql性能优化,必知必会

浏览次数:123 时间:2019-08-13

首先看下面一条比较完成语句,都是比较常见的关键字。

SQL 必知必会

在mac终端操作sqlite:

  • cd 数据库所在的目录
  • sqlite3 数据库文件名 //带后缀)(此时已经打开数据库)
  • .tables //显示数据库中所有已经创建的表
  • .schema //显示所有表的模式
  • .headers on //显示字段名(查询时才会显示)
  • .mode column/list/line
  • 执行sql语句必须在末尾加分号

一,sql性能优化基础方法论

对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为:

cpu消耗

内存使用

对磁盘,网络或其他I/O设备的输入/输出(I/O)操作。

 

但我们遇到性能问题时,要判断的第一点就是“在这三种资源中,是否有哪一种资源达到了有问题的程度”,因为这一点能指导我们搞清楚“需要优化重构什么”和“如何优化重构它”

1010cc时时彩标准版 1

一,sql性能优化基础方法论

对于功能,我们可能知道必须改进什么;但对于性能问题,有时我们可能无从下手。其实,任何计算机应用系统最终队可以归结为:

cpu消耗

内存使用

对磁盘,网络或其他I/O设备的输入/输出(I/O)操作。

阅读本文,建议大家已经掌握了扎实的互联网架构技术,可参考:互联网架构技术清单

但我们遇到性能问题时,要判断的第一点就是“在这三种资源中,是否有哪一种资源达到了有问题的程度”,因为这一点能指导我们搞清楚“需要优化重构什么”和“如何优化重构它”

1010cc时时彩标准版 2

MySQL语句的执行顺序如下:

USE Temp;

SELECT empid, YEAR(orderdate) AS orderyear, COUNT(*) AS numorders
FROM Sales.Orders
WHERE custid = 71
GROUP BY empid, YEAR(orderdate)
HAVING COUNT(*) > 1
ORDER BY empid, orderyear;

distinct

 SELECT DISTINCT name FROM TB_BOOK_TAG;

关键字distinct,用于去除name列中所有行中重复元素。

二,sql调优领域

应用程序级调优

sql语句调优

管理变化调优

示例级调优

内存

数据结构

实例配置

操作系统交互

I/O

swap

Parameters

二,sql调优领域

应用程序级调优

sql语句调优

管理变化调优

示例级调优

内存

数据结构

实例配置

操作系统交互

I/O

swap

Parameters

执行序号 执行操作 解释
1 from 对from的左边的表的输出,会作为右边的表的输入计算笛卡尔积得到虚拟表T1
2 on 对虚表T1进行on过滤,过滤出所有满足on谓词条件的列,生成虚拟表T2
3 join 如果指定了outer join(比如left join、 right join),那么保留表中未匹配的行就会作为外部行添加到虚拟表T2中,产生虚拟表T3, from子句中包含两个以上的表的话,那么就会对上一个join连接产生的结果VT3和下一个表重复执行步骤1~3这三个步骤,一直到处理完所有的表为止。
4 where 对虚拟表T3进行where条件过滤。只有符合<where-condition>的记录才会被插入到虚拟表T4中
5 group by 根据group by子句中的列,对T4中的记录进行分组操作,产生T5
6 with (cube、rollup) 对表T5进行cube或者rollup操作,产生表T6
7 having 对虚拟表T6应用having过滤,只有符合<having-condition>的记录才会被 插入到虚拟表T7中
8 select 执行select操作,选择指定的列,插入到虚拟表T8中
9 distinct 对T8中的记录进行去重。产生虚拟表T9
10 order by 将虚拟表T9中的记录按照<order_by_list>进行排序操作,产生虚拟表T10
11 limit 取出指定行的记录,产生虚拟表T11, 并将结果返回

我们来详细分析一下sql语句的逻辑处理顺序,虽然select在每条语句的第一位,但实际上它是被最后才处理的

limit

SELECT name FROM TB_BOOK_TAG LIMIT 5;

关键字limit,返回name列指定行数。

SELECT name FROM TB_BOOK_TAG LIMIT 5 OFFSET 0;
等同于下面写法(shortcut)
SELECT name FROM TB_BOOK_TAG LIMIT 0,5;

三,sql优化方法

优化业务数据

优化数据设计

优化流程设计

优化sql语句

优化物理结构

优化内存分配

优化I/O

优化内存竞争

优化操作系统

三,sql优化方法

优化业务数据

优化数据设计

优化流程设计

优化sql语句

优化物理结构

优化内存分配

优化I/O

优化内存竞争

优化操作系统

查询子句

命令格式: select [all|distinct] select_expr from -> where -> group by [合计函数] -> having -> order by -> limit

select_expr
ex:

-- 可以用 * 表示所有字段。
select * from student;
-- 可以使用表达式(计算公式、函数调用、字段也是个表达式)
select name, age,512*2, now() from student;
-- 可以为每个列使用别名。适用于简化列标识,避免多个列标识符重复。使用 as 关键字,也可省略 as.
select age as student_age from student;

from 子句
用于标识查询来源。
ex:

-- 可以为表起别名。使用as关键字,也可以省略as。
select * from student as st, clazz as c;
select * from student st,clazz c;
-- from子句后,可以同时出现多个表。多个表会横向叠加到一起,而数据会形成一个笛卡尔积。
select * from student, clazz;

where 子句
从from获得的数据源中进行筛选。
ex:

--查询id小于10的记录
select * from student where id < 10;
  • 整型1表示真,0表示假。
  • 表达式由运算符和运算数组成。
    • 运算数:变量(字段)、值、函数返回值
    • 运算符:=, <=>, <>, !=, <=, <, >=, >, !, &&, ||, in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
      is/is not (ture|false|unknown)

group by 子句, 分组子句

命令格式:group by 字段/别名 [排序方式]

分组后会进行排序。升序:asc,降序:desc
以下[合计函数]需配合 group by 使用:
count 返回不同的非NULL值数目 count(*)、count(字段)
sum 求和
max 求最大值
min 求最小值
avg 求平均值
group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。
ex:

--统计所有用户的订单编号,各编号间用逗号分隔
select user_id, group_concat(order_id,',') from order group by user_id;

having 子句,条件子句
与 where 功能、用法相同,执行时机不同。
where 在开始时执行检测数据,对原数据进行过滤。
having 对筛选出的结果再次进行过滤。
having 字段必须是查询出来的,where 字段必须是数据表存在的。
where 不可以使用字段的别名,having 可以。因为执行where代码时,可能尚未确定列值。
where 不可以使用合计函数。一般需用合计函数才会用 having
SQL标准要求having必须引用group by子句中的列或用于合计函数中的列。

order by 子句,排序子句
order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...
升序:asc,降序:desc
支持多个字段的排序。

limit 子句,限制结果数量子句
仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合,按照记录出现的顺序,索引从0开始。
limit 起始位置, 获取条数。

select * from student limit 10 ,10;

省略第一个参数,表示从索引0开始。
limit 获取条数

select * from student limit 10;

ps:limit和offset连用可用于分页

distinct, all 选项
distinct 去除重复记录
默认为 all, 全部记录

1.from  

limit ... offset ...

关键字LIMIT ... OFFSET ...,limit后跟的数字指定显示多少行,offset后跟的数字表示从什么位置开始。(0是第一行)

四,sql优化过程

定位有问题的语句

检查执行计划

检查执行计划中优化器的统计信息

分析相关表的记录数、索引情况

改写sql语句、使用HINT、调整索引、表分析

有些sql语句不具备优化的可能,需要优化处理方式

达到最佳执行计划

四,sql优化过程

定位有问题的语句

检查执行计划

检查执行计划中优化器的统计信息

分析相关表的记录数、索引情况

改写sql语句、使用HINT、调整索引、表分析

有些sql语句不具备优化的可能,需要优化处理方式

达到最佳执行计划

union

将多个select查询的结果组合成一个结果集合。

命令格式:select ... union[all | distinct] select ....

默认 distinct 方式,即所有返回的行都是唯一的,建议对每个select查询加上小括号包裹。order by 排序时,需加上 limit 进行结合。需要各select查询的字段数量一样。每个select查询的字段列表(数量、类型)应一致,因为结果中的字段名以第一条select语句为准。
ex:

select * from student union select * from student_temp;

from型
from后要求是一个表,必须给子查询结果取个别名。简化每个查询内的条件。from型需将结果生成一个临时表格,可用以原表的锁定的释放。子查询返回一个表,子查询需用括号包裹。表型子查询。
ex:

select from (select from student where id>0) as stu where id<10;

where型
子查询返回一个值,标量子查询。不需要给子查询取别名。
where子查询内的表,不能直接用以更新。
ex:

--取出订单表中消费金额最大的订单记录
select * from order where money = (select max(money) from order);

列子查询
如果子查询结果返回的是一列。使用 in 或 not in 完成查询。
exists 和 not exists 条件 如果子查询返回数据,则返回1或0。常用于判断条件。
ex:

select column1 from t1 where exists (select * from t2);

行子查询,查询条件是一个行。
ex:

select * from t1 where (id, gender) in (select id, gender from t2);

行构造符:(col1, col2, ...) 或 row(col1, col2, ...)行构造符通常用于与对能返回两个或两个以上列的子查询进行比较。

特殊运算符
!= all() 相当于 not in
= some() 相当于 in。any 是 some 的别名
!= some() 不等同于 not in,不等于其中某一个。
all, some 可以配合其他运算符一起使用。

连接查询(join)
将多个表的字段进行连接,可以指定连接条件。

连接方式 解释 例子

内连接(inner join)|默认就是内连接,可省略inner。只有数据存在时才能发送连接。即连接结果不能出现空行。 on 表示连接条件。其条件表达式与where类似。也可以省略条件(表示条件永远为真) 也可用where表示连接条件。还有 using, 但需字段名相同。 using(字段名)|select * from student s join student_course sc on s.id=sc.student_id;
交叉连接 cross join|没有条件的内连接。|select * from tb1 cross join tb2;
外连接(outer join)|如果数据不存在,也会出现在连接结果中。|select * from student outer join student_course on student.id = student_course.student_id;
左外连接 left join|如果数据不存在,左表记录会出现,而右表为null填充|select * from student left join student_course on student.id = student_course.student_id;
右外连接 right join|如果数据不存在,右表记录会出现,而左表为null填充|select * from student right join student_course on student.id = student_course.student_id;
自然连接(natural join)|自动判断连接条件完成连接。相当于省略了using,会自动查找相同字段名。|select info.id, info.name, info.stu_num, extra_info.hobby, extra_info.sex from info, extra_info where info.stu_num = extra_info.stu_id;

2.where

注释

 --this is a comment

关键--加注释,单行注释。

 /* comments */

关键/**/,多行注释。

五,什么是好的sql语句

尽量简单,模块化

易读,易维护

节省资源

内存

cpu

扫描的数据块要少

少排序

不造成死锁

五,什么是好的sql语句

尽量简单,模块化

易读,易维护

节省资源

内存

cpu

扫描的数据块要少

少排序

不造成死锁

3.group by

order by

 SELECT * FROM TB_BOOK_TAG ORDER BY name;

关键字:order by 字段名,按该字段所属列的首字母进行排序。要确保该子句是select语句中最后一条子句,否则会出现错误。

 SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY publisher,pubdate;

关键字:order by 字段名 字段名,首先按publisher进行排序,然后按照pubdate进行排序。对于第二个字段的排序,当且仅当具有多个相同的publisher时才会对其按照pubdate进行排序,如果publisher列中所有值都是唯一的,则不会按pubdate进行排序。

六,sql语句的处理过程

sql语句的四个处理阶段:

 

1010cc时时彩标准版 3

解析(PARSE):

检查语法

检查语义和相关的权限

在共享池中查找sql语句

合并(MERGE)视图定义和子查询

确定执行计划

绑定(BIND)

在语句中查找绑定变量

赋值(或重新赋值)

执行(EXECUTE)

应用执行计划

执行必要的I/O和排序操作

提取(FETCH)

从查询结果中返回记录

必要时进行排序

使用ARRAY FETCH机制

六,sql语句的处理过程

sql语句的四个处理阶段:

1010cc时时彩标准版 4

解析(PARSE):

检查语法

检查语义和相关的权限

在共享池中查找sql语句

合并(MERGE)视图定义和子查询

确定执行计划

绑定(BIND)

在语句中查找绑定变量

赋值(或重新赋值)

执行(EXECUTE)

应用执行计划

执行必要的I/O和排序操作

提取(FETCH)

从查询结果中返回记录

必要时进行排序

使用ARRAY FETCH机制

4.having

desc

SELECT publisher,pubdate FROM TB_BOOK_ENTITY ORDER BY pubdate DESC;

关键字:desc,order by 默认是按升序进行排序,当在字段名后加desc后,将对该字段进行降序排列。

SELECT pubdate,price FROM TB_BOOK_ENTITY ORDER BY pubdate DESC,price;

pubdate按降序排列,price,仍然按照升序排列(在pubdate相同的行)。所以,如果想在多个列上进行降序,必须对每一列都指定desc关键字。

七,sql表的基本连接方式

表连接有几种?

sql表连接分成外连接、内连接和交叉连接。

新建两张表:

表1:student  截图如下:

 

1010cc时时彩标准版 5

表2:course  截图如下:

 

1010cc时时彩标准版 6

(此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键。)

一、外连接

外连接可分为:左连接、右连接、完全外连接。

1、左连接  left join 或 left outer join

SQL语句:select * from student left join course on student.ID=course.ID

执行结果:

 

1010cc时时彩标准版 7

左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).

注:此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。

2、右连接  right join 或 right outer join

SQL语句:select * from student right join course on student.ID=course.ID

执行结果:

 

1010cc时时彩标准版 8

右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。

注:同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。

3、完全外连接  full join 或 full outer join

SQL语句:select * from student full join course on student.ID=course.ID

执行结果:

 

1010cc时时彩标准版 9

完全外连接包含full join左右两表中所有的行,如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL),如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。

二、内连接  join 或 inner join

SQL语句:select * from student inner join course on student.ID=course.ID

执行结果:

 

1010cc时时彩标准版 10

inner join 是比较运算符,只返回符合条件的行。

此时相当于:select * from student,course where student.ID=course.ID

三、交叉连接 cross join

1.概念:没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

SQL语句:select * from student cross join course

执行结果:

 

1010cc时时彩标准版 11

如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student cross join course where student.ID=course.ID

此时将返回符合条件的结果集,结果和inner join所示执行结果一样。

七,sql表的基本连接方式

表连接有几种?

sql表连接分成外连接内连接交叉连接。

新建两张表:

表1:student  截图如下:

1010cc时时彩标准版 12

表2:course  截图如下:

1010cc时时彩标准版 13

(此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键。)

一、外连接

外连接可分为:左连接、右连接、完全外连接。

1、左连接  left join 或 left outer join

SQL语句:select * from student left join course on student.ID=course.ID

执行结果:

1010cc时时彩标准版 14

左外连接包含left join左表所有行,如果左表中某行在右表没有匹配,则结果中对应行右表的部分全部为空(NULL).

注:此时我们不能说结果的行数等于左表数据的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。

2、右连接  right join 或 right outer join

SQL语句:select * from student right join course on student.ID=course.ID

执行结果:

1010cc时时彩标准版 15

右外连接包含right join右表所有行,如果左表中某行在右表没有匹配,则结果中对应左表的部分全部为空(NULL)。

注:同样此时我们不能说结果的行数等于右表的行数。当然此处查询结果的行数等于左表数据的行数,因为左右两表此时为一对一关系。

3、完全外连接  full join 或 full outer join

SQL语句:select * from student full join course on student.ID=course.ID

执行结果:

1010cc时时彩标准版 16

完全外连接包含full join左右两表中所有的行,如果右表中某行在左表中没有匹配,则结果中对应行右表的部分全部为空(NULL),如果左表中某行在右表中没有匹配,则结果中对应行左表的部分全部为空(NULL)。

二、内连接  join 或 inner join

SQL语句:select * from student inner join course on student.ID=course.ID

执行结果:

1010cc时时彩标准版 17

inner join 是比较运算符,只返回符合条件的行。

此时相当于:select * from student,course where student.ID=course.ID

三、交叉连接 cross join

1.概念:没有 WHERE 子句的交叉联接将产生连接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。

SQL语句:select * from student cross join course

执行结果:

1010cc时时彩标准版 18

如果我们在此时给这条SQL加上WHERE子句的时候比如SQL:select * from student cross join course where student.ID=course.ID

此时将返回符合条件的结果集,结果和inner join所示执行结果一样。

5.select

where

SELECT * FROM TB_BOOK_TAG WHERE count = 1;

关键字:where,指定搜索条件进行过滤。where子句在表名(from子句)之后给出。在同时使用whereorder by时,应该让order by位于where之后。

操作符 说明
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于
!< 不小于
> 大于
>= 大于等于
!> 不大于
BETWEEN 在指定的两个值之间
IS NULL 为NULL值

注意:NULL和非匹配
通过过滤选择不包含(如<>)指定值的所有行时,你可能希望返回含NULL值的行,但是这做不到,因为NULL有特殊的含义,数据库不知道它们是否匹配,所以在进行匹配过滤或非匹配过滤时,不会返回这些结果。

八,sql优化最佳实践

1,选择最有效率的表连接顺序

首先要明白一点就是SQL 的语法顺序和执行顺序是不一致的

SQL的语法顺序:

    select   【distinct】 ....from ....【xxx  join】【on】....where....group by ....having....【union】....order by......

SQL的执行顺序:

   from ....【xxx  join】【on】....where....group by ....avg()、sum()....having....select   【distinct】....order by......

from 子句--执行顺序为从后往前、从右到左

表名(最后面的那个表名为驱动表,执行顺序为从后往前, 所以数据量较少的表尽量放后)

where子句--执行顺序为自下而上、从右到左

将可以过滤掉大量数据的条件写在where的子句的末尾性能最优

group by 和order by 子句执行顺序都为从左到右

select子句--少用*号,尽量取字段名称。 使用列名意味着将减少消耗时间。

2,避免产生笛卡尔积

含有多表的sql语句,必须指明各表的连接条件,以避免产生笛卡尔积。N个表连接需要N-1个连接条件。

3,避免使用*

当你想在select子句中列出所有的列时,使用动态sql列引用“*”是一个方便的方法,不幸的是,是一种非常低效的方法。sql解析过程中,还需要把“*”依次转换为所有的列名,这个工作需要查询数据字典完成!

4,用where子句替换having子句

where子句搜索条件在进行分组操作之前应用;而having自己条件在进行分组操作之后应用。避免使用having子句,having子句只会在检索出所有纪录之后才对结果集进行过滤,这个处理需要排序,总计等操作。如果能通过where子句限制记录的数目,那就能减少这方面的开销。

5,用exists、not exists和in、not in相互替代

原则是哪个的子查询产生的结果集小,就选哪个

select * from t1 where x in (select y from t2)

select * from t1 where exists (select null from t2 where y =x)

IN适合于外表大而内表小的情况;exists适合于外表小而内表大的情况

6,使用exists替代distinct

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在select子句中使用distinct,一般可以考虑使用exists代替,exists使查询更为迅速,因为子查询的条件一旦满足,立马返回结果。

低效写法:

select distinct dept_no,dept_name from dept d,emp e where d.dept_no=e.dept_no

高效写法:

select dept_no,dept_name from dept d where  exists (select 'x' from emp e where e.dept_no=d.dept_no)

备注:其中x的意思是:因为exists只是看子查询是否有结果返回,而不关心返回的什么内容,因此建议写一个常量,性能较高!

用exists的确可以替代distinct,不过以上方案仅适用dept_no为唯一主键的情况,如果要去掉重复记录,需要参照以下写法:

select * from emp  where dept_no exists (select Max(dept_no)) from dept d, emp e where e.dept_no=d.dept_no group by d.dept_no)

7,避免隐式数据类型转换

隐式数据类型转换不能适用索引,导致全表扫描!t_tablename表的phonenumber字段为varchar类型

以下代码不符合规范:

select column1 into i_l_variable1 from t_tablename where phonenumber=18519722169;

应编写如下:

select column1 into i_lvariable1 from t_tablename where phonenumber='18519722169';

8,使用索引来避免排序操作

在执行频度高,又含有排序操作的sql语句,建议适用索引来避免排序。排序是一种昂贵的操作,在一秒钟执行成千上万次的sql语句中,如果带有排序操作,往往会消耗大量的系统资源,性能低下。索引是一种有序结果,如果order by后面的字段上建有索引,将会大大提升效率!

9,尽量使用前端匹配的模糊查询

例如,column1 like 'ABC%'方式,可以对column1字段进行索引范围扫描;而column1 kike '

本文由1010cc时时彩标准版发布于1010cc三分网站,转载请注明出处:1010cc时时彩标准版高效sql性能优化,必知必会

关键词:

上一篇:【1010cc时时彩标准版】质量调优,SQLOS的职分调解

下一篇:没有了