2022-2023年大二上mysql学习汇总

CRUD等操作（DDL、DML、DQL）

权限操作：

　　create user 用户名@"localhost或%" identified by '密码' 　show grants for 用户名@主机名　　grant 权限列表(all/insert/delete/select等) on 库名(*).表名(*) to 用户名@主机名 remove 与授予一样

函数：

　　内置(后面加as 可以使函数别名)：

　　　　字符串：concat拼接 substring截取 lower/upper大小写 l/r pad用某个字符填充 trim去头尾空格

　　　　　数值：ceil / floor向上/下取整 round四舍五入 mod rand 0~1随机数

　　　　　日期：curdate/curtime 当前日期/时间 now year/month/day date_add在指定时间加指定时间 datediff日期相差天数

　　　　　流程：if ifnull返回不为空的值 case when [val] then [res] ... else [default] end 如果val为true，返回res，否则default（when...then可以多个）

约束：

　　非空not null、唯一unique、主键primary key、默认default、检查check（满足某个条件）、外键foreign key、自增auto_increment

　　添加外键：alter table 表名 add constraint 外键名称(如fk_表名_列名) foreign key （列）references 主表（列） [添加行为 on update/on delete 有no action/restrict/cascade/set null/set default]

　　删除外键：alter table 表名 drop foreign key 外键名称

多表：一对多是通过多表做外键、多对多是建立中间表做外键、一对一是单表拆分后在其中一张表中创建外键联系另一张

　　内连接(交集)：隐式select 字段 from 表1, 表2 where ... 显示select 字段 from 表1 [inner]join 表2 on 条件

　　左/右外连接：select 字段 from 表1 left/right [outer] join 表2 on 条件

　　联合union和or的区别：用union在通常情况下比用or的效率要高的多，因为where里面用or会引起全表扫描，也有特殊情况

　　子查询 = > <等 in，not in，all，any等

事务：

　　select @@autocommit;查询事务 set @@autocommit;设置当前窗口的sql语句手动提交 commit;提交事务 rollback;回滚事务 start transaction/begin;开启事务

　　四大特性：ACID

　　并发事务问题：脏读（读到未提交事务）、不可重复读（在同一未提交事务中查询不一致）、幻读（因为未保存事务，查询还是之前事务的模样，另一事务更改后，原来事务无法提交成功）

　　隔离级别（锁机制）：select @@TRANSACTION_ISOLATION查看隔离级别 set [session当前|global所有客户端] transaction isolation level 隔离级别 RU(读未提交)RC(读已提交)RR(可重复读)serializable(串行化)

存储引擎：

　　mysql体系结构分连接层、服务层、引擎层（索引在引擎层实现的）、存储层

　　引擎是基于数据库中的表的，也被称为表结构 show engines查询所有引擎（也能直接show create table 表名查询整张表的结构）

　　innodb特性：插入缓冲、自适应哈希、双写入、异步io、刷新邻接页

　　引擎类型：

　　　　innoDB：mysql默认引擎，具备高可靠、高性能，特点：DML遵循ACID，支持事务；行级锁，提高并发性能；支持外键约束；

　　　　　　　　引擎为innoDb的表，data目录中每个库里面对应的表都有表.idb 文件（表空间文件），里面有表结构、数据、索引，对应mysql参数 innodb_file_per_table

　　　　MyISAM：早期默认引擎，特点：不支持事务和外键；支持表锁、不支持行锁；访问速度快

　　　　　　　　引擎为MyISAM的，表文件中xxx.sdi文件存放表空间，xxx.MYD存放数据，xxx.MYI 存放索引

　　　　Memory：存放在内存中，易丢失；特点：访问速度快，hash索引

　　　　　　　　引擎为Memory的，表中只有xxx.sdi存放表结构，因为在内存中不存放数据

　　引擎选择：innoDB优点就是支持事务、外键、行级锁，MyISAM可被mongodb取代，Memory可被redis取代

索引：优：提高查询效率，减少cpu消耗，缺：占用空间，更新表减慢 所以建议在查询次数多的建立

　　索引类型：B+Tree（在B-Tree基础上所有元素都会出现在叶子结点上，数据都在叶子结点上，叶子结点形成单向链表）最常见，Hash（InnoDB不支持但具有自适应hash功能，Memory支持）不支持范围索引，B-Tree(从下往上插入节点)

　　B+树相对于二叉树层级更少，效率更高，对于B-树因为除了叶子结点存放数据，上面的结点本应存放数据的空间空出，可以存放更多的键和指针，适当的控制树的高度

　　索引分类：

　　　　聚集索引（Clustered Index）必须且只有一个、二级索引（辅助/非聚集）可以多个

　　　　聚集索引选取：主键索引、没主键选第一个唯一索引、都没有会自动生成隐藏的rowid

　　　　如果需要查询行数据最好用主键索引查询，效率会快很多

　　create index 索引名 on 表(字段，[字段]...)[规定asc和desc] 创建索引 show index from 表查看索引 drop index 索引名 on 表名

　　SQL性能分析：

　　　　执行频率：主要看查询次数以及增删改次数的比重来选择优化方案，show global/session status like 'Com_______(7个_)';

　　　　慢查询日志：默认关闭，用来查询时间超过指定时间的sql语句。linux中如需开启需在/etc/mysql.conf.d/mysqld.cnf中配置 slow_query_log=1#开启 long_query_time=2设置超过2秒的为慢查询（不写默认10秒），记录下来

　　　　　　　　　　show variables like 'slow_query_log'查看是否开启慢查询

　　　　profile：查看时间都消耗到哪里了。show @@having_profiling查看是否支持profile，set profiling=1开启profiling show profiles查看每条sql语句耗时情况 show profile [cpu] for query profile的id 用来查看sql语句具体情况

　　　　执行计划explain：在sql语句前面加上explain/desc，其中返回的type字段表示连接类型，性能由好到坏为NULL、system、const、eq_ref、ref、range、index、all（全表扫描）另外filtered字段表示返回结果的行数占需读行数的百分比，越大越好，key字段是执行查询语句实际用到的索引

　　索引使用：

　　　　最左前缀法则：复合索引创建之后，在执行查询语句的时候必须包含最左边的字段，并且连续向右（但顺序无所谓）

　　　　范围查询：也是跟复合索引有关，如果业务允许情况下，尽量用>=代替>等

　　　　在查询时，对索引字段进行运算操作（包括函数运算）会使索引失效

　　　　对于索引是字符串类型时，在查询该字段时不加‘’引号能查出，但会失效

　　　　对于模糊匹配，进行尾部模糊索引不会失效，头部会

　　　　对于查询中含有or，如果前字段有索引，后字段无，则索引都失效；两个字段都有索引则都不失效

　　　　数据分布影响：当索引查询的速度慢于全盘扫描，则不用索引查询

　　　　sql提示：sql会自动选择索引，但也可以通过提示来指定使用哪个索引在查询语句的表名之后跟上建议用/ignore不用/force必须 index(索引名)

　　　　覆盖索引：尽量使用覆盖索引即查询出的字段都有索引，不用select * ，可能会用到回表查询（先走二级索引，拿到数据后在走聚集索引）

　　　　前缀索引：当为字段类型为字符串做索引时，有时字符串过长会造成空间浪费，可以取前缀 create index 索引名 on 表（column(n)) n利用截取函数对比整体来看到第几个会开始没重复

　　　　如果查询多个字段，并且这几个字段含有联合索引，建议使用联合索引而不是单表查询

　　索引下推：将服务层的需要做的筛选在引擎层（索引）完成，为了减少回表次数，减少io操作

SQL优化：

　　插入优化：尽量批量插入，手动提交事务start transaction -> commit ，主键顺序插入

　　　　如需本地文件批量插入用load登录mysql时mysql --local-infile -u root -p；show@@local_infile查看是否开启；set global local_infile开启，load data local infile '路径' into table 表名 fields terminated by '每列分隔符' line terminated by '每行分隔符'；

　　主键优化：

　　　　数据组织方式：在innoDB中表数据根据主键顺序存放，叫索引组织表。页分裂和页合并。

　　　　尽量降低主键的长度，尽量顺序插入，尽量不改主键

　　order by优化：using filesort不是通过索引直接返回结果，using index通过索引直接排序，通过explain查询执行计划的最后一个字段查看

　　　　如果需要排序的字段在索引中（覆盖索引）且符合索引中升序还是降序，那么就为using index

　　　　排序缓冲区：大数据量排序，可以增加sort_buffer_size参数

　　group by优化：使用覆盖索引

　　limit优化：查询分页加子查询

　　count优化：没有很好的方式，可以自己维护一张表里面有个字段记录数据条数，注意count(1)是总条数

　　update优化：update会加锁，对于筛选字段(where)有索引加行锁，没有索引加表锁，尽量避免行锁升级为表锁

存储对象：

　　视图： 创建视图：create [or replace] view 视图名 as select语句（其中在字段名后面加上from 视图名就可以继承视图） [with [cascaded | local] check option检查选项] 查看创建视图语句：show create view 视图名查看视图数据：select * from 视图名修改视图：在创建视图中加上or replace 或者 alter 删除视图：drop

　　　　　　检查选项：检查增加修改是否符合select语句

　　　　　　视图如果要更新，则视图中的行与基础表中必须是一对一的关系（distinct，group by，having等就不行）

　　　　　　视图作用：操作简单（复杂的查询可以在原有的视图上操作）、安全（让某人开发某功能指定字段，看不见其他字段）、数据独立

　　存储过程：将sql语言层面的代码封装与重用

　　　　　　创建存储过程：create procedure 存储名称([参数列表]) begin SQL语句 end；因为语句中也存在;分号，所以需要用delimiter指定结束符

　　　　　　调用：call 存储名称（[参数]）查看创建存储过程的sql语句：show create procedure 存储名称查看存储过程的状态：select * from information_schema.ROUTINES where ROUTINE_SCHEMA= 'xxx'　　　　删除存储过程：drop procedure [if exits] 存储名称

　　　　　　变量：系统变量@@：全局变量(global)、会话变量(session) 查看系统变量show [session|global] variables [like xx]或者 select @@[session|global].系统变量名

　　　　　　　　　设置系统变量：set [session|global]或者@@[session|global] 系统变量名=值　　只不过服务重启后会失效，要想不失效，需更新/etc/mysql.conf.d/mysqld.cnf

　　　　　　　　　自定义变量@：赋值set @变量名 = 值使用select @变量名

　　　　　　　　　局部变量：只在begin和end之间有效，声明declare 变量名变量类型[default默认值...] 赋值set 变量名=值　　　　

　　　　　　存储参数：in输入参数（默认），out输出，inout输入/输出

　　　　　　　　　　例子：create procedure 存储过程名（in 参数1 int，out 参数2 varchar(1)）begin ... end 在中间的sql语句中可以调用参数1，在end之前返回参数2的最终结果 call xx(参数1 值，参数2 @自定义变量) select @自定义变量

　　　　　　if判断：if 判断 then 语句；elseif 判断 then 语句；else 语句；end if；

　　　　　　case：类似于switch case when 条件1 then [when 条件2 then...] [else...]

　　　　　　while：语法while 条件 do sql逻辑 end while；

　　　　　　repeat：满足条件退出循环，与while相反 repeat sql逻辑 until 条件 end repeat；

　　　　　　loop：死循环，配合leave（类似于break）和iterate（类似于continue）使用 [循环名称]loop sql逻辑 end loop [循环名称]；

　　　　　　cursor：游标，用来存储查询结果集的数据类型，语法：声明declare 游标名称 cursor for 查询语句；打开open 游标名称；获取游标记录fetch 游标名称 into 变量[...]；关闭游标close 游标；

　　　　　　条件处理程序handler：语法declare continue/exit handler for condition_value[,condition_value] ...statement 其中condition有SQLSTATE的sqlstate_value如20000

　　存储函数：类似于存储过程，语法不同，参数只有in，但有return，没有存储过程使用广泛

　　　　　　语法：create function 名称[参数列表] returns type [characteristic] begin sql语句 return ...； end；其中characteristic有determinstic、no sql等

　　触发器：

　　　　　　类型：insert/update/delete型触发器，目前只支持行级触发器for each row，不支持语句级

　　　　　　语法：create trigger 触发器名称 before/after insert/update/delete on 表名 for each row begin 触发器内容； end；查看show triggers 删除drop trigger [指定表名] 触发器名称

锁：

　　分类：全局锁、表级锁、行级锁

　　全局锁：语法：添加锁flush tables with read lock；备份mysqldump ；解锁unlock tables；

　　　　　　缺点：业务停摆、从库不能执行主库同步的二进制日志导致主从延迟（一种解决方法是在备份时mysqldump加上--single-transaction）

　　表级锁： 类型：表共享读锁、表共享写锁；语法：lock tables 表名 read/write； unlock tables；　

　　　　　　特点：读锁：所有客户端只能读不能写；写锁：本机可写可读，其他客户端不能写不能读

　　　　　　元数据锁（MDL）：系统自动，如在事务期间或者表正在操作时允许元数据锁不冲突的语法进行、冲突的阻塞，避免DML和DDL冲突，保证读写正确

　　　　　　意向锁：为了解决一张表中行锁与表锁的冲突，加入意向锁来减少表锁的检查；

　　　　　　　　　　类型：意向共享锁（IS）（与表共享读兼容，与写互斥）（insert等会加这个）、意向排他锁（IX）（与表共享读/写都互斥）（update等会加这个）

　　行级锁：innoDB数据基于索引组织的，行锁通过索引上的索引项加锁实现，而不是对记录加锁，data_locks参数可以查看行锁和意向锁

　　　　　　记住：innoDB默认是B+树，索引对应的叶子结点是有序的

　　　　　　分类：行锁（锁定单行记录，防止update和delete）、间歇锁（锁定索引之间的间隙，就是链表之间，防止insert产生幻读）、临键锁（前两个组合）

　　　　　　行锁：共享锁（S）、排他锁（X），只有共享锁之间是兼容的，其他组合都互斥

　　　　　　　　　　增删改自动加排他锁，select可以默认不加锁，可以手动加共享（lock in share mode）或排他锁（for update）

　　　　　　间歇锁/临键锁：唯一索引的等值查询会优化为间歇锁；普通索引的等值查询临键锁会退化为间歇锁；唯一索引的范围查询会成临键锁

　　　　　　注：事务开启的时候，读数据使用的是临时锁，根据索引变不同类型的锁，因为如果不是根据索引的话，会变成表锁；

　　悲观锁/乐观锁：两种“锁”是一种思想，悲观锁的实现是update以及select ... for update时对数据上排他锁（也就是依赖锁机制），而乐观锁不依赖锁机制，因为它认为数据不会发生冲突，只有当提交时才会检测，这种检测需要自己实现，如通过时间戳或者版本号记录数据版本

innoDB引擎：

　　逻辑存储引擎：表空间里有段，段里有区，区里有页，页按行存放

　　　　　　表空间文件对应.idb文件，用来存储记录、索引等数据，

　　　　　　段：数据段也就是B+树叶子结点、索引段 B+树非叶子节点、回滚段，段用来管理多个区

　　　　　　区：表空间的对应的单元结构，默认1M，里面默认引擎页大小16K

　　　　　　页：最小单元，记录和索引存储在页中行：按顺序存放

　　架构：缓冲池与磁盘的交互和存储

　　　　　　内存结构：

　　　　　　　　缓冲池Buffer Pull：增删改查最先取到数据的地方，独立MySQL服务器建议设置总内存的80%，如果没有就去磁盘加载，减少磁盘io，底层采用链表结构，用页为单位分为3中状态：free/clean/dirty page

　　　　　　　　更改缓冲区：执行DML语句时，如果缓冲池没有，先不去磁盘寻找，而是将修改的数据保存到更改缓冲区，当读取时，将更改缓冲区的数据与需要读的数据合并后刷新到磁盘

　　　　　　　　自适应哈希：参数开关：adaptive_hash_index，

　　　　　　　　日志缓冲区：缓冲日志的效果，有redo log，undo log，可更改缓冲区大小（默认16M）和刷新到磁盘时间（默认1：每次提交事务时，0：每秒，2:1和2的结合）

　　　　　　磁盘结构：

　　　　　　　　系统表空间System Tablespace：参数：innodb_data_file_path，更改缓冲区的存储区域，可能包含索引等

　　　　　　　　独立表空间File-Per-Table Tablespace：参数：innodb_file_per_table，结尾为.ibd的表空间文件

　　　　　　　　通用表空间：语法创建：create tablespace xxx add datafile '表空间文件名' engine = 存储引擎创建表时：create table xxx ... tablespace 表空间文件名

　　　　　　　　撤销表空间：存储undo log日志另外还有临时表空间

　　　　　　　　双写缓冲区Doublewrite Buffer Files：用于将缓冲区存储到磁盘时出现异常的情况下数据恢复，保证安全

　　　　　　　　重做日志Redo log：也是保证日志存储到磁盘时出现异常时数据恢复用，保证事务持久性

　　　　　　后台线程：将缓冲区数据刷新到磁盘

　　　　　　　　Master Thread：负责调度其他线程，保证异步刷新到磁盘，包括脏页刷新，合并插入缓存，undo页回收

　　　　　　　　IO Thread：负责IO请求回调，可以通过show engine innodb status查看其中的IO

　　　　　　　　Purge Thread：回收提交过的undo log

　　　　　　　　Page Cleaner Thread：减轻Master Thread，协助脏页刷新

　　事务原理（底层）：

　　　　　　ACID特性，ACD通过redo log和undo log保障，I（隔离）通过锁机制和MVCC多版本并发控制保障

　　　　　　持久性通过redo log保障：redo log记录事务提交时对数据页的修改（也就是脏页）（是物理日志，记录内容），如何保障：利用WAL（先写日志），因为直接执行脏页的刷新可能出错，那么就先写入日志，错误时数据恢复来再次刷新，保障数据刷新成功

　　　　　　原子性通过undo log保障：undo log记录数据被修改前的信息（回滚日志）（是逻辑日志，每一步执行什么操作）

　　MVCC：（在快照读时通过MVCC查找历史版本）主要用来解决不可重复读和幻读，提高并发效率

　　　　　　概念：

　　　　　　　　当前读：读取的是记录的最新版本，读取时加锁，如select ... lock、select ... for update等

　　　　　　　　快照读：简单的不加锁select，读取的是历史版本

　　　　　　　　MVCC：维护一个数据的多个版本，使读写不冲突，快照读为MVCC提供了非阻塞读功能，具体还依赖于数据库记录中的三个隐式字段、undo log日志、readview

　　　　　　实现原理：

　　　　　　　　记录（行）中的隐式字段：DB_TRX_ID最近修改事务id，DB_ROLL_PIR回滚主键，配合undo log指向上一个版本，DB_ROW_ID隐藏主键，表中没有主键才有这个

　　　　　　　　undo log日志：insert时，产生的undo log日志只在回滚时需要，在事务提交后立即删除；update、delete时在回滚和快照读都需要，不会立即删除

　　　　　　　　undo log版本链：不同事务或相同事务对同一条记录修改，使undo log生成链表，头部为最新的旧记录，尾部为最早的旧记录（就是链表指针的头尾结点概念）

　　　　　　　　readview（读视图）：是快照读时MVCC提取数据的依据，记录并维护系统当前未提交事务的id；其中包括四个字段m_ids当前活跃事务id，min_trx_id最小活跃事务id，max_trx_id当前最大事务id+1，creator_trx_id是readview创建者的事务id

　　　　　　　　版本链访问（trx_id当前事务id）：1.trx_id == creator_trx_id可以访问（数据是当前这个事务更改的）2.trx_id < min_trx_id可以（数据已提交）3.trx_id > max_trx_id不可以访问（事务是在readview后开启）4.min_trx_id <= trx_id <= max_trx_id如果trx_id不在m_ids中可以访问