首页  思维导图  详情

Mysql

2020-08-26 10:24:03   1  举报





AI智能生成

差不多面试题mysql

mysql

Java

数据分析

作者其他创作

大纲/内容

三范式

1、确保每一列的原子性

2、有主键，非主键字段依赖主键，与主键直接相关

3、非主键字段不能传递依赖

log

bin log

server层的归档日志，记录了所有更改操作
逻辑日志，记录了语句所有的逻辑操作
文件写完则生成新文件继续追加写

redo log

属于InnoDB 引擎，是物理日志，记录的是某个数据页做什么修改

undo log

记录修改前的值，用来回滚

relay log

InnoDB和MyISAM的不同点

InnoDB支持事务和mvcc、外键

默认采用行级锁

索引实现

innodb数据文件本身就是主索引文件，而myisam是分开的

innodb是聚集索引

聚集索引就是按照每张表的主键构造一棵B+树，同时叶子节点中存放的即为整张表的行记录数据。

辅助索引：叶子页中保存主键值，通过这个主键值来回表查询到一条完整记录

myisam叶子节点的数据区域是存放的是地址

事务隔离级别

如何实现事务

通过预写日志实现的，redo和undo机制是实现数据库事务的基础

断电/数据库崩溃时通过redolog重新刷入数据保证了事务的持久性

undo log是在事务执行失败的时候撤销对数据库的操作，保证了原子性

隔离级别

Read Uncommitted

可以读未提交的脏数据

Read Committed

只能读已提交的数据

默认隔离级别，如Oracle、SQL Server

Repeatable Read

其他的事务操作提交，每次返回不同的结果集

mysql默认，RC不支持binlog

serializable

读写都加锁

越高的隔离级别，能解决的数据一致性问题越多，理论上性能损耗更大，可并发性越低。隔离级别依次为

SERIALIZABLE > RR > RC > Read-Uncommited

锁

全局锁

加锁

方法一： Flush tables with read lock (FTWRL)。

方法二：set global readonly=true

使用场景：做全库逻辑备份时

表锁

lock tables xxx read/write
unlock tables 主动释放锁，也可以在客户端断开的时候自动释放

使用场景

InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。MySQL这样设计并不是给你挖坑。它有自己的设计目的。

即便你在条件中使用了索引字段，MySQL会根据自身的执行计划，考虑是否使用索引(所以explain命令中会有possible_key 和 key)。如果MySQL认为全表扫描效率更高，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

第一种情况：全表更新。事务需要更新大部分或全部数据，且表又比较大。若使用行锁，会导致事务执行效率低，从而可能造成其他事务长时间锁等待和更多的锁冲突。

第二种情况：多表查询。事务涉及多个表，比较复杂的关联查询，很可能引起死锁，造成大量事务回滚。这种情况若能一次性锁定事务涉及的表，从而可以避免死锁、减少数据库因事务回滚带来的开销。

行锁

行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强

加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；
对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：

共享锁：select * from tableName where … + lock in share more

排他锁：select * from tableName where … + for update

行锁优化

尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。
尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。
尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。
尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发处理能力一般。只需了解一下。

间隙锁

innodb如何加锁

总结

1 InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。

2 InnoDB 自动给修改操作加锁，给查询操作不自动加锁

3 行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。

4 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。

5 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。

6 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。

主从复制

主库的更新事件写到binlog

主库创建一个binlog dump thread 将binlog发送到从库

从库创建一个I/O线程接收binlog并存到relaylog

从库创建一个sql线程将数据写道slave的db

复制方式

异步

成功写入binlog就返回客户端，无须等待传入给从库的过程
但是一旦主库宕机，可能丢失数据

半同步

确保写入relay log才会通知主库的等待线程
若等待超时，则自动转为异步，直到有一台从库成功接收binlog

索引

索引结构

Mysql选⽤B+树这种数据结构作为索引，可以提⾼查询索引时的磁盘IO效

率，并且可以提⾼范围查询的效率，并且B+树⾥的元素也是有序的。

hash索引：可以快速的精确查询（等于），但是不⽀持范围查询（大于小于）

二叉树：数据越多，则查询成本随着树高增加而增加

B树：要⽐完全平衡⼆叉树要“矮”，B树中的⼀个节点可以存储多个元素。

但是B+树中的⾮叶⼦节点会冗余⼀份在叶⼦节点中，并且叶⼦节点之间⽤指针相连

⼀个B+树的节点中到底存多少个元素？
考B+树中⼀个节点到底多⼤合适？

B+树中⼀个节点为⼀⻚或⻚的倍数最为合适。

因为如果⼀个节点的⼤⼩⼩于1⻚，那么读取这个节点的时候其实也会读出1⻚，造成资源的浪费。

顺序1/0不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）

聚集索引与非聚集索引

页

各个数据⻚可以组成⼀个双向链表

⽽每个数据⻚中的记录⼜可以组成⼀个单向链表

每个数据⻚都会为存储内容⽣成⼀个⻚⽬录，在通过主键查找某条记录的时候可以在⻚⽬录中使⽤⼆分法快速定位到对应的槽
然后再遍历该槽对应分组中的记录即可快速找到指定的记录
以其他列(⾮主键)作为搜索条件：只能从最⼩记录开始依次遍历单链表中的每条记录。

如果我们写 select * from user where username='xxx'

定位到记录所在的⻚

需要遍历双向链表，找到所在的⻚

从所在的⻚内中查找相应的记录

由于不是根据主键查询，只能遍历所在⻚的单链表了

回表

有个主键为ID的索引，和⼀个普通name字段的索引
执行select * from user where username='xxx'

简单说就是mysql内部需要经过两次查询

如何判断：执行计划只有在使用了索引，且Extra是Using where的情况下，才代表回表查询数据。

先查询name索引上的xxx ，找到对应的id=2，然后通过主键索引，找到id=2对应的值

覆盖索引

避免回表，如果只查询ID，其实在Name字段的索引上就已经有了，那就不需要回表了。

减少树的搜索次数，提升性能

最左匹配原则

联合索引只能⽤于查找key是否存在（相等），遇到范围查询 (>、<、between、like左匹配)等就不能进⼀步匹配了，后续退化为线性查找。
因此，列的排列顺序决定了可命中索引的列数。

如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到

注意事项

尽量选择区分度⾼的列作为索引，区分度的公式是 COUNT(DISTINCT col)/COUNT(*)。表示字段不

重复的⽐率，⽐率越⼤我们扫描的记录数就越少。

索引列不能参与计算，尽量保持列“⼲净”。⽐如， FROM_UNIXTIME(create_time)='2016-06-06'

就不能使⽤索引，原因很简单，B+树中存储的都是数据表中的字段值，但是进⾏检索时，需要把所

有元素都应⽤函数才能⽐较，显然这样的代价太⼤。所以语句要写成：

create_time=UNIX_TIMESTAMP('2016-06-06')。

尽可能的扩展索引，不要新建⽴索引。⽐如表中已经有了a的索引，现在要加（a,b）的索引，那么

只需要修改原来的索引即可。

在经常使用在WHERE子句，需要排序的列中的列上面创建索引，加快条件的判断速度。

将某一列设置为default null，where 是可以走索引，另外索引列是否设置 null 是不影响性能的。但是，还是不建议列上允许为空。

删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用

基本架构

连接器

经过tcp握手，验证身份即可连接客户端

此时处于空闲状态，show processlist，其中的Command列显示为Sleep
的这⼀⾏，就表示现在系统⾥⾯有⼀个空闲连接。

数据库的客户端太久没响应，连接器就会⾃动断开了，这个时间参数

是wait_timeout控制住的，默认时⻓为8⼩时。

使⽤⻓连接。

但是这⾥有个缺点，内存会飙得很快，我们知道MySQL在执⾏过程中临时使⽤的内存是管理在连接对象⾥⾯的。
只有在链接断开的时候才能得到释放，那如果⼀直使⽤⻓连接，那就会导致OOM（Out Of Memory），
会导致MySQL重启，在JVM⾥⾯就会导致频繁的Full GC。

解决方法
1、定期断开长连接
2、执行一个较大的查询后，执⾏mysql_reset_connection可以重新初始化连接资源

查询缓存(MySQL 8.0 版本后移除)

缓存很容易失效

query_cache_type设置成为DEMAND，这样SQL默认不适⽤缓存

分析器

词法分析

语法分析

优化器

mysql认为的最优的执行方案去执行，确认索引，对执行顺序优化

执行器

一条查询sql执行过程

一条更新sql执行过程

调优

排除缓存干扰

explain执行计划

字段含义

1.id

执行select子句或者操作表的顺序

select_type

查询的类型，区别于普通的查询、联合查询、子查询等

simple

简单的select查询，不包含子查询和union

primary

查询中包含任何复杂的子查询

subquery

在select或where列表中包含了子查询

derived

union

table

这一行的数据是关于哪张表的

type

　查询类型从最好到最差依次是:system>const>eq_ref>ref>range>index>All,一般情况下,得至少保证达到range级别,最好能达到ref

const:表示通过索引一次就找到了

eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描

ref:非唯一性索引扫描,返回匹配某个单独值的行,它可能会找到多个符合条件的行,所以他应该属于查找和扫描的混合体

possible_keys

显示可能应用在这张表中的索引

key

实际使用的索引,如果为null,则没有使用索引

key_len

表示索引中使用的字节数

ref

显示索引的哪一列被使用了

rows

估算出找到所需的记录所需要读取的行数

extra

重要的额外信息，特别注意损耗性能的两个情况，using filesort和using temporary。

Using filesort:说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的排序操作称为"文件排序"

Using temporary :使用了临时表保存中间结果,mysql在对查询结果排序时使用临时表,常见于order by和分组查询group by

使用覆盖索引，联合索引

索引下推

MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

唯一索引普通索引选择

查询过程

假设，执行查询的语句是 select id from T where k=5。
先是通过B+树从树根开始，按层遍历搜索到叶子节点确定数据页
数据页内部通过二分法来定位记录

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

基本没有性能差距

InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，以页为单位，将其整体读入内存。
在InnoDB中，每个数据页的大小默认是16KB。

更新过程

需要更新一个数据页时，在内存中则直接更新
不在内存中，就将更新操作缓存在changbuffer中，下次查询需要访问数据页，把数据页读入内存时，再执行changebuffer操作
减少磁盘IO

对于唯⼀索引来说，所有的更新操作都要先判断这个操作是否违反唯⼀性约束。
要判断表中是否存在这个数据，⽽这必须要将数据⻚读⼊内存才能判断，如果都已经读⼊到内存了，那
直接更新内存会更快，就没必要使⽤change buffer了。
因此，唯⼀索引的更新就不能使⽤change buffer，实际上也只有普通索引可以使⽤。

业务场景

⼀个业务的更新模式是写⼊之后⻢上会做查询不适合使用changebuffer

写多读少的业务，常⻅的就是账单类、⽇志类的系统

前缀索引

字段长度比较长（邮箱）的字段，如何优化

条件字段函数操作

函数计算不走索引

字符集不同可能不走索引

需要convert（）转换

flush

如何把握刷flush的时机

每个主机io能力不同，这就要⽤到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能⼒，这个值建议设置成磁盘的
IOPS，磁盘的IOPS可以通过fio这个⼯具来测试。