官方mysql中查看索引是否被使用到

以你之姓@ 2022-07-18 00:28 175阅读 0赞

http://blog.sina.com.cn/s/blog\_5037eacb0102vm5c.html

官方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';

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

| 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;

\+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

\+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+

| 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';

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

| 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';

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

| 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 |

\+------+

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';

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

| 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';

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

| 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';

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

\+-------------+---------------+-------------+-------------+------------+------------+-------------+

| 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 欢迎一起学习