MySQL部分总结

mysql学习笔记，如有不足还请指出，谢谢。

外连接，内连接，全连接

外连接：左外、右外

内连接：自己和自己连接

全连接：左外连接+右外链接

mysql unique字段

unique可以在数据库层面避免插入相同数据，但是使用INSERT ... ON DUPLICATE KEY UPDATE和INSERT IGNORE 依然可以无视插入相同数据报错从而插入数据

int（1），int（10）varchar char

int（1）和int（10） 只是表示显示int范围的宽度，但不改变占用的字节数

插入1 ，int（1）显示1 int（10）显示0000000001

varchar可变括号指定的是最大长度，表示字符个数， char不可变，会填充空字符

能使用int存储的都是用int存储，字符串的存储会占用太多的空间。

B+树

B+树3层便可存储大概2000w的数据，在这么大数据量中，最多进行3次的磁盘IO，呢么B+树层高增长的因素就是数据量的暴增。

所以在此想到主键的问题，用UUID还是自增？显然UUId没有规律，呢么在存储或者读取的时候，可能要进行多次的磁盘IO去寻找合适的页存储数据，效率很低，使用自增有规律的主键就可以很大程度上避免这种问题，定位合适的页存储数据的效率比较高

锁

全局锁：全库的数据备份

表级锁：整个表

表锁：

表共享读，不阻塞其他客户端的读，但阻塞写

独占写，阻塞其他客户端的读写

元数据锁:维护表结构的一致性，存在事务的时候，这时候就会加上共享读，共享写锁（对这个表可以进行select 也可以update insert delete），但不能修改表结构

意向锁：在存在行锁时候，我们想要加表锁，需要遍历整个表检测当前表或者当前行是否有锁。但是使用意向锁之后，只需哟判断它是否于存在的锁是兼容的即可，不需要整表扫描去加锁。

意向共享锁：select ... from .... lock in share mode。和表锁的共享锁兼容，排他锁互斥

意向排他锁:select .... from ... for update。和锁都互斥

行级锁：数据行，锁粒度最小

1、行锁：锁定单行 RC，RR

2、间隙锁：锁住索引记录的间隙，所住记录的一个区间，保证在这个区间内的数据不会被改变，RR。防止幻读

在给不存在的记录唯一索引加锁时候，会成间隙锁

3、临键锁：间隙锁+行锁

Innodb 加行级锁是基于索引的， 所以对于update操作要使用索引（无论主键索引还是普通索引），否则会对整个表加锁

存储引擎

存储引擎是mysql特有名字 是对数据的不同形式存储的方式。

ENGINE=InnoDB AUTO_INCREMENT DEFAULT CHARSET=UTF-8 存储引擎 编码方式 （默认）

查看引擎 show engines \t

支持9个引擎 版本不同支持情况不同

关于mysql常用的引擎

myisam

使用三个文件表示每个表

格式文件-存储表的结构定义

数据文件-存储表内容

索引文件-数据表索引（缩小扫描范围提升效率）

可被转换为压缩，只读表来节省空间。

只要是主键或者加有unique字段的都会自动加入索引

innodb

默认的存储引擎 支持事务，支持数据库崩溃后数据恢复

每个表都为.frm格式表示

innodb表空间tablespace被用于存储表的内容

提供一组用来记录事务性活动的日志文件

用commit savepoint rollback支持事务处理

提供全acid兼容

多版本（mvcc）和行级锁定

支持外键积引用的完整性级联删除和更新

保证数据安全但是安全不能压缩节省空间
 

区别

1、myisam 不支持行级锁

2、myisam 不支持MVCC

3、mysiam 不支持事务

4、mysiam索引为B树，Innodb为B+树

5、myisam不支持redolog文件，数据损坏后不能恢复

MVCC

多版本并发控制，是一种用于数据库管理系统的并发控制机制，它可以在保证数据一致性的前提下，提高数据库的并发性能。

在传统的并发控制中，为了保证数据的一致性，通常会使用锁机制来限制并发访问，这可能会导致大量的锁竞争，降低系统的并发性能。而 MVCC 通过为数据的每个版本创建一个快照，使得不同的事务可以同时访问数据的不同版本，避免了大部分的锁竞争，从而提高了并发性能。

隐藏字段+undolog+版本链+readVIew

回滚到哪一个版本需要油readVIew控制。

readView是快照都SQL执行时候MVCC提取数据的依据记录当前活跃的事务ID

依次拿到旧版本的数据，比较四条规则，只要满足1 2 4中任意一条就可以回滚，若满足3 则不能回滚。总的来说，要回滚到的版本，就是距离已经结束的事务中最近的版本，要么是当前的事务，要么是不在当前活跃的事务中结束最近的。

RR级别下，在第一次读时候长生readView，直至commit后重新读再生成readView才会更新。

MVCC的局限性：

• 存储空间开销：为了存储数据的多个版本，需要额外的存储空间，特别是在数据更新频繁的情况下，存储空间的开销会比较大。
• 垃圾回收问题：随着时间的推移，数据库中会积累大量的旧数据版本，需要定期进行垃圾回收，以释放存储空间。

memory

在数据库目录内每个表以.frm格式文件表示

表数据及索引被存储在内存中

表级锁机制

不能包含text或blob字段

在内存储存查询快但是断电数据就消失不需要和硬盘交互

memory存储引擎以前被称为heap引擎

事务

事务就是一个完整的业务逻辑

事务要么同时成功要么同时失败，才能保证数据的正确。

只有DML语句与事务有关(insert delete update)

事务本质就是多条dml语句同时成功同时失败。

提交事务标志着事务结束也是一种全部成功的结束

回滚事务表示事务全部失败的结束

手动控制事务 start transaction

脏读、幻读、不可重复读

脏读：读到其他事务未提交的数据

幻读: 在事务开始时读到的数据和一段时间后读取到的数据条数不同

不可重复读：事务开始时和事务中任何时候读取到的数据不同

事物的特性：

原子性：事务是最小工作单元不可拆分，要么同时成功，要么同时失败

一致性 ：同一个事物当中所有操作同时成功或失败

持久性：事务完成后的保证，事务提交相当于将没有保存到硬盘上的数据保存到硬盘上。

隔离性：事务与事务之间具有隔离性

隔离级别（4个）：读未提交(最低级别)，读已提交，序列化读（最高级别），可重复读。

读未提交：事务A可以读到事务B未提交的数据会出现脏读现象

读已提交：事务A只能读到事务B提交的数据 解决了脏读现象。但是不可重复读取数据。

可重复读取：事务A开启之后不管是多久，每一次事务读取到的数据都是一致的，即使事务N将数据已经修改，并且提交了事务A读取到的数据还是没有发生改变会出现幻读现象。

读取到的都是事务开启时的数据,只要事务不结束读取到的数不会改变。

mysql默认的隔离级别是可重复读。

可以使用间隙锁，锁定范围内的数据，尽可能避免幻读情况

可序列化：隔离级别最低 解决了所有问题 表示事务排队，不能并发。类似于synchronized.

它确保并发执行的事务产生的结果与按顺序串行执行这些事务的结果相同。

commit:执行后数据保存在数据库中 不能被回滚

rollback：回滚数据 回滚到最近一次的commit

redolog undolog binlog

redolog：重做日志，事务对数据的修改记录。这个文件分为buffer和file，事务提交后会把buffer中的数据刷新到file中。buffer在内存中，file在磁盘中

undolog：记录事务之前的数据，回滚失败的事务

binlog：记录对数据的所有update insert delete语句，方便数据库的数据恢复

statement中只保存对一行数据进行修改的最终的数据

row会记录，更新前、后的数据变更，而不是记录对数据操作的命令。表结构语句也存储。

约束

约束分类：

1. 单列约束：一列约束，多列约束：多个列加约束
2. 列级约束，声明约束在字段后边

表级约束，表中所有字段声明完之后，在所有字段后加上约束

表级约束可以不命名默认为列名

使用表级约束与列级约束作用不同

create table test_db(id  int unique,name varchar(15) unique,password varchar(15) unique 
);
create table test_db2(id  int unique,name varchar(15) ,password varchar(15) ,constraint unique (name,password)
);

表约束可insert两个name不同password相同的数据(只要其中的一个字段不同即分为不同的两条数据)，而列级只能插入name与password 都不同的数据

1. 约束作用： not null 非空约束

unique 唯一性约束 primary key 主键约束 foreign key 外键约束 check 检查约束

default 默认值约束、

1. 在create table 时添加约束 或者使用alter table 增加约束或者删除约束

not null 只能单独使用，不能组合使用 。

声明为unique约束的字段仍然可以使用null，且多条数据都可以使用null。删除唯一性约束只能通过删除唯一性索引删除。使用唯一性约束也会创建唯一性索引

删除唯一性约束
alter table dbname drop index columnname;

not null unique可以联合使用会成为主键

primary key 主键约束：任何一张表都应该有主键，没有主键则无效 建议使用单一主键做到特别区分 复合主键比较复杂不建议使用。且主键不能重复定义 只能添加一个主键约束。

·

create table test_db(id  int unique,name varchar(15) unique,password varchar(15) unique 
);
create table test_db2(id  int unique,name varchar(15) ,password varchar(15) ,constraint primary key (name,password)
);
与unique的表级约束相同效果 可以多列复合主键约束但本质上仍然为一个主键

自然主键 ：使用数字代替

业务主键：使用业务关联

使用自然主键较多 当业务主键改变影响较大、

使用自增进行主键值设置
int id primary key auto_increment

foreign key外键约束：作为引用其它表的信息 在引用另一张表时另一张表不一定是主键但最起码具有唯一性约束。

constraint fk foreign key (column) references tablename(column) 添加外键约束

使用外键则与主表关联的列必须为主键。只有主表的数据存在从表才能加入相关的外键数据。

check检查约束（mysql暂且不支持）

索引

数据库索引是使用B+Tree数据结构，（不同存储引擎的索引结构不同 myiasm索引为btree，innodb为b+tree）对于每一个索引，按照B+树的存储结构存储每一个索引键，相当于在B+树中，每个索引键是一个叶子节点，在最后一层才存储索引对应的所有数据。并且数据按照链表的结构相连。

B树和B+树的区别在于：存储的叶子节点个数，B+树每个叶节点存储的更多，呢么树就会更加的扁平，呢么高度就相对越低，查询性能更好。

1. 在主键索引形成的B+Tree里面，非叶子结点存储的是主键索引，叶子结点存储的是数据
2. 在非主键索引形成的B+Tree里面，非叶子结点存储的是当前索引，叶子结点存储的是主键索引

索引也会存储成一张表，呢么数据量大的时候，索引表也会占用内存，对于插入删除的操作，非常耗费时间。

create table customer(id int(10) auto_increment,custmoer_no varchar(20),custmoer_name varchar(20),primary key(id),//主键索引unique idx_custmoer_no(custmoer_no),key idx_custmoer_name(custmoer_name),key idx_name_no(custmoer_name,custmoer_no)
)；drop table if exists customer;create table customer(id int(10),custmoer_no varchar(20),custmoer_name varchar(20)
)
alter table customer add primary key(id);alter table customer add unique idx_custmoer_no(custmoer_no)alter table customer add index idx_custmoer_name(custmoer_name)
-- 删除索引
drop index idx_custmoer_name on customer 
-- 多值索引
alter table customer add index idx_custmoer_name_no(custmoer_name,custmoer_no)
-- 删除索引
drop index idx_custmoer_name_no on customer

使用explain 可以看到查询sql语句时候的执行过程

explain SELECT * from person WHERE pname="test8858773"

explain中 type字段类型解释： eq_ref 意思为进行了连表查询。

在mysql中一个字段如果有unique约束的话，也会自动地创建索引对象。在数据库中，任何一张表的任何一条记录在硬盘存储上都有一个物理存储编号。

使用索引条件;数据量大  通常出现在where后 ，字段出现较少的dml操作

创建索引：

create index emp_ename_index on emp（ename）；

创建了一个名为emp_ename_index 的索引。drop index emp_ename_index on emp

回表：

• 如果执行查询 SELECT id FROM users WHERE id = 1;，由于查询只需要 id 字段的值，而 id 字段上有索引，索引中就包含了 id 的值，所以数据库可以直接从索引中获取所需数据，不需要回表。
• 需要回表的查询：若执行查询 SELECT id, name, age, email FROM users WHERE id = 1;，虽然通过 id 索引能快速定位到满足条件的行的位置，但索引中通常只包含 id 字段的值，而 name、age 和 email 字段的数据需要根据索引记录的行位置，回到数据表中去读取，这就产生了回表操作。可以使用覆盖索引进行优化，覆盖索引就是将一些我们需要常查的字段添加索引，呢么在索引查找的过程中，找到索引也就找到了这些字段，可以减少回表次数。在需要多个字段添加覆盖索引时候，我们可以进行分表，将常用的字段存在一个表，另一些字段在存另外的表。这样的话对索引文件友好，并且若小表存储，一次可以导入更多数据到内存，减少IO次数。

在使用复合索引的时候

alter table person add index idx_name_age_score(name,age,score)
create index idx_name_age_score on person(name,age,score)

复合索引：两个字段或者更多的字段联合起来添加一个索引叫做复合索引。

在使用索引查询时候，不能跳过name索引字段不用，而直接使用age，score字段索引，这时候索引就会失效。就是最左索引不能跳过。

前缀索引：

create index idx_prefix on article(content(5));表示截取content字段中前5个字符建立索引

查看sql语句是否使用了索引进行检索

explain select * from emp where ename=’king‘;

索引失效 ：

1、避免模糊查询 select * from emp where ename like ’%T‘；若是like ‘T%’此时索引仍然生效

2、当使用or时两边的字段都有索引的情况下才会用索引，否则失效：

3、索引列使用了运算失效。

在where 中索引列使用了函数

select * from emp where lower（enmae）=“smith”；

4、右边不能再有使用索引

5、索引不能用 !=

6、使用is null 可以用索引，not null 是全表扫描

7、索引不能用于范围查找时，在范围查找条件的后边还存在索引进行 order by或者group by 或者进行=#{数据}的情况。

索引的用途在于判断 （=#{数据}）时 进行判断的，因为考虑索引的数据结构，本质上还是搜索。且限制最左的情况是由于，索引在存储时候按照从左到右的顺序存储复合索引时候，若跳过最左索引这时候就可能无法确定查找索引的顺序，导致失效。

在排序时候也要遵守最左前缀，所以在order by、where中只要用到最左前缀索引，且没有进行范围查找，呢么就会在这查询中用到索引。

索引优化手段：

单一索引：一个字段加索引

复合索引：两个或更多字段加索引

主键索引：主键加索引

唯一性索引：使用unique字段加索引

唯一性越强的字段加索引效率越高。

1、在进行范围查找的字段不加索引，否则进行后续排序的索引字段时会失效。

2、进行外连接时，由于主表要进行全表扫描，加不加索引都没有用，所以要给附表加上索引，提高附表的查找效率。

3、进行分组排序时候，相当于情况1，

4、limit优化，给id加上索引，并且使用子查询

select a.* from user a,(select id from user order by id limit 9000000,10) b 
where a.id=b.id

开启慢查询日志，这时候能够在日志文件看到异常的sql语句，进行后续的改进，调优时候进行开启

set global slow_query_log=1
set global long_query_time=3
show VARIABLES like '%long_query_time%'

锁都是基于索引去找到数据记录再加锁的，而索引的规则是：通过其它索引找到主键索引，所以：

1、没有使用索引做更新相关操作会锁表。

2、通过唯一/主键索引等值加锁，只会锁具体的行，非唯一索引则不一定，SQL优化器会基于数据分布选择记录锁，或临键锁。

3、只有在RR级别下才有间隙锁，目的是为了解决幻读，如果操作的数据是跨多个范围，就会加多个区间的间隙锁。

4、MySQL默认的锁就是【临键锁】，所以在执行SQL的时候，记录锁和间隙锁是会同时存在的。范围是左开右闭的区间。

5、在SQL查询的时候，我们知道是先通过索引去找数据的，其实加锁也是基于索引的，通过索引找到对应的数据然后把相关的数据行都锁上，如果没有使用索引就会扫描全表，也就会锁表。

聚集索引和二级索引

聚集索引：索引和数据存储在一起，索引叶子节点，存储行数据

二级索引：不存在一起，先查索引找到主键索引（主键就是聚集索引），然后去查行数据

慢SQL分析

1、使用explain关键字分析SQL的执行过程中索引是否生效，例如用到了排序，就要看是否在排序字段上索引生效，使用where过滤条件时，是否遵守了最左索引规则，

2、减少回表操作，二级索引存储的时主键索引，故在二级索引中没有查询到所有数据后，还是会通过主键索引查询数据

3、减少select * 的使用，指定查询字段

4、查看慢sql日志，定位到具体的sql语句

深分页优化

深分页通常是在记录量较大时候问题，例

select * from t1 where id>1000,0000 limit 1000,0000,10

这时候sql会先取出1000,0001条数据，然后在去舍弃1000，0000条数据，返回地1000，0001条数据。

解决办法就是使用子查询或者inner join

# 通过子查询来获取 id 的起始值，把 limit 1000000 的条件转移到子查询
SELECT * FROM t_order WHERE id >= (SELECT id FROM t_order where id > 1000000 limit 1) LIMIT 10;

inner join

-- 使用 INNER JOIN 进行延迟关联
SELECT t1.*
FROM t_order t1
INNER JOIN (SELECT id FROM t_order where id > 1000000 LIMIT 10) t2 ON t1.id = t2.id;

尽可能利用主键快速定位到分割点，返回需要的数据条数。

冷热数据分离

冷数据：不会被经常访问的数据，例如普通用户发布的日常视频

热数据：需要被经常访问的数据,例如知名博主的视频

1、可以直接在业务层根据一定规则分库，例如判断这个发表用户的粉丝量

2、使用定时任务，扫描数据表，判断是否被经常访问，进行冷热数据存储

读写分离 主从复制

读写分离即使用mysql集群，主节点进行数据的写入操作，从节点进行数据的读操作。呢么此时问题就是，从节点如何保持和主节点的数据一致性

利用主节点的binlog文件，从节点使用IO线程请求主节点的binlog文件，主节点使用binlog dump操作进行发送binlog给从库，从库收到文件后进行数据更新到relaylog。（例如，cannel，可以模仿从库，订阅主库的binlog文件实现es或者reids的数据同步）

虽然可行，但是会存在主从延迟的情况，也就是从库读取到binlog文件后，更新从库数据也需要时间，这段时间内，主从库的数据仍然不能保证一致性。

首先延迟原因：1、binlog数据量过大，主库和从库的IO线程发送和接受速度不一致，主库写速度快，从库慢，导致从库更新不及时

解决方案：1、对于一致性，实时性较强的请求，强制使用主库读 2、要求不高便延迟读取。

视图

视图是虚拟存在的

创建视图：（此处只能为查询语句）

create view emp_view as select * from emp;

删除视图：

drop view emp_view；

注意用dql语句才能以view形式创建。

视图作用：

对视图进行操作会导致原表被操作。（通过对视图的操作会影响到原表的数据，视图操作与表操作crud相同）

视图用来简化sql语句，将复杂的sql语句查找出的表当作一个视图，创建出视图后可以反复使用，便于开发与维护。（存储在硬盘上）