网站建设福州公司,电商摄影,建设官方网站多少,主流软件开发平台转载自 SQL索引一步到位
SQL索引在数据库优化中占有一个非常大的比例#xff0c; 一个好的索引的设计#xff0c;可以让你的效率提高几十甚至几百倍#xff0c;在这里将带你一步步揭开他的神秘面纱。 1.1 什么是索引#xff1f; SQL索引有两种#xff0c;聚集索引和非聚…转载自 SQL索引一步到位
SQL索引在数据库优化中占有一个非常大的比例 一个好的索引的设计可以让你的效率提高几十甚至几百倍在这里将带你一步步揭开他的神秘面纱。 1.1 什么是索引 SQL索引有两种聚集索引和非聚集索引索引主要目的是提高了SQL Server系统的性能加快数据的查询速度与减少系统的响应时间
下面举两个简单的例子
图书馆的例子一个图书馆那么多书怎么管理呢建立一个字母开头的目录例如a开头的书在第一排b开头的在第二排这样在找什么书就好说了这个就是一个聚集索引可是很多人借书找某某作者的不知道书名怎么办图书管理员在写一个目录某某作者的书分别在第几排第几排这就是一个非聚集索引
字典的例子字典前面的目录可以按照拼音和部首去查询我们想查询一个字只需要根据拼音或者部首去查询就可以快速的定位到这个汉字了这个就是索引的好处拼音查询法就是聚集索引部首查询就是一个非聚集索引. 看了上面的例子下面的一句话大家就很容易理解了聚集索引存储记录是物理上连续存在而非聚集索引是逻辑上的连续物理存储并不连续。就像字段聚集索引是连续的a后面肯定是b非聚集索引就不连续了就像图书馆的某个作者的书有可能在第1个货架上和第10个货架上。还有一个小知识点就是聚集索引一个表只能有一个而非聚集索引一个表可以存在多个。 1.2 索引的存储机制 首先无索引的表查询时是按照顺序存续的方法扫描每个记录来查找符合条件的记录这样效率十分低下,举个例子如果我们将字典的汉字随即打乱没有前面的按照拼音或者部首查询那么我们想找一个字按照顺序的方式去一页页的找这样效率有多底大家可以想象。 聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致其实理解起来非常简单还是举字典的例子如果按照拼音查询那么都是从a-z的是具有连续性的a后面就是bb后面就是c 聚集索引就是这样的他是和表的物理排列顺序是一样的例如有id为聚集索引那么1后面肯定是2,2后面肯定是3所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是可能按照偏房查询的时候根据偏旁‘弓’字旁索引出两个汉字张和弘但是这两个其实一个在100页一个在1000页这里只是举个例子他们的索引顺序和数据库表的排列顺序是不一样的这个样的就是非聚集索引。 原理明白了那他们是怎么存储的呢在这里简单的说一下聚集索引就是在数据库被开辟一个物理空间存放他的排列的值例如1-100所以当插入数据时他会重新排列整个整个物理空间而非聚集索引其实可以看作是一个含有聚集索引的表他只仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针其实就有点和堆栈差不多的感觉了 1.3 什么情况下设置索引 动作描述 使用聚集索引 使用非聚集索引 外键列 应 应 主键列 应 应 列经常被分组排序(order by) 应 应 返回某范围内的数据 应 不应 小数目的不同值 应 不应 大数目的不同值 不应 应 频繁更新的列 不应 应 频繁修改索引列 不应 应 一个或极少不同值 不应 不应 建立索引的原则
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列;
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段建立索引。如果建立的是复合索引索引的字段顺序要和这些关键字后面的字段顺序一致否则索引不会被使用。
7) 对于那些查询中很少涉及的列重复值比较多的列不要建立索引。
8) 对于定义为text、image和bit的数据类型的列不要建立索引。
9) 对于经常存取的列避免建立索引
9) 限制表上的索引数目。对一个存在大量更新操作的表所建索引的数目一般不要超过3个最多不要超过5个。索引虽说提高了访问速度但太多索引会影响数据的更新操作。
10) 对复合索引按照字段在查询条件中出现的频度建立索引。在复合索引中记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录系统再按照第二个字段的取值排序以此类推。因此只有复合索引的第一个字段出现在查询条件中该索引才可能被使用,因此将应用频度高的字段放置在复合索引的前面会使系统最大可能地使用此索引发挥索引的作用。 1.4 如何创建索引 1.41 创建索引的语法
CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name
ON {table_name | view_name} [WITH [index_property [,....n]]
说明
UNIQUE: 建立唯一索引。
CLUSTERED: 建立聚集索引。
NONCLUSTERED: 建立非聚集索引。
Index_property: 索引属性。 UNIQUE索引既可以采用聚集索引结构也可以采用非聚集索引的结构如果不指明采用的索引结构则SQL Server系统默认为采用非聚集索引结构。
1.42 删除索引语法
DROP INDEX table_name.index_name[,table_name.index_name]
说明table_name: 索引所在的表名称。
index_name : 要删除的索引名称。
1.43 显示索引信息
使用系统存储过程sp_helpindex 查看指定表的索引信息。
执行代码如下
Exec sp_helpindex book1; 1.5 索引使用次数、索引效率、占用CPU检测、索引缺失 当我们明白了什么是索引什么时间创建索引以后我们就会想我们创建的索引到底效率执行的怎么样好不好我们创建的对不对 首先我们来认识一下DMVDMV (dynamic management view)动态管理视图和函数返回特定于实现的内部状态数据。推出SQL Server 2005时微软介绍了许多被称为dmvs的系统视图让您可以探测SQL Server 的健康状况诊断问题或查看SQL Server实例的运行信息。统计数据是在SQL Server运行的时候开始收集的并且在SQL Server每次启动的时候统计数据将会被重置。当你删除或者重新创建其组件时某些dmv的统计数据也可以被重置例如存储过程和表而其它的dmv信息在运行dbcc命令时也可以被重置。 当你使用一个dmv时你需要紧记SQL Server收集这些信息有多长时间了以确定这些从dmv返回的数据到底有多少可用性。如果SQL Server只运行了很短的一段时间你可能不想去使用一些dmv统计数据因为他们并不是一个能够代表SQL Server实例可能遇到的真实工作负载的样本。另一方面SQL Server只能维持一定量的信息有些信息在进行SQL Server性能管理活动的时候可能丢失所以如果SQL Server已经运行了相当长的一段时间一些统计数据就有可能已被覆盖。 因此任何时候你使用dmv当你查看从SQL Server 2005的dmvs返回的相关资料时请务必将以上的观点装在脑海中。只有当你确信从dmvs获得的信息是准确和完整的你才能变更数据库或者应用程序代码。
下面就看一下dmv到底能带给我们那些好的功能呢
1.51 索引使用次数
我们下看一下下面两种查询方式返回的结果这两种查询的查询用途一致
①----
declare dbid int
select dbid db_id()
select objectnameobject_name(s.object_id), s.object_id, indexnamei.name, i.index_id , user_seeks, user_scans, user_lookups, user_updates
from sys.dm_db_index_usage_stats s, sys.indexes i
where database_id dbid and objectproperty(s.object_id,IsUserTable) 1
and i.object_id s.object_id
and i.index_id s.index_id
order by (user_seeks user_scans user_lookups user_updates) asc
返回查询结果 ②使用多的索引排在前面
SELECT objects.name , databases.name , indexes.name , user_seeks , user_scans , user_lookups , partition_stats.row_count
FROM sys.dm_db_index_usage_stats stats LEFT JOIN sys.objects objects ON stats.object_id objects.object_id LEFT JOIN sys.databases databases ON databases.database_id stats.database_id LEFT JOIN sys.indexes indexes ON indexes.index_id stats.index_id AND stats.object_id indexes.object_id LEFT JOIN sys.dm_db_partition_stats partition_stats ON stats.object_id partition_stats.object_id AND indexes.index_id partition_stats.index_id
WHERE 1 1
--AND databases.database_id 7 AND objects.name IS NOT NULL AND indexes.name IS NOT NULL AND user_scans0
ORDER BY user_scans DESC , stats.object_id , indexes.index_id
返回查询结果 user_seeks : 通过用户查询执行的搜索次数。 个人理解 此统计索引搜索的次数
user_scans: 通过用户查询执行的扫描次数。 个人理解此统计表扫描的次数无索引配合 user_lookups: 通过用户查询执行的查找次数。 个人理解用户通过索引查找在使用RID或聚集索引查找数据的次数对于堆表或聚集表数据而言和索引配合使用次数 user_updates: 通过用户查询执行的更新次数。 个人理解索引或表的更新次数
我们可以清晰的看到那些索引用的多那些索引没用过大家可以根据查询出来的东西去分析自己的数据索引和表
1.52 索引提高了多少性能
新建了索引到底增加了多少数据的效率呢到底提高了多少性能呢运行如下SQL可以返回连接缺失索引动态管理视图发现最有用的索引和创建索引的方法
SELECT
avg_user_impact AS average_improvement_percentage,
avg_total_user_cost AS average_cost_of_query_without_missing_index,
CREATE INDEX ix_ [statement]
ISNULL(equality_columns, _)
ISNULL(inequality_columns, _) ON [statement] ( ISNULL(equality_columns, )
ISNULL(inequality_columns, ) )
ISNULL( INCLUDE ( included_columns ), )
AS create_missing_index_command
FROM sys.dm_db_missing_index_details a INNER JOIN
sys.dm_db_missing_index_groups b ON a.index_handle b.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats c ON
b.index_group_handle c.group_handle
WHERE avg_user_impact 40 返回结果 虽然用户能够修改性能提高的百分比但以上查询返回所有能够将性能提高40%或更高的索引。你可以清晰的看到每个索引提高的性能和效率了
1.53 最占用CPU、执行时间最长命令
这个和索引无关但是还是在这里提出来因为他也属于DMV带给我们的功能吗他可以让你轻松查询出那些sql语句占用你的cpu最高 SELECT TOP 100 execution_count, total_logical_reads /execution_count AS [Avg Logical Reads], total_elapsed_time /execution_count AS [Avg Elapsed Time], db_name(st.dbid) as [database name], object_name(st.dbid) as [object name], object_name(st.objectid) as [object name 1], SUBSTRING(st.text, (qs.statement_start_offset / 2) 1, ((CASE statement_end_offset WHEN - 1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) 1) AS statement_text FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st WHERE execution_count 100 ORDER BY 1 DESC; 返回结果 执行时间最长的命令
SELECT TOP 10 COALESCE(DB_NAME(st.dbid),
DB_NAME(CAST(pa.value as int))*,
Resource) AS DBNAME,
SUBSTRING(text,
-- starting value for substring CASE WHEN statement_start_offset 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 1 END,
-- ending value for substring CASE WHEN statement_end_offset 0
OR statement_end_offset -1
OR statement_end_offset IS NULL
THEN LEN(text)
ELSE statement_end_offset/2 END -
CASE WHEN statement_start_offset 0
OR statement_start_offset IS NULL
THEN 1
ELSE statement_start_offset/2 END 1
) AS TSQL,
total_logical_reads/execution_count AS AVG_LOGICAL_READS
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) pa
WHERE attribute dbid
ORDER BY AVG_LOGICAL_READS DESC ; 看到了吗直接可以定位到你的sql语句优化去吧。还等什么呢
1.54缺失索引
缺失索引就是帮你查找你的数据库缺少什么索引告诉你那些字段需要加上索引这样你就可以根据提示添加你数据库缺少的索引了
SELECT TOP 10
[Total Cost] ROUND(avg_total_user_cost * avg_user_impact * (user_seeks user_scans),0)
, avg_user_impact
, TableName statement
, [EqualityUsage] equality_columns
, [InequalityUsage] inequality_columns
, [Include Cloumns] included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle g.index_handle
ORDER BY [Total Cost] DESC;
查询结果如下 1.6 适当创建索引覆盖 假设你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外键列(ProductID)上创建了一个索引假设ProductID列是一个高选中性列那么任何在where子句中使用索引列(ProductID)的select查询都会更快如果在外键上没有创建索引将会发生全部扫描但还有办法可以进一步提升查询性能。 假设Sales表有10,000行记录下面的SQL语句选中400行(总行数的4%)
SELECT SalesDate, SalesPersonID FROM Sales WHERE ProductID 112 我们来看看这条SQL语句在SQL执行引擎中是如何执行的 1)Sales表在ProductID列上有一个非聚集索引因此它查找非聚集索引树找出ProductID112的记录; 2)包含ProductID 112记录的索引页也包括所有的聚集索引键(所有的主键键值即SalesID); 3)针对每一个主键(这里是400)SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置; SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。 在上面的步骤中对ProductID 112的每个主键记录(这里是400)SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDateSalesPersonID)。 如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,SalesPersonID)的值SQL Server引擎可能不会执行上面的第3和4步直接从非聚集索引树查找ProductID列速度还会快一些直接从索引页读取这三列的数值。 幸运的是有一种方法实现了这个功能它被称为“覆盖索引”在表列上创建覆盖索引时需要指定哪些额外的列值需要和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子
CREATE INDEX NCLIX_Sales_ProductID--Index name ON dbo.Sales(ProductID)--Column on which index is to be created INCLUDE(SalesDate, SalesPersonID)--Additional column values to include 应该在那些select查询中常使用到的列上创建覆盖索引但覆盖索引中包括过多的列也不行因为覆盖索引列的值是存储在内存中的这样会消耗过多内存引发性能下降。 1.7 索引碎片
在数据库性能优化一数据库自身优化一文中已经讲到了这个问题再次就不做过多的重复地址http://www.cnblogs.com/AK2012/archive/2012/12/25/2012-1228.html 1.8 索引实战摘抄
之所以这章摘抄是因为下面这个文章已经写的太好了估计我写出来也无法比这个好了所以就摘抄了
人们在使用SQL时往往会陷入一个误区即太关注于所得的结果是否正确而忽略了不同的实现方法之间可能存在的性能差异这种性能差异在大型的或是复杂的数据库环境中如联机事务处理OLTP或决策支持系统DSS中表现得尤为明显。
笔者在工作实践中发现不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。
在对它们进行适当的优化后其运行速度有了明显地提高
下面我将从这三个方面分别进行总结
为了更直观地说明问题所有实例中的SQL运行时间均经过测试不超过秒的均表示为 1秒。----
测试环境: 主机HP LH II---- 主频330MHZ---- 内存128兆----
操作系统Operserver5.0.4----
数据库Sybase11.0.3 一、不合理的索引设计----
例表record有620000行试看在不同的索引下下面几个 SQL的运行情况
---- 1.在date上建有一非个群集索引
select count(*) from record where date 19991201 and date 19991214and amount 2000 (25秒)
select date ,sum(amount) from record group by date(55秒)
select count(*) from record where date 19990901 and place in (BJ,SH) (27秒)
---- 分析----
date上有大量的重复值在非群集索引下数据在物理上随机存放在数据页上在范围查找时必须执行一次表扫描才能找到这一范围内的全部行。
---- 2.在date上的一个群集索引
select count(*) from record where date 19991201 and date 19991214 and amount 2000 14秒
select date,sum(amount) from record group by date28秒
select count(*) from record where date 19990901 and place in (BJ,SH)14秒
---- 分析---- 在群集索引下数据在物理上按顺序在数据页上重复值也排列在一起因而在范围查找时可以先找到这个范围的起末点且只在这个范围内扫描数据页避免了大范围扫描提高了查询速度。
---- 3.在placedateamount上的组合索引
select count(*) from record where date 19991201 and date 19991214 and amount 2000 26秒
select date,sum(amount) from record group by date27秒
select count(*) from record where date 19990901 and place in (BJ, SH) 1秒
---- 分析---- 这是一个不很合理的组合索引因为它的前导列是place第一和第二条SQL没有引用place因此也没有利用上索引第三个SQL使用了place且引用的所有列都包含在组合索引中形成了索引覆盖所以它的速度是非常快的。
---- 4.在dateplaceamount上的组合索引
select count(*) from record where date 19991201 and date 19991214 and amount 2000( 1秒)
select date,sum(amount) from record group by date11秒
select count(*) from record where date 19990901 and place in (BJ,SH) 1秒
---- 分析---- 这是一个合理的组合索引。它将date作为前导列使每个SQL都可以利用索引并且在第一和第三个SQL中形成了索引覆盖因而性能达到了最优。
---- 5.总结----
缺省情况下建立的索引是非群集索引但有时它并不是最佳的合理的索引设计要建立在对各种查询的分析和预测上。
一般来说
①.有大量重复值、且经常有范围查询between, , , 和order by、group by发生的列可考虑建立群集索引
②.经常同时存取多列且每列都含有重复值可考虑建立组合索引
③.组合索引要尽量使关键查询形成索引覆盖其前导列一定是使用最频繁的列。 二、不充份的连接条件
例表card有7896行在card_no上有一个非聚集索引表account有191122行在account_no上有一个非聚集索引试看在不同的表连接条件下两个SQL的执行情况
select sum(a.amount) from account a,card b where a.card_no b.card_no20秒
select sum(a.amount) from account a,card b where a.card_no b.card_no and a.account_nob.account_no 1秒
---- 分析---- 在第一个连接条件下最佳查询方案是将account作外层表card作内层表利用card上的索引其I/O次数可由以下公式估算为
外层表account上的22541页外层表account的191122行*内层表card上对应外层表第一行所要查找的3页595907次I/O
在第二个连接条件下最佳查询方案是将card作外层表account作内层表利用account上的索引其I/O次数可由以下公式估算为外层表card上的1944页外层表card的7896行*内层表account上对应外层表每一行所要查找的4页 33528次I/O
可见只有充份的连接条件真正的最佳方案才会被执行。
总结
1.多表操作在被实际执行前查询优化器会根据连接条件列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充份考虑带有索引的表、行数多的表内外表的选择可由公式外层表中的匹配行数*内层表中每一次查找的次数确定乘积最小为最佳方案。
2.查看执行方案的方法-- 用set showplanon打开showplan选项就可以看到连接顺序、使用何种索引的信息想看更详细的信息需用sa角色执行dbcc(3604,310,302)。 三、不可优化的where子句
1.例下列SQL条件语句中的列都建有恰当的索引但执行速度却非常慢
select * from record wheresubstring(card_no,1,4)5378(13秒)
select * from record whereamount/30 100011秒
select * from record whereconvert(char(10),date,112)1999120110秒
分析
where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的因此它不得不进行表搜索而没有使用该列上面的索引
如果这些结果在查询编译时就能得到那么就可以被SQL优化器优化使用索引避免表搜索因此将SQL重写成下面这样
select * from record where card_no like5378% 1秒
select * from record where amount 1000*30 1秒
select * from record where date 1999/12/01 1秒
你会发现SQL明显快起来
2.例表stuff有200000行id_no上有非群集索引请看下面这个SQL
select count(*) from stuff where id_no in(0,1)23秒
分析---- where条件中的in在逻辑上相当于or所以语法分析器会将in (0,1)转化为id_no 0 or id_no1来执行。
我们期望它会根据每个or子句分别查找再将结果相加这样可以利用id_no上的索引
但实际上根据showplan,它却采用了OR策略即先取出满足每个or子句的行存入临时数据库的工作表中再建立唯一索引以去掉重复行最后从这个临时表中计算结果。因此实际过程没有利用id_no上索引并且完成时间还要受tempdb数据库性能的影响。
实践证明表的行数越多工作表的性能就越差当stuff有620000行时执行时间竟达到220秒还不如将or子句分开
select count(*) from stuff where id_no0select count(*) from stuff where id_no1
得到两个结果再作一次加法合算。因为每句都使用了索引执行时间只有3秒在620000行下时间也只有4秒。
或者用更好的方法写一个简单的存储过程
create proc count_stuff asdeclare a intdeclare b intdeclare c intdeclare d char(10)beginselect acount(*) from stuff where id_no0select bcount(*) from stuff where id_no1endselect cabselect dconvert(char(10),c)print d
直接算出结果执行时间同上面一样快 ---- 总结---- 可见所谓优化即where子句利用了索引不可优化即发生了表扫描或额外开销。
1.任何对列的操作都将导致表扫描它包括数据库函数、计算表达式等等查询时要尽可能将操作移至等号右边。
2.in、or子句常会使用工作表使索引失效如果不产生大量重复值可以考虑把子句拆开拆开的子句中应该包含索引。
3.要善于使用存储过程它使SQL变得更加灵活和高效。
从以上这些例子可以看出SQL优化的实质就是在结果正确的前提下用优化器可以识别的语句充份利用索引减少表扫描的I/O次数尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程上述这些只是在应用层次的一种体现深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。
