老杜MySQL第四天笔记

索引

基本介绍

索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制

一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引，索引就相当于一本书的目录，是为了缩小扫描范围而存在的一种机制

就好比一本字典来说，查找某个汉字有两种方式：

第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。效率比较低。

​ 第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过索引检索，效率较高。

• select * from t_user where name = ‘jack’;

以上的这条SQL语句回去name字段上扫描，如果name字段上没有添加索引（目录），或者说没有给name字段创建索引。MySQL会进行全扫描，会将name字段上的每一个值都比对一遍，效率比较低

MySQL在查询方面主要就是两种方式：

第一种方式：全表扫描

第二种方式：根据索引检索

注意：

在实际中，汉语字典前面的目录是排序的，按照a b c d e f…排序，为什么排序呢？

因为只有排序了才会有区间查找这一说！（缩小扫描范围其实就是扫描某个区间罢了！）

在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql当中索引是一个B-Tree数据结构。

遵循左小右大原则存放。采用中序遍历方式遍历取数据。

索引的实现原理

• 假设有一张用户表：t_user

提醒一：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK主键。另外在MySQL中，一个字段上如果****有unique约束的话，也会自动创建索引对象

即：主键、unique约束会创建索引

**提醒二：**在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号

**提醒三：在MySQL当中，索引是一个单独的对象，**不同的存储引擎以不同的形式存在

在MyISAM存储引擎中，索引存储在一个.MYI文件中

在InnoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace的当中

在MEMORY存储引擎当中，索引被存储在内存当中

总之，不管索引存储在哪里，索引在MySQL当中都是一个树的形式存在（自平衡二叉树：B-Tree）。底层：B-Tree二叉树

什么情况下给字段添加索引

• 在MySQL当中，主键上、以及unique字段上都会自动添加索引的

什么情况下，我们会考虑给字段添加索引：

1. 数据量庞大
2. 该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描
3. 该字段很少的DML（insert delete update）操作，因为DML之后，索引需要重新排序

• 建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能
• 建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的

索引的创建和删除

创建索引

• create index emp_ename_index on emp(ename);
• 给emp表的ename字段添加索引，别名：emp_ename_index

删除索引

• drop index emp_ename_index on emp;
• 将emp表上的emp_ename_index索引对象删除

MySQL检索是否使用了索引

• explain select * from emp where ename = ‘KING’;

• type = ALL 代表没有使用索引
• 自己创建一个索引，再检查
• create index emp_ename_index on emp(ename);
• explain select * from emp where ename = ‘KING’;

• type = ref 代表使用了索引

索引失效

第一种情况

• explain select * from emp where ename like ‘%T’;

• 我们发现索引失效了，这是****因为模糊匹配当中以’%'开头了
• 我们要尽量避免模糊查询的时候以’%'开始，这是一种优化的手段/策略

第二种情况

• explain select * from emp where ename = ‘KING’ or job = ‘MANAGE’;

• **使用or的时候会失效，**如果使用or的话那么要求两边的条件的字段都要有索引，才会走索引，如果其中一边的字段没有索引，但是另一边字段上的索引还是会实现

第三种情况

复合索引：两个字段或者更多的字段联合起来添加一个索引

• create index emp_job_sal_index on emp(job, sal);
• explain select * from emp where job = ‘MANAGER’;

• explain select * from emp where sal = 800;

• 我们发现使用左边的字段查找，使用索引，使用右边的字段查找，索引失效
• 这是因为****在使用复合索引的时候，没有使用左侧的列查找，索引失效

第四种情况

• 在where当中索引列参加了运算，索引失效

第五种情况

• 在where当中索引列使用了函数

索引的分类

• 索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引
• 索引在数据库当中的分类

1. 单一索引：一个字段上添加索引
2. 复合索引：两个字段或者更多的字段上添加索引
3. 主键索引：主键上添加索引
4. 唯一性索引：具有unique约束的字段上添加索引

注意：唯一性比较弱的字段上添加索引用处不大

视图

• 视图就是view：站在不同的角度去看待同一份数据

创建视图和删除视图

创建视图对象

• create view dept2_view as select * from dept2;

删除视图对象

• drop view dept2_view

注意：只有DQL语句才能以view的形式创建

即create view view_name as 之后的语句必须是DQL语句，即select语句

视图基本操作

• **我们可以面向视图对象进行增删改查，对视图对象的增删改查，**会导致原表被操作

• 视图的特点：通过对视图的操作，会影响到原表数据

• 面向视图查询

select * from dept2_view;

• 面向视图查询

insert into dept2_view(deptno, dname, loc) values (60, ‘SALES’, ‘BEIJING’);

• 查询原表数据

select * from dept2;

• 面向视图删除

delete from dept2_view;

• 查询原表数据

select * from dept2;

• 创建视图对象

• 查询视图对象

select * from emp_dept_view;

• 面向视图更新

update emp_dept_view set sal = 1000 where dname = ‘ACCOUNTING’;

• 原表数据被更新

select * from emp;

视图在实际开发中的作用

假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。

​ 每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？

​ 可以把这条复杂的SQL语句以视图对象的形式新建。

​ 在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。

​ 并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是存储在硬盘上的，不会消失。

再提醒一下：

​ 视图对应的语句只能是DQL语句。

​ 但是视图对象创建完成之后，可以对视图进行增删改查等操作。

小插曲：

​ 增删改查，又叫做：CRUD。

​ CRUD是在公司中程序员之间沟通的术语。一般我们很少说增删改查。

​ 一般都说CRUD。

​ C:Create（增）

​ R:Retrive（查：检索）

​ U:Update（改）

​ D:Delete（删）

DBA常用命令

数据导出

• mysqldump bjpowernode > D:\bjpowernode.sql -u root -p123456
• 注意：这个要在windows的DOS命令窗口中
• 也可以到处指定的表：mysqldump bjpowernode emp > D:\bjpowernode.sql -u root -p123456

数据导入

• 需要先登录到MySQL数据库服务器上
• 然后创建数据库：create database bjpowernode
• 使用数据库：use bjpowernode
• 然后初始化数据库：source D:\bjpowernode.sql

其他的具体看文档

数据库设计三范式

基本介绍

• 数据库设计范式：数据库表的设计一句，教你怎么进行数据库表的设计

数据库设计范式共有3个

第一范式：要求任何一张表必须有主键，每一个字段****原子性不可再分

**第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，**不要产生部分依赖

**第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，**不要产生传递依赖

• 设计数据库表的时候，按照以上的范式进行，可以避免表中数据的冗余，空间的浪费

第一范式

• 最核心、最重要的范式，所有表的设计都需要满足。必须有主键，并且每一个字段都是原子性不可再分

例：

• **以上的学生表不满足第一范式，因为第一没有主键，**第二联系方式可以分为邮箱地址和电话

第二范式

• 建立在第一范式的基础之上，要求所有非主键字段必须完全依赖主键，不要产生部分依赖

• 上述这张表描述了学生和老师的关系：（1个学生可能有多个老师，1个老师有多个学生）
• **这是非常典型的：**多对多关系
• 以上表不满足第一范式

以学生编号和教师编号，两个字段联合做主键，是复合主键（PK：学生编号+教师编号）

经过修改之后，以上的表满足了第一范式，但是不满足第二范式

因为"张三"依赖1001，而"王老师"依赖001，产生了****部分依赖

产生部分依赖的缺点：数据冗余了，空间浪费了，张三和王老师都重复了

• 使用三张表来表示多对多的关系

• **口诀：**多对多，三张表，关系表两个外键

第三范式

• 第三范式建立在第二范式的基础之上，要求所有非主键字典必须直接依赖主键，不要产生传递依赖

• 以上表是一对多关系，满足第一范式（有主键），也满足第二范式（因为主键不是复合主键，没有产生部分依赖）主键是单一主键
• 不满足第三范式（因为一年一班依赖01，01依赖1001，产生了传递依赖）【即一年一班收到班级编号影响，然后班级编号又依赖学生编号PK】

• **口诀：**一对多，两张表，多的表加外键

总结表的设计

一对多：一对多，两张表，多的表加外键

多对多：多对多，三张表，关系表两个外键

一对一：外键唯一

• 解释****一对一

由于在实际的开发中，可能存在一张表字段太多，太庞大，这个时候要拆分表

总结

数据库设计三范式是理论上的

实践和理论有的时候有偏差

最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度

因为在SQL当中，表和表之间连接次数越多，效率越低（笛卡尔积）

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，SQL语句的编写难度也会降低