由索引引出简单实验几例 ***********************************************声明************************************************ 原创作品，出自 深蓝的blog 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。 表述有错误之处
由索引引出简单实验几例
***********************************************声明************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。
表述有错误之处，请您留言，不胜感激。
提醒：点击目录，更有助于您的查看。
*****************************************************************************************************
对之前的小例子重新归纳了一下，希望可以帮助对索引有进一步的理解。
【例1】数据量小不需建索引
//如果表的数据量很少，全表扫描和走索引成本相差很小，使用索引是不是就没有必要了。 实验操作： SQL> SELECT ENAME,JOB,SAL FROM SCOTT.EMP; //先找到一张小表以作实验，查看表中信息，只有14行 ENAME JOB SAL ---------- --------- ------ SMITH CLERK 800 ALLEN SALESMAN 1600 WARD SALESMAN 1250 JONES MANAGER 2975 MARTIN SALESMAN 1250 BLAKE MANAGER 2850 CLARK MANAGER 2450 SCOTT ANALYST 3000 KING PRESIDENT 5000 TURNER SALESMAN 1500 ADAMS CLERK 1100 JAMES CLERK 950 FORD ANALYST 3000 MILLER CLERK 1300 已选择14行。 SQL> SET AUTOTRACE ON SQL> SET AUTOTRACE TRACEONLY SQL> SELECT * FROM SCOTT.EMP WHERE ENAME='JAMES'; //全表扫描查找JAMES的信息 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 3 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| EMP | 1 | 38 | 3 (0)| 00:00:01 | -------------------------------------------------------------------------- SQL> CREATE INDEX IND_EMP_ENAME ON SCOTT.EMP(ENAME); //为ENAME列建索引 SQL> SELECT * FROM SCOTT.EMP WHERE ENAME='JAMES'; //走列索引查找JAMES的信息 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IND_EMP_ENAME | 1 | | 1 (0)| 00:00:01 | -------------------------------------------------------------------------------- //全表扫描成本是3%，走索引成本是2% //从以上实验发现，在表的数据量很小的情况下，全表扫描和走索引成本上相差不大。
【例2】全表扫描IO成本低于使用索引情况
**************************************************************************
举一个例子，不恰当的使用索引，比用全表扫描的的IO成本更加高。
**************************************************************************
解答：
思路：创建一组rowid是散落在多个表数据块中的索引，这样由于索引列数据的分布情况和索引中的顺序差异很大，致使通过全表扫表比走索引更能降低IO的使用成本。
操作如下：
SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS;
//创建了一个TAB_HYL表以作实验
SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS;
//分析这张TAB_HYL实验表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';
//查找出实验表上的行数、块数
NUM_ROWS BLOCKS
---------- ----------
72606 1033
SQL> SELECT 72606/1033 FROM DUAL;
//计算平均每个块中的行数为70行
72606/1033
----------
70.286544
SQL> DROP TABLE TAB_HYL PURGE;
//删除这张表，这张表就是为了计算出每块所占的行数，从而对其进行构建完成实验
SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<=70;
//重新创建实验表让它装入70行形成第一个块
SQL> INSERT INTO TAB_HYL SELECT * FROM TAB_HYL;
//复制相同的70行插到实验表中,即实验表中共有140行数据，两个块
SQL> /
//再次执行相同操作，但此时基准的实验表为140行，因此第三次插入了140行数据，即现在实验表有280行数据
SQL> /
//按照上面的方法以下连续创建，形成多个块，让每个块中都有相同的键值而形成一组实验用的ROWID
SQL> /
SQL> /
SQL> /
SQL> /
SQL> COMMIT;
SQL> CREATE INDEX IND_H1 ON TAB_HYL(OBJECT_ID);
//创建实验表中OBJECT_ID列的索引，之后通过该列值进行查询，来说明查询的成本
SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS; //分析一下实验表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';
//查看一下此时实验表的行数、块数已经达到实验准备条件，可以开始试验了
NUM_ROWS BLOCKS
---------- ----------
8960 103
SQL> SET AUTOTRACE ON
SQL> SET AUTOTRACE TRACEONLY
//设定跟踪
SQL> SELECT * FROM TAB_HYL WHERE OBJECT_ID=70;
//通过上面创建了索引的列来查找，得到下面的分析结果，记住cpu的成本为30，并且数据库自动完成的是走全表扫描，说明数据库已经判断出什么方式查询，成本更低了。
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 128 | 10112 | 30 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB_HYL | 128 | 10112 | 30 (0)| 00:00:01 |
----------------------------------------------------------------------
//之后我们人为让查询走索引再看一下分析结果。
SQL> SELECT /*+INDEX(TAB_HYL IND_H1)*/ * FROM TAB_HYL WHERE OBJECT_ID=70;
//强制查询走索引，输出一下结果，看到成本是102，要远高于全表扫描的成本（全表扫描是30，见上表）。
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 128 | 10112 | 102(0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB_HYL | 128 | 10112 | 102(0)| 00:00:02 |
|* 2 | INDEX RANGE SCAN | IND_H1 | 128 | | 1(0)| 00:00:01 |
----------------------------------------------------------------------
通过以上实验说明，当索引列数据的分布情况和索引中的顺序差异很大这种情况出现时，做索引范围扫描效率偏低。
【例3】构造表时集簇因子数分别为接近块数、接近行数
**************************************************************************
建两张表，各建一个索引。要求A表的索引集簇因子接近表块数，B表的索引集簇因子接近表行数。
**************************************************************************
（一）、创建A表：索引集簇因子接近表块数
操作：
SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS;
//先创建了一个TAB_HYL表以作实验源表，为了通过这个表分析出表中一个块所占的行数
SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS;
//分析这张TAB_HYL实验表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';
//查找出实验表上的行数、块数
NUM_ROWS BLOCKS
---------- ----------
72606 1033
SQL> SELECT 72606/1033 FROM DUAL;
//计算平均每个块中的行数为70行
72606/1033
----------
70.286544
SQL> DROP TABLE TAB_HYL PURGE;
//删除这张表
SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<=70;
//重新创建实验表让它装入70行形成第一个块
SQL> INSERT INTO TAB_HYL SELECT * FROM TAB_HYL;
//复制相同的70行插到实验表中,即实验表中共有140行数据，两个块
SQL> /
//再次执行相同操作，但此时基准的实验表为140行，因此第三次插入了140行数据，即现在实验表有280行数据
SQL> /
//按照上面的方法以下连续创建，这是为了构造实验表的集簇因子
SQL> /
SQL> /
SQL> /
SQL> /
SQL> COMMIT;
SQL> CREATE TABLE TAB_A AS SELECT * FROM TAB_HYL ORDER BY OBJECT_ID;
//根据实验表创建出表A，表A是通过OBJECT_ID排序的，因此就得到了键值相同的分布较集中的块
SQL> CREATE INDEX IND_H1 ON TAB_A(OBJECT_ID);
//创建A表中OBJECT_ID列的索引
SQL> ANALYZE TABLE TAB_A COMPUTE STATISTICS;
//分析一下A表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME = 'TAB_A';
NUM_ROWS BLOCKS
---------- ----------
8960 102
SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'IND_H1';
//查看A表索引列的b-tree级别、叶的块数、不同的key值、平均每个key所占的叶块的数量、聚集的因子
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
------- ----------- ------------- ----------------------- -----------------
1 18 70 1 102
//得到了A表索引列的集簇因子数（102）与上面的A表的块数（102）是相同的。
（二）、创建B表：索引集簇因子接近表行数
操作：
SQL> CREATE TABLE TAB_HYL AS SELECT * FROM DBA_OBJECTS;//创建了一个实验表以作实验
SQL> ANALYZE TABLE TAB_HYL COMPUTE STATISTICS;//分析这张TAB_HYL实验表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_HYL';//查找出实验表上的行数、块数
NUM_ROWS BLOCKS
---------- ----------
72606 1033
SQL> SELECT 72606/1033 FROM DUAL;//计算平均每个块中的行数为70行
72606/1033
----------
70.286544
SQL> DROP TABLE TAB_HYL PURGE;//删除这张表
SQL> CREATE TABLE TAB_B AS SELECT * FROM DBA_OBJECTS WHERE ROWNUM<=70;//创建B表让它装入70行形成第一个块
SQL> INSERT INTO TAB_B SELECT * FROM TAB_B;//复制相同的70行插到B表中,即B表中共有140行数据，两个块
SQL> / //再次执行相同操作，但此时基准的B表为140行，因此第三次插入了140行数据，即现在B表有280行数据
SQL> / //按照上面的方法以下连续创建，这是为了构造B表的集簇因子
SQL> /
SQL> /
SQL> /
SQL> /
SQL> COMMIT;
SQL> CREATE INDEX IND_H2 ON TAB_B(OBJECT_ID);//创建B表中OBJECT_ID列的索引
SQL> ANALYZE TABLE TAB_B COMPUTE STATISTICS; //分析一下B表
SQL> SELECT NUM_ROWS,BLOCKS FROM USER_TABLES WHERE TABLE_NAME ='TAB_B';//查看一下此时B表的行数、块数
NUM_ROWS BLOCKS
---------- ----------
8960 103
SQL> SELECT BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR
2 FROM USER_INDEXES
3 WHERE INDEX_NAME = 'IND_H2';
//查看B表索引列的b-tree级别、叶的块数、不同的key值、平均每个key所占的叶块的数量、集簇因子
BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR
------- ----------- ------------- ----------------------- -----------------
1 18 70 1 7070
//B表索引列的集簇因子(7070)和B表中的行数(8960)相对接近.
【例4】有关索引监控
************************************************************************** 对一张表的索引开监控，看是否有使用到。 ************************************************************************** 会话A： SQL> ALTER INDEX IND_H1 MONITORING USAGE; //对上面练习中用到的IND_H1索引开监控 SQL> SELECT * FROM V$OBJECT_USAGE; //通过查看V$OBJECT_USAGE视图查看对IND_H1索引的监控信息，MON为YES代表已经开监控了，当前没有人用到 INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------------- ---------------------- --- --- ------------------- ------------------- IND_H1 TAB_A YES NO 03/18/2014 16:54:29 会话B： SQL> SET AUTOTRACE ON; //开监控，确认下面的操作是走索引的 SQL> SELECT * FROM TAB_A WHERE OBJECT_ID=70; //使用带索引列查询，分析结果如下 -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 128 | 10112 | 3 (0)| 00:0 0:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TAB_A | 128 | 10112 | 3 (0)| 00:0 0:01 | |* 2 | INDEX RANGE SCAN | IND_H1 | 128 | | 1 (0)| 00:0 0:01 | -------------------------------------------------------------------------------- 会话A： SQL> SELECT * FROM V$OBJECT_USAGE; //再次通过V$OBJECT_USAGE视图查看对IND_H1索引的监控信息，MON为YES代表已经开监控了，USE为YES代表当前有人在使用 INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------------- ---------------------- --- --- ------------------- ------------------- IND_H1 TAB_A YES YES 03/18/2014 16:54:29
***********************************************声明************************************************
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处（http://blog.csdn.net/huangyanlong）。
表述有错误之处，请您留言，不胜感激。
提醒：点击目录，更有助于您的查看。
*****************************************************************************************************
陳述
本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn
如何識別和優化MySQL中的慢速查詢？ （慢查詢日誌，performance_schema）Apr 10, 2025 am 09:36 AM要優化MySQL慢查詢，需使用slowquerylog和performance_schema：1.啟用slowquerylog並設置閾值，記錄慢查詢；2.利用performance_schema分析查詢執行細節，找出性能瓶頸並優化。
MySQL和SQL：開發人員的基本技能Apr 10, 2025 am 09:30 AMMySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。
描述MySQL異步主奴隸複製過程。Apr 10, 2025 am 09:30 AMMySQL異步主從復制通過binlog實現數據同步，提升讀性能和高可用性。 1)主服務器記錄變更到binlog；2)從服務器通過I/O線程讀取binlog；3)從服務器的SQL線程應用binlog同步數據。
mysql：簡單的概念，用於輕鬆學習Apr 10, 2025 am 09:29 AMMySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。
MySQL：數據庫的用戶友好介紹Apr 10, 2025 am 09:27 AMMySQL的安裝和基本操作包括：1.下載並安裝MySQL，設置根用戶密碼；2.使用SQL命令創建數據庫和表，如CREATEDATABASE和CREATETABLE；3.執行CRUD操作，使用INSERT,SELECT,UPDATE,DELETE命令；4.創建索引和存儲過程以優化性能和實現複雜邏輯。通過這些步驟，你可以從零開始構建和管理MySQL數據庫。
InnoDB緩衝池如何工作，為什麼對性能至關重要？Apr 09, 2025 am 12:12 AMInnoDBBufferPool通過將數據和索引頁加載到內存中來提升MySQL數據庫的性能。 1)數據頁加載到BufferPool中，減少磁盤I/O。 2)臟頁被標記並定期刷新到磁盤。 3)LRU算法管理數據頁淘汰。 4)預讀機制提前加載可能需要的數據頁。
MySQL：初學者的數據管理易用性Apr 09, 2025 am 12:07 AMMySQL適合初學者使用，因為它安裝簡單、功能強大且易於管理數據。 1.安裝和配置簡單，適用於多種操作系統。 2.支持基本操作如創建數據庫和表、插入、查詢、更新和刪除數據。 3.提供高級功能如JOIN操作和子查詢。 4.可以通過索引、查詢優化和分錶分區來提升性能。 5.支持備份、恢復和安全措施，確保數據的安全和一致性。
與MySQL中使用索引相比，全表掃描何時可以更快？Apr 09, 2025 am 12:05 AM全表掃描在MySQL中可能比使用索引更快，具體情況包括：1)數據量較小時；2)查詢返回大量數據時；3)索引列不具備高選擇性時；4)複雜查詢時。通過分析查詢計劃、優化索引、避免過度索引和定期維護表，可以在實際應用中做出最優選擇。
熱AI工具
Undresser.AI Undress
人工智慧驅動的應用程序，用於創建逼真的裸體照片
AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。
Undress AI Tool
免費脫衣圖片
Clothoff.io
AI脫衣器
AI Hentai Generator
免費產生 AI 無盡。
熱門文章
R.E.P.O.能量晶體解釋及其做什麼（黃色晶體）
3 週前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳圖形設置
3 週前By尊渡假赌尊渡假赌尊渡假赌
刺客信條陰影：貝殼謎語解決方案
1 週前ByDDD
R.E.P.O.如果您聽不到任何人，如何修復音頻
3 週前By尊渡假赌尊渡假赌尊渡假赌
WWE 2K25：如何解鎖Myrise中的所有內容
3 週前By尊渡假赌尊渡假赌尊渡假赌
熱工具
PhpStorm Mac 版本
最新（2018.2.1 ）專業的PHP整合開發工具
Dreamweaver Mac版
視覺化網頁開發工具
禪工作室 13.0.1
強大的PHP整合開發環境
MantisBT
Mantis是一個易於部署的基於Web的缺陷追蹤工具，用於幫助產品缺陷追蹤。它需要PHP、MySQL和一個Web伺服器。請查看我們的演示和託管服務。
SublimeText3漢化版
中文版，非常好用
