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子句中的表的视图。
删除视图。
drop view vw_student_score;
说明：如果希望更新视图，可以先用上面的命令删除视图，也可以通过
create or replace view来更新视图。
视图的规则和限制。
- 视图可以嵌套，可以利用从其他视图中检索的数据来构造一个新的视图。视图也可以和表一起使用。
- 创建视图时可以使用
order by子句，但如果从视图中检索数据时也使用了order by，那么该视图中原先的order by会被覆盖。 - 视图无法使用索引，也不会激发触发器（实际开发中因为性能等各方面的考虑，通常不建议使用触发器，所以我们也不对这个概念进行介绍）的执行。
函数
MySQL 中的函数跟 Python 中的函数太多的差异，因为函数都是用来封装功能上相对独立且会被重复使用的代码的。如果非要找出一些差别来，那么 MySQL 中的函数是可以执行 SQL 语句的。下面的例子，我们通过自定义函数实现了截断超长字符串的功能。
delimiter $$
create function truncate_string(
content varchar(10000),
max_length int unsigned
) returns varchar(10000) no sql
begin
declare result varchar(10000) default content;
if char_length(content) > max_length then
set result = left(content, max_length);
set result = concat(result, '……');
end if;
return result;
end $$
delimiter ;
说明1：函数声明后面的
no sql是声明函数体并没有使用 SQL 语句；如果函数体中需要通过 SQL 读取数据，需要声明为reads sql data。说明2：定义函数前后的
delimiter命令是为了修改定界符，因为函数体中的语句都是用;表示结束，如果不重新定义定界符，那么遇到的;的时候代码就会被截断执行，显然这不是我们想要的效果。
在查询中调用自定义函数。
select truncate_string('和我在成都的街头走一走，直到所有的灯都熄灭了也不停留', 10) as short_string;
+--------------------------------------+
| short_string |
+--------------------------------------+
| 和我在成都的街头走一…… |
+--------------------------------------+
过程
过程（又称存储过程）是事先编译好存储在数据库中的一组 SQL 的集合，调用过程可以简化应用程序开发人员的工作，减少与数据库服务器之间的通信，对于提升数据操作的性能也是有帮助的。其实迄今为止，我们使用的 SQL 语句都是针对一个或多个表的单条语句，但在实际开发中经常会遇到某个操作需要多条 SQL 语句才能完成的情况。例如，电商网站在受理用户订单时，需要做以下一系列的处理。
- 通过查询来核对库存中是否有对应的物品以及库存是否充足。
- 如果库存有物品，需要锁定库存以确保这些物品不再卖给别人， 并且要减少可用的物品数量以反映正确的库存量。
- 如果库存不足，可能需要进一步与供应商进行交互或者至少产生一条系统提示消息。
- 不管受理订单是否成功，都需要产生流水记录，而且需要给对应的用户产生一条通知信息。
我们可以通过过程将复杂的操作封装起来，这样不仅有助于保证数据的一致性，而且将来如果业务发生了变动，只需要调整和修改过程即可。对于调用过程的用户来说，过程并没有暴露数据表的细节，而且执行过程比一条条的执行一组 SQL 要快得多。
下面的过程实现了查询某门课程的最高分、最低分和平均分。
drop procedure if exists sp_score_stat;
delimiter $$
create procedure sp_score_stat(
courseId int,
out maxScore decimal(4,1),
out minScore decimal(4,1),
out avgScore decimal(4,1)
)
begin
select max(score) into maxScore from tb_record where cou_id=courseId;
select min(score) into minScore from tb_record where cou_id=courseId;
select avg(score) into avgScore from tb_record where cou_id=courseId;
end $$
delimiter ;
说明：在定义过程时，因为可能需要书写多条 SQL，而分隔这些 SQL 需要使用分号作为分隔符，如果这个时候，仍然用分号表示整段代码结束，那么定义过程的 SQL 就会出现错误，所以上面我们用
delimiter $$将整段代码结束的标记定义为$$，那么代码中的分号将不再表示整段代码的结束，整段代码只会在遇到end $$时才会执行。在定义完过程后，通过delimiter ;将结束符重新改回成分号（恢复现场）。
上面定义的过程有四个参数，其中第一个参数是输入参数，代表课程的编号，后面的参数都是输出参数，因为过程不能定义返回值，只能通过输出参数将执行结果带出，定义输出参数的关键字是out，默认情况下参数都是输入参数。
调用过程。
call sp_score_stat(1111, @a, @b, @c);
获取输出参数的值。
select @a as 最高分, @b as 最低分, @c as 平均分;
删除过程。
drop procedure sp_score_stat;
在过程中，我们可以定义变量、条件，可以使用分支和循环语句，可以通过游标操作查询结果，还可以使用事件调度器，这些内容我们暂时不在此处进行介绍。虽然我们说了很多过程的好处，但是在实际开发中，如果频繁的使用过程并将大量复杂的运算放到过程中，会给据库服务器造成巨大的压力，而数据库往往都是性能瓶颈所在，使用过程无疑是雪上加霜的操作。所以，对于互联网产品开发，我们一般建议让数据库只做好存储，复杂的运算和处理交给应用服务器上的程序去完成，如果应用服务器变得不堪重负了，我们可以比较容易的部署多台应用服务器来分摊这些压力。
如果大家对上面讲到的视图、函数、过程包括我们没有讲到的触发器这些知识有兴趣，建议大家阅读 MySQL 的入门读物《MySQL必知必会》进行一般性了解即可，因为这些知识点在大家将来的工作中未必用得上，学了也可能仅仅是为了应付面试而已。
扫一扫
1386
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
