查看原文
其他

企业面试题|最常问的MySQL面试题集合(三)

点击上方“民工哥技术之路”,选择“置顶公众号”

有趣有内涵的文章第一时间送达!

接上二篇:

企业面试题|最常问的MySQL面试题集合(一)

企业面试题|最常问的MySQL面试题集合(二)

问题27:简述MySQL分表操作和分区操作的工作原理,分别说说分区和分表的使用场景和各自优缺点。

考点分析
  • 分区表的原理

  • 分库分表的原理

延伸:
  • MySQL的复制原理及负载均衡

分区表的工作原理

对用户而言,分区表是一个独立的逻辑表,但是底层MySQL将其分成了多个物理子表,这对用户来说是透明的,每一个分区表都会使用一个独立的表文件。

如图所示:MySQL将表分成多个物理字表,但PHP客户端并无感知,仍然认为操作的是一个表。

创建表时使用partition by子句定义每个分区存放的数据,执行查询时,优化器会根据分区定义过滤那些没有需要的数据的分区,这样只需要查询数据所在分区即可。

这样子表相对于未分区的表来说占用空间小,数据量更小,因此操作速度更快。

分区的主要目的是将数据按照一个较粗的粒度分在不同的表中,这样可以将相关的数据存放在一起,而且如果想一次性的删除整个分区的数据也和方便。

适用场景
  • 1、表非常大,无法全部存在内存,或者只在表的最后有热点数据,其他都是历史数据。

  • 2、分区表的数据更易维护,可以对独立的分区进行独立的操作。

  • 3、分区表的数据可以分布在不同的机器上,从而高效适用资源。

  • 4、可以使用分区表来避免某些特殊的瓶颈

  • 5、可以备份和恢复独立的分区

限制
  • 1、一个表最多只能有1024个分区

  • 2、5.1版本中,分区表表达式必须是整数,5.5可以使用列分区

  • 3、分区表字段如果有主键和唯一索引列,那么主键列和唯一索引列都必须包含进来

  • 4、分区表中无法使用外键约束

  • 5、需要对现有表的结构进行修改

  • 6、所有分区都必须使用相同的存储引擎

  • 7、分区函数中可以使用的函数和表达式会有一些限制

  • 8、某些存储引擎不支持分区

  • 9、对于MyISAM的分区表,不能使用load index into cache

  • 10、对于MyISAM表,使用分区表时需要打开更多的文件描述符

分库分表的工作原理

通过一些HASH算法或者工具实现将一张数据表垂直或者水平进行物理切分

适用场景
  • 1、单表记录条数达到百万或千万级别时

  • 2、解决表锁的问题

分表方式

水平分表:

表很大,分割后可以降低在查询时需要读的数据和索引的页数,同时也降低了索引的层数,提高查询次数

适用场景
  • 1、表中的数据本身就有独立性,例如表中分表记录各个地区的数据或者不同时期的数据,特别是有些数据常用,有些不常用。

  • 2、需要把数据存放在多个介质上。

水平切分的缺点
  • 1、给应用增加复杂度,通常查询时需要多个表名,查询所有数据都需UNION操作

  • 2、在许多数据库应用中,这种复杂度会超过它带来的优点,查询时会增加读一个索引层的磁盘次数

垂直分表

把主键和一些列放在一个表,然后把主键和另外的列放在另一个表中

适用场景
  • 1、如果一个表中某些列常用,另外一些列不常用

  • 2、可以使数据行变小,一个数据页能存储更多数据,查询时减少I/O次数

缺点

管理冗余列,查询所有数据需要join操作

分表缺点
  • 有些分表的策略基于应用层的逻辑算法,一旦逻辑算法改变,整个分表逻辑都会改变,扩展性较差

  • 对于应用层来说,逻辑算法增加开发成本

MySQL的复制原理及负载均衡

MySQL主从复制工作原理

  • 在主库上把数据更高记录到二进制日志

  • 从库将主库的日志复制到自己的中继日志

  • 从库读取中继日志的事件,将其重放到从库数据中

MySQL主从复制解决的问题

  • 数据分布:随意开始或停止复制,并在不同地理位置分布数据备份

  • 负载均衡:降低单个服务器的压力

  • 高可用和故障切换:帮助应用程序避免单点失败

  • 升级测试:可以用更高版本的MySQL作为从库

解题方法

充分掌握分区分表的工作原理和适用场景,在面试中,此类题通常比较灵活,会给一些现有公司遇到问题的场景,大家可以根据分区分表,MySQL复制、负载均衡的适用场景来根据情况进行回答

问:28:设定网站用户数量在千万级,但是活跃用户数量只有1%,如何通过优化数据库提高活跃用户访问速度?

  • 可以使用MySQL的分区,把活跃用户分在一个区,不活跃用户分在另外一个区,本身活跃用户区数据量比较少,因此可以提高活跃用户访问速度。

  • 还可以水平分表,把活跃用户分在一张表,不活跃用户分在另一张表,可以提高活跃用户访问速度。

问题29:SQL语句应该考虑哪些安全性?

考点分析

SQL查询的安全方案

延伸:

MySQL的其它安全设置

SQL查询的安全方案
  • 1、使用预处理语句防止SQL注入

     delete user where id = ?

  • 2、写入数据库的数据一定要进行特殊字符转义

  • 3、查询错误信息不要返回给用户,将错误记录到日志

注意:

PHP端尽量使用PDO对数据库进行操作,PDO拥有对预处理语句很好的支持的方法,MySQLi也有,但是可扩展性不如PDO,MySQL函数在新版中已经趋向于淘汰,所以不建议使用,而且它没有很好的支持预处理方法。

  • 1、定期做数据备份

  • 2、不给查询用户root权限,合理分配权限

  • 3、关闭远程访问数据库权限

  • 4、修改root口令,不用默认口令,使用较复杂的口令

  • 5、删除多余的用户

  • 6、改变root用户的名称

  • 7、限制一般用户浏览其它库

  • 8、限制用户对数据文件的访问权限

解题方法

通常情况下,SQL安全的考点都在防SQL注入的问题,因此只要遇到此类考点,优先考虑SQL注入的防护手段。

问题30:为什么使用mysqli和PDO连接数据库会比mysql连接数据库更安全?
mysqli和PDO支持预处理,可以防止SQL注入,mysql不支持预处理。

推荐阅读

[强烈推荐] 精心整理|公众号文章目录大全

民工哥的十年故事续集:杭漂十年,今撤霸都!

面对假劣疫苗,同事连夜做了这些,希望有帮助

Docker,你到底知道多少?

消息队列常见的 5 个应用场景

国务院又出新广告!官方卖萌真的不是一次两次了!

一图看懂中国最牛的500家公司!!

很多朋友从此绝交的,故事很短,却说穿了人性!

优秀架构师必须掌握的架构思维

340亿!互联网巨头遭天价罚单!

BAT大厂面试题整理,附答案(视频哦)!

·end·

—写文不易,你的转发就是对我最大的支持—

我们一起愉快的玩耍吧

目前40000+人已关注加入我们

       

       

关注公众号点击菜单“微信群” 入群与小伙伴一起交流吧!

    您可能也对以下帖子感兴趣

    文章有问题?点此查看未经处理的缓存