MySQL数据库学习日志(六):索引和视图
索引
腾讯云数据库负责人林晓斌说过:“我们面试 MySQL 同事时只考察两点,索引和锁”。
(一)索引概述
- MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
- 索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。
- 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储的磁盘上。
(二)优缺点
1. 优点
类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO(读和写)成本。
通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。
2. 缺点
实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。
虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。
索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。
(三)分类
1. 按照底层实现的方式
主要有Hash索引和B+索引。(两种查找方式数据结构均有介绍)
Hash索引 O(1)
B+索引
2. 按照功能划分
主要介绍普通索引、唯一索引和组合索引(多列索引)。
普通索引
(1)特点
普通索引使用没有限制,允许在所有数据类型上创建,可以有重复值和空值。
一个索引只包含单个列,一个表可以有多个单列索引。
(2)创建普通索引
1)建表时创建索引
语法
INDEX 索引名 (列名)
示例
create table student(
id int primary key,
name varchar(20),
age int,
index name_indx(name)
)
测试是否创建成功
show index from student
2)修改表时创建索引
语法
ALTER TABLE 表名 ADD INDEX 索引名(列名)
示例
create table student2(
id int primary key,
name varchar(20),
age int
)
ALTER TABLE student2 ADD INDEX name_indx(name)
测试是否创建成功
show index from student2
3)直接创建
语法
CREATE INDEX 索引名 ON 表名(列名)
示例
create table student3(
id int primary key,
name varchar(20),
age int
)
CREATE INDEX name_indx ON student3(name)
测试是否创建成功
show index from student3
唯一索引
(1)特点
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值的组合必须是唯一的,创建方法和普通索引类似。
(2)创建唯一索引
1)建表时创建索引
语法
UNIQUE INDEX 索引名 (列名)
示例
create table student4(
id int primary key,
name varchar(20),
age int,
email varchar(200),
UNIQUE INDEX email_un_index(email)
)
测试是否创建成功
show index from student4
2)修改表时创建索引
语法
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(列名)
示例
create table student5(
id int primary key,
name varchar(20),
age int,
email varchar(200)
)
ALTER TABLE student5 ADD UNIQUE INDEX email_un_indx(email)
3)直接创建
语法
CREATE UNIQUE INDEX 索引名 ON 表名(列名)
示例
create table student6(
id int primary key,
name varchar(20),
age int,
email varchar(200)
)
CREATE UNIQUE INDEX email_un_indx ON student6(email)
4)添加唯一约束方式
示例
create table student7(
id int primary key,
name varchar(20),
age int,
email varchar(200) unique
)
添加了唯一约束的列,数据库会直接添加唯一索引。
组合索引
(1)特点
同时在多个列上添加索引。当查询时,条件是添加了索引的列的组合,可以提高查询效率。
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。
(2)创建组合索引
1)建表时创建索引
语法
INDEX 索引名 (列名1,列名2...,列名n)
示例
create table student8(
id int primary key,
name varchar(20),
age int,
index name_age_indx(name,age)
)
2)修改表时创建索引
语法
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名 (列名1,列名2...,列名n)
示例
create table student9(
id int primary key,
name varchar(20),
age int
)
ALTER TABLE student9 ADD UNIQUE INDEX name_age_indx(name,age)
3)直接创建
语法
CREATE INDEX 索引名 ON 表名(列名1,列名2...,列名n)
示例
create table student10(
id int primary key,
name varchar(20),
age int
);
CREATE INDEX name_age_indx ON student10(name,age)
(四)创建索引的条件
1. 创建索引
(1)主键自动建立唯一索引。
(2)频繁作为查询条件的字段应该创建索引。
(3)查询中与其它表关联的字段,外键关系建立索引。
(4)查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度。
(5)普通/组合索引的选择问题,在高并发下倾向创建组合索引。
2. 不创建索引
(1)频繁更新的字段不适合创建索引。
因为每次更新不单单是更新了记录还会更新索引,加重了IO负担
(2)Where条件里用不到的字段不创建索引
(3)表记录太少。
(4)经常增删改的表。
(5)如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。
(五)索引使用注意事项
1. 索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。Is null和is not null 无法使用索引。
2. 使用短索引
对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3. 索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by中的列是不会使用索引的。因此数据库默认排序在符合要求的情况下不要使用排序操作;尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引。
4. like语句查询
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5. 不要在列上进行运算
例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<’2007-01-01′。关于这一点可以围观:一个单引号引发的MySQL性能损失。
6. MySQL使用不等于(!= 或者<>)时无法使用索引会导致全表扫描
在MySQL的查询语句的查询条件中如果使用了不等于,那么将索引失效,进行全表扫描方式查询。
7. 最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
假设index(a,b,c)
(六)索引的内部原理
- 一般来说,索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘上。
- 这样的话,索引查找过程中就要产生磁盘I/O消耗,相对于内存存取,I/O存取的消耗要高几个数量级,所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。
- 换句话说。索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。
相关算法介绍
Hash算法
优点:通过字段的值计算hash值,定位数据非常快。
缺点:不能进行范围查找,因为散列表中的值是无序的,无法进行大小比较。
排序二叉树
特性:分为左子树,右子树和根节点,左子树比根节点值要小,右子树比根节点值要大。
缺点:有可能产生不平衡 类似于链表的结构。
平衡二叉树
特点:
a、它的左子树和右子树都是平衡二叉树
b、左子树比中间小,右子树比中间值大
c、左子树和右子树的深度之差的绝对值不超过1
缺点:
a、插入操作需要旋转
b、支持范围查询,但回旋查询效率较低,比如要查找大于8的,会回旋到父节点7、10
c、如果存放几百条数据的情况下,树高度越高,查询效率会越慢
BTREE树
目前大部分数据库及文件系统都采用B-Tree或其变种B+tree作为索引结构,Btree结构可以有效的解决之前的相关算法所遇到的问题。
B-Tree(具体介绍详解—>学习数据结构 反正搞懂它会感觉到非常的神奇)
B+tree
MyISAM引擎使用B+Tree
MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。
InnoDB引擎使用B+Tree
InnoDB的叶节点的data域存放的是数据,相比MyISAM效率要高一些,但比较占硬盘内存大小。
关于这方面常见的面试题:(嘿嘿)
谈谈你对b树和b+树的理解:
三个方面回答(答案来源于抖音–>@跟着Mic学架构(java面试系列))
-
我们B树是一种多路平衡查找树 ,从在二叉树基础上增加一条规则–>二叉排序树(因为会出现斜树的情况)–>平衡二叉树(为了保持平衡引入左旋和右旋机制)–>然后B树是一种多路平衡二叉树,它满足平衡二叉树的所有规则,同时它也可以有多个子树,子树的数量取决于它关键字的数量。子路数量等于关键字数量+1。因此从这个特征来看,在存储同样数据量的情况下平衡二叉树它的高度是一定会大于B树的。而所谓的B+树是在B树的基础上做了增强。
两者最大的区别:B树的数据存储在每个节点上 而B+树中的每个数据是存储在叶子节点上,并且通过双向链表的方式把叶子节点的所有数据进行连接(属于InnoDB的特征)。B+树的子路数量是等于它关键字的数量。
-
B树和B+树一般是应用在文件系统和数据库系统中,用来去减少磁盘IO所带来的性能损耗的一个机制。以Mysql中Innob为例,当我们去通过select语句去查询一条数据的时候,InnoDB需要去磁盘上去读取数据而这个过程会涉及到磁盘I/O以及磁盘的随机I/O。我们知道,磁盘I/O的性能是特别低的,特别是随机磁盘的I/O,为了去理解为什么性能低呢,我们来看一下磁盘IO的工作原理。首先,系统会把数据的逻辑地址传给磁盘,磁盘控制电路按照寻址的逻辑把逻辑地址翻译成物理地址,也就是确定要读取的数据在哪个磁道哪个扇区,为了读取这个扇区的数据,需要把磁头放在这个扇区的上面,为了实现这个点,磁盘会不断的去旋转。把目标的扇区旋转到磁头的下面,使得磁头能够去找到对应的磁道,这里会涉及到寻道的时间和旋转时间的一个损耗。很明显,磁盘I/O这个过程的性能开销是非常大的特别是查询的数据量比较多的情况下,所以在InnoDB里面干脆对存储在磁盘上的数据建立一个索引,然后把索引数据以及索引列对应的磁盘地址以B+树的方式进行存储。当我们需要查找目标数据的时候,我们根据索引从B+树中查找目标数据就可以了。由于B+树的子路比较多,所以只需要较少次数的磁盘IO就能查找到目标数据。
-
为什么要用B树或者B+树来做索引结构呢,原因是AVL树的高度要比B树或B+树的高度高,而高度就意味着磁盘IO的数量,所以为了减少磁盘IO的次数,文件或数据库系统才会使用B树或者B+树来做数据结构。
视图
(一)视图概述
- 视图view是一个虚拟表,非真实存在,其本质是根据sqL语句获取动态的数据集,并为其命名,用户使用时只需使用视图名称即可获取结果集,并可以将其当作表来使用。
- 数据库中只存放了视图的定义,而并没有存放视图中的数据。这些数据存放在原来的表中。
- 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。因此,视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变,显示在视图中的数据也会发生改变。
(二)视图的作用
- 简化代码,可以把重复使用的查询封装成视图重复使用,同时可以使复杂的查询易于理解和使用。
- 安全原因,如果一张表中有很多数据,很多信息不希望让所有人看到,此时可以使用视图视,如:社会保险基金表,可以用视图只显示姓名,地址,而不显示社会保险号和工资数等,可以对不同的用户,设定不同的视图。
(三)视图的基本操作
1. 创建视图
创建视图的语法为:
create [or replace]
view view_name [(column_list)]
as select_statement
[with [cascaded | local] check option]
参数说明:
(1)view_name :表示要创建的视图名称。
(2)column_list:可选项,指定视图中各个属性的名词,默认情况下与SELECT语句中的查询的属性相同。
(3)select_statement:表示一个完整的查询语句,将查询记录导入视图中。
(4)[with [cascaded | local] check option]:可选项,表示更新视图时要保证在该视图的权限范围之内。
当使用WITH CHECK OPTION子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。因为mysql允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项:LOCAL和CASCADED。如果我们没有在WITH CHECK OPTION子句中显式指定关键字,则mysql默认使用CASCADED。
当我们操作当前视图时,cascaded检查选项是,如果当前视图有检查选项,则插入数据要满足包括当前视图条件以及满足当前视图所依赖的视图的条件。如果当前视图没有检查选项,则插入数据要满足当时视图所依赖视图有检查选项及其依赖的视图的条件。
当我们在操作当前视图时,local检查选项是递归的查找当前视图所依赖的视图是否有检查选项,如果有,则检查;如果没有,就不做检查。
示例:
create or replace view view1_emp
as
select ename,job from emp;
-- 查看表和视图
show full tables;
select * from view1_emp;
2. 修改视图
修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时,可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW(存在则替换)或ALTER VIEW语句来修改视图。
语法
alter view 视图名 as select语句
示例
alter view view1_emp
as
select a.deptno,a.dname,a.loc,b.ename,b.sal from dept a, emp b where a.deptno = b.deptno;
3. 更新视图
某些视图(行列子集视图)是可更新的。也就是说,可以在UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。如果视图包含下述结构中的任何一种,那么它就是不可更新的:
- 聚合函数(SUM(), MIN(), MAX(), COUNT()等)
- DISTINCT
- GROUP BY
- HAVING
- UNION或UNION ALL
- 位于选择列表中的子查询
- JOIN
- FROM子句中的不可更新视图
- WHERE子句中的子查询,引用FROM子句中的表。
- 仅引用文字值(在该情况下,没有要更新的基本表)
视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,就可能会造成数据更新失败。
示例
create or replace view view1_emp
as
select ename,job from emp;
update view1_emp set ename = '周瑜' where ename = '鲁肃'; -- 可以修改
insert into view1_emp values('孙权','文员'); -- 不可以插入
3. 其它操作
- 重命名视图
-- rename table 视图名 to 新视图名;
rename table view1_emp to my_view1
- 删除视图
-- drop view 视图名[,视图名…];
drop view if exists view_student;
删除视图时,只能删除视图的定义,不会删除数据。
end
嘤嘤嘤 呜呜呜