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

server.properties

• broker.id=0

+ kafka集群是由多个节点组成的，每个节点称为一个broker，中文翻译是代理。每个broker都有一个不同的brokerId，由broker.id指定，是一个不小于0的整数，各brokerId必须不同，但不必连续。如果我们想扩展kafka集群，只需引入新节点，分配一个不同的broker.id即可。
+ 启动kafka集群时，每一个broker都会实例化并启动一个kafkaController，并将该broker的brokerId注册到zooKeeper的相应节点中。集群各broker会根据选举机制选出其中一个broker作为leader，即leader kafkaController。leader kafkaController负责主题的创建与删除、分区和副本的管理等。当leader kafkaController宕机后，其他broker会再次选举出新的leader kafkaController。

• log.dir = /export/data/kafka/

+ broker持久化消息到哪里，数据目录

• log.retention.hours = 168

+ log文件最小存活时间，默认是168h，即7天。相同作用的还有log.retention.minutes、log.retention.ms。retention是保存的意思。
+ 数据存储的最大时间超过这个时间会根据log.cleanup.policy设置的策略处理数据，也就是消费端能够多久去消费数据。
+ log.retention.bytes和log.retention.hours任意一个达到要求，都会执行删除，会被topic创建时的指定参数覆盖。

• log.retention.check.interval.ms

+ 多长时间检查一次是否有log文件要删除。默认是300000ms，即5分钟。

• log.retention.bytes

+ 限制单个分区的log文件的最大值，超过这个值，将删除旧的log，以满足log文件不超过这个值。默认是-1，即不限制。

• log.roll.hours

+ 多少时间会生成一个新的log segment，默认是168h，即7天。相同作用的还有log.roll.ms、segment.ms。

• log.segment.bytes

+ log segment多大之后会生成一个新的log segment，默认是1073741824，即1G。

• log.flflush.interval.messages

+ 指定broker每收到几个消息就把消息从内存刷到硬盘（刷盘）。默认是9223372036854775807 好大。
+ kafka官方不建议使用这个配置，建议使用副本机制和操作系统的后台刷新功能，因为这更高效。这个配置可以根据不同的topic设置不同的值，即在创建topic的时候设置值。
+ 补充说明：

1
2
3
4
5

text复制代码在Linux操作系统中，当我们把数据写入到文件系统之后，数据其实在操作系统的page cache里面，并没有刷到磁盘上去。如果此时操作系统挂了，其实数据就丢了。

1、kafka是多副本的，当你配置了同步复制之后。多个副本的数据都在page cache里面，出现多个副本同时挂掉的概率比1个副本挂掉，概率就小很多了

2、操作系统有后台线程，定期刷盘。如果应用程序每写入1次数据，都调用一次fsync，那性能损耗就很大，所以一般都会在性能和可靠性之间进行权衡。因为对应一个应用来说，虽然应用挂了，只要操作系统不挂，数据就不会丢。

• log.flflush.interval.ms

+ 指定broker每隔多少毫秒就把消息从内存刷到硬盘。默认值同log.flflush.interval.messages一样，9223372036854775807。
+ 同log.flflush.interval.messages一样，kafka官方不建议使用这个配置。

• delete.topic.enable=true

+ 是否允许从物理上删除topic

kafka-eagle概述

在生产环境下，在Kafka集群中，消息数据变化是我们关注的问题，当业务前提不复杂时，我们可以使用Kafka命令提供带有Zookeeper客户端工具的工具，可以轻松完成我们的工作。随着业务的复杂性，增加Group和 Topic，那么我们使用Kafka提供命令工具，已经感到无能为力，那么Kafka监控系统目前尤为重要，我们需要观察 消费者应用的细节。

为了简化开发者和服务工程师维护Kafka集群的工作有一个监控管理工具，叫做 Kafka-eagle。这个管理工具可以很容易地发现分布在集群中的哪些topic分布不均匀，或者是分区在整个集群分布不均匀的的情况。它支持管理多个集群、选择副本、副本重新分配以及创建Topic。同时，这个管理工具也是一个非常好的可以快速浏览这个集群的工具，

搭建安装 kafka-eagle

环境要求:需要安装jdk，启动zk以及kafka的服务

1
2
3
4
5

bash复制代码# 启动Zookeeper
zkServer.sh start

#启动Kafka
nohup ./kafka-server-start.sh /export/servers/kafka/config/server.properties 2>&1 &

修改本机host文件

1
2
3
4
5
6

复制代码192.168.200.20 kafka1
192.168.200.20 kafka2
192.168.200.20 kafka3
192.168.200.11 node1
192.168.200.12 node2
192.168.200.13 node3

搭建步骤:

• 下载kafka-eagle的源码包

+ kafka-eagle官网：[download.kafka-eagle.org/](http://download.kafka-eagle.org/)  
我们可以从官网上面直接下载最新的安装包即可kafka-eagle-bin-1.3.2.tar.gz这个版本即可
+ 代码托管地址：[github.com/smartloli/E…](https://github.com/smartloli/EFAK/tags)

• 上传安装包并解压:

+ 这里我们选择将kafak-eagle安装在第三台
+ 如果要解压的是zip格式，需要先安装命令支持。
    - yum install unzip
    - unzip xxxx.zip

1
2
3
4
5

bash复制代码#将安装包上传至 node01服务器的/export/softwares路径下, 然后解压
cd /export/softwares/
unzip kafka-eagle.zip
cd cd kafka-eagle-web/target/
tar -zxf kafka-eagle-web-2.0.1-bin.tar.gz -C /export/servers

• 准备数据库:

+ kafka-eagle需要使用一个数据库来保存一些元数据信息，我们这里直接使用msyql数据库来保存即可，在node01服务器执行以下命令创建一个mysql数据库即可

1
2

lua复制代码--进入mysql客户端:
create database eagle;

• 修改kafka-eagle配置文件

1
2
3
4
5
6
7
8
9
10
11

ini复制代码cd /export/servers/kafka-eagle-bin-1.3.2/kafka-eagle-web-1.3.2/conf
vim system-config.properties

#内容如下:
kafka.eagle.zk.cluster.alias=cluster1
cluster1.zk.list=node1:2181,node2:2181,node3:2181

kafka.eagle.driver=com.mysql.jdbc.Driver
kafka.eagle.url=jdbc:mysql://10.1.192.208:3306/eagle
kafka.eagle.username=root
kafka.eagle.password=wu7787879

默认情况下MySQL只允许本机连接到MYSQL实例中，所以如果要远程访问，必须开放权限：

1
2
3
4

sql复制代码//修改权限
update user set host = '%' where user ='root'; 
//刷新配置
flflush privileges;

• 配置环境变量
  • kafka-eagle必须配置环境变量，node03服务器执行以下命令来进行配置环境变量

1
2
3
4
5
6
7

bash复制代码vi /etc/profile
#内容如下:
export KE_HOME=/export/servers/kafka-eagle-bin-1.3.2/kafka-eagle-web-1.3.2
export PATH=:$KE_HOME/bin:$PATH

#让修改立即生效，执行
source /etc/profile

• 启动kakfa-eagle

1
2
3

bash复制代码cd kafka-eagle-web-1.3.2/bin
chmod u+x ke.sh
./ke.sh start

• 访问主界面:http://node03:8048/ke/account/signin?/ke/
  • 用户名：admin
  • 密码：123456

本文转载自: 掘金

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

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

作者：汤圆

个人博客：javalover.cc

简介

前面我们介绍了表单登录的入门案例；

本篇介绍下表单退出登录的入门案例，代码基于表单登录的案例进行了修改；

代码地址见文末

目录

1. 退出登录的基本配置
2. 退出登录的相关跳转
3. 退出登录时的缓存更新
4. 退出登录时的处理器

正文

1. 退出登录的基本配置

最基本的配置如下所示：

1
2
3
4
5
6

java复制代码 @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
            // 退出登录 所有用户都可以访问
            .logout().permitAll();
    }

这里默认的退出登录url为/logout，通过在url中访问http://localhost:8090/logout就可以退出登录了。

当然最方便的还是在界面中进行链接跳转，如下所示：

1

html复制代码    <a href="logout">退出</a>

2. 退出登录的相关跳转

logoutSuccessUrl配置：

退出登录跳转成功后的默认界面是根路径，比如http://localhost:8090/；

下面我们可以进行简单的配置，配置成自己指定的界面，如下所示：一般推荐将退出登录成功后跳转的链接设置为登录界面（习惯）

1
2
3
4
5
6
7

java复制代码 @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
            // 退出登录 所有用户都可以访问
            .logout().permitAll() 
            .logoutSuccessUrl("/login");
    }

logoutUrl配置：

退出登录跳转的默认url为/logout，比如http://localhost:8090/logout，如果退出登录成功，就跳转到上面配置的路径；

配置如下所示：

1
2
3
4
5
6
7

java复制代码 @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
            // 退出登录 所有用户都可以访问
            .logout().permitAll() 
            .logoutUrl("/logout");
    }

3. 退出登录时的缓存更新

这里的缓存指的就是session和cookie；

在退出登录之后，需要将session失效处理，并删除对应的cookie；

对应的命令为：invalidateHttpSession() 和 deleteCookies(...name);

配置如下所示：

其中删除的Cookies名称为JSESSIONID，这个就是前后端交互的一个凭证id，是在第一次前端请求后端时，后端返回的id；后续的请求后端会根据JSESSIONID来匹配对应的session

1
2
3
4
5
6
7
8
9

java复制代码 @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
            // 退出登录 所有用户都可以访问
            .logout().permitAll() 
            .logoutUrl("/logout")
            .invalidateHttpSession(true)
            .deleteCookies("JSESSIONID");
    }

4. 退出登录时的处理器

退出成功后，不仅可以设置特定的url，还可以执行一些自定义的操作；

对应的命令为：logoutSuccessHandler

比如我们需要记录退出登录时访问的最后一个界面，那么可以通过如下的代码来实现；

先定义一个处理器：CustomLogoutSuccessHandler.java

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

java复制代码
public class CustomLogoutSuccessHandler extends
        SimpleUrlLogoutSuccessHandler implements LogoutSuccessHandler {

    @Override
    public void onLogoutSuccess(
            HttpServletRequest request,
            HttpServletResponse response,
            Authentication authentication)
            throws IOException, ServletException {

        String refererUrl = request.getHeader("Referer");
        System.out.println("Logout from: " + refererUrl);

        super.onLogoutSuccess(request, response, authentication);
    }
}

然后在配置中注入该处理器，通过方法注入，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

java复制代码public class SecurityConfiguration extends WebSecurityConfigurerAdapter {

    @Bean
    public LogoutSuccessHandler logoutSuccessHandler(){
        return new CustomLogoutSuccessHandler();
    }
     @Override
    protected void configure(HttpSecurity http) throws Exception {
        http
            // 退出登录 所有用户都可以访问
            .logout()
                .permitAll()
                .logoutSuccessUrl("/login")
                .logoutUrl("/logout")
                .logoutSuccessHandler(logoutSuccessHandler());
    }

}

这样我们在退出登录时，就可以看到控制台打印下面的内容：

1

bash复制代码Logout from: http://localhost:8090/home

总结

本篇介绍了退出登录的相关配置和处理；

配置有：

• logoutUrl(): 退出登录链接配置
• logoutSuccessUrl(): 退出登录成功后的跳转链接
• invalidateHttpSession: 失效session
• deleteCookies() : 删除对应cookie，多个cookieName逗号分隔
• LogoutSuccessHandler：退出登录时执行的相关操作

源码地址

本文转载自: 掘金

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

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

一、什么是可靠消息最终一致性事务

可靠消息最终一致性方案是指当事务发起方执行完成本地事务后并发出一条消息，消息消费者一定能够接收消息并处理事务成功，此方案强调的是只要消息发给事务参与方最终事务要达成一致。

涉及的思想是：把一系列事务拆分成本地事务。

基本原理：

• 事务发起者A 执行本地事务
• 事务发起者 A通过 MQ 将需要执行的事务信息发送给事务参与者 B
• 事务参与者 B 接收到消息后执行本地事务

此方案是基于消息中间件来实现的，具体流程如下图所示：

事务发起方将消息发给消息中间件，事务参与方从消息中间件接收消息，事务发起方和消息中间件之间，事务参与方（消息消费方）和消息中间件之间都是通过网络，由于网络通信的不确定性会导致分布式事务问题。

因此，可靠消息最终一致性方案要解决以下几个问题：

• 1、本地事务与消息发送的原子性问题

+ 也就是事务发起方执行本地事务和发送消息这两件事情 ，要么全部成功，要么全部是失败，这必须是原子性操作。
+ 如果先执行本地事务，发送消息失败了怎么办？如果先发送消息，执行本地事务失败了怎么办？如何保证它们原子性呢？

• 2、事务参与方接收消息的可靠性

+ 事务参与方必须能够从消息队列接收到消息，如果接收到消息失败可以重复接收消息

• 3、消息重复消费的问题

+ 由于网络2 的存在，若某个消费节点超时但是消费成功，此时消息中间件会重复投递此消息，就导致消息的重复消费。
+ 因此，消息消费需要保证幂等性。

二、解决方案

针对上述的一系列问题，如何解决它们呢，这里提供两种解决方案。

2.1 本地消息表

本地消息表是由 eBay 提出的，此方案的核心是将分布式事务拆分成本地事务进行处理，然后通过定时任务将消息发送至消息中间件，待确认消息发送给消费方成功再将消息删除。

拿下订单扣减库存为例：

• 步骤 1 和2 执行本地事务，创建订单，然后发送消息到mq，更新订单和发送消息属于同一个事务。
• 步骤 34567 为事务参与方进行执行它的本地事务，如果执行完成则删除消息。
• 步骤 8 则定时扫描本地消息表，看是否有未完成的任务，有则重试，防止消费者消费失败。

优点：

• 从应用设计开发的角度实现了消息数据的可靠性，消息数据的可靠性不依赖于消息中间件，弱化了对 MQ 中间件特性的依赖。

缺点：

• 与具体的业务场景绑定，耦合性强，不可公用。消息数据与业务数据同库，占用业务系统资源。业务系统在使用关系型数据库的情况下，消息服务性能会受到关系型数据库并发性能的局限。
• 需要维护定时任务

2.2 RocketMQ 事务消息方案

1、事务发起方发送prepare消息到MQ

2、消息发送成功后执行本地事务

3、根据本地事务执行结果返回commit或者是rollback

4、如果消息时rollback，MQ将删除该prepare消息不进行下发，如果是commit消息，mq将会把这个消息发送到consumer

5、执行本地事务的过程中，执行端挂掉，或者超时，MQ将会不停的询问其同组的其他producer来获取状态

6、Consumer端的消费成功机制有MQ保证

RocketMQ 消息中间把消息分为两个阶段：Prepared 阶段和 确认阶段。

• Prepared 阶段：

1

复制代码该阶段主要发一个消息到 rocketmq，但该消息只存储在 commitlog 中，但 消息队列 中不可见，也就是消费端（订阅端）无法看到此消息

• commit /rollback 阶段（确认阶段）

1

复制代码该阶段主要把 prepared 消息保存到 消息队列中，即让消费端可以看到此消息，也就是可以消费此消息。

仍然以下订单扣减库存为例：

其中需要说明的一点就是 RocketMQ它会定期回查状态，即遍历 commitlog 中的预备消息，去会查本地事务的执行状态，发现本地事务没有执行成功就 rollback，如果成功就发送 commit 消息。此时回查状态可以设计一张 事务表，把业务表和事务绑定在同一个本地事务中，如果本地事务执行完成，就将状态表的id 状态改为 true，到时回查该表即可。

优点：

• 消息数据独立存储，降低业务系统与消息系统之间的耦合。

• 吞吐量优于本地消息表方案。

缺点：

• 一次消息发送需要两次网络请求(half消息 + commit/rollback)。

• 需要实现消息回查接口。

参考

www.jianshu.com/p/e31d9ebed…

本文转载自: 掘金

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

问题

1. PerThreadQueuedDispatcher.dispatch() 将事件和订阅者封装到一个 Event 对象中并将其提供给线程本地队列，然后轮询它以执行与ImmediateDispatcher.dispatch() 相同的逻辑有什么意义？
2. dispatching.get() 总是返回 false 吗？为什么呢？

源码部分

ImmediateDispatcher#dispatch()

1
2
3
4
5
6

scss复制代码void dispatch(Object event, Iterator<Subscriber> subscribers) {
  checkNotNull(event);
  while (subscribers.hasNext()) {
    subscribers.next().dispatchEvent(event);
  }
}

PerThreadQueuedDispatcher#dispatch()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

vbnet复制代码void dispatch(Object event, Iterator<Subscriber> subscribers) {
  checkNotNull(event);
  checkNotNull(subscribers);
  Queue<Event> queueForThread = queue.get();
  queueForThread.offer(new Event(event, subscribers)); 
  // Isn't dispatching.get() always return false? Why the if then?
  if (!dispatching.get()) { 
    dispatching.set(true);
    try {
      Event nextEvent;
      while ((nextEvent = queueForThread.poll()) != null) {
        while (nextEvent.subscribers.hasNext()) {
          nextEvent.subscribers.next().dispatchEvent(nextEvent.event);
        }
      }
    } finally {
      dispatching.remove();
      queue.remove();
    }
  }
}

假设条件

1. 同一个线程中，执行过程中存在 事件 嵌套现象，具体为一个事件 A 触发了事件 B 和 C，而事件 B 又触发了事件 D

验证

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

typescript复制代码class Test {
    class A {}
    class B {}
    class C {}
    class D {}
​
    EventBus bus = new EventBus();
​
    Test() {
        bus.register(this);
        bus.post(new A());
    }
​
    @Subscribe void listen(A obj) {
        System.out.println("A");
        bus.post(new B());
        bus.post(new C());
    }
​
    @Subscribe void listen(B obj) {
        System.out.println("B");
        bus.post(new D());
    }
​
    @Subscribe void listen(C obj) {
        System.out.println("C");
    }
​
    @Subscribe void listen(D obj) {
        System.out.println("D");
    }
}

将这些事件视为一种树，其中每个事件都会产生额外的“子”事件：

1
2
3
4
5

css复制代码    A
   / \
  B   C
 /
D

有两种常见的遍历树的方式：深度优先（A、B、D、C）和广度优先（A、B、C、D）。这就是两个调度器之间的区别。

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

typescript复制代码class Test {
    class A {}
    class B {}
    class C {}
    class D {}
​
    EventBus bus = new EventBus();
​
    Test() {
        bus.register(this);
        bus.post(new A());
    }
​
    @Subscribe void listen(A obj) {
        System.out.println("A");
        bus.post(new B());
        bus.post(new C());
    }
​
    @Subscribe void listen(B obj) {
        System.out.println("B");
        bus.post(new D());
    }
​
    @Subscribe void listen(C obj) {
        System.out.println("C");
    }
​
    @Subscribe void listen(D obj) {
        System.out.println("D");
    }
}

我们可以将这些事件视为一种树，其中每个事件都会产生额外的“子”事件：

1
2
3
4
5

css复制代码    A
   / \
  B   C
 /
D

有两种常见的遍历树的方式：深度优先（A、B、D、C）和广度优先（A、B、C、D）。这就是两个调度器之间的区别。

直接调度程序在事件创建时对其进行处理，从而导致深度优先调度。

执行步骤为： A->B->D->C

排队调度器在事件被提交时将它们排队，并通过轮询队列来处理它们，从而导致广度优先调度。该dispatching标志用于将队列处理限制为根事件。子事件将找到设置的标志并继续前进。

执行步骤为： A->B->C->D

总结

1. ImmediateDispatcher 在事件处理上，属于深度优先调度
2. PerThreadQueuedDispatcher 通过 dispatching 线程执行标示位和 queueForThread执行队列 ，在调度上属于广度优先调度

\

本文转载自: 掘金

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

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

1.1 题目

熟练掌握 scrapy 中 Item、Pipeline 数据的序列化输出方法；

Scrapy+Xpath+MySQL数据库存储技术路线爬取当当网站图书数据
候选网站：www.dangdang.com/

1.2 思路

1.2.1 setting.py

• 打开请求头
  
• 连接数据库信息
  
• ROBOTSTXT_OBEY设置为False
  
• 打开pipelines

1.2.2 item.py

编写item.py的字段

1
2
3
4
5
6
7

python复制代码class DangdangItem(scrapy.Item):
    title = scrapy.Field()
    author = scrapy.Field()
    publisher = scrapy.Field()
    date = scrapy.Field()
    price = scrapy.Field()
    detail = scrapy.Field()

1.2.3 db_Spider.py

• 观察网页，查看分页

第二页

第三页

所以很容易发现这个page_index就是分页的参数

• 获取节点信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

python复制代码    def parse(self, response):
        lis = response.xpath('//*[@id="component_59"]')
        titles = lis.xpath(".//p[1]/a/@title").extract()
        authors = lis.xpath(".//p[5]/span[1]/a[1]/text()").extract()
        publishers = lis.xpath('.//p[5]/span[3]/a/text()').extract()
        dates = lis.xpath(".//p[5]/span[2]/text()").extract()
        prices = lis.xpath('.//p[3]/span[1]/text()').extract()
        details = lis.xpath('.//p[2]/text()').extract()
        for title,author,publisher,date,price,detail in zip(titles,authors,publishers,dates,prices,details):
            item = DangdangItem(
                title=title,
                author=author,
                publisher=publisher,
                date=date,
                price=price,
                detail=detail,
            )
            self.total += 1
            print(self.total,item)
            yield item
        self.page_index += 1
        yield scrapy.Request(self.next_url % (self.keyword, self.page_index),
                             callback=self.next_parse)

• 指定爬取数量

爬取102条

1.2.4 pipelines.py

• 数据库连接

1
2
3
4
5
6
7
8
9
10

python复制代码    def __init__(self):
        # 获取setting中主机名，端口号和集合名
        host = settings['HOSTNAME']
        port = settings['PORT']
        dbname = settings['DATABASE']
        username = settings['USERNAME']
        password = settings['PASSWORD']
        self.conn = pymysql.connect(host=host, port=port, user=username, password=password, database=dbname,
                                    charset='utf8')
        self.cursor = self.conn.cursor()

• 插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

python复制代码    def process_item(self, item, spider):
        data = dict(item)
        sql = "INSERT INTO spider_dangdang(title,author,publisher,b_date,price,detail)" \
              " VALUES (%s,%s, %s, %s,%s, %s)"
        try:
            self.conn.commit()
            self.cursor.execute(sql, [data["title"],
                                      data["author"],
                                      data["publisher"],
                                      data["date"],
                                      data["price"],
                                      data["detail"],
                                      ])
            print("插入成功")
        except Exception as err:
            print("插入失败", err)
        return item

结果查看，一共102条数据，这个id我是设置自动自增的，因为有之前测试的数据插入，所以id并没有从1开始

本文转载自: 掘金

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

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

前言

Hello 大家好，我是l拉不拉米，死锁问题是面试中出现频率很高的一个问题，今天『面试の神』系列就来讲一讲死锁。

背景

线程同步是克服多线程程序中竞争条件的好工具。但是，它也有阴暗的一面：死锁，难以发现、重现和修复的严重错误。防止它们发生的唯一可靠方法是正确设计您的代码，这是本文的主题。我们将看看死锁的起源，找到一种发现现有代码中潜在死锁的方法，并提出设计无死锁同步的实用方法。

假设读者已经熟悉多线程编程，并且对 Java 中的线程同步原语有很好的理解。在接下来的部分中，我们不会区分同步语句和锁 API，使用术语“锁”来表示这两种类型的可重入锁。

死锁机制

现在有以下两种方法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

java复制代码void increment() {
    synchronized(lock1) {
        synchronized(lock2) {
            variable++;
        }
    }
}

void decrement() {
    synchronized(lock2) {
        synchronized(lock11) {
            variable--;
        }
    }
}

这两个方法被有意设计为产生死锁，以便于我们能详细考虑这是如何发生的。

increment() 和 decrement() 基本上都由以下 5 个步骤组成：

显然，这两种方法中的第 1 步和第 2 步只有在相应的锁空闲时才能通过，否则，执行线程将不得不等待它们的释放。

假设有两个并行线程，一个执行 increment()，另一个执行 decrement()。每个线程的步骤会按正常顺序执行，但是，如果我们将两个线程放在一起考虑，一个线程的步骤将与另一个线程的步骤随机交错。随机性来自系统线程调度程序强加的不可预测的延迟。可能的交织模式或场景非常多并且可以分为两组。第一组是其中一个线程足够快以获取两个锁的地方。

比如下面的表格：

该组中的所有案例均成功完成。

在第二组中，两个线程都成功获取了锁。结果见下表：

该组中的所有情况都会导致第一个线程等待第二个线程拥有的锁，而第二个线程等待第一个线程拥有的锁的情况，因此两个线程都无法进一步进行：

这是一个典型的死锁情况。它至少满足一下3点：

• 至少有两个线程，每个线程至少占用两个锁。
• 死锁只发生在特定的线程时序组合中。
• 死锁的发生取决于锁定顺序。

第二个属性意味着死锁不能随意重现。此外，它们的再现性取决于操作系统、CPU 频率、CPU 负载和其他因素。后者意味着软件测试的概念不适用于死锁，因为相同的代码可能在一个系统上完美运行而在另一个系统上失败。

因此，交付正确应用程序的唯一方法是通过设计消除死锁。这种设计有两种基本方法，现在，让我们从更简单的方法开始。

粗粒度同步

如果我们的应用程序中的任何线程都不允许同时持有多个锁，则不会发生死锁。但是我们应该使用多少锁以及将它们放在哪里？

最简单和最直接的答案是用一个锁保护所有事务。例如，为了保护一个复杂的数据对象，您可以将其所有公共方法声明为同步的。 java.util.Hashtable 中使用了这种方法。简单的代价是由于缺乏并发而导致的性能损失，因为所有方法都是相互阻塞的。

幸运的是，在许多情况下，粗粒度同步可以以较少限制的方式执行，从而允许一些并发和更好的性能。为了解释它，我们应该引入一个事务连接变量的概念。假设如果满足两个条件中的任何一个，则两个变量在事务上连接：

1. 存在涉及两个变量的交易。
2. 两个变量都连接到第三个变量（传递性）。

因此，您首先以这样一种方式对变量进行分组，即同一组中的任何两个变量在事务上都是连接的，而不同组中的任何两个变量都没有。然后通过单独的专用锁保护每个组：

这种粗粒度同步的一个很好的现实例子是 java.util.concurrent.ConcurrentHashMap。在这个对象内部，有许多相同的数据结构（“桶”），每个桶都由自己的锁保护。事务被分派到由键的哈希码确定的存储桶。因此，具有不同键的交易大多会进入不同的存储桶，这使得它们可以并发执行而不会牺牲线程安全性，由于存储桶的事务独立性，这是可能的。

死锁分析

假设需要确定给定的代码是否包含潜在的死锁。我们称这种任务为“同步分析”或“死锁分析”。我们要如何处理这个问题？

最有可能的是，我们会尝试对线程争用锁的所有可能场景进行排序，试图找出是否存在不良场景。在死锁机制一节中，我们采用了最简单的方法，结果发现场景太多了。即使在最简单的情况下，也有 252 个，因此彻底检查它们是不可能的。在实践中，您可能最终只会考虑几个场景，并希望自己没有遗漏一些重要的事情。换句话说，公平的死锁分析无法通过初级的方法完成，我们需要一种专门的、更有效的方法。

锁定图

此方法包括构建锁定图并检查它是否存在循环依赖关系。锁定图是显示锁和线程在这些锁上的交互的图形。此类图中的每个闭环都表示可能存在死锁，并且没有闭环保证了代码的死锁安全性。

如何画锁定图？我们以死锁机制一节中的代码为例：

1. 对于代码中的每个锁，在图表上放置一个相应的节点；在示例中，这些是 lock1 和 lock2
2. 对于所有线程试图在已经持有锁 A 的情况下获取锁 B 的语句，画一个从节点 A 到节点 B 的箭头；在这个例子中， increment() 中有 lock1 -> lock2， decrement() 中有 lock2 -> lock1。如果一个线程按顺序使用多个锁，则为每两个连续的锁绘制一个箭头。

这里形成了一个闭环：lock1 -> lock2 -> lock1，告诉我们代码包含潜在的死锁。

更复杂的例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

java复制代码void transaction1(int amount) {
    synchronized(lock1) {
        synchronized(lock2) {
            // do something;
        }
    }
}

void transaction2(int amount) {
    synchronized(lock2) {
        synchronized(lock13) {
            // do something;
        }
    }
}

void transaction3(int amount) {
    synchronized(lock3) {
        synchronized(lock11) {
            // do something;
        }
    }
}

让我们看看这段代码是否是死锁安全的。有3个锁：lock1、lock2、lock3和3条锁路径：lock1 -> lock2 in transaction1()，lock2 -> lock3 in transaction2()，lock3 -> lock1 in transaction3()。

绘制锁定图如下：

同样，图 A 表明我们的设计包含潜在的死锁。但是，不仅如此。它还提示我们如何修复设计；我们只需要打破循环！例如，我们可以在方法 transaction3() 中交换锁。相应的箭头改变方向，图 B 中的图变为无循环，保证了固定代码的死锁安全性。

带锁排序的细粒度同步

采取尽可能细粒度的同步方式，希望得到最大可能的事务并发度作为回报。这种设计基于两个原则。

第一个原则是禁止任何变量同时参与多个事务。

为了实现这一点，我们将每个变量与一个唯一的锁相关联，并通过获取与相关变量关联的所有锁来启动每个事务。以下代码说明了这一点：

1
2
3
4
5
6
7
8
9

java复制代码void transaction(Item i1, Item i2, Item i3, double amount) {
    synchronized(i1.lock) {
        synchronized(i2.lock) {
            synchronized(i3.lock) {
                // do something;
            }
        }
    }
}

一旦获得了锁，其他事务就不能访问这些变量，因此它们不会被并发修改。这意味着系统中的所有事务都是一致的。同时，允许在不相交变量集上的事务并发运行。因此，我们获得了一个高度并发但线程安全的系统。

但是，这样的设计会立即导致死锁的可能性，因为现在我们处理多个线程和每个线程的多个锁。此时，我们就需要用到第二个设计原则。

第二个设计原则是必须以规范的顺序获取锁以防止死锁。

这意味着我们将每个锁与一个唯一的常量索引相关联，并始终按照它们的索引定义的顺序获取锁。将这个原理应用到上面的代码中，我们得到了细粒度设计的完整说明：

1
2
3
4
5
6
7
8
9
10
11
12

java复制代码void transaction(Item i1, Item i2, Item i3, double... amounts) {
    // 使用item的id属性作为锁的索引
    Item[] order = {i1, i2, i3};
    Arrays.sort(order, (a,b) -> Long.compare(a.id, b.id));
    synchronized(order, [0].lock) {
        synchronized(order, [1].lock) {
            synchronized(order, [2].lock) {
                // do something;
            }
        }
    }
}

但是，确定规范排序确实可以防止死锁吗？我们能证明吗？答案是肯定的，我们可以使用锁定图来完成。

假设我们有一个有 N 个变量的系统，所以有 N 个关联的锁，因此图中有 N 个节点。如果没有强制排序，锁会以随机顺序被抓取，所以在图中，会有双向随机箭头，并且肯定会存在表示死锁的闭环：

如果我们强制执行锁排序，从高到低索引的锁路径将被排除，所以唯一剩下的箭头将是那些从左到右的箭头：

在上图中我们找不到一个闭环，因为只有当箭头双向流动时，闭环才可能存在，没有闭环意味着没有死锁。

通过使用细粒度锁和锁排序，我们可以构建一个高并发、线程安全和无死锁的系统。但是，提高并发性是否需要付出代价？

首先，在低并发的情况下，与粗粒度的方法相比，存在一定的速度损失。每个锁捕获是一个相当昂贵的操作，但细粒度设计假设锁捕获至少是两倍。但是，随着并发请求数量的增加，由于使用了多个 CPU 内核，细粒度设计很快就会变得更好。

其次，由于大量的锁对象，存在内存开销。幸运的是，这很容易解决。如果受保护的变量是对象，我们可以摆脱单独的锁对象，并将变量本身用作自己的锁。否则，例如如果变量是原始数组元素，我们可能只需要有限数量的额外对象。为此，我们定义了从变量 ID 到中等大小的锁数组的映射。在这种情况下，锁必须按它们的实际索引排序，而不是按变量 ID。

总结

在本文中，我们探讨了多线程编程中的死锁问题。我们发现如果按照一定的设计模式编写同步代码，可以完全避免死锁。我们还研究了此类设计为何以及如何工作，其适用性的限制是什么，以及如何有效地发现和修复现有代码中的潜在死锁。预计所提供的材料为设计完美的无死锁同步提供了足够的实用指南。

最后

创作不易，如果觉得这篇文章对您有所帮助，还请多多关注，多多点赞！！感谢！！

本文转载自: 掘金

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

简单谈谈MySQL的索引失效问题

前言

本笔记学习于B站尚硅谷MySQL数据库高级

DQL、DML、DDL语句不在本次课程之内，有需要可以参考视频MySQL_基础

我的mysql版本为

Server version: 5.5.62-log MySQL Community Server (GPL)

课程简介

本次课程主要面向java开发人员，并不完全契合运维和数据库工程师

1、mysql的架构介绍

2、索引优化分析

3、查询截取分析

mysql逻辑架构

和其它数据库相比，mysql有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用

主要体现在存储引擎的架构上

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离

这种架构可以根据业务的需求和实际需要选择合适的存储引擎

简述mysql的架构层次及其功能

1、连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信

主要完成一些类似于连接处理、授权认证、及相关的安全方案

在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。

同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限

2、服务层

第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程、函数等。

在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。

如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

3、引擎层

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

后面会具体介绍使用得比较多的MyISAM和InnoDB引擎

4、存储层

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

存储引擎简介

上面介绍了存储引擎是负责了mysql对于数据的存储和提取

我们使用sql命令查看

1
2

sql复制代码mysql> show engines;
mysql> show variables like '%storage_engine%'

show engines命令写出了当前的引擎及其简述

show variables like '%storage_engine%'命令写出了默认引擎是innodb

可以看到我们当前版本最常用的**InnoDB引擎是默认引擎**，对于他的描述是

Supports transactions, row-level locking, and foreign keys

即：支持事务、行锁和外键

MyISAM与InnoDB对比

谈谈SQL

执行顺序

对于我们开发人员写的sql是

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

sql复制代码select distinct
	<select_list>
from
	<left_table> <join_type>
join <right_table> on <join_condition>
where
	<where_condition>
group by
	<group_by_list>
having
	<having_condition>
order by
	<order_by_condition>
limit
	<limit_number>

而对于mysql服务来说，是从from开始解析的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

sql复制代码from
	<left_table>
on <join_condition>
<join_type> join <right_table>
where
	<where_condition>
group by
	<group_by_list>
having
	<having_condition>
select
distinct
	<select_list>
order by
	<order_by_condition>
limit
	<limit_number>

7中join理论

• ==内连接==：inner用于查交集，即多表中的共有数据
• ==左连接==：left join则将覆盖左表，可以查左表及交集或剔除同时存在于左右表的数据
• ==右连接==：right join覆盖右表，可以查右表及交集或剔除同时存在于左右表的数据
• ==全外连接==：full outer join覆盖左右表，可以查笛卡尔积，左右表所有数据或剔除左右表共有数据

+ 注意：MySQL不支持FULL OUTER JOIN，但是我们可以对左连接和右连接的结果做union操作来实现
+ > MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中
> 
> 
> 多个 SELECT 语句会删除重复的数据（可以用UNION ALL查询重复）
> 
> 
> `SELECT 列名称 FROM 表名称 UNION [ALL] SELECT 列名称 FROM 表名称 ORDER BY 列名称;`

谈谈索引

什么是索引？

官方对于索引的定义是：索引（index）是帮助mysql高效获取数据的数据结构

可以理解为：一种排好序的高效率查找的数据结构

对于mysql中innodb引擎到底是B树还是B+树，其实网上有很多争议，我们查看mysql开发手册

可以看到，开发手册中提到

Everyone has seen a B-tree and knows that the entries in the root page point to the leaf pages. (I indicate those pointers with vertical ‘|’ bars in the drawing.) But sometimes people miss the detail that leaf pages can also point to each other (I indicate those pointers with a horizontal two-way pointer ‘<–>’ in the drawing). This feature allows InnoDB to navigate from leaf to leaf without having to back up to the root level. This is a sophistication which you won’t find in the classic B-tree, which is why InnoDB should perhaps be called a B+-tree instead.

每个人都看到过B树，并且知道根页面中的条目指向叶页面。（我在图形中用垂直“|”条表示这些指针。）但有时人们会忽略页页也可以相互指向的细节（我在图形中用水平双向指针“<–>”表示这些指针）。此功能允许InnoDB从一个叶导航到另一个叶，而无需备份到根级别。这是一种在经典的B-树中找不到的复杂性，这就是为什么InnoDB应该被称为B+树的原因。

mysql的索引同样也是用到的B+树

注：此处存疑，我是5.5的mysql，建立索引后，显示index_type为BTREE，即B树

索引的目的在于：提高查询的效率，可以类比为有目录的书籍或字典

如果没有索引，将会进行全表扫描，在数据量庞大的时候，是非常影响效率的！

索引的优点与缺点

优点

• 快速查询，提高检索效率，降低了IO成本
• 对数据排序，降低了数据排序的成本，降低cpu的消耗

缺点

• 索引也是一个文件，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
• 虽然索引大大提高了检索效率，但是会降低更新表的效率
  • 更新表时，mysql不仅要保存数据，还要更新索引文件每次更新添加了索引列的字段
• 索引是提高效率的一个因素，但是如果mysql中有大量的表时，需要花时间研究建立最符合业务需求的索引或者优化查询

一般来说索引也是非常大的，不可能全部存储在内存当中，因此索引往往以索引文件形式存储在磁盘上

既然索引意义在于排序和查找，那么它就会影响到sql中的where查找和order by排序

索引分类

单值索引

• 一个索引只包含一个列
• 一个表中可以有多个单值索引

适用于单个列查询需求量大时的情况

复合索引

复合索引也就是多列索引，由多列组合创建的索引，使用的时候遵循最左前缀原则

最左前缀原则：在查询条件中使用了复合索引的第一个字段，索引才会被使用

唯一索引

索引列的值必须唯一，允许有空值

用关键字UNIQUE把索引定义为唯一索引

主键索引

主键索引是特殊的唯一索引，**不允许存在空值 **

基本语法

创建

1
2

sql复制代码create [unique] index 索引名 on 表名(列名(长度));
alter table 表名 add index [索引名] (列名(长度));

1
2
3
4
5
6
7
8
9
10
11
12
13
14

sql复制代码1、PRIMARY KEY（主键索引）
ALTER TABLE table_name ADD PRIMARY KEY ( column )

2、UNIQUE(唯一索引)
ALTER TABLE table_name ADD UNIQUE (column)

3、INDEX(普通索引)
ALTER TABLE table_name ADD INDEX index_name ( column )

4、FULLTEXT(全文索引)
ALTER TABLE table_name ADD FULLTEXT ( column )

5、多列索引
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

删除

1

sql复制代码drop index [indexName] on tableName;

查看

1

sql复制代码show index from tableName

索引结构

BTree索引

B树图示

B+树图示

背景板浅蓝色的块我们称之为一个磁盘块

可以看到每个磁盘块包含几个数据项(深蓝色小块)和指针(黄色小块) ,

如磁盘块1包含数据项17和35，包含指针P1、 P2、 P3

B树的特点：每个节点都有数据和指针

• 节点排序
• 一个节点可以存多个元索，多个元索也排序了
• 每个节点都存储key和data

B+树的特点：数据只存在于叶子节点

• 拥有B树的特点
• 非叶子节点上的都是索引指针，叶子节点中存储了所有的元素与索引，并且排好了顺序
• 叶子节点之间有指针

+ 增加了**顺序访问指针** ，每个叶子节点增加一个指向相邻叶子节点的指针

• 只有叶子节点存储data，叶子节点包含了这棵树的所有索引和数据

在B+树中

真实的数据存在于叶子节点，即5,8,9,10,15,18,20,26,27,28,30,33,35,38,50,56,60,63,65,73,79,80,85,88,90,96,99

非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，例如5、28、65并不真实存在于数据表中

Hash索引

hash索引由于其结构，所以在每次查询的时候直接一次到位，不像b-tree那样一点点的前进。所以hash索引的效率高于b-tree，但hash也有缺点，主要如下：

• 由于存放的是hash值，所以仅支持<=>以及in操作
• hash索引无法通过操作索引来排序，这是因为存放的时候经过hash计算，但是计算的hash值和存放的不一定相等，所以无法排序
• 在组合所以里，无法对部分使用索引
• 不能避免全表扫描，只是由于在memory表里支持非唯一值hash索引，就是不同的索引键，可能存在相同的hash值
• 当存在大量相同hash值得时候，hash索引的效率会变低

full-text索引

full-text在mysql里仅有myisam支持它，而且支持full-text的字段只有char、varchar、text数据类型

full-text主要是用来代替like “%***%”效率低下的问题

R-Tree索引

r-tree在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有myisam、bdb、innodb、ndb、archive几种。

相对于b-tree，r-tree的优势在于范围查找.

应不应该创建索引

哪些情况需要创建

• 主键自动创建唯一索引，默认命名为PRIMARY
• 频繁作为查询条件的字段
• 与其他表关联，作为查询条件的外键字段
• 单键索引/复合索引？高并发下推荐复合索引
• 查询中排序的字段，排序字段通过索引访问将提高排序速度
• 查询中统计或分组的字段，group by也要排序，所以也推荐建立索引

哪些情况不需要创建

• 表记录太少，建了也没效果，可能引起索引失效
• 频繁更新的字段，更新字段的同时也会更新索引，消耗性能、降低效率
• where条件中不使用的字段
• 经常增删改的表
• 包含许多重复内容的数据列，建立索引没有太大的效果

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

性能优化分析：SQL性能慢

服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

• CPU瓶颈：
  • CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候
• IO瓶颈：
  • 磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

使用 IP 而不是域名做数据库路径，避免 DNS 解析问题

Explain的使用

explain是什么？

使用Explain关键字，可以模拟mysql语句优化器执行优化sql语句，从而知道mysql是如何优化的，以分析sql语句或者表结构的性能瓶颈如何

id type key rows Extra比较重要，记得复习复习

explain怎么用？

explain+sql语句即可

1

sql复制代码explain select * from t_user;

1

sql复制代码explain select * from t_user where uid=1;

表的读取顺序

并不是怎么写的就怎么个执行顺序，因为mysql会进行重写排序（有点类似于java的指令重排）

table对应的表名

id为执行等级

• id相同时，从上至下执行
• id越大的table越先被执行

当table为<derived2>时，表示这个table是由id为2的表执行后衍生出来的虚表（子查询给别名就会出现）

数据读取的操作类型

select_type是查询的类型

用于区分普通查询、联合查询、子查询等的复杂查询

• simple
  • 简单的select查询，不包含子查询或者union
• primary
  • 查询中若包含任何复杂的子部分，最外层的查询将标记为PRIMARY
• subquery
  • select或where后跟的子查询
• derived
  • 在from列表中包含的子查询衍生出的临时虚拟表将标记为DERIVED
• union
  • 若第二个select出现在union之后，则被标记为UNION
  • 若UNION包含在from子句的子查询中，外层select将被标记为DERIVED
• union result
  • 从UNION表获取结果的select

type是访问类型

显示查询用了哪种类型

从最好到最差依次是

system>const>eq_ref>ref>range>index>ALL（ALL就是全表扫描）

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

• system
  • 表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计
• const
  • **单表查询情况下通过索引一次就找到了，**const用于比较primary key主键或者unique聚簇索引。
  • 因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换一个常量
• eq_ref
  • 多表查询的情况下的唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
  • 常见于主键或唯一索引扫描的联合查询
• ref
  • 非唯一性索引扫描，返回匹配某个单独值的所有行
  • 本质上也是一种索引访问，它返回所有匹配某个单独值的行
  • 它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
• range
  • 只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引
  • 一般就是在where语句中出现了between、<、>、in等的查询
  • 这种范围扫描索引扫描比全索引扫描要好，因为它只需要开始于索引的某一点， 而结束语另一点，不用扫描全部索引（前提是加了索引）
• index
  • Full Index Scan, index 与ALL区别为index类型只遍历索引树
  • 这通常比ALL快，因为索引文件通常比数据文件小
  • 也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的
• ALL
  • 遍历全表以找到匹配的行

哪些索引可以被使用，哪些索引被实际使用了

possible_keys显示可能引用在这张表的索引，一个或多个

查询涉及到的字段上若存在索引，则该索引将会被列出，但查询时不一定被实际引用！

keys表示实际用到的一个索引，如果为null则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在key列表中

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表

类似于sql语句EXPLAIN select * from t_user where uname='yyf' and uid=2

其中uid是默认的主键索引，uname是创建的普通索引

possible_keys为PRIMARY,index_user_uname，表示可能用到的索引有两个

而keys为PRIMARY，表示实际用到的是主键索引

key_len表示索引中使用的字节数

可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_ len 显示的值为索引字段的最大可能长度，并非实际使用长度

即key_ len是 根据表定义计算而得，不是通过表内检索出的

表之间的引用

ref显示索引的哪一列被使用了

如果可能的话，是一个常数（const）

哪些列或常量被用于查找索引列上的值

每张表有多少行被优化器查询了

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

如果是全表扫描，这个值会很大，效率也就很低

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

• Using filesort
  • 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
    • filesort是通过相应的排序算法,将取得的数据在内存中进行排序
  • MySQL中无法利用索引完成的排序操作称为”文件排序“
  • 在MySQL中filesort 的实现算法实际上是有两种
    • 双路排序：是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行指针信息，然后在sort buffer 中进行排序
    • 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序。
• Using temporary
  • 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。
• USING index
  • 表示相应的select操作中使用了覆盖索引(Covering Index)， 避免访问了表的数据行，效率不错
  • 如果同时出现using where，表明索引被用来执行索引键值的查找;
  • 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
• Using where
  • 表明使用了where过滤
• using join buffer
  • 使用了连接缓存
• impossible where
  • where子句的值总是false，不能用来获取任何元组
• select tables optimized away
  • 在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作
  • 或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
• distinct
  • 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

mysql存储过程创建循环

这里我们写一个mysql的循环insert数据，用于测试

因为mysql是不能像java直接while循环的，需要创建并调用存储过程

存储过程是一个代码段，在mysql执行过程中，遇到分号就执行了，怎么去改掉分号呢，让代码继续执行呢，用delimiter

delimiter就是告诉mysql解释器，该段命令已经结束了，可以执行了。

默认情况下，delimiter是分号; 遇到分号就执行。

后面的//符号 就是告诉mysql，遇到//再执行

1
2
3
4
5
6
7
8
9
10
11
12
13
14

sql复制代码delimiter //                            #定义标识符为双斜杠
drop procedure if exists test;          #如果存在test存储过程则删除
create procedure test()                 #创建无参存储过程,名称为test
begin
    declare i int default 0;            #申明变量并赋值
    while i < 1000 do                   #结束循环的条件: 当i大于10时跳出while循环
        insert into test(username,balance) values ("while",i);    #往test表添加数据
        set i = i + 1;                  #循环一次,i加一
    end while;                          #结束while循环
end
//                                      #结束定义语句
delimiter ;                             #改回原来的标识符
call test();                            #调用存储过程
select * from test order by id desc limit 0,1000;                 #查看test表数据

我们创建一个表，并在表中插入10万条数据，用于测试

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

sql复制代码CREATE TABLE `t_index` (
  `id` int unsigned zerofill NOT NULL AUTO_INCREMENT,
	`uid` VARCHAR(255) not NULL,
  `username` varchar(255) not NULL,
	`password` VARCHAR(255) not null,
  `balance` int unsigned zerofill not NULL,
	`views` int not null,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

delimiter //
drop procedure if exists test;
create procedure test()
begin
    declare i int default 0;
    while i < 100000 do
        insert into t_index(uid,username,`password`,balance,views)
				values (i,"while","while",i,i);
        set i = i + 1;
    end while;
end
//
delimiter ;
call test();
select * from t_index order by id desc limit 0,1000;

select查询语句写的差劲

当业务中发现，sql的执行时间很长，等待时间很长时，有哪些可能引起的因素呢？

下面来分析一下

查询了不需要的记录

• 像应用程序select * 时其实并不需要全部的数据，却没有去加limit进行限制，从而把全表的数据都捞出来，显然多此一举。
• 单条查询最后增加 LIMIT 1，如果说找到一条就停止扫描了，无需全表扫描

多表关联时返回全部列

• 多个表进行关联，像一些没有什么意义的列也全部查出来，也是会影响性能。

总是取出全部的列

• 像select * 这类查询，取出全部列，不一定会完成索引覆盖这类优化，会触发各种回表查询，为服务器带来额外的IO、内存和CPU消耗。

重复查询相同的数据

• 对于每次查询都返回同样结果的这类查询，其实查一遍就够了，把结果存到Redis这类缓存中，减轻MySQL的压力。

小表驱动大表

即：先操作小的数据集，再操作大的数据集

in和exists怎么选用？

in先查内表，exists先查外表

所以in里的子集小时，优先用in，反之用exists

• exists：
  • 将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留
  • 因为只返回结果TRUE/FALSE，所以子查询中不管是select *还是select 1或其他都可以，执行时会忽略字段的
• in：
  • in中的查询只执行一次，它将查询出的所有的数据缓存起来，然后检查外表中查询出的字段在缓存中是否存在，如果存在则将外表的查询数据加入到结果集中，直到遍历完外表中所有的结果集为止

1
2
3
4
5

sql复制代码# 先查B表，再放入A表查询
select * from A where id in (select id from B)

# 先查A表，再联系B表查询
select * from A where exists (select 1 from B where B.id = A.id)

我们的宗旨是先操作小的，那么当B数据量比A小的时候，我们应该先操作B表

而in先查内表，即先查B表，所以：B小用in

当A数据量比B小的时候，我们应该先操作A表

而exists先查外表，即先查A表，所以：A小用exists

order by排序出现FileSort

order by字句，尽量使用index排序，即索引的排序，避免使用filesort排序，即文件排序

尽可能的在索引列上完成排序操作（默认的ASC排列）

切记：order by严格按照顺序判断是否索引

使用index排序的两种条件：

• order by语句使用索引最左前列原则
• 使用where字句与order by字句条件列组合满足索引最左前列原则

什么时候会出现filesort呢：

• 未建立索引
• 不遵守最左前缀原则
• 不一致遵守默认的ASC顺序（就是一个使用ASC，一个使用DESC），索引失效

+ 当然只要**都是ASC**或**都是DESC**的话，就不会失效

那么解决filesort就按照上文相反的操作执行即可

在MySQL中filesort的实现算法实际上是有两种

• 双路排序（Mysql4.1之前）：
  • 两次扫描磁盘，最终得到数据。比较耗时，效率较低
  • 取出数据的行指针和order by列，对它们进行排序，然后扫描已经排序好的列表，按照表中的值从列表中重新读取对应的数据输出
  • 从磁盘读取排序字段，在sort_buffer进行排序，再从磁盘取其他字段
• 单路排序（mysql4.1之后）：
  • 是一次性取出满足条件行的所有字段，按照order by列在sort_buffer中进行排序，然后扫描排序后的字段进行输出
  • 它的效率更快一些， 避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存中进行排序
    • 如果一次没取的完就出问题了，就要分批次去执行IO操作取数据，反而效率变低了
      • 增大sort_buffer_size参数，增大缓冲区大小
      • 增大max_length_for_sort_data参数

提高order by的速度

当然要使用索引了，尽量不要让索引失效

最好不要用select *，导致查询的数据过大

• 当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序，所以可以提高max_length_for_sort_data参数
• 两种算法的数据都有可能超出sort_buffer的容量， 超出之后，会创建tmp文件进行合并排序，导致多次I/O， 但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size参数

小小总结一波

create index test on test(a,b,c);

为a、b、c字段创建联合索引

遵循最左原则：可以使用索引

• order by a
• order by a,b
• order by a,b,c
• order by a DESC , b DESC , c DESC
• where a = const ORDER BY b , c
• where a=const and b=const ORDER BY c
• where a=const and b>=const ORDER BY b , c (虽然b是范围，但是遵循了a-b-c，所以有索引)

索引失效

• ORDER BY a DESC , b ASC , c DESC (排序不一致)
• ORDER BY b , c (丢失a索引)
• where a=const ORDER BY c (丢失b索引)
• where a=const ORDER BY b , d (d不是索引的一部分)
• where a in (…) ORDER BY b , c (in也是范围查询，索引失效)

group by的情况和order by差不多

group by的本质是：先排序再分组，遵照索引的最左前缀原则

where优先级高于having，所以能写在where的条件就不要写在having了

索引失效的7种条件

索引失效是建立了索引，但是没有用上

可以看一下博文《索引失效和注意事项》，总结的比较全面

下面是可能出现索引失效的情况

索引失效口诀：模型数空运最快

• 模：模糊查询LIKE以%开头
• 型：数据类型错误
• 数：对索引字段使用内部函数
• 空：索引列是NULL
• 运：索引列进行四则运算
• 最：复合索引不按索引列最左开始查找
• 快：全表查找预计比索引更快

1、单独引用复合索引里非第一位置的索引列

复合索引遵守“最左前缀”原则

即在查询条件中使用了复合索引的第一个字段，索引才会被使用。

因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

假如有INDEX(a,b,c)，
当条件为 a 或 a,b 或 a,b,c 时都可以使用索引，
但是当条件为 b,c 时将不会使用索引。

因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减

实际上是会去索引文件里查的，type为index

2、对索引列进行运算

运算包括（+、-、*、/、！、<>、!=、%、or、in、exist等），导致索引失效

or导致索引失效：使用union

注意or也是会导致索引失效的，所以要么在业务中进行运算，要么在mysql中使用union

union 用于把来自多个select 语句的结果组合到一个结果集合中

当使用union时，mysql 会把结果集中重复的记录删掉

使用union all时，mysql 会把所有的记录返回，且效率高于union

1
2
3
4
5
6
7

sql复制代码select * from t_index where uid ='921930193014' or balance = 499010

# ↑改为↓，使用union

select * from t_index where uid ='921930193014'
UNION 
select * from t_index where balance = 499010

错误的例子：select * from test where id-1=9;

正确的例子：select * from test where id=10;

注意！！

sql 中如果使用了 not in ， not exists ， <> ， ！= 这些不走索引 range类型的查询条件后的字段索引无效

< ， > ， <= ， >= ，这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引

range类型的范围查询字段及其后面的素引无效

什么是range类型的查询条件及其后面的字段索引无效？

我们来看一个例子

t_index表有140w的数据，结构如下

1
2
3
4
5
6
7
8
9

sql复制代码CREATE TABLE `t_index` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `uid` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `balance` int(10) unsigned zerofill NOT NULL,
  `views` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1400001 DEFAULT CHARSET=utf8;

1
2
3
4

sql复制代码CREATE index index_tindex_username_balance_views on t_index(username,balance,views)

EXPLAIN
select * from t_index where username = 'while' and balance >= 499008 ORDER BY views DESC limit 1

建立username,balance,views3个字段的索引，sql查询如上，我们来查看这个sql的效果

可以看见，虽然使用到了3字段索引，可是查询的type还是range级别的，而且extra中是using filesort外部文件排序，没有使用到views的索引排序，当然效率会低一些

type变成了range,这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

但是我们已经建立了索引,为啥没用索引的排序呢?

这是因为按照BTree索引的工作原理,

先排序username

如果遇到相同的username，则再排序balance，如果遇到相同的balance，则再排序views

当balance字段在联合索引里处于中间位置时,

因balance > 499008 的条件是一个范围值(所谓range)

MySQL无法利用索引再对后面的views部分进行检索，即range类型范围查询字段及其后面的素引无效

那我们如何解决这个问题呢？去掉范围条件字段的索引，即：减去balance的索引列

只添加username和views的索引

1
2
3
4
5
6

sql复制代码DROP index index_tindex_username_balance_views on t_index

CREATE index index_tindex_username_views on t_index(username,views)

EXPLAIN
select * from t_index where username = 'while' and balance >= 499008 ORDER BY views DESC limit 1

可以看到type变为ref，ref显示const常量，extra也没有了using filesort

优化器根据索引顺序优化

当然，mysql的优化器是会根据索引顺序进行对应的优化的

（mysql不完全顺从sql语句的条件顺序，会根据索引来执行最优顺序达到最优结果）

我们来看这样一张表，有字段a ，b， c， d

并为abcd同时创建多列索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14

sql复制代码CREATE TABLE `abcd` (
  `id` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `a` varchar(255) NOT NULL,
  `b` varchar(255) NOT NULL,
  `c` varchar(255) NOT NULL,
  `d` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `i` (`a`,`b`,`c`,`d`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;

# 多执行几次插入用于测试数据
insert into abcd(a,b,c,d) values('a','b','c','d');

CREATE index i on abcd(a,b,c,d)

我们来看一下下面4条sql的执行情况

1、正常顺序a-b-c-d

a='a' and b='b' and c='c' and d='d'

1
2

sql复制代码EXPLAIN
select * from abcd where a='a' and b='b' and c='c' and d='d'

那当然是type为ref啦，正常使用索引，ref显示4个const，表示这4个全都走了索引

2、条件倒序d-c-b-a

d='d' and c='c' and b='b' and a='a'

1
2

sql复制代码EXPLAIN
select * from abcd where d='d' and c='c' and b='b' and a='a'

和正常顺序没有区别，因为优化器将顺序调整为了和索引一致的a-b-c-d

3、a-b 范围d 再c

上面我们提到，调整为了与索引一致的a-b-c-d顺序，那么我们如果将d提到c前面，并使用范围索引，那会不会导致c的索引失效呢？

结果是：不会的

1
2

sql复制代码EXPLAIN
select * from abcd where a='a' and b='b' and d>='d' and c='c'

我们可以看见，ref还有3个const，因为ref显示索引的哪一列被使用了，所以我们可以得出

这条sql一定是被优化成了where a='a' and b='b' and c='c' and d>='d'

所以a、b、c都走了索引，d因为范围查询没有走

4、a-b 范围c 再d

如果还是a-b-c-d的顺序，然后c使用范围查询，d还走索引嘛？

那当然不走了，上文提到了范围查询字段后面的素引无效，所以c和d都不走索引了

那ref应该只剩2个const了，即a和b，让我们来看看结果

1
2

sql复制代码EXPLAIN
select * from abcd where a='a' and b='b' and c>='c' and d='d'

结果无误，ref只有2个const，即a和b走索引，c范围查询不走且导致后面的d也不走索引

5、a-b-d 并根据c排序

直接上代码

1
2

sql复制代码EXPLAIN
select * from abcd where a='a' and b='b' and d='d' ORDER BY c

此时会用到几个索引？2个、3个还是4个？

答案可以说是3个

可能有人会说这不是只有2个const嘛，其实严格上来说，用到了c字段索引来排序而不是查找，故没统计

（如果c没走索引，应该是显示using filesort，见例6）

而这个const指的是a和b的常量

6、a-b 根据d排序

1
2

sql复制代码EXPLAIN
select * from abcd where a='a' and b='b' ORDER BY d

中间c断了，d当然不走索引了，所以只有a和b走了索引

所以在extra中显示了using filesort

7、a 根据c、b排序

我们先说一下where a='a' ORDER BY b,c用到了几个索引？

3个，a、b、c都用到了

根据顺序来的，a用于查找，b、c用于排序，无Using filesort，用到了3个索引，没毛病把

进入正题，下列sql用到了几个索引？

1个，只有a用到了，c和b没有用到，因为不符合顺序，在order by中严格遵守顺序

所以出现了Using filesort

1
2

sql复制代码EXPLAIN
select * from abcd where a='a' ORDER BY c,b

3、对索引应用内部函数

这种情况下应该建立基于函数的索引

select * from template t where ROUND(t.logicdb_id) = 1;

此时应该建ROUND(t.logicdb_id)为索引。

4、类型错误

如字段类型为varchar，where条件用number

例：template_id字段是varchar类型。

错误写法：select * from template t where t.template_id = 1

正确写法：select * from template t where t.template_id = ‘1’

值得一提的是，这个类型错误的失效条件并不是类型不同就一定索引失效

我们写一个例子，t_user表的uid的类型为int，student表的id的类型为varchar

分析一下下面两个语句是否使用了索引

1
2

sql复制代码# 失效
EXPLAIN select * from student where id = 1;

1
2

sql复制代码# 主键索引
EXPLAIN select * from t_user where uid = '1';

另外提一嘴：

varchar的查询条件为数字时，会变成字符串截取数字来查询

int的查询条件为字符串时，会隐式地将数字转换为字符串来查询，所以会走索引

在mysql中的varchar并不是使用的equals的比较，而是字符串截取了

此时我们存在一条数据id='1'的数据，我们使用where id = 1能不能查询出来数据呢？

是可以的

如果id='123xxx'的数据，使用where id = 123能不能查询出来呢？

同样是可以的

可是id = 'xx123'使用where id = 123就查询不出来了

存在一个uid = 1，使用where uid = '1'时，是能查询出来的，且能走索引（见前几张带explain查询的图）

5、如果MySQL预计使用全表扫描要比使用索引快，则不使用索引

6、like的模糊查询以%开头，索引失效

like “%aaa%” 不会使用索引

like “aaa%” 可以使用索引

7、索引列没有限制 not null

只要列中包含有NULL值都将不会被包含在索引中

复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的

关联查询太多join：从表索引

可能是开发人员编写sql时设计的缺陷

也可能是业务中不得已的需求导致的

以left join左连接为例

在左连接中，左表会将数据全部访问，所以我们应该为右表建立索引

例如select * from a left join b on a.id = b.id时，我们为b表的id建立索引

right join右连接同理给左表条件字段建立索引

多表查询中也是如此

• 尽可能减少Join语句中的NestedLoop的循环总次数：永远用小结果集驱动大的结果集
• 优先优化NestedLoop的内层循环
• 保证Join语句中被驱动表上Join条件字段已经被索引
• 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置

所以得出结论：从表建立索引

慢SQL

我们这里就粗略带过，需要的同学可以自己去深入了解

慢查询的开启并捕获

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来-定的性能影响。

慢查询日志支持将日志记录写入文件

开启慢查询日志，设置阈值，比如超过10s的sql就是慢SQL，并将其抓取出来

查看慢查询日志

可以查看是否开启和日志文件路径

1

sql复制代码show VARIABLES like '%slow_query_log%';

查看慢sql阈值时间，默认10s

mysql判断阈值是当时间大于long_query_time时触发，而不是大于等于

1

sql复制代码show VARIABLES like '%long_query_time%'

开启慢查询配置

1
2
3
4
5
6

sql复制代码# 开启慢查询日志
set global show_query_log = 1;
# 修改文件路径
set global slow_query_log_file = "IF-slow.log"
# 修改时间阈值
set global long_query_time = 10

使用命令开启后，只对当前数据库生效，重启mysql后会失效

修改阈值等，需要重新建立查询会话再查询才能正常显示修改后的值

如果需要永久生效的话，可以在my.ini配置文件中加入

• 开启慢查询
• 指定日志文件及其路径
• 慢sql查询时间阈值

1
2
3

ini复制代码slow-query-log=1
slow_query_log_file="IF-slow.log"
long_query_time=10

explain+慢SQL分析

我们将阈值设置为3，即set global long_query_time = 3

然后执行select sleep(4)让sql睡4秒，让他成为慢sql被记录到日志中去

然后打开log日志查看

1
2
3
4
5
6

verilog复制代码# Time: 211118 12:07:44
# User@Host: root[root] @  [127.0.0.1]
# Query_time: 4.003336  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use test1;
SET timestamp=1637208464;
select SLEEP(4);

可以看见很多数据

• 产生记录的时间Time
• sql执行时间Query_time
• 锁时间Lock_time
• 返回的行记录Rows_sent
• 扫描的行数Rows_examined
• 使用了test1表use test1;
• 执行的sql语句select SLEEP(4);

安装perl

要想执行mysqldumpslow.pl(这是perl程序)，下载perl编译器。

下载地址：pan.baidu.com/s/1i3GLKAp

就是ActivePerl_5.16.2.3010812913.msi，一步一步安装后，将bin增加环境变量path

进入mysql的bin目录执行命令

1

powershell复制代码C:\Program Files\MySQL\MySQL Server 5.5\bin>perl mysqldumpslow.pl --help

能输出参数则安装成功

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

powershell复制代码C:\Program Files\MySQL\MySQL Server 5.5\bin>perl mysqldumpslow.pl --help
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]

Parse and summarize the MySQL slow query log. Options are

  --verbose    verbose
  --debug      debug
  --help       write this text to standard output

  -v           verbose
  -d           debug
  -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                al: average lock time
                ar: average rows sent
                at: average query time
                 c: count
                 l: lock time
                 r: rows sent
                 t: query time
  -r           reverse the sort order (largest last instead of first)
  -t NUM       just show the top n queries
  -a           don't abstract all numbers to N and strings to 'S'
  -n NUM       abstract numbers with at least n digits within names
  -g PATTERN   grep: only consider stmts that include this string
  -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
               default is '*', i.e. match all
  -i NAME      name of server instance (if using mysql.server startup script)
  -l           don't subtract lock time from total time

-s,排序，c,t,l,r以及ac,at,al,ar各自是依照query次数。时间，lock时间，返回记录排序。加a就是倒序。

-t,top n。跟上数字就是算出top多少条
-g。跟正则表达式。

使用命令查看日志

1

powershell复制代码mysqldumpslow.pl -r -s c -a -t   >C:\ProgramData\MySQL\MySQL Server 5.5\data\IF-slow.log

完结撒花

因为本文主要讲索引失效问题，所以其他章节本文草草略过，本文到此结束，谢谢观看！

本文转载自: 掘金

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

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

当我们有从网上爬下来的新鲜数据

标题 name， 作者 author， 评分 grade， 统计有多少人看过 stats

读取数据

使用pandas的 read_csv 方法读取数据， usecols可以选择某些指定的列进行读取，默认是所有列

1
2

ini复制代码import pandas as pd
df = pd.read_csv("foodInfo.csv", usecols=['name', 'author', 'grade', 'stats'])

可以输出前五条看看效果 print(df.head())

去重

1

scss复制代码print(df.duplicated().value_counts())

通过输出的数据我们可以看到一共有103条数据，其中有一条重复的，我们也可以通过df.duplicated()查看是哪一条是重复的数据

1

ini复制代码df.drop_duplicates(keep='first', inplace=True)

drop_duplicates 去重根据不同的情况有3个参数

subset ： 列名数组，默认是全选，也就是如果指定的那几列的数据都重复了才进行删除

keep ：默认是first， first是只保留第一次出现的重复行， last是只保留最后一次出现的重复行， False是删除所有的重复行

inplace ： 为True就是在原数据上直接更改， 为False就是需要变量接收的

缺失值处理

1
2
3
4
5

ini复制代码# 查看哪一列有缺失值
print(df.isnull().any())

# 定位到有缺失值的那一列
data = df[df.isnull().values==True]

删除缺失值 dropna

1

ini复制代码df.dropna(how='any', inplace=True)

axis ：0是行，1是列，默认是行

subset :对特定的列进行缺失值删除处理

how : any只要出现1个缺失值就删除整行，all所有列都是缺失值才删除

thresh : 缺失值的数量标准，达到这个阈值才会删除

inplace ： 为True就是在原数据上直接更改， 为False就是需要变量接收的

填充缺失值 fillna

我是指定一个值去替换缺失值，将数据里面这个作者有评分的平均分填在缺失值上

1
2
3
4
5
6
7
8
9
10

python复制代码def fillByAuthor(author):
    count = 0
    sum = 0.0
    for i in range(len(df)):
        if math.isnan(df.grade[i]):
            continue
        if df.author[i] == author:
            count = count + 1
            sum = sum + df.grade[i]
    return round(sum / count, 2)

1
2

ini复制代码a = fillByAuthor('王光光光光')
df.fillna(a, inplace=True)

inplace : 为True就是在原数据上直接更改， 为False就是需要变量接收的

method : pad/ffill：用前一个非缺失值去填充该缺失值; backfill/bfill：用下一个非缺失值填充该缺失值

None：指定一个值去替换缺失值（缺省默认这种方式）

limit ：限制填充个数

axis：修改填充方向

另存为

1

arduino复制代码df.to_csv("clean_data.csv")

本文转载自: 掘金

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

相信对与不少在北上广深飘着的年轻人来说，是选择继续留在大城市拼搏呢还是回到小城市发展，一直都困扰着他们。毕竟像网络上说的那样，“异乡容不下肉身，故乡装不下灵魂”。那么退居到二三线找一个城市安居乐业，房子首先是要考虑解决的问题，不同城市的购房成本也是不一样的。不过从总体上来看，新一线或二三线城市的购房成本比在一线城市中购房要低不少，而且有不少新一线城市是很适合居住的城市，今天小编以一个爬虫工作者通过获取数据分析来为大家分析一下，逃离北上广之后，还有哪些城市适合年轻人定居。

需求：根据输入的城市名获取该城市的房价信息，对获得的数据进行简单的分析。

目标网站：lianjia.com/

实现思路：像链家这样的网站一般反爬策略是做的比较好的，并且获取的数据也不少，所以在访问过程中是需要加上代理ip的。本文使用了最简单的爬虫动态转发代理。实现代码如下：

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

ini复制代码#! -*- encoding:utf-8 -*-
        import base64            
        import sys
        import random

        PY3 = sys.version_info[0] >= 3

        def base64ify(bytes_or_str):
            if PY3 and isinstance(bytes_or_str, str):
                input_bytes = bytes_or_str.encode('utf8')
            else:
                input_bytes = bytes_or_str

            output_bytes = base64.urlsafe_b64encode(input_bytes)
            if PY3:
                return output_bytes.decode('ascii')
            else:
                return output_bytes

        class ProxyMiddleware(object):                
            def process_request(self, request, spider):
                # 代理服务器(产品官网 www.16yun.cn)
                proxyHost = "t.16yun.cn"
                proxyPort = "31111"

                # 代理验证信息
                proxyUser = "username"
                proxyPass = "password"

                request.meta['proxy'] = "http://{0}:{1}".format(proxyHost,proxyPort)

                # 添加验证头
                encoded_user_pass = base64ify(proxyUser + ":" + proxyPass)
                request.headers['Proxy-Authorization'] = 'Basic ' + encoded_user_pass                    

                # 设置IP切换头(根据需求)
                tunnel = random.randint(1,10000)
                request.headers['Proxy-Tunnel'] = str(tunnel)

通过获取到的数据分统计了一下全国主要一些城市的购房成本，其中长沙、沈阳、重庆、杭州的房价收入比相对偏低，相对来说要轻松些，当然除了房子本身之外，一个城市的各种设施等因素也是不少年轻人着重考虑的，所以考虑定居一城市也是需要从多方面进行考量的。

本文转载自: 掘金

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

在Ubuntu 上安装和配置Snort 3 NIDS

概述

在本教程中，你将学习如何在Ubuntu21.04上安装和配置Snort3NIDS。Snort是一个轻量级的网络入侵检测系统。它具有基于规则的日志记录功能，除了检测各种攻击和扫描(如缓冲区溢出、端口扫描、CGI攻击、SMB探测等)之外，还可以执行内容搜索/匹配。Snort具有实时警报功能，可以将警报发送到
syslog或者记录为一个单独的“警报”文件，甚至通过Samba发送到Windows计算机。

Snort 3一些功能特性：

• 支持多线程
• 共享配置和属性表
• 使用简单的、脚本化配置
• 关键组件可热插拔
• 无端口配置的自动检测服务
• 在规则中支持粘性缓冲区
• 自动生成参考文档
• 更好的跨平台支持

安装Snort 3

Ubuntu 21.04系统软件源目前提供的是 snort 2.9版本

所以我们需要从源码编译安装Snort 3

1、更新系统软件源

1

shell复制代码osboxes@osboxes:~$ sudo apt update

2、安装依赖软件包和编译所需的一些工具

1

shell复制代码sudo apt install build-essential libpcap-dev libpcre3-dev libnet1-dev zlib1g-dev luajit hwloc libdnet-dev libdumbnet-dev bison flex liblzma-dev openssl libssl-dev pkg-config libhwloc-dev cmake cpputest libsqlite3-dev uuid-dev libcmocka-dev libnetfilter-queue-dev libmnl-dev autotools-dev libluajit-5.1-dev libunwind-dev

3、安装Snort DAQ

下载并安装最新版本的 Snort DAQ（数据采集库）。 默认的 Ubuntu 软件库中不是最新版的，所以需要从源代码编译安装

1
2
3
4
5
6
7
8

shell复制代码osboxes@osboxes:~$ mkdir snortSourceFiles //建一个存放源码的目录
osboxes@osboxes:~$ cd snortSourceFiles
osboxes@osboxes:~/snortSourceFiles$ git clone https://github.com/snort3/libdaq.git //从github上下载daq源码
osboxes@osboxes:~/snortSourceFiles$ cd libdaq/
osboxes@osboxes:~/snortSourceFiles/libdaq$ ./bootstrap //编译安装
osboxes@osboxes:~/snortSourceFiles/libdaq$ ./configure
osboxes@osboxes:~/snortSourceFiles/libdaq$ sudo make
osboxes@osboxes:~/snortSourceFiles/libdaq$ sudo make install

4、安装Google开发的线程缓存的malloc：TCMalloc(可选)

TCMalloc提供高效的多线程内存管理实现，用于替代操作系统的内存分配相关的函数（malloc、free，new，new[]等），具有减少内存碎片、适用于多核、更好的并行性支持等特性。

1
2
3
4
5
6

shell复制代码osboxes@osboxes:~/snortSourceFiles/libdaq$ cd ..
osboxes@osboxes:~/snortSourceFiles$ wget https://github.com/gperftools/gperftools/releases/download/gperftools-2.8/gperftools-2.8.tar.gz
osboxes@osboxes:~/snortSourceFiles$ tar xzf gperftools-2.8.tar.gz
osboxes@osboxes:~/snortSourceFiles$ cd gperftools-2.8/
osboxes@osboxes:~/snortSourceFiles/gperftools-2.8$ ./configure
osboxes@osboxes:~/snortSourceFiles/gperftools-2.8$ sudo make && sudo make install

5、从源代码安装 Snort 3

从Snort 3的GitHub 库中获取源码

1
2

shell复制代码osboxes@osboxes:~/snortSourceFiles/gperftools-2.8$ cd ../
osboxes@osboxes:~/snortSourceFiles$ git clone https://github.com/snort3/snort3.git

编译安装

1
2
3
4
5
6
7
8
9
10
11
12
13
14

shell复制代码osboxes@osboxes:~/snortSourceFiles$ cd snort3/
osboxes@osboxes:~/snortSourceFiles/snort3$ sudo ./configure_cmake.sh --prefix=/usr/local --enable-tcmalloc

Build Directory : build
Source Directory: /home/osboxes/snortSourceFiles/snort3
-- The CXX compiler identification is GNU 10.3.0
-- The C compiler identification is GNU 10.3.0
...........
-- Configuring done
-- Generating done
-- Build files have been written to: /home/osboxes/snortSourceFiles/snort3/build
osboxes@osboxes:~/snortSourceFiles/snort3$ cd build/
osboxes@osboxes:~/snortSourceFiles/snort3/build$ sudo make 
osboxes@osboxes:~/snortSourceFiles/snort3/build$ sudo make install

更新共享库

1

shell复制代码osboxes@osboxes:~/snortSourceFiles/snort3/build$ sudo ldconfig

安装完成，查看snort版本信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

shell复制代码osboxes@osboxes:~/snortSourceFiles/snort3/build$ snort -V

   ,,_     -*> Snort++ <*-
  o"  )~   Version 3.1.16.0
   ''''    By Martin Roesch & The Snort Team
           http://snort.org/contact#team
           Copyright (C) 2014-2021 Cisco and/or its affiliates. All rights reserved.
           Copyright (C) 1998-2013 Sourcefire, Inc., et al.
           Using DAQ version 3.0.5
           Using LuaJIT version 2.1.0-beta3
           Using OpenSSL 1.1.1j  16 Feb 2021
           Using libpcap version 1.10.0 (with TPACKET_V3)
           Using PCRE version 8.39 2016-06-14
           Using ZLIB version 1.2.11
           Using LZMA version 5.2.5

以上信息说明Snort3安装成功并且可以工作正常了。

配置Snort 3

网卡配置

首先，需要把 Snort 监听网络流量的网卡设置为混杂模式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

shell复制代码osboxes@osboxes:~$ ip a show
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq_codel state UP group default qlen 1000
    link/ether 00:0c:29:cb:31:9d brd ff:ff:ff:ff:ff:ff
    altname enp2s1
    inet 192.168.81.115/24 brd 192.168.81.255 scope global dynamic ens33
       valid_lft 85892sec preferred_lft 85892sec
    inet6 fe80::20c:29ff:fecb:319d/64 scope link 
       valid_lft forever preferred_lft forever
osboxes@osboxes:~$ sudo ip link set dev ens33 promisc on //设置网卡为混杂模式

禁用网卡 Offload功能，以防止 Snort 截断大于 1518 字节的大数据包。可以用以下命令检查是否启用了此功能：

1
2
3
4

shell复制代码osboxes@osboxes:~$ ethtool -k ens33 | grep receive-off
generic-receive-offload: on
large-receive-offload: off [fixed]
osboxes@osboxes:~$

可以看到 GRO是启用状态，使用下列命令进行禁用：

1

shell复制代码osboxes@osboxes:~$ sudo ethtool -K ens33 gro off lro off

不过这个禁用和开启网卡混杂模式都是临时的，你可以把命令写入开机启动项中，让它重启后依然生效。

配置 Snort 3 规则集

规则集是Snort的核心组成部分，主要有三种类型的规则集：

• Community Rules （社区规则集）
• Registered Rules （注册规则集）
• Subscriber Rules （订阅规则集）
• 社区规则： Snort 免费规则集。
• 注册规则：它们也是免费提供的，但必须注册才能获得它们。
• 订阅规则： 商业付费规则集

本教程以配置社区规则集为例。

创建Snort规则存放目录。在/usr/local/etc/snort/snort_defaults.lua配置文件中，默认的规则集存放路径(RULE_PATH)为/usr/local/etc/rules。

1

shell复制代码osboxes@osboxes:~$ sudo mkdir /usr/local/etc/rules

从Snort官网www.snort.org/downloads/#… 3`社区规则集

1

shell复制代码osboxes@osboxes:~$ wget https://www.snort.org/downloads/community/snort3-community-rules.tar.gz

解压到规则集目录

1
2
3

shell复制代码osboxes@osboxes:~$ sudo tar xzf snort3-community-rules.tar.gz -C /usr/local/etc/rules/
osboxes@osboxes:~$ ls /usr/local/etc/rules/snort3-community-rules/
AUTHORS  LICENSE  sid-msg.map  snort3-community.rules  VRT-License.txt

接下来，我们需要配置Snort 3，Snort的主配置文件为：/usr/local/etc/snort/snort.lua。

修改其中的HOME_NET变量和EXTERNAL_NET变量，将HOME_NET修改为需要进行网络攻击防范检测的网络范围。将EXTERNAL_NET设置为除HOME_NET以外的所有网络范围。

1

shell复制代码osboxes@osboxes:~$ sudo vim /usr/local/etc/snort/snort.lua

在ips部分，定义规则集的路径：

你也可以修改/usr/local/etc/snort/snort_defaults.lua配置文件，修改和定义Snort的一些默认配置

安装 Snort OpenAppID

OpenAppID 是一个应用程序层插件，它使 Snort 能够检测网络中使用的各种应用程序，如 Facebook、Netflix、Twitter等。

从 Snort 官网(www.snort.org/downloads/#…%E4%B8%8B%E8%BD%BD%E5%B9%B6%E5%AE%89%E8%A3%85) Snort OpenAppID

1

shell复制代码osboxes@osboxes:~$ wget https://www.snort.org/downloads/openappid/19913 -O OpenAppId-19913.tgz

Tips：请注意，这里的openappid下载地址会因为更新而变化。

解压并复制到相应的目录：

1
2

shell复制代码osboxes@osboxes:~$ tar -xzf OpenAppId-19913.tgz 
osboxes@osboxes:~$ sudo cp -R odp /usr/local/lib/

编辑 Snort 3 配置文件并指定 OpenAppID 库的位置

1

shell复制代码osboxes@osboxes:~$ sudo vim /usr/local/etc/snort/snort.lua

创建Snort日志存放目录

1

shell复制代码osboxes@osboxes:~$ sudo mkdir /var/log/snort

检查snort配置是否正确：

1
2
3
4
5
6
7
8
9
10
11
12
13
14

shell复制代码osboxes@osboxes:~$ snort -c /usr/local/etc/snort/snort.lua
--------------------------------------------------
o")~   Snort++ 3.1.16.0
--------------------------------------------------
Loading /usr/local/etc/snort/snort.lua:
Loading snort_defaults.lua:
Finished snort_defaults.lua:
......
Finished /usr/local/etc/snort/snort.lua:
--------------------------------------------------
pcap DAQ configured to passive.

Snort successfully validated the configuration (with 0 warnings).
o")~   Snort exiting

提示Snort successfully validated the configuration (with 0 warnings).表示配置文件正确。

创建自定义本地规则集，用来测试Snort。

1

shell复制代码osboxes@osboxes:~$ sudo vim /usr/local/etc/rules/local.rules

创建一个用于检测ping的规则，内容如下：

1

c复制代码alert icmp any any -> $HOME_NET any (msg:"ICMP connection test"; sid:1000001; rev:1;)

测试规则是否编写正确：

1

shell复制代码snort -c /usr/local/etc/snort/snort.lua -R /usr/local/etc/rules/local.rules

然后执行测试：

1

shell复制代码sudo snort -c /usr/local/etc/snort/snort.lua -R /usr/local/etc/rules/local.rules -i ens33 -A alert_fast -s 65535 -k none

然后在别的机器上执行ping命令，ping snort主机，就可以看到输出的警告信息

配置Snort 3的日志输出

修改snort配置文件，在configure outputs部分配置是否输出为文件

这样修改后，snort将写入日志到文件alert_fast.txt

检查配置：

1

shell复制代码osboxes@osboxes:~$ snort -c /usr/local/etc/snort/snort.lua

执行测试，用-l指定日志目录

1

shell复制代码sudo snort -c /usr/local/etc/snort/snort.lua -R /usr/local/etc/rules/local.rules -i ens33 -s 65535 -k none -l /var/log/snort/

警告会被写入到日志文件：

你可以把自定义规则路径写进snort.lua配置文件中

设置Snort 为服务模式运行

虽然可以使用-D参数将snort设置为后台运行，但为了更加方便，我们可以为Snort创建一个systemd服务单元，让Snort以服务模式运行。

为了安全起见，单独为snort创建一个用户，并设置用户shell为nologin

1

shell复制代码osboxes@osboxes:~$ sudo useradd -r -s /usr/sbin/nologin -M -c SNORT_IDS snort

创建snort3服务：

1

shell复制代码osboxes@osboxes:~$ sudo vim /etc/systemd/system/snort3.service

内容如下：

1
2
3
4
5
6
7
8
9
10

shell复制代码[Unit]
Description=Snort 3 NIDS Daemon
After=syslog.target network.target

[Service]
Type=simple
ExecStart=/usr/local/bin/snort -c /usr/local/etc/snort/snort.lua -s 65535 -k none -l /var/log/snort -D -i ens33 -m 0x1b -u snort -g snort

[Install]
WantedBy=multi-user.target

重新加载systemd配置，并修改/var/log/snort目录权限。

1
2
3

shell复制代码osboxes@osboxes:~$ sudo systemctl daemon-reload 
osboxes@osboxes:~$ sudo chmod -R 5775 /var/log/snort
osboxes@osboxes:~$ sudo chown -R snort:snort /var/log/snort

启动snort服务并配置开机启动

1
2
3

shell复制代码osboxes@osboxes:~$ sudo systemctl enable --now snort3.service 
Created symlink /etc/systemd/system/multi-user.target.wants/snort3.service → /etc/systemd/system/snort3.service.
osboxes@osboxes:~$

检查运行状态：

1
2
3
4
5
6
7
8
9
10
11

shell复制代码osboxes@osboxes:~$ sudo systemctl status snort3.service 
● snort3.service - Snort 3 NIDS Daemon
     Loaded: loaded (/etc/systemd/system/snort3.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2021-11-18 03:02:05 UTC; 30s ago
   Main PID: 29398 (snort)
      Tasks: 2 (limit: 4544)
     Memory: 197.4M
     CGroup: /system.slice/snort3.service
             └─29398 /usr/local/bin/snort -c /usr/local/etc/snort/snort.lua -s 65535 -k none -l /var/log/snort -D -i ens33 -m 0x1b -u snort >

Nov 18 03:02:05 osboxes systemd[1]: Started Snort 3 NIDS Daemon.

状态显示Active: active (running) ，说明服务运行正常。

至此，Snort 3的安装和基本配置就完成了。其他更多配置和用法，可以参考Snort官网的文档。

参考资料

www.scribd.com/document/47…

www.snort.org/documents

说明

关于合天网安实验室

合天网安实验室（www.hetianlab.com）-国内领先的实操型网络安全在线教育平台 真实环境，在线实操学网络安全 ； 实验内容涵盖：系统安全，软件安全，网络安全，Web安全，移动安全，CTF，取证分析，渗透测试，网安意识教育等。

本文转载自: 掘金

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