这些年背过的面试题——MySQL篇

• 列存储 Hbase
• K-V存储 Redis
• 图像存储 Neo4j
• 文档存储 MongoDB
• 云存储OSS

• 用户行为日志收集系统收集日志之后推送到ETL做数据的清洗和转换

• 把ETL过后的数据发送到推荐引擎计算每个消费者的推荐结果，其中推荐逻辑包括规则和算法两部分

• 收集用户最近浏览、最长停留等特征，分析商品相似性、用户相似性、相似性等算法。

• 把推荐引擎的结果存入Aerospike集群中，并提供给广告投放引擎实时获取

  分别通过HDFS和HBASE对日志进行离线和实时的分析，然后把用户画像的标签(tag : 程序猿、宅男...)结果存入高性能的Nosql数据库Aerospike中，同时把数据备份到异地数据中心。前端广告投放请求通过决策引擎（投放引擎）向用户画像数据库中读取相应的用户画像数据，然后根据竞价算法出价进行竞价。竞价成功之后就可以展现广告了。而在竞价成功之后，具体给用户展现什么样的广告，就是有上面说的个性化推荐广告来完成的。

Neo4j是一个开源基于java开发的图形noSql数据库，它将结构化数据存储在图中而不是表中。它是一个嵌入式的、基于磁盘的、具备完全的事务特性的Java持久化引擎。程序数据是在一个面向对象的、灵活的网络结构下，而不是严格的表中，但具备完全的事务特性、企业级的数据库的所有好处。

• 优势总结:
  

• 性能上，使用cql查询，对长程关系的查询速度快

• 擅于发现隐藏的关系，例如通过判断图上两点之间有没有走的通的路径，就可以发现事物间的关联

// 查询三层级关系节点如下：with可以将前面查询结果作为后面查询条件match (na:Person)-[re]-(nb:Person) where na.name="林婉儿" WITH na,re,nb match (nb:Person)- [re2:Friends]->(nc:Person) return na,re,nb,re2,nc// 直接拼接关系节点查询match data=(na:Person{name:"范闲"})-[re]->(nb:Person)-[re2]->(nc:Person) return data// 使用深度运算符显然使用以上方式比较繁琐,可变数量的关系->节点可以使用-[:TYPE*minHops..maxHops]-。match data=(na:Person{name:"范闲"})-[*1..2]-(nb:Person) return data

MongoDB 是一个基于分布式文件存储的数据库，是非关系数据库中功能最丰富、最像关系数据库的。在高负载的情况下，通过添加更多的节点，可以保证服务器性能。由 C++ 编写，可以为 WEB 应用提供可扩展、高性能、易部署的数据存储解决方案。

{key:value,key2:value2}和Json类似，是一种二进制形式的存储格式，支持内嵌的文档对象和数组对象，但是BSON有JSON没有的一些数据类型，比如 value包括字符串,double,Array,DateBSON可以做为网络数据交换的一种存储形式,它的优点是灵活性高，但它的缺点是空间利用率不是很理想。

/* 查询 find() 方法可以传入多个键(key)，每个键(key)以逗号隔开*/db.collection.find({key1:value1, key2:value2}).pretty()/* 更新 $set ：设置字段值 $unset :删除指定字段 $inc：对修改的值进行自增*/db.collection.update({where},{$set:{字段名:值}},{multi:true})/* 删除 justOne :如果设为true，只删除一个文档，默认false，删除所有匹配条件的文档*/db.collection.remove({where}, {justOne: <boolean>, writeConcern: <回执> } )

• 文档结构的存储方式，能够更便捷的获取数据。

  对于一个层级式的数据结构来说，使用扁平式的，表状的结构来查询保存数据非常的困难。

• 内置GridFS，支持大容量的存储。
  GridFS是一个出色的分布式文件系统，支持海量的数据存储，满足对大数据集的快速范围查询。

• 性能优越

  千万级别的文档对象，近10G的数据，对有索引的ID的查询 不会比mysql慢，而对非索引字段的查询，则是全面胜出。 mysql实际无法胜任大数据量下任意字段的查询，而mongodb的查询性能实在牛逼。写入性能同样很令人满意，同样写入百万级别的数据，mongodb基本10分钟以下可以解决。

• 不支持事务
• 磁盘占用空间大

MySQL 8.0 版本

1、开通服务

2、创建存储空间

3、上传文件、下载文件、删除文件

4、域名绑定、日志记录

图片编辑（裁剪、模糊、水印）

视频截图

开源的轻量级分布式文件系统。它对文件进行管理，功能包括：文件存储、文件同步、文件访问（文件上传、文件下载）等，解决了大容量存储和负载均衡的问题。使用FastDFS很容易搭建一套高性能的文件服务器集群提供文件上传、下载等服务。如相册网站、视频网站等。

• 和流行的web server无缝衔接，FastDFS已提供apache和nginx扩展模块

• 文件ID由FastDFS生成，作为文件访问凭证，FastDFS不需要传统的name server

• 分组存储，灵活简洁、对等结构，不存在单点

• 文件不分块存储，上传的文件和OS文件系统中的文件一一对应

• 中、小文件均可以很好支持，支持海量小文件存储

• 支持相同内容的文件只保存一份，节约磁盘空间

• 支持多块磁盘，支持单盘数据恢复

• 支持在线扩容 支持主从文件

• 下载文件支持多线程方式，支持断点续传

• 客户端（client）

  通过专有接口，使用TCP/IP协议与跟踪器服务器或存储节点进行数据交互。

• 跟踪器（tracker）

  Trackerserver作用是负载均衡和调度，通过Tracker server在文件上传时可以根据策略找到文件上传的地址。Tracker在访问上起负载均衡的作用。

• 存储节点（storage）

  Storageserver作用是文件存储，客户端上传的文件最终存储在Storage服务器上，Storage server没有实现自己的文件系统而是利用操作系统的文件系统来管理文件。存储节点中的服务器均可以随时增加或下线而不会影响线上服务。

// FastDFS采用内存池的做法。 // v5.04对预分配采用增量方式，tracker一次预分配 1024个，storage一次预分配256个。 max_connections = 10240// 根据实际需要将 max_connections 设置为一个较大的数值，比如 10240 甚至更大。// 同时需要将一个进程允许打开的最大文件数调大vi /etc/security/limits.conf 重启系统生效 * soft nofile 65535 * hard nofile 65535
work_threads = 4 // 说明：为了避免CPU上下文切换的开销，以及不必要的资源消耗，不建议将本参数设置得过大。// 公式为： work_threads + (reader_threads + writer_threads) = CPU数
// 对于单盘挂载方式，磁盘读写线程分 别设置为 1即可 // 如果磁盘做了RAID，那么需要酌情加大读写线程数，这样才能最大程度地发挥磁盘性能disk_rw_separated：磁盘读写是否分离 disk_reader_threads：单个磁盘读线程数 disk_writer_threads：单个磁盘写线程数 

select id from table_xx where id = ? and version = Vupdate id from table_xx where id = ? and version = V+1

select id from table_xx where id > 100 for update;select id from table_xx where id > 100 lock in share mode;

CREATE  [UNIQUE | FULLTEXT]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]；说明：UNIQUE:可选。表示索引为唯一性索引。FULLTEXT:可选。表示索引为全文索引。INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的。索引名:可选。给创建的索引取一个新名称。字段名1:指定索引对应的字段的名称，该字段必须是前面定义好的字段。注：索引方法默认使用B+TREE。

select * from student  A where A.age='18' and A.name='张三';

mysql> explain select * from staff;+----+-------------+-------+------+---------------+------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------+|  1 | SIMPLE      | staff | ALL  | NULL          | 索引  | NULL    | NULL |    2 | NULL  |+----+-------------+-------+------+---------------+------+---------+------+------+-------+1 row in set

SELECT uid From user Where gid = 2 order by ctime asc limit 10ALTER TABLE user add index idx_gid_ctime_uid(gid,ctime,uid) #创建联合覆盖索引，避免回表查询

select * from mytbl order by id limit 100000,10  改进后的SQL语句如下：select * from mytbl where id >= ( select id from mytbl order by id limit 100000,1 ) limit 10select * from mytbl inner ori join (select id from mytbl order by id limit 100000,10) as tmp on tmp.id=ori.id;

• 原理：将主服务器的binlog日志复制到从服务器上执行一遍，达到主从数据的一致状态。
• 过程：从库开启一个I/O线程，向主库请求Binlog日志。主节点开启一个binlog dump线程，检查自己的二进制日志，并发送给从节点；从库将接收到的数据保存到中继日志（Relay log）中，另外开启一个SQL线程，把Relay中的操作在自身机器上执行一遍
• 优点：
• 作为备用数据库，并且不影响业务

• 可做读写分离，一个写库，一个或多个读库，在不同的服务器上，充分发挥服务器和数据库的性能，但要保证数据的一致性

1. 虚拟IP或DNS服务 （Keepalived +VIP/DNS  和 MMM 架构）

问题：在虚拟 IP 运维过程中，刷新ARP过程中有时会出现一个 VIP 绑定在多台服务器同时提供连接的问题。这也是为什么要避免使用 Keepalived+VIP 和 MMM 架构的原因之一，因为它处理不了这类问题而导致集群多点写入。

2. 提升备库为主库（MHA、QMHA）

如何进行分库分表

分表用户id进行分表，每个表控制在300万数据。

分库根据业务场景和地域分库，每个库并发不超过2000

• id保证业务在关联多张表时可以在同一库上操作
• range方便扩容和数据统计
• hash可以使得数据更加平均

垂直拆分：一个表拆成多个表，可以将一些冷数据拆分到冗余库中

不是写瓶颈优先进行分表

• 分库数据间的数据无法再通过数据库直接查询了。会产生深分页的问题

• 分库越多，出现问题的可能性越大，维护成本也变得更高。

• 分库后无法保障跨库间事务，只能借助其他中间件实现最终一致性。

• 富查询：采用分库分表之后，如何满足跨越分库的查询？使用ES的宽表

  借助分库网关+分库业务虽然能够实现多维度查询的能力，但整体上性能不佳且对正常的写入请求有一定的影响。业界应对多维度实时查询的最常见方式便是借助 ElasticSearch；

• 数据倾斜：数据分库基础上再进行分表；

• 分布式事务：跨多库的修改及多个微服务间的写操作导致的分布式事务问题？

• 深分页问题：按游标查询，或者叫每次查询都带上上一次查询经过排序后的最大 ID；

• 线上系统里所有写库的地方，增删改操作，除了对老库增删改，都加上对新库的增删改；

• 系统部署以后，还需要跑程序读老库数据写新库，写的时候需要判断updateTime；

• 循环执行，直至两个库的数据完全一致，最后重新部署分库分表的代码就行了；

• 支持3万的写并发，配合MQ实现每秒10万的写入速度

• 读写分离6万读并发，配合分布式缓存每秒100读并发

• 2000张表每张300万，可以最多写入60亿的数据

• 32张用户表，支撑亿级用户，后续最多也就扩容一次

1. 推荐是 32 库 * 32 表，对于我们公司来说，可能几年都够了。

2. 配置路由的规则，uid % 32 = 库，uid / 32 % 32 = 表

3. 扩容的时候，申请增加更多的数据库服务器，呈倍数扩容

4. 由 DBA 负责将原先数据库服务器的库，迁移到新的数据库服务器上去

5. 修改一下配置，重新发布系统，上线，原先的路由规则变都不用变

6. 直接可以基于 n 倍的数据库服务器的资源，继续进行线上系统的提供服务。

• 使用redis可以

• 并发不高可以单独起一个服务，生成自增id

• 设置数据库step自增步长可以支撑水平伸缩

• UUID适合文件名、编号，但是不适合做主键

• snowflake雪花算法，综合了41时间（ms）、10机器、12序列号（ms内自增）

show slave status

• 分库，拆分为多个主库，每个主库的写并发就减少了几倍，主从延迟可以忽略不计。

• 重写代码，写代码的同学，要慎重，插入数据时立马查询可能查不到。

• 如果确实是存在必须先插入，立马要求就查询到，然后立马就要反过来执行一些操作，对这个查询设置直连主库或者延迟查询。主从复制延迟一般不会超过50ms

select * from user where name = "xxx" and community="other";

select * from user where 1=1

int getUserSize() {        List<User> users = dao.getAllUser();        return null == users ? 0 : users.size();}