① 请简述项目中优化sql语句执行效率的方法,从哪些方面,sql语句性能如何分析
1. SQL优化的原则是：将一次操作需要读取的BLOCK数减到最低,即在最短的时间达到最大的数据吞吐量。
调整不良SQL通常可以从以下几点切入：
? 检查不良的SQL，考虑其写法是否还有可优化内容
? 检查子查询 考虑SQL子查询是否可以用简单连接的方式进行重新书写
? 检查优化索引的使用
? 考虑数据库的优化器
2. 避免出现SELECT * FROM table 语句，要明确查出的字段。
3. 在一个SQL语句中，如果一个where条件过滤的数据库记录越多，定位越准确，则该where条件越应该前移。
4. 查询时尽可能使用索引覆盖。即对SELECT的字段建立复合索引，这样查询时只进行索引扫描，不读取数据块。
5. 在判断有无符合条件的记录时建议不要用SELECT COUNT （*）和select top 1 语句。
6. 使用内层限定原则，在拼写SQL语句时，将查询条件分解、分类，并尽量在SQL语句的最里层进行限定，以减少数据的处理量。
7. 应绝对避免在order by子句中使用表达式。
8. 如果需要从关联表读数据，关联的表一般不要超过7个。
9. 小心使用 IN 和 OR，需要注意In集合中的数据量。建议集合中的数据不超过200个。
10. <> 用 < 、 > 代替，>用>=代替，<用<=代替，这样可以有效的利用索引。
11. 在查询时尽量减少对多余数据的读取包括多余的列与多余的行。
12. 对于复合索引要注意，例如在建立复合索引时列的顺序是F1，F2，F3，则在where或order by子句中这些字段出现的顺序要与建立索引时的字段顺序一致，且必须包含第一列。只能是F1或F1，F2或F1，F2，F3。否则不会用到该索引。
13. 多表关联查询时，写法必须遵循以下原则，这样做有利于建立索引，提高查询效率。格式如下select sum（table1.je） from table1 table1, table2 table2, table3 table3 where (table1的等值条件（=）) and (table1的非等值条件) and (table2与table1的关联条件) and (table2的等值条件) and (table2的非等值条件) and (table3与table2的关联条件) and (table3的等值条件) and (table3的非等值条件)。
注:关于多表查询时from 后面表的出现顺序对效率的影响还有待研究。
14. 子查询问题。对于能用连接方式或者视图方式实现的功能，不要用子查询。例如：select name from customer where customer_id in ( select customer_id from order where money>1000)。应该用如下语句代替：select name from customer inner join order on customer.customer_id=order.customer_id where order.money>100。
15. 在WHERE 子句中，避免对列的四则运算，特别是where 条件的左边，严禁使用运算与函数对列进行处理。比如有些地方 substring 可以用like代替。
16. 如果在语句中有not in（in）操作，应考虑用not exists（exists）来重写,最好的办法是使用外连接实现。
17. 对一个业务过程的处理，应该使事物的开始与结束之间的时间间隔越短越好，原则上做到数据库的读操作在前面完成，数据库写操作在后面完成，避免交叉。
18. 请小心不要对过多的列使用列函数和order by,group by等，谨慎使用disti软件开发t。
19. 用union all 代替 union，数据库执行union操作，首先先分别执行union两端的查询，将其放在临时表中，然后在对其进行排序，过滤重复的记录。
当已知的业务逻辑决定query A和query B中不会有重复记录时，应该用union all代替union，以提高查询效率。
② 如何在EXCEL中使用SQL进行数据处理与分析
在EXCEL中使用SQL进行数据处理与分析步骤有：
工具原料：excel2013版本
打开“excel”，在“数据”选项卡中，找到“自其他来源”；
③ 怎样分析sql语句的执行计划
写好一段SQL代码以后，可以通过查看SQL的执行计划，初步预测该SQL在运行时的性能好坏，尤其是在sql调优时，我们可以通过查看执行计划， 来分析sql性能问题，本文简单介绍怎么在plsql中查看SQL语句的执行计划。
http://jingyan..com/article/ab69b270bffc2e2ca7189fee.html④ 如何用SQL分析电商用户行为数据（案例）
本文以“淘宝用户行为数据集”的分析全过程为例，展示数据分析的全过程
——使用工具：MySQL，Excel，Navicat，PowerBI
——分析类型：描述分析，诊断分析
——分析方法：漏斗分析，用户路径分析，RFM用户价值分析，活跃/存留分析，帕累托分析，假设验证分析。
（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）
（目录如下）
1.分析流程和方法
当没有清晰的数据看板时我们需要先清洗杂乱的数据，基于分析模型做可视化，搭建描述性的数据看板。
然后基于描述性的数据挖掘问题，提出假设做优化，或者基于用户特征数据进行预测分析找规律，基于规律设计策略。简单来说：
——描述性分析就是：“画地图”
——诊断性分析就是：“找问题”
——预测性分析就是 ：“找规律”
在数据分析中有两个典型的场景：
一种是有数据，没有问题：需要先整体分析数据，然后再根据初步的描述分析，挖掘问题做诊断性分析，提出假设，设计策略解决问题。
另一种是已经发现了问题，或者已经有了假设，这种做数据分析更偏向于验证假设。
2.淘宝用户行为分析
本次是对“淘宝用户行为数据集”进行分析，在分析之前我们并不知道有什么问题，所以需要先进行描述性分析，分析数据挖掘问题。
我们首先来看下这个数据集的元数据：
根据以上数据字段我们可以拿用户行为为主轴从纵深方向提出一些问题，然后再从数据中找答案
纵向：
——这个数据集中用户的日活跃和周活跃时间有什么规律吗？
——在当日活跃的用户次日，三日，四日……还有多少活跃？
深向：
——用户从浏览到购买的整体转化率怎么样？
——用户从浏览到购买的路径是怎么样子的？
——平台主要会给用户推送什么商品？
——用户喜欢什么类目？喜欢什么商品？
——怎么判断哪些是高价值用户 ？
下面是叮当整理的常用分析方法：
我们可以给前面的问题匹配一下分析方法，便于后面的分析：
为了便于后面的数据分析，在分析之前我们需要先对做一下清洗
看元数据（字段解释，数据来源，数据类型，数据量……）初步发现问题为之后的处理做准备。
确定缺失值范围，去除不需要字段，填充缺失内容
根据元数据格式和后续分析需要的格式对数据进行处理
去除重复值，异常值
——去除重复值：并把用户ID,商品ID，时间戳设置为主键
——异常值处理：查询并删除2017年11月25日至2017年12月3日之外的数据
查询并删除小于2017-11-25的
——验证数据：
——分析思路：
——SQL提数：
——Excel可视化：
活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。
用户在周六周日相比其他时间更活跃（周六周日为休息日，用户有更多时间）
一天内用户活跃的最高峰期为21点（用户在这个时间段空闲较多）
——分析思路：
——SQL提数：
列出每用户每天及当天后面又活跃的日期，并创建“活跃时间间隔表”用于后面求次日存留，三日存留……
对“活跃时间间隔表视图”引用进行分组统计，计算每日存留人数并创建视图
对存留人数表进行计算，统计活跃用户留存率
——Excel可视化：
——分析思路：
——SQL提数：
-把各种用户行为分离出来并创建视图方便后续查询用户行为数据
查询整体数据漏斗
——Excel可视化：
用户从浏览到购买整体转化率2.3%，具体主要在哪个环节流失还需要再细分用户路径分析
——分析思路：
——SQL提数：
——PowerBI可视化：
用户从浏览到购买的路径主要有4条，路径越长转化率越底
路径1：浏览→购买：转化率1.45%
路径2：浏览→加购物车→购买：转化率0.33
路径3：浏览→收藏→购买：转化率0.11%
路径4：浏览→收藏→加购物车→购买：转化率0.03%
——分析思路：
——SQL提数：
——Excel可视化：
——描述性分析：
浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。
浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。
——分析思路：
——SQL提数：
查询计算商品转化率，升序排列，取前100个
——Excel可视化：
——描述性分析：
从商品看：有17款商品转化率超过了1。
从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。
——分析思路：
用户价值分析常用的分析方式是RFM模型
本次分析中的R,F,M具体定义（仅用于演示分析方法，无实际业务参考价值）：
——SQL取数与分析：
1）建立打分标准：先计算R,F的值，并排序，根据R,F值最大值和最小值得区间设计本次得打分标准
-查询并计算R，F值创建视图
-引用RF数值表，分别查询R,F的最大值和最小值
-结合人工浏览的建立打分标准
2）给R，F按价值打分
3）计算价值的平均值
4）用平均值和用户分类规则表比较得出用户分类
——Excel可视化
通过描述性分析得到可视化的数据后我们一般会先看一下是否符合业务常识
如果符合常识接下来我们会通过与行业平均数据和本产品的同比环比对比看是否正常，如果不正常就要找原因，设计解决方案，如果正常那就看是否有可以优化的地方。
我们首先来看一下这些描述性分析是否符合业务常识和指标是否正常：
1.活跃曲线整体为上升状态，同为周六日，12月2号，3号相比11月25日，26日活跃度更高。
2.用户在周六周日相比其他时间更活跃
3.一天内用户活跃的最高峰期为21点
4.从2017年11月15日致2017年12月3日，活跃用户新增38%
5.从2017年11月15日致2017年12月3日，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。
6.用户从浏览到购买整体转化率2.3%
7.用户从浏览到购买的路径主要有4条，路径越长转化率越低。
8.浏览量top100的商品浏览量呈阶梯分布，越靠前的阶梯之间的落差相对越大在这个阶梯中的商品越少，越靠后商品浏览量阶梯之间的落差相对越小，同阶梯内的商品越多。
9.浏览量TOP100的商品所属类目中，4756105，3607361，4357323三个类目浏览量远超其他类目。
10.从商品看：有17款商品转化率超过了1。
11.从类目看：这些商品所属类目分布均匀，除965809，4801426，2735466，2640118，5063620，4789432，2945933这7个类目之外，其他类目都只有一个商品在转化率TOP100的商品中。
根据以上诊断分析我们梳理出了以下假设，做假设验证。
假设1：这些商品中有高转化率的爆款商品
对比浏览量TOP5的商品，发现这些商品转化率在同一类目下并不高，假设不成立
假设2：4756105，3607361，4357323三个类目属于高频刚需类目
-创建类目购买频次表
-计算类目购买频次平均值
-查询4756105，3607361，4357323三个类目的购买频次
4756105，3607361，4357323三个类目的用户购买频次明显高于平均值，假设成立
假设3：有部分用户是未点击商详直接从收藏和购物车购买的。
用户不是直接从收藏和购物车购买的，只是后续复购未点击商详，假设不成立
假设4：淘宝推荐的商品主要是“同一类目下的高转化商品”
用Excel对浏览量TOP100的商品ID和转化率TOP100的商品ID进行去重，结果无重复值，假设不成立
3.结论：
1）用户活跃：用户活跃曲线整体呈上升趋势，在一周中周六，周日活跃度比平时更高，在一天中用户活跃曲线从凌晨4点开始往上升，在中午12点和下午5~6点有两个小低谷（吃饭），到晚上9点时活跃度达到顶峰。
2）用户留存：从2017年11月15日致2017年12月3日的用户留存数据来看，淘宝的用户留存数据较好，活跃用户次日留存增长18.67%，当日的活跃用户留存也在快速增长，第七日留存比次日留存高18.56%。
3）用户转化：整体转化2.3%，用户从浏览到购买的路径主要有4条，路径越长转化率越低。
4）平台推荐与用户偏好：从数据集中的数据来看，排除用户兴趣偏好标签，淘宝给用户用户推送的商品主要是高频刚需的类目，促使用户复购，流量回流平台。
以上结论受数据量和数据类型的影响，并不一定准确，仅用来练习数据分析方法。
（考虑到阅读体验文章中只放了SQL截图，如需PDF版本，再公众号后台回复“用户行为分析”领取）
⑤ 通过分析SQL语句的执行计划优化SQL
如何干预执行计划
-
-
使用hints提示
基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在Oracle中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。
hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：
1.
使用的优化器的类型
2.基于代价的优化器的优化目标，是all_rows还是first_rows。
3.
表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。
4.
表之间的连接类型
5.
表之间的连接顺序
6.
语句的并行程度
除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行定期的分析。
如何使用hints:
Hints只应用在它们所在sql语句块(statement
block，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。
我们可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT,
UPDATE,
or
DELETE关键字的后面
使用hints的语法：
{DELETE|INSERT|SELECT|UPDATE}
/*+
hint
[text]
[hint[text]]...
*/
or
{DELETE|INSERT|SELECT|UPDATE}
--+
hint
[text]
[hint[text]]...
注解：
1.DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。
2.“+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。
3.hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。
4.text
是其它说明hint的注释性文本
如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。⑥ 如何在EXCEL中使用SQL进行数据处理与分析
方法一：利用Microsoft Query使用SQL语句
⑦ 寻求高手解开sql语句
SQLServer数据库管理的常用SQL语句
1. 查看数据库的版本
select @@version
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname'
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8.查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列的，索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_proceres
查看存储过程和函数的源代码
sp_helptext '@procere_name'
查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock
进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3
检查死锁用sp_who_lock过程
sp_who_lock
11.收缩数据库日志文件的方法
收缩简单恢复模式数据库日志，收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法:
set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable('@error_table_name',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误，
以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。
修复完成后，请备份数据库。
repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。
这些修复可以很快完成，并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复(如重建索引)。
执行这些修复时不会有丢失数据的危险。⑧ sql数据分析是啥意思
sql数据分析是结构化查询语言。
结构化查询语言（Structured Query Language）简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式。
所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套，这使它具有极大的灵活性和强大的功能。
SQL具有数据定义、数据操纵、数据查询和数据控制的功能。
1、SQL数据定义功能：能够定义数据库的三级模式结构，即外模式、全局模式和内模式结构。在SQL中，外模式又叫做视图（View），全局模式简称模式（Schema），内模式由系统根据数据库模式自动实现，一般无需用户过问。
2、SQL数据操纵功能：包括对基本表和视图的数据插入、删除和修改，特别是具有很强的数据查询功能。
3、SQL的数据控制功能：主要是对用户的访问权限加以控制，以保证系统的安全性。
⑨ sql数据分析需要学什么
作为数据分析师，你首先需要从正在查询的数据库中读取数据。我们一般会采用这种方法：
了解SELECT语句的工作方式。这其中包括研究逻辑查询处理的工作方式。逻辑查询处理描述了数据库引擎执行SELECT语句的子句的顺序。了解它会引导你编写更好，更准确的查询。
研究联接的工作方式
内部联接是最有效的。左，右和完全外部联接的效率较低，但有时必须使用它们。内部联接可以导致行被过滤掉，外部联接不能导致过滤。研究加入，因此你知道何时使用每种类型。只有最简单的数据库查询才不涉及联接。
了解如何使用GROUP BY子句进行聚合
了解窗口函数/有序分析函数。这些是在SQL中进行分析的缩影。最初很难将你的头缠绕在它们周围，但是这是值得的。一旦了解了它们，便会一直使用它们。
了解数据库规范化。如果你了解规范化，你将了解为什么架构师以这种方式构造数据库的理论。这有助于编写查询并确定要联接的表。
了解实体关系图（ERD）的工作方式。大多数ERD是使用鱼尾纹符号构建的。确保知道基数和可选性约束是如何工作的，这将帮助你解密连接表时要使用的连接类型。
一旦掌握了这些知识，就应该扩展到学习SQL的DML和DDL子类别。DML代表数据操作语言，SELECT语句是该语言的一部分。DDL代表数据定义语言，这是数据库架构师用来创建表的语言。
本文禁止转载或摘编
本文为我原创
大数据
数据分析
数据分析师
1
收藏
分享
推荐文章
35岁转行数据分析师可以吗？
学习 · 56阅读
Excel之PowerQuery 提取单元格指定文字
学习 · 175阅读
[简述]答题验证系统部署说明
学习 · 865阅读
