MYSQL进阶复习

• 以前本地的mkdown转移的csdn
  mysql进阶：
  事务：原子性 一致性 隔离性(连个事务没有关系) 持久性
  脏读：一个事务读取到另外一个还没有提交的数据
  不可重复读：一个事务先后读取数据，两次读取的数据不同
  幻读：一个事务按照条件查询数据时 没有对应的数据，插入时主键出现冲突

事务的隔离级别：在这里插入图片描述

存储引擎：存储引擎是基于表的，不是基于数据库的 show engines; engine = innodb;

innoDB:

![外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传](https://img-home.csdnimg.cn/images/20230724024159.png?
MyISAM:

memory：

存储引擎选择

索引

B+tree

所有节点会出现在叶子节点 分支节点只起到索引的作用

聚集索引和二级索引

回表查询 先在二级索引中找到聚集索引 然后聚集所以再去找

索引的操作

• 查看 show index from tb_user;

• 创建 create index idx_user_name on tb_user(name);

• create unique index idx_user_name on tb_user(name)

![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/0551e1d44de6417081a78dfaf44938fd.png#pic_center

sql性能分析

• 查看频次 show global status like ‘Com_______’ 7个下划线

慢查询日志

show variables like ‘slow_query_log’

• select @@have_profiling;
• show profiles; 查看sql耗时情况

explain

type： system 查询了系统表 const 使用了唯一索引 ref 使用了索引但不是非唯一索引 index使用了索引但是对索引进行了扫描 all 全表扫描

索引使用原则

• 最左前缀法则 索引的生效条件 和位置没有关系 就是字段必须存在
• 再联合查询中 如果出现范围><,索引就是会失效 （>=,<=就是可以索引成功）
• 尽量不要在索引列上做运算操作，索引将失效

• 字符串查询不加单引号 索引失效

• 模糊查询

• 前后都加也是失效的
• or连接的条件

• mysql的智能

• SQL提示 告诉数据库使用哪个索引 不用哪个索引
  

• 覆盖索引

• 

• extra

  • using index condition 查找使用了索引，但是需要回表查询数据
  • null 也是要回表查询
  • using where,using index 使用了索引，但是需要的数据都在索引列中能找到，不需要回表

• 前缀索引

  • 

• 索引的设计原则

  • 1 命名规范

    普通索引：idx_字段名

    唯一索引：ux_字段名

插入数据的优化

• 批量插入

  • 使用load指令插入
  • 

• 手动事务提交

• 主键顺序插入

主键优化

表空间 段 区 页 行

• 
• 

orderby优化

• 

• backword index 反向扫描索引

• 我们创建的索引是升序的结构 也需要遵循最左前缀法则

• 

• 只有实现覆盖索引 创建的索引才有意义要不然还是要回表查询（注意）

• 

group by 优化

• using index 没问题
• using temporary 使用临时表 有问题 需要优化

limit 优化

• 通过覆盖索引加子查询来进行优化
• 

count优化

• count 不计数null

update优化

• 在update的时候条件需要是索引
  

视图

• with check option
  

• 在view的基础上创建view 然后再创建view 然后再操作 就是级联去检查

• 
  

• 视图的更新条件 视图中的数据要和实体数据表中的数据是一对的关系

存储过程

create procedure p1()
begin

end;
call p1();
-- 查看
select *from information_schema.ROUTINES where ROUTINE_SCHEMA ='itcast'
show create procedure p1;
drop procedure if exists p1;

在命令行中可能有问题 那是结束的原因 使用 delimiter $$ 来改变结束符

mysql存储过程语法
- 变量（系统变量，会话变量）默认是session级别的
- 查看系统变量 show [session|global] variables; 查看所有的系统变量
- show global variables like 'auto%'; 模糊匹配
- 准确查看某个系统变量的值 select @@global.autocommit  
- 设置系统变量
- set session autocommit= 0；
- set session
用户定义变量
- set @var_name = 'sunchenchao'
- 查看 select @var_name,@var_name2;
- 没有赋值直接拿 拿到的就是null
局部变量
- 就是在begin 和 end 范围之内
- 定义局部变量 declare stu_count int defautl 0;
- 赋值 select count(*) into stu_count from student;
- select stu_count;
if的语法
if  score>=85 then
	set resutl = '优秀';
else if scoure >60
	set resutl ='合格’;
else
	set resutl = 'buhege';
end if;
-
存储过程的参数
- in类型 作为输入
- out 作为输入出
- inout 即可以作为输出也可以作为输入
create procedure p2(in socre int, out resutl varchar(20))
begin
	set resutl =20;
end;
call p2(68,@result);//@resutl 用户定义的变量
select @resutl;
-
Case
case case_value
	when when_v then 执行
	when when_b then 执行
end case;	
-
where
where 条件 do
	逻辑
end where;
-
repeat 

• loop
• 

游标

• 游标的写发
• 退出条件
• 存储函数

触发器

• old 和 new 是自带的参数 old是原来的参数 new 是最新的参数 concat 字符串的拼接

锁

全局锁

• 使用情况就是对数据库做备份就是使用这个锁

• 加全局锁 flush table with read lock;

• 文件备份 mysqldump -uroot -p1232 db01 > D:/dv01.sql

• mysqldump --single-transaction -uroot -p1232 db01 > D:/dv01.sql 不加锁的数据一致性备份

表级锁

• 表锁

  • 共享读锁
    • 加了读锁只能读 只有释放了可以更新 其他客户端也只能读
  • 表独占锁
    • 自己家了写锁 可读可写 其他客户端不可读不可写
  • lock tables 表名 read/write
  • unlock tables

• 元数据锁

  • 是系统自动控制的 就是对表结构的控制 如果有事务的时候是不允许修改的

  • 

  • 修改表结构是exclusive的锁 与其他所有的锁都是互斥的

• 意向锁（主要是处理行锁和表锁之间的关系 先加上了行锁后要加表锁 就不用一行一行的去查看表中是否有行锁 直接就是会阻塞）

• lock in share mode

  • 

  • 

  • 

• 行级锁

  • 
  • 行锁

    • 共享锁：允许事务去读一行，组织其他事务获得相同数据集的排他锁

    • 排他锁

    • 

    • 

    • lock_model : s共享锁 res_not_gap 没有间隙锁 x是排他锁

    • 

    • 间隙锁

    • select * from performace_schema.data_locks; 就是查看锁的情况 Gap就是间隙锁 lockdata为8 就是到上一条记录之间的中间

    • 

    • 间隙锁和行锁称为临建锁 左开右闭

间隙锁：锁住两个数据之间的数据，

临键锁：行锁+间隙锁

间隙锁和临间锁在RR隔离级别下解决幻读

唯一索引的等值查询，给不存在的一行记录加锁时，会优化为间隙锁

普通索引的等值查询，向右遍历时，第一个不满足查询需求时，临键锁退化为间隙锁 2，5, 8，11，当给8加锁时，由于普通索引能有多个，所以8的前后都有可能出现8，这是会给8加行锁，5-8,8-11加间隙锁；

唯一索引的范围查询，>=5,会给5加行锁，（5,8]临键锁，（8,11]临键锁，（11，∞）

innoDB

架构

内存结构

• changeBuffer

• 

• 

• 

磁盘结构

• 

• 

• 

后台线程

• 
• aio 异步io show engine inondb status;

事务的原理

• 

• 

  redo.log

  • 重做日志
  • 

undo.log

• 回滚日志
• 

MVCC

• 当前读 就是读取到最新的数据 会无视事务
• 快照读 简单的select 不加锁就是快照读
• 

隐藏字段

• 

• DB_REX_ID 最近修改事务ID

undolog

• 

• undolog 版本链

• 

  readview

  
  

数据库系统管理

• 系统表的各种作用 data_lock 数据库表的锁
• 

mysql 常用工具

• 

• 

• 

• 

运维篇

错误日志

• show variables like ‘%log_error%’

二进制日志

• show variables like ‘%log_bin%’

• 

• -statement

• row 基于行的

• binlog_format = statement 在php.ini文件中去修改这样可以改变二进制文件的方式

• 

• mysqlbinlog -v logfilename

• mysqlbinlog --set-charset=utf-8 /var/lib/mysql/mysql-bin.000001>backuptmp.sql
  

• mysqlbinlog -v /var/lib/mysql/mysql-bin.000001 --start-position=219 --stop-position=982 | mysql -uroot -p123456
  

• 清理二进制文件

  • 

  • 也可以配置ini文件的binlog过期时间

查询日志

• show variables like ‘%general%’

• ini文件
• set golbal general_log = on

慢查询日志

• slow_query 慢查询日志
• 

主从复制

• 

• 主库的配置

• 

• systemctl stop firewalld

• systemctl disable firewalld

• 

• systemctl restart

• 

• 从库的配置

• 

• 

• 

• Replica_IO_RUNNING

• REPLICA_SQL_RUNNING 只要这两个是ok的就是主从复制没有什么问题

• 需要把初始数据操作一份 然后在开启主从复制 这样才可以达到一致

分库分表

• 

• 

• 

mycat

• mycat 就懶得搞了 图片一个一个从本地的mkdown弄下来真的麻烦

• http://dl.mycat.org.cn/ 下载

• 

• 

• 

• 实际问题的解决

• 

• 表结构一样 里面内容不一样 就是水平分表

• 

• 在mycat下 conf目录下

• 

• 

• 启动服务

• 

• 

• 

• 

• rule.xml 分片规则