优化你的系统--索引(一) 正确使用索引

最新推荐文章于 2020-04-07 11:46:20 发布
最新推荐文章于 2020-04-07 11:46:20 发布
阅读量82 收藏
点赞数

优化你的系统--索引(一) 正确使用索引

正确应用索引可以明显提高应用性能.
正确应用索引包括两部分,创建正确的索引和这确使用索引.
这里我们关心后者,开发人员如何正确使用索引.

首先建立测试环境.

SQL> create table test (id number,tu_mdn varchar2(20),name varchar2(20)) tablespace users;

表已创建。

[@more@]

优化你的系统--索引(一) 正确使用索引

正确应用索引可以明显提高应用性能.
正确应用索引包括两部分,创建正确的索引和这确使用索引.
这里我们关心后者,开发人员如何正确使用索引.

首先建立测试环境.

SQL> create table test (id number,tu_mdn varchar2(20),name varchar2(20)) tablespace users;

表已创建。

SQL> begin
  2       for i in 1..10000 loop
  3  insert into test values (i,13300000000+i,'索引测试'||i);
  4       end loop;
  5       commit;
  6  end;
  7  /

PL/SQL 过程已成功完成。

SQL> select count(*) from test;

  COUNT(*)
----------
     10000

SQL> select * from test where rownum<10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
       667 13300000667          索引测试667
       668 13300000668          索引测试668
       669 13300000669          索引测试669
       670 13300000670          索引测试670
       671 13300000671          索引测试671
       672 13300000672          索引测试672
       673 13300000673          索引测试673
       674 13300000674          索引测试674
       675 13300000675          索引测试675

已选择9行。

SQL> analyze table test compute statistics;

表已分析。

SQL> create index test_tu_mdn_idx on test(tu_mdn);

索引已创建。

SQL> analyze index test_tu_mdn_idx compute statistics;

索引已分析

了解了什么情况下会导致索失效
典型的几种索引失效的例子.

1 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
由于表的字段tu_mdn定义为varchar2(20),但在查询时把number类型做为where条件传给Oracle,这样会导致索引失效.

SQL> set autotrace on
SQL> select * from test where tu_mdn=13333333333;

未选定行


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=14)
   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=4 Card=1 Bytes=14)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
        269  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
         
正确的sql
SQL> select * from test where tu_mdn='13333333333';

未选定行


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=14)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=14)

   2    1     INDEX (RANGE SCAN) OF 'TEST_TU_MDN_IDX' (NON-UNIQUE) (Co
          st=1 Card=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        269  bytes sent via SQL*Net to client
        372  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

2 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
SQL> create index test_id on test(id);
SQL> analyze index test compute statistics;
SQL> select * from test where id-1=9;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=100 Bytes=260
          0)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=5 Card=100 Bytes=2600)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

正确sql:
SQL> select * from test where id=10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=26)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
          es=26)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID' (NON-UNIQUE) (Cost=1 Car
          d=1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


3 使用Oracle内部函数导致索引失效.
对于这样情况应当创建基于函数的索引.
SQL> select * from test where round(id)=10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=100 Bytes=260
          0)

   1    0   TABLE ACCESS (FULL) OF 'TEST' (Cost=5 Card=100 Bytes=2600)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


创建基于函数的索引解决问题:       
SQL> create index test_id_fbi_idx on test(round(id));

索引已创建。
SQL> analyze index test_id_fbi_idx compute statistics;

索引已分析
SQL> select * from test where round(id)=10;

        ID TU_MDN               NAME
---------- -------------------- --------------------
        10 13300000010          索引测试10


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=100 Bytes=260
          0)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=100 B
          ytes=2600)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID_FBI_IDX' (NON-UNIQUE) (Co
          st=1 Card=40)

Statistics
----------------------------------------------------------
         22  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        499  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
         

还有一些操作会导致索引实效，我只说明原因和避免方法.


4 避免在索引列使用not,is null,is not null,Oracle不维护空值.
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．
对于单列索引，如果列包含空值，索引中将不存在此记录.
对于复合索引，如果每个列都为空，索引中同样不存在此记录.　
因为空值不存在于索引列中,所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引.


5 用>=代替> 当然前提是你对业务做出正确的判断.
比如我想找出id大于9000的数据,那么有两种方法可以查出.
select * from test where id > 9000; t1
已用时间:  00: 00: 00.04

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1000 Bytes=2
          6000)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=16 Card=1000
           Bytes=26000)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID_IDX' (NON-UNIQUE) (Cost=6
           Card=1000)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        150  consistent gets
          0  physical reads
          0  redo size
      38761  bytes sent via SQL*Net to client
       1229  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed

select * from test  where id >= 9001; t2

已用时间:  00: 00: 00.03

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1002 Bytes=2
          6052)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=16 Card=1002
           Bytes=26052)

   2    1     INDEX (RANGE SCAN) OF 'TEST_ID_IDX' (NON-UNIQUE) (Cost=6
           Card=1002)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        150  consistent gets
          0  physical reads
          0  redo size
      38820  bytes sent via SQL*Net to client
       1229  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 
      

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-789301/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/76065/viewspace-789301/

cuhutai2494
关注
索引使用及优化问题
sssssssen的博客
09-21 190
一、索引的概述 1. 什么是索引？ 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。通俗一点地说，如果把数据库比作字典，要去查找一个数据（字典里面的字），通过目录就很方便快速定位，而索引就相当于字典的目录。 2. 索引的优缺点 优点： 快速访问数据表中的特定信息，提高检索速度； 创建唯一性索引，保证数据库表中每一行数据的唯一性； 加...
MySQL优化 - 索引
一个什么都不会的人
02-05 209
索引介绍、为什么要加索引、为什么加了索引会变快、索引优缺点、索引分类、使用索引应该注意那些、如何查询索引
优化索引技巧
syviah的博客
06-18 336
mysql的优化可以从硬件设备的选择、操作系统、数据库结构设计、SQL查询、应用程序各个方面进行优化，这里只从数据库的设计及查询语句方面进行优化。 1、创建索引 对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加 索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如
索引使用建议及索引的优缺点
weixin_34401479的博客
08-01 207
建议使用索引： 1.经常检索的列。 2.经常用于表连接的列。 3.经常排序、分组的列。 不建议使用索引： 1.技术很低的列。 2.更新频繁但检索不频繁的列。 3.BLOG/TEXT等长内容列。 4.很少用于检索的列。 索引的优点： 1.提高数据检索效率。 2.提高表间的JOIN效率。 3.利用唯一性索引，保证数据的唯一性。 4.提高排序和分组效率。 索...
MySQL索引性能优化分析
热门推荐
张彦峰的博客
04-07 173万+
总结MySQL索引性能优化，主要集中在索引的背景介绍以及索引的高效创建、使用和查询等，最有给出除了索引的一些其他优化思路
04-VIP-Mysql索引优化实战一.zip
06-04
定期分析和优化索引可以减少碎片，提高查询效率。使用ANALYZE TABLE或OPTIMIZE TABLE命令可以帮助评估和改善索引性能。 此外，过度使用索引也可能带来负面影响，如占用额外的存储空间和增加写操作的开销。因此，...
MySQL性能优化之如何高效正确的使用索引
01-19
在后续使用索引，或者优化索引时，可以从这些方面出发，进一步来加深对索引正确高效的使用。 一、索引失效 索引失效，是一个老生常谈的话题了。只要提到数据库优化、使用索引，都能一口气说出一大堆索引失效的场景，...
SQL查询优化--索引的使用与管理[收集].pdf
10-20
SQL查询优化是一个重要的数据库管理任务，它涉及到如何有效地利用索引来加速数据检索。索引是数据库管理系统中的一个重要...正确使用索引、理解和分析执行计划以及适时维护索引都可以显著改善数据库系统的整体效率。
cpp-H3是一个使用六边形网格的地理空间索引系统
08-16
H3，全称为Hexagon Hierarchical Spatial Index，是一种高效、灵活的地理空间索引系统，它利用六边形网格进行空间数据的组织和管理。H3由Uber开发并开源，为大数据分析、地理空间查询优化以及分布式计算提供了强大的...
Mysql使用索引的正确方法及索引原理详解
12-16
综上所述，正确地使用和设计索引是MySQL数据库性能优化的重要环节。开发者应当在数据库设计阶段就考虑到索引的需求，避免在后期因性能问题而进行索引的补救。同时，了解索引的工作原理和磁盘I/O特性，有助于我们做出...
python从入门到实践外星人入侵
09-14
python pycharm 外星人
『人事流程图新』公务车辆管理流程图.xlsx
09-14
『人事流程图新』公务车辆管理流程图.xlsx
应届大学生求职通用简历表格
09-14
应届大学生求职通用简历表格
基于Go语言的国密算法库（gmsm）.zip
最新发布
09-14
本项目是一个基于Go语言实现的国密SM2/SM3/SM4算法库。它提供了sm2椭圆曲线算法、SM3密码哈希算法以及SM4分组密码算法的实现。该库支持密钥生成、签名验证、加密解密等基础操作，同时提供了与现有标准接口兼容的证书生成、读写以及证书链操作等功能。此外，该项目还提供了详细的使用说明和通信渠道，方便用户交流和获取技术支持。该项目适用于需要对国密算法进行开发和应用的场景，如区块链、金融、信息安全等领域。 1、资源项目源码均已通过严格测试验证，保证能够正常运行； 2、项目问题、技术讨论，可以给博主私信或留言，博主看到后会第一时间与您进行沟通； 3、本项目比较适合计算机领域相关的毕业设计课题、课程作业等使用，尤其对于人工智能、计算机科学与技术等相关专业，更为适合； 4、下载使用后，可先查看reADME.md文件（如有），本项目仅用作交流学习参考，请切勿用于商业用途。
4.html
09-14
4
TestDataPlan-CET.pdf
09-14
TestDataPlan-CET.pdf
专属题库-网络安全管理员二级（技师）实操练习题.pdf
09-14
专属题库-网络安全管理员二级（技师）实操练习题.pdf
2.html
09-14
2
一个类Excel纯JS表格上下滚动显示、在线编辑js类控件
09-14
1.本控件设计封装了一个纯javascript表格显示js类控件，可以直接接口mysql数据库结果集查询，调用只需一行语句。控件具有自定义显示颜色、自定义显示宽高、类似excel上下左右滚动翻页、按字段排序、选择、修改、添加、插入、删除、鼠标调整列宽、双击(按空格键)直接编辑、自动返回编辑json数据，一键导出excel、自适应显示等功能。为力求网页下载简单方便快速，没有加入华丽图片背景(可后期叠加)，全调用只有一个文件，js压缩下来只43k。 2.tableshow控件为纯js编写，只有一个tableshow.min.js文件，只需一行调用代码即可。其他js和css为辅助显示用，非tableshow控件调用。 3.为了说明tableshow使用方法和注意事项，用了三个html文件进行示例显示，所有使用方法和注意事项在这三个文件中进行了说明。另外，对tableshow插件的操作使用，点击插件调用显示表格的“操作-帮助”即可显示。
Mysql索引优化指南：正确使用与原理详解
在MySQL数据库管理系统中，正确地使用索引是提升查询性能的关键。索引是一种特殊的数据结构，类似于书籍的目录，它帮助数据库快速定位和访问数据，尤其是在处理大量数据时，其作用尤为显著。索引优化对于那些频繁...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值