MySQL的索引单表优化案例分析

MySQL单表记录数过大的优化策略详解 分区表是将大表划分成多个小表,每个小表称为一个分区。可以根据时间、范围、列值等进行分区。根据数据库里面数据表的相关性进行拆分。例如,用户表中既有用户的登录信息又有用户的基本信息,可以将用户表拆分成两个单独的表,甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分,把一张列比较多的表拆分为多张表。水平拆分是指数据表行的拆分,表的行数超过200万行时,就会变慢,这时可以把一张的表的数据拆成多张表来存放。举个例子:我们可以将用户信息表拆分成多个用户信息表,这样就可以避免单。 阅读详情

贴个广告

楼主的博客已全部搬迁至自己的博客,感兴趣的小伙伴请移步haifeiWu与他朋友们的博客专栏

建表

  • 建立本次优化案例中所需的数据库及数据表
CREATE DATABASE db0206;
USE db0206;

CREATE TABLE `db0206`.`article`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `author_id` INT(11) UNSIGNED NOT NULL,
  `category_id` INT(11) UNSIGNED NOT NULL,
  `views` INT(11) UNSIGNED NOT NULL,
  `comments` INT(11) UNSIGNED NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8;


INSERT INTO `db0206`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (NULL, '1', '1', '1', '1', '1', '1');
INSERT INTO `db0206`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (NULL, '2', '2', '2', '2', '2', '2');
INSERT INTO `db0206`.`article` (`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (NULL, '3', '3', '3', '3', '3', '3');

单表索引分析

  • 下面我们来执行这条sql:查询category_id为1,且comments大于1的情况下,views最多的article_id
SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
  • 通过explain命令来查看sql查询优化信息
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
  • sql查询优化信息

sql查询分析

  • 结论:很显然type是ALL,即最坏情况。Extra里还出现Using filesort(文件内排序),也是最坏情况,所以优化是必须的。

开始优化

1.1新建索引+删除索引

  • 建立索引的SQL语句
CREATE INDEX idx_article_ccv ON article (category_id,comments,views);
  • 再次执行查询分析sql
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
  • 查询分析结果

第一次优化结果

  • 结论:
    type变成了range,这是可以忍受的。但是extra里使用了Using filesort 仍然是无法接受的。
    但是我们已经建立的索引,为啥没有用呢?
    这是因为按照BTree索引的工作原理
    先排序category_id,
    如果遇到相同的category_id则再排序comments,如果遇到相同的commetns则再排序views
    当comments字段在联合索引中处于中间位置时,
    因为comments > 1 条件是一个范围值(所谓的range),
    Mysql无法利用索引再对后面的views部分进行检索,即range类型查询字段后面索引无效。

1.2 第二次优化

  • 删除不合适的索引
DROP INDEX idx_article_ccv ON article;
  • 重新建立索引
CREATE INDEX idx_article_cv ON article(category_id,views);

-重新执行查询分析

EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
  • 查询分析结果
    优化成功

  • 结论:
    根据MySQL的查询分析报告可知,使用当前建立的索引,达到了type=ref,且extra中没有出现Using filesort,因此,我们现在使用的索引结构达到了最优的情况。

Mysql高级篇(中)—— 索引优化 MySQL索引失效的场景、案例分析及如何优化索引 阅读详情

相关推荐

MySQL 单表访问方法详解

mysql访问表数据的方法讲解

qq_43460315的博客 1319

MySQL - 单表数据量大表优化方案

MySQL - 单表数据量大表优化方案

Mr_WoLong 3551

信号完整性仿真SI之Cadence Sigrity SPEED2000_眼图仿真(四)

今天给大家分享一个仿真的操作流程,是关于Cadence Sigrity SPEED2000的时域波形仿真和眼图仿真。我们会经常看见硬件工程师调试示波器上的一些信号的波形变化,其实这些也可以通过仿真电路的搭建来做出投板前的仿真,来优化走线,以减少版本的迭代次数。如果要和实际的信号保持一致,就要求仿真设置的参数和实际的参数保持一致,这也是仿真的重点和难点。下面先让我们了解一下Cadence Sigrity SPEED2000时域仿真的操作流程: 1.仿真模式选择与文件导入 选择Cadence Sigrity下的

刘小同学的博客 1万+

记录一次mysql单表查询优化

mysql单表优化

maomao_dreamhigh的博客 472

高通量测序数据分析:RNA-seq

深度测序相关数据库与数据格式 SRA toolkit 一、NCBI 和EBI、DDBJ组成INSDC,数据内容相同所以找NCBI就行。 (一)NCBI常用数据库 GenBank:遗传序列数据库,收集了所有公开的DNA序列及其注释 GEO (Gene Expression Omnibus) :收集整理各种表达芯片数据,后来加入了甲基化、lncRNA、miRNA、CNV等其他芯片,还有高通量测序数据...

qq_41134363的博客 3万+

微信小程序内容安全审核:从API调用到工程化落地的全流程实践

内容安全是互联网应用开发中的基础性技术环节,尤其在用户生成内容(UGC)场景下,它直接关系到平台的合规性与用户体验。其核心原理通常结合了关键词过滤、自然语言处理(NLP)和计算机视觉(CV)等技术,通过AI模型对文本、图像、音频进行多维度风险识别。这项技术的价值在于,它不仅是满足监管要求的必要手段,更是构建健康社区生态、保护品牌声誉、实现产品长期稳定运营的基石,广泛应用于社交、电商、社区论坛等各类UGC平台。本文聚焦于微信小程序生态,深入剖析其官方提供的内容安全解决方案,详细解读如何将文本同步检测与多媒体异

weixin_28622215的博客 457

MySQL优化之--单表优化

MySQL单表优化 MySQL优化

贺浦力特的博客 1113

IntelliJ IDEA 2025.1 + Gradle 镜像配置避坑指南:解决依赖下载慢的终极方案

本文针对IntelliJ IDEA 2025.1开发环境中Gradle依赖下载缓慢的问题,提供了详细的镜像配置避坑指南。通过解析Gradle依赖解析机制,重点指导如何正确配置阿里云等国内镜像仓库,并分享验证配置生效、排查常见错误及高级优化技巧,旨在彻底解决依赖下载难题,提升开发效率。

weixin_28835551的博客 532

MySQL查询优化方案

mysql优化

一名后端程序猿 1889

HC-SR04测距不准?STM32F103定时器捕获模式详解与精度提升实战

本文详细解析了HC-SR04超声波测距模块在STM32F103上的精度问题,并提供了定时器输入捕获模式的实战配置方法。通过硬件级时间测量和温度补偿算法,将测距精度提升至±0.1cm,适用于工业级应用场景。文章包含完整的代码实现和测试对比数据,帮助开发者解决超声波测距不准的常见问题。

weixin_30313409的博客 352

816华为OD机试真题 新系统 - 电池充电时间计算 (Java/Py/C/C++/Js/Go)

816华为OD机试真题 新系统 - 电池充电时间计算 (Java/Py/C/C++/Js/Go)

算法大师! 479

单表优化查询速度的几种方法及案例

为什么要优化表,因为可变字段经常修改,会产生多余的空白段及碎片,如果使用delete语句删除记录,其实物理上也没有真的删除这条记录。你会发现删除后该数据文件的大小没变,MySQL为啥这样设计,就是当新增的时候新增记录回去填补那些被标记为删除的空间,因为新增记录并不会百分百填补碗删除记录的空间,也会产生空白段与碎片。3.如果是分页列表,要注意当数据量大时,如果该分页排序规则复杂,就算是前面100页翻页可能都会慢,更别说尾页了。1.数据库索引:是增加或者删除多余的索引,尽量使用联合索引。...

qq_41369135的博客 2925

芒果YOLO11改进146:OBB旋转检测头Head篇:即插即用|轻量化非对称多级压缩LOADH旋转检测头,原创改进适配YOLOv8高效检测头,检测头新颖改进,全网首发独一份

OBB旋转检测头Head篇:即插即用|轻量化非对称多级压缩LOADH旋转检测头,原创改进适配YOLOv8高效检测头,检测头新颖改进,全网首发独一份

包括YOLOv5、YOLOv7、YOLOv8等模型改进 647

MySQL高级之索引优化分析

目录 1、慢 SQL 2、join 查询 2.1、SQL 执行顺序 2.2、JOIN 连接查询 3、索引简介 3.1、索引是什么 3.2、索引原理 3.3、索引优劣势 3.4、MySQL 索引分类 3.5、MySQL 索引语法 3.6、MySQL 索引结构 3.7、何时需要建索引 4、性能分析 4.1、性能优化概述 4.2、Explain 概述 4.3、Explain 详解 5、索引优化 5.1、单表索引优化 5.2、两表索引优化 5.3、三表索引优化 6、索.

可小辉的博客 671

从零到多智能体:Google Agent开发套件(ADK)入门指南

ADK促进自定义工具生产的能力是其优势之一。本质上,工具只是一个带有描述性文档字符串的Python函数。

独立开发,substack顶级编辑 2495

jre9下载 jdk9

(Java SE Runtime Environment 9.0) 9.0u175 官方最新版 【x86|x64】

校园招聘之Mysql索引

详细文档 Mysql高级文档:https://pan.baidu.com/s/1XQQsDsCcrgcdM04dm_d-yw 提取码h0p7 1. 是什么 2. 存储位置 3. 优缺点 4. Mysql索引结构 4.1 Mysql两种引擎的索引实现 (1) MyISAM索引实现 (2) InnoDB索引实现 (3) 总结 5. 索引分类 ...

aozhu7951的博客 306

OV9734 DATASHEET,完整规格书

OV9734完整规格书,包括电气参数,尺寸规格,寄存器配置表等

MySQL单表优化

MySQL单表优化单表优化概述字段优化索引优化SQL语句优化引擎优化系统调优参数升级硬件 单表优化概述 当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以在以下几个方面进行优化 除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度, 一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。 而事实上很多时候MySQL单表的...

JustinNeil的博客 2632

Data-Structures-and-Algorithm-Analysis-in-Java.pdf

作者: Weiss, Mark A. 出版社: Addison Wesley Data Structures and Algorithm Analysis in Java is an “advanced algorithms” book that fits between traditional CS2 and Algorithms Analysis courses. In the old ACM Curriculum Guidelines, this course was known as CS7. This text is for readers who want to learn good programming and algorithm analysis skills simultaneously so that they can develop such programs with the maximum amo...

霍尼韦尔SoftMaster使用说明

霍尼韦尔SoftMaster使用说明

MySql优化(四)索引优化分析(案例分析)

写在前面: 我热爱技术,热爱分享,热爱生活, 我始终相信:技术是开源的,知识是共享的! 博客里面的内容大部分均为原创,是自己日常的学习记录和总结,便于自己在后面的时间里回顾,当然也是希望可以分享自己的知识。目前的内容几乎是基础知识和技术入门,如果你觉得还可以的话不妨关注一下,我们共同进步! 个人除了分享博客之外,也喜欢看书,写一点日常杂文和心情分享,如果你感兴趣,也可以关注关注! 微信公众号:傲骄鹿先生 ...

在路上 2790

mysql单表索引数量多少合适_【干货!!!】MySQL 大表优化方案

当MySQL单表记录数过大时,增删改查性能都会急剧下降,可以参考以下步骤来优化:单表优化除非单表数据未来会一直不断上涨,否则不要一开始就考虑拆分,拆分会带来逻辑、部署、运维的各种复杂度,一般以整型值为主的表在千万级以下,字符串为主的表在五百万以下是没有太大问题的。而事实上很多时候MySQL单表的性能依然有不少优化空间,甚至能正常支撑千万级以上的数据量:字段1、尽量使用TINYINT、SMALLIN...

weixin_39685578的博客 9605

MySQL单表、双表、三表优化

MySQL单表、双表、三表优化 学习了mysql中使用explain进行sql语句性能分析,总结一下mysql单表、双表、三表优化 在看这篇文章之前,应该要先理解好左连接、右连接等一些知识。(链接) 单表优化: 单表优化就要注意SQL语句是否能够正确利用到联合索引(即走不走索引) 例如: 建立联合索引 a_b_c_index(a,b,c) #查询a为xx值,b大于xx值 以c排序的最大一行数据 S...

qq_41797857的博客 565

mysql(6)慢查询优化、索引优化、以及表等优化总结

MySQL优化概述 MySQL数据库常见的两个瓶颈是:CPU和I/O的瓶颈。 CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候那么平瓶颈就会出现在网络上。 我们可以用mpstat, iostat, sar和vmstat来查看系统的性能状态。除了服务器硬件的性能瓶颈,对于MySQL系统本身...

cowbin2012的专栏 1万+

MySQL索引优化

这条语句,实际只有name会走索引,因为name是右侧模糊匹配,得到的结果是不确定的,name字段过滤完,得到的索引行里的age和position是无序的,无法很好的利用索引。而5.6引入索引下推后,上面的sql在获取到联合索引里匹配到名字是 ‘LiLei’ 开头的索引后,还会对age和position进行过滤,用过滤后剩下的索引对应的主键id再回表查整行数据。综上所述,对于order by的优化,尽量在索引列上完成排序,遵循索引建立(索引创建的顺序)时的最左前缀法则。命令,查看rows字段的值。

西岭千秋雪的博客 1585

MySQL单表过亿条数据,如何优化查询速度?

MySQL单表数据量过亿时,查询性能会急剧下降(全表扫描需遍历亿级数据,索引效率也可能因树高过大受影响),优化需从架构设计、索引策略、查询优化、存储层优化四个维度综合入手,具体方案如下:单表过亿的核心问题是「数据量超出MySQL高效处理范围」,优先通过拆分减少单表数据量:将一张大表按规则拆分为多个结构相同的小表,每张表数据量控制在千万级(MySQL单表千万级是性能拐点)。结合主从复制,将查询压力分流到从库:亿级表的索引设计需「精准命中查询,避免冗余索引」,否则索引维护成本(写入时更新索引)和查询时的索引遍历

长风破浪会有时的博客 1463

【MYSQL】mysql单表亿级数据查询优化处理

MySQL单表数据量过亿时性能下降明显,可通过合理使用索引进行优化。文章介绍了索引创建、查看和删除的SQL语法(CREATE INDEX/SHOW INDEX/ALTER TABLE DROP INDEX)。同时全面总结了MySQL聚合函数,包括基本聚合(COUNT/SUM/AVG/MIN/MAX)、高级统计(STD/VARIANCE)、分组函数(GROUP_CONCAT/WITH ROLLUP)和窗口函数(MySQL 8.0+的ROW_NUMBER等)。特别强调聚合函数与GROUP BY的配合使用、NUL

JAVA领域优质创作者,基于分片网络查询方法专利发明者。 1323
上一篇: Mysql的七种join
下一篇: Maven安装教程
whfstudio
博客等级 码龄10年 12粉丝 46原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值