精华内容
下载资源
问答
  • 收集统计信息导致索引被监控

    千次阅读 2013-03-26 17:33:34
    对于索引的调整,我们可以通过Oracle提供的索引监控特性来跟踪索引是否被使用。尽管该特性并未提供索引使用的频度,但仍不失为我们参考的... 1、基于Oracle 10g 收集统计信息索引被监控情形scott@CNMMBO> select * fro

          对于索引的调整,我们可以通过Oracle提供的索引监控特性来跟踪索引是否被使用。尽管该特性并未提供索引使用的频度,但仍不失为我们参考的方式之一。然而,最近在Oracle 10.2.0.3中发现收集统计信息时导致索引也被监控,而不是用于sql查询引发的索引监控。如此这般,索引监控岂不是鸡肋?

     

    1、基于Oracle 10g 收集统计信息索引被监控情形

    scott@CNMMBO> select * from v$version where rownum<2;
    
    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
    
    --创建临时表t
    scott@CNMMBO> create table t(id number constraint t_pk primary key);
    
    Table created.
    
    --启用索引监控
    scott@CNMMBO> alter index t_pk monitoring usage;
    
    Index altered.
    
    --查看对象的使用情况
    scott@CNMMBO> select * from v$object_usage where index_name='T_PK';
    
    INDEX_NAME                     Table Name        MON USE START_MONITORING    END_MONITORING
    ------------------------------ ----------------- --- --- ------------------- -------------------
    T_PK                           T                 YES NO  03/22/2013 20:53:23
    
    --收集表t上的统计信息
    scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    --下面的查询中提示索引没有被使用
    --这应该是由于表上没有数据的缘故,也就不存在对应的索引段
    scott@CNMMBO> select * from v$object_usage where index_name='T_PK';
    
    INDEX_NAME                     Table Name        MON USE START_MONITORING    END_MONITORING
    ------------------------------ ----------------- --- --- ------------------- -------------------
    T_PK                           T                 YES NO  03/22/2013 20:53:23
    
    --下面尝试插入两条数据
    scott@CNMMBO> insert into t select 1 from dual;
    
    1 row created.
    
    scott@CNMMBO> insert into t select 2 from dual;
    
    1 row created.
    
    --再次收集统计信息
    scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    --Author : Robinson
    --Blog   : http://blog.csdn.net/robinson_0612
    
    --这下子,索引变成了已经被使用
    scott@CNMMBO> select * from v$object_usage where index_name='T_PK';
    
    INDEX_NAME                     Table Name         MON USE START_MONITORING    END_MONITORING
    ------------------------------ ------------------ --- --- ------------------- -------------------
    T_PK                           T                  YES YES 03/22/2013 20:53:23
    
    

    2、基于Oracle 11g收集统计信息索引被监控情形

    SQL> select * from v$version where rownum<2;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    
    --下面的操作与上面雷同
    SQL> create table t(id number constraint t_pk primary key);
    
    Table created.
    
    SQL> alter index t_pk monitoring usage;
    
    Index altered.
    
    SQL> set linesize 190
    SQL> select * from v$object_usage where index_name='T_PK';
    
    INDEX_NAME                     TABLE_NAME        MON USE START_MONITORING    END_MONITORING
    ------------------------------ ----------------- --- --- ------------------- -------------------
    T_PK                           T                 YES NO  03/22/2013 20:37:57
    
    SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from v$object_usage where index_name='T_PK';
    
    INDEX_NAME                     TABLE_NAME       MON USE START_MONITORING    END_MONITORING
    ------------------------------ ---------------- --- --- ------------------- -------------------
    T_PK                           T                YES NO  03/22/2013 20:37:57
    
    SQL> insert into t select 111 from dual;
    
    1 row created.
    
    SQL> insert into t select 222 from dual;
    
    1 row created.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> exec dbms_stats.gather_table_stats('SCOTT','T',cascade=>true);
    
    PL/SQL procedure successfully completed.
    
    --可以看出,插入数据后,收集统计信息并不会导致索引被使用
    SQL> select * from v$object_usage where index_name='T_PK';
    
    INDEX_NAME                     TABLE_NAME       MON USE START_MONITORING    END_MONITORING
    ------------------------------ ---------------- --- --- ------------------- -------------------
    T_PK                           T                YES NO  03/22/2013 20:37:57
    
    SQL> select index_name,NUM_ROWS,SAMPLE_SIZE,LAST_ANALYZED from dba_indexes where index_name='T_PK';
    
    INDEX_NAME                       NUM_ROWS SAMPLE_SIZE LAST_ANALYZED
    ------------------------------ ---------- ----------- -------------------
    T_PK                                    2           2 2013/03/22 20:39:10
    
    --发布sql查询语句
    SQL> select * from t where id=222;
    
            ID
    ----------
           222
    
    --再次查看,索引监控提示该索引已经被使用
    SQL>  select * from v$object_usage where index_name='T_PK';
    
    INDEX_NAME                     TABLE_NAME       MON USE START_MONITORING    END_MONITORING
    ------------------------------ ---------------- --- --- ------------------- -------------------
    T_PK                           T                YES YES 03/22/2013 20:37:57
    
    SQL> alter index t_pk nomonitoring usage;
    
    

    3、小结
      a、对于索引监控,在Oracle 10g中当收集统计信息时,如果当前索引的统计信息也被收集则导致该索引被监控
      b、注意索引能否被收集到还依赖于estimate_percent以及method_opt等收集时的相关参数
      c、由于上述情形存在因此索引监控在10g中功能有限,不过对于索引的使用情况也可以通过查询DBA_HIST_SQL_PLAN来获得
      d、在Oracle 11g中,不会出现上述情况

     

    更多参考

    PL/SQL --> 游标

    PL/SQL --> 隐式游标(SQL%FOUND)

    批量SQL之 FORALL 语句

    批量SQL之 BULK COLLECT 子句

    PL/SQL 集合的初始化与赋值

    PL/SQL 联合数组与嵌套表
    PL/SQL 变长数组
    PL/SQL --> PL/SQL记录

    SQL tuning 步骤

    高效SQL语句必杀技

    父游标、子游标及共享游标

    绑定变量及其优缺点

    dbms_xplan之display_cursor函数的使用

    dbms_xplan之display函数的使用

    执行计划中各字段各模块描述

    使用 EXPLAIN PLAN 获取SQL语句执行计划

    Oracle ROWID

    NULL 值与索引(一)

    NULL 值与索引(二)

    启用 AUTOTRACE 功能

    函数使得索引列失效

    Oracle 绑定变量窥探

    Oracle 自适应共享游标

    展开全文
  • mysql索引详解

    万次阅读 多人点赞 2021-07-07 21:40:09
    分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB:所有的表都...

    🍅 Java学习路线:搬砖工逆袭Java架构师

    🍅 简介:Java领域优质创作者🏆、CSDN哪吒公众号作者✌ 、Java架构师奋斗者💪

    🍅 扫描主页左侧二维码,加入群聊,一起学习、一起进步 

    🍅 欢迎点赞 👍 收藏 ⭐留言 📝  

    一、MySQL三层逻辑架构

    MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图:

    1、第一层负责连接管理、授权认证、安全等等。

    每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池,避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时,服务器对其进行认证。可以通过用户名和密码的方式进行认证,也可以通过SSL证书进行认证。登录认证通过后,服务器还会验证该客户端是否有执行某个查询的权限。

    2、第二层负责解析查询

    编译SQL,并对其进行优化(如调整表的读取顺序,选择合适的索引等)。对于SELECT语句,在解析查询前,服务器会先检查查询缓存,如果能在其中找到对应的查询结果,则无需再进行查询解析、优化等过程,直接返回查询结果。存储过程、触发器、视图等都在这一层实现。

    3、第三层是存储引擎

    存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信,这些API屏蔽了不同存储引擎之间的差异,使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。

    二、对比InnoDB与MyISAM

    1、 存储结构

    MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

    InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

    2、 存储空间

    MyISAM: MyISAM支持支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后,不会再进行修改操作,可以使用压缩表,极大的减少磁盘的空间占用。

    InnoDB: 需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

    3、 可移植性、备份及恢复

    MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

    InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。

    4、 事务支持

    MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。

    InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

    5、 AUTO_INCREMENT

    MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。

    InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。

    6、 表锁差异

    MyISAM: 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

    InnoDB: 支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

    7、 全文索引

    MyISAM:支持 FULLTEXT类型的全文索引

    InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。

    8、表主键

    MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。

    InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

    9、表的具体行数

    MyISAM: 保存有表的总行数,如果select count() from table;会直接取出出该值。

    InnoDB: 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。

    10、CRUD操作

    MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。

    InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。

    11、 外键

    MyISAM:不支持

    InnoDB:支持

    三、sql优化简介

    1、什么情况下进行sql优化

    性能低、执行时间太长、等待时间太长、连接查询、索引失效。

    2、sql语句执行过程

    (1)编写过程

    select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

    (2)解析过程

    from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

    3、sql优化就是优化索引

    索引相当于书的目录。

    索引的数据结构是B+树。

    四、索引

    1、索引的优势

    (1)提高查询效率(降低IO使用率)

    (2)降低CPU使用率

    比如查询order by age desc,因为B+索引树本身就是排好序的,所以再查询如果触发索引,就不用再重新查询了。

    2、索引的弊端

    (1)索引本身很大,可以存放在内存或硬盘上,通常存储在硬盘上。

    (2)索引不是所有情况都使用,比如①少量数据②频繁变化的字段③很少使用的字段

    (3)索引会降低增删改的效率

    3、索引的分类

    (1)单值索引

    (2)唯一索引

    (3)联合索引

    (4)主键索引

    备注:唯一索引和主键索引唯一的区别:主键索引不能为null

    4、创建索引

    alter table user add INDEX `user_index_username_password` (`username`,`password`)

    5、MySQL索引原理 -> B+树

    MySQL索引的底层数据结构是B+树

    B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。

    B-Tree结构图中每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。

    B+Tree相对于B-Tree有几点不同:

    非叶子节点只存储键值信息。
    所有叶子节点之间都有一个链指针。
    数据记录都存放在叶子节点中。
    将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

    通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:

    InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为〖10〗^3)。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

    实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

    数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引,聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    (1)使用联合索引的全部索引键可触发联合索引

    (2)使用联合索引的全部索引键,但是用or连接的,不可触发联合索引

    (3)单独使用联合索引的左边第一个字段时,可触发联合索引

    (4)单独使用联合索引的其它字段时,不可触发联合索引

    六、分析sql的执行计划---explain

    explain可以模拟sql优化执行sql语句。

    1、explan使用简介

    (1)用户表

    (2)部门表

    (3)未触发索引

    (4)触发索引

    (5)结果分析

    explain中第一行出现的表是驱动表。

    1. 指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]
    2. 未指定联接条件时,行数少的表为[驱动表]

    对驱动表直接进行排序就会触发索引,对非驱动表进行排序不会触发索引。

    2、explain查询结果简介

    (1)id:SELECT识别符。这是SELECT的查询序列号。

    (2)select_type:SELECT类型:

    1. SIMPLE: 简单SELECT(不使用UNION或子查询)
    2. PRIMARY: 最外面的SELECT
    3. UNION:UNION中的第二个或后面的SELECT语句
    4. DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
    5. UNION RESULT:UNION的结果
    6. SUBQUERY:子查询中的第一个SELECT
    7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
    8. DERIVED:导出表的SELECT(FROM子句的子查询)

    (3)table:表名

    (4)type:联接类型

    1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
    2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    6. index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    7. unique_subquery:该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    8. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    9. range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
    10. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    11. all:对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    (5)possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    (6)key:key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

    (7)key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

    (8)ref:ref列显示使用哪个列或常数与key一起从表中选择行。

    (9)rows:rows列显示MySQL认为它执行查询时必须检查的行数。

    (10)Extra:该列包含MySQL解决查询的详细信息。

    1. Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
    2. Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    3. range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
    4. Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
    5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
    6. Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    7. Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。
    8. Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
    9. Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个乘积来确定执行哪个多表SELECT语句。

    🍅 Java学习路线:搬砖工逆袭Java架构师

    🍅 简介:Java领域优质创作者🏆、CSDN哪吒公众号作者✌ 、Java架构师奋斗者💪

    🍅 扫描主页左侧二维码,加入群聊,一起学习、一起进步 

    🍅 欢迎点赞 👍 收藏 ⭐留言 📝  

    展开全文
  • 索引

    万次阅读 2020-02-19 23:37:23
    MySQL索引笔记

    定义


    索引是一种数据结构,通过使用索引,可以加快检索、排序。
    底层通常是BTree,通过BTree可以将无序的数据相对有序。
    索引的缺点是:

    1. 如果表中数据增删改,那么索引也要修改。
    2. 索引占用物理空间。
    3. 创建索引和维护索引耗费时间。

    索引分类


    1. 单值索引

      一个索引只包含单个列,一个表可以有多个单列索引

    2. 唯一索引

      索引列的值必须唯一,允许为空值

    3. 复合索引

      一个索引包含多个列

    基本语法


    1. 创建

      create [unique] index index_name on table(column_name(length));
      alter table_name add [unique] index [index_name] on (column_name(length))

    2. 删除

      drop index [index_name] on table_name;

    3. 查看

      show index from table_name;

    索引结构


    1. BTree索引
      原理:

    2. Hash索引

    3. full-text索引

    4. R-Tree索引

    建立索引注意事项


    1. 主键自动建立唯一索引
    2. 频繁作为查询的字段应该创建索引
    3. 外键建立索引
    4. 频繁更新的字段不要建立索引
    5. where条件里用不到的字段不创建索引
    6. 优先组合索引
    7. 表记录少不要建立索引
    8. 经常增删改的表不要建立索引
    9. 数据重复且平均的字段不要建立索引(比如说性别字段)

    Explain关键字


    Explain关键字用于查询MySQL如何执行SQL语句的,进而依此优化表结构或查询语句。

    作用

    • 表的读取顺序
    • 数据读取操作的操作类型.
    • 哪些索引可以使用
    • 哪些索引被实际使用
    • 表之间的引用
    • 每张表有多少行被优化器查询

    Explain查询结果

    在这里插入图片描述

    表的读取顺序

    1. id相同,从上到下依次执行
    2. id不同,由大到小依次执行

      数字大的优先执行,数字相同的顺序执行
      derived 衍生

    select_type

    在这里插入图片描述

    1. Simple简单查询
    2. Primary最外层查询标记为Primary
    3. SubQUERY子查询
    4. Derived
    5. Union联合查询
    6. Union Result

    Type

    访问类型:
    最好到最差:

    system > const > eq_ref > ref > range > index > ALL
    至少达到range级别

    1. system表只有一条记录(等于系统表)
    2. const比较唯一索引(如where id = 1)
    3. eq_ref唯一性索引扫描(如where t1.id = t2.id)
    4. ref非唯一性索引扫描
    5. range 索引范围查询(如查询索引大于10小于20)
    6. index遍历全部索引
    7. ALL全表查询

    possibble_kesy和key

    possible_keys显示可能应用在这张表的索引
    key实际使用的索引
    如果key为null,说明索引失效或不存在。

    key_len

    表示索引字段中使用的字节数,长度越短越好

    ref

    显示索引的那一列被使用了。如果可能,最好是const。

    rows

    根据表统计信息和索引选用情况,大致估算出找到所需的记录需要读取的行数。
    使用索引和不使用查询的对比

    extra

    额外的重要信息。

    1. Using Filesort :文件排序(不要出现)

    2. Using temporary:使用了临时表(order by和group by,不要出现这个)

      优化这两个,group by一定要按照索引的顺序和个数来。如idx_col1_col2,使用group by col1,col2,而不是group by col2,否则会使用临时表或文件排序(对索引外部排序)

    3. Using index: 使用了索引

      覆盖索引:查询的列就是所建的索引

    索引优化


    1. 左连接右表建立索引,右连接左表建立索引
    2. join语句,小结果集驱动大结果集。
    3. 优先优化内层循环
    4. 保证join字段已经被索引

    索引失效

    1. 全值匹配我最爱
    2. 最佳左前缀法则:查询从索引的最左前列并且不跳过索引中的列
    3. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫
    4. 存储引擎不能使用索引中范围条件右边的列
    5. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select*
    6. mysq|在使用不等于**(!=或者<>)**的时候无法使用索引会导致全表扫描
    7. is null ,is not null也无法使用索引
    8. like以通配符开头(’%abc… ')mysq|索引失效会变成全表扫描的操作(使用覆盖索引可以避免通配符匹配开头索引失效的问题)
    9. 字符串不加单引号索引失效
    10. 少用or,用它来连接时会索引失效

    口诀

    查询优化

    在这里插入图片描述

    1. 慢查询

      通过开启慢查询,可以把执行速度慢的SQL筛选出来

    2. 小表驱动大表

    3. order by 的顺序和复合索引顺序一致就不会产生filesort

      如index_AB 索引,使用order by A,B不会产生filesort ,使用order by B或者order by B,A就会filesort
      order by 不要和select * 一起使用
      尝试提高sort_buffer_size和max_length_for_sort_data参数

      在这里插入图片描述

    慢查询

    慢查询用于筛选响应时间超过阈值的语句。阈值默认是10秒,可以配合explain语句进行分析。

    select variables like ‘%slow_query_log%’;
    查询是否开启慢查询

    set global slow_query_log =1;
    开启慢查询

    如果要永久开启慢查询,则需要更改my.cnf文件

    set global long_query_time = 3;
    阈值设置为3秒

    日志分析工具mysqldumpslow
    在这里插入图片描述

    在这里插入图片描述

    Show Profile

    mysql 提供用来分析当前会话中语句执行的资源消耗情况,用于SQL调优。

    查询是否开启 show variables like ‘profiling’;
    开启set profiling = on;
    查看结果 show profiles;

    诊断SQL:show profiles cpu,block io for query ;
    在这里插入图片描述
    如果结果表status中出现以下:在这里插入图片描述
    则说明出现问题。

    展开全文
  • 1,查询索引状态 1.1 查询user_indexes表 select status,T.* from user_indexes T where table_name='表名' 状态列STATUS说明: valid:当前索引有效 N/A :分区索引 有效 unusable:索引失效 1.2 查询分区索引-...

    1,查询索引状态

    1.1 查询user_indexes表
    select status,T.* from user_indexes T where table_name='表名'
    
    状态列STATUS说明:
         valid:当前索引有效
         N/A :分区索引 有效
         unusable:索引失效
    
    1.2 查询分区索引-user_ind_partitions表
    select index_name, partition_name, status
      from user_ind_partitions
     where index_name = '索引名称';
    
    索引状态说明:
    	 USABLE:索引有效
    	 UNUSABLE:索引失效
    
    1.3 检查损坏索引
    SELECT status, COUNT(*)
         FROM dba_indexes
         GROUP BY status
    UNION
    SELECT status, COUNT(*)
         FROM dba_ind_partitions
         GROUP BY status
    UNION
    SELECT status, COUNT(*)
        FROM dba_ind_subpartitions
        GROUP BY status
    

    2,索引重建

    2.1全局索引重建
    alter index pk_kc03 rebuild nologging;
    or
    alter index pk_kc03 rebuild
    
    2.2 local索引重建
    select b.table_name,
    a.INDEX_NAME,
    a.PARTITION_NAME,
    a.STATUS,
    'alter index ' || a.index_name || ' rebuild partition ' ||partition_name || ';' --重建列
    from USER_IND_PARTITIONS a, user_part_indexes b
    where a.index_name = b.index_name
    and b.TABLE_NAME IN ('PART_TAB_SPLIT')
    and STATUS = 'UNUSABLE'
    ORDER BY b.table_name, a.INDEX_NAME, a.PARTITION_NAME;
    

    在针对truncate等 操作时直接更新 index 也可以搞定。

    alter table part_tab_trunc truncate partition p2 Update GLOBAL  indexes;
    
    展开全文
  • InnoDB索引

    万次阅读 2019-08-05 16:30:09
    1.概述 InnoDB存储引擎支持一下几种索引 B+ 树索引 全文索引 哈希索引 2.B+ 树索引 ...B+树索引可以分为聚集索引(clustered inex)和辅助索引(secondary),其内部全是B+树结构,高度平衡。...
  • Oracle收集索引统计信息

    千次阅读 2013-12-11 22:44:01
    相信大家对索引结构非常熟悉了,它是由根、支、叶...下面分析索引统计信息的相关内容。 一、如何查询索引统计信息  查询索引统计信息需要用到user_ind_statistics,下面是典型的查询语句。 SELECT INDEX_NAME AS N
  • 在《面试官:为啥加了索引查询会变快?》一文中,我们介绍了索引的数据结构,正是因为索引使用了B+树,才使得查询变快。说白了,索引的原理就是减少查询的次数、减少磁盘IO,达到快速查找所需数据的目的 我们一起来...
  • GIS空间索引

    万次阅读 2020-03-11 12:02:02
    在GIS系统中,空间索引技术就是通过更加有效的组织方式,抽取与空间定位相关的信息组成对原空间数据的索引,以较小的数据量管理大量数据的查询,从而提高空间查询的效率和空间定位的准确性。 常见的GIS空间索引 KD...
  • 普通索引 最基本的索引类型,没有唯一性之类的限制。普通索引可以通过以下几种方式创建: 创建索引,例如CREATE INDEX ON tablename (列的列表); 修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列...
  • 数据库索引

    千次阅读 多人点赞 2019-08-20 22:49:54
    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。数据库索引好比是一本书前面的目录,能加快数据库的查询速度。索引分为聚簇索引和非聚簇索引两种,聚簇索引是按照...
  • MySQL普通索引、唯一索引、主索引、外键索引、复合索引、全文索引的使用
  • PostgreSQL - 查询表结构和索引信息

    万次阅读 2018-11-19 23:38:45
    PostgreSQL的表一般都是建立在public这个schema下的,假如现在有个数据表t_student,可以用以下几种方式来查询表结构和索引信息。 使用\d元命令查看表字段信息索引信息 在cmd界面使用psql连接db后,输入\d加上表名...
  • elasticsearch查询某个索引分片信息

    万次阅读 2019-10-30 14:19:53
    elasticsearch6.8 ...通过下面的路径可以查询elasticsearch某个索引分片信息 http://xx.xx.xx.xx:9200/索引名称/_search_shards { "nodes": { "Q6i1duFYQLmJFIu812DQjQ": { "name": "node1", "ephemera...
  • MySQL索引统计信息information_schema.INDEX_STATISTICS背景:环境:现象:索引下线设置索引不可见删除索引 背景: MySQL的开源版本MariaDB、Percona MySQL Server和AliSQL 5.6版本支持统计索引信息,即可以统计...
  • 在oracle中查找所有的表的索引的命令 select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name  在oracle中实现索引的批量重建的...
  • Java获得数据库基本信息,包括表的信息、表中列的信息索引信息、存储过程信息等。先创建与数据库的连接,通过Connection 对象的getMetaData()方法可以创建DatabaseMetaData对象,从该对象中获取数据库的基本信息。...
  • 索引3:Hash索引与BitMap索引

    千次阅读 2020-02-11 13:41:49
    hash索引是将索引键通过hash运算后,将运算结果的hash值和对应的行指针信息存储Bucket。 引用:‘’哈希索引(hash index)基于哈希表实现,只有精确匹配索引所有列的查询才有效。对于每一行数据,存储引擎都会对...
  • MongoDB 地理位置索引

    千次阅读 2019-03-22 21:58:12
    地理信息索引分为两类:2D平面索引,2DSphere球面索引。在2D索引里面基本上能够保存的信息都是坐标,而且坐标保存的就是经纬度坐标。 具体的应用场景在:微信的摇一摇,大众点评等查询附近的住宿地。滴滴、膜拜、...
  • 索引:是一个单独的、物理的数据库结构,它是某个表中一列或若干类的集合相应的指向表中物理标识这些值的数据页的逻辑指针清单,索引是依赖于表建立的,它提供了数据库中编排表中数据的内部方法。一个表分为两部分...
  • MySQL索引索引优化

    千次阅读 2020-09-04 16:48:00
    索引是对数据库表中一个或多个列的值进行排序的结构,建立索引有助于快速获取信息。 你也可以这样理解:索引就是加快检索表中数据的方法。数据库的索引类似于书籍的索引。在书籍中,索引允许用户不必翻阅完整个书就...
  • MySQL索引

    万次阅读 2020-11-25 17:15:44
    1.索引简介 在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种...这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。 当表中有大量记录时,若要对表进行查
  • Oracle 查看表的索引信息

    万次阅读 2019-07-12 10:29:58
    select * from user_ind_columns where table_name = upper(‘agent_master’) // agent_master为表名 ...的时候oracle会自动创建索引,如果再次在改主键对应的自动创建索引会报错。 用user_inde...
  • Mysql索引

    千次阅读 2019-12-02 13:21:26
    好记忆不如烂笔头, 能记下点什么, 就记下点什么, 方便后期的巩固. 一.介绍 1.什么是索引? 2.... 1.MySQL的索引分类 2.各索引应用场景 3.索引类型 4.操作索引 5.唯一索引 四.测试...
  • Hash索引和BTree索引

    万次阅读 多人点赞 2015-08-06 23:20:49
    索引是帮助mysql获取数据的数据结构。最常见的索引是Btree索引和Hash索引。 不同的引擎对于索引有不同的支持:Innodb和MyISAM默认的索引是Btree索引;而Mermory默认的索引是Hash索引。 Hash索引 所谓...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 962,219
精华内容 384,887
关键字:

信息索引