精华内容
下载资源
问答
  • ORACLE查看索引是否有效

    千次阅读 2020-07-31 17:19:41
    SELECT STATUS FROM USER_INDEXES WHERE INDEX_NAME='索引名'; VALID——索引有效
    SELECT STATUS FROM USER_INDEXES WHERE INDEX_NAME='索引名';

    VALID——索引有效

    展开全文
  • MySQL中EXPLAIN解释命令 查看索引是否生效

    万次阅读 多人点赞 2017-03-29 15:31:50
    explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了: 如: explain select surname,first_name form...

    explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。

    使用方法,在select语句前加上explain就可以了:

    如:

     
    1. explain select surname,first_name form a,b where a.id=b.id 

    EXPLAIN列的解释:

    table:显示这一行的数据是关于哪张表的

    type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL

    possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

    key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MYSQL会选择优化不足的索引。这种情况下,可以在SELECT语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MYSQL忽略索引

    key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好

    ref:显示索引的哪一列被使用了,如果可能的话,是一个常数

    rows:MYSQL认为必须检查的用来返回请求数据的行数

    Extra:关于MYSQL如何解析查询的额外信息。将在表4.3中讨论,但这里可以看到的坏的例子是Using temporary和Using filesort,意思MYSQL根本不能使用索引,结果是检索会很慢

    extra列返回的描述的意义

    Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了

    Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了

    Range checked for each Record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一

    Using filesort: 看到这个的时候,查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

    Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的,这发生在对表的全部的请求列都是同一个索引的部分的时候

    Using temporary 看到这个的时候,查询需要优化了。这里,MYSQL需要创建一个临时表来存储结果,这通常发生在对不同的列集进行ORDER BY上,而不是GROUP BY上

    Where used 使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,这就会发生,或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)

    system 表只有一行:system表。这是const连接类型的特殊情况

    const:表中的一个记录的最大值能够匹配这个查询(索引可以是主键或惟一索引)。因为只有一行,这个值实际就是常数,因为MYSQL先读这个值然后把它当做常数来对待

    eq_ref:在连接中,MYSQL在查询时,从前面的表中,对每一个记录的联合都从表中读取一个记录,它在查询使用了索引为主键或惟一键的全部时使用

    ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分(比如,利用最左边前缀)时发生。对于之前的表的每一个行联合,全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好

    range:这个连接类型使用索引返回一个范围中的行,比如使用>或<查找东西时发生的情况

    index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描(比ALL更好,因为索引一般小于表数据)

    ALL:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免


    转自 http://www.jb51.net/article/38357.htm

    在分析查询性能时,考虑EXPLAIN关键字同样很管用。EXPLAIN关键字一般放在SELECT查询语句的前面,用于描述MySQL如何执行查询操作、以及MySQL成功返回结果集需要执行的行数。explain 可以帮助我们分析 select 语句,让我们知道查询效率低下的原因,从而改进我们查询,让查询优化器能够更好的工作。

    一、MySQL 查询优化器是如何工作的
    MySQL 查询优化器有几个目标,但是其中最主要的目标是尽可能地使用索引,并且使用最严格的索引来消除尽可能多的数据行。最终目标是提交 SELECT 语句查找数据行,而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快,那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试,查询就可以执行地更快。
    EXPLAIN 的每个输出行提供一个表的相关信息,并且每个行包括下面的列: 

    说明
    idMySQL Query Optimizer 选定的执行计划中查询的序列号。表示查询中执行 select 子句或操作表的顺序,id 值越大优先级越高,越先被执行。id 相同,执行顺序由上至下。


    select_type 查询类型说明
    SIMPLE简单的 select 查询,不使用 union 及子查询
    PRIMARY最外层的 select 查询
    UNIONUNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
    DEPENDENT UNIONUNION 中的第二个或随后的 select 查询,依 赖于外部查询的结果集
    SUBQUERY子查询中的第一个 select 查询,不依赖于外 部查询的结果集
    DEPENDENT SUBQUERY子查询中的第一个 select 查询,依赖于外部 查询的结果集
    DERIVED用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。
    UNCACHEABLE SUBQUERY结果集不能被缓存的子查询,必须重新为外 层查询的每一行进行评估。
    UNCACHEABLE UNIONUNION 中的第二个或随后的 select 查询,属 于不可缓存的子查询


    说明
    table输出行所引用的表


    type 重要的项,显示连接使用的类型,按最 优到最差的类型排序说明
    system表仅有一行(=系统表)。这是 const 连接类型的一个特例。
    constconst 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。
    eq_refconst 用于用常数值比较 PRIMARY KEY 时。当 查询的表仅有一行时,使用 System。
    ref连接不能基于关键字选择单个行,可能查找 到多个符合条件的行。 叫做 ref 是因为索引要 跟某个参考值相比较。这个参考值或者是一 个常数,或者是来自一个表里的多表查询的 结果值。
    ref_or_null如同 ref, 但是 MySQL 必须在初次查找的结果 里找出 null 条目,然后进行二次查找。
    index_merge说明索引合并优化被使用了。
    unique_subquery在某些 IN 查询中使用此种类型,而不是常规的 ref:value IN (SELECT primary_key FROM single_table WHERE some_expr)
    index_subquery在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与 unique_subquery 类似,但是查询的是非唯一 性索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
    range只检索给定范围的行,使用一个索引来选择 行。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可 以使用 range。
    index全表扫描,只是扫描表的时候按照索引次序 进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
    all最坏的情况,从头到尾全表扫描。



    说明
    possible_keys指出 MySQL 能在该表中使用哪些索引有助于 查询。如果为空,说明没有可用的索引。


    说明
    keyMySQL 实际从 possible_key 选择使用的索引。 如果为 NULL,则没有使用索引。很少的情况 下,MYSQL 会选择优化不足的索引。这种情 况下,可以在 SELECT 语句中使用 USE INDEX (indexname)来强制使用一个索引或者用 IGNORE INDEX(indexname)来强制 MYSQL 忽略索引


    说明
    key_len使用的索引的长度。在不损失精确性的情况 下,长度越短越好。


    说明
    ref显示索引的哪一列被使用了


    说明
    rowsMYSQL 认为必须检查的用来返回请求数据的行数


    说明
    rowsMYSQL 认为必须检查的用来返回请求数据的行数


    extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

    extra 项说明
    Using filesort表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”
    Using temporary表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

    下面来举一个例子来说明下 explain 的用法。
    先来一张表:

    代码如下:

    CREATE TABLE IF NOT EXISTS `article` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `author_id` int(10) unsigned NOT NULL,
    `category_id` int(10) unsigned NOT NULL,
    `views` int(10) unsigned NOT NULL,
    `comments` int(10) unsigned NOT NULL,
    `title` varbinary(255) NOT NULL,
    `content` text NOT NULL,
    PRIMARY KEY (`id`)
    );

    再插几条数据:
     代码如下:

    INSERT INTO `article`
    (`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
    (1, 1, 1, 1, '1', '1'),
    (2, 2, 2, 2, '2', '2'),
    (1, 1, 3, 3, '3', '3');

    需求:
    查询 category_id 为 1 且 comments 大于 1 的情况下,views 最多的 article_id。
    先查查试试看:
    代码如下:

    EXPLAIN
    SELECT author_id
    FROM `article`
    WHERE category_id = 1 AND comments > 1
    ORDER BY views DESC
    LIMIT 1\G

    看看部分输出结果:
    代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: article
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 3
            Extra: Using where; Using filesort
    1 row in set (0.00 sec)

    很显然,type 是 ALL,即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。优化是必须的。

    嗯,那么最简单的解决方案就是加索引了。好,我们来试一试。查询的条件里即 where 之后共使用了 category_id,comments,views 三个字段。那么来一个联合索引是最简单的了。

    如下:

    ALTER TABLE `article` ADD INDEX x ( `category_id` , `comments`, `views` );

    结果有了一定好转,但仍然很糟糕:
     代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: article
             type: range
    possible_keys: x
              key: x
          key_len: 8
              ref: NULL
             rows: 1
            Extra: Using where; Using filesort
    1 row in set (0.00 sec)

    type 变成了 range,这是可以忍受的。但是 extra 里使用 Using filesort 仍是无法接受的。但是我们已经建立了索引,为啥没用呢?这是因为按照 BTree 索引的工作原理,先排序 category_id,如果遇到相同的 category_id 则再排序 comments,如果遇到相同的 comments 则再排序 views。当 comments 字段在联合索引里处于中间位置时,因comments > 1 条件是一个范围值(所谓 range),MySQL 无法利用索引再对后面的 views 部分进行检索,即 range 类型查询字段后面的索引无效。
    那么我们需要抛弃 comments,删除旧索引:
    代码如下:

     DROP INDEX x ON article;

    然后建立新索引:
    代码如下:

    ALTER TABLE `article` ADD INDEX y ( `category_id` , `views` ) ;

    接着再运行查询:
    代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: article
             type: ref
    possible_keys: y
              key: y
          key_len: 4
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)

    可以看到,type 变为了 ref,Extra 中的 Using filesort 也消失了,结果非常理想。
    再来看一个多表查询的例子。
    首先定义 3个表 class 和 room。
     代码如下:

    CREATE TABLE IF NOT EXISTS `class` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `card` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`)
    );
    CREATE TABLE IF NOT EXISTS `book` (
    `bookid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `card` int(10) unsigned NOT NULL,
    PRIMARY KEY (`bookid`)
    );
    CREATE TABLE IF NOT EXISTS `phone` (
    `phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `card` int(10) unsigned NOT NULL,
    PRIMARY KEY (`phoneid`)
    ) engine = innodb;

    然后再分别插入大量数据。插入数据的php脚本:
     代码如下:

    <?php
    $link = mysql_connect("localhost","root","870516");
    mysql_select_db("test",$link);
    for($i=0;$i<10000;$i++)
    {
        $j   = rand(1,20);
        $sql = " insert into class(card) values({$j})";
        mysql_query($sql);
    }
    for($i=0;$i<10000;$i++)
    {
        $j   = rand(1,20);
        $sql = " insert into book(card) values({$j})";
        mysql_query($sql);
    }
    for($i=0;$i<10000;$i++)
    {
        $j   = rand(1,20);
        $sql = " insert into phone(card) values({$j})";
        mysql_query($sql);
    }
    mysql_query("COMMIT");
    ?>

    然后来看一个左连接查询:
     代码如下:

    explain select * from class left join book on class.card = book.card\G

    分析结果是:
    代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    2 rows in set (0.00 sec)

    显然第二个 ALL 是需要我们进行优化的。
    建立个索引试试看:
    代码如下:

    ALTER TABLE `book` ADD INDEX y ( `card`);

     代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ref
    possible_keys: y
              key: y
          key_len: 4
              ref: test.class.card
             rows: 1000
            Extra:
    2 rows in set (0.00 sec)

    可以看到第二行的 type 变为了 ref,rows 也变成了 1741*18,优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。
    删除旧索引:
    代码如下:

    DROP INDEX y ON book;

    建立新索引。
    复制代码 代码如下:

    ALTER TABLE `class` ADD INDEX x ( `card`);

    结果
    复制代码 代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    2 rows in set (0.00 sec)

    基本无变化。
           然后来看一个右连接查询:
    复制代码 代码如下:

    explain select * from class right join book on class.card = book.card;

    分析结果是:
    复制代码 代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ref
    possible_keys: x
              key: x
          key_len: 4
              ref: test.book.card
             rows: 1000
            Extra:
    2 rows in set (0.00 sec)

    优化较明显。这是因为 RIGHT JOIN 条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引。
    删除旧索引:
    复制代码 代码如下:

    DROP INDEX x ON class;

    建立新索引。
    复制代码 代码如下:

    ALTER TABLE `book` ADD INDEX y ( `card`);

    结果
    复制代码 代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    2 rows in set (0.00 sec)

    基本无变化。

    最后来看看 inner join 的情况:

    复制代码 代码如下:

    explain select * from class inner join book on class.card = book.card;

    结果:
    复制代码 代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ref
    possible_keys: x
              key: x
          key_len: 4
              ref: test.book.card
             rows: 1000
            Extra:
    2 rows in set (0.00 sec)

    删除旧索引:
    代码如下:

    DROP INDEX y ON book;

    结果
     代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    2 rows in set (0.00 sec)

    建立新索引。
    代码如下:

    ALTER TABLE `class` ADD INDEX x ( `card`);

    结果
    代码如下:

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    2 rows in set (0.00 sec)

    综上所述,inner join 和 left join 差不多,都需要优化右表。而 right join 需要优化左表。

    我们再来看看三表查询的例子

    添加一个新索引:



    ALTER TABLE `phone` ADD INDEX z ( `card`);
    ALTER TABLE `book` ADD INDEX y ( `card`);



    explain select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: class
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20000
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: book
             type: ref
    possible_keys: y
              key: y
          key_len: 4
              ref: test.class.card
             rows: 1000
            Extra:
    *************************** 3. row ***************************
               id: 1
      select_type: SIMPLE
            table: phone
             type: ref
    possible_keys: z
              key: z
          key_len: 4
              ref: test.book.card
             rows: 260
            Extra: Using index
    3 rows in set (0.00 sec)

    后 2 行的 type 都是 ref 且总 rows 优化很好,效果不错。

    MySql 中的 explain 语法可以帮助我们改写查询,优化表的结构和索引的设置,从而最大地提高查询效率。当然,在大规模数据量时,索引的建立和维护的代价也是很高的,往往需要较长的时间和较大的空间,如果在不同的列组合上建立索引,空间的开销会更大。 因此索引最好设置在需要经常查询的字段中

    展开全文
  • 查看索引是否有效 1.未使用索引前 jinbo=# explain analyze select * from jinbo.student where score < 80 and score < 90 and sex = 1; QUERY PLAN ...

    查看索引是否有效

    1.未使用索引前

    jinbo=# explain analyze select * from jinbo.student where score < 80 and score < 90 and sex = 1;
                                                 QUERY PLAN                                             
    ----------------------------------------------------------------------------------------------------
     Seq Scan on student  (cost=0.00..220.45 rows=4 width=23) (actual time=0.030..2.195 rows=1 loops=1)
       Filter: ((score < 80) AND (score < 90) AND (sex = 1))
       Rows Removed by Filter: 11453
     Planning time: 0.119 ms
     Execution time: 2.217 ms
    (5 rows)
    

    使用索引后

    jinbo=# create index on jinbo.student(score, sex);
    CREATE INDEX
    jinbo=# explain analyze select * from jinbo.student where score < 80 and score < 90 and sex = 1;
                                                              QUERY PLAN                                                          
    ------------------------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on student  (cost=4.40..10.54 rows=2 width=23) (actual time=0.063..0.064 rows=1 loops=1)
       Recheck Cond: ((score < 80) AND (score < 90) AND (sex = 1))
       Heap Blocks: exact=1
       ->  Bitmap Index Scan on student_score_sex_idx  (cost=0.00..4.40 rows=2 width=0) (actual time=0.058..0.058 rows=1 loops=1)
             Index Cond: ((score < 80) AND (score < 90) AND (sex = 1))
     Planning time: 0.398 ms
     Execution time: 0.093 ms
    (7 rows)
    
    

    查看索引使用情况

    查看索引使用的统计情况的表:

    pg_stat_user_indexes

    jinbo=# \d+ pg_stat_user_indexes;
                         View "pg_catalog.pg_stat_user_indexes"
        Column     |  Type  | Collation | Nullable | Default | Storage | Description 
    ---------------+--------+-----------+----------+---------+---------+-------------
     relid         | oid    |           |          |         | plain   | 
     indexrelid    | oid    |           |          |         | plain   | 
     schemaname    | name   |           |          |         | plain   | 
     relname       | name   |           |          |         | plain   | 
     indexrelname  | name   |           |          |         | plain   | 
     idx_scan      | bigint |           |          |         | plain   | 
     idx_tup_read  | bigint |           |          |         | plain   | 
     idx_tup_fetch | bigint |           |          |         | plain   | 
    View definition:
     SELECT pg_stat_all_indexes.relid,
        pg_stat_all_indexes.indexrelid,
        pg_stat_all_indexes.schemaname,
        pg_stat_all_indexes.relname,
        pg_stat_all_indexes.indexrelname,
        pg_stat_all_indexes.idx_scan,
        pg_stat_all_indexes.idx_tup_read,
        pg_stat_all_indexes.idx_tup_fetch
       FROM pg_stat_all_indexes
      WHERE (pg_stat_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND pg_stat_all_indexes.schemaname !~ '^pg_toast'::text;
    
    

    pg_stat_all_indexes

    jinbo=# \d+ pg_stat_all_indexes 
                          View "pg_catalog.pg_stat_all_indexes"
        Column     |  Type  | Collation | Nullable | Default | Storage | Description 
    ---------------+--------+-----------+----------+---------+---------+-------------
     relid         | oid    |           |          |         | plain   | 
     indexrelid    | oid    |           |          |         | plain   | 
     schemaname    | name   |           |          |         | plain   | 
     relname       | name   |           |          |         | plain   | 
     indexrelname  | name   |           |          |         | plain   | 
     idx_scan      | bigint |           |          |         | plain   | 
     idx_tup_read  | bigint |           |          |         | plain   | 
     idx_tup_fetch | bigint |           |          |         | plain   | 
    View definition:
     SELECT c.oid AS relid,
        i.oid AS indexrelid,
        n.nspname AS schemaname,
        c.relname,
        i.relname AS indexrelname,
        pg_stat_get_numscans(i.oid) AS idx_scan,
        pg_stat_get_tuples_returned(i.oid) AS idx_tup_read,
        pg_stat_get_tuples_fetched(i.oid) AS idx_tup_fetch
       FROM pg_class c
         JOIN pg_index x ON c.oid = x.indrelid
         JOIN pg_class i ON i.oid = x.indexrelid
         LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE c.relkind = ANY (ARRAY['r'::"char", 't'::"char", 'm'::"char"]);
    

    查看使用记录

        jinbo=# select * from pg_stat_all_indexes where schemaname = 'jinbo' and relname = 'student';
         relid  | indexrelid | schemaname | relname |     indexrelname      | idx_scan | idx_tup_read | idx_tup_fetch 
        --------+------------+------------+---------+-----------------------+----------+--------------+---------------
         111451 |     228633 | jinbo      | student | student_score_sex_idx |        3 |            3 |             2
         
    //---------------------------------------------------分割线---------------------------------------------
    
    jinbo=# select * from pg_stat_user_indexes where schemaname = 'jinbo' and relname = 'student';
     relid  | indexrelid | schemaname | relname |     indexrelname      | idx_scan | idx_tup_read | idx_tup_fetch 
    --------+------------+------------+---------+-----------------------+----------+--------------+---------------
     111451 |     228633 | jinbo      | student | student_score_sex_idx |        3 |            3 |             2
    (1 row)
    
    展开全文
  • 数据库索引是否生效

    千次阅读 2018-09-06 11:18:26
    一、如何判断数据库索引是否生效 首先在接着探讨之前,我们先说一下,如何判断数据库的索引是否生效!相信大家应该猜到了,就是explain!explain显示了MySQL如何使用索引来处理select语句以及连接表。他可以帮助...

    一、如何判断数据库索引是否生效

    首先在接着探讨之前,我们先说一下,如何判断数据库的索引是否生效!相信大家应该猜到了,就是explain!explain显示了MySQL如何使用索引来处理select语句以及连接表。他可以帮助选择更好的索引和写出更优化的查询语句。

    例如我们有一张表user,为name列创建索引name_index,如下所示:  

    使用explain分析语句如下:

    可以看到,使用explain显示了很多列,各个关键字的含义如下:

    • table:顾名思义,显示这一行的数据是关于哪张表的;

    • type:这是重要的列,显示连接使用了何种类型。从最好到最差的连接类型为:const、eq_reg、ref、range、indexhe和ALL;

    • possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。可以为相关的域从where语句中选择一个合适的语句;

    • key: 实际使用的索引。如果为NULL,则没有使用索引。很少的情况下,MySQL会选择优化不足的索引。这种情况下,可以在Select语句中使用USE INDEX(indexname)来强制使用一个索引或者用IGNORE INDEX(indexname)来强制MySQL忽略索引;

    • key_len:使用的索引的长度。在不损失精确性的情况下,长度越短越好;

    • ref:显示索引的哪一列被使用了,如果可能的话,是一个常数;

    • rows:MySQL认为必须检查的用来返回请求数据的行数;

    • Extra:关于MySQL如何解析查询的额外信息。

    具体的各个列所能表示的值以及含义可以参考MySQL官方文档介绍,地址:https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

    二、哪些场景会造成索引生效

    1、应尽量避免在 where 子句中使用 != 或 <> 操作符,否则引擎将放弃使用索引而进行全表扫描;

    2、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,即使其中有条件带索引也不会使用,这也是为什么尽量少用 or 的原因;

    3、对于多列索引,不是使用的第一部分,则不会使用索引;

    4、如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不会使用索引;

    5、like的模糊查询以 % 开头,索引失效;

    6、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描;

    7、应尽量避免在 where 子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描;

    8、不要在 where 子句中的 “=” 左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引;

    9、如果MySQL估计使用全表扫描要比使用索引快,则不使用索引;

    10、不适合键值较少的列(重复数据较多的列)

    假如索引列TYPE有5个键值,如果有1万条数据,那么 WHERE TYPE = 1将访问表中的2000个数据块。再加上访问索引块,一共要访问大于200个的数据块。如果全表扫描,假设10条数据一个数据块,那么只需访问1000个数据块,既然全表扫描访问的数据块少一些,肯定就不会利用索引了。

     

    原文:https://mp.weixin.qq.com/s?__biz=MzI1NDQ3MjQxNA==&mid=2247485517&idx=1&sn=3d8c4d325366e2ddc6cb58fbf7329eab&chksm=e9c5f1fcdeb278eae6a467e4a944ae90c53c15a431bfb855d707c807461d3db8ddd12b5a070a&scene=21#wechat_redirect

    展开全文
  • oracle 查看索引是否失效

    万次阅读 2018-09-22 14:50:13
    数据库使用的oracle数据库,可视化管理工具使用的PLSQL 查看表中的索引 选中表,右键, view –&gt;indexes就可以查看到表中的索引 ...select status from user_...​​​​​​​valid 表示 索引 有效!...
  • MySQL创建索引/判断索引是否生效

    千次阅读 2019-08-06 12:36:51
    查询是否索引:EXPLAIN + 查询语句 EXPLAIN SELECT * FROM jingjia_info; EXPLAIN列的解释: table:显示这一行的数据是关于哪张表的。 type:这是重要的列,显示连接使用了何种类型。从最好到最差的...
  • 使用mssql代码查询某个数据库中所有索引的情况,包括碎片信息
  • 第一步: explain plan for select * from T_USER where OPEN_ID='12345'; 第二步: select * from table(dbms_xplan.display);
  • 怎么知道索引是否生效

    千次阅读 2017-09-19 14:36:17
    explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了: 如: explain select surname,first_name from...
  • oracle查询索引是否生效

    千次阅读 2015-04-15 12:15:37
    如果不是失效的索引,那么都是有效的   REM list of the unusable index,index partition,index subpartition in Database    Select owner, index_name, status  From dba_indexes  where...
  • MYSQL 判断索引是否生效

    千次阅读 2018-08-10 09:44:00
    explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了: 如:   explain select surname,first_name...
  • 在哪些列上添加索引比较好: 1.比较频繁的作为查询的字段 2.唯一性太差的字段不适合加索引,要找唯一性比较好的 3.更新太频繁的字段不适合做索引 4.不会出现在where中的 不应该建立索引 查询某表的所有索引: show ...
  • 整篇以表sys_user为例,表名sys_user,字段如下 1、索引的创建 第一种:create index 索引名 on 表名 (要加索引的字段) ...检测索引是否生效: EXPLAIN select * from sys_user where user_name = '11' ...
  • Oracle使用PLsql时判断索引是否生效

    千次阅读 2017-08-22 18:12:04
    执行完select语句按F5即可查看索引是否生效 索引扫描分五种: INDEX UNIQUE SCAN(索引唯一扫描) INDEX RANGE SCAN(索引范围扫描) INDEX FULL SCAN(索引全扫描) INDEX FAST FULL SCAN(索引快速扫描...
  • 如何知道mysql中sql语句索引是否生效

    千次阅读 2014-05-26 16:10:38
    在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。 例如: mysql> explain select * from t4 where id=3952602 oraccountid=31754306 ; +----+-------------+-------+...
  • 首先新建一个查询 1:选中要执行的语句 ...3:查看输出界面,INDEX则走了索引查询 下图走了索引查询,运行该语句,耗时0.9s 下图语句没有走索引TABLE ACCESS (FULL)走的是全表扫描,查询速度非常慢 ...
  • 1、如何判断索引是否生效? 答:在查询语句前加上explain。 explain函数作用:显示了MYSQL如何使用索引来处理select语句以及连接表。 explain select id , name table where name like 'abc%' 2、索引失效的情况...
  • 查看Sqlserver数据库中索引使用情况

    千次阅读 2020-05-25 12:14:46
    索引能加快数据库的数据查询,也会占用太多的资源开销,所以索引并不是越多越好,当加了索引后应该定期查看索引的使用情况,把没用的或者用的少了进行删除或修改,那如何查询索引的使用情况呢?如下: use vims_...
  • 在表中已经添加索引:device_serial_number -> idx_serial_number, access_time -> idx_access_time % 在左边: sql: EXPLAIN SELECT tar.id FROM t_access_record tar where 1=1 and tar.device_...
  • 查看某个表的索引使用情况 select relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch from pg_stat_user_indexes where relname = table_name order by idx_scan asc, idx_tup_read asc, idx_tup_...
  • 如何查看mongodb的索引命中率

    千次阅读 2018-11-27 17:30:50
    如何查看mongodb的索引命中率 一、背景 现在mongodb使用率很高,经常会遇到查询慢时,就会创建索引,而有时候索引命中率又不高,下面来介绍下测试环境下如何查看索引命中率 二、方案 1、首先查看查询collection的...
  • DB2查看索引的使用情况

    千次阅读 2018-09-21 16:47:35
    db2pd可以查看应用对数据库中的表和索引的访问情况 db2pd -db dbname -tcbstats all -file db2pd_tab_all.txt 有时候,你只想查看对某一个表和其上的索引的访问情...
  • SqlServer中查看索引的使用情况

    万次阅读 2019-02-07 16:52:00
    —在优化查询SQL语句,查看索引使用情况SQL语句: select db_name(database_id) as N'数据库名称',  object_name(a.object_id) as N'表名',  b.name N'索引名称',  user_seeks N'用户索引查找次数',  user_...
  • select status,T.* from user_indexes T where table_name='TABLE1' ...
  • 上图中where后是否用到了索引,如果用了,是否充分用到索引,用到了哪些列?为什么? 命中了索引a 命中了索引a和b 索引充分利用,包含三列的联合索引都命中了 因为最左前缀原则,三个都没有命中索引 命中一个a ...
  • GreenPlum–索引详解

    2021-01-19 21:52:27
    1)OLTP系统中,比如Oracle会大量使用索引,因为在事物不断变化的系统中,使用索引可以有效提高查询和修改的性能。 2)OLAP系统中,累积了大量的历史数据,而且修改和删除的操作会比OLTP系统少很多。 3)分布式...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 505,756
精华内容 202,302
关键字:

怎么查看索引是否生效