MySql面试总结第一部分：B，B+，引擎，索引

1为什么用B/B+树这种结构来实现索引呢？？

答：红黑树等结构也可以用来实现索引，但是文件系统及数据库系统普遍使用B/B+树结构来实现索引。mysql是基于磁盘的数据库，索引是以索引文件的形式存在于磁盘中的，索引的查找过程就会涉及到磁盘IO消耗，磁盘IO的消耗相比较于内存IO的消耗要高好几个数量级，所以索引的组织结构要设计得在查找关键字时要尽量减少磁盘IO的次数。

2为什么mysql的索引使用B+树而不是B树呢？？（区别）

1）B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO效率更高。
2）mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。
3）B+树同时支持随机检索和顺序检索；
5）B+树的查询效率更加稳定。
B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。
B+树的叶子节点使用指针顺序连接在一起，只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。顺序指针：目的是为了提高区间访问的性能。

3.B树 是一种平衡的多路搜索树。

特点：1.定义任意非叶子结点最多只有M个儿子；且M>2；
2.根结点的儿子数为[2, M]；
3.除根结点以外的非叶子结点的儿子数为[M/2, M]；
4.每个结点存放至少M/2-1（取上整）和至多M-1个关键字；（至少2个关键字）
5.非叶子结点的关键字个数=指向儿子的指针个数-1；
6.非叶子结点的关键字：K[1], K[2], …, K[M-1]；且K[i] < K[i+1]；
7.非叶子结点的指针：P[1], P[2], …, P[M]；其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[M-1]的子树，其它P[i]指向关键字属于(K[i-1],K[i])的子树；
8.所有叶子结点位于同一层；

4.B+树—多路搜索树

B+树是是一个n叉树，每个节点通常有多个孩子，一棵B+树包含根节点、内部节点和叶子节点。根节点可能是一个叶子节点，也可能是一个包含两个或两个以上孩子节点的节点。
B+ 树的特点是能够保持数据稳定有序，其插入与修改拥有较稳定的对数时间复杂度。B+ 树元素自底向上插入。
B+树是B-树的变体，也是一种多路搜索树：
1.其定义基本与B-树相同，除了：
2.非叶子结点的子树指针与关键字个数相同；
3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）；
5.为所有叶子结点增加一个链指针；
6.所有关键字都在叶子结点出现； B+的特性：
1.所有关键字都出现在叶子结点的链表中（稠密索引），且链表中的关键字恰好是有序的；
2.不可能在非叶子结点命中；
3.非叶子结点相当于是叶子结点的索引（稀疏索引），叶子结点相当于是存储（关键字）数据的数据层；
4.更适合文件索引系统；

5.MYISAM和INNODB的不同？

几点区别：
a)构造上的区别
MyISAM在磁盘上存储成三个文件，其中.frm文件存储表定义；.MYD (MYData)为数据文件；.MYI (MYIndex)为索引文件。
innodb是由.frm文件、表空间（分为独立表空间或者共享表空间）和日志文件（redo log）组成。
b)事务上的区别
myisam不支持事务；而innodb支持事务。
c)锁上的区别
myisam使用的是表锁；而innodb使用的行锁（innodb也支持表锁）。
表级锁：直接锁定整张表，在锁定期间，其他进程无法对该表进行写操作，如果设置的是写锁，那么其他进程读也不允许，因此myisam支持的并发量低，但myisam不会出现死锁；
行级锁：只对指定的行进行锁定，其他进程还是可以对表中的其他行进行操作的。因此行锁能大大的减少数据库操作的冲突，但有时会导致死锁。
d)是否支持外键的区别
myisam不支持外键，innodb支持外键
e) select count(*)的区别
对于没有where的count()使用MyISAM要比InnoDB快得多。 MyISAM内置了一个计数器，count()时它直接从计数器中读，而InnoDB必须扫描全表。
f)myisam只把索引都load到内存中，而innodb存储引擎是把数据和索引都load到内存中 ，innodb不支持全文索引（5.7支持），当使用数据库事务时，innodb引擎是首选。由于锁的粒度更小，写操作不会锁定全表，所以在并发较高时，使用Innodb引擎会提升效率。

6.适用场景

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询频繁；(3)没有事务。
InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和插入都相当的频繁，并且表锁定的机会比较大的情况。

7.索引方式
MyISAM：非聚集索引

1）在MyISAM中，主索引和辅助索引（Secondary
key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
2）MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
3）MyISAM的索引方式也叫做“非聚集”的.主键B+树在叶子节点存储指向真正数据行的指针，而非主键.

InnoDB(聚集索引)-————-使用B+Tree作为索引结构.

1）InnoDB的数据文件本身就是索引文件,这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
2）InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。
3）聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
4）不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。
5）在InnoDB中不建议用非单调的字段作为主键，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

8 为什么MyISAM会比Innodb 的查询速度快。

1）INNODB在做SELECT的时候，要维护的东西比MYISAM引擎多很多；
2）INNODB缓存数据块，MYISAM只缓存索引块， 这中间还有换进换出的减少；
3）innodb寻址要映射到块，再到行，MYISAM 记录的直接是文件的OFFSET，定位比INNODB要快
4）INNODB还需要维护MVCC一致；虽然你的场景没有，但他还是需要去检查和维护 —MVCC ( Multi-Version Concurrency Control )多版本并发控制

9.数据库索引

一个排序的数据结构，以协助快速查询、更新数据库表中数据。索引的实现通常是B树及其变种B+树。
索引代价：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

10.创建索引的好处：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
10.1.缺点：

第一，创建索引和维护索引要耗费时间。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

11.什么情况需要创建索引：

1）在经常需要搜索的列上，可以加快搜索的速度；
2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；
3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4）在经常需要根据范围进行搜索的列上创建索引和经常需要排序的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

12.不应该创建索引的情况：

     第一，对于那些在查询中很少使用列不应该创建索引。------增加了索引，反而降低了系统的维护速度和增大了空间需求。
     第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。（与第五条相似）
     第三，对于那些定义为text, image和bit数据类型的列不应该增加索引。----这些列的数据量要么相当大，要么取值很少。
     第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。----当修改性能远远大于检索性能时，不应该创建索引。
     第五，索引的选择性较低不应该建立索引。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值：Index Selectivity = Cardinality / #T  显然选择性的取值范围为(0, 1]，选择性越高的索引价值越大，这是由B+Tree的性质决定的。

13.索引类别：

主键索引primary key——是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。
唯一索引unique key ——索引列的值必须唯一。
普通索引 key
全文索引 fulltext —–在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行。只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引（现在Innodb也支持全文索引了mysql5.7）
全文搜索的限制比较多，比如只能通过MyISAM引擎，比如只能在CHAR,VARCHAR,TEXT上设置全文索引。比如搜索的关键字默认至少要4个字符，比如搜索的关键字太短就会被忽略掉。
组合索引 – 多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。 使用组合索引时遵循最左前缀原则
注：在mysql中执行查询时，只能使用一个索引，如果我们在lname,fname,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。

——-主键索引和唯一索引的区别———

唯一性索引列允许空值，而主键列不允许为空值
主键可以被其他表引用为外键，而唯一索引不能。
一个表最多只能创建一个主键，但可以创建多个唯一索引。
主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。

14.建立索引的时机

一般来说，在WHERE和JOIN中出现的列需要建立索引，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。
以通配符%和_开头作查询时，MySQL不会使用索引————like’%admin’

15.B和B+两种处理索引的数据结构的不同之处：

a，B树中同一键值不会出现多次，并且它有可能出现在叶结点，也有可能出现在非叶结点中。而B+树的键一定会出现在叶结点中，并且有可能在非叶结点中也有可能重复出现，以维持B+树的平衡。
b，因为B树键位置不定，且在整个树结构中只出现一次，虽然可以节省存储空间，但使得在插入、删除操作复杂度明显增加。B+树相比来说是一种较好的折中。
c，B树的查询效率与键在树中的位置有关，最大时间复杂度与B+树相同(在叶结点的时候)，最小时间复杂度为1(在根结点的时候)。而B+树的时候复杂度对某建成的树是固定的。

16.索引结构–聚集索引和非聚集索引

非聚集索引：B+树索引结构–叶子节点存储物理地址
聚集索引：B+ 1）主键索引：叶子节点存储整条记录，而不是物理节点。
2）非主键索引：叶子节点存储的是主键索引，通过主键索引来查找记录。

17.索引优化–最左前缀原则

索引条件下：
1）全列匹配，当搜索的列是索引的全部字段；此时不需要考虑顺序问题，因为MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。
2）最左前缀匹配 查询条件指定了索引的第一列，会使用部分索引； 主索引为< emp_no, title, from_date> 例如
WHERE emp_no=’10001’ 会使用部分索引
WHERE emp_no=’10001’ AND from_date=’1986-06-26’ 会使用部分索引 如果想让后者也在索引列中，方法一就是创建辅助索引 < emp_no,from_date>，或者“隔离列”的优化方法，将emp_no与from_date之间的“坑”填上；在这种成为“坑”的列值比较少的情况下，可以考虑用“IN”来填补这个“坑”从而形成最左前缀WHERE emp_no=’10001’ AND title IN (‘Senior ‘,。。。) AND from_date=’1986-06-26’;“填坑”后性能提升了一点。如果经过emp_no筛选后余下很多数据，则后者性能优势会更加明显。当然，如果title的值很多，用填坑就不合适了，必须建立辅助索引。
WHERE from_date=’1986-06-26’ 不会使用索引 匹配某列前缀字符串 WHERE emp_no=’10001’ AND title LIKE ‘Senior%’;此时可以用到索引，如果通配符%不出现在开头，则可以用到索引，但根据具体情况不同可能只会用其中一个前缀 范围查询：
范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
查询条件中含有函数或表达式 如果查询条件中含有函数或表达式，则MySQL不会为这列使用索引（虽然某些在数学意义上可以使用）

18索引优化—前缀索引

与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销。
< first_name, left(last_name, 4)> ADD INDEX first_name_last_name4(first_name, last_name(4)); 前缀索引兼顾索引大小和查询速度，但是其缺点是不能用于ORDER BY和GROUP BY操作，也不能用于Covering index（即当索引本身包含查询所需全部数据时，不再访问数据文件本身）。

19.覆盖索引概念：

MySQL可以利用索引返回SELECT列表中的字段，而不必根据索引再次读取数据文件。包含所有满足查询需要的数据的索引成为覆盖索引(Covering Index)。也就是平时所说的不需要回表操作。

20.判断标准：

在查询前面使用explain，可以通过输出的extra列来判断，对于一个索引覆盖查询，显示为using index,MySQL查询优化器在执行查询前会决定是否有索引覆盖查询。
注意：覆盖索引也并不适用于任意的索引类型，索引必须存储列的值。Hash和full-text索引不存储值，因此MySQL只能使用B-TREE。并且不同的存储引擎实现覆盖索引都是不同的。并不是所有的存储引擎都支持它们。如果要使用覆盖索引。一定要注意SELECT 列表值取出需要的列。不可以是SELECT * ，因为如果将所有字段一起做索引会导致索引文件过大。查询性能下降。不能为了利用覆盖索引而这么做。

21.只需要读取索引而不用读取数据有以下一些优点：

(1)索引项通常比记录要小，所以MySQL访问更少的数据；
(2)索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；
(3)大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。
(4)覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

22.区别

Innodb中，索引存贮的是数据本身，可以在需要查询的列都在索引中存在，则不需要进行数据的查询，直接查询索引就获得了数据
myisam中，索引主要是缓存索引。count(*)在该存储引擎中是覆盖索引，但是如果count（其他字段）则不是。

23.全索引扫描并不一定就比全表扫描好，取决于数据存储位置。如果数据在内存，那么这两种没有太大区别。如果数据在磁盘，全表扫描比全索引扫描要好，这是因为，全表扫描是顺序读数据，sequential read，是顺序IO。而全索引扫描，可能会产生随机读(reandom read)，随机IO，显然，顺序读要比随机读快很多。