精华内容
下载资源
问答
  • 上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这...

    一、前言

    上一节我们讲解了聚集索引和非聚集索引的区别(索引知识系列一:聚集索引与非索引详解 ),我们知道非聚集索引在查询过程中有回表的过程,这就造成了效率的下降。那如何不用回表或者减少回表以提高查询速度呢?这就是本章要讲的内容。

    二、联合索引

    联合索引(也叫组合索引、复合索引、多列索引)是指对表上的多个列进行索引。联合索引的创建方法跟单个索引的创建方法一样,不同之处仅在于有多个索引列。

    开讲之前我们先弄一张学生表,表数据如下:
    在这里插入图片描述
    下面我们给出一个需求:查询表中以字母"L"开头的姓名及年龄。

    1、常规的写法(回表查询)

    SELECT name,age FROM `t_user` where name like 'l%' ;
    

    这种写法,明显效率是低下的,我们用explain 分析一下:
    在这里插入图片描述
    由图中可以看出,在数据库中进行了全表扫描。下面我们看一下数据库中的执行过程。

    第一步:全表扫描数据,找出以“l”开头的主键id.
    第二步:将所有查询出来的数据每一个都回表,根据id来查询出想要的数据。
    

    2.优化写法(索引覆盖)
    因为我们要查询name和age。所以,我们对name和age建立了联合索引,建立后的索引图如下:
    在这里插入图片描述
    从图中我们可以看出,叶子节点中的键值都是按顺序存储的并且都包含了名字和年龄,即(“Ann”,36)、(“HanMeimei”,17)、(“Kate”,17)、(“LiLei”,18)、(“Lili”,16)、(“Lisa”,19)、(“Lucy”,17)、(“WeiHua”,32)、(“ZhangWei”,18)、(“ZhangWei”,25)。

    索引会先根据 name 排序,如果 name 相同,再根据 age 进行排序。

    我们对name和age建立索引后,当我们查询name和age二个字段时,直接会从索引中查出来而不需要回表查询,这种方式就是索引覆盖。执行步骤是这样的:

    第一步:使用联合索引(name,age)查询以“l”开头的数据
    第二步:在索引中取出name和age.
    

    这种方式是不是高效多了,你要是还不信,我们用explain看一下,如下图:

    EXPLAIN SELECT name,age FROM `t_user` where name like 'l%' ;
    

    在这里插入图片描述
    从图中我们看的出,使用了(name,age)索引。

    2.1 联合索引最左匹配原则

    联合索引在使用的时候一定要注意顺序,一定要注意符合最左匹配原则。

    最左匹配原则:在通过联合索引检索数据时,从索引中最左边的列开始,一直向右匹配,如果遇到范围查询(>、<、between、like等),就停止后边的匹配。

    这个定义不太好理解,我解释一下:

    假如对字段 (a, b, c) 建立联合索引,现在有这样一条查询语句:

    where a > xxx and b=yyy and c=zzz
    where a like 'xxx%' and b=yyy and c=zzz
    

    在这个条件语句中,只有a用到了索引,后面的b,c就不会用到索引。这就是“如果遇到范围查询(>、<、between、like等),就停止后边的匹配。”的意思。

    我们还是假如对字段 (a, b, c) 建立联合索引,

    1.如下查询语句可以使用到索引:

    where a = xxx
    where a = xxx and b = xxx
    where a = xxx and b = xxx and c = xxx
    where a like 'xxx%'
    where a > xxx
    where a = xxx order by b
    where a = xxx and b = xxx order by c group by a
    

    2.如下查询条件也会使用索引:

    where b = xxx and a = xxx
    where a = xxx and c = xxx and b = xxx
    

    虽然b和a的顺序换了,但是mysql中的优化器会帮助我们调整顺序。

    3.如下查询条件只用到联合索引的一部分:

    where a = xxx and c = xxx   可以用到 a 列的索引,用不到 c 列索引。
    where a like 'xxx%' and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    where a > xxx and b = xxx 可以用到 a 列的索引,用不到 b 列的索引。
    

    4.如下查询条件完全用不到索引

    where b = xxx
    where c = xxx
    where a like '%xxx'			-- 不满足最左前缀
    where d = xxx order by a	-- 出现非排序使用到的索引列 d 
    where a + 1 = xxx	-- 使用函数、运算表达式及类型隐式转换等
    

    如何选择合适的联合索引

    1.where a = xxx and b = xxx and c = xxx 如果我们的查询是这样的,建索引时,就可以考虑将选择性高的列放在索引的最前列,选择性低的放后边。

    2.如果是 where a > xxx and b = xxx 或 where a like ‘xxx%’ and b = xxx 这样的语句,可以对 (b, a) 建立索引。

    3.如果是 where a = xxx order by b 这样的语句,可以对 (a, b) 建立索引。

    三、索引覆盖

    索引覆盖在上面我们已经介绍了。由上面的介绍我们知道,建立了联合索引后,直接在索引中就可以得到查询结果,从而不需要回表查询聚簇索引中的行数据信息。

    索引覆盖可以带来很多的好处:

    • 辅助索引不包含行数据的所有信息,故其大小远小于聚簇索引,因此可以减少大量的IO操作。
    • 索引覆盖只需要扫描一次索引树,不需要回表扫描聚簇索引树,所以性能比回表查询要高。
    • 索引中列值是按顺序存储的,索引覆盖能避免范围查询回表带来的大量随机IO操作。 判断一条语句是否用到索引覆盖:

    这个我们需要用explain查看一下。
    在这里插入图片描述
    Using index 就表示使用到了索引 , 并且所取的数据完全在索引中就能拿到,也就是用到了索引覆盖。

    四、索引下推

    索引下推是索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。

    索引下推是把本应该在 server 层进行筛选的条件,下推到存储引擎层来进行筛选判断,这样能有效减少回表。

    举例说明:

    首先使用联合索引(name,age),现在有这样一个查询语句:

    select *  from t_user where name like 'L%' and age = 17;
    

    这条语句从最左匹配原则上来说是不符合的,原因在于只有name用的索引,但是age并没有用到。

    不用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:再根据这四条索引数据中的 id 值,逐一进行回表扫描,从聚簇索引中找到相应的行数据,将找到的行数据返回给 server 层。
    第三步:在server层判断age = 17,进行筛选,最终只留下 Lucy 用户的数据信息。
    

    使用索引下推的执行过程:

    第一步:利用索引找出name带'L'的数据行:LiLei、Lili、Lisa、Lucy 这四条索引数据
    第二步:根据 age = 17 这个条件,对四条索引数据进行判断筛选,最终只留下 Lucy 用户的数据信息。
    (注意:这一步不是直接进行回表操作,而是根据 age = 17 这个条件,对四条索引数据进行判断筛选)
    第三步:将符合条件的索引对应的 id 进行回表扫描,最终将找到的行数据返回给 server 层。
    

    比较二者的第二步我们发现,索引下推的方式极大的减少了回表次数。

    索引下推需要注意的情况:

    下推的前提是索引中有 age 列信息,如果是其它条件,如 gender = 0,这个即使下推下来也没用

    开启索引下推:

    索引下推是 MySQL 5.6 及以上版本上推出的,用于对查询进行优化。默认情况下,索引下推处于启用状态。我们可以使用如下命令来开启或关闭。

    set optimizer_switch='index_condition_pushdown=off'; 	-- 关闭索引下推
    set optimizer_switch='index_condition_pushdown=on';		-- 开启索引下
    

    五、结尾

    好了,本章就讲到这里吧,下一章,我们对所有的知识进行一下总结。

    另外大家帮忙关注我,每天更新优质内容。关注我有大量学习资料和学习视频赠送。
    在这里插入图片描述
    扫二维码关注公众号【Java程序员的奋斗路】可领取如下学习资料:
    1.1T视频教程(大约有100多个视频):涵盖Javaweb前后端教学视频、机器学习/人工智能教学视频、Linux系统教程视频、雅思考试视频教程,android.等
    2.项目源码:20个JavaWeb项目源码

    展开全文
  • 普通索引 与 唯一索引1、普通索引2、唯一索引3、在不同业务情况下 唯一索引 与 普通索引 的选择场景 : 维护与一个市民系统 :查询过程更新过程 :总结 :change bufferchange buffer 和 redo log 运行情景插入数据读取...


    唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。

    非主键的都是二级索引, 二级索引又包括了唯一索引和普通索引

    1、普通索引

    • 普通索引 -> 由关键字KEY或INDEX定义的索引
    • 作用 : 普通索引的唯一任务是加快对数据的访问速度
    • 适用场景 :
      • 查询条件为(WHERE column)
      • 排序条件为(ORDER BY column)中的数据列创建索引
      • 只要有可能,就应该选择一个数据最整齐、最紧凑的数据列来创建索引。

    2、唯一索引

    • 普通索引允许被索引的数据列包含重复的值
    • 如果能确定某个数据列将只包含彼此各不相同的值,在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。
    • 优点 :
      • 一是简化了MySQL对这个索引的管理工作,这个索引也因此而变得更有效率;
      • 二是MySQL会在有新记录插入数据表时,自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了;如果是,MySQL将拒绝插入那条新记录。
      • 唯一索引可以保证数据记录的唯一性
      • 事实上,在许多场合,人们创建唯一索引的目的往往不是为了提高访问速度,而只是为了避免数据出现重复。

    3、在不同业务情况下 唯一索引 与 普通索引 的选择

    场景 : 维护与一个市民系统 :
    • 每一个人都有对应的且唯一的身份证号码, 而且业务代码保证不会写入两个重复的身份证号码, 如果需要查询市民的姓名, 就会执行类似下面的 SQL 语句 :

      select name from CityUser where id_card = 'xxxxxxxyyyyyyzzzzz';
      
    • id_card 索引建立考虑

      • id_card 字段创建唯一索引
      • 创建一个普通索引
    • 在逻辑上, 这两个选择都是正确的, 但在性能方面该如何做出选择呢 ?

    查询过程
    • 执行查询的语句
    select id from Table where k = 5;
    
    • 存储结构
    image-20210109135449069
    • 普通索引 :
    • 查找到满足条件的第一个记录 (5, 500)后, 需要查找下一个记录, 直到碰到第一个不满足 k = 5 的记录
    • 唯一索引 :
      • 由于唯一索引具有唯一性, 查找第一个满足条件的记录后, 就会停止继续检索
    • 性能差距
      • 对性能的影响 - > 微乎其微
      • 两者的性能差距在于 普通索引相对于 唯一索引 需要多进行一次 “查找和判断下一条记录”, 就相当于多一次指针寻找和计算
      • InnoDB 的数据是安数据页为单位进行读写的, 也就是说, 当需要读一条记录的时候, 是以页为单位, 将其整体读如内存, 在 InnoDB 中 页的大小为 16KB
      • 考虑到如果 k = 5 的数据为这一页的最后一个数据, 那么查询的时候会出现跨页情况, 同时相对于 16KB 数据来说, 最后一个数据的概率基本可以达到忽略不计, 对 CPU 操作来说这个成本基本上可以忽略不计
    • 结论 : 在查询过程中 普通索引 的性能与 唯一索引 的性能只存在微小的差距, 可以忽略不计
    更新过程 :
    • 插入一条数据 (4, 400), InnoDB 的处理流程
    • 更新数据页在内存中 :
      • 唯一索引 : 找到目的位置 -> 判断有无冲突 -> 插入数据 -> 执行结束
      • 普通索引 : 找到目的位置 -> 插入数据 -> 执行结束
      • 区别 : 唯一索引 对比 普通索引 多了一次判断过程, 但只是对 CPU 消耗极其微小的时间
    • 更新数据页不在内存中 :
      • change buffer 底下面有解释
      • 唯一索引 : 将数据页读入内存 -> 找到目的位置 -> 判断有无冲突 -> 插入数据 -> 执行结束
      • 普通索引 : 将更新记录在 change buffer -> 执行结束
      • 区别 : 唯一索引会进行大量的读磁盘操作, 较严重的影响了数据的写入效率, 在这个方面性能比普通索引低很多
    总结 :
    • 其实, 这两类索引在查询能力上是没有差别的, 主要考虑的是对更新的性能的影响, 所以建议尽量选择普通索引

    • 普通索引对大表的更新优化还是很明显的

    • 普通索引 与 唯一索引, 普通索引在更新时速度更快, 尽量选择普通索引

    • 更新之后就是查询时, 不使用 change buffer

    • change buffer 更适合普通索引

    • 如果所有的更新后面,都马上伴随着对这个记录的查询,那么你应该关闭 change buffer。而在其他情况下,change buffer 都能提升更新性能。

    innodb_change_buffer_max_size = 0
    
    change buffer
    • Q :

      • 内存大小
      • 运行逻辑
      • 有无满情景 ->
      • 执行时间
      • 执行流程,数据一致性控制
    • change buffer 是一个可以持久化的数据, 所以在内存种有拷贝, 当然也会写入磁盘种

    • 更新一个数据页

      • 数据页在内存中就直接更新
      • 这个数据页不在内存中, 在不影响数据一致性的前提下, InnoBD 会将这些更新操作缓存在 change buffer 中, 这样就不需要从磁盘中读入这个数据页, 在下次查询需要访问这个数据页的时候, 将数据页读如内存, 然后执行 change buffer 中与 这个页有关的操作, 通过这种方式来保证这个数据逻辑的正确性
    • 更新后 - > merge

      • 将 change buffer 中下的操作应用到原数据页, 得到的最新结果为 merge
      • 触发 merge 的情景 :
        • 访问数据页, 加载进入内存的时候
        • 系统后台线程会定期 merge
        • 在数据库正常关闭 ( shutdown ) 的过程中
    • 优点 :

      • 减少读磁盘的次数, 语句的执行速度会得到明显的提升
      • 数据读入内存是需要 buffer pool 的, 所以这种方式还能避免占用内存, 提高内存利用率
    • 使用情景 :

      • change buffer 只限用于 普通索引, 且不适用 唯一索引
      • 因 merge 的时候是真正进行数据更新的时候, 而 change buffer 的主要目的是将变更动作缓存下来, 在一个数据页做 merge 之前记录的变更越多, change buffer 记录的变更越多收益就越大
      • 对于写多读少的业务来说, 使用 change buffer 的效果最好
      • 对于写完立即读的业务而言, changer buffer 反而会起反作用, 因为这样随机 IO 的次数并不会减少, 反而增加了 change buffer 的维护代价
    change buffer 和 redo log 运行情景
    • WAL 提升性能的核心机制 -> 尽量减少随机读写
    插入数据
    // k1 在内存中, k2 不在内存中
    mysql> insert into t(id,k) values(id1,k1),(id2,k2);
    

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ok8OykY3-1610175490702)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210109144554266.png)]

    • 更新流程 :
      • Page 1 在内存中, 直接更新内存
      • Page 2 不在内存中, 就在内存的 change buffer 中记录操作
      • 将上述两个动作记入 redo log 中 -> 3, 4
      • 事务完成
      • 总的操作 : 写两处内存, 写一次磁盘
    读取数据
    • 读数据不会涉及到 redo log 所以 redo log 不会参与读的过程中

    [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-fB036Y22-1610175490706)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210109144948698.png)]

    • 读数据的流程
      • 读 Page 1 的时候,直接从内存返回
      • 要读 Page 2 的时候,需要把 Page 2 从磁盘读入内存中,然后应用 change buffer 里面的操作日志,生成一个正确的 merge
    总结 :
    • redo log 主要节省的是随机写磁盘的 IO 消耗(转成顺序写),
    • change buffer 主要节省的则是随机读磁盘的 IO 消耗。
    change buffer 会不会因为断电而数据丢失呢 ?
    • 答案肯定是不会的, 不然肯定不会拿来使用的, 毕竟数据丢失可是很大的问题
    • change buffer有一部分在内存有一部分在 ibdata.
      • 做purge操作,应该就会把change buffer里相应的数据持久化到ibdata
    • redo log里记录了数据页的修改以及change buffer新写入的信息
    • 如果掉电,持久化的change buffer数据已经purge,不用恢复。主要分析没有持久化的数据, 情况又分为以下几种:
      • change buffer 写入,redo log 虽然做了 fsync 但未commit, binlog 未 fsync 到磁盘,这部分数据丢失
      • change buffer 写入,redo log写入但没有commit, binlog以及 fsync 到磁盘,先从 binlog 恢复 redo log,再从 redo log 恢复 change buffer
      • change buffer 写入,redo log 和 binlog 都已经 fsync.那么直接从redo log 里恢复。

    参考 : 林晓斌 MySQL实战45讲 https://time.geekbang.org/column/article/70848

    [gentleman_hai] (https://home.cnblogs.com/u/gentlemanhai/) https://www.cnblogs.com/gentlemanhai/p/13685295.html

    展开全文
  • SQL Server 索引管理——禁用无用索引

    千次阅读 2019-05-28 13:38:34
    SQL Server 索引管理——禁用无用索引 前文中,对于不再使用的索引,直接生成删除的脚本,在文中,也提到了直接删除无用索引存在的风险。为了保险起见,如果我们使用的是SQLServer 2005及以后版本,我们可以使用...

    SQL Server 索引管理——禁用无用索引

     

    前文中,对于不再使用的索引,直接生成删除的脚本,在文中,也提到了直接删除无用索引存在的风险。为了保险起见,如果我们使用的是SQL Server 2005及以后版本,我们可以使用其新增加的功能:索引禁用。这样如果后期发现禁用掉的索引是需要的,我们就可以及时重建索引,保证数据库的性能,如果通过足够时长的观察,确定索引确实无用,则可以将禁用索引删除(备份、删除禁用索引的方式将在后续文章中给出)。对于的禁用后的索引,需要重新启用,只需要使用索引重建就可以启用。

    禁用、启用索引脚本如下:

    USE [DBName]
    GO
    --禁用索引语法
    ALTER INDEX indexName ON tableName DISABLE
    GO
    --启用索引语法
    ALTER INDEX indexName ON tableName REBUILD
    GO

    下面我们根据记录索引使用状态的动态视图sys.dm_db_index_usage_stats 生成不用索引的禁用脚本。这里的索引不使用指的是:

    • 用户查找(user_seeks)次数为0

    • 用户扫描(user_scans)次数为0

    • 用户书签查找(user_lookups)次数为0

    • 索引更新(user_updates)次数大于0

    即无用索引为该索引在用户搜索、扫描、查找中都没有使用到。注意视图中的user_updates指的是表的插入、删除、更新等使得索引维护更新的次数。如果user_seeks、user_scans、user_lookups均为0,而user_updates大于0,说明索引不但对性能提升无益,还需要消耗额外的性能来维护索引,这种索引最好禁用甚至删除(当然是在确定确实没有使用的情况下)。

    DECLARE @SQL NVARCHAR(MAX)
    SELECT @SQL = stuff((
    SELECT 'ALTER INDEX '+ QUOTENAME(ind.NAME,'[')+' ON '+QUOTENAME(sch.NAME,'[')+'.'+QUOTENAME(obj.NAME,'[') +' DISABLE '+CHAR(10)
    FROM sys.dm_db_index_usage_stats ius
        INNER JOIN sys.indexes ind on ius.index_id = ind.index_id and ius.OBJECT_ID=ind.object_id
        INNER JOIN sys.objects obj on ius.OBJECT_ID = obj.OBJECT_ID
        INNER JOIN sys.schemas sch on obj.schema_id = sch.schema_id
    WHERE  OBJECTPROPERTY(ius.OBJECT_ID, 'IsSystemTable') = 0
        AND LEFT(obj.NAME, 3) NOT IN ('sys','sql','que','fil')
        AND UPPER(ind.type_desc) = 'NONCLUSTERED'
        AND ind.is_primary_key = 0
        AND ind.is_unique_constraint = 0
        AND ius.user_seeks = 0
        AND ius.user_scans = 0
        AND ius.user_lookups = 0
        AND ius.user_updates > 0
        AND ius.database_id = db_id()
        AND sch.name <> 'sys'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'')
    SELECT @SQL FOR XML PATH('')

    脚本中我们排除了系统表的索引,以及聚集索引,主键及有唯一约束的索引。这里最终返回的是XML数据(点击XML,即可以查看生成的文本),而不是使用PRINT打印出来,是防止文本过长而截断,另外,细心的读者会发现,在构造索引禁用语句最后加了CHAR(10)换行字符,使得最终的语句每条一行。

    注意:动态视图记录信息在数据服务重启、数据库分离后信息将会被清除,所以要在数据库运行足够长时间后方可使用本脚本生成禁用索引脚本。

    如果数据库实例足够长时间没有重启或者分离过,有用的索引都会记录到索引使用状态视图中,而没有记录到视图的这部分索引既不会被查询用到,也不需要消耗性能进行索引维护(一般出在废弃的表中)。如果硬盘空间紧张,需要对数据库进行瘦身,可以对这部分索引进行动手处理,我们给出了如下的禁用脚本生成的脚本:

    DECLARE @dbid INT=DB_ID();
    DECLARE @sql VARCHAR(MAX);
    WITH cte AS(
           SELECT
                  [object_id],index_id
           FROM sys.indexes
           EXCEPT --排除在用的对象索引
           SELECT
                  [object_id],index_id
           FROM sys.dm_db_index_usage_stats
           WHERE database_id=@dbid)
    SELECT @sql=(
           SELECT
                  'ALTER INDEX '+QUOTENAME(i.name,'[')+' on '
                           +QUOTENAME(s.name,'[')+'.'+ QUOTENAME(o.name,'[')
                           +' DISABLE '
                           + CHAR(10)  --换行符
           FROM sys.indexes i
           INNER JOIN cte ON cte.[object_id]=i.[object_id] AND cte.index_id=i.index_id 
           INNER JOIN sys.objects o ON i.[object_id]=o.[object_id]
              INNER JOIN sys.schemas s ON o.SCHEMA_ID=s.SCHEMA_ID
           WHERE o.[type] IN ('U','V')--用户表或者视图
                         AND i.[type]>0                    --非堆
           ORDER BY s.name,o.name
    FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)');
    SELECT @sql FOR XML PATH('');

    最后,为了帮助理解什么样的操作会触发sys.dm_db_index_usage_stats记录索引使用状态,我们做了如下测试。先创建测试表:

    CREATE TABLE indexesUsageStatsTest(
           id INT IDENTITY(1,1),
           name VARCHAR(50),
           TYPE INT
    );

    表创建后,查看视图sys.dm_db_index_usage_stats

    SELECT * FROM sys.dm_db_index_usage_stats
    WHERE OBJECT_ID= OBJECT_ID('indexesUsageStatsTest',N'U')
           and database_id=DB_ID();

    此时视图无数据。

    然后分别在表上创建聚集索引、和非聚集索引,并查询视图,发现视图仍没有数据

    CREATE CLUSTERED INDEX cix_id ON indexesUsageStatsTest(id);
    CREATE NONCLUSTERED INDEX ix_type ON indexesUsageStatsTest(TYPE);

    向表中插入数据

    INSERT INTO indexesUsageStatsTest(name,TYPE)
    SELECT TOP 100 FirstName,BusinessEntityID
    FROM Person.Person;

     

    插入数据后,执行视图查询,发现视图中增加了两条记录,由于此时,该次插入数据造成所有索引都有一次更新,两个索引的user_updates值均变为1.。接下来执行如下脚本进行全表查询:

    SELECT * FROM indexesUsageStatsTest;

    执行查询时,打开“执行计划”,可以看到该查询使用了聚集索引扫描(clustered index scan)

    再执行视图查询,结果如下:

    从结果可以看出,聚集索引(index_id=1)的user_scans值变为1,并且last_user_scan的时间更新为查询执行时间。

    在上面的查询的基础上,加上条件id=10

    SELECT * FROM indexesUsageStatsTest
    WHERE id=10;

    此时使用的是聚集索引搜索,我们再执行视图的查询,并查看结果

    聚集索引(index_id=1)的user_seeks值变为1,并且last_user_seek的时间更新为查询执行时间。

    为获取书签查找,查询不包含在任何索引中的列name,并强制使用索引ix_type

    SELECT name FROM indexesUsageStatsTest with(index(ix_type))
    WHERE TYPE>38 and TYPE<7000;

    查询计划中使用ix_type进行索引查找,cix_id进行键查找。再执行视图查询,并查看结果:

    聚集索引(index_id=1)进行一次Key Lookup,其user_lookups变为1,并且非聚集索引(index_id=2)进行了移除索引查找,其对应列user_seeks 值更新为1.

    UPDATE indexesUsageStatsTest SET TYPE=50
    WHERE TYPE=38;

    更新使用非聚集索引查找,查看视图可以发现,非聚集索引的user_seeks增加1,并且更新数据引起索引维护,两个索引的user_updates均增加1.

    数据删除使用非聚集索引查找,查看视图可以发现,非聚集索引的user_seeks增加1,并且更新数据引起索引维护,两个索引的user_updates均增加1.

    DELETE indexesUsageStatsTest
    WHERE TYPE=50;

    最后我们重启一下实例,并查询视图

    发现视图没有记录对应表索引使用信息,因为实例启动时,视图记录清除,索引还没有使用。再次执行

    SELECT name FROM indexesUsageStatsTest with(index(ix_type))
    WHERE TYPE>38 and TYPE<7000;

    并查看记录

    发现增加使用的索引信息,并从0开始计数。这也是我们强调使用该脚本的要保证数据库运行足够长时间的原因。

    如果喜欢,可以搜索关注 MSSQLServer 公众号,将有更多精彩内容分享:

                                                                     

    展开全文
  • Java获得数据库基本信息,包括表的信息、表中列的信息索引信息、存储过程信息等。先创建与数据库的连接,通过Connection 对象的getMetaData()方法可以创建DatabaseMetaData对象,从该对象中获取数据库的基本信息。...

    转载地址:

       http://www.codefans.net/articles/1791.shtml

    Java获得数据库基本信息,包括表的信息、表中列的信息、索引信息、存储过程信息等。先创建与数据库的连接,通过Connection 对象的getMetaData()方法可以创建DatabaseMetaData对象,从该对象中获取数据库的基本信息。通过使用DatabaseMetaData 对象的getDatabaseProductName()方法可以获取连接的数据库DBMS 名称,使用getUserName()方法可以获取建立连接使用的用户名。获取表的信息可以使用该对象的getTables()方法,如:

    ResultSet rs=dbmd.getTables(catalog,schemapattern,tablenamepattern,type[]);

    其中catalog 为目录名,null 表示忽略目录;schemapattern 为大纲的匹配模式,null 表示忽略;tablenamepattern 表示表名称的匹配模式;type 为需要检索的表类型列表。使用getTables()获得一个结果集,此结果集中每一行都有一个表的信息,可以通过getInt()、getString()等方法来获得每一行中各列的信息,主要有TABLE_NAME、TABLE_TYPE 等,关于这些信息的描述在程序中作了注释,也可以参考JDBC 的API 手册。通过使用DatabaseMetaData 对象的getColumns()方法来获得数据库表的列的信息,如:

    ResultSet rs=dbmd.getColumns(catalog,schemapattern,tablenamepattern,columnsnamepattern);

    其中columnsnamepattern 表示列名匹配模式。通过getColumns()方法获得的结果集每一行都是一个列的描述,主要有TABLE_NAME、COLUMN_NAME 、TYPE_NAME 等。获取索引信息是使用了DatabaseMetaData 对象的getIndexInfo()方法,方法使用如下:

    ResultSet rs=dbmd.getIndexInfo(catalog,schemapattern,tablenamepattern,unique,approximate);

    其中,unique 为boolean 型,当为真值时,返回具有唯一值的索引,而为假时,不论索引值是否唯一都返回;approximate 为true 时,返回近似值,为false 时,返回精确值。通过getIndexInfo()方法获得的结果集中每一行都是一个索引的描述,主要有TABLE_NAME、INDEX_NAME、TYPE 等。使用DatabaseMetaData 对象的getProcedures()方法获取存储过程的信息,使用方法如下:

    ResultSet rs=dbmd.getProcedures(catalog,schemapattern,procedurenamepattern);

    其中,procedurenamepattern 是存储过程名称的匹配模式。该方法返回的结果集是存储过程的描述信息,主要有PROCEDURE_NAME、PROCEDURE_TYPE 等。使用DatabaseMetaData 对象的getProceduresColumns()方法获取存储过程的信息,使用方法如下:

    ResultSet rs=dbmd.getProceduresColumns(catalog,schemapattern,procedurenamepattern,cloumnnamepattern);

    其中,cloumnnamepattern 表示列名的匹配模式。具体的程序代码如下:

    1.编写useTable 类的基本框架,在该类中仅包括main()方法,在main()方法中先加载驱动程序,建立与数据库的连接,创建数据库表,获取表中信息的结果集,输出表的信息,获取表中列的信息,输出表中列的信息,获取索引信息,输出索引信息,获取存储过程信息,输出存储过程信息。

    2.对数据库编程,useTable 类的代码如下:

    001 class useTable
    002 {
    003 public static void main(String argv[])
    004 {
    005 try
    006 {
    007 String ul,namestr,typestr,cstr,strn,indexstr,prostr;
    008 short data;
    009 int index,pron;
    010 String[] type={"table"};
    011 ul="jdbc:odbc:useDSN";
    012 //加载驱动程序
    013 Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    014 //建立连接
    015 Connection con=DriverManager.getConnection(ul,"sa","");
    016 //使用DatabaseMetaData 对象获取数据源相关信息
    017 DatabaseMetaData dbmd=con.getMetaData();
    018 //获取表中信息的结果集
    019 ResultSet rs=dbmd.getTables(null,null,null,type);
    020 //输出表的信息
    021 while(rs.next())
    022 {
    023 //获取表名
    024 namestr=rs.getString("TABLE_NAME");
    025 //获取表的类型
    026 typestr=rs.getString("TABLE_TYPE");
    027 //输出结果集
    028 System.out.println("输出数据源中所有表的信息");
    029 strn="数据库表名:"+namestr+" "+"表的类型:"+typestr;
    030 System.out.println(strn);
    031 }
    032 //获取表中列的信息
    033 rs=dbmd.getColumns(null,null,"student","%");
    034 //输出表中列的信息
    035 while(rs.next())
    036 {
    037 //获取表名
    038 namestr=rs.getString("TABLE_NAME");
    039 //获取列名
    040 cstr=rs.getString("COLUMN_NAME");
    041 //获取列类型
    042 typestr=rs.getString("TYPE_NAME");
    043 //获取列的SQL 类型
    044 data=rs.getShort("DATA_TYPE");
    045 //输出列信息
    046 System.out.println("输出数据库中列的信息");
    047 strn="表名:"+namestr+" "+"列名:"+cstr+" "+"列类型:"+" "+"列SQL 类型:"+data;
    048 System.out.println(strn);
    049 }
    050 //获取索引信息
    051 rs=dbmd.getIndexInfo(null,null,"student",false,false);
    052 //输出索引信息
    053 while(rs.next())
    054 {
    055 //获取索引名
    056 namestr=rs.getString("INDEX_NAME");
    057 //获取索引类型
    058 index=rs.getInt("TYPE");
    059 switch(index)
    060 {
    061 case 0:
    062 {
    063 indexstr="没有索引";
    064 break;
    065 }
    066 case 1:
    067 {
    068 indexstr="聚集索引";
    069 break;
    070 }
    071 case 2:
    072 {
    073 indexstr="哈希表索引";
    074 break;
    075 }
    076 case 3:
    077 {
    078 indexstr="其它索引";
    079 break;
    080 }
    081 }
    082 strn="索引名:"+namestr+" "+"索引类型:"+index;
    083 System.out.println(strn);
    084 }
    085 //获取存储过程信息
    086 rs=dbmd.getProcedures(null,null,"%");
    087 //输出存储过程信息
    088 System.out.println("存储过程信息");
    089 while(rs.next())
    090 {
    091 //获取存储过程名称
    092 namestr=rs.getString("PROCEDURE_NAME");
    093 //获取存储过程类型
    094 pron=rs.getInt("PROCEDURE_TYPE");
    095 switch(pron)
    096 {
    097 case 0:
    098 {
    099 prostr="返回结果未知";
    100 break;
    101 }
    102 case 1:
    103 {
    104 prostr="没有返回结果";
    105 break;
    106 }
    107 case 2:
    108 {
    109 prostr="有返回结果";
    110 break;
    111 }
    112 }
    113 strn="存储过程名称:"+namestr+" "+"存储过程类型:"+prostr;
    114 System.out.println(strn);
    115 }
    116 //获取存储过程列信息
    117 rs=dbmd.getProcedureColumns(null,null,"%","%");
    118 //输出存储过程列信息
    119 System.out.println("存储过程列信息");
    120 while(rs.next())
    121 {
    122 //获取存储过程名称
    123 namestr=rs.getString("PROCEDURE_NAME");
    124 //获取存储过程类型
    125 prostr=rs.getString("COLUMN_NAME");
    126 strn="存储过程:"+namestr+" "+"存储过程列名:"+prostr;
    127 System.out.println(strn);
    128 }
    129 //关闭连接
    130 con.close();
    131 }
    132 catch(Exception e)
    133 {
    134 System.out.println(e.getMessage());
    135 e.printStackTrace();
    136 }
    137 }
    138 }

    因为程序使用了JDBC 类,所以需要引入import java.sql.*;包。


    展开全文
  • Elasticsearch7.3索引管理

    千次阅读 2019-09-08 11:59:36
    索引打开与关闭 索引删除 索引复制 前置条件 已有es7.3环境 本文基于es7.3,有些api在低版本的es中可能不适用 已有kibana环境 本文使用libana执行es操作请求,需要提前安装kibana,并会使用kibana中的开发工具 ...
  • 联合索引的树结构、最左匹配原则、如何选择合适的索引列顺序、索引下推图文讲解
  • 文章目录分布式NoSQL列存储数据库Hbase(七)知识点02:课程目标知识点03:Phoenix二级索引设计知识点04:二级索引:全局索引设计知识点05:二级索引:全局索引实现知识点06:二级索引:覆盖索引设计知识点07:二级...
  • 接着上一节的内容,这一...关闭索引只能显示索引元数据信息,不能够进行读写操作。 关闭索引的两种方式,第一种是比较low的操作,不建议使用........ 比如我们新建一个索引student2 第一种: 关闭索引: 我们用 PO...
  • 关闭索引只能显示索引元数据信息,不能够进行读写操作。 比如我们新建一个索引student2 我们用 POST http://192.168.1.110:9200/student2/_close/ 关闭索引 点击提交请求; 再概要首页里,可以刷新下 看到...
  • 普通索引与唯一索引

    万次阅读 多人点赞 2019-04-08 20:20:06
    所谓普通索引,就是在创建索引时,不附加任何限制条件(唯一、非空等限制)。该类型的索引可以创建在任何数据类型的字段上。 所谓唯一索引,就是在创建索引时,限制索引的值必须是唯一的。通过该类型的索引可以更快速...
  • MySQL数据库索引

    万次阅读 多人点赞 2018-09-23 09:31:41
    索引是什么 索引有哪些结构 数据库有哪些索引 唯一索引 聚簇索引与非聚簇索引 全文索引 使用索引一定能提高查询性能吗? 哪些情况下设置了索引但是无法使用 哪些情况下需要设置索引、哪些情况下不需要 什么...
  • 文章目录场景关闭refresh_interval 场景 es是近实时搜索, 从写入到读取是需要的时间的, 这个时间由refresh_interval来决定; 在初始化时需要关闭以提升性能 关闭refresh_interval 取值解释 -1 关闭自动刷新 1 1...
  • Java获得数据库基本信息,包括表的信息、表中列的信息索引信息、存储过程信息等。先创建与数据库的连接,通过Connection 对象的getMetaData()方法可以创建DatabaseMetaData对象,从该对象中获取数据库的基本信息。...
  • es索引命令

    千次阅读 2020-03-21 10:36:17
    es常用命令 文章目录es常用命令1.索引管理1.1创建索引1.2...索引配置4.1更新/新增索引配置4.2获取配置4.3索引分析5索引监控5.1索引统计5.3索引分片信息5.4索引恢复5.5索引分片存储信息6状态管理6.1索引刷新6.2冲洗...
  • 在码农的世界里,优美的应用体验,来源于程序员对细节的处理以及自我...如在我们的用户表中,每个用户都有一个身份证号,用户在注册或者是身份信息认证时,业务代码已校验了这个身份证号的唯一性。 当经常会有根据用户.
  • 人们往往会忽略的是,并行处理也可以加快索引处理的速度,从而提高数据库服务器的整体性能。 使用IBM DB2 for i,可以使用CPU进行索引处理。 特别是,当基础数据更改时,创建索引以及维护索引时,DB2 for i可以使用...
  • mysql索引-Btree索引-hash索引-使用场景

    千次阅读 2018-07-31 11:21:59
    B-tree索引特点: 按定义索引时列的顺序排序 myisam中:存储行物理位置,使用前缀压缩技术使得索引更小; innodb中:存储主键值,使用B+Tree索引; 加快数据的查询速度 适合范围查找 使用场景: 全值匹配 匹配...
  • MySQL索引详解

    2019-06-02 10:00:58
    一、InnoDB的索引模型 在InnoDB中,表都是根据主键顺序以索引的形式存放的,这种存储方式的表称为索引组织表。InnoDB使用了B+树索引模型,所以数据都是存储在B+树中的。每一个索引在InnoDB里面对应一棵B+树 有一个...
  • ES索引理解

    2020-12-01 22:37:59
    索引 创建索引 创建索引的时候可以通过修改number_of_shards和number_of_replicas参数的数量来修改分片和副本的数量。 在默认的情况下分片的数量是5个,副本的数量是1个。 例如,创建三个主分片,两个副本分片的...
  • MySQL使用全文索引(fulltext index) 及中文全文索引使用

    万次阅读 多人点赞 2019-07-24 10:52:56
    全文索引介绍 ----------------------------------------------------------------------------------------------------------------------------- 1.创建全文索引(FullText index) 旧版的MySQL的全文索引只能...
  • postgresql 建立索引

    2020-06-01 17:42:23
    有一些运行时参数可以关闭各种各样的查询规划。 4. 强制使用索引用法将会导致两种可能:一是系统选择是正确的,使用索引实际上并不合适,二是查询计划的开销计算并不能反映现实情况。这样你就应该对使用和不使用索引...
  • 阿里面试官:MySQL如何设计索引更高效?

    万次阅读 多人点赞 2021-01-11 08:22:46
    索引高度 MySQL的索引时B+tree结构,即使表里有上亿条数据,索引的高度都不会很高,通常维持在3-4层左右,我来计算下索引idx_name的高度,从上面知道索引信息:index_id = 4003, page_no = 5,它的偏移量offset...
  • 一般系统存储的文件信息,有可能会被删除和更新,那么相关的索引文件也要进行删除和更新操作,来保证全文检索结果的正确性、实时性。 一、删除索引 下面我们来编写删除索引的代码: public ...
  • Mysql—Innodb引擎 索引

    2019-09-04 23:12:16
    索引对于数据库而言,就像是目录对于书籍,可以快速定位想要查找的信息,提高效率。而Innodb引擎更是使用索引来组织数据,Innodb引擎目前支持三种形式的索引,B+树索引、全文索引、哈希索引。 B+树索引是常用并且...
  • 索引

    2018-08-09 08:17:24
    索引在存储引擎层实现,所以并没有统一的索引标准:不同的存储引擎的索引工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引,即时多个存储引擎支持同一类型的索引,其底层实现也可能不同。 如果索引包含...
  • es之索引配置

    千次阅读 2019-12-23 17:35:01
    在es中有很多配置参数,有些配置是可以在建好索引后重新配置的,比如索引的副本数量、索引的分词等 1.更新索引配置 ...参数: ...添加索引分析器之前必须先关闭索引,添加之后再打开索引 POST http://127.0....
  • 索引设计实现

    2020-06-17 18:32:24
    为了高效地查询数据库中的数据,我们常常会给表中的字段添加索引,如何添加索引才能使索引更高效 添加的索引是否越多越好 明明添加了索引却不生效 索引有哪些类型 评判一个索引设计的好坏标准 什么是索引索引的...
  • change buffer 唯一索引和普通索引

    千次阅读 2020-01-03 16:55:51
    唯一索引和普通索引 change buffer 学习检测 什么是change buffer? 唯一索引和普通索引查找数据流程及性能对比? 唯一索引和普通索引更新数据流程及性能对比? change buffer 适用场景? change buffer 和 ...
  • 打开/关闭索引6. 获取所有索引列表 索引(Index) 本篇文章主要学习索引的相关操作。 1. 添加索引 PUT example { "settings" : { "index" : { "number_of_shards" : 2, #设置分片的数量,在集群中...
  • MySQL索引之哈希索引和自适应哈希索引(Adaptive Hash Index) 官网:htt...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 244,111
精华内容 97,644
关键字:

信息索引怎么关闭