索引+视图+数据库设计

一、索引

        索引在数据库表上的字段上添加，为了提高查询效率。可以一个字段或多个字段联合添加索引。索引是为了缩小扫描范围存在的一种机制。

        如果字段上没有索引，mysql在查找一个字段时会进行全扫描，效率较低。

Notations:

（1）实际中汉语字典的目录是排序的，因为经过排序才会有区间查找，缩小扫描范围。在mysql中索引也需要排序，其数据结构是Binary Tree。

（2）在任何数据库中，任何一张表的任何一个记录，在硬盘上都有一个硬盘的存储编号。

（3）mysql中的主键字段和添加了unique的字段都有自动创建的索引。

（4）索引是一个单独的对象，不同的引擎中以不同形式存在。在myisam中，其存储在一个.MYI文件中。innodb中存在tablespace中。在memory中存在内存中。但无论什么方式，索引都是以数的形式存在的。

1.1 索引实现原理

        例如idIndex是id字段的索引对象，如果执行下列查询：

                select name from user where id=101;

        mysql注意到id字段有索引对象，然后通过索引idIndex定位到101（索引对象是以B-Tree存储的，可以缩小查找范围）。然后找到101的物理编号，马上进行转换：

                select name from user where 物理编号=xxx；

          考虑加索引的情况：

                （1）数据量大；（2）字段经常出现在where之后；（3）不经常被DML语句操作（操作之后，索引需要重新排序）。

Notations:索引不建议添加太多，需要维护。

1.2 索引的创建和删除

        添加索引：

                create index user user_index on user(id);

                create index user user_index on user(id，name);

//给user表的id字段添加user_index索引和复合索引。

        删除索引：

                drop index user_index on user;

        查看一个字段是否使用了索引：

                explain 查询语句;

 //没有索引的字段type为ALL,有的是ref

1.3 索引的失效

        索引失效的情况：

                （1）如带有like的模糊查询语句，如果查询内容是%开头的匹配语句，索引会失效。

                        所以避免使用这种语句是一种优化策略。

                  （2）使用or的时候两个字段都必须有索引，索引才不会失效。

                          少用or也是一种优化策略。

               （3）使用复合索引的时候，没有使用左侧的列查找。

                （4）在where列中，索引列参加了数学运算。

                （5）在where列中使用了函数。

1.4 索引的分类

        对数据库进行优化时优先考虑的就是索引，其分类有：单一索引（一个字段上加索引）、复合索引（两个或更多添加索引）、主键索引（主键上加索引）、唯一性索引（unique修饰的字段上添加索引)等。

        唯一性不强的字段上添加索引用处不大，会产生大量的重复数据。

二、视图

        view:站在不同的角度看待同一份数据。创建视图对象：

        create view user_view as select * from user;

Notations:as后面只能是DQL语句

        删除视图：

        drop view user_view;

        作用：修改视图会影响到原表的数据。将操作原表的语句中的表名直接换成视图名，会影响到原表。通过视图修改原表的数据：

        update user_view set userPassword='7654321' where id=12;

        在实际开发中，视图用于简化mysql语句。有的DQL语句很长，如果需要在多个位置使用，可以使用视图，简化语句。视图是存在硬盘上而不是内存的，也不会消失。

三、DBA常用命令

        创建用户：create user jk identified by '123456';

        主要掌握数据的导入导出。

        在windows 的dos窗口中执行：

                mysqldump 数据库名>导出文件路径 -u用户名 -p密码

        删除数据库：drop database 数据库名

        导入数据库需要先登录mysql,再创建数据库：create database 数据库名;

        再将数据导入数据库：                

                source 路径名；

四，数据库设计三范式

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

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

        一般有复合主键的表都有部分依赖。

Notations:如果一张表是两个字段有多对多关系，为了满足第二范式，需要设计三张表，其中关系表有两个外键。

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

Notations:如果一张表两个字段有一对多的关系，为了满足第三范式，分成两张表，多出来的表添加外键。

        不满足第三范式，如：某个字段不直接依赖于主键依赖，而是直接依赖于另一个直接依赖于主键的字段。

        按照三范式设计数据库表，可以避免数据冗余，节省空间。

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

            多对多，三张表，关系表两外键；

            一对一，两张表，外键唯一（unique）；