CDA数据分析师 出品

大家好，欢迎来到小编的MySQL课堂。今天我们一起来看一下MySQL中的索引、关联子查询以及语句的优化技巧。

一、MySQL的索引

数据库索引，是数据库管理系统中一个排序的数据结构，以协助快速查询数据库表中数据。

1、索引的意义

索引用于快速找出在某个字段中有特定值的行。如果不使用索引，MySQL必须从第一条记录开始检索表中的每一条记录，直到找出相关的行，那么表越大，查询数据所花费的时间就越多。如果在表中查询的字段有索引，MySQL能够快速到达一个位置去检索数据文件，而不需要再去查看所有数据，那么将会节省很大一部分查询时间。比如说emp表中1W个员工的记录，要查询salesman的员工信息，如果没有索引，服务器会从表中第一条记录开始，一条条往下遍历，直到找到职位为salesman的员工信息。如果有了索引，它会把job这个字段，通过一定的方法进行存储，在查询这个字段上的信息时，能够快速找到对应的数据，而不需要再遍历1W条记录了。

2、索引的优缺点

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

优点

· 通过索引对数据进行检索，大大提高了数据的查询效率。

缺点

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

· 索引也需要占空间的，创建的索引太多，索引文件也会占用数据库的存储空间。

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

3、创建索引的原则

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

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

· 频繁作为查询条件的字段应该创建索引

· 查询中排序的字段应该创建索引

· 查询中分组或统计的字段应该创建索引

2）不需要创建索引的情况：

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

· 需要频繁增删改的字段不适合创建索引

· where子句中用不到的字段不需要创建索引

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

4、索引的结构

索引是在存储引擎中实现的，使用不同的存储引擎，所支持的索引也是不同的。在mysql中常用两种索引结构BTree和Hash，两种算法检索方式不一样，对查询的作用也不一样。MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎支持HASH和BTREE索引。

MySQL的InnoDB存储引擎是支持hash索引的，不过我们必须启用，hash索引的创建由InnoDB存储引擎自动优化创建，我们干预不了。

5、索引的类型

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

· 普通索引：最基本的索引，没有任何限制

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

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

· 组合索引：一个索引包含多个字段，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。

· 全文索引：通过关键字符，就能找到该字段所属的记录行。仅限MyISAM引擎，且只能在CHAR,VARCHAR,TEXT类型的字段上使用。

· 空间索引：对空间数据类型（GEOMETRY、POINT、LINESTRING、POLYGON）的字段建立的索引，仅限MyISAM引擎，且要求索引字段的取值不能为空。

6、索引的操作

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

1）创建索引

创建表的同时须指定索引名、表名和字段名。

语法：

create index <索引名> on <表名>(<字段名>);

自动创建索引

· 在表中定义了主键约束时，会自动创建一个对应的主键索引。

· 在表中定义了外键约束时，会自动创建一个对应的普通索引。

· 在表中定义了唯一约束时，会自动创建一个对应的唯一索引。

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

mysql> create index job_index on emp(job);

2）查看索引

语法：

show index from <表名>;

示例：查看emp表中的索引

mysql> show index from emp;+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+| emp | 0 | PRIMARY | 1 | empno | A | 14 | NULL | NULL | | BTREE | | | YES | NULL || emp | 0 | ename | 1 | ename | A | 14 | NULL | NULL | YES | BTREE | | | YES | NULL || emp | 1 | deptno | 1 | deptno | A | 3 | NULL | NULL | YES | BTREE | | | YES | NULL || emp | 1 | job_index | 1 | job | A | 5 | NULL | NULL | | BTREE | | | YES | NULL |+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

3）使用索引

在查询语句中使用索引会大大提升数据的检索速度。示例：

mysql> select ename,job,deptno from emp where job='salesman';+--------+----------+--------+| ename | job | deptno |+--------+----------+--------+| allen | salesman | 30 || ward | salesman | 30 || martin | salesman | 30 || turner | salesman | 30 |+--------+----------+--------+

4）删除索引

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

drop index <索引名> on <表名>;

示例：

mysql> drop index job_index on emp;mysql> select ename,job,deptno from emp where job='salesman';+--------+----------+--------+| ename | job | deptno |+--------+----------+--------+| allen | salesman | 30 || ward | salesman | 30 || martin | salesman | 30 || turner | salesman | 30 |+--------+----------+--------+

二、SQL关联子查询

执行逻辑

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

例题精讲

成绩表主要信息如下：

要解决的问题是：查询各科成绩前三名的记录。使用关联子查询解决，代码如下：

select * from sc twhere (select sum(score>t.score) from sc where c_id=t.c_id)<3order by c_id,score desc;

第一步：先执行外层查询

select * from sc t;

查询结果也就是该表的全部记录

第二步：因为子查询中连接了这个表本身（where cid=t.cid），所以将第一条记录转到子查询。子查询是sum(score>t.score)即01课程中score>80的人数，所以先判断score>t.score，满足为1，不满足为0，用sum求和即查询出01课程中score>80的有几个人。

相当于执行了：

select sum(score>80) from sc where c_id='01';

查询结果如下：

01课程中score>80的人数为0，即80为第一名。第三步：子查询的结果进入外部查询where子句中和3比较，0<3,条件成立，最终输出t表中的第一条记录。循环执行：t表中第二、三条记录转到子查询，执行判断，输出第二、三条记录。t表中第四条记录转到子查询，01课程中score>70的人数有3人,3<3条件不成立，所以不输出t表中第四条记录。

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

三、SQL语句的优化技巧

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

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

1、where子句中不使用 != ,否则放弃索引全表扫描

比较运算符能用 "="就不用"!="，"="增加了索引的使用几率。

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

select number from t1 where number is null;

优化后 ：在number列上设置默认值0,确保number列无NULL值

select number from t1 where number=0;

3、尽量避免 or 连接条件,否则放弃索引全表扫描优化前 ：

select id from t1 where id=10 or id=20 or id=30;

优化后：

select id from t1 where id=10union allselect id from t1 where id=20union allselect id from t1 where id=30;

4、模糊查询尽量避免使用前置 % ,否则全表扫描

select name from t1 where name like "%c%";

5、尽量避免使用 in 和 not in,否则全表扫描

select id from t1 where id in(1,2,3,4);select id from t1 where id between 1 and 4;

6、尽量避免使用 select * ...;用具体字段代替 * ,不要返回用不到的字段

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

更多优质内容及精彩资讯，可点击【了解更多】迅速进入！