一、索引
1-1 . 索引概述
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构(有序)。
1-2 索引的优势和劣势
优势:
- 索引就相当于书籍的目录,提高数据的查询速度,降低数据库的IO成本
- 通过索引对数据进行排序,降低了排序的成本
劣势:
- 实际上索引也是一张表,该表中保存了主键与索引字段,并指向实体类的记录,所以索引列也是要占用空间的。
- 虽然索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行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