MySQL学习记录DAY8

十四、索引

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

一张表的一个字段可以添加一个索引，当然多个字段联合起来也可以添加索引

索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制

        用字典查找某个汉字有两种方式

        第一种方式：一页页找，全字典扫描，效率低

        第二种方式：先通过目录（索引）定位一个大概的位置，然后直接定位，做局域性扫描，效率较高

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

        ①：全表扫描

        ②：根据索引检索

⚠在实际中，汉语字典的目录是按照首字母排序的，因为只有有排序才会有区间查找一说

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

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

2、索引的实现原理

任何数据库当中主键上都会自动添加索引

 在mysql中，一个字段上如果有unique约束的话，也会自动创建索引

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

在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）

 实现原理就是缩小扫描的范围，避免全表扫描

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

条件1：数据量庞大

条件2：该字段经常出现在where后面，以条件的形式存在，也就是这个字段总是被扫描

条件3：该字段很少的DML操作（因为DML之后，索引需要重新排序）

建议不要随意添加索引，因为索引需要维护

4、索引创建和删除

create index emp_ename_index on emp(ename);

给emp的ename字段添加索引，取名为emp_ename_index

drop index emp_ename_index on emp;

将emp表上的emp_ename_index索引对象删除

5、如何查看sql语句是否使用了索引进行检索

explain select * from emp where ename = 'KING';

rows=14 所以没有进行索引

create index emp_ename_index on emp(ename);

创建索引后

explain select * from emp where ename = 'KING';

rows=1 所以使用了索引

6、什么时候索引会失效

①select * from emp where ename like '%T';

ename上即使添加了索引，也不会走索引

原因是模糊匹配当中以%开头

尽量避免模糊查询的时候以“%”开头，这是一种优化的手段/策略

②使用or的时候会失效，如果使用or，除非or两边的条件字段都要有索引，才会走索引，如果其中一边一个字段没有索引，那么另一个字段上的索引也会失效

此时可以使用union进行优化

③使用复合索引的时候，没有使用左侧的列查找，索引失效

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

create index emp_job_sal_index on emp(job,sal);

explain select * from emp where job = 'manager';

explain select * from emp where sal = 1000;

用job查询会使用索引，用sal查询则不会使用索引

④在where当中索引列参加了数学运算

create index emp_sal_index on emp(sal);

explain select * from emp where sal + 1= 1000;

此时查询不会用到索引

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

explain select * from emp where lower(ename)='smith';

此时查询不会用到索引

7、索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引

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

唯一性越强，效率越高

十五、视图View

1、站在不同的角度看待同一份数据就是视图

2、创建和删除

create view emp_view as select * from emp;

drop view emp_view;

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

create view emp_view as 后面的语句必须是查询语句

3、用视图做什么

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

select * from emp_view;

insert into emp_view(id,name,job) values(…………）

select * from emp;

原表数据也会发生变化

4、视图对象在开发中的实际运用

增删改查又被叫做CRUD

create retrive update delete 

十六、DBA命令

重点掌握：数据的导入和导出

导出：在cmd输入 mysqldump 数据库名>文件导出位置 -u root -p 

导入：source 拖入sql文件

十七、数据库设计三范式（熟记*）

1、数据库表的设计依据

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

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

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

3、第一范式

最核心最重要的范式，必须有主键，而且每个字段都不可再分

4、第二范式

要求所有非主键字段完全依赖主键，不能产生部分依赖 

该表是复合主键，学生姓名依赖学生编号，教师姓名依赖教师编号，所以其中有部分依赖，并没有完全依赖主键，不满足第二范式

为了让上表满足第二范式，需要使用三张表表示多对多关系

 5、第三范式

要求所有非主键字段直接依赖主键，不要产生传递依赖

只有一个主键，不存在部分依赖，所以满足第二范式

但是此时班级名称依赖班级编号，班级编号依赖学生编号，所以产生了传递依赖，产生了数据容易

 如何解决？

拆分成两张表：学生表和班级表

6、总结表的设计

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

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

一对一：一对一，外键唯一

可能遇到一张表字段太多，太庞大，此时需要拆分

使用外键+unique约束