MYSQL 简单总结（2）

短命女 2023-01-21 10:19 1阅读 0赞

**目录**

truncate和delete的区别

7种join

事务

Mysql的参数设置优化

https://www.cnblogs.com/angryprogrammer/p/6667741.html

https://www.cnblogs.com/marixh/p/12952654.html

主从复制 与 读写分离

分库分表

--------------------

# truncate和delete的区别 #

> 1、事务：truncate是不可以rollback的，但是delete是可以rollback的；
> 
>               原因：truncate删除整表数据(ddl语句,隐式提交)，delete是一行一行的删除，可以rollback
> 
> 2、效果：truncate删除后索引 id从零开始 ，delete不会删除索引 （比如ID删除钱是10，那么truncate删除后会从0开始，delete还是会11开始）  
> 
> 3、delete 删除可以返回行数

# 7种join #

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70][]

# 事务 #

> 事务（ACID）原则： 原子性，一致性，隔离性，持久性
> 
> 原子性实现原理：
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 1][]
> 
> 持久性实现原理：
> 
> ![20210516004428307.png][]
> 
>   隔离级别：
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 2][]
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 3][]
> 
> 参考文献： [https://www.jianshu.com/p/3c8249a13fbb][https_www.jianshu.com_p_3c8249a13fbb]

# Mysql的参数设置优化 #

> 参考文献：
> 
> ### [https://www.cnblogs.com/angryprogrammer/p/6667741.html][https_www.cnblogs.com_angryprogrammer_p_6667741.html] ###
> 
> ### [https://www.cnblogs.com/marixh/p/12952654.html][https_www.cnblogs.com_marixh_p_12952654.html] ###

# 主从复制 与 读写分离 #

> 配置：通过binlog(二进制日志)，进行主从复制。Relay Log(中继日志)
> 
> 参考文献： [https://www.cnblogs.com/gl-developer/p/6170423.html][https_www.cnblogs.com_gl-developer_p_6170423.html]
> 
> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 4][]
> 
> 参考文献：[https://mp.weixin.qq.com/s/qrUn9EkuVFwxS1I2lry4Kg][https_mp.weixin.qq.com_s_qrUn9EkuVFwxS1I2lry4Kg]

# 分库分表 #

> ![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 5][]
> 
> [参考文献： https://haoxuanli.blog.csdn.net/article/details/113696569][https_haoxuanli.blog.csdn.net_article_details_113696569]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70]: /images/20221020/571cef4be2a44cecbd0007cf481c793e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 1]: /images/20221020/70fc615f153d495ab65841d84f9d4bf6.png
[20210516004428307.png]: /images/20221020/e07e5e3472b44c3593f3406bfcd41269.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 2]: /images/20221020/cbf59c488b914e5e8361363fe584fb27.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 3]: /images/20221020/829d8d17507d4f2aa839b6a75795cdd2.png
[https_www.jianshu.com_p_3c8249a13fbb]: https://www.jianshu.com/p/3c8249a13fbb
[https_www.cnblogs.com_angryprogrammer_p_6667741.html]: https://www.cnblogs.com/angryprogrammer/p/6667741.html
[https_www.cnblogs.com_marixh_p_12952654.html]: https://www.cnblogs.com/marixh/p/12952654.html
[https_www.cnblogs.com_gl-developer_p_6170423.html]: https://www.cnblogs.com/gl-developer/p/6170423.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 4]: /images/20221020/4ec085b42a4f4afaaed7537286335205.png
[https_mp.weixin.qq.com_s_qrUn9EkuVFwxS1I2lry4Kg]: https://mp.weixin.qq.com/s/qrUn9EkuVFwxS1I2lry4Kg
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1bHZlamllMDE0Mw_size_16_color_FFFFFF_t_70 5]: /images/20221020/cdd9d4e8e6c54d2ea7f2ddccf3f6340f.png
[https_haoxuanli.blog.csdn.net_article_details_113696569]: https://haoxuanli.blog.csdn.net/article/details/113696569