Zhu.Yang

朱阳的个人博客(公众号:think123)

0%

进程与线程

计算机的核心是CPU,它承担了所有的计算任务。它就像一座工厂,时刻在运行。
假定工厂的电力有限,一次只能供给一个车间使用。也就是说,一个车间开工的时候,其他车间都必须停工。背后的含义就是,单个CPU一次只能运行一个任务。
进程就好比工厂的车间,它代表CPU所能处理的单个任务。任一时刻,CPU总是运行一个进程,其他进程处于非运行状态。
一个车间里,可以有很多工人。他们协同完成一个任务。
线程就好比车间里的工人。一个进程可以包括多个线程。

JAVA中的多线程

一般来说,当运行一个应用程序的时候,就启动了一个进程,当然有些会启动多个进程。启动进程的时候,操作系统会为进程分配资源,其中最主要的资源是内存空间,因为程序是在内存中运行的。在进程中,有些程序流程块是可以乱序执行的,并且这个代码块可以同时被多次执行。实际上,这样的代码块就是线程体。线程是进程中乱序执行的代码流程。当多个线程同时运行的时候,这样的执行模式成为并发执行。
多线程的目的是为了最大限度的利用CPU资源。

Java编写程序都运行在在Java虚拟机(JVM)中,在JVM的内部,程序的多任务是通过线程来实现的。每用java命令启动一个java应用程序,就会启动一个JVM进程。在同一个JVM进程中,有且只有一个进程,就是它自己。在这个JVM环境中,所有程序代码的运行都是以线程来运行。
一般常见的Java应用程序都是单线程的。比如,用java命令运行一个最简单的HelloWorld的Java应用程序时,就启动了一个JVM进程,JVM找到程序程序的入口点main(),然后运行main()方法,这样就产生了一个线程,这个线程称之为主线程。当main方法结束后,主线程运行完成。JVM进程也随即退出 。

当我们在tomcat服务器中运行一个WEB应用程序的时候,其实就是启动了一个JAVA进程,对WEB界面的每个操作都是在一个单独的线程中执行的,对于相同的操作每个线程执行的方法体是一样的(比如说同时有多个人在淘宝查看同一个商品的详细)。

Java中如何使用多线程

  1. 直接继承java.lang.Thread或者直接使用java.lang.Thread
    1
    new Thread().start();
  2. 实现java.lang.Runnable接口,并将其作为参数传递给java.lang.Thread
    1
    2
    3
    4
    5
    6
    7
    new Thread(new Runnable() {

    @Override
    public void run() {

    }
    }).start();
    注意,这里调用的是start方法,只有调用start方法才会启动线程,然后线程会执行run方法中的代码,如果直接调用run方法,java只是当成一个普通的方法调用,并不会启动一个线程

    多线程的使用场景

    刚才也说了线程是为了更好的利用CPU资源,那么我们一般在哪些场景下使用多线程呢?当然这并没有一个统一的答案,我在这里总结一下我使用多线程的场景
  3. 在数据库备份等比较耗时的操作中使用多线程,后台默默执行即可
  4. 系统需要定时做一些任务的时候,比如定时发送邮件,更新配置等
  5. 需要异步处理的数据的时候,比如说前台触发完成一个任务,后台线程直接执行,前台只需要轮询状态即可。

什么是线程安全

线程安全指的是多线程环境下,一个类在在执行某个方法的时候,对类的内部实例变量的访问是安全的。因此对于下面列出来的2类变量,并不存在任何线程安全的说法:

  1. 方法签名中的任何参数对象
  2. 处于方法内部的局部变量

为什么呢?
Java虚拟机在运行java程序的过程中会将它所管理的内存区域划分成为几个不同的区块,其将会包括以下几个运行时数据区块
Java运行时数据区

从上图中可以看出有所有线程共享的数据库只有堆和方法区,方法区用于存取以被虚拟机加载的类信息、常量、静态变量、即时编译器编译后的代码等数据,而堆的唯一目的就是存放对象实例,几乎所有的对象实例都在这里分配内存。

一个本地变量可能是原始类型,在这种情况下,它总是“呆在”线程栈上。

一个本地变量也可能是指向一个对象的一个引用。在这种情况下,引用(这个本地变量)存放在线程栈上,但是对象本身存放在堆上。

一个对象可能包含方法,这些方法可能包含本地变量。这些本地变量任然存放在线程栈上,即使这些方法所属的对象存放在堆上。

一个对象的成员变量可能随着这个对象自身存放在堆上。不管这个成员变量是原始类型还是引用类型。

静态成员变量跟随着类定义一起也存放在堆上。

存放在堆上的对象可以被所有持有对这个对象引用的线程访问。当一个线程可以访问一个对象时,它也可以访问这个对象的成员变量。如果两个线程同时调用同一个对象上的同一个方法,它们将会都访问这个对象的成员变量,但是每一个线程都拥有这个本地变量的私有拷贝。

更多关于java内存模型数据可以参考http://ifeve.com/java-memory-model-6/

线程不安全的代码

前面也说了,多线程是完成同一件任务的,那么必然存在对资源的竞争,那么在竞争的时候,对资源的不正当使用就会导致程序出现问题,例如下面这段代码

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
public class MyThread extends Thread{
protected MySalary mySalary;

protected int value;

public MyThread(MySalary mySalary,int value) {
this.mySalary = mySalary;
this.value = value;
}

@Override
public void run() {
mySalary.add(value);
}


public static void main(String[] args) throws InterruptedException {
MySalary ms = new MySalary();
MyThread t1 = new MyThread(ms,200);
MyThread t2 = new MyThread(ms,300);
t1.start();
t2.start();
Thread.sleep(1000);//主线程睡眠1秒钟,保证两个线程运行完毕
System.out.println(Thread.currentThread().getName() + " run over and salary = " + salary);
}

}

class MySalary {
public int salary = 1000;
public void add(int value) {
try {
Thread.sleep(150);
this.salary = salary + value;
//打印出当前运行的线程
System.out.println(Thread.currentThread() + " run and salary = " + salary);
} catch (InterruptedException e) {
e.printStackTrace();
}
}
}

预期结果应该打印的是1500,但是实际情况可能与预期结果不符,在本地多运行几次之后出现了这样的结果:

1
2
3
Thread-0 run over and salary = 1300
Thread-1 run over and salary = 1300
main run over and salary = 1300

这就是多线程运行导致的线程不安全的结果!

为什么会导致线程不安全

在没有同步的情况下,Java内存模型允许编译器对操作顺序进行重排序,并将数值缓存在寄存器中,此外,它还允许CPU对操作顺序进行重排序,并将数值缓存在寄存器中。所以在缺乏足够同步的多线程程序中,要想对内存的操作顺序进行判断,几乎无法得出正确的结论。

之前说了Java虚拟机对内存的划分,但是其和硬件是存在差异,在硬件级别上是不存在堆栈的划分的,压根没有这个概念。
硬件中对于内存的划分

要知道,CPU的运行速度是非常快的,CPU的运行数据是从内存(主存)来的,但是从内存中取数据对于CPU来说是非常慢的,对于一些常用的数据,CPU就做了一些缓存,所以有了CPU的缓存。而我们上面造成的多线程问题也正是由于这种”信息不对等”的情况导致的。刚才也说了硬件和JVM的运行时区并不一样,那么它们之间的关系是怎样的呢?这里同样可以用一张图来描述它们的关系
JVM与硬件的交互

这个时候就可以解释为什么会出现上面代码运行的结果了
线程不安全代码解析

synconized的作用以及使用

synconized是java中的关键字,使用它可以保证线程安全,同步块在Java中是同步在某个对象上。所有同步在一个对象上的同步块在同时只能被一个线程进入并执行操作。所有其他等待进入该同步块的线程将被阻塞,直到执行该同步块中的线程退出。
这就相当于寝室6个同学同时要上厕所,然后一起抢厕所,抢到厕所的那个人会把厕所门给关上,表示厕所有人,然后其他人只能在门外等着,等别人上完厕所了,其他人在接着抢。
那么上面线程不安全的代码可以进行部分改写

1
2
3
 synchronized (this) {
this.salary = salary + value;
}

此时,无论启动多少个线程都能够保证线程的安全了,synchronized不仅可以使用在代码块上还可以使用在方法声明上,但是要注意的是synconized是比较耗费系统资源的,所以要尽可能小范围的使用。

volitale的使用

volitale的作用是保证内存可见性,如果一个成员变量被valitale修饰,在A线程中修改了valitale变量的值,对其他线程来说该修改是可见的。
即读取valitale变量相当于进入同步代码块,写入valitale变量相当于退出同步代码块,而且读取valitaile变量的开销仅仅只比非valitale变量的开销略高一点,更远远低于synchronized.
值得注意的是volitale并不能保证count++这样操作的原子性,所以volitale并不能保证线程安全。

因此synchronized和volitale的区别是:加锁机制既可以确定可见性又可以保证原子性,而volatile变量只能确保可见性。

所以它的使用场景一般包括:确保它们自身状态的可见性,确保它们所引用状态的可见性,以及标示一些重要的程序生命周期的事件的发生(例如初始化或者关闭)。
一种典型的使用方式:检查某个判断标记判断是否退出循环

1
2
3
4
valitale boolean isExit;
while(!isExit) {
doSomething();
}

这样当isExit标志被另外一个线程修改为true的时候,执行判断的线程就能够准确的读取到isExit的值,从而退出循环。

ThreadLocal的使用

既然提到了Thread,就不得不说ThreadLocal了,先看下ThreadLocal内部实现机制:

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
public void set(T value) {
Thread t = Thread.currentThread();
//通过当前线程获取当前线程的本地变量ThreadLocalMap
ThreadLocalMap map = getMap(t);
if (map != null)
map.set(this, value);
else
createMap(t, value);
}

public T get() {
Thread t = Thread.currentThread();
ThreadLocalMap map = getMap(t);
if (map != null) {
//获取当前线程中以当前ThreadLocal实例为key的变量值
ThreadLocalMap.Entry e = map.getEntry(this);
if (e != null)
return (T)e.value;
}
//当map不存在时,设置初始值,可以通过new ThreadLocal时覆盖initialValue方法,这也是setInitialValue主要的调用方法
return setInitialValue();
}

ThreadLocalMap getMap(Thread t) {
return t.threadLocals;
}

void createMap(Thread t, T firstValue) {
t.threadLocals = new ThreadLocalMap(this, firstValue);
}

从ThreadLocal的内部实现机制分析,synchronized这种机制是提供一份变量让不同的线程排队访问,而ThreadLocal是为每个线程都提供一份变量的副本,从而实现同时访问而不受影响。
从这里也看出来了两者之间的应用场景不同,synchronized是为了让每个线程对变量的修改都对其他线程可见,而ThreadLocal是为了线程对象的数据不受其他线程影响,它最适合的场景应该是在同一线程的不同开发层次中共享数据。

安全的发布对象

我们分析了,其实线程不安全就是因为多个线程对公共资源(存在于堆中的对象)的不正当利用,那么如何保证发布的对象线程安全呢?
一个正确构造的对象可以通过以下方式来安全的发布:

  1. 在静态初始化函数中初始化一个对象引用(JVM在静态初始化的时候会保证线程安全)
  2. 将对象的引用保存到volatile类型的域或者AtomicReferance中
  3. 将对象的引用保存到某个正确构造对象的的final类型域中
  4. 将对象的引用保存到一个由锁保护的域中

Java中其他保证线程安全的方式

  1. 通过将一个键或者值放入HashTable、synchronizedMap或者ConcurrentMap中,可以安全的将它发布给任何从这些容器中访问它的线程(无论是直接访问还是通过迭代器访问)
  2. 通过将某个元素放入Vector、CopyOnWriteArrayList、CopyOnWriteArraySet、synchronizedList或者synchronizedSet中,可以将元素安全的发布到任何从这些容器中访问它的线程
  3. 通过将某个元素放入BlockingQueue或者ConcurrentLinkedQueue中,可以将元素安全的发布到任何从这些队列中访问该元素的线程

类库中的其他机制也可以实现安全发布,比如Futrue和Exchange.

写一个线程安全的单例模式

  1. 饿汉式
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    public class MyFactory {

    public static MyFactory factory = new MyFactory();

    private MyFactory() {
    }

    public static MyFactory getFactory() {
    return factory;
    }
    }

  2. 懒汉式
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    public class MyFactory {
    //这里的volitale不可或缺
    public volitale static MyFactory factory = null;

    private MyFactory() {
    }

    public static MyFactory getFactory() {
    if(factory == null){
    synchronized (MyFactory.class){
    if(factory == null){
    factory = new MyFactory();
    }
    }
    }
    return factory;
    }
    }

懒汉式使用了”双重检查锁”,在上锁之前多进行一次null检查就可以减少绝大多数的加锁操作。当然这种写法也不是唯一的,你可以使用静态内部类,还使用枚举(这种是Effective Java的推荐写法)。使用枚举除了线程安全和防止反射强行调用构造器之外(上面起到的其他方式均不能避免这种情况),还提供了自动序列化机制,防止反序列化的时候创建新的对象。

写在最后

线程安全说简单也简单,说难也难,简单就简单在控制好被线程共享的资源即可,难就难在在控制的同时保证程序运行效率以及不出现线程安全问题。当然这设计到很多方面了,本文也只是作者在了解了多线程之后写下的粗浅见接,这其中也参考了很多资料,如有错误或者遗漏,请指出。

参考资料

  1. http://www.ruanyifeng.com/blog/2013/04/processes_and_threads.html
  2. http://ifeve.com/java-memory-model-6/

重温一下MySQL,最近看了MySQL必知必会这本书,对书上一些以前模糊的地方做了一下笔记。

阅读全文 »

认识Java中的基本数据类型

Java中有8大基本数据类型,它的对应包装类型以及数据范围如下:

基本数据类型名称 封装数据类型名称 所占字节数 取值范围
boolean Boolean 1 true/false
byte Byte 1 -128~127
char Character 2 0~65535
short Short 2 -32768~32767
int Integer 4 -2的31次方到2的31次方-1
long Long 8 2的63次方到2的63次方-1
float Float 4 3.402823e+38 ~ 1.401298e-45
double Double 8 1.797693e+308~ 4.9000000e-324

具体的字节数可以通过Character.SIZE,Double.SIZE等方法进行查看所占bit位,除以8就是所占字节数。

值得一提的是Float.MIN_VALUE以及Double.MIN_VALUE以表示的是Float以及Double所能表示的最小正数,也就是说存在这样一种情况,0到±Float.MIN_VALUE之间的值float类型无法表示,0 到±Double.MIN_VALUE之间的值double类型无法表示。这并没有什么好奇怪的,因为这些范围内的数值超出了它们的精度范围。

装箱和拆箱

在编译阶段,若将原始类型int赋值给Integer类型,就会将原始类型自动编译为Integer.valueOf(int),这种叫做装箱;如果将Integer类型赋值给int类型,则会自动转换调用intValue()方法,这种叫做拆箱。

我们来看一段代码:

1
2
3
Integer i = new  Integer(42);
Integer j = new Integer(42);
System.out.println(i > j ? 1 : i == j ? 0 : -1);//-1

返回的是-1这是为什么呢? 执行i>j的时候会导致自动拆箱,也就是说直接比较它们的基本类型值,比较的结果肯定是否定的,那么接下来就会比较i==j,这里比较的是对象的引用,返回当然为false,所以最终结果为-1.

修改方案1:

1
2
3
int i2 = i;
int j2 = j;
System.out.println(i2 > j2 ? 1 : i2 == j2 ? 0 : -1);

修改方案2:

1
System.out.println(i > j ? 1 : i.intValue() == j.intValue() ? 0 : -1);

基本数据类型和封装类型的比较

先来看一段代码,关于基本数据类型和封装类型之间的比较

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
public static void main(String[] args) {
Integer i = 127;
Integer i2 = 127;

int i3 = 127;

Integer j = 128;
Integer j2 = 128;
int j3 = 128;

Integer k = new Integer(127);
Integer k2 = 127;

Integer m = -128;
int m2 = -128;

Integer q = -129;
Integer q2 = -129;
int q3 = -129;
Integer q4 = q3;

System.out.println(i == i2);//true
System.out.println(i == i3);//true
System.out.println(j == j2);//false
System.out.println(j == j3);//true
System.out.println(k == k2);//false
System.out.println(k == i3);//true
System.out.println(m == m2);//true
System.out.println(q == q2);//false
System.out.println(q == q3);//true
System.out.println(q == q4);//false
}

从上面的结果可以看出了当Integer和int比较的时候,实际上是在做拆箱操作,当Integer和
Integer进行比较的时候会发现当值的范围在-128到127之间比较的时候总是为true(k==k2这种比较特殊,因为k是new的一个对象),那么为什么会出现当值在-128在127之间的时候比较总会相等呢?是因为Integer这个类对这个区间的数据做了一个缓存,具体可以查看JDK源码:

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

private static class IntegerCache {
static final int low = -128;
static final int high;
static final Integer cache[];

static {
// high value may be configured by property
int h = 127;
String integerCacheHighPropValue =
sun.misc.VM.getSavedProperty("java.lang.Integer.IntegerCache.high");
if (integerCacheHighPropValue != null) {
int i = parseInt(integerCacheHighPropValue);
i = Math.max(i, 127);
// Maximum array size is Integer.MAX_VALUE
h = Math.min(i, Integer.MAX_VALUE - (-low));
}
high = h;

cache = new Integer[(high - low) + 1];
int j = low;
for(int k = 0; k < cache.length; k++)
cache[k] = new Integer(j++);
}

private IntegerCache() {}
}

public static Integer valueOf(int i) {
assert IntegerCache.high >= 127;
if (i >= IntegerCache.low && i <= IntegerCache.high)
return IntegerCache.cache[i + (-IntegerCache.low)];
return new Integer(i);
}

从上面的代码可以看出Integer缓存的结果区间,不错我们可以通过VM参数来修改缓存的High值。

查看源码可以看到Byte缓存了(byte)(-128127)之间的值,Character缓存了(char)(0-127)之间的值,Short缓存了(short)(-128127)之间的值,Long缓存了(long)(-128~127)之间的值

类型之间的强制转换

关于强制转换,我们同样先看一段代码:

1
2
3
4
int s = 32768;

System.out.println((short)s);//-32768

结果为什么会这样呢?因为int是4个字节,也就是32位,而short是2个字节也就是16位,其最高位需要用来表示是正数还是负数,所以其最大值只有32767。当int转换为short的时候高位就被截断了.

当一个小数据向大数据转换的时候,系统会自动将其转换而不用我们手动添加转换,这些类型由”小”到”大”分别为 (byte,short,char)–int–long–float—double。这里我们所说的”大”与”小”,并不是指占用字节的多少,而是指表示值的范围的大小。

比如以下的代码都是编译通过的

1
2
3
4
5
6
byte b = 12;
int i = b;
long lon = i;
float f = lon;
double d = f;

如果低级类型为char型,向高级类型(整型)转换时,会转换为对应ASCII码值,例如

1
2
3
4
char c='c'; int i=c;

System.out.println("output:"+i);//output:99

对于byte,short,char三种类型而言,他们是平级的,因此不能相互自动转换,可以使用下述的强制类型转换。

1
2
short i=99 ; char c=(char)i; 
System.out.println("output:"+c);//output:c

所以,高等级的数据类型转换为低等级的数据类型的时候一定要慎重,尽量不去做这种操作

最近好久没有写博客了,主要是最近太忙,一直都在加班,所以没有时间写博客,但是这个博客还是耗费了自己比较大的心血的,从备案到组建,一路过来一路心酸呀,工作一年以来自己进步也蛮大,处理事情的能力也得到了一定的提高,之前接触到的工作使用比较多的是Infobright,由于工作内容涉及到了大量的查询,也不使用事务,所用我们的数据库使用的最多的就是MyIsam以及Brighthouse引擎,但是Infobright的资料比较少,现在就针对自己在这过程中遇到的问题进行一个总结。

阅读全文 »

java中float以及double都是属于浮点数,那么它们在计算机中的表现形式是如何的呢?还是如同整数那样使用进行表示吗?那么小数位应该如何表示呢?正负又是怎样表示的呢?...

阅读全文 »

二进制表示法

在计算机的世界里,只有0和1,我们也通常使用0和1来表示数字,比如8在计算机(8位机)中的二进制表示法就是00001000,那么负数呢?在计算机中如何表示负数呢?

大学学习过的计算机原理中告诉我们负数在计算机中是使用补码(维基百科中也叫作二补数)进行表示的,那么介绍这种表示方法之前想要介绍几个概念。

原码:将一个整数,转换成二进制,就是其原码。如单字节的5的原码为:0000 0101;-5的原码为1000 0101

反码:正数的反码就是其原码;负数的反码是将原码中,除符号位以外,每一位取反。如单字节的5的反码为:0000 0101;-5的原码为1111 1010。

补码:正数的补码就是其原码;负数的反码+1就是补码。如单字节的5的补码为:0000 0101;-5的补码为1111 1011。

1
2
3
4
5
System.out.println(Integer.toBinaryString(-5));
System.out.println(Integer.toBinaryString(5));
//在我的计算机中的输出结果为:
-5的二进制为:11111111 11111111 11111111 11111011
5的二进制为: 101

为什么要使用补码表示负数?
假设我们不使用补码,而仍然采用高位标示符号位的方法,那么-5我们可以表示为1000 0101,那么我们来做一个加法。看看8+(-5)=3是否可以正确实现。

1
2
3
4
0 0 0 0 1 0 0 0
1 0 0 0 0 1 0 1
------------------
1 0 0 0 1 1 0 1

最终的结果是-13,很明显这个结果并不是我们想要的,难道需要为正数和负数相加设计一套新的电路?

现在我们来看看使用补码的方式,同样运行8+(-5)=3

1
2
3
4
5
6
  0 0 0 0 1 0 0 0
1 1 1 1 1 0 1 1
------------------
0 0 0 0 0 0 0 1 1 此时产生了溢出位,将被自动舍弃。

[1000 0101(-5源码)------>1111 1010(-5反码,除符号位之外取反)----->1111 1011(补码)]

此时,得到的就是我们想要的结果3了。

补码的本质

在回答补码为什么能正确实现加法运算之前,我们先看看它的本质,也就是那两个步骤的转换方法是怎么来的。
要将正数转成对应的负数,其实只要用0减去这个数就可以了。比如,-8其实就是0-8。
已知8的二进制是00001000,-8就可以用下面的式子求出:

1
2
3
 0 0 0 0 0 0 0 0 
0 0 0 00 0 0
---------

因为00000000(被减数)小于0000100(减数),所以不够减。请回忆一下小学算术,如果被减数的某一位小于减数,我们怎么办?很简单,问上一位借1就可以了。

所以,0000000也问上一位借了1,也就是说,被减数其实是100000000,算式也就改写成:

1
2
3
4
1 0 0 0 0 0 0 0 0 
0 0 0 0 1 0 0 0
---------
 1 1 1 1 1 0 0 0

进一步观察,可以发现100000000 = 11111111 + 1,所以上面的式子可以拆成两个:

1
2
3
4
5
6
7
 1 1 1 1 1 1 1 1
0 0 0 0 1 0 0 0
---------
 1 1 1 1 0 1 1 1
0 0 0 0 0 0 0 1
---------
 1 1 1 1 1 0 0 0

2的补码的两个转换步骤就是这么来的。

为什么正数加法适用补码?

实际上,我们要证明的是,X-Y或X+(-Y)可以用X加上Y的2的补码完成。
Y的2的补码等于(11111111-Y)+1。所以,X加上Y的2的补码,就等于X + (11111111-Y) + 1,我们假定这个算式的结果等于Z,即 Z = X + (11111111-Y) + 1

那么Z = X - Y + 100000000 = X-Y(100000000在8位机中产生了溢出),这就证明了可以使用补码来进行负数的加法运算

关于补码的其他说明

  1. 二进制补码
  2. 二补数–维基百科

工作中有遇到mysql优化的问题,当时查找了很多资料,直到看到了这篇文章对mysql的慢查询以及优化有了更加深入的认识。

以下的文章内容均为转载,可以到http://tech.meituan.com/mysql-index.html查看原文。

MySQL凭借着出色的性能、低廉的成本、丰富的资源,已经成为绝大多数互联网公司的首选关系型数据库。虽然性能出色,但所谓“好马配好鞍”,如何能够更好的使用它,已经成为开发工程师的必修课,我们经常会从职位描述上看到诸如“精通MySQL”、“SQL语句优化”、“了解数据库原理”等要求。我们知道一般的应用系统,读写比例在10:1左右,而且插入操作和一般的更新操作很少出现性能问题,遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,所以查询语句的优化显然是重中之重。
本人从13年7月份起,一直在美团核心业务系统部做慢查询的优化工作,共计十余个系统,累计解决和积累了上百个慢查询案例。随着业务的复杂性提升,遇到的问题千奇百怪,五花八门,匪夷所思。本文旨在以开发工程师的角度来解释数据库索引的原理和如何优化慢查询。

一个慢查询引发的思考

1
2
3
4
5
6
7
8
9
10
select
count(*)
from
task
where
status=2
and operator_id=20839
and operate_time>1371169729
and operate_time<1371174603
and type=2;

系统使用者反应有一个功能越来越慢,于是工程师找到了上面的SQL。
并且兴致冲冲的找到了我.
“这个SQL需要优化,给我把每个字段都加上索引”
我很惊讶,问道“为什么需要每个字段都加上索引?”
“把查询的字段都加上索引会更快”工程师信心满满
“这种情况完全可以建一个联合索引,因为是最左前缀匹配,所以operate_time需要放到最后,而且还需要把其他相关的查询都拿来,需要做一个综合评估。”
“联合索引?最左前缀匹配?综合评估?”工程师不禁陷入了沉思。
多数情况下,我们知道索引能够提高查询效率,但应该如何建立索引?索引的顺序如何?许多人却只知道大概。其实理解这些概念并不难,而且索引的原理远没有想象的那么复杂。

MySQL索引原理

索引目的

索引的目的在于提高查询效率,可以类比字典,如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从下往下找到y字母,再找到剩下的sql。如果没有索引,那么你可能需要把所有单词看一遍才能找到你想要的,如果我想找到m开头的单词呢?或者ze开头的单词呢?是不是觉得如果没有索引,这个事情根本无法完成?

索引原理

除了词典,生活中随处可见索引的例子,如火车站的车次表、图书的目录等。它们的原理都是一样的,通过不断的缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样,但显然要复杂许多,因为不仅面临着等值查询,还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢?我们回想字典的例子,能不能把数据分成段,然后分段查询呢?
最简单的如果1000条数据,1到100分成第一段,101到200分成第二段,201到300分成第三段……这样查第250条数据,只要找第三段就可以了,一下子去除了90%的无效数据。
但如果是1千万的记录呢,分成几段比较好?稍有算法基础的同学会想到搜索树,其平均复杂度是lgN,具有不错的查询性能。但这里我们忽略了一个关键的问题,复杂度模型是基于每次相同的操作成本来考虑的,数据库实现比较复杂,数据保存在磁盘上,而为了提高性能,每次又可以把部分数据读入内存来计算,因为我们知道访问磁盘的成本大概是访问内存的十万倍左右,所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘,那么这里先简单介绍一下磁盘IO和预读,磁盘读取数据靠的是机械运动,每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分,寻道时间指的是磁臂移动到指定磁道所需要的时间,主流磁盘一般在5ms以下;旋转延迟就是我们经常听说的磁盘转速,比如一个磁盘7200转,表示每分钟能转7200次,也就是说1秒钟能转120次,旋转延迟就是1/120/2 = 4.17ms。
传输时间指的是从磁盘读出或将数据写入磁盘的时间,一般在零点几毫秒,相对于前两个时间可以忽略不计。那么访问一次磁盘的时间,即一次磁盘IO的时间约等于5+4.17 = 9ms左右,听起来还挺不错的,但要知道一台500 -MIPS的机器每秒可以执行5亿条指令,因为指令依靠的是电的性质,换句话说执行一次IO的时间可以执行40万条指令,数据库动辄十万百万乃至千万级数据,每次9毫秒的时间,显然是个灾难。下图是计算机硬件延迟的对比图,供大家参考:
系统硬件对比图

考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,而是把相邻的数据也都读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

索引的数据结构

前面讲了生活中索引的例子,索引的基本原理,数据库的复杂性,又讲了操作系统的相关知识,目的就是让大家了解,任何一种数据结构都不是凭空产生的,一定会有它的背景和使用场景,我们现在总结一下,我们需要这种数据结构能够做些什么,其实很简单,那就是:每次查找数据时把磁盘IO次数控制在一个很小的数量级,最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢?就这样,b+树应运而生。

详解b+树

B+树
如上图,是一颗b+树,关于b+树的定义可以参见B+树,这里只说一些重点,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示,如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

b+树性质

  1. 通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

  2. 当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

慢查询优化

关于MySQL索引原理是比较枯燥的东西,大家只需要有一个感性的认识,并不需要理解得非常透彻和深入。我们回头来看看一开始我们说的慢查询,了解完索引原理之后,大家是不是有什么想法呢?先总结一下索引的几大基本原则

建索引的几大原则

  1. 最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。
  2. =和in可以乱序,比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式
  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录
  4. 索引列不能参与计算,保持列“干净”,比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
  5. 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

回到开始的慢查询

根据最左匹配原则,最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引;其中status、operator_id、type的顺序可以颠倒,所以我才会说,把这个表的所有相关查询都找到,会综合分析;
比如还有如下查询

1
2
select * from task where status = 0 and type = 12 limit 10;
select count(*) from task where status = 0 ;

那么索引建立成(status,type,operator_id,operate_time)就是非常正确的,因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则

查询优化神器 - explain命令

关于explain命令相信大家并不陌生,具体用法和字段含义可以参考官网explain-output官方参考文档,这里需要强调rows是核心指标,绝大部分rows小的语句执行一定很快(有例外,下面会讲到)。所以优化语句基本上都是在优化rows。

慢查询优化基本步骤

  1. 先运行看看是否真的很慢,注意设置SQL_NO_CACHE
  2. where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
  3. explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  4. order by limit 形式的sql语句让排序的表优先查
  5. 了解业务方使用场景
  6. 加索引时参照建索引的几大原则
  7. 观察结果,不符合预期继续从1分析

几个慢查询案例

下面几个例子详细解释了如何分析和优化慢查询

复杂语句写法

很多情况下,我们写SQL只是为了实现功能,这只是第一步,不同的语句书写方式对于效率往往有本质的差别,这要求我们对mysql的执行计划和索引原则有非常清楚的认识,请看下面的语句

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
select
distinct cert.emp_id
from
cm_log cl
inner join
(
select
emp.id as emp_id,
emp_cert.id as cert_id
from
employee emp
left join
emp_certificate emp_cert
on emp.id = emp_cert.emp_id
where
emp.is_deleted=0
) cert
on (
cl.ref_table='Employee'
and cl.ref_oid= cert.emp_id
)
or (
cl.ref_table='EmpCertificate'
and cl.ref_oid= cert.cert_id
)
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00';

先运行一下,53条记录 1.87秒,又没有用聚合语句,比较慢

1
53 rows in set (1.87 sec)
  1. explain
1
2
3
4
5
6
7
8
9
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+--------------------------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where; Using temporary |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 63727 | Using where; Using join buffer |
| 2 | DERIVED | emp | ALL | NULL | NULL | NULL | NULL | 13317 | Using where |
| 2 | DERIVED | emp_cert | ref | emp_certificate_empid | emp_certificate_empid | 4 | meituanorg.emp.id | 1 | Using index |
+----+-------------+------------+-------+---------------------------------+-----------------------+---------+-------------------+-------+---------
-----------------------+

简述一下执行计划,首先mysql根据idx_last_upd_date索引扫描cm_log表获得379条记录;然后查表扫描了63727条记录,分为两部分,derived表示构造表,也就是不存在的表,可以简单理解成是一个语句形成的结果集,后面的数字表示语句的ID。

derived2表示的是ID = 2的查询构造了虚拟表,并且返回了63727条记录。

我们再来看看ID = 2的语句究竟做了写什么返回了这么大量的数据,首先全表扫描employee表13317条记录,然后根据索引emp_certificate_empid关联emp_certificate表,rows = 1表示,每个关联都只锁定了一条记录,效率比较高。获得后,再和cm_log的379条记录根据规则关联。从执行过程上可以看出返回了太多的数据,返回的数据绝大部分cm_log都用不到,因为cm_log只锁定了379条记录。

如何优化呢?可以看到我们在运行完后还是要和cm_log做join,那么我们能不能之前和cm_log做join呢?仔细分析语句不难发现,其基本思想是如果cm_log的ref_table是EmpCertificate就关联emp_certificate表,如果ref_table是Employee就关联employee表,我们完全可以拆成两部分,并用union连接起来,注意这里用union,而不用union all是因为原语句有“distinct”来得到唯一的记录,而union恰好具备了这种功能。
如果原语句中没有distinct不需要去重,我们就可以直接使用union all了,因为使用union需要去重的动作,会影响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

select
emp.id
from
cm_log cl
inner join
employee emp
on cl.ref_table = 'Employee'
and cl.ref_oid = emp.id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0
union
select
emp.id
from
cm_log cl
inner join
emp_certificate ec
on cl.ref_table = 'EmpCertificate'
and cl.ref_oid = ec.id
inner join
employee emp
on emp.id = ec.emp_id
where
cl.last_upd_date >='2013-11-07 15:03:00'
and cl.last_upd_date<='2013-11-08 16:00:00'
and emp.is_deleted = 0

  1. 不需要了解业务场景,只需要改造的语句和改造之前的语句保持结果一致

  2. 现有索引可以满足,不需要建索引

  3. 用改造后的语句实验一下,只需要10ms 降低了近200倍!

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

+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
| 1 | PRIMARY | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 1 | PRIMARY | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | Using where |
| 2 | UNION | cl | range | cm_log_cls_id,idx_last_upd_date | idx_last_upd_date | 8 | NULL | 379 | Using where |
| 2 | UNION | ec | eq_ref | PRIMARY,emp_certificate_empid | PRIMARY | 4 | meituanorg.cl.ref_oid | 1 | |
| 2 | UNION | emp | eq_ref | PRIMARY | PRIMARY | 4 | meituanorg.ec.emp_id | 1 | Using where |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+--------+---------------------------------+-------------------+---------+-----------------------+------+-------------+
53 rows in set (0.01 sec)

明确应用场景

举这个例子的目的在于颠覆我们对列的区分度的认知,一般上我们认为区分度越高的列,越容易锁定更少的记录,但在一些特殊的情况下,这种理论是有局限性的

1
2
3
4
5
6
7
8
9
10
11
select
*
from
stage_poi sp
where
sp.accurate_result=1
and (
sp.sync_status=0
or sp.sync_status=2
or sp.sync_status=4
);
  1. 先看看运行多长时间,951条数据6.22秒,真的很慢
1
951 rows in set (6.22 sec)
  1. 先explain,rows达到了361万,type = ALL表明是全表扫描

    1
    2
    3
    4
    5
    +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
    | 1 | SIMPLE | sp | ALL | NULL | NULL | NULL | NULL | 3613155 | Using where |
    +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
  2. 所有字段都应用查询返回记录数,因为是单表查询 0已经做过了951条

  3. 让explain的rows 尽量逼近951

看一下accurate_result = 1的记录数

1
select count(*),accurate_result from stage_poi  group by accurate_result;
1
2
3
4
5
6
7
8
9

+----------+-----------------+
| count(\*) | accurate_result |
+----------+-----------------+
| 1023 | -1 |
| 2114655 | 0 |
| 972815 | 1 |
+----------+-----------------+

我们看到accurate_result这个字段的区分度非常低,整个表只有-1,0,1三个值,加上索引也无法锁定特别少量的数据

再看一下sync_status字段的情况

1
select count(*),sync_status from stage_poi  group by sync_status;
1
2
3
4
5
6
7
8

+----------+-------------+
| count(\*) | sync_status |
+----------+-------------+
| 3080 | 0 |
| 3085413 | 3 |
+----------+-------------+

同样的区分度也很低,根据理论,也不适合建立索引

问题分析到这,好像得出了这个表无法优化的结论,两个列的区分度都很低,即便加上索引也只能适应这种情况,很难做普遍性的优化,比如当sync_status 0、3分布的很平均,那么锁定记录也是百万级别的

  1. 找业务方去沟通,看看使用场景。业务方是这么来使用这个SQL语句的,每隔五分钟会扫描符合条件的数据,处理完成后把sync_status这个字段变成1,五分钟符合条件的记录数并不会太多,1000个左右。了解了业务方的使用场景后,优化这个SQL就变得简单了,因为业务方保证了数据的不平衡,如果加上索引可以过滤掉绝大部分不需要的数据

  2. 根据建立索引规则,使用如下语句建立索引

1
alter table stage_poi add index idx_acc_status(accurate_result,sync_status);
  1. 观察预期结果,发现只需要200ms,快了30多倍。
1
952 rows in set (0.20 sec)

我们再来回顾一下分析问题的过程,单表查询相对来说比较好优化,大部分时候只需要把where条件里面的字段依照规则加上索引就好,如果只是这种“无脑”优化的话,显然一些区分度非常低的列,不应该加索引的列也会被加上索引,这样会对插入、更新性能造成严重的影响,同时也有可能影响其它的查询语句。所以我们第4步调差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
31
32
33
34
35
36
37
select
c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
inner join
contact_branch cb
on c.id = cb.contact_id
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
order by
c.created_time desc limit 0 ,
10;

还是几个步骤

  1. 先看语句运行多长时间,10条记录用了13秒,已经不可忍受

  2. 1
    10 rows in set (13.06 sec)
  3. explain

1
2
3
4
5
6
7
8
9
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+
| 1 | SIMPLE | oei | ref | idx_category_left_right,idx_data_id | idx_category_left_right | 5 | const | 8849 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | bu | ref | PRIMARY,idx_userid_status | idx_userid_status | 4 | meituancrm.oei.data_id | 76 | Using where; Using index |
| 1 | SIMPLE | cb | ref | idx_branch_id,idx_contact_branch_id | idx_branch_id | 4 | meituancrm.bu.branch_id | 1 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 108 | meituancrm.cb.contact_id | 1 | |
+----+-------------+-------+--------+-------------------------------------+-------------------------+---------+--------------------------+------+----------------------------------------------+

从执行计划上看,mysql先查org_emp_info表扫描8849记录,再用索引idx_userid_status关联branch_user表,再用索引idx_branch_id关联contact_branch表,最后主键关联contact表。
rows返回的都非常少,看不到有什么异常情况。我们在看一下语句,发现后面有order by + limit组合,会不会是排序量太大搞的?于是我们简化SQL,去掉后面的order by 和 limit,看看到底用了多少记录来排序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select
count(*)
from
contact c
inner join
contact_branch cb
on c.id = cb.contact_id
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
1
2
3
4
5
6
7
+----------+
| count(\*) |
+----------+
| 778878 |
+----------+

1 row in set (5.19 sec)

发现排序之前居然锁定了778878条记录,如果针对70万的结果集排序,将是灾难性的,怪不得这么慢,那我们能不能换个思路,先根据contact的created_time排序,再来join会不会比较快呢?
于是改造成下面的语句,也可以用straight_join来优化

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
select
c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (1,2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 10802
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,10;

验证一下效果 预计在1ms内,提升了13000多倍!

1
10 rows in set (0.00 sec)

本以为至此大工告成,但我们在前面的分析中漏了一个细节,先排序再join和先join再排序理论上开销是一样的,为何提升这么多是因为有一个limit!大致执行过程是:mysql先按索引排序得到前10条记录,然后再去join过滤,当发现不够10条的时候,再次去10条,再次join,这显然在内层join过滤的数据非常多的时候,将是灾难的,极端情况,内层一条数据都找不到,mysql还傻乎乎的每次取10条,几乎遍历了这个数据表!
用不同参数的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
31
32
33
34
35
36
37
38
39
40
41
42
43
select
sql_no_cache c.id,
c.name,
c.position,
c.sex,
c.phone,
c.office_phone,
c.feature_info,
c.birthday,
c.creator_id,
c.is_keyperson,
c.giveup_reason,
c.status,
c.data_source,
from_unixtime(c.created_time) as created_time,
from_unixtime(c.last_modified) as last_modified,
c.last_modified_user_id
from
contact c
where
exists (
select
1
from
contact_branch cb
inner join
branch_user bu
on cb.branch_id = bu.branch_id
and bu.status in (
1,
2)
inner join
org_emp_info oei
on oei.data_id = bu.user_id
and oei.node_left >= 2875
and oei.node_right <= 2875
and oei.org_category = - 1
where
c.id = cb.contact_id
)
order by
c.created_time desc limit 0 ,
10;
1
Empty set (2 min 18.99 sec)

2 min 18.99 sec!比之前的情况还糟糕很多。由于mysql的nested loop机制,遇到这种情况,基本是无法优化的。这条语句最终也只能交给应用系统去优化自己的逻辑了。
通过这个例子我们可以看到,并不是所有语句都能优化,而往往我们优化时,由于SQL用例回归时落掉一些极端情况,会造成比原来还严重的后果。所以,第一:不要指望所有语句都能通过SQL优化,第二:不要过于自信,只针对具体case来优化,而忽略了更复杂的情况。

慢查询的案例就分析到这儿,以上只是一些比较典型的案例。我们在优化过程中遇到过超过1000行,涉及到16个表join的“垃圾SQL”,也遇到过线上线下数据库差异导致应用直接被慢查询拖死,也遇到过varchar等值比较没有写单引号,还遇到过笛卡尔积查询直接把从库搞死。再多的案例其实也只是一些经验的积累,如果我们熟悉查询优化器、索引的内部原理,那么分析这些案例就变得特别简单了。

写在后面的话

本文以一个慢查询案例引入了MySQL索引原理、优化慢查询的一些方法论;并针对遇到的典型案例做了详细的分析。其实做了这么长时间的语句优化后才发现,任何数据库层面的优化都抵不上应用系统的优化,同样是MySQL,可以用来支撑Google/FaceBook/Taobao应用,但可能连你的个人网站都撑不住。套用最近比较流行的话:“查询容易,优化不易,且写且珍惜!”

仅仅凭借这么一个例子就能写出漂亮的sql语句是不够的,平时自己还需要多多练习,在此推荐两本书籍:

  1. <<必知必会Mysql>>
  2. <<高性能Mysql>>

SerializerFeature在fastjson中扮演着一个极其重要的角色,通过它我们可以定制序列化,那么它本身是如何实现的呢?如此的设计有有何出彩之处呢?本篇文章将会重点围绕这些要素进行分析。

阅读全文 »