SQL学习笔记（5）

一、索引 index

1、什么是索引？

• 索引是在数据库表的字段上添加的，是为了缩短扫描范围的一种机制。
• 一张表的一个字段可以添加一个索引，也可以几个字段联合起来添加索引

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

没有索引，MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行
mysql数据库中的索引也需要排序，这个索引的排序和treeset数据结构相同
TreeSet（TreeMap） 底层是一个自平衡的二叉树
在mysql当中索引是一个B-tree 数据结构

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

2、索引的使用原理

主键、ID、unique都会自动添加上索引字段

在任何数据库当中，每个表的每个记录都在硬盘存储上都有一个硬盘的物理存储编号。

MySQL中，索引是一个单独的对象，索引在不同的存储引擎中以不同形式存在

• 在mylsam存储引擎中，索引存储在一个.myi的文件上
• 在innoDB存储引擎中，索引存储在一个逻辑名称叫做tablespace中
• 在memory存储引擎中，索引存储在内存中
• 不管索引存储在哪，索引在mysql中都是以一个树的形式存在（自平衡二叉树 ：B-tree）

 3、索引的创建

什么条件下，我们会考虑给字段添加索引呢？

1. 数据量庞大
2. 该字段经常出现在where的后面，也就是说这个字段总是被扫描。
3. 该字段很少的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

数据导入？(中有示例）

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

四、数据库设计三范式*****

1、数据库设计范式

数据库的设计依据

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

2、第一范式

最核心、最重要的方式，所有表的设计都要满足：

必须有主键（PK），而且每个字段都是原子性不可再分

3、第二范式

没有主键， 不满足第一范式

解决方法：学生编号和教师编号联合组成复合编号

学生字段和教师字段都有对主键产生部分依赖的情况，不满足第二范式

（多对多）解决的办法：将冗余字段单独拿出来建立表

4、第三范式

 建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖）

示例：

解决的办法：将冗余字段单独拿出来建立表