MySQL
2022-05-09 19:50:56 0 举报AI智能生成
MySQL
作者其他创作
大纲/内容
事务与锁
事务
原子性
操作都成功或者都不成功
一致性
操作都成功或者不成功数据要保持一直
隔离性
每个会话都有自己独立的环境不受其他会话的影响
持久性
操作完的数据储存在磁盘上
事务并发问题
脏写
t1会话的数据被t2修改了根本没写在磁盘上
脏读
读到未提交的数据
不可重复读
读到已提交的数据
幻读
自己读的数据被被人修改了，在innoDB可重复读的级别被mvcc机制解决了，但是解决不了自己读-写的幻读
隔离级别
读已提交
读未提交
可重复读
串行化
通过读也加锁的方式解决所有问题
mvcc
undo日志
read-view试图
锁
表锁
行锁
间隙锁gap-lock
临键锁(Next-key Locks)
行锁+间隙锁
innoDB执行的过程
分库分表
索引
索引结构
b+tree
非叶子节点放上去冗余主键（或索引）
叶子节点放上主键和data数据或者是索引和主键
叶子节点放上主键和data数据或者是索引和主键
为什么不用b-tree
目的是存放更多的主键或索引快速查询，因为MySQL是关系型，磁盘储存的数据库
要满足快速查询，join,范围查询等等
要满足快速查询，join,范围查询等等
如果是mongodb非关系型，内存行数据库当然选择b-tree，b+tree的冗余索引对它
来说放到内存中是一种负担
来说放到内存中是一种负担
索引优化
原则
最左前缀原则
小表（指结果集小的表）驱动大表
尽量查询都覆盖索引
建立主键唯一约束
因为你不建立主键MySQL会找列中找一个不重复的做主键
如果没有他会隐藏列做一个主键
如果没有他会隐藏列做一个主键
如果不需要太多字段的查询可以尽量在索引覆盖（using index）上查询
join查询
最好都建立索引
底层算法导致的
nlj（nested-loop join）算法
t1表有索引的时候EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
将t2的表拿出数据一行一行比较t1表，总共磁盘iot2全部，t1扫描t2的全部所以
一共是2倍的t2次
一共是2倍的t2次
bnl(block nested-loop join)算法
t1无索引的时候
将t2拿到join buffer(那么1024字节)内存中比较，内存次数就是t2*t1次，然后join buffer不够就会分段去拿再次比较t1
所以会多次磁盘io拿t1
所以会多次磁盘io拿t1
为什么不走npj算法
因为走了就是t1*t2次磁盘io相比较内存比较的方式效率大大降低
join数量不要超过3个
join优化也是优化子查询语句一旦出现性能问题不好处理
java可以分担查询的任务
因为java集群，多线程，中间件等等很容易扩展，MySQL一旦
出现性能问题难以扩展
出现性能问题难以扩展
sql底层执行原理
binlog
配置binlog
配置开启binlog<br>log-bin=/usr/local/mysql/data/binlog/mysql-bin<br>注意5.7以及更高版本需要配置本项：server-id=123454（自定义,保证唯一性）;<br>#binlog格式，有3种statement,row,mixed<br>binlog-format=ROW<br>#表示每1次执行写入就与硬盘同步，会影响性能，为0时表示，事务提交时mysql不做刷盘操作，由系统决定<br>sync-binlog=1
恢复命令
1 mysql> show variables like '%log_bin%'; 查看bin-log是否开启22
2 mysql> flush logs; 会多一个最新的bin-log日志
3 mysql> show master status; 查看最后一个bin-log日志的相关信息
4 mysql> reset master; 清空所有的bin-log日志
2 mysql> flush logs; 会多一个最新的bin-log日志
3 mysql> show master status; 查看最后一个bin-log日志的相关信息
4 mysql> reset master; 清空所有的bin-log日志
数据归档操作
从bin-log恢复数据
1恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)
2恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731" /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)
3恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00" --start-date= "2019-03-02 11:55:00"|mysql -uroot -p test(数据库)
1恢复全部数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库名)
2恢复指定位置数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults --start-position="408" --stop-position="731" /usr/local/mysql/data/binlog/mysql-bin.000001 |mysql -uroot -p tuling(数据库)
3恢复指定时间段数据
/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/binlog/mysql-bin.000001 --stop-date= "2018-03-02 12:00:00" --start-date= "2019-03-02 11:55:00"|mysql -uroot -p test(数据库)
0 条评论
下一页
