mysql技能梳理

语言: CN / TW / HK

3 月,跳不动了?>>>

这几天看完了 即客时间的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里,攒的差不多了,再一起更新到磁盘。redologbinlog的二次提交也是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. 系统直连,在代码里配置上主从的数据库地址,然后拦截器根据语句属性自动切换

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

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

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

分享到: