基础安装

yum -y install epel-release

yum -y install iptables-services

# 关闭selinux

sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

setenforce 0

# 关闭firewalld

systemctl stop firewalld

systemctl disable firewalld

在CentOS 7上编译内核支持IPSec，您可以按照以下步骤操作：

1.下载内核源代码：

```bash

sudo yum groupinstall "Development Tools" -y

sudo yum install openssl-devel -y

sudo yum install kernel-devel -y

uname -r

3.10.0-1160.62.1.el7.x86_64

wget https://cdn.kernel.org/pub/linux/kernel/v3.x/linux-3.10.tar.gz

tar -xzvf linux-3.10.tar.gz

cd linux-3.10

```

2.配置内核选项：

```bash

make menuconfig

```

在menuconfig中，您需要找到以下选项并启用它们：

- Networking support -> Networking options -> TCP/IP networking -> IPsec support

- IPv6: IPsec tunnel support

- Cryptographic API -> AES cipher algorithms

- Cryptographic API -> SHA1 digest algorithm

- Cryptographic API -> HMAC support

保存配置并退出。

3.编译内核：

```bash

make

```

4.安装新内核：

```bash

sudo make modules_install install

```

5.重新启动系统并选择新编译的内核。

现在，您的CentOS 7系统已经编译支持IPSec的内核。您可以配置和使用IPSec来加密和保护网络通信。

Strongswan是一个开源的IPsec实现，CentOS 7中安装Strongswan 5.9.13版本可以按照以下步骤进行：

1. 首先，确认系统中已经安装了所需的依赖包：

```

sudo yum install epel-release

sudo yum install gcc make perl openssl-devel libcurl-devel wget

```

2. 下载Strongswan 5.9.13版本的源码包：

```

wget https://download.strongswan.org/strongswan-5.9.13.tar.gz

```

3. 解压源码包并进入解压后的目录：

```

tar -zxvf strongswan-5.9.13.tar.gz

cd strongswan-5.9.13

```

4. 编译和安装Strongswan：

```

./configure --prefix=/usr/strongswan/ --sysconfdir=/etc/strongswan/ --enable-eap-dynamic --enable-eap-identity --enable-eap-md5 --enable-eap-mschapv2 --enable-eap-tls --enable-eap-ttls --enable-eap-tnc --enable-eap-aka --enable-eap-aka-3gpp2 --enable-eap-sim --enable-eap-sim-file --enable-eap-simaka-pseudonym --enable-eap-simaka-reauth --enable-eap-simaka-pseudonym --enable-eap-simaka-reauth --enable-eap-peap --enable-eap-tls --enable-eap-tnc --enable-eap-dynamic --enable-tls --enable-xauth-eap --enable-certexpire --enable-radattr --enable-gcrypt

make && make install

sudo make install

```

5. 配置Strongswan的IPsec：

```

sudo cp -r /usr/strongswan/etc/ipsec.conf.sample /etc/strongswan/ipsec.conf

sudo cp -r /usr/strongswan/etc/ipsec.secrets.sample /etc/strongswan/ipsec.secrets

sudo cp -r /usr/strongswan/etc/strongswan.conf.sample /etc/strongswan/strongswan.conf

```

6. 启动Strongswan服务：

```

sudo systemctl start strongswan

```

至此，Strongswan 5.9.13版本已经成功安装并启动在CentOS 7系统中。您可以根据需要进一步配置和使用Strongswan的功能。

Iptables设置

```

# Generated by iptables-save v1.4.21 on Thu Feb 29 15:26:18 2024

*mangle

:PREROUTING ACCEPT [8351:4045050]

:INPUT ACCEPT [8351:4045050]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [10452:2403987]

:POSTROUTING ACCEPT [10452:2403987]

-A FORWARD -m policy --pol ipsec --dir in  -p tcp -m tcp --tcp-flags SYN,RST SYN -m tcpmss --mss 1361:1536 -j TCPMSS --set-mss 1360

-A FORWARD -m policy --pol ipsec --dir out -p tcp -m tcp --tcp-flags SYN,RST SYN -m tcpmss --mss 1361:1536 -j TCPMSS --set-mss 1360

COMMIT

# Completed on Thu Feb 29 15:26:18 2024

# Generated by iptables-save v1.4.21 on Thu Feb 29 15:26:18 2024

*nat

:PREROUTING ACCEPT [4:168]

:INPUT ACCEPT [0:0]

:OUTPUT ACCEPT [11:664]

:POSTROUTING ACCEPT [11:664]

-A POSTROUTING -s 192.168.88.0/24 -o ens3 -m policy --dir out --pol ipsec -j ACCEPT

-A POSTROUTING -s 192.168.88.0/24 -o ens3 -j MASQUERADE

-I POSTROUTING -m policy --pol ipsec --dir out -j ACCEPT

COMMIT

# Completed on Thu Feb 29 15:26:18 2024

# Generated by iptables-save v1.4.21 on Thu Feb 29 15:26:18 2024

*filter

:INPUT ACCEPT [0:0]

:FORWARD ACCEPT [0:0]

:OUTPUT ACCEPT [379:79472]

-A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT

-A INPUT -p icmp -j ACCEPT

-A INPUT -i lo -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 22 -j ACCEPT

-A INPUT -p tcp -m multiport --dports 843,587,465,25 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 21 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 53 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 25 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 110 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 80 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 139 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 443 -j ACCEPT

-A INPUT -p tcp -m state --state NEW -m tcp --dport 445 -j ACCEPT

-A INPUT -p udp -m udp --dport 53 -m conntrack --ctstate NEW -j ACCEPT

-A INPUT -p tcp -m tcp --dport 22 -m conntrack --ctstate NEW -j ACCEPT

-A INPUT -p tcp -m tcp --dport 1723 -m conntrack --ctstate NEW -j ACCEPT

-A INPUT -p tcp -m tcp --dport 47 -m conntrack --ctstate NEW -j ACCEPT

-A INPUT -p udp -m udp --dport 500 -j ACCEPT

-A INPUT -p udp -m udp --dport 4500 -j ACCEPT

-A INPUT -j REJECT --reject-with icmp-host-prohibited

#-A FORWARD -s 192.168.88.0/24 -j ACCEPT

-A FORWARD -i ens3 -j ACCEPT

-A FORWARD -j REJECT --reject-with icmp-host-prohibited

COMMIT

# Completed on Thu Feb 29 15:26:18 2024

```

创建证书过程中的参数说明

--ca        创建ca

--lifetime  证书有效期,默认天

--type      类型

--size      长度

--dn        提取 X.509 证书的主题 DN

--gen       生成一个新的私钥

--issue     使用 CA 证书和密钥颁发证书

--pub       从私钥/证书中提取公钥

--self      创建自签名证书

#######

--cakey     CA的秘钥    

--cacert    CA的证书

--san       包含在证书中ubjectAltName的扩展

--flag      添加extendedKeyUsage标志

--outform   生成的证书编码

使用strongswan创需要的证书文件（文件名自己定义就好）

# 先创建一个临时目录，放证书文件

mkdir cert && cd cert

# 创建ca秘钥

strongswan pki --gen --type rsa --size 4096 --outform pem > ca.haose888.net-key.pem

# 创建ca证书（"C=,O=,CN="，这里面内容自定义就好）

strongswan pki --self --ca --lifetime 3650 --in ca.haose888.net-key.pem --type rsa --dn "C=CN,O=haose888.net,CN=haose888.net" --outform pem > ca.haose888.net.cer

# 创建服务端秘钥

strongswan pki --gen --type rsa --size 4096 --outform pem > server.haose888.net-key.pem

# 创建服务端公钥

strongswan pki --pub --in server.haose888.net-key.pem --outform pem > server.haose888.net-pub.pem

# 创建服务端证书(注意：这里--dn参数内容要和上面的对应，--san 要修改为你的服务器域名或者公网ip，我这里使用域名)

strongswan pki --pub --in server.haose888.net-key.pem | strongswan pki \

--issue \

--lifetime 3650 \

--cakey ca.haose888.net-key.pem \

--cacert ca.haose888.net.cer \

--dn "C=CN,O=haose888.net,CN=haose888.net" \

--san="haose888.net" \

--flag serverAuth \

--flag ikeIntermediate \

--outform pem > server.haose888.net-cert.pem

复制生成的证书文件到对应的目录中

cp ca.haose888.net.cer /etc/strongswan/ipsec.d/cacerts/

cp ca.haose888.net-key.pem /etc/strongswan/ipsec.d/private/

cp server.haose888.net-key.pem /etc/strongswan/ipsec.d/private/

cp server.haose888.net-cert.pem /etc/strongswan/ipsec.d/certs/

cp server.haose888.net-pub.pem /etc/strongswan/ipsec.d/certs/

修改配置/etc/strongswan/ipsec.conf文件，经测试win10和ios系统都可以正常连接使用

配置中的文件名要和你创建的一致
协商协议可自定义
分配的客户端的虚拟ip网段可以自定义
dns看情况自定义）

config setup

    # 是否严格执行证书吊销规则

    #strictcrlpolicy=yes

    # 如果同一个用户在不同的设备上重复登录，yes 断开旧连接，创建新连接；no 保持旧连接，并发送通知；never 同 no，但不发送通知。

    uniqueids = no

######################

#    公用配置        #

######################

conn %default

    # 是否启动压缩

    compress = yes

    # 数据传输协议加密算法列表,对于IKEv2，可以在包含相同类型的多个算法（这个我复制别人的）

    ike = aes256-sha256-modp1024,3des-sha1-modp1024,aes256-sha1-modp1024!

    # 断开连接的操作，hold表示保持到重连直到超时，clear表示清除

    dpdaction = clear

    # 断开后重新连接时长

    dpddelay = 30s

    # 断开连接后超时时长，只对IKEv1有用

    dpdtimeout = 60s

    # 空闲时长，吵过后断开连接

    inactivity = 300s

    # 指定服务端与客户端的 DNS，多个用“,”分隔（看你服务器在哪，dns就修改为对应的）

    leftdns = 8.8.8.8

    rightdns = 8.8.8.8

    # 你服务端ip

    left = %any

    # 客户端ip

    right = %any

conn IKE-BASE

    # 服务器端根证书DN名称

    leftca = "C=CN, O=haose888.net, CN= haose888.net"

    # 是否发送服务器证书到客户端

    leftsendcert = always

    # 客户端不发送证书

    rightsendcert = never

    # 服务器端证书

    leftcert = server.haose888.net-cert.pem

    # 客户端分配的虚拟IP地址段

    rightsourceip=192.168.88.0/24

###########################################################

#    win10可用 | 苹果手机使用IKEv2模式(需要手机上安装证书)    #

###########################################################

conn IKEv2

    also = IKE-BASE

    # 密钥交换协议加密算法列表，可以包括多个算法和协议

    esp = aes256-sha256,3des-sha1,aes256-sha1!

    # 使用ikev2

    keyexchange = ikev2

    # 服务端ip，可以是%any，表示从本地ip中取

    left = %any

    # 服务器端虚拟ip子网，0.0.0.0/0表示通配

    leftsubnet = 0.0.0.0/0

    # 客户端ip，%any表示任意

    right = %any

    # 服务端校验方式，使用证书

    leftauth=pubkey

    # 客户端认证使用 EAP 扩展认证， EAP(Username/Password)

    rightauth=eap-mschapv2

    # 服务端ID （和上面创建服务端证书时一致(--san指定的内容)，这里使用域名）

    leftid = haose888.net

    # 客户端 id，任意

    rightid = %any

    # 不自动重置密钥

    rekey = no

    # 指定客户端eap id

    eap_identity = %any

    # 当服务启动时, 应该如何处理这个连接项,add 添加到连接表中

    auto = add

    # 开启 IKE 消息分片

    fragmentation=yes

#############################################

#    Android ipsec xuauth psk | IOS ipsec   #

#############################################

conn IPSec-IKEv1-PSK

    also=IKE-BASE

    esp=aes256-sha256_96,3des-sha1,aes256-sha1!

    keyexchange=ikev1

    fragmentation=yes

    leftauth=psk

    rightauth=psk

    rightauth2=xauth

    auto=add

修改/etc/strongswan/ipsec.secrets配置文件，配置认证秘钥和用户密码(这个配置我看别人的)

# ipsec.secrets - strongSwan IPsec secrets file

# 使用证书验证时的服务器端私钥

: RSA server.haose888.net-key.pem

# 预设psk秘钥

: PSK "123456"

# XAUTH 方式, 只适用于 IKEv1

vpnuser : XAUTH "123456"

# EAP 方式,用户和密码

vpnuser : EAP "123456"

开启内核转发

echo "net.ipv4.ip_forward = 1" >> /etc/sysctl.conf

sysctl -p

启动strongswan服务

# 启动（如果参数配置有误，这里有提示的）

systemctl start strongswan-starter.service

# 停止

systemctl stop strongswan-starter.service

# 重启

systemctl restart strongswan-starter.service

# 查看状态

systemctl status strongswan-starter.service

测试连通

ping -c 4 -I ens3 haose888.net

使用安卓连接，使用ipsec xauth psk 方式

我手机自带的ikev2有bug用不了，只能用这个ipsec方式测试；而且还遇到 Android 设备有 MTU/MSS 问题，表现为使用 IPsec/XAuth ("Cisco IPsec") 模式可以连接到 VPN 但是无法打开网站。需要在服务器上加上iptables规则才能解决

# 解决安卓使用ipsec/xauth 方式连接后上不了网（这是看到别人的解决办法，经测试确实可以解决）

iptables -t mangle -A FORWARD -m policy --pol ipsec --dir in -p tcp -m tcp --tcp-flags SYN,RST SYN -m tcpmss --mss 1361:1536 -j TCPMSS --set-mss 1360

iptables -t mangle -A FORWARD -m policy --pol ipsec --dir out -p tcp -m tcp --tcp-flags SYN,RST SYN -m tcpmss --mss 1361:1536 -j TCPMSS --set-mss 1360

# 该文件表示在全局范围内关闭路径MTU探测功能

echo "net.ipv4.ip_no_pmtu_disc = 1">> /etc/sysctl.conf

sysctl –p

使用win10系统连接

需要先把ca证书(ca.haose888.net.cer证书文件)下载下来，安装到系统里面

新建一个vpn连接，选择IKEv2，写上用户和密码

有个注意的地方，下面图中的红框文字的说明

连接到服务器成功，查看出口ip（如果看到ip还是本地默认的，说明上图中的那个没勾选，勾选后看到的就是vpn的网络地址了）

TOP

#!/bin/bash

#
# Author: Alone(hi@anlo.ng)
# Create: certbot certonly --manual --preferred-challenges dns-01 --email mail@domain.com -d laravel.run -d *.laravel.run --server https://acme-v02.api.letsencrypt.org/directory --manual-auth-hook /path/to/certbot-auth-dnspod.sh
# Renew:  certbot renew --manual-public-ip-logging-ok --manual-auth-hook /path/to/certbot-auth-dnspod.sh
#

# https://www.dnspod.cn/console/user/security
API_TOKEN=""

USER_AGENT="AnDNS/1.0.0 (hi@anlo.ng)"
DOMAIN=$(expr match "$CERTBOT_DOMAIN" '.*\.\(.*\..*\)')
TXHOST=$(expr match "$CERTBOT_DOMAIN" '\(.*\)\..*\..*')
[ -z "$DOMAIN" ] && DOMAIN="$CERTBOT_DOMAIN"
[ -z "$TXHOST" ] || TXHOST="_acme-challenge.$TXHOST"
[ -z "$TXHOST" ] && TXHOST="_acme-challenge"

if [ -z "$API_TOKEN" ]; then
    [ -f $HOME/.dnspod_token_$DOMAIN ] && API_TOKEN=$(cat $HOME/.dnspod_token_$DOMAIN)
fi

if [ -z "$API_TOKEN" ]; then
    [ -f /etc/dnspod_token_$DOMAIN ] && API_TOKEN=$(cat /etc/dnspod_token_$DOMAIN)
fi

if [ -z "$API_TOKEN" ]; then
    [ -f $HOME/.dnspod_token ] && API_TOKEN=$(cat $HOME/.dnspod_token)
fi

if [ -z "$API_TOKEN" ]; then
    [ -f /etc/dnspod_token ] && API_TOKEN=$(cat /etc/dnspod_token)
fi

if [ -z "$API_TOKEN" ]; then
    API_TOKEN="$DNSPOD_TOKEN"
fi

PARAMS="user_token=$API_TOKEN&format=json"

echo "\
CERTBOT_DOMAIN: $CERTBOT_DOMAIN
DOMAIN:         $DOMAIN
TXHOST:         $TXHOST
VALIDATION:     $CERTBOT_VALIDATION"
echo "PARAMS:         $PARAMS"

if [ -f /tmp/CERTBOT_$CERTBOT_DOMAIN/VALIDATION ]; then
    VALIDATION_PRE=$(cat /tmp/CERTBOT_$CERTBOT_DOMAIN/VALIDATION)
    if [ "$CERTBOT_VALIDATION" = "$VALIDATION_PRE" ]; then
        echo "Same Validation: $CERTBOT_VALIDATION"
        exit
    fi
fi

RECORDS1=$(curl -s -X POST "https://api.dnspod.com/Record.List" \
    -H "User-Agent: $USER_AGENT" \
    -d "$PARAMS&domain=$DOMAIN&sub_domain=$TXHOST" > t.txt)
    
if [ `jq 'has("records")' t.txt` == true ];then
echo "true"
`jq .records[0].id -r t.txt > records.txt`
else
echo "flase"
`jq .status.message t.txt`
fi

RECORDS=`cat records.txt`

echo "\
RECORDS:        $RECORDS"

sleep 3

if [ -n "$RECORDS" ]; then
    RECORD_ID="$RECORDS"
    RECORD_ID1=$(curl -X POST "https://api.dnspod.com/Record.Modify" \
        -H "User-Agent: $USER_AGENT" \
        -d "$PARAMS&domain=$DOMAIN&record_id=$RECORD_ID&sub_domain=$TXHOST&value=$CERTBOT_VALIDATION&record_type=TXT&record_line=default" > t.txt)
        echo "if1"
`jq .record.id t.txt > records.txt`

RECORD_ID=`cat records.txt`
else
    RECORD_ID1=$(curl -X POST "https://api.dnspod.com/Record.Create" \
        -H "User-Agent: $USER_AGENT" \
        -d "$PARAMS&domain=$DOMAIN&record_id=$RECORD_ID&sub_domain=$TXHOST&value=$CERTBOT_VALIDATION&record_type=TXT&record_line=default"> t.txt)
        echo "if2"
`jq .record.id t.txt > records.txt`
RECORD_ID=`cat records.txt`
fi

echo "\
RECORD_ID:      $RECORD_ID"

# Save info for cleanup
if [ ! -d /tmp/CERTBOT_$CERTBOT_DOMAIN ]; then
    mkdir -m 0700 /tmp/CERTBOT_$CERTBOT_DOMAIN
fi
echo $DOMAIN > /tmp/CERTBOT_$CERTBOT_DOMAIN/DOMAIN
echo $RECORD_ID > /tmp/CERTBOT_$CERTBOT_DOMAIN/RECORD_ID
echo $CERTBOT_VALIDATION > /tmp/CERTBOT_$CERTBOT_DOMAIN/VALIDATION

# Sleep to make sure the change has time to propagate over to DNS
sleep 30

TOP

问题发现

我认为一条很简单的SQL然后跑了很久，明明我已经都建立相应的索引，逻辑也不需要优化。

SELECT a.custid, b.score, b.xcreditscore, b.lrscoreFROM (    SELECT DISTINCT custid    FROM sync.`credit_apply`    WHERE SUBSTR(createtime, 1, 10) >= '2019-12-15'
        AND rejectrule = 'xxxx') a    LEFT JOIN (        SELECT *
        FROM sync.`credit_creditchannel`
    ) b    ON a.custid = b.custid;

查看索引状态：

credit_apply表

mysql> show index from sync.`credit_apply`;+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| credit_apply |          0 | PRIMARY  |            1 | applyId     | A         |     1468496 | NULL     | NULL   |      | BTREE      |         |               || credit_apply |          1 | index2   |            1 | custId      | A         |      666338 | NULL     | NULL   |      | BTREE      |         |               || credit_apply |          1 | index2   |            2 | createTime  | A         |     1518231 | NULL     | NULL   |      | BTREE      |         |               |+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

或者

CREATE TABLE `credit_apply` (
  `applyId` bigint(20) NOT NULL AUTO_INCREMENT,
  `custId` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ruleVersion` int(11) NOT NULL DEFAULT '1',
  `rejectRule` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT 'DP0000',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `extra` text COLLATE utf8mb4_unicode_ci,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `mobile` varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT '',  PRIMARY KEY (`applyId`) USING BTREE,  KEY `index2` (`custId`,`createTime`)) ENGINE=InnoDB AUTO_INCREMENT=1567035 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

sync.`credit_creditchannel`表

mysql> show index from sync.`credit_creditchannel` ;+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table                | Non_unique | Key_name                    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| credit_creditchannel |          0 | PRIMARY                     |            1 | recId       | A         |      450671 | NULL     | NULL   |      | BTREE      |         |               || credit_creditchannel |          1 | nationalId_custid           |            1 | nationalId  | A         |      450770 | NULL     | NULL   |      | BTREE      |         |               || credit_creditchannel |          1 | nationalId_custid           |            2 | custId      | A         |      450770 | NULL     | NULL   | YES  | BTREE      |         |               || credit_creditchannel |          1 | credit_creditchannel_custId |            1 | custId      | A         |      450770 |       10 | NULL   | YES  | BTREE      |         |               |+----------------------+------------+-----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

 或者

CREATE TABLE `credit_creditchannel` (
  `recId` bigint(20) NOT NULL AUTO_INCREMENT,
  `nationalId` varchar(128) NOT NULL DEFAULT '',
  `identityType` varchar(3) NOT NULL DEFAULT '',
  `brief` mediumtext,
  `score` decimal(10,4) NOT NULL DEFAULT '0.0000',
  `npaCode` varchar(128) NOT NULL DEFAULT '',
  `basic` mediumtext,
  `createTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `request` mediumtext,
  `custId` varchar(128) DEFAULT '',
  `xcreditScore` decimal(10,4) DEFAULT '0.0000',
  `queryTime` varchar(24) DEFAULT '',
  `lrScore` decimal(10,4) DEFAULT '0.0000',  PRIMARY KEY (`recId`) USING BTREE,  KEY `nationalId_custid` (`nationalId`,`custId`),  KEY `credit_creditchannel_custId` (`custId`(10))) ENGINE=InnoDB AUTO_INCREMENT=586557 DEFAULT CHARSET=utf8

我们都可以看到相应的索引。以现在简单的sql逻辑理论上走custid这个索引就好了

解释函数explain

mysql> explain SELECT a.custid, b.score, b.xcreditscore, b.lrscore FROM(SELECT DISTINCT custid FROM sync.`credit_apply` WHERE SUBSTR(createtime, 1, 10) >= '2019-12-15' AND rejectrule = 'xxx') aLEFT JOIN (select * from sync.`credit_creditchannel`) bON a.custid = b.custid;+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+| id | select_type | table                | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra                                              |+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+|  1 | PRIMARY     | <derived2>           | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |  158107 |   100.00 | NULL                                               ||  1 | PRIMARY     | credit_creditchannel | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |  450770 |   100.00 | Using where; Using join buffer (Block Nested Loop) ||  2 | DERIVED     | credit_apply         | NULL       | index | index2        | index2 | 518     | NULL | 1581075 |    10.00 | Using where                                        |+----+-------------+----------------------+------------+-------+---------------+--------+---------+------+---------+----------+----------------------------------------------------+3 rows in set (0.06 sec)

如何去看我们的SQL是否走索引？

我们只需要注意一个最重要的type 的信息很明显的提现是否用到索引：

type结果

type结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，得保证查询至少达到range级别，最好能达到ref，否则就可能会出现性能问题。

possible_keys：sql所用到的索引

key：显示MySQL实际决定使用的键（索引）。如果没有选择索引，键是NULL

rows: 显示MySQL认为它执行查询时必须检查的行数。

分析：

我们的credit_creditchannel是ALL，而possible_keys是NULL索引在查询该表的时候并没有用到索引怪不得这么慢！！！！！！！！！

分析和搜索解决办法

换着法的改sql也没用；换着群问大神也没用；各种搜索引擎搜才总算有点思路。 

索引用不上的原因可能是字符集和排序规则不相同。

于是看了了两张表的字符集和两张表这个字段的字符集以及排序规则:

alter database sync default character set utf8mb4;//修改数据库的字符集alter table sync.credit_creditchannel default character set utf8mb4;//修改表的字符集

修改表排序规则

alter table sync.`credit_creditchannel` convert to character set utf8mb4 COLLATE utf8mb4_unicode_ci;

由于数据库中的数据表和表字段的字符集和排序规则不统一，批量修改脚本如下：

1. 修改指定数据库中所有varchar类型的表字段的字符集为ut8mb4，并将排序规则修改为utf8_unicode_ci

SELECT CONCAT('ALTER TABLE `', table_name, '` MODIFY `', column_name, '` ', DATA_TYPE, '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci', CASE 
        WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL'
        ELSE ''
    END, ';')FROM information_schema.COLUMNSWHERE (TABLE_SCHEMA = 'databaseName'
    AND DATA_TYPE = 'varchar'
    AND (CHARACTER_SET_NAME != 'utf8mb4'
        OR COLLATION_NAME != 'utf8mb4_unicode_ci'));

2. 修改指定数据库中所有数据表的字符集为UTF8，并将排序规则修改为utf8_general_ci　

SELECT CONCAT('ALTER TABLE ', table_name, 'CONVERT TO CHARACTER SET  utf8mb4 COLLATE utf8_unicode_ci;')FROM information_schema.TABLESWHERE TABLE_SCHEMA = 'databaseName'

explain 查看是否用到了索引

mysql> explain SELECT a.custid, b.score, b.xcreditscore, b.lrscore FROM(SELECT DISTINCT custid FROM sync.`credit_apply` WHERE SUBSTR(createtime, 1, 10) >= '2019-12-15' AND rejectrule = 'xxx') aLEFT JOIN (select * from sync.`credit_creditchannel`) bON a.custid = b.custid;+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+| id | select_type | table                | partitions | type  | possible_keys               | key                         | key_len | ref      | rows    | filtered | Extra       |+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+|  1 | PRIMARY     | <derived2>           | NULL       | ALL   | NULL                        | NULL                        | NULL    | NULL     |  146864 |   100.00 | NULL        ||  1 | PRIMARY     | credit_creditchannel | NULL       | ref   | credit_creditchannel_custId | credit_creditchannel_custId | 43      | a.custid |       1 |   100.00 | Using where ||  2 | DERIVED     | credit_apply         | NULL       | index | index2                      | index2                      | 518     | NULL     | 1468644 |    10.00 | Using where |+----+-------------+----------------------+------------+-------+-----------------------------+-----------------------------+---------+----------+---------+----------+-------------+

就是这样！！！！

补充大全：

可以看到结果中包含10列信息，分别为

id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

对应的简单描述如下：

• id: select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序===id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行

• select_type: 表示查询的类型。用于区别普通查询、联合查询、子查询等的复杂查询。

• table: 输出结果集的表 显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称

• partitions:匹配的分区

• type:对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。

• possible_keys:表示查询时，可能使用的索引

• key:表示实际使用的索引

• key_len:索引字段的长度

• ref:列与索引的比较

• rows:扫描出的行数(估算的行数)

• filtered:按表条件过滤的行百分比

• Extra:执行情况的描述和说明

挑选一些重要信息详细说明：

• select_type

• SIMPLE 简单的select查询，查询中不包含子查询或者UNION

• PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

• SUBQUERY 在SELECT或WHERE列表中包含了子查询

• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

• UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

• UNION RESULT 从UNION表获取结果的SELECT

• type

• mysql找到数据行的方式，效率排名

• NULL > system > const > eq_ref > ref > range > index > all

***一般来说，得保证查询至少达到range级别，最好能达到ref。

1. 
   

1. system 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

2. const   通过索引一次就找到了，const用于比较primary key 和 unique key，因为只匹配一行数据，所以很快。如果将主键置于where列表中，mysql就能将该查询转换为一个常量

3. eq_ref 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键索引和唯一索引  区别于const eq_ref用于联表查询的情况

4. ref 非唯一索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，他可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

5. range   只检索给定范围的行，使用一个索引来选择行，一般是在where中出现between、<、>、in等查询，范围扫描好于全表扫描，因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引

6. index   Full Index Scan，Index与All区别为index类型只遍历索引树。通常比All快，因为索引文件通常比数据文件小。也就是说，虽然all和index都是读全表，但是index是从索引中读取的，而all是从硬盘读取的

7. ALL Full Table Scan,将遍历全表以找到匹配的行

• possible_keys

指出mysql能使用哪个索引在表中找到记录，查询涉及到的字段若存在索引，则该索引被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示null）
实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）
查询中若使用了覆盖索引（select 后要查询的字段刚好和创建的索引字段完全相同），则该索引仅出现在key列表中 possible_keys为null

• key

key列显示mysql实际决定使用的索引，必然包含在possible_keys中。如果没有选择索引，键是NULL。想要强制使用或者忽视possible_keys列中的索引，在查询时指定FORCE INDEX、USE INDEX或者IGNORE index

• key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

• ref

显示索引的那一列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

• rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好

• extra

包含不适合在其他列中显式但十分重要的额外信息

• 
  

• Using Index:表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

• Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤

• Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by

• Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”

• Using join buffer：表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

• Impossible where：where子句的值总是false，不能用来获取任何元组

• Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行

• No tables used：Query语句中使用from dual 或不含任何from子句

以上两种信息表示mysql无法使用索引

1. 
   

1. using filesort ：表示mysql会对结果使用一个外部索引排序，而不是从表里按索引次序读到相关内容，可能在内存或磁盘上排序。mysql中无法利用索引完成的操作称为文件排序

2. using temporary: 使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

TOP