本周技术研究部(TRD)的一名DBA 对我们编写SQL时的一些问题,进行了汇报讲演,以下是来自它的脚本,我在它讲演的基础上写出了自己想表述的,以便于大家相互交流学习
代码如下:/*
--注意:准备数据(可略过,非常耗时)
CREATE TABLE CHECK1_T1
(
ID INT,
C1 CHAR(8000)
)
CREATE TABLE CHECK1_T2
(
ID INT,
C1 CHAR(8000)
)
DECLARE @I INT
SET @I=1
WHILE @IBEGIN
INSERT INTO CHECK1_T1 SELECT @I,'C1'
INSERT INTO CHECK1_T2 SELECT 10000+@I,'C1'
SET @I=@I+1
END
CREATE TABLE CHECK2_T1
(
ID INT,
C1 CHAR(8000)
)
DECLARE @I INT
SET @I=1
WHILE @IBEGIN
INSERT INTO CHECK2_T1 SELECT @I,'C1'
SET @I=@I+1
END
INSERT INTO CHECK2_T1 VALUES(10001,'C2')
INSERT INTO CHECK2_T1 VALUES(10002,'C1')
CREATE TABLE CHECK3_T1
(
ID INT,
C1 CHAR(7000)
)
CREATE TABLE CHECK3_T2
(
ID INT,
C1 CHAR(7000)
)
DECLARE @I INT
SET @I=1
WHILE @IBEGIN
IF @I%2 =0
BEGIN
INSERT INTO CHECK3_T1 SELECT @I,'C1'
END
ELSE
BEGIN
INSERT INTO CHECK3_T1 SELECT @I,'C2'
END
IF @I%100=0
BEGIN
INSERT INTO CHECK3_T2 SELECT @I,'C1'
INSERT INTO CHECK3_T2 SELECT @I+50000,'C2'
END
SET @I=@I+1
END
CREATE TABLE CHECK4_T1
(
ID INT,
C1 CHAR(500),
)
DECLARE @I INT
SET @I=1
WHILE @IBEGIN
IF @I%100000 =0
BEGIN
INSERT INTO CHECK4_T1 SELECT @I,'C2'
END
ELSE
BEGIN
INSERT INTO CHECK4_T1 SELECT @I,'C1'
END
SET @I=@I+1
END
CREATE NONCLUSTERED INDEX NCIX_C1 ON CHECK4_T1(C1)
CREATE TABLE CHECK5_T1
(
ID INT,
C1 CHAR(10),
)
DECLARE @I INT
SET @I=1
WHILE @IBEGIN
INSERT INTO CHECK5_T1 SELECT @I,'C1'
IF @I%2=0
BEGIN
INSERT INTO CHECK5_T1 SELECT @I,'C1'
END
SET @I=@I+1
END
*/
--=====================================
--1、 Union all 代替 Union
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--测试一:(26s) 执行计划:表扫描->排序->合并联接
SELECT ID,C1 FROM CHECK1_T1 --1W条数据
UNION
SELECT ID,C1 FROM CHECK1_T2 --1W条数据
--测试二: (4s) 执行计划:表扫描->表扫描串联
SELECT ID,C1 FROM CHECK1_T1 --1W条数据
UNION ALL
SELECT ID,C1 FROM CHECK1_T2 --1W条数据
--总结:测试一中的union 排序和去重合并是相当耗时的,如果不要此功能,大数据时最好加上ALL
--=====================================
--2、 Exists 代替 Count(*)
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
----测试一: (7s) 执行计划:表扫描-> 流聚合-> 计算矢量
DECLARE @COUNT INT
SELECT @COUNT=COUNT(*) FROM CHECK2_T1 WHERE C1='C1' --1W条数据
IF @COUNT>0
BEGIN
PRINT 'S'
END
----测试二: (0s) 执行计划:常量扫描/表扫描-> 嵌套循环-> 计算标量
IF EXISTS(SELECT 1 FROM CHECK2_T1 WHERE C1='C1') --1W条数据
BEGIN
PRINT 'S'
END
--总结:判断是否存在,用Exist即可,没必要用COUNT(*)将表的所有记录统计出来,扫描一次
--=====================================
--3、 IN(Select COL1 From Table)的代替方式
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--测试一: (3s)执行计划:表扫描 -> 哈希匹配
SELECT ID,C1 FROM CHECK3_T2 --400行
WHERE ID IN (SELECT ID FROM CHECK3_T1 WHERE C1='C1') --2W行
--测试二:(1s)执行计划:表扫描-> 并行度 -> 位图 -> 排序 -> 合并联接 -> 并行度
SELECT A.ID,A.C1 FROM CHECK3_T2 A
INNER JOIN CHECK3_T1 B ON A.ID=B.ID WHERE B.C1='C1'
--测试三:(3s)执行计划:表扫描-> 哈希匹配
SELECT A.ID,A.C1 FROM CHECK3_T2 A
WHERE EXISTS (SELECT 1 FROM CHECK3_T1 B WHERE B.ID=A.ID AND B.C1='C1')
--总结:能用INNER JOIN 尽量用它,SQL SERVER在查询时会将关联表进行优化
--=====================================
--4、 Not Exists 代替 Not In
--测试一:(8s) 执行计划:表扫描-> 嵌套循环 -> 哈希匹配
SELECT ID,C1 FROM CHECK3_T1 --2W行
WHERE ID NOT IN (SELECT ID FROM CHECK3_T2 WHERE C1='C1') --400行
--测试二:(4s) 执行计划:表扫描-> 哈希匹配
SELECT A.ID,A.C1 FROM CHECK3_T1 A
WHERE NOT EXISTS (SELECT 1 FROM CHECK3_T2 B WHERE B.ID=A.ID AND B.C1='C1')
--总结:尽量不使用NOT IN ,因为会调用嵌套循环,建议使用NOT EXISTS代替NOT IN
--=====================================
--5、 避免在条件列上使用任何函数
DROP TABLE CHECK4_T1
CREATE NONCLUSTERED INDEX NCIX_C1 ON CHECK4_T1(C1) --加上非聚集索引
---测试一:(4s)执行计划: 索引扫描
SELECT * FROM CHECK4_T1 WHERE RTRIM(C1)='C2'
---测试二:(0s)执行计划: 索引查找
SELECT * FROM CHECK4_T1 WHERE C1='C2'
--总结:where条件里对索引字段使用了函数,会使索引查找变成索引扫描,从而查询效率大幅下降
--=====================================
--6、 用sp_executesql执行动态sql
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
CREATE PROC UP_CHECK5_T1 (
@ID INT
)
AS
SET NOCOUNT ON
DECLARE @count INT,
@sql NVARCHAR(4000)
SET @sql = 'SELECT @count=count(*) FROM CHECK5_T1 WHERE ID = @ID'
EXEC sp_executesql @sql,
N'@count INT OUTPUT, @ID int',
@count OUTPUT,
@ID
PRINT @count
CREATE PROC UP_CHECK5_T2 (
@ID INT
)
AS
SET NOCOUNT ON
DECLARE @sql NVARCHAR(4000)
SET @sql = 'DECLARE @count INT;SELECT @count=count(*) FROM CHECK5_T1 WHERE ID = ' + CAST(@ID AS VARCHAR(10)) + ';PRINT @count'
EXEC(@sql)
---测试一:瞬时
DECLARE @N INT
SET @N=1
WHILE @NBEGIN
EXEC UP_CHECK5_T1 @N
SET @N=@N+1
END
---测试二:2s
DECLARE @N INT
SET @N=1
WHILE @NBEGIN
EXEC UP_CHECK5_T2 @N
SET @N=@N+1
END
CREATE CLUSTERED INDEX CIX_ID ON CHECK5_T1(ID)
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
--查看缓存计划
SELECT a.size_in_bytes '占用字节数',
total_elapsed_time / execution_count '平均时间',
total_logical_reads / execution_count '逻辑读',
usecounts '重用次数',
SUBSTRING(d.text, (statement_start_offset / 2) + 1, ((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(text)
ELSE statement_end_offset
END - statement_start_offset) / 2) + 1) '语句'
FROM sys.dm_exec_cached_plans a
CROSS apply sys.dm_exec_query_plan(a.plan_handle) c,
sys.dm_exec_query_stats b
CROSS apply sys.dm_exec_sql_text(b.sql_handle) d
WHERE a.plan_handle = b.plan_handle
ORDER BY total_elapsed_time / execution_count DESC;
--总结:通过执行下面缓存计划可以看出,第一种完全使用了缓存计划,查询达到了很好的效果;
--而第二种则将缓存计划浪费了,导致缓存很快被占满,这种做法是相当不可取的
--=====================================
--7、 Left Join 的替代法
--测试一 执行计划:表扫描 -> 哈希匹配
SELECT A.ID,A.C1 FROM CHECK3_T1 A --2W行
LEFT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1' --400行
--测试二 执行计划:表扫描 -> 哈希匹配
SELECT A.ID,A.C1 FROM CHECK3_T1 A
RIGHT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE a.C1='C1'
--测试三 执行计划:表扫描 -> 哈希匹配
SELECT A.ID,A.C1 FROM CHECK3_T1 A
INNER JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1'
--总结:三条语句,在执行计划上完全一样,都是走的INNER JOIN的计划,
--因为测试一和测试二中,WHERE语句都包含了LEFT 和RIGHT表的字段,SQLSERVER若发现只要有这个表的字段,则会自动按照INNER JOIN进行处理
--补充测试:(1s)执行计划:表扫描-> 并行度 -> 位图 -> 排序 -> 合并联接 -> 并行度
SELECT A.ID,A.C1 FROM CHECK3_T2 A --400行
INNER JOIN CHECK3_T1 B ON A.ID=B.ID WHERE A.C1='C1' --2W行
--总结:这里有一个比较有趣的地方,若主表和关联表数据差别很大时,走的执行计划走的另一条路
--=====================================
--8、 ON(a.id=b.id AND a.tag=3)
--测试一
SELECT A.ID,A.C1 FROM CHECK3_T1 A
INNER JOIN CHECK3_T2 B ON A.ID=B.ID AND A.C1='C1'
--测试二
SELECT A.ID,A.C1 FROM CHECK3_T1 A
INNER JOIN CHECK3_T2 B ON A.ID=B.ID WHERE A.C1='C1'
--总结:内连接:无论是左表和右表的筛选条件都可以放到WHERE子句中
--测试一
SELECT A.ID,A.C1,B.C1 FROM CHECK3_T1 A
LEFT JOIN CHECK3_T2 B ON A.ID=B.ID AND B.C1='C1'
--测试二
SELECT A.ID,A.C1,B.C1 FROM CHECK3_T1 A
LEFT JOIN CHECK3_T2 B ON A.ID=B.ID WHERE B.C1='C1'
--总结:左外连接:当右表中的过滤条件放入ON子句后和WHERE子句后的结果不一样
--=====================================
--9、 赋值给变量,加Top 1
--测试一:(3s) 执行计划:表扫描
DECLARE @ID INT
SELECT @ID=ID FROM CHECK1_T1 WHERE C1='C1'
SELECT @ID
--测试二:(0s)执行计划:表扫描-> 前几行
DECLARE @ID INT
SELECT TOP 1 @ID=ID FROM CHECK1_T1 WHERE C1='C1'
SELECT @ID
--总结:给变量赋值最好都加上TOP 1,一从查询效率上增强,二为了准确性,若表CHECK1_T1有多个值,则会取最后一条记录赋给@ID
--=====================================
--10、 考虑是否适合用CASE语句
DECLARE @S INT=1
SELECT * FROM CHECK5_T1
WHERE C1=(CASE @S WHEN 1 THEN C1 ELSE 'C2' END)
SELECT * FROM CHECK5_T1
WHERE @S=1 OR C1='C2'
/*--=====================================
、检查语句是否需要Distinct. 执行计划:表扫描-> 哈希匹配-> 并行度-> 排序
select distinct c1 from CHECK3_T1
、禁用Select *,指定具体列名
select c1 from CHECK4_T1
select * from CHECK4_T1
、Insert into Table(*),指定具体的列名
、Isnull,没有必要的时候不要对字段使用isnull,同样会产生无法有效利用索引的问题,
和避免在筛选列上使用函数同样的原理。
、嵌套子查询,加上查询条件,确保子查询的结果集最小
--=====================================*/

MySQL在Web應用中的主要作用是存儲和管理數據。 1.MySQL高效處理用戶信息、產品目錄和交易記錄等數據。 2.通過SQL查詢,開發者能從數據庫提取信息生成動態內容。 3.MySQL基於客戶端-服務器模型工作,確保查詢速度可接受。

構建MySQL數據庫的步驟包括:1.創建數據庫和表,2.插入數據,3.進行查詢。首先,使用CREATEDATABASE和CREATETABLE語句創建數據庫和表,然後用INSERTINTO語句插入數據,最後用SELECT語句查詢數據。

MySQL適合初學者,因為它易用且功能強大。 1.MySQL是關係型數據庫,使用SQL進行CRUD操作。 2.安裝簡單,需配置root用戶密碼。 3.使用INSERT、UPDATE、DELETE、SELECT進行數據操作。 4.複雜查詢可使用ORDERBY、WHERE和JOIN。 5.調試需檢查語法,使用EXPLAIN分析查詢。 6.優化建議包括使用索引、選擇合適數據類型和良好編程習慣。

MySQL適合初學者,因為:1)易於安裝和配置,2)有豐富的學習資源,3)SQL語法直觀,4)工具支持強大。儘管如此,初學者需克服數據庫設計、查詢優化、安全管理和數據備份等挑戰。

是的,sqlisaprogramminglanguges pecialized fordatamanage.1)它具有焦點,focusingonwhattoachieveratherthanhow.2)sqlisessential forquerying forquerying,插入,更新,更新,和detletingdatainrelationalDatabases.3)

ACID屬性包括原子性、一致性、隔離性和持久性,是數據庫設計的基石。 1.原子性確保事務要么完全成功,要么完全失敗。 2.一致性保證數據庫在事務前後保持一致狀態。 3.隔離性確保事務之間互不干擾。 4.持久性確保事務提交後數據永久保存。

MySQL既是數據庫管理系統(DBMS),也與編程語言緊密相關。 1)作為DBMS,MySQL用於存儲、組織和檢索數據,優化索引可提高查詢性能。 2)通過SQL與編程語言結合,嵌入在如Python中,使用ORM工具如SQLAlchemy可簡化操作。 3)性能優化包括索引、查詢、緩存、分庫分錶和事務管理。

MySQL使用SQL命令管理數據。 1.基本命令包括SELECT、INSERT、UPDATE和DELETE。 2.高級用法涉及JOIN、子查詢和聚合函數。 3.常見錯誤有語法、邏輯和性能問題。 4.優化技巧包括使用索引、避免SELECT*和使用LIMIT。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

AI Hentai Generator
免費產生 AI 無盡。

熱門文章

熱工具

Atom編輯器mac版下載
最受歡迎的的開源編輯器

PhpStorm Mac 版本
最新(2018.2.1 )專業的PHP整合開發工具

禪工作室 13.0.1
強大的PHP整合開發環境

WebStorm Mac版
好用的JavaScript開發工具

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)