MySQL学习

Penguin QQ.

已于 2023-09-19 21:18:35 修改

阅读量64

点赞数

文章标签： mysql 学习

于 2023-09-13 09:01:12 首次发布

本文链接：https://blog.csdn.net/qq_69990421/article/details/132844803

版权

MySQL学习

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾
SQL语句可以使用空格/缩进来增强语句的可读性
MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
注释：
- 单行注释： -- 注释内容或# 注释内容
- 多行注释：/注释内容/

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语句，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语句，用来对数据库表中的数据进行增删改查
DQL	Data Query Language	数据查询语言，用来查询数据库表中的记录
DCL	Data Control Language	数据控制语言，用来创建数据用户、控制数据库的访问权限

DDL

操作数据库

创建数据库

 CREATE DATABASE[IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];

删除数据库

 DROP DATABASE[IF EXISTS] 数据库名；

使用数据库

 USE 数据库名；

操作表

查询当前数据库所有的表

 SHOW TABLES;

查询表结构

 DESC 表名；

查询指定表的建表语句

 SHOW CREATE TABLE 表名；

创建表

 CREATE TABLE 表名(
 字段1 字段1类型[COMMENT 字段1注释] ，
 字段2 字段2类型[COMMENT 字段2注释] ，
 字段3 字段3类型[COMMENT 字段3注释],
     ...
 字段n 字段n类型[COMMENT 字段n注释]
 )[COMMENT 表注释];

修改表

添加字段

 ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

修改字段名

 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];

删除字段

 ALTER TABLE 表名 DROP 字段名

修改表名

 ALTER TABLE 表名 RENAME TO 新表名

删除表

 DROP TABLE [IF EXISTS] 表名;

表的数据类型

数值类型

类型	大小	有符号范围	无符号范围	描述
TINYINT	1 byte	(-128，127)	(0,255)	小整数值
SAMLLINT	2 bytes	(-32768，32767)	(0,65535)	大整数值
INT或INTEGER	4 bytes	(-2147483648，2147483647)	(0，4294967295)	大整数值
BIGINT	8 bytes	(-2^63，2^63-1)	(0，2^64-1)	极大整数值
FLOAT	4 bytes	(-3.402823466E+38，3.402823466351E+38)	0和(1.175494351E-38，3.402823466E+38)	单精度浮点整数值
DOUBLE	8 bytes	(-1.7976931348623157E+308，1.7976931348623157E+308)	0和(2.2250738585072014E-308，1.7976931348623157E+308)	双精度浮点整数值

字符串类型

类型	大小	描述
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TEXT	0-65535 bytes	长文本数据
TINYTEXT	0-255 bytes	短文本字符串

对于char类型数据而言，char(10)，如果只存入了一个字符，那么剩下的字符将用空格暂替，也就说无论如何都将沾满10个比特位。而varchar(10)只存储了一个字符就只占用一个字段，但是相比较起来char的性能更好，因为varchar需要去计算要用到的内存空间

日期类型

类型	大小	范围	格式	描述
DATE	3	1000-01-01至9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59 至 838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901-2155	YYYY	年份值
DATETIME	8	1000-01-0100：00：00至9999-12-3123：59：59	YYYY-MM-DD HH:MM:SS	混合日期和时间值

DML

添加数据(INSERT)

部分添加

 INSERT INTO 表名(字段1,字段2,....) VALUES (值1,值2,....)

所有字段添加

 INSERT INTO 表名 VALUES(值1，值2)

批量添加

 INSERT INTO 表名(字段名1,字段名2) VALUES(值,值2,)(值1,值2,)(值1,值2.);

 INSERT INTO 表名 VALUES(值1,值2,)(值1,值2,.).(值1,值2,);

修改数据(UPDATE)

 UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];

删除数据(DELETE)

 DELETE FROM 表名 [WHERE 条件]

DQL

查询语句

 SELECT
 字段列表
 FROM
 表名列表
 WHERE
 条件列表
 GROUP BY
 分组字段列表
 HAVING
 分组后条件列表
 ORDER BY
 排序字段列表
 LIMIT
 分页参数

查询多个字段

 SELECT 字段1,字段2,字段3...FROM 表名;

 SELECT * FROM 表名;

设置别名

 SELECT 字段1 [AS 别名1],字段2 [AS 别名2]... FROM 表名;

去除重复的记录

 SELECT DISTINCT 字段列表 FROM 表名;

条件查询

 SELECT 字段列表 FROM 表名 WHERE 条件列表;

聚合函数

概念

将一列数据作为一个整体，进行纵向计算

常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

语法

 SELECT 聚合函数(字段列表) FROM 表名;

所有的null值不参与计算

分组查询

语法

 SELECT 字段列表 FROM 表名 [WHERE 条件]GROUP BY 分组字段名 [HAVING 分组后过滤条件]

where和having

执行时机：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
判断条件不同：where不能对聚合函数进行判断，而having可以

排序查询

语法

 SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2:

分页查询

语法

 SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;

起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，mysql中的是limit

如果查询的是第一页数据，起始索引可以省略，直接简写limit 10

函数

字符串函数

函数	功能
CONCAT(S1,S2,...Sn)	宇符串拼接，将S1,S2，...Sn拼接成一个字符申
LOWER(str)	将字符串str全部转为小写
UPPER(str)	将宁符串str全部转为大写
LPAD(str,n,pad)	左填充。用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)	右填充。用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

语法

 SELECT 函数(参数);

数值函数

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1内的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

日期函数

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date, INTERVALexpr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)	返回起始时间date1和结束时间date2之间的天数

流程控制函数

函数	功能
lF(value,t,f)	如果value为true，则返回t，否则返回f
IFNULL(value1，value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1] THEN [reg]]ELSE I default] END	如果val1为true，返回res1，...否则返回default默认值
CASE[expr] WHEN [val1] THEN [res1] ..ELSE[ default] END	如果expr的值等于val1，返回res1，...否则返回default默认值

约束

概念

约束时作用于表中字段上的规则，用于限制储存在表中的数据

目的

保证数据库中的数据正确、有效性和完整性

分类

约束	描述	关键字
非空约束	限制该字段的数据不能为null	NOT NULL
唯一约束	保证该字段的所有数据都是唯一、不重复的	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空且唯一	PRIMARY KEY
默认约束	保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束(8.0.16版本之后)	保证字段值满足某一个条件	CHECK
外键约束	用来让两张表的数据之间建立连接，保证数据的一致性和完整性	FOREIGN KEY

外键

概念

外键是用来让两张表之间建立连接的，从而保证数据的一致性和完整性

语法

 CREATE TABLE表名(
 字段名数据类型
     ...
 [CONSTRAINT] [外健名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
 );

 ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)；

删除

 ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

约束

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，知果有，则不允许删除/更新，与ES利T一致
RESTRICT	当在父表中删除/更新对应记录时，首先检查该记录思否有对应外健。如果有则不允许删更新。(与N口CIC则一致）
CASCADE	当在父表中删除/更新对应记录时，首先检查该记录思否有对应外健，如果有，则也删除/更新外键在子表中的记录。
SET NULL	当在父表中删除对应记录时，首先检查该记录思否有对应外健，如果有则设置子表中该外键值为null(这就要求该外键允许取null)
STE DEFAULT	父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)

多表查询

多表关系

一对多

部门与员工的关系

实现

再多的一方建立外键，指向一的一方的主键

多对多

学生与课程的关系
一个学生可以选择多个课程，一个课程可以供多个学生选择

实现

建立第三张中间表，中间表至少包含2个外键，分别关联两方主键

一对一

用户与用户详情

实现

在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

概念

指从多张表中查询数据

实现

需要消除无效的笛卡尔积

连接查询

内连接

相当于查询A、B交集部分数据

隐式内连接

 SELECT 字段列表 FROM 表1,表2 WHERE 条件...

显示内连接

 SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件.;

外连接

左外连接：查询左表所有的数据，以及两张表交际部分数据

 SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件..;

右外连接：查询右表所有的数据，以及两张表交集部分数据

 SELECT 字段列表 FROM 表1 right [OUTER] JOIN 表2 ON 条件..;

自连接

当前表与自身表的连接查询，自连接必须使用表别名

 SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;

子查询

sql语句中嵌套sql语句，称为嵌套查询，又称子查询

 SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2);

嵌套的语句可以是update/delete/insert/select中的任意一个

把多次查询的结果合并起来，形成一个新的结果集

 SELECT 字段列表 FROM 表A
 UNION[ALL]
 SELECT 字段列表 FROM 表B

对于联合查询的多张表的列数必须保持一致，字段类型也必须一致

事务

概念

事务是一组操作的集合，他是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或者撤销操作，即这些操作要么同时成功，要么同时失败

MySQL进阶

存储引擎

mysql体系结构

mysql服务端是分层的，第一层是连接层。是用来校验、检查、连接池的管理等，比如验证账号和密码
第二层是服务层
第三层是引擎层，更换mysql存储数据的引擎方式
第四层存储层，将数据存储到磁盘上，以及一些日志文件等

存储引擎介绍

介绍

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型
默认引擎就是InnoDB

指定引擎

语法如下：

 CREATE TABLE 表名(
 字段1 字段1类型 [COMMENT 字段1注释],
 字段n 字段n类型 [COMMENT 字段n注释]
 )ENGINE=INNODB[COMMENT表注释];

存储引擎特点

InnoDB

介绍

InnoDB是MySQL的默认存储引擎（从MySQL 5.5开始）。它支持事务、行级锁定、外键约束以及崩溃恢复等功能，适用于需要强调数据完整性和事务支持的应用

特点

DML操作支持ACID模型，支持事务
行级锁，提高并发访问性能
支持外键约束，保证数据的完整性和正确性

文件

在innoDB引擎下，每一张表都对应一个.ibd的文件，储存该表的表结构、数据和索引

MyISAM

介绍

MyISAM是MySQL的另一个常用存储引擎。它不支持事务和行级锁定，但具有较高的插入和查询速度。MyISAM适用于读写比例较低的应用，如日志记录或报表生成

特点

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

Memory

介绍

Memory引擎的表数据是存储在内存的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存起来

特点

内存存放
hash索引(默认)

文件

xxx.sdi文件，只存储表结构的信息，而具体的数据存储在内存中

引擎选择

InnoDB

是mysql的默认存储引擎，支持事务、外键，如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新，删除操作，那么InnoDB存储引擎是比较合适的选择

MyISAM

如果应用是以读写操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么就可以选择这个存储引擎

Memory

将所有教据保存在内存中，访问远度快、通常用于的临时表和缓存。MEMOY的缺陷就是对表的大小有限制，太大的表无法缓存内存中，而且无法保障数据安全

锁

概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除了传统的计算机资源(CPU、RAM、I/O)的争用以外，数据也是一种供许多用户共享的资源。
如何保证数据并发访问的一致性、有效性是所有数据必须解决的一个问题。
锁冲突也是影响数据库并发访问性能的一个重要因素。

全局锁

介绍

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

表级锁

表级锁，每次操作锁住整张表。锁定粒度大，发生锁冲突的概率高，并发度低。应用在MyISAM、InnoDB、BDB中

表锁

又可以分为：

表共享读锁:自己和别人都可以取数据，但不能写数据
表独占写锁：自己可以写和读数据，别人不能读也不能写

语法

加锁

 lock tables 表名...read/write

释放锁

 unlock tables /客户端断开连接

元数据锁

meta data lock (MDL)

MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。简单的说就是有事务还没有提交时，不能修改表的结构

注意

当一个事务去查询表的数据时，数据库会自动地加上共享读锁(MDL)，当对表结构进行变更操作时，加入MDL写锁(排它)EXCLUSIVE
共享读，共享写锁是互相兼容的，但与EXCLUSIVE是不兼容的，这也就导致了两个事务都可以查询数据和更新数据，但是不能修改表结构，因为修改表结构后会加EXCLUSIVE，与共享锁互斥，这个事务就会处于阻塞状态

意向锁

为了避免DML在执行时，加的行锁与表锁发生冲突，在InnoDB引擎中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查

当加了意向锁以后，另一个线程在加锁的话，如果这个锁和意向锁是不兼容的，那么另一个线程就会阻塞，一直到锁被释放

意向共享锁(IS)：由语句slect....lock in share mode添加；与共享读锁兼容，与独占写锁互斥;事务再给一个数据行加共享锁前必须先获取该表的IS锁
意向排它锁(IX):由insert、update、delete、select...for update添加；与共享锁读锁以及排他锁都互斥。意向锁之间不互斥；事务在给一个数据行加排他锁前必须获取该表的IX锁

行级锁

行级锁粒度最小，发生冲突的概率最低，并发度最高。应用在InnoDB引擎中

行级锁分为三类：

1.行锁：锁定的是当个行记录的锁，防止其他事务对此行进行update和delete。在RR，RC隔开级别下都支持

2.间隙锁：锁定的是两个记录间的间隙，不包含该记录，确保索引间的间隙不变，防止其他事务在这个间隙进行Insert，产生幻读。RR支持

3.临键锁：行锁和间隙锁组合，同时锁住数据，并锁住数据前面的间隙Gap,RR下支持

行锁

InnoDB支持两种类型的行锁：

共享锁(S)：允许一个事物去读一行，阻止其他事务获得相同数据集的排他锁(共享锁和共享锁兼容，和排他锁互斥)
排他锁(X)：允许获取排他锁的事务更新数据和读取数据，阻止其他事务获得相同数据集的共享锁和排他锁(阻止加其他锁)，也就是写锁和读锁(共享锁和共享锁兼容，排它和排它互斥)

SQL	行锁类型	说明
INSERT	排他锁	自动加锁
UPDATE	排他锁	自动加锁
DELETE	排他锁	自动加锁
SELECT	不加锁
SELECT ... LOCK IN SHARE MODE	共享锁	需要手动加
SELECT .. FOR UPDATE	排他锁	需要手动加

演示

默认情况下，InnoDB在RR默认隔离级别下，使用间隙锁进行搜索和索引扫描，以防止幻读

针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动的将间隙锁优化为行锁
InnoDB的行锁针对索引加的锁，不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，也就是升级为表锁
也就是说InnoDB的行锁是通过给表的索引字段加锁实现的

检索锁和临键锁演示

默认情况下，InnoDB在RR默认隔离级别下，使用间隙锁进行搜索和索引扫描，以防止幻读

索引上的等值查询(唯一索引)，给不存在的记录加锁时优化为间隙锁
索引上的等值查询(普通索引)，向右遍历时最后一个值不满足查询需求时，临键锁退化为间隙锁
索引上的范围查询(唯一索引)，会访问到不满足条件的第一个值为止

注意：间隙锁的目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙锁上采用间隙锁

锁的兼容情况

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及的数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。

InnoDB引擎

逻辑存储结构

表空间

idb文件，一个mysql实例可以有多个表空间，用于存储记录、索引等数据

段

分为数据段、索引段、回滚段，InnoDB是索引组织表，数据端就是叶子节点，索引段即为非叶子节点。段用来管理多个区(Extent)

区(Extent)

表空间的单元结构，每个区大小为1M。默认情况下，InnoDB存储引擎页大小为16K，即一个区中一共有64个连续的页

页

页是InnoDB引擎磁盘管理的最小单元，每个页的大小默认为16K。为了保证页的连续性，InnoDB引擎每次会从磁盘申请4-5个区

行

InnoDB存储引擎数据是按行进行存放的

存储架构

介绍

InnoDB的架构分为内存结构和磁盘结构

内存结构

Buffer Pool

缓冲池，是内存中一个主区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改时，先操作缓冲池的数据，若缓冲池中没有存储这个数据，再从磁盘中加载并缓存，然后在以一定的频率刷新到新的磁盘，从而减少磁盘IO，加快处理速度

Change Buffer

更改缓冲区，针对非唯一二级索引，在执行DML语句时，如果这些数据Page没在Buffer Pool里，不会直接操作磁盘，而是会将数据变更存在更改缓冲区中，在未来读取数据时，在将数据合并恢复到Buffer Pool中，再将合并后的数据刷新到磁盘中

作用

与聚集索引不同，二级索引通常时不唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页，如果每一次都操作磁盘，会造成大量的磁盘IO。有了change buffer后，我们可以在缓冲池中进行合并处理，减少磁盘IO

自适应hash索引

用于优化对buffer pool数据的查询。InnoDB引擎会监控对表上个索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引

自适应hash索引无需人工干预，是系统自动完成的

log buffer

日志缓冲区，用来保存要写入磁盘中的log日志数据，默认大小为16MB，日志缓冲区的日志会定期刷新到磁盘中。
如果需要更新、插入或删除多行的事务，增加缓冲的大小可以节省磁盘IO

磁盘结构

太多了，不想记

事务原理

事务

事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

事务特性

原子性

最小的操作单位，不可分割，要么全部成功，要么全部失败

一致性

事务完成时，必须使所有的数据都保持一致状态

隔离性

数据库系统提供的隔离机制，保证事务在不受外部并发操作的影响下独立运行

持久性

事务一旦提交或回滚，他对数据库中的数据的改变是永久的

具体实现

持久性

redo log

重做日志，记录的是事务提交时，数据页的物理修改，是用来实现事务的持久性

因为InnoDB作为mysql的数据存储引擎，数据都是存放在磁盘上的。但是如果每次读写数据都要到磁盘上读取，IO的开销会很大，效率低。因此，InnoDB提供了缓存池(buffer pool)，Buffer Pool中包含了磁盘中部分数据的映射，作为访问数据库的缓存；当从数据库读取数据时，会首先从Buffer Pool中读取，如果Buffer Pool中没有的话在到磁盘中读取，再将读取的数据缓存到Buffer Pool中。当写入数据库时，会首先写入Buffer Pool中，Buffer Pool中的数据会定期的刷新到磁盘上，这一过程称为刷脏，更新的数据页称为脏页

Buffer Pool虽然大大提高了mysql的效率，但是也带了一些问题，如果mysql宕机了导致内存中的数据被清楚没有被刷新到磁盘上，这样就会导致数据的丢失，事务的持久性无法保证

于是redo log被引进来解决这个问题：redo log由两部分组成，重做日志缓冲(redo log buffer)以及重做文件日志(redo log file)，前者是保存在内存中的，后者保存在磁盘中的，当事务提交以后，就会将redo log buffer中的数据变化刷新到磁盘的redo log file中，发生错误时，mysql就会扫描file中的数据以重新更新数据，以此来保证数据的一致性

原子性

undo log

实现原子性

回滚日志，用于记录数据被修改前的信息，作用包含两个：回滚事务和MVCC多版本并发控制

将所有对数据的修改（增、删、改）都写入日志（undo log）。如果一个事务中的一部分操作已经成功，但另一部分操作，由于断电/系统崩溃/其它的软硬件错误而无法成功执行，则通过回溯日志，将已经执行成功的操作撤销，从而达到全部操作失败的目的。undo log和redo log记录物理日志不同，undo log记录的是逻辑日志。可以理解为当有一条delete语句时，undo log中会记录一条插入语句，反之亦然。当执行rollback时，就可以从undo log中的逻辑记录中读取到相应的内容并进行回滚

隔离性

在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。

锁机制

隔离性要求同一时刻只能有一个事务对数据库进行操作，InnoDB通过锁机制来保证这一点。

锁机制可以概括为：事务在修改数据时，需要获取对应的锁；获得锁之后，事务便可以修改数据，该事务操作期间，这部分数据时锁定的，其他事务如果需要修改数据，需要等待当前事务提交或回滚后释放锁

行锁与表锁

表锁锁定的是整张表，并发性能较差；行锁只需要锁定要操作的数据，并发性能好。但由于加锁本身需要消耗资源，因此在锁定数据多的情况下可以使用表锁来节省资源。处于性能考虑，绝大部分都是使用行锁

一致性

一致性是指事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。数据库的完整性约束包括但不限于：实体完整性（如行的主键存在且唯一）、列完整性（如字段的类型、大小、长度要符合要求）、外键约束、用户自定义完整性（如转账前后，两个账户余额的和应该不变）
可以说，一致性是事务追求的最终目标：前面提到的原子性、持久性和隔离性，都是为了保证数据库状态的一致性。

引入事务出现的问题

并发事务处理能大大增加数据库资源的利用率，提高数据库系统的事务吞吐量，从而可以支持可以支持更多的用户。但并发事务处理也会带来一些问题，主要包括以下几种情况

脏读

一个事物读到了另一个事务未提交的更新数据

幻读

一个事务读到了另一个事务已提交的新增数据

不可重复读

一个事务读到另一个事务已提交的更新数据

以及更新丢失

这些问题大多都是由数据库一致性问题。为了解决这些问题，mysql提供了四种事务隔离机制：

串行化：事务之间以一种串行的方式执行，安全性非常高，效率低
RR可重复读：是MySQL默认的隔离级别，同一个事务中相同的查询会看到同样的数据行，安全性较高，效率较好
RC读已提交：一个事务可以读到另一个事务已经提交的数据，安全性较低，效率较高
RU读未提交：一个事务可以读到另一个事务未提交的数据，安全性低，效率高

这些方法的思想大致分为下面两种：

一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库

MVCC

介绍

多版本井发控制，是用来提高数据库并发性能，用更好的方式去处理读写冲突。做到即使有读写冲突时，也能做到不加锁，非阻塞并发读。
MVCC的具体实现依赖于数据库记录中三个隐式字段、undo log、readView

基本概念

当前读

读取的是记录的最新版本，读取时还要保证其他事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如：select .... lock in share mode(共享锁)

示例

当我们同时开启两个事务时，一个事务查询表中的数据，一个更改表中的一行数据，当事务提交了以后，第一个事务查询到的数据仍然是没有更新前的，当加了lock in share mode共享锁之后，第一个事务查询到的才是最新的数据，这就是当前读

快照读

简单的select就是快照读，快照读读取的是历史版本的，不加锁，非阻塞读

这其中就涉及到了隔离级别

RC，读已提交，读取的是已经提交的数据，也就是每次查询都生产一个新的快照
RR，可重复读，读取的是快照的数据
SERIALIZABLE：快照读退化为当前读

实现原理

隐式字段

当数据库的引擎生成一个表以后，它还会自动的给我们增加三个字段：DB_TRX_ID,DB_ROLL_PTR,DB_ROW_ID

隐式字段	含义
DB_TRX_ID	最近修改事务的id，记录这条记或最后一次修改该记录的事务id
DB_ROLL_PTR	回滚指针，指向这条记录的上一个版本，用于配合undo log，指向上一个版本
DB_ROW_ID	隐藏主键，如果表结构中没有主动设置主键，将会生产该隐式字段

undo log日志

回滚日志，当操作了之后就会产生
当Insert的时候，产生的undo log日志只会在回滚时需要，在事务提交后，可立即删除
而update、delete的时候，产生的undo log日志不仅在回滚时需要，在快照读时也需要，不会被立即删除

当修改了数据以后，会自动生成一个旧版本的数据保存在undo log日志中，而该条记录后的回滚指针指向的就是这个旧版本数据的地址，当再次修改了数据以后又会生成一个新的旧数据，这条新数据里面也保存了上一条的在undo log中保存的旧数据的地址，以此生了一条版本链

readView

readView是快照图SQL执行时MVCC提取数据的依据，记录并维护系统当前的活跃的事务id，

readView主要包含了四个核心字段

字段	含义
m_ids	当前获取的事务id集合
min_trx_id	最小活跃事务id
max_trx_id	预分配事务id，当前最大事务id+1
creator_trx_id	readView创建者的事务id

索引

概述

介绍

索引是帮助mysql高效获取数据的数据结构(有序)。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引

优缺点

优势	劣势
提高数据检索的效率，较低数据库的IO成本	索引列也要占用空间
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗	索引大大提高了查询效率，同时也降低了表的更新速率，如对表进行插入、更新、删除时，效率降低

索引结构

mysql索引是在存储引擎层实现的，不同的存储引擎有不同的结构。

常见索引

主要包含了一下几种：

索引结构	描述
B+Tree	最常见的索引类型，大部分引擎都支持B+树索引
Hash	底层数据结构使用哈斯表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。Memory引擎特有的
R-Tree(空间索引)	空间索引是MyISAM引擎的一种特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene,ES

二叉树

缺点：当顺序插入时，会形成一个链表，查询性能大大降低。大数据情况下，层级较深，检索速度慢

解决方法———红黑树

但红黑树也存在问题，就是在大数据量的情况下，层级较深，检索速度慢

BTree

每一个节点下最多存在5个子节点，也就是5阶，每个节点最多存储4个key，5个指针

创建过程：B-Tree Visualization

B+Tree

这是一个4阶的B+树示例图

所有的数据都会出现叶子节点，上面的节点主要起到索引的作用，而叶子节点是用来存放数据的。
所有的叶子节点会形成一个双向循环链表

Hash

先计算一行的hash值，然后每个数据根据内部的hash函数进行运算，得到具体的槽位，在这个槽位中存储的就是这个数据以及对应的行的hash值
如果映射到了同一个槽位上，可以通过链表来解决

特点

Hash索引只能用于对等比较，不支持范围查询
无法利用索引完成排序操作
查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引

索引分类

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT

具体演示

有主键的情况下，主键就是聚集索引，每个叶子节点所存放的数据就是对应的行的数据

没有主键的情况下，就是二级索引，所存储的数据就是对应的id

假设要查找一个非id的字段的值，首先根据二级索引查找到对应的id值，然后再到聚集索引中根据id查找对应的行的值

索引语法

创建索引

语法

CREATE[UNIQUE|FULLTEXT]INDEX index_name ON table_name (index_col_name);

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX index_name ON table_name;

索引失效

索引列运算

不要再索引列上进行运算操作，否则索引列将失效

explain select * from lb_user where substing(phone,10,2) = 15';

字符串不加引号

字符串类型字段使用时，不加引号，索引将失效

模糊查询

如果是尾部进行模糊查询匹配，索引不会失效。如果是头部模糊匹配，索引失效。

explain select * from tb_user where profession like '软件%';
explain select * from tb_user where profession like '%工程';
explain select * from tb_user where profession like '%工%';

or连接条件

用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。只有两边都有索引时才会生效

explain select * from tb_user where id=10 or age=23;
explain select * from tb_user where 'phones1779999o17' or age=23;

数据分布影响

如果mysql评估使用索引比全表更慢，则不使用索引

SQL提示

SQL提示，是优化数据库的一种手段，简单地说就是在sql语句中加入一些人为的提示来优化操作目的

use index

explain select * from tb_user use index(idx_user pro) where profession ='软件工程';

ignore index

explain select * from tb_user ignore index(iox_user_pro) where pnofession '软件工';

force index

explain select * from tb_user force index(idx_user_pro) where professton ='软件工程';

但仅仅只是推荐，具体如何还得看mysql自己评估，除了force（强制）

覆盖索引

尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中能够全部找到），减少select *；

explain select id,profession from tb_user where profession = '软件工程' and age = 31 and status = '0';
explain select id,profession,age,status from tb_user where profession ='软件工程' and age = 31 and status= 'O';

注意：

using index condition:查找使用了索引，但是需要回表查询数据

using where;using index:查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询

前缀索引

当字段类型为字符类型时(char,varchar)，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的IO,影响查询效率。
此时我们可以之将字符串的一部分前缀，建立索引，这样就可以大大节约索引空间，从而提高效率

语法

create index idx_xxxx on table_name(column(n));

前缀长度

可以根据索引的选择性来决定，而选择性是指不重复的索引值和数据表的记录总数的比值，索引选择性越高查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的

select count(distnct email) / count(*) from tb_user;
select count(distinct substring(email,1,5))/ count(*) from tb_user;

单列/联合索引

单列索引

及一个索引包含单个列

联合索引

及一个索引包含多个列

联合索引流程

创建索引

create unique index idx_phone_name on tb_user(phone，name)；

使用索引

索引设计原则

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

SQL优化

插入数据

批量插入

Insert into tb_test values(1,'Tom'),(2,'Cat'),(3,yerry');

手动提交事务

start transaction;
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,Jerry');
insert into tb_test values(4,'Tom'),(5,'Cat'),(6,Jerry');
insert into tb_test values(7,'Tom'),(8,'Cat'),(9,Jerry');
commit;