MySQL面试题

B树和B+树的不同

B树每个节点数据项都包含了完整的行数据，他没有冗余索引，所以有可能查询的时候直接在根节点查询出来，所以它的查找不够稳定

B+树完整行数据都存储在叶子节点，其他冗余节点存储的是索引值和子节点（数据页）的最小值的地址，因为冗余节点只存储索引地址，所以数据页大小相同，在同高度的树下，B+树会比B树存储更多的数据，同时在叶子节点之间都有指向前后节点的指针方便处理范围查询。

注：Mysql中B+树一个节点默认是16k

为什么InnoDB表必须建主键，并推荐使用整型自增主键？

因为InnoDB表不建主键的话，MySQL会尝试自己查找一列没有数据重复的列作为键，如果没有则MySQL自己创建一列隐形id作为主键构建聚集索引，而这些操作都是可以避免的。

选择整型是为了便于比较，比如字符型就需要逐个字符比较，自增是为了避免整理数据页的问题，因为一个节点就是一张数据页，如果现在需要插入一个主键id小于当前页最后一条数据id大于倒数第二条数据id的数据，B+树又必须要是排序好的数据结构，所以就需要将最后一条数据修改移动，而自增就可以只需要在这种情况，新增数据页就好了

为什么非主键索引结构叶子节点存储的是主键值？

节省存储空间

为什么没有按索引的最左前列原理使用条件，不会使用索引查询

（name， age, position） select * from tbl where age = 10

因为在构建索引时，要满足排序规则排序，首先是按照name大小排序，name相同按age大小排序，所以排序的时候是按照从左到右的优先级排序的，所以在查询时也是先按name查询，如果没有name条件就没办法使用索引查找了，只能使用全表查找了

什么是数据库的触发器，存储过程，游标？

视图的优缺点

优点：
使用视图，可以简化数据操作。
使用视图，可以定制用户数据，聚焦特定的数据。
使用视图，基表中的数据就有了一定的安全性

缺点：（针对由复杂的多表查询创建的视图）
性能差
修改限制

建立索引的原则

针对于数据量较大，且查询比较频繁的表建立索引。
针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
单列索引不存储null值，复合索引不存储全为null的值。请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询。

为什么要建立联合索引

在查询条件and连接的两个字段 phone、name上都是有单列索引的，但是最终mysql只会选择一个索引，也就是说，只能走一个字段的索引，此时是会回表查询的。

在联合索引中包含 phone、name的信息，在叶子节点下挂的是对应的主键id，如果联合索引的字段包含了查询的字段，那么此时是一个覆盖索引，所以查询是无需回表查询的。即使联合索引不能全部包含查询的字段，也会因为需要回表的数据量小查询的速度快一些

什么是最左前缀法则

如果有联合索引，则要遵守最左前缀法则，也就是查询条件必须从左到右依次是联合索引的字段，如果跳过或者遇到范围查找(>,<，between, like)，则后面的字段索引失效

索引失效的情况

索引列上进行运算操作，索引将失效。（例如substring(phone,10,2) = ‘15’）
字符串类型字段使用时，不加引号，索引将失效。（隐式类型转换，索引将失效。）
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
MySQL评估使用索引比全表更慢，则不使用索引（因为索引是用来索引少量数据的，如果通过索引查询返回大批量的数据，则还不如走全表扫描来的快，此时索引就会失效。）
联合索引中，出现范围查询(>,<，between, like)，范围查询右侧的列索引失效。
单列索引不存储null值，复合索引不存储全为null的值。

MYSQL优化

索引优化
1. 参照建立索引的原则
2. 一般分页查询越往后，分页查询效率越低，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优。（子查询只查id，覆盖索引避免回表 eg:
  select * from tb_sku t , (select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;）
SQL优化