MySQL篇面试题
# 主键相关问题
可以修改主键自增的起始值吗？ 可以 示例：PIMARY KEY AUTO_INCREMENT = 100
主键是唯一非空，普通的字段我们也可以添加唯一和非空约束，有区别吗？
有的，一张表只能有一个主键，但是一张表可以有多个唯一非空的字段
# 什么是事务？
面试话术
事务就是一组操作的集合，它是一个不可分割的工作单元，这些操作要不同时成功要不同时失败。
# 事务四大特性
事务四大特性统称ACID，分别是：原子性、一致性、隔离性、持久性
- 原子性：在一个事务的所有操作，要么同时成功要么同时失败
- 一致性：事务执行的前后，数据是保持一致的
- 隔离性：各各事物之间应该互不干扰
- 持久性：事务一旦提交，做出的修改是永久的
隔离性又会延伸不同隔离级别能够避免的问题：
- **脏读：**一个事务，读取到另一个事务中未提交的数据。
- **不可重复读(**虚读)：在同一个事务中，两次读取到的数据不一样。不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了。
- 幻读：一个事务操作(DML)数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。幻读的重点在于新增或者删除：同样的条件，第 1 次和第 2 次读出来的记录数不一样。
隔离级别又分：
- 读未提交：会出现脏读、不可重复读、幻读
- 读已提交：会出现不可重复读、幻读（oracle默认）
- 可重复读：会出现幻读（MySQL默认）
- 串行化：可以避免所有问题
# 请说说MySQL表级锁和行级锁？
面试话术
表级锁就是字面意思，锁整张表，当一个事务在修改表数据的时候，另一个事务无法修改表数据，锁的整张表嘛，自然对性能的影响也比较大，一般在数据迁移的时候才会用。
行级锁的话就是对某一条记录加锁，行级锁又分共享锁和排他锁，共享锁就是加个lock in share mode，意思我在改你就不能改，但是可以看，排他锁则是加个for update，意思连看都不给看，就是不给查询。
# MySQL索引有哪些类型？
面试话术
普通索引（INDEX）：最基本的索引，没有任何限制
唯一索引（UNIQUE）：与普通索引类似，不同的是唯一索引必须唯一，但允许有空值，一张表可以有多个唯一索引
主键索引（PRIMAY KEY）：是一种特殊的唯一索引，值唯一，但不允许有空值，一张表可以用多个唯一索引。
组合索引（INDEX）/复合索引/联合索引：为了更多的提高MySQL效率可以建立组合索引，遵循最左前缀原则。
全文索引（FULLTEXT）：仅可用于MyISAM表，用于在一篇文章中，检索文本信息的，针对较大的数据，生成全文索引很耗时耗空间。
# 建立索引有什么好处和坏处？
面试话术
索引是帮助MYSQL高效获取数据的数据结构，其好处最主要就是能提高检索效率，通过索引对列数据进行排序，降低数据排序成本，降低CPU功耗。然后可以通过建立唯一索引或者主键索引来保证数据的唯一性。
当作为表连接条件时，可以加速表连接，在作为分组和排序字段时可以减少分组和排序时所消耗的时间
坏处的话，会影响增删的性能吧，因为在插入数据的时候需要维护索引的结构，而且索引也是需要占用空间的，所以随着数据越多，建立的索引越多，占用的空间也就越大。
# 如何防止SQL注入？
面试话术
SQL注入主要就是对JDBC的那个PreparedStatement和Statement对象的选用嘛，Statement是存在SQL注入问题的，因为他是单纯的做一个SQL字符串拼接，而PreparedStatement会进行预编译（sql语义确认），所以不会有SQL注入这个问题。
也可以在传参中做一些校对，比如过滤掉一下SQL的关键字、符号之类的，比如where啊 大于小于，以及参数的格式等等。然后在用mybatis和mybatisplus的时候就注意参数的占位用##{}而不是${}，${}底层使用的是Statement，是存在SQL注入问题的。
# 请问MySQL优化的思路？
面试话术
SQL优化啊...首先可以查看慢查询日志，看看是那条查询sql比较慢，定位到了之后可以通过explain来查看执行计划，比如看一下type连接类型是什么啊，一般我们优化的目标就是朝着eq_ref和ref优化，让他使用到聚簇索引或者非聚簇索引，以及看一下possible_key、key、key_len，就是查看可能用到的索引以及实际用到的索引，还有索引的长度，就是使用联合索引的时候是否有把全部都用上，然后就是看rows，如果说把该做的优化都做了，连接类型也优化到eq_ref了，rows还是很大，那就说明优化已经到极限了。
然后在创建索引的时候也有些东西需要注意，比如说尽量建立联合索引来避免回表查询，也节省内存空间，像是字符串类型的字段，当这个字段存储较长的字符串时，而且又需要频繁查询这个字段，正常建立索引的话会导致索引变得非常大，导致查询的时候会浪费大量的磁盘IO来加载这个索引，影响效率，索引建立索引时需要建立前缀索引，然后通过计算这个字符串的选择性来确定前缀索引的长度，我记得是有一个公式的，但是记不清具体是怎样的了。
然后建立索引后会带来的问题就是在增删改索引字段的时候需要消耗额外的性能去维护索引结构，所以这也是我们为什么建表的时候会去添加一个逻辑删除字段来减少真正的删除操作。
sql语句的编写有一些点要注意，比如查询的时候，需要什么字段就查什么字段，尽量少用*，尽量与查询条件一致，原因是首先要啥查啥嘛，查的字段越少肯定相对的快一些，其次是当查询条件为非聚集索引时，由于非聚簇索引存放的只有列数据，所以当查询条件的列无法满足查询结果需要的列时，会需要回表查询，这时可以利用覆盖索引来避免回表问题，也就是让查询字段与查询条件涉及的字段一致，所以查询的时候也要注意尽量使用聚集索引，比如主键Id，因为聚集索引的表数据是跟索引存到一个叶子节点的，非聚集索引的话会有回表查询会相对慢一点。
然后要走索引就要避免一些索引失效的操作，比如范围查询的时候尽量不要用> 或 < 能用>=就用>=，因为范围查询的话会使索引失效，说白了就是条件尽可能的准确，不要用or、模糊查询like的百分号不要放左边、不要对索引列做函数运算，比如字符串切割之类的，然后如果列类型是字符串，在条件中一定要用引号引起来，以及索引字段的值不能为null，也就是说需要建立索引的字段尽量设置为非空字段。这些都是会导致索引失效的情况，然后查询的时候要遵守最左前缀原则。
在使用limit分页查询的时候，由于limit在做数据筛选的时候是必须得先扫描前面索引的数据，比如我limit 1000,10 实际上还是得先扫描前1000条数据，然后取后10条，所以效率就比较慢，当分页的深度比较高的时候，可以选择使用索引，例如 :select u* from user u,(select id from user order by id limit 1000,10)a where u.id =a.id;
也就是通过主键id查询出第1000条记录开始到后10条记录的主键，再通过这些主键去查询记录。
以及在使用update的时候，介于InnoDB引擎的默认事务隔离级别，一个事务在更新数据的时候会加锁，如果更新的字段是索引字段的话，那么加的则是行锁，如果是非索引字段则加的是表锁，加表锁的话就会比较影响性能了，所以尽量的给需要频繁更新的字段加上索引以提高并发修改的性能。
然后如果表数据量真的太大，还可以用Shardingsphere做个水平分库分表，再者如果查询业务比较复杂还可以考虑将MySQL的数据导到ES中，亦或是其他的数据库，比如MongoDB。
# BTree和B+Tree的区别？
面试话术
两者最大的区别我觉得是BTree不管是叶子节点和非叶子节点都会存储数据和索引，而且叶子节点数据是无链指针，而B+Tree的话只有叶子节点存放数据，非叶子节点只存放指针，而且叶子节点有单向有链指针。
B树主要的优势是越靠近根节点的记录查找的越快，因为越快找到索引嘛。但是范围查找的时候存在回旋查找问题，而B+数的话因为叶子节点存在单向链表，范围查找的时候可以避免回旋查找的问题，但是存在无法反向查找的问题，因为是单向链表，没办法回头去找数据，所以MySQL对B+树又做了优化，叶子节点采用双向链表，就大大提高了这个效率。
# 你知道哪些MySQL引擎？
面试话术
我记得的有InnoDB、MyISAM、Momery，这三个比较常见吧，InnoDB是MySQL默认的引擎，与其他两个最大的区别是支持事务、外键、行级锁、采用聚簇索引，MyISAM的话是MySQL之前的一个默认存储引擎，因为只支持表级锁、而且不支持事务，用的还是非聚簇索引，所以就被替代了。不过也是有运用场景的，对于事务完整性要求不高的场景，比如日志、足迹这类的数据存储，擦写频率高的场景还是可以用的，不过这块现在好像更多使用MongoDB来存储了。Momery的话最大特点就是内存存储吧，可以做为缓存来使用，不过现在缓存也一般都用Redis了，所以这两个引擎都不是很常用吧，InnoDB最大的优势就是支持事务，这个是难以替代的。
# 请问关于设计数据库你有什么心得？
面试话术
数据库设计，首先得遵守三大范式吧，第一范式字段不可分，就是姓名就只能放姓名，不能说张三-男这样，第二范式就是说要求一个表中要有主键，而且表中的所有字段都要与主键有关系，非主键都要依赖于主键，第三范式的话就是说一个表中只能描述一个实体，不能说用户信息跟地址信息都放一个表里，不过一般这种时候我们会做一些冗余字段的设计，就像是订单表里会冗余一个用户名的字段，来减少表连接查询，以及一个字段中存储多个id好像也有，也是为了减少表连接，提高查询效率，虽然这样有些违反三大范式，为了提高查询效率还是有必要的。
# 若要插入1kw条记录到mysql应该怎么优化？
面试话术
首先肯定要分页，分批导入。
然后如果允许改变数据库引擎的话，可以先将引擎设置为MyISAM，因为MyISAM是不支持事务的，不需要维护一些事务需要的表结构，innodb在插入数据的时候需要维护表级缓存，myisam只需要维护索引(文件级offset定位数据行，不需要缓存表)，innodb在插入和查询的时候需要维护mvcc，innodb在插入时维护主外键关系等，简单说就是MyISAM的结构简单，在插入数据时不需要消耗更多的资源去维护其他结构。所以可以先使用MyISAM导入数据，导入完成后再将存储引擎转换为InnoDB。
如果说不允许改变的话，那只能在sql语句上做优化了，首先得使用批量插入，分批插入，然后将自动提交事务改为手动提交事务，因为自动提交事务的话会需要频繁的开关事务，也会影响性能，然后就是主键顺序插入，主键顺序插入要比乱序插入要更快，这个跟数据的组织方式有关，这个是数据量小的情况。
数据量超过百万的时候就需要使用MySQL提供的Load指令来进行插入，注意格式，以及主键顺序插入。
# 为什么不建议用UUID做索引？
面试话术
UUID是长字符串类型，建立的索引比较消耗磁盘空间，构建的索引结构也会比较深。
#
(opens new window)为什么MySQL的索引可以加快查询效率？
面试话术
那这个就要从mysql的索引结构说起了，mysql5.5之前是B树，然后5.5版本之后就优化为了B+树，然后使用这种数据结构也是有个故事。
首先B树是二叉树下的一个分支，二叉树是存在一些问题的，首先二叉树规则就是左小右大存储，那就有可能会出现链表的情况，他不会进行自平衡，就会导致深度很大。
于是就有了平衡二叉树，但平衡二叉树又有频繁左旋和右旋来进行平衡的问题，这里在新增或删除节点的时候就比较影响性能，然后深度的问题也没有解决。
于是就出现了红黑树，由于红黑树的平衡规则（任意节点到其可到达的叶节点中黑色节点的数量相同，保证了红黑树的最长路径不会超过最短路径的2倍，从而保持了树的相对平衡），加入颜色的变化来尽可能避免旋转操作，从而达到更好的增删时的性能，并且相较于平衡二叉树能更好的控制层级深度。
但是终归红黑树的叶子结点还是最多允许2个，所以在这点上深度控制还是过于局限，所以在B树中，由二叉变为了多分叉，从而达到了更优秀的深度控制。
B树是一种多叉路的平衡查找树，ta不管是叶子节点还是非叶子节点都会存储数据，而且叶子节点数据无链指针。
介于叶子结点数据无链指针，所以会出现回旋查找问题
B+树在B树的基础上做了一种优化，使其更适合外存储索引结构，首先B+树只在叶子节点存储数据，非叶子节点不存储数据，只存储指针，并且在叶子节点增加了一条双向指针。
B树和B+树对比：
磁盘读写代价比B+树更低（能够更精确的读取到索引行的数据，无需在检索索引时加载多余的数据）
查询效率比B树更稳定（由于数据存储在叶子节点，检索路径基本一致，所以检索效率基本也差不多）
B+树便于扫库和区间查询（通过叶子节点的双向指针避免回旋查找问题）