MySQL底层概述—8.JOIN排序索引优化

大纲

1.Join算法原理

2.IN和EXISTS函数

3.MySQL排序之索引排序(Using index)

4.MySQL排序之额外排序(Using filesort)

5.排序优化之尽量使用索引排序

6.索引单表优化

7.索引多表优化

1.Join算法原理

(1)Join简介

(2)驱动表的定义

(3)三种Join算法

(4)总结

(1)Join简介

Join是用来联表匹配两个表的数据，筛选并合并出符合要求的结果集。Join操作有多种方式，取决于最终数据的合并效果。常用连接方式的有以下几种：

一.Left Join左外连接

二.Right Join右外连接

三.Inner Join内连接

(2)驱动表的定义

多表关联查询时，第一个被处理的表就是驱动表，驱动表会关联其他表。驱动表会直接影响多表连接的关联顺序，也决定后续关联时的查询性能。

驱动表的选择遵循一个原则：在对最终结果集没影响的前提下，优先选择结果集最小的表作为驱动表。

(3)三种Join算法

一.简单嵌套循环连接算法

简单嵌套循环连接算法就是一个双层for循环。通过循环外层表的行数据，逐个与内层表的所有行数据比较来获取结果。这种算法是最简单的方案，性能也一般，对内循环没优化。例如有这样一条SQL：

-- 连接用户表与订单表，连接条件是：u.id = o.user_id
select * from user t1 left join order t2 on t1.id = t2.user_id;
-- user表为驱动表，order表为被驱动表

转换成代码执行时的思路是这样的：

for (user表的行 uRow : user表) {
    for (Order表的行 oRow : order表) {
        if (uRow.id == oRow.user_id) {
            returnuRow;
        }
    }
}

匹配过程如下图：

简单嵌套循环连接算法的特点：

一.容易理解，通过双层循环比较数据来获得结果；

二.查询效率非常慢，假设A表有N行，B表有M行；

简单嵌套循环连接算法的开销：

A表每扫描1次，B表就要扫描M次。一共有N个内循环，每个内循环要M次，一共有内循环N * M次；

二.索引嵌套循环连接算法

索引嵌套循环连接算法的优化思路是：减少内层表数据的匹配次数。

与简单嵌套循环连接算法最大的区别在于：用来进行Join的字段已经在被驱动表中建立了索引，从原来的"匹配次数 = 外层表行数 * 内层表行数"，变成"匹配次数 = 外层表的行数 * 内层表索引的高度" ，极大提升了性能。

当Order表的user_id为索引时执行过程会如下图：使用索引嵌套循环连接算法的前提是匹配的字段必须建立了索引。

三.块嵌套循环连接算法

如果Join的字段有索引，MySQL会使用索引嵌套循环连接算法。但如果Join的字段没有索引，MySQL此时会如何处理？

因为不存在索引了，所以被驱动表需要进行扫描。此时MySQL并不会简单粗暴的应用简单嵌套循环连接算法，而是加入Join Buffer缓冲区，降低内循环个数，即被驱动表的扫描次数。

在外层循环扫描User表的记录时，会把Join用到的列都缓存到Buffer中。Join Buffer中的数据记录无需一条一条取出和Order表进行比较，而是可以将整个Join Buffer的数据拿去和Order表进行批量比较。

如果User表中要进行Join的数据很多，则会缓存很多次到Join Buffer中。如果Join Buffer很大，可容纳User表所有记录，则Order表只需访问一次。Join Buffer默认大小256K，有n个Join操作，就会生成n-1个Join Buffer。

mysql> show variables like '%join_buffer%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+


mysql> set session join_buffer_size=262144;
Query OK, 0 rows affected (0.00 sec)

(4)总结

一.用小结果集驱动大结果集

其本质就是减少外层循环的数据量；

二.为匹配的条件增加索引

其本质就是减少内层表的扫描次数；

三.增大Join Buffer的大小

一次缓存数据越多，内层表扫描次数越少；

四.减少不必要的字段查询

字段越少，Join Buffer缓存的数据就越多；

2.IN和EXISTS函数

(1)in函数(后面跟小表)

(2)exists函数(后面跟大表)

(3)in和exists的区别

首先创建部门表与员工表，并插入数据：

-- 部门表
CREATE TABLE department (
    id INT(11) PRIMARY KEY,
    deptName VARCHAR(30),
    address VARCHAR(40)
);


-- 部门表测试数据
INSERT INTO `department` VALUES (1, '研发部', '1层');
INSERT INTO `department` VALUES (2, '人事部', '3层');
INSERT INTO `department` VALUES (3, '市场部', '4层');
INSERT INTO `department` VALUES (5, '财务部', '2层');


-- 员工表
CREATE TABLE employee (
    id INT(11) PRIMARY KEY,
    name VARCHAR(20),
    dep_id INT(11),
    age INT(11),
    salary DECIMAL(10, 2)
);


-- 员工表测试数据
INSERT INTO `employee` VALUES (1, '鲁班', 1, 15, 1000.00);
INSERT INTO `employee` VALUES (2, '后裔', 1, 22, 2000.00);
INSERT INTO `employee` VALUES (4, '阿凯', 2, 20, 3000.00);
INSERT INTO `employee` VALUES (5, '露娜', 2, 30, 3500.00);
INSERT INTO `employee` VALUES (6, '李白', 3, 25, 5000.00);
INSERT INTO `employee` VALUES (7, '韩信', 3, 50, 5000.00);
INSERT INTO `employee` VALUES (8, '蔡文姬', 3, 35, 4000.00);
INSERT INTO `employee` VALUES (3, '孙尚香', 4, 20, 2500.00);

(1)in函数(后面跟小表)

假设department表的数据小于employee表数据，那么将所有部门的员工都查出来应该使用in函数。

-- 编写SQL，使in函数
SELECT * FROM employee e WHERE e.dep_id IN (SELECT id FROM department);

in函数的执行原理：in语句只执行一次，将in后的小表中的所有id字段查询出来并且缓存。然后检查department小表中的id与employee大表中的dep_id是否相等。如果相等则添加到结果集，直到遍历完department小表所有的记录。

-- 先循环: select id from department; 相当于得到了小表的数据
-- 后循环: select * from employee where e.dep_id  = d.id;
for (i = 0; i < $dept.length; i++) {  -- 小表
    for (j = 0 ; j < $emp.legth; j++) {  -- 大表
        if ($dept[i].id == $emp[j].dep_id) {
            $result[i] = $emp[j];
            break;
        }
    }
}

结论：如果子查询得出的结果集记录较少，主查询中的表较大时应该用in。

(2)exists函数(后面跟大表)

假设department表的数据大于employee表数据，那么将所有部门下的的员工都查出来，应该使用exists函数。

EXPLAIN SELECT * FROM employee e WHERE EXISTS 
    (SELECT id FROM department d WHERE d.id = e.dep_id);

exists特点：exists子句返回的是一个布尔值，如果有返回数据则返回值是true。如果结果为true，外层的查询语句会进行匹配，否则外层查询语句将不进行查询或者查不出任何记录。

exists函数的执行原理：先循环exists前面的表，此时为employee小表。然后遍历循环exists后面的表，此时为department为大表。

-- 先循环: SELECT * FROM employee e;
-- 再判断: SELECT id FROM department d WHERE d.id = e.dep_id
for (j = 0; j < $emp.length; j++) {  -- 小表
    -- 遍历循环外表，通过exists()函数检查外表中的记录有没有和内表的的数据一致的, 匹配得上就放入结果集
    if (exists(emp[i].dep_id)) {   -- 大表
        $result[i] = $emp[i];
    }
}

(3)in和exists的区别

一.子查询的结果集记录较少，主查询中的表较大且又有索引时用in；

二.主查询的结果集记录较少，子查询中的表较大且又有索引时用exists；

in后面跟的是小表，exists后面跟的是大表。

3.MySQL排序之索引排序(Using index)

(1)MySQL中的两种排序方式

(2)索引排序

(1)MySQL中的两种排序方式

一.索引排序

通过索引顺序扫描直接返回有序数据；

二.额外排序

没用到索引排序就对返回的数据使用文件排序；

Order By优化的核心原则：尽量减少文件排序，通过索引直接返回有序的数据，也就是参与排序的字段一定要有索引。

(2)索引排序

因为索引的结构是B+树，索引中的数据是按照一定顺序进行排列的，所以在排序查询中如果能利用索引，就能避免额外的排序操作。EXPLAIN分析查询时，Extra显示为Using index；

比如查询条件是where age = 19 order by name，那么查询过程就是会找到满足age = 19的记录，而符合这条的所有记录一定是按照name排序的，所以也不需要额外进行排序。

4.MySQL排序之额外排序(Using filesort)

(1)按执行位置划分：Sort Buffer排序和Sort Buffer + 临时文件排序

(2)按执行方式划分：全字段排序和rowid排序

(3)总结

所有不通过索引直接返回排序结果的操作都是Filesort排序，也就是进行了额外的排序操作，额外排序。EXPLAIN分析查询时，Extra显示为Using filesort。

(1)按执行位置划分：Sort Buffer排序和Sort Buffer + 临时文件排序

一.Sort Buffer排序

MySQL为每个线程各维护了一块内存区域Sort Buffer，用于进行排序。Sort Buffer的大小可以通过sort_buffer_size来设置。

mysql> SHOW variables LIKE '%sort_buffer_size%';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| sort_buffer_size        | 262144  |
+-------------------------+---------+

注意：sort_buffer_size并不是越大越好(和Join Buffer一样默认256K)。由于是连接级别的参数，每个连接就会有一个Sort Buffer。那么过大设置Sort Buffer在高并发的场景下，可能会耗尽系统内存。

二.Sort Buffer + 临时文件排序

如果加载记录的字段总长度小于sort_buffer_size，那么就使用Sort Buffer排序；如果超过sort_buffer_size，则使用Sort Buffer + 临时文件进行排序。

临时文件又分为内存临时表和磁盘临时表。如果内存临时表大小超过tmp_table_size，那么就会转成磁盘临时表。

(2)按执行方式划分：全字段排序和rowid排序

执行方式是由一个参数与排序后获取的单条记录的字段总长度决定的，这个参数就是max_length_for_sort_data。

如果排序后获取的单条记录的字段总长度 <= max_length_for_sort_data，那么就使用全字段排序，反之则使用rowid排序。

mysql> SHOW variables LIKE 'max_length_for_sort_data';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| max_length_for_sort_data | 1024  |
+--------------------------+-------+

一.全字段排序

全字段排序就是将查询的所有字段全部加载进来进行排序。优点：查询快，执行过程简单，排序后不需要回表。缺点：需要的空间大。

SELECT name,age,addr FROM user WHERE addr = '北京' ORDER BY name LIMIT 1000; -- addr有索引

上面查询语句的执行流程：

第一：初始化Sort Buffer，确定放入name、age、addr这3个字段；

第二：从索引addr中找到第一个满足addr='北京'的主键ID(ID_x)；

第三：到主键索引中找到ID_x，取出对应字段的值，存入Sort Buffer；

第四：从索引addr取下一个记录的主键ID；

第五：重复3、4，直到addr值不满足条件；

第六：对Sort Buffer的数据按name快速排序；

第七：把排序结果中的前1000行返回给客户端；

二.rowid排序

rowid排序相对于全字段排序，不会把所有字段都放入Sort Buffer，所以在Sort Buffer中进行排序之后还得回表查询。缺点：会产生更多次数的回表查询，查询可能会慢一些。优点：所需的空间更小。

SELECT name,age,addr FROM user WHERE addr = '北京' ORDER BY name LIMIT 1000; -- addr有索引

假设name、age、addr3个字段定义的总长度为36，而max_length_for_sort_data = 16，就是单行的长度超了。此时MySQL会认为单行太大，需要换一个算法。

放入Sort Buffer的字段就会只有要排序的字段name和主键id，那么排序的结果中就少了addr和age，就需要回表了。

上面查询语句的执行流程：

第一：初始化Sort Buffer，确定放入2个字段，name和id；

第二：从索引addr中找到第一个满足addr='北京'的主键ID(ID_x)；

第三：到主键索引中取出整行，把name、id这2个字段放入Sort Buffer；

第四：从索引addr取下一个记录的主键ID；

第五：重复3、4，直到addr值不满足条件；

第六：对Sort Buffer的数据按name快速排序；

第七：取排序结果中的前1000行，回表取出name、age、addr返回；

(3)总结

如果MySQL认为内存足够大，会优先选择全字段排序，也就是把需要获取的字段都先放到Sort Buffer中。这样排序后就会直接从内存里返回查询结果了，不用再回表去取数据。

MySQL的设计思想：如果内存够，就多利用内存，尽量减少磁盘访问。对于InnoDB来说，rowid排序会回表多次造成磁盘读，不会优先被选择。

5.排序优化之尽量使用索引排序

(1)MySQL中的两种排序方式

一.索引排序：通过索引扫描返回有序数据；

二.额外排序：对返回的数据进行文件排序；

(2)Order By优化的核心原则

尽量减少额外排序，通过索引返回有序数据。

(3)排序优化场景

场景一：只查询用于排序的索引字段，可以利用索引进行排序，最左原则

场景二：排序字段在多个索引中，那么无法使用索引进行排序

场景三：只查询用于排序的索引字段和主键，可以利用索引进行排序

场景四：查询主键之外的没有添加索引的字段，不会利用索引排序

场景五：排序字段顺序与索引列顺序不一致，无法利用索引排

场景六：where条件是范围查询时，不按索引顺序排序会使索引失效

场景七：升降序不一致，无法利用索引排

添加索引：

-- 为employee表创建联合索引
ALTER TABLE employee ADD INDEX idx_name_age(name,age);
-- 为薪资字段添加索引
ALTER TABLE employee ADD INDEX idx_salary(salary);

查看employee表的索引情况：

SHOW INDEX FROM employee;

场景1：只查询用于排序的索引字段，可以利用索引进行排序，最左原则

查询name和age两个字段，对name与age排序。

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name,e.age;

场景2：排序字段在多个索引中，无法使用索引进行排序

查询name和salary字段，对name与salary排序。

EXPLAIN SELECT e.name, e.salary FROM employee e ORDER BY e.name,e.salary;

场景3：只查询用于排序的索引字段和主键，可以利用索引进行排序

查询id和name，对name进行排序。

EXPLAIN SELECT e.id, e.name FROM employee e ORDER BY e.name;

场景4：查询主键之外的没有添加索引的字段，不会利用索引排序

查询dep_id，对name进行排序。

EXPLAIN SELECT e.dep_id FROM employee e ORDER BY e.name;--索引失效
EXPLAIN SELECT id, e.dep_id FROM employee e ORDER BY e.name;--索引失效
EXPLAIN SELECT * FROM employee e ORDER BY e.name;--索引失效
EXPLAIN SELECT e.id, e.age FROM employee e ORDER BY e.name;--索引生效

场景5：排序字段顺序与索引列顺序不一致，无法利用索引排序

使用联合索引时，ORDER BY的排序字段顺序和联合索引列顺序匹配。

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.age,e.name;

场景6：where条件是范围查询时，不按索引顺序排序会使索引失效

比如添加条件"age>18"，然后再根据age排序。

EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age > 10 ORDER BY e.age;
EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.name > 'test' ORDER BY e.name;

注意：ORDER BY子句的字段不要求一定是索引中第一列才可以利用索引排序。比如在等值查询时非第一列时也可利用索引排序(都一样相当于没排序)，但范围查询非第一列时，则不可以利用索引排序。

EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age = 18 ORDER BY e.age;

场景7：升降序不一致，无法利用索引排序

ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。

-- 升序
EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name , e.age ;
-- 降序
EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name DESC, e.age DESC;

name字段升序，age字段降序，索引会失效。

EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name, e.age DESC;

6.索引单表优化

下面是一张用户通讯表的表结构信息，这张表来源于实际项目中，有接近500万条数据。

CREATE TABLE user_contacts (
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) DEFAULT NULL COMMENT '用户标识',
    mobile VARCHAR(50) DEFAULT NULL COMMENT '手机号',
    name VARCHAR(20) DEFAULT NULL COMMENT '姓名',
    verson INT(11) NOT NULL DEFAULT '0' COMMENT '版本',
    create_by VARCHAR(64) DEFAULT NULL COMMENT '创建者',
    create_date DATETIME NOT NULL COMMENT '创建时间',
    update_by VARCHAR(64) DEFAULT NULL COMMENT '更新者',
    update_date DATETIME NOT NULL COMMENT '更新时间',
    remarks VARCHAR(255) DEFAULT NULL COMMENT '备注信息',
    del_flag CHAR(1) NOT NULL DEFAULT '0' COMMENT '删除标识',
    PRIMARY KEY (id)
);

(1)查询所有名字中包含李的用户姓名和手机号，并根据user_id字段排序

SELECT name, mobile FROM user_contacts WHERE name LIKE '李%' ORDER BY user_id;

通过explain命令，查看SQL查询优化信息。

EXPLAIN SELECT name, mobile FROM user_contacts WHERE name LIKE '%李%' ORDER BY user_id;

分析的结果显示：type=ALL是最坏的情况，并且Extra中还出现了Using FIlesort，文件排序未使用到索引，所以必须优化。

具体的优化过程如下：

一.首先添加联合索引

该联合索引包含所有要查询的字段，使其成为覆盖索引，试图一并解决like模糊查询时索引失效问题。

ALTER TABLE user_contacts ADD INDEX idx_nmu(name, mobile, user_id);

二.explain分析

EXPLAIN SELECT name, mobile FROM user_contacts WHERE name LIKE '%李%' ORDER BY user_id;

三.分析结果显示

type类型提升到了index级别，通过索引就获取到全部数据，但Extra字段中还存在Using filesort。

四.继续优化

根据最左侧列+最左前缀法则，只有最左侧列是有序的，所以按哪一列排序就把该列放在索引最左侧。在创建联合索引时，正确的顺序应该是：user_id, name, mobile

-- 删除索引
DROP INDEX idx_nmu ON user_contacts;
-- 添加重新排序后的索引
ALTER TABLE user_contacts ADD INDEX idx_unm(user_id, name, mobile);

五.发现type=index，Using filesort没有了

EXPLAIN SELECT name, mobile FROM user_contacts WHERE name LIKE '%李%' ORDER BY user_id;

(2)统计手机号是135、136、186、187开头的用户数量

EXPLAIN SELECT COUNT(*) FROM user_contacts 
    WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';

通过explain命令，查看SQL查询优化信息：

type = index表示使用到了索引，但是进行了索引全表扫描；

key = idx_unm表示使用到了联合索引，但是rows的效果并不是很好；

Extra = Using where; Using index表示查询的列被索引覆盖了，但是无法通过该索引直接获取数据；

综合上面的执行计划给出的信息，需要进行优化，具体的优化过程如下：

一.经过上面的分析，发现联合索引没有发挥作用

所以尝试对mobile字段单独建立索引。

ALTER TABLE user_contacts ADD INDEX idx_m(mobile);

二.再次执行，得到下面的分析结果

EXPLAIN SELECT COUNT(*) FROM user_contacts 
    WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';

type = range表示使用了索引进行范围查询，常见于使用>、>=、<、<=、BETWEEN、IN()或like等运算符的查询中；

key = idx_m表示MySQL选择了为mobile字段创建的索引进行数据检索；

rows = 1575026表示获取所需数据而进行扫描的行数，由原来的4162898变为1575026，比之前减少了近三分之一；

三.count(*)和count(1)和count(列名)区别

进行统计操作时，count的统计条件有三种：

EXPLAIN  SELECT COUNT(*) FROM user_contacts 
    WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';


EXPLAIN  SELECT COUNT(id) FROM user_contacts 
    WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';


EXPLAIN  SELECT COUNT(1) FROM user_contacts 
    WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';

执行效果：

count(*)包括了所有的列，在统计时不会忽略列值为null的数据；

count(1)用1表示代码行，在统计时也不会忽略列值为null的数据；

count(列名)在统计时会忽略列值为空的数据，不统计值为null的某字段；

执行效率：

列名为主键时count(列名)会比count(1)快；

列名不是主键时count(1)会比count(列名)快；

如果表没有主键，count(1)会比count(*)快；

如果表只有一个字段，则count(*)最优；

(3)查询2019-2-16新增的用户联系人信息，查询字段是name和mobile

EXPLAIN SELECT name,mobile FROM user_contacts WHERE DATE_FORMAT(create_date,'%Y-%m-%d')='2019-02-16';

一.优化

explain分析的结果显示type=ALL进行了全表扫描，需要进行优化。下面为create_date字段添加索引：

ALTER TABLE user_contacts ADD INDEX idx_cd(create_date);
EXPLAIN SELECT name,mobile FROM user_contacts WHERE DATE_FORMAT(create_date,'%Y-%m-%d')='2019-02-16';

添加索引后，发现并没有使用到索引key=null。分析原因：create_date字段是datetime类型，转换为日期再匹配，因为需要查询出所有行进行转换后再过滤，所以导致索引失效。

二.继续优化

改为使用between...and...使索引生效；

EXPLAIN SELECT name,mobile FROM user_contacts WHERE create_date 
    BETWEEN '2019-02-16 00:00:00' AND '2019-02-16 23:59:59';

type=range表示使用了索引进行范围查询；

Extra=Using index condition; Using MRR：

Using index condition表示使用了部分索引，MRR表示把随机磁盘读转化为顺序磁盘读，从而提高了索引查询的性能；

(4)获取用户通讯录表第10万条数据开始后的100条数据

EXPLAIN SELECT * FROM user_contacts uc LIMIT 100000,100;
-- 查询记录量越来越大，所花费的时间也会越来越多
EXPLAIN SELECT * FROM user_contacts uc LIMIT 1000000,1000;
EXPLAIN SELECT * FROM user_contacts uc LIMIT 2000000,10000;
EXPLAIN SELECT * FROM user_contacts uc LIMIT 3000000,100000;

LIMIT子句可以被用于指定SELECT语句返回的记录数，需注意以下几点：

第一个参数指定第一个返回记录行的偏移量，注意从0开始；

第二个参数指定返回记录行的最大数目；

如果只给定一个参数：它表示返回最大的记录行数目，初始记录行的偏移量是0(而不是1)。

优化1：通过主键索引进行分页(主键字段是连续递增的)

直接进行limit操作，会产生全表扫描，速度很慢。查询的数据量越大，花费时间越多。limit限制的是从结果集的M位置处取出N条输出，其余抛弃。

假设ID是连续递增的：根据查询页数和记录数可算出查询的id范围，然后配合limit使用。

EXPLAIN SELECT * FROM user_contacts WHERE id >= 100001 LIMIT 100;

可见，type类型提升到了range级别。

优化2：如果主键字段不是连续递增的，则使用子查询优化

-- 首先定位偏移位置的id
SELECT id FROM user_contacts LIMIT 100000,1;
-- 根据获取到的id值向后查询
EXPLAIN SELECT * FROM user_contacts WHERE id >= 
  (SELECT id FROM user_contacts LIMIT 100000,1) LIMIT 100;

7.索引多表优化

(1)相关表介绍

一.用户手机认证表

该表约有11万数据，保存的是通过手机认证后的用户数据，关联字段是user_id。

CREATE TABLE `mob_autht` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '标识',
    `user_id` int(11) NOT NULL COMMENT '用户标识',
    `mobile` varchar(11) NOT NULL COMMENT '手机号码',
    `seevc_pwd` varchar(12) NOT NULL COMMENT '服务密码',
    `autht_indc` varchar(1) NOT NULL DEFAULT '0' COMMENT '认证标志',
    `verson` int(11) NOT NULL DEFAULT '0' COMMENT '版本',
    `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
    `create_date` datetime NOT NULL COMMENT '创建时间',
    `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
    `update_date` datetime NOT NULL COMMENT '更新时间',
    `remarks` varchar(255) DEFAULT NULL COMMENT '备注信息',
    `del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标识',
    PRIMARY KEY (`id`)
);

二.紧急联系人表

该表约有22万数据，注册成功后，用户添加的紧急联系人信息，关联字段是user_id。

CREATE TABLE `ugncy_cntct_psn` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '标识',
    `psn_info_id` int(11) DEFAULT NULL COMMENT '个人信息标识',
    `user_id` int(11) NOT NULL COMMENT '向钱用户标识',
    `cntct_psn_name` varchar(10) NOT NULL COMMENT '联系人姓名',
    `cntct_psn_mob` varchar(11) NOT NULL COMMENT '联系手机号',
    `and_self_rltn_cde` char(2) NOT NULL COMMENT '与本人关系代码 字典表关联',
    `verson` int(11) NOT NULL DEFAULT '0' COMMENT '版本',
    `create_by` varchar(64) DEFAULT NULL COMMENT '创建者',
    `create_date` datetime NOT NULL COMMENT '创建时间',
    `update_by` varchar(64) DEFAULT NULL COMMENT '更新者',
    `update_date` datetime NOT NULL COMMENT '更新时间',
    `remarks` varchar(255) DEFAULT NULL COMMENT '备注信息',
    `del_flag` char(1) NOT NULL DEFAULT '0' COMMENT '删除标识',
    PRIMARY KEY (`id`)
);

三.借款申请表

该表约有11万数据，保存的是每次用户申请借款时填写的信息，关联字段是user_id。

CREATE TABLE `loan_apply` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '借款申请标识',
    `loan_nbr` VARCHAR(50) NOT NULL COMMENT '借款编号',
    `user_id` INT(11) NOT NULL COMMENT '用户标识',
    `idnt_info_id` INT(11) DEFAULT NULL COMMENT '身份信息标识',
    `psn_info_id` INT(11) DEFAULT NULL COMMENT '个人信息标识',
    `mob_autht_id` INT(11) DEFAULT NULL COMMENT '手机认证标识',
    `bnk_card_id` INT(11) DEFAULT NULL COMMENT '银行卡标识',
    `apply_limit` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '申请额度',
    `apply_tlmt` INT(3) NOT NULL COMMENT '申请期限',
    `apply_time` DATETIME NOT NULL COMMENT '申请时间',
    `audit_limit` DECIMAL(16,2) NOT NULL COMMENT '审核额度',
    `audit_tlmt` INT(3) NOT NULL COMMENT '审核期限',
    `audit_time` DATETIME DEFAULT NULL COMMENT '审核时间',
    `cfrm_limit` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '确认额度',
    `cfrm_tlmt` INT(3) NOT NULL COMMENT '确认期限',
    `cfrm_time` DATETIME DEFAULT NULL COMMENT '确认时间',
    `loan_sts_cde` CHAR(1) NOT NULL COMMENT '借款状态:0 未提交 1 提交申请(初始) 2 已校验 3 通过审核4 未通过审核 5开始放款 6放弃借款 7 放款成功 ',
    `audit_mod_cde` CHAR(1) NOT NULL COMMENT '审核模式：1 人工 2 智能',
    `day_rate` DECIMAL(16,8) NOT NULL DEFAULT '0.00000000' COMMENT '日利率',
    `seevc_fee_day_rate` DECIMAL(16,8) NOT NULL DEFAULT '0.00000000' COMMENT '服务费日利率',
    `normal_paybk_tot_day_rate` DECIMAL(16,8) NOT NULL DEFAULT '0.00000000' COMMENT '正常还款总日利率',
    `ovrdu_fee_day_rate` DECIMAL(16,8) DEFAULT NULL COMMENT '逾期违约金日利率',
    `day_intr_amt` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '日利率金额',
    `seevc_fee_day_intr_amt` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '服务日利率金额',
    `normal_paybk_tot_intr_amt` DECIMAL(16,2) NOT NULL DEFAULT '0.00' COMMENT '综合日利率金额',
    `cnl_resn_time` DATETIME DEFAULT NULL COMMENT '放弃时间',
    `cnl_resn_cde` CHAR(8) DEFAULT NULL COMMENT '放弃原因：关联字典代码',
    `cnl_resn_othr` VARCHAR(255) DEFAULT NULL COMMENT '放弃的其他原因',
    `verson` INT(11) NOT NULL DEFAULT '0' COMMENT '版本',
    `create_by` VARCHAR(64) DEFAULT NULL COMMENT '创建者',
    `create_date` DATETIME NOT NULL COMMENT '创建时间',
    `update_by` VARCHAR(64) DEFAULT NULL COMMENT '更新者',
    `update_date` DATETIME NOT NULL COMMENT '更新时间',
    `remarks` VARCHAR(255) DEFAULT NULL COMMENT '备注信息',
    `loan_dst_cde` CHAR(1) NOT NULL DEFAULT '0' COMMENT '0,未分配; 1,已分配',
    `del_flag` CHAR(1) NOT NULL DEFAULT '0' COMMENT '删除标识',
    `last_loan_apply_id` INT(11) DEFAULT NULL COMMENT '上次借款申请标识',
    PRIMARY KEY (`id`)
);

(2)查询所有认证用户的手机号及认证用户的紧急联系人的姓名与手机号

EXPLAIN SELECT 
    ma.mobile '认证用户手机号',
    ucp.cntct_psn_name '紧急联系人姓名',
    ucp.cntct_psn_mob '紧急联系人手机号' 
FROM mob_autht ma LEFT JOIN ugncy_cntct_psn ucp 
ON ma.user_id = ucp.user_id;

type类型都是ALL，使用了全表扫描。

一.优化：为mob_autht表的user_id字段添加索引

ALTER TABLE mob_autht ADD INDEX idx_user_id(user_id);

根据小结果集及驱动大结果集的原则，驱动表即使建立索引也不会生效，mob_autht有10万数据是驱动表，ugncy_cntct_psn有11万数据是被驱动表。一般情况下：左外连接左表就是驱动表，右外连接右表就是驱动表。此外，explain分析结果中的第一行数据的表信息，就是驱动表。

二.继续优化：为ugncy_cntct_psn表的user_id字段添加索引

ALTER TABLE ugncy_cntct_psn ADD INDEX idx_userid(user_id);

mob_autht的type类型为ALL；

ugncy_cntct_psn的type类型是ref；

(3)获取紧急联系人数量 > 8的用户手机号信息

-- 1.获取紧急联系人 > 8的用户ID
SELECT user_id , COUNT(user_id) c FROM ugncy_cntct_psn GROUP BY user_id HAVING c > 8;


-- 2.获取认证用户的ID与手机号
SELECT user_id, mobile FROM mob_autht;


-- 3.将上面两条SQL进行JOIN连接
EXPLAIN SELECT ucp.user_id,COUNT(ucp.user_id),m.user_id,m.mobile FROM ugncy_cntct_psn ucp 
    INNER JOIN (SELECT user_id, mobile FROM mob_autht) m ON m.user_id = ucp.user_id 
    GROUP BY ucp.user_id HAVING COUNT(ucp.user_id) > 8;

Explain的分析结果显示：

Using temporary：创建了临时表保存结果；

Using filesort：使用了文件排序；

上面这种情况的原因是对所有GROUP BY col1,col2...的字段进行了排序。如果想避免排序对性能消耗，可指定ORDER BY NULL来禁止排序，这样就不会使用文件排序了；

EXPLAIN SELECT ucp.user_id,COUNT(ucp.user_id),m.user_id,m.mobile FROM ugncy_cntct_psn ucp 
    INNER JOIN (SELECT user_id, mobile FROM mob_autht) m ON m.user_id = ucp.user_id 
    GROUP BY ucp.user_id HAVING COUNT(ucp.user_id) > 8 ORDER BY NULL;

(4)获取所有智能审核的用户手机号和申请额度、申请时间、审核额度

EXPLAIN SELECT 
    ma.mobile '用户认证手机号',
    la.apply_limit '申请额度',
    la.apply_time '申请时间',
    la.audit_limit '审核额度' 
FROM mob_autht ma inner JOIN loan_apply la ON ma.id = la.mob_autht_id 
WHERE la.audit_mod_cde = '2';

一.优化分析

查询loan_apply表，使用的条件字段为audit_mod_cde。因为该字段没有添加索引，导致type=ALL发生全表扫描。于是为audit_mod_cde字段添加索引，来提高查询效率。

ALTER TABLE loan_apply ADD INDEX idx_amc(audit_mod_cde);

添加索引后type的类型确实提升了，但是需要注意的扫描的行还是很高，并且Using where表示：通过索引访问时，需要再回表访问所需的数据。

注意：如果执行计划显示使用索引，但rows值很高 + Extra显示为Using Where，那么执行效果不会很好，因为索引访问的成本主要在回表上。

二.继续优化

audit_mod_cde字段的含义是审核模式，只有两个值：1人工，2智能。所以在根据该字段进行查询时，会有大量的相同数据。

比如：统计一下audit_mod_cde = 2的数据总条数，查询结果是9万多条。该表的总数接近11万条，查询出的数据行超过了表的总记录数的30%，这时就不建议添加索引。

比如有1000万的数据，就算平均分后结果集也有500万条，结果集还是太大，查询效率依然不高。

SELECT COUNT(*) FROM loan_apply; -- 109181条
SELECT COUNT(*) FROM loan_apply la WHERE la.audit_mod_cde = '2' ; -- 91630条

总结：唯一性太差的字段不需要创建索引，即便该字段被用在了where条件中。

三.继续优化

如果一定要根据状态字段进行查询，可根据业务需求添加一个日期条件。比如先获取某一时间段的数据，然后再区分状态字段。

ALTER TABLE loan_apply ADD INDEX idx_at(apply_time);
-- 获取2019年1月1号~1月5号的数据
EXPLAIN SELECT 
    ma.mobile '用户认证手机号',
    la.apply_time '申请时间',
    la.apply_limit '申请额度',
    la.audit_limit '审核额度' 
FROM loan_apply la INNER JOIN mob_autht ma ON la.mob_autht_id = ma.id 
WHERE apply_time BETWEEN '2019-01-01 00:00:00' AND '2019-01-05 23:59:59' AND la.audit_mod_cde = '2';

Explain结果中的Extra为"Using index condition; Using where; Using MRR;"，其中Using index condition表示只有一部分索引生效。

Using MRR中的MRR表示：通过范围扫描将数据存入read_rnd_buffer_size，然后对其按主键排序，最后使用排序好的数据进行顺序回表。因为InnoDB中叶子节点数据是按照主键进行排列的，这样就转换随机IO为顺序IO了，从而减小磁盘的随机访问。