mysql数据库：索引Index/视图view/DBA常见操作/数据库设计三范式（老杜）

目录

一、索引（Index）

二、视图view    

三、DBA常见操作（未完待补充）

四、数据库设计三范式（面试）

---

一、索引（Index）

    1.1定义：

        绑定在某些上字段使用，提高查询效率，可以绑定在一个或多个上，缩小扫描范围。

        理解：

          对于一本字典来说，查找某个汉字有两种方式：
          第一种方式：一页一页挨着找，直到找到为止，这种查找方式属于全字典扫描。
          效率比较低。
          第二种方式：先通过目录（索引）去定位一个大概的位置，然后直接定位到这个
          位置，做局域性扫描，缩小扫描的范围，快速的查找。这种查找方式属于通过
          索引检索，效率较高。

    书籍内容查找方式：
                逐页
                目录
    mysql查找的方式：
                全表
                 根据索引检索

    1.2 根据索引扫描首先需要排序：

        排序结构与TreeSet数据结构相同，TreeSet底层是一个自平衡的二叉树、在mysql当中索引是一个B-Tree数据结构。

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

        注释：

    1.2数据的实现原理

        注释1：主键、unique约束上自动添加索引，mysql数据库中.
        注释2：任何数据库中，任何一张表上的每一条记录在硬盘有哦一个硬盘的物理存储编号
        注释3：需要不断维护。

        每一条记录都会生成一个存储编号，根据添加了索引的此条记录的字段会直接对应到该物理存储的编号从而查询到整条记录。

    1.3考虑添加索引的场所
        
        场景1：数据量庞大（需要测试，根据不同硬件环境不同分别处理）

        场景2：被添加的字段经常当作限制条件添加到where后面，需要经常被扫描。

        场景3：该字段很少的DML（insert delete update）操作，原因：增删改之后需要重新排序。

        建议：不要添加过多索引，过多反而降低系统的性能，建议通过primary key 以及 unique使用

    1.4索引的创建与删除： index
        创建：
        语法：create index ___(索引名) on ___ （___） 表名（字段名）

        案例：mysql> create index emp_ename_index on emp(ename);
        删除：
        语法：drop index ____索引名 on ___表名；

        案例：mysql> drop index emp_ename_index on emp;

    1.5查看数据库中是否使用了索引检索表格

        explain

        案例：explain select * from emp where  ename = 'king';

        结果1：
        type---ALL    (未加索引，检索了全部)
        type---ref     (加了索引，检索一条)

    1.6索引的失效

        情景1：like '%T'，模糊匹配中以%开头了，原因不知道第一个字符是什么。
        优化，尽量避免模糊查询当中以%开头。
        
        情景2：or,使用or的情况时，两边的字段都添加了索引才会通过索引查询，否则失效。
        优化，尽量少用or比如union

        情景2：使用复合索引时，没有使用左侧列查找，索引失效。
            复合索引：两个或多个索引联合添加一个索引，叫做复合索引；
        
        情景3：复合索引

        情景4：where列当中参加了运算
        注意是字段参加了运算而不是其中的值；

        情景5：在where当中索引列使用函数；（单行处理函数）

        情景6：。。。。。

    1.7索引是各种数据库进行优化的的重要手段，优化时优先考虑的因素就是索引；
        单一索引；一个字段上添加索引
        复合索引；多个字段联合添加索引

        主键索引；主键上添加索引
        唯一性索引；unique字段上添加索引

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

二、视图view    

    2.1定义：不同角度看同一份数据
    2.2如何创建，删除
        create view (视图的名字) as select * from emp;
        cerate view view_emp as select * from emp;

        案例：复制表，创建视图，删除视图；
        create table dept2 as select * from dept;
        create view dept2_view as select * from dept;
        drop view dept2_view;
        注释：创建视图的语句当中后面跟的一定时dql语句；

    2.3用途
        特点：面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作
        不同于表复制，操作视图其关联的原表会被改动
        
        2.3.1操作
        //面向视图查询
        select * from dept_view;

        //面向视图插入
        insert into dept2_view ()value();

        //面向视图删除
        delete from dept_view;

        //查询原表数据
        select * from dept2;

        //创建视图对象
        错误操作：
        create view
            emp_view,dept_view
            as
            select 
            *
            from
            emp
            join
            dept
            on
            emp.deptno=dept.deptno
        正确操作：
            create view
            emp_dept_view
            as
            select 
            *
            from
            emp e
            join
            dept d
            on
            e.deptno=d.deptno;

        //面向视图更新
            update emp_dept_view set sal =1000 where dname ='king';
            注释：原表数据被更新

        2.3.2用途：简化sql语句
            sql的查询可能很长且复杂，需要多次使用，利用命令一个新的对象使用视图对象，且便于修改，修改视图对象中所反映的sql语句

            注释：视图存储在硬盘而非内存，关机重启后不会消失
                视图对应的只能是sql语句，但可以对视图后期进行增删改查等操作

            插曲：术语CRUD,程序员当中沟通的术语，代表增删改查。
                C:create（增）
                R:retrive(查)
                U:update(改)
                D:delete(删)

三、DBA常见操作（未完待补充）

    3.1导入导出（数据的备份）
        导出数据库：
        mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p13008172713Z.R
        导出数据库指定的表：
        mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p13008172713Z.R
        
        数据的导入：
        source D:\bjpowernode.sql

四、数据库设计三范式（面试）

    4.1定义：数据表的设计依据，设计表的基础

        第一范式：每一张表都有主键，且每一个字段都呈现原子性不可再分。
        第二范式：建立在第一范式的基础之上，要求每一个非主键字段都完全抵赖于主键，不要产生部分依赖；
        第三范式：建立在第二范式的基础之上，要求每一个非主键字段都直接依赖于主键，不要产生传递性依赖；

        注释：所设计的数据表出现数据冗余，造成空间的浪费；

    4.2第一范式：
        最核心重要的范式；
        
        案例学生表；
        t_student
        ----------------------------------------------------------------------------
        编号（PK）            name        联系信息
        01            张三            zhangsan@123 13300921911
        02            李四            lisi@12332    15213234421
        03            王五            wangwu@123    16332343434

        更改后
        t_student
        编号(PK)            name        邮箱                电话
        ----------------------------------------------------------------------------
        01            张三            zhangsan@123    13300921911
        02            李四            lisi@12332    15213234421
        03            王五            wangwu@123    16332343434
        不符合原子性不可再分

    4.3第二范式：
        建立在第一范式的基础之上，要求所有非主键字段完全依赖，不产生部分依赖；
        
        案例学生老师表：
        学生编号        学生        老师编号        老师
        -------------------------------------------------------
        01            张三        101            赵老师
        02            李四        102            王老师
        03            王五        101            赵老师
        04            赵六        102            王老师

        修改满足范式1后
        给学生编号号和老师编号添加复合主键

        （学生编号    老师编号）pk        学生            老师
        ---------------------------------------------------------------
        01            101            张三            赵老师
        02            102            李四            王老师
        03            101            王五            赵老师
        04            102            赵六            王老师
        修改满足范式2后
        范例2：
        学生与老师字段只依赖于复合编号中的一部分，依次为学生编号与老师编号，未产生完全依赖；
        修改：
        学生编号PK        学生
        --------------------------
            01        张三    
            02        李四    
            03        王五    
            04        赵六    

        老师编号PK        老师
        ---------------------------
            101        赵老师
            102        王老师

        id号    学生编号FK    老师编号FK
        ----------------------------
        1    01        101    
        2    02        102    
        3    03        101    
        4    04        102    
        口诀：多对多表的设计：多对多，三张表，两个外键

    4.4第三范式：
        学生班级表
        学生编号pk        学生姓名    班级编号        班级
        ------------------------------------------------------
        01            张三        101            1班
        02            李四        102            2班
        03            王五        101            1班
        04            赵六        102            2班

        解析：
        符合第一范式，有主键，且完全依赖于主键原子性不可再分
        符合第二范式，完全依赖于主键，不产生部份依赖
        不符合第三范式，非主键字段具有传递性，
        如：班级依赖于班级编号，传递到学生编号

        修改满足范例3后：
        学生编号pk        学生姓名fk    
        ------------------------------
        01            张三        
        02            李四        
        03            王五        
        04            赵六        
        
        班级编号        班级fk     
        ------------------------   
        101            1班      
        102            2班      
        
        关系
        口诀：一对多，两张表，多的表加外键

    4.5总结表的设计：
        口诀1:多对多，三张表，两个外键
        口诀2:一对多，两张表，多的表加外键
        口诀3：一对一，外键唯一
            场景：一张表字段太多，太庞大，需要拆分
            案例：用户信息表；

    4.6最后嘱咐：
        数据表的设计三范式是理论上的；
        时间和理论有一些偏差。
        最终的目的都是为了满足客户的需求，有时候会拿冗余换执行速度。
        原因：sql语句中，表和表的连接次数越多，效率越低（笛卡尔积）

        面试的时候回到现实，回归到客户
        有的时候会拿数据冗余，来减少表的连接次数。这样做也是合理的，并且会降低开发人员的编写难度；

        面试的时候可以带一些这样的话语，更容易打动面试官