MySQL性能调优与架构设计【面试题二】

什么是虚拟生成列？

虚拟生成列又叫GeneratedColumn，是MySQL 5.7引入的新特性，就是数据库中这一列由其他列计算而得。在MySQL 5.7中，支持两种Generated
Column，即Virtual
Generated Column（虚拟生成的列）和Stored
Generated Column（存储生成的列），二者含义如下：

1、Virtual
Generated Column（虚拟生成的列）：不存储该列值，即MySQL只是将这一列的元信息保存在数据字典中，并不会将这一列数据持久化到磁盘上，而是当读取该行时，触发触发器对该列进行计算显示。

2、Stored
Generated Column（存储生成的列）： 存储该列值，即该列值在插入或更新行时进行计算和存储。所以相对于Virtual Column列需要更多的磁盘空间，与Virtual
Column相比并没有优势。因此，MySQL
5.7中，不指定Generated
Column的类型，默认是Virtual
Column

在表中允许Virtual
Column和Stored
Column的混合使用

提高效率：由于mysql在普通索引上加函数会造成索引失效，造成查询性能下降，Generated Column（函数索引）刚好可以解决这个问题，可以在Generated Column加上索引来提高效率。但是不能建立虚拟列和真实列的联合索引，同时虚拟列是不允许创建主键索引和全文索引。

创建虚拟生成列的语法：

CREATE TABLE triangle (

a double DEFAULT NULL,

b double DEFAULT NULL,

sidec double GENERATED
ALWAYS AS (SQRT(a * a + b * b))

) ;

alter table triangle add column sided tinyint(1) generated always as
(a*b) virtual;

请说下事务的基本特性

事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。

原子性指的是一个事务中的操作要么全部成功，要么全部失败。

一致性指的是数据库总是从一个一致性的状态转换到另外一个一致性的状态。比如A转账给B100块钱，假设中间sql执行过程中系统崩溃A也不会损失100块，因为事务没有提交，修改也就不会保存到数据库。

隔离性指的是一个事务的修改在最终提交前，对其他事务是不可见的。

持久性指的是一旦事务提交，所做的修改就会永久保存到数据库中。

事务并发可能引发什么问题？

当一个事务读取到了另外一个事务修改但未提交的数据，被称为脏读。

当事务内相同的记录被检索两次，且两次得到的结果不同时，此现象称为不可重复读。

在事务执行过程中，事务2将新记录添加到正在读取的事务1中，导致事务1按照某个相同条件多次读取记录时，后读取时读到了之前没有读到的记录，发生幻读。

事务2中是删除了符合的记录而不是插入新记录，那事务1中之后再根据条件读取的记录变少了，在MySQL中这种现象不属于幻读，相当于对每一条记录都发生了不可重复读的现象。

请描述下MySQL中InnoDB支持的四种事务隔离和区别

read uncommitted：未提交读，可能发生脏读、不可重复读和幻读问题。

read committed：提交读，可能发生不可重复读和幻读问题，但是不会发生脏读问题。

repeatable read：可重复读，在SQL标准中可能发生幻读问题，但是不会发生脏读和不可重复读的问题，但是MySQL通过MVCC基本解决了幻读问题。这也是MySQL的缺省隔离级别。

serializable：串行化读，脏读、不可重复读和幻读问题都不会发生。

MySQL有哪些索引类型

从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）；

从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）；

从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一索引等等。

简单描述MySQL各个索引的区别

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。

普通索引(由关键字KEY或INDEX定义的索引)的唯一任务是加快对数据的访问速度。

普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。

主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。

索引可以覆盖多个数据列，如像INDEX(columnA, columnB)索引，这就是联合索引。

MySQL的索引对数据库的性能有什么影响

索引（Index）是帮助MySQL高效获取数据的数据结构，所以索引可以极大的提高数据的查询速度。

但是每建立一个索引都要为它建立一棵B+树，一棵很大的B+树由许多数据页组成会占据很多的存储空间。

而且每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引，同时这些操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影响。

为什么MySQL的索引要使用B+树而不是B树？

答案见下一小节

InnoDB一棵B+树可以存放多少行数据？

当然在实际的数据库中，一个节点可以存储的数据可以很多，为什么？

计算机在存储数据的时候，有最小存储单元，这就好比我们今天进行现金的流通最小单位是一毛。在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是 4k，而对于我们的 InnoDB 存储引擎也有自己的最小储存单元——页（Page），一个页的大小是 16K。Innodb 的所有数据文件（后缀为 ibd 的文件），他的大小始终都是 16384（16k）的整数倍。

数据表中的数据都是存储在页中的，所以一个页中能存储多少行数据呢？假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。

对于B+树而言，只有叶子节点存放数据，非叶子节点存放的是只保存索引信息和下一层节点的指针信息。一个非叶子节点能存放多少指针？

其实这也很好算，我们假设主键 ID 为常用的bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170个。

那么可以算出一棵高度为2的B+树，存在一个根节点和若干个叶子节点能存放 1170*16=18720 条这样的数据记录。

根据同样的原理我们可以算出一个高度为 3 的
B+ 树可以存放： 1170117016=21902400 条这样的记录。

所以在 InnoDB 中 B+ 树高度一般为 1-3 层，就能满足千万级的数据存储。

那么为什么MySQL的索引要使用B+树而不是B树？

而 B 树和B+树的最大区别就是，B 树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致 IO 操作变多，查询性能变低。

HashMap适合做数据库索引吗？

1、hash表只能匹配是否相等，不能实现范围查找；

2、当需要按照索引进行order by时，hash值没办法支持排序；

3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了阿和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引；

4、当数据量很大时，hash冲突的概率也会非常大。

InnoDB中只有B+树索引吗？

InnoDB存储引擎不仅仅有B+树索引，它还支持全文索引、哈希索引。

InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)。使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。我们对这个自适应哈希索引能够干预的地方很少，只能设定是否启用和分区个数。

从MySQL5.6.x开始，InnoDB开始支持全文检索，内部的实现机制就是倒排索引。但是MySQL整体架构上对全文检索支持并不好而且限制很多，比如每张表只能有一个全文检索的索引，不支持没有单词界定符( delimiter）的语言，所以如果有大批量或者专门的全文检索需求，还是应该选择专门的全文检索引擎。

什么是密集索引和稀疏索引？

密集索引的定义：叶子节点保存的不只是键值，还保存了位于同一行记录里的其他列的信息，由于密集索引决定了表的物理排列顺序，一个表只有一个物理排列顺序，所以一个表只能创建一个密集索引。

稀疏索引：叶子节点仅保存了键位信息以及该行数据的地址，有的稀疏索引只保存了键位信息机器主键。

mysam存储引擎，不管是主键索引，唯一键索引还是普通索引都是稀疏索引，innodb存储引擎：有且只有一个密集索引。

所以，密集索引就是innodb存储引擎里的聚簇索引，稀疏索引就是innodb存储引擎里的普通二级索引。

为什么要用自增列作为主键？

1、如果我们定义了主键(PRIMARY
KEY)，那么InnoDB会选择主键作为聚集索引。

如果没有显式定义主键，则InnoDB会选择第一个不包含有NULL值的唯一索引作为主键索引。

如果也没有这样的唯一索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着行记录的写入而主键递增，这个ROWID不像ORACLE的ROWID那样可引用，是隐含的)。

2、数据记录本身被存于主索引（一颗B+Tree）的叶子节点上，这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放

因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）

3、如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页

4、如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置。此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE
TABLE来重建表并优化填充页面。

主键和唯一键有什么区别？

主键不能重复，不能为空，唯一键不能重复，可以为空。

建立主键的目的是让外键来引用。

一个表最多只有一个主键，但可以有很多唯一键