|
|
家好这里是小码哥，今天主要介绍的是MySql面试题的第二篇
MySql数据库是我们都需要学习的其sql编写，逻辑，优化表设计，响应速度，索引，查询规范几乎所有学习视频都是以Mysql为主数据库学习的，Mysql的博大精深就让小码哥带你好好了解下。
在企业中也是经常会提问到的相信大家深有体会，小码哥最近整理了java后端程序员从0-1的全部面试题，《祝大家看完面试顺利》关注我们持续更新 《想要更快拿到资源的请在公众号《码出宇宙》回复：码出八股文_斩出offer线》获取全套
16、B+树和B-树的区别
B+树性质：
B+树是B-树的变体，也是一种多路搜索树：
1.其定义基本与B-树同，除了：
2.非叶子结点的子树指针与关键字个数相同；
3.非叶子结点的子树指针P，指向关键字值属于[K, K[i+1])的子树（B-树是开区间）；
4.为所有叶子结点增加一个链指针； 5.所有关键字都在叶子结点出现； B-树性质：
是一种多路搜索树（并不是二叉的）：
1.定义任意非叶子结点最多只有M个儿子；且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]；
4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K < K[i+1]；
7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P指向关键字属于(K[i-1], K)的子树；
8.所有叶子结点位于同一层； 17、红黑树和平衡二叉树区别如下：
1、红黑树放弃了追求完全平衡，追求大致平衡，在与平衡二叉树的时间复杂度相差不大的情况下，保证每次插入最多只需要三次旋转就能达到平衡，实现起来也更为简单。 2、平衡二叉树追求绝对平衡，条件比较苛刻，实现起来比较麻烦，每次插入新节点之后需要旋转的次数不能预知。
18、二叉树、红黑树、B树小结
B-树：多路搜索树，每个结点存储M/2到M个关键字，非叶子结点存储指向关键字范围的子结点；所有关键字在整颗树中出现，且只出现一次，非叶子结点可以命中；B+树：在B-树基础上，为叶子结点增加链表指针，所有关键字都在叶子结点中出现，非叶子结点作为叶子结点的索引；B+树总是到叶子结点才命中；B*树：在B+树基础上，为非叶子结点也增加链表指针，将结点的最低利用率从1/2提高到2/3；
19、Undo原理：（备份旧数据）
在操作任何数据之前，首先将数据备份到一个地方（这个存储数据备份的地方称为Undo Log）。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
20、Redo原理：（保存最新数据）
和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是Redo Log已经持久化。系统可以根据Redo Log的内容，将所有数据恢复到最新的状态。
21、MySQL的并发控制与加锁分析
MVCC的设计目的是什么，怎么使用版本号判断数据的可见性
MVCC是一种多版本并发控制机制。锁机制可以控制并发操作,但是其系统开销较大,
而MVCC可以在大多数情况下代替行级锁,使用MVCC,能降低其系统开销。 1、人们一般把基于锁的并发控制机制称成为悲观机制，而把MVCC机制称为乐观机制。这是因为锁机制是一种预防性的，读会阻塞写，写也会阻塞读，当锁定粒度较大，时间较长时并发性能就不会太好；而MVCC是一种后验性的，读不阻塞写，写也不阻塞读，等到提交的时候才检验是否有冲突，由于没有锁，所以读写不会相互阻塞，从而大大提升了并发性能。 2、MVCC的一种简单实现是基于CAS（Compare-and-swap）思想的有条件更新（Conditional Update）。普通的update参数只包含了一个keyValueSet’，Conditional Update在此基础上加上了一组更新条件conditionSet { … data[keyx]=valuex, … }，即只有在D满足更新条件的情况下才将数据更新为keyValueSet’；否则，返回错误信息。
22、SQL中定义的四种标准隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改，哪些是在事务内和事务间可见 的，哪些是不可见的。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。
- 未提交读（Read uncommitted）：在未提交读级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也被称为脏读（Dirty Read）。这个级别会导致很多问题，从性能上来说，未提交读不会比其他的级别好太多，但是缺乏其他级别的很多好处，在实际应用中一般很少使用。
- 提交读（Read committed）：大多数数据库系统的默认隔离级别都是提交读（但Mysql不是）。提交读满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读（nonrepeatable read），因为两次执行同样的查询，可能会得到不一样的结果。
- 可重复读（Repeatable read）：可重复读解决了脏读的问题。该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读（Phantom read）问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务中又在该范围插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom row）。可重复读是MySQL的默认事务隔离级别。
- 可串行化（Serializable）：可串行化是最高的隔离级别。它通过强制事务串行执行，避免了前面所说的幻读问题。简单来说，可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑用该级别。
23、慢查询
MySQL慢查询开启，语句分析
一、开启mysql慢查询
方式一:修改配置文件
在 my.ini 增加几行: 主要是慢查询的定义时间（超过2秒就是慢查询），以及慢查询log日志记录（ slow_query_log）
方式二:通过MySQL数据库开启慢查询:
二、查看慢查询的数量
show global status like &#39;%slow%&#39;; 三、分析慢查询日志
直接分析mysql慢查询日志 ,利用explain关键字可以模拟优化器执行SQL查询语句，来分析sql慢查询语句
例如：执行EXPLAIN SELECT * FROM res_user ORDER BYmodifiedtime LIMIT 0,1000得到如下结果：
显示结果分析：
table | type | possible_keys | key |key_len | ref | rows | Extra
- table 显示这一行的数据是关于哪张表的
- type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL
24、Mysql调优
1、创建索引（恰当的添加索引）
如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
所以要建在合适的地方，合适的对象上。经常操作 / 比较 / 判断的字段应该建索引。
2、适当的用复合索引代替单索引
比如有一条语句是这样的：
select * from users where area=’beijing’ and age=22; 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。
因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
3、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5、like语句操作 （不鼓励模糊查询，会全表扫描）
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6、不使用NOT IN和操作
NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替。
7、开启查询缓存。
避免某些 SQL 函数直接在 SQL 语句中使用，从而导致 Mysql 缓存失效。
query_cache_type【0(OFF)1(ON)2(DEMAND)】来控制缓存的开关. 数据修改会带来缓存失效。
8、表的设计
垂直分割表，使得固定表与变长表分割，从而降低表的复杂度和字段的数目。
9、读写分离方案
海量数据的存储及访问，通过对数据库进行读写分离，来提升数据的处理能力, 数据库的写操作都集中到一个数据库上，而一些读的操作呢，可以分解到其它数据库上。
优点：得数据库的处理压力分解到多个数据库上，从而大大提升数据处理能力 缺点：付出数据复制的成本。
10、缓存技术
搭建redis或者memcache做为缓存层，提高数据库读取速度。
11、Mysql limit 分页机制和优化实例
300W数据，select XXX from tableA limit 1000000,10;会导致mysql将1000000之前的所有数据全部扫描一次，大量浪费了时间。
Solution
查询字段，加索引，可以建立与主键的复合索引 limit最大的问题在于要扫描前面不必要的数据，所以可以先对主键的条件做设定，然后记录住主键的位置再取行。
select * from p2p_20131230 where main_id > 1000000 order by main_id limit 10; 12、增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
25、索引的创建
1、创建索引
在执行CREATE TABLE语句时可以创建索引，也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。
普通索引、UNIQUE索引或PRIMARY KEY索引区别 如果不允许重复值，则使用UNIQUE索引或PRIMARY KEY索引，否则用普通索引，另外PRIMARY KEY索引是主键索引，一张表只有一个字段是PRIMARY KEY索引。
2、单字段索引创建
ALTER TABLE ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。 ALTER TABLE table_name ADD INDEX index_name (column_list) ALTER TABLE table_name ADD UNIQUE (column_list) ALTER TABLE table_name ADD PRIMARY KEY (column_list)
CREATE INDEX CREATE INDEX可对表增加普通索引或UNIQUE索引 CREATE INDEX index_name ON table_name (column_list) CREATE UNIQUE INDEX index_name ON table_name (column_list) 单字段索引删除
DROP INDEX index_name ON talbe_name 3、复合索引创建
ALTER TABLE myIndex ADD INDEX name_city_age (Name(10),City,Age); 为什么是Name(10)？ 一般情况下名字的长度不会超过 10，这样会加速索引查询速度，还会减少索引文件的大小，提高 INSERT 的更新速度。
4、Mysql复合索引符合最左原则：
对于复合索引:Mysql从左到右的使用索引中的字段，一个查询可以只使用索引中的一部份，但只能是最左侧部分。例如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 .当最左侧字段是常量引用时，索引就十分有效。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
5、哪些字段适合作为索引
注明：有两个概念叫做窄索引和宽索引,窄索引是指索引列为1-2列的索引,宽索引也就是索引列超过2列的索引; 设计索引的一个重要原则就是能用窄索引不用宽索引,因为窄索引往往比组合索引更有效;
6、何时用单字段索引
表的主键、外键必须有索引 数据量超过300的应该有索引 经常与其他表进行连接的表，在连接字段上应该建立索引 经常出现在where子句中的字段，特别是大表的字段，应该建立索引 索引应该建立在选择性高的字段上 索引应该建立在小字段上，对于大的文本字段甚至超长字段，不要建立索引 复合索引的简历需要进行仔细的分析；尽量考虑使用单字段索引代替
7、何时是用复合索引
根据where条件建索引是极其重要的一个原则，复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；如果where条件中是OR关系，加索引不起作用。
8、复合索引和多个单列索引的效率比较
比如有一条语句是这样的：
select * from users where area=’beijing’ and age=22; 如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。
9、如何使用复合索引与单索引索引？
对于具有2个用and连接条件的语句，且2个列之间的关联度较低的情况下，复合索引有一定优势。 对于具有2个用and连接条件的语句，且2个列之间的关联度较高的情况下，复合索引有很大优势。 对于具有2个用or连接条件的语句，单索引有一定优势，因为这种情况下复合索引将会导致全表扫描，而前者可以用到index merge的优化。
10、索引的建立的注意事项？
频繁进行数据操作（insert、update、delete）的表，不要建立太多的索引； 删除无用的索引，避免对执行计划造成负面影响； 以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处
在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
11、Mysql索引命中规则
最左匹配原则 先定位该sql的查询条件，有哪些，那些是等值的，那些是范围的条件。 等值的条件去命中索引最左边的一个字段，然后依次从左往右命中，范围的放在最后。
26、什么是视图
视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，试图通常是 有一
个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比 多表查询
27、水平分区
保持数据表结构不变，通过某种策略存储数据分片。
- 这样每一片数据分散到不同的表或者库中，达到了布式的目的。
- 水平拆分可以支撑非常大的数据量。
- 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据 拆成多张表来存放。
举个例子：
我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表 数据量过
大对性能造成影响。水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问 题，
但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆 分最好分库 。
水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性 能较差，逻辑复杂。
《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻 辑、部署、运维的各种复杂度 ，
一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问 题的。如果实在要分片，
尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。
客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的
Sharding-JDBC 、阿里的TDDL是两种
比较常用的实现。
- 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在 谈的
Mycat 、360的Atlas、网易的
DDB等等都是这种架构的实现。
28、分库分表之后,id 主键如何处理
因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。
生成全局 id 有下面这几种方式：UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。
比较适合用于生成唯一的名字 的标示比如文件的名字。
数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。
这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复 杂，
可用性降低，编码更加复杂，增加了系统成本。
Twitter的snowflflake算法 ：Github 地址：https://github.com/twitter-archive/snowflflake。
美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、 单调
递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等 中间件。
29、存储过程优化思路
- 尽量利用一些 sql 语句来替代一些小循环，例如聚合函数，求平均函数等。
- 中间结果存放于临时表，加索引。
- 少使用游标。 sql 是个集合语言，对于集合运算具有较高性能。而 cursors 是过程运算。比如对一 个
100 万行的数据进行查询。游标需要读表 100 万次，而不使用游标则只需要少量几次读取。
- 事务越短越好。 sqlserver 支持并发操作。如果事务过多过长，或者隔离级别过高，都会造成并发 操作
的阻塞，死锁。导致查询极慢，cpu 占用率极地。
- 使用 try-catch 处理错误异常。
- 查找语句尽量不要放在循环内
30、MySQL 中有哪几种锁？
1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最 低。
2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最 高。
3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间， 并发度一般 |
|
发表于 2022-12-21 16:12:38