文章目录
锁
意向锁 是表级锁,加了行锁自动 加上意向锁 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支持事务