文档章节

mysql技能梳理

北风刮的不认真了
 北风刮的不认真了
发布于 03/26 16:20
字数 2457
阅读 5.5K
收藏 16

这几天看完了 即客时间的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. 系统直连,在代码里配置上主从的数据库地址,然后拦截器根据语句属性自动切换

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

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

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

© 著作权归作者所有

北风刮的不认真了

北风刮的不认真了

粉丝 40
博文 56
码字总数 33609
作品 1
朝阳
后端工程师
私信 提问
加载中

评论(2)

joeyhacker
joeyhacker
导图的图片有点虚, 看不清
北风刮的不认真了
北风刮的不认真了 博主
可以放大的,放大后就好了
dbaplus丛书丨《MySQL DBA工作笔记》限量签名版来了!

MySQL是开源数据库的典型代表,也是当下企业数字化转型中用于管理数据的排头兵。DT时代暴增的数据量,以及业务上云趋势,对MySQL DBA提出了越来越高的要求,而随着自动化、智能化平台的落地开...

社群丛书
2019/08/17
0
0
会议纪要-06.12.2015

祁博: 01.阵容模块 02.技能点购买 凯庆: 01.祭坛竞技场商店bug修改 雷鹏: 01.AI重构 艳明: 01.好友,邮件系统开发 02.修改bug 晓秋: 01.符文规则熟悉 徐刚: 01.跟进梳理光远未完成的系...

索亚之息
2015/06/12
1
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
会议纪要-06.11.2015

祁博: 01.关卡怪物预览 02.新功能开发 03.上阵布阵UI替换 凯庆: 01.祭坛bug修改 雷鹏: 01.战斗过度流程梳理 艳明: 01.好友系统开发完毕 02.修改bug 晓秋: 01.620之后版本数据 徐刚: 01...

索亚之息
2015/06/11
0
0
论初级PHPer如何点亮技能树

论初级PHPer如何点亮技能树 PHP点点通2016-09-2650 阅读 前言 对于很多刚毕业或者大四的同学,都会有个困惑,我如何学PHP,为什么知识会那么杂,然后实习中公司大概需要哪些要求。此篇文章并未...

PHP点点通
2016/09/26
0
0

没有更多内容

加载失败,请刷新页面

加载更多

郑州哪哪里可以开工程款发票-郑州_新闻网

【电薇同步;1.3.8 - 2.7.4.1 - 5.2.9.7.】张生、诚、信、合、作,保、真、售、后、保、障、长、期、有、效。adb的全称为Android Debug Bridge,是Android手机通用...

yyqqvip
51分钟前
30
0
Nginx 反向代理访问

在Nginx 配置 server { listen 80; server_name www.xiaocx.org www.xiaocx.org www.xiaocx.org; root /Users/maison/work/xiaocx/dist; index i......

韩庚庚
55分钟前
33
0
python笔记:环境变量已设置CMD中一直报错"python"不是内部命令,也不是可运行的程序或批处理文件

这些天虽然也写了几个小工具,但是打包都是在anaconda prompt中完成的,因为CMD中一直报错"python"不是内部命令,也不是可运行的程序或批处理文件,各种查度,千篇一律的是环境变量配置的问题...

小玲_001
57分钟前
13
0
AI+BI服务模式

术语与缩写解释 缩写、术语 解 释 BI 商业智能(Business Intelligence,简称:BI),又称商业智慧或商务智能,指用现代数据仓库技术、线上分析处理技术、数据挖掘和数据展现技术进行数据分析...

zoegu228
58分钟前
22
0
leetcode1227(面试题 17.09. 第 k 个数)--C语言实现

求: 有些数的素因子只有 3,5,7,请设计一个算法找出第 k 个数。注意,不是必须有这些素因子,而是必须不包含其他的素因子。例如,前几个数按顺序应该是 1,3,5,7,9,15,21。 示例 1:...

拓拔北海
今天
27
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部