面试汇总-MySQL-杂项

目录

1、悲观锁和乐观锁

2、数据库关键字的执行顺序是什么？

3、SQL优化

3.1、如何进行sql优化？

3.2、常见的join算法

3.2.1、Hash Join

3.2.2、Merge Join

3.2.3、Nested Loop Join

3.3、Join前后表的数据量对查询性能有什么影响？

4、MyISAM和InnoDB存储引擎区别

5、Mysql一个表最多支持多少个索引

6、MySQL的binlog

6.1、格式

6.1.1、Statement

6.1.2、Row

6.1.3、Mixed

6.2、查看binlog日志文件(mysqlbinlog)

7、delete语句删除数据后，表数据文件大小未发生改变

8、存储过程

9、视图

10、范式

11、​​​​​​​当自增主键id达到最大值时，如果继续插入数据，会是什么结果？

12、表结构设计细节问题

13、MySQL性能知识点

13.1、Exists和in的性能对比

​​​​​​​13.2、Dependent subquery：子查询

​​​​​​​13.3、主从同步时，逻辑日志有什么缺点？

14、一个自增表有5条数据，id为1到5，删除id为4和5的数据，重启MySQL，又新增一条数据，新增的数据id为几？

---

1、悲观锁和乐观锁

        悲观锁：对数据的冲突采取一种悲观的态度，始终假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住；

• 共享锁：读锁，允许多个并发事物读取锁定的资源，可同时读，不可写；
• 排它锁：写锁，不允许同时写，不允许写的同时读；

        乐观锁：认为数据一般情况下不会造成冲突，在数据进行提交更新的时候，才会对数据的冲突与否进行检测，如果发现冲突了，才返回错误信息。

Mysql乐观锁实现：
        为数据表增加一个版本标识（int），当读取数据时，同时独处版本标识，数据每更新一次，版本标识加1；更新前，需将读取的版本标识与数据库当前版本标识进行对比，相等的情况下才予以更新，否则操作的就是过期数据；

Mysql悲观锁实现：
        首先需关闭mysql的自动提交属性(autocommit)，在事务中，只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一笔数据时会等待其它事务结束后才执行，一般SELECT ... 则不受此影响。在不同的事务中同时执行包含上面两个关键字段的查询语句时，后开启的事务会被阻塞，直到先开启的事务关闭。

Java乐观锁实现：
        冲突检测和数据更新。典型的是Compare and swap(CAS)，当多个线程尝试使用CAS同时更新同一个变量时，只有其中一个线程能更新变量的值，而其他线程都失败，失败的线程并不会被挂起，而是被告知这次竞争中失败，并可以再次尝试。

Java悲观锁实现：
        Synchronized。

2、数据库关键字的执行顺序是什么？

        From，on，join，where，group by，having，select，distinct，order by，limit

3、SQL优化

3.1、如何进行sql优化？

• 避免进行全表扫描：

        在where和order by涉及的列上建立索引；

        避免在where子句中对字段进行null值判断(空值判断会放弃使用索引而导致全表扫描)

        避免在where子句中使用!=，<>操作符和来or连接条件(原因同上)；

        In和not in也会导致全表扫描；

        模糊查询时的%也会导致全表扫描；

        在where子句中对字段进行表达式或函数操作会导致全表扫描

• 适量使用索引但不能滥用索引；
• 尽量使用数字型字段而不是字符型：字符型比较时开销较大；
• 尽可能使用varchar代替char，变长字段存储空间小，在相对较小的空间内搜索效率较高；
• 查询时应尽量避免使用select *；
• 避免频繁创建和删除临时表，以减少系统表资源的消耗；
• 避免大事务操作，提高系统并发能力。

3.2、常见的join算法

3.2.1、Hash Join

        散列连接，适用于join的两个表数据量相差很大的情况；

• 优化器使用两个表中较小的表（通常是小一点的那个表或数据源）利用连接键（JOIN KEY）在内存中建立散列表，将列数据存储到hash列表中；
• 然后扫描较大的表，同样对JOIN KEY进行HASH后探测散列表，找出与散列表匹配的行。
• USE_HASH(table_name1 table_name2)提示来强制使用散列连接。

3.2.2、Merge Join

        排序合并连接，适用于不等价关联(>,<,>=,<=,<>)、HASH_JOIN_ENABLED=false、没有索引且数据已经排序的情况。

• 先将关联表的关联列各自做排序，然后从各自的排序表中抽取数据，到另一个排序表中做匹配。
• 使用USE_MERGE(table_name1 table_name2)来强制使用排序合并连接。

3.2.3、Nested Loop Join

        嵌套循环连接，适用于驱动表的记录集比较小（<10000）且内表关联列存在索引的情况。JOIN的顺序很重要，驱动表的记录集一定要小，返回结果集的响应时间是最快的。

• USE_NL(table_name1 table_name2)可强制CBO 执行嵌套循环连接。

3.3、Join前后表的数据量对查询性能有什么影响？

• 小表驱动大表，小表在前可提高执行效率。

  左连接的驱动表就是左边的那个表，右连接的驱动表就是右边的那个表。

  解释1：通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

  解释2：驱动表查询出数据需要一条一条的加入到join_buffer中，这需要IO操作，比较耗时，因此如果驱动表比较小，那么效率就高，这是小表驱动大表的一个主要原因。

• 如果内表走索引，性能高，但是如果内表是二级索引，效率也低，因为要回表查主键。
• 如果内表不走索引，为全表查询，此时小表驱动大表、大表驱动小表性能相差不大。此时，查询成本为双循环。

4、MyISAM和InnoDB存储引擎区别

MyISAM专注性能，InnoDb专注事务。两者最大的区别就是InnoDb支持事务和行锁。

其他：

• MyISAM为非聚集索引，索引和数据文件分离，索引保存的是数据文件的指针；

        InnoDB为聚集索引，索引和数据文件绑定在一起，必须有主键。

• MyISAM的存储文件分别为frm(表结构)、MYD(数据文件)、MYI(索引文件)；

        InnoDB的存储文件为frm(表结构)、ibd(表的数据和索引)。

• MyISAM保存了表的总行数、InnoDB未保存表的总行数。
• 系统崩溃后，MyISAM恢复数据较困难。

一般来说，如果需要事务支持、大量的update或delete操作，则选择InnoDB。

5、Mysql一个表最多支持多少个索引

• Innodb：

        最多1017列，最多创建64个二级索引，单个索引最多包含16列。

• Mysiam：

        最多4096列，最多创建64个二级索引，单个索引最多包含16列。

6、MySQL的binlog

        binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。

6.1、格式

6.1.1、Statement

基于SQL语句的复制，每一条会修改数据的SQL都会记录在binlog日志中。

• 优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。
• 缺点：必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到和在master端执行时候相同的结果。

6.1.2、Row

不记录sql语句及上下文信息，仅保存哪条记录被修改了。

• 优点：日志会很清楚地记录下每一行数据修改的细节。
• 缺点：所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容。

6.1.3、Mixed

        一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。

6.2、查看binlog日志文件(mysqlbinlog)

• Statement：mysqlbinlog mysql-bin.000001 
• Row：mysqlbinlog -vv mysql-bin.000001 

7、delete语句删除数据后，表数据文件大小未发生改变

• drop table table_name：删除表结构和数据，立刻释放磁盘空间。
• truncate table table_name：删除表数据，立刻释放磁盘空间。
• delete from table_name：删除全表数据，MyISAM立刻释放磁盘空间，InnoDb不释放空间。
• delete from table_name where：带条件删除数据，均不释放磁盘空间。
• delete后执行optimize table table_name，可立即释放磁盘空间。

        delete操作后，如果磁盘空间未被释放，会在下次增加数据时继续使用。

8、存储过程

        存储过程是能完成一定操作的一组SQL语句。

优势：

        一般的SQL语句每一次使用都需要进行编译，而存储过程只在创建时进行编译，之后执行可直接使用，进而提高数据库的执行速度。

        封装复杂的数据库操作。

        可以重复使用，减少数据库开发人员的工作量。

        安全性高，可设定指定用户使用权限。

9、视图

Select *from (select *from table1 where age = 11) where height > 170

视图可以理解成虚拟的表，是由数据库中实际的表通过select查询得来。比如，上面所示的SQL语句中，红色字体就可以抽象成一个视图。

通过：create view age_11 as select *from table1 where age = 11，就得到了一个名称为age_11的视图，在后续的操作中就可以直接使用age_11视图（select *from age_11 where height >170）。

10、​​​​​​​范式

规范的数据库设计应该遵守的规则和指导方法。

• 第一范式：属性不可分割，每个字段都应该是不可拆分的
• 第二范式：主键约束，要求数据库的每一个实例或者行必须可以被唯一的区分，即能根据主键值获取到唯一的其他属性列数据。
• 第三范式：外键约束，要求表中不能有其他表中存在的、存储相同信息的非主键字段。

11、​​​​​​​当自增主键id达到最大值时，如果继续插入数据，会是什么结果？

        再次插入时，主键自增ID为最大id，报主键冲突的错误。

12、表结构设计细节问题

• 在对unsigned修饰的整型字段进行减法操作时，如果计算的结果超出了范围(小于0或者大于最大整型)，mysql就会报错。可通过设置数据库参数sql_mode = NO_UNSIGNED_SUBTRACTION允许相减的结果为signed来解决这个问题。
• 用自增整型做主键时，一律使用BIGINT，而不是INT。减少后期数据量大时表结构调整。
• MySQL8.0版本前，整型自增不持久化，可能存在回溯问题。

        正常情况下，自增主键为1,2,3，删除3后，下一个肯定为4，主键不回溯；

        回溯情况下，自增主键为1,2,3，删除3后，数据库重启，那下一个主键值为3。

• 不要使用Float，Double两种浮点类型，后续MySQL不再支持。
• 如果想要存储emoji表情，最好把列字符集设置为UTF8MB4。
• 密码存储，加密时，推荐使用动态盐值 + 非固定加密算法的方式加密。
• TIMESTAMP和DATETIME：推荐使用DATETIME。

        TIMESTAMP优点是带有时区属性，缺点是它的最大值2038年已经快要到了。此外TIEMSTAMP需要通过时区计算时间，调用底层函数__tz_convert时会加锁，高并发访问时，性能也会有问题。

• 表结构设计时，推荐为每一个核心业务表，设计一个last_modify_date字段记录最后修改时间。
• 核心业务表，一定不要用自增键做主键。

        问题：回溯、分布式环境下存在主键不唯一，公开主键值，容易泄露数据。

• Innodb_autoinc_lock_mode控制自增锁持有的时间。

        值为1：每条SQL结束后释放自增锁；

        值为2：每次自增释放自增锁。

        举例：如果一条Insert语句插入了10条数据，如果mode值为1，那么只需要进行一次获取/释放锁的操作；如果mode值为2，那么久需要进行10次获取/释放锁的操作。

• UUID(无序)：时间低位(12位) + 时间中位(16位) + 时间高位(32位) + UUID版本号(4位)；

        UUID_TO_BIN：将时间高位放在前面，解决了UUID插入时乱序问题。二进制存储，精简了存储空间。

        BIN_TO_UUID：将二进制值反转为UUID字符串。

• 尽量减少数据表的数据冗余，冗余数据可能会增加维护难度。
• 页：页大小为16K，一个个页组成了每张表的表空间。

        一个页中存放的记录数越多，数据库性能越好。页存放在磁盘上，MySQL数据库要先将磁盘中的页读取到内存缓冲池，然后以页为单位来读取和管理数据。页中存放数据越多，可减少磁盘IO，从而提升性能。

• 页压缩技术，可将16K的页压缩成8K或者4K，以减少磁盘IO时的请求，从而提升数据库的整体性能。

        ROW_FORMAT=COMPRESSED：启动页压缩；

        KEY_BLOCK_SIZE=8：压缩大小。

        COMPRESS页压缩：对性能不敏感，只对存储空间有要求(比如日志表、监控表等)。

        TPC压缩：对存储有压缩需求，又希望不影响性能，推荐使用TPC压缩。

13、MySQL性能知识点

13.1、Exists和in的性能对比

        性能的差异在于驱动表的不同。

        如果主查询中的表较大且又有索引，子查询得出的结果集记录较少时，应该用in；反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

• in的执行顺序：

        select * from A where A.ID in（select B.ID from B ）

        首先执行一次子查询，子查询先产生结果集；

        然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出。

• exists的执行顺序：

        select * from A  where exists(select 1 from B where A.ID=B.ID)

        首先执行一次外部查询；

        对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值；

​​​​​​​13.2、Dependent subquery：子查询

        当执行计划中有DEPENDENT SUBQUERY时，表示这是一个依赖子查询，执行速度通常特别慢，需要手动转化成两张表的关联查询：Join。

​​​​​​​13.3、主从同步时，逻辑日志有什么缺点？

        事务不能太大，否则会导致二进制文件很大，事务提交慢。

        把大事务拆成小事务。

14、一个自增表有5条数据，id为1到5，删除id为4和5的数据，重启MySQL，又新增一条数据，新增的数据id为几？

• 如果表为MyISAM引擎，id = 6。
• 如果表为MySQL8.0之前的InnoDB引擎，id = 4；重启后，索引丢失。
• 如果表为MySQL8.0之后的InnoDB引擎，id = 6，MySQL8.0之后的InnoDB引擎会把索引持久化到日志中。
• ​​​​​​​

以上内容为个人学习理解，如有问题，欢迎在评论区指出。

部分内容截取自网络，如有侵权，联系作者删除。