⑩③【MySQL】详解SQL优化

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云数据库 RDS MySQL,高可用系列 2核4GB
简介: ⑩③【MySQL】详解SQL优化


⑩③【MySQL】了解并掌握SQL优化


1. 插入数据 优化

insert优化

  • ⚪使用批量插入


  • 手动提交事务(每次SQL语句执行后事务自动提交,手动提交避免了多次提交,提升效率)


  • ⚪使用主键顺序插入(顺序比乱序速度更快,性能更高)



大批量插入数据

如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入。操作如下:

# (命令行)客户端连接数据库时,加上参数: --local-infile
mysql --local-infile -u root -p
-- 查看从本地加载文件导入数据的开关是否开启
select @@local_infile;
-- 设置全局参数local_infile为1,表示开启从本地加载文件导入数据的开关。
set global local_infile=1;
-- 执行load指令将准备好的数据,加载到表结构中
-- 加载文件: /root/sql.log 中的数据插入表
-- 字段间使用 逗号',' 分隔
-- 行间使用 换行'\n' 分隔
load data local infile '/root/sql.log' into table `表名` fields terminated by ',' lines terminated by '\n';




2. 主键优化

数据组织方式

  • 在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table 简称IOT)。


页分裂

  • 页可以为空,也可以填充一半,也可以填充100%。每个页包含了至少2行数据(如果一行数据多大,会行溢出),根据主键排列。

分裂后插入↓

重新设置指针↓


页合并

  • 当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
  • 当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用

合并↓


主键设计原则

  • 主键设计原则:
  • ①在满足业务需求的情况下,尽量降低主键的长度
  • ②插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
  • 尽量不要使用UUID做为主键或者作为其他自然主键,如身份证号。
  • ④在业务操作时,尽量避免对主键的修改




3. order by 排序优化

order by 优化

①. Using filesort : 通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

②. Using index : 通过有序索引顺序扫描直接返回有序数据,这种情况即为using index,.不需要额外排序,操作效率高。

排序效率:Using index > Using filesort


  • order by优化策略:
  • ①根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
-- 没有建立索引时,排序性能为:`Using filesort`
explain select id,age,phone from tb_user order by age,phone;
-- 为排序字段建立合适索引
create index idx_age_phone_aa on tb_user(age,phone);
-- 等价于:
create index idx_age_phone_aa on tb_user(age asc,phone asc);
-- 建立索引后,排序性能提升为:`Using index`
  • ②尽量使用覆盖索引,非覆盖索引需要回表查询,会从Using index 变为 Using filesort。
  • ③多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC\DESC)。
-- 一个升序一个降序
select id,age,phone from tb_user order by age asc,phone desc;
-- 注意联合索引在创建时的规则
create index idx_age_phone_ad on tb_user(age asc,phone desc);

  • 如果不可避免地出现filesort,大数据量排序时,可以适当增大排序缓冲区sort_buffer_size的大小(默认256k)。
-- 查看参数sort_buffer_size大小
show variables like 'sort_buffer_size';
-- 设置参数sort_buffer_size大小
set sort_buffer_size=自定义的大小;




4. group by 分组优化

  • 根据分组字段建立合适的索引来提高效率。
  • 分组操作时,多字段通过联合索引排序也是遵循最左前缀法则的。
-- 如何建立合适索引:建议使用联合索引,可参考上文的order by优化




5. limit 分页优化

一个常见又非常头疼的问题就是大数据量的分页,如:limit2000000,10,此时需要MySQL排序前2000010记录,仅仅返回2000000到2000010的记录,其他记录丢弃,查询排序的代价非常大。

  • 优化策略
  • 一般分页查询时,通过建立覆盖索引能够较好提升性能,可通过覆盖索引+子查询形式进行优化。
-- 优化前:
select * from tb_sku limit 2000000,10;
-- 优化后
-- 子查询的id字段存在主键索引,order by性能得到优化
-- 根据子查询的到的主键字段id进行查询,效率高。
select s.* from 
tb_sku s,(select id from tb_sku order by id limit 2000000,10) i
where s.id = i.id;




6. count 优化

count()

  • count()是一个聚合函数,对于返回的结果集,一行行地判断,如果count()函数的参数不是NULL,累计值就加1,否则不加,最后返回累计值。(只记录不为NULL的记录)
  • **用法:**count(*)、count(主键)、count(字段)、count(1)
  • count(*)
  • InnoDB引擎并不会把全部字段取出来,而是专门做了优化不取值,服务层直接按行进行累加

  • count(主键)
  • InnoDB引擎会遍历整张表,把每一行的主键id值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为NULL)。

  • count(字段)
  • **没有not null约束:**InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。
  • **有not null约束:**InnoDB引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加。

  • count(1)
  • lnnoDB引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加。





7. update 更新优化

需要优化的问题

  • InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,更新没有索引的记录或索引失效,使用的锁会从行锁变为表锁。
  • 使用表锁会使并发性能下降,所以应当经可能去更新 使用了索引的字段。




相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
MySQL慢查询优化、索引优化、以及表等优化详解
本文详细介绍了MySQL优化方案,包括索引优化、SQL慢查询优化和数据库表优化,帮助提升数据库性能。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
MySQL慢查询优化、索引优化、以及表等优化详解
|
5天前
|
SQL 缓存 监控
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略,系统性能优化必知必备,大厂面试高频。关注【mikechen的互联网架构】,10年+BAT架构经验倾囊相授。
大厂面试高频:4 大性能优化策略(数据库、SQL、JVM等)
|
14天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
59 10
|
11天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
36 3
|
13天前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考,优化 SQL Server 数据库性能得从多个方面着手,包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术,可以在数据量增大时保持较好的性能。同时,定期进行数据库维护和清理,保证数据库高效运行。
|
14天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
37 1
|
15天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
77 1
|
21天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
48 5
|
16天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
47 0
|
18天前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
32 0