Mysql如何确定哪些表和查询没有有效地使用索引？

Mysql如何确定哪些表和查询没有有效地使用索引？

有几个选项可以做到这一点:

1. Use the Slow Query Log.
2. Use the Sys Schema.
3. Use the Performance Schema directly.

Slow Query Log

慢速查询日志包括有关检查的行数和返回的行数的信息。这可用于查找为返回的行检查最多行的查询。

SET GLOBAL slow_query_log=1;
SET GLOBAL log_queries_not_using_indexes = 1,
SET GLOBAL long_query_time = 10000;

The Sys Schema

如果您安装了Sys模式(MySQL 5.7中的默认值)，有两个视图旨在帮助查找可能使用索引的查询和表:

sys.schema_tables_with_full_table_scans - 列出经历全表扫描的表，按扫描的行数排序。

select * from sys.schema_tables_with_full_table_scans;

sys.statements_with_full_table_scans - 列出完全不使用索引或无法使用“良好索引”的查询

select query,db,exec_count,total_latency,rows_examined,rows_examined_avg,rows_sent_avg,last_seen from sys.statements_with_full_table_scans order by rows_examined_avg desc limit 3;

The Performance Schema Directly

performance_schema.events_statements_summary_by_digest

这是sys.statements_with_full_table_scans视图使用的表。使用SUM_NO_INDEX_USED和SUM_NO_GOOD_INDEX_USED列，它们可用于查找不使用有效索引或根本不使用索引的规范化语句。此外，可以将列SUM_ROWS_EXAMINED与SUM_ROWS_AFFECTED和SUM_ROWS_SENT进行比较，以查看检查了多少行，而实际影响或返回了多少行。​

performance_schema.events_statements_current,

performance_schema.events_statements_history,

performance_schema.events_statements_history_long

这三个表包含实际执行的查询，还包含NO_INDEX_USED和SUM_NO_GOOD_INDEX_USED列，您可以使用它们来查找各个语句的执行情况。

范例： mysql 8 比 mysql 5.7 用户更好友，QUERY_SAMPLE_TEXT字段提供了详细具体的原始sql，而 5.7  query 字段无原始sql

执行耗时 top 10

mysql 8：查看执行耗时 top 10

select sys.foRmat_time(p.avg_timer_wait) as avg_time ,
s.rows_examined_avg,
s.rows_sent_avg,
s.exec_count,
s.no_index_used_count,
s.last_seen ,
s.digest,
p.QUERY_SAMPLE_TEXT
from sys.statements_with_full_table_scans s
join performance_schema.events_statements_summary_by_digest p on s.digest=p.digest
order by AVG_TIMER_WAIT desc
limit 10 ;

mysql5.7：查看执行耗时 top 10

select sys.foRmat_time(p.avg_timer_wait) as avg_time,
s.rows_examined_avg,
s.rows_sent_avg,
s.exec_count,
s.no_index_used_count,
s.last_seen ,
s.digest,
s.query
from sys.statements_with_full_table_scans s
join performance_schema.events_statements_summary_by_digest p on s.digest=p.digest
order by AVG_TIMER_WAIT desc
limit 10 ;


执行次数最多 top10

select schema_name,
digest_text,
count_star,
sys.format_time(sum_timer_wait) as sum_time,
sys.format_time(min_timer_wait) as min_time,
sys.format_time(avg_timer_wait) as avg_time,
sys.format_time(max_timer_wait) as max_time,
sys.format_time(sum_lock_time) as sum_lock_time,
sum_rows_affected,
sum_rows_sent,
sum_rows_examined
from `performance_schema`.`events_statements_summary_by_digest`
where schema_name is not null
order by `COUNT_STAR` desc
limit 10;