学习笔记day_09_索引，视图，DBA命令，数据库三范式

索引

索引的概念

• 什么是索引？

  索引是为了提高查询效率存在的一种机制。索引相当于一本书的目录，是为了缩小扫描范围而存在的一种机制。

• 一个简单的查询语句：select * from user where name =‘jack’;

  • 系统会根据where后面的条件来确定扫描的字段，所以会在name字段上扫描
  • 如果没有给name字段创建索引，Mysql会进行全扫描，将name字段上的每一个值都和‘jack’比对一遍。

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

• 在实际中，汉语字典前面的目录是排序的，按照a b c d e f…排序，
  为什么排序呢？因为只有排序了才会有区间查找这一说！（缩小扫描范围
  其实就是扫描某个区间罢了！）

  在mysql数据库当中索引也是需要排序的，并且这个所以的排序和B+树
  数据结构相同。

如何添加索引

• 如何添加索引

   语法格式：
   create [unique] index 索引名 on 
   表名（列名）
  /unique 可有可无，unique表示一个索引值只能对应一条数据记录，如果该列上的值有重复的则不能加unique，所以unique尽量可以不加上/ 
   以user表user（id，age）为例
  +------+------+
  | id   | age  |
  +------+------+
  |    1 |   20 |
  |    2 |   21 |
  |    3 |   21 |
  +------+------+
   给id字段加上索引
   create unique index id_index on user(id);
   
   查看user表上的索引
   show index from user;
  结果如下图
  
  

  [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jPOlKZPV-1634992372525)(C:\Users\86181\AppData\Roaming\Typora\typora-user-images\image-20211023172826259.png)]

• 在没有给age添加索引前，执行select * from user where age=20;要检索的所有的记录
  mysql> explain select * from user where age=20;
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+
  | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+#当没有检索时type为all
  |  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
  +----+-------------+-------+------+---------------+------+---------+------+------+-------------+# rows列代表是一种检索的条数
  对age添加索引
  create index age_index on user(age);
  
  mysql> explain select * from user where age=20;
  +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
  | id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra       |
  +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
  |  1 | SIMPLE      | user  | ref  | age_index     | age_index | 5       | const |    1 | Using where |
  +----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
  此时检索的记录条数为1，可以明显看出索引的作用了
  

• 什么条件下，我们会考虑给字段添加索引呢？
  条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
  条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
  条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

  建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。
  建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的

索引失效

• 第一种情况:模糊查询

  select * from emp where ename like ‘%T’;

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

• 第二种情况

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

• 第三种情况

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

  什么是复合索引？两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引

   create index emp_job_sal_index on emp(job,sal);
   explain select * from emp where job = 'MANAGER';# type=ref rows=3
   
   explain select * from emp where sal=800;#type=all rows=14,说明索引失效
  

• 第四种情况：

  在where当中索引列参加了运算，索引失效。

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

• 第五种情况：

  在where当中索引列使用了函数
  explain select * from emp where lower(ename) = ‘smith’;

• 还有其他的情况，这里就不一一叙述了

视图

• 视图的定义：试图是从一个或几个基本表中（或试图）导出来的表。

• 视图和基本表不同，是一个虚表。数据库种只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。

• 对视图进行增删改，会导致原表被操作

• 创建视图的语法

  create view 视图名（字段名1，字段名2...）
  as select语句
  现有两张表：course 和sc
  mysql> select * from course;
  +-----+---------+
  | cno | cname   |
  +-----+---------+
  |   1 | sql     |
  |   2 | math    |
  |   3 | english |
  |   4 | c       |
  |   5 | c++     |
  |   6 | java    |
  +-----+---------+
  mysql> select * from sc;
  +----+-----+-------+
  | id | cno | grade |
  +----+-----+-------+
  |  1 |   1 |    92 |
  |  2 |   2 |    85 |
  |  3 |   3 |    88 |
  |  4 |   2 |    90 |
  |  5 |   3 |    80 |
  +----+-----+-------+
  create view view_ng(id,name,grade) 
  as 
  select id,cname,grade from course,sc where course.cno=sc.cno;
  mysql> select * from view_ng;
  +----+---------+-------+
  | id | name    | grade |
  +----+---------+-------+
  |  1 | sql     |    92 |
  |  2 | math    |    85 |
  |  3 | english |    88 |
  |  4 | math    |    90 |
  |  5 | english |    80 |
  +----+---------+-------+
  
  注意：在mysql不能通过一个视图来同时修改两张表
  mysql> insert into view_ng(id,name,grade) values(7,3,99);
  ERROR 1393 (HY000): Can not modify more than one base table through a join view 'my_db.view_ng'
  

• 并不是所有的视图都是可更新的，因为有些视图的更新不能唯一地有意义地转换成相应基本表的更新

  假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。每一次使用这个sql语句的时候都需要重新编写，很长，很麻烦，怎么办？可以把这条复杂的SQL语句以视图对象的形式新建。在需要编写这条SQL语句的位置直接使用视图对象，可以大大简化开发。并且利于后期的维护，因为修改的时候也只需要修改一个位置就行，只需要修改视图对象所映射的SQL语句。

• 我们以后面向视图开发的时候，使用视图的时候可以像使用table一样。
  可以对视图进行增删改查等操作。视图不是在内存当中，视图对象也是
  存储在硬盘上的，不会消失。

DBA常用命令

重点掌握：
数据的导入和导出（数据的备份）
其它命令了解一下即可。

数据导出？
	注意：在windows的dos命令窗口中：
		mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456
	
	可以导出指定的表吗？
		mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

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

设计数据库的三范式

• 数据库设计三范式

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

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

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

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

• 数据库设计三范式是理论上的。

  实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。
  不可再分。

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

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

• 数据库设计三范式是理论上的。

  实践和理论有的时候有偏差。最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。
  因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，并且对于开发人员来说，sql语句的编写难度也会降低。