AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL高级第三篇（共四篇）之应用优化、查询缓存优化、内存管理优化、MySQL锁问题、常用SQL技巧（二）

2023-07-30 414
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
RDS MySQL Serverless 高可用系列，价值2615元额度，1个月
简介： 锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

5. Mysql锁问题


5.1 锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

在数据库中，除传统的计算资源（如 CPU、RAM、I/O 等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

5.2 锁分类

从对数据操作的粒度分 ：

1.表锁：操作时，会锁定整个表。

2.行锁：操作时，会锁定当前操作行。

从对数据操作的类型分：

1.读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

2.写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

5.3 Mysql 锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。下表中罗列出了各存储引擎对锁的支持情况：

20210131115847880.png


MySQL这3种锁的特性可大致归纳如下 ：

image.png

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web 应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理（OLTP）系统。

5.2 MyISAM 表锁

MyISAM 存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。


5.2.1 如何加表锁

MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

显示加表锁语法：

加读锁 ： lock table table_name read;
加写锁 ： lock table table_name write；


5.2.2 读锁案例

准备环境(这里用的都是myisam 引擎):

create database demo_03 default charset=utf8mb4;
use demo_03;
CREATE TABLE `tb_book` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
`publish_time` DATE DEFAULT NULL,
`status` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思
想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思
想','2088-08-08','0');
CREATE TABLE `tb_user` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');


操作步骤：

客户端 一 ：

1.获得tb_book 表的读锁

lock table tb_book read;


2.执行查询操作

select * from tb_book;

20210131125116469.png

可以正常执行 ， 查询出数据。


客户端 二 ：

3. 执行查询操作(也能正常查询)

select * from tb_book;


客户端 一 ：

4. 查询未锁定的表(可以看到报错了)

select name from tb_user;

20210131125411275.png


客户端 二 ：

5. 查询未锁定的表（可以正常查询出未锁定的表；）

select name from tb_user;

20210131125555659.png


客户端 一 ：

6. 执行插入操作(结果发现不能插入)

insert into tb_book values(null,'Mysql高级','2088-01-01','1');

20210131125710836.png

执行插入，直接报错 ，由于当前tb_book 获得的是 读锁，不能执行更新操作。

客户端 二 ：

7. 执行插入操作（结果是一直等待，直到客户端一释放读锁）

insert into tb_book values(null,'Mysql高级','2088-01-01','1');

20210131125904376.png


当在客户端一中释放锁指令 unlock tables 后 ， 客户端二中的 inesrt 语句 ， 立即执行 ；

image.png


5.2.3 写锁案例

客户端 一 :

1.获得tb_book 表的写锁

lock table tb_book write ;

2.执行查询操作（成功）

select * from tb_book ;


3.执行更新操作（成功）

update tb_book set name = 'java编程思想（第二版）' where id = 1;


客户端 二 :

4.执行查询操作(结果是一直等待，直到客户端一释放写锁)

select * from tb_book ;

20210131130502672.png


当在客户端一中释放锁指令 unlock tables 后 ， 客户端二中的 select 语句 ， 立即被执行 ；

20210131130651353.png


5.2.4 结论

锁模式的相互兼容性如表中所示：image.png

由上表可见：

1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；

2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。

此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

5.2.5 查看锁的争用情况

show open tables；

20210131131121370.png

In_user : 表当前被查询使用的次数。如果该数为零，则表是打开的，但是当前没有被使用。


Name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。

show status like 'Table_locks%';


2021013113122596.png

Table_locks_immediate ： 指的是能够立即获得表级锁的次数，每立即获取锁，值加1。


Table_locks_waited ： 指的是不能立即获取表级锁而需要等待的次数，每等待一次，该值加1，此值高说明存在着较为严重的表级锁争用情况。


5.3 InnoDB 行锁


5.3.1 行锁介绍

行锁特点 ：偏向InnoDB 存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。

InnoDB 与 MyISAM 的最大不同有两点：一是支持事务；二是采用了行级锁。

5.3.2 背景知识

事务及其ACID属性


事务是由一组SQL语句组成的逻辑处理单元。


事务具有以下4个特性，简称为事务ACID属性。

20210131131435225.png

并发事务处理带来的问题

20210131131503178.png

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

20210131140255530.png

备注 ： √ 代表可能出现 ， × 代表不会出现 。

Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：

show variables like 'tx_isolation';


5.3.3 InnoDB 的行锁模式

InnoDB 实现了以下两种类型的行锁。

●  共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。

●  排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；

对于普通SELECT语句，InnoDB不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁 。

共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

5.3.4 案例准备工作

create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);


5.3.5 行锁基本演示

以下innodb的所有演示均要关闭自动提交（set autocommit=0）。


两个客户端，均关闭自动提交，然后更新同一行数据，结果发现当客户端一更新后，如果不commit，客户端二则一直等待。

20210131142816753.png


如果是不同行，则不会锁：

2021013114292713.png


5.3.6 无索引行锁升级为表锁

如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样。


查看当前表的索引 ： show index from test_innodb_lock ;

20210131143414498.png

可以看到我们有两个索引。


接下来我们让name索引失效（删除也行），也就是在where条件使用数字而不是字符型，由于 执行更新时 ， name字段本来为varchar类型， 我们是作为数字类型使用，存在类型转换，索引失效，最终行锁变为表锁 ；

20210131143918751.png


可以看到更新的根本不是一行数据，但是还是客户端二还是阻塞了。

如果name换成字符，则索引有效，则不会走表锁：

20210131144149384.png


5.3.7 间隙锁危害

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）” ， InnoDB也会对这个 “间隙” 加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁） 。


示例 ：

20210131144718225.png

可以看到虽然id为2的行不存在，但是因为间隙锁的存在，它也被锁住了，导致插入语句陷入等待。


5.3.8 InnoDB 行锁争用情况

show status like 'innodb_row_lock%';


20210131144910995.png

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg:每次等待所花平均时长
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
当等待的次数很高，而且每次等待的时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。


5.3.9 总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些，但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势。

但是，InnoDB的行级锁同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议：

●  尽可能让所有数据检索都能通过索引来完成，避免无索引行锁升级为表锁。

●  合理设计索引，尽量缩小锁的范围

●  尽可能减少索引条件，及索引范围，避免间隙锁

●  尽量控制事务大小，减少锁定资源量和时间长度

●  尽可使用低级别事务隔离（但是需要业务层面满足需求）


6. 常用SQL技巧


6.1 SQL执行顺序

我们的编写顺序：

SELECT DISTINCT
<select list>
FROM
<left_table> <join_type>
JOIN
<right_table> ON <join_condition>
WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT
<limit_params>


电脑的执行顺序：

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT DISTINCT <select list>
ORDER BY <order_by_condition>
LIMIT <limit_params>


6.2 正则表达式使用

正则表达式（Regular Expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。

image.png


示例：

20210131145447887.png


6.3 MySQL 常用函数

数字函数：

20210131145525749.png


字符串函数：

20210131145537579.png


日期函数：20210131145616407.png


聚合函数：

20210131145642746.png

文章标签：
云数据库 RDS MySQL 版
自然语言处理
SQL
OLTP
关系型数据库
索引
MySQL
数据库
调度
缓存
存储
关键词：
云数据库 RDS MySQL 版优化
SQL应用
查询SQL
云数据库 RDS MySQL 版查询
SQL优化
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
Bug哆哆
目录
相关文章
mikechen的互联网架构
|
10天前
|
SQL 缓存 监控
大厂面试高频：4 大性能优化策略（数据库、SQL、JVM等）
本文详细解析了数据库、缓存、异步处理和Web性能优化四大策略，系统性能优化必知必备，大厂面试高频。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
mikechen的互联网架构
37 6
大厂面试高频：4 大性能优化策略（数据库、SQL、JVM等）
云深知何处
|
7天前
|
SQL 监控 关系型数据库
SQL语句当前及历史信息查询-performance schema的使用
本文介绍了如何使用MySQL的Performance Schema来获取SQL语句的当前和历史执行信息。Performance Schema默认在MySQL 8.0中启用，可以通过查询相关表来获取详细的SQL执行信息，包括当前执行的SQL、历史执行记录和统计汇总信息，从而快速定位和解决性能瓶颈。
云深知何处
27 1
sunrr
|
18天前
|
SQL 存储 缓存
如何优化SQL查询性能？
【10月更文挑战第28天】如何优化SQL查询性能？
sunrr
64 10
小Lee
|
12天前
|
SQL 关系型数据库 MySQL
查询INFORMATION_SCHEMA.PROCESSLIST和INFORMATION_SCHEMA.INNODB_TRX的sql语句
【11月更文挑战第2天】
小Lee
25 2
威哥爱编程
|
17天前
|
SQL 存储 缓存
SQL Server 数据太多如何优化
11种优化方案供你参考，优化 SQL Server 数据库性能得从多个方面着手，包括硬件配置、数据库结构、查询优化、索引管理、分区分表、并行处理等。通过合理的索引、查询优化、数据分区等技术，可以在数据量增大时保持较好的性能。同时，定期进行数据库维护和清理，保证数据库高效运行。
威哥爱编程
53 4
1689161901914944
|
26天前
|
SQL 数据库 开发者
功能发布-自定义SQL查询
本期主要为大家介绍ClkLog九月上线的新功能-自定义SQL查询。
1689161901914944
28 6
刘大猫.
|
22天前
|
SQL 关系型数据库 MySQL
mysql编写sql脚本：要求表没有主键，但是想查询没有相同值的时候才进行插入
mysql编写sql脚本：要求表没有主键，但是想查询没有相同值的时候才进行插入
刘大猫.
31 0
东方睿赢
|
1月前
|
消息中间件 缓存 NoSQL
Redis 是一个高性能的键值对存储系统，常用于缓存、消息队列和会话管理等场景。
【10月更文挑战第4天】Redis 是一个高性能的键值对存储系统，常用于缓存、消息队列和会话管理等场景。随着数据增长，有时需要将 Redis 数据导出以进行分析、备份或迁移。本文详细介绍几种导出方法：1）使用 Redis 命令与重定向；2）利用 Redis 的 RDB 和 AOF 持久化功能；3）借助第三方工具如 `redis-dump`。每种方法均附有示例代码，帮助你轻松完成数据导出任务。无论数据量大小，总有一款适合你。
东方睿赢
77 6
mikechen的互联网架构
|
10天前
|
缓存 NoSQL 关系型数据库
大厂面试高频：如何解决Redis缓存雪崩、缓存穿透、缓存并发等5大难题
本文详解缓存雪崩、缓存穿透、缓存并发及缓存预热等问题，提供高可用解决方案，帮助你在大厂面试和实际工作中应对这些常见并发场景。关注【mikechen的互联网架构】，10年+BAT架构经验倾囊相授。
mikechen的互联网架构
38 6
大厂面试高频：如何解决Redis缓存雪崩、缓存穿透、缓存并发等5大难题
赵渝强老师
|
11天前
|
存储 缓存 NoSQL
【赵渝强老师】基于Redis的旁路缓存架构
本文介绍了引入缓存后的系统架构，通过缓存可以提升访问性能、降低网络拥堵、减轻服务负载和增强可扩展性。文中提供了相关图片和视频讲解，并讨论了数据库读写分离、分库分表等方法来减轻数据库压力。同时，文章也指出了缓存可能带来的复杂度增加、成本提高和数据一致性问题。
赵渝强老师
34 4
【赵渝强老师】基于Redis的旁路缓存架构

热门文章

最新文章

  • 1
    测试开启MySQL performance_schema后对性能的影响
  • 2
    RDS入门——Excel文件转存到RDS数据库实践
  • 3
    Linux操作系统上的mysql安装
  • 4
    mysql常用操作
  • 5
    MySQL数据库管理深度解析：创建新数据库的完全指南
  • 6
    C语言连接MySQL数据库
  • 7
    JDBC操作MySQL5日期类型字段的问题解决方法
  • 8
    Mariadb/Mysql TEMPORARY表
  • 9
    FAQ系列 | 启用SELinux后，PHP连接MySQL异常
  • 10
    mysql 数据类型
  • 1
    HadoopCPU、内存、存储限制
    286
  • 2
    操作系统中的内存管理技术解析
    67
  • 3
    操作系统中的内存管理机制探究
    35
  • 4
    【性能优化篇】U3D游戏卡顿大作战：内存与渲染效率的极致提升
    359
  • 5
    计算机内存
    47
  • 6
    JAVA内存模型与JVM内存模型的区别
    42
  • 7
    Object o = new Object()在内存中占几个字节
    41
  • 8
    深入理解操作系统的内存管理机制
    46
  • 9
    探索现代操作系统中的内存管理技术
    67
  • 10
    【Linux】进程间通信——system V(共享内存 | 消息队列 | 信号量)（下）
    67

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server在电子商务中的应用与实践
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • SQL的增删改查及函数应用
  • MyBase MySQL实例资源灵活弹配
  • PolarDB MySQL自动SQL限流
  • 如何开通SQL Server的专属集群
  • 使用DAS实现数据库自动SQL优化
  • 使用DAS实现数据库自动SQL限流

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • postgresql
    • 下一篇
    阿里云OSS设置跨域访问