[面试] 2. 关于MySQL的面试题

最新推荐文章于 2024-08-06 08:24:20 发布

城市里的元

最新推荐文章于 2024-08-06 08:24:20 发布

阅读量1.1k

点赞数 2

分类专栏： MySQL 后端开发数据库

本文链接：https://blog.csdn.net/sc_lilei/article/details/87881627

版权

MySQL 同时被 3 个专栏收录

7 篇文章 0 订阅

订阅专栏

后端开发

5 篇文章 1 订阅

订阅专栏

数据库

2 篇文章 0 订阅

订阅专栏

本文章收录于：后端工程师面试题目总结（提供参考答案）

1. 谈谈mysql字符集和排序规则?

扩展：更多关于此题目参考：https://www.cnblogs.com/wcwen1990/p/6917109.html

1.1 定义

mysql字符集是一套字符和编码。字符集有Unicode，编码方案有utf8、GBK和GB2312（都支持中文）、ASCII（不支持中文）

1.2 mysql中编码方案使用情况如何？

mysql中，具体到同一张表中的不同字段都可以使用不同的编码方案。而类oracle这种RDB不行

1.3 对Unicode的认识有多少？

       是一种在计算机上使用的字符集，包含了世界上所有语言，它为每种
       语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨平台、跨语言进行文本转换、处理的要求。
       Unicode存在多种编码方案，包括utf-8，utf-16,utf-32，gbk等等。utf是unicode transformation format。

unicode与utf8的区别，前者是一套包含所有语言的字符集，每个语言的每个字符都有其唯一的编号，虽然也可以把unicode直接作为编码方案，但是这样的方案太浪费空间，每个字符都要耗费4字节，而用ascii编码一个英文字符只需要1字节，所以就有了后来的utf8在内的各种编码方案，它们都声称兼容unicode，意思就是这些编码方案也可以存储和表示unicode字符。

注意：mysql内的utf8编码不是真正的utf8，真正对齐标准utf8编码的mysql内置的编码方案是utf8mb4，相关信息可以参考此文章

1.4 解释一下排序规则？

       它是在用来比较某种编码方案内的字符的一套规则。每种编码方案都可能有多种校对规则，并且都有一个默认的校对规则，
       并且每个校对规则只是针对某种编码方案，和其他编码方案没有关系。
       如utf8可以使用的排序规则有：
       utf8_unicode_ci(忽略大小写)，这种一般应用于邮箱字段存储
       utf8_unicode_cs，
       utf8_bin(将数据通过二进制编码存储，大小写敏感)
       修改字段的字符集和排序规则：ALTER DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci

2. varchar 与 char 的区别？

2.1 变长与定长

       char 表示定长，长度固定，varchar表示变长，即长度可变。char(10)表示存储10个字符，不足10个用空格补足，
       超过则截断超出部分（varchar也是）

2.2 各自的大小限制？

       char类型是字符数限制0-255,varchar是字节限制0-65535。
       注意：varchar类型的数据超过255个字符时还需要1到2个字节（小于等于255时需要1个字节，大于255则是两个字节）
       来存储真实长度（在头部）。

2.3 按utf-8编码，varchar最多存多少字符？

假如要创建一张表：create table t4(c int, c2 char(30), c3 varchar(M)) charset=utf8; 参考源

那么此处M的最大值是 (65535-1-2-4-30*3)/3=21812；

解释：减1是varchar字段默认NULL需要1个字节标识（仅varchar字段的null需要1个标识位）

减2是varchar字段需要2个字节存储其自身长度

减4是int类型需要4个字节

减30*3是char字段占用的长度30字符*3字节（存中文是三字节，字母是1字节，这里假设存中文）

注意：char(N), N是字符数，varchar(M)，M是字节数指的是最多存10个字符。

3. primary key 和 unique key区别？

   作用：前者主要是用来保证每条记录的唯一性以及作为外键字段，后者设计出来只是保证字段的唯一性
   相同：两者都不能有重复数据
   不同：
       a. 一个表中最多有一个primary key，但可以有多个unique key
       b. primary key字段不能为null，unique key可以

4. 外键有什么用，是否该用外键?外键一定需要索引吗?

   作用：简单说就是保持数据的一致性、完整性。
   是否改用外键：
       这要先说下外键的优势与劣势了。
       优势：
           a. 对关联表增删改操作由数据库帮我们实现，降低了开发成本
           b. 外键在一定程度上能够清楚的表明业务逻辑，使得设计尽可能周到和全面。
       劣势：
           a. 外键降低数据库性能，在一个海量的数据库中，使用外键会导致每一步数据库操作产生相当大的延时，这在
               如今速度为王的互联网时代是无法忍受的。
           b. 外键等于把数据的一致性事务实现，全部交给数据库服务器完成；
           c. 有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；
           d. 外键还会因为需要请求对其他表内部加锁而容易出现死锁；
   是否一定要索引？
       父表的关联字段必须建索引（除非是主键），否则添加外键约束失败。子表则不用，会自动为其创建。
       父表指的是被关联表。

小结：对于企业内部系统，数据不多（百万内），用外键没问题。

5. myisam与innodb的区别?innodb的两阶段锁定协议是什么情况?

   1、两者都支持索引
   2、myisam不支持事务，后者支持
   3、myisam是表级锁，后者是行级锁
   4、myisam不支持外键，后者支持
   5、前者支持数据压缩，后者不支持

   两段锁协议：
       指的是在事务执行过程中加锁和解锁两次行为，主要用于实现并行事务中对某些操作的串行化，既保证了事务的并行，
       又避免了脏读，脏写的发生。

6. 索引有什么用，大致原理是什么?设计索引有什么注意点?

6.1 索引有什么用

加快查询速度

6.2 大致原理？

       回答这个问题首先搞清楚索引有几种类型，目前用的最多的是哈希，BTREE，全文索引，
       不同索引类型适用不同场景，且不同存储引擎支持的索引类型也不尽相同。
       innodb支持btree和全文，myisam不支持hash，仅memory引擎支持显式hash索引，BTREE是使用最多的索引类型
       首先是hash索引：
           1、它是理解起来最简单的索引，基于hash表实现，只有查询条件精确匹配hash索引中的所有列的时候，才能用到
           hash索引。
           2、对于hash索引中的所有列，存储引擎都会为每一行记录计算一个hash码，hash索引中存储的就是hash码。
           3、hash索引包含键值，hash码和指针（指向具体数据）

           hash索引的限制：
               1、必须进行二次查找。因为hash表中存的是hash码，不是具体数据，所以还得通过指针找到对应的数据行。
               2、不支持部分索引查找，也不支持范围查找。因为是hash表是键值结构，所以只能是精确的等值查询，不能范围和模糊查询。
               3、当某些行的hash码相同时，hash表存储方式是，key为hash码，value为这些行的对应指针，指针通过链表形式连接。
               3、hash码可能冲突。如使用CRC32函数对数据进行hash得到的的0~2^32-1范围内的数字，仍然有小的冲突几率，当冲突时，
                   引擎就得遍历链表中的所有行指针来获取目标数据。少量重复数据还好，大量的话就大大降低hash索引效率了。
                   所以，hash索引不宜在重复可能较大的字段上建立，比如姓名。
       其次是BTREE索引：
           1、底层使用B+TREE数据结构实现，也叫平衡查找树。叶子到根部的结点距离相等。所有记录都按照键的大小排列，
               叶子结点之间通过指针连接，类似链表。叶子结点存的是数据，和相邻叶子结点的指针。
           2、BTREE索引更适合范围查询，因为索引是顺序存储的。
           3、适用于全值匹配查询。
           4、使用BTREE索引必须按照一定规则查询，否则查询语句将会全表扫描。
               （比如使用not in,<>,正则,函数，!>,!<,not like时均不会用到索引）

       最后是全文索引：
           1、它的底层原理是“倒排索引”，这也是一种数据结构
           2、倒排索引，亦称反向索引。一般用来存储在文本搜索场景中，每个单词在一个或一组文档中的存储位置。
           3、给列建立全文索引后（插入有数据），去自带数据库information_schema下的查看表INNODB_FT_INDEX_TABLE内容，
               这个表就是存的倒排索引数据。表中包含字段：word(单词), doc_id（文档ID）, position（文档存储位置）等。
               innodb采用是全倒排索引的方式，这种方式会占用更多空间，但也更高效，可通过一个单词快速找到对应文档以及
               在文档中出现的位置。
           4、查询时有两种模式供使用，自然语言和布尔模式，默认前者。
               自然语言模式使用起来比较简单，SELECT * FROM test WHERE MATCH(title) AGAINST('what' in NATURAL LANGUAGE MODE);
               表示查询带有‘what’的文档。

               布尔模式功能较多，如SELECT * FROM test WHERE MATCH(title) AGAINST('+Pease -hot' in BOOLEAN MODE);
               表示查询包含Pease,不包含hot的文档。

           5、5.6版本Innodb才开始支持全文索引，5.7通过ngram插件才支持中文的全文索引，因为中文分词不能根据空格。
               mysql只允许在char，varchar，text类型的列上建立全文索引。

6.3 设计索引有什么注意点?

       1、只对频繁查询的字段建立索引
       2、唯一性较差的字段不适合单独建索引，但可以做联合索引
       3、频繁更新的字段不适合，因为当数据量较大时，已建立索引的字段频繁更新，会导致索引文件频繁更新，数据库性能降低。

7. 哪些情况索引不生效？

7.1 WHERE子句中进行NULL值判断，以及使用 !=,not in,!>,!<,not like等反向比较操作的时候
7.2 使用or时，or相邻的字段未建立索引
7.3 使用LIKE查询时，将通配符写在开头
7.4 使用多列索引时，未使用多列索引的第一个字段
7.5 字段是字符串类型，但使用int，null类型比较
7.6 WHERE子句中使用函数表达式
7.7 mysql认为全表扫描比索引更快时

8. 什么是最左匹配原则？

   简单说：在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，
   当建立联合索引时，如alter table xx add index cover_index (col1,col2,col3)时
   数据库其实是建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
   然后查询：SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4” 就会使用(col1,col2)这个索引
   查询：SELECT * FROM test WHERE col1=“1” AND clo4=“4” 就会使用(col1)这个索引
   但是查询：SELECT * FROM test WHERE col2=“2” 就用不到联合索引，显然，数据库没有单独为col2建立索引
   也不能这样：SELECT * FROM test WHERE col1=“1” AND clo3=“3” 跳过了col2字段也是用不到索引的
   但可以这样：SELECT * FROM test WHERE col2=“2” AND col1=“1” 书写的顺序不影响。

   一句话总结，必须从联合索引的左边第一个字段一个一个的向后匹配，不能跳字段使用。

9、说说悲观锁和乐观锁

悲观锁与乐观锁是在资源并发中常见的两种锁设计方式。

9.1 悲观锁

       它的特点是，先获取锁，再做业务操作。即认为在它去操作数据库数据的同时，老是有人来抢，索性直接先获取锁，
       谁都抢不了，我先来。在mysql中的实现是“select … for update”语句或者直接“update... where...”，两者都能获得排它锁，
       防止别人操作。
       悲观锁因为要使用事务，降低了DB并发性能，适合强一致性的场景。

9.2 乐观锁

       它的特点是，先做业务操作，失败后就等着别人释放锁。即认为在它去操作数据库的同时，没有人跟他抢，那就不加锁了。
       直接修改业务数据。如果执行修改语句的时候发现资源被别人上了锁，就重试。
       mysql中如何实现：
给表增加一个字段叫版本号：version，int类型，修改一次加个1。然后通过几行SQL语句实现：
           SELECT DATA,version as old_v FROM xxx；
           rows = UPDATE SET data=data-1,version=version+1 WHERE version=old_v；
           if rows > 0:
               //修改成功
           else:
               //修改失败，然后重新再走一次此流程，直到成功（但是retry会造成较大的开销）。
       乐观锁适用于读多写少，并发冲突较少的场景。

10、关于join（快速掌握join点这里）

10.1 SQL语句中，join有哪些等效语句？

       join == inner join == cross join
       注：在不加on关键字时，返回的数据都是笛卡尔积（指的是两张表的所有行的所有组合情况，
       如A表10行，B表10行，那么返回的数据有10*10=100行）,
       网上的文章多说cross join才是返回的笛卡尔积，但其实三者完全等效（实测）。