面试题目整理（MySQL系列-索引）
最近又开始面试了，整理一些面试的高频考点吧。有幸参与一次社招面试，看的还是底层知识：
1、数据库存储引擎分类：
（1）：存储引擎主要有: 1. MyISAM（不支持事务） , 2. InnoDB（支持事务、行级锁定和外键）, 3. Memory, 4. Archive, 5. Federated 。 默认为:InnoDB 引擎。InnoDB 底层存储结构为 B+树， B 树的每个节点对应 innodb 的一个 page，page 大小是固定的，一般设为 16k
（2）：使用场景？
1)经常更新的表，适合处理多重并发的更新请求。
2)支持事务。
3)可以从灾难中恢复(通过 bin-log 日志等)。
4)外键约束。只有他支持外键。
5)支持自动增加列属性 auto_increment。
2、创建索引的原则：
（1） ：选择唯一性索引：唯一性索引的值是唯一的。可以更快的通过该索引来确定某条记录。 （2） ：为经常需要排序、分组、以及联合查询的列创建索引。 （3） ：为经常作为查询条件的列创建索引。 （4） ：限制索引的数目：越多的索引越导致表的查询效率变低，因为索引表在每次更新表数据的时候都会重新创建这个表的索引，表的数据越多，索引列越多，那么创建索引的时间消耗就越大。 （5） ：如果索引的值很长，那么查询的速度会受到影响。 （6） ：如果索引字段的值很长，最好使用值得前缀来进行索引。 （7） ：删除不再使用或者很少使用的索引。 （8） ：最左前缀匹配原则，非常重要的原则。 （9） ：尽量选择区分度高的列作为索引. （10）: 索引列不能参与计算，保持列“干净”:带函数的查询不参与索引。 （11）：尽量的扩展索引，不要新建索引。
3、使用索引的一些注意点：
（1）：like以%开头索引无效，当like以&结尾，索引有效。 （2）：当且仅当or语句查询条件的前后列均为索引时，索引生效。 （3）：组合索引，使用的不是第一列索引时候，索引失效，即最左匹配规则。 （4）：数据类型出现隐式转换，如varchar不加单引号的时候可能会自动转换为int类型，这个时候索引失效。 （5）：在索引列上使用IS NULL或者 IS NOT NULL 时候，索引失效，因为索引是不索引空值得。 （6）：在索引字段上使用，NOT、 <>、！= 、时候是不会使用索引的，对于这样的处理只会进行全表扫描。 （7）：对索引字段进行计算操作，函数操作时不会使用索引。 （8）：当全表扫描速度比索引速度快的时候不会使用索引。
4、索引分类
（1）：单列索引 1）普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。 2）唯一索引：索引列中的值必须是唯一的，但是允许为空值， 3）主键索引：是一种特殊的唯一索引，不允许有空值。 （2）：组合索引：多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。 （3）：全文索引：只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT 类型字段上使用全文索引，介绍了要求，说说什么是全文索引，就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个靓仔，靓女 ..." 通过靓仔，可能就可以找到该条记录 （4）：空间索引：空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。
5、添加索引
主键索引：ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 唯一索引：ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 普通索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 全文索引：ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 多列索引：ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
6、B+树索引和Hash索引的区别
1：hash索引适合等值查询，但是无法进行范围查询，B+树索引支持范围查询 2：hash索引没办法利用索引完成排序。 3：hash索引不支持多列联合索引的最左匹配规则。 4：如果有大量重复健值得情况下，hash索引的效率会很低，因为哈希碰撞问题。
7、聚簇索引和非聚簇索引
innoDB的B+Tree存储了整行数据的是主键索引，也被成为聚凑索引。
存储主键的值：成为非主键索引，也被称为非聚凑索引
优劣：
聚簇索引查询会更加快些。因为主键索引树的页子节点存储的是整行数据。
也就是我们需要得到的数据。而非主键索引的页子节点是主键的值，
查询的主键之后，我们还需要通过主键的值再次进行查询数据。（这个过程被称之为回表）。
8、最左匹配原则
在创建联合索引时候，一般需要遵循最左匹配原则。即联合索引中的属性识别度最高的放在查询语句的最前面。
9、优化器的执行
1：根据搜索条件，找出可能使用的索引。 2：计算全表扫描的代价。 3：计算使用不同索引执行查询的代价。 4：对比各种执行方案的代价，找出成本最低的一个。
10、MySQL5.6和MySQL5.7对于索引的优化
mysql5.6引入了索引下推优化，默认是开启的。
例子：user表中（a,b,c）构成一个索引。
select * from user where a='23' and b like '%eqw%' and c like 'dasd'。
解释：如果没有索引下推原则，则mysql会通过a='23' 先查询出一个对应的数据。然后返回到mysql服务端。
mysql服务端再基于两个like模糊查询来校验and查询出的数据是否符合条件。这个过程就设计到回表操作。
如果使用了索引下推技术，则mysql会首先返回返回条件a='23'的数据的索引，然后根据模糊查询的条件来校验索引行数据是否符合条件，
如果符合条件，则直接根据索引来定位对应的数据，如果不符合直接reject掉。因此，有了索引下推优化，可以在有like条件的情况下，减少回表的次数。
11、InnoDB和MyISAM的区别
（1）InnoDB 支持事务，MyISAM 不支持事务。这是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
（2）InnoDB 支持外键，而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败；
（3）InnoDB 是聚簇索引，MyISAM 是非聚簇索引。
聚簇索引的文件存放在主键索引的叶子节点上，因此 InnoDB 必须要有主键，
通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再
通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。
而 MyISAM 是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。
主键索引和辅助索引是独立的。
（4）InnoDB 不保存表的具体行数，
执行select count(*) from table 时需要全表扫描。
而 MyISAM 用一个变量保存了整个表的行数，执行
上述语句时只需要读出该变量即可，速度很快；
（5）InnoDB 最小的锁粒度是行锁，MyISAM 最小的锁粒度是表锁。
一个更新语句会锁住整张表，导致其他查询和更新都会被阻塞，
因此并发访问受限。这也是 MySQL 将默认存储引擎从 MyISAM 变成 InnoDB 的重要原因之一；
稀奇古怪的面试题：
一张表，里面有ID自增主键，当insert了17条记录之后，删除了第15,16,17条记录，再把Mysql重启，再insert一条记录，这条记录的ID是18还是15 ？
如果表的类型是MyISAM，那么是18。因为MyISAM表会把自增主键的最大ID 记录到数据文件中，重启MySQL自增主键的最大ID也不会丢失；
如果表的类型是InnoDB，那么是15。因为InnoDB 表只是把自增主键的最大ID记录到内存中，所以重启数据库或对表进行OPTION操作，都会导致最大ID丢失。
哪个存储引擎执行 select count(*) 更快，为什么?
MyISAM更快，因为MyISAM内部维护了一个计数器，可以直接调取。
在 MyISAM 存储引擎中，把表的总行数存储在磁盘上，当执行 select count(*) from t 时，直接返回总数据。
在 InnoDB 存储引擎中，跟 MyISAM 不一样，没有将总行数存储在磁盘上，当执行 select count(*) from t 时，会先把数据读出来，一行一行的累加，最后返回总数量。
InnoDB 中 count(*) 语句是在执行的时候，全表扫描统计总数量，所以当数据越来越大时，语句就越来越耗时了，为什么 InnoDB 引擎不像 MyISAM 引擎一样，将总行数存储到磁盘上？这跟 InnoDB 的事务特性有关，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。
12、索引本身也很大，不可能全部存储在内存中，一般以索引文件的形式存储在磁盘上，存贮形式：
