索引分析
<p><strong>索引树高（B+树）</strong></p>
<pre><code> SELECT
A.SPACE AS TBL_SPACEID,
A.TABLE_ID,
A.NAME AS TABLE_NAME,
FILE_FORMAT,
ROW_FORMAT,
SPACE_TYPE,
B.INDEX_ID,
B.NAME AS INDEX_NAME,
PAGE_NO,
B.TYPE AS INDEX_TYPE
FROM
information_schema.INNODB_SYS_TABLES A
LEFT JOIN information_schema.INNODB_SYS_INDEXES B ON A.TABLE_ID = B.TABLE_ID
WHERE
A.table_id = B.table_id
AND A.space != 0
AND A.NAME = 'qiwuiot/history_sensor';</code></pre>
<p><strong>查看冗余索引</strong></p>
<pre><code> SELECT
*
FROM
`performance_schema`.`table_io_waits_summary_by_index_usage`
WHERE
COUNT_STAR = 0
AND SUM_TIMER_WAIT = 0</code></pre>
<p><strong>查看性能较差的索引</strong></p>
<pre><code> SELECT
*
FROM
`performance_schema`.`table_io_waits_summary_by_index_usage`
ORDER BY
MAX_TIMER_WAIT DESC</code></pre>
<p><strong>查看某张表是否有不使用索引的情况</strong></p>
<pre><code> SELECT
*
FROM
`performance_schema`.`table_io_waits_summary_by_index_usage`
WHERE
OBJECT_NAME = 'table_name'
AND INDEX_NAME IS NULL</code></pre>