xml地图|网站地图|网站标签 [设为首页] [加入收藏]
SERVER诊断系列,column快很多奇怪案例分析
分类:数据库

概述

 

索引设计是数据库设计中比较重要的一个环节,对数据库的性能起着至关重要的作用,但是索引的设计却又不是那么容易的事情,性能也不是那么轻易就获取到的,很多的技术人员因为不恰当的创建索引,最后使得其效果适得其反,可以说“成也索引,败也索引”。在我经历过的,众多的数据库性能问题案例中,80% 系统都存在索引不合理的问题.

 

 

SQL RIGHT JOIN 关键字

RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行,即使在左表 (table_name1) 中没有匹配的行。

遇到MYSQL傻傻的地方,下面给个案例,大家感受下:

为什么需要索引

 

数据在磁盘上是以块的形式存储的。为确保对磁盘操作的原子性,访问数据的时候会一并访问所有数据块。磁盘上的这些数据块与链表类似,即它们都包含一个数据段和一个指针,指针指向下一个节点(数据块)的内存地址,而且它们都不需要连续存储(即逻辑上相邻的数据块在物理上可以相隔很远)。

鉴于很多记录只能做到按一个字段排序,所以要查询某个未经排序的字段,就需要使用线性查找,即要访问N/2个数据块,其中N指的是一个表所涵盖的所有数据块。如果该字段是非键字段(也就是说,不包含唯一值),那么就要搜索整个表空间,即要访问全部N个数据块。

然而,对于经过排序的字段,可以使用二分查找,因此只要访问log2 N个数据块。同样,对于已经排过序的非键字段,只要找到更大的值,也就不用再搜索表中的其他数据块了。这样一来,性能就会有实质性的提升。

举个例子,我们使用微软的示例数据库, 对于下面的查询:

select SalesOrderDetailID FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID=61026

1.SalesOrderDetailID 列没有索引

查询会进行全表扫描。逻辑读如下图:

 

图片 1

表的数据页数如下所示,两者是几乎相等的。说明他对每个数据页都进行需要读取一次。

图片 2

 

2.SalesOrderDetailID 建立索引,逻辑读取仅2次。

因为log2 1246 是等于10.28. 最大的逻辑读取次数是11.

 图片 3

 

RIGHT JOIN 关键字语法

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2 
ON table_name1.column_name=table_name2.column_name

注释:在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN。

 注意以下两个sql只有select *和select g.id区别。

利用EXPERT找到缺失索引

 

既然索引对我们如此重要,那么我们如何去快速,方便的找到需要的索引呢。利用体检专家可以轻松做到。

原始的表 (用在例子中的):

"Persons" 表:

Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

"Orders" 表:

Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65

SQL1:
SELECT
g.id
FROM
table1 g
INNER JOIN table2 l ON concat('订单号:',CONVERT(g.id,char)) = l.info
WHERE LOCATE('付款操作',l.info) AND g.p = 2
LIMIT 100

1.通过执行计划

在执行计划选项卡,对于查询语句,如果存在缺失索引,体检专家会在缺失索引列上打钩,如图所示。

同时列出SQL了 语句执行次数和执行时间。

 图片 4

 

右连接(RIGHT JOIN)实例

现在,我们希望列出所有的定单,以及定购它们的人 - 如果有的话。

您可以使用下面的 SELECT 语句:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName

结果集:

LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
    34764

RIGHT JOIN 关键字会从右表 (Orders) 那里返回所有的行,即使在左表 (Persons) 中没有匹配的行。

查询时间:5.28s

2.通过数据库

在数据库选项卡中,选中缺失索引,体检专家会自动列出数据库中所有的缺失索引。

 

 图片 5

 

 

SQL2:
SELECT
*
FROM
table1 g
INNER JOIN table2 l ON concat('订单设为付款操作成功 订单号:',CONVERT(g.id,char)) = l.info
WHERE LOCATE('付款操作成功',l.info) AND g.p = 2
LIMIT 100

生成脚本

在找到缺失索引后可以通过体检专家自动生成脚本。执行计划中也可以类似的生成脚本。

 

 图片 6

 

图片 7

 

查询时间:0.21s

如何建立

 

通过体检专家可以轻松找到系统中缺失的索引。但对于系统中大量的缺失索引,我们如何去选择呢?

注意以下Sending data的sql1比sql2慢,这是不是很诡异!!!!!!!!!!!!!!

1.执行计划中的缺失索引

具体的值,根据业务不同可能有些不同。但是执行次数越多,执行时间越长,影响百分比越大,就越要建立索引。我的建议是:执行次数每天超过200,执行时间超过1000ms,影响百分比超过50%。

 图片 8

SQL1:

2.数据库中缺失索引

平均影响百分比越大,平均用户开销越大,用户查找次数越大,那么就越需要建立。

同样给出我的建议值:影响百分比50%,用户开销1,用户查找次数每天200

图片 9

 

 图片 10

总结

贴图是来自北京某银行系统。系统长时间语句非常多,严重影响前端用户体验。对系统第一轮优化,通过体检专家建立合适的索引后,系统的整体性能提升超过50%以上。

 

 

 文章用到的 Expert FOR SQLSERVER 工具下载链接:

 

 

SQL2:

本文由澳门新葡亰手机版发布于数据库,转载请注明出处:SERVER诊断系列,column快很多奇怪案例分析

上一篇:批量拆分SQL语句为事务并批处理,5120的解决办法 下一篇:没有了
猜你喜欢
热门排行
精彩图文