MySQL
的逻辑架构
MySQL
逻辑架构图主要分三层:
(1)第一层负责连接处理,授权认证,安全等等
(2)第二层负责编译并优化SQL
(3)第三层是存储引擎。
一条SQL
查询语句执行过程
mysql分为server层与存储引擎层,server层包含连接器、分析器、优化器、执行器**。**
接下来以一条sql查询语句执行过程介绍各个部分功能。客户端执行一条sql:
1、首先由连接器进行身份验证,权限管理
2、若开启了缓存,会检查缓存是否有该sql对应结果(缓存存储形式key-vlaue,key是执行的sql,value是对应的值)若开启缓存又有该sql的映射,将结果直接返回;
3、分析器进行词法语法分析
4、优化器会生成执行计划、选择索引等操作,选取最优执行方案
5、然后来到执行器,打开表调用存储引擎接口,逐行判断是否满足查询条件,满足放到结果集,最终返回给客户端;若用到索引,筛选行也会根据索引筛选。
两阶段提交(一条更新语句怎么执行?)
1、引擎先根据筛选条件筛选对应的行返回给执行器(若对应的行在内存直接返回,否则先去磁盘读取再返回)
2、执行器执行相关更新操作然后调用引擎接口写回更新后数据
3、引擎将新数据更新到内存,将更新操作记录到redolog
,redolog
处于prepare
,告知执行器执行完,可提交事务
4、执行器生成该操作的binlog
并将binlog
写入磁盘
5、执行器调用引擎事务提交接口,引擎把刚写入的redolog
改为commit
状态,更新完成。
何为两阶段提交?(2PC
)
mysql
中在server
层级别有个binlog
日志,归档日志,用于备份,主从同步复制,如果采用一主多从架构,主备切换,那就必须用到binlog
进行主从同步。
此时事务提交就必须保证redolog
与binlog
的一致性,一般情况没有开启binlog
日志,事务提交不会两阶段提交,若需要主从同步就必须开启binlog
使用两阶段提交保证数据一致性。
为什么要两阶段提交?
保证redolog
与binlog
一致性,保证事务在多个引擎的原子性。
两阶段提交过程?
Prepare 阶段:
InnoDB
将回滚段undolog
设置为 prepare
状态;将redolog
写文件并刷盘;
(1、先写redolog,事务进入prepare状态)
Commit 阶段:
-
Binlog
写入文件; -
binlog
刷盘; -
InnoDB commit
;
prepare
成功,binlog
写盘,然后事务进入commit
状态,同时会在redolog
记录commite
标识,代表事务提交成功)
redolog
与binlog
怎样联系起来的?(XID
)
- 崩溃恢复的时候,会按顺序扫描
redo log
,若redolog
既有prepare
又有commit
,直接提交 - 如果碰到只有prepare、而没有
commit
的redo log
,就拿着XID
去binlog
找对应的事务。
怎样判断binlog
是否完整?
statement
格式的binlog
,最后会有COMMIT
row
格式 末尾有XID event
2pc
不同时刻的崩溃恢复?
- 1、
redolog
有commite
标识,事务完整,直接提交事务 - 2、若
redolog
里面的事务只有完整的prepare
,则判断对应事务的binlog
是否存在并完整 (是-提交事务 | 否-回滚事务)
索引
什么是索引
- 排好序的数据结构,可以帮助快速查找数据
- 优缺点:索引可以提高查询速度,查询使用优化隐藏器提高性能,但是也会占据物理空间,降低增删改的速度,因为还要操作索引文件
索引类型
覆盖索引+回表+索引下推+联合索引
- 普通索引:可以重复
- 唯一索引:唯一,可为空,表中只有一个主键索引,可多个唯一索引
- 主键索引
-
- 唯一,不为空,叶子结点存出了行记录数据,主键索引也称聚簇索引,对应非主键索引的叶子结点存的主键的值(二级索引),用二级索引查需要回表操作(根据二级索引查到主键,再根据主键去主键索引查)
- 一般推荐用自增主键,保证空间利用率,减少页分裂
- 全文索引
- 覆盖索引:索引字段覆盖了查询语句涉及的字段,直接通过索引文件就可以返回查询所需的数据,不必通过回表操作。
- 回表:通过索引找到主键,再根据主键id去主键索引查。
- 索引下推
- 在根据索引查询过程中就根据查询条件过滤掉一些记录,减少最后的回表操作
假如执行:
select * from stu where name=? and age=?
没有索引下推先再存储引擎根据name筛选数据返回给server层,然后server层再根据age过滤,有索引下推直接根据name和age在存储引擎层就筛选得到结果
索引底层数据结构?
B+树、hash
hash底层是哈希表实现,等值查询,可以快速定位,一般情况效率很高,不稳定,当出现大量键重复哈希冲突,效率下降,不支持范围查询,无法用于排序分组,无法模糊查询,多列索引的最左前缀匹配原则,总要回表操作等。
聚集索引与非聚集索引的区别
- 一个表中只能拥有一个聚集索引,而非聚集索引一个表可以存在多个。
- 聚集索引,索引中键值的逻辑顺序决定了表中相应行的物理顺序;非聚集索引,索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同。
- 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。
- 聚集索引:物理存储按照索引排序;非聚集索引:物理存储不按照索引排序;
最左前缀原则和最左匹配原则
最左前缀原则,就是最左优先,在创建多列索引时,要根据业务需求,where 子句中使用最频繁的一列放在最左边。
当我们创建一个组合索引的时候,如 (a1
,a2
,a3
),相当于创建了(a1
)、(a1
,a2
)和(a1
,a2
,a3
)三个索引,这就是最左匹配原则。
索引不适合哪些场景?
- 数据量少的不适合加索引
- 更新比较频繁的也不适合加索引 = 区分度低的字段不适合加索引(如性别)
索引有哪些优缺点?
(1) 优点:
- 唯一索引可以保证数据库表中每一行的数据的唯一性
- 索引可以加快数据查询速度,减少查询时间
(2)缺点:
- 创建索引和维护索引要耗费时间
- 索引需要占物理空间,除了数据表占用数据空间之外,每一个索引还要占用一定的物理空间
- 以表中的数据进行增、删、改的时候,索引也要动态的维护。
B树与B+树区别?为何用B+树?
(1)为什么不是普通二叉树?
如果二叉树特殊化为一个链表,相当于全表扫描。平衡二叉树相比于二叉查找树来说,查找效率更稳定,总体的查找速度也更快。
(2)为什么不是平衡二叉树呢?
我们知道,在内存比在磁盘的数据,查询效率快得多。如果树这种数据结构作为索引,那我们每查找一次数据就需要从磁盘中读取一个节点,也就是我们说的一个磁盘块,但是平衡二叉树可是每个节点只存储一个键值和数据的,如果是B树,可以存储更多的节点数据,树的高度也会降低,因此读取磁盘的次数就降下来啦,查询效率就快啦。
(3)为什么不是 B 树而是 B+ 树呢?
B+ 树非叶子节点上是不存储数据的,仅存储键值,而B树节点中不仅存储键值,也会存储数据。
innodb
中页的默认大小是16KB
,如果不存储数据,那么就会存储更多的键值,相应的树的阶数(节点的子节点树)就会更大,树就会更矮更胖,如此一来我们查找数据进行磁盘的IO次数有会再次减少,数据查询的效率也会更快。
B+ 树索引的所有数据均存储在叶子节点,而且数据是按照顺序排列的,链表连着的。那么 B+ 树使得范围查找,排序查找,分组查找以及去重查找变得异常简单。
- 非叶子结点不存data,只存key,查询更稳定,增大了广度(B+树出度更大,树高矮,节点小,磁盘IO次数少);
- 叶子结点下一级指针(范围查询);
- 索引冗余。
Hash 索引和 B+ 树索引区别是什么?你在设计索引是怎么抉择的?
-
B+ 树可以进行范围查询,Hash 索引不能。
-
B+ 树支持联合索引的最左侧原则,Hash 索引不支持。
-
B+ 树支持 order by 排序,Hash 索引不支持。
-
Hash 索引在等值查询上比 B+ 树效率更高。
-
B+ 树使用 like 进行模糊查询的时候,like 后面(比如%开头)的话可以起到优化的作用,Hash 索引根本无法进行模糊查询。
与红黑树相比:
更少查询次数:B+树出度更大,树高更低,查询次数更少
磁盘预读原理:为了减少IO操作,往往不严格按需读取,而是预读。B+树叶子结点存储相临,读取会快一些。
**存储更多索引结点:**B+树只在叶子结点储存数据,非叶子结点存索引,而一个结点就是磁盘一个内存页,内存页大小固定,那么相比B树这些可以·存更多的索引结点,出度更大,树高矮,查询次数少,磁盘IO少。
索引设计原则(查询快,占用空间少)
-
出现在where子句或则连接子句中的列
-
基数小的表没必要
-
使用短索引,如果索引长字符串列,应该指定前缀长度
-
定义有外键的数据列一定索引
-
不要过度索引
-
更新频繁的不适合
-
区分度不高的不适合,如性别
-
尽量扩展索引,别新建索引,如(a)->(a,b)
-
字符串字段建立索引方法
-
-
1、直接创建完整索引,这样可能比较占用空间;
-
2、创建前缀索引,节省空间,但会增加查询扫描次数,并且不能使用覆盖索引;
-
3、倒序存储,再创建前缀索引,用于绕过字符串本身前缀的区分度不够的问题;
-
4、额外用一个字段进行索引,额外计算开销
-
总结:索引设计原则要求查询快,占用空间少;一般建在where条件,匹配度高的;要求基数大,区分度高,不要过大索引,尽量扩展,用联合索引,更新频繁不适合、使用短索引。
索引失效场景?
- 以“%”开头的like语句,索引无效,后缀“%”不影响
or
语句前后没有同时使用索引- 列类型是字符串,一定要在条件中将数据用引号引用,否则失效(隐式转换)
- 如果mysql估计使用全表扫描比索引快,则不用索引(键值少,重复数据多)
- 组合索引要遵守最左前缀原则——不使用第一列索引 失效
- 在索引字段上使用
not
,<>,!= (对它处理是全表扫描) - 对索引字段进行计算操作,字段使用函数也会失效
is null
如何创建索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
CREATE INDEX index_name ON table_name (column_list);
#在create table时创建
非聚簇索引一定会回表查询吗?
查询字段全部命中索引,覆盖索引,不走回表,直接从索引得到结果,不要查数据文件
总结:覆盖索引就不走回表
联合索引的建立规则?
-
将查询需求频繁或者字段选择性高的列放在前面
-
索引的复用,可以少维护一些索引(a)->(a,b)
-
- 如果既有联合查询,又有基于a、b各自的查询呢?:考虑的原则就是空间,将小的单独建索引
最左匹配原则
从左往右匹配,直到遇到范围查询,建立联合索引(a,b,c)
索引是先根据a排序,a相同时b有序,a不同无序,以此类推。总之遇到范围查询就停。
(a,b)联合索引 [(2,4),(),()]
\|/ \|/
[(1,1),(1,2),(2,1)] [(2,4),(3,1),(3,2)]
规律:a有顺序(1,1,2,2,2,3,3)b无顺序,a相同时b又有顺序,不同a之间b没有顺序,所以a=1,b>2走联合索引;a>1,b>2不走索引。
select * from table_name where a = '1' and b = '2' and c = '3'
//全值匹配查询,用到索引,与顺序无关,查询优化器,会自动优化查询顺序
select * from table_name where a = '1'
select * from table_name where a = '1' and b = '2'
select * from table_name where a = '1' and b = '2' and c = '3'
//匹配左边的列时,用到了索引
select * from table_name where b = '2'
select * from table_name where c = '3'
select * from table_name where b = '1' and c = '3'
//没有用到索引
select * from table_name where a = '1' and c = '3'
//a用到了索引,b、c没有到
select * from table_name where a > 1 and a < 3 and b > 1;
//只有a用到索引,在1<a<3的范围内b是无序的,不能用索引,找到1<a<3的记录后,只能根据条件 b > 1继续逐条过滤
select * from table_name where a = 1 and b > 3;
// a=1的情况下b是有序的,进行范围查找走的是联合索引 走 a b索引(a相同时b有序)
前缀索引
尽量创建短索引,对长子字符串创索引可使用前缀索引,使用字段值前几个字符作为索引 index(filed(10))
百万级数据如何删除
删除数据的速度和创建的索引数量是成正比的。先删索引,再删无用数据,再创建索引
普通索引和唯一索引怎样选
-
查询比较
- 查询会以页为单位将数据页加载进内存,不需要一条记录一条记录读取磁盘。然后唯一索引根据条件查询到记录时就返回结果,普通索引查到第一条记录往后遍历直到不满足条件,由于都在内存中,不需要磁盘读取那么大开销,带来的额外查询开销忽略不计,所以查询性能几乎一致
-
更新比较
-
唯一索引由于更新时要检查唯一性,所以需要将数据页先加载进内存才能判断,此时直接操作内存,不需要操作change buffer
-
补充:普通索引若数据再内存中直接内存中更新,否则会将更新操作先记录到channge buffer中,等下一次查询将数据读到内存中再进行change buffer里相关更新操作后将数据返回,这样一来,再写多读少的情况下就减少了磁盘IO,若写完就马上查询,就大可不必用change buffer,不但没提高多少效率还造成维护change buffer额外消耗
-
将change buffer的操作对应到原始数据页的操作称为merge(可以查询来时读到内存再修改数据,后台线程也会merge,数据库正常关闭也会merge)
-
-
适合场景
- 写多读少,选用普通索引更好,可以利用change buffer进行性能优化减少磁盘IO,将更新操作记录到change bufer,等查询来了将数据读到内存再进行修改.
锁
MySQL
遇到死锁问题,如何解决的?
-
查看死锁日志
show engine innodb status
; -
找出死锁
SQL
; -
分析
SQL
加锁情况; -
模拟死锁案发 ;
-
分析死锁日志 ;
-
分析死锁结果;
- 悲观锁:
悲观锁她专一且缺乏安全感了,她的心只属于当前事务,每时每刻都担心着它心爱的数据可能被别的事务修改,所以一个事务拥有(获得)悲观锁后,其他任何事务都不能对数据进行修改啦,只能等待锁被释放才可以执行。
- 乐观锁:
乐观锁的“乐观情绪”体现在,它认为数据的变动不会太频繁。因此,它允许多个事务同时对数据进行变动。
实现方式:乐观锁一般会使用版本号机制或CAS算法实现。
MVCC
原理
多版本并发控制。
MVCC
(Multiversion Concurrency Control),即多版本并发控制技术。
MVCC
在MySQL InnoDB
中的实现主要是为了提高数据库并发性能,用更好的方式去处理读-写冲突,做到即使有读写冲突时,也能做到不加锁,非阻塞并发读。
原理提炼总结
使用版本链+Read View
版本链 同一行数据可能有多个版本
innodb
数据表每行数据记录会有几个隐藏字段,row_id
,事务ID,回滚指针。
1、Innodb
采用主键索引(聚簇索引),会利用主键维护索引,若表没有主键,就用第一个非空唯一索引,若没有唯一索引,则用row_id
这个隐藏字段作为主键索引。
2、事务开启会向系统申请一个事务ID,严格递增,会向行记录插入最近操作它的那个事务的ID。
3、undolog
会记录事务前老版本数据,然后行记录中回滚指针会指向老版本位置,如此形成一条版本链。因此可以利用undo log
实现回滚,保证原子性,同时用于实现MVCC
版本链。
Read View读已提交隔离级别下,会在每次查询都生成一个Read View
,可重读读只在事务开始时生成一个Read View
,以后每次查询都用这个Read View
,以此实现不同隔离界别。
Read View里面包含些什么?(一致性视图)
一个数组+up_limit_id(低水位)+low_limit_id(高水位)(这里的up
,low
没写错,就是这么定义的)
数组里包含事务启动时当前活跃事务ID(未提交事务),低水位就是活跃事务最小ID,高水位就是下一次将分配的事务ID,也就是目前最大事务ID+1。
数据可见性规则是怎样实现的?
数据版本的可见性规则,就是**基于数据的row
、trx_id
和这个一致性视图(Read View)**的对比结果得到的。
视图数组把所有的row trx_id
分成了几种不同的情况
读取原理
某事务T要访问数据A,先获取该数据A中的事务id(获取最近操作它的事务的事务ID),对比该事务T启动时刻生成的Read View:
1、如果在readview
的左边(比readview
都小),表示这个事务可以访问这数据(在左边意味着该事务已经提交)
2、如果在readview
的右边(比readview
都大),表示这个版本是由将来启动的事务生成的,是肯定不可见的;
3、如果当前事务在未提交事务集合中:
a、若 row trx_id
在数组中,表示这个版本是由还没提交的事务生成的,不可见;
b. 若 row trx_id
不在数组中,表示这个版本是已经提交了的事务生成的,可见。
不可以访问,获取roll_pointer
,通过版本链取上一版本
根据数据历史版本事务ID
再重新与视图数组对比。
这样执行下来,虽然期间这一行数据被修改过,但是事务A不论在什么时候查询,看到这行数据的结果都是一致的,所以我们称之为一致性读。
事务
MySQL
事务得四大特性以及实现原理
-
原子性:事务作为一个整体被执行,包含在其中的对数据库的操作要么全部被执行,要么都不执行。
-
一致性:指在事务开始之前和事务结束以后,数据不会被破坏,假如A账户给B账户转10块钱,不管成功与否,A和B的总金额是不变的。
-
隔离性:多个事务并发访问时,事务之间是相互隔离的,即一个事务不影响其它事务运行效果。简言之,就是事务之间是进水不犯河水的。
-
持久性:表示事务完成以后,该事务对数据库所作的操作更改,将持久地保存在数据库之中。
事务的隔离级别有哪些?MySQL
的默认隔离级别是什么?
-
读未提交(
Read Uncommitted
) -
读已提交(
Read Committed
) -
可重复读(
Repeatable Read
) -
串行化(
Serializable
)
MySQL
默认的事务隔离级别是可重复读(Repeatable Read)
ACID实现原理
原子性:undolog
(记录事务开始前的老版本数据,可以保证原子操作,回滚,实现MVCC
版本链)
隔离性:MVCC
持久性:redo log
(记录事务开启后对数据的修改,可用于crash
-safe
)
什么是幻读,脏读,不可重复读呢?
事务A、B交替执行,事务A被事务B干扰到了,因为事务A读取到事务B未提交的数据,这就是脏读。
在一个事务范围内,两个相同的查询,读取同一条记录,却返回了不同的数据,这就是不可重复读。
事务A查询一个范围的结果集,另一个并发事务B往这个范围中插入/删除了数据,并静悄悄地提交,然后事务A再次查询相同的范围,两次读取得到的结果集不一样了,这就是幻读。
幻读问题详解
创建table
DROP TABLE IF EXISTS `tx`;
CREATE TABLE `tx` (
`age` int(5) DEFAULT NULL,
`name` varchar(5) DEFAULT NULL,
`id` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of tx
-- ----------------------------
INSERT INTO `tx` VALUES ('20', '张三', '1');
INSERT INTO `tx` VALUES ('20', '李四', '2');
结论
1、发现RR隔离界别若只快照读与当前读没有幻读问题,快照读(普通查询,如select * from table
)读取旧的历史版本,用MVCC实现
,会在事务开始时生成一个Read View
,之后都用这个Read View实现RR隔离级别。
当前读(select ... for update
,select ... lock in share mode
,update/insert/delete
语句)读取最新数据版本,依靠间隙锁或则临键锁解决幻读,当你事务T1
执行当前读,然后事务T2
插入语句,事务T2
会被阻塞住,插不进去。
2、当你事务T1
中先执行快照读,事务T2
插入数据并提交,事务T1
再执行当前读(比如以相同条件更新数据),会发现出现幻读,更新到了新插入行的数据。
白话文解释:事务1先以某个条件比如age=20
的查询得到2
条数据,然后事务2
插入新的数据age
也为20
然后提交事务,此时事务1更新age=20
的数据,发现更新到了3
行,把事务T2
新插入的那行也更新了。
所以幻读注重你插入新数据都修改改到了新插入的数据,而不可重复读是你修改了某个数据,两次查询得到不一致结果。
总结:(RR隔离界别并没有完全解决幻读)只使用快照都或则当前读不会幻读。若先快照读,然后当前读,期间按快照读相同条件插入数据,当前读就会发生幻读。
**总结:**读已提交和可重复读实现原理就是MVCC Read View
不同的生成时机。可重复读只在事务开始时生成一个Read View
,之后都用的这个;读已提交每次执行前都会生成Read View
。
脏页?怎样刷新脏页
内存数据页和磁盘数据页不一致。
刷脏页情景:
- redo log写满了,停止所有更新操作,将checkpoint向前推进,推进那部分日志的脏页更新到磁盘。
- 系统内存不够,需要将一部分数据页淘汰,如果是干净页,直接淘汰就行了,脏页的话,需要全部同步到磁盘。
mysql
自认为空闲时mysql
正常关闭之前
三大范式
第一范式:字段具有原子性,不可再分(字段单一职责)
第二范式:满足第一范式,每行应该被唯一区分,加一列存放每行的唯一标识符,称为主键(都要依赖主键)
第三范式:满足一二范式,且一个表不能包含其他表已存在的非主键信息(不间接依赖-不存在其他表的非主键信息)
范式优点与缺点:
优点:范式化,重复冗余数据少,更新快,修改少,查询时更少的distinct
缺点:因为一个表不存在冗余重复数据,查询可能造成很多关联,效率变低,可能使一些索引策略无效,范式化将列存在不同表中,这些列若在同一个表中可以是一个索引。
MyISAM
与InnoDB
区别
InnoDB
聚簇索引,MyISAM
非聚簇索引InnoDB
数据与索引一起保存.ibd
,MyISAM
表结构.frm
索引.myi
数据.myd
InnoDB
支持事务、外键、行锁表锁,MyISAM
不支持事务、外键、只支持表锁select count(*) from table
MyISAM
查询更优,InnoDB
更新更优- 都是
B+tree
索引 MyISAM
支持全文索引,InnoDB5.6
后支持
MyISAM
-
不支持事务,但是每次查询都是原子的
-
支持表级锁,每次操作对整个表加锁
-
存储表的总行数
-
一个
MyISAM
表有三个文件:表结构.frm
索引.myi
数据.myd
-
采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致,但是辅索引不用保证唯一性。
Innodb
-
支持ACID事务,支持四种隔离级别
-
支持行级锁及外键约束,因此支持写并发
-
不存储总行
-
主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值;因此从辅索引查找数据,需要先通过辅索引找到主键值,再访问辅索引;最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。
使用场景
大多数时候我们使用的都是 InnoDB
存储引擎,在某些读密集的情况下,使用 MyISAM
也是合适的。不过,前提是你的项目不介意 MyISAM
不支持事务、崩溃恢复等缺点(可是~我们一般都会介意啊!)。
MyISAM
适合读多更新少的:MyISAM
索引跟数据分开放,因此有读取更快的说法。InnoDB
适合插入更新频繁的:索引与数据一起放,建立索引更复杂,使用行锁,更新频繁效率更高- 需要事务,高并发场景用
Innodb
:Innodb
支持事务,采用行锁 MyISAM
查询比InnoDB
快,更新InnoDB
快
场景:MyISAM
查询更优,InnoDB
更新更优
MyISAM
适合读多,更新少的场景。MyISAM
使用非聚簇索引,数据和索引分开存的,而InnoDB
数据和索引存一起的,数据量大时,一个内存页大小固定,读进内存的数据MyISAM
就多一点(数据量小看不出差距,数据量大时差距就明显)。
因为MyISAM
只把索引指针读进内存,可以存更多,查询速度也就更快,而且InnoDB
还需要维护其他数据,比如其他隐藏字段 row_id
、tx_id
等
自增主键:
InnoDB
引擎的自增值,其实是保存在了内存里,并且到了MySQL 8.0
版本后,才有了“自增值持久化”的能力。
也就是才实现了“如果发生重启,表的自增值可以恢复为MySQL
重启前的值”,具体情况是:(查看表结构,会看到自增主键=多少)
-
在
MySQL 5.7
及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值max(id)
,然后将max(id)+1
作为这个表当前的自增值。 -
举例来说:如果一个表当前数据行里最大的id是10,
AUTO_INCREMENT
=11。这时候,我们删除id=10的行,AUTO_INCREMENT
还是11。但如果马上重启实例,重启后这个表的AUTO_INCREMENT
就会变成10。也就是说,MySQL
重启可能会修改一个表的AUTO_INCREMENT
的值。 -
在
MySQL 8.0
版本,将自增值的变更记录在了redo log
中,重启的时候依靠redo log
恢复重启之前的值。
自增值修改机制:
- 如果插入数据时id字段指定为0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT值填到自增字段;
- 如果插入数据时id字段指定了具体的值,就直接使用语句里指定的值。
自增值新增机制:
- 如果准备插入的值>=当前自增值,新的自增值就是“准备插入的值+1”;
- 否则,自增值不变。
为什么自增主键不连续
-
在
MySQL
5.7及之前的版本,自增值保存在内存里,并没有持久化 -
事务回滚(自增值不能回退,因为并发插入数据时,回退自增ID可能造成主键冲突)
-
唯一键冲突(由于表的自增值已变,但是主键发生冲突没插进去,下一次插入主键=现在变了的子增值+1,所以不连续)
eg:
假设,表t里面已经有了(1,1,1)这条记录,这时我再执行一条插入数据命令:
insert into t values(null, 1, 1); (自增id,唯一键c,普通字段d)
这个语句的执行流程就是:
-
执行器调用InnoDB引擎接口写入一行,传入的这一行的值是(0,1,1);
-
InnoDB发现用户没有指定自增id的值,获取表t当前的自增值2;
-
将传入的行的值改成(2,1,1);
-
将表的自增值改成3;
-
继续执行插入数据操作,由于已经存在c=1的记录,所以报Duplicate key error,语句返回。
这个表的自增值改成3,是在真正执行插入数据的操作之前。这个语句真正执行的时候,因为碰到唯一键c冲突,所以id=2这一行并没有插入成功,但也没有将自增值再改回去。
所以,在这之后,再插入新的数据行时,拿到的自增id就是3。也就是说,出现了自增主键不连续的情况。
Innodb
为什么推介用自增ID
- 主键页就会近乎于顺序的记录填满,提升了页面的最大填充率,不会有页的浪费。
- 新插入的行一定会在原有的最大数据行下一行,mysql定位和寻址很快,不会为计算新行的位置而做出额外的消耗。
- 减少了页分裂和碎片的产生
UUID
:大量的随机IO+页分裂导致移动大量的数据+数据会有碎片。
总结:自增ID有序,会按顺序往最后插入,而UUID
无序,随机生成,随机插入,会造成频繁页分裂,内存碎片化,大量随机IO
SQL
优化
1,优化表结构
(1)尽量使用数字型字段
若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
(2)尽可能的使用 varchar
代替 char
变长字段存储空间小,可以节省存储空间。
(3)当索引列大量重复数据时,可以把索引删除掉
比如有一列是性别,几乎只有男、女、未知,这样的索引是无效的。
2,优化查询
-
应尽量避免在
where
子句中使用!=
或<>
操作符 -
应尽量避免在
where
子句中使用or
来连接条件 -
任何查询也不要出现
select * from table
-
避免在
where
子句中对字段进行null
值判断
3,索引优化
- 对作为查询条件和
order by
的字段建立索引 - 避免建立过多的索引,多使用组合索引
日志机制分析
为了保证事务ACID中的一致性与原子性,mysql
采用WAL
,预写日志,先写日志,合适时再写磁盘。
innodb
引擎级别有undo log
与redo log
,mysql server
级别有bin log
。
undo log
:回滚日志
作用:
undolog
记录事务开始前老版本数据,用于实现回滚,保证原子性,实现MVCC
,会将数据修改前的旧版本保存在undolog
,然后行记录有个隐藏字段回滚指针指向老版本。
redo log
:物理日志
作用:会记录事务开启后对数据做的修改,crash-safe
-
空间一定,写完后会循环写,有两个指针write pos指向当前记录位置,checkpoint指向将擦除的位置,redolog相当于是个取货小车,货物太多时来不及一件一件入库太慢了这样。
-
就先将货物放入小车,等到货物不多或则小车满了或则店里空闲时再将小车货物送到库房。用于crash-safe,数据库异常断电等情况可用redo log恢复。
写入流程
先写redo log buffer
,然后wite
到文件系统的page cache
,此时并没有持久化,然后fsync
持久化到磁盘。
写入策略
根据innodb_flush_log_at_trx_commit
参数控制(我的记忆:innodb
以事务的什么提交方式刷新日志)
-
事务提交时只把redo log留在redo log buffer
-
将redo log直接持久化到磁盘(所以有个双“1”配置,后面会讲)
-
只是把redo log写到page cache
bin log:用于主备同步
有3种格式:
-
row
:记录整行数据,更新记录更新前后的数据,缺点:记录每行数据,占空间 -
statement
:记录整条sql语句,缺点:可能造成主从不一致- mysql> delete from t where a>=4 and b<=5 limit 1;
- 主库是索引a,那么删除a=4
- 备库是索引b,那么删除b=5
-
mixed
:会判断statement
格式下SQL
语句是否会造成主备不一致,不造成就statement
格式,否则就row
格式。
写入机制:
- 事务执行过程中将日志记录到
binlog cache
(系统为binlog
分配了一块内存,每个线程一份) - 事务提交时,执行器把
binlog cache
里的完整事务写入到binlog
中,并清空binlog cache
write
:把日志写到文件系统的page cache
,没有写磁盘,速度快fsync
:将数据持久化到磁盘的操作,这时才占磁盘IOPS
- 根据
sync_binlog
参数控制:- 只write,不
fsync
- 每次
fsyncN
- 每次事务都
write
,等累积到N后才fsync
。
- 只write,不
可以将sync_binlog
设置大一点提高性能(可以提高IO性能,但是若发生异常,日志会丢失)
这里sync_binlog
和innodb_flush_log_at_trx_commit
配合设置双1模式
Explain分析
在 select
语句之前增加 explain
关键字,会返回执行计划的信息。
id
列:是select
语句的序号,MySQL
将select
查询分为简单查询和复杂查询。select_type
列:表示对应行是是简单还是复杂的查询。- table 列:表示
explain
的一行正在访问哪个表。 - type 列:最重要的列之一。表示关联类型或访问类型,即
MySQL
决定如何查找表中的行。从最优到最差分别为:system
>const
>eq_ref
>ref
>fulltext
>ref_or_null
>index_merge
>unique_subquery
>index_subquery
>range
>index
>ALL
possible_keys
列:显示查询可能使用哪些索引来查找。key
列:这一列显示MySQL
实际采用哪个索引来优化对该表的访问。key_len
列:显示了MySQL
在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。ref
列:这一列显示了在key
列记录的索引中,表查找值所用到的列或常量,常见的有:const
(常量),func
,NULL
,字段名。rows
列:这一列是MySQL
估计要读取并检测的行数,注意这个不是结果集里的行数。Extra
列:显示额外信息。比如有Using index
、Using where
、Using temporary
等。
MySQL
数据库cpu
飙升的话,要怎么处理呢?
排查过程:
(1)使用top
命令观察,确定是mysqld
导致还是其他原因。
(2)如果是mysqld
导致的,show processlist
,查看session
情况,确定是不是有消耗资源的sql
在运行。
(3)找出消耗高的 sql
,看看执行计划是否准确, 索引是否缺失,数据量是否太大。
处理:
(1)kill
掉这些线程(同时观察 cpu
使用率是否下降),
(2)进行相应的调整(比如说加索引、改 sql
、改内存参数)
(3)重新跑这些 SQL
。
其他情况:
也有可能是每个 sql
消耗资源并不多,但是突然之间,有大量的 session
连进来导致 cpu
飙升,这种情况就需要跟应用一起来分析为何连接数会激增,再做出相应的调整,比如说限制连接数等
MYSQL
调优篇
一条sql执行很慢的原因?
我们平时写SQL
时,都要养成用explain
分析的习惯。慢查询的统计,运维会定期统计给我们
优化慢查询思路:
- 分析语句,是否加载了不必要的字段/数据
- 分析
SQL
执行句话,是否命中索引等 - 如果
SQL
很复杂,优化SQL
结构 - 如果表数据量太大,考虑分表
-
大多数情况下很正常,偶尔很慢,则有如下原因
- 数据库在刷新脏页,例如 redo log 写满了需要同步到磁盘。
- 执行的时候,遇到锁,如表锁、行锁。
SQL
写的烂了
-
这条
SQL
语句一直执行的很慢,则有如下原因;- 没有用上索引或则索引失效:例如该字段没有索引;由于对字段进行运算、函数操作导致无法用索引。
- 有索引可能会走全表扫描
怎样判断是否走全表扫描:
索引区分度(索引的值不同越多,区分度越高),称为基数,而数据量大时不可能全部扫描一遍得到基数,而是采样部分数据进行预测,那有可能预测错了,导致走全表扫描。
sql优化(定位低效率sql,慢查询怎样处理)
- 数据库中设置
SQL
慢查询
- 方式一:修改配置文件 在
my.ini
增加几行: 主要是慢查询的定义时间(超过2秒就是慢查询),以及慢查询log
日志记录(slow_query_log
)
[mysqlld]
//定义查过多少秒的查询算是慢查询,我这里定义的是2秒
long_query_time=2
#5.8、5.1等版本配置如下选项
log-slow-queries="mysql_slow_query.log"
#5.5及以上版本配置如下选项
slow-query-log=On
slow_query_log_file="mysql_slow_query. log"
1/记录下没有使用索引的query
log-query-not-using-indexestpspb16glos dndnorte/t
- 方式二:通过
MySQL
数据库开启慢查询:
mysql>set global slow_query_log=ON
mysql>set global long_query_time = 3600;
mysql>set global log_querise_not_using_indexes=ON;
- 分析慢查询日志:可以通过以下命令定位低效率执行
SQL
,show processlistsql 可以用 explain 分析执行计划
索引
-
尽量覆盖索引,5.6支持索引下推;
-
组合索引符合最左匹配原则;
-
避免索引失效;
-
再写多读少的场景下,可以选择普通索引而不要唯一索引,更新时,普通索引可以使用
change buffer
进行优化,减少磁盘IO
,将更新操作记录到change bufer
,等查询来了将数据读到内存再进行修改; -
索引建立原则(一般建在
where
和order by
,基数要大,区分度要高,不要过度索引,外键建索引);
sql语句
1、分页查询优化
该方案适用于主键自增的表,可以把Limit查询转换成某个位置的查询。
select * from tb_sku where id>20000 limit 10;
2、优化insert语句
-
多条插入语句写成一条
-
在事务中插数据
-
数据有序插入(主键索引)
数据库结构优化
1、将字段多的表分解成多个表
有些字段使用频率高,有些低,数据量大时,会由于使用频率低的存在而变慢,可以考虑分开。
2、对于经常联合查询的表,可以考虑建立中间表
优化器优化
1、优化器使用MRR
原理:MRR 【Multi-Range Read】将ID或键值读到buffer排序,通过把「随机磁盘读」,转化为「顺序磁盘读」,减少磁盘IO,从而提高了索引查询的性能。
mysql >set optimizer_switch='mrr=on';
explain 查看 Extra多了一个MRR
explainselect*from stu where age between 10 and 20;
对于 MyiSAM
,在去磁盘获取完整数据之前,会先按照 rowid
排好序,再去顺序的读取磁盘。
对于 Innodb
,则会按照聚簇索引键值排好序,再顺序的读取聚簇索引。
**磁盘预读:**请求一页的数据时,可以把后面几页的数据也一起返回,放到数据缓冲池中,这样如果下次刚好需要下一页的数据,就不再需要到磁盘读取(局部性原理)
索引本身就是为了减少磁盘 IO,加快查询,而 MRR,则是把索引减少磁盘 IO 的作用,进一步放大
https://zhuanlan.zhihu.com/p/148680235
架构优化
读/写分离(主库写,从库读)
总结:
1、先设置慢查询(my.ini或数据库命令)
2、分析慢查询日志
3、定位低效率sql(show processlist)
4、explain分析执行计划(是否索引失效,用到索引没,用了哪些)
5、优化(索引+sql语句+数据库结构优化+优化器优化+架构优化)
主从同步
主从三条线程+binlog+relaylog(中继日志)
原理:
- binlog会在服务器启动生成,用于记录主库数据库变更记录,当binlog发生变更时,主结点的log dump线程会将其内容发给各个从结点,从结点的 IO线程接收binlog内容,并写入relay log(从节点上),从结点的SQL线程读取relay log内容对数据库数据进行更新重放,保证主从一致性
同步问题:
- 全同步复制:主库强制同步日志到从库,等全部从库执行完才返回客户端,性能差
- 半同步复制:主库收到至少一个从库确认就认为操作成功,从库写入日志成功返回ack确认
高可用架构
一主一备
主库A与备库B,客户端操作A,B把更新A的语句同步过来本地执行,数据就一致了,建议将备库设置为只读模式。
因为同步更新线程是超级权限不影响,而且设置为只读(1、可以标识哪个为备库2、当需要从备库查询时避免误操作)
主备延迟
同一个事务,备库执行完时间与主库执行完时间之差。一个服务器开放N个链接给客户端来连接的,这样有会有大并发的更新操作, 但是从服务器的里面读取
binlog
的线程仅有一个,当某个SQL
在从服务器上执行的时间稍长 或者由于某个SQL
要进行锁表就会导致,主服务器的SQL
大量积压,未被同步到从服务器里。这就导致了主从不一致, 也就是主从延迟。
主从复制分了五个步骤进行:
-
步骤一:主库的更新事件(
update
、insert
、delete
)被写到binlog
-
步骤二:从库发起连接,连接到主库。
-
步骤三:此时主库创建一个
binlog dump thread
,把binlog
的内容发送到从库。 -
步骤四:从库启动之后,创建一个
I/O
线程,读取主库传过来的binlog
内容并写入到relay log
-
步骤五:还会创建一个
SQL
线程,从relay log
里面读取内容,从Exec_Master_Log_Pos
位置开始执行读取到的更新事件,将更新内容写入到slave
的db
-
一般情况,日志从主库发到备库造成的时间很短的,主要原因是备库接收完这个
binlog
执行这个事务造成的时间,所以,主备延迟最直接的表现是,备库消费中转日志(relay log)的速度,比主库生产binlog
的速度要慢。 -
主备延迟的来源;
- 主备库部署机器性能差异
- 只考虑主库压力,忽略备库压力,备库写压力大,占用了
cpu
资源,导致同步延迟- 解决办法:一主多从,分摊读压力;
- 解决办法:通过
binlog
输出到外部系统,比如Hadoop
这类系统,让外部系统提供统计类查询的能力;
- 大事务,大事务让主库执行很久,那么到备库也要执行很久,导致延迟很久,比如一次是删很多数据
主从同步延迟的解决办法
-
主服务器要负责更新操作,对安全性的要求比从服务器要高,所以有些设置参数可以修改,比如
sync_binlog
=1,innodb_flush_log_at_trx_commit
= 1 之类的设置等。 -
选择更好的硬件设备作为
slave
。 -
把一台从服务器当度作为备份使用, 而不提供查询, 那边他的负载下来了, 执行
relay log
里面的SQL
效率自然就高了。 -
增加从服务器喽,这个目的还是分散读的压力,从而降低服务器负载。
分库分表方案:
- 水平分库:以字段为依据,按照一定策略(
hash
、range
等),将一个库中的数据拆分到多个库中。 - 水平分表:以字段为依据,按照一定策略(
hash
、range
等),将一个表中的数据拆分到多个表中。 - 垂直分库:以表为依据,按照业务归属不同,将不同的表拆分到不同的库中。
- 垂直分表:以字段为依据,按照字段的活跃性,将表中字段拆到不同的表(主表和扩展表)中。
常用的分库分表中间件:
sharding-jdbc
Mycat
分库分表可能遇到的问题
-
事务问题:需要用分布式事务啦
-
跨节点
Join
的问题:解决这一问题可以分两次查询实现 -
跨节点的
count
,order by
,group by
以及聚合函数问题:分别在各个节点上得到结果后在应用程序端进行合并。 -
数据迁移,容量规划,扩容等问题
-
ID问题:数据库被切分后,不能再依赖数据库自身的主键生成机制啦,最简单可以考虑
UUID
-
跨分片的排序分页问题
主备切换策略(由于有主备延迟,导致有多种切换策略)
1、可靠性优先策略(实际保证这个)
-
切换流程:等到主备数据同步再将备库设置为读写,业务转到备库B
-
判断备库B的同步延迟时间(seconds_behind_master)小于某个值时,将主库A设置为只读(readonly=ture),此时系统会不可用主从都只读,然后继续等备库的同步延迟时间为0了,将B备库设置为读写(readonly=false),然后业务请求转到B
-
问题:当主库设置为只读时,此时旧的主备都只读,系统不可用,所以要求备库同步延迟尽量短时才开始切换
2、备库并行复制
若备库执行日志的速度一直慢于主库生成日志速度,延迟可能会达到小时级别,若主库持续高压力,备库可能始终追追不上主库节奏。采用备库并行复制解决。
模型:
-
1、coordinator负责读取中转日志和分发事务
-
2、各个workers负责真正执行
-
3、workers个数由slave_paralles_wokers决定,一般设置8-16(32核),备库还需要其他查询
coordinator分发规则(每个版本须遵守)
- 1、更新同一行的两个事务须分配到同一个worker
- 2、同一个事务不能拆分,需分配到同一个worker