MySQL高级部分（上） 面试必看

一、索引

1-1 . 索引概述

MySQL官方对索引的定义为：索引（index）是帮助MySQL高效获取数据的数据结构（有序）。

1-2 索引的优势和劣势

优势：

1. 索引就相当于书籍的目录，提高数据的查询速度，降低数据库的IO成本
2. 通过索引对数据进行排序，降低了排序的成本

劣势：

1. 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的。
2. 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为需要维持索引记录的关系。

1-3 索引的结构

索引是在存储引擎中实现的，所以不同的存储引擎有着不同的索引

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，InnoDB从Mysql5.6版本开始支持全文索引。

我们平常所说的索引，如果没有特别指明，都是指B+树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+tree 索引，统称为 索引。

B-Tree数据结构

特点:

BTree又叫多路平衡搜索树，一颗m叉的BTree特性如下：

• 树中每个节点最多包含m个孩子。
• 除根节点与叶子节点外，每个节点至少有[ceil(m/2)]个孩子。
• 若根节点不是叶子节点，则至少有两个孩子。
• 所有的叶子节点都在同一层。
• 每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

演变过程：

以5叉BTree为例，key的数量：公式推导[ceil(m/2)-1] <= n <= m-1。所以 2 <= n <=4 。当n>4时，中间节点分裂到父节点，两边节点分裂。

插入 C N G A H E K Q M F W L T Z D P R X Y S 数据为例。

1). 插入前4个字母 C N G A

2).插入H，n>4，中间元素G字母向上分裂到新的节点

3). 插入E，K，Q不需要分裂

4). 插入M，中间元素M字母向上分裂到父节点G

5). 插入F，W，L，T不需要分裂

6). 插入Z，中间元素T向上分裂到父节点中

7). 插入D，中间元素D向上分裂到父节点中。然后插入P，R，X，Y不需要分裂

8). 最后插入S，NPQR节点n>5，中间节点Q向上分裂，但分裂后父节点DGMT的n>5，中间节点M向上分裂

到此，该BTREE树就已经构建完成了， BTREE树 和 二叉树 相比， 查询数据的效率更高， 因为对于相同的数据量来说，BTREE的层级结构比二叉树小，因此搜索速度快。

B+Tree数据结构

B+Tree为BTree的变种，B+Tree与BTree的区别为：

1). n叉B+Tree最多含有n个key，而BTree最多含有n-1个key。

2). B+Tree的叶子节点保存所有的key信息，依key大小顺序排列。

3). 所有的非叶子节点都可以看作是key的索引部分。

观察图片可知，只有叶子结点才存储数据，非叶子结点存储的仅是索引

MySQL中的B+Tree

MySql索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。

1-4 索引的分类

1） 单值索引 ：即一个索引只包含单个列，一个表可以有多个单列索引

2） 唯一索引 ：索引列的值必须唯一，但允许有空值

3） 复合索引 ：即一个索引包含多个列

1-5 索引的基本语法

索引在创建表的时候，可以同时创建， 也可以随时增加新的索引。

创建索引：

CREATE 	[UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name 
[USING  index_type]
ON tbl_name(index_col_name,...)
#解释
create [索引的类型] index 索引的名字 [使用的结构,默认为B+Tree索引] on 表名(列名)

#例子
CREATE INDEX idx_np_category_pid ON np_category(pid)

查看索引：

show index  from  table_name;

#解释
show index from 表名

#例子
show index from np_category

删除索引：

DROP  INDEX  index_name  ON  tbl_name;

#解释
DROP INDEX 索引名 ON 表名

Alter命令

1). alter  table  tb_name  add  primary  key(column_list); 

	该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
	
2). alter  table  tb_name  add  unique index_name(column_list);
	
	这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）
	
3). alter  table  tb_name  add  index index_name(column_list);

	添加普通索引， 索引值可以出现多次。
	
4). alter  table  tb_name  add  fulltext  index_name(column_list);
	
	该语句指定了索引为FULLTEXT， 用于全文索引

1-6 索引的设计原则

• 对查询频次较高，且数据量比较大的表建立索引。
• 对查询的条件建立索引，也就是对where中的字段建立索引
• 使用唯一索引，区分度越高，使用索引的效率越高。
• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。对于插入、更新、删除等DML操作比较频繁的表来说，索引过多，会引入相当高的维护代价，降低DML操作的效率，增加相应操作的时间消耗。另外索引过多的话，MySQL也会犯选择困难病，虽然最终仍然会找到一个可用的索引，但无疑提高了选择的代价。
• 尽量使用较短的字段建立索引，因为索引也需要占用磁盘空间，如果使用长字段的话会降低IO的速度，从而降低性能。
• 利用最左前缀，N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

创建复合索引:

	CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);

就相当于（只要包含了最左边的列即可）
	对name 创建索引 ;
	对name , email 创建了索引 ;
	对name , email, status 创建了索引 ;

二、视图

2-1 视图的概述

​ 视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

特性

• 简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2-2 创建视图或修改视图

创建视图：

CREATE [OR REPLACE] [ALGORITHM = {
  UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS select_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

#例如
CREATE VIEW view_stuInfo AS SELECT * FROM stuInfo WHERE stuQQ LIKE "1%"

CREATE VIEW 视图名称 AS 查询语句

因为视图的本质就是虚拟的表，所以操作视图的语句和操作表的语句大致相同。如果查询上述视图的数据，就是用：

select * from view_stuInfo

这里不建议对视图进行update的操作，因为视图的作用就是用来查看数据的

查看视图：

可以使用show tables查看，也可以使用sql链接工具，比如SqlLyog

查看创建视图时的语句，可以使用：

SHOW CREATE VIEW view_stuInf