Mysql数据库的索引和视图

一、 Mysql的索引

1.什么是Mysql的索引?

索引就像是书的目录，是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引中包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（BTree）中，使SQL可以快速有效地查找与键值关联的行。

2.索引的原理

索引的原理大致概括为以空间换时间，数据库在未添加索引的时候进行查询默认的是进行全量搜索，也就是进行全局扫描，有多少条数据就要进行多少次查询，然后找到相匹配的数据就把他放到结果集中，直到全表扫描完。而建立索引之后，会将建立索引的KEY值放在一个n叉树上（BTree）

3.索引的优点:

1.建立索引的列可以保证行的唯一性，生成唯一的rowId(唯一标识)
2.建立索引可以有效缩短数据的检索时间
3.建立索引可以加快表与表之间的连接
4.为用来排序或者是分组的字段添加索引可以加快分组和排序顺序

4.索引的缺点：

1.创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
2.创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
3.会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长

5.索引的储存方式

MySQL主要提供2种方式的索引：B-Tree（包括B+Tree）索引，Hash索引
（1）B-Tree的结构
一棵m阶的B-Tree有如下特性：

1. 每个节点最多有m个孩子。
2. 除了根节点和叶子节点外，其它每个节点至少有Ceil(m/2)个孩子。
3. 若根节点不是叶子节点，则至少有2个孩子
4. 所有叶子节点都在同一层，且不包含其它关键字信息
5. 每个非终端节点包含n个关键字信息（P0,P1,…Pn, k1,…kn）
6. 关键字的个数n满足：ceil(m/2)-1 <= n <= m-1
7. ki(i=1,…n)为关键字，且关键字升序排序。
8. Pi(i=1,…n)为指向子树根节点的指针。P(i-1)指向的子树的所有节点关键字均小于ki，但都大于k(i-1)
   InnoDB存储引擎中默认每个页的大小为16KB

（2）B+Tree的结构

B+Tree相对于B-Tree有几点不同：
非叶子节点只存储键值信息。
所有叶子节点之间都有一个链指针

6.Mysql的使用时机和注意事项：

时机：
（1）主键，unique字段；
（2）和其他表做连接的字段需要加索引；
（3）在where里使用＞，≥，＝，＜，≤，is null和between，in等字段；
（4）使用不以通配符开始的like，where A like ‘China%’；
（5）聚集函数MIN()，MAX()中的字段；
（6）order by和group by字段；

注意事项：

（1）数据量大的，经常进行查询操作的表要建立索引
（2）使用短索引，可以提高检索速度，还可以减少磁盘空间和io操作
（3）越小的数据类型通常更好：越小的数据类型通常在磁盘、内存中都需要更少的空间，处理起来更快
（4）尽量避免NULL：应该指定列为NOT NULL。含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。
（5）对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义，所以索引的建立应当更多的选取唯一性更高的字段
（6）如果有些表注定只会进行查询所有，也就没必要添加索引，因为查询全部只能进行全量搜索即扫描全表
（7）添加多列索引的时候，对应的多条件查询可以触发该索引的同时，索引最左侧的列的单条件查询也可以触发。

7.索引何时失效：

（1）组合索引未使用最左前缀，例如组合索引（A，B），where B=b不会使用索引；
（2）like未使用最左前缀，where A like ‘%China’；
（3）搜索一个索引而在另一个索引上做order by，where A=a order by B，只使用A上的索引，因为查询只使用一个索引
（4）or会使索引失效。如果查询字段相同，也可以使用索引。例如where A=a1 or A=a2（生效），where A=a or B=b（失效）
（5）如果列类型是字符串，要使用引号。例如where A=’China’，否则索引失效（会进行类型转换）；
（6）在索引列上的操作，函数（upper()等）、or、！=(<>)、not in等；

8.Mysql的常见类型的创建和删除

Mysql索引的常见分类：
按照类型分：
1、普通型索引 INDEX|KEY
这是最基本的索引类型，而且它没有唯一性之类的限制。
2、唯一索引 (UNIQUE)
这种索引和前面的“普通索引”基本相同，但有一个区别：索引列的所有值都只能出现一次，即必须唯一。
3、主键 (PRIMARY KEY)
主键是一种唯一性索引，但它必须指定为“PRIMARY KEY”。（主键相当于聚合索引，是查找最快的索引）
ALTER TABLE tablename ADD PRIMARY KEY (列的列表);
4、单列索引和多列索引
索引可以是单列索引，也可以是多列索引。
（1）单列索引就是常用的一个列字段的索引，常见的索引。
（2）多列索引就是含有多个列字段的索引
alter table student add index sy(name,age，score);
索引sy就为多列索引，多列索引在以下几中情况下才能有效：
select * from student where name=’jia’ and age>=’12’ //where条件中含有索引的首列字段和
第二个字段
select * from student where name=’jia’ //where条件中只含有首列字段
select * from student where name=’jia’ and score<60//where条件中含有首列字段和第三个字段

按物理存储方式分：
5.聚簇索引和非聚簇索引
聚簇索引:(nclustered) 索引的顺序对应的是表中记录的物理顺序
非聚簇索引:( nonclustered )
聚簇索引主键的插入速度要比非聚簇索引主键的插入速度慢很多。
聚簇索引适合排序，非聚簇索引不适合用在排序的场合。因为聚簇索引本身已经是按照物理顺序放置的，排序很快。非聚簇索引则没有按序存放，需要额外消耗资源来排序。
每个表只能有一个聚簇索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚簇索引。实际上，对每个表你最多可以建立249个非聚簇索引。非聚簇索引需要大量的硬盘空间和内存。另外，虽然非聚簇索引可以提高从表中取数据的速度，它也会降低向表中插入和更新数据的速度。每当你改变了一个建立了非聚簇索引的表中的数据时，必须同时更新索引。因此你对一个表建立非聚簇索引时要慎重考虑。如果你预计一个表需要频繁地更新数据，那么不要对它建立太多非聚簇索引。另外，如果硬盘和内存空间有限，也应该限制使用非聚簇索引的数量。

Mysql索引创建、删除、查看语法:

创建：
直接创建索引
CREATE 索引类型 INDEX 索引的名字 ON 表名|视图名 (列名(长度) 排序规则)
修改表时创建
ALTER TABLE 表名 ADD 索引类型 INDEX 索引的名字(列名(长度) 排序规则);
创建表的时候指定索引
CREATE TABLE 表名 ( […], 索引类型 INDEX 索引的名字(列名(长度) 排序规则)
删除：

1. ALTER TABLE 表名 DROP INDEX 索引名
2. DROP INDEX 索引名
   查看表中的索引：
   SHOW INDEX FROM 表名

二、MySQL的视图

1.Mysql视图的介绍:

视图是一种虚拟存在的表，对于使用视图的用户来说基本上是透明的。视图并不在数据库中实际存在，行和列数据来自定义视图的查询总使用的表，并且是在使用视图时动态生成的。

2.视图的创建、修改、删除、查看

创建：
Create view 视图名称（视图中列的名字用逗号分隔）as SQL语句
创建视图时如果指定视图列明的话必须和查询结果数量和顺序对应
修改：
1.Create or replace algorithm = 视图算法类型 view 视图名称（视图中列的名字用逗号分隔）as SQL语句
2.alter view 视图名 (列名) as select 列名 from 表名;
删除：
DROP VIEW 视图名
查看视图：
show tables 查看全部的视图和表
desc 视图名 视图的结构
查看视图定义：
show create view 视图名

3.视图和表的区别

1、视图是已经编译好的sql语句 而表不是
2、视图没有实际的物理记录。表而有。
3、表是内容，视图是窗口
4、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
5、表是内模式，视图是外模式
6、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构
7、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
8、视图的建立和删除只影响视图本身，不影响对应的基本表。

4.视图的优点：

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

5.视图的缺点：

1，性能：从数据库视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的
2，表依赖关系：将根据数据库的基础表创建一个视图。每当更改与其相关联的表的结构时，都必须更改视图。

2.视图的算法类型:
MySQL的视图算法有三种类型：MERGE、TEMPTABLE、UNDEFINED。
如果没有ALGORITHM子句，默认算法是UNDEFINED（未定义的）。算法会影响mysql处理视图的方式。
1，MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
2，TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。
3，UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

6.不可更新的视图类型：

（1）若视图的字段是来自字段表达式或常数，则不允许对此视图执行INSERT、UPDATE操作，允许执行DELETE操作；
（2）若视图的字段是来自库函数，则此视图不允许更新；
（3）若视图的定义中有GROUP BY子句或聚集函数时，则此视图不允许更新；
（4）若视图的定义中有DISTINCT任选项，则此视图不允许更新；
（5）若视图的定义中有嵌套查询，并且嵌套查询的FROM子句中涉及的表也是导出该视图的基表，则此视图不允许更新；
（6）若视图是由两个以上的基表使用jion连接导出的，此视图不允许更新；
（7）一个不允许更新的视图上定义的视图也不允许更新；
（8）由一个基表定义的视图，只含有基表的主键或候补键，并且视图中没有用表达式或函数定义的属性，才允许更新。