学习MYSQL

DDL 建表
DML增删改
DQL查询
DCL控制用户权限

存储引擎

MYSQL体系结构
*连接层
*服务层（DML DDL ）
*引擎层（可插拔）（索引在这里，不通的引擎 索引结构不同）
*存储层，

外键：
外键关系（Foreign Key Relationship）：从表的外键与主表的主键或唯一键相对应。
级联更新（Cascade Update）：当主表中的主键或唯一键更新时，从表中对应的外键也会自动更新。
级联删除（Cascade Delete）：当主表中的主键或唯一键删除时，从表中对应的外键也会自动删除或置为NULL。

存储引擎

基于表的（创建表的时候）
Innodb默认--5.5版本后
myISAM早期默认
Memory

Innodb

*特点
*支持DML ， 事务 ，行锁 ，高并发，外键约束，保证数据完整和正确
*磁盘：每个表都有一个表空间文件（表结构 ，数据，索引）

逻辑存储结构：表--段--区（1M）--页(16K)--行

MYISAM（mongodb替代）

*不支持事务，外键，行锁
*支持表锁，
*访问速度快
*磁盘有三个文件（分别存：表结构 ，数据，索引）

Memory（redis替代）

*内存存放
*hash索引（默认）
磁盘一个文件（表结构）


选择引擎  
事务-外键-完整性等  Innodb
插入-读取多，更新和删除少的 MYISAM
临时Memory

索引

*概述：是一种高速获取数据的-有序的数据结构
（二叉树：先比较 --小的往左， 大的往右）
*优点：
高速获取数据，
提高排序效率  降低cpu消耗
缺点：
占用磁盘
影响增删改
 

索引结构

B+tree索引      支持所有引擎
hash索引      支持memory

Btree:中间元素向上分裂     4ket 5指针

B+tree: 
*所有的元素都会出现在叶子节点
*所有叶子都会形成一个单向链表

mysql优化后的B+tree
*增加一个指向相邻叶子节点的链表指针（双向），形成有序的指针提高访问

Hash（等值配匹-不能范围-不能排序）通常一次查询 很快
*将键值换算成hash值 映射到对应的槽位上，然后存在hash表中
*hash冲突（碰撞）通过链表来解决
 

Innodb自适应hash功能，在指定环境下可以自动构建


为什么Innodb选择使用B+tree
*相对二叉树，层级更少，查询效率更高

*对于Btree,无论是叶子还是非叶子节点都存储数据，这样导致一页中存储的键值和指针减少，要保存同样的数据就加树高度，导致性能降低,有单向链表可以访问查询
xiangb
*相对Hash   不能范围-不能排序

B+树退化的极端情况是所有的数据都插入在最左边(或最右边)的叶子节点上,导致树的高度增加,从而失去了B+树的平衡特性。在这种情况下,B+树的性能可能会下降到类似于链表的查询效率,从而失去了B+树的优势

 

索引分类

主键索引，唯一索引，常规索引，全文索引（查找文中的关键字）
 

innodb索引的存储形式
1:聚集索引（必须有 且一个）（主键）
结构：（叶子节点挂的就是row数据）！！！！！！！！！！！！！！！！！！

2:二级索引 (多个)
结构：（叶子节点挂的就是id）！！！！！！！！！！！！！！！！！！

select * from aaa where  name= 666( 先二级查询找到id，再根据id聚集查询找到数据)
（回表查询）

聚集索引选取规则
*优先主键
*第一个唯一索引
*自动生成一个隐藏的

Innodb主键索引的B+tree高度为多高？？？！！！！！！！！！！！！！

*每个节点(包含key,指针（6字节）)落在磁盘的页（16K）当中
*假设：
一行数据大小为1k ,一页可以存16行，指针占用6byte,  bigint主键（key值）占用8byte
高度为2
第一层 n*8 +(n+1)*6 =16*1024  计算n=1170   n就是ket的数量
第二层 1171*16 = 18730   一万八
如果是三层 1171*1171*16 = 2千1百万

 

SQL分析

*查询SQL执行各种操作的频率 show global status like  'Com____'
*查询慢sql日志 （默认关闭的） 打开并 设置时间
*查看sql执行各个阶段耗时   profile ，（先查看是否支持）
*执行计划explain

explain
*id 越大越优先执行，相同就从上到下执行
*type 连接类型：null ,system ,const(主键查询),ref（一般查询） ,all
* possible_key 可能用到的索引
 *key 实际用到的索引
*key_len索引长度 越短越好
*rows查询行数
*filterd 结果和读取比例  越大100%越好   
*extra 额外信息
 

索引使用

避免回表- 避 避免回表免回表 避免回表 避免回表  避免回表  避免回表  避免回表

失效的情况

*最左前缀法则 ：联合索引 最左的字段一定要存在 不然就失效，跳过的也失效
*范围查询失效：联合索引 >< 范围查询失效    ， 用>= <= 解决
*不要再索引列进行sub等运算 会失效（在hwhere 里面）
*字符串不加引号 索引失效
*模块查询头不配匹就会失效
*使用or 两边都要有索引 才会生效
*MYSQL评估 走全文比索引快  就不会走索引

SQL提示（选择走哪个索引）

背景：同一个联合索引和一般索引(自动选择 不固定)，可能会先走联合索引
*use index    建议使用这个索引
*ignore index  不走
*force index   必须走
例子：     select * from  table use index(id)   where id = 11

覆盖索引

（返回的数据 尽量包含索引 ，少用select* 很容易回表）
*查询返回减少无用字段 多余的字段会导致回表查询（索引信息没有包含全部数据）
ps-尽量在索引就能回去所有数据
 

前缀索引 

*对大字段部分长度做索引 减少索引体积空间
*选择长度越长越好  最好唯一

单列索引（多个，只会有快的那一个）
联合索引（存在多个查询条件 建议使用）  考虑建立索引的顺序
 

设计原则

*数据量大
*查询频繁
*针对 在 where ,order by, group by 的字段
*选择区分度高的 例如id 唯一的
*如果是大字符串的  建议前缀索引
*尽量联合索引 减少空间，减少回表，尽量覆盖索引
*控制索引的数量，会影响增删改
*索引不能为null，建表的时候 约束not null,

SQL优化

insert优化
*批量插入（最大1千）
*手动提交事务
*主键顺序插入（乱序会产生页分裂）
*load指令大大批量插入数据（百万级别）

主键优化
表数据是根据主键顺序组织存放的  称为（索引组织表）
*因为innodb数据是有序的， 乱序插入会 页分裂
*数据删除只是标记，当删除默认页50%，就会页合并
1尽量减少主键长度，影响空间和查询，二级索引也存有主键
2顺序插入 现在自动增长，避免页分裂
3尽量不要用uuid 身份证 做主键 因为他们是无序的且长
4业务操作 避免对主键修改

order by 优化
排序有两种
1，Using filesort 通过索引或者全表扫描，得到数据 放到缓冲区完成排序的
2，Using index 通过索引顺序扫描直接得到有序数据，速度快（优化的目标）（覆盖索引）
*创建索引默认升序
*创建联合索引
优化
1建立合适索引 遵循最左前缀法则
2尽量使用 覆盖索引
3多索引 一个升序 一个降，就注意索引创建对应的升降，
4缓冲区默认256K，大数据可以 调高一点，不然会在磁盘操作 慢

group by 优化
*通过索引操作
*最左前缀法则

Limit优化
越往后分页越慢，
*思路：通过覆盖索引，再加子查询

count()优化
*自己记录
*count(*)最快直接统计行数
count(0)填充数字统计行数 快
*count(字段) 统计非null

update优化
innodb行数锁是针对索引加的，不是子记录加，如果索引失效 会升级表锁
*一定要加索引

 

视图

*虚拟表，只保存sql逻辑  要关联基础表
创建 create view aa1 AS select id from bb  加检查选项  
检查选项：casaded(默认)（向上找），local（递归找）
*视图也可以作为基表，检查选项继承

*可以当作一个表来使用
*视图可以更新的条件是 和基础表数据一对一

作用：
简单，：简化其他操作
安全，：数据库授权 部分字段
数据独立：屏蔽基础班结构变化带来的影响
 

存储过程
 

定义：存储在数据库的sql集合，
作用：简化开发人员工作，减少应用和客户段的交互 
特点： （封装和重用），接收参数和返回参数，减少网络传输

创建 
create procedure name {参数列表}
begin
   -----sql
end
调用   call name {参数列表}

查看：
1可以查看某个表有哪些存储过程
2查看存储过程的 语句
3工具（routine下面）

系统变量（如提交事务）（两个@@）
*全局变量：gloabl  会话变量:session
*查看show  和设置set(默认重启还原)

自定义变量（一个@） 在当前会话生效
可以直接赋值 可以查询赋值  没有赋值就是null

局部变量
*在begin 和 end 之间生效
声明declare

参数 
in 默认
out
inout

存储函数（例如 sum，1到N的累加结果）  

*封装方法（）可以用存储过程代替
 

触发器 tirgger

*是和表有关的对象  在表增删改的时候触发（指定的sql集合）
*只支持行级
old new 操作前后的数据

锁 锁 锁 锁

（锁了以后 只读）

*全局锁
经典使用：全库备份
加锁---备份--解锁

*表级锁
*myIsam innodb 都支持
1表锁（读锁,写锁）  lock tble name   read/write
        读锁：阻塞两个客户端写，可读
        写锁：阻塞其他客户端的读写

2元数据锁（系统自动控制）
在表有 DML DDL操作的时候 ，自动加索避免数据冲突

3意向锁-自动
表锁前 ，先查看有没有通过意向锁检查是否存在行锁在

共享锁 IS（读）， 可以兼容共享 ，其他不行
排他锁 IX（读写），都不兼容

*行级锁(加载索引)

innodb支持

RU 读未提交  ，RC读提交， RR 重复读(l临界锁) ，SE 序列化
分类：行锁， 间隙锁， 临界锁
*行锁 ： RC RR级别支持（防止幻读） 例子锁5或10

幻读(Phantom Read)是指在事务提交之前,其他事务已经修改了该事务读取的数据,导致该事务读取的数据不准确或出现错误

不可重复读(Non-Repeatable Read)是指在一个事务中,一个数据被多次读取到,但是如果在读取该数据之前,有其他事务已经修改了该数据,那么第二次读取到的数据将不是第一次读取时的数据,而是修改后的数据。

有索引的时候就自动优化为行锁，不然对所有记录加锁-会升级为表锁

为什么没有索引，锁一行数据会导致锁整表？
为什么表里面没有索引的时候，锁住一行数据会导致锁整表？或者说，如果锁住的是索引，一张表没有索引怎么办？所以，一张表有没有可能没有索引？
（1）如果我们定义了主键（PRIMARY KEY），那么InnoDB会选择主键作为聚集索引。
（2）如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。
（3）如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐藏的聚集索引，它会随着行记录的写入而逐渐递增。

所以，没有使用索引时为什么锁表，是因为查询没有使用索引，会进行全表扫描，然后把每一个隐藏的聚集索引都锁住了。
 

*间隙锁（gap）：RR,例子5-10之间6789
防止其他数据加入间隙 产生幻读
*临界锁（行锁+间隙锁）： RR，例子锁5678910


增删改：加的是排他锁，自动
查：不加锁，，可以手动加
 

Innodb
结构：
1表空间 
2段，数据段，索引段，回滚段，
3区，1M ，包含64页
4页,（innodb磁盘管理最小单位）16k
5行

内存架构：先在内存（buffer）操作 一段时间再更新到磁盘

事务原理
*原子性 undo log
*一致性    都由日志来保证（redo log + undo log）
*持久性   redo log

*隔离性：由锁+MVCC保证的
 

MVCC 多版本并发控制!!!!!!!!!!!!!!!
概念
当前读：读取最新数据，加锁 不能被其他修改
快照读：简单select 不加锁 非阻塞。（RR第一次查询是）

MVCC实现依赖
*快照读，*3个隐藏式字段，*undo log（回滚日志），readView
#3个字段
1最近修改事务的ID
2回滚指针，（配合undo log 可以找回上一个版本）
3隐藏主键（只有再没有主键的情况才会有）
#undo log（回滚日志
#readView  
*是快照读获取数据的依据

MVcc在某些场景中替代了相对的低效的锁（共享锁，排他锁），在保证了隔离性的基础上，提升了读取效率和并发性；

解决

读读：不存在并发问题

读写：存在并发问题，有事务隔离问题，脏读、不可重复读、幻读。

写写：有并发问题，可能会造成数据更新丢失。

MVCC（Multi-Version Concurrency Control），即多版本并发控制，在MySQL InnoDB中处理并发时，不加锁和非阻塞并发读，提高并发读性能的一种机制。MVCC维持了数据的多个版本，使得并发读写时没有冲突

二、SELECT操作：
在这里就需要使用readview结构来实现了，所谓readview顾名思义是一个视图内存结构，·在事务select查询数据时，就会构造一个readview，里面记录了该数据版本链的一些统计值，这样在后续查询处理是就无需遍历所有版本链了；

当一个事务执行 SELECT 操作时，它会使用自己的读视图来确定应该看到哪个数据版本。具体规则如下：

如果数据版本的事务ID小于 min_trx_id，表示该版本已经提交，可以读取。

如果数据版本的事务ID大于 max_trx_id，表示该版本在事务开始后创建，不可见。

如果数据版本的事务ID在 min_trx_id 和 max_trx_id 之间，但在 m_ids 集合中，表示该版本由尚未提交的事务创建，不可见。

如果数据版本的事务ID在 min_trx_id 和 max_trx_id 之间，并且不在 m_ids 集合中，表示该版本由已提交的事务创建，可见。

三、UPDATE操作：
创建新版本：当一个事务执行UPDATE操作时，它会创建一个新的数据版本，而不是直接修改原始数据。这个新版本会与当前事务的事务ID相关联，并记录在数据库中。

写入新数据：更新操作会写入新的数据版本，但原始数据版本仍然保留。这意味着其他正在执行SELECT操作的事务仍然可以看到原始数据版本，而不会受到UPDATE操作的影响。

提交事务：只有当事务成功提交后，新的数据版本才会对其他事务可见。在提交时，将更新的数据版本标记为已提交，而Read View会在其他事务中考虑这个已提交的版本。
 

四、MVcc可以解决不可重复读和幻读问题吗？
幻读是没有办法通过mvcc单独解决的，对应不可重复读问题，可以在事务第一个查询时，创建一个readView，后续查询都是用这一个readView进行判断，所以每次查询结果都是一样的就解决了不可重复读问题。

五、MVCC结合什么方式解决数据库幻读问题？
范围锁：

为了解决幻读问题，可以使用范围锁（Range Locking）。范围锁允许事务锁定一定范围的数据，以确保其他事务不能在这个范围内插入新数据或修改已有数据。这可以防止幻读问题的发生，因为事务可以锁定整个范围，从而保持了数据的一致性。
例如，在一个订单表中，如果一个事务正在检查某个时间范围内的所有订单，并且希望防止其他事务在这个范围内插入新订单，它可以使用范围锁来锁定这个时间范围。
 

数据库隔离级别：

读未提交（read Uncommited）:在该隔离级别，所有的事务都可以读取到别的事务中未提交的数据，会产生脏读问题，在项目中基本不怎么用，安全性太差；

 读已提交（read commited）:这是大多数数据库默认的隔离级别，但是不是MySQL的默认隔离级别；这个隔离级别满足了简单的隔离要求：一个事务只能看见已经提交事务所做的改变，所以会避免脏读问题； 由于一个事务可以看到别的事务已经提交的数据，于是随之而来产生了不可重复读和虚读等问题（下面详细介绍这种问题，结合问题来理解隔离级别的含义）；

可重复读（Repeatable read）：这是MySQL的默认隔离级别，它确保了一个事务中多个实例在并发读取数据的时候会读取到一样的数据；不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。

可串行化（serializable）：事物的最高级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，一般为了提升程序的吞吐量不会采用这个
 

MYcat

概念：逻辑库----逻辑表-----（分片规则）---分片节点