上海做网站哪家公司好,绍兴seo计费管理,上海网站建设品牌,建设工程法律法规目录
1、数据库三范式
2、数据库事务的特性
3、MySQL数据库引擎
4、说说 InnoDB 与 MyISAM 的区别
5、索引是什么#xff1f;
6、索引数据结构
7、MySQL 索引类型有哪些#xff1f;
8、索引有什么优缺点#xff1f;
9、索引设计原则
9、使用索引应该注意些什…目录
1、数据库三范式
2、数据库事务的特性
3、MySQL数据库引擎
4、说说 InnoDB 与 MyISAM 的区别
5、索引是什么
6、索引数据结构
7、MySQL 索引类型有哪些
8、索引有什么优缺点
9、索引设计原则
9、使用索引应该注意些什么
10、什么是视图
11、什么是内联接、左外联接、右外联接
12、说一说drop、delete与truncate的区别
13、varchar 与 char 的区别
14、varchar(30) 中的 30 代表的涵义
15、int(11) 中的 11 代表什么涵义
16、主键和候选键有什么区别
17、主键与唯一索引有什么区别
18、为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢
19、说说count(pk/*/1/字段)的区别
20、MySQL 中一条查询 SQL 是如何执行的
21、查询需要使用事务吗
22、MySQL 中一条更新语句是怎么执行的
23、并发事务带来哪些问题?
24、事务隔离级别
25、说说事务隔离级别的实现原理
26、说说什么是 MVCC
27、说说 MVCC 的实现原理
28、说说快照读与当前读
29、说说 MySQL 数据库的锁
30、说说间隙锁、临键锁和记录锁
31、说说悲观锁和乐观锁
32、说说什么是锁升级
33、怎样尽量避免死锁的出现
34、说说RR与RC的区别和应用场景
35、说说 undolog、redolog 和 binlog
36、大表如何优化
37、分库分表之后id 主键如何处理
38、MySQL 优化手段有哪些
39、说说深度分页优化 1、数据库三范式
第一范式列不可再分。第二范式行可以唯一区分主键约束。第三范式表的非主属性不能依赖与其他表的非主属性、外键约束且三大范式是一级一级依赖的第二范式建立在第一范式上第三范式建立第一第二范式上。
2、数据库事务的特性
原子性(Atomic)组成一个事务的多个数据库操作是一个不可分割的原子单元只有所有操作都成功 整个事务才会提交。任何一个操作失败已经执行的任何操作都必须撤销让数据库返回初始状态。通过undo log日志来实现。一致性(Consistency)事务操作成功后数据库所处的状态和它的业务规则是一致的。即数据不会被破坏。 如A转账100元给B不管操作是否成功A和B的账户总额是不变的。由其它3个特性和业务代码的正确性来保证的。隔离性(Isolation)在并发数据操作时不同的事务拥有各自的数据空间它们的操作不会对彼此产生干扰。MVCC机制和各种锁机制来保证的。持久性(Durabiliy)一旦事务提交成功事务中的所有操作都必须持久化到数据库中。WALwrite-ahead logging机制来保证的。
3、MySQL数据库引擎
show engines; MYISAM全表锁拥有较高的执行速度不支持事务不支持外键并发性能差占用空间相对较小对事务完整性没有要求以select、insert为主的应用基本上可以使用这引擎。Innodb行级锁提供了具有提交、回滚和崩溃回复能力的事务安全支持自动增长列支持外键约束并发能力强占用空间是MYISAM的2.5倍处理效率相对会差一些。Memory全表锁存储在内容中速度快但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失默认使用HASH索引检索效率非常高但不适用于精确查找主要用于那些内容变化不频繁的代码表。MERGE是一组MYISAM表的组合。
4、说说 InnoDB 与 MyISAM 的区别
InnoDB支持事务MyISAM不支持对于InnoDB每一条SQL语言都默认封装成事务自动提 交这样会影响速度所以最好把多条SQL语言放在begin和commit之间组成一个事务。InnoDB支持外键而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败。InnoDB是聚集索引数据文件是和索引绑在一起的必须要有主键通过主键索引效率很高。但是辅助索引需要两次查询先查询到主键然后再通过主键查询到数据。因此主键不应该 过大因为主键太大其他索引也都会很大。而MyISAM是非聚集索引数据文件是分离的 索引保存的是数据文件的指针。主键索引和辅助索引是独立的。 InnoDB不保存表的具体行数执行select count(*) from table时需要全表扫描。而MyISAM用 一个变量保存了整个表的行数执行上述语句时只需要读出该变量即可速度很快。Innodb不支持全文索引而MyISAM支持全文索引查询效率上MyISAM要高。 在 MySQL 5.1 及之前的版本中 MyISAM 是默认的存储引擎而在 MySQL 5.5 版本以后默认使用 InnoDB 存储引擎。 MyISAM 索引文件和数据文件是分离的非聚集一张表生成三个文件 xxx.frm存储表结构 xxx.MYD存储表数据 xxx.MYI存储表索引 索引和数据索引行对应的所有列数据不是分离的聚集 一张表生成两个文件 xxx.frm存储表结构 xxx.ibd存储索引和数据 MySQL8.0之后将.frm文件整合到了.ibd文件
5、索引是什么
官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说数据库索引好比是一本书 前面的目录能加快数据库的查询速度。 一般来说索引本身也很大不可能全部存储在内存中因此索引往往是存储在磁盘上的文件中 的可能存储在单独的索引文件中也可能和数据一起存储在数据文件中。 我们通常所说的索引包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等没有特 别说明默认都是使用B树结构组织多路搜索树并不一定是二叉的的索引。
6、索引数据结构
二叉树 红黑树二叉平衡树Hash表 对索引的key进行一次hash计算就可以定位出数据存储的位置。 等值查询效率高IN不能排序不能进行范围查询hash冲突问题。 B-Tree 节点具有相同的深度页节点的指针为空。 所有索引元素不重复。 节点中的数据所有从左到右递增排列。 数据有序范围查询。 BTreeB-Tree变种 非页子节点不存储data只存储索引冗余可以放更多的索引。 页子节点包含所有的索引字段。 页子节点用指针双向指针连接提高区间访问的性能。 高版本MySQLMySQL8加载的时候一二层叶子数据冗余索引加载在内存中底层叶子数据存储在磁盘。 7、MySQL 索引类型有哪些
主键索引索引列中的值必须是唯一的不允许有空值。普通索引MySQL中基本索引类型没有什么限制允许在定义索引的列中插入重复值和空值。唯一索引索引列中的值必须是唯一的但是允许为空值。全文索引只能在文本类型CHAR、VARCHAR、TEXT类型字段上创建全文索引。字段长度比较大时如果创建普通索引在进行like模糊查询时效率比较低这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。空间索引MySQL在5.7之后的版本支持了空间索引而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。前缀索引在文本类型如CHAR、VARCHAR、TEXT类列上创建索引时可以指定索引列的长度但是数值类型不能指定。 其他按照索引列数量分类 1. 单列索引。 2. 组合索引组合索引的使用需要遵循最左前缀匹配原则最左匹配原则。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。
8、索引有什么优缺点
优点
提高数据的检索速度降低数据库IO成本。使用索引的意义就是通过缩小表中需要查询的记录数目从而加快搜索的速度。降低数据排序的成本降低CPU消耗。索引之所以查询快是原因事先已经将数据排好序。若该字段正好需要排序则正好降低了排序的成本。
缺点
占用存储空间。索引实际上也是一张表记录了主键与索引字段一般以索引文件的形式存储在磁盘上。降低更新表速度。表的数据发生了变化对应的索引也需要一起变更从而降低了更新速度。否则索引指向的物理数据可能不对这也是索引失效的原因之一。
9、索引设计原则
代码先行索引后上建议等主业务功能开发完毕把涉及到该表相关sql拿出来分析之后再建立索引。选择合适的列选择经常用于查询、连接、过滤和排序的列作为索引列这些列能够帮助加速查询操作。联合索引的选择创建联合索引应尽可能覆盖业务上的多个列单个表联合索引不建议超过3个。覆盖索引覆盖索引可以减少回表次数根据业务情况尽可能的使用覆盖索引。避免过长索引长字符串我们建议采用前缀索引前缀索引长度不建议超过20。选择适当的索引类型根据查询类型选择适当的类型如普通索引、唯一索引、全文索引等。考虑查询的顺序如果查询中涉及排序操作考虑在排序的列上创建索引以避免文件排序操作。避免冗余索引不要创建重复的索引冗余索引会增加维护开销。考虑分区度尽量不要挑选区分度不高的字段作为索引。定期维护索引定期检查和优化索引包括删除不再使用的索引、重建碎片化的索引等。
9、使用索引应该注意些什么 10、什么是视图
视图是一种虚拟的表具有和物理表相同的功能。可以对视图进行增改查操作试图通常是 有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易 相比多表查询。
11、什么是内联接、左外联接、右外联接
内联接Inner Join匹配2张表中相关联的记录。 左外联接Left Outer Join除了匹配2张表中相关联的记录外还会匹配左表中剩余的记 录右表中未匹配到的字段用NULL表示。 右外联接Right Outer Join除了匹配2张表中相关联的记录外还会匹配右表中剩余的记 录左表中未匹配到的字段用NULL表示。在判定左表和右表时要根据表名出现在Outer Join 的左右位置关系。
12、说一说drop、delete与truncate的区别
SQL中的drop、delete、truncate都表示删除但是三者有一些差别。
delete 和 truncate只删除表的数据不删除表的结构。速度一般来说drop truncate delete。delete 语句是dml这个操作会放到rollback segement中事务提交之后才生效;。如果有相应的trigger执行的时候将被触发。truncatedrop是ddl, 操作立即生效原数据不放到rollback segment中不能回滚。操作不触发trigger.。
13、varchar 与 char 的区别
CHAR 和 VARCHAR 类型在存储和检索方面有所不同 char 是一种固定长度的类型长度值范围是1 到255当 CHAR 值被存储时多余部分用空格填充到特定长度检索CHAR 值时需删除尾随空格。varchar 则是一种可变长度的类型。对效率要求高用 char对空间使用要求高用 varchar。
14、varchar(30) 中的 30 代表的涵义
varchar(30) 中 30 的涵义最多存放 30 个字符。varchar(30) 和 (130) 存储 hello 所占空间一 样但后者在排序时会消耗更多内存因为 ORDER BY col 采用 fifixed_length 计算 col 长度 memory 引擎也一样。
15、int(11) 中的 11 代表什么涵义
int(11) 中的 11不影响字段存储的范围只影响展示效果。
16、主键和候选键有什么区别
表格的每一行都由主键唯一标识,一个表只有一个主键。主键也是候选键。按照惯例候选键可以被指定为主键并且可以用于任何外 键引用。
17、主键与唯一索引有什么区别
主键一定会创建一个唯一索引但是有唯一索引的列不一定是主键。主键不允许为空值唯一索引列允许空值。一个表只能有一个主键但是可以有多个唯一索引。主键可以被其他表引用为外键唯一索引列不可以。主键是一种约束而唯一索引是一种索引是表的冗余数据结构两者有本质区别。
18、为什么 SELECT COUNT(*) FROM table 在 InnoDB 比 MyISAM 慢
对于 SELECT COUNT(*) FROM table 语句在没有 WHERE 条件的情况下InnoDB 比 MyISAM 可能会慢很多尤其在大表的情况下。因为InnoDB 是去实时统计结果会全表扫描MyISAM内部维持了一个计数器预存了结果所以直接返回即可。
19、说说count(pk/*/1/字段)的区别
count(pk)/count(*)/count(1)统计的是符合条件的数据库表的行数count(字段)统计的是符合条件的且列值不为null的数据库表的行数。
执行时间和执行计划 执行计划完全一样 我们可以看到这4个SQL的执行时间几乎一样 执行计划也一样说明这4个SQL执行效率差不多。所以我们可以简单认为他们执行效率和执行时间没有区别。但如果从细节上考虑还是有细微区别
字段有索引count(*)≈count(1)count(字段)count(pk) 说明字段有索引count(字段)统计走二级索引二级索引存储数据比主键索引少索引count(字段)count(pk)。
字段无索引count(*)≈count(1)count(pk)count(字段) 说明字段无索引count(字段)走不了索引count(pk)可以走主键索引所以count(pk)count(字段)。
count(1)和count(字段)执行过程类似不过count(1)不需要取出字段统计就用常量做统计count(字段)还需要取出字段并且count(字段)还需要过滤掉null值。所以理论上count(1)比count(字段)会快一点。
count(*)是SQL92定义的标准统计行的语法内部做过很多优化MySQL并不会把全部字段取出来而是专门做了优化不取值按行累加效率很高所以一般场景下推荐使用count(*)来统计行数。
为什么对于count(pk)MySQL最终选择辅助索引而不是主键索引因为二级索引相对于主键索引存储数据更少检索性能更高MySQL5.7内部做了优化。
20、MySQL 中一条查询 SQL 是如何执行的
select name from t_user where id1;
取得链接使用使用到 MySQL 中的连接器。查询缓存key 为 SQL 语句value 为查询结果如果查到就直接返回。不建议使用次缓存在 MySQL 8.0 版本已经将查询缓存删除也就是说 MySQL 8.0 版本后不存在此功能。分析器分为词法分析和语法分析。此阶段只是做一些 SQL 解析语法校验。所以一般语法错误在此阶段。优化器是在表里有多个索引的时候决定使用哪个索引或者一个语句中存在多表关联的时候join决定各个表的连接顺序。执行器通过分析器让 SQL 知道你要干啥通过优化器知道该怎么做于是开始执行语句。执行语句的时候还要判断是否具备此权限没有权限就直接返回提示没有权限的错误有权限则打开表根据表的引擎定义去使用这个引擎提供的接口获取这个表的第一行判断 id 是都等于 1。如果是直接返回如果不是继续调用引擎接口去下一行重复相同的判断直到取到这个表的最后一行最后返回。
21、查询需要使用事务吗
如果仅有一个查询方法使用事务也没有意义。所以不建议使用事务。RC隔离级别加不加事务没有什么影响。因为最终查询出来的都是最新提交的数据。RR隔离级别永远都是读取第一次查询时的数据库快照除非本事务内有更新操作。这种快照读最大的好处就是保证时间维度是一致的。如对并发要求不高的财务报表/OA报表统计查询。
总结对并发要求不高且对数据有时间维度一致性要求下的场景可以考虑RR事务隔离级别下对于多个查询方法开启事务。其它场景查询方法一般不建议使用事务。
22、MySQL 中一条更新语句是怎么执行的
update t_user set name XXX where id 1;
加载id1的记录所在的整页数据到缓存池。旧值写入undolog便于回滚。更新内存数据。写redo log到RedoBuff。redo log顺序写入磁盘准备提交事务prepare阶段。准备提交事务binlog写入磁盘。写入commit标记到redo log文件里提交事务完成该标记为了保证事务提交后redo log和binlog数据一致。系统空闲时随机写入磁盘以page为单位写入。
为什么MySQL不直接更新磁盘上的数据而设置这么一套复杂的机制来执行SQL
因为来一个请求就直接对磁盘文件进行随机读写然后更新磁盘文件里的数据性能可能相当差所以直接更新磁盘文件不能让数据库抗住高并发。
MySQL这套机制看起来复杂但他可以保证每个更新请求都是更新内存BufferPool然后顺序写日志文件同时还能保证各种异常情况下的数据一致性。
更新内存的性能是极高的然后顺序写磁盘上的日志文件的性能也是远高于随机读写磁盘文件的。
正是通过这套机制才能让MySQL在较高配置的机器上每秒可以抗下几千甚至上万的读写请求。
什么是redo log和binlog的2阶段提交
prepare 阶段binlog写入磁盘持久化。
这个阶段SQL已经执行并更新BufferPool内存数据。生成redo log到redolog buff并开始顺序写入磁盘。
commit 阶段写入commit标记到redo log文件里提交事务完成。
什么是WAL机制
先写redo log后刷新数据库文件的机制叫做WALWrite-Ahead Logging机制效率更高。
23、并发事务带来哪些问题?
脏读Dirty read当一个事务正在访问数据并且对数据进行了修改而这种修改还没有提交到数据库中这时另外一个事务也访问了这个数据然后使用了这个数据。因为这个数据是还没有提交的数据那么另外一个事务读到的这个数据是“脏数据”依据“脏数据”所做的操作可能是不正确的。
例如事务T1修改字段A为21还未提交事务T2读取到事务T1修改但为提交的值21。 丢失修改Lost to modify指在一个事务读取一个数据时另外一个事务也访问了该数据那么在第一个事务中修改了这个数据后第二个事务也修改了这个数据。第二个事务比第一个事务先提交第一个事务修改的结果会覆盖第二事务的结果因此称为丢失修改。
例如事务t1读取某表中的数据A20事务2也读取A20事务T1修改A19事务T2也修改A21最终结果A19事务T2的修改被丢失。 事实上这种情况在InnoDB存储引擎中并不会发生。因为我们对InnoDB存储引擎的数据库表进行更新操作的时候需要对行加锁因此当事务T1修改A还没有提交的时候事务T2对A的更新是会被阻塞的直到事务T1提交事务释放锁。
不可重复读Unrepeatableread:指在一个事务内多次读同一数据。在这个事务还没有结束时另一个事务也访问该数据。那么在第一个事务中的两次读数据之间由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况因此称为不可重复读。
例如事务T1第一次读取字段A为20事务T2修改了字段A为21事务T2第二次读取字段A为21。 幻读Phantom read幻读与不可重复读类似。它发生在一个事务读取了几行数据接着另一个事务插入了一些数据时。在随后的查询中第一个事务就会发现多了一些原本不存在的记录就好像发生了幻觉一样所以称为幻读。
例如事务T1查询表t查询结果有1条记录事务T2新增了一条记录事务T1再次查询的时候查询结果有2条记录。 不可重复读和幻读区别 不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改幻读的重点在于新增 或者删除比如多次读取一条记录发现记录增多或减少了。
24、事务隔离级别
隔离级别脏写脏读不可重复读幻读读未提交(Read Uncommitted)×√√√读已提交(Read CommittedRC)××√√可重复读(Repeatable ReadRR)×××√串行化(Serializable)××××
MySQL默认隔离级别可重复读Oracle默认隔离级别读已提交。
25、说说事务隔离级别的实现原理
MVCC机制锁实现。
MyISAM在执行select前会自动给涉及到的所有表加读锁在执行update、insert、delete前会给所有涉及到的表加写锁。
InnoDB在执行select时非串行化隔离级别不会加锁。但是update、insert、delete会加写锁。另外读锁会阻塞写但不会阻塞读。而写锁会把读和写都阻塞。
读未提交(Read Uncommitted)读取undo log日志版本链最新数据排他锁。
读已提交(Read CommittedRC)MVCC机制排他锁。
可重复读(Repeatable ReadRR)MVCC机制排他锁。
串行化(Serializable)共享锁排他锁。
26、说说什么是 MVCC
多版本并发控制MVCCMulti-Version Concurrency Control是一种用来解决读 - 写冲突的无 锁并发控制。也就是为事务分配单向增长的时间戳为每个修改保存一个版本。版本与事务时间戳 关联读操作只读该事务开始前的数据库的快照复制了一份数据。这样在读操作不用阻塞写操 作写操作不用阻塞读操作提高了数据库并发读写的性能避免了脏读和不可重复读但不能解决更新丢失问题。
MySQL在RR和RC隔离级别下都实现了MVCC机制。
27、说说 MVCC 的实现原理
MVCC 的目的就是多版本并发控制在数据库中的实现就是为了解决读写冲突它的实现原理主要是依赖行记录的 3 个隐式字段、undo 日志版本链和 Read View 根据可见性算法来实现的。
行记录的隐式字段
在MySQL数据库表的每行记录中除了我们自己定义的字段外还有几个隐式字段 db_trx_id6byte最近插入/修改的事务ID记录创建这条记录/最后一次修改该记录的事务ID。 db_roll_ptr7byte回滚指针指向这条记录的上一个版本。 db_row_id6byte隐含的自增ID隐藏主键。 一行数据完整字段 undo 日志版本链
undo log 分两种 insert undo log代表事务在insert是产生的undo log只在事务回滚时需要并且在事务提交后被立即丢弃。 update undo log事务在进行update时产生的undo log不仅在事务回滚时需要在快照读时也需要所以不能删除只有在快照读或者事务回滚不涉及该日志时对应的日志才会被purge线程同一清除。 例如现在要对t_account表id1的记录做如下修改
update t_account set balance blance 100 where id 1;
在事务A修改改行记录时数据库会先对该行记录加排他锁。把该行数据拷贝到undo log中。拷贝完成后修改该行记录balance100我们假设现有事务的事务id1此次修改的事务id2。提交事务释放锁。
undo log日志版本链 Read View
事务进行快照读操作的时候产生的读视图在该事务执行快照读的那一刻会生成数据库系统当前的一个快照记录并维护系统当前活跃事务的ID。 主要由三部分组成 trx_ids一个数值列表用户维护Read View生成时刻系统正活跃的事务ID列表。 up_limit_id是trx_ids列表中事务ID最小的ID。 low_limit_idRead View生成时刻系统尚未分配的下一个事务ID也就是目前已出现过的事务ID的最大值1。
可见性算法 1、有5个事务事务A、事务B、事务C、事务D、事务E正在执行。 2、A1和B2执行的sql与本测试无关无需关注目的仅是生成对应的事务ID。 3、C3更新balance100并提交事务记录undo log。 4、D4在RR事务隔离级别下执行了查询sql现阶段活跃的事务ID100、200、300最大事务ID所以Read View的构成trx_ids[100,200]、up_limit_id100、low_limit_id301。我们姑且写成 Read View[100,200]301。 5、同理E4在RC事务隔离级别下的Read View[100,200]301。 6、A5更新balance200记录undo log。 7、A6更新balance300并提交事务记录undo log。 8、D7在RR事务隔离级别下Read View为第一次查询时的Read View[100,200]301保持不变。 9、E7在RC事务隔离级别下每次查询都更新Read View此时Read View[200]301。 10、B8更新balance400记录undo log。 11、B9更新balance500并提交事务记录undo log。 12、D10在RR事务隔离级别下Read View为第一次查询时的Read View[100,200]301。 13、E11在RC事务隔离级别下每次查询都要更新Read View此时Read View[]301。 总结
RR事务隔离级别下同一个事务中的第一个快照读才会创Read VIew之后的快照读获取的都是同一个Read View。
RC事务隔离级别下每个快照读都会获取最新的Read View。
MVCC机制的实现就是通过Read View与undo log日志链版本对比机制使得不同的事务隔离级别根据日志链对比规则读取同一条数据在日志版本链上的不同版本数据。
28、说说快照读与当前读
快照读读取的是undo log日志版本链的可见版本该可见版本可能是历史版本不用加锁。
所谓undo log日志版本链是指一行数据被多个事务依次修改后MySQL会保留修改前的数据undo loghuigun日志并且用两个隐藏字段trx_id和roll_pointer把这些undo log日志串联起来形成一个历史记录版本链。
快照读是基于MVCC机制实现的使用普通的不加锁的select语句读取
select * from t where .....
当前读读取的是undo log日志版本链的最新版本并且当前读返回的记录都会加上锁保证其它事务不会再并发修改这条记录。
当前读的五种方式
-- 共享锁
select ... lock in share mode;
-- 排他锁
select ... for update;
insert into ...;
delete from ...;
update ...;
在MySQL中只有RC和RR这两种事务隔离级别才会使用快照读。
在RC中每次select都会重新生成一个快照读取的是最新的事务提交版本数据。
在RR中则会在事务开启后第一次select查询时生成快照之后的select都是使用第一创生成的快照只有在本事务中对数据更改才会更新快照。
29、说说 MySQL 数据库的锁
按照锁的粒度划分表级锁、页级锁和行级锁。
按照对数据库操作类型划分共享锁读锁和排他锁写锁。
按照锁的对象划分间隙锁、临键锁和记录锁。
按照加锁的方式划分乐观锁和悲观锁。
共享锁不堵塞多个用户可以同一时刻读取同一个资源相互之间没有影响。排它锁一个写操作阻塞其他的读锁和写锁这样可以只允许一个用户进行写入防止其他用户读取正在写入的资源。表锁系统开销最小会锁定整张表MyISAM 使用表锁。行锁容易出现死锁发生冲突概率低并发高InnoDB 支持行锁必须有索引才能实现否则会自动锁全表那么就不是行锁了。
30、说说间隙锁、临键锁和记录锁
记录锁Record Lock对表中的记录加锁简称行锁。 记录锁加锁5种方式
-- 共享锁
select ... lock in share mode;
-- 排他锁
select ... for update;
insert into ...;
delete from ...;
update ...;
间隙锁Gap Lock锁的是两个值之间的间隙间隙锁在可重复读隔离级别下才会生效。 例如表记录 那么间隙锁就有id为(3,10)、(10,20)、(20,正无穷)这三个区间在Session1下面执行sql
select * from account where id 18 for update;
则其它Session无法在(10,20)这个间隙范围内插入任何数据。如果执行如下sql
select * from account where id 25 for update;
则其它Session无法在(20,正无穷)这个范围内插入任何数据。也就是说只要在间隙范围内锁了一条存在的记录会锁住整个间隙范围不会锁边界这样能防止其它Session在这个间隙范围内插入数据就解决了可重复读隔离级别的幻读问题。
临键锁Next-key Locks是记录锁和间隙锁的组合指的是在加某条记录及这个记录前面间隙上的锁它会同时锁索引记录和间隙。范围是左开右闭。
31、说说悲观锁和乐观锁
悲观锁说的是数据库被外界包括本系统当前的其他事物以及来自外部系统的事务处理修改保持着保守态度因此在整个数据修改过程中将数据处于锁状态。悲观的实现往往是依靠数据库提供的锁机制也只有数据库层面提供的锁机制才能真正保证数据访问的排他性否则即使在本系统汇总实现了加锁机制也是没有办法保证系统不会修改数据。在悲观锁的情况下为了保证事务的隔离性就需要一致性锁定读。读取数据时给加锁其它事务无法修改这些数据。修改删除数据时也要加锁其它事务无法读取这些数据。 乐观锁相对悲观锁而言乐观锁机制采取了更加宽松的加锁机制。悲观锁大多数情况下依靠数据库的锁机制实现以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销特别是对长事务而言这样的开销往往无法承受。而乐观锁机制在一定程度上解决了这个问题。乐观锁大多是基于数据版本Version记录机制实现。何谓数据版本即为数据增加一个版本标识在基于数据库表的版本解决方案中一般是通过为数据库表增加一个“version”字段来实现。读取出数据时将此版本号一同读出之后更新时对此版本号加一。此时将提交数据的版本数据与数据库表对应记录的当前版本信息进行比对如果提交的数据版本号大于数据库表当前版本号则予以更新否则认为是过期数据。
32、说说什么是锁升级
MySQL 中有共享锁和排它锁也就是读锁和写锁。MySQL 行锁只能加在索引上如果操作不走索引就会升级为表锁。因为 InnoDB 的行锁是加在索引上的如果不走索引自然就没法使用行锁了原因是 InnoDB 是将 primary key index和相关的行数据共同放在 B 树的页节点。InnoDB 一定会有一个 primary keysecondary index 查找的时候也是通过找到对应的 primary再找对应的数据行。当非唯一索引上记录数超过一定数量时行锁也会升级为表锁。测试发现当非唯一索引相同的内容不少于整个表记录的二分之一时会升级为表锁。因为当非唯一索引相同的内容达到整个记录的二分之一时索引需要的性能比全文检索还要大查询语句优化时会选择不走索引造成索引失效行锁自然就会升级为表锁。
33、怎样尽量避免死锁的出现
设置获取锁的超时时间至少能保证最差情况下可以退出程序不至于一直等待导致死锁。设置按照同一顺序访问资源类似于串行执行。避免事务中的用户交叉。保持事务简短并在一个批处理中。使用低隔离级别。使用绑定链接。
34、说说RR与RC的区别和应用场景
并发度 RC事务隔离级别对数据的加锁仅支持锁粒度最低的记录锁不像RR还支持间隙锁和临键锁所以对并发的支持更好。
死锁 RR事务隔离级别为了解决幻读引入了间隙锁和临键锁但是也大大曾大了锁粒度增加了死锁的可能性。
快照读 也叫一致性读是基于MVCC机制的非阻塞读实现的读取的是undo log日志版本链的可见版本。在MySQL中只有RC和RR这两种隔离级别才会使用快照读。在RC中每次select都会重新生成一个快照读取的是最新的事务提交的版本数据。而RR则会在事务开启后第一次select查询的时候生成快照只有在本事务中对数据更改才会更新快照。
锁机制 在RC中只支持记录锁而RR除了支持记录锁还支持间隙锁和临键锁。
主从同步 MySQL的binlog支持三种日志格式STATEMENT、ROW和MIXED。 RR事务隔离级别三种都支持而RC仅支持ROW。
应用场景 RC事务隔离级别适用于并发性能要求较高的场景例如大部分在线业务系统。 RR事务隔离级别适用于并发性能要求不高的场景同时对数据的时间维度一致性要求较高的场景例如财务报表统计。
总结MySQL默认隔离级别是RR而大厂确改为RC的主要原因就是考虑到并发、性能、死锁等因素。
35、说说 undolog、redolog 和 binlog
undo log
也叫回滚日志。用于事务回滚。如果事务提交失败要回滚数据可以使用undo log日志里面的数据恢复到Buff Pool里面的缓存数据。
redo log
也叫重做日志。日志记录方式 物理日志。主要用于恢复磁盘ibd文件里面的数据。如果事务提交成功Buff Pool里面的数据还没来及写入磁盘此时系统宕机可以使用redo log日志恢复磁盘ibd文件里面的数据。
binlog
也叫归档日志。日志记录方式逻辑日志。主要用于恢复数据库磁盘里面的数据。多用于数据恢复、主从同步等场景。
物理日志与逻辑日志
逻辑日志可以简单理解为记录的就是sql语句。
物理日志MySQL数据最终是摆存在数据页中的物理日志记录的就是数据页变更。简单来说就是在哪个数据页做了什么修改
binlog和undo log日志都属于逻辑日志redo log属于物理日志。
36、大表如何优化
限定数据的范围务必禁止不带任何限制数据范围条件的查询语句。 比如我们当用户在查询订单历史的时候我们可以控制在一个月的范围内。读/写分离经典的数据库拆分方案主库负责写从库负责读。垂直分区根据数据库里面数据表的相关性进行拆分。 例如用户表中既有用户的登录信息又有用户的基本信息可以将用户表拆分成两个单独的表甚至放到单独的库做分库。简单来说垂直拆分是指数据表列的拆分把一张列比较多的表拆分为多张表。垂直拆分的优点可以使得列数据变小在查询时减少读取的Block数减少I/O次数。此外垂直分区可以简化表的结构易于维护。垂直拆分的缺点主键会出现冗余需要管理冗余列并会引起Join操作可以通过在应用层进行Join来解决。此外垂直分区会让事务变得更加复杂。水平分区保持数据表结构不变通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中达到了分布式的目的。 水平拆分可以支撑非常大的数据量。水平拆分是指数据表行的拆分表的行数超过200万行时就会变慢这时可以把一张的表的数据拆成多张表来存放。 例如我们可以将用户信息表拆分成多个用户信息表这样就可以避免单一表数据量过大对性能造成影响。
水平拆分可以支持非常大的数据量。需要注意的一点是分表仅仅是解决了单一表数据过大的问题但由于表的数据还是在同一台机器上其实对于提升MySQL并发能力没有什么意义所以水平拆分最好分库 。水平拆分能够 支持非常大的数据量存储应用端改造也少但 分片事务难以解决 跨节点Join性能较差逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片因为拆分会带来逻辑、部署、运维的各种复杂度 一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片尽量选择客户端分片架构这样可以减少一次和中间件的网络I/O。
数据库分片的两种常见方案
客户端代理 分片逻辑在应用端封装在jar包中通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。中间件代理 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。
详细内容可以参考MySQL大表优化方案。
37、分库分表之后id 主键如何处理
因为要是分成多个表之后每个表都是从 1 开始累加这样是不对的我们需要一个全局唯一的 id来支持。生成全局 id 有下面这几种方式
UUID不适合作为主键因为太长了并且无序不可读查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。数据库自增 id : 两台数据库分别设置不同步长生成不重复ID的策略来实现高可用。这种方式生成的 id 有序但是需要独立部署数据库实例成本高还会有性能瓶颈。利用 redis 生成 id : 性能比较好灵活方便不依赖于数据库。但是引入了新的组件造成系统更加复杂可用性降低编码更加复杂增加了系统成本。Twitter的snowflake算法https://github.com/twitter-archive/snowflake。美团的Leaf分布式ID生成系统 Leaf 是美团开源的分布式ID生成器能保证全局唯一性、趋势递增、单调递增、信息安全里面也提到了几种分布式方案的对比但也需要依赖关系数据库、Zookeeper等中间件。https://tech.meituan.com/2017/04/21/mt-leaf.html
38、MySQL 优化手段有哪些
表结构索引
选择合适的数据库引擎。选择正确的索引。适当的反范式设计表结构。
SQL语句优化
避免select * 查询尽可能使用索引扫描等等。只要一行数据时使用 limit 1。查询时如果已知会得到一条数据这种情况下加上limit 1会增加性能。因为mysql数据库引擎会在找到一条结果停止搜索而不是继续查询下一条是否符合标准直到所有记录查询完毕。not exists代替not in。not exists 用到了连接能够发挥已经建立好的索引的作用not in不能使用索引。not in是最慢的方式要同每条记录比较在数据量比较大的操作中不建议使用这种方式。操作符的优化尽量不采用不利于索引的操作符。如in、not in、is null、is not null、 等某个字段总要拿来搜索为其建立索引。减少使用or使用 union all 或者 union 来替代。
Mysql参数优化
设置Buffer_pool大小官方推荐总内存50%-70%左右。设置刷盘策略平衡好数据安全性和性能关系。 sync-binlog控制binlog刷入磁盘的频率default vaule1。 0禁止MySQL服务器将二进制日志同步到磁盘。相反MySQL服务器依赖于操作系统不时地将二进制日志刷新到磁盘就像处理其他文件一样。此设置提供了最佳性能但是在出现电源故障或操作系统崩溃时服务器可能提交了未同步到二进制日志的事务。 1允许在事务提交之前将二进制日志同步到磁盘。这是最安全的设置但是由于磁盘写操作的增加可能会对性能产生负面影响。在出现电源故障或操作系统崩溃时二进制日志中缺少的事务仅处于准备状态。这允许自动恢复例程回滚事务从而保证二进制日志中没有丢失任何事务。 N其中N是0或1之外的值:在收集了N个二进制日志提交组之后将二进制日志同步到磁盘。在出现电源故障或操作系统崩溃时服务器可能提交了未刷新到二进制日志的事务。由于磁盘写操作数量的增加该设置可能会对性能产生负面影响。值越大性能越好但是数据丢失的风险越大。 innodb-flush-log-at-trx-commit控制redo log刷新到磁盘default vaule1。 1默认设置为1是完全符合ACID要求的。日志在每次事务提交时被写入并刷新到磁盘。 0设置为0时每秒将日志写入并刷新到磁盘一次。没有刷新日志的事务可能在崩溃中丢失。 2如果设置为2则在每个事务提交之后写入日志并每秒刷新一次磁盘。没有刷新日志的事务可能在崩溃中丢失。
硬件系统设置
CPU核心数、磁盘的读写性能、网卡、内存大小等等。
39、说说深度分页优化
场景描述分页查询是我们业务场景中最常见的。但是如果数据量很大时页数也就随之很大这是如果查询最后几页就会出现深度分页问题。
select * from employee limit 99990,10;
表示从employee表中取出99990行开始的10行记录看似值查询了10行记录实际上这条sql是先读取100000条记录然后抛弃前99990条记录然后读取到后面10条想要的记录。因此要查询一张大表比较靠后的数据执行效率是非常低的。
优化方案
根据自增且连续的主键排序分页
select * from employee limit 99990,10;
select * from employee where id 99990 limit 10; 执行计划 很遗憾我们大多数场景下都不适合因为我们的业务中基本上都会存在删除业务。
根据非主键字段排序的分页
select * from employee order by name limit 99990,10; 根据MySQL的cost成本计算这条语句是不会走索引的。那么我们如何优化呢其关键就是让排序时返回的字段尽可能少所以可以让排序和分页操作先查出主键然后根据主键查询对应的记录SQL改写如下
select * from employee e innor join(select id from employee order by name limit 99990,10) ed on e.id ed.id; 原SQL使用的是filesort优化后SQL使用的是索引排序。 执行时间对比时间成本减少了一半以上。
