MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结

简介: MySQL数据库——SQL优化(3/3)-limit 优化、count 优化、update 优化、SQL优化 小结

limit 优化

在数据量比较大时,如果进行limit分页查询,在查询时,越往后,分页查询效率越低。



当在进行分页查询时,如果执行limit 2000000,10,此时需要MySQL排序前2000010条记

录,仅仅返回 2000000 ~ 2000010 的记录,其他记录丢弃,查询排序的代价非常大。


优化思路

一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查

询形式进行优化。

使用覆盖索引:

select id from tb_sku order by id limit 2000000,10;
 
-- 使用到了主键索引,效率高

加上子查询:(当作两张表进行查询)

1. select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a
2. where t.id = a.id;

count 优化

概述

在之前的测试中,我们发现,如果数据量很大,在执行count操作时,是非常耗时的。


MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行 count(*) 的时候会直接返回这个数,效率很高; 但是如果是带条件的count,MyISAM引起也慢。


InnoDB 引擎就麻烦了,它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。


如果说要大幅度提升InnoDB表的count效率,主要的优化思路:自己计数,自己维护计数变量(可以借助于redis这样的数据库进行,但是如果是带条件的count又比较麻烦了)。

count用法

count() 是一个聚合函数,对于返回的结果集,一行行地判断,如果 count 函数的参数不是NULL,累计值就加 1,否则不加,最后返回累计值。

用法:count(*)、count(主键)、count(字段)、count(数字) image.png

按照效率排序的话,count(字段) < count(主键 id) < count(1) ≈ count(*),所以,尽量使用 count(*)。

update 优化

我们主要需要注意一下update语句执行时的注意事项。

update course set name = 'SpringBoot' where name = 'PHP' 

当我们开启多个事务,在执行上述的SQL时,我们发现行锁升级为了表锁。

导致该update语句的性能大大降低。

InnoDB的行锁是针对索引加的锁,不是针对记录加的锁 ,并且该索引不能失效,否则会从行锁升级为表锁 。

SQL优化 小结

插入数据

  • insert:批量插入、手动控制事务、主键顺序插入
  • 大批量插入: load data local infile

主键优化


  • 主键长度尽量短,顺序插入(否则会造成页分裂)         AUTO_INCREMENT        UUID   主键不使用uuid

order by 优化

  • using index:直接通过索引返回数据,性能高
  • using filesort:需要将返回的结果在排序缓冲区排序

group by 优化

  • 使用索引,多字段分组满足最左前缀法则

limit 优化

  • 覆盖索引+子查询

count 优化

  • 性能:count(字段) count(主键id) count(1) count(*)

update 优化

  • 尽量根据主键/索引字段进行数据更新



END



相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。 &nbsp; 相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/mysql&nbsp;
目录
相关文章
|
SQL 存储 监控
SQL日志优化策略:提升数据库日志记录效率
通过以上方法结合起来运行调整方案, 可以显著地提升SQL环境下面向各种搜索引擎服务平台所需要满足标准条件下之数据库登记作业流程综合表现; 同时还能确保系统稳健运行并满越用户体验预期目标.
596 6
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
1114 9
|
缓存 关系型数据库 BI
使用MYSQL Report分析数据库性能(下)
使用MYSQL Report分析数据库性能
785 158
|
关系型数据库 MySQL 数据库
自建数据库如何迁移至RDS MySQL实例
数据库迁移是一项复杂且耗时的工程,需考虑数据安全、完整性及业务中断影响。使用阿里云数据传输服务DTS,可快速、平滑完成迁移任务,将应用停机时间降至分钟级。您还可通过全量备份自建数据库并恢复至RDS MySQL实例,实现间接迁移上云。
|
关系型数据库 MySQL 数据库
阿里云数据库RDS费用价格:MySQL、SQL Server、PostgreSQL和MariaDB引擎收费标准
阿里云RDS数据库支持MySQL、SQL Server、PostgreSQL、MariaDB,多种引擎优惠上线!MySQL倚天版88元/年,SQL Server 2核4G仅299元/年,PostgreSQL 227元/年起。高可用、可弹性伸缩,安全稳定。详情见官网活动页。
2025 152
|
关系型数据库 MySQL 数据库
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎
阿里云数据库RDS支持MySQL、SQL Server、PostgreSQL和MariaDB引擎,提供高性价比、稳定安全的云数据库服务,适用于多种行业与业务场景。
1405 156
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(中)
使用MYSQL Report分析数据库性能
905 156
|
缓存 监控 关系型数据库
使用MYSQL Report分析数据库性能(上)
最终建议:当前系统是完美的读密集型负载模型,优化重点应放在减少行读取量和提高数据定位效率。通过索引优化、分区策略和内存缓存,预期可降低30%的CPU负载,同时保持100%的缓冲池命中率。建议每百万次查询后刷新统计信息以持续优化
938 161

推荐镜像

更多