当前位置: 首页 > news >正文

【示例】MySQL-SQL语句优化

前言

本文主要讲述不同SQL语句的优化策略。

SQL | DML语句

insert语句

  • 插入数据的时候,改为批量插入

  • 插入数据的时候,按照主键顺序插入

  • 大批量插入数据的时候(百万),用load指令,从本地文件载入(需要在全局变量中开启,从而允许load指令)

update

注意更新语句的检索条件,尽量选择有索引的列;尽量选择主键索引。

  • 当使用索引的时候:InnoDB引擎下执行update语句,添加的是行锁;当使用主键索引的时候,能减少回表查询
# 该事务下使用的锁是:行锁
update course set name = 'javaEE' where id = 1;
  • 当没有索引或者索引失效的时候,会从行锁升级为表锁:
# 该事务下使用的锁是:表锁
update course set name = 'SpringBoot' where name = 'PHP' ;

SQL | DQL语句

order by

MySQL的排序有两种方式,尽量使用Using index

  • Using filesort:将满足条件的数据行放到sort buffer中完成排序操作。使用索引或不使用索引的情况,都有可能出现该种排序方式

  • Using index:通过有序索引,按照顺序扫描,直接返回有序数据,不需要额外排序。效率要高于Using filesort

不同场景下,会选用不同的排序方式,也有某些场景,两种排序方式都存在。


使用Using filesort的情况

  • 无索引排序
  • 多列排序,各列都单独创建索引
# 无索引
explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age, phone;# 多列排序,均为单列索引
explain select id,age,phone from tb_user order by age, phone;

使用Using index的情况

  • 单列排序,有单列索引
  • 联合索引正常使用
# 单列索引
explain select age from tb_user order by age;# 联合索引正常使用。给(age、phone)创建联合索引
explain select id,age,phone from tb_user order by age;
explain select id,age,phone from tb_user order by age, phone;

其他情况

# 给(age、phone)创建联合索引# Using index; Using filesort
explain select id,age,phone from tb_user order by phone;
explain select id,age,phone from tb_user order by phone, age;# Backward index scan; Using index;
explain select id,age,phone from tb_user order by age, phone desc;

联合索引默认是增序的,在MySQL8版本中,可以建立降序索引:

CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name(index_col_name asc, index_col_name_2 desc, ...);

group by

在分组操作的时候,通过索引可以提升效率,但是同时也遵循最左匹配原则:

# === 无索引
# Using temporary
explain select profession , count(*) from tb_user group by profession;# === 建立单独索引
# Using index
explain select profession , count(*) from tb_user group by profession;# === 建立联合索引:(profession,age)
# Using index
explain select profession , count(*) from tb_user group by profession;# Using index,Using temporary
explain select age , count(*) from tb_user group by age;

limit

limit查询,查询的数据越往后,时间消耗越大:

# 0.00sec
select * from tb_sku limit 0, 10;# 10.79sec
select * from tb_sku limit 100000, 10;

可以通过:覆盖索引 + 子查询(根据位置分类,属于from后面的子查询;根据返回结果分类,属于列子查询),对SQL进行优化:

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

count

MySQl中统计数量的函数是count()。针对count的优化:

  • 方案1:不使用count,自己计数(难搞)
  • 方案2:合理的选用count用法。效率:count(字段) < count(主键) < count(1) = count(*)

image-20240327180026535

其余 | 主键优化

  • 满足业务的前提下,尽量减低主键的长度
  • 插入数据尽量顺主键插入,可以选择自增主键
  • 尽量不要用uuid或身份证号作为主键,插入数据不具备排序性质
  • 业务操作,避免对主键进行修改
http://www.lryc.cn/news/339398.html

相关文章:

  • QT 线程的使用
  • Python基于flask的豆瓣电影分析可视化系统
  • 【迅为iTOP-4412-linux 系统制作(4)】ADB 或者 TF 卡烧写测试
  • 阿里云对象存储OSS批量上传,单个上传,批量删除,单个删除!
  • Python的国际化和本地化【第162篇—国际化和本地化】
  • 播放Samba协议下的音视频文件
  • Excel全套213集教程
  • 【七 (1)指标体系建设-构建高效的故障管理指标体系】
  • Go gin框架(详细版)
  • Git分布式版本控制系统——Git常用命令(二)
  • LeetCode 59.螺旋矩阵II
  • 03-JAVA设计模式-适配器模式
  • MVVM架构模式
  • leetcode2924--找到冠军II
  • 嵌入式|蓝桥杯STM32G431(HAL库开发)——CT117E学习笔记13:RTC实时时钟
  • 统一用安卓Studio修改项目包名
  • Spring Cloud Gateway详细介绍以及实现动态路由
  • transformer上手(6)—— 微调预训练模型
  • web前端框架设计第四课-条件判断与列表渲染
  • 计算机网络:数据链路层 - CSMA/CD协议
  • 力扣LeetCode138. 复制带随机指针的链表 两种解法(C语言实现)
  • 强大的压缩和解压缩工具 Keka for Mac
  • 论文速读:Do Generated Data Always Help Contrastive Learning?
  • 华为欧拉系统(openEuler-22.03)安装深信服EasyConnect软件(图文详解)
  • git commit --amend用法
  • 分布式系统:缓存与数据库一致性问题
  • JavaEE企业开发新技术5
  • mysql dump导出导入数据
  • 刷题记录3
  • Decorator 装饰