大厂linux面试题攻略五之数据库管理

一、数据库管理-MySQL语句

0.MySQL基本语句：

1.SQL语句-增
创建xxx用户：
mysql>create user xxx@ '%' indentified by '123456';
xxx表示用户名
'%'b表示该用户用来连接数据库的方式（远程或本地连接）
indentified by '123456'设置密码创建数据库：
mysql>create database web;web数据库名创建数据表（创建表要先use 数据库）：
mysql>create table a1 (id int,name char(30));插入数据：
mysql>insert into a2(id,name,age) values (1,'sdds',21);2.SQL语句-删
删除用户：
mysql>drop user aaa@ '%';删除数据库：
mysql>drop database web;删除数据表：
mysql>drop table a1;删除数据：
mysql>delete from a2 where id=5;
mysql>delete from a2 where age between 23 and 25;3.SQL语句-改
修改表中的数据：
mysql>update a2 set age=21 where id=3;修改数据表的名称：
mysql>alter table a2 rename a1;修改数据表的字段类型：
mysql>describe a1;查看表的字段结构和类型
mysql>alter table a1 modify name char(50);modify表示字段
mysql>describe a1;修改数据表的字段类型：
mysql>alter a1 change name username char(50) not null default '';（change相当于rename+modify）
mysql>describe a1;添加删除字段：
mysql>alter table a1 add time datetime;（datatime时间格式类型）
mysql>alter table a1 drop time;4.SQL语句-查
查看所有数据库：
mysql>show databases;查看指定xxx库内的所有数据表：
mysql>use xxx;
mysql>show tables;查看指定数据表a1的字段结构：
mysql>describe a1;查看所有MySQL用户密码及登陆方式：
mysql>select User,Password,Host from mysql.user;5.SQL语句-授权
授予用户全部权限：
mysql>grant all on aa.a1 aaa@ '%';#aa库a1表
#给已存在的aaa用户授权
mysql>grant all on aa.a1 to aa@ '%' identified by '123456';
#创建aa用户并授权取消abc用户的删除库、表、表中数据的权限(回收权限)：
mysql>revoke drop,delete on aa.a1 from abc@ '%';
#取消删除权限（登陆abc测试）
mysql>show grants for abc@ '%';
#查看指定aaa用户的授权
mysql>show grants for aaa@ '%';6.SQL语句-启动关闭
启动:
service mysqld start
/etc/init.d/mysqld start
mysqld_safe &关闭：
service mysqld stop
/etc/init.d/mysqld stop
mysqladmin -uroot -p123456 shutdown

1.库表student.repprt,有3个字段，姓名、学科、成绩，记录如下，根据要求完成SQL语句

1.查询姓李的同学的个数

select count(*) from student.report where Name like '李%';

2.查询表中成绩前2名同学的名字，并按分数从大到小的顺序排列

select Name from report ORDER BY Result DESC limit 2;

二、数据库管理-MySQL集群

0.MySQL集群知识

MySQL主从工作原理：

MySQL主从复制是一种数据同步机制，它允许将一个MySQL数据库服务器（主服务器）的数据复制到其他MySQL服务器（从服务器）。下面是MySQL主从工作原理的概述：

配置主服务器：在主服务器上，需要开启二进制日志（binary logging）。二进制日志记录对数据库的所有修改操作（如插入、更新、删除），并将这些操作以二进制格式存储到日志文件中。
配置从服务器：在从服务器上，需要配置主服务器的相关信息，包括主服务器的IP地址、端口号、用户名和密码等。从服务器会连接到主服务器，并从主服务器上获取二进制日志文件。
同步数据：从服务器连接到主服务器后，会请求主服务器上的二进制日志文件，并从指定的位置开始读取。从服务器会执行这些日志文件中的操作，将主服务器上的修改操作应用到自己的数据库中，从而与主服务器保持数据一致。
异步复制：主从复制是异步的，即主服务器上的修改操作不会立即同步到从服务器上。相反，主服务器会将修改操作写入二进制日志，并通知从服务器有新的日志文件可用。从服务器会定期检查是否有新的日志文件可用，并下载、解析并执行新的日志文件。
主从延迟：由于主从复制是异步的，从服务器上的数据可能会有一定的延迟。这意味着从服务器上的数据可能不是实时的，可能会滞后于主服务器。这种延迟的程度取决于网络延迟、主服务器的负载以及从服务器的处理能力等因素。

总结来说，MySQL主从复制是通过记录和复制二进制日志（bin-log日志）来实现数据同步的。主服务器将修改操作写入日志文件，从服务器则通过获取和执行这些日志文件来同步主服务器上的数据。这种机制可以实现数据备份、读写分离以及负载均衡等应用场景。

1.MYSQL一主多从，主库宕机，如何合理切换到从库，其它从库如何处理?

从服务器切换为主服务：

登陆所有从库查看post信息，使用POST最大的做为新的主库，然后将从为提升为新的主库，登陆从库(新的主库) 执行stop slave（停止从服务）

修改my.cnf配置文件，开启log-bin并重新启动数据库服务，登陆数据库执行restet master ,show master status\G;查看主库信息，最后创建授权同步用户与权限和网站使用数据库的用户与权限，最后修改对应服务器的IP地址等信息

登陆其它从库，执行change master操作，查看同步状态

2.单台MySQL达到性能瓶颈时，如何击碎性能瓶颈?

数据库代理工具:Amoeba
Amoeba致力于MySQL的分布式数据库前端代理层，它主要在应用层访问MySQL的时候充当SQL路由功能，专注于分布式数据库代理层 (Database Proxy)开发。具有负载均衡、高可用性、SQL 过滤、读写分离、可路由相关的到目标数据库、可并发请求多台数据库合并结果。通过Amoeba你能够完成多数据源的高可用、负载均衡、数据切片的功能。

三、数据库管理-MySQL索引

0.索引的基本知识

什么是索引：

索引本质是数据结构，排好序的快速查找数据结构，可以提高查找效率
数据分身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引

索引的分类：

主键索引
单值索引一个索引只包含单个列，一个表可以有多个单列索引。如果字段会被经常用来检索就可以用单值索引
复合索引，一个索引包含多个列如电话簿上姓+名字。最好不超过5个字段
唯一索引，所有列的值必须唯一，但是允许有空值
普通索引和唯一索引可以称为辅助索引

劣势是什么：

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，索引列也是要占用空闻的。
虽然索引大大提高了查询速度，但是会降低更新表的速度，如对表进行INSERT,UPDATE，DELETE。因为更新表示，MySQL不仅要保存数据，还要保存一下索引文件，每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询

哪些情况不要创建索引

表记录太少，如果才几万条数据，不要建索引。三百万数据量创建索引
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
性别，国籍字段。有个公式索引的选择性段中不同的值个数除以总行数。

1.MySQL什么时候创建索引?

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
where条件里用不到的字段不创建索引
单键/组合索引的选择问题，在高并发下倾向创建组合索引
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段(group by)

2.误操作drop语句导致数据库数据破坏，请给出恢复的实际大体步骤

手动切割binlog日志并记好切割好的binlog日志文件位置，这里假设为009，备份全部binlog日志
找到之前全备数据最后备份到的binlog文件位置并记好位置，这里假设为005
用mysqladmin命令将005到008binlog文件中的SQL语句分离出来，并找到drop库的语句将其删掉
将之前全备数据导入mysq服务器
将步骤3中分离出的SQL语句导入mysq1服务器
将009binlog文件删除，再次刷新binlog日志，到此数据库已恢复成功

四、数据库管理-Redis

0.Redis工作原理

Redis是一个key-value存储系统，它支持的value类型相对较多，包括string、list、set和zset，这些数据都支持push/pop/add/remove及交并补等操作，而且这些操作都是原子性的，在此基础上，redis支持各种不同方式的排序。为了保证效率，数据是缓存在内存中的，Redis会周期性的把数据写入磁盘或者把修改操作写入追加的记录文件，并且在此基础上实现了master-slave同步。

原子性（Atomicity）是指一个操作或一组操作要么全部执行成功，要么全部不执行，不会出现部分执行的情况。换句话说，原子性操作是不可分割的，要么完全执行，要么不执行，不存在中间状态。在并发编程中，原子性是一种非常重要的概念，用于确保多个线程或进程对共享数据的访问不会导致数据不一致或不确定的结果。如果多个线程同时访问和修改相同的数据，而没有保证原子性，可能会导致数据损坏、计算错误或其他异常结果。原子性是并发编程中保证数据一致性和正确性的重要手段。通过使用锁或原子类型等方法，可以确保操作在并发环境下以原子方式执行，从而避免数据竞争和不一致的结果。正确地处理并发访问共享数据的问题，可以提高代码的正确性和可靠性。

1.如何保证Redis能永久（持久化）保存数据?

Redis持久化方案：

方法一

Redis持久化-RDB：
在Redis运行时，RDB程序将当前内存中的数据库快照保存到磁盘中，当Redis需要重启时，RDB程序会通过重载RDB文件来还原数据库。

保存(rdbSave)
rdbSave负责将内存中的数据库数据以RDB格式保存到磁盘中，如果RDB文件已经存在将会替换已有的RDB文件。保存RDB文件期间会阻塞主进程，这段时间期间将不能处理新的客户端请求，直到保存完成为止
读取(rdbLoad)
当Redis启动时，会根据配置的持久化模式，决定是否读取RDB文件,并将其中的对象加载到内存中。

方法二

Redis持久化-AOF：
以协议文本的方式，将所有对数据库进行的写入命令记录到AOF文件，达到记录数据库状态的目的。

AOF的保存
        1.将客户端请求的命令转换为网络协议格式
        2.将协议内容字符串追加到变量server.aof_buf中
        3.当AOF系统达到设定的条件时，会调用aof_fsync(文件描述符号)将数据写入磁盘

AOF的读取
        1.AOF保存的是数据协议格式的数据，所以只要将AOF中的数据转换为命令，模拟客户端重新执行一遍，就可以还原所有数据库状态
        2.创建模拟的客户端
        3.读取AOF保存的文本，还原数据为原命令和原参数。然后使用模拟的客户端发出这个命令请求
        4.继续执行第二步，直到读取完AOF文件

AOF重写流程
        1.AOF重写完成会向主进程发送一个完成的信号
        2.会将AOF重写缓存中的数据全部写入到文件中
        3.用新的AOF文件，覆盖原有的AOF文件

2.如何利用Redis对MySQL进行性能优化?

Redis 是一个高性能的内存数据库，可以用作缓存层，提升 MySQL 的性能。以下是一些利用 Redis 对 MySQL 进行性能优化的方法：

缓存查询结果：将经常被查询的数据结果缓存到 Redis 中，避免反复查询 MySQL 数据库。当需要该数据时，先从 Redis 中查询，如果存在则直接返回，如果不存在再从 MySQL 中查询。
缓存数据对象：将经常被查询的数据对象缓存到 Redis 中，可以以 JSON 或其他格式存储。当需要该对象时，先从 Redis 中查询，如果存在则直接返回，如果不存在再从 MySQL 中查询并将结果存储到 Redis 中。
缓存热点数据：将热点数据缓存到 Redis 中，例如热门商品、最新动态等。通过定期刷新缓存保持数据的最新性。
缓存查询条件：将经常被使用的查询条件缓存到 Redis 中。当需要执行该查询时，先从 Redis 中获取查询条件，然后执行查询。
使用消息队列：将写操作转化为消息，通过消息队列将写操作同步到 MySQL 和 Redis 中。这样可以避免直接操作 MySQL 和 Redis 造成的性能瓶颈。
数据预加载：在系统启动时，将常用数据加载到 Redis 中，以减少后续查询 MySQL 的次数。
分布式缓存：使用 Redis Cluster 或者 Redis Sentinel 部署多个 Redis 实例，提高缓存的可用性和性能。