MySql学习总结

最新推荐文章于 2022-07-06 02:56:23 发布

享知

最新推荐文章于 2022-07-06 02:56:23 发布

阅读量310

点赞数 1

本文链接：https://blog.csdn.net/qq_30561643/article/details/103624919

版权

MySql学习总结

指标

TPS Transactions Per Second (每秒传输的事务处理个数)，这个指服务器每秒处理的事务数，支持事务的存储引擎如InnoDB等特有的一个性能指标。
QPS Queries Per Second （每秒钟查询处理）同时适应与InnoDB和MyISAM引擎

等待时间：执行Sql等待放回结果之间的等待时间

TPS =(COM_COMMIT + COM_ROLLBack)/update

QPS = Question/ uptime

MYSQL 逻辑架构

连接层
当Mysql启动MySql服务器就是一个进程，等待客户端
服务层
引擎层
存储层

Mysql锁机制

存储引擎锁采用的锁

MyISAM 表级锁（table-level-locking）
MEMORY 行级锁（row-level locking）、表级锁，在默认情况下是行级锁

锁的特征

表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般

适应场景

从锁的角度来说
表级锁更适合以查询为主，只有少量按索引条件跟新数据的应用 OLAP
行级锁
大量按索引条件并发更新、查询。OLTP

MyISAM

共享读锁

语法 (给表加锁)

lock table 表名 read
解锁
unlock tables;
查询创建表语句

show create table t_user

创建t_user 表语句

CREATE TABLE `t_user` (
  `name` varchar(255) DEFAULT NULL,
  `id` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

创建t_role表语句

CREATE TABLE `t_role` (
  `role_name` varchar(255) DEFAULT NULL,
  `role_id` int(11) NOT NULL,
  PRIMARY KEY (`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

测试读

开启第一个客户端

-- 查询数据库
select * from t_user;

-- 共享锁的语法

lock table t_user read;
-- 没有问题
select * from t_user;
-- Table 't_user' was locked with a READ lock and can't be updated 错误日志

-- 在同一个session查询同一张表
insert into t_user(id, name) value (3, 'tom')
update t_user set id =2 where id =1

-- 在同一个session中查询不同表 Table 't_role' was not locked with LOCK TABLES
select * from  t_role;
insert into t_role(role_id,role_name) value(2, 'master');

UNLOCK tables;

在开启一个session

select * from t_user;
-- 阻塞
insert into t_user(id, name) value (3, 'tom')

select * from t_role;
-- 在另外一个session插入成功
insert into t_role(role_id,role_name) value(2, 'master');

测试写

测试写锁

lock table t_user write
select * from t_user;

-- Table 't_role' was not locked with LOCK TABLES
insert into t_role(role_id,role_name) value(10, 'master');
-- 受影响的行: 1
insert into t_user(id, name) value (3, 'tom')

select * from t_user;

另外启动一个窗口阻塞

insert into t_user(id, name) value (11, 'tom')

show status Like ‘table_locks_waited’ 查看当前表等待的锁的次数
总结
1、对于MyISAM表的读操作、不会阻塞其他用户对同一表的读请求，但会阻塞同一表的写请求；
2、不会阻塞当前表的读操作；
3、一个session使用Lock table命令给表加锁，这个session可以查询表中的记录，当更新、访问其他表都会出现错误；
4、另外一个session可以查询表中的记录，但更新就会出现等待（通过加锁的session解锁命令来释放信号）
5、写锁，会阻塞其他用户对同一表的读和写操作
6、当前session允许CRUD，对其他操作表报错

InnoDB 锁机制

前言锁的相关概念

行锁
共享锁又称：
读锁。当一个事物对某几行上读锁时，允许其他事物对这几行读操作，不允许写操作，也不允许其他事物
给者几行上排他锁，但允许上读锁。
排他锁
写锁。当一个事物对某几个上写锁时，不允许其他事物写，但允许读。更不允许其他事物给这几行上任何锁
包括写锁。

锁

共享锁

语法：
上共享锁的写法 lock in share model
例如：
select * from 表 where 条件 lock in share model

排他锁

语法：
上排他锁的写法： for update
例如：
select * from 表 where 条件 for update.

总结

1、两个事务不能锁同一个索引
2、 insert、delete、update在事务中都会自动默认添加排他锁
3、行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了。

测试锁机制

排他锁测试

在一个session中查询，在另外一个session发生更新操作

开启session

# 排他锁测试
begin 

select *  from t_role where role_id = 10 for update

开启两外一个session

# 排他锁测试
begin 
-- 阻塞
select *  from t_role where role_id = 10 for update
-- 成功 
update t_role set role_id = 1 where role_id = 11

对同一个Id 在一个session更新操作、在另外一个session也更新

发生更新操作

update t_role set role_id = 20 where role_id = 1

等待

--等待
update t_role set role_id = 30 where role_id = 1

Mysql事务

事务的种类

A 原子性（atomicty）：一个事务必须视为一个不可分割的最小单元，整个事务中的所有操作要么全部提交，要么全部失败。
C ：一致性(consistency)是指事务将数据库从一种一致性转换到另外一种一致性状态，在事务开始之前和事务结束之后
数据库中的数据没有被破坏
I 隔离性（Isolation）：隔离性要求一个事务对数据中数据修改，在未提交完成前对于其他事务是不可见的。
D 持久性 Durability：一旦事务提交，则其所修改就会永久保存到数据库中。此时即系统崩溃，已经提交修改数据也不会丢失。

事务的隔离级别

未提交读（READ UNCOMMITED）脏读
已提交读(READ COMMITED) 不可从重复读
可重复读（REPEATABLE READ）
可串行化 (SERIALIZABLE)

myql默认可重复读（REPEATABLE READ）通过该命令查看 show variables like ‘%tx_isolation%’
在这里插入图片描述

事务的特征

事务并发问题

脏读：事务A读取事务B更新的数据，然后B回滚操作,那么A读取到的数据就是脏数据。
不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据做了更新并提交，
导致A多次读取同一数据是，结果不一致。
幻读：系统管理员A将数据中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻读一样。

不可重复读读和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或者删除。解决不可重复读的问题只需要锁住满足的条件就行，解决幻读需要锁表

测试代码

未提交读（ READ UNCOMMITTED）脏读

在一个客户端设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--开启事物
start TRANSACTION  
-- 进行操作
update t_account set money = money -50 where id = 1
-- 查询
select * from t_account where id = 1
--等待另外一个客户端器来然后再执行
ROLLBACK

启动另外一个客户端

select * from t_account where id = 1

update t_account set money = money -50 where id = 1

总结：本来A账号中有500经过操作后变成500-50=450，然后启动另外一个客户端执行查询语句发现还是500；

已提交读不可重复读

-- 设置
SET SESSION TRANSACTION ISOLATION LEVEL READ  COMMITTED;
-- 启动
start TRANSACTION
--操作
select * from t_account where id = 1
update t_account set money = money -50 where id = 1
select * from t_account where id = 1
COMMIT

启动另外一个客户端

select * from t_account where id = 1

总结：当第一个客户执行操作的时候，然后第一个客户端查询，然后再第一个客户端执行commit操作，然后
第一个客户端执行查询操作看到最新数据。

在一个客户端设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
beign 开启事务
更新操作 (先执行)
提交事务 commit 操作（在下一个客户端读之前）

在开启一个客户端设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED；
beign 开启事务
查询操作 --.> 发现读的数据一上一个客户端读到的数据不一致
当上一个客户端开启commit操作，读取到数据发现一致。

可重复读

在一个客户端设置隔离级别

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
start TRANSACTION
	update t_account set money = money -50 where id = 1
	select * from t_account where id = 1
commit

在开启一个客户端设置隔离级别

select * from t_account where id = 1

SET SESSION TRANSACTION ISOLATION LEVEL repeatable COMMITTED；
beign 开启事务
查询操作 --.> 发现读的数据一上一个客户端读到的数据不一致
当上一个客户端开启commit操作，读取到数据还是不一致。

总结：可重复读就是，在同一个session中读到的数据永远不变，当session（客户端）修改了数据库中的数据提交了但是在另外一个session中还是不会读到这条提交的数据

可串行化

幻读
总结：同时开启两个session，然后开启事务，在第一个session中插入一条数据，然后执行查询；在第二个session中开启事务然后执行查询，无法发现之前那条数据;都commit 之后然后执行查询操作，发现数据库中多了一条数据。

如果解决幻读呢？串行化

事务的特性

事务隔离级别（总结）

事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁，行锁，页锁的问题，从而锁住了一些行；如果没有索引，更新数据时会锁住整张表

事务隔离级别为串行化时，读写数据都会锁住整张表。

业务设计

逻辑设计

范式设计

数据库表设计中的所有字段只具有单一属性；单一属性的列有基本数据类型组成；设计出来的表都是简单的二维表；
要求表中具有第一业务组件，也就是说符合第二范式的表不能存在在非主键列只对部分组件依赖关系；
非主键列既不能依赖于也不传递依赖于业务主键；

优点：

可以尽量减少数据冗余
范式化比反范式化更快
方式化得通常比反方式化得表更小
缺点：
对于查询需要对多个表进行关联
更难进行索引优化

总结:

反范式设计

为了性能和读取效率考虑而适当得对数据设计范式要求进行违反
允许存在少量得冗余；

优点：

减少表得关联；
可以更好得优化锁；

缺点：

存在冗余数据难以维护异常；
对数据得修改需要更多的成本；

总结：这个需要根据具体业务，做具体实现；例如我们查询次数比较多得情况下可以考虑他。

物理设计

定义数据库、表及字段的命名规范
选择合适的存储引擎
为表添加字段选择合适的数据类型
建立数据库结构

命名规范

可读性
表意性原则
长名原则

存储引擎选择

在这里插入图片描述

数据类型选择

当一个列可以选择多种数据类型时
- 数字；

日期、时间；
字符型；
对于相同的数据类型优先考虑占用空间小的类型；

浮点类型重点考虑数据精度丢失的问题建议使用Declmal
在这里插入图片描述

日期类型
timestamp类型于datatime区别

timestamp与时区有关，datatime与时区无关

慢查询

慢查询日志，是指mysql记录超过long_query_time参数设定的时间阈值的sql语句的日志。该日志能为SQL语句优化带来很好的帮助。默认（关闭）

慢查询配置

慢查询基本配置

slow_query_log 启动停止技术慢查询日志
slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
log_queries_not_using_indexes 是否记录未使用索引的SQL
log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

记录符合条件得SQL

查询语句
数据修改语句
已经回滚得SQL

总结：启动、设置慢查询日志文件、时间、索引。

慢查询解读

在这里插入图片描述

慢查询分析

mysqldumpslow

常用的慢查询日志分析工具（mysqldumpslow）汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出

pt_query_digest

索引入门

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。

索引是什么

在这里插入图片描述
总结：索引保存数据区 + 指针区

索引的分类

普通索引 – > 即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引 --> 索引列的值必须唯一，但允许有空值
复合索引 --> 即一个索引包含多个列
聚簇索引(聚集索引) --> 并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。
非聚簇索引 --> 不是聚簇索引，就是非聚簇索引

基础语法

查看索引
SHOW INDEX FROM table_name\G
创建索引
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
删除索引
DROP INDEX [indexName] ON mytable;

执行计划

什么是执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

语法
   Explain + SQL语句

执行计划的作用

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

执行计划的详解

采用学生与选课做优化演示

课程表

CREATE TABLE `t_course` (
  `course_num` int(11) DEFAULT NULL,
  `course_name` varchar(255) DEFAULT NULL,
  `course_id` int(11) NOT NULL,
  PRIMARY KEY (`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

学生表

TABLE `t_student` (
  `stu_name` varchar(255) DEFAULT NULL,
  `stu_id` int(11) NOT NULL DEFAULT '0',
  `stu_age` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

学生与课程表之间的关系

CREATE TABLE `t_stu_course_mapping` (
  `course_id` int(11) NOT NULL DEFAULT '0',
  `stu_id` int(11) NOT NULL,
  PRIMARY KEY (`stu_id`,`course_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

在这里插入图片描述

执行计划-ID
1、select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
2、三种情况
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在
测试验证

EXPLAIN
SELECT
	*
FROM
	t_student s
LEFT JOIN t_stu_course_mapping m ON s.stu_id = m.stu_id
LEFT JOIN t_course c ON m.course_id = c.course_id
WHERE
	s.stu_id = 1

result:
在这里插入图片描述

select_type：查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询
在这里插入图片描述

验证

table: 显示这一行的数据是关于哪张表的

验证

type：显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要记忆的
system>const>eq_ref>ref>range>index>ALL

验证

在这里插入图片描述
1、system：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计。
2、const：表示通过索引一次就找到了const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
3、eq_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
4、ref 非唯一性索引扫描，返回匹配某个单独值的所有行；本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
5、range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
6、all Full Table Scan，将遍历全表以找到匹配的行

测试
1、第一种情况测试 system

EXPLAIN 
select * from (
   select * from t_role r where r.role_id = 1
)t

在这里插入图片描述

2、const 这个很容易理解就是查询sql添加where条件后面跟着了primary key或者unique

select * from t_role r where r.role_id = 1

在这里插入图片描述
3、

享知

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySql学习总结

MySql学习总结指标TPS Transactions Per Second (每秒传输的事务处理个数)，这个指服务器每秒处理的事务数，支持事务的存储引擎如InnoDB等特有的一个性能指标。QPS Queries Per Second （每秒钟查询处理）同时适应与InnoDB和MyISAM引擎等待时间：执行Sql等待放回结果之间的等待时间TPS =(COM_COMMIT + CO...
复制链接

扫一扫