线上MySQL数据库高负载的解决思路--再次论程序应用索引的重要性

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 前言:过去的笔记整理而得,未免丢失,发布个人博客。[2012年的资料笔记] 场景:数据库的负载飙升,CPU高达99%。查看进程。通过猜测推理,定位了一些select语句 363478427 | apps_read     | 192.
前言:过去的笔记整理而得,未免丢失,发布个人博客。[2012年的资料笔记]
场景:数据库的负载飙升,CPU高达99%。

查看进程。通过猜测推理,定位了一些select语句
363478427 | apps_read     | 192.168.1.113:48945 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '???')                                            | 
| 363478430 | apps_read     | 192.168.1.113:48948 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '??????')                                         | 
| 363478434 | apps_read     | 192.168.1.113:48952 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '?????????')                                      | 
| 363478437 | apps_read     | 192.168.1.113:48955 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '????????')                                       | 
| 363478462 | apps_read     | 192.168.1.113:48957 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '???????')                                        | 
| 363478500 | apps_read     | 192.168.1.113:48960 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '????')                                           | 
| 363478511 | apps_read     | 192.168.1.113:48963 | apps       | Query       |       0 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '??????')                                         | 
| 363478518 | apps_read     | 192.168.1.113:48964 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = 'T2??')                                           | 
| 363478535 | apps_read     | 192.168.1.113:48965 | apps       | Query       |       0 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '???')                                            | 
| 363478540 | apps_read     | 192.168.1.113:48968 | apps       | Query       |       1 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '??')                                             | 
| 363478613 | apps_read     | 192.168.1.113:48971 | apps       | Query       |       0 | Sending data                                                   | select * from category_doc_info
 where (doc_title = '???')                                            | 
| 363478630 | apps_read     | 192.168.1.113:48975 | apps       | Query       |       0 | Sending data                                                   | select *

查看此表的数据库表结构如下:
---------------------------------------------------------------------------------------------------------------------------------------------------------
| category_doc_info | CREATE TABLE `category_doc_info` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `category_id` int(10) unsigned NOT NULL COMMENT
  `doc_title` varchar(255) NOT NULL COMMENT 
  `category_show` tinyint(1) unsigned NOT NULL COMMENT
  `category_Coordinate` tinyint(1) unsigned NOT NULL default '2' 
  `category_order` tinyint(1) unsigned NOT NULL default '0' 
  PRIMARY KEY  (`id`),
  UNIQUE KEY `INDEX_SEARCH` (`category_id`,`doc_title`),
) ENGINE=InnoDB AUTO_INCREMENT=343502 DEFAULT CHARSET=utf8 | 
---------------------------------------------------------------------------------------------------------------------------------------------------------
发现只有一个组合索引。但是完全没有用到。
现实场景:都是where (doc_title = '???')  的语句

查看具体的一条SQL语句的执行计划,如下:
mysql> explain  select * from category_doc_info where (doc_title = '独出新裁');
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table             | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | category_doc_info | ALL  | NULL          | NULL | NULL    | NULL |  232717 | Using where | 
+----+-------------+-------------------+------+---------------+------+---------+------+--------+-------------+

可以发现都是全表查询,并且是高并发的访问上述语句。

经过分析,修改生产环境的表结构,如下:
| category_doc_info | CREATE TABLE `category_doc_info` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `category_id` int(10) unsigned NOT NULL,
  `doc_title` varchar(255) NOT NULL',
  `category_show` tinyint(1) unsigned NOT NULL,
  `category_Coordinate` tinyint(1) unsigned NOT NULL default '2',
  `category_order` tinyint(1) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `INDEX_SEARCH` (`category_id`,`doc_title`),     
  KEY `idx_category_title` (`doc_title`)                            //新添加的表索引
) ENGINE=InnoDB AUTO_INCREMENT=343502 DEFAULT CHARSET=utf8 |

让上述的程序应用走索引,数据库的负载恢复正常,性能恢复正常。 

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
11天前
|
数据库 索引
深入探索数据库索引技术:回表与索引下推解析
【10月更文挑战第15天】在数据库查询优化的领域中,回表和索引下推是两个核心概念,它们对于提高查询性能至关重要。本文将详细解释这两个术语,并探讨它们在数据库操作中的作用和影响。
36 3
|
11天前
|
数据库 索引
深入理解数据库索引技术:回表与索引下推详解
【10月更文挑战第23天】 在数据库查询性能优化中,索引的使用是提升查询效率的关键。然而,并非所有的索引都能直接加速查询。本文将深入探讨两个重要的数据库索引技术:回表和索引下推,解释它们的概念、工作原理以及对性能的影响。
30 3
|
17天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
1天前
|
缓存 NoSQL 数据库
运用云数据库 Tair 构建缓存为应用提速,完成任务得苹果音响、充电套装等好礼!
本活动将带大家了解云数据库 Tair(兼容 Redis),通过体验构建缓存以提速应用,完成任务,即可领取罗马仕安卓充电套装,限量1000个,先到先得。邀请好友共同参与活动,还可赢取苹果 HomePod mini、小米蓝牙耳机等精美好礼!
|
8天前
|
SQL Java 数据库连接
在Java应用中,数据库访问常成为性能瓶颈。连接池技术通过预建立并复用数据库连接,有效减少连接开销,提升访问效率
在Java应用中,数据库访问常成为性能瓶颈。连接池技术通过预建立并复用数据库连接,有效减少连接开销,提升访问效率。本文介绍了连接池的工作原理、优势及实现方法,并提供了HikariCP的示例代码。
21 3
|
8天前
|
存储 Java 关系型数据库
在Java开发中,数据库连接是应用与数据交互的关键环节。本文通过案例分析,深入探讨Java连接池的原理与最佳实践
在Java开发中,数据库连接是应用与数据交互的关键环节。本文通过案例分析,深入探讨Java连接池的原理与最佳实践,包括连接创建、分配、复用和释放等操作,并通过电商应用实例展示了如何选择合适的连接池库(如HikariCP)和配置参数,实现高效、稳定的数据库连接管理。
22 2
|
2天前
|
存储 关系型数据库 数据库
Postgres数据库BRIN索引介绍
BRIN索引是PostgreSQL提供的一种高效、轻量级的索引类型,特别适用于大规模、顺序数据的范围查询。通过存储数据块的摘要信息,BRIN索引在降低存储和维护成本的同时,提供了良好的查询性能。然而,其适用场景有限,不适合随机数据分布或频繁更新的场景。在选择索引类型时,需根据数据特性和查询需求进行权衡。希望本文对你理解和使用PostgreSQL的BRIN索引有所帮助。
6 0
|
8天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
40 0
|
8天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
35 0
|
24天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
57 3
Mysql(4)—数据库索引