MYSQL经典面试题

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

数据是存储在磁盘上的，如果没有索引，查询数据时就需要将所有的数据都加在到内存中，依次检索。且CPU一次加载的数据量是有限的，这样就需要多次IO,开销比较大。使用索引后，索引通过B+Tree组织，通过B+Tree搜索算法能快速检索到数据，而且将数据所在页加载到内存中，减少了IO开销，同时数据是按索引排好序的，当通过索引字段进行排序或范围查找时速度很快。

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

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

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

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

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

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

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

1) 适合创建索引的情况

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

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

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

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

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

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

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

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

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前后有一个条件的列不是索引，那么查询中将不使用索引。

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。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由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

持续更新中。。。。