MySQL索引优化整合案例实现

绝地灬酷狼 2024-03-17 16:52 50阅读 0赞

#### 目录 ####

*  1 JOIN优化
 *   *  1.1 JOIN算法原理
     *  1.2 in和exists函数
 *  2 order by优化
 *   *  2.1 索引排序
     *  2.2 额外排序
     *  2.3 排序优化
 *  3 索引单表优化案例
 *   *  3.1. 建表
     *  3.2. 单表索引分析
     *  3.1.1 需求
     *  3.1.2 优化
 *  4 索引多表优化案例

--------------------

## 1 JOIN优化 ##

![在这里插入图片描述][8e397dca38d649ae81b3dd6a12612c23.png]

### 1.1 JOIN算法原理 ###

**1) JOIN回顾**

JOIN 是 MySQL 用来进行联表操作的，用来匹配两个表的数据，筛选并合并出符合我们要求的结果集。

JOIN 操作有多种方式，取决于最终数据的合并效果。常用连接方式的有以下几种:

![在这里插入图片描述][c37c2c2ecc65488c99ef1b3c57c4cd54.png]

**2) 驱动表的定义**

什么是驱动表 ?

*  多表关联查询时,第一个被处理的表就是驱动表,使用驱动表去关联其他表.
 *  驱动表的确定非常的关键,会直接影响多表关联的顺序,也决定后续关联查询的性能

驱动表的选择要遵循一个规则:

*  在对最终的结果集没有影响的前提下,优先选择结果集最小的那张表作为驱动表

**3) 三种JOIN算法**

**1.Simple Nested-Loop Join（ 简单的嵌套循环连接** )

*  简单来说嵌套循环连接算法就是一个双层for 循环 ，通过循环外层表的行数据，逐个与内层表的所有行数据进行比较来获取结果.
 *  **这种算法是最简单的方案，性能也一般。对内循环没优化。**
 *  例如有这样一条SQL:
    
        xxxxxxxxxx
    
        -- 连接用户表与订单表 连接条件是 u.id = o.user_id
    
        select * from user t1 left join order t2 on t1.id = t2.user_id;
    
        -- user表为驱动表,order表为被驱动表
 *  转换成代码执行时的思路是这样的:
    
        xxxxxxxxxx
    
        for(user表行 uRow : user表){
    
        for(Order表的行 oRow : order表){
    
        if(uRow.id = oRow.user_id){
    
        return uRow;
    
        }
    
        }
    
        }
 *  匹配过程如下图

![在这里插入图片描述][613713133a01468e898c9eeeb04286bd.png]

*  SNL 的特点
    
     *  简单粗暴容易理解，就是通过双层循环比较数据来获得结果
     *  查询效率会非常慢,假设 A 表有 N 行，B 表有 M 行。SNL 的开销如下：
        
         *  A 表扫描 1 次。
         *  B 表扫描 M 次。
         *  一共有 N 个内循环，每个内循环要 M 次，一共有内循环 N \* M 次

**2) Index Nested-Loop Join（ 索引嵌套循环连接 ）**

*  Index Nested-Loop Join 其优化的思路: **主要是为了减少内层表数据的匹配次数** , 最大的区别在于，用来进行 join 的字段已经在被驱动表中建立了索引。
 *  从原来的 `匹配次数 = 外层表行数 * 内层表行数` , 变成了 `匹配次数 = 外层表的行数 * 内层表索引的高度` ，极大的提升了 join的性能。
 *  当 `order` 表的 `user_id` 为索引的时候执行过程会如下图：

![在这里插入图片描述][401882e8e73f4a7ea6e1d2abef2a2e4f.png]

**注意：使用Index Nested-Loop Join 算法的前提是匹配的字段必须建立了索引。**

**3) Block Nested-Loop Join( 块嵌套循环连接 )**

*  如果 join 的字段有索引，MySQL 会使用 INL 算法。如果没有的话，MySQL 会如何处理？
 *  因为不存在索引了，所以被驱动表需要进行扫描。这里 MySQL 并不会简单粗暴的应用 SNL 算法，而是加入了 buffer 缓冲区，降低了内循环的个数，也就是被驱动表的扫描次数。

![在这里插入图片描述][4bf0845eb33143bfbc508f41eb31b52c.png]

*  在外层循环扫描 user表中的所有记录。扫描的时候，会把需要进行 join 用到的列都缓存到 buffer 中。buffer 中的数据有一个特点，里面的记录不需要一条一条地取出来和 order 表进行比较，而是整个 buffer 和 order表进行批量比较。
 *  如果我们把 buffer 的空间开得很大，可以容纳下 user 表的所有记录，那么 order 表也只需要访问一次。
 *  MySQL 默认 buffer 大小 256K，如果有 n 个 join 操作，会生成 n-1 个 join buffer。
    
        xxxxxxxxxx
    
        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) 总结**

1.  永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)
2.  为匹配的条件增加索引(减少内层表的循环匹配次数)
3.  增大join buffer size的大小（一次缓存的数据越多，那么内层包的扫表次数就越少）
4.  减少不必要的字段查询（字段越少，join buffer 所缓存的数据就越多

### 1.2 in和exists函数 ###

上面我们说了 小表驱动大表,就是小的数据集驱动大的数据集, 主要是为了减少数据库的连接次数,根据具体情况的不同,又出现了两个函数 `exists` 和 `in` 函数

创建部门表与员工表,并插入数据

xxxxxxxxxx
    -- 部门表
    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 函数

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

*  **in函数的执行原理**
    
    1.  `in` 语句, 只执行一次, 将 `department` 表中的所有id字段查询出来并且缓存.
    2.  检查 `department` 表中的id与 `employee` 表中的 `dep_id` 是否相等, 如果相等 添加到结果集, 直到遍历完`department` 所有的记录.

![在这里插入图片描述][e18a62a09ccc43b4b454f9ebc1f142c6.png]

xxxxxxxxxx

-- 先循环: select id from department; 相当于得到了小表的数据

for(i = 0; i < $dept.length; i++){  -- 小表

-- 后循环: select * from employee where e.dep_id  = d.id;

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 函数.
    
        xxxxxxxxxx
    
        explain SELECT * FROM employee e WHERE EXISTS
    
        (SELECT id FROM department d WHERE d.id = e.dep_id);
 *  `exists` 特点
    
    `exists` 子句返回的是一个 布尔值，如果有返回数据，则返回值是`true`，反之是`false`。
    
    如果结果为 `true` , 外层的查询语句会进行匹配,否则 外层查询语句将不进行查询或者查不出任何记录。

![在这里插入图片描述][0049fd4b1d4a4a8faf672d65fde0a347.png]

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

**3) in 和 exists 的区别**

*  如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 `in`
 *  如果主查询得出的结果集记录较少，子查询中的表较大且又有索引时应该用 `exists`
 *  一句话: **in后面跟的是小表，exists后面跟的是大表。**

## 2 order by优化 ##

MySQL中的两种排序方式

*  索引排序: 通过有序索引顺序扫描直接返回有序数据
 *  额外排序: 对返回的数据进行文件排序
 *  ORDER BY优化的核心原则: **尽量减少额外的排序，通过索引直接返回有序数据。**

### 2.1 索引排序 ###

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

![在这里插入图片描述][e83d7de180f94f32ab458219ce9cbc5d.png]

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

### 2.2 额外排序 ###

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

**1) 按执行位置划分**

*  Sort\_Buffer MySQL 为每个线程各维护了一块内存区域 sort\_buffer ，用于进行排序。sort\_buffer 的大小可以通过 sort\_buffer\_size 来设置。
    
        xxxxxxxxxx
    
        mysql> show variables like '%sort_buffer_size%';
    
        +-------------------------+---------+
    
        | Variable_name           | Value   |
    
        +-------------------------+---------+
    
        | sort_buffer_size        | 262144  |
    
        +-------------------------+---------+
    
        mysql> select 262144 / 1024;
    
        +---------------+
    
        | 262144 / 1024 |
    
        +---------------+
    
        |      256.0000 |
    
        +---------------+
    
    > 注: sort\_Buffer\_Size 并不是越大越好，由于是connection级的参数，过大的设置+高并发可能会耗尽系统内存资源。
 *  Sort\_Buffer + 临时文件
    
    如果加载的记录字段总长度（可能是全字段也可能是 rowid排序的字段）小于 sort\_buffer\_size 便使用 sort\_buffer 排序；如果超过则使用 sort\_buffer + 临时文件进行排序。
    
    临时文件种类：
    
    临时表种类由参数 tmp\_table\_size 与临时表大小决定，如果内存临时表大小超过 tmp\_table\_size ，那么就会转成磁盘临时表。因为磁盘临时表在磁盘上，所以使用内存临时表的效率是大于磁盘临时表的。

**2) 按执行方式划分**

执行方式是由 `max_length_for_sort_data` 参数与用于排序的单条记录字段长度决定的，如果用于排序的单条记录字段长度 `<= max_length_for_sort_data` ，就使用全字段排序；反之则使用 rowid 排序。

xxxxxxxxxx
    mysql> show variables like 'max_length_for_sort_data';
    +--------------------------+-------+
    | Variable_name            | Value |
    +--------------------------+-------+
    | max_length_for_sort_data | 1024  |
    +--------------------------+-------+

**2.1) 全字段排序**

全字段排序就是将查询的所有字段全部加载进来进行排序。

优点：查询快，执行过程简单 缺点：需要的空间大。

xxxxxxxxxx
    select name,age,add from user where addr = '北京' order by name limit 1000; -- addr有索引

![在这里插入图片描述][918bcb20b8654e1eb9158c5b72ec06fc.png]

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

1.  初始化 sort\_buffer，确定放入 name、age、addr 这3个字段。
2.  从索引 addr 中找到第一个满足 addr=’北京’ 的主键ID（ID\_x）。
3.  到主键索引中找到 ID\_x，取出整行，取 name、addr、age 3个字段的值，存入 sort\_buffer。
4.  从索引 addr 取下一个记录的主键ID。
5.  重复3、4，直到 addr 值不满足条件。
6.  对 sort\_buffer 中的数据按照 name 做快速排序。
7.  把排序结果中的前1000行返回给客户端。

**2.2) rowid排序**

rowid 排序相对于全字段排序，不会把所有字段都放入sort\_buffer。所以在sort buffer中进行排序之后还得回表查询。

缺点：会产生更多次数的回表查询，查询可能会慢一些。

优点：所需的空间更小

xxxxxxxxxx
    select name,age,add 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，就需要回表了。

![在这里插入图片描述][9bd0cf6511164489beb339535dab1773.png]

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

1.  初始化 sort\_buffer，确定放入2个字段，name 和 id。
2.  从索引 addr 中找到第一个满足addr=’北京’的主键ID（ID\_x）。
3.  到主键索引中取出整行，把 name、id 这2个字段放入 sort\_buffer。
4.  从索引 addr 取下一个记录的主键ID。
5.  重复3、4，直到addr值不满足条件。
6.  对 sort\_buffer 中的数据按照 name 做快速排序。
7.  取排序结果中的前1000行，并按照 id 的值到原表中取出 name、age、addr 3个字段的值返回给客户端。

**总结**

*  如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort\_buffer中， 这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。
 *  MySQL 的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

### 2.3 排序优化 ###

**添加索引**

*  为 `employee` 表 创建索引
    
        xxxxxxxxxx
    
        -- 联合索引
    
        ALTER TABLE employee ADD INDEX idx_name_age(NAME,age);
    
        -- 为薪资字段添加索引
    
        ALTER TABLE employee ADD INDEX idx_salary(salary);
 *  查看 `employee` 表的索引情况
    
        xxxxxxxxxx
    
        SHOW INDEX FROM employee;

![在这里插入图片描述][33b3d13f2d1d4bd08a2544dfbc539016.png]

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

*  查询 `name, age` 两个字段, 并使用 `name` 与 `age` 行排序
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name,e.age;

![在这里插入图片描述][b6a2b25fb0294146a1191da1ba61d4c1.png]

**场景2: 排序字段在多个索引中,无法使用索引排序**

*  查询 `name , salary` 字段, 并使用 `name` 与 `salary` 排序
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.name, e.salary FROM employee e ORDER BY e.name,e.salary;

![在这里插入图片描述][63a35da8d1244cbba37f71225d34a902.png]

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

*  查询 `id , name` , 使用 `name` 排序
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.id, e.name FROM employee e ORDER BY e.name;

![在这里插入图片描述][eaea13d89bf4451a8d548e15207967db.png]

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

*  查询 `dep_id` ,使用 `name` 进行排序
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.dep_id FROM employee e ORDER BY e.name;

![在这里插入图片描述][c7738a1858784d09984d7a7b690bd4c7.png]

**场景5: 排序字段顺序与索引列顺序不一致,无法利用索引排序**

*  使用联合索引时, ORDER BY子句也要求, 排序字段顺序和联合索引列顺序匹配。
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.age,e.name;

![在这里插入图片描述][ea35dcc431494cd09e8668f640ceeedd.png]

**场景6: where 条件是 范围查询时, 会使order by 索引 失效**

*  比如 添加一个条件 : `age > 18` ,然后再根据 `age` 排序.
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age > 10 ORDER BY e.age;

![在这里插入图片描述][fb9154a3b0e74c6497860710af4b27d2.png]

*  **注意: ORDERBY子句不要求必须索引中第一列,没有仍然可以利用索引排序。但是有个前提条件，只有在等值过滤时才可以，范围查询时不**
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.name, e.age FROM employee e WHERE e.age = 18 ORDER BY e.age;

![在这里插入图片描述][f6cc9eecaedc43879f287b611d00b581.png]

**场景7: 升降序不一致,无法利用索引排序**

*  ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。
    
        xxxxxxxxxx
    
        -- 升序
    
        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;

![在这里插入图片描述][f72947ff0f944bf1b6235bf21cd320b8.png]

*  name字段升序,age字段降序,索引失效
    
        xxxxxxxxxx
    
        EXPLAIN SELECT e.name, e.age FROM employee e ORDER BY e.name, e.age DESC;

![在这里插入图片描述][6ab43bc892684f2082733ba503ce02f4.png]

## 3 索引单表优化案例 ##

### 3.1. 建表 ###

*  **创建表 插入数据**
 *  下面是一张`用户通讯表`的表结构信息,这张表来源于真实企业的实际项目中,有接近500万条数据.
    
        xxxxxxxxxx
    
        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)
    
        );
    
        --  数据：课后资料 sql脚本中（测试前需删除表全部索引）

### 3.2. 单表索引分析 ###

### 3.1.1 需求 ###

*  查询所有名字中包含李的用户姓名和手机号,并根据user\_id字段排序

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

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

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

![在这里插入图片描述][2ebd3e3339984ada856c08ef06e2cce2.png]

*  **结论**：很显然type是ALL，即最坏情况。Extra里还出现Using filesort（文件内排序,未使用到索引），也是最坏情况，所以优化是必须的。

### 3.1.2 优化 ###

1.  首先添加联合索引, 该联合索引包含所有要查询的字段,使其成为覆盖索引,一并解决like模糊查询时索引失效问题
    
        xxxxxxxxxx
    
        -- 添加联合索引
    
        ALTER TABLE user_contacts ADD INDEX idx_nmu(NAME,mobile,user_id);
2.  进行分析
    
        xxxxxxxxxx
    
        EXPLAIN SELECT NAME, mobile FROM  user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;
3.  结果: type的类型提升到了index, 但是 Using filesort 还有.

![在这里插入图片描述][d2071ec308b547f8b009ecef4f90d748.png]

**分析结果显示: type连接类型提升到了index级别,通过索引就获取到了全部数据,但是Extra字段中还是存在 Using filesort.**

1.  继续优化: 根根据最佳左前缀法则,之后最左侧列是有序的, 在创建联合索引时,正确的顺序应该是: **user\_id,NAME,mobile**
    
        xxxxxxxxxx
    
        -- 删除索引
    
        DROP INDEX idx_nmu ON user_contacts
    
        -- 添加重新排序后的索引
    
        ALTER TABLE user_contacts ADD INDEX idx_unm(user_id,NAME,mobile);
2.  执行查询,发现type=index , Using filesort没有了.
    
        xxxxxxxxxx
    
        EXPLAIN SELECT NAME, mobile FROM  user_contacts WHERE NAME LIKE '%李%' ORDER BY user_id;

![在这里插入图片描述][61904763ee8442f2b87246a56e7eff46.png]

**需求二:**

*  统计手机号是135、136、186、187开头的用户数量.
    
        xxxxxxxxxx
    
        EXPLAIN  SELECT COUNT(*) FROM user_contacts
    
        WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';
 *  通过explain命令 查看SQL查询优化信息

![在这里插入图片描述][201799ea5ef24a95847c22bbb467b13f.png]

`type=index` : 用到了索引,但是进行了索引全表扫描

`key=idx_unm`: 使用到了联合索引,但是效果并不是很好

`Extra=Using where; Using index`: 查询的列被索引覆盖了,但是无法通过该索引直接获取数据.

综合上面的执行计划给出的信息,需要进行优化.

**优化**

1.  经过上面的分析,发现联合索引没有发挥作用,所以尝试对 mobile字段单独建立索引
    
        xxxxxxxxxx
    
        ALTER TABLE user_contacts ADD INDEX idx_m(mobile);
2.  再次执行,得到下面的分析结果
    
        xxxxxxxxxx
    
        EXPLAIN  SELECT COUNT(*) FROM user_contacts
    
        WHERE mobile LIKE '135%' OR mobile LIKE '136%' OR mobile LIKE '186%' OR mobile LIKE '187%';

![在这里插入图片描述][48db17ff78c5406ab44cf3503582c4ab.png]

`type=range`: 使用了索引进行范围查询,常见于使用>，>=，<，<=，BETWEEN，IN() 或者 like 等运算符的查询中。

`key=idx_m`: mysql选择了我们为mobile字段创建的索引,进行数据检索

`rows=1575026`: 为获取所需数据而进行扫描的行数,比之前减少了近三分之一

**count(\*) 和 count(1)和count(列名)区别**

进行统计操作时,count中的统计条件可以三种选择:

xxxxxxxxxx
    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%';

xxxxxxxxxx
    执行效果:
      count(*) 包括了所有的列,在统计时 不会忽略列值为null的数据.
      count(1) 用1表示代码行,在统计时,不会忽略列值为null的数据.
        count(列名)在统计时,会忽略列值为空的数据,就是说某个字段的值为null时不统计.
    执行效率:
      列名为主键, count(列名)会比count(1)快
        列名为不是主键, count(1)会比count(列名)快
        如果表没有主键,count(1)会比count(*)快
        如果表只有一个字段,则count(*) 最优.

**需求三:**

*  查询2017-2-16日,新增的用户联系人信息. 查询字段: name , mobile
    
        xxxxxxxxxx
    
        EXPLAIN SELECT NAME,mobile FROM user_contacts  WHERE DATE_FORMAT(create_date,'%Y-%m-%d')='2017-02-16';

![在这里插入图片描述][a1c17c55a3164560b29693af1f06a2f4.png]

**优化:**

*  explain分析的结果显示 `type=ALL` : 进行了全表扫描,需要进行优化,为create\_date字段添加索引.
    
        xxxxxxxxxx
    
        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')='2017-02-16';

![在这里插入图片描述][d6d86200e04e4d89a2d8f2db699b6583.png]

添加索引后,发现并没有使用到索引 `key=null`

*  分析原因: create\_date字段是datetime类型 ,转换为日期再匹配,需要查询出所有行进行过滤, 所以导致索引失效.

**继续优化:**

*  改为使用 between … and … ,使索引生效
    
        xxxxxxxxxx
    
        EXPLAIN SELECT NAME,mobile FROM user_contacts  WHERE create_date
    
        BETWEEN '2017-02-16 00:00:00' AND '2017-02-16 23:59:59';

![在这里插入图片描述][641b923654bf4740b4662c25dbf4f5f3.png]

`type=range` : 使用了索引进行范围查询

`Extra=Using index condition; Using MRR` :Using index condition 表示使用了部分索引, MRR表示InnoDB存储引擎 通过把「随机磁盘读」，转化为「顺序磁盘读」,从而提高了索引查询的性能.

**需求四:**

*  获取用户通讯录表第10万条数据开始后的100条数据.
    
        xxxxxxxxxx
    
        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;

![在这里插入图片描述][0e83d88a633a43fea842fe174a5d0993.png]

*  LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：
    
     *  第一个参数指定第一个返回记录行的偏移量，注意从0开始()
     *  第二个参数指定返回记录行的最大数目
     *  如果只给定一个参数：它表示返回最大的记录行数目
     *  初始记录行的偏移量是 0(而不是 1)
 *  优化1: 通过索引进行分页
    
    直接进行limit操作 会产生全表扫描,速度很慢. Limit限制的是从结果集的M位置处取出N条输出,其余抛弃.
    
    假设ID是连续递增的,我们根据查询的页数和查询的记录数可以算出查询的id的范围，然后配合 limit使用
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM user_contacts WHERE id  >= 100001 LIMIT 100;

![在这里插入图片描述][819c73596f7247e58f352b5a931af864.png]

*  type类型提升到了 range级别
 *  优化2: 使用子查询优化
    
        xxxxxxxxxx
    
        -- 首先定位偏移位置的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;

![在这里插入图片描述][53bf04bd00544fcbb35545edf6fcd444.png]

## 4 索引多表优化案例 ##

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

xxxxxxxxxx
    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`

xxxxxxxxxx
    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`

xxxxxxxxxx
    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`),
      UNIQUE KEY `ind_loan_nbr` (`loan_nbr`) USING BTREE,
    ) ;

**需求一:**

*  查询所有认证用户的手机号以及认证用户的紧急联系人的姓名与手机号信息
    
        xxxxxxxxxx
    
        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;

![在这里插入图片描述][5347a192ab2049b88e4ea0dc93729be7.png]

*  type 类型都是ALL, 使用了全表扫描
 *  优化: 为`mob_autht` 表的 user\_id字段 添加索引
    
        xxxxxxxxxx
    
        alter table mob_autht add index idx_user_id(user_id);

![在这里插入图片描述][5fabb61f648747abb60f49a6cc0ae451.png]

*  根据小结果及驱动大结果集的原则, `mob_autht` 是驱动表,驱动表即使建立索引也不会生效.
 *  一般情况下: 左外连接左表是驱动表,右外连接右表就是驱动表.
 *  explain分析结果的第一行的表,就是驱动表
 *  继续优化: 为`ugncy_cntct_psn`表的 user\_id字段 添加索引
    
        xxxxxxxxxx
    
        ALTER TABLE ugncy_cntct_psn ADD INDEX idx_userid(user_id);

![在这里插入图片描述][db05d9753a8e43ce8890d2280b6252d9.png]

*  `mob_autht` 的type类型为ALL, `ugncy_cntct_psn`的type类型是ref

**需求二:**

*  获取所有智能审核的用户手机号和申请额度、申请时间、审核额度
    
        xxxxxxxxxx
    
        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';

![在这里插入图片描述][7f921c00f42242a5b7e039cde3b9642d.png]

*  优化分析
    
     *  查询 `loan_apply`表,使用的条件字段为 `audit_mod_cde` ,因为该字段没有添加索引,导致 `type=ALL` 发生全表扫描,
     *  为`audit_mod_cde` 字段添加索引,来提高查询效率.
        
            xxxxxxxxxx
        
            ALTER TABLE loan_apply ADD INDEX idx_amc(audit_mod_cde);

![在这里插入图片描述][17842723319c4adc8008e95e3d4bcdbd.png]

添加索引后type的类型确实提升了,但是需要注意的扫描的行还是很高,并且 Extra字段的值为 `Using where` 表示: 通过索引访问时,需要再回表访问所需的数据.
    
    > 注意: 如果执行计划中显示走了索引，但是rows值很高，extra显示为using where，那么执行效果就不会很好。因为索引访问的成本主要在回表上.

*  继续优化:
    
     *  `audit_mod_cde` 字段的含义是审核模式,只有两个值: 1 人工 2 智能 ,所以在根据该字段进行查询时,会有大量的相同数据.
     *  比如: 统计一下 `audit_mod_cde = '2'` 的数据总条数,查询结果是9万多条,该表的总数接近11万条,查询出的数据行超过了表的总记录数的30%, 这时就不建议添加索引 ( 比如有1000万的数据，就算平均分后结果集也有500万条,结果集还是太大，查询效率依然不高 ).
        
            xxxxxxxxxx
        
            SELECT COUNT(*) FROM loan_apply; -- 109181条
        
            SELECT COUNT(*) FROM loan_apply la WHERE la.audit_mod_cde = '2' ; -- 91630条
     *  **总结: 唯一性太差的字段不需要创建索引,即便用于where条件.**
 *  继续优化:
    
     *  如果一定要根据状态字段进行查询,我们可以根据业务需求 添加一个日期条件,比如获取某一时间段的数据,然后再区分状态字段.
        
            xxxxxxxxxx
        
            -- 获取2017年 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 '2017-01-01 00:00:00'
        
            AND  '2017-01-05 23:59:59' AND la.audit_mod_cde = '2';

![在这里插入图片描述][6ea38e53b2684e0a810f7f86ae58e8c7.png]

`extra = Using index condition;` : 只有一部分索引生效
    
    `MRR` 算法: 通过范围扫描将数据存入 `read_rnd_buffer_size` ，然后对其按照 Primary Key（RowID）排序，最后使用排序好的数据进行顺序回表，因为 InnoDB 中叶子节点数据是按照 Primary Key（RowID）进行排列的，这样就转换随机IO为顺序IO了，从而减小磁盘的随机访问.

[8e397dca38d649ae81b3dd6a12612c23.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/0582e4f8f88e4d6dac76bfa29e41cb32.png
[c37c2c2ecc65488c99ef1b3c57c4cd54.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/885f6ce26bfc473a983267558e253d71.png
[613713133a01468e898c9eeeb04286bd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/29c7ed4919f74d87baa8e1e108180356.png
[401882e8e73f4a7ea6e1d2abef2a2e4f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/68ce94f954ed4ad28cda85b53fc76590.png
[4bf0845eb33143bfbc508f41eb31b52c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f49094023efc4eca8f97042303483acc.png
[e18a62a09ccc43b4b454f9ebc1f142c6.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/5f352a1f27d0404b8e5b178916da9d9d.png
[0049fd4b1d4a4a8faf672d65fde0a347.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/88cea9b639894f74926defc9ca51fa08.png
[e83d7de180f94f32ab458219ce9cbc5d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/f25026304e894b1985af95f6c4c0782c.png
[918bcb20b8654e1eb9158c5b72ec06fc.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/c7778aeb589c4f5faffd72f911f39a54.png
[9bd0cf6511164489beb339535dab1773.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b82caccd58f541c7a605ea2a89b73340.png
[33b3d13f2d1d4bd08a2544dfbc539016.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/c6dda988e461458fba371ee2248acba3.png
[b6a2b25fb0294146a1191da1ba61d4c1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/fae35a333ba54a368ba7da88c61aa583.png
[63a35da8d1244cbba37f71225d34a902.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/71b3be5c6cac41629e0a69082cdb8114.png
[eaea13d89bf4451a8d548e15207967db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/8e71eee55a734a2fa5eac4cb0efbc212.png
[c7738a1858784d09984d7a7b690bd4c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/620f62afad974a9dab59cba118026c69.png
[ea35dcc431494cd09e8668f640ceeedd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b69db8af4ffb4802af973b28de8684c4.png
[fb9154a3b0e74c6497860710af4b27d2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/c16fba6c51e841c9b5a26c9a014f90e6.png
[f6cc9eecaedc43879f287b611d00b581.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/a8cd58d4f41046b6ab7a1f0cbbf52899.png
[f72947ff0f944bf1b6235bf21cd320b8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/e6a5956da476453c9a58ce36b79d0b73.png
[6ab43bc892684f2082733ba503ce02f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/4ddf3bb652ec4661b44994539605b713.png
[2ebd3e3339984ada856c08ef06e2cce2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/c94310b58a424f17a1bef7075ce8a35f.png
[d2071ec308b547f8b009ecef4f90d748.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/932fb0d5ad7f452d91039877825b7ec8.png
[61904763ee8442f2b87246a56e7eff46.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/aeb0ebbc96884715a047e0b3b0adc5b9.png
[201799ea5ef24a95847c22bbb467b13f.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/d05bbe31461f4d0fa0d2ddef78397434.png
[48db17ff78c5406ab44cf3503582c4ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/7b20cd76e59c48c3a25827b764ad9c44.png
[a1c17c55a3164560b29693af1f06a2f4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/ab56112cabbc415daad5f7c83d423bc1.png
[d6d86200e04e4d89a2d8f2db699b6583.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/6e63783296e64762bdda00773f3892e3.png
[641b923654bf4740b4662c25dbf4f5f3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/21bc6286952b4581adec2c379844352a.png
[0e83d88a633a43fea842fe174a5d0993.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/9d8a3c4467004183aa904a50e8ba2a5f.png
[819c73596f7247e58f352b5a931af864.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/a03494a3dea14b6c9c3460224b4b052a.png
[53bf04bd00544fcbb35545edf6fcd444.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/301c4c1a75a648c49a2da203a9d501b8.png
[5347a192ab2049b88e4ea0dc93729be7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/009c841ed8744a4099c8a2ec908e2a72.png
[5fabb61f648747abb60f49a6cc0ae451.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/56e542fa34e84da28ac2dc4aa1e29441.png
[db05d9753a8e43ce8890d2280b6252d9.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/8323b2688ae84dc99235a26a3d8854c2.png
[7f921c00f42242a5b7e039cde3b9642d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/a3cce19744f247d8a733d37ee54a3512.png
[17842723319c4adc8008e95e3d4bcdbd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/8310a87a9b1848c493c80d949fbf239b.png
[6ea38e53b2684e0a810f7f86ae58e8c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/d2dc8e7de56642ef9ffffefe68d9c347.png