MySQL高频面试题

InnoDB引擎的三大特点，四大特性

三大特点：

1. 支持事务安全

2. 支持行锁

3. 支持外键

四大特性：

• 插入缓冲（insert buffer)

• 二次写(double write)

• 自适应哈希索引(ahi)

• 预读(read ahead)

什么是脏读？幻读？不可重复读？

• 脏读(Dirty Read)：一个事务读到另一个事务还未提交的数据

• 不可重复读(Non-repeatable read)：一个事务先后读取同一条记录，但是两次读取的数据不相同

• 幻读(Phantom Read)：一个事务按条件查询数据时，并没有对应的数据行；但是在插入数据时又发现这行数据已经存在

事务的隔离级别

隔离级别	脏读	不可重复读	幻读
read uncommitted(读未提交)	√	√	√
read committed(读已提交)	×	√	√
repeatable read（mysql默认）	×	×	√
serializable（可串行化）	×	×	×

*事务的四大特性ACID

• 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败

• 一致性：事务完成时，必须使所有的数据都保持一致状态

• 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

• 持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

锁有哪些

MySQL中的锁，可以分为三类：

• 全局锁：锁定数据库中所有的表

• 表级锁：每次操作锁住整张表

• 行级锁：每次操作锁住对应的行数据

全局锁

全局锁就是对整个数据库实例进行加锁，加锁后整个实例就处于只读状态.

表锁

表锁每次操作锁住整张锁，很容易发生锁冲突，表锁又分为表锁,元数据锁，意向锁；

1. 表锁

   表共享读锁：都可以读表

   表独占写锁：只有自己可以读写

元数据锁MDL

元数据锁是在加锁过程中系统自动控制，在访问一张表时会自动加上，MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务时，不可以对元数据进行写入操作，避免DML和DDL冲突。

意向锁

为了解决DML在执行时加的行锁与表锁的冲突，加入意向锁使加表锁时不需要检测每行数据是否加锁

1. 意向共享锁：与表锁共享锁兼容，与表锁排它锁互斥

2. 意向排它锁：与表锁共享锁和排它锁都互斥

行级锁

每次操作锁住对应的行数据，锁定力度最小，发生锁冲突概率最低，并发度最高

1. 共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁。

2. 排它锁：允许获取排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁

explain 属性

获取MySQL如何执行select语句的信息，包含在select语句执行中表如何连接和连接的顺序 ，性能分析

explain 各字段的含义

1. ID：查询的序列号，表示了查询中执行select子句或者操作表的顺序；ID相同自上而下，ID不同 值越大越先执行

2. select_type：表示select的类型，常见的取值有simple（简单表），primary（主查询），union（union中第二个或更后边的查询语句），subquery（select/where之后包含了子查询）;

3. type：表示连接类型 由好到差：null-system-const-eq_ref-ref-range-index-all

4. possible_key：表示可能用到的索引

5. key：实际用到的索引

6. key_len：表示索引中使用的字节数，该值为最大可能的长度并非实际使用长度

7. rows： MySQL认为必须要执行查询的行数

8. filtered：返回结果的行数占需读取行数的百分比，越大越好

索引有哪些

分类	含义	特点
聚集索引	索引结构的叶子节点保存行数据	有且只有一个
二级索引	索引结构的叶子节点关联对应的主键	可多个

分类	含义	特点	关键字
主键索引	针对表中主键创建的索引	默认创建，唯一	primary
唯一索引	避免同一个表中某数据列中的值重复	可多个	unique
常规索引	快速定位特殊数据	可多个
全文索引	全文索引查找的是文本中的关键字	可多个	fulltext

Hash，B-tree，B+tree的优点

Hash索引底层就是hash表，进行查找是调用一次hash函数就可以获取到相应的键值，B+树底层实现的是多路平衡二叉树，每次查找都是从根节点出发查找到叶子节点方可获的所查数据。

• hash索引进行等值查询更快(一般情况下)，但是却无法进行范围查询。

• B树可以在内部节点同时存储键和值，因此，把频繁访问的数据放在靠近根节点的地方将会大大提高热点数据的查询效率。这种特性使得B树在特定数据重复多次查询的场景中更加高效。

• B树只适合随机检索，B+树同时支持随机检索和顺序检索。由于B+树的内部节点只存放键，不存放值，因此，一次读取，可以在内存页中获取更多的键，有利于更快地缩小查找范围。 而且 B+树的叶节点由一条链相连 全数据遍历时效率更高

覆盖索引

查询使用了索引，在该索引中已经全部能够找到， 而不需要从聚集索引中查询。

尽量使用覆盖索引减少select*

覆盖索引的好处

• 减少IO操作 当查询某字段时，需要先查询辅助索引，再查询聚集索引。如果在辅助索引中就找到查询结果，就不需要查询聚集索引，这样明显减少IO操作。而且辅助索引的叶子结点不包含行记录的所有数据，也可以减少IO操作。

• 有利于统计 对于select count(*) from users这个查询表记录数的sql，通过遍历聚集索引和辅助索引都可以得到结果，但优化器会选择辅助索引，因为辅助索引需要的IO操作少于聚集索引。

创建索引的原则

1. 针对于数据量较大，且查询比较频繁的表建立索引

2. 针对于常作为查询添加，排序，分组操作的字段建立索引

3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高 使用效率越高

4. 如果是字符串类型的字段，字段长度较长，可以针对字段的特点建立前缀索引

5. 尽量使用联合索引，减少单列索引

6. 要控制索引的数量，索引越多维护索引的代价也越大

7. 如果索引列不能存储Null值，请在创建表的时候使用Not Null约束它

索引失败的情况

最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则。

即查询从索引的最左列开始，并且不跳过索引中的列；若跳过某一列，索引将部分失效（缺失部分的后边）。

范围查询

联合索引中，出现范围查询（> <），范围查询右侧的列索引失效

索引列运算

不要在索引列上进行运算操作，否则索引会失效

字符串不加引号

字符串类型字段使用时不加引号，索引也会失效

模糊查询

如果是尾部模糊匹配索引不会失效，如果是头部模糊查询 索引会失效

or连接条件

如果or在前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

数据分布影响

如果MySQL评估使用索引比全表更慢，则不使用索引

SQL优化

在插入数据时，多条数据批量操作并且手动提交事务，大批量插入数据时使用MySQL数据库提供的load指令进行插入

对主键进行优化：主键存在页分裂和页合并

• 页分裂：主键乱序插入可能导致

• 页合并：删除记录时不是直接物理删除而是被标记

在满足业务的情况下尽量降低主键长度，插入时尽量顺序插入，避免对逐渐地修改

排序字段建立合适的索引，尽量使用覆盖索引；

尽量根据主键/索引字段进行数据更新

InnoDB存储结构

逻辑存储架构

表空间：一个mysql实例可以对应多个表空间，用于存储记录，索引等

段：分为数据段，索引段，回滚段；数据段就是B+ 树的叶子节点，索引段就是B+树的非叶子节点。段用来管理多个区

区：表空间的单元结构，默认情况下一个区中有64个连续的页

页：存储引擎磁盘管理的最小单元

行：数据按行存储

内存架构

Buffer pool：缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据，然后再以一定的频率刷新到磁盘，从而减少磁盘IO

缓冲池以页为单位，底层采用链表管理

Change Buffer：更改缓冲区，在执行DML语句时，如果这些数据没在缓冲池中就不会直接操作磁盘而是会将数据变更存在更改缓冲区，在未来数据被读取时，再将数据合并恢复到缓冲池中，这样可以减少磁盘IO

自适应Hash索引：用于优化缓冲池的数据查询，无需人工干预，是系统根据情况自动完成

Log Buffer:日志缓冲区，用来保存要写入到磁盘中Log日志数据，会定期将日志刷新到磁盘中

磁盘结构

系统表空间：更改缓冲区的存储空间

文件表空间：包含单个表的数据和索引

通用表空间：需要自己创建

撤销表空间：MySQL实例在初始化阶段会自动创建两个默认的undo表空间 用于存储undo log日志

临时表空间：InnoDB使用会话临时表空间和全局临时表空间存储用户创建的临时表

MVCC-多版本并发控制

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

• 快照读：简单的select(不加锁) 就是快照读，读取的是记录数据的可见版本，有可能是历史数据

隐式字段

• DB_TRX_ID 最近修改事务 ID：记录创建这条记录/最后一次修改该记录的事务 ID

• DB_ROLL_PTR 回滚指针，指向这条记录的上一个版本,用于配个undo log指向上一个版本

• DB_ROW_ID 隐含的自增 ID（隐藏主键），如果数据表没有主键将会生成该隐藏字段

undo log

回滚日志，在插入删除更新时，产生便于数据回滚的日志，

在insert时 产生的undo log只在回滚的时候需要，事务提交后就立刻删除

在update，delete时，产生的undo log不仅在回滚时需要，在快照读的时候也需要，不会被立刻删除

readview

读视图是快照读执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id

字段	含义
m_ids	当前活跃的事务集合
min_trx_id	最小活跃事务ID
max_trx_id	预分配事务ID，当前最大事务ID+1
creator_trx_id	ReadView创建这的事务ID