精华内容
下载资源
问答
  • 索引是以表列为基础的数据库对象,它保存着表排序的索引列,并且记录了索引数据表中的物理存储位置,实现了表数据逻辑排序,其主要目的是提高SQL Server系统性能,加快数据查询速度和减少系统响应...

    索引的用途

    索引是以表列为基础的数据库对象,它保存着表中排序的索引列,并且记录了索引列在数据表中的物理存储位置,实现了表中数据的逻辑排序,其主要目的是提高SQL Server系统的性能,加快数据的查询速度和减少系统的响应时间。
    使用索引的代价
    建立索引的几点原则。

    (1) 定义有主键的数据列一定要建立索引。因为主键可以加速定位到表中的某一行。

    (2) 定义有外键的数据列一定要建立索引。外键列通常用于表与表之间的连接,在其上创建索引可以加快表间的连接。

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

    ① 对于需要在指定范围内快速或频繁查询的数据列,因为索引已经排序,其指定的范围是连续的,查询可以利用索引的排序,加快查询的时间。

    ② 经常用在WHERE子句中的数据列,将索引建立在WHERE子句的集合过程中,对于需要加速或频繁检索的数据列,可以让这些经常参与查询的数据列按照索引的排序进行查询,加快查询的时间。

    (4) 对于那些查询中很少涉及的列、重复值比较多的列不要建立索引。例如,在查询中很少使用的列,有无索引并不能提高查询的速度,相反增加了系统维护时间和消耗了系统空间;又如,“性别”列只有列值“男”和“女”,增加索引并不能显著提高查询的速度。

    (5) 对于定义为text、image和bit数据类型的列不要建立索引。因为这些数据类型的数据列的数据量要么很大,要么很小,不利于使用索引。

    展开全文
  • 索引的一个主要目的就是加快检索表数据的方法。 例子:这样一个查询语句selecr * from table1 where id=1000; 如果没有索引的话,必须遍历整个表,知道id等于10000的这一行被找到为止。但是有了索引之后(必须...

    说白了索引就是一个查找问题

    索引是对数据库表中的一列或者说是多列进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。索引的一个主要目的就是加快检索表中数据的方法

    例子:这样一个查询语句selecr * from table1 where id=1000;
    如果没有索引的话,必须遍历整个表,知道id等于10000的这一行被找到为止。但是有了索引之后(必须在id这一列上建立索引),即可在索引中查找,由于索引是经过某种算法优化过的,因为查找次数要少的多。可见索引是用定位的O(logN)。

    数据库索引的数据结构基础:B+tree

    数据库索引是用于提高数据库表的数据访问速度

    a)、索引的目的是什么?

    1 能够避免全表扫描(如:数据表中有1000条数据,我们只需要根据条件查询其中的一条,针对该列创建一个索引只需要扫描这一条即可如果不创建索引我们得扫描1000条
    2 让服务器快速定位到表的位置提高检索速度
    3 帮助服务器避免排序和临时表的生成
    将随机 I/O 变成 顺序 I/O
    5 创建唯一性索引,保证数据库表中每一行数据的唯一性
    加速表和表之间的连接
    7 使用分组排序子句进行数据检索时,可以显著减少查询中分组和排序的时间

    b)、索引对数据库系统的负面影响是什么?

    创建索引和维护索引需要耗费时间,这个时间随着数据量的增加而增加
    2 索引需要占用物理空间,不光是表需要占用数据空间,每个索引也需要占用物理空间
    3 当对表进行增、删、改的时候,索引也要动态维护这样就降低了数据的维护速度

    c)、MySQL索引的创建原则(在哪些列上,以怎样的方式,创建索引更好)

    1 在经常需要搜索的列上,可以加快搜索的速度

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

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

    选择性最高的列(选择性=不重复的索引值/总记录)

    对字符串进行索引,保证选择性较高前缀不能太长,即前缀的基数应接近完整列的基数可以节省大量的索引空间后缀索引:字段值反向存储;(注意:平均选择性高,数据分布不均,陷阱)

    6 根据情况创建复合索引,复合索引可以提高查询效率;

    避免创建过多索引索引会额外占用磁盘空间降低写操作效率

    8 主键尽可能选择较短的数据类型(如:int/bigint),可以有效减少索引的磁盘占用提高查询效率

    d)、 不应该在这些列上建立索引?

    1 对于那些在查询中很少使用或者参考的列不应该创建索引,这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求
    当修改性能远远大于检索性能时,不应该创建索引,这是因为修改功能和检索功能是互相矛盾的

    如何在使用SQL创建索引:

    之前的例子中,在Employee_Name列上创建索引的SQL如下:

    CREATE INDEX name_index
    ON Employee (Employee_Name)
    

    如何创建联合索引

    我们可以在雇员表上创建两个列的联合索引,SQL如下:

    CREATE INDEX name_index
    ON Employee (Employee_Name, Employee_Age)
    

    索引的类型

    索引有很多种类型,都是实现在存储引擎层的

    1 普通索引:最基本的索引,没有任何约束限制。
    2 唯一索引:与普通索引类似,但是 具有唯一性约束。
    3 主键索引:特殊的唯一索引,不允许有空值。
    4 组合索引:将多个列组合在一起创建,可以覆盖多个列。
    5 外键索引:只有 InnoDB类型的表才可以使用外键索引,保证数据的一致性、完整性和实现级联操作。【基本上不使用】
    6 全文索引:MySQL自带的全文索引只能用于 MyISAM,并且只能对英文进行全文检索。【基本上不使用】
    7 B+树索引
    8 哈希索引
    9 空间数据索引(R-Tree)

    • MyISAM存储引擎
    • 用作地理数据存储
    • 无须前缀查询
    • 从所有维度索引数据
    • 使用任意维度来组合查询
    • B+Tree的不同
      可使用所有维度来索引数据,而无需最左前缀原则

    MySQL索引的注意事项

    ① 复合索引遵循前缀原则;

    // 创建一个索引
    KEY(a, b, c); 
    
    // 以下3种情况该索引生效:
    WHERE a=1 and b=2 and c=3;
    WHERE a=1 and b=2;
    WHERE a=1
    
    // 以下2情况该索引不生效:
    WHERE b=2 and c=3; // 因为跳过了a
    WHERE a=1 and c=3; // 因为跳过了b
    

    常考题:

    • 题目:我创建了一个 WHERE a=1 and b=2 或者 WHERE a=1这样的查询,我需要创建一个什么样的索引比较合适?
    • 答案:KEY(a, b);

    注:KEY(a, b,c) 表示 创建了一个索引,同时作用于a,b,c,目的是为了缩短查询范围(如:创建一个组合索引,章节段,查询第几章中的第几节的第几段,这样缩短了查询的范围)。

    ② like 查询,% 不能放前,可以使用全文索引;

    where name like "%wang%"; // 不要使用该方法
    

    ③ column is null 可以使用索引;

    ④ 如果MySQL估计使用索引比全表扫描更慢,会放弃使用索引;

    假设表中有100条数据,在写条件的时候这样写

    where id > 1 and id < 100 
    

    即使对 ID设置了索引,在搜索的时候,搜索的是 2~99 之间的数据。

    搜索过程:首先找 2 这条数据,去索引中看看它在什么位置,找到索引后,再去定位数据行;然后再去看 3, 再去看索引,再去找数据行,;再去看 4,再去看索引,再去找数据行,… ,一直到 99 都要这样去操作。

    所以在这里面本身来说,我们这样来查询,首先我们会多一个查询索引的步骤,所以MySQL认为,本身就100条数据,要查询其中的98条数据,而这98条数据都需要去看索引,所以就很慢,直接把这100条数据都扫描一遍,然后返回其中的98条 不就可以了么,这样效率会更高,它会自动去放弃,不需要我们手动放弃,这是我们优化器做的事

    ⑤ 如果 or 前的条件中的列有索引,后面的没有,索引都不会被用到; 老版本不合并,5.0及以上版本合并

    where a or b; // 如果a中有索引,b中没有索引,该索引就会失效
    

    ⑥ 列类型是字符串类型,查询时一定要给值加引号否则索引失效

    // 在创建的时候设置了 name varchar(16)
    // 搜索的时候,name为100
    where name = 100; // 写成这样的形式,可以搜索到,但是没有用到索引【因为本身是字符串类型,而写成了整型】
    where name = "100"; // 此时才用到了索引【一定要给值加引号】
    

    创建 MySQL复合索引(组合索引) 应该注意哪些事项?

    • 遵循前缀原则
    • 看查询条件(如果查询条件中是 WHERE a=1 AND b=2 我们可以创建一个 KEY(a,b) 这样的复合索引 ,如果搜索 WHERE a=1 时,该索引也可以被使用到)。

    https://blog.csdn.net/csdnlijingran/article/details/88607333

    展开全文
  • SQL索引在数据库优化占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。 1.1 什么是索引? 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 什么情况下设置索引
      在这里插入图片描述
    建立索引的原则:

    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索引一步到位(此文章为“数据库性能优化二:数据库表优化数据库性能优化二:数据库表优化”附属文章之一)

    展开全文
  • 概述生产库sqlserver怎么也占了三分之一,所以今天主要聊聊怎么sqlserver数据库上去建索引。1、索引概述创建索引一般有以下两个目的:维护被索引唯一性和提供快速访问表数据策略。大型数据库有两种索引...

    概述

    生产库中sqlserver怎么也占了三分之一,所以今天主要聊聊怎么在sqlserver数据库上去建索引。


    1、索引概述

    创建索引一般有以下两个目的:维护被索引列的唯一性和提供快速访问表中数据的策略。

    大型数据库有两种索引即簇索引和非簇索引:

    1)非簇索引的表是按堆结构存储数据,所有的数据均添加在表的尾部;

    2)簇索引的表,其数据在物理上会按照簇索引键的顺序存储,一个表只允许有一个簇索引。

    因此,根据B树结构,可以理解添加任何一种索引均能提高按索引列查询的速度,但会降低插入、更新、删除操作的性能,尤其是当填充因子(Fill Factor)较大时。所以对索引较多的表进行频繁的插入、更新、删除操作,建表和索引时因设置较小的填充因子,以便在各数据页中留下较多的自由空间,减少页分割及重新组织的工作。

    索引是从数据库中获取数据的最高效方式之一。95% 的数据库性能问题都可以采用索引技术得到解决。作为一条规则,我通常对逻辑主键使用唯一的成组索引,对系统键(作为存储过程)采用唯一的非成组索引,对任何外键列[字段]采用非成组索引。不过,索引就象是盐,太多了菜就咸了。你得考虑数据库的空间有多大,表如何进行访问,还有这些访问是否主要用作读写,(即:在实际使用当中,应该充分考虑到索引的开销,包括磁盘空间的开销及处理开销(如资源竞争和加锁)。如果数据频繁的更新或删加,就不宜建立索引)

    实际上,可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。


    2、聚集索引结构

    在 SQL Server 中,索引是按 B 树结构进行组织的。

    聚集索引单个分区中的结构:

    b0d09eb452ad0a6d733b103a20f4c04a.png


    聚集索引(Clustered Index)特点:

    • 聚集索引的叶节点就是实际的数据页
    • 聚集索引中的排序顺序仅仅表示数据页链在逻辑上是有序的。而不是按照顺序物理的存储在磁盘上
    • 行的物理位置和行在索引中的位置是相同的
    • 每个表只能有一个聚集索引
    • 聚集索引的平均大小大约为表大小的5%左右

    3、非聚集索引结构

    非聚集索引与聚集索引具有相同的 B 树结构,它们之间的显著差别在于以下两点:

    • 1. 基础表的数据行不按非聚集键的顺序排序和存储。
    • 2. 非聚集索引的叶层是由索引页而不是由数据页组成。

    下图示意了单个分区中的非聚集索引结构:

    6e5ce8f04f6ac478a52028024358f916.png

    非聚集索引 (Unclustered Index) 特点:

    • 非聚集索引的页,不是数据,而是指向数据页的页。
    • 若未指定索引类型,则默认为非聚集索引。
    • 叶节点页的次序和表的物理存储次序不同
    • 每个表最多可以有249个非聚集索引
    • 在非聚集索引创建之前创建聚集索引(否则会引发索引重建)

    4、聚集索引和非聚集索引的区别

    其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,就会很自然地翻开字典的前几页,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果你翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明你的字典中没有这个字;同样的,如果查“张”字,那你也会将你的字典翻到最后部分,因为“张”的拼音是“zhang”。也就是说,字典的正文部分本身就是一个目录,你不需要再去查其他目录来找到你需要找的内容。
    我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。
    如果你认识某个字,你可以快速地从自动中查到这个字。但你也可能会遇到你不认识的字,不知道它的发音,这时候,你就不能按照刚才的方法找到你要查的字,而需要去根据“偏旁部首”查到你要找的字,然后根据这个字后的页码直接翻到某页来找到你要找的字。但你结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如你查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在你看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到你所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到你所需要的页码。
    我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。
    进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

    两者的存储特点:

    1. 聚集索引。表数据按照索引的顺序来存储的,也就是说索引项的顺序与表中记录的物理顺序一致。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页。 在一张表上最多只能创建一个聚集索引,因为真实数据的物理顺序只能有一种。
    2. 非聚集索引。表数据存储顺序与索引顺序无关。对于非聚集索引,叶结点包含索引字段值及指向数据页数据行的逻辑指针,其行数量与数据表行数据量一致。

    总之,聚集索引是一种稀疏索引,数据页上一级的索引页存储的是页指针,而不是行指针。而对于非聚集索引,则是密集索引,在数据页的上一级索引页它为每一个数据行存储一条索引记录。


    5、使用 聚集索引 或 非聚集索引 的场景

    下面的表总结了何时使用聚集索引或非聚集索引:

    967c59e264668d88251117b20f421170.png

    总结

    “水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。
    所以说,我们要建立一个“适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使数据库能得到高性能的发挥。

    觉得有用的朋友多帮忙转发哦!后面会分享更多devops和DBA方面的内容,感兴趣的朋友可以关注下~

    0ca57b922ef6519f34cf239d2c481239.gif
    展开全文
  • 概述生产库sqlserver怎么也占了三分之一,所以今天主要聊聊怎么sqlserver数据库上去建索引。1、索引概述创建索引一般有以下两个目的:维护被索引唯一性和提供快速访问表数据策略。大型数据库有两种索引...
  • 数据库索引

    2018-03-26 21:16:00
    索引是对数据库一列或多列的值进行排序的一种结构,使用索引可快速访问数据库的特定信息。如果想按特定职员的姓来查找他或...建立索引的目的是加快对表记录的查找或排序。为表设置索引要付出代价的:一是...
  • 索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理...
  • SQL索引在数据库优化占有一个非常大的比例,一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。  1.1什么是索引?  SQL索引有两种,聚集索引和非聚集索引,索引...
  • SQL索引在数据库优化占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。  1.1 什么是索引?  SQL索引有两种,聚集索引和非聚集索引,...
  • 数据库索引初步了解

    热门讨论 2018-05-27 21:25:14
    目的索引的一个主要目的就是加快检索表数据的方法。是针对表而建立的,它是由数据页面以外的索引页面组成的,每个索引页面的行都会含有逻辑指针,以便加速检索物理数据,能协助信息搜索者尽快的找到符合限制条件...
  • 索引的一个主要目的就是加快检索表数据,亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。 如果说把数据库看作字典的话,那么索引可以被看作目录。 在数据库,对字段建立索引可以大大提高...
  • 建立索引的目的是加快对表记录的查找或排序,为表设置索引要付出代价;一是增加了数据库的存储空间,二是插入和修改数据时要花费更多的时间(因为索引也会随之改变)。 (1),设置合适的索引之后,数据库利用...
  • oracle---数据库索引

    2018-07-02 17:26:02
    索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理...
  • 索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段得与数据库结构一起考虑。应用系统的性能直接与索引的合理...
  • 数据库索引(Oracle和Mysql)学习总结

    千次阅读 2018-07-04 14:08:19
    索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理...
  • 索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理...
  • 索引 索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的...
  • 索引,索引的建立、修改、删除2007-10-05 13:29 来源: 作者: 网友评论 0 条 浏览次数 2986索引索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性...
  • SQL索引在数据库优化占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。  1.1 什么是索引?  SQL索引有两种,聚集索引和非聚集索引,索引...
  • 索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理...
  • 索引是关系数据库中用于存放每一条记录的一种对象,主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理...

空空如也

空空如也

1 2 3 4 5 ... 11
收藏数 202
精华内容 80
关键字:

在数据库中建立索引的主要目的是