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,无论是叶子还是非叶子节点都存储数据，这样导致一页中存储的键值和指针减少，要保存同样的数据就加树高度，导致性能降低

*相对Hash   不能范围-不能排序

索引分类

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

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级别支持（防止幻读）
有索引的时候就自动优化为行锁，不然对所有记录加锁-会升级为表锁

*间隙锁：RR
*临界锁（行锁+间隙锁）： RR

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

代码007普通

打赏 收藏 海报 链接