精华内容
下载资源
问答
  • 怎么知道索引是否生效

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

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

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

    如:

    1. explain select surname,first_name from 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:这个连接类型对于前面的每一个记录联合进行完全扫描,这一般比较糟糕,应该尽量避免


    展开全文
  • 数据库索引是否生效

    千次阅读 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

    展开全文
  • 整篇以表sys_user为例,表名sys_user,字段如下 1、索引的创建 第一种:create index 索引名 on 表名 (要加索引的字段) ...检测索引是否生效: EXPLAIN select * from sys_user where user_name = '11' ...

    整篇以表sys_user为例,表名sys_user,字段如下
    在这里插入图片描述

    1、索引的创建

    第一种create index 索引名 on 表名 (要加索引的字段)
    具体:create index user_name_index on sys_user (user_name)
    在这里插入图片描述
    检测索引是否生效:

    EXPLAIN select * from sys_user where user_name = '11'
    

    在这里插入图片描述
    未加索引前是这样的:
    在这里插入图片描述
    几个字段的意思:
    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如何解析查询的额外信息。

    第二种alter table 表名 add index 索引名 (要加索引的字段)
    具体:alter table sys_user add index user_name_index (user_name)
    第二种方式,检验索引是否生效同第一种

    2、索引的使用
    有些时候会涉及到联合索引的创建,创建方式同单列索引
    以第一种创建方式为例,给user_password和user_email创建联合索引:

    create index sys_user_password_email on sys_user (user_password,user_email)
    

    验证索引是否生效:
    在这里插入图片描述
    联合索引涉及到最左匹配原则,左边索引失效的时候,右边索引也失效

    展开全文
  • 查看索引是否有效 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)
    
    展开全文
  • 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中sql语句索引是否生效

    千次阅读 2014-05-26 16:10:38
    在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。 例如: mysql> explain select * from t4 where id=3952602 oraccountid=31754306 ; +----+-------------+-------+...
  • MySQL创建索引/判断索引是否生效

    千次阅读 2019-08-06 12:36:51
    查询是否索引:EXPLAIN + 查询语句 EXPLAIN SELECT * FROM jingjia_info; EXPLAIN列的解释: table:显示这一行的数据是关于哪张表的。 type:这是重要的列,显示连接使用了何种类型。从最好到最差的...
  • 只有%在右面的时候like的字段索引生效
  • MYSQL 判断索引是否生效

    千次阅读 2018-08-10 09:44:00
    explain显示了MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法,在select语句前加上explain就可以了: 如:   explain select surname,first_name...
  • SELECT STATUS FROM USER_INDEXES WHERE INDEX_NAME='索引名'; VALID——索引有效
  • mysql---联合索引是否生效

    千次阅读 2017-12-17 18:01:19
    create table test( id1 int , id2 int, id3 int, id4 int, ...用到索引 explain select * from test where id1 ; 用到索引 explain select * from test where id1 < 10 and id2 > 1; 用到索引 expla
  • MySQL中EXPLAIN解释命令 查看索引是否生效

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

    千次阅读 2017-08-22 18:12:04
    执行完select语句按F5即可查看索引是否生效 索引扫描分五种: INDEX UNIQUE SCAN(索引唯一扫描) INDEX RANGE SCAN(索引范围扫描) INDEX FULL SCAN(索引全扫描) INDEX FAST FULL SCAN(索引快速扫描...
  • * 假如表A有索引(a, b, c),然后现在查询SQL是 ``` select * from A, B where b = xxx and a = xxx and d = xxx and c = xxx ``` ...请问这种情况下生效索引依然是(a, b, c)三列么?
  • 在哪些列上添加索引比较好: 1.比较频繁的作为查询的字段 2.唯一性太差的字段不适合加索引,要找唯一性比较好的 3.更新太频繁的字段不适合做索引 4.不会出现在where中的 不应该建立索引 查询某表的所有索引: show ...
  • Mysql时间字段加索引是否生效的问题

    千次阅读 2020-04-17 19:07:14
    在查询数据条数约占总条数五分之一以下时能够使用到索引,但超过五分之一时,则使用全表扫描了。 参考1:https://www.cnblogs.com/tiancai/p/9518730.html 参考2:...
  • mysql测试索引是否有效,有用

    千次阅读 2018-09-12 14:30:27
    mysql最有效测试索引是否失效的方法 解决方法: 百度上有很多关于索引失效的讨论,但他们说的一定是对的吗?还不如自己测试,一目了然。 使用关键字explain即可测试:...
  • 上图中where后是否用到了索引,如果用了,是否充分用到索引,用到了哪些列?为什么? 命中了索引a 命中了索引a和b 索引充分利用,包含三列的联合索引都命中了 因为最左前缀原则,三个都没有命中索引 命中一个a ...
  • 索引是否失效 场景有很多:or like %string , %string% &lt;&gt; , !=&gt;not in , not exist经过自测,在查询数据量占总数据量的30%+ 索引就会失效我们一起来看一下:如图给test_user表 test...
  • mysql 联合索引 in查询是否生效

    千次阅读 2018-08-10 19:36:39
    联合索引生效需满足最左前缀原则,即如果联合索引列为a,b,c三列,a,b,c 、a,b 、a生效,b,c、a,c、b、c等不生效(此处的顺序不是where条件后面的先后顺序,而是where条件中是否存在这些列,如果where中只存在a,c列,...
  • 几乎所有的小伙伴都可以随口说几句关于创建索引的优缺点,也知道什么时候创建索引能够提高我们的查询性能,什么时候索引会更新,但是你有没有注意到,即使你设置了索引,有些时候索引他是不会生效的!这不仅考察了...
  • mysql 联合索引生效的条件、索引失效的条件

    万次阅读 多人点赞 2019-02-23 10:11:30
    1.联合索引失效的条件 联合索引又叫复合索引。两个或更多个列上的索引被称作复合索引。 对于复合索引:Mysql从左到右的使用索引中的字段,一个查询...当最左侧字段是常量引用时,索引就十分有效。 利用索...
  • 这一节想分享一下在MySQL中给字段加了索引,但是查询的时候却不生效索引的情况,让更多的开发者可以少踩坑,接下来直接进入正文~~~ 为什么索引生效 在上一篇MySQL(二)如何设计索引我们有提到过,MySQL使用的...
  • mysql索引生效情况

    千次阅读 2019-06-13 20:53:24
    索引生效情况 使用了like,且以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作 加了联合索引,如果where条件中是OR关系,索引不起作用 加了联合索引,但是使用时没有符合最左原则(联合索引采用最左...
  • 备注:添加索引之后验证索引有效性: 执行1:EXPLAIN PLAN FOR select accouts, accrate, completeratio, def1, def10, def11, def12, def13, def14, def15, def16, def17, def18, def19, def2, def20, def3, def4,...
  • MySQL索引生效

    2018-09-23 09:56:09
    如何判断数据库索引是否生效 explain语句显示MySQL如何使用索引来处理select语句以及连接表,他可以帮助选择更好的索引和写出更优化的查询语句 可以看到,使用explain显示了很多列,各个关键字的含义如下: ...
  • Mysql联合索引生效判断

    千次阅读 2020-07-09 15:43:10
    当最左侧字段是常量引用时,索引就十分有效。 以下是一些例子: (1) select * from myTest where a=3 and b=5 and c=4; ---- abc顺序 abc三个索引都在where条件里面用到了,而且都发挥了作用 (2) selec

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 492,028
精华内容 196,811
关键字:

如何知道索引是否生效