mysql索引无效且sending data耗时巨大原因分析

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介:   一朋友最近新上线一个项目,本地测试环境跑得好好的,部署到线上却慢得像蜗牛一样。后来查询了一下发现一个sql执行了16秒,有些长的甚至80秒。本地运行都是毫秒级别的查询。下面记录一下困扰了两天的,其中一条sql的优化。

  一朋友最近新上线一个项目,本地测试环境跑得好好的,部署到线上却慢得像蜗牛一样。后来查询了一下发现一个sql执行了16秒,有些长的甚至80秒。本地运行都是毫秒级别的查询。下面记录一下困扰了两天的,其中一条sql的优化。

  表结构及现象描述:

CREATE TABLE `wp_goods` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_openid` varchar(255) NOT NULL DEFAULT '',
  `description` longtext ,
  `upset_price` decimal(10,2) DEFAULT NULL ,
  `reference_price` decimal(10,2) DEFAULT NULL ,
  `offer_unit` decimal(10,2) DEFAULT NULL ,
  `end_time` int(11) DEFAULT NULL ,
  `type` tinyint(4) DEFAULT NULL ,
  `is_bail` tinyint(4) DEFAULT NULL ,
  `is_express` tinyint(4) DEFAULT NULL ,
  `is_return` tinyint(4) DEFAULT NULL ,
  `createtime` int(11) DEFAULT NULL ,
  `is_sell` tinyint(4) DEFAULT NULL ,
  `is_draft` tinyint(1) NOT NULL DEFAULT '1' ,
  `scan_count` int(11) NOT NULL ,
  `title` varchar(255) NOT NULL ,
  `is_trash` tinyint(1) NOT NULL DEFAULT '1' ,
  `countdown` smallint(6) NOT NULL DEFAULT '0' ,
  `bail_money` tinyint(4) NOT NULL DEFAULT '0' ,
  `cat_id` tinyint(4) NOT NULL,
  `sort` int(10) unsigned NOT NULL DEFAULT '1' ,
  PRIMARY KEY (`id`),
  KEY `cat_id` (`cat_id`),
  KEY `index_id_user_openid` (`id`,`user_openid`) USING BTREE,
  KEY `index_user_openid` (`user_openid`) USING BTREE,
  KEY `index_id` (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=10094 DEFAULT CHARSET=utf8;

CREATE TABLE `sys_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `openid` varchar(50) DEFAULT NULL,
  `nickname` varchar(20) DEFAULT NULL,
  `sex` char(255) DEFAULT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `country` varchar(10) DEFAULT NULL,
  `province` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `headimgurl` varchar(200) DEFAULT NULL,
  `createtime` varchar(20) DEFAULT NULL,
  `is_subject` tinyint(4) NOT NULL DEFAULT '1' ,
  `black` tinyint(4) NOT NULL DEFAULT '1' ,
  `wd_sort` smallint(5) unsigned DEFAULT '1000' ,
  `wp_sort` smallint(5) unsigned NOT NULL DEFAULT '1000' ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `openid` (`openid`)
) ENGINE=MyISAM AUTO_INCREMENT=14044 DEFAULT CHARSET=utf8;

CREATE TABLE `jd_jianding` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `expert_id` int(11) DEFAULT NULL ,
  `gid` int(11) DEFAULT NULL ,
  `goods_value` varchar(50) DEFAULT NULL ,
  `result` varchar(500) DEFAULT NULL ,
  `jdtime` int(11) DEFAULT NULL ,
  `is_essence` tinyint(4) NOT NULL DEFAULT '0' ,
  `istrue` tinyint(4) DEFAULT '0' ,
  `wid` int(11) DEFAULT '0',
  `scan_num` int(11) DEFAULT '0' ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`gid`),
  KEY `index_wid` (`wid`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=9142 DEFAULT CHARSET=utf8;

  表wp_goods数据量10094,sys_users数据量14044, jd_jianding数据量9142。

  执行sql:

SELECT 
  `g`.`id`,
  `g`.`title`,
  `g`.`upset_price`,
  `u`.`nickname`,
  `j`.`istrue` 
FROM
  `wp_goods` `g` 
  LEFT JOIN `sys_users` `u` 
    ON g.user_openid = u.openid 
  LEFT JOIN `jd_jianding` `j` 
    ON g.id = j.wid 
ORDER BY `g`.`id` DESC 
LIMIT 6 ;

  耗时16秒,而本地数据库执行耗时0.02毫秒。

  原因分析:

  1、explain/desc 发现left join索引不起作用。

explain SELECT 
  `g`.`id`,
  `g`.`title`,
  `g`.`upset_price`,
  `u`.`nickname`,
  `j`.`istrue` 
FROM
  `wp_goods` `g` 
  LEFT JOIN `sys_users` `u` 
    ON g.user_openid = u.openid 
  LEFT JOIN `jd_jianding` `j` 
    ON g.id = j.wid 
ORDER BY `g`.`id` DESC 
LIMIT 6 ;

  分析结果:

id    select_type    table    partitions    type    possible_keys    key    key_len    ref    rows    filtered    Extra
1    SIMPLE    g    \N    ALL    \N    \N    \N    \N    10093    100.00    Using temporary; Using filesort
1    SIMPLE    u    \N    ref    openid    openid    153    mydb.g.user_openid    10    100.00    Using where
1    SIMPLE    j    \N    ALL    index_wid    \N    \N    \N    7975    100.00    Using where; Using join buffer (Block Nested Loop)

  索引无效,Using join buffer (Block Nested Loop)相当于遍历表查询。

  2、profile分析了下,发现几乎所有耗时都在sending data且缓存sending cached result to clien没开启。

  show variables like '%cache%';

  query_cache_type为off,在配置文件/etc/my.cf中添加“query_cache_type = 1”配置项并重启。

  执行后耗时10s,如果将order by去掉后耗时3秒。即使是耗时3秒也是无法接受的。

  通过profile分析下具体耗时

SHOW VARIABLES LIKE '%profil%'
SET profiling = 1;

SELECT 
  `g`.`id`,
  `g`.`title`,
  `g`.`upset_price`,
  `u`.`nickname`,
  `j`.`istrue` 
FROM
  `wp_goods` `g` 
  LEFT JOIN `sys_users` `u` 
    ON g.user_openid = u.openid 
  LEFT JOIN `jd_jianding` `j` 
    ON g.id = j.wid 
ORDER BY `g`.`id` DESC 
LIMIT 6 ;

show profile for query 1;

  

  发现几乎所有耗时都在sending data部分。

  3、查看jd_jianding表索引,show index from jd_jianding发现cardinality的值为1。

  

Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment
jd_jianding    0    PRIMARY    1    id    A    7975    \N    \N        BTREE        
jd_jianding    0    uk_name    1    gid    A    \N    \N    \N    YES    BTREE        
jd_jianding    1    index_wid    1    wid    A    1    \N    \N    YES    BTREE    

  4、优化表jd_jianding,analyze table jd_jianding,再次执行仍然如此。

  然而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 查看其值

  5、查看表jd_jianding字段wid的值全为默认值0,于是将其中一条记录的wid字段值update为非0;再次analyze table jd_jianding。

  再次执行,效果杠杠的,耗时只有0.02毫秒。困扰两天的问题终于得到了解决。

  6、把步骤4修改的字段值还原回来。

 

  后记,原因大致如下:

1、mysql没有开启查询缓存。
2、新添加字段默认值都一样,导致索引不可用。

 

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
打赏
0
0
0
0
2
分享
相关文章
【Mysql】耗时7200秒整理的mysql笔记!常用API汇总!包教包会!
⭐Hi~ o( ̄▽ ̄)ブ大家好我是雪人,今天分享出我的Mysql整理笔记!⭐耗时7200秒整理的资料直接拿走!⭐适合新手入门与API查阅观看!⭐本文包含以下内容:Mysql安装,SQL语句分类汇总,约束,mysql数据类型,事务,忘记ROOT密码等…
129 1
mysql性能优化:单表1400w查询最后十条数据(耗时0.036s)
看几个关键字段,type、key、extra,不算完美,但也还行,毕竟我们这种非DBA选手,sql能力有限 顺便科普下这个执行计划,看id列,1 1 2,执行顺序是第三行 第一行 第二行,记住口诀:id不同大的先走,id相同,从上往下
1516 0
mysql性能优化:单表1400w查询最后十条数据(耗时0.036s)
MySQL的Slow_log如何记录SQL的MDL锁耗时
MySQL的Slow_log如何记录SQL的MDL锁耗时
1619 0
MySQL的Slow_log如何记录SQL的MDL锁耗时
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
缓存与数据库的一致性方案,Redis与Mysql一致性方案,大厂P8的终极方案(图解+秒懂+史上最全)
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
183 42
MySQL生产环境迁移至YashanDB数据库深度体验
这篇文章是作者将 MySQL 生产环境迁移至 YashanDB 数据库的深度体验。介绍了 YashanDB 迁移平台 YMP 的产品相关信息、安装步骤、迁移中遇到的各种兼容问题及解决方案,最后总结了迁移体验,包括工具部署和操作特点,也指出功能有优化空间及暂不支持的部分,期待其不断优化。
如何排查和解决PHP连接数据库MYSQL失败写锁的问题
通过本文的介绍,您可以系统地了解如何排查和解决PHP连接MySQL数据库失败及写锁问题。通过检查配置、确保服务启动、调整防火墙设置和用户权限,以及识别和解决长时间运行的事务和死锁问题,可以有效地保障应用的稳定运行。
142 25
云数据库:从零到一,构建高可用MySQL集群
在互联网时代,数据成为企业核心资产,传统单机数据库难以满足高并发、高可用需求。云数据库通过弹性扩展、分布式架构等优势解决了这些问题,但也面临数据安全和性能优化挑战。本文介绍了如何从零开始构建高可用MySQL集群,涵盖选择云服务提供商、创建实例、配置高可用架构、数据备份恢复及性能优化等内容,并通过电商平台案例展示了具体应用。
数据库数据恢复——MySQL简介和数据恢复案例
MySQL数据库数据恢复环境&故障: 本地服务器,安装的windows server操作系统。 操作系统上部署MySQL单实例,引擎类型为innodb,表空间类型为独立表空间。该MySQL数据库没有备份,未开启binlog。 人为误操作,在用Delete命令删除数据时未添加where子句进行筛选导致全表数据被删除,删除后未对该表进行任何操作。
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等