锁、慢查询日志、Explain、读写分离、模糊查询%在最前面、order by 造成的全表扫描

Forever Nore

已于 2023-03-17 20:12:46 修改

阅读量853

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

于 2022-09-27 23:04:45 首次发布

本文链接：https://blog.csdn.net/qq_30659573/article/details/126990145

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

锁

意向锁是表级锁，加了行锁自动加上意向锁 IX，加表锁时检查是否有意向锁，有—>阻塞，无—>加表锁

在这里插入图片描述

表锁（Table Lock）

① 表级别的S锁、X锁

LOCK TABLES t READ ：InnoDB存储引擎会对表 t 加表级别的 S锁 。
LOCK TABLES t WRITE ：InnoDB存储引擎会对表 t 加表级别的 X锁 。
② 意向锁（intention lock）
意向共享锁（intention shared lock, IS）：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取-->某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他锁（intention exclusive lock, IX）：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取-->某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

结论：意向锁之间互不排斥，但除了 IS 与 S 兼容外， 意向锁会与 共享锁 / 排他锁 互斥。
IX，IS是表级锁，不会和行级的X，S锁发生冲突。只会和表级的X，S发生冲突。

③ 元数据锁（MDL锁）

当对一个表做增删改查操作的时候，加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁

行锁

① 记录锁（Record Locks）

SELECT column FROM table ... where	xxx LOCK IN SHARE MODE; # 对这行记录--> 加读锁
SELECT column FROM table ... where 	xxx  FOR UPDATE;		# 对这行记录--> 加写锁
update xxx  where yyy #行锁

② 间隙锁（Gap Locks）

id=3,id=8 有这两条记录

begin;
update xxx where id=5 # 对(3,8)这个区间加锁

begin;
update xxx where id=7 # 此时会阻塞

③ 临键锁（Next-Key Locks）

id=3,id=8 有这两条记录

begin;
select * from student where id <=8 and id > 3 for update; #对id=8这个加行锁，对(3,8)区间加间隙锁

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环。死锁示例

在这里插入图片描述

出现死锁以后，有 两种策略 ：

直接进入等待，直到超时。这个超时时间可以通过参数
innodb_lock_wait_timeout 来设置。
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务（将持有最少行级
排他锁的事务进行回滚），让其他事务得以继续执行。将参数innodb_deadlock_detect设置为
on ，表示开启这个逻辑

性能优化

数据库优化步骤

观察服务器状态，看是否有周期性波动，没有看下一条，有的话加缓存、更改缓存失效策略，解决不了看下一条
开启慢查询、分析SQL，
SQL等待时间长—>调整服务器参数
SQL执行时间长—>索引优化、join优化、数据库设计优化
都没有解决，看SQL是否到达瓶颈，到达瓶颈—>读写分离、分库分表

慢查询日志

开启慢查询日志

show variables like '%slow_query_log%'; # 查看慢查询是否开启 
set global slow_query_log='ON';			# 开启慢查询
show variables like '%slow_query_log%'; # 再次查看

在这里插入图片描述

慢查询分析已经开启，同时文件保存在/var/lib/mysql/8126f1e25c25-slow.log文件中。

修改long_query_time

show variables like '%long_query_time%';

在这里插入图片描述

#  设置global的方式对当前session的long_query_time 失效,对新连接的客户端有效。
set global long_query_time = 1;
show global variables like '%long_query_time%';
set long_query_time=1;

在这里插入图片描述

查看慢查询数目

查询当前系统中有多少条慢查询记录

SHOW GLOBAL STATUS LIKE '%Slow_queries%';
show status like 'slow_queries';

慢查询日志分析工具：mysqldumpslow

查看mysqldumpslow的帮助信息,不在mysql界面！

mysqldumpslow --help

在这里插入图片描述
mysqldumpslow 命令的具体参数如下：
-a: 不将数字抽象成N，字符串抽象成S
-s: 是表示按照何种方式排序：
c: 访问次数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间（默认方式）
ac:平均查询次数
-t: 即为返回前面多少条的数据；

按照查询时间排序，查看前五条 SQL 语句

mysqldumpslow -s t -t 5 /var/lib/mysql/8126f1e25c25-slow.log

工作常用

#得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/8126f1e25c25-slow.log
#得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/8126f1e25c25-slow.log
#得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/8126f1e25c25-slow.log
#另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/8126f1e25c25-slow.log | more

关闭慢查询日志

方式1：永久性方式

[mysqld]
slow_query_log=OFF #或者，把slow_query_log一项注释掉 或 删除

方式2：临时性方式

SET GLOBAL slow_query_log=off; # 关闭后，重启MySQL服务

Explain

使用explain查看sql执行情况,从而分析索引是否满足需求

使用

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

字段含义

Explain 执行计划中各个字段的含义

在这里插入图片描述

读写分离

搭建主从之后:

在主机中执行以下SQL，在从机中查看数据库、表和数据是否已经被同步

CREATE DATABASE db_user;
USE db_user;
CREATE TABLE t_user (
 id BIGINT AUTO_INCREMENT,
 uname VARCHAR(30),
 PRIMARY KEY (id)
);
INSERT INTO t_user(uname) VALUES('zhang3');
INSERT INTO t_user(uname) VALUES(@@hostname);

创建项目
SpringBoot脚手架：http://start.aliyun.com
添加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
        <version>5.1.1</version>
    </dependency>

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    <dependency>
        <groupId>com.baomidou</groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.3</version>
    </dependency>

    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
    
</dependencies>

创建实体类

@TableName("t_user")
@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String uname;
}

创建Mapper

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

配置读写分离
application.properties

# suppress inspection "SpringBootApplicationProperties" for whole file
# 应用名称
spring.application.name=sharging-jdbc-demo
# 开发环境设置
spring.profiles.active=dev
# 内存模式
spring.shardingsphere.mode.type=Memory

# 配置真实数据源
spring.shardingsphere.datasource.names=master,slave1

# 配置第 1 个数据源
spring.shardingsphere.datasource.master.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.master.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.master.jdbc-url=jdbc:mysql://192.168.111.101:3307/db_user?allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.master.username=root
spring.shardingsphere.datasource.master.password=123456

# 配置第 2 个数据源
spring.shardingsphere.datasource.slave1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.slave1.driver-class-name=com.mysql.jdbc.Driver
spring.shardingsphere.datasource.slave1.jdbc-url=jdbc:mysql://192.168.111.101:3308/db_user?allowPublicKeyRetrieval=true
spring.shardingsphere.datasource.slave1.username=root
spring.shardingsphere.datasource.slave1.password=123456



# 读写分离类型，如: Static，Dynamic,myds是逻辑数据源！！！
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.type=Static
# 写数据源名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.write-data-source-name=master
# 读数据源名称，多个从数据源用逗号分隔
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.props.read-data-source-names=slave1

# 负载均衡算法名称
spring.shardingsphere.rules.readwrite-splitting.data-sources.myds.load-balancer-name=alg_round

# 负载均衡 算法配置
# 负载均衡 算法类型
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_round.type=ROUND_ROBIN
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_random.type=RANDOM
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.type=WEIGHT
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.props.slave1=1
spring.shardingsphere.rules.readwrite-splitting.load-balancers.alg_weight.props.slave2=2

# 打印SQl
spring.shardingsphere.props.sql-show=true

问题

模糊查询%在最前面，能否或者怎么使用索引?

结论：like查询百分号前置，并不是100%不会走索引。如果只select索引字段，或者select索引字段和主键，也会走索引的。

使用 order by 造成的全表扫描

SELECT * FROM user ORDER BY age DESC

上述语句在 age 上加了索引，但依然造成了全表扫描，这是因为我们使用了 SELECT ,导致回表查询，MySQL 认为回表的代价比全表扫描更大，所以不选择使用索引，如果想使用到 age 的索引，我们可以用覆盖索引*来代替

SELECT age FROM user ORDER BY age DESC
SELECT * FROM user ORDER BY age DESC limit 10  # 或者加上 limit 的条件（数据比较小）

无法避免对索引列使用函数，怎么使用索引

想记录 2016 ~ 2018 所有年份 7月份的交易记录总数

SELECT count(*) FROM tradelog WHERE month(t_modified)=7;

由于索引列是函数的参数，所以显然无法用到索引，我们可以将它改造成基本字段区间的查找如下:

SELECT count(*) FROM tradelog WHERE
    -> (t_modified >= '2016-7-1' AND t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' AND t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' AND t_modified<'2018-8-1');

InnoDB和MyISAM 区别

InnoDB：索引和数据存储在一起 .ibd
MyISAM ：索引和数据是分开存放的 .MYD与.MYI
MyISAM 索引文件仅仅保存数据记录的地址
InnoDB支持行锁，MyISAM 是表锁
InnoDB支持事务

Forever Nore

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
锁、慢查询日志、Explain、读写分离、模糊查询%在最前面、order by 造成的全表扫描

3). 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据。2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个。:子查询查id,将这个id的结果集当成一张表，进行关联查询。1). 赃读：一个事务读到另外一个事务还没有提交的数据。③ 临键锁（Next-Key Locks）:指的最左边的列，是指在查询时，联合索引的。
复制链接

扫一扫