MySQL_mysql 读已提交

MySQL

一、数据库操作

1.1、查询数据库

1.1.1、查看所有数据库

show databases;
1

1.1.2、查看当前数据库

select database();
1

1.2、创建数据库

create database [if not exists] $数据库名$ [character set utf8mb4] [collate utf8mb4_general_ci];
1

1.3、选择数据库

use $数据库名$;
1

1.4、删除数据库

drop database [if exists] $数据库名$;
1

二、表操作

2.1、查询表

2.1.1、查询数据库中所有表

show tables;
1

2.1.2、查询数据库中所有表的状态

show table status;
1

2.1.3、查询表结构

desc $表名$
1

2.1.4、查询表的建表语句

show create table $表名$
1

2.1.5、查看表中所有字段信息

show full columns from $表名$
1

2.2、创建表

-- 语法格式
create table [if not exists] $表名$ (
    $字段名$ $数据类型$ [约束] [comment $字段注释$],
    $字段名$ $数据类型$ [约束] [comment $字段注释$],
    ......
    $字段名$ $数据类型$ [约束] [comment $字段注释$]
) engine = InnoDB
  character set = utf8mb4
  collate = utf8mb4_general_ci 
  comment = $表注释$;
-- 示例
create table student
(
    id           bigint unsigned not null auto_increment comment '主键id',
    name         varchar(20)     not null default '' comment '学生姓名',
    sex          varchar(1)      not null default '男' comment '学生性别:男,女',
    age          tinyint(3)      not null default 0 comment '学生年龄',
    score        decimal(5, 2)            default 0.00 comment '学生成绩',
    teacher_id   bigint          not null comment '教师主键id',
    gmt_create   datetime        not null default current_timestamp comment '数据创建时间',
    gmt_modified datetime        not null default current_timestamp on update current_timestamp comment '数据修改时间',
    is_delete    tinyint(1)      not null default 0 comment '逻辑删除:0-未删除, 1-已删除',
    primary key (id)
) engine = InnoDB
  character set = utf8mb4
  collate = utf8mb4_general_ci comment = '学生表';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2.3、修改表

2.3.1、添加字段

alter table $表名$ add column $字段名$数据类型$ [约束] [comment $字段注释$];
1

2.3.2、修改字段类型

alter table $表名$ modify column $字段名$ $数据类型$ [约束] [comment $字段注释$];
1

2.3.3、修改字段名和类型

alter table $表名$ change $旧字段名$ $新字段名$ $数据类型$ [约束] [comment $字段注释$];
1

2.3.4、删除字段

alter table $表名$ drop column $字段名$;
1

2.3.5、修改表名

alter table $表名$ rename to $新表名$;
1

2.3.6、修改表的字符集

alter table $表名$ character set $字符集$;
1

2.3.7、修改表的自增值

alter table student auto_increment = $自增值$
1

2.4、删除表

drop table [if exists] $表名$;
1

2.5、截断表

truncate table $表名$
1

三、数据操作

3.1、查询数据

3.1.1、执行顺序

from—>where—>group by—>having—>select—>order by—>limit

3.1.2、基本语法

select [distinct] [count(*)/count(1)/count(字段名)] [avg(字段名), max(字段名),min(字段名),sum(字段名)]$字段列表$ 
from $表名$ [as 别名]
[inner/left/right join 表名 [as 别名] on 条件] m 
[where 条件列表]
[group by 分组字段列表]
[having 分组后条件列表]
[order by 排序字段列表 [asc/desc]]
[limit [起始索引] 数据条数]
1
2
3
4
5
6
7
8

3.2、添加数据

# 插入一条数据,指定字段名
insert into $表名$ ($字段名1$, $字段名2$,...... ,$字段名n$) values ($值1$,$值2$,......,$值n$);
# 插入一条数据,表中所有字段
insert into $表名$ values ($值1$,$值2$,......,$值n$);
# 插入多条数据,指定字段名
insert into $表名$ ($字段名1$, $字段名2$,...... ,$字段名n$) 
values ($值1$,$值2$,......,$值n$),
($值1$,$值2$,......,$值n$),
($值1$,$值2$,......,$值n$);
# 插入多条数据,表中所有字段名
insert into $表名$
values ($值1$,$值2$,......,$值n$),
($值1$,$值2$,......,$值n$),
($值1$,$值2$,......,$值n$);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

3.3、修改数据

update $表名$ set $字段1$ = $值1$, $字段2$ = $值2$,......,$字段n$ = $值n$ [where 条件];
1

3.4、删除数据

delete from $表名$ [where 条件];
1

四、事务

4.1、事务特性

• 原子性（Atomicity）：事务是一个不可分割的工作单位，要么同时成功，要么同时失败。例：当两个人发起转账业务时，如果A转账发起，而B因为一些原因不能成功接受，事务最终将不会提交，则A和B的请求最终不会成功。
• 持久性（Consistency）：一旦事务提交，他对数据库的改变就是永久的。注：只要提交了事务，将会对数据库的数据进行永久性刷新。
• 隔离性（Isolation）：多个事务之间相互隔离的，互不干扰
• 一致性（Durability）：事务执行接收之后，数据库完整性不被破坏

4.2、事务操作

4.2.1、查看事务的提交方式

# 1代表自动提交 0代表手动提交
select @@autocommit;
1
2

4.2.2、设置事务的提交方式

# 1代表自动提交 0代表手动提交
set @@autocommit = 1; 
1
2

4.2.3、开启事务

start transaction或begin;
1

4.2.4、提交事务

commit;
1

4.2.5、回滚事务

rollback;
1

4.3、并发事务问题

4.3.1、脏读

• 一个事务读到另一个事物还没有提交的数据

4.3.2、幻读

• 一个事务先后读取同一条记录，但两次读到的数据不同

4.3.3、不可重复读

• 一个事务按照条件查询数据时，没有对应的数据行，但是插入数据时，又发现这行数据已存在

4.4、事务隔离级别

4.4.1、概述

​ 数据库事务的隔离级别有4种，由低到高分别为Read Uncommited、Read Commited、Repeatable Read、Serializable。并发数据访问时可能会出现以下问题，3类数据读取问题（脏读、不可重复读、幻读）和2类数据更新问题（第1类丢失更新和第2类丢失更新）。

4.4.2、隔离级别

• 读未提交（Read Uncommited）：即一个事务可以读取另一个未提交事务的数据；并发操作会导致脏读
• 读已提交（Read Commited）：即一个事务要等到另一个事务提交后才能读取数据；解决脏读问题；并发操作会导致不可重复读
• 可重复读（Repeatable Read）：即开始读取数据（事务开启）时，不再允许修改操作；解决不可重复读问题；并发操作会导致幻读（对应insert操作），MySQL默认隔离级别
• 串行化（Serializable）：最高的事务隔离级别，该级别下，事务串行化顺序执行；避免脏读、不可重复读与幻读；但是该级别效率低下，比较消耗数据库性能，一般不用。

4.4.3、隔离级别并发问题

4.4.4、隔离级别操作

4.4.4.1、查看事务隔离级别

select @@transaction_isolation;
1

4.4.4.2、设置事务隔离级别

set [session | global] transaction isolation level [read uncommitted | read committed | repeatable read | serializable];
1

4.4.5、隔离级别演示

4.4.5.1、读未提交

• 出现脏读问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gPeUcFlh-1665646331265)(mysql\读未提交.png)]

4.4.5.2、读已提交

• 解决脏读问题，出现不可重复读问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xL4ObZZF-1665646331269)(mysql\读已提交.png)]

4.4.5.3、可重复读

• 解决不可重复读问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Sqax2NrZ-1665646331273)(mysql\可重复读.png)]

• 出现幻读问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-utLzPoSV-1665646331280)(mysql\可重复读-幻读.png)]

4.4.5.4、串行化

• 解决幻读问题

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zMCVfYIj-1665646331283)(E:\Code\笔记\mysql\串行化.png)]

5、存储引擎

• 查看存储引擎

show engines;
1

• InnoDB

  • 特点:
    • DML操作村寻ACID模型，支持事务
    • 行级锁，提高并发访问性能
    • 支持外键约束，保证数据的完整性和正确性
  • 逻辑结构图

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-h8c5EPB6-1665646331287)(E:\Code\笔记\mysql\InnoDB结构.png)]

• MyISAM

  • 特点
    • 不支持事务，不支持外键
    • 支持表锁，不支持行锁
    • 访问速度快

• Memory

  • 特点
    • Memory引擎的表数据存在内存中，由于受到硬件问题、或断电问题的影响，只能将这些表临时表或缓存使用
    • Hash索引（默认）

• InnoDB、MyISAM、Memory对比

6、索引

6.1、索引概述

• 优缺点
  • 提高数据检索的效率，降低数据库的IO成本
  • 通过索引对数据进行排序，降级数据排序的成本，降低CPU的消耗
  • 索引需要占用空间
  • 索引大大提高了查询效率，但是降低了更新表的速率

6.2、索引结构

• B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
• Hash索引：底层数据结构是哈希表实现的，只有精确匹配的查询才有效，不支持范围查询
• R-Tree（空间索引）：空间索引是MyISAM引擎的一个特殊索引，主要用于地理空间类型，通常使用较少
• Full-Text（全文索引）：是一种通过简历倒排索引，快速匹配文档的方式，类似于Lucene，Solar，ES

6.3、索引分类

• 聚集索引的选取规则
  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一索引作为聚集索引
  • 如果没有主键，也没有合适的唯一索引，则InnoDB会自动生成一个隐藏的rowid作为聚集索引
• 聚集索引与二级索引示例图

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aH8JVqoO-1665646331292)(E:\Code\笔记\mysql\索引分类.png)]

6.4、索引操作

6.4.1、创建索引

create [unique | fulltext] index $index_name$ on $table_name$(column_name1,column_name2...);
1

6.4.2、查看索引

show index from $table_name$;
1

6.4.3、删除索引

drop index $index_name$ on $table_name$;
1

6.5、性能分析

6.5.1、查看SQL执行频率

show global status like 'Com_______';
1

6.5.2、慢查询日志

# 查看慢查询日志的状态
show variables like 'slow_query_log';
# 开启慢查询日志
set global slow_query_log = 1;
1
2
3
4

6.5.3、profile详情

# 查看数据库是否支持profile操作
select @@have_profiling;
# 查看profiling状态
select @@profiling;
#开启profiling
set profiling = 1;
# 查看每一条sql耗时的基本情况
show profiles;
# 查看指定的sql各个阶段的耗时情况
show profile for query $query_id$
# 查看指定的sql的CPU使用情况
show profile cpu for query $query_id$
1
2
3
4
5
6
7
8
9
10
11
12

6.5.4、explain执行计划

explain select $字段名$ from $表名$ where $条件$;
1

• 各字段含义

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lcsCHcT3-1665646331295)(E:\Code\笔记\mysql\explain.png)]

  • id：select查询的序列号，表示查询中执行select语句或者是操作表的顺序（id相同，执行顺序从上到下，id不同，值越大越先执行）

  • select_type

    • simple：简单表，即不使用表连接或者子查询
    • primary：主查询，即外层的查询
    • union：union中的第二个或者后面的查询语句
    • subquery：子查询

  • table：查询的表名

  • type：表示连接类型，性能由好到差分别是null，system，const，eq_ref，ref，range，index，all

    • null：未查询表中数据，一般业务不会出现null
    • system：查询系统表
    • const：根据主键或者唯一索引查询
    • eq_ref：
    • ref：使用非唯一性的索引查询
    • 7range：
    • index：遍历整个索引树
    • all：查询所有数据

  • possible_keys：显示可能应用在这张表上的索引，一个或多个

  • key：实际用到的索引，如果为null表示没有使用索引

  • key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度

  • rows：MySQL认为必须要执行查询的次数，在InnoDB引擎的表中，是一个估计值，可能结果并不准确

  • filtered：查询返回结果的函数占需读取行数的百分比，filtered值越大越好

6.6、最左匹配原则

• 如果索引了多列（联合索引），要遵循最左匹配原则，最左匹配法则是指查询从索引中的最左列开始，且不跳过索引中的列，如果跳跃某一列，索引将部分失效（后面的字段索引失效）

6.7、索引失效

• 索引列上进行运算操作
• 字符串不加引号
• 尾部模糊查询索引不会失效，头部模糊查询索引失效
• 用or连接的条件，如果or前的条件中的列有索引，而后面的列中没有索引，name索引全部失效
• 使用索引比比全表扫描还要慢，则不使用索引

6.8、指定索引

• 使用指定索引（建议使用，不一定使用）

select * from $table_name$ use index($index_name$) $where语句$;
1

• 不使用指定索引

select * from $table_name$ ignore index($index_name$) $where语句$;
1

• 必须使用指定索引（一定使用）

select * from $table_name$ force index($index_name$) $where语句$;
1

6.9、前缀索引

• 创建前缀索引

create index $index_name$ on $table_name$($column(n)$);
1

6.10、索引设计原则

• 针对数据量较大，且查询比较频繁的表建立索引
• 针对常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引
• 尽量选择区分度高的列建立索引，尽量建立唯一索引，区分度越高，使用索引效率越高
• 如果是字符串类型的字段，字段的长度较长，可以针对字段的特点建立前缀索引
• 尽量使用联合索引，减少单列索引，查询时联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
• 要控制索引的数量，索引并不是多多益善，索引越多，维护索引的代价就越大，会影响增删改的效率
• 如果索引列不能存储null值，在创建表时使用not null约束，当优化器知道每列是否包含null值时，它可以更好地确定哪个索引最有效地用于查询

7、锁

7.1、全局锁

• 全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML语句、DDL语句、已经更新操作的事务提交语句都会被阻塞
• 其典型应用场景就是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
• 全局锁操作

# 加全局锁
flush tables with read lock;
# 备份数据库
mysqldump -uroot -p1234 jdbc > D:/1.sql;
# 释放全局锁
unlock tables;
1
2
3
4
5
6

7.2、表级锁

• 表级锁，每次操作锁住整张表，锁定粒度大，发生冲突的概率最高，并发度最低

• 分类

  • 表锁

    • 表共享读锁（read lock）

      所有客户端只能进行读取数据， 当前客户端不能进行写操作，其他客户端写操作阻塞，待当前客户端释放锁之后进行写操作

    • 表独占写锁（write lock）

      当前客户端既能读取数据又能写入数据， 其他客户端读写操作阻塞，待当前客户端释放锁之后进行读写操作

    # 加锁
    lock tables $table_name$ read/write;
    # 释放锁
    unlock tables;
    1
    2
    3
    4

  • 元数据锁（meta data lock，MDL）

    • MDL加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上
    • MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作
    • 为了避免DML与DDL冲突，保证读写的正确性

  • 意向锁

    • 为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
    • 意向共享锁（IS）：由语句select … lock in share mode 添加，与表锁共享锁（read）兼容，与表锁排它锁（write）互斥
    • 意向排它锁（IX）：由insert、update、delete、select … for update添加，与表锁共享锁（read）及表锁排它锁（write）都互斥，意向锁之间不会互斥

7.3、行级锁

• 行级锁，每次操作锁住对应的行数据，锁定粒度最小，发生冲突的概率最低，并发度最高
• InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁
• 行级锁分类
  • 行锁：锁定单个航记录的锁，防止其他事物对此进行修改和删除，在隔离级别读已提交，可重复中均支持
    • 针对唯一索引进行检索时，对已存在的记录进行等值匹配，将会自动优化为行锁
    • InnoDB的行锁是针对于索引假的锁，不通过索引条件检索数据，那么InnoDB会对表中所有记录都加锁，此时就会升级为表锁
  • 间隙锁：锁定索引记录的间隙（不含该记录），确保索引记录间隙不变，防止其他事物在这个间隙中增加数据，产生幻读，在隔离级别可重复读中支持
    • 索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隙锁
    • 索引上的等值查询（普通索引），享有便利是最后一个值不满足查询条件时，next-key lock退化为间隙锁
    • 索引上的范围查询（唯一索引），会访问到不满足条件的第一个值为止
  • 临键锁：行锁和间隙锁的组合，同时锁住数据，并锁住数据前的间隙，在隔离级别可重复读中支持
• 共享锁和排它锁
  • 共享锁：允许一个事务去读一行，阻止其他事物获得相同数据集的排它锁
  • 排它锁：允许获取排它锁的事务更新数据，阻止其他事物获取相同数据集的共享锁和排它锁