联合索引最左匹配原则

最新推荐文章于 2024-10-08 16:57:59 发布

Torey_Li

最新推荐文章于 2024-10-08 16:57:59 发布

阅读量2.7k

点赞数 3

分类专栏： SQL 文章标签： mysql

本文链接：https://blog.csdn.net/Torey_Li/article/details/115033373

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

联合索引最左匹配原则的成因

联合索引是指将多个列一起设置成一个索引，例如：将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);