一、 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有如下特性:
- 每个节点最多有m个孩子。
- 除了根节点和叶子节点外,其它每个节点至少有Ceil(m/2)个孩子。
- 若根节点不是叶子节点,则至少有2个孩子
- 所有叶子节点都在同一层,且不包含其它关键字信息
- 每个非终端节点包含n个关键字信息(P0,P1,…Pn, k1,…kn)
- 关键字的个数n满足:ceil(m/2)-1 <= n <= m-1
- ki(i=1,…n)为关键字,且关键字升序排序。
- 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 索引的名字(列名(长度) 排序规则)
删除:
- ALTER TABLE 表名 DROP INDEX 索引名
- 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)由一个基表定义的视图,只含有基表的主键或候补键,并且视图中没有用表达式或函数定义的属性,才允许更新。