目录
第二种情况:使用了 or,要是使用or要求or两边都使用了索引时才会走索引
一、索引 index
1、什么是索引?
- 索引是在数据库表的字段上添加的,是为了缩短扫描范围的一种机制。
- 一张表的一个字段可以添加一个索引,也可以几个字段联合起来添加索引
MySQL 在查询方面主要就两种方式:
- 全表查询
- 通过索引检索
没有索引,MySQL 不得不首先以第一条记录开始,然后读完整个表直到它找出相关的行
mysql数据库中的索引也需要排序,这个索引的排序和treeset数据结构相同
TreeSet(TreeMap) 底层是一个自平衡的二叉树
在mysql当中索引是一个B-tree 数据结构
遵循左大右小原则存放,采用中序遍历方式遍历数据
2、索引的使用原理
主键、ID、unique都会自动添加上索引字段
在任何数据库当中,每个表的每个记录都在硬盘存储上都有一个硬盘的物理存储编号。
MySQL中,索引是一个单独的对象,索引在不同的存储引擎中以不同形式存在
- 在mylsam存储引擎中,索引存储在一个.myi的文件上
- 在innoDB存储引擎中,索引存储在一个逻辑名称叫做tablespace中
- 在memory存储引擎中,索引存储在内存中
- 不管索引存储在哪,索引在mysql中都是以一个树的形式存在(自平衡二叉树 :B-tree)
3、索引的创建
什么条件下,我们会考虑给字段添加索引呢?
- 数据量庞大
- 该字段经常出现在where的后面,也就是说这个字段总是被扫描。
- 该字段很少的DML(insert delete update)操作。(因为DML之后,索引需要重新排序)
- 建议不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能。
- 建议通过主键查询,建议通过unique约束的字段进行查询,效率是比较高的。
创建索引
create index 索引名 on 表名(字段名);//在表的某个字段上添加索引
create index emp_ename_index on emp(ename);
删除索引
drop index 索引名 on 表名;//把索引从表中删除
drop index emp_ename_index on emp;
查看索引
show index from emp;
在mysql当中,怎么查看一个SQL语句是否使用了索引进行检索?
explain select * from emp where ename='KING';
看到type = ALL说明没有使用了索引检索
4、索引失效的情况:
第一种情况:模糊匹配时以%开头
select * from emp where ename like '%A';
mysql> create index emp_ename_index on emp(ename);
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from emp where ename='king';
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | emp_ename_index | emp_ename_index | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where ename like '%g';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 14 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
第二种情况:使用了 or,要是使用or要求or两边都使用了索引时才会走索引
mysql> explain select * from emp where ename='king' or job = 'manager';
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | emp_ename_index | NULL | NULL | NULL | 14 | 16.43 | Using where |
+----+-------------+-------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
第三种情况:使用复合索引时没有使用左侧的列查找
复合索引:两个或多个字段联合起来建立一个索引
create index emp_ename_job_index on emp(ename,job);
explain select * from emp where ename='king';
explain select * from emp where job='manager';
第四种情况:在where当中索引项参与了运算
create index emp_sal_index on emp(sal);
explain select * from emp where sal = 800;
explain select * from emp where sal+1=801;
第五种情况:where当中索引项使用了函数
explain select * from emp where lower(ename)='king';
...
二、视图 view
视图:站在不同角度看同一份数据
1、视图对象的创建与删除
只有DQL语句(select)才能以view的形式创建
① 创建视图对象
create view dept2_view as select * from dept;
② 删除视图对象
drop view dept2_view;
2、用视图做什么?
可以面向视图对象进行增删改查,对视图对象的增删改查,会导致原表被操作!
① 面向视图查询
select * from dept2_view;
② 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,'saleman','beijing');
③ 面向视图删除
delete from dept2_view;
④ 面向视图更新
update dept2_view set loc='beijing' where deptno=40;
3、视图在实际开发中的作用
方便,简化开发,利于维护
假设有一条非常复杂的SQL语句,而这条SQL语句需要在不同的位置上反复使用,怎么办?
可以把这条复杂的SQL语句以视图对象的形式新建,可以大大简化开发。
并且利于后期的维护,因为修改的时候也只需要修改一个位置就行,只需要修改视图对象所映射的SQL语句。
使用视图的时候可以像使用table一样,可以对视图进行增删改查等操作。
视图和视图对象不是在内存当中,而是存储在硬盘上的,不会消失。
三、DBA常用命令
重点掌握:数据的导入和导出(数据的备份)其它命令了解一下即可。
数据导出?
在windows的dos命令窗口中:
mysqldump bjpowernode>E:\Mysql\bjpowernode.sql -uroot -p123456
可以导出指定的表吗?
mysqldump bjpowernode emp>E:\Mysql\bjpowernode.sql -uroot -p123456
数据导入?(SQL学习笔记(1)中有示例)
注意:需要先登录到mysql数据库服务器上。
然后创建数据库:create database bjpowernode;
使用数据库:use bjpowernode
然后初始化数据库:source D:\bjpowernode.sql
四、数据库设计三范式*****
1、数据库设计范式
数据库的设计依据
设计数据库表的时候,按照以上的范式进行,可以避免表中数据的冗余,空间的浪费。
2、第一范式
最核心、最重要的方式,所有表的设计都要满足:
必须有主键(PK),而且每个字段都是原子性不可再分
3、第二范式
没有主键, 不满足第一范式
解决方法:学生编号和教师编号联合组成复合编号
学生字段和教师字段都有对主键产生部分依赖的情况,不满足第二范式
(多对多)解决的办法:将冗余字段单独拿出来建立表
4、第三范式
建立在第二范式基础上的,非主键字段不能传递依赖于主键字段。(不要产生传递依赖)
示例:
解决的办法:将冗余字段单独拿出来建立表