Percona 5.5定位未使用的索引

╰半橙微兮° 2024-04-18 00:48 10阅读 0赞

打开userstat参数，然后让MySQL运行一段时间。  
之后可以在INFORMATION\_SCHEMA.INDEX\_STATISTICS表中查询到索引的使用频率。

1.  mysql> show global variables like '%users%';  
    
2.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
3.  | Variable\_name | Value |  
    
4.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
5.  | userstat | OFF |  
    
6.  \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
7.  1 row in set (0.00 sec)  
    
8.    
    
9.  mysql> select \* from information\_schema.index\_statistics;  
    
10. Empty set (0.00 sec)  
    
11.   
    
12. mysql> set global userstat=1;  
    
13. Query OK, 0 rows affected (0.00 sec)  
    
14.   
    
15. mysql> show global variables like '%users%';  
    
16. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
17. | Variable\_name | Value |  
    
18. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
19. | userstat | ON |  
    
20. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+  
    
21. 1 row in set (0.00 sec)  
    
22.   
    
23. mysql> select \* from information\_schema.index\_statistics;  
    
24. Empty set (0.00 sec)  
    
25.   
    
26. mysql> explain select \* from emp where hiredate > '1982-01-01' and deptno = 20;  
    
27. \+\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
28. | id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |  
    
29. \+\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
30. | 1 | SIMPLE | emp | range | idx\_date\_sal\_job,idx\_date | idx\_date\_sal\_job | 4 | NULL | 3 | Using where |  
    
31. \+\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\+  
    
32. 1 row in set (0.00 sec)  
    
33.   
    
34. mysql> select \* from information\_schema.index\_statistics;  
    
35. Empty set (0.00 sec)  
    
36.   
    
37. mysql> select \* from emp where hiredate > '1982-01-01' and deptno = 20;  
    
38. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
39. | empno | ename | job | mgr | hiredate | sal | comm | deptno |  
    
40. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
41. | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000 | NULL | 20 |  
    
42. | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100 | NULL | 20 |  
    
43. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
44. 2 rows in set (0.00 sec)  
    
45.   
    
46. mysql> select \* from information\_schema.index\_statistics;  
    
47. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
48. | TABLE\_SCHEMA | TABLE\_NAME | INDEX\_NAME | ROWS\_READ |  
    
49. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
50. | test | emp | idx\_date\_sal\_job | 3 |  
    
51. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
52. 1 row in set (0.04 sec)  
    
53.   
    
54. mysql> select \* from emp;  
    
55. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
56. | empno | ename | job | mgr | hiredate | sal | comm | deptno |  
    
57. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
58. | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |  
    
59. | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |  
    
60. | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |  
    
61. | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |  
    
62. | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |  
    
63. | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |  
    
64. | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |  
    
65. | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000 | NULL | 20 |  
    
66. | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |  
    
67. | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |  
    
68. | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100 | NULL | 20 |  
    
69. | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |  
    
70. | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |  
    
71. | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |  
    
72. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
73. 14 rows in set (0.00 sec)  
    
74.   
    
75. mysql> select \* from information\_schema.index\_statistics;  
    
76. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
77. | TABLE\_SCHEMA | TABLE\_NAME | INDEX\_NAME | ROWS\_READ |  
    
78. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
79. | test | emp | PRIMARY | 14 |  
    
80. | test | emp | idx\_date\_sal\_job | 3 |  
    
81. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
82. 2 rows in set (0.00 sec)  
    
83.   
    
84. mysql> select \* from emp where hiredate > '1982-01-01' and deptno = 20;  
    
85. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
86. | empno | ename | job | mgr | hiredate | sal | comm | deptno |  
    
87. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
88. | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000 | NULL | 20 |  
    
89. | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100 | NULL | 20 |  
    
90. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
91. 2 rows in set (0.00 sec)  
    
92.   
    
93. mysql> select \* from emp;  
    
94. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
95. | empno | ename | job | mgr | hiredate | sal | comm | deptno |  
    
96. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
97. | 7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 | NULL | 20 |  
    
98. | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300 | 30 |  
    
99. | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500 | 30 |  
    
100. | 7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 | NULL | 20 |  
    
101. | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 | 30 |  
    
102. | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 | NULL | 30 |  
    
103. | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 | NULL | 10 |  
    
104. | 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000 | NULL | 20 |  
    
105. | 7839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000 | NULL | 10 |  
    
106. | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0 | 30 |  
    
107. | 7876 | ADAMS | CLERK | 7788 | 1983-01-12 00:00:00 | 1100 | NULL | 20 |  
    
108. | 7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 | NULL | 30 |  
    
109. | 7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 | NULL | 20 |  
    
110. | 7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 | NULL | 10 |  
    
111. \+\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\+  
    
112. 14 rows in set (0.00 sec)  
    
113.   
    
114. mysql> select \* from information\_schema.index\_statistics;  
    
115. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
116. | TABLE\_SCHEMA | TABLE\_NAME | INDEX\_NAME | ROWS\_READ |  
    
117. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
118. | test | emp | PRIMARY | 28 |  
    
119. | test | emp | idx\_date\_sal\_job | 6 |  
    
120. \+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\+\-\-\-\-\-\-\-\-\-\-\-\+  
    
121. 2 rows in set (0.00 sec)

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

转载于:http://blog.itpub.net/26506993/viewspace-2150859/