1.程序计数器

程序计数器是一块很小的内存空间，代表当前线程执行的字节码的行号指示器。
Java虚拟机的多线程是通过线程轮流切换并分配处理器执行时间的方式来实现的，在任何一个确定的时刻，一个处理器（对于多核处理器来说是一个内核）都只会执行一条线程中的指令。
为了线程切换后恢复到正常的执行位置，每条线程都需要一个独立的程序计数器，各线程之间计数器互不影响，独立存储，我们称这类内存区域为“线程私有”的内存。

此内存区域是唯一一个在Java虚拟机规范中没有规定任何OutOfMemoryError情况的区域。

2.Java虚拟机栈

Java虚拟机栈也是线程私有的，它的生命周期与线程相同。
虚拟机栈描述的是Java方法执行的内存模型。
每个方法在执行的同时都会创建一个栈帧，用于存储局部变量表，操作数栈，动态链接，方法出口等信息。
每一个方法从调用直至执行完成的过程，就对应着一个栈帧在虚拟机栈中入栈到出栈的过程。

局部变量表存放了编译器可知的各种基本数据类型（boolean、byte、char、short、int、float、long、double），对象引用（reference类型，它不等同与对象本身，可能是一个指向对象起始地址的引用指针，也可能是指向一个代表对象的句柄或其他与此对象相关的位置）和returnAddress类型（指向了一条字节码指令的地址）。

局部变量表所需的内存空间在编译期间完成分配，当进入一个方法时，这个方法需要在帧中分配多大的局部变量空间是完全确定的，在方法运行期间不会改变局部变量表的大小。

• 如果线程请求的栈深度大于虚拟机所允许的深度，将抛出StackOverflowError异常；

​ 在单线程中，使用-Xss参数减少栈内存容量或者定义大量的本地变量，增大此方法帧中本地变量表的长度。结果将抛出StackOverflowError异常，异常出现时输出的堆栈深度响应缩小。

虚拟机栈StackOverflowError异常

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

/**
 * VM args: -Xss128k
 */
public class JavaVMStackSOF {
 	private int stackLength = 1;
 	public void stackLeak() {
 		stackLength++;
 		stackLeak();
 	}
 	public static void main(String[] args) throws Throwable {
 		JavaVmStackSOF sof = new JavaVmStackSOF();
 		try {
 			sof.stackLeak();
 		} catch (Throwable e) {
 			System.out.println("stack length:" + sof.stackLength);
 			throw e;			
 		}
 	}
}

• 如果虚拟机栈可以动态扩展，如果扩展时无法申请到足够的内存，将会抛出OutOfMemoryError异常。

​ 通过不断建立线程的方式会产生内存溢出异常，因为虚拟机栈是线程私有的，不断创建线程需要不断的分配虚拟机栈内存。每个线程分配到的栈容量越大，可以建立的线程数量自然就越少，建立线程时越容易把剩下的内存耗尽。如果是建立过多的线程导致的内存溢出，在不能减少线程数或者更换64位虚拟机的情况下，就只能通过减少最大堆（-Xmx）和减少栈容量（-Xss）来换取更多的线程。

创建线程导致内存溢出异常

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

/**
 * VM args: -Xms20m -Xmx 20m -XX:+HeapDumpOnOutOfMemoryError
 */
public class JavaVMStackOOM {
    private void dontStop() {
        while (true) {}
    }
    public void stackLeakByThread() {
        while (true) {
            Thread thread = new Thread(new Runable() {
                @Override
                public void run() {
                    dontStop();
                }
            });
            thread.start();
        }
    }
    public static void main(String[] args) throws Throwable {
        JavaVMStackOOM oom = new JavaVMStackOOM();
        oom.stackLeakByThread();
    }
}

3.本地方法栈

虚拟机栈为Java方法（也就是字节码）服务，而本地方法栈则为虚拟机使用到的Native方法服务。
在虚拟机规范中对本地方法栈中方法使用的语言、使用方式与数据结构并没有强制规定，因此具体的虚拟机可以自由实现它。
与虚拟机栈一样，本地方法栈区域也会抛出StackOverflowError和OutOfMemory异常。

4.Java堆

对于大多数应用来说，Java堆是Java虚拟机所管理的内存中最大的一块。Java堆是被所有线程共享的一块内存区域，在虚拟机启动是创建。
此内存区域的唯一目的就是存放对象实例，几乎所有的对象实例都要在这里分配内存。
Java堆是垃圾收集器管理的主要区域，因此很多时候也被称作“GC堆”（Garbage Collected Heap）。
Java堆可以处于物理上不连续的内存空间，只要逻辑上是连续的即可，就像我们的磁盘空间一样。
在实现时，既可以实现成固定大小的，也可以是可扩展的，不过当前主流的虚拟机都是按照可扩展来实现的（通过-Xmx和-Xms控制）。
如果再堆中没有内存进行实例分配，并且堆也无法再扩展时，将会抛出OutOfMemoryError异常。

Java堆用于存储对象实例，只要不断创建对象，并且保证GC Roots到对象之间有可达路径来避免垃圾回收机制清楚这些对象，那么在对象数量到达最大堆的容量限制后就会产生内存溢出异常。

Java堆内存溢出异常测试

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

/**
 * VM args: -Xms20m -Xmx 20m -XX:+HeapDumpOnOutOfMemoryError
 */
public class HeapOOM {
 	static class OOMObject {}
 	public static void main(String[] args) {
 		List<OOMObject> list = new ArrayList<>();
 		while (true) {
 			list.add(new OOMObjects());
 		}
 	}
}

5.方法区

方法区和Java堆一样，是各个线程共享的内存区域，它用于存储已被虚拟机加载的类信息、常量、静态变量、即使编译器编译后的代码等数据。
当方法区无法满足内存分配需求时，将抛出OutOfMemoryError异常。

方法区用于存放Class的相关信息，如类名、访问修饰符、常量池、字段描述、方法描述等等。在此借助CGLib直接操作字节码运行时生成大量的动态类，填满方法区，使其内存溢出。

借助CGLib使方法区出现内存溢出异常

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

/**
 * VM args: -XX:PermSize=10M -XX:MaxPermSize=10M
 */
public class JavaMethodAreaOOM {
	static class OOMObject {}
    public static void main(String[] args) {
        while (true) {
            Enhancer enhancer = new Enhancer();
            enhancer.setSuperclass(OOMObject.class);
            enhancer.setUseCache(false);
            enhancer.setCallback(new MethodInterceptor() {
                public Object intercept(Object obj, Method method, Object[] args, MethodProxy proxy) throws Throwable {
                    retuen proxy.invokeSuper(obj, args);
                }
            });
            enhancer.create;
        }
    }
}

6.运行时常量池

运行时常量池是方法区的一部分。
Class文件中除了有类的版本、字段、方法、接口等描述信息外，还有一项信息是常量池，用于存放编译期生成的各种字面量和符号引用，这部分内容将在类加载后进入方法区的运行时常量池中存放。

字面量：文本字符串、声明为final的常量值等；
符号引用：类和接口的完全限定名、字段的名称和描述符、方法的名称和描述符

运行时常量池相对于Class文件常量池的另外一个重要特征是具备动态性，运行期间可以将新的常量放入池中（String类的intern()方法）
当常量池无法再申请到内存时会抛出OutOfMemoryError异常。

运行时常量池导致的内存溢出异常

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

/**
 * VM args: -XX:PermSize=10M -XX:MaxPermSize=10M
 */
public class RuntimeConstantPoolOOM {
    public static void main(String[] args) {
    	// 使用List保持着常量池引用，避免Full GC回收常量池行为
        List<String> list = new ArrayList<>();
        // 10M的PermSize在interger范围内足够产生OOM了
        int i = 0;
        while (true) {
            list.add(String.valuOf(i++).intern());
        }
    }
}

常量池的好处：
常量池是为了避免频繁的创建和销毁对象而影响系统性能，其实现了对象的共享。
例如字符串常量池，在编译阶段就把所有的字符串文字放到一个常量池中。
1.节省内存空间：常量池中所有相同的字符串常量被合并，只占用一个空间。
2.节省运行时间：比较字符串时，==比equals()快。对于两个引用变量，只用==判断引用是否相等，也就可以判断实际值是否相等。

双等号==的含义：
基本数据类型之间应用双等号，比较的是他们的数值。
引用类型之间应用双等号，比较的是他们在内存中的存放地址。

关于常量池的扩展：https://www.jianshu.com/p/c7f47de2ee80

7.直接内存

直接内存并不是虚拟机运行时数据区的一部分，也不是Java虚拟机规范中定义的内存区域。但是这部分内存也被频繁地使用，而且也可能导致OutOfMemoryError异常出现。

使用Unsafe分配本机内存

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

/**
 * VM args: -Xmx20M -XX:MaxDirectMemorySize=10M
 */
public class DirectMemoryOOM {
    private static final int _1MB = 1024 * 1024;
    public static void main(String[] args) throws Exception {
        Field unsafeField = Unsafe.class.getDeclaredFields()[0];
        unsafeField.setAccessible(true);
        Unsafe unsafe = (Unsafe) unsafeField.get(null);
        while (true) {
            unsafe.allocateMemory(_1MB);
        }
    }
}

参考文献：《深入理解Java虚拟机》（第2版）周志明 著

​ Java虚拟机规范中试图定义一种Java内存模型（JMM）来屏蔽掉各种硬件和操作系统的内存访问差异，以实现让Java程序在各种平台下都能达到一致的内存访问效果。

​ Java内存模型的主要目标是定义程序中各个变量的访问规则，即在虚拟机中将变量存储到内存和从内存中取出变量这样的底层细节。此处的变量与Java编程中所说的变量有所区别，它包括实例字段、静态字段和构成数组对象的元素，但不包括局部变量与方法参数，因为后者是线程私有的，不会被共享，自然就不会存在竞争问题。

​ Java内存模型规定了所有的变量都存储在主内存中（虚拟机内存的一部分）。每条线程还有自己的工作内存，线程的工作内存中保存了被该线程使用到的变量的主内存副本拷贝，线程对变量的所有操作（读取、赋值等）都必须在工作内存中进行，而不能直接读写主内存中的变量。不同的线程之间也无法访问对方工作内存的变量，线程间变量值的传递均需要通过主内存来完成，线程、主内存、工作内存三者的交互关系如下图所示。

​ 关于主内存与工作内存之间具体的交互协议，Java内存模型中定义了以下8中操作来完成，虚拟机实现时必须保证下面提及的每一种操作都是原子的、不可再分的。

1.lock(锁定)：作用于主内存的变量，把一个变量标记为一条线程独占状态。

2.unlock(解锁)：作用于主内存的变量，把一个处于锁定状态的变量释放出来，释放后的变量才可以被其他线程锁定。

3.read(读取)：作用于主内存的变量，把一个变量值从主内存传输到线程的工作内存中，以便随后的load动作使用。

4.load(载入)：作用于工作内存的变量，它把read操作从主内存中得到的变量值放入工作内存的变量副本中。

5.use(使用)：作用于工作内存的变量，把工作内存中的一个变量值传递给执行引擎。

6.assign(赋值)：作用于工作内存的变量，它把一个从执行引擎接收到的值赋给工作内存的变量。

7.store(存储)：作用于工作内存的变量，把工作内存中的一个变量的值传送到主内存中，以便随后的write的操作。

8.write(写入)：作用于工作内存的变量，它把store操作从工作内存中的一个变量的值传送到主内存的变量中。

​ Java内存模型还规定了在执行上述8种基本操作时必须满足如下规则：

1.不允许read和load、store和write操作之一单独出现，以上两个操作必须按顺序执行，但没有保证必须连续执行，也就是说，read与load之间、store与write之间是可插入其他指令的。

2.不允许一个线程丢弃它的最近的assign操作，即变量在工作内存中改变了之后必须把该变化同步回主内存。

3.不允许一个线程无原因地（没有发生过任何assign操作）把数据从线程的工作内存同步回主内存中。

4.一个新的变量只能从主内存中“诞生”，不允许在工作内存中直接使用一个未被初始化（load或assign）的变量，换句话说就是对一个变量实施use和store操作之前，必须先执行过了assign和load操作。

5.一个变量在同一个时刻只允许一条线程对其执行lock操作，但lock操作可以被同一个条线程重复执行多次，多次执行lock后，只有执行相同次数的unlock操作，变量才会被解锁。

6.如果对一个变量执行lock操作，将会清空工作内存中此变量的值，在执行引擎使用这个变量前，需要重新执行load或assign操作初始化变量的值。

7.如果一个变量实现没有被lock操作锁定，则不允许对它执行unlock操作，也不允许去unlock一个被其他线程锁定的变量。

8.对一个变量执行unlock操作之前，必须先把此变量同步回主内存（执行store、write操作）。

参考文献：《深入理解Java虚拟机》（第2版）周志明 著

简介

　　索引是存储引擎用于快速找到记录的一种数据结构。

　　索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。当数据量较少且负载较低时，不恰当的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能则会急剧下降。

　　索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。创建一个真正“最优”的索引经常需要重写查询。

一、索引基础

　　MySQL的索引类似于书籍对应了页码的目录。

　　索引可以包含一个或多个列的值。如果索引包含多个列，name列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引的大不相同的。

索引的类型

1.B-Tree索引

​ 当人们讨论索引的时候如果没有特别指明类型，那么多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。

　　B-Tree对索引列是顺序组织存储的，索引很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像“找出所有以I到K开头的名字”这样的查找效率是会常高。

假如有如下数据表：

　　CREATE TABLE people(

　　　　last_name　varchar(50) not null,

　　　　first_naem　varchar(50) not null,

　　　　dob　　　　date　　　　not null,

　　　　gender 　　 enum(‘m’,’f’) not null,

　　　　key(last_name, first_naem, dob)

　　);

对于表中的每一行数据，索引中包含了last_name, first_name和dob列的值。

B-Tree索引适合于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。

WHERE条件中查询条件可任意顺序，MySQl的查询优化器会优化到匹配最佳索引。

前面所述的索引对如下类型的查询有效。

全值匹配：

　　select * from people where last_name = ‘Allen’ and first_name = ‘Cuba’ and dob = ‘1960-01-01’;

　　全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。

匹配最左前缀：

　　select * from people where last_name = ‘Allen’;

　　前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。

匹配列前缀：

　　select * from people where last_name like ‘J%’;

　　也可以只匹配某一列的值得开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只用了索引的第一列。

匹配范围值：

　　select * from people where last_name between ‘Allen’ and ‘Barrymore’;

　　例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。

精确匹配某一列并范围匹配另外一列：

　　select * from people where last_name = ‘Allen’ and first_name like ‘K%’;

　　前面提到的索引也可用于查找所有姓为Allen，并且名字是字母K开头的人。即第一列last_name全匹配，第二列first_name范围匹配。

只访问索引的查询：

　　select last_name, first_name from people where last_name = ‘Allen’ and first_name = ‘Cube’;

　　B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行（覆盖索引）。

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY字句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

下面是一些关于B-Trss索引的限制：

如果不是按照索引的最左列开始查找，则无法使用索引。

　　select * from people where first_name = ‘Bill’;

　　select * from people where dob = ‘xxxx-xx-xx’;

　　select * from people where last_name like ‘%X’;

　　例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。

不能跳过索引中的列。

　　select * from people where last_name = ‘Smith’ and dob = ‘xxxx-xx-xx’;（只能使用索引的第一列）

　　也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名（first_name），则MySQL只能使用索引的第一列。

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

　　select * from people where last_name=’Smith’ AND first_name LIKE ‘J%’ AND dob = ‘1976-12-23’;

　　这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件。如果范围查询列值得数量有限，那么可以通过使用多个等于条件来代替范围条件。

到这里读者应该可以明白，前面提到的索引列的顺序是多么重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

2.哈希索引

3.空间数据索引（R-Tree）

4.全文索引

5.其他索引类别

二、索引的优点

1.索引大大减少了服务器需要扫描的数据量。

2.索引可以帮助服务器避免排序和临时表。

3.索引可以将随机I/O变为顺序I/O。

索引是最好的解决方案吗？

索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录匹配。例如可以使用分区技术。如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术来代替索引。

三、高性能的索引策略

1.独立的列

　　“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

　　反例：mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

　　　　　mysql> SELECT … WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(data_col) <= 10;

　　我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

2.前缀索引和索引选择性

　　字符列太长会让索引变得大且慢，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。

　　但这样会降低索引的选择性。索引的选择性是指，不重复的索引值和数据表的记录总数的比值。

　　索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

　　一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。

　　对于BLOB、TEXT或者更长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

　　诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。

　　创建前缀索引：mysql> ALTER TABLE sakila.city ADD KEY(city(7));

3.多列索引

　　一个常见的错误就是，为每个列创建独立的索引，后者按照错误的顺序创建多列索引。

4.选择合适的索引列顺序

　　对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放到索引最前列。但一定要记住别忘了WHERE字句中的排序、分组和范围条件等其他因素，这些因素可能对查询性能造成非常大的影响。

5.聚簇索引

6.覆盖索引

　　如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。

7.使用索引扫描来做排序

　　只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果排序。如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。

　　例如，Sakila示例数据库的表rental在列（retal_date, inventory_id,customer_id）上有名为rental_date的索引。

　　MySQL可以使用rental_date索引为下面的查询做排序，EXPLAIN中不会出现文件排序（firesort）操作；

　　mysql> EXPLAIN SELECT rental_id, staff_id FROM rental WHERE rental_date = ‘2005-05-25’ ORDER BY inventory_id, customer_id\G;

　　即使ORDER BY子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。

　　mysql> … WHERE rental_date = ‘2005-05-25’ ORDER BY inventory_id DESC;

　　第一列提供了常量条件，而使用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀。

　　mysql> … WHERE rental_date > ‘2005-05-25’ ORDER BY rental_date, inventory_id;

　　上面这个查询也没问题，ORDER BY使用的两列就是索引的最左前缀。

反例，下面是一些不能使用索引做排序的查询：

• 下面这个查询使用了两张不同的排序方向，但是索引列都是正序排序的：

　　　　… WHERE rental_date = ‘2005-05-25’ ORDER BY inventory_id DESC, customer_id ASC;

• 下面这个查询的ORDER BY子句中引用了一个不在索引的列：

　　　　… WHERE rental_date = ‘2005-05-25’ ORDER BY inventory_id, staff_id;

• 下面这个查询的WHERE和ORDER BY中的列无法组合成索引的最左前缀：

　　　　… WHERE rental_date = ‘2005-05-25’ ORDER BY customer_id;

• 下面这个查询在索引列的第一列上是范围条件，所以MySQL无法使用索引的其余列：

　　　　… WHERE rental_date > ‘2005-05-25’ ORDER BY inventory_id, customer_id;

• 这个查询在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询：

　　　　… WHERE rental_date = ‘2005-05-25’ AND inventory_id IN (1, 2) ORDER BY customer_id;

8.压缩（前缀压缩）索引

9.冗余和重复索引

　　重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

　　如果创建了索引（A,B），再创建（A）就是冗余索引，因为这只是前一个索引的前缀索引。因此前一个也可以当做后一个索引来使用。如果再创建（B,A）、（B），则不是冗余索引。

　　冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引（A,B）而不是扩展已有的索引（A），还有一种情况是将一个索引扩展为（A,ID），其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

　　尽量扩展已有的索引而不是创建新索引。但有时候处于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。例如，如果再整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那性能可能会急剧下降。

10.未使用的索引

11.索引和锁

四、索引案例学习

　　假设要设计一个在线约会网站，用户信息表有很多列，包括国家、地区、城市、性别、眼睛颜色，等等。网站必须支持上面这些特征的各种组合来搜索用户，还必须允许根据用户的最后在线时间、其他会员对用户的评分等对用户进行排序并对结果进行限制。如何设计索引满足上面的复杂需求呢？

1.支持多种过滤条件

　　将大部分查询中都会用到的列作为组合索引的前缀，即使查询没有使用到该列，那么可以通过在查询条件中新增IN()列表(多个等值条件查询)查询条件来让MySQL选择该索引。这种做法在该列的选择性不高的时候非常有效，但如果列有太多不同的值，就会让IN()列表太长，这样做就不行了。

2.避免多个范围条件

假设我们有一个last_online列并希望通过下面的查询显示在过去几周上线过的用户：

　WHERE eye_color IN (‘brown’, ‘blue’, ‘hezel’)

　　AND hair_color IN (‘black’, ‘red’, ‘blonde’, ‘brown’)

　　AND sex IN (‘M’, ‘F’)

　　ADN last_online > DATE_SUB(NOW(), INTERVAL 7 DAY)

　　ADN age BETWEEN 18 ADN 25

这个查询有一个问题：他有两个范围条件，last_online列和age列，MySQL可以使用last_online列索引或者age列索引，但无法同时使用它们。

方法一：将age字段转换为一个IN()的列表；

方法二：实现计算好一个active列，由定时任务来维护，每当用户登录时，将对应值设置为1，并且将过去连续七天未曾登录的用户的值设置为0；

3.优化排序

　　使用文件排序对小数据集是很快的，但如果一个查询匹配的结果有上百万行的话会怎么样？例如如果WHERE子句只有sex列，如何排序；对于那些选择性非常低的列，可以增加一些特殊的索引来做排序。例如，可以创建（sex，rating）索引用于下面的查询：

　　mysql> SELECT FROM profiles WHERE sex = ‘M’ ORDER BY rating LIMIT 10;

这个查询使用了ORDER BY和LIMIT，如果没有索引的话会很慢。

即使有索引，如果用户界面上需要翻页，而且翻页翻到比较靠后时查询也可能非常慢。

　　mysql> SELECT FROM profiles WHERE sex = ‘M’ ORDER BY rating LIMIT 1000000 10;

无论如何创建索引，这种查询都是个严重的问题。因为随着偏移量的增加，MySQL需要花费大量的时间来扫描需要丢弃的数据。

　　优化这类索引的另一个比较好的策略是使用延迟关联，通过使用覆盖索引查询返回需要的主键，再根据这些主键关联原表获取需要的行。这可以减少MySQL扫描那些需要丢弃的行数。下面这个查询显示了如何高效地使用（sex，rating）索引进行排序和分页：

　　mysql> SELECT FROM profiles INNER JOIN (SELECT FROM profiles WHERE x.sex = ‘M’ ORDER BY rating LIMIT 1000000, 10) AS x USING();

五、补充：

1.创建索引SQL：

　　CREATE INDEX index_name ON table_name(col[(length)]…);

　　ALTER TABLE table_name ADD INDEX index_name(col[(length)]…);

2.删除索引SQL：

　　DROP INDEX index_name ON table_name;

　　ALTER TABLE table_name DROP INDEX index_name;

3.走不走索引的总结（如有错误，欢迎指正）：

• NOT IN，<>，LIKE ‘%…’ 不走索引；
• =，IN(多个等值条件查询)，LIKE ‘(非%开头)…’，BETWEEN … AND …(范围条件查询)，EXISTS，NOT EXISTS走索引；
• <，<=，>，>=如果字段是整数类型会走索引；字符类型根据实际查询速度来判断；如果全盘扫描速度比索引速度要快则不走索引；

接口项目地址：https://github.com/Nguyen-Vm/s-program

API：

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

@RestController
@RequestMapping("/area")
public class AreaController {
    @Autowired
    private AreaService areaService;

    @RequestMapping(value = "/list", method = RequestMethod.GET)
    private Map<String, Object> listArea(){
        Map<String, Object> modelMap = new HashMap<>();
        List<Area> areaList = areaService.getAreaList();
        modelMap.put("list", areaList);
        return modelMap;
    }

    @RequestMapping(value = "/insert", method = RequestMethod.POST)
    private Map<String, Object> insertArea(@RequestBody Area area){
        Map<String, Object> modelMap = new HashMap<>();
        boolean result = areaService.addArea(area);
        modelMap.put("result", result);
        return modelMap;
    }

}

发送GET请求代码示例：

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

public class Main {

    public static void main(String[] args) throws IOException {

        InetAddress inetAddress = InetAddress.getLocalHost();
        // 获取本地IP
        String hostName = inetAddress.getHostAddress();

        String getUrlStr = String.format("http://%s:%s/s-program/area/list", hostName, 8080);
        get(getUrlStr);
    }

    public static void get(String urlStr) throws IOException {
        URL url = new URL(urlStr);

        HttpURLConnection connection = (HttpURLConnection) url.openConnection();

        // 返回结果-字节输入流转换成字符输入流，控制台输出字符
        BufferedReader br = new BufferedReader(new InputStreamReader(connection.getInputStream()));
        StringBuilder sb = new StringBuilder();
        String line;
        while ((line = br.readLine()) != null) {
            sb.append(line);
        }
        System.out.println(sb);
    }
}

发送POST请求代码示例：

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

public class Main {

    public static void main(String[] args) throws IOException {

        InetAddress inetAddress = InetAddress.getLocalHost();
        // 获取本地IP
        String hostName = inetAddress.getHostAddress();

        String postUrlStr = String.format("http://%s:%s/s-program/area/insert", hostName, 8080);
        post(postUrlStr, "{\"areaName\": \"中国上海\", \"priority\": 1}");


    }

    public static void post(String urlStr, String body) throws IOException {
        URL url = new URL(urlStr);

        HttpURLConnection connection = (HttpURLConnection) url.openConnection();
        connection.setRequestMethod("POST");
        // 设置Content-Type
        connection.setRequestProperty("Content-Type", "application/json");
        // 设置是否向httpUrlConnection输出，post请求设置为true，默认是false
        connection.setDoOutput(true);

        // 设置RequestBody
        PrintWriter printWriter = new PrintWriter(connection.getOutputStream());
        printWriter.write(body);
        printWriter.flush();

        // 返回结果-字节输入流转换成字符输入流，控制台输出字符
        BufferedReader br = new BufferedReader(new InputStreamReader(connection.getInputStream()));
        StringBuilder sb = new StringBuilder();
        String line;
        while ((line = br.readLine()) != null) {
            sb.append(line);
        }
        System.out.println(sb);
    }
}

市场上有些这样的应用，会展示某年某月一个月的数据，比如女神们经常用的“大姨妈”APP，一些游戏的用户签到信息，等等

那我们在写后台接口的时候，就需要返回这一个月的数据，今天我就分享一下笔者经常在工作中使用的方法。

数据库DB中存了许多用户的应用数据，每条数据有一个日期字段，可以是Integer类型(yyyyMMdd)，可以是String类型(yyyy-MM-dd)，还可以是Date类型.

首先定义了一个这样的类来保存某天的数据：

1
2
3
4
5
6
7
8
9

public class CalendarDate<T>{
    public Integer day;

    public Integer weekDay;

    public Boolean isToday;

    public T info;
}

四个字段的意思分别是：day-这个月的第几天，weekDay-星期几，isToday-是否是今天，info-该天的用户数据。

请求接口如下，请求需要年和月两个参数：

1
2
3
4
5
6
7
8
9

@GetMapping("/calendar")
public List<CalendarDate<List<String>>> calendarDates(@RequestParam Integer year, @RequestParam Integer month) {
    Function<String, Optional<List<String>>> function = day -> {
        List<String> datas = new ArrayList<>();
        datas.add(day); // DB: datas = tableMapper.findByDay(day);
        return Optional.of(datas);
    };
    return DateUtils.calendar(year, month, function);
}

重点就是下面封装好的工具类：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

public class DateUtils {


    public static <T> List<CalendarDate<T>> calendar(int year, int month, Function<String, Optional<T>> function) {
        List<CalendarDate<T>> cdList = new ArrayList<>();
        int monthDays = monthDays(year, month);
        CalendarDate<T> cdR;
        for (int day = 1; day <= monthDays; day++) {
            cdR = new CalendarDate<>();
            cdR.day = day;
            LocalDate date = LocalDate.of(year, month, day);
            cdR.weekDay = dayOfWeek(date);
            cdR.isToday = isToday(date);
            if (function != null) {
                Optional<T> optional = function.apply(date.toString());
                if (optional.isPresent()) {
                    cdR.info = optional.get();
                }
            }
            cdList.add(cdR);
        }
        return cdList;
    }

    private static boolean isToday(LocalDate date) {
        LocalDate today = LocalDate.now();
        return date.getYear() == today.getYear() &&
                date.getMonth() == today.getMonth() &&
                date.getDayOfMonth() == today.getDayOfMonth();
    }

    private static int dayOfWeek(LocalDate date) {
        return null == date ? 0 : date.getDayOfWeek().getValue();
    }

    private static int monthDays(int year, int month) {
        Calendar calendar = Calendar.getInstance();
        calendar.set(Calendar.YEAR, year);
        calendar.set(Calendar.MONTH, month - 1);
        calendar.set(Calendar.DATE, 1);
        calendar.roll(Calendar.DATE, -1);
        return calendar.get(Calendar.DATE);
    }
}

针对不同的业务要求，编写不一样的function函数，就可以返回日历形式的用户数据了。

项目地址如下：https://github.com/cnblogs-projects/cnblogs-calendar

首先简单介绍一下Entity生命周期中的Managed，Detached，Removed三种状态变化过程，如下图所示：

1.entityManager.persist(Object entity);　　新增数据；

如果entity的主键不为空，而数据库没有该主键，会抛出异常；

如果entity的主键不为空，而数据库有该主键，且entity的其他字段与数据库不同，persist后不会更新数据库；

2.entityManager.find(Class entityClass, Object primaryKey);　　根据主键查找数据；

如果主键格式不正确，会抛出illegalArgumentException异常;

如果主键在数据库未找到数据返回null；

3.entityManager.remove(Object entity);　　删除数据；

只能将Managed状态的Entity实例删除，由此Entity实例状态变为Removed；

4.entityManager.merge(T entity);　　

将 Detached状态的Entity实例转至Managed状态；

5.entityManager.clear();

将所有的Entity实例状态转至Detached状态；

6.entityManager.flush();

将所有Managed状态的Entity实例同步到数据库；

7.entityManager.refresh(Object entity);

加载Entity实例后，数据库该条数据被修改，refresh该实例，能得到数据库最新的修改，覆盖原来的Entity实例；

原数据：

期望数据：

sql语句：

INSERT INTO emp(name, salary, reword)

​ SELECT

​ name,

​ GROUP_CONCAT(CASE WHEN type = ‘月薪’ THEN money*12 END),

​ GROUP_CONCAT(CASE WHEN type = ‘年终奖’ THEN money END)

​ FRPM emp

​ GROUP BY name;

不管什么应用，用户账号体系都可以设置两张表，一张系统用户表，一张真实信息表；

用户表有OPEN_ID字段，真实信息表有手机号字段。

用户进入系统，生成一条用户数据；用户验证手机号后，生成一条真实信息数据；这条真实信息数据关联了那条用户数据；

没有关联真实信息的用户，称为游客；关联了真实信息的用户，称为注册用户。

公众号菜单入口进行微信网页授权获取用户的OPEN_ID，并将其保存在COOKIE中；用户验证完手机号后，将手机号也存进COOKIE；

微信授权后，有OPEN_ID：

　　先从COOKIE中获取手机号，得到空串或者手机号：

　　　　手机号查询真实查询真实信息:　

　　　　　　找到真实信息，真实信息查询用户数据：

　　　　　　　　找到用户数据，赋值OPEN_ID；——老用户授权，完善或覆盖OPEN_ID。

　　　　　　　　没找到用户数据，添加有OPEN_ID，真实信息主键ID的用户数据；——手机号没有关联系统用户，基本不会出现。

　　　　　　没找到真实信息，OPEN_ID查询用户数据:

　　　　　　　　没有找到，添加有OPEN_ID的用户数据，称之为游客；找打了，就不添加。——游客，也有可能是COOKIE-PHONE失效，之后会删除。

验证手机号，有手机号：

　　通过手机号查询真实信息，有则返回真实信息主键ID，没有添加有手机号的真实信息数据，返回主键ID；

　　从COOKIE中获取OPEN_ID，得到空串或OPEN_ID：

　　　　真实信息主键ID查询用户数据：

　　　　　　没有找到用户数据：

　　　　　　　　如果OPEN_ID是空串，初始化一条用户数据；关联手机号，返回验签；——COOKIE中没有OPEN_ID的游客注册

　　　　　　　　OPEN_ID不是空串，用该OPEN_ID去数据库找到该用户数据；关联手机号，返回验签；——游客注册

　　　　　　找到用户数据，删除所有该OPEN_ID下，真实信息为NULL的用户数据；如果用户数据OPNE_ID为空，赋值OPEN_ID，保存用户数据，返回验签。——老用户登录

需求：分页查询学生信息

项目环境：Spring Boot 2.0.6.RELEASE

1
2
3
4
5
6

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.0.6.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

Maven依赖：

1
2
3
4
5
6
7
8
9

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.38</version>
</dependency>

分页查询返回体类:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

public class IPagination<T> {
    /** 当前页数 **/
    private int pager;
    /** 总页数 **/
    private int pages;
    /** 每页条数 **/
    private int size;
    /** 总条数 **/
    private long total;
    /** 忽略数据条数 **/
    private int offset;
    /** 列表数据 **/
    private List<T> list = new ArrayList<>();

    public IPagination() {
    }

    public IPagination(int pager, int size) {
        if (pager >= 1 && size >= 1) {
            this.pager = pager;
            this.size = size;
        } else {
            throw new RuntimeException("invalid pager: " + pager + " or size: " + size);
        }
    }

    public static IPagination create(int pager, int size) {
        return new IPagination(pager, size);
    }

    public void setTotal(long total) {
        this.total = total;
    }

    public void setList(List<T> list) {
        this.list = list;
    }

    public int getSize() {
        return size;
    }

    public int getPager() {
        return pager == 0 ? 1 : pager;
    }

    public int getOffset() {
        return size * (getPager() - 1);
    }

    public int getPages() {
        return Double.valueOf(Math.ceil((double) total / (double) size)).intValue();
    }

    public long getTotal() {
        return total;
    }

    public List<T> getList() {
        return list;
    }
}

Controller层：

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

@RestController
@RequestMapping("/api/student")
public class StudentApiController {

    @Autowired
    private StudentService studentService;

    @PostMapping("/search")
    public IPagination<StudentResponse> search(@RequestBody StudentSearchRequest request) {
        return studentService.search(request);
    }
}

就一个简单的POST请求，请求体有页数、每页条数、查询参数等属性。

Service层：

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

@Service
public class StudentService {

    @Autowired
    private PaginationMapper paginationMapper;

    /**
     * 分页查询学生信息
     * @param request
     * @return
     */
    public IPagination<StudentResponse> search(StudentSearchRequest request) {
        // 拼接SQL语句
        StringBuilder sql = new StringBuilder("SELECT id, name FROM t_galidun_student ");
        // 查询需要的参数，先存进Map
        Map<String, Object> maps = new HashMap<>();
        if (request.name != null) {
            sql.append("WHERE name LIKE :name");
            maps.put("name", "%" + request.name + "%");
        }
        // 调用通用方法返回查询结果
        return paginationMapper.nativeSearch(request.nowPage, request.pageSize, sql.toString(), maps, StudentResponse.class);
    }

}

在这一层主要是拼接sql，提供查询需要的参数，最后调用通用方法返回结果。

Mapper层：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

@Component
public class PaginationMapper {

    @PersistenceContext
    private EntityManager entityManager;

    /**
     * 分页查询通用方法
     *
     * @param nowPage  当前页
     * @param pageSize 每页条数
     * @param sql      sql语句
     * @param maps     sql查询参数
     * @param clazz    返回类型
     * @param <T>
     * @return
     */
    public <T> IPagination<T> search(Integer nowPage, Integer pageSize, String sql, Map<String, Object> maps, Class<T> clazz) {
        // 初始化分页返回体
        IPagination pagination = IPagination.create(nowPage, pageSize);
        // 查询结果总条数
        int total = getQueryWithParameters(entityManager.createNativeQuery(sql), maps).getResultList().size();
        pagination.setTotal(total);
        if (total == 0) return pagination;
        Query query = getQueryWithParameters(entityManager.createNativeQuery(sql), maps);
        // 忽略指定条数据，返回一页数据
        query.setFirstResult(pagination.getOffset()).setMaxResults(pagination.getSize());
        // 指定返回对象类型
        query.unwrap(NativeQueryImpl.class).setResultTransformer(Transformers.aliasToBean(clazz));
        // 列表数据
        pagination.setList(query.getResultList());
        return pagination;
    }

    /**
     * 设置查询所需的参数
     *
     * @param query
     * @param maps
     * @return
     */
    private Query getQueryWithParameters(Query query, Map<String, Object> maps) {
        if (maps.size() > 0) {
            for (String key : maps.keySet()) {
                query.setParameter(key, maps.get(key));
            }
        }
        return query;
    }
}

这是个通用的方法，只需要传入查询的页数，每页数据条数，sql语句，查询参数，返回体类型即可。

每个Query只能调用一次getResultList方法，调用之后再次调用就会抛异常，所以方法中有两处entityManager.createNaticeQuery(sql)，一次是为了查询总条数，另一次是查询当前页的数据。

查询总条数的时候可以改为使用COUNT（主键或者非NULL索引）；读者有其他能提高查询性能的方法，方便的话，分享一下吧。

项目地址：https://github.com/Nguyen-Vm/entity-manager