首页 > 数据库 >mysql性能优化(索引)

mysql性能优化(索引)

时间:2024-09-19 16:15:44浏览次数:8  
标签:GROUP filesort len 索引 key mysql temporary 优化

mysql大部分性能的问题都出在索引上,因此索引优化是头等大事

1.最左匹配原则

这是索引基本的匹配原则,这里不多说,只要记住索引是要按顺序创建的,mysql是按顺序去b+树上查找的就行

2.Using temporary; Using filesort

往往出现这个都代表着不好的情况,通常是在groupby和orderby的情况下出现的。

  • "Using filesort"表示MySQL必须通过对结果集进行排序来满足GROUP BY操作的要求。这通常发生在没有适用于GROUP BY子句的索引或优化器选择不使用索引的情况下。

  • "Using temporary"表示MySQL需要创建一个临时表来处理GROUP BY操作。这通常发生在GROUP BY子句中包含了多个字段,或者在查询中使用了聚合函数(如SUM、COUNT等)。

一些常见的解决方法包括:

  • 确保相关列上存在适当的索引。
  • 优化查询以减少GROUP BY子句中的字段数量。
  • 考虑使用覆盖索引,即包含所有SELECT列表和GROUP BY子句的索引,以避免回表操作。
  • 减少数据量级,如果经过索引过滤后的数据只有几条,那么出现filesort和temporary是完全不用考虑的。

你以为的filesort这些特殊的标记一定会影响性能?其实并不是,我举个例子:

select a,b,c from t where a=1 and b<2 group by c;
  • 当我对a,b,c建了索引的时候,上面的sql是会出现temporary和filesort的(这是因为b是范围查询,导致后面c不生效)。
  • 而当我对a,c,b建了索引的时候,上面的sql是不会出现temporary和filesort的。
    但是。。。如果表里大部分数据都是b>2的,那么第一个索引a,b,c会比第二个索引a,c,b快很多很多,为什么呢?这得从索引执行逻辑来看,第一个索引先是根据a和b过滤了大部分数据,最后剩下1%数据进行temporary和filesort,这是无所谓的。第二个索引是根据a过滤完,还剩99%的数据,再对99%的数据根据c进行group by,尽管没有temporary和filesort,但是这量级太大了,远远没有第一个索引快。

所以,有时候我们看执行效率,不能光看执行计划extra的部分,还要看rows和filtered部分,如果rows很小,那么出现temporary和filesort也是无所谓的。

3.key_len

key_len是执行计划中会出现的一个关键词,这是大多数人会忽略的一个指标,实际上在某些场景下还挺有用,他能帮助我们判断mysql是否按自己的要求去执行索引了。比如上面那个sql。如果a,b走了索引,key_len为18,如果a,b,c都走了索引,则key_len为27.这是因为这里abc都是bigint类型的,所以长度为8,再加上由于default null,所以再加了一个长度。这个指标能帮助我们快速定位索引执行用到了哪些字段。

下面是长度参考表

|类型|长度|
|-|-|-|
|int|4|
|bigint|8|
|tinyint|1|
|smallint|2|
|mediumint|3|
|float |4|
|double |8|
|date |3|
|timestamp|4|
|datetime|5|

text,char,varchar等字符串类型的,需要根据字符集的不同来区分

|字符集|1个字符占用字节数(Maxlen)|
|-|-|-|
|GBK|2||
|UTF8|3||
|UTF8mb4|4||
|latin1|1||

额外的规则是:

  • 如果字段是default null的,长度要+1,如果是not null的则不用。
  • 如果varchar类型的,需要+2,因为这是这是变长的

所以在UTF8mb4字符集下,一个varchar(50) default null字段key_len的总长度就是50*4+2+1=203

标签:GROUP,filesort,len,索引,key,mysql,temporary,优化
From: https://www.cnblogs.com/leecoder5/p/18420827

相关文章

  • 正确创建索引,索引何时失效?
    正确的创建合适的索引,是提升数据库查询性能的基础。在正式讲解之前,对后面举例中使用的表结构先简单看一下:createtableuser(idbigintnotnullcomment'id'primarykey,namevarchar(200)nullcomment'name',agebigintnullcomment'age......
  • 全网最简单ubuntu18.04+mysql5.7+nginx+uwsgi一次性部署Django!!!!!
    ubuntu18.04,自带python3.6,mysql5.7 Nginx*******************************1.安装nginx apt-getupdate apt-getupgrade apt-getinstallnginx 2.修改端口为81(可选),是一个链接 /etc/nginx/sites-enabled/default3.servicenginxreload4.servicenginx......
  • MySQL强化篇指优化思路总结
    基础--连接退出数据库连接:mysql-h地址-P接口-u用户名-p密码退出:exit或者/q数据库操作关键字create创建数据库createdatabase数据库名如:createdatabasetestdefaultcharsetutf8关键字show查看当前有哪些数据库showdatabase;查看创建数据库的语句......
  • 云计算-k8s上使用定时任务配置elatiscdump和mysqldump
    现在很多中间件都是容器化部署到k8s平台上,例如elasticsearch和mysql。一般的商业产品k8s都有针对这些中间的备份功能,但是如果我们要对这些容器的化的中间件导出数据进行备份,可以采用k8s的定时任务来执行elasticdump定时任务elasitcdump是一款开源的ES数据迁移工具,国内码云地址 h......
  • 优化下载性能:使用Python多线程与异步并发提升下载效率
    文章目录......
  • 升讯威在线客服系统如何高性能同时支持 MySQL 和 SQL Server
    升讯威在线客服与营销系统是基于.netcore/WPF开发的一款在线客服软件,宗旨是:开放、开源、共享。努力打造.net社区的一款优秀开源产品。前段时间我发表了一系列文章,开始介绍基于.netcore的升讯威在线客服系统开发过程。随时Linux+MySQL的广泛应用,必须让系统同时支......
  • 关于Centos9下使用dnf安装mysql报错的解决
    报错内容如下:```MySQL8.0CommunityServer3.0MB/s|3.1kB00:00file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql-2022的GPG公钥(0x3A79BD29)已安装仓库"MySQL8.0CommunityServer"的GPG公钥已安装,但是不适用于此软件包。请检查此仓库的公钥URL是否配置正确。.失......
  • 分类预测 | Matlab实现SMA-CNN-SVM黏菌算法优化卷积支持向量机分类预测
    分类预测|Matlab实现SMA-CNN-SVM黏菌算法优化卷积支持向量机分类预测目录分类预测|Matlab实现SMA-CNN-SVM黏菌算法优化卷积支持向量机分类预测分类效果基本描述程序设计参考资料分类效果基本描述1.Matlab实现SMA-CNN-SVM黏菌算法优化卷积支持向量机分类预......
  • 多输入多输出 | Matlab实现DBO-BP蜣螂算法优化BP神经网络多输入多输出预测
    多输入多输出|Matlab实现DBO-BP蜣螂算法优化BP神经网络多输入多输出预测目录多输入多输出|Matlab实现DBO-BP蜣螂算法优化BP神经网络多输入多输出预测预测效果基本介绍程序设计往期精彩参考资料预测效果基本介绍多输入多输出|Matlab实现DBO-BP蜣螂算法优化BP神经网络多输入......
  • 顶刊算法 | Matlab实现鹈鹕算法POA-CNN-LSTM-Multihead-Attention多头注意力机制多变
    顶刊算法|Matlab实现鹈鹕算法POA-CNN-LSTM-Multihead-Attention多头注意力机制多变量时间序列预测,优化前后对比目录顶刊算法|Matlab实现鹈鹕算法POA-CNN-LSTM-Multihead-Attention多头注意力机制多变量时间序列预测,优化前后对比预测效果基本介绍程序设计参考资料预测效果基本......