数据库外键关联查询_MySQL索引、关联子查询与SQL语句的优化技巧

由 CDA 数据分析师制作

大家好，欢迎来到我的MySQL课堂，今天我们来学习一下MySQL中索引、相关子查询、语句的优化技巧。

1. MySQL 索引

数据库索引是数据库管理系统中的一种排序数据结构，用于帮助快速查询数据库表中的数据。

1. 指数的意义

索引用于快速查找某个字段具有特定值的行。如果不使用索引，MySQL 必须从第一条记录开始搜索表中的每一条记录，直到找到相关行。表越大，速度越慢，查询数据所花费的时间越多。如果表中查询的字段有索引，MySQL 可以快速到达某个位置来检索数据文件，而不必再次查找所有数据，这将节省大量的查询时间。例如，emp 表中有 10,000 条员工记录，而要查询员工信息，如果没有索引，服务器将从表中的第一条记录开始，逐条向下遍历，直到找到带有该职位的员工信息。它会以一定的方式存储 job 字段。当查询此字段上的信息时，可以快速找到相应的数据，而不必遍历 10,000 条记录。

2. 索引的优点和缺点

MySQL所有字段类型都可以添加索引，但并不是索引越多越好，需要根据业务数据合理使用。

优势

通过索引检索数据，大大提高了数据查询效率。

缺点

创建和维护索引需要时间，并且随着数据量的增加，所花费的时间也会增加。

索引也会占用空间，如果建立的索引过多，索引文件也会占用数据库的存储空间。

当表中增加、删除、修改数据时，索引也需要动态维护，降低了数据维护的速度。

3. 创建索引的原则

1）需要创建索引的情况：

自动为主、外键和唯一约束字段创建索引

经常用作查询条件的字段应该被索引

查询中排序的字段应该被索引

· 查询中用于分组或统计的字段应该被索引

2）无需创建索引：

表中记录太少，不需要创建索引

需要频繁添加、删除、修改的字段不适合创建索引

where 子句中未使用的字段不需要建立索引

重复值较多的字段不需要建立索引

4. 索引结构

索引是在存储引擎中实现的，不同的存储引擎支持的索引不同，MySQL中常见的两种索引结构是BTree和Hash，两种算法的检索方式不同，对查询的影响也不同。/HEAP存储引擎只支持BTREE索引，而/HEAP存储引擎同时支持HASH和BTREE索引。

MySQL 的存储引擎支持哈希索引，但我们必须启用它。哈希索引的创建由存储引擎自动优化，我们无法干预。

5. 指数类型

索引类型可分为以下几种：

普通索引：最基本的索引，无任何限制

唯一索引：索引字段的值不能重复。它可以有空值，但是空值只能出现一次。

主键索引：索引字段的值不能为空，不能重复。

复合索引：索引中包含多个字段，只有查询条件中使用到建立索引时的第一个字段时，才会使用该索引。

全文索引：通过key字符可以找到该字段所属的记录行，这个是引擎限制的，只能用在CHAR，TEXT类型字段上。

空间索引：在空间数据类型（POINT、 、 ）字段上建立的索引，受限于引擎，要求索引字段值不能为空。

6. 索引操作

其实索引也是一张表，在创建索引的时候，数据库管理系统会在本地磁盘上创建一个索引文件，里面存储了索引字段，指向实体表的记录。

1）创建索引

创建表时必须指定索引名、表名、字段名。

语法：

索引在（）；

自动创建索引

当在表中定义主键约束时，会自动创建相应的主键索引。

当在表中定义外键约束时，会自动创建相应的普通索引。

当在表中定义唯一约束时，会自动创建相应的唯一索引。

示例：在 emp 表中为 job 添加普通索引

mysql> emp(job) 上的索引；

2）查看索引

语法：

显示索引来自；

示例：查看emp表中的索引

mysql>显示来自 emp 的索引；+-------+------------+-----------+--------- -----+-------------+-----------+-------------+---- ------+--------+---------+------------+---- ----------+---------+----------------+| 表 | | | | | | | | | 空 | | | | | |+-------+------------+-----------+------------- -+-------------+-------------+-------------+-------- --+--------+------+------------+----------+-------- -------+---------+----------------+| emp | 0 | | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | 是 | NULL || emp | 0 | ename | 1 | ename | A | 14 | NULL | NULL | 是 | BTREE | | | 是 NULL || emp | 1 | | 1 | | A | 3 | NULL | NULL | 是 | BTREE | | | 是 | NULL || emp | 1 | | NULL | | BTREE | | | 是 | NULL |+-------+------------+-----------+---- ----------+-------------+-------------+------------- +-------+--------+---------+--------- +---------------+---------+----------------+

3）使用索引

在查询语句中使用索引将大大提高数据检索的速度。例如：

mysql> ename,job, 来自 emp 其中 job='';+--------+-------+--------+| ename | job | | +--------+-----------+--------+| allen | | 30 || ward | | 30 || | | | 30 || | | 30 |+--------+----------+--------+

4）删除索引

删除索引只是删除表中的索引对象，表中的数据不会被删除。语法：

删除索引；

例子：

mysql> 删除 emp 上的索引；mysql> ename,job，从 emp 中删除 job=''；+--------+-------+-------- +| ename | job | |+--------+----------+--------+| allen | | 30 || ward | | 30 || | | | 30 || | | 30 |+--------+----------+--------+

2. SQL相关子查询

执行逻辑

对于外部查询返回的每条记录，内部查询都会执行一次。在相关子查询中，信息流是双向的。外部查询的每条记录都会传递给子查询，然后子查询会根据条件执行并返回其记录。然后外部查询会根据返回的记录做出决策。

示例解释

成绩单主要信息如下：

需要解决的问题是：查询每个主题的前三条记录。使用相关子查询来解决。代码如下：

* 来自 sc（sum(score>t.score) 来自 sc 其中 c_id=t.c_id）

步骤 1：先执行外部查询

* 来自 sc t；

查询结果是表中的所有记录。

第二步：因为子查询是自己连接表（where cid=t.cid），所以把第一条记录转给子查询，子查询为sum(score>t.score)，就是求01课程中score>的和。score>80的人数为80人，所以先判断是否score>t.score，如果满足则为1，否则为0。用sum求出01课程中score>80的人数。

这相当于执行：

总和（分数>80）来自 sc，其中 c_id='01'；

查询结果如下：

01课程成绩>80的人数为0人，即80为第一名。第三步：子查询的结果进入外层查询的where子句，与3进行比较。0循环执行：t表中的第二条、第三条记录转到子查询，执行判断，输出第二条、第三条记录。t表中的第四条记录转到子查询。01课程成绩>70的人数为3人，3

继续循环，直到表t中最后一条记录，最后输出结果按照课程号和成绩排序。

3. SQL语句优化技巧

应用程序慢如牛的原因有很多，可能是网络问题，可能是系统架构问题，也可能是数据库问题。那么如何才能提高数据库SQL语句的执行速度呢？

如果对程序中嵌入的SQL语句使用一些优化技巧，将会达到事半功倍的效果。

1. where子句中不要使用!=，否则索引全表扫描被放弃

如果可以使用比较运算符“=”，则不要使用“!=”。“=”会增加使用索引的概率。

2.尽量避免NULL值判断，否则放弃优化前的索引全表扫描：

从 t1 开始，其中为空；

优化后：在该列上设置默认值0，保证该列没有NULL值

从 t1 开始，其中 =0；

3.尽量避免或连接条件，否则放弃优化前的索引全表扫描：

来自 t1 的 id，其中 id=10 或 id=20 或 id=30；

优化：

来自 t1 的 id，其中 id=来自 t1 的 id，其中 id=来自 t1 的 id，其中 id=30；

4. 模糊查询中避免使用前导%，否则将扫描整个表。

名称来自 t1，其中名称类似“%c%”；

5.尽量避免使用in和not in，否则会扫描全表

id 来自 t1，其中 id 在（1,2,3,4）中；id 来自 t1，其中 id 1 和 4；

6. 尽量避免使用 *…；使用具体字段代替 *，并且不要返回未使用的字段

SQL 调优的方法有很多种，同一个查询结果可以用很多种不同的方式查询，其实最好的方式还是先在开发环境中用最真实的数据集和硬件环境进行测试，然后再发布到生产环境中去。