MYSQL经典面试题

什么是索引

索引（Index）是帮助MySQL高效获取数据的数据结构

为什么使用索引

数据是存储在磁盘上的，如果没有索引，查询数据时就需要将所有的数据都加在到内存中，依次检索。且CPU一次加载的数据量是有限的，这样就需要多次IO,开销比较大。使用索引后，数据是按索引进行排序的，当通过索引字段进行排序或范围查找速度就比较快。且索引会按照一定的数据结构组织，比如innodb使用B+树存储索引，一般2~3层的B+树就可以存储8-10亿的数据，这样一般就只需2-3次的IO，较少了IO开销，同时查询数据时可按B+树的二分搜索快速检索的数据。

索引优缺点

优点：
1)提高数据查询效率，降低数据库IO成本;
2)通过索引列对数据进行排序，加快数据排序分组的速度，降低了CPU消耗
3)加快表与表之间的连接
缺点：
1)索引也是一张表，需要占用磁盘空间;
2)增删改数据时需要维护索引，影响效率

索引有哪些

主键索引： 设定主键后数据库自动为其建立的索引，innodb为聚簇索引

如果不指定主键的话，则会查看表中是否存在非空的唯一列，如果存在将此列作为主键。如果不存在，创建row_id作为主键

单值索引（单列索引 | 普通索引）： 除了主键以外，为表中的其他字段创建的索引
唯一索引： 索引列的值必须唯一，但允许有空值（主键索引和唯一索引的区别：主键索引不能为空，唯一索引可以存在多个null）

				*NULL 的定义 ，是指未知值。 所以多个NULL ，都是未知的，不能说它们是相等的，也不能说是不等，就是未知的。所以多个NULL的存在是不违反唯一约束的。*

复合索引： 基于表中的多个列共同创建的索引

MYISAM存储引擎还支持全文索引

适合 | 不适合创建索引的情况

1) 适合创建索引的情况

• 主键自动建立唯一索引
• 经常查询的字段
• 查询中经常用于连接的字段，通过外键关系建立索引，加快连接速度
• 经常需要排序的字段建立索引，因为索引已经排好序，可以加快排序查询速度
• 查询中统计或者分组字段

2)不适合创建索引的情况

• 表记录太少
• 经常增删改的表或者字段
• Where 条件里用不到的字段
• 区分度不高的字段不适合建立索引，如性别等
• 参与列计算的列不适合建索引

B树 | B+树

B树： 所有数据分散在整颗数据中，每个节点都存储key和data,并且叶子节点指针为null
B+树： 非叶子节点只存储指向孩子节点的指针和索引，所有数据都存储在叶子节点中，叶子节点包含了这棵树的所有键值，同时叶子节点通过指针指向上一节点和下一节点，构成双向的循环链表。

为什么使用B+树

数组、链表查询数据时需要挨个遍历，速度慢，而二分查找树，红黑树每个节点存储只能存储一条记录，存储数据有限，也不考虑，所以在红黑树基础上做了改进使用B树存储更多的数据，但是B树由于每个节点都存储索引和数据，一个节点存储的数据是有限的，这就导致B树需要增加高度来保证存储更多的数据，CPU是按页加载数据到内存的，B树高度的增加就导致IO的次数增多，时间开销大。同时它的所有数据都分布在整棵树上，不适合范围查找和排序。

所以又在B树基础上做了改进得到B+树，B+树的非叶子节点只存储索引，所有的索引和数据都存储在叶子节点，由于非叶子节点只存储索引，这样一个节点就可以存储更多的数据，减少树的高度，减少IO次数，一般2-3层的B+树就可以存储8-10亿的数据量。

数据是按索引排好序的存放在叶子节点，同时叶子节点之前使用双向链表连接，范围查找，按索引列进行排序时速度很快。

同时B+树的性能更稳定，每次搜索都是从根节点开始，叶子节点结束。

MyISAM | InnoDB

1、MySAM是非事务安全的，而InnoDB是事务安全的
2、MyISAM锁的粒度是表级的，而InnoDB支持行级锁
3、MyISAM支持全文类型索引，而InnoDB不支持全文索引
4、

• MyISAM管理非事务表，提供高速存储和检索以及全文搜索能力，如果在应用中执行大量select操作可选择
• InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，可选择。
  5、
• MyISAM 和InnoDB都使用B+树实现索引，但是MyISAM是非聚簇索引，索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。检索数据时首先按照B+Tree搜索算法在对应索引表中搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，到内存中读取相应数据记录。
• InnoDB使用聚簇索引，数据按主键聚集，数据和索引放在同一个文件文件中。检索数据时首先按照B+Tree搜索算法找到对应叶子节点上存储数据即可。
  6、
• InnoDB的所有辅助索引都引用主键作为data域，通过辅助索引检索时**首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
• MyISAM辅助索引和主索引树结构相似，叶子节点都保存记录的真实地址。通过辅助索引检索时无需访问主键的索引树

什么情况索引失效?

• 在进行最左前缀比配时，中间有个索引列使用的是范围查询，则会导致后面的索引列失效
• 模糊查询若以%开头，使用 !=，is null,is not null，or会变成全文索引
• 对索引列计算–失效
• 字符串不加单引号会查询时需要类型转换–失效
• 查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

MySQL主键基本int型，而不推荐用UUID作为主键原因？

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。UUID数据很离散，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整（插入删除新的数据记录会破坏B-Tree的性质，因此在插入删除时，需要对树进行一个分裂、合并、转移等操作以保持B-Tree性质），十分低效，如果是int自增的，不仅方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。

什么不建议使用过长的字段作为主键？

因为所有辅助索引都引用主索引，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

数据库的底层实现总结（索引底层实现）：

数据库使用B+树来组织索引，将主键组织到一棵B+树中，而所有数据基于主键排好序储存在叶子节点上，叶子节点之间使用双向指针连接，方便范围查询和按主键排序。非叶子节点只存储键值，所有数据按页存储，数据一默认一页的大小为16KB,就B+树这个数据结构而言，一个3层的B+树存储的数据是8亿-10亿左右，且顶层常驻内存，一般2~3次IO就可将想要检索的数据加载到内存中。

不同存储引擎对索引实现略有差别，MySIAM和InnoDB都是用B+树实现索引，但MySIAM使用非聚簇索引，主索引和辅助索引叶子节点都存储数据真实地址，而InnoDB主键索引叶子节点存储数据，所有辅助索引都引用主键作为data域。

所以InnoDB通过辅助索引检索时：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。而MyISAM由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

如何捕获慢SQL

1.观察。至少跑一天，看看生产的慢SQL情况

2.开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL,并将它抓取出来

3.explain+慢SQL优化

4.show profile:可用于sql的调优的测量，查询SQL执行的资源消耗情况和生命周期情况，

5.进行SQL数据库服务器的参数调优

数据库优化

• 选择正确的数据库引擎myISM（提供高速存储和检索以及全文搜索能力)，INNODB（支持事务，具有ACID属性，可应用于频繁insert,update的场景）
• 根据服务层面：配置mysql性能优化参数；
• 从系统层面增强mysql的性能：优化数据表结构、字段类型(尽量使用小的数据类型)，为合适的字段建立索引，尽量每张表都有主键id
• 明确的字段使用enum(性别，国家，省市)：查询速度块
• 从数据库层面增强性能：优化SQL语句，合理使用字段索引，如覆盖索引，最左前缀匹配等。避免会是数据库索引失效而引起全表扫描的情况。避免使用select*(查询字段越多，速度就越慢，且数据多对网络的传输也会负债过重)
• 使用小表驱动大表

  如in,exists中
  in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表大多需要查询，不可避免
  exits是对外表做loop循环，每次loop循环在对内表（子查询）进行查询，那么因为对内表查询使用索引（内表的效率高。故可用大表），而外表有多大都需要遍历，不可避免

• 不常使用的数据迁移备份，避免每次都在海量数据中去检索。
• 分表，分库、读写分离等等。
• 使用缓存优化查询（进行多次相同的查询，结果就会被放入缓存中，后续再进行同样的查询，就直接从缓存中提取，不会到表中提取）
• 使用explain 、show profile 乐意查看sql是怎么运行的，怎么处理的，帮助我们分析sql的瓶颈
• 代码层面增强性能：使用缓存和NoSQL数据库方式存储，如MongoDB/Memcached/Redis来缓解高并发下数据库查询的压力。

事务

一条或多条sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行

事务四大特性-ACID

**原子性:**事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

**一致性：**事务执行之前之后都必须处于一致状态。比如a与b账户共有1000块，两人之间转账之后无论成功还是失败，它们的账户总和还是1000。

隔离性：并发执行的各个事务之间不能互相干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的。

**持久性：**一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

常见并发问题

脏读: 一个事务读取了另一个事务没有提交的数据

幻读: 一个事务读取某个范围内的数据时，另一个事务插入了新的纪录，导致当前事务读出多的数据

**不可重复读:**一个 事务多次读取某行数据的过程中，另一个事务修改了数据并提交了，导致当前事务读出的不同的数据

隔离级别

READ UNCOMMITTED(读未提交数据)：允许事务读取未被其他事务提交的变更，脏读，不可重复读和幻读的问题都会出现

READ COMMITTED(读已提交的数据)：只允许事务读取已经被其他事务提交的变更【避免了脏读】
可以避免脏读，但不可重复读和幻读问题任然存在

REPEATABLE READ(可重复读)：确保事务可以多次从一个字段中读取相同的值，在这份事务持续期间，禁止其他事务对这个字段进行更新，可以避免脏读和不重复，但幻读的问题任然存在

SERIALIZABLE (串行化)：确保事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表执行插入，更新和删除操作，所有并发问题都可以避免，但性能十分低下

Mysql 默认的事务隔离级别为: REPEATABLE READ

MVCC多版本并发控制

数据库在并发操作下，读写，写写有数据安全问题，读写会出现脏读，幻读，不可重复读，写写会出现数据更新丢失问题。

数据库采用悲观锁是可以解决以上问题的，但是效率低，所以就有了MVCC。通过维护记录的各个版本，读取当前事务可见的版本。

MVCC的实现原理是依靠记录中的3个隐含字段、undo log日志、Read View来实现的。

DB_TRX_ID：当最近修改(修改/插入)事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID

**DB_ROLL_PTR：**回滚指针,指向这条记录的上一个版本

**DB_ROW_ID:**隐含的自增ID,如果数据表没有主键，InnoDB 会自动以DB_ROW_ID产生一个聚簇索引

实际还有一个删除 flag 隐藏字段, 既记录被更新或删除并不代表真的删除，而是删除 flag 变了

udolog中维护了一条版本链，保存了数据的各个历史版本，每条记录他除了包括数据，还有两个隐藏字段，产生这条记录的事务id,回滚指针，指向上一版本。

当执行的是普通select操作时会产生一个快照读视图，然后通过可见性算法比较是读最新数据还是历史版本，

版本未提交，不可见
版本已提交，但却是在视图创建后提交的，不可见：如 BC 之于 A
版本已提交，且是在视图创建前提交的，可见

RR（可重复读）、和RC（提交读）都是通过MVCC实现的，不同就是差在快照读时
前者创建一个快照和Read View并且下次快照读时使用的还是同一个Read View所以其他事务修改数据对他是不可见的、解决了不可重复读问题。
后者则是每次快照读时都会产生新的快照和Read View、所以就会产生不可重复读问题。

当前读 | 快照读

当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁

select lock in share mode (共享锁), select for update; update; insert; delete (排他锁)

快照读：读取的是记录的历史版本。快照读的实现是基于多版本并发控制，即 MVCC ,可避免了加锁操作，降低了开销，提高了并发访问的性能

不加锁的select

MYSQL左连接、右连接、内连接的区别

左连接：在 LEFT JOIN 左边的表里面数据全被全部查出来，右边的数据只会查出符合ON后面的符合条件的数据，不符合的会用NULL代替。

右连接：与 LEFT JOIN 正好相反，右边的数据会会全部查出来，左边只会查出ON后面符合条件的数据，不符合的会用NULL代替。

内连接：相当于左连接与右连接的合并，去掉所有含NULL的数据行，剩下的就是查询出来的数据了。其实就是两边的表都必须满足条件。

主从复制

主从复制解决的问题：主机宕机，造成数据丢失。

• 主数据库出现问题，可以切换到从数据库
• 可以进行数据库层面的读写分离
• 可以在从数据库上进行日常备份

将主数据库中的DDL和DML操作通过二进制日志传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

过程：主机会将每次操作记录到binlog中，备份时从机会开启一个线程，与主机建立连接，它通知主服务器从服务器在日志中读取的最后一次成功更新的位置，将binlog中的数据备份到中继日志中，从机读取执行中继日志，从而实现主从机数据同步。

三范式

范式：数据库表结构符合某种设计标准

1nf:所有属性都是不可分割的（数据冗余，增删改异常）

2nf:消除非主属性对码的部分函数依赖（减少了数据冗余，增删改异常）

3nf:消除了非主属性对于码的传递函数依赖（减少数据冗余，无增删改异常）

limit

limit是mysql的语法
select * from table limit m,n
其中m是指记录开始的index，从0开始，表示第一条记录
n是指从第m+1条开始，取n条。

mysql日志

binlog:二进制日志，存储在mysql的server层，在mysql中默认不开启，可以数据同步和恢复

undolog:回滚日志，MVCC时使用

redolog:前滚日志【undolog和redolog都是存储在innodb存储引擎中】，记录mysql的数据增删改操作

errorlog:错误日志，在mysql执行过程中，sql出错会记录到sql中

slowlog:慢日志，当执行时间超过某个限定值时，会记录该sql语句

relaylog:中继日志，主从复制时会用到

binlog,redolog二阶段提交

binlog,redolog都会记录数据，记录顺序是什么样的？

执行流程：
1、执行器先从引擎中找到数据，如果在内存中直接返回，如果不在内存中，查询后返回
2、执行器拿到数据之后会先修改数据,然后调用引擎接口重新写入数据
3、.引擎将数据更新到内存，同时写数据到redo中，此时处于prepare阶段，并通知执行器执行完成，随时可以操作
4、执行器生成这个操作的binlog
5、执行器调用引擎的事务提交接口，引擎把刚刚写完的redo改成commit状态，更新完成

MYSQL锁机制

MyISAM在执行查询语句(SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。MySQL的表级锁有两种模式:
表共享读锁(Table Read Lock),表独占写锁(Table Write Lock)

Innodb存储引擎由于实现了行级锁定，每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，此时 就会升级为表锁。

**行锁（Record Lock）：

• （行级）共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。
• （行级）排他锁（X）：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁。

sql语句中：
insert update delete 增删改 加的排它锁 是自动加锁
select … for update 加的排他锁

select 不加任何锁
select … lock in share mode 加的共享锁

select … lock in share mode 加的行级锁-行锁-共享锁 和 表级锁-意向锁-意向共享锁
insert update delete select … for update 加行级锁-行锁-排它锁 和 表级锁-意向锁-意向排它锁

**间隙锁（Gap Lock）：**锁定一个区间的索引记录