面试全家桶
1.主流数据库服务软件有哪些？开源且跨平台的数据库软件有哪些？
甲骨文公司 Oracle
IBM DB2
微软 SQL Server
美国 Sybase 公司 Sybase
加州大学伯克利分校计算机系开发的 PostgreSQL
开源且跨平台的数据库软件有：
MySQL、PostgreSQL：开源且跨平台
Oracle、DB2：跨平台不开源
SQL Server：不跨平台不开源
Sybase：跨平台不开源
2.MySQL 数据库的服务进程叫什么名字？监听端口是多少？默认数据库目录是？
服务进程名是 mysqld；监听端口是 3306；默认数据库目录为 /var/lib/mysql。
3.MySQL默认的3个库叫什么名字？哪个库里的数据不占用物理磁盘空间？
3 个默认库：mysql、test 和 information_schema。
其中，information_schema 库的数据不占用磁盘空间，仅保存在内存里。
4.请列出 MySQL 常用的数据类型，并写出定义这些数据类型所使用的关键字
数值类型：所用关键字为 int、float
字符类型：所用关键字为 char、varchar
日期时间类型：所用关键字为 year、time、datetime
枚举类型：所用关键字为 set、enum
5.简述索引的优点与缺点，默认情况下哪个文件保存表的索引信息？
索引的优点与缺点如下：
索引就像一本书的目录
加快查询记录的速度
会降低插入、更新记录的速度
默认情况下“表名.MYI” 文件保存表的索引信息
6.简述在表中创建外键字段要满足那些条件？
在表中创建外键字段要满足以下条件：
表必须都使用 innodb 存储引擎
表中外键字段的类型要匹配
被参照字段要有明确的索引
7.简述 MySQL 体系结构的组成，并描述每个组成部分的作用。
主要包括 8 个部分：
连接池：进程数限制、内存检查、缓存检查等。
SQL 接口：用户通过 sql 客户端发过来的命令，由 sql 接口接收，sql 操作(DML 数据操作语言：查询、修改、升级
数据等；DDL 数据定义语言：创建一个新的数据库、新的索引、删除一个用户等；存储过程、视图触发器。
分析器： 分析查询语句 事务处理 对象访问权限。
优化器： 优化访问路径 、 生成执行树。
缓存和缓冲：保存 sql 查询结果。
存储引擎：用于管理存储的文件系统，将逻辑结构转换为物理结构的程序；不同的存储引擎有不同的功能和存储方
式。
管理工具：备份，恢复，安全，移植，集群等，这些工具一般和文件系统打交道，不需要和 mysql-server 打交道，
它们对应的都是命令。
物理存储设备(文件系统)。
8.简述 MySQL 数据库访问的执行过程。
1）客户端发出请求。
2）服务器端开辟线程响应客户端请求。
3）客户端发起 sql 语句查询数据库。
4）查询缓存：记录用户的 sql 查询语句，如果查询内容相同，直接从查询缓存回复。
5）如果缓存没有进入分析器。
6）分析器：分析用户命令语法是否正确，将用户的命令进行切片，一个词一个词用空格隔开，获得用户要查询的
表、内容、用户的权限等。
7）优化器：执行路径的选择，生成执行树。（每个 SQL 语句都有很多执行路径，优化的目的就是在这些执行路径
里选择最优的执行路径）。
8）存储引擎：用于管理存储的文件系统，不同的存储引擎有不同的功能和存储方式。
9.简述 MySQL 数据库中插入、更新、查询、删除表记录的指令格式。
连接到 MySQL 数据库服务器，练习以下表记录操作：
向表中插入记录的语法格式
更新表记录的语法格式
查询表记录的语法格式
删除表记录的语法格式
参考答案
1）向表中插入记录的语法格式
insert （into）表名（字段名列表） values(字段名=值，字段名=值，…);
2）更新表记录的语法格式
update 表名（set） （字段名=值，字段名=值，…） where（条件表达式列表）；
3）查询表记录的语法格式
select （字段名列表） （from） 表名 （where） 条件表达式列表；
4）删除表记录的语法格式
delete （from） 表名 （where） （ 条件表达式列表）；
10.在 MySQL-MMM 集群中有几种角色，各自的功能是什么？
共 3 种角色：客户端、monitor 节点（管理节点）、agent 节点（数据库节点）
客户端：访问集群
管理节点：负责所有的监控工作的监控守护进程，决定故障节点的移除或恢复
数据库节点：运行在 MySQL 服务器上的代理守护进程，提供简单远程服务集、提供给监控节点（可用来更改只读模
式、复制的主服务器等）
11.什么是 MySQL 集群？
答案：MySQL 集群是一个无共享的（shared-nothing），分布式节点架构的存储方案，其目的是提供容错性和高性
能。数据在单个数据节点（有时也称存储节点）上存储和复制，每个数据节点运行在独立的服务器上并维护数据的
一份拷贝。每个集群还有管理节点。数据更新使用读已提交隔离级别（read-committed isolation）来保证所有节
点数据的一致性，使用两阶段提交机制（two-phased commit）保证所有节点都有相同的数据 （如果任何一个写
操作失败，则更新失败）。
MySQL 集群的最初实现将所有信息都保存在主存内，没有任何永久性存储。后来 MySQL 集群允许数据存储在磁
盘上。通过存储引擎层 MySQL 服务器作为查询引擎，可以使 MySQL 集群的性能达到最佳。这样就可以将 MySQL 应
用透明地迁移到 MySQL 集群中去。
无共享的对等节点使得某台服务器上的更新操作在其他服务器上立即可见。传播更新使用一种复杂的通信机
制，这一机制专用来提供跨网络的高吞吐量。该架构通过多个 MySQL 服务器分配负载，从而最大程度地达到高性能，
通过在不同位置存储数据保证高可用性和冗余。
12.什么是读写分离？
MySQL Proxy 最强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务
性查询，而从数据库处理 SELECT 查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。 当
然，主服务器也可以提供查询服务。使用读写分离最大的作用无非是环境服务器压力。
13.读写分离的好处
1.增加冗余
2.增加了机器的处理能力
3.对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时
间上的延迟。
14.读写分离提高性能之原因
1.物理服务器增加，负荷增加
2.主从只负责各自的写和读，极大程度的缓解 X 锁和 S 锁争用
3.从库可配置 myisam 引擎，提升查询性能以及节约系统开销
4.从库同步主库的数据和主库直接写还是有区别的，通过主库发送来的 binlog 恢复数据，但是，最重要区别在于
主库向从库发送 binlog 是异步的，从库恢复数据也是异步的
5.读写分离适用与读远大于写的场景，如果只有一台服务器，当 select 很多时，update 和 delete 会被这些 select
访问中的数据堵塞，等待 select 结束，并发性能不高。 对于写和读比例相近的应用，应该部署双主相互复制
6.可以在从库启动是增加一些参数来提高其读的性能，例如--skip-innodb、--skip-bdb、--low-priority-updates
以及--delay-key-write=ALL。当然这些设置也是需要根据具体业务需求来定得，不一定能用上
7.分摊读取。假如我们有 1 主 3 从，不考虑上述 1 中提到的从库单方面设置，假设现在 1 分钟内有 10 条写入，150
条读取。那么，1 主 3 从相当于共计 40 条写入，而读取总数没变，因此平均下来每台服务器承担了 10 条写入和 50
条读取（主库不承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被
分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白了就是拿机器和带宽换性能。MySQL 官方文档中
有相关演算公式：官方文档 见 6.9FAQ 之“MySQL 复制能够何时和多大程度提高系统性能”
8.MySQL 复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最
快的速度恢复服务，因此不能光看性能，也就是说 1 主 1 从也是可以的。
15.MySQL 主从复制原理？
分为同步复制和异步复制，实际复制架构中大部分为异步复制。
复制的基本过程如下：
1)、Slave 上面的 IO 进程连接上 Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的
日志内容；
2)、Master 接收到来自 Slave 的 IO 进程的请求后，通过负责复制的 IO 进程根据请求信息读取制定日志指定位
置之后的日志信息，返回给 Slave 的 IO 进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已
经到 Master 端的 bin-log 文件的名称以及 bin-log 的位置；
3)、Slave 的 IO 进程接收到信息后，将接收到的日志内容依次添加到 Slave 端的 relay-log 文件的最末端，并
将读取到的 Master 端的 bin-log 的文件名和位置记录到 master-info 文件中，以便在下一次读取的时候能够清楚
的告诉 Master“我需要从某个 bin-log 的哪个位置开始往后的日志内容，请发给我”；
4)、Slave 的 Sql 进程检测到 relay-log 中新增加了内容后，会马上解析 relay-log 的内容成为在 Master 端真
实执行时候的那些可执行的内容，并在自身执行。
Mysql 为了解决这个风险并提高复制的性能，将 Slave 端的复制改为两个进程来完成。提出这个改进方案的人
是 Yahoo!的一位工程师“Jeremy Zawodny”。这样既解决了性能问题，又缩短了异步的延时时间，同时也减少了可
能存在的数据丢失量。当然，即使是换成了现在这样两个线程处理以后，同样也还是存在 slave 数据延时以及数据
丢失的可能性的，毕竟这个复制是异步的。只要数据的更改不是在一个事物中，这些问题都是会存在的。如果要完
全避免这些问题，就只能用 mysql 的 cluster 来解决了。不过 mysql 的 cluster 是内存数据库的解决方案，需要将
所有数据都 load 到内存中，这样就对内存的要求就非常大了，对于一般的应用来说可实施性不是太大。
复制常用架构
Mysql 复制环境 90%以上都是一个 Master 带一个或者多个 Slave 的架构模式，主要用于读压力比较大的应用的
数据库端廉价扩展解决方案。因为只要 master 和 slave 的压力不是太大（尤其是 slave 端压力）的话，异步复制
的延时一般都很少很少。尤其是自 slave 端的复制方式改成两个进程处理之后，更是减小了 slave 端的延时。而带
来的效益是，对于数据实时性要求不是特别的敏感度的应用，只需要通过廉价的 pc server 来扩展 slave 的数量，
将读压力分散到多台 slave 的机器上面，即可解决数据库端的读压力瓶颈。这在很大程度上解决了目前很多中小型
网站的数据库压力瓶颈问题，甚至有些大型网站也在使用类似方案解决数据库瓶颈。
编译安装数据库
1.首先安装编译MySQL的编译器,和依赖包. ```BASH [root@localhost ~]# yum install -y gcc gcc-c++ [root@localhost ~]# yum install -y cmake bison ``` 2.编译安装Ncurses,此工具是MySQL的依赖包. ```BASH [root@localhost ~]# yum install -y ncurses-devel [root@localhost ~]# tar -xzvf ncurses-6.1.tar.gz [root@localhost ~]# cd ncurses-6.1/ [root@localhost ~]# ./configure --with-shared --without-debug --without-ada --enable-overwrite [root@localhost ~]# make && make install ``` 3.编译安装MySQL,过程需要等待. ```BASH [root@localhost ~]# groupadd mysql [root@localhost ~]# useradd mysql -s /sbin/nologin -M -g mysql [root@localhost ~]# tar -xzvf mysql-5.5.59.tar.gz [root@localhost ~]# cd mysql-5.5.59/
[root@localhost ~]# cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql-5.5
-DMYSQL_DATADIR=/usr/local/mysql-5.5/data
-DMYSQL_UNIX_ADDR=/usr/local/mysql-5.5/mysql.sock
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DENABLED_LOCAL_INFILE=ON
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1
-DWITH_FAST_MUTEXES=1
-DWITH_ZLIB=bundled
-DENABLED_LOCAL_INFILE=1
-DWITH_READLINE=1
-DWITH_EMBEDDED_SERVER=1
-DMYSQL_USER=mysql
-DMYSQL_TCP_PORT=3306
-DWITH_DEBUG=0
------------------------------------------------------------------
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql #安装位置
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock #指定socket文件位置
-DEXTRA_CHARSETS=all #扩展字符支持
-DDEFAULT_CHARSET=utf8 #默认字符集
-DDEFAULT_COLLATION=utf8_general_ci #默认字符校对
-DWITH_MYISAM_STORAGE_ENGINE=1 #安装myisam存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 #安装innodb存储引擎
-DWITH_MEMORY_STORAGE_ENGINE=1 #安装memory存储引擎
-DWITH_READLINE=1 #支持readline库
-DENABLED_LOCAL_INFILE=1 #启用加载本地数据
-DMYSQL_USER=mysql #指定mysql运行用户
-DMYSQL_TCP_PORT=3306 #指定mysql端口
------------------------------------------------------------------
[root@localhost ~]# make && make install
4.给MySQL目录授权,并拷贝MySQL配置文件.
```BASH
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql-5.5/
[root@localhost ~]# cp -a /usr/local/mysql-5.5/support-files/my-medium.cnf /etc/my.cnf
[root@localhost ~]# cp -a /usr/local/mysql-5.5/support-files/mysql.server /etc/init.d/mysqld
[root@localhost ~]# chmod +x /etc/init.d/mysqld
5.初始化MySQL.
[root@localhost ~]# cd /usr/local/mysql-5.5/scripts/
[root@localhost ~]# /usr/local/mysql-5.5/scripts/mysql_install_db
--basedir=/usr/local/mysql-5.5/
--datadir=/usr/local/mysql-5.5/data/
--user=mysql
6.启动MySQL.
[root@localhost ~]# /etc/init.d/mysqld start
[root@localhost ~]# /usr/local/mysql-5.5/bin/mysqld_safe --user=mysql &
7.配置环境变量,并设置登陆密码.
[root@localhost ~]# echo 'export PATH=/usr/local/mysql-5.5/bin:$PATH' >> /etc/profile
[root@localhost ~]# source /etc/profile
[root@localhost ~]# /usr/local/mysql-5.5/bin/mysql_secure_installation
8.设置MySQL开机启动.
[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]# chkconfig --list mysqld
[root@localhost ~]# echo "/usr/local/mysql/bin/mysqld_safe --user=mysql &" >> /etc/rc.local
9.登陆MySQL测试.
[root@localhost ~]# /usr/local/mysql-5.5/bin/mysql -uroot -p
MySQL5.5 排错
[问题描述]
描述：MySQL 第一次初始化后无法启动
错误代码：Starting MySQL..The server quit without updating PID file ([FAILED]al/mysql-5.5/data/www.pid).
错误代码：Starting MySQL.180421 12:24:46 mysqld_safe error: log-error set to '/usr/local/mysql-5.5/error.log', however file don't exists. Create writable for user 'mysql'.
The server quit without updating PID file (/usr/local/mysql[FAILED]a/www.pid).
[解决策略]
vim /etc/my.cnf
[mysqld]
log-error = /usr/local/mysql-5.5/error.log
datadir = /usr/local/mysql-5.5/data
chown -R mysql.mysql /usr/local/mysql-5.5
./usr/local/mysql-5.5/scripts/mysql_install_db
参考文献：mysql5.7从入门到精通