购物网站建设的原理,做一个网站的建设流程,手工制作大全 简单易学,哪里ui培训班好Mysql 1. MySQL的索引原理是什么?什么是索引#xff1f;以及索引的优缺点#xff1f;2. 解释一下B树和B树的区别及各自定义?3. MyISAM索引和Innodb索引的区别#xff1f;4. 什么是聚簇索引#xff1f;辅助索引#xff1f;5.非聚簇索引一定会回表查询么#xff1f;6. 什… Mysql 1. MySQL的索引原理是什么?什么是索引以及索引的优缺点2. 解释一下B树和B树的区别及各自定义?3. MyISAM索引和Innodb索引的区别4. 什么是聚簇索引辅助索引5.非聚簇索引一定会回表查询么6. 什么是回表操作7. 什么是索引覆盖?8.什么是短索引9.索引的数据结构有哪几种10. MySQL的索引的创建原则MySQL不适合建立索引的情况?11. 使用索引一定能提高查询性能么12. MySQL的主键索引联合索引?唯一索引?13. MySQL的索引下推是啥意思14. MySQL索引的最左前缀原则是什么15. MySQL的索引的rows很大会引起什么问题16. MySQL的解决慢查询的方法?explain方法怎么用的?17. MySQL的Sql优化方法有什么18. MySQL的left join、right join、inner join、join的区别19. MySQL的小表驱动大表是什么意思20. binlog和redo log的具体解释22. binlog和redo log区别23. MySQL的exists和in的区别是什么24.drop、delete、truncate的区别25.UNION 和UNION All的区别26. MySQL的数据库的连接池都有什么你用过什么27. 数据库的三大范式是什么28. 使用自增主键和UUID的区别29. 字段为什么要求定义为not null30. 什么是数据库事务以及事务的四大特性31. MySQL的脏读?幻读?不可重复读?32. MySQL的隔离机制?33. 隔离级别和锁的关系34. MySQL的锁都有什么表、页、行级锁共享锁排它锁35. MySQL中Innodb和MyISAM中使用什么锁36. MySQL中Innodb行级锁怎么实现的37. MySQL的死锁及其解决的方法38.乐观锁和悲观锁定义及实现39.超键外键候选键主键40. SQL的生命周期的步骤41. 大表数据查询怎么优化42. 关心过业务系统里sql的耗时么统计过慢查询么对慢查询都怎么优化43. 大批量删除数据的方法44. 大表优化数据量大CRUD慢该如何优化45. MySQL的分库分表你们是怎么做的?用了什么中间件46. MySQL的主从复制的工作原理47. MySQL的读写分离是什么以及解决方案48.MySQL有一个连接断了,我怎样保证获取不到这个连接?49. MySQL的搜索引擎之间的区别与特点怎么选择合适的引擎50. MySQL的更新语句的执行流程51. 什么情况会导致锁表52. 为什么插入MySQL会变慢? 持续更新中~ 1. MySQL的索引原理是什么?什么是索引以及索引的优缺点
索引是一种数据结构使用索引可以快速访问数据库表中的特定信息。索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。索引是一个文件是要占用物理空间的 优点
通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。可以大大加快数据的检索速度提高查询性能。可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。在使用分组和排序子句进行数据检索时索引可以显著减少查询中分组和排序的时间。通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。
缺点
创建索引和维护索引需要耗费时间这种时间随着数据量的增加而增加。索引需要占用物理空间除了数据表占用的数据空间之外每一个索引还需要占用一定的物理空间。当对表中的数据进行增加、删除和修改时索引也需要动态维护这可能会降低数据的维护速度。
索引的基本原理
把创建了索引的列的内容进行排序对排序结果生成倒排表在倒排表内容上拼上数据地址链在查询的时候先拿到倒排内容再取出数据地址链从而拿到具体数据
2. 解释一下B树和B树的区别及各自定义? 叶子节点没有子节点的节点 非叶子节点度不为0的节点又称分支节点 根节点顶端的节点称为根节点 一个节点下面最多两个节点 度结点拥有的子树数 平衡二叉树AVL树满足二叉树特性基础每个节点树高度差不超过1 二叉查找树左子节点小于当前节点的键值右子节点大于当前节点的键值 为什么选择B树实现索引 B树的内部节点只存放键不存值因此一次读取可以在内存中获取更多的键。一般来说索引本身也很大不可能全部存储内存中因此索引往往以索引文件形式存到磁盘上索引查找就要产生I/O消耗所以选择一个数据结构的优劣就是I/O查找复杂度B树叶子节点存有全部数据叶子节点用链表链接连接也是顺序存储的读值得时候会提前读进内存减少I/O次数查询排序都快磁盘分block块存储的一次读取若干block块B树不存数据所以B树单次IO信息量大于B树B树IO次数就少于B树所以用B树
B树
B树适合随机检索B树不稳定最好是根节点就查到了最坏查到叶子节点B树的每个节点都带有数据
B树
B树所有的叶子节点包含了全部的数据B树所有非叶子节点可以看成是索引节点没有数据只存键B树每个叶子节点都带有指向下一个叶子节点的指针形成有序链表B树必须查询到叶子节点
3. MyISAM索引和Innodb索引的区别
Innodb是聚簇索引MyISAM是非聚簇索引Innodb的主键索引的叶子节点存储着行数据因此主键索引非常高效MyISAM索引的叶子节点存储的事行数据地址需要再一次寻址才能得到数据Innodb非主键索引的叶子节点存储的是主键和其他带索引的列数据因此查询时做到覆盖索引会非常高效。
4. 什么是聚簇索引辅助索引
聚簇索引将数据存储和索引放到了一块找到了索引就找到了数据(在一棵树上) 非聚簇索引索引结构的叶子节点指向了数据的对应位置上(不在一棵树上)。 辅助索引也称为非主键索引或二级索引
辅助索引通常只存在于非聚集索引上在InnoDB存储引擎中辅助索引的叶子节点包含的不是数据行的完整信息而是对应数据行的主键值。这是因为InnoDB的数据是按照主键的顺序存储的也就是说主键索引也称为聚簇索引决定了数据的物理存储顺序。当执行一个查询时如果使用辅助索引来查找数据InnoDB首先会查找辅助索引来找到相应的主键值然后使用这个主键值去查找聚簇索引即主键索引最终定位到实际的数据行。这个过程需要两步因此被称为“二次查找”。辅助索引不会存在于聚簇索引上。在InnoDB存储引擎中聚簇索引并不是一种单独的索引类型而是一种数据存储方式。聚簇索引的叶子节点存放着一整行的数据而辅助索引非聚簇索引的叶子节点存储的是主键值而不是数据的物理位置。辅助索引是在聚簇索引之上创建的用于提高查询性能。由于辅助索引的存在不影响数据在聚簇索引中的组织所以一张表可以有多个辅助索引。 总之辅助索引和聚簇索引是两种不同的索引类型它们在InnoDB存储引擎中有不同的结构和用途。辅助索引用于提高查询性能而聚簇索引则决定了数据的物理存储顺序 5.非聚簇索引一定会回表查询么
不一定如果涉及到的查询语句所查询的字段是否全部命中了索引如果全部命中了就不必再回表查询。
6. 什么是回表操作
通过索引查询找到主键的键值在通过主键值查全表就叫回表查询。
7. 什么是索引覆盖?
如果要查询的字段都建立过索引那么索引会直接在索引表中查询而不会访问原始数据(否则只要有一个字段没有建立索引就会全表扫描)这就叫做索引覆盖。 因此我们需要尽可能在select后只写必要查询的字段以增加索引覆盖的几率。 8.什么是短索引
短索引Short Index是一种优化索引的方法用于减少索引占用的存储空间和提高查询性能。在MySQL数据库中短索引是指对字符串类型的列进行索引时不使用整个字符串的长度而是选择字符串的前N个字符来创建索引。这样可以减少索引的存储空间并且由于索引长度变短查询速度也可能会得到提高。
短索引的使用场景通常是在字符串类型的列中这些列的数据值很长但是前缀部分就足够区分不同的数据值。通过只索引字符串的前N个字符可以避免对整个字符串进行索引从而减少索引的存储空间。同时由于查询时通常只需要匹配字符串的前缀部分短索引也可以提高查询性能。
需要注意的是短索引虽然可以减少存储空间和提高查询性能但也会降低索引的选择性。索引的选择性是指不重复的索引值基数与数据表中的记录总数的比值。选择性越高索引的过滤效果越好查询性能也会更高。因此在选择使用短索引时需要权衡存储空间和查询性能与索引选择性的关系选择最合适的索引策略。
9.索引的数据结构有哪几种
哈希索引底层的数据结构就是哈希表只能用于对等比较例如‘’是一次定位数据所以检索效率高于b树索引。 B树索引是mysql默认的算法因为不仅可以用在、、、、和between这些比较操作符上还可以用于like操作符只要查询不以通配符开头就可比如‘jack%’可以‘%jack’就不可以。
10. MySQL的索引的创建原则MySQL不适合建立索引的情况?
数据少的情况不适合建立索引经常insert不适合建立索引数据重复且平分比如只有A、B两个值不适合建立索引非空字段上建立索引查询频繁的字段才适合建立索引尽量的扩展索引而不是新建索引定义为text、image、bit的数据类型不要建立索引
11. 使用索引一定能提高查询性能么
不一定索引是需要空间来存储的也需要定期维护而且每当有记录被修改、删除和增加的时候索引本身也会被修改。
12. MySQL的主键索引联合索引?唯一索引?
主键索引数据列不可以重复不允许null值 唯一索引数据列不可以重复允许为null值一个表允许多个唯一索引存在 联合索引(复合索引)
可以使用多个字段建立一个索引在联合索引中如果想要命中索引需要按照建立索引时的字段顺序挨个使用否则无法命中索引。mysql使用索引的时候需要索引有序假设现在建立了‘nameageschool’的联合索引name索引的排序为先按照name排序再按ageschool进行排序。注意事项在建立联合索引时候一般情况下将需求查询频繁或者字段选择性搞的列放在前面。
13. MySQL的索引下推是啥意思
是5.6之后提出的一个新特性(Index Condition Pushdown简称ICP)用于优化数据查询
假设我们设置了一个联合索引name、age语句是“name like “张%” and age 20 and school ‘大学’ ”在5.6之前只会走name的索引name右边的都不看拿到了主键值再去回表操作如果name条件找到了1000个那就要回1000次表在5.6之后在取出索引同时会根据where条件直接过滤掉不满足条件记录的减少回表次数这就是索引下推name like ‘张%’ age 20查出来之后再对school进行回表判断就好了。使用ICP会把age的判断下推给存储引擎不使用ICP会把age的判断给server进行 需要注意的是索引下推是否使用以及在哪些场景下使用取决于优化器的选择。优化器会根据查询的具体情况和数据分布等因素判断使用索引下推是否可以提高查询效率。因此在某些场景下即使存在索引下推的条件优化器也可能选择不使用索引下推。 14. MySQL索引的最左前缀原则是什么
最左匹配原则的原理利用索引前提是索引里的key有序abcd联合索引a是全局有序b是针对a来说局部有序以此类推在创建联合索引时根据业务需求where子句中使用最频繁的一列放在最左边遇到范围查询(,,between,like等)就会停止匹配比如a1 and b2 and c3 and d4,如果建立的是abcd的索引顺序d是用不到索引的abc的顺序可以任意调整和in是可以乱序的a1 and b2 and c3建立abc的索引顺序可以任意顺序mysql的查询优化器会帮忙优化成索引可以识别的形式。
15. MySQL的索引的rows很大会引起什么问题
当面试官提到“索引的rows很大会怎么样”时他们可能想考察你对索引性能、数据库优化和查询效率的理解。
“当索引的rows很大时这意味着索引可能变得不那么高效因为它需要更多的时间和资源来查找数据。具体来说大索引可能导致以下问题
性能下降随着索引大小的增加查找特定值所需的时间可能会增加从而导致查询性能下降。空间成本大索引会占用更多的磁盘空间这可能会增加存储成本并可能影响其他数据库操作的性能。维护开销大索引在进行插入、更新或删除操作时需要更多的维护这可能会影响到系统的整体性能。
为了应对这些问题我们可以采取以下策略
优化索引设计只为必要的列创建索引避免过度索引。同时选择适当的索引类型和数据类型。定期维护通过重建或优化索引来减少其大小并保持其性能。监控和调整定期监控索引的使用情况和性能根据需要进行调整。
16. MySQL的解决慢查询的方法?explain方法怎么用的?
EXPLAIN 命令是 MySQL 中用于查看查询优化器如何执行 SQL 查询的主要方法。通过 EXPLAIN我们可以获得查询执行计划中的很多信息从而分析 SQL 语句的执行情况找出可能的性能瓶颈并进行相应的优化。
要使用 EXPLAIN只需在 SQL 查询的 SELECT 语句前加上 EXPLAIN 关键字。例如
EXPLAIN SELECT * FROM student_info WHERE student_idA01234567;执行了包含 EXPLAIN 的查询后你会得到一个包含多列的输出结果。这些列的含义如下
id查询的序列号用于区分查询中的各个部分。select_type查询的类型用于区分普通查询、联合查询、子查询等复杂查询。type访问类型显示使用了何种类型的索引或全表扫描。其值从最好到最差依次为 Systemconst(主键索引或者唯一索引)eq_ref(在join查询中用primary key or unique not null索引关联)ref(使用非唯一索引查找数据)range(索引范围查找)index(遍历索引)ALL(全表扫描)fulltext(使用全文索引) table查询的数据表。possible_keys可能应用到的索引但不一定被查询实际使用。key实际使用的索引。ref显示索引的哪一列被用于查找。rows根据表统计信息及索引选用情况大致估算出找到所需的记录所需要读取的行数。
此外还有其他一些列如 Extra用于显示额外的信息如是否使用了文件排序、临时表等。
在查看 EXPLAIN 的输出时你需要注意以下几点
确保查询使用了正确的索引。如果 key 列显示为 NULL那么可能需要考虑添加或优化索引。注意 type 列的值。如果值为 ALL表示进行了全表扫描这通常意味着性能较差可能需要优化查询或添加索引。rows 列的值也很重要。如果它的值很大那么查询可能需要优化以减少需要检查的行数。
通过分析 EXPLAIN 的输出你可以更好地理解查询的执行过程并找出可能的性能瓶颈。这对于优化复杂的 SQL 查询和提高数据库性能非常有帮助。
17. MySQL的Sql优化方法有什么
对查询进行优化应尽量避免全表扫描首先应考虑在where及order by涉及的列上建立索引尽量避免在where字句中使用!,,,null,or,in,not in,like,字段表达式,函数查询的select 不要用*用啥字段写啥字段。
18. MySQL的left join、right join、inner join、join的区别
INNER JOIN或 JOIN
返回两个表中匹配的行。只有当左表和右表中的连接字段都有匹配的值时行才会出现在结果集中。如果左表或右表中的某行没有匹配项那么该行不会出现在结果集中。这是一种“等值连接”因为它只返回在两个表中具有相同连接字段值的行。
LEFT JOIN或 LEFT OUTER JOIN
返回左表中的所有行以及右表中匹配的行。如果左表中的某行在右表中没有匹配项那么结果集中该行的右表部分将包含NULL值。这意味着无论右表是否有匹配项左表中的所有行都会出现在结果集中。
RIGHT JOIN或 RIGHT OUTER JOIN
与LEFT JOIN相反它返回右表中的所有行以及左表中匹配的行。如果右表中的某行在左表中没有匹配项那么结果集中该行的左表部分将包含NULL值。这意味着无论左表是否有匹配项右表中的所有行都会出现在结果集中。 总的来说选择哪种连接类型取决于你想要从查询中获取什么样的数据。如果你只想获取两个表中都有的数据那么使用INNER JOIN。如果你想从一个表中获取所有数据即使它在另一个表中没有匹配项那么使用LEFT JOIN或RIGHT JOIN具体取决于你想保留哪个表的所有数据。 19. MySQL的小表驱动大表是什么意思
小表驱动大表是一种数据库查询优化策略其核心思想是在进行表连接操作时优先遍历数据量较小的表小表然后使用该小表中的数据去大表中查找匹配的行。这种策略通过减少需要检查的行数来优化查询性能。它在处理大量数据时非常有用可以帮助提高查询的效率和性能。
这种策略之所以有效是因为它减少了需要在大表中进行的查找次数。如果先遍历大表再去小表中查找匹配的行可能会导致大量的无效查找因为大表中的很多行可能在小表中没有对应的匹配行。而通过小表驱动大表的方式我们可以确保每次在大表中进行的查找都是基于小表中的实际数据从而提高了查询效率。
为了实现小表驱动大表的策略通常需要确保小表上的连接字段如上述例子中的用户ID有合适的索引以便快速查找匹配的行。此外在某些情况下可能还需要手动调整SQL查询语句的顺序或使用特定的查询提示来告诉数据库优化器采用这种策略。
20. binlog和redo log的具体解释
Binlog二进制日志
Binlog记录的是逻辑操作它捕捉的是MySQL上层即Server层执行的实际SQL语句。例如如果你执行了一个UPDATE语句来修改某个表中的记录Binlog会记录这个UPDATE语句的完整内容而不是记录实际在数据页上所做的物理更改。
这种记录方式有几个好处
可读性强Binlog的内容对于人类来说相对容易理解因为它记录的是我们实际执行的SQL语句。支持复制在主从复制场景中Master节点的Binlog会被发送到Slave节点Slave节点通过执行这些Binlog中的SQL语句来保持与Master的数据同步。支持点时间恢复如果你知道某个特定的时间点你可以使用Binlog来恢复到那个时间点的数据状态。
Redo Log重做日志
Redo Log是InnoDB存储引擎层面的日志它记录的是数据页的物理更改。当事务发生时InnoDB不会立即修改实际的数据页而是先将这些更改记录到Redo Log中。这意味着即使系统突然崩溃已提交的事务的更改仍然可以通过Redo Log来恢复。
Redo Log记录的是“在某个数据页上做了什么修改”而不是具体的SQL语句。例如如果你执行了一个UPDATE语句Redo Log不会记录这个UPDATE语句而是记录这个操作实际在数据页上所做的更改如更改了哪些字节的值。
这种记录方式的好处是
持久性确保即使在系统崩溃的情况下已提交的事务的更改也不会丢失。快速恢复在系统重启后InnoDB可以通过扫描Redo Log来快速应用未提交到数据页的事务更改从而快速恢复到崩溃前的状态。 总结一下Binlog和Redo Log的主要区别在于它们记录的内容和目的。Binlog记录的是逻辑操作即SQL语句主要用于复制和数据恢复而Redo Log记录的是物理更改即数据页上的更改主要用于确保事务的持久性和在系统崩溃时的快速恢复。 22. binlog和redo log区别
Binlog二进制日志和Redo Log重做日志是MySQL数据库中两种不同类型的日志它们在数据库的操作和恢复过程中起着不同的作用。以下是它们之间的主要区别
产生位置Binlog是在MySQL的Server层产生的而Redo Log是在InnoDB存储引擎层产生的。这意味着MySQL数据库中的任何存储引擎对于数据库的更改都会产生Binlog。 日志内容Binlog是逻辑日志它记录的是SQL语句的原始逻辑比如“给ID2这一行的c字段加1”。而Redo Log是物理日志记录的是数据页上的修改即“在某个数据页上做了什么修改”。 记录时间Binlog记录的是事务提交完毕之后的DML和DDL SQL语句。而Redo Log记录的是事务发起之后的DML和DDL SQL语句。 文件使用方式Binlog不是循环使用的当文件写满或者实例重启之后会生成新的Binlog文件。而Redo Log是循环使用的当最后一个文件写满之后会重新写第一个文件。 作用Binlog的主要作用是用于主从复制和数据恢复。而Redo Log的主要作用是确保事务的持久性即在系统崩溃或故障时能够利用Redo Log来恢复数据。
23. MySQL的exists和in的区别是什么
如果查询的两个表大小差不多in和exists差别不大如果两个表一个是大表一个是小表子查询表大的用的是exists反之用innot in 和not exists如果查询语句用了not in那么内外表都进行全表扫描没有用到索引not exists的子查询能用上表的索引所以not exists比not in 快 为什么某种情况下exists比in快 当使用 IN 时子查询需要返回所有满足条件的值然后主查询针对这些值进行过滤。如果子查询返回的结果集很大这可能会导致主查询的性能下降。 当使用EXISTS 时只要子查询找到至少一个满足条件的行它就会停止执行。这种“短路”行为使得 EXISTS 在某些情况下比 IN 更高效。 24.drop、delete、truncate的区别
delete表结构还在删除部分数据可回滚 truncate表结构还在删除全部数据不可回滚 drop表结构和数据都被删除索引和权限也会被删除不可回滚
25.UNION 和UNION All的区别
union all 不会合并重复记录行union效率高于union all
26. MySQL的数据库的连接池都有什么你用过什么
MySQL数据库的连接池是一种技术用于管理数据库连接的创建、使用和释放以提高应用程序的性能和可伸缩性。通过连接池应用程序可以重用已经创建的数据库连接而不是每次需要与数据库交互时都创建新的连接。这样可以减少连接和断开连接的开销提高应用程序的响应速度。
在MySQL中常用的连接池实现有以下几种
MySQL Connector/J连接池这是MySQL官方提供的Java数据库连接JDBC驱动程序的连接池实现。它提供了对连接池的配置选项如初始连接数、最大连接数、最小连接数等。许多Java应用程序使用MySQL Connector/J来连接MySQL数据库并利用其连接池功能。
C3P0连接池C3P0是一个开源的Java连接池库支持多种数据库包括MySQL。它提供了丰富的配置选项和性能监控功能被广泛用于企业级应用程序中。
HikariCP连接池HikariCP是另一个流行的Java连接池库以其高性能和低延迟而著称。它提供了简洁的配置和强大的功能如自动配置、连接超时、连接测试等。HikariCP在许多性能要求高的应用程序中被广泛使用。
DBCP连接池DBCPDatabase Connection Pooling是Apache Commons项目的一部分是一个开源的Java连接池库。它支持多种数据库包括MySQL并提供了连接池的基本功能如连接管理、配置和监控。
连接池代理除了上述提到的连接池实现还有一些连接池代理工具如ProxySQL。ProxySQL是一个开源的MySQL代理它可以实现连接池、查询缓存、读写分离等功能。通过将应用程序连接到ProxySQL可以间接地使用MySQL连接池。
在实际使用中我根据自己的需求和项目特点曾经使用过MySQL Connector/J连接池和HikariCP连接池。MySQL Connector/J连接池简单易用适合简单的应用程序和快速原型开发。而HikariCP连接池则更适合于对性能有较高要求的生产环境它能够提供更快的连接速度和更好的性能表现。
27. 数据库的三大范式是什么
第一范式每个列都不可以再拆分第二范式在第一范式基础上非主键列完全依赖于主键而不能是依赖于主键的一部分第三范式在第二范式的基础上非主键列只依赖于主键不依赖于其他非主键。
28. 使用自增主键和UUID的区别
推荐使用自增主键Id因为Innodb的主键索引是聚簇索引主键索引的B树叶子节点上存储了主键索引和全部数据顺序存储如果是自增的就可以向后排列即可但是如果是UUID新来的Id与原来的大小不确定就会造成数据移动产生很多内存碎片不连接造成性能下降 使用自增主键也有缺点合并表时会有主键冲突
29. 字段为什么要求定义为not null
null值会占用更多的字节且会在程序中造成很多与预期不符的情况。
30. 什么是数据库事务以及事务的四大特性
事务是一个不可分割的数据库操作序列也是数据库并发控制的基本单位其执行的结果必须使数据库从一种一致状态变到另一种一致性状态。事务是逻辑上的一组操作要么都执行要么都不执行。
原子性事务是最小的执行单位不允许分割一致性执行事务前后数据保持一致多个事务对同一个数据读取的结果是相同的隔离性并发访问数据库时一个用户的事务不被其他事务所干扰各并发事务之间数据库是独立的持久性一个事务被提交之后他对数据库中数据的改变是持久的即使数据库发生故障也不应该对其有任何影响。
31. MySQL的脏读?幻读?不可重复读?
脏读(Drity Read)事务A读取事务B更新的数据事务B回滚A读到的就是脏数据幻读(Phantom Read)在一个事务的两次查询中数据不一致一个事务第一次查询查了几列数据另一个事物此时插入了新的数据先前的事务在接下来的查询中会发现多了几列数据就叫幻读不可重复读(Non-repeatable Read)在一个事物的两次查询数据不一致这可能是两次查询过程中插入了一个事务更新或者删除的原有的数据
32. MySQL的隔离机制?
SQL的四个隔离级别是为了解决事务并发执行时可能出现的问题如脏读、不可重复读和幻读。这四个隔离级别分别是
读未提交Read Uncommitted
这是最低的隔离级别。在这个级别下一个事务可以读取另一个未提交事务的数据。因此它可能读取到“脏”数据即其他事务尚未提交的数据。可能出现的问题脏读、不可重复读、幻读。
读已提交Read Committed
这是大多数数据库系统的默认隔离级别例如Oracle、SQL Server。在这个级别下一个事务只能读取另一个已经提交的事务的数据。可以避免脏读因为读取的数据都是已经提交的。但是仍然可能出现不可重复读和幻读。
可重复读Repeatable Read
这是MySQL的默认隔离级别。在这个级别下一个事务在整个过程中可以多次读取同一数据并且每次读取的数据都是相同的即其他事务不能修改该数据。可以避免脏读和不可重复读但是仍然可能出现幻读。
串行化Serializable
这是最高的隔离级别。在这个级别下事务是完全串行执行的即一次只能有一个事务执行。避免了脏读、不可重复读和幻读但性能开销最大因为事务必须等待其他事务完成才能执行。 事务隔离机制的实现基于锁机制和并发调度其中并发调度用的是MVVC(多版本并发控制)通过保存修改的旧版本信息来支持并发一致性读和回滚等特性。 33. 隔离级别和锁的关系
读取未提交Read Uncommitted读取数据不需要加共享锁这样就不会跟被修改的数据上的排它锁冲突
读取已提交Read Committed读操作需要加共享锁但是在语句执行完以后释放共享锁
可重复读Repeatable Read读操作需要加共享锁但是在事务提交之前并不释放共享锁也就是必须等待事务执行完毕以后才释放共享锁
串行化Serializable限制性最强的隔离级别因为该级别锁定整个范围的键并一直持有锁直到事务完成。
34. MySQL的锁都有什么表、页、行级锁共享锁排它锁 隔离级别越低事务请求的锁越少 在没有索引的情况下Innodb只能使用表锁 在关系型数据库中可以按照锁的颗粒度把数据库分为行级锁表级锁和页级锁
行级锁是最细粒度的一种锁对行进行加锁加锁力度最小但是开销也最大会出现死锁并发程度最高行级锁又分为共享锁和排它锁表级锁最大力粒度的一种锁实现简单加锁快资源消耗较少锁冲突出现最高并发度最低表级锁又分为表共享读锁(共享锁)和表独占写锁(排他锁)页级锁一次锁定相邻的一组记录介于表级锁和行级锁之间会出现死锁并发度一般。
锁的类别上可分为
共享锁又叫做读锁当用户要进行数据读取时对数据加上共享锁可以同时加多个排它锁又叫做写锁当用户进行写入数据时对数据上排它锁排它锁只可以加一个他和其他排它锁共享锁都互斥。
35. MySQL中Innodb和MyISAM中使用什么锁
MyISAM采用表级锁Innodb支持行级锁和表级锁默认是行级锁
36. MySQL中Innodb行级锁怎么实现的
InnoDB的行锁是通过给索引的索引项加锁来实现的。 InnoDB按照辅助索引进行数据操作时辅助索引和主键索引都将锁定指定的索引项。通过索引进行数据检索时InnoDB才使用行级锁否则InnoDB将使用表锁。
需要注意的是即使查询条件中使用了索引字段是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的。例如对于一些很小的表MySQL可能会认为全表扫描效率更高从而不使用索引这种情况下InnoDB将使用表锁而不是行锁。
InnoDB的行锁机制(算法)包括RecordLock、CapLock和Next-key Lock三种锁。
RecordLock锁定单个行记录的锁它支持RC和RR隔离级别。CapLock间隙锁锁定索引记录间隙确保索引记录的间隙不变它仅在RR隔离级别下支持。Next-key Lock是记录锁和间隙锁的组合同时锁住数据并且锁住数据前后范围它在RR隔离级别下被使用。当SQL操作含有唯一索引时InnoDB会对Next-Key Lock进行优化降级为RecordLock仅锁住索引本身而非范围。
37. MySQL的死锁及其解决的方法
死锁是指两个或者多个事务在同一个资源上相互占用并请求锁定对方的资源从而导致恶性循环的现象。 解决办法
如果不同程序会并发存取多个表尽量约定以相同的顺序访问表可以大大降低死锁机会在同一个事务中尽可能做到一次锁定所需要的所有资源减少死锁产生的概率对于非常容易产生死锁的业务部分可以尝试升级锁颗粒度通过表级锁减少死锁产生的概率
38.乐观锁和悲观锁定义及实现
乐观锁假设不会发生冲突只在提交时检查是否违反数据完整性在修改数据时把事务锁起来通过version的方式来进行锁定实现方式一般会用版本号控制和CAS算法。适用于读场景多的情况。 悲观锁假设会发生冲突在查询时就把事务锁起来实现方式使用数据库中的锁机制。适用于写场景多的情况。
39.超键外键候选键主键
超键Super Key在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以作为一个超键多个属性组合在一起也可以作为一个超键。超键包含候选键和主键。 候选键Candidate Key是最小超键即没有冗余元素的超键。 主键Primary Key数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键且主键的取值不能缺失即不能为空值Null。主键是选中的一个候选键具有唯一性。 外键Foreign Key在一个表中存在的另一个表的主键称此表的外键。外键主要是用来描述两个表的关系。
40. SQL的生命周期的步骤
建立连接应用服务器与数据库服务器之间建立连接。这通常涉及TCP/IP连接和GLP协议。 接收SQL请求数据库服务器接收到客户端发送的SQL请求。 解析SQL数据库进程解析SQL语句生成执行计划。在这个过程中数据库会检查SQL语句的语法引用的对象以及调用优化器来确定查询计划。 执行SQL根据解析和优化后的执行计划数据库执行SQL语句。这可能包括从磁盘读取数据、对数据进行逻辑处理、排序、分组、过滤等操作。 返回结果数据库将执行结果发送回客户端。结果可能是一个数据集、一个错误消息、一个影响行数等信息。 关闭连接在完成所有操作后数据库服务器关闭与客户端的连接释放相关资源。
41. 大表数据查询怎么优化
使用索引
确保查询条件中的列有索引特别是WHERE、JOIN和ORDER BY子句中用到的列。避免在索引列上使用函数或计算这可能导致索引失效。使用覆盖索引Covering Index来减少数据查找时需要读取的数据量。定期监控索引的使用情况避免维护过多的无效索引。
查询优化
减少返回的数据量只选择需要的列避免SELECT *。使用LIMIT语句限制返回的行数。避免在查询中使用子查询特别是在大数据集上可以考虑使用JOIN来代替。使用预编译的SQL语句如Prepared Statements来避免SQL注入并减少解析时间。
表分区
对于非常大的表可以考虑使用表分区将数据分散到不同的物理区域提高查询性能。根据查询模式和业务需求选择合适的分区键。
数据库设计
规范化数据库设计避免数据冗余但也要考虑到查询性能有时候适当的反规范化可以提高查询速度。使用合适的数据类型避免不必要的数据类型转换。
硬件和配置优化
提高I/O性能如使用SSD、增加内存等。调整数据库配置参数如缓冲区大小、连接池大小等。考虑使用多核处理器并行处理查询。
使用缓存
利用查询缓存来存储频繁执行的查询结果减少数据库访问。使用应用级别的缓存如Redis、Memcached来缓存热点数据。
分批处理
对于非常大的数据集考虑分批处理数据而不是一次性处理所有数据。使用分页查询如LIMIT和OFFSET来分批获取数据。
异步处理
对于非实时要求的查询可以考虑使用异步处理如后台任务或消息队列。
监控和诊断
使用数据库监控工具来识别性能瓶颈。分析查询执行计划EXPLAIN来找出性能问题。
外部工具和服务
使用数据库代理如ProxySQL来分发和缓存查询。 - 考虑使用数据仓库或分布式数据库系统来处理大数据量。
考虑查询逻辑
避免在查询中使用NOT IN、 或 ! 操作符因为它们通常不会使用索引。 - 使用UNION ALL 代替UNION除非需要去除重复行。 - 避免在WHERE子句中使用OR来连接条件这可能导致全表扫描。
42. 关心过业务系统里sql的耗时么统计过慢查询么对慢查询都怎么优化
是的我统计过慢查询。在项目中我通常会定期分析数据库的慢查询日志来识别性能问题。通过慢查询日志我可以找到执行时间较长的查询语句并对其进行优化。 通过配置项slow_query_log on是开启慢查询设置临界值long_query_time 2查看show VARIABLES like ‘long_query_time’会在datadir下产生一个xxx-slow.log的文件一旦超过我们设置的临界时间就会记录到该文件里。
43. 大批量删除数据的方法
如果数据量大先删除索引再删除数据删完数据再重新建索引如果涉及到外键关联可以先关闭外键约束执行删除操作然后再重新启用外键约束可以使用SET FOREIGN_KEY_CHECKS0;来关闭外键约束执行删除操作后再使用SET FOREIGN_KEY_CHECKS1;重新启用外键约束TRUNCATE TABLE 命令会删除表中的所有数据但不会删除表本身。这是一个非常快速的操作因为它不记录个别行的删除操作。
44. 大表优化数据量大CRUD慢该如何优化
限定数据范围比如查询历史订单的时候可以控制在一个月范围内读写分离主库负责写从库负责读缓存使用缓存可以通过分库分表的方式进行优化主要有垂直分表和水评分表。
45. MySQL的分库分表你们是怎么做的?用了什么中间件
当面试官问到关于MySQL的分库分表以及使用的中间件时你可以按照以下方式回答
分库分表的做法
水平拆分Sharding
将同一个表中的记录分散到多个结构相同的表中通常基于某个字段的值进行拆分如用户ID的范围、哈希值等。这样可以将单个表的数据量减小提高查询性能并且可以通过增加更多的数据库服务器来分散读写压力。
垂直拆分
将一个表中的列分散到多个不同的表中每个表只包含部分列。这种方式通常用于将大表拆分为多个小表以减少单个表的复杂度和提高查询性能。
使用的中间件
在分库分表的架构中中间件起到了关键的作用它们帮助管理和路由数据库请求。以下是一些常见的MySQL分库分表中间件
ShardingSphere原名Sharding-JDBC
这是一个开源的分布式数据库解决方案套件提供了数据分片、读写分离、分布式事务和数据库治理等功能。ShardingSphere可以作为JDBC驱动的一个轻量级框架也可以结合Spring Boot等框架使用。
MyCAT
是一个开源的、支持MySQL协议的分布式数据库系统主要解决了单一MySQL数据库在大量数据、高并发场景下的性能瓶颈问题。MyCAT提供了全局的表结构对上层应用来说无需关心底层数据的分布和分片细节。
Vitess
由YouTube开发并开源的数据库分片解决方案支持MySQL和PostgreSQL。Vitess提供了自动化的分片、故障转移、备份和恢复等功能。
DRDS分布式关系型数据库服务
阿里云提供的一种数据库服务它支持MySQL协议并为用户提供了透明的分库分表解决方案。DRDS可以帮助用户快速构建高并发、高可用、可扩展的分布式数据库系统。
46. MySQL的主从复制的工作原理
master数据写入更新binlogmaster创建一个dump线程向slave推送binlogslave连接到master创建一个IO线程接收binlog并记录到relay log中继日志中slave再开启一个sql线程读取relay log事件并在slave执行完成同步slave记录自己binlog
47. MySQL的读写分离是什么以及解决方案
读写分离
将数据库的读操作和写操作分离到不同的数据库服务器上以提高系统的并发处理能力。写操作通常发生在主数据库上而读操作可以发生在多个从数据库上。
MySQL的读写分离的解决方案有多种以下是一些常见的方法
基于应用层的解决方案 程序修改MySQL操作类直接在应用程序中区分读操作和写操作将读请求发送到从库Slave写请求发送到主库Master。这种方式的优点是直接和数据库通信简单快捷可以实现随机方式的负载均衡和权限独立分配。但缺点是需要应用程序自己维护更新和增减服务器。 使用开源框架例如MyBatis Plus、Sharding-JDBC等这些框架提供了读写分离的功能可以方便地在应用程序中实现读写分离。基于代理层的解决方案 使用MySQL代理如mysql-proxy、Amoeba、MaxScale等。这些代理可以拦截应用程序对MySQL的请求根据请求类型将其转发到相应的主库或从库。代理层的优点是对于应用程序来说是完全透明的不需要修改应用程序代码。但缺点是可能存在单点失效的问题需要通过部署多个代理实例来解决。 使用数据库中间件如MyCAT、ShardingSphere-Proxy等。这些中间件也提供了读写分离的功能并且提供了更多的功能如数据分片、负载均衡等。使用中间件的优点是可以实现更高级别的数据库管理和优化但缺点是需要部署和维护额外的中间件服务。
48.MySQL有一个连接断了,我怎样保证获取不到这个连接?
当面试官问到关于如何处理MySQL中的一个断开的连接并如何确保不再获取到这个连接时他们可能想考察你对数据库连接管理和错误处理的理解。这可能涉及到多个方面包括连接池管理、错误捕获和处理、以及可能的重试策略。
以下是你可能给出的回答
连接池管理
使用连接池如HikariCP, C3P0, DBCP等来管理数据库连接。连接池会维护一个活动的连接集合当应用程序需要数据库连接时它会从池中获取一个连接。
当连接断开或无效时连接池通常会检测到这种情况并将其从池中移除。这样应用程序就不会再尝试使用这个无效的连接。
错误捕获和处理
在执行数据库操作时使用try-catch语句来捕获可能抛出的异常。当捕获到与连接断开相关的异常如java.sql.SQLException时可以关闭当前连接并尝试从连接池中获取一个新的连接。
重试策略
当检测到连接断开时可以实施一个重试策略尝试重新连接数据库。可以设置最大重试次数和重试间隔以防止无限循环或过多的重试导致资源浪费。
监控和日志
监控数据库连接的状态和性能以便及时发现并解决问题。记录详细的日志包括连接断开的原因、时间、以及采取的解决措施以便后续分析和优化。 示例回答 “当MySQL中的一个连接断开时我会首先确保我的应用程序使用了一个可靠的连接池来管理数据库连接。这样当连接断开或无效时连接池可以自动将其从池中移除。在执行数据库操作时我会使用try-catch语句来捕获可能的异常。如果捕获到与连接断开相关的异常我会关闭当前连接并从连接池中获取一个新的连接。此外我还会实施一个重试策略当检测到连接断开时尝试重新连接数据库。为了确保问题的及时发现和解决我还会监控数据库连接的状态和性能并记录详细的日志。” 49. MySQL的搜索引擎之间的区别与特点怎么选择合适的引擎
Innodb(5.5之后默认引擎)提供了对数据库ACID事务的支持并且还提供了行级锁和外键的约束它的设计的目标就是处理大数据容量的数据库系统MyIASM(5.5之前默认引擎)不支持行级锁和外键也不提供事务的支持MEMORY所有数据都在内存中数据处理的速度快但是安全性不高
Innodb和MyIASM的区别
MyISAMInnodb存储空间可被压缩存储空间较小需要更多的内存和存储他会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引文件格式数据和索引是分开存储的数据.MYD索引.MYI数据和索引是集中存储的.ibd可移植性、备份及恢复文件的形式存储跨平台数据转移更方便在备份和恢复时可以单独对某个表进行操作拷贝数据文件备份binlog或者用mysqldump外键不支持支持事务不支持支持锁支持表级锁行级锁、表级锁、锁的力度更小并发能力更高select更优insert、update、delete更优select count(*)更快内部维护了一个计数器可以直接调取索引的实现方式B树索引MyISAM是堆表B树索引是索引组织表哈希索引不支持支持全文索引支持不支持
50. MySQL的更新语句的执行流程
51. 什么情况会导致锁表
52. 为什么插入MySQL会变慢?
