零基础入门学习Python第二阶05MySQL详解01

深入MySQL

索引

索引是关系型数据库中用来提升查询性能最为重要的手段。关系型数据库中的索引就像一本书的目录，我们可以想象一下，如果要从一本书中找出某个知识点，但是这本书没有目录，这将是意见多么可怕的事情！我们估计得一篇一篇的翻下去，才能确定这个知识点到底在什么位置。创建索引虽然会带来存储空间上的开销，就像一本书的目录会占用一部分篇幅一样，但是在牺牲空间后换来的查询时间的减少也是非常显著的。

MySQL 数据库中所有数据类型的列都可以被索引。对于MySQL 8.0 版本的 InnoDB 存储引擎来说，它支持三种类型的索引，分别是 B+ 树索引、全文索引和 R 树索引。这里，我们只介绍使用得最为广泛的 B+ 树索引。使用 B+ 树的原因非常简单，因为它是目前在基于磁盘进行海量数据存储和排序上最有效率的数据结构。B+ 树是一棵平衡树，树的高度通常为3或4，但是却可以保存从百万级到十亿级的数据，而从这些数据里面查询一条数据，只需要3次或4次 I/O 操作。

B+ 树由根节点、中间节点和叶子节点构成，其中叶子节点用来保存排序后的数据。由于记录在索引上是排序过的，因此在一个叶子节点内查找数据时可以使用二分查找，这种查找方式效率非常的高。当数据很少的时候，B+ 树只有一个根节点，数据也就保存在根节点上。随着记录越来越多，B+ 树会发生分裂，根节点不再保存数据，而是提供了访问下一层节点的指针，帮助快速确定数据在哪个叶子节点上。

在创建二维表时，我们通常都会为表指定主键列，主键列上默认会创建索引，而对于 MySQL InnoDB 存储引擎来说，因为它使用的是索引组织表这种数据存储结构，所以主键上的索引就是整张表的数据，而这种索引我们也将其称之为聚集索引（clustered index）。很显然，一张表只能有一个聚集索引，否则表的数据岂不是要保存多次。我们自己创建的索引都是二级索引（secondary index），更常见的叫法是非聚集索引（non-clustered index）。通过我们自定义的非聚集索引只能定位记录的主键，在获取数据时可能需要再通过主键上的聚集索引进行查询，这种现象称为“回表”，因此通过非聚集索引检索数据通常比使用聚集索引检索数据要慢。

接下来我们通过一个简单的例子来说明索引的意义，比如我们要根据学生的姓名来查找学生，这个场景在实际开发中应该经常遇到，就跟通过商品名称查找商品是一个道理。我们可以使用 MySQL 的explain关键字来查看 SQL 的执行计划（数据库执行 SQL 语句的具体步骤）。

explain select * from tb_student where stuname='林震南'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 11
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

在上面的 SQL 执行计划中，有几项值得我们关注：

select_type：查询的类型。
- SIMPLE：简单 SELECT，不需要使用 UNION 操作或子查询。
- PRIMARY：如果查询包含子查询，最外层的 SELECT 被标记为 PRIMARY。
- UNION：UNION 操作中第二个或后面的 SELECT 语句。
- SUBQUERY：子查询中的第一个 SELECT。
- DERIVED：派生表的 SELECT 子查询。
table：查询对应的表。
type：MySQL 在表中找到满足条件的行的方式，也称为访问类型，包括：ALL（全表扫描）、index（索引全扫描，只遍历索引树）、range（索引范围扫描）、ref（非唯一索引扫描）、eq_ref（唯一索引扫描）、const / system（常量级查询）、NULL（不需要访问表或索引）。在所有的访问类型中，很显然 ALL 是性能最差的，它代表的全表扫描是指要扫描表中的每一行才能找到匹配的行。
possible_keys：MySQL 可以选择的索引，但是有可能不会使用。
key：MySQL 真正使用的索引，如果为NULL就表示没有使用索引。
key_len：使用的索引的长度，在不影响查询的情况下肯定是长度越短越好。
rows：执行查询需要扫描的行数，这是一个预估值。
extra：关于查询额外的信息。
- Using filesort：MySQL 无法利用索引完成排序操作。
- Using index：只使用索引的信息而不需要进一步查表来获取更多的信息。
- Using temporary：MySQL 需要使用临时表来存储结果集，常用于分组和排序。
- Impossible where：where子句会导致没有符合条件的行。
- Distinct：MySQL 发现第一个匹配行后，停止为当前的行组合搜索更多的行。
- Using where：查询的列未被索引覆盖，筛选条件并不是索引的前导列。

从上面的执行计划可以看出，当我们通过学生名字查询学生时实际上是进行了全表扫描，不言而喻这个查询性能肯定是非常糟糕的，尤其是在表中的行很多的时候。如果我们需要经常通过学生姓名来查询学生，那么就应该在学生姓名对应的列上创建索引，通过索引来加速查询。

create index idx_student_name on tb_student(stuname);

再次查看刚才的 SQL 对应的执行计划。

explain select * from tb_student where stuname='林震南'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ref
possible_keys: idx_student_name
          key: idx_student_name
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

可以注意到，在对学生姓名创建索引后，刚才的查询已经不是全表扫描而是基于索引的查询，而且扫描的行只有唯一的一行，这显然大大的提升了查询的性能。MySQL 中还允许创建前缀索引，即对索引字段的前N个字符创建索引，这样的话可以减少索引占用的空间（但节省了空间很有可能会浪费时间，时间和空间是不可调和的矛盾），如下所示。

create index idx_student_name_1 on tb_student(stuname(1));

上面的索引相当于是根据学生姓名的第一个字来创建的索引，我们再看看 SQL 执行计划。

explain select * from tb_student where stuname='林震南'\G

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tb_student
   partitions: NULL
         type: ref
possible_keys: idx_student_name
          key: idx_student_name
      key_len: 5
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

不知道大家是否注意到，这一次扫描的行变成了2行，因为学生表中有两个姓“林”的学生，我们只用姓名的第一个字作为索引的话，在查询时通过索引就会找到这两行。

如果要删除索引，可以使用下面的SQL。

alter table tb_student drop index idx_student_name;

或者

drop index idx_student_name on tb_student;

在创建索引时，我们还可以使用复合索引、函数索引（MySQL 5.7 开始支持），用好复合索引实现索引覆盖可以减少不必要的排序和回表操作，这样就会让查询的性能成倍的提升，有兴趣的读者可以自行研究。

我们简单的为大家总结一下索引的设计原则：

最适合索引的列是出现在WHERE子句和连接子句中的列。
索引列的基数越大（取值多、重复值少），索引的效果就越好。
使用前缀索引可以减少索引占用的空间，内存中可以缓存更多的索引。
索引不是越多越好，虽然索引加速了读操作（查询），但是写操作（增、删、改）都会变得更慢，因为数据的变化会导致索引的更新，就如同书籍章节的增删需要更新目录一样。
使用 InnoDB 存储引擎时，表的普通索引都会保存主键的值，所以主键要尽可能选择较短的数据类型，这样可以有效的减少索引占用的空间，提升索引的缓存效果。

最后，还有一点需要说明，InnoDB 使用的 B-tree 索引，数值类型的列除了等值判断时索引会生效之外，使用>、<、>=、<=、BETWEEN...AND... 、<>时，索引仍然生效；对于字符串类型的列，如果使用不以通配符开头的模糊查询，索引也是起作用的，但是其他的情况会导致索引失效，这就意味着很有可能会做全表查询。

视图

视图是关系型数据库中将一组查询指令构成的结果集组合成可查询的数据表的对象。简单的说，视图就是虚拟的表，但与数据表不同的是，数据表是一种实体结构，而视图是一种虚拟结构，你也可以将视图理解为保存在数据库中被赋予名字的 SQL 语句。

使用视图可以获得以下好处：

可以将实体数据表隐藏起来，让外部程序无法得知实际的数据结构，让访问者可以使用表的组成部分而不是整个表，降低数据库被攻击的风险。
在大多数的情况下视图是只读的（更新视图的操作通常都有诸多的限制），外部程序无法直接透过视图修改数据。
重用 SQL 语句，将高度复杂的查询包装在视图表中，直接访问该视图即可取出需要的数据；也可以将视图视为数据表进行连接查询。
视图可以返回与实体数据表不同格式的数据，在创建视图的时候可以对数据进行格式化处理。

创建视图。

-- 创建视图
create view `vw_avg_score` 
as 
    select `stu_id`, round(avg(`score`), 1) as `avg_score` 
    from `tb_record` group by `stu_id`;

-- 基于已有的视图创建视图
create view `vw_student_score` 
as 
    select `stu_name`, `avg_score` 
    from `tb_student` natural join `vw_avg_score`;

提示：因为视图不包含数据，所以每次使用视图时，都必须执行查询以获得数据，如果你使用了连接查询、嵌套查询创建了较为复杂的视图，你可能会发现查询性能下降得很厉害。因此，在使用复杂的视图前，应该进行测试以确保其性能能够满足应用的需求。

使用视图。

select * from `vw_student_score` order by `avg_score` desc;

+--------------+----------+
| stuname      | avgscore |
+--------------+----------+
| 杨过         |     95.6 |
| 任我行       |     53.5 |
| 王语嫣       |     84.3 |
| 纪嫣然       |     73.8 |
| 岳不群       |     78.0 |
| 东方不败     |     88.0 |
| 项少龙       |     92.0 |
+--------------+----------+

既然视图是一张虚拟的表，那么视图的中的数据可以更新吗？视图的可更新性要视具体情况而定，以下类型的视图是不能更新的：

使用了聚合函数（SUM、MIN、MAX、AVG、COUNT等）、DISTINCT、GROUP BY、HAVING、UNION或者UNION ALL的视图。
SELECT中包含了子查询的视图。
FROM子句中包含了一个不能更新的视图的视图。
WHERE子句的子查询引用了FROM子句中的表的视图。