MySQL入门
索引
什么是索引?
- 索引是各种数据库进行优化的重要手段!
- 索引是在数据库表的字段上添加的,是为了提高查询效率而存在的一种机制。
- 一张表的一个字段可以添加一个索引,多个字段联合起来也可以添加索引。
- 索引就相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。
例子:对于一本字典来说,查找某个汉字有两种方式:
第一种:一页一页挨着找,知道找到为止,这种查找方式属于全字典扫描,效率较低。
第二种:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个位置,做局域性扫描,缩小扫描的范围,快速的查找,这种查找方式属于通过索引检索,效率较高。
注意:在实际开发中,汉语字典前面的目录是排序的,按照 a、b、c… 排序,为什么排序?因为只有排序了才会有区间查找这一说法(缩小扫描范围其实就是扫描某个区间罢了)
- 在mysql 数据库当中,索引也是需要排序的,并且这个索引的排序和 TreeSet 数据结构相同,TreeSet 底层是一个自平衡的二叉树!在 mysql 中索引是一个 B-Tree 数据结构。
- 在mysql中,主键字段和unique字段上都会自动添加索引
索引实现原理
假设现在有一张 t_user 表:
id(PK) name 每一个行记录在硬盘上都有物理存储编号
--------------------------------------------------------------
100 zhangsan 0x1111
120 lisi 0x2222
99 wangwu 0x3333
88 zhaoliu 0x6666
101 houqi 0x2332
55 wuba 0x9999
100 xiaojiu 0x6954
说明一:
1、在任何数据库表当中,主键都会自动添加索引,所以这里的 id 字段上自动会有索引。
2、在 mysql 当中,一个字段上如果有 unique 约束的话,也会自动创建索引对象。
说明二:
1、在任何数据库当中,任何一张表的任何一条记录,在硬盘的存储上都有一个硬盘的物理存储编号。
说明三:
1、在 mysql 中,索引是一个单独的对象,不同的存储引擎以不同的形式存储:
在 MyISAM 存储引擎中,索引存储在一个 .MYI 文件中;
在 InnoDB 存储引擎中,索引存储在一个逻辑名称叫做 tablespace 的文件中;
在 MEMORY 存储引擎中,索引被存储在内存中
2、不管索引存储在哪里,索引在 mysql 中都是一个树的形式存在(自平衡二叉树,B-Tree)
这个图应该不是那么的准确,大家大概了解一下就好了
什么条件下,我们会考虑给字段添加索引?
条件一:数据量庞大(多少数据才算庞大?这个不能确定,需要测试,因为每一个硬件环境不同)
条件二:该字段经常出现在 where 的后面,以条件的方式存在,也就是说这个字段总是被扫描。
条件三:该字段很少的 MDL(insert、update、delete)操作(因为 DML 之后,索引需要重新进行排序,如果频繁执行 DML,那么索引就需要频繁的排序,这样会拖慢查询速度)
建议:不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能;建议查询的时候通过主键或unique约束的子弹进行查询,这样的效率是比较高的。
索引的创建与删除
语法案例
-- 查看索引
show index from emp;
-- 创建索引
-- 给 emp 表的 ename 字段添加索引,起名为:emp_ename_index
create index emp_ename_index on emp(ename);
-- 删除索引
-- 删除 emp 表中的 emp_ename_index 索引
drop index emp_ename_index on emp;
在 mysql 中,怎么查看一个 SQL 语句是否使用了索引进行检索?
未添加索引的效果:
-- 我现在的 emp 表中有 14 行数据
explain select * from emp where ename = 'KING';
根据上图,我们可以看到 rows = 14、type = ALL 也就是说这条 SQL 语句是全表检索。
添加索引之后的效果:
-- 添加索引
create index emp_ename_index on emp(ename);
-- 查看结果
explain select * from emp where ename = 'KING';
根据上图,我们可以看到 rows = 1,也就是说这条 SQL 只检索了一条数据。
索引的失效
索引说明时候会失效呢?
情况一
- 以百分号(%)开头就会导致索引失效。
select * from emp where ename like "%T";
问:上述语句中,ename 上即使添加了索引,也不会走索引,为什么呢?
解:原因是因为模糊查询当中以 “%” 开头了(百分号开头表示不知道第一个字符是什么,所以每次都需要先去获取到对应的字符,然后才开始塞选)
> 所以在使用模糊查询的时候,需要尽量避免使用 “%” 开头(这是一种优化的手段/策略),如果一定要使用的话,那么就只能全表检索了。
情况二
-
在使用 or 的时候会失效,如果使用 or,那么要求 or 两边的字段都要有索引,如果其中一边的一个字段没有索引,那么另一个字段上的索引也会失效,所以这就是不建议使用 or 的原因。
union 不会使索引失效。
explain select * from emp where ename = 'KING' or job = 'MANAGER';
情况三
- 使用复合索引的时候,没有使用左侧的列进行查找,索引就会失效!
- 复合索引:两个字段或更多字段联合起来添加一个索引,叫做复合索引。
-- 创建复合索引
create index emp_job_sal_index on emp(job, sal);
-- 使用了索引(复合索引左边的列):
explain select * from emp where job = 'MANAGER';
explain select * from emp where job = 'MANGER' and sal = 800;
-- 未使用索引(复合索引右边的列):
explain select * from emp where sal = 800;
-- or 如果想要使用索引,就必须保证两边的字段都是有索引的,而这里的复合索引中,右边的索引字段是不能单独使用的,如果单独使用,则不会具有索引
explain select * from emp where job = 'MANGER' or sal = 800;
情况四
- 在 where 当中索引列字段参加了运算,索引就会失效
-- 删除刚才创建的复合索引
drop index emp_job_sal_index on emp;
-- 给 sal 列添加索引
create index emp_sal_index on emp(sal);
-- 使用了索引:
explain select * from emp where sal = 800;
-- 没有使用索引:
explain select * from emp where sal + 1 = 800;
-- 如果是值进行了运算,则不会导致索引失效,比如:
explain select * from emp where sal = 800 + 1;
情况五
- 在 where 当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';
…
索引的分类
- 单一索引:一个字段上添加索引。
- 复合索引:两个字段或更多字段添加索引。
- 主键索引:主键上添加索引。
- 唯一性索引:具有 unique 约束的字段上添加约束。
…
注意:唯一性比较弱的字段上添加索引用处不大(简单理解:在一列中如果有很多重复值,那么此时索引的作用并不大;所以索引添加的字段越唯一,效率就越高!)。
视图(view)
什么是视图?
- view:站在不同的角度去看待同一份数据。
- 视图对象也是一个文件,在数据库中也是以文件的形式存在的,并不会因为重启 MySQL 而消失。
如何创建视图对象?
- 语法:
create view 视图名称 as select * from 表名;
create view dept_bak_view as select * from dept_bak;
注意:只有 DQL 语句才能以 view 的形式创建视图,也就是说 as 后面的语句必须是 DQL 语句(查询语句)
如何删除视图对象?
- 语法:
drop view 视图名;
drop view dept_bak_view;
有了视图之后,可以干什么?
-
我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作。
-
视图的特点:通过对视图的操作,会影响到原表数据。
注意:视图可以影响到原表数据,原表也可以影响视图中的数据。
-
作用:提高检索效率,隐藏表的实现细节。
---------------------------单表---------------------------
-- 面向视图查询
select * from dept_bak_view;
-- 面向视图插入
insert into dept_bak_view(deptno, dname, loc)
values(50, 'SALES', 'BEIJING');
-- 查询原表数据
select * from dept_bak;
-- 面向视图删除
delete from dept_bak_view;
-- 查询原表数据
select * from dept_bak;
---------------------------多表---------------------------
-- 创建视图对象
create view emp_dept_view as
select e.ename, e.sal, d.dname from emp e
inner join dept d
on e.deptno = d.deptno;
-- 查询视图对象
select * from emp_dept_view;
-- 更新视图对象
update emp_dept_view set sal = 1000 where ename = 'TEST_name';
-- 查询原表数据
select e.ename, e.sal, d.dname from emp e
inner join dept d
on e.deptno = d.deptno;
看了以上案例,视图对象在实际开发中到底有什么用呢?
-
假设有一条非常复杂的 SQL,而这条 SQL 语句需要在不同的位置上反复使用吗,每一次使用这个 SQL 语句的时候都需要重新编写,非常的麻烦,这个时候就可以使用视图。
将这条复杂的 SQL 语句以视图对象的形式新建,在需要编写这条 SQL 语句的位置直接使用视图对象,可以大大的简化开发,并且利于后期的维护,因为修改的时候也只需要修改一个位置就行,只需要修改视图对象所映射的 SQL 语句。
说明
- 以后再面向视图开发的时候,使用视图的时候就可以像使用表一样;
- 可以对视图进行增删改查等操作,视图不是在内存中的,而是存储在硬盘中的,不会消失。
什么是CRUD?
- 增删改查又被称为:CRUD
- CRUD 是在公司中程序员之间沟通的术语,一般我们很少说增删改查,一般都是说 CRUD
- C:Create(增)
- R:Retrive(查 / 检索)
- U:Update(改)
- D:Delete(删)
DBA 常用命令(了解)
1、创建数据库账号密码
语法:CREATE USER 用户名 IDENTIFIED BY '密码';
create user heweibin identified by '1234';
2、数据的导出
语法:mysqldump 数据库名>路径:\文件名.sql -u 用户名 -p 密码
-- 导出指定数据库
mysqldump test_sql>D:\test.sql -u root -p 1234
-- 导出指定数据库下指定的表
mysqldump test_sql emp>D:\emp.sql -u root -p 1234
3、数据的导入
步骤:
1. 先登录到 mysql 数据库服务器上
2. 然后创建数据库:create database test_sql
3. 使用数据库:use test_sql
4. 初始化数据库:source D:\test.sql
数据库设计的三大范式
什么是数据库设计范式?
- 数据库表的设计依据,教我们怎么进行数据库表的设计。
说明
- 第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分!
- 第二范式:建立在第一范式的基础之上,要求所有非主键字段,完全依赖主键,不要产生部分依赖!
- 第三范式:建立在第二范式的基础之上,要求所有非主键字段,直接依赖主键,不要产生传递依赖!
- 设计数据库表的时候,按照上面的三大范式进行设计,可以避免表中数据的冗余,空间的浪费。
第一范式
- 最核心,最重要的范式,所有表的设计都需要满足。
- 必须要有主键,并且每一个字段都是原子性不可再分。
学生编号 学生姓名 联系方式
----------------------------------------------------
1001 张三 zs@123.com, 13912345678
1002 李四 ls@123.com, 16612345678
1003 王五 ww@123.com, 19912345678
问:以上为学生表,满足第一范式吗?
解:不满足
1、没有主键
2、联系方式可以分为邮箱地址和电话
将上面的表修改为满足第一范式的:
学生编号(PK) 学生姓名 邮箱地址 联系电话
------------------------------------------------------------
1001 张三 zs@123.com 13912345678
1002 李四 ls@123.com 16612345678
1003 王五 ww@123.com 19912345678
第二范式
- 建立在第一范式的基础之上,要求所有非主键字段必须完全依赖主键,不能产生部分依赖。
学生编号 学生姓名 教师编号 教师姓名
------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系:一个学生可能有多个老师,一个老师可能有多个学生(这是典型的多对多关系)
问:以上为学生表,满足第一范式吗?
解:不满足
1、没有主键
2、联系方式可以分为邮箱地址和电话
将上面的表修改为满足第一范式的:
学生编号 + 教师编号(PK) 学生姓名 教师姓名
-----------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
》说明:将学生编号和教师编号联合做主键:复合主键,此时就是满足第一方式的
问:修改后的表结构是否满足第二范式?
解:不满足
原因:张三依赖 1001,王老师依赖 001,显然产生了部分依赖(产生部分依赖的缺点:数据冗余了,空间浪费了)
修改为满足第二范式的表:
使用三张表来表示多对多的关系,分别为:学生表、教师表、学生教室关系表
学生表:
学生编号(PK) 学生姓名
-------------------------
1001 张三
1002 李四
1003 王五
教师表:
教师编号(PK) 教师姓名
---------------------------
001 王老师
002 赵老师
学生教师关系表:
id(PK) 教师编号(FK) 学生编号(FK)
-------------------------------------------------
1 001 1001
2 002 1002
3 001 1001
4 002 1002
多对多口诀:多对多,三张表,关系表两个外键!
第三范式
- 第三范式建立在第二范式的基础之上,要求所有非主键字段必须直接依赖主键,不能产生传递依赖
学生编号(PK) 学生姓名 班级编号 班级名称
------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
问:以上设计的表,满足第一范式吗?
解:满足
问:以上设计的表,是否满足第二范式?
解:满足,因为主键不是复合主键,没有产生部分依赖,主键是单一主键
问:以上设计的表,是否满足第三范式?
解:不满足
原因:一年一班依赖 01, 01 依赖 1001,产生了传递依赖,不符合第三范式的要求,产生了数据的冗余。
将上面的表修改为满足第三范式的:
班级表:
班级编号(PK) 班级名称
----------------------------
01 一年一班
02 一年二班
03 一年三班
学生表:
学生编号(PK) 学生姓名 班级编号(FK)
--------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
学生表和班级表的关系是:一个班级对应多个学生,一个学生对应一个班级(是典型的一对多关系)
一对多口诀:一对多,两张表,多的表加外键
总结
-
一对多:一对多,两张表,多的表加外键!
-
多对多:多对多,三张表,关系表加两个外键!
-
一对一:一对一,外键唯一!
一对一放在一张表中不就好了吗?为什么还要拆分表?
在实际开发中,可能存在一张表字段太多,太庞大了,这个时候就需要拆分表。
其它说明
- 数据库设计的三大范式是理论上的。
- 实践和理论有的时候有偏差。
- 最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度,因为在 SQL 中,表和表之间连接次数越多,效率越低(笛卡尔积)。
- 有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,并且对于开发人员来说, SQL 语句的编写难度也会降低。