mysql面试题

索引常见使用规则？

比如product表，创建索引：

create index (shop_id, product_id, gmt_create)

1. 全列匹配

   where条件正好用到这3个字段，就用到联合索引。

2. 最左前缀匹配

   SQL里，正好用到联合索引最左边的一个或几个列表。

3. 最左前缀匹配，但中间某个值没有匹配

   如果sql用了联合索引第一列和第三列，会按照第一列在索引里找，找完后对结果集根据第三列做全表扫描，不会走第三列的索引了。

4. 前缀匹配

   如果不是等值，或比如=, >, <=等操作，而是Like操作。则只有Like ‘xx%’ (%在最后)这种才能用上索引。

5. 范围匹配

   只有符号最左前缀的列的范围才用到索引，范围之后的列用不到索引。

   比如: select * from product where shop_id >= 1 and product_id = 1；

   这里就只用到shop_id索引。

6. 包含函数

   对某个列用了函数，则该列不走索引。

索引缺点以及使用注意？

 常见缺点：增加磁盘消耗，占用磁盘文件，同时高并发频繁插入和修改索引，会导致性能损耗。

使用注意：1. 尽量创建少的索引，一个表，两三个。

         2. 一个字段的值几乎都不太一样的，比如id，这样用索引效果最好。

小结：互联网系统中，一般尽量降低SQL复杂度，用简单的主键索引(聚簇索引)+少数联合索引，

可以覆盖一个表的所有SQL查询，更复杂的业务逻辑，应该放在Java代码里实现。

SQL越简单，后面迁移，分库分表，读写分离的成本更低，减少对SQL的改造。

MySQL最好用在在线及时存储，不要用于计算(Join, 子查询，函数等等)。高并发场景下，计算放在Java内存里。

MyISAM和InnoDB存储引擎的区别是啥？

myisam：

不支持事务，不支持外键约束，索引文件和数据文件分开，这样在内存里可以缓存更多的索引，对查询的性能会更好，适用于那种少量的插入，大量查询的场景。

innodb：

主要特点就是支持事务，走聚簇索引，强制要求有主键，支持外键约束，高并发、大数据量、高可用等相关成熟的数据库架构，分库分表、读写分离、主备切换，全部都可以基于innodb存储引擎。

Myisam和Innodb存储引擎的索引实现？

myisam的索引中，每个叶子节点的data存放的是数据行的物理地址，每行对应一个物理地址。数据文件单独存放一个文件。最大特点是数据文件和索引文件是分开的。

innodb的数据文件本身就是索引文件，必须有主键，会根据主键建立一个默认索引，叫聚簇索引。
innodb下，对某个非主键字段创建索引，那么最后那个叶子节点的值就是主键的值，再用主键的值到聚簇索引里查找到数据，叫回表。
innodb，建议统一用auto_increment自增值作为主键，可以保持聚簇索引直接加记录，不用修改原来b+树的结构，浪费时间。

Mysql 里面为什么用 B+ 树？

因为哈希范围查询很慢。链表要遍历。剩下的就是树。广为人知的，二叉搜索树，AVL 树，红黑树，B树等等。

二叉搜索树会退化为链表，层数可能也会很多
AVL树层数依然过多
红黑树只是优化了插入、更新，弱化了平衡，在更新和搜索中取了折中。但层数过多的问题没有解决。
B 树，层数变少了，但如果访问下一页需要回到父节点到兄弟节点
B+ 树，将叶子节点用链表串联起来了， 还有就是子节点中包含了父节点的信息，解决了 B 树的问题，B+树范围查询更简便。B+树中序遍历要简单得多。

事务

事务(TRANSACTION) 是作为单个逻辑工作单元执行的一系列操作。

多个操作作为一个整体向系统提交，要么都执行，要么都不执行。

事务是一个不可分割的逻辑单元。

事务特性：

ACID【原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)、持久性(Durability)】

事务并发的几类问题：

脏读：一个事务可以读到另一个事务未提交的结果，所有的并发事务问题都会发生。
不可重复读：在一个事务中多次读取同一个数据时，结果出现不一致。因为另外一个事务在对此记录进行update操作。
幻读：在一个事务中使用相同的 SQL 两次读取，第二次读取到了其他事务新插入的行。
丢失更新：所有事务依次逐个执行，所以可以解决并发事务的所有问题。
事务的隔离级别：

事务的四种隔离级别

读未提交:提供了事务建最小限度的隔离。顾名思义，就是一个事务可以读取另一个未提交事务的数据。上面的问题全都可能出现。
读提交：处于读已提交级别的事务可以看到其他事务对数据的修改。解决脏读问题。
可重复读：在开始读取数据（事务开启）时，不再允许修改操作。解决了脏读，不可重复读和部分幻读问题，仍可能幻读，MySQL 默认采用可重复读隔离级别；通过多版本并发控制 (MVCC) 机制来实现。
串行化：数据库事务的最高隔离级别。在此级别下，事务串行执行。可以避免脏读、不可重复读、幻读等读现象。但是效率低下，耗费数据库性能，一般不推荐使用。

多版本并发控制 (MVCC)

多版本并发控制 (MVCC) 通过保持某个时间点的数据快照来实现。也就是说，无论事务执行多长时间，事务中看到的数据都不会受到其他事务的影响。根据事务的开始时间，每个事务可能在同一时间看到同一表的不同数据。
简单来说，多版本并发控制的思想就是保存数据的历史版本，通过数据行的多版本控制来实现数据库的并发控制。通过这种方式，我们可以比较版本号，以确定是否显示数据，并在不锁定事务隔离效果的情况下读取数据。

事务的实现

事务的 ACID 特性是由关系数据库管理系统来实现的。
DBMS 采用 日志 来保证事务的 原子性、一致性 和 持久性。Mysql通过预写式日志undo.log、redo.log，如果整个事务执行的过程系统崩溃或者断电了，在系统重启的时候，恢复机制会将undo log中未提交的事务进行回滚，保证事务的原子性；而redo log中已提交的事务重做，保证事务的持久性。
DBMS 采用 锁机制 来实现事务的隔离性。当多个事务同时更新数据库中相同的数据时，只允许 持有锁的事务 能更新该数据，其他事务必须等待，直到前一个事务释放了锁，其他事务才有机会更新该数据。

Binlog和Redo Log的一致性问题

通过2PC（2阶段提交方案）解决，具体为：
阶段1：事务提交之前，Redo Log,Undo Log已写入，然后Binlog写入内存，等待刷盘；
阶段2：先刷盘Binlog，然后让InnoDB执行Commit。
以Binlog的刷盘判定一个事务是否被成功提交，让Redo Log 向Binlog靠齐。

乐观锁和悲观锁 + 使用场景

悲观锁：在所有操作前都上锁
乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。
乐观锁适用于写比较少的情况下（多读场景），即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
但如果是多写的情况，一般会经常产生冲突，这就会导致上层应用会不断的进行retry，这样反倒是降低了性能，所以一般多写的场景下用悲观锁就比较合适。

死锁的解决方法

死锁的产生是在这样一种环境中：比如我们有两个进程AB，他们都需要资源1和资源2，当进程A持有资源1，进线程B持有资源2的时候，他们都需要对方手上的进程，而一般操作系统又不允许抢占，这个时候就发生了死锁。

从这个例子中其实可以总结出死锁的几个必要条件：

一个资源只能被一个进程所占有，不能共享
一个线程请求资源失败时，它会等待而不是释放
一个线程在释放资源之前其他进程不能抢夺资源
循环等待
从死锁产生的原因未明可以设计一些方法去避免死锁的发生：

静态分配资源，一开始就把一个进程所需的全部资源都分配给它，但这样会降低资源的使用效率
允许抢占，需要设置进程的不同优先级，高优先级的进程可以抢占低优先级的进程的资源
把资源进行编号，申请资源必须按照资源的编号顺序来申请
如果死锁已经发生了，就需要去解开死锁，其本质思想就是分配资源打破循环等待

可以运行抢占，从一个或多个进程中抢出资源来给其他进程
也可以终止一些进程，来达到释放资源的目的

MySQL主从复制？

主从复制是指将 主数据库（Master）中的 DDL 和 DML 操作通过二进制日志传输到 从数据库（Slave） 上，然后将这些日志重新执行（重做），从而使得从数据库的数据与主数据库保持一致。MySQL 支持单向、异步复制，复制过程中一个服务器充当主服务器，而一个或多个其它服务器充当从服务器。
主从复制的作用有：

当主数据库出现问题时，可以切换到从数据库；
可以进行数据库层面的读写分离，实现负载均衡；
可以在从数据库上进行实时数据备份。
基本原理流程：
MySQL 的主从复制是一个 异步 的复制过程（一般情况下感觉是实时的），数据将从一个 MySQL 数据库（Master）复制到另外一个 MySQL 数据库（Slave），在 Master 与 Slave 之间实现整个主从复制的过程是由三个线程参与完成的，其中有两个线程（SQL 线程和 I/O 线程）在 Slave 端，另外一个线程（ I/O 线程）在 Master 端。
Master 端：打开二进制日志（binlog ）记录功能 —— 记录下所有改变了数据库数据的语句，放进 Master 的 binlog 中；
Slave 端：开启一个 I/O 线程 —— 负责从 Master上拉取 binlog 内容，放进自己的中继日志（Relay log）中；
Slave 端：SQL 执行线程 —— 读取 Relay log，并顺序执行该日志中的 SQL 事件。

三种主从复制的方式：
1）同步复制：所有从机接收到Binlog,才认为事务提交成功；最安全，但性能差，一般不用
2）异步复制：只要主机事务提交成功，就对客户端返回成功；后台线程异步把Binlog同步给从机，然后从机回放。最快，但可能丢失数据；
3）半同步复制：部分从机接收到Binlog，才对客户端返回成功
一般做法是牺牲一致性换取高可用；数据丢失后，人工修复。
为了解决主从复制延迟太大，切换到从机后数据丢失太多的问题，采用了并行回放策略（1.按数据维度并行——两个事务没有操作交集可以并行；2.按事务提交顺序并行——同时提交成功的事务是可以并行的）

主从同步的延迟原因及解决办法？

架构方面
1） 业务的持久层采用分库架构，mysql 服务能力水平扩展，分散压力
2） 单个库读写分离，一主多从，主写读从，分散压力。这样从库比主库压力高，保护主库
3） 服务在业务和DB之间加入 memcache 和 redis 的cache层，降低读的压力
4） 不同业务的 mysql 放在不同的物理机，降低压力
5） 使用比主库更好的硬件设备，Mqsql 压力小，延迟就减少了

硬件方面
1） 采用好服务器，比如 4u 比 2u 性能明显好，2u 比 1u 性能明显好。
2） 存储用 ssd 或者盘阵或者 san，提升随机写的性能。
3） 主从间保证处在同一个交换机下面，并且是万兆环境。

Mysql 主从同步加速

1） sync_binlog 在 Slave 端设置为 0。
2） log-slave-updates 从服务器从主服务器接受的更新日志不计入二进制日志
3） 直接禁用 Slave 的 binlog
4） Slave 端，如果存储引擎是 innodb，innodb_flush_log_at_trx_commit =2
5） 同步参数调整主库是写，对数据安全性较高，比如 sync_binlog=1，innodb_flush_log_at_trx_commit = 1 之类的设置是需要的而 slave 则不需要这么高的数据安全，完全可以将 sync_binlog 设置为 0 或者关闭 binlog，innodb_flushlog 也可以设置为 0 来提高 sql 的执行效率

调优

SQL慢，一般就是没有用索引。看执行计划，有没有走索引。索引是提高数据库性能的常用方法，他可以令数据库服务器比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。

减少使用select * from ，确切查询到指定列名上。
读取适当记录，使用limit。

使用连接（join）来代替子查询（select），连接(JOIN)之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上 需要两个步骤的查询工作。inner join内连接也叫做等值连接一般比较快，left/right join是外链接。

使用事务来执行语句块。保证ACID。换句话说，就是可以保持数据库中的数据的一致性和完整性。事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL语句操作失败，那么Rollback命令就可以把数据库恢复到begin开始之前的状态。

union 和union all的区别

union会进行去重和默认排序

union all 不会 所以union all执行效率快一点

SQL优化

如果是确定且有限的集合时，可以使用in，或者not in。
其他情况使用exist 或者join代替。
尽量设置非空限制，否则空值会影响一些判断以及容易出问题。
索引只能提高查询速度，相反，会降低insert和update的速度，因为有了索引，添加数据时不仅要添加数据表，还要添加索引表，还可能调整索引表中其他数据的位置，因此索引会降低insert语句的速度