在我们开发的工程中，有时候会报
 [Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ------
 这种异常 不用多想，肯定是我们的sql语句出现问题，下面我只说我经常犯的一种错误。当然还有很多种错误，我们遇到再说。
 
 这时候 我们不难发现 错误的原因是我们这里多了一个 ，号
 
有时候也会出现这种错误 [Err] 1054 - Unknown column ‘----------’ in 'field list’
 
 这时候是我们的字段写错了 我们会发现数据库中并没有这个字段 而是我们的字段写错了
 
下面为大家写上两个同级sql
 
select a.Attribute_Id as Attribute_Id,          
       a.Attribute_Name as Attribute_Name,      
       a.Attribute_Alias as Attribute_Alias,
       a.Attribute_Unit as Attribute_Unit,
       a.DeviceType_Id as DeviceType_Id,
       a.SortCode as SortCode,
       a.UpdateTime as UpdateTime,   
       a.isdel as isDel
			from  zz_deviceattribute as a
where   Attribute_Id in(
select Attribute_Id from ZZ_DeviceAttributeRelation
where Device_Id =  '05871a37-a242-40f9-bbea-33bdea7009d5')
 
 select distinct 
 				a.Attribute_Id as Attribute_Id,
				a.Attribute_Name as Attribute_Name,
				a.Attribute_Alias as Attribute_Alias,
				a.Attribute_Unit as Attribute_Unit,
				a.DeviceType_Id as DeviceType_Id,
				a.SortCode as SortCode,
				a.UpdateTime as UpdateTime,   
				a.isdel as isDel        
		from ZZ_DeviceAttributeRelation  as b
				inner join zz_deviceattribute as a 
				on a.Attribute_Id = b.Attribute_Id
				where b.Device_Id =  '05871a37-a242-40f9-bbea-33bdea7009d5'
				and a.isDel ='0'
				order by a.SortCode
 

 欢迎大家加入技术群聊

 
 
 
  
 
   

    前面一篇文章从实例的角度进行数据库优化，通过配置一些参数让数据库性能达到最优。但是一些“不好”的SQL也会导致数据库查询变慢，影响业务流程。本文从SQL角度进行数据库优化，提升SQL运行效率。
   
 
  
 
 
 
 
 
  
判断问题SQL
 
  
判断SQL是否有问题时可以通过两个表象进行判断：
 
  
 
    

     系统级别表象
    
 
    
 
      

       CPU消耗严重
      
 
 
      

       IO等待严重
      
 
 
      

       页面响应时间过长
      
 
 
      

       应用的日志出现超时等错误
      
 
  
可以使用sar命令，top命令查看当前系统状态。
 
  
也可以通过Prometheus、Grafana等监控工具观察系统状态。（感兴趣的可以翻看我之前的文章）
 
  
 
    

     SQL语句表象
    
 
    
 
      

       冗长
      
 
 
      

       执行时间过长
      
 
 
      

       从全表扫描获取数据
      
 
 
      

       执行计划中的rows、cost很大
      
 
  
冗长的SQL都好理解，一段SQL太长阅读性肯定会差，而且出现问题的频率肯定会更高。更进一步判断SQL问题就得从执行计划入手，如下所示：
 
  
执行计划告诉我们本次查询走了全表扫描Type=ALL，rows很大(9950400)基本可以判断这是一段"有味道"的SQL。
 
  
获取问题SQL
 
  
不同数据库有不同的获取方法，以下为目前主流数据库的慢查询SQL获取工具
 
  
 
    
 
     
MySQL
 
    
 
    
 
      

       慢查询日志
      
 
 
      

       测试工具loadrunner
      
 
 
      

       Percona公司的ptquery等工具
      
 
 
    
 
     
Oracle
 
    
 
    
 
      

       AWR报告
      
 
 
      

       测试工具loadrunner等
      
 
 
      

       相关内部视图如v$、$session_wait等
      
 
 
      

       GRID CONTROL监控工具
      
 
 
    
 
     
达梦数据库
 
    
 
    
 
      

       AWR报告
      
 
 
      

       测试工具loadrunner等
      
 
 
      

       达梦性能监控工具（dem）
      
 
 
      

       相关内部视图如v$、$session_wait等
      
 
  
SQL编写技巧
 
  
SQL编写有以下几个通用的技巧：
 
  
• 合理使用索引
 
  
 
   
索引少了查询慢；索引多了占用空间大，执行增删改语句的时候需要动态维护索引，影响性能 选择率高（重复值少）且被where频繁引用需要建立B树索引；
 
   
一般join列需要建立索引；复杂文档类型查询采用全文索引效率更好；索引的建立要在查询和DML性能之间取得平衡；复合索引创建时要注意基于非前导列查询的情况
 
  
 
  
• 使用UNION ALL替代UNION
 
  
 
   
UNION ALL的执行效率比UNION高，UNION执行时需要排重；UNION需要对数据进行排序
 
  
 
  
• 避免select * 写法
 
  
 
   
执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。
 
  
 
  
• JOIN字段建议建立索引
 
  
 
   
一般JOIN字段都提前加上索引
 
  
 
  
• 避免复杂SQL语句
 
  
 
   
提升可阅读性；避免慢查询的概率；可以转换成多个短查询，用业务端处理
 
  
 
  
• 避免where 1=1写法
 
  
• 避免order by rand()类似写法
 
  
 
   
RAND()导致数据列被多次扫描
 
  
 
  
SQL优化
 
  
执行计划
 
  
完成SQL优化一定要先读执行计划，执行计划会告诉你哪些地方效率低，哪里可以需要优化。我们以MYSQL为例，看看执行计划是什么。（每个数据库的执行计划都不一样，需要自行了解）explain sql
 
  
字段
解释
id
每个被独立执行的操作标识，标识对象被操作的顺序，id值越大，先被执行，如果相同，执行顺序从上到下
select_type
查询中每个select 字句的类型
table
被操作的对象名称，通常是表名，但有其他格式
partitions
匹配的分区信息(对于非分区表值为NULL)
type
连接操作的类型
possible_keys
可能用到的索引
key
优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了“坏味道”
key_len
被优化器选定的索引键长度，单位是字节
ref
表示本行被操作对象的参照对象，无参照对象为NULL
rows
查询执行所扫描的元组个数（对于innodb，此值为估计值）
filtered
条件表上数据被过滤的元组个数百分比
extra
执行计划的重要补充信息，当此列出现Using filesort , Using temporary 字样时就要小心了，很可能SQL语句需要优化
  
接下来我们用一段实际优化案例来说明SQL优化的过程及优化技巧。
 
  
优化案例
 
  
 
    
 
     
表结构
 
     
CREATE TABLE `a`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULLAUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULLAUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
 
    
 
 
    
 
     
三张表关联，查询当前用户在当前时间前后10个小时的订单情况，并根据订单创建时间升序排列，具体SQL如下
 
     
select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；
 
    
 
 
    
 
     
查看数据量  
 
     
 
    
 
 
    
 
     
原执行时间
 
    
 
 
    
 
     
原执行计划
 
    
 
 
    
 
     
初步优化思路
 
    
 
  
 
    

     SQL中 where条件字段类型要跟表结构一致，表中
     user_id 为varchar(50)类型，实际SQL用的int类型，存在隐式转换，也未添加索引。将b和c表
     user_id 字段改成int类型。
    
 
 
    

     因存在b表和c表关联，将b和c表
     user_id创建索引
    
 
 
    

     因存在a表和b表关联，将a和b表
     seller_name字段创建索引
    
 
 
    

     利用复合索引消除临时表和排序
    
 
  
 
   
初步优化SQL
 
   
alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
 
  
 
  
 
   
查看优化后执行时间
 
   
 
  
 
  
 
   
查看优化后执行计划
 
  
 
  
 
   
查看warnings信息
 
  
 
  
 
   
继续优化alter table a modify "gmt_create" datetime DEFAULT NULL;
 
  
 
  
 
   
查看执行时间
 
   
 
  
 
  
 
   
查看执行计划
 
  
 
  
 
   
总结
 
  
 
  
 
    

     查看执行计划 explain
    
 
 
    

     如果有告警信息，查看告警信息 show warnings;
    
 
 
    

     查看SQL涉及的表结构和索引信息
    
 
 
    

     根据执行计划，思考可能的优化点
    
 
 
    

     按照可能的优化点执行表结构变更、增加索引、SQL改写等操作
    
 
 
    

     查看优化后的执行时间和执行计划
    
 
 
    

     如果优化效果不明显，重复第四步操作
    
 
  

    
  
 
  
系列文章
 
  

    
  
 
  
数据库优化 - 实例优化 
记一次对达梦数据库的优化过程
  

    
  
 
 
 
 
 
  
 
   
 
    
 
     
 
      
 
       
 
        
温馨提示
 
       
 
      
 
      
 
       
 
        
如果你喜欢本文，请关注我的个人公众号！或者关注我的个人博客www.javadaily.cn
 
       
 
      
 
     
 
    
 
   
 
  
 
  

   
  
 
  
 
 
 
 
 
 
 

              SQL Server 2014安装图解与问题分析
 
1 SQLServer2014简介
 
SQL Server2014 是Microsoft 公司推出的关系型数据库管理系统。它用于大规模联机事务处理（OLTP）、数据仓库和电子商务应用的数据库平台；也是用于数据集成、分析和报表解决方案的商业智能平台。
 
 
 
注：
 
 
1 SQL案例部分详见博客专栏链接 ：
 
 
                                    SQL基础操作_110个微案例  用户自定义存储过程实战
 
 
                                    Kettle应用实战与50个案例代码
 
 
2 其它数据库安装链接: Mysql 8(多实例)Windows解压配置安装      
 
 
                                     SQL Server 2017(2019) Linux安装手册 
 
 
                                     MySQL 8.0 CentOS 7安装手册 
 
 
3 Win10 安装时的"需要microsoft.net framework 3.5 service pack 1"错误见文末的问题分析。
 
 
4 如果仅是为了学习SQL建议安装mysql。
 
 
准备
 
2.1 环境说明
 
SQL Server 2014可以安装在一下window平台上(更多可参见官网介绍)：
 
1 Win7 64位
 
2 Win10 专业版，详见文末附图
 
3 Windows Server 2008 R2, Windows Server 2008 R2 SP1, Windows Server 2012, Windows Server 2012 R2
 
 
 安装
 
3.1下载软件
 
通过下载工具下载SQL Server 2014企业版,链接见下:
 
ed2k://|file|cn_sql_server_2014_enterprise_edition_x64_dvd_3932882.iso|2898847744|A33CE10CD989083D1AD882DF0D56CFBE|/
 
如果链接失效，可参见如下链接（或联系博主免费索取）：
 
 
 
链接：https://pan.baidu.com/s/1S16qceng24o8Ayc1v_WSEg 
提取码：2jjg
 
 
3.2 安装
 
1 将下载好的iso文件解压到指定目录下.,比如D:\temp下.然后点击setup开始安装.
 
 
2 点击左侧的安装选项,然后选择全新安装
 
 
3 输入产品密钥,然后点击下一步.
 
 
4 选中接受条款,然后点击下一步
 
 
5 进入安装规则,点击下一步.
 
 
6 进入设置角色项,选择要安装的SQL Server的类型.
 
 
7 选择SQL Server的功能,勾选相关服务后,根据需要更改安装目录.
 
 
8 功能规则
 
 
9 设置实例ID的名称,这里一般选择默认值.如果在一台机器里安装两个实例,则则需要新起个名字.
 
 
10 进入服务器配置选项,这里账户可以按照指定的用户,也可以按照默认值.
 
 
11 进入数据库引擎配置项,这里可以指定混合模式并设置SA密码,添加当前用户为SQL Server管理员.
 
 
12 选择SSAS服务配置,这里一般选择”多维和数据挖掘模式”,同时指定当前用户是SSAS服务的管理员.
 
 
13 SSRA服务配置,这里选择”安装和配置”
 
 
14 指定Distributed Replay的访问权限,这里选择的是当前用户.
 
 
15 指定控制器的名称并指定相关目录.
 
 
16 进入准备安装选项,点击安装.
 
 
17 耐心等待直至安装完成.出现下图界面时点击关闭,完成安装.
 
 
 
 
使用
 
4.1 SSMS功能验证
 
SSMS 是SQL Server 2014 Management Studio的缩写,是与DBE(数据库引擎).SSAS(多维数据库),数据报表引擎(SSRS),数据流引擎(SSIS)进行图形化交互的集成工具.如下章节主要是验证SSMS的使用.
 
 1 首先打开SSMS,在”开始”→”所有程序”找到” Microsoft SQL Server 2014”然后点击” SQL Server 2014 Management Studio”
 
 
2 点击要连接的数据库引擎,这里默认是本地数据库引擎,缩写成”.”,点击连接进入SSMS.
 
 
 
3 如下图所示新建使用当前连接生成一个查询窗口.
 
 
4 执行SQL语句,查看当前安装的SQL Server的版本信息.
 
 
 
问题分析
 
针对Win10 一般在功能规则环节会出现“需要microsoft.net framework 3.5 service pack 1”的错误，即SQL Server 2014依赖于.net framework 3.5 sp1。
 
 
解决方法
 
 
 
1）方法1 Windows系统更新
 Step1： 同时按住键盘上微标和字母r，调出window命令行。
 Step2：命令行里输入control 进入控制面板
 
 
Step3：找到程序和功能
 Step4：点击启用或关闭Windows功能
 
 
Step5：勾选".NET Framework 3.5(包括.NET2.0和3.0)"以及它的子项
 
 
Step6：等待系统更新完毕
 
 
Step7：重新启动SQL Server2014安装脚本
 
 
 
 
2） 方法2：到微软官方网站找到.net framework 3.5 service pack 1的安装包，自行安装。
 
 
延展阅读
 
1） 查看当前.net framework版本信息，可按如下操作（微标+r后输入cmd再输入如下命令查看）
 
reg query "HKLM\Software\Microsoft\NET Framework Setup\NDP" /s /v version | findstr /i version | sort /+26 /r
 
 
2） 其它安装错误可按照界面提示信息自行排查、修复。
 
3）Win10安装后附图：
 
 
4) 查看SQL Server版本号、内部版本号
 
-- 查看SQL Server产品、版本、内部版本号等信息
SELECT RIGHT(LEFT(@@VERSION,26),16) N'产品版本编号', 
 SERVERPROPERTY('ProductVersion') N'版本编号',
 SERVERPROPERTY('ProductLevel') N'版本层级',
 SERVERPROPERTY('Edition') N'版本',
 DATABASEPROPERTYEX('master','Version') N'内部版本号码',
 @@VERSION N'版本编号、处理器架构、日期和操作系统等'
 
 
各版本与内部版本号对应关系汇总 表见下：
 

官网下载地址：https://www.microsoft.com/zh-cn/download/details.aspx?id=29066
 
 
选择图片中的三个，然后点击下载 ，文件内存很大
 
 
 
 
 
下载以后，如图所示，双击.exe程序进行安装
 
 
 
 
安装结束以后，就会有SQLFULL_x64_CHS文件夹生成
 
 
打开SQLFULL_x64_CHS文件夹，双击SETUP应用程序，开始安装SQL
 
 
选择安装-全新sql......（第一个）
 
 
安装成功以后点击确定
 
 
选择第一个如下图所示
 
 
即使出错，也点击下一步
 
 
 安装程序支持规则；
 
 
选择设置角色第一个
 
 
建议全选；由于SQL占用空间较大，本机修改了功能目录存储位置； 
 
两个位置不能一样，不然会和我一样报错，如下图所示：（我改过来了，一个是sqlserver2012，一个是server文件夹）
 
 
安装规则
 
 
默认实例，可以更改文件夹位置
 
 
磁盘要求
 
 
 
 
服务器设置，直接下一步
 
 
 数据库引擎配置 
 
1、混合模式
 
2、设置密码，要记得，越简单越好如123456
 
3、添加当前用户
 
4、如下图所致
 
 
 
 
Analysis Services配置，选择“添加当前用户”；
 
 
Reporting Services配置，默认不用改就好了
 
 
 
 
分布式重播控制器，点击“添加当前用户”；
 
 
随意填一个控制器名称，然后下一步
 
 
错误报告
 
 
安装配置规则； 
 
 
 
安装完成，到这里就彻底结束了，我真的想说，好长啊，好占内存啊
 
 
 
 
 
双击SQL server Management Studiol
 
 
选择
 
身份验证：SQL SERVER身份验证
 
登录名：sa
 
密码：安装的时候设置的密码
 
 
您的支持是我分享经验的源泉