1、索引
1.1、什么是索引?
索引是在数据库表的字段上添加的,是为了提高效率存在的一种机制
一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引
索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制
对于一本字典来说,查找某个汉字有两种方式:
第一种方式:一页一页挨着找,知道找到为止,这种查找方式属于全字典扫描,效率比较低
第二种方式:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个位置,做
局域性扫描,缩小扫描的范围,快速的查找。这种查找方式属于通过索引检索,效率较高
select * from t_user where name = 'jack';
以上这条sql语句回去name字段上扫描,为什么?
因为查询条件是:name='jack'
如果name字段上没有添加索引(目录),或者说没有给name字段创建索引
mysql会进行全扫描,会将name字段上的每一个值都比对一遍
mysql在查询方法就是两种方式:
第一种方式:全表扫描
第二种方式:根据索引检索
注意:
在实际中,汉语字典前面的目录是排序的,按照a b c d....排序,为什么排序呢?
因为只有排序了才会有区间查找这一说!(缩小扫描范围,其实就是扫描某个
区间罢了!)
在mysql数据库当中索引也是需要排序的,并且这个索引的排序和TreeSet数据
结构相同。TreeSet底层是一个自平衡的二叉树!在mysql当中索引是一个B_Treeeeeeeeeeeee
数据结构
遵循左小右大原则存放
1.2、索引的实现原理?
提醒1:在任何数据库当中主键上都会自动添加对象,id字段上有索引,因为id是PK
另外在mysql当中,一个字段上如果有uniqe约束的话,也会自动创建索引对象
提醒2:在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有一个硬盘
的物理存储编号
提醒3:在mysql当中,索引是一个单独的对象,不同的索引引擎以不同的方式存在,
在MyISAM存储引擎中,索引存储在一个.MYI文件中。在InnoDB存储引擎中,索引
存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储
在内存当中。不管索引存储在哪里,索引在mysql当中都是一个树的形式存在
1.3、在mysql当中,主键上,以及unique字段上都会自动添加索引的
什么条件下,我们会考虑给字段添加索引呢?
条件1:数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不一样)
条件2:该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描
条件3:该字段很少的DML(insert delete uptade)操作(因为DML之后,索引需要重新排序)
建议不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能
建议通过主键查询,建议通过unique约束的字段进行查询,效率较高
1.4、索引怎么创建?怎么删除?语法是什么?
创建索引:
create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引,起名:emp_ename_index
删除索引:
drop index emp_ename_index on emp;
1.5、在mysql当中,怎么查看一个sql语句是否使用了索引?
语句:
explain select * from emp where ename = 'smith';
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
| 1 | SIMPLE | emp | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | Using where |
+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
这里type 对应的ref rows行数对应的1条
1.6、索引有失效的时候,什么时候索引失效呢?
失效的第一种情况:
explain select *from emp where ename like '%T';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
这里的type对应的是all 查询的行数是14
ename上即使添加了索引,也不会走索引,为什么?
原因是因为模糊匹配当中以“%”开头
尽量避免模糊查询的时候以“%”开始
这是一种优化手段
失效的第二种情况:
使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有索引,
这样才会走索引,如果一边有一边没有的话,不会走索引,索引这就是为什么
不建议使用or的原因
explain select * from emp where ename = 'smith' or job = 'manager';
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | emp_ename_index | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
explain select * from emp where ename = 'smith' union select * from emp where job = 'manager';
失效的第三种情况:
使用复合索引的时候,没有使用左侧的列查找,索引失效
什么是符合索引?
两个字段,或者更多的字段联合起来添加一个索引,叫做符合索引
create index emp_job_sal_index on emp(job,sal);
explain select * from emp where job='manager';
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
| 1 | SIMPLE | emp | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | Using where |
+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
explain select * from emp where sal='800';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
失效的第四种情况:
在where当中索引列参加了运算,索引失效
create index emp_sal_index on emp(sal);
explain select * from emp where sal = 800;
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
| 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
explain select * from emp where sal+1 = 800;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
失效的第五种情况:
在where当中索引使用了函数
explain select * from emp where lower(name) = 'smith';
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1.7、索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
索引在数据可当中分了很多类?
单一索引:一个字段上添加索引
复合索引:两个字段或者更多的字段上添加索引
主键索引:主键上添加索引
唯一性索引:具有unique约束的字段上添加索引
注意:唯一性比较弱的字段上添加索引用处不大。
2、视图
2.1、什么是视图?
view:站在不同的角度去看待同一份数据
2.2、怎么创建视图对象?怎么删除视图对象?
创建视图对象:
create view view_emp2 as select * from emp2;
删除视图对象:
drop view view_emp2;
注意:只有DQL语句才能以view的形式创建
2.3、用视图做什么?
我们可以面向视图对象进行增删改查,对视图对象的增删改查,
会导致原表被操作!(视图的特点:通过对视图的操作,会影响到原表数据)
创建视图:
create view emp2_view as select * from emp2;
//创建视图之后,可以对视图进行增删改查的操作,然后会改变原表
insert into emp2_view(empno) values(0001);
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+--------+-----------+------+------------+---------+---------+--------+
2.4、视图对象在实际开发中到底有什么?《方便,简化,利于维护》
假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用, 每一次使用
这个sql语句的时候都需要重新编写,很长,很麻烦,怎么办?
可以把这条复杂的SQL语句以视图对象的形式新建。
在需要编写这条SQL语句的位置直接使用视图对象,可以大大简化开发。并利于后期的维护
因为修改的时候也只需要修改一个位置就行,只需要修改视图对象锁映射的SQL语句
我们以后面向视图开的时候,使用视图的时候像使用table一样。可以对视图进行增删改查等操作
视图不是在内存当中,视图对象也是存储在硬盘上的,不会消失
在提醒一下:
视图对应的语句只能是DQL语句
但是视图对象创建完成之后,可以对象视图进行增删改查等操作
增删改查又叫做:CRUD
C:Create(增)
R:Retrive(查)
U:Update(改)
D:Delete(删)
3、DBA常用命令?
重点掌握:
数据的导入和导出(数据的备份)
数据导出?
注意:在dos命令窗口中 不是在mysql下:
mysqldump zheng>D:\zheng.sql -uroot -p密码;
导出指定的表
mysqldump zheng emp>D:\zheng.sql -uroot -p密码;
数据导入?
注意:需要先登录到mysql数据库服务器上
然后创建数据库:create database zheng;
使用数据库:use zheng;
然后初始化数据库:
source D:\zheng.sql
4、数据库设计三范式(面试官经常问)
4.1、什么是数据库设计范式?
数据库表的设计依据,教你怎么进行数据库表的设计
4.2、数据库设计范式共有3个
第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分
第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖
第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖
设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费
第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。
口诀:多对多?三张表,关系表两个外键。
t_student学生表
sno(pk) sname
-------------------
1 张三
2 李四
3 王五
t_teacher 讲师表
tno(pk) tname
---------------------
1 王老师
2 张老师
3 李老师
t_student_teacher_relation 学生讲师关系表
id(pk) sno(fk) tno(fk)
----------------------------------
1 1 3
2 1 1
3 2 2
4 2 3
5 3 1
6 3 3
第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。
口诀:一对多?两张表,多的表加外键。
一
班级t_class
cno(pk) cname
--------------------------
1 班级1
2 班级2
多 一个班对应多个学生
学生t_student
sno(pk) sname classno(fk)
---------------------------------------------
101 张1 1
102 张2 1
103 张3 2
104 张4 2
105 张5 2
提醒:在实际的开发中,以满足客户的需求为主,有的时候会拿冗余换执行速度。
4.3、总结表的设计?
一对多:
一对多,两张表,多的表加外键
多对多:
多对多,三张表,关系表两个外键
一对一:
在实际的开发中,可能存在一张表的字段太多,太庞大,这个时候就要拆分表
一对一怎么设计?
一对一设计有两种方案:主键共享
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk+fk) realname tel ....
------------------------------------------------
1 张三 1111111111
2 李四 1111415621
一对一设计有两种方案:外键唯一。
t_user_login 用户登录表
id(pk) username password
--------------------------------------
1 zs 123
2 ls 456
t_user_detail 用户详细信息表
id(pk) realname tel userid(fk+unique)....
-----------------------------------------------------------
1 张三 1111111111 2
2 李四 1111415621 1