mysql数据库查询实际扫描行数
例如我执行的语句是:
SELECT iss_dt AS date,SUM( user_num ) AS count FROM ads_cusprofile_et_info WHERE label_id = 'sex' AND iss_dt >= '20181228' AND iss_dt <= '20191228' GROUP BY iss_dt;
/* 打开optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on';
/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = 'Innodb_rows_read';
SELECT iss_dt AS date,SUM( user_num ) AS count FROM ads_cusprofile_et_info WHERE label_id = 'sex' AND iss_dt >= '20181228' AND iss_dt <= '20191228' GROUP BY iss_dt;
explain SELECT iss_dt AS date,SUM( user_num ) AS count FROM ads_cusprofile_et_info WHERE label_id = 'sex' AND iss_dt >= '20181228' AND iss_dt <= '20191228' GROUP BY iss_dt;
/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`;
/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';
/* 计算Innodb_rows_read差值 */
select @b-@a;