近期系统上线，新增加一个报表到生产环境，报表建立好之后在测试环境运行正常，1s内可以出现结果，但到生产环境后发现此报表需要进1000s才能出现，下面针对如何解决：
执行explain sql命令进行分析，具体如下：
通过explain可以看到w表实际是有索引，但是在做关联查询时并未走索引，而是走的全表扫描。
由于此次不知道什么原因造成不走索引，因此将以上sql实际执行完毕，得到以下结果，可以看到sending data话费了约70%的时间。
sending data步骤从字面意思可能我们都理解为从服务器发送数据到客户端，但实际并不是此意思。
查询一下“Sending data”状态的含义，原来这个状态的名称很具有误导性，所谓的“Sending data”并不是单纯的发送数据，而是包括“收集 + 发送 数据”。
这里的关键是为什么要收集数据，原因在于：mysql使用“索引”完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。
后面通过网上查询方法告知有可能是查询缓存未开启，经过确认mysql 8版本以后无此功能，通过以下命令可以确认。
mysql> SHOW VARIABLES LIKE '%cache%';
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_stmt_cache_size | 32768 |
| have_query_cache | NO |
| host_cache_size | 1403 |
可以看到have_query_cache 字段为NO，表名不支持查询缓存功能，因此与此无关系。
解决方案1：升级服务器配置
由于本查询数据量较大，服务器内存较小，怀疑是内存问题，当天晚上升级服务器配置，并修改数据库buffer配置及相关临时内存及空间配置。
具体如下：
max_heap_table_size= 268435456
tmp_table_size = 536870912
innodb_buffer_pool_size = 12G
重启服务器和数据库服务器后，查询瞬间变快，认为已经解决了此问题，但是实际并未解决，下面的步骤才是解决问题的最后方法。
解决方法二：优化配置
由于采用升级服务器内容并未解决问题，第二天继续分析并查找网上资料，通过网上一篇文章与本次现象一致，按照此文章顺利解决了问题。
先通过 show index from t_dat_waybill 查看表的索引情况，具体如下：由于原有的图navicat卡死未能正常保存，实际cardinality中字段为1。
然后采用文章中ANALYZE TABLE t_dat_waybill命令，后通过show index from t_dat_waybill 命令无1的结果。此次执行查询视图命令瞬间在1s内出现，至此成功解决问题。
以下为此命令的解释说明，供大家参考。
然而mysql的文档时这么说的。The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing
大意如下：
1)、它代表的是索引中唯一值的数目的估计值。如果是myisam引擎，这个值是一个准确的值。如果是innodb引擎，这个值是一个估算的值，每次执行show index 时，可能会不一样
2)、创建Index时(primary key除外)，MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数；
3)、值的大小会影响到索引的选择
4)、创建Index时，MyISAM的表Cardinality的值为null，InnoDB的表Cardinality的值大概为行数。
5)、可以通过Analyze table来更新一张表或者mysqlcheck -Aa来进行更新整个数据库
6)、可以通过 show index 查看其值
此文章中部分内容和解决步骤参考此文章：mysql索引无效且sending data耗时巨大原因分析 - 秋楓 - 博客园 (cnblogs.com)
文章评论