1 myisam 和 innodb 引擎的区别
innodb 支持事务，外键，myisam 不支持
innodb 支持 mvcc ,myisam 不支持
innodb 支持表锁、行锁，myisam 仅支持表锁
innodb 必须有主键，myisam 不需要
2 mysql有几种锁
按锁粒度划分有三种：表锁、页锁、行锁
加锁机制：可分为 悲观锁和乐观锁
兼容性：意向所 和 排他锁
实现：记录锁、gap锁、next-key锁、插入意向锁
3 事务和锁
update、delete、insert 无论是否在事务，都加锁。保证数据的一致性
快照读：读取的是快照版本，也就是 mvcc 历史版本 readView 里的数据 ，普通的 SELECT 就是快照读
当前读：读取的是最新版本，UPDATE、DELETE、INSERT、SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE 是当前读，需要加锁
RC、RR、SERIALIZABLE 级别的隔离，当前读都会需要借助锁实现
RR 隔离级别需要先 select ... for update 加锁进行当前读操作，才能防止幻读
对于SERIALIZABLE隔离级别的事务来说，InnoDB规定使用加锁的方式来访问记录
4 什么情况会加锁
update、delete、insert 无论是否在事务，都加锁。保证数据的一致性
在事务里，for update、LOCK IN SHARE MODE 会分别加一个 排他锁和共享锁，直至事务结束
select 无论是否在事务，都不加锁
5 隔离级别，具体的实现原理是什么
读未提交
读已提交
可重复读
串行化
原理
事务就ACID四个特性
原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态
持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复
隔离性：通过锁以及MVCC，使事务相互隔离开
一致性：通过回滚、恢复，以及并发情况下的隔离性，从而实现一致性
6 redo log 、 undo log 、 binlog
redo log：
innodb 为了提高磁盘I/O读写性能，存在一个 buffer pool 的内存空间，数据页读入会缓存到 buffer pool，事务的提交则实时更新到 buffer pool，
undo log：
事务需要保证原子性，也是说事务中的操作要么全部完成，要么什么也不做。如果事务执行到一半，出错了怎么办-回滚。但是怎么回滚呢，靠 undo 日志。undo 日志就是我们执行sql的逆操作
binlog：
binlog指二进制日志，它记录了数据库上的所有改变，并以二进制的形式保存在磁盘中，它可以用来查看数据库的变更历史、数据库增量备份和恢复、MySQL的复制
一条更新语句，redo log 、 undo log 、 binlog的对应更新顺序流程是怎样的
数据的更新插入删除都是两阶段提交的，如果 redo 不是两阶段提交；
redo 先写，binlog 后写，会导致依赖 binlog 同步的从库数据缺失。
binlog 先写，redo log 后写，则会导致从库多出未提交的脏修改。主从库数据会不一致
7 double insert buffer 、redo log buffer 、change buffer
double insert buffer：数据页的更新需要两次写doubleWrite，其原因：重做日志是对页层面的物理操作与备份。如果磁盘页坏了的时候，那么用重做日志去重做是没有意义的，所以需要一个副本，在页损坏的时候，用副本页去还原原本的页，然后再进行重做日志
change buffer: Change Buffer与 Insert Buffer一样，适用对象还是非唯一的辅助索引
redo log buffer 类似 buffer pool，它是申请出来的一片连续内存，然后里面划分出了N多个空的redo log block。redo log 刷新到磁盘的时机如下
后台线程每隔1秒自动刷盘
关闭 mysql 服务
记录达到 log buffer 空间一半时刷盘
做 checkpoint 的时候
事务提交时会刷盘。数据库中 innodb_flush_log_at_trx_commit 参数就控制了在事务提交时，如何将 buffer 中的日志数据刷新到磁盘file中
参数值为0：提交事务也不进行刷盘操作
参数值为1：提交事务一次就刷盘一次（ 默认刷盘策略）
参数值为2：每次提交事务时，只会将buffer中的内容写入页面缓存中，不会在将页面缓存中的数据刷盘到file中
8 check point 是怎么确定的
Sharp CheckPoint ： 数据库关闭时，将所有脏页刷新到磁盘，默认的工作方式。但如果运行时，也使用Sharp方式，会对数据库的可用性造成很大影响
Fuzzy CheckPoint ：每次只刷新一部分脏页
Master ThreadCheckPoint master thread 会以一定的频率从缓冲池的脏页列表中刷新一定比例的页会磁盘，这个过程是异步的，不会阻塞查询
Flush_lru_list checkPoint
LRU列表要保证有差不多100个空闲页可用。
Page Cleaner线程中，会检查LRU列表中是否有足够的可用空间，没有的话，则移除LRU尾部的页，如果这些页中有脏页，则需要CheckPoint
Async/Sync Flush CheckPoint
重做日志不可用的情况下，需要强制将一些页刷回到磁盘，而此时脏页是从脏页列表中选取的。若将已经写入到重做日志的LSN记为redo_lsn
用来保证重做日志的循环使用。MySQL 5.6 以后，该操作移到了 Page Cleaner Thread 中，故不会阻塞用户的查询
Dirty Page too much CheckPoint
总的来说，是为了保证缓冲池中有足够可用的页。InnoDB—max_dirty_pages_pct 标识当缓冲池中脏页的数量占据 75%时，强制 CheckPoint，刷新部分脏页回磁盘
9 mysql 优化的切入点
explain分析sql语句，查看执行计划，优化sql
查看是否涉及多表和子查询，优化Sql结构，如去除冗余字段，避免返回不必要的数据
优化索引结构，看是否可以适当添加索引
数量大的表，可以考虑进行分库/分表
数据库主从分离，读写分离
查看mysql执行日志，分析是否有其他方面的问题
10 导致索引失效的几种情况
查询条件含 or、is null、is not null、in、exists、not in、!=、<> 都可能会导致索引失效
列是字符串类型，查询时条件没有使用引号扣起来
like 前模糊匹配
在列字段使用内置函数，或者做加减乘除运算
innodb 估算走全表扫描比走索引快，大概是 1/3 ?
连接查询，两个表用作关联的字段，其编码不一致
联合索引，不遵循最左前缀原则
11 varchar(20) 20 有什么意义 和 char(20) 的20 又有什么不同
vachar(20) 指明其字段长度最大是 20，其长度可变
char(20)，指明其字段长度是 20，长度不可变，多余部分空格填充
int(10),指明有效长度是10 ，多余部分前面用零填充
12 给字符串列建索引 需要注意什么
选择固定前缀子串作为索引，可以节约空间，缺点可能需要多次IO扫描
如果不是字符串全长度作为索引，在索引覆盖时仍需要回表查询
固定前缀的离散值必须够大，不然导致索引失效
倒序存储
字符串计算hash值，建立一个hash值的索引列。范围查询时索引失效
13 有哪几种索引
索引结构上可分为两种类型
聚簇索引
非聚簇索引，二级索引
一个表中只能拥有一个聚集索引，而非聚集索引一个表可以存在多
聚集索引，索引中键值的逻辑顺序决定了表中相应行的物理顺序；非聚集索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同
索引是通过二叉树的数据结构来描述的，我们可以这么理解聚簇索引：索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点，只不过有一个指针指向对应的数据块
innodb的索引语法分为五种
主键索引：数据列不允许重复，不允许为NULL，一个表只能有一个主键
唯一索引：数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引
普通索引：基本的索引类型，没有唯一性的限制，允许为NULL值
全文索引：是目前搜索引擎使用的一种关键技术，对文本的内容进行分词、搜索
组合索引：多列值组成一个索引，用于组合搜索，效率大于索引合并
innodb对索引的优化
索引下推：是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，
最左前缀原则：MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，
覆盖索引：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快
14 innodb 索引的底层实现
innodb 索引分为 B+树索引 和 hash索引。哈希索引是自适应索引，由innodb 是否建立，DBA无法干预
B+ 和 hash 索引的区别
B+树可以进行范围查询，Hash索引不能。
B+树支持联合索引的最左侧原则，Hash索引不支持。
B+树支持order by排序，Hash索引不支持。
Hash索引在等值查询上比B+树效率更高。
14.1 为啥使用B+作为数据索引，而不是二叉树，或者平衡树，B树
二叉树在特殊情况会退化为链表，查询操作是O(n)
平衡二叉树，树的高度太高。而mysql的数据持久化在磁盘，读取磁盘IO会非常多。而B+是矮胖型树，磁盘IO小很多
B树会在非节点存在键值和数据，innodb的页默认是16KB，其高度不比平衡二叉树低多少。而B+在非叶子节点只存键值，树高度很低
B+树的叶子节点会使用前后指针连起来，那B+树用来范围查找，排序，分组及去重都非常简单快速
15 now() 和 current_date() 有什么区别
now()精确到时分秒，current_date() 只精确日期
16 blob 和 text 的区别
Blob 用于存储二进制数据，而 Text 用于存储大字符串
Blob 值被视为二进制字符串（字节字符串）,它们没有字符集，并且排序和比较基于列值中的字节的数值。
text 值被视为非二进制字符串（字符字符串）。它们有一个字符集，并根据字符集的排序规则对值进行排序和比较
17 建立索引的原则是什么，是否越多越好，为什么
数据量少的不适合加索引
更新比较频繁的也不适合加索引
区分度低的字段不适合加索引（如性别）
创建索引和更新索引要耗费时间
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
以表中的数据进行增、删、改的时候，索引也要动态的维护
定义有外键的数据列一般要建立索引
在order by或者group by子句中，创建索引需要注意顺序
18 连接查询，innodb 有哪几种优化机制，了解吗
Index Nested-Loop Join （NLJ）select * from t1 straight_join t2 on (t1.a=t2.a);；t2 的字段 a 上有索引
从表 t1 中读入一行数据 R
从数据行 R 中，取出 a 字段到表 t2 里去查找
取出表 t2 中满足条件的行，跟 R 组成一行，作为结果集的一部分
重复执行步骤 1 到 3，直到表 t1 的末尾循环结束
在形式上，这个过程就跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引，所以我们称之为“Index Nested-Loop Join”，简称 NLJ
Simple Nested-Loop Join select * from t1 straight_join t2 on (t1.a=t2.b);；t2 的字段 b 上没有索引
因为 t2.b 没有索引，只能循环遍历比较。 如果 t1 有100行数据，t2 有 1000 行，则需要 100 * 1000 = 10 0000。
Block Nested-Loop Join
Simple Nested-Loop Join 太低效了，innodb 会优化成 Block Nested-Loop Join
把表 t1 的数据读入线程内存 join_buffer 中，由于我们这个语句中写的是 select *，因此是把整个表 t1 放入了内存
扫描表 t2，把表 t2 中的每一行取出来，跟 join_buffer 中的数据做对比，满足 join 条件的，作为结果集的一部分返回
Block Nested-Loop Join 算在内存操作，速度快很多
Multi-Range Read，这个优化的主要目的是尽量使用顺序读盘。
根据索引 a，定位到满足条件的记录，将 id 值放入 read_rnd_buffer 中
将 read_rnd_buffer 中的 id 进行递增排序
排序后的 id 数组，依次到主键 id 索引中查记录，并作为结果返回
Batched Key Access（BKA），(BKA)是对 NLJ 算法的优化
我们就把表 t1 的数据取出来一部分，先放到一个临时内存 join_buffer
然后按照 MRR 思想批量去 第二张表 t2 根据 ID 顺序查询数据
BNL 转 BKA
select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000; t2.b 没有索引一些情况下，我们可以直接在被驱动表上建索引(或者使用临时表)，这时就可以直接转成 BKA 算法了
19 buffer pool
预读：用于异步将磁盘的页读取到buffer pool中，预料这些页会马上被读取到
自适应哈希索引：提升热点等值查询sql的效率
double write buffer，保障在innodb崩溃的时候，也能保证数据的一致性
redo log buffer 保证 redo log 刷盘的效率
20 innodb 什么时候会用到临时表
sort buffer、内存临时表和 join buffer。这三个数据结构都是用来存放语句执行过程中的中间数据，以辅助 SQL 语句的执行的。
临时表的建立有以下几个场景
union 执行流程
group by 执行流程
尽量让 group by 过程用上表的索引，使用 explain 确认没有 Using temporary 和 Using filesort。此时优化成不使用临时表，不排序
如果 group by 需要统计的数据量不大，尽量只使用内存临时表；也可以通过适当调大 tmp_table_size 参数，来避免用到磁盘临时表
group by 和 order_id 字段不一致时。（ group by order_id order by id）
join 语句，order by 的列不在驱动表里
distinct + order by
from 里的子查询
21 mysql 的主从同步整个流程是怎样的
主库的更新事件(update、insert、delete)被写到binlog
从库发起连接，连接到主库
此时主库创建一个binlog dump thread，把binlog的内容发送到从库。
从库启动之后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log
还会创建一个SQL线程，从relay log里面读取内容，从 Exec_Master_Log_Pos 位置开始执行读取到的更新事件，
异步复制、半同步和全同步
主库执行完提交事务后，立刻异步执行将结果返给给客户端，并不关心从库是否收到并处理。
主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到 relay log 中才返回给客户端。
当主库提交事务之后，所有的从库节点必须收到、并且提交这些事务，然后主库线程才能继续做后续操作
22 mysql 读写分离时，存在主从延迟，有哪几种策略解决
网络延迟
当sql在进行锁表时，可能会导致大量 sql 积压，未同步到服务器，此时有主从延时
如果服务器硬件性能好，可以设置 sync_binlog = 1 , innodb_flush_log_at_trx_commit = 1
读写分离减少主库压力
如果读从库存在延迟
强制读主库
写入redis缓存中间件，从 redis 读取最新数据
延迟读
23 自适应哈希索引(AHI)了解过吗，innodb 在什么情况会用到 hash index
MySql数据库中的索引B+树的一般高度为3~4层，所以如果用索引B+树查找的话一般要进行3-4次的查找
Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升
自适应哈希索引是通过缓冲池的B+树页来构造的，因此建立的速度很快，不需要对整张表构建哈希索引
hash自适应索引会占用innodb buffer pool
自适应hash索引只适合搜索等值的查询，如 select * from table where index_col='xxx'，而对于其他查找类型，如范围查找，是不能使用的
哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
24 B+树，它的优点在哪
B+Tree 的根节点和枝节点中都不会存储数据，只有叶子节点才存储数据
B+Tree 的每个叶子节点增加了一个指向相邻叶子节点的指针，它的最后一个数据会指向下一个叶子节点的第一个数据，形成了一个有序链表的结构
扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子节点就可以了，不需要遍历整棵 B+Tree 拿到所有的数据）
B+Tree 的磁盘读写能力相对于 B Tree 来说更强，同数据量下磁盘I/0次数更少（根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字更多）
范围查询和排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链表）
25 最左前缀匹配，索引下推，索引覆盖
索引下推：是 MySQL 5.6 引入的， 可以在索引遍历过程中，对索引中包含的字段先做判断，
最左前缀原则：MySQl建立联合索引时，会遵循最左前缀匹配的原则，即最左优先。如果你建立一个（a,b,c）的联合索引，
覆盖索引：只需要在一棵索引树上就能获取 SQL 所需的所有列数据，无需回表，速度更快
26 数据库的乐观锁和悲观锁是什么，怎么实现
悲观锁:当前线程在修改，其他线程阻塞等待。select for update 、select in share lock
乐观锁:其他线程过来，先放过去修改。当前线程如果看到别的线程没修改过则修改成功，如果别的线程修改过则修改失败或者重试。
27 union all 和 union 有什么区别，哪个效率更好
Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
Union All：对两个结果集进行并集操作，包括重复行，不进行排序
UNION 的效率高于 UNION ALL
28 多表关联查询，有什么优化建议不
减少子查询，使用 join 代替
in和not in也不慎重使用的话，会导致全表扫描
使用exists而不是in是一个不错的选择
避免索引失效
29 超大表数据分页，怎么处理
select from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的。
如果使用的索引列 ID 是连续的,我们还可以 select * from table where id > 1000000 limit 10
业务上限制翻页次数
30 group by 产生的数据是不是有序的
group by xxx 默认会按 xxx 排序
如果不想排序可以加语句 order by null，选择不排序
31 group by 和 order by 怎么优化
group by 非执行时选取的索引列时，则额外使用临时表并默认排序，所以可以尽量 group by (index_x) 或者 group by xxx order by null
group by xxx 和 order by yyy 不同的列时，也会使用临时表，尽量使得 xxx == yyy
32 分表分库，怎么分
分表方案（水平分表，垂直分表，切分规则hash等）
垂直分表：把长度较大且访问不频繁的字段，拆分出来创建一个单独的扩展表 xxx_ext 进行存储
水平分表：分成多个结构相同的表，而每个表只占原表一部分数据，然后按不同的条件分散到多个数据库中
库内分表：库内分表虽然将表拆分，但子表都还是在同一个数据库实例中，只是解决了单一表数据量过大的问题，并没有将拆分后的表分布到不同机器的库上，还在竞争同一个物理机的CPU、内存、网络IO
分库分表：分库分表则是将切分出来的子表，分散到不同的数据库中，从而使得单个表的数据量变小，达到分布式的效果
切分规则
ID RANGE: 从0到10000一个表，10001到20000一个表
HASH取模: 例如电商的 order 和 order_ext 关联一个 user_id ，然后user_id hash取模，分配到不同的数据库上
地理区域：比如按照华东，华南，华北这样来区分业务，七牛云应该就是如此
时间：按照时间切分，就是将6个月前，甚至一年前的数据切出去放到另外的一张表，因为随着时间流逝，这些表的数据 被查询的概率变小，所以没必要和“热数据”放在一起
分库分表一些问题（事务问题？跨节点Join的问题）
分布式事务
跨节点Join的问题
分页、排序，分组的坑
全局唯一主键问题
分库分表中间件（Mycat，sharding-jdbc等）
33 MVCC了解过不，它的实现原理是怎样的的
mvcc 是一系列的 undo log 相连行成的版本视图。它的实现原理主要是依赖数据记录中
MVCC其好处是读不加锁，读写不冲突，并发性能好
详解可以看看这篇文章：数据库篇：mysql事务原理之MVCC视图+锁
34 limit 100000 很慢怎么办
如果id是连续的，筛选大于上次查询的最大记录ID，再往下limit
限制翻页页数
order by 索引列。再做限制
35 百万级别或以上的数据，你是如何删除的
我们想要删除百万数据的时候可以先删除索引
然后批量删除其中无用数据
删除完成后重新创建索引
posted @
2022-07-21 11:55
潜行前行
阅读(
296 )
评论()
编辑
收藏
举报
