一、索引
1、什么是索引
索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制。
在mysql数据库当中索引也是需要排序的,并且这个索引的排序和TreeSet
数据结构相同。TreeSet(TreeMap)底层是一个自平衡的二叉树!在mysql
当中索引是一个B-Tree数据结构。
2、索引的实现原理
(1)在任何数据库当中主键上都会自动添加索引对象。
另外在mysql当中,一个字段上如果有unique约束的话,也会自动创建索引对象。(2)在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有
一个硬盘的物理存储编号。(3)在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式
存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中。在InnoDB存储引擎中
索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引
被存储在内存当中。不管索引存储在哪里,索引在mysql当中都是一个树的形式
存在。(自平衡二叉树:B-Tree)
3、什么条件下,我们会考虑给字段添加索引呢?
条件1:数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不同)
条件2:该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描。
条件3:该字段很少的DML(insert delete update)操作。(因为DML之后,索引需要重新排序。)建议不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能。
建议通过主键查询,建议通过unique约束的字段进行查询,效率是比较高的。
结论:数据量大、该字段经常做为条件、该字段经常用于查询
4、索引的创建和删除
创建索引:
mysql> create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引,起名:emp_ename_index
语法:create index 索引名 on 表名(字段);
删除索引:
mysql> drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除。
语法:drop index 索引名 on 表名;
在mysql当中,怎么查看一个SQL语句是否使用了索引进行检索
使用 explain
explain select * from emp where ename = 'KING';
语法:explain 查询语句;
5、索引什么时候失效(列举了部分)
1、
ename上即使添加了索引,也不会走索引,为什么?
原因是因为模糊匹配当中以“%”开头了!
尽量避免模糊查询的时候以“%”开始。
演示:explain select * from emp where ename like '%T';
2、
使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有
索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个
字段上的索引也会实现。所以这就是为什么不建议使用or的原因。
演示:explain select * from emp where ename = 'KING' or job = 'MANAGER';
3、
使用复合索引的时候,没有使用左侧的列查找,索引失效
使用复合索引:create index emp_job_sal_index on emp(job,sal);
演示:explain select * from emp where job = 'MANAGER';-->不会失效
演示:explain select * from emp where sal = 800;-->会失效,因为没有使用左侧列
4、
在where当中索引列参加了运算,索引失效。
explain select * from emp where sal+1 = 800;
5、
在where当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';
二、视图
1、什么是视图?
view:站在不同的角度去看待同一份数据。
2、表复制
语法:create table emp as select * from emps;
创建一张emp表,并将emps里所有数据复制到emp里。
3、创建视图和删除视图
创建视图对象:
create view emp_view as select * from emp;
删除视图对象:
drop view emp_view;
注意:只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;
结论:视图就是和表复制差不多,区别在于创建的视图一旦改变数据那么元数据也会跟着变。
4、用视图做什么
我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致
原表被操作!(视图的特点:通过对视图的操作,会影响到原表数据。)
5、视图对象在实际开发中到底有什么用?
作用:方便,简化开发,利于维护
视图对象创建完成之后,可以对视图进行增删改查等操作。
三、DBA常用命令
重点掌握:
数据的导入和导出(数据的备份)
数据导出?
注意:在windows的dos命令窗口中:
mysqldump 数据库名>D:\数据库名.sql -uroot -p密码
可以导出指定的表吗?
mysqldump 数据库名 表名>D:\数据库名.sql -uroot -p密码
mysqldump命令是在bin目录下数据导入?
注意:需要先登录到mysql数据库服务器上。
然后创建数据库:create database 数据库名;
使用数据库:use 数据库名
然后初始化数据库:source D:\数据库名.sql
四、数据库设计三范式(掌握)
数据库设计范式共有?
第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分。第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,
不要产生部分依赖。第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,
不要产生传递依赖。声明:三范式是面试官经常问的,所以一定要熟记在心!
设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费。
表的设计口诀:
一对多:
口诀: 一对多,两张表,多的表加外键多对多:
口诀:多对多,三张表,关系表两个外键一对一:
一对一放到一张表中不就行了吗?为啥还要拆分表?
在实际的开发中,可能存在一张表字段太多,太庞大。这个时候要拆分表。
口诀:一对一,外键唯一(foreign key + unique)
牢记:
数据库设计三范式是理论上的。
实践和理论有的时候有偏差。
最终的目的都是为了满足客户的需求,有的时候会拿冗余换执行速度。
因为在sql当中,表和表之间连接次数越多,效率越低。(笛卡尔积)
有的时候可能会存在冗余,但是为了减少表的连接次数,这样做也是合理的,
并且对于开发人员来说,sql语句的编写难度也会降低。
三范式总结
第一范式:有主键,具有原子性,字段不可分割
第二范式:完全依赖,没有部分依赖
第三范式:没有传递依赖
数据库设计尽量遵循三范式,但是还是根据实际情况进行取舍,有时可能会拿冗余换速度,最终用目的要满足客户需求
小总结。