MySQL数据库进阶系统学习6（MySQL高级-视图-事务-索引-账户管理-主从）

第六部分: MySQL高级

• 详细资料参考html文件17

9.1 视图

• 1. 问题
     对于复杂的查询，往往是有多个数据表进行关联查询而得到，如果数据库因为需求等原因发生了改变，
     为了保证查询出来的数据与之前相同，则需要在多个地方进行修改，维护起来非常麻烦
     解决办法：定义视图
• 2. 视图是什么
     通俗的讲，视图就是一条SELECT语句执行后返回的结果集，改条查询语句的结果保存为一张表，就是视图。
     所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
     视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，
     不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；
     方便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；
• 3. 定义视图
     建议以v_开头
     create view 视图名称 as select语句;
• 4. 查看视图
     查看表会将所有的视图也列出来
     show tables;
• 5. 使用视图
     视图的用途就是查询
     select * from v_stu_score;
• 6. 删除视图
     drop view 视图名称;
     例：
     drop view v_stu_sco;
• 8. 视图的作用
     提高了重用性，就像一个函数
     对数据库重构，却不影响程序的运行
     提高了安全性能，可以对不同的用户
     让数据更加清晰

• 实例：

• 提取出京东商品数据库三张表的所有信息
  mysql> select * from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;
  ±—±--------------------------------±--------±---------±----------±--------±-----------±-----±--------------±-----±-----+
  | id | name | cate_id | brand_id | price | is_show | is_saleoff | id | name | id | name |
  ±—±--------------------------------±--------±---------±----------±--------±-----------±-----±--------------±-----±-----+
  | 1 | r510vc 15.6英寸笔记本 | 1 | 1 | 3399.000 | | | 1 | 笔记本 | 1 | 华硕 |
  | 2 | y400n 14.0英寸笔记本电脑 | 1 | 2 | 4999.000 | | | 1 | 笔记本 | 2 | 联想 |
  | 4 | x550cc 15.6英寸笔记本 | 1 | 1 | 2799.000 | | | 1 | 笔记本 | 1 | 华硕 |
  | 3 | g150th 15.6英寸游戏本 | 2 | 3 | 8499.000 | | | 2 | 游戏本 | 3 | 雷神 |
  | 5 | x240 超极本 | 3 | 2 | 4880.000 | | | 3 | 超级本 | 2 | 联想 |
  | 6 | u330p 13.3英寸超极本 | 3 | 2 | 4299.000 | | | 3 | 超级本 | 2 | 联想 |
  | 7 | svp13226scb 触控超极本 | 3 | 4 | 7999.000 | | | 3 | 超级本 | 4 | 索尼 |
  | 8 | ipad mini 7.9英寸平板电脑 | 4 | 5 | 1998.000 | | | 4 | 平板电脑 | 5 | 苹果 |
  | 9 | ipad air 9.7英寸平板电脑 | 4 | 5 | 3388.000 | | | 4 | 平板电脑 | 5 | 苹果 |
  | 10 | ipad mini 配备 retina 显示屏 | 4 | 5 | 2788.000 | | | 4 | 平板电脑 | 5 | 苹果 |
  | 11 | ideacentre c340 20英寸一体电脑 | 5 | 2 | 3499.000 | | | 5 | 台式机 | 2 | 联想 |
  | 12 | vostro 3800-r1206 台式电脑 | 5 | 6 | 2899.000 | | | 5 | 台式机 | 6 | 戴尔 |
  | 13 | imac me086ch/a 21.5英寸一体电脑 | 5 | 5 | 9188.000 | | | 5 | 台式机 | 5 | 苹果 |
  | 14 | at7-7414lp 台式电脑 linux ） | 5 | 7 | 3699.000 | | | 5 | 台式机 | 7 | 宏碁 |
  | 15 | z220sff f4f06pa工作站 | 6 | 8 | 4288.000 | | | 6 | 服务器/工作站 | 8 | 惠普 |
  | 16 | poweredge ii服务器 | 6 | 6 | 5388.000 | | | 6 | 服务器/工作站 | 6 | 戴尔 |
  | 17 | mac pro专业级台式电脑 | 6 | 5 | 28888.000 | | | 6 | 服务器/工作站 | 5 | 苹果 |
  | 20 | x3250 m4机架式服务器 | 6 | 9 | 6888.000 | | | 6 | 服务器/工作站 | 9 | ibm |
  | 18 | hmz-t3w 头戴显示设备 | 7 | 4 | 6999.000 | | | 7 | 笔记本配件 | 4 | 索尼 |
  | 19 | 商务双肩背包 | 7 | 4 | 99.000 | | | 7 | 笔记本配件 | 4 | 索尼 |
  | 21 | 商务双肩背包 | 7 | 4 | 99.000 | | | 7 | 笔记本配件 | 4 | 索尼 |
  ±—±--------------------------------±--------±---------±----------±--------±-----------±-----±--------------±-----±-----+
  21 rows in set (0.03 sec)

• 提取出京东商品数据库三张表的部分信息
  mysql> select g.*, c.name as cate_name, b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;
  ±—±--------------------------------±--------±---------±----------±--------±-----------±--------------±-----------+
  | id | name | cate_id | brand_id | price | is_show | is_saleoff | cate_name | brand_name |
  ±—±--------------------------------±--------±---------±----------±--------±-----------±--------------±-----------+
  | 1 | r510vc 15.6英寸笔记本 | 1 | 1 | 3399.000 | | | 笔记本 | 华硕 |
  | 2 | y400n 14.0英寸笔记本电脑 | 1 | 2 | 4999.000 | | | 笔记本 | 联想 |
  | 3 | g150th 15.6英寸游戏本 | 2 | 3 | 8499.000 | | | 游戏本 | 雷神 |
  | 4 | x550cc 15.6英寸笔记本 | 1 | 1 | 2799.000 | | | 笔记本 | 华硕 |
  | 5 | x240 超极本 | 3 | 2 | 4880.000 | | | 超级本 | 联想 |
  | 6 | u330p 13.3英寸超极本 | 3 | 2 | 4299.000 | | | 超级本 | 联想 |
  | 7 | svp13226scb 触控超极本 | 3 | 4 | 7999.000 | | | 超级本 | 索尼 |
  | 8 | ipad mini 7.9英寸平板电脑 | 4 | 5 | 1998.000 | | | 平板电脑 | 苹果 |
  | 9 | ipad air 9.7英寸平板电脑 | 4 | 5 | 3388.000 | | | 平板电脑 | 苹果 |
  | 10 | ipad mini 配备 retina 显示屏 | 4 | 5 | 2788.000 | | | 平板电脑 | 苹果 |
  | 11 | ideacentre c340 20英寸一体电脑 | 5 | 2 | 3499.000 | | | 台式机 | 联想 |
  | 12 | vostro 3800-r1206 台式电脑 | 5 | 6 | 2899.000 | | | 台式机 | 戴尔 |
  | 13 | imac me086ch/a 21.5英寸一体电脑 | 5 | 5 | 9188.000 | | | 台式机 | 苹果 |
  | 14 | at7-7414lp 台式电脑 linux ） | 5 | 7 | 3699.000 | | | 台式机 | 宏碁 |
  | 15 | z220sff f4f06pa工作站 | 6 | 8 | 4288.000 | | | 服务器/工作站 | 惠普 |
  | 16 | poweredge ii服务器 | 6 | 6 | 5388.000 | | | 服务器/工作站 | 戴尔 |
  | 17 | mac pro专业级台式电脑 | 6 | 5 | 28888.000 | | | 服务器/工作站 | 苹果 |
  | 18 | hmz-t3w 头戴显示设备 | 7 | 4 | 6999.000 | | | 笔记本配件 | 索尼 |
  | 19 | 商务双肩背包 | 7 | 4 | 99.000 | | | 笔记本配件 | 索尼 |
  | 20 | x3250 m4机架式服务器 | 6 | 9 | 6888.000 | | | 服务器/工作站 | ibm |
  | 21 | 商务双肩背包 | 7 | 4 | 99.000 | | | 笔记本配件 | 索尼 |
  ±—±--------------------------------±--------±---------±----------±--------±-----------±--------------±-----------+
  21 rows in set (0.00 sec)

• 将上面查询的结果作为一张虚拟的表，这就是视图
  mysql> create view v_goods_info as select g.*, c.name as cate_name, b.name as brand_name from goods as g left join goods_cates as c on g.cate_id=c.id left join goods_brands as b on g.brand_id=b.id;
  Query OK, 0 rows affected (0.02 sec)

mysql> show tables
-> ;
±--------------------+
| Tables_in_jing_dong |
±--------------------+
| goods |
| goods_brands |
| goods_cates |
| v_goods_info |
±--------------------+
4 rows in set (0.00 sec)

• 插入一条数据，再次查看视图，已经自动更新
  mysql> insert into goods values(0,‘笔记本包’,‘7’,‘5’,‘109’,default,default);
  Query OK, 1 row affected (0.01 sec)
  mysql> select * from v_goods_info;
  ±—±--------------------------------±--------±---------±----------±--------±-----------±--------------±-----------+
  | id | name | cate_id | brand_id | price | is_show | is_saleoff | cate_name | brand_name |
  ±—±--------------------------------±--------±---------±----------±--------±-----------±--------------±-----------+
  | 1 | r510vc 15.6英寸笔记本 | 1 | 1 | 3399.000 | | | 笔记本 | 华硕 |
  | 2 | y400n 14.0英寸笔记本电脑 | 1 | 2 | 4999.000 | | | 笔记本 | 联想 |
  | 3 | g150th 15.6英寸游戏本 | 2 | 3 | 8499.000 | | | 游戏本 | 雷神 |
  | 4 | x550cc 15.6英寸笔记本 | 1 | 1 | 2799.000 | | | 笔记本 | 华硕 |
  | 5 | x240 超极本 | 3 | 2 | 4880.000 | | | 超级本 | 联想 |
  | 6 | u330p 13.3英寸超极本 | 3 | 2 | 4299.000 | | | 超级本 | 联想 |
  | 7 | svp13226scb 触控超极本 | 3 | 4 | 7999.000 | | | 超级本 | 索尼 |
  | 8 | ipad mini 7.9英寸平板电脑 | 4 | 5 | 1998.000 | | | 平板电脑 | 苹果 |
  | 9 | ipad air 9.7英寸平板电脑 | 4 | 5 | 3388.000 | | | 平板电脑 | 苹果 |
  | 10 | ipad mini 配备 retina 显示屏 | 4 | 5 | 2788.000 | | | 平板电脑 | 苹果 |
  | 11 | ideacentre c340 20英寸一体电脑 | 5 | 2 | 3499.000 | | | 台式机 | 联想 |
  | 12 | vostro 3800-r1206 台式电脑 | 5 | 6 | 2899.000 | | | 台式机 | 戴尔 |
  | 13 | imac me086ch/a 21.5英寸一体电脑 | 5 | 5 | 9188.000 | | | 台式机 | 苹果 |
  | 14 | at7-7414lp 台式电脑 linux ） | 5 | 7 | 3699.000 | | | 台式机 | 宏碁 |
  | 15 | z220sff f4f06pa工作站 | 6 | 8 | 4288.000 | | | 服务器/工作站 | 惠普 |
  | 16 | poweredge ii服务器 | 6 | 6 | 5388.000 | | | 服务器/工作站 | 戴尔 |
  | 17 | mac pro专业级台式电脑 | 6 | 5 | 28888.000 | | | 服务器/工作站 | 苹果 |
  | 18 | hmz-t3w 头戴显示设备 | 7 | 4 | 6999.000 | | | 笔记本配件 | 索尼 |
  | 19 | 商务双肩背包 | 7 | 4 | 99.000 | | | 笔记本配件 | 索尼 |
  | 20 | x3250 m4机架式服务器 | 6 | 9 | 6888.000 | | | 服务器/工作站 | ibm |
  | 21 | 商务双肩背包 | 7 | 4 | 99.000 | | | 笔记本配件 | 索尼 |
  | 22 | 笔记本包 | 7 | 5 | 109.000 | | | 笔记本配件 | 苹果 |
  ±—±--------------------------------±--------±---------±----------±--------±-----------±--------------±-----------+

9.2 事务

• 所谓事务,它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。
  例如，银行转帐工作：从一个帐号扣款并使另一个帐号增款，
  这两个操作要么都执行，要么都不执行。所以，应该把他们看成一个事务。
  事务是数据库维护数据一致性的单位，在每个事务结束时，都能保持数据一致性

• 事务四大特性(简称ACID)
  原子性(Atomicity)
  一致性(Consistency)
  隔离性(Isolation)
  持久性(Durability)

• 下面举一个银行应用是解释事务必要性的一个经典例子。假如一个银行的数据库有两张表：支票表（checking）和储蓄表（savings）。
  现在要从用户Jane的支票账户转移200美元到她的储蓄账户，那么至少需要三个步骤：

  1. 检查支票账户的余额高于或者等于200美元。
  2. 从支票账户余额中减去200美元。
  3. 在储蓄帐户余额中增加200美元。

上述三个步骤的操作必须打包在一个事务中，任何一个步骤失败，则必须回滚所有的步骤。

• 一个很好的事务处理系统，必须具备这些标准特性：

  原子性（atomicity）
  一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，
  要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

  一致性（consistency）
  数据库总是从一个一致性的状态转换到另一个一致性的状态。
  （在前面的例子中，一致性确保了，即使在执行第三、四条语句之间时系统崩溃，
  支票账户中也不会损失200美元，因为事务最终没有提交，所以事务中所做的修改也不会保存到数据库中。）

  隔离性（isolation）
  通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的。
  （在前面的例子中，当执行完第三条语句、第四条语句还未开始时，此时有另外的一个账户汇总程序开始运行，
  则其看到支票帐户的余额并没有被减去200美元。）

  持久性（durability）
  一旦事务提交，则其所做的修改会永久保存到数据库。（此时即使系统崩溃，修改的数据也不会丢失。）

• 事务命令
  表的引擎类型必须是innodb类型才可以使用事务，这是mysql表的默认引擎
  查看表的创建语句，show create table goods;
  可以看到engine=innodb

• 开启事务，命令如下：
  开启事务后执行修改命令，变更会维护到本地缓存中，而不维护到物理表中
  begin;
  或者
  start transaction;

• 提交事务，命令如下
  将缓存中的数据变更维护到物理表中
  commit;

• 回滚事务，命令如下
  注意：在提交前回滚，提交后就不能回滚了，只能删除数据了

  放弃缓存中变更的数据
  rollback;
  注意：
  修改数据的命令会自动的触发事务，包括insert、update、delete
  而在SQL语句中有手动开启事务的原因是：可以进行多次数据的修改，如果成功一起成功，否则一起会滚到之前的数据

9.3 索引

• 索引是什么
  索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，
  它们包含着对数据表里所有记录的引用指针。
  更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度

• 索引目的
  索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，
  我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
  如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，

• 索引原理
  数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，
  还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。
  数据库应该选择怎么样的方式来应对所有的问题呢？
  我们回想字典的例子，能不能把数据分成段，然后分段查询呢？
  最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，
  201到300分成第三段……这样查第250条数据，只要找第三段就可以了，
  一下子去除了90%的无效数据。

• 索引使用

  • 查看索引
    show index from 表名;

  • 创建索引
    如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
    字段类型如果不是字符串，可以不填写长度部分
    create index 索引名称 on 表名(字段名称(长度))

  • 删除索引：
    drop index 索引名称 on 表名;

• 索引查询时间对比

  • 开启运行时间监测：
    set profiling=1;
  • 查找第1万条数据ha-99999
    select * from test_index where title=‘ha-99999’;
  • 查看执行的时间：
    show profiles;
  • 为表title_index的title列创建索引：
    create index title_index on test_index(title(10));
  • 执行查询语句：
    select * from test_index where title=‘ha-99999’;
  • 再次查看执行的时间
    show profiles;

  mysql> show profiles;
  ±---------±-----------±--------------------------------------------------+
  | Query_ID | Duration | Query |
  ±---------±-----------±--------------------------------------------------+
  | 1 | 0.07055400 | select * from test_index where title=‘ha-99999’ |
  | 2 | 0.58853000 | create index title_index on test_index(title(10)) |
  | 3 | 0.01141550 | show index from test_index |
  | 4 | 0.00039400 | select * from test_index where title=‘ha-99999’ |
  ±---------±-----------±--------------------------------------------------+
  4 rows in set, 1 warning (0.00 sec)

  • 明显可以看见，创建索引后，再次查询，时间明显缩短

• 参考案例007

• 注意
  要注意的是，建立太多的索引将会影响更新和插入的速度，
  因为它需要同样更新每个索引文件。对于一个经常需要更新和插入的表格，
  就没有必要为一个很少使用的where字句单独建立索引了，
  对于比较小的表，排序的开销不会很大，也没有必要建立另外的索引。
  建立索引会占用磁盘空间

9.4 账户管理

• 参考index文件17.4

• 账户管理
  在生产环境下操作数据库时，绝对不可以使用root账户连接，而是创建特定的账户，授予这个账户特定的操作权限，然后连接进行操作，主要的操作就是数据的crud
  MySQL账户体系：根据账户所具有的权限的不同，MySQL的账户可以分为以下几种
  服务实例级账号：，启动了一个mysqld，即为一个数据库实例；如果某用户如root,拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库、连同这些库中的表
  数据库级别账号：对特定数据库执行增删改查的所有操作
  数据表级别账号：对特定表执行增删改查等所有操作
  字段级别的权限：对某些表的特定字段进行操作
  存储程序级别的账号：对存储程序进行增删改查的操作
  账户的操作主要包括创建账户、删除账户、修改密码、授权权限等

• 注意：
  进行账户操作时，需要使用root账户登录，这个账户拥有最高的实例级权限
  通常都使用数据库级操作权限

9.5 MySQL主从

• 参考index文件17.5

• 主从同步的定义
  主从同步使得数据可以从一个数据库服务器复制到其他服务器上，在复制数据时，
  一个服务器充当主服务器（master），其余的服务器充当从服务器（slave）。
  因为复制是异步进行的，所以从服务器不需要一直连接着主服务器，
  从服务器甚至可以通过拨号断断续续地连接主服务器。
  通过配置文件，可以指定复制所有的数据库，某个数据库，甚至是某个数据库上的某个表。

• 使用主从同步的好处：

  • 通过增加从服务器来提高数据库的性能，在主服务器上执行写入和更新，在从服务器上向外提供读功能，
    可以动态地调整从服务器的数量，从而调整整个数据库的性能。
  • 提高数据安全，因为数据已复制到从服务器，从服务器可以终止复制进程，
    所以，可以在从服务器上备份而不破坏主服务器相应数据
  • 在主服务器上生成实时数据，而在从服务器上分析这些数据，从而提高主服务器的性能

• 配置主从同步的基本步骤
  有很多种配置主从同步的方法，可以总结为如下的步骤：

  1. 在主服务器上，必须开启二进制日志机制和配置一个独立的ID
  2. 在每一个从服务器上，配置一个唯一的ID，创建一个用来专门复制主服务器数据的账号
  3. 在开始复制进程前，在主服务器上记录二进制文件的位置信息
  4. 如果在开始复制之前，数据库中已经有数据，就必须先创建一个数据快照（可以使用mysqldump导出数据库，或者直接复制数据文件）
  5. 配置从服务器要连接的主服务器的IP地址和登陆授权，二进制日志文件名和位置