mysql技能梳理

这几天看完了 即客时间的MySQL实战45讲。又梳理了一遍自己的MySQL知识

事务

不管是什么数据库，事务都是要考虑的。 一般情况下开发注重的是隔离性，如幻读，脏读，不可重复读。然后就是MySQL是怎么实现的。

知道事务的基本概念，那么就该知道数据库锁是什么个意思，然后有些时候死锁，数据库的卡顿，长时间没有响应都是因什么而起

索引和查询

MySQL 设计思想是”如果内存够，就要多利用内存，尽量减少磁盘访问“，而B+树就是能拥有高效查询减少磁盘访问次数的存储结构。基本上纵观MySQL的知识点，基本上都是遵循这个思想。

索引的结构

学习索引，要知道索引是什么样子的结构，为啥用B+，不用数组，不用哈希表，不用二叉树。一是为了减少io操作，二是结构不行，哈希只适合等值，数组插入性能不行，不过用来做静态存储还是可以的。

键

主键： 叶子节点存的是整行的数据。
非主键：叶子节点存的是主键的值

明白了键的存储方式后，查询的时候有一项优化叫覆盖索引,目的就是避免回表，实际操作是联合索引，使用联合索引就要知道最左匹配原则，然后就是知道在查询时，多条件的时候，怎么使用索引的

索引的创建和维护

创建时需要考量的有索引的长度，索引的使用场景，如果是联合索引还需要考虑怎么利用最左匹配原则来少创建索引

需要考虑长度的一般都是字符串索引，太长的是创建不了的。如果太长了也需要创建，可以考虑指给前缀创建索引，如果字符串类似身份证号似的，前缀都差不多一样，就需要换个思路，例如倒过来，使用hash值等

索引随着数据的删除或新增会产生空洞的，有时候会发现删了半个表的数据了，但磁盘空间没减少多少，这是因为索引的空间没有释放。针对这些，我们可以通过重建索引来解决

现在很多业务都是逻辑删

SQL

关于数据库的基本概念和存储的方式理解后，我们就可以进一步去分析理解一条SQL的执行效率是因何而定的

order by

排序的时候有个用来排序的空间sort_buffer， 先说对无索引的排序，MySQL会根据内存的大小决定使用全字段排序还是rowid排序，rowid就是只在sort_buffer 对需要排序的的字段+主键进行排序，然后根据主键进行回表取回所有数据。全字段排序就是全字段都在sort_buffer里，排序完直接给结果，就不用回表了。因为回表有磁盘访问，所以咱们尽量避免。

刚入行时，前辈就说，使用mybatis在select时别用*，需要什么字段就写什么。原因是传输的数据多，io消耗大，确实，但不仅消耗io，还有内存呢。

优化 使用覆盖索引，查询的条件以及order by的条件能联合起来，这样查的时候基本上就是有序的，可以避免排序

覆盖索引简直是万能，但是，索引长度越长，占用的空间也就越大。所以还是需要慎重的。

join

很多公司优化最多的恐怕就是join。

用小表作为驱动表，小表就是where后行数小的表。有时候我们会用left join ，但实际上有时候并不是左边的就是驱动表，哪个为驱动表是优化器来选择的。不过我们还是可以根据业务来预估的。

join的时候，也有个空间用来join，join_buffer。

避免全表扫描，尽管我们有查询条件，但在没有索引的情况下必然是全表扫描，然后两个表都是全表扫描，然后再匹配一下，最简单的join语句，也需要计算 tableA.size*tableB.size次。表越大，查询时间约久。

换种思路

使用临时表呀，当两个非常大的表join时，把要查询的东西插入临时表，再从临时表查询，也许会更快。

使用前，要先测试

使用hash join，数据库中是没有hash join，这个操作是在业务代码里。java举例来说，多个表join时，可以分别根据条件查出list，转成HashMap,然后遍历匹配就好啦~

group by

引用课程里的原话：

如果对 group by 语句的结果没有排序要求，要在语句后面加 order by null；

尽量让 group by 过程用上表的索引，确认方法是 explain 结果里没有 Using temporary 和 Using filesort；

如果 group by 需要统计的数据量不大，尽量只使用内存临时表；

也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表；

如果数据量实在太大，使用 SQL_BIG_RESULT 这个提示，来告诉优化器直接使用排序算法得到 group by 的结果。

count

Count 是需要扫表的，而对于有索引的字段来说，min,max不需要，因为最大值最小值就是索引的边界嘛

我们有时争议 count(0),count(字段)还是count(*)。 如果不需要跳过null的情况，还是用 \*的好，这个MySQL是有优化的。总结其说的，count(*)和coun(0)类似，> count(主键) > count(普通字段)

参考 https://time.geekbang.org/column/article/72775

网上还有其他理论的，https://www.cnblogs.com/-flq/p/10302965.html

但一个给出了原因，第二个只说了结论，我更倾向于前者说的

语句的执行

更新语句

update t set a=1 where id=2;

基本上操作都是在内存里，所以查询的时候内存如果有也是直接取内存的。当需要从数据库查的时候，有个merge的过程，从charge_buffer 到磁盘。

在这里我们要重点了解下redolog ，MySQL不会直接把更新操作更新到磁盘上，是把更新操作都记录在redolog里，攒的差不多了，再一起更新到磁盘。redolog和binlog的二次提交也是MySQL的事务的重要操作。

当MySQL宕机时，redolog也是恢复数据的重要一环。

查询语句

select xx,yy from t where xx =1 order by yy;

查询缓存的在8.0版本开始删除了。

日志

MySQL能够回滚到任何一个时刻的状态，这是真的。这个需要从binlog讲起。binlog里记录了所有的操作记录，有3种格式，statement，row，mixed。statement基本上跟执行的SQL一致，row则包含当时的所有字段的值，mixed就是混合的状态。考虑数据库的恢复以及主从同步的情况，可能需要设置成mixed的模式。

三种模式的讲解参考 ： https://www.cnblogs.com/rinack/p/9595370.html

性能

基本上用好索引后，大部分优化都解决了。但除此之外，还有一些关于锁的，关于IO的。

例如大规模的删数据，修改表结构，都会产生锁，当并发量的时候，更是执行时间看不见尽头...

偶尔什么也没做，也会发现数据库卡一下。这可能是刷新脏页。脏页就是内存和磁盘不一致的页面，在适当的场景需要同步merge。例如redolog写满了，内存不足了，还有2个场景基本不用考虑MySQL空闲了，MySQL关闭了。刷脏页的速度也跟脏页的比例，写磁盘的速度，不过写在流行的SSD就提速了很多，很多服务器IOPS已经不再是瓶颈。

刷新脏页频率可以通过参数设置，从而达到调优的效果，有点像java的GC，不同的是一个是持久化，一个是清理内存。emmm，前者在持久化也清理内存！

高可用

主从，主主互备

在还是个萌新的时候，就搞过主从，是直接按照网上的文档弄的，当时知其然不知其所以然。现在总结下，这个思想类似算法的分而治之然后再收集合并。纲领是这样，做起来很复杂。思路基本上2种

1. 使用代理，类似 mycat
2. 系统直连，在代码里配置上主从的数据库地址，然后拦截器根据语句属性自动切换

各有优缺点，第一种开发上省事了，但运维的难度还是有的，第二种开发上麻烦点，但现在成熟的代码库挺多的，但灵活度上可能不如第一种，例如切换主从，切换数据库...

但不管那种方式，都有主从同步延时的风险，这个可能是因为网络，可能是因为从库硬件不行，也有可能使用了大事务。总之，能预防，有监控，发生问题，有解决方案。

做笔记嘛，还是思维导图感觉好用