Mysql 面试题

聚簇索引和非聚簇索引

• 聚簇索引的优点
  聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
  聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的，
• 聚簇索引的缺点
  聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
  插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
  聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
  聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。
• 非聚簇索引
  非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

行锁和表锁

• 行锁：支持引擎为innodb，开销大，加锁慢，冲突低，并发度高，可能导致锁表，，行表通过索引实现，分为共享锁（lock in share mode ）和排他锁（for update），共享锁就是读锁，允许其他事务一起读，不允许写，排他锁不允许其他线程加任何锁。使用大量按索引更新数据，少量查询

• 表锁：支持引擎为innodb和myisam，开销小，加锁快，冲突高，并发度低，不会导致锁表，适合已查询为主，更新为辅的表

B+树比起B树有什么优点？

• 树的高度更小：中间的节点不存指针，同样大小的磁盘可页可以容纳更多的节点元素，树的高度就小。数据相同的情况下，B+树比B树更加“矮胖”，查找起来就更快
• 查询时间更稳定：B+树每次查找都必须要到叶子阶段才能获取数据，而B树不一定，B树可以在非叶子节点上获取数据。因此B+树查找到时间更稳定。
• 叶子节点串联：B+树中每一个叶子节点独有指向下一个叶子节点的指针，方便范围查询和全表查询：只需要从一个叶子节点开始顺着指针一直扫描下去即可，而B树则要对树做中序遍历。

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

1. 有了主键会将主键作为聚簇索引，没有会选择不包含null的列的唯一索引作为聚簇索引，如果也没有会将隐藏的6字节的rowID作为聚簇索引
2. 如果使用自增的列做为索引插入数据是直接顺序添加到当前索引的后面，如果使用非自增列例如学号等很有可能需要移动移动其他数据，导致内存碎片，索引结构不紧凑，需要使用OPTIMIZE TABLE来优化

innodb 和 myisam对比

1. innodb支持事务，支持行级锁和表级锁

2. myisam不支持，支持表锁，适合读多写少

3. Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI

    Innodb：frm是表定义文件，ibd是数据文件
   
    Myisam：frm是表定义文件，myd是数据文件，myi是索引文件
   

4. innodb必须有主键

5. InnoDB是聚簇索引（叶子节点存数据），MyISAM是非聚簇索引（叶子节点存指针）