【mysql】常见问题

1.mysqld和mysql.server启动mysql有什么区别

mysqld：这个可执行文件就代表着 MySQL 服务端程序，运行这个可执行文件就可以直接启动一个服务端进程。

mysqld_safe：这是一个启动脚本，它会间接调用 mysqld，而且还顺便启动了另外一个监控进程，这个监控进程在 MySQL 服务端进程挂了之后可以帮助重启它。另外，使用mysqld_safe 启动 MySQL 服务端进程时，它会记录服务端出错信息和其他诊断信息，从而方便后续排查错误原因。

mysql.server：这也是一个启动脚本，它会间接调用 mysqld_safe（可以查看其链接文件的源码看到更多细节）。

所以，通常我们推荐：mysql.server start   /  mysql.server stop来启动/关闭

使用 mysql.server 启动 MySQL 服务端程序时，除了 strat 和 stop 之类的启停命令不再支持其他参数传递，如果要设置 MySQL 服务端启动参数，只能通过配置文件设置。

2.mysql长连接与短连接的区别

长连接

指连接成功后，如果客户端持续有请求，则一直使用同一个连接，对应在 Web 应用中，就是后端程序与数据库之间的连接建立之后，就会一直重用这个连接（为了提升数据库并发性，可以建立一个数据库连接池）；

短连接

指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个，对应在 Web 应用中，就是后端程序与数据库建立连接，完成查询/更新后，就断开连接，下次操作数据库再重连。

不过在 MySQL 中，使用长连接会有一定的副作用 —— 有些时候会导致 MySQL 占用内存涨得特别快。这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源只有在连接断开的时候才会释放。对于这个问题，通常有两种解决方案：

定期断开长连接：使用一段时间，或者程序里判断执行过一个占用较大内存的查询后，主动断开连接；

如果使用的是 MySQL 5.7 或者更高版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。

3.mysql语句的执行流程

如果命中查询缓存，MySQL 不需要执行后面的复杂操作就可以直接返回结果，查询效率会很高。但是通常不建议这么做，原因和是否要在业务逻辑中保存模型类查询结果一样：因为这个缓存的 key 是查询语句，只要有一点不同（实际项目中查询字段、查询条件千姿百态）就会导致缓存命中失败，同时，数据表记录本身也是不断更新（插入、更新、删除）的，更新之后，之前的查询缓存就全部失效了，所以从维护成本和实际收益上看，得不偿失。

MySQL 8.0开始废弃查询缓存。

4.mysql innodb、MyISAM有什么区别？

它们都选择了B+树做索引，但是

数据存储方式不同：MyISAM 数据和索引是分开存储的（MyISAM每个表有三个文件：.frm 保存的是表结构，.MYI 保存的是索引信息，.MYD 保存的是所有数据记录），innodb数据和索引存储在一起（.frm 用于存放表结构，.ibd 用于存放表数据和索引）（都是二进制文件，无法直接查看内容）

表空间：InnoDB可以通过改变配置设置将所有表数据和索引存放在系统表空间或独立表空间。MyISAM 不存在表空间一说，所有的数据和索引都是存储在数据库对应的数据目录下的。

innodb支持行级锁、外键、事务，myisam不支持（MyISAM 引擎不支持数据库事务，所以在 MyISAM 中没有 redo 日志功能）。

5.内连接与外连接的主要区别

内连接

驱动表（JOIN 关键字前面的表，这里是 pages）按照查询条件匹配的结果集在被驱动表（JOIN 关键字后面的表，这里是 users）找不到对应的匹配记录，则对应的记录不会出现在最终结果集中；

外连接

左连接

只要驱动表（LEFT JOIN 前面的表）按照查询条件匹配到结果，则对应记录就会出现在最终结果集中，如果被驱动表（LEFT JOIN 后面的表，可能有多个）匹配记录为空，则会以 NULL 填充；

右连接

与左连接相反，RIGHT JOIN 之后的表是驱动表，之前的表是被驱动表，不管被驱动表是否匹配到记录，只要驱动表匹配到记录，就会出现在最终结果集中，被驱动表为空的话也以 NULL 填充。

笛卡尔积

只要驱动表或被驱动表任意一个匹配到数据就出现在结果集中，为空的以null填充。

（日常开发中，使用左连接的场景最多，即使是右连接，也会转化为左连接来实现，因为左连接更符合人类的思维模式）

6.sql优化技巧

使用与业务无关的自增列做主键

即innodb默认的id列，它可以让你的页近乎被顺序填满，且这一列因为与你业务无关，通常不需要update，就减少移动开销（这是电话号、身份证做主键的直接劣势），减少B+树的分裂，并且查找速度快，占用空间小，这会使得二级索引占的空间也减小。

尽量减少不必要、不合理索引

维护索引是需要代价的，不仅占用空间更会减慢增删改的速度，而一些基数值很小（区分度不高）的、不常用的、后期弃用的索引，是时候删掉了。

随时explain、show index

随时查看语句是否按自己预期命中索引，不要想当然，在任何时候都是适用的。mysql还支持隐藏索引，alter table 表名 alter index  索引名 invisible/visible;隐藏/展示。

7.为什么索引主键推荐用自增id，而不是uuid？

整数更节省空间

相比uuid，整数占字节更少，由于二级索引存放的是主键的值，主键占字节少会成倍节约空间。

查找方便、区间查询

相比字符串依次对比查找，按整数查找单个值、区间查询都非常方便

插入位置确定、避免B+树的频繁合并和分裂

自增主键的插入位置固定在最右，避免了uuid根据字符串排序插入频繁的合并和分裂B+树

8.innodb引擎的特性有哪些？

插入缓冲

对于非唯一索引的插入和更新，会先判断插入的非聚集索引页是否在缓冲池中，如果在则直接插入；若不在，则先放到Insert Buffer（后优化为change buffer） 中，再按照一定的频率进行合并操作后写回disk。这样通常能将多个插入合并到一个操作中，目的还是为了减少随机IO带来性能损耗。

二次写

在应用（apply）重做日志前，用户需要一个页的副本，当写入失效发生时，先通过页的副本来还原该页，再进行重做，这就是double write。

刷新临接页（预读）

当把某页数据读到内存后，并不着急释放，之后读数据时如果在内存中找到要读的数据就可以直接返回，无需再从B+树上查找。

（页内部采用二分查找）

比如select * from user where id<=5 如果找到id=1的数据符合要求，会将其所在页整个加载到内存， 一页有16Kb(默认，可以调)，一条数据假如100b，那么一页可以加载16条数据，<=5的5条一起加载到内存，不必再在B+树上找了

自适应哈希索引

Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，就对其建立哈希索引以带来速度的提升。

9.为什么（innodb、myisam等都）使用B+树做索引？

I/O较少

与B树相比，B+树的根节点不存放数据，只有叶子节点存放数据（myisam是数据所在页码），相同数据量，B+树会更矮，这会让I/O减少。

适合区间、遍历

mysql使用的B+树叶子节点互相有双指针指向前后数据，即双向循环链表。这非常适合做区间查询和遍历。

缓存命中率高

B+树与B树常常把整页的数据一次读入内存，这样下次读本页上的数据行就可以直接在内存中读取，这就是预读机制，因而缓存命中率相对较高，这也会让I/O减少。

增删改维护成本低

B+树是平衡查找树，虽然查找和最优二叉树有一点点差距，但与最优二叉树相比，增删改的维护成本很低。

10.聚集索引、非聚集索引区别？

B+树索引可以分为聚集索引和非聚集索引两种。

聚集索引（聚簇索引）

每张表只能按某个键构造B+树，这个索引的叶子节点存放了完整行数据（溢出除外），这个键就是其聚簇索引，它是唯一的，Innodb中，聚簇索引默认就是主键索引。

（聚集索引是逻辑上连续的，而不是物理上连续）

二级索引（非聚集索引、辅助索引）

每张innodb表除了聚集索引，其他唯一、普通、联合索引都是二级索引，他们内部也是B+树结构，叶子节点存放的都是聚集索引的值，需要通过聚集索引的值再查询行数据，所以叫“二级索引”，myisam主键存放的是指向完整行数据所在页的指针，所以它也是非聚集索引，myisam没有聚集索引。

11.redo log 、undo log、bin log区别

	redo log（重做日志）	undo log（回滚日志）	bin log（二进制日志）
产生	innodb引擎在事务执行过程中产生 每个事务可产生多条 undo本身页会产生redo log	innodb在事务执行过程中产生 每个事务可产生多条	mysql数据库在事务提交时产生，所有的存储引擎都可以共用它 每个事务产生1条
内容	物理日志，记录对页的更新操作	逻辑日志，记录一条数据在当前操作前的状态	逻辑日志，记录更新语句sql或被修改的行
用途	前滚日志，事务执行完成后写入redo log即表示执行成功，之后按计划写入磁盘，可以每秒或者按系统设定写入，这就是“日志先行”（WAL）技术，这是为了提高写入效率。 如果 MySQL 数据库服务端发生异常崩溃，重启时可以根据这个日志记录的步骤完成事务已提交但未持久化到磁盘的数据更新操作，从而保证事务的持久性。	回滚操作 快照读，支持MVCC，通过版本链在不加锁的情况下保证事务的原子性、一致性	恢复数据 主从架构
写入	循环写，固定大小，写满后擦除（官方建议redo log配置尽量大一些，哪怕导致恢复时间较长，但是会减少很多磁盘写入） 可以设置每次操作写入磁盘、每秒写入磁盘、根据系统设定写入磁盘 文件大小可设置	用户可以设置存储在独立表空间或在共享表空间文件中，也需要回收 文件大小可设置	追加写，不会覆盖 可以设置每次操作写入磁盘、每N次操作写入磁盘
位置	磁盘单独的日志文件	存放在表空间，undo段	磁盘单独的日志文件

除此之外，mysql还有错误日志、慢查询日志

12.bin log日志有哪些记录格式？

	row(行记录，默认格式)	statement(语句记录)	mixed(混合记录)
记录方式	记录所有对数据行的改变	记录执行的语句	MySQL 底层会判断一条 SQL 语句是否会导致主备数据不一致，如果不会则以 STATEMENT 格式记录，否则以 ROW 格式记录
优势	所有的改变都可以写入到日志，这也是最安全的方式 可以用来做数据恢复	记录语句较少
缺点	通常来说写入量更大，日志文件在写入日志的时候会被锁住，如果数据太多可能会导致性能问题（可以设置 binlog_row_image=minimal 来减少这个缺点）	有些语句在不同服务器执行效果不同，比如删除带limit的语句会根据选择的索引删除不同的数据，可能造成主从不一致等问题

13.mysql主从复制不一致可能的原因和处理方法有哪些？

可能的原因

网络原因

主从复制需要通过网络传输binlog文件，网络延迟较大时会造成主从同步不及时的情况

负载不同

如果主服务器写入量较大或从主机读压力过大，都会使得延迟更明显

设置原因

比如主数据库上面设置的max_allowed_packet比从数据库大，当一个大的sql语句，能在主数据库上面执行完毕，从数据库上面设置过小，无法执行，导致的主从不一致。

异常宕机

为了性能考虑很多时候并不是把bin log、redo log落盘操作设定为1即每次执行完成都落盘，这会导致重启后丢失一些操作的数据。

版本不同

尤其当主库为高版本，从库为低版本时，主库可以支持的操作从库不支持，就会造成不一致的情况。

解决方案

数据差异不是特别大：直接更新从库数据

预防措施

master:innodb_flush_log_at_trx_commit=1&sync_binlog=1（redo log、bin log 双1）
slave:master_info_repository="TABLE"&relay_log_info_repository="TABLE"&relay_log_recovery=1
设置从库库为只读模式
可以使用5.7增强半同步避免数据丢失等
binlog row格式
必须引定期的数据校验机制

14.mysql主从延迟因素哪些？如何解决？

因素

硬件配置较低

网络延迟大

大事物操作

优化方案

提升从库配置

不必和主库完全一致，但性能也不能太差

增加从库的数量

尽量避免在业务高峰期进行大事务操作

配置从库支持并行复制

slave-parallel-type配置为LOGICAL_CLOCK，可以利用数据库事务 redo log 组提交的特性（同一组提交的事务可以分发到不同的进程并发执行），实现模拟主库的并行模式，从而大大提升从库并行复制的能力

实时性要求较高的查询强制读主库

对于基于 GTID 协议实现的主从复制，还可以通过 GTID 相关字段确保主从无延迟

这种也不是完全准备

15.怎样通过主备架构实现高可用？

为了更好地兼顾系统可用性和高负载，我们可以设计一主一备多从的数据库集群架构，有时候，为了降低成本，如果系统写负载压力不大的话，我们会让备库也承担读请求，这个时候，除了指定主库宕机后升级为主库的备库外，一主一备多从似乎和一主多从也没啥区别。

主备关系的切换

可靠性优先

在主备切换过程中不会有数据丢失，数据是安全的，但是存在数据库无法写入新数据的服务不可用阶段，这个时间的长短取决于主从延迟的时间，主从延迟时间越短，服务不可用时间越短

可用性优化

在主备切换过程中不影响数据库服务的读写操作，但是缺点是可能造成数据的丢失

这种手动切换在线上是不能接受的，一般会选择中间件，在中间件这一层，可以做很多工作，比如读写分离、主从切换、SQL 安全校验、负载均衡、分库分表、在线扩容等很多工作，如mycat、kingshard，在中间件这一层，可以做很多工作，比如读写分离、分片、主从自动切换、SQL 安全校验、负载均衡、分库分表、在线扩容等很多工作。

这是代理型中间件工作原理

 

 

8.explain

EXPLAIN语句提供有关MySQL如何执行语句的信息