mysql 权限如何管理？
答MySQL的权限分为 表权限、列权限、过程权限。
| 权限分布 | 可以设置的权限 |
|---|---|
| 表权限 | select,insert,update,delete,create,deop,grant,index,alter,refernces |
| 列权限 | select,insert,update,refernces |
| 过程权限 | execute,alter routine,grant |
- 通过权限表来控制用户对数据库的访问，主要有下面四张表：user,db,table_priv,column_priv。mysql启动时，服务器会将这些表中权限信息读到内存，每个用户执行sql前，会先进行校验，依次查找是否具备执行该sql的权限。
- 查看权限的命令：show grants;
mysql中的角色
答
从 MySQL8.0开始才有角色。它是权限的集合，可以为用户赋予角色，即拥有了角色的权限。
sql执行过程
答
简单理解：
详细一点
mysql整体架构是怎样的？
答
- 连接层：用户身份校验和权限校验
- 服务层：主要完成大多数核心服务功能，如sql接口，并完成缓存的查询（8.0开始没有查缓存这步了），sql的分析和优化及部分内置函数的执行。
在该层，服务器会解析查询并创建相应的内部解析树，并完成相应的优化：如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作 - 引擎层：插件式的存储引擎将查询处理和其他的系统任务以及数据的存储提取分离。该层是真正的负责MySQL中数据的存储和提取，对物理服务器级别维护的底层数据执行操作
查看sql执行过程
答
- 设置参数profiling：表示记录sql查询过程
select @@profiling;
set profiling = 1 ;
- 执行sql
- show profiles; 查看执行记录
查看具体某一个 show profile for query 7;
oracle 中sql执行流程
答
Innodb 与 MyISAM 的区别
答
| 对比项 | MyISAM | Innodb |
|---|---|---|
| 外键 | N | Y |
| 事务 | N | Y |
| 行表锁 | 表锁 | 行锁 |
| 缓存 | 只缓存索引，不缓存真实数据 | 既缓存索引，也缓存真实数据，对内存要求较高。 |
| 关注点 | 性能：节省资源、消耗少、简单业务 | 事务：并发写、事务、更大资源 |
MyISAM
- 支持全文索引、压缩、空间函数等，但不支持事务、行锁和外键。
- 优点是访问速度快，对事务完整性没有要求或者以select、insert为主的应用
- 文件结构
.frm 存储表结构
.MYD 存储数据
.MYI 存储索引
Innodb
- 支持事务,使用多版本并发控制（MVCC）来获取高并发性。另外还提供插入缓存、二次写、自适应哈希索引、预读等高性能和高可用功能。
- 文件结构
.frm 存储表结构
.idb 存储索引和数据
其他的存储引擎？
答
- archive：用于存档，只有插入和查询两个功能。数据压缩能力非常强。
- CSV：存储的数据用，隔开，可以用excel直接打开，用于数据转存非常有用
- memory：表结构存放到磁盘，数据放到内存中，响应速度快，但是崩溃时数据会丢失。
Innodb存储引擎如何选择主键
答
- 如果显示指定，则以指定的列做索引；
- 如果没有指定，但存在非空且唯一索引，则按索引定义顺序的第一个在非空唯一索引做主键；
- 如果也没有，则自动创建一个6字节的隐式列作为主键
InnoDB 维护了一个全局的 dictsys.row_id，所以未定义主键的表都共享该row_id，每次插入一条数据，都把全局row_id当成主键id，然后全局row_id加 1。该全局row_id在代码实现上使用的是bigint unsigned类型，但实际上只给row_id留了6字节，这种设计就会存在一个问题：如果全局row_id一直涨，一直涨，直到2的48幂次-1时，这个时候再+1，row_id的低48位都为0，结果在插入新一行数据时，拿到的row_id就为0，存在主键冲突的可能性。
Innodb 逻辑存储结构
答
- 表空间：数据库由一个或多个表空间组成，从管理上可以划分为系统表空间、用户表空间、撤销表空间、临时表空间。
- 段：段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。比如索引段、数据段、回滚段等。
- 区：区是连续页组成的空间，任何情况下，区的大小都是1M，为了保证页的连续性，会一次申请4~5个区到内存。innodb默认页是16kb，所有一个区有64个页。
- 页：页是Innodb磁盘管理的基本单位。常见的有数据页、系统页、Undo页、事务数据页等
页的内部结构简单理解
答
行的格式
答
compact行记录格式
- 变长字段列表：按列的顺序倒着放，如列的长度小于255，用1个字节表示，若大于255，用两个字节表示
- null 标志位：也是倒叙，记录那些列是null,为了节省空间
- 记录头信息：记录该行是否被删除、下一条记录的相对位置等
- 具体的列，用户定义的列和两个隐藏列（事务ID列和回滚指针列，如何没有主键，则还有row_id列）
什么是buffer pool？buffer pool都存储哪些值？
答
为了弥补磁盘IO与CPU读写速度的巨大差异，innodb 对数据的操作首先实现内存中进行，然后再根据一定的策略写入磁盘（checkpoint），为了提高效率，innodb还有一个重要特性：预读。当我们读取一些数据时，大概率还会使用它周边的数据，预读就能提取加载，减少IO操作。
buffer pool中主要有数据页、索引页、插入缓存、锁信息、自适应哈希索引、数据字典等。
innodb 如何管理buffer pool？
答
innodb 将缓存池划分成 LRU list、free list、flush list.
- 采用优化后的LRU算法进行数据淘汰策略。
- 采用checkpoint机制将脏页刷到磁盘。
优化后的LRU算法
不是简单的将读取到的数据直接放到LRU 列表的首部，而是将数据先放到midpoint位置（3/8处），在一定时间（）后才将数据放到列表热端。这样可以避免某些非高频的操作（比如做全表扫描，但又只用一次的）将真正的热点数据挤出 LRU list。
- FREE list:空闲页，当需要从缓冲池中获取页时，首先检查free list中是否有空闲页，有就移除，然后放到 lru list.否则再根据LRU算法，移除LRU LIST尾部的数据。
- LRU list:用来管理已读取的页
- FLUSH list: 当页的数据被修改后就称为脏页。flush list中的页就是脏页。脏页既存在与lru list也存在 flush list中。lru list用来管理缓冲池中页的可用性， flush list用来将页刷新到磁盘，两者不影响。
执行更新操作，数据会立即同步到磁盘吗？
答
当执行更新操作时，先修改缓冲池中的数据并不是立即修改磁盘。而是通过checkpoint机制刷新到磁盘。
如果同步磁盘是宕机，数据库如何处理？（mysql如何保证数据一致性？）
答
如果在缓冲池将页写入到磁盘时发生了宕机，那数据就不能恢复了。为了避免这种情况，会采用提前写日志策略。当事务提交时，先写重做日志，在修改页。如果宕机，则可以使用重做日志恢复数据。
- 采用提取写日志策略
当有事务修改数据时，首先写入redo log buffer,在事务提交时（或者master thread每秒的操作）会将redo log buffer写到redo log file进行持久化 【注释①】 。之后如果宕机，就可以从redo log中恢复数据 - 两次写。将缓冲池中的脏页刷到磁盘时，并不是直接写入磁盘，而是通过memcpy将脏页复制到内存中的double write buffer,之后再通过double write buffer分两次，每次1M顺序写入共享表空间的物理磁盘，然后马上调用fsyn函数同步磁盘，避免缓冲写带来的问题。解决了部分写失效问题
- 重用redolog时，checkpoint机制会将缓冲池中的页刷新到当前redo log的位置。
注释①:
1 为什么redo log写入磁盘不需要两次写？
因为redolog写入的单位就是512字节，也就是磁盘IO的最小单位，所以无所谓数据损坏。
2 将redo log buffer写到redo log file进行持久化由参数innodb_flush_log_at_trc_commit控制，有三个值
0 : 每秒的频率将redo log buffer写入redo log file，如何mysql宕机，可能丢失这1秒的数据
1 :默认值，提交时将redo log buffer写入redo log file，并执行fsyn.真正的落盘
2 : 提交时将redo log buffer写入redo log file,但不执行并执行fsyn。如果操作系统崩溃，可能丢失数据。
什么是 checkpoint ?
答
由于重做日志可以能无限大，缓冲池也不可能缓存下所有的数据。所以需要将数据写入磁盘。
checkpoint解决以下几个问题：
- 缩短数据库的恢复时间
- 缓冲此不够用时，将脏页刷新到磁盘
- 重做日志不够用时，刷新脏页
innodb 的后台线程？
答
master thread
主要负责将缓冲池的数据异步刷新到磁盘，保证数据一致性，包含脏页的刷新、合并插入缓冲、undo页的回收等。
master thread拥有最高的线程优先级别，由多个循环组成：
-
主循环：包含两大部分操作，每秒和每10秒的操作
每秒操作
- 日志缓冲刷新到磁盘，及时事务还没有提交。（这也是为什么再大的事务提交的时间也很短）
- 合并插入缓冲
- 至多刷新100个缓冲池的脏页到磁盘
- 如果没用用户活动，则切到后台循环
每10秒操作
- 刷新100个脏页到磁盘
- 合并至多5个插入缓冲
- 将日志缓冲刷新到磁盘
- 删除无用的undo页
-
后台循环
- 删除无用的undo页
- 合并20个插入缓冲
- 跳转到主循环
- 不断刷新100个页直到符合条件（可能跳转到flush loop中完成，当然后面版本已经在单独的新城中执行）
-
刷新循环
-
暂停循环：当没有事情的时候，就将主线程挂起
IO thread
innodb使用了大量的AIO来处理写IO请求，这样可以极大提高数据库性能。IO thread的作用就是负责这些IO请求的回调。
purge thread
事务提交后，有些undo log就不需要了，因此需要purge thread来回收这些不需要的undo 页。一起时在master thread中完成，1.1开始放到单独的线程中处理，减轻master thread的压力。
page cleaner thread
将脏页刷新到磁盘。1.2开始出现，也是为了减轻master thread的压力。
innodb 的关键特性？
答
插入缓冲
插入缓冲并不是缓冲池的一部分，它与数据页一样，也是物理页的一部分。
过程
对于非聚集索引的插入或更新，不是直接插入到索引页中，而是先判断插入的非聚集索引是否在缓冲池中，如果在，则直接插入；如果不在，则先插入insert buffer中，再按一定的频率（master thread 每秒和每10秒的操作），将insert buffer合并。
使用插入缓冲的条件
索引是辅助索引且不是唯一（unique）索引
优点
提高非聚集索引的插入性能。会先与辅助索引进行merge,再写磁盘，这样一是减少了插入次数，而是减少了随机IO概率。
缺点
如果有大量的写操作都使用了insert buffer，然后数据库宕机，恢复时就可能耗时很长
两次写
自适应哈希索引
innodb存储引擎会自动根据访问的频率和模式来自动为某些热点页建立哈希索引
AIO
为了提高磁盘操作性能，采用AIO来处理磁盘操作，AIO还可以进行IO merge操作，将多个IO合并成一个IO，以此提高IOPS性能
刷新临近页
当刷新一个脏页时，innodb会检查该页所在区的所有页，如果有脏页，就一起刷新。提高刷盘效率
哪些情况适合建索引？
答
- 字段具有唯一性
- 频繁作为where查询条件的字段
- 经常被group by和order by的列
- distinct字段
- 离散性高的字段
- 联合索引优于单值索引
哪些情况会索引失效？
答
- 无法使用最左匹配
- 没用按索引顺序查询时，可能会use filesort
- 计算、函数、类型转换（特别注意自动转换的）
- 使用!= ,not in ,not null
- or前后存在非索引的列
- 使用的字符集不一致时
当一个字段需要创建索引，但又比较长时，怎么处理？
答
先计算字段的选择度
select count(destinct left(列名,索引长度)/count(*) from table;
# demo 取不同的长度查看离散性，该值越小越好
select count(destinct left(address,10)/count(*) from test;
select…for update 会锁表还是锁行？
答
select查询语句是不会加锁的，但是select …for update除了有查询的作用外，还会加锁呢，而且它是悲观锁。那么它加的是行锁还是表锁，这就要看是不是用了索引/主键。没用索引/主键的话就是表锁，否则就是是行锁。
mysql b+数有序为什么就能提高性能？
答
- 可以利用顺序IO，避免了随机IO
- 可以利用预读
与性能相关的一些参数
答
- Connections 连接musql的次数
- Uptime mysql上线时间
- Slow_queries 慢查询次数
- Com_* 增删改查的次数
show status like 'Com_%';
慢查询日志分析
show variables like 'slow_%'
找到慢查询日志后，使用mysqldumpslow 可以分析日志
# 取出使用最多的10条慢查询
mysqldumpslow -s c -t 10 /var/run/mysqld/mysqld-slow.log
# 取出查询时间最慢的3条慢查询
mysqldumpslow -s t -t 3 /var/run/mysqld/mysqld-slow.log
# 得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log
# 按照扫描行数最多的
mysqldumpslow -s r -t 10 -g 'left join' /var/run/mysqld/mysqld-slow.log
explain 说明
答
| 列明 | 描述 |
|---|---|
| id | id 大的先执行；id相同，顺序执行 |
| select_type | select 关键字对于的那个sql的查询类型 |
| type | 单表的访问方式 结果值从最好到最坏依次是:system > const > eq_ref > ref> fulltext> ref-or-null> index-merge >unique_subquery > index_subquery> range> index > ALL |
| possible_keys | 可能用的的索引 |
| key | 实际使用的索引长度，对于联合索引，该值越大越好 |
| ref | 当使用索引列等值匹配时，匹配对象的信息 |
| rows | 预计需要读取的条数，越小越好 |
| filtered | 某个表经过搜索条件过滤后剩余条数的百分比(主要在分析连表查询中使用，该值决定了被驱动表要执行的次数 rows * filtered) |
| extra | 额外信息 需要避免或减少use filesort 和子查询 |
explan 有四种输出格式
- 传统格式：就是平时使用的默认格式
- JSON 格式：这种数据更为详细，能看到read_cost（执行成本信息）
- 在使用了explan之后，接着执行 show warnings 可以看到优化后的sql
- trace 分析优化器执行计划
# 1 开启 trace 分析
set optimizer_trace ='enabled=on',end_markers_in_json =on,optimizer_trace_max_mem_size =100000;
# 2 执行sql语句
select * from xx where ...
# 3 select * from information_schema.OPTIMIZER_TRACE;
可以看到的主要信息：1 查询sql;2 优化后的sql；3 执行成本
mysql 条件下推
答
select * from a where key1 > 's' and key1 like '%a';
# a 表有二级索引 ind_key1(key)
以前改sql的执行过程为：
根据二级索引找到满足 key1 > ‘s’ 的索引，然后回表，查出数据，再筛选满足key1 like '%a’的数据返回
有了条件下推之后：
根据二级索引找到满足 key1 > ‘s’ 的索引，然后接着判断是否满足key1 like ‘%a’，满足再回表查询，不满足就跳过，这样减少了大量的随机IO。
关于 sys schema的相关使用
答
# 查询冗余索引
select * from sys.schema_redundant_indexes;
# 查询未使用的索引
select * from sys.schema_unused_indexes;
# 查询索引的使用情况
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted from sys.schema_index_statistics;
# 查询sql 执行效率
select db,exec_count,query from sys.statement_analysis order by exec_count desc ;
# 查询使用了临时表或者磁盘临时表的sql
select db,exec_count,tmp_tables ,tmp_disk_tables,query from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables>0
# 查询锁阻塞情况
select * from sys.innodb_lock_waits;
查询sys库，MySQL会消耗大量资源去收集相关信息，严重会导致业务请求被阻塞。在生产环境需慎用。
JOIN 实现原理
答
核心思想：小表驱动大表
select * from a join b on ...
- 简单嵌套循环：使用没有索引的字段关联时，从A取一条，遍历B表，筛选符合条件的数据
- 索引嵌套循环：被驱动表关联字段有索引，这样驱动表直接通过匹配条件与被驱动表的索引匹配，避免了与每一条记录匹配，减少被驱动表的匹配次数
- 块嵌套循环：为了减少IO操作次数，不再是从驱动表读一条就到被驱动表去遍历，而是取一块，引入join buffer.将驱动表join相关的数据列（也包含需要查询的）缓冲到join buffer中，在全表扫描被驱动表。减少被驱动表的访问次数。
- hash join:MySQL8开始，废弃块嵌套循环，使用hash join.优化器使用较小的表利用join key在内存中建立hash 表，然后扫描较大的表并进行探测hash表，找出与hash表匹配的行
- 对于小表，则直接放于内存中
- 对于大表，优化器将它分成若干个分区，不能存到内存的部分就写入磁盘的临时段，此时临时段较大时性能就会很高
- 它能很好的工作于没有索引的大表。但是只能应用于等值连接情况
为什么子查询效率不高
答
- 需要为内层查询简历临时表，查询完毕后还需要撤销这些表。消耗过多的CPU和IO资源
- 子查询的结果存在临时表中，无论是内存临时表还是磁盘临时表都不存在索引
- 子查询的结果集越大，对查询性能的影响就越大
排序优化
答
首先，如果排序字段有索引，则索引本身就有序，效率自然高，否则就需要在内存中排序，占用CPU较多，数量大时还需要写临时文件，效率更低
filesort的算法
- 双路排序：需要两次磁盘扫描，并且是随机IO。读取行指针和order by列，然后排序，排好后在取磁盘获取对应的数据输出。
- 单路排序：只需一次扫描，而且是顺序IO，但需要更多的内存空间。就是一次读取所有的列，然后再排序。但是当sort_buffer容量小或者数据过量时，则会造成多次IO，更慢。
group by的使用
答
- 先排序再分组,所有最好能不排序就不排
- where效率高于having
- 数量不要超过1000条，否则sql会很慢
exists和in的区别
答
当参与过滤的记录数 驱动表>被驱动表 时，用IN；反之用exists
select(1),select(*),select(key)的区别
答
select(1),select(*)没有区别；select(key），当key是二级索引时，则会比较快
如何设计有序的ID
答
可以使用时间戳+业务编号的方式+机器码 之类的
数据库三范式
答
- 第一范式：确保每列的原子性，即每一列都是不可在分割，不能是集合、数组等
- 第二范式：每列都和主键完全依赖，尤其是复合主键的情况下，非主键列不应该只依赖部分主键
- 第三范式：每一列都和主键直接相关，而不是间接相关
范式的优点：有利于减少数据冗余，大多情况下应该满足到第三范式
范式的缺点：降低查询效率
常用的mysql优化相关的参数
答
- innodb_buffer_pool_size:表示表和索引的最大缓冲。值越大查询速度越快，但是值太大会影响操作系统性能
- key_buffer_size:表示索引缓冲区的大小。线程共享。适当增加可以提高索引处理效率，但不是越大越好，该值太大会导致操作系统频繁换页而性能下降。4G内存时，可设为256M或384M
- table_cache：表示同时打开表的个数。该值越大，能打开的表个数越多
事务？
答
ACID
原子性、一致性、隔离性、持久性
隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交(read uncommited) | 有 | 有 | 有 |
| 读已提交(read commited) | 无 | 有 | 有 |
| 可重复读 (repeatable read) | 无 | 无 | 有 |
| 序列化 (serialiable) | 无 | 无 | 无 |
mysql 的事务实现机制
答
实现原理
- 事务的隔离性由锁机制保证
- 事务的持久性由redo log保证
- 事务的一致性和原子性由undo log保证
mysql 默认隔离级别
repeatable read,并且也解决了幻读问题（通过mvcc实现）
| A事务 | B事务 |
|---|---|
| 开启事务a | 开始事务B |
| 查询count(*) from test where id =3; // 结果=0 | |
| B 插入一条记录 | |
| B提交事务 | |
| A再次查询count(*) from test where id =3; // 结果依然为=0（这种幻读，mysql是可以解决的） | |
| 但是如果A 执行插入操作：inert into test values(3) ，这时就会报错：主键冲突 | |
| 如果要彻底解决幻读，除了使用序列化之外，还可以使用select … for update |
undo并不是redo的逆过程
undo 和 redo都可视为一种恢复操作，区别是：
- redo log: 记录的是物理级别上页修改的操作，比如页号xx,偏移量yy,写入了zz数据。主要是保证数据的可靠性
- undo log: 记录的是逻辑操作日志，比如当insert一条数据，那么undo log就记录一条delete操作。主要用于事务回滚和一致性非锁定读，undo log也是要写入redo log中的
为什么需要redo 日志
答
背景
- 缓冲此可以消除CPU与磁盘直接的鸿沟，checkpoint 也可以保证数据最终落盘。但是，checkpoint并不是实时触发的，而是master thread每个一段时间去处理。如果在这中间发生宕机，那么数据就会丢失，无法恢复。
- 事务的持久性来说，对于已经提交的事务，无论发生什么，已经做的更改不应该丢失。
如何保证事务的持久性？
实现一：事务提交之前，把该事务所修改的页全部刷到磁盘。但带来的问题：
- 修改量与刷新磁盘工作量严重不成比例
有时候只是改了某条记录的一个字段，但是innodb是以页为单位进行磁盘IO的 - 随机IO慢
一个事务可能包含很多语句，可能修改许多页，这些页并不能保证是连续的，需要进行很多随机IO
实现二：我们只需要把修改了哪些值记录一下即可（比如页号xx,偏移量100处的字节值1改成了2），当数据库宕机恢复时，读取这些修改记录进行恢复即可。
innodb的实现
innodb采用WAL（write-ahead-logging）技术，先写日志，再写磁盘，只有日志写入成功，才算事务提交成功，这个日志就是redo log
redo log的特点
- redo log降低了磁盘刷新频率
- redo log占用空间非常小，刷新快
- redo log是顺序写入的（顺序IO效率更高）
- 事务执行过程中，redo log不断记录。并不是提交时才记录，所以大事务提交速度依然很快
为什么需要undo 日志
答
undo log 的作用
- 回滚数据
undo回滚并不是将数据库物理的恢复到原来的样子，而只是逻辑的恢复，虽然所有的修改都被逻辑的取消了，但是数据结构和页本身在回滚之后可能大不相同。新加的页并不会删除。 - MVCC
当用户读取一行记录时，若该记录被其他事务占用，当前事务可以通过undo读取之前的行版本信息，以此实现非锁定读。
那如何处理脏读、不可重复读、幻读问题呢？
- 读写都加锁。这样所有读写操作都需要排队执行，性能低
- MVCC。所谓MVCC就是生成一个ReadView,通过ReadView从undo log 历史记录版本中找到符合条件的记录版本。查询语句只能读到在生成ReadView之前已提交事务所做的更改，而写操作是针对最新版本的记录。
innodb 的锁
答
- 共享锁（S锁）：共享锁可以和其他锁（共享锁、意向共享锁、意向排它锁）共存，不能与X锁共存
- 排它锁（X锁）：独占锁，都不共存
- 意向共享锁（IS）：
- 意向排它锁（IX）
意向锁的作用
- 为了协调行锁和表锁的关系，支持多粒度锁并存
- 意向锁是表锁但是与行锁不冲突
- 表明某个事物正在某些行持有了锁或者准备持有
- 数据库会自动为表加意向锁
比如当为某条记录加上X锁时，数据库自动为表加上IX锁，这样其他事务开需要对表加X锁时（比如修改表结构等），不必检测所有记录才知道是否有锁，而直接就能知道该表中有记录已被其他事务上锁了，从而提高性能。
auto_increment 自增的模式
答
- innodb_autoinc_lock_mode = 0（传统锁定模式）
每当执行insert的时候，都会得到一个表级锁（AUTO-INC），使得生成的字段顺序，且在binlog重放时，可以保证master与slave中的数据是相同的。但是并发能力差 - innodb_autoinc_lock_mode = 1（连续锁定模式）
8.0之前默认的。这个模式下，大部分插入也是使用AUTO-INC，但是对于批量插入，且知道具体个数的，则通过mutex(轻量锁)的控制下获取所需数量的值避免AUTO-INC锁，它只在分配过程持有锁，不需要等待sql执行完。 - innodb_autoinc_lock_mode = 2（交错锁定模式）
8.0开始，默认使用交错锁。这种模式下不会使用AUTO-INC锁。自动递增值保证在所有并发执行的insert中是唯一且单调递增的，但是由于多个insert可以同时生成数字，所以可能不连续。
行锁（记录锁）
答
- 当事务对一条记录加S锁时，其他事务依然可以再加S锁，但不能加X锁
- 当事务对一条记录加X锁时，其他事务是能等待
间隙锁（Gap locks）
答
Gap lock是为了防止插入幻读记录而提出的。
| id | 3 | 8 |
|---|---|---|
| name | 张三 | 李四 |
| 当为id=8的记录加LOCK_GAP锁时，则会锁定(3,8)区间，此时另一个事务想插入id=4的记录就会阻塞。 |
临键锁（Next-KEY LOCKS）
答
有时候我们既要所住某条记录，又要阻止其他事务在该记录之前的间隙插入新纪录
| id | 3 | 8 |
|---|---|---|
| name | 张三 | 李四 |
| 当为id=8的记录加Next-KEY LOCKS时，则会锁定(3,8]区间，此时另一个事务想插入id=4的记录就会阻塞。或者操作id=8的记录也会阻塞。 |
插入意向锁
答
插入意向锁是在插入一条记录前，由insert操作产生的一种间隙锁。该锁表示插入意向，当多个事务在同一区间（gap）插入不同位置的多条数据时，事务不需要等待。插入意向锁可以分成两个部分：
- 插入意向锁是一种特殊的间隙锁，间隙锁可以锁定开区间的部分记录
- 插入意向锁之间互不排斥，只有记录本身（主键、唯一索引）不冲突
innodb 如何处理死锁问题？
答1. 等待超时机制（innodb_lock_wait_timeout=50s） 当事务等待超时就会回滚。缺点是时间长，如果设置短了又可能误伤普通的事务 2. 死锁检测机制 数据库保存锁的信息链和事务等待链连个部分。然后构建一个以事务为顶点,锁为边的有向图，如果有向图存在环，则存在死锁。
当阻塞线程越多，死锁检测越耗时。处理方式
- 控制并发量
- 业务上控制
与锁相关的参数
答
show status like '%row_lock%';
| variable_name | value | 说明 |
|---|---|---|
| Innodb_row_lock_current_waits | 0 | 当前等待的锁个数 |
| Innodb_row_lock_time | 61863984 | 锁总等待的时间 |
| Innodb_row_lock_time_avg | 0 | 锁等待的平均时间 |
| Innodb_row_lock_time_max | 50973 | 启动到现在锁等待的最长一次的时间 |
| Innodb_row_lock_waits | 126988512 | 启动到现在锁等待的总次数 |
- 8.0 之前可以根据information_schema中INNODB_TRX、INNODB_LOCKS 和INNODB_LOCK_WAITS查看所相关信息
- 8.0 开始删除了INNODB_LOCKS，添加了performance_schema.data_locks,INNODB_LOCK_WAITS也被performance_schema.data_lock_waits取代
多版本并发控制（MVCC）
答MVCC多版本并发控制，指当一个事务正在操作某些行时，其他事务可以看到被更新之前的值，不必等待该事务释放锁。 MVCC是通过数据行的多个版本管理来实现数据库的并发控制，保证了事务隔离级别下的一致性。
实现原理
MVCC通过 read view 和undo log 历史快照实现，具体流程如下：
- 首先获取事务自己的版本号，即事务ID；
- 获取ReadView(读已提交：每次selcet都会生成一个ReadView；可重复读：只在第一次select时生成一个ReadView)
- 查询得到的数据，然后与ReadView中的事务ID进行比较
- 如果不符合ReadView的规则，就需要从历史快照中获取
- 最后返回符合规则的数据
由于读已提交，每次selcet都会生成一个ReadView，所以两次读之间有其他事务修改提交时，第二次读产生新的ReadView，就会读到其他事务提交的数据，从而产生不可重复读和幻读；而可重复读只在第一次select生成ReadView，即使中间有其他事务提交，ReadView没有变化，所以依然只能读到以前的数据，进而避免了不可重复读和幻读。
binlog
答
binlog的主要应用场景
- 用于数据恢复。如果MySQL数据库意外停止，可以通过binlog来查看用户执行了哪些操作、对数据库文件做了哪些修改，然后利用binlog恢复数据
- 数据备份。由于日志的延续性和时效性，master把他的binlog传给slaves来达到master-slave数据一致的目的
如何查看binlog
一个update语句包含如下事件：
- query事件 负责开始一个事物（BEGIN）
- Table_map事件 负责映射需要的表
- update_rows 负责写入数据
- XID事件 负责结束事务
show binlog events in "master-bin.000059" from 439 limit 5,2;
- in : 指定查询的binlog文件名
- from pos :起始位置
- limit 偏移量
- row_count :总条数
binlog 的格式
答
statement
每一条修改的sql都会记录到binlog中，如果sql使用函数，则可能主从不一致
优点：日志少，不用记录每一行的变化，节约IO，提高性能
row
仅保存哪条记录被修改，不存在主从不一致。
缺点：日志量大
mixed
结合statement 和 row
如何使用binlog 恢复数据
答
- 找到binlog日志文件
show variables like '%bin%'
-
flush logs; 刷新日志，这样我们后面执行的操作就会记录到新的日志里，方面我们做数据恢复。
-
可以使用按时间恢复数据或者按事件起始到结束的方式恢复
-
如果选择按事件起始到结束的方式恢复，
show binlog events in 'binlog 文件名称'; -
根据之前的操作，找到对应sql的起始和结束位置
-
执行命令
mysqlbinlog --start-position=3933 --stop-position=4219 /zcloud/db/werew/logs//mysql-bin.000002 | mysql -h 192.168.100.18 -u zcloud_platform -P3402 -p hntest -
如果失败，ERROR 1782 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to ANONYMOUS when @@GLOBAL.GTID_MODE = ON.则执行下面语句，再重试。
set global gtid_mode='on_permissive'; set global gtid_mode='off_permissive';
binlog 与 redolog 对比
答
- redolog 是物理日志，记录内容是在某个数据也上做了什么修改，属于innodb存储引擎的
- binlog 是逻辑日志，记录的是语句的原始逻辑，属于mysql server层。
- 虽然都属于持久化日志，但是侧重点不同：redolog 让innodb存储引擎用于崩溃恢复能力；binlog保证MySQL集群架构的数据一致性
数据库优化思路和效果对比
答
relay log
答
relay log只在从服务器上存在。从服务为了与主服务器保持一致，要成主服务器读取二进制日志内容，存到本地日志文件中，这个本地日志文件就是relay log。
如何查看
与binlog一样也是二进制文件，使用mysqlbinlog工具进行查看。
日志恢复小细节
如果从服务器宕机，需要在其他系统上或者重装系统后再恢复，则可能会导致数据恢复失败，因为relay log保存了主机名，所以需要把主机名改为原来的名称。
主从复制
答
原理
-
三个线程
- binlog 转储线程：是主库线程。当从库线程链接的时候，主库可以将binlog发送给从库，当主库读取事件时，会在binlog上加锁，读取完成后，在释放锁。
- 从库IO线程：链接到主库，向主库发送请求更新binlog。这时从库的IO线程就可以读取主库的binlog转储线程发送的binlog更新部分，复制到relay log中
- 从库SQL线程：负责执行relay log中的事件，保持主从同步。
-
具体步骤
- master 将写操作记录到binlog。这些记录叫做二进制日志事件
- slave将master的二进制事件拷贝到它的relay log中
- slave重做relay log。
作用
- 读写分离。通过主从复制来同步数据，然后读写分离提高数据库并发能力
- 数据备份。是一种热备份
- 数据库高可用。当主库宕机时，可以切换到备库，保证服务正常运行。
解决复制是数据不一致的三种方式
答
- 异步复制：指事务提交后，不需要等待从库返回任何结果，就直接返回。
- 优点：不影响主库写的效率，并发性更好
- 缺点：①如果主库宕机，而binlog还没有同步到从库，此时主从不一致。②如果这时候选出了新的主库，这主库缺少原来主服务器中已提交的事务
- 半同步复制：事务提交后，需要等待至少一个从库收到binlog，并且写入relay log才返回。
- 优点：主从一致性得到一定的提高
- 缺点：增加了网络连接的延迟，降低了主库写效率。依然有部分从库不一致情况
- 组复制（MGR）:①是基于paxos协议的状态机复制。②首先将多个节点共同组成一个复制组，在执行读写事务时，需要通过一致性协议层的同意，也就是读写事务想要提交，必须经过超半数节点的同意，才可以提交。③在一个复制组内有多个节点组成，他们各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据一致性
使用Paxos协议的还有如zookeeper
数据备份与恢复
答
- 物理备份：直接备份数据文件，复制到其他目录。速度快，但占用空间大。一般使用xtrabackup工具
- 逻辑备份：利用工具进行sql导出。速度慢，但占用空间小，更灵活，一般使用mysqldump
表的导入与导出
答
- 使用select … into outfile
1 需要先确认导出路径 show global variables like '%secure%'; 如果是空的：表示可以导出到任意路径 如果是具体路径：表示只能放到该路径下 如果是null:表示禁止该操作 - 使用mysqldump导出
mysqldump -uroot -p -T "/test/sql/" dbname filename - 使用mysql命令导出
mysql -uroot -p --execute="select ... from ..." dbname> "/test/t.sql" - 使用工具：navicat\idea等
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
