# 联合索引最左匹配原则的成因
联合索引是指 将多个列 一起设置成一个索引，例如：将a,b设置成联合索引,则命中索引规则如下：
- where a=6 走索引
- where a=6,b=1 ,走索引
- where b=1 , 不走索引
- where a like 'a%' ,走索引
- where a like '%a%',不走索引
- where a like 'a%' and b='2', a 走索引，b 不走索引
- 最左匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配， 比如 a=3 and b=4 and c>5 and d=6,如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的 索引则都可以用到，a,b,d的顺序可以任意调整
- = 和 in 可以乱序，比如：a=1 and b=2 and c=3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化
成索引可以识别的形式
联合索引最左匹配原则的原因?
mysql创建复合索引的规则：是首先会对复合索引的最左边的第一个字段进行排序，然后在对最左边第一个字段排序的基础上 再对对第二个索引字段进行排序，就相当于order by 字段1，字段2，字段3...这样的规则，所以第一个字段是绝对有序的， 而第二个字段就是无序的了，因此通常情况下直接使用第二个字段进行条件判断是用不到索引的， 这就是所谓的联合索引最左匹配原则的原因
测试数据脚本
1.找到mysql的my.cnf配置文件，将max_heap_table_size改大些，改成4000M，重启下mysql服务即可。
#创建一张内存表
CREATE TABLE `person_info_memory` (
`id` INT (7) NOT NULL AUTO_INCREMENT,
`account` VARCHAR (10),
`name` VARCHAR (20),
`area` VARCHAR (20),
`title` VARCHAR (20),
`motto` VARCHAR (50),
PRIMARY KEY (`id`),
UNIQUE(`account`),
KEY `index_area_title`(`area`,`title`)
) ENGINE = MEMORY AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
#创建一张店铺小数据表
CREATE TABLE `shop_info_small` (
`shop_id` INT (2) NOT NULL AUTO_INCREMENT,
`shop_name` VARCHAR (20),
`person_id` INT (2),
`shop_profile` VARCHAR (50),
PRIMARY KEY (`shop_id`),
UNIQUE(`shop_name`)
) ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
#创建一张小数据表
CREATE TABLE `person_info_small` (
`id` INT (2) NOT NULL AUTO_INCREMENT,
`account` VARCHAR (10),
`name` VARCHAR (20),
`area` VARCHAR (20),
`title` VARCHAR (20),
`motto` VARCHAR (50),
PRIMARY KEY (`id`),
UNIQUE(`account`),
KEY `index_area_title`(`area`,`title`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
#创建一张大数据表
CREATE TABLE `person_info_large` (
`id` INT (7) NOT NULL AUTO_INCREMENT,
`account` VARCHAR (10),
`name` VARCHAR (20),
`area` VARCHAR (20),
`title` VARCHAR (20),
`motto` VARCHAR (50),
PRIMARY KEY (`id`),
UNIQUE(`account`),
KEY `index_area_title`(`area`,`title`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
#创建一个能够返回随机字符串mysql自定义函数
DELIMITER $$
CREATE FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8
BEGIN
DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
DECLARE return_str varchar(255) DEFAULT '' ;
DECLARE i INT DEFAULT 0;
WHILE i < n DO
SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*12 + RAND()*50),1));
SET i = i +1;
END WHILE;
RETURN return_str;
END $$
#创建一个批量往内存表里灌数据的存储过程
DELIMITER $$
CREATE PROCEDURE `add_person_info_large`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n ) DO
INSERT into person_info_memory (account,name,area,title, motto) VALUEs (rand_string(10),rand_string(20),rand_string(20) ,rand_string(20),rand_string(50));
set i=i+1;
END WHILE;
END $$
#创建一个批量往小表里灌数据的存储过程
DELIMITER $$
CREATE PROCEDURE `add_person_info_small`(IN n int)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i <= n ) DO
INSERT into person_info_small (account,name,area,title, motto) VALUEs (rand_string(10),rand_string(20),rand_string(20) ,rand_string(20),rand_string(50));
set i=i+1;
END WHILE;
END $$
#调用存储过程，插入100万条数据(由于我们的随机数可能会出现重复的情况，所以插入的条数也许达不到100万便会出错停止,可以自行加入些随机数优化一下)
CALL add_person_info_large(1000000);
#调用存储过程，插入10条数据到小表里
CALL add_person_info_small(2);
#将内存表的数据移动到person_info_large中
insert into person_info_large(account,name,area,title,motto)
select account,name,area,title,motto from person_info_memory;
#若遇数据冲突没法到达100万的情况，通过变换唯一键值的方式来插入数据
insert into person_info_large(account,name,area,title,motto)
select concat(substring(account, 2),'a'),concat(substring(name, 2),'a'),area,title,motto from person_info_memory;
insert into person_info_large(account,name,area,title,motto)
select concat(substring(account, 2),'b'),concat(substring(name, 2),'b'),area,title,motto from person_info_memory;
CREATE TABLE `test_myisam` (
`id` INT (2) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (20),
`unique_id` INT (2),
`normal_id` INT (2),
PRIMARY KEY (`id`),
UNIQUE(`unique_id`),
INDEX(`normal_id`)
) ENGINE = MYISAM AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
CREATE TABLE `test_innodb` (
`id` INT (2) NOT NULL AUTO_INCREMENT,
`name` VARCHAR (20),
`unique_id` INT (2),
`normal_id` INT (2),
PRIMARY KEY (`id`),
UNIQUE(`unique_id`),
INDEX(`normal_id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8
insert into test_innodb (name,unique_id,normal_id) values('a',1,1),('d',4,4),('h',8,8),('k',11,11);
insert into test_myisam (name,unique_id,normal_id) values('a',1,1),('d',4,4),('h',8,8),('k',11,11);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
# 本站导航
MySql慢查询日志
mySql联合索引最左匹配原则
mySql锁
mySql优化
回到主导航页
# 支持我-微信扫一扫-加入微信公众号
# 赞赏作者
