官方mysql中檢視索引是否被使用到
官方mysql中檢視索引是否被使用到：
在percona版本或marida中可以通過 information_schea.index_statistics檢視得到，
在官方版本中如何檢視呢？
select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage;
應該可以通過上面的sql得到。 如果read,fetch的次數都為0的話，應該是沒有被使用過的。
通過下面的例子，可以證實：
啟動mysql:
mysql> show create table a.t3;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE `t3` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) NOT NULL DEFAULT 'bb',
KEY `idx_t3` (`id`),
KEY `idx_t3_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage where object_name='t3';
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| TABLE | a | t3 | idx_t3 | 0 | 0 | 0 |
| TABLE | a | t3 | idx_t3_name | 0 | 0 | 0 |
| TABLE | a | t3 | NULL | 0 | 0 | 0 |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
3 rows in set (0.01 sec)
mysql> explain select id from t3 where id=1;
ERROR 1046 (3D000): No database selected
mysql> explain select id from a.t3 where id=1;
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 | SIMPLE | t3 | ref | idx_t3 | idx_t3 | 5 | const | 1 | Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage where object_name='t3';
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| TABLE | a | t3 | idx_t3 | 0 | 0 | 0 |
| TABLE | a | t3 | idx_t3_name | 0 | 0 | 0 |
| TABLE | a | t3 | NULL | 0 | 0 | 0 |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
3 rows in set (0.01 sec)
mysql> use a;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select id from t3 where id=1;
Empty set (0.00 sec)
mysql> select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage where object_name='t3';
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| TABLE | a | t3 | idx_t3 | 1 | 1 | 1 |
| TABLE | a | t3 | idx_t3_name | 0 | 0 | 0 |
| TABLE | a | t3 | NULL | 0 | 0 | 0 |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
3 rows in set (0.00 sec)
mysql> select id from t3 where id=10; +------+
| id |
+------+
| 10 |
| 10 |
+------+
2 rows in set (0.00 sec)
mysql> select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage where object_name='t3';
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| TABLE | a | t3 | idx_t3 | 4 | 4 | 4 |
| TABLE | a | t3 | idx_t3_name | 0 | 0 | 0 |
| TABLE | a | t3 | NULL | 0 | 0 | 0 |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
3 rows in set (0.01 sec)
mysql> select name from t3 where name='a';
Empty set (0.00 sec)
mysql> select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage where object_name='t3';
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| TABLE | a | t3 | idx_t3 | 4 | 4 | 4 |
| TABLE | a | t3 | idx_t3_name | 1 | 1 | 1 |
| TABLE | a | t3 | NULL | 0 | 0 | 0 |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
3 rows in set (0.01 sec)
mysql> select name from t3 where name='name1';
+-------+
| name |
+-------+
| name1 |
+-------+
1 row in set (0.01 sec)
mysql> select object_type,object_schema,object_name,index_name,count_star,count_read,COUNT_FETCH from performance_schema.table_io_waits_summary_by_index_usage where object_name='t3';
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| object_type | object_schema | object_name | index_name | count_star | count_read | COUNT_FETCH |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
| TABLE | a | t3 | idx_t3 | 4 | 4 | 4 |
| TABLE | a | t3 | idx_t3_name | 3 | 3 | 3 |
| TABLE | a | t3 | NULL | 0 | 0 | 0 |
+-------------+---------------+-------------+-------------+------------+------------+-------------+
3 rows in set (0.01 sec)
索引被使用之後， 對應的值會增加。
轉載請註明源出處
QQ 273002188 歡迎一起學習
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-1680455/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle中檢視某個表的索引是否有效Oracle索引
- 監控Oracle索引是否被使用？Oracle索引
- Linux中檢視指令碼程式是否存在的命令！Linux指令碼
- 跟蹤查詢DBLink遠端表是否使用到索引索引
- mysql 檢視索引MySql索引
- eclipse中檢視某個方法（函式）被誰呼叫Eclipse函式
- Mysql利用explain確認是否使用索引MySqlAI索引
- 在rhel4中檢視mysql的版本MySql
- mysql建立索引和檢視MySql索引
- Centos中檢視nginx、apache、php、mysql配置檔案路徑CentOSNginxApachePHPMySql
- oracle中檢視一張表是否有主鍵，主鍵在哪個欄位上Oracle
- mysql檢視錶建立的索引MySql索引
- 如何在MySQL中檢視binlog二進位制日誌？MySql
- linux中檢視oracle程式在做什麼，是否sql引起了鎖 kill掉程式LinuxOracleSQL
- MySQL null值欄位是否使用索引的總結MySqlNull索引
- Oracle中檢視無效的物件、約束、觸發器和索引（Helloblock寫作）Oracle物件觸發器索引BloC
- 檢視Linux埠是否被開放@[環境部署]Linux
- Oracle檢視歸檔是否被備庫應用Oracle
- 分析索引是否有效索引
- mysql 建立索引的方法--建立檢視MySql索引
- mysql的新建索引會導致insert被lockedMySql索引
- linux下檢視埠是否被佔用以及檢視所有埠Linux
- 使資料視窗中的被選中行具有更好的外觀
- Linux 中檢視 DNS 與 配置LinuxDNS
- 語法檢視錶結構，索引mysql索引MySql
- 故障案例：MySQL唯一索引有重複值，官方卻說This is not a bugMySql索引
- 分析索引是否變'壞'索引
- LINUX中如何檢視某個埠是否被佔用Linux
- Linux中如何檢視8080埠是否被佔用?Linux
- javascript中檢測變數是否存在時，最好使用typeofJavaScript變數
- javascript中檢測某個字串在陣列中是否存在JavaScript字串陣列
- ORACLE中檢視執行計劃(轉)Oracle
- Cookie 是否會被取代Cookie
- test是否被執行？
- LINUX中埠是否被佔用應該如何檢視呢？Linux
- JavaScript檢測window.open()彈出視窗是否被攔截JavaScript
- js檢測window.open()彈出視窗是否被關閉JS
- Linux下如何知道是否有人在使壞？Linux