精华内容
下载资源
问答
  • 索引关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理...

    索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点。

    §3.5.1 建立索引

    1. CREATE INDEX命令语法:

    CREATE INDEX

    CREATE [unique] INDEX [user.]index

    ON [user.]table (column [ASC | DESC] [,column

    [ASC | DESC] ] ... )

    [CLUSTER [scheam.]cluster]

    [INITRANS n]

    [MAXTRANS n]

    [PCTFREE n]

    [STORAGE storage]

    [TABLESPACE tablespace]

    [NO SORT]

    Advanced

    其中:

    schema ORACLE模式,缺省即为当前帐户

    index 索引名

    table 创建索引的基表名

    column 基表中的列名,一个索引最多有16列,long列、long raw

    列不能建索引列

    DESC、ASC 缺省为ASC即升序排序

    CLUSTER 指定一个聚簇(Hash cluster不能建索引)

    INITRANS、MAXTRANS 指定初始和最大事务入口数

    Tablespace 表空间名

    STORAGE 存储参数,同create table 中的storage.

    PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)

    NOSORT 不(能)排序(存储时就已按升序,所以指出不再排序)

    2.建立索引的目的:

    建立索引的目的是:

    l 提高对表的查询速度;

    l 对表有关列的取值进行检查。

    但是,对表进行insert,update,delete处理时,由于要表的存放位置记录到索引项中而会降低一些速度。

    注意:一个基表不能建太多的索引;

    空值不能被索引

    只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

    Create index ename_in on emp (ename,sal);

    取消

    评论

    展开全文
  • 索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。为什么创建索引呢?这因为,创建索引可以大大提高系统的性能。第一,通过创建唯一性索引,可以...

    09399e06606e5a6e59e622ddfe89746b.png

    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她,则与在表中搜索所有的行相比,索引有助于更快地获取信息。

    索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

    为什么要创建索引呢?

    这是因为,创建索引可以大大提高系统的性能。第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。

    第二,可以大大加快 数据的检索速度,这也是创建索引的最主要的原因。

    第三,可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。

    第四,在使用分组和排序 子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。

    第五,通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

    索引是建立在数据库表中的某些列的上面。因此,在创建索引的时候,应该仔细考虑在哪些列上可以创建索引,在哪些列上不能创建索引。一般来说,应该在这些列上创建索引,例如:在经常需要搜索的列上,可以加快搜索的速度;

    在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构;

    在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度;

    在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的;

    在经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;

    在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

    同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:第一,对于那些在查询中很少使用或者参考的列不应该创建索引。这是因 为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。

    第二,对于那 些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。

    第三,对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少。

    第四,当修改性能远远大于检索性能时,不应该创建索 引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。当减少索引时,会提高修改性能,降低检索性能。因 此,当修改性能远远大于检索性能时,不应该创建索引。

    索引的特征

    索引有两个特征,即唯一性索引和复合索引。

    唯一 性索引保证在索引列中的全部数据是唯一的,不会包含冗余数据。

    如果表中已经有一个主键约束或者唯一性键约束,那么当创建表或者修改表时,SQL Server自动创建一个唯一性索引。然而,如果必须保证唯一性,那么应该创建主键约束或者唯一性键约束,而不是创建一个唯一性索引。

    当创建唯一性索引 时,应该认真考虑这些规则:当在表中创建主键约束或者唯一性键约束时,SQL Server自动创建一个唯一性索引;

    如果表中已经包含有数据,那么当创建索引时,SQL Server检查表中已有数据的冗余性;

    每当使用插入语句插入数据或者使用修改语句修改数据时,SQL Server检查数据的冗余性:如果有冗余值,那么SQL Server取消该语句的执行,并且返回一个错误消息;

    确保表中的每一行数据都有一个唯一值,这样可以确保每一个实体都可以唯一确认;

    只能在可以保证实体 完整性的列上创建唯一性索引,例如,不能在人事表中的姓名列上创建唯一性索引,因为人们可以有相同的姓名。

    复合索引就是一个索引创建 在两个列或者多个列上。

    在搜索时,当两个或者多个列作为一个关键值时,最好在这些列上创建复合索引。当创建复合索引时,应该考虑这些规则:最多可以把16个列合并成一个单独的复合索引,构成复合索引的列的总长度不能超过900字节,也就是说复合列的长度不能太长;

    在复合索引中,所 有的列必须来自同一个表中,不能跨表建立复合列;

    在复合索引中,列的排列顺序是非常重要的,因此要认真排列列的顺序,原则上,应该首先定义最唯一的列,例 如在(COL1,COL2)上的索引与在(COL2,COL1)上的索引是不相同的,因为两个索引的列的顺序不同;

    为了使查询优化器使用复合索引,查询语 句中的WHERE子句必须参考复合索引中第一个列;

    当表中有多个关键列时,复合索引是非常有用的;

    使用复合索引可以提高查询性能,减少在一个表中所创建的 索引数量。

    更多web开发知识,请查阅 HTML中文网 !!

    展开全文
  • SQL索引有两种,聚集索引和非聚集索引索引主要目的是提高了SQL Server系统性能,加快数据查询速度与减少系统响应时间 下面举两个简单例子: 图书馆例子:一个图书馆那么多书,怎么管理呢?建立一个字母...

    1.1 什么是索引?

    SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间

    下面举两个简单的例子:

    图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员再写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

    看了上面的例子:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。
    还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

    1.2 索引的存储机制

    首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下.
    举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多低,大家可想而知。

    聚集索引和非聚集索引的根本区别是:表记录的排列顺序和与索引的排列顺序是否一致.

    其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是c, 聚集索引就是这样的,他是和表的物理排列顺序是一样的,例如有id为聚集索引,那么1后面肯定是2,2后面肯定是3,所以说这样的搜索顺序的就是聚集索引。非聚集索引就和按照部首查询是一样是,可能按照偏旁查询的时候,根据偏旁‘弓’字旁,索引出两个汉字,张和弘,但是这两个其实一个在100页,一个在1000页,(这里只是举个例子),他们的索引顺序和数据库表的排列顺序是不一样的,这就是非聚集索引。

    原理明白了,那他们是怎么存储的呢?在这里简单的说一下,聚集索引就是在数据库被开辟一个物理空间存放他的排列的值,例如1-100,所以当插入数据时,他会重新排列整个物理空间,而非聚集索引其实可以看作是一个含有聚集索引的表,他仅包含原表中非聚集索引的列和指向实际物理表的指针。他只记录一个指针,其实就有点和堆栈差不多的感觉了

    1.3 什么情况下设置索引
      在这里插入图片描述
    建立索引的原则:

    1. 定义主键的数据列一定要建立索引。(一般数据库默认都会为主键生成索引)

    2. 定义有外键的数据列一定要建立索引。

    3. 对于经常查询的数据列最好建立索引。

    4. 对于需要在指定范围内的快速或频繁查询的数据列;

    5. 经常用在WHERE子句中的数据列。

    6. 经常出现在关键字order by、group by、distinct后面的字段,建立索引。
      如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。

    7. 对于查询中很少涉及的列,重复值比较多的列不要建立索引。
      为什么重复值比较多的列不要建立索引?
      如果mysql评估使用索引比全表更慢,则不使用索引,如果一个列的重复值比较多,就算建立了索引也不会走索引.

    8. 对于定义为text、image和bit的数据类型的列不要建立索引。

    9. 对于经常存取的列避免建立索引 。

    10. 限制表上的索引数目。对一个存在大量更新操作的表,所建索引的数目一般不要超过3个,最多不要超过5个。索引虽说提高了访问速度,但太多索引会影响数据的更新操作。

    11. 对复合索引,按照字段在查询条件中出现的频度建立索引。在复合索引中,记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录,系统再按照第二个字段的取值排序,以此类推。因此只有复合索引的第一个字段出现在查询条件中,该索引才可能被使用,因此将应用频度高的字段,放置在复合索引的前面,会使系统最大可能地使用此索引,发挥索引的作用。

    索引优化视频

    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: 索引属性。

    –创建聚集索引
    create CLUSTERED INDEX 索引名称 ON 表名(字段名)

    –创建非聚集索引
    create NONCLUSTERED INDEX 索引名称 ON 表名(字段名)

    –删除指定约束
    alter table 表名
    drop constraint 主键约束名称

    –将指定字段设置成主键非聚集索引
    alter table 表名
    add constraint 主键约束名称 primary key NONCLUSTERED(字段名)

    –创建表指定主键为非聚集索引,默认不写, NONCLUSTERED为聚集索引
    CREATE TABLE Test
    (
    ID INT PRIMARY KEY NONCLUSTERED --非聚集索引
    )

    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检测、索引缺失

    当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

    首先我们来认识一下DMV,DMV (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 objectname=object_name(s.object_id), s.object_id, indexname=i.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_scans>0
    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 适当创建覆盖索引
    如果索引包含所有满足查询需要的数据的索引称为覆盖索引(Covering Index),也就是平时所说的不需要回表操作。

    简单的说,覆盖索引覆盖所有需要查询的字段(即,大于或等于所查询的字段)。MySQL可以通过索引获取查询数据,因而不需要读取数据行。

    覆盖索引的好处:

    索引大小远小于数据行大小。因而,如果只读取索引,则能极大减少对数据访问量。
    索引按顺序储存。对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少。
    避免对主键索引的二次查询。二级索引的叶子节点包含了主键的值,如果二级索引包含所要查询的值,则能避免二次查询主键索引(聚簇索引,聚簇索引既存储了索引,也储存了值)。

    假设你在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列上有一个非聚集索引,因此它查找非聚集索引树找出ProductID=112的记录;

    2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值,即SalesID);

    3)针对每一个主键(这里是400),SQL Server引擎查找聚集索引树,找出真实的行 在对应页面中的位置;

    4)SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

    在上面的步骤中,对ProductID = 112的每个主键记录(这里是400),SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate,SalesPersonID)。

    如果非聚集索引页中包括了聚集索引键和其它两列(SalesDate,,SalesPersonID)的值,SQL Server引擎可能不会执行上面的第3和4步,直接从非聚集索引树查找ProductID列速度还会快一些,直接从索引页读取这三列的数值。

    幸运的是,有一种方法实现了这个功能,它被称为“覆盖索引”,在表列上创建覆盖索引时,需要指定哪些额外的列值 和聚集索引键值(主键)一起存储在索引页中。下面是在Sales 表ProductID列上创建覆盖索引的例子:

    CREATE INDEX NCLIX_Sales_ProductID_Index name
    ON dbo.Sales(ProductID) --------要在其上创建索引的列
    INCLUDE(SalesDate, SalesPersonID) ----------要包含的附加列值

    应该在那些select查询中常使用到的列上创建覆盖索引,但覆盖索引中包括过多的列也不行,因为覆盖索引列的值是存储在内存中的,这样会消耗过多内存,引发性能下降。

    1.7 索引碎片

    在数据库性能优化一:数据库自身优化一文中已经讲到了这个问题,再次就不做过多的重复地址:数据库性能优化一:数据库自身优化(大数据量)

    1.8 索引实战(摘抄)

    之所以这章摘抄,是因为下面这个文章已经写的太好了,估计我写出来也无法比这个好了,所以就摘抄了

    人们在使用SQL时往往会陷入一个误区,即太关注于所得的结果是否正确,而忽略了不同的实现方法之间可能存在的性能差异,这种性能差异在大型的或是复杂的数据库环境中(如联机事务处理OLTP或决策支持系统DSS)中表现得尤为明显。

    笔者在工作实践中发现,不良的SQL往往来自于不恰当的索引设计、不充份的连接条件和不可优化的where子句。

    在对它们进行适当的优化后,其运行速度有了明显地提高!

    下面我将从这三个方面分别进行总结:

    为了更直观地说明问题,所有实例中的SQL运行时间均经过测试,不超过1秒的均表示为(< 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 < '19991214’and 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 date(28秒)

    select count(*) from record where date >‘19990901’ and place in (‘BJ’,‘SH’)(14秒)

    ---- 分析:---- 在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。

    ---- 3.在place,date,amount上的组合索引

    select count(*) from record where date >‘19991201’ and date < ‘19991214’ and amount >2000 (26秒)

    select date,sum(amount) from record group by date(27秒)

    select count(*) from record where date >‘19990901’ and place in ('BJ, ‘SH’)(< 1秒)

    ---- 分析:---- 这是一个不很合理的组合索引,因为它的前导列是place,第一和第二条SQL没有引用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在组合索引中,形成了索引覆盖,所以它的速度是非常快的。

    ---- 4.在date,place,amount上的组合索引

    select count(*) from record where date >‘19991201’ and date < ‘19991214’ and amount >2000(< 1秒)

    select date,sum(amount) from record group by date(11秒)

    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_no(20秒)

    select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.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< 1000(11秒)

    select * from record whereconvert(char(10),date,112)=‘19991201’(10秒)

    分析:

    where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;

    如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表搜索,因此将SQL重写成下面这样:

    select * from record where card_no like’5378%’(< 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_no='1’来执行。

    我们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用id_no上的索引;

    但实际上(根据showplan),它却采用了"OR策略",即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。因此,实际过程没有利用id_no上索引,并且完成时间还要受tempdb数据库性能的影响。

    实践证明,表的行数越多,工作表的性能就越差,当stuff有620000行时,执行时间竟达到220秒!还不如将or子句分开:

    select count() from stuff where id_no='0’select count() from stuff where id_no=‘1’

    得到两个结果,再作一次加法合算。因为每句都使用了索引,执行时间只有3秒,在620000行下,时间也只有4秒。

    或者,用更好的方法,写一个简单的存储过程:

    create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count() from stuff where id_no='0’select @b=count() from stuff where id_no='1’endselect @c=@a+@bselect @d=convert(char(10),@c)print @d

    直接算出结果,执行时间同上面一样快!

    ---- 总结:---- 可见,所谓优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。

    1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。

    2.in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。

    3.要善于使用存储过程,它使SQL变得更加灵活和高效。

    从以上这些例子可以看出,SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

    参考: SQL索引一步到位(此文章为“数据库性能优化二:数据库表优化数据库性能优化二:数据库表优化”附属文章之一)

    展开全文
  •  SQL索引有两种,聚集索引和非聚集索引索引主要目的是提高了SQLServer系统性能,加快数据查询速度与减少系统响应时间 下面举两个简单例子: 图书馆例子:一个图书馆那么多书,怎么管理呢?建立一个.....

    SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。

      1.1 什么是索引?

      SQL索引有两种,聚集索引和非聚集索引,索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间 

    下面举两个简单的例子:

    图书馆的例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引

    字典的例子:字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

        看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

     

       1.2 索引的存储机制

        首先,无索引的表,查询时,是按照顺序存续的方法扫描每个记录来查找符合条件的记录,这样效率十分低下,举个例子,如果我们将字典的汉字随即打乱,没有前面的按照拼音或者部首查询,那么我们想找一个字,按照顺序的方式去一页页的找,这样效率有多底,大家可以想象。

           聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致,其实理解起来非常简单,还是举字典的例子:如果按照拼音查询,那么都是从a-z的,是具有连续性的,a后面就是b,b后面就是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检测、索引缺失

      当我们明白了什么是索引,什么时间创建索引以后,我们就会想,我们创建的索引到底效率执行的怎么样?好不好?我们创建的对不对?

      首先我们来认识一下DMV,DMV (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 objectname=object_name(s.object_id), s.object_id, indexname=i.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_scans>0

    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列上有一个非聚集索引,因此它查找非聚集索引树找出ProductID=112的记录;

      2)包含ProductID = 112记录的索引页也包括所有的聚集索引键(所有的主键键值,即SalesID);

      3)针对每一个主键(这里是400),SQL Server引擎查找聚集索引树找出真实的行在对应页面中的位置;

      SQL Server引擎从对应的行查找SalesDate和SalesPersonID列的值。

      在上面的步骤中,对ProductID = 112的每个主键记录(这里是400),SQL Server引擎要搜索400次聚集索引树以检索查询中指定的其它列(SalesDate,SalesPersonID)。

      如果非聚集索引页中包括了聚集索引键和其它两列(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秒的均表示为(< 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 < '19991214'and 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 date(28秒)

    select count(*) from record where date >'19990901' and place in ('BJ','SH')(14秒)

    ---- 分析:---- 在群集索引下,数据在物理上按顺序在数据页上,重复值也排列在一起,因而在范围查找时,可以先找到这个范围的起末点,且只在这个范围内扫描数据页,避免了大范围扫描,提高了查询速度。

    ---- 3.在place,date,amount上的组合索引

    select count(*) from record where date >'19991201' and date < '19991214' and amount >2000 (26秒)

    select date,sum(amount) from record group by date(27秒)

    select count(*) from record where date >'19990901' and place in ('BJ, 'SH')(< 1秒)

    ---- 分析:---- 这是一个不很合理的组合索引,因为它的前导列是place,第一和第二条SQL没有引用place,因此也没有利用上索引;第三个SQL使用了place,且引用的所有列都包含在组合索引中,形成了索引覆盖,所以它的速度是非常快的。

    ---- 4.在date,place,amount上的组合索引

    select count(*) from record where date >'19991201' and date < '19991214' and amount >2000(< 1秒)

    select date,sum(amount) from record group by date(11秒)

    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_no(20秒)

    select sum(a.amount) from account a,card b where a.card_no = b.card_no and a.account_no=b.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< 1000(11秒)

    select * from record whereconvert(char(10),date,112)='19991201'(10秒)

    分析:

    where子句中对列的任何操作结果都是在SQL运行时逐列计算得到的,因此它不得不进行表搜索,而没有使用该列上面的索引;

    如果这些结果在查询编译时就能得到,那么就可以被SQL优化器优化,使用索引,避免表搜索,因此将SQL重写成下面这样:

    select * from record where card_no like'5378%'(< 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_no='1'来执行。

    我们期望它会根据每个or子句分别查找,再将结果相加,这样可以利用id_no上的索引;

    但实际上(根据showplan),它却采用了"OR策略",即先取出满足每个or子句的行,存入临时数据库的工作表中,再建立唯一索引以去掉重复行,最后从这个临时表中计算结果。因此,实际过程没有利用id_no上索引,并且完成时间还要受tempdb数据库性能的影响。

    实践证明,表的行数越多,工作表的性能就越差,当stuff有620000行时,执行时间竟达到220秒!还不如将or子句分开:

    select count(*) from stuff where id_no='0'select count(*) from stuff where id_no='1'

    得到两个结果,再作一次加法合算。因为每句都使用了索引,执行时间只有3秒,在620000行下,时间也只有4秒。

    或者,用更好的方法,写一个简单的存储过程:

    create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char(10)beginselect @a=count(*) from stuff where id_no='0'select @b=count(*) from stuff where id_no='1'endselect @c=@a+@bselect @d=convert(char(10),@c)print @d

    直接算出结果,执行时间同上面一样快!

     

    ---- 总结:---- 可见,所谓优化即where子句利用了索引,不可优化即发生了表扫描或额外开销。

    1.任何对列的操作都将导致表扫描,它包括数据库函数、计算表达式等等,查询时要尽可能将操作移至等号右边。

    2.in、or子句常会使用工作表,使索引失效;如果不产生大量重复值,可以考虑把子句拆开;拆开的子句中应该包含索引。

    3.要善于使用存储过程,它使SQL变得更加灵活和高效。

    从以上这些例子可以看出,SQL优化的实质就是在结果正确的前提下,用优化器可以识别的语句,充份利用索引,减少表扫描的I/O次数,尽量避免表搜索的发生。其实SQL的性能优化是一个复杂的过程,上述这些只是在应用层次的一种体现,深入研究还会涉及数据库层的资源配置、网络层的流量控制以及操作系统层的总体设计。

    参考: SQL索引一步到位(此文章为“数据库性能优化二:数据库表优化”附属文章之一)

    转载于:https://www.cnblogs.com/aspirant/p/8928173.html

    展开全文
  • 随着信息时代的来临,数据库已经应用到了越来越多的生产环境中。使用数据库的主要目的就是方便快速地使用数据,数据库中的所有操作都离不开查询优化这个环节,那么需要如何操作才能优化...创建索引的主要过程: ...
  • 不过,你是否碰到过类似情况,查询条件字段明明有创建索引,可是MySQL 服务器为什么没有使用索引,而是走全表扫描呢?是否曾怀疑查询优化器生成执行计划是不是有问题呢?查询优化器根据数据表统计信息来...
  • SQL索引有两种,聚集索引和非聚集索引索引主要目的是提高了SQLServer系统性能,加快数据查询速度与减少系统响应时间下面举两个简单例子:图书馆例子:一个图书馆那么多书,怎么管理呢?建立一个字母开头...
  • 不过,你是否碰到过类似情况,查询条件字段明明有创建索引,可是MySQL 服务器为什么没有使用索引,而是走全表扫描呢?是否曾怀疑查询优化器生成执行计划是不是有问题呢?查询优化器根据数据表统计信息来...
  • SQL索引

    2019-10-28 11:52:54
    索引的一个主要目的就是加快检索表中数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。 如何创建索引 CREATE INDEX语句用于在表中创建索引创建索引有利于在数据库中更快速高效的查找数.....
  • 浅析数据库索引

    2020-06-09 00:22:48
    索引的目的就是加快数据的查询速度,就相当于一本书的目录。 索引的分类和结构 索引的结构(按存储结构分类)主要有B-tree索引,哈希索引和全文索引。 索引的类型主要有聚集索引,非聚集索引和联合索引。 Btree索引 ...
  • 对索引的简单理解 ...因此索引的目的就是更好优化存储结构,从而使用空间换取时间的方法增加查询的效率。当然如果创建了糟糕的索引不仅不会加快查询的效率,反而增加不必要的储存消耗。 B+Tree的演化
  • 维护表有三个主要的目的:找到并修复损坏的表,维护准确的索引统计信息,减少碎片。一、找到并修复损坏的表表损坏很糟糕的事情。对于MySQL存储引擎,表损坏通常系统崩溃导致的。其他引擎也会由于硬件问题、MySQL...
  • 性能优化-索引1 索引1.1 什么是索引1.2 索引的存储机制1.3 创建索引原则1.4 如何创建索引1.4.1 创建索引1.4.1 删除索引1.4.1 显示索引2 案例分析 1 索引 SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引...
  • SQL 索引

    2012-12-15 17:29:29
    索引的最终目的是为了提高查询速度,一般创建到你常做为查询条件的列。 查询呗 ,其它情况下用不到!,而且加完索引后 增删改 都会很慢 所以请慎重加啊! 查询的时候,能使查询的速度提高。不过一个索引大概占用表1.5...
  • 深入理解数据库索引

    2019-09-29 04:13:33
    3. 索引的创建于删除不会影响到数据本身,但会影响到数据检索的速度。 4. 索引也会占用表空间,而且可能会比表本身大 聚集索引(clustered index,也称聚类索引、簇集索引) 聚集索引指数据库表行中数据的物理顺序...
  • 1、什么是索引 索引就是一个指针,指向表里的数据。 索引在数据库里指向数据在表里的准确物理位置。...索引的创建与删除不会影响到数据本身,但会影响数据检索的速度。 索引也会占据物理存储空间
  • 我理解阅读一本经典书,无论是技术书籍还是生活数据,带着目的去读,知道书讲得是什么,并且结合自己理解,输出一定文字。 本篇文章主要详细介绍数据在表中是如何组织和存放,表是特定实体数据集合。 1. ...
  • SQL高级 视图 视图介绍 视图是一种虚拟存在表,对于使用视图...主要是在查看时候方便知道这个是视图,主要是起到见名之意 create view 视图名字 as select 语句 -- 查看视图 show tables; -- 删除视图 drop vi
  • Andy Gutmans和Zeev SurakiZend的主要作者。可以去Zend站点(http://www.zend.com)了解更多。 PHP应用在个人性质web工程中增长显著。根据Netcraft在1999年10月报告,有931122个域和321128个IP地址利用PHP...
  • 实际情况也这样的,XML设计的目的就是用来方便的共享和交互数据的。下一章,我们将系统的了解关于XML的各种术语。 二.DTD的有关术语 什么是DTD,我们上面已经简略提到。DTD一种保证XML文档格式正确的有效...
  • Louis主要的兴趣领域数据库架构和用T-SQL编码,并且,他设计过许多数据库,在这许多年中编写过数以千计存储过程和触发器。  Scott Klein一位独立咨询师,对SQL Server、.NET和XML相关所有知识都充满热情。...
  • 5.1.3 在多个列上创建索引 171 5.1.4 定义索引列排序方向 172 5.1.5 查看索引元数据 172 5.1.6 禁用索引 174 5.1.7 删除索引 174 5.1.8 使用DROP_EXISTING改变既有索引 174 5.2 控制索引创建性能和...
  • 27、GC是什么? 为什么要有GC?  GC是垃圾收集意思(Gabage Collection),内存处理是编程人员容易出现问题地方,忘记或者错误内存回收会导致程序或系统不稳定甚至崩溃,Java提供GC功能可以自动监测对象...
  • 说明:Oracle中需要创建用户一定要具有dba(数据库管理员)权限用户才能创建,而且创建的新用户不具备任何权限,连登录都不可以。 用法:create user 新用户名 identified by 密码 例子: 2. 修改密码 说明:...
  • 对计算输出结果可以采取不同的处理手段,通常要求直接显示在用户使用界面上,但是为了达到能够方便的与其它应用程序进行数据交换通信,以及与其他高级语言进行混合编程的目的,我们还需要设计相应的实现接口,以达...
  • 4.2.1 反射原理,反射创建类实例三种方式是什么。 4.2.2 反射中,Class.forName和ClassLoader区别 。 4.2.3 描述动态代理几种实现方式,分别说出相应优缺点。 4.2.4 动态代理与cglib实现区别。 4.2.5 ...
  • FoxPro比FoxBASE在功能和性能上又有了很大改进,主要是引入了窗口、按纽、列表框和文本框等控件,进一步提高了系统开发能力。  什么是数据库主码(主键)?  能够唯一表示数据表中每个记录【字段】或者...
  • 并利用具体例子来全面介绍每个特性,不仅讨论了各个特性是什么,还说明了它是如何工作,如何使用这个特性来开发软件,以及有关常见陷阱。  本书面向所有oracle 数据库应用开发人员和dba。 作译者 作者  ...
  • [Oracle.11g权威指南(第2版)].谷长勇.扫描版.pdf

    千次下载 热门讨论 2013-06-23 21:16:09
    14.6 创建索引分区 338 14.6.1 索引分区概念 338 14.6.2 本地索引分区 338 14.6.3 全局索引分区 340 14.7 管理索引分区 340 14.7.1 索引分区管理操作列表 341 14.7.2 索引分区管理操作实例 341 14.8 查看分区...

空空如也

空空如也

1 2 3 4
收藏数 65
精华内容 26
关键字:

创建索引的目的主要是什么