B+树索引适⽤的条件

标签：name 记录 number 索引 birthday 条件 排序

假设有张存储人基本信息的表，DDL如下：

CREATE TABLE person_info(
   	id INT NOT NULL	auto_increment,
   	name VARCHAR(100) NOT NULL,
   	birthday DATE NOT NULL,
   	phone_number CHAR(11) NOT NULL,
   	country	varchar(100) NOT NULL,
   	PRIMARY	KEY (id),
   	KEY  `idx_name_birthday_phone_number` (name,birthday,phone_number)
);

索引应用

全值匹配

搜索条件中的列和索引列⼀致的话，这种情况就称为全值匹配

SELECT	* FROM	person_info	
WHERE name ='Ashburn' AND birthday = '1990-09-27' 
AND phone_number ='15123983239';

建⽴的idx_name_birthday_phone_number索引包含的3个列在这个查询语句中都展现出来了。查询过程如下：

• 1、因为B+树的数据⻚和记录先是按照name列的值进⾏排序的，所以先可以很快定位name列的值是Ashburn的记录位置。
• 2、在name列相同的记录⾥⼜是按照birthday列的值进⾏排序的，所以在name列的值是Ashburn的记录⾥⼜可以快速定位birthday列的值是'1990-09-27'的记录。
• 3、如果很不幸，name和birthday列的值都是相同的，那记录是按照phone_number列的值排序的，所以联合索引中的三个列都可能被⽤到。

如果我们调换name、birthday、phone_number这⼏个搜索列的顺
序对查询的执⾏过程有影响么？
答案是：没影响哈。MySQL有⼀个叫查询优化器的东东，会分析这些搜索条件并且按照可以使⽤的索引中列的顺序来决定先使⽤哪个搜索条件

匹配左边的列

如果想使⽤联合索引中尽可能多的列，搜索条件中的各个列必须是联合索引中从最左边连续的列

SELECT * FROM person_info WHERE	birthday='1990-09-27'

这个查询用不到索引，因为B+树的数据⻚和记录先是按照name列的值排序的，在name列的值相同的情况下才使⽤birthday列进⾏排序

SELECT * FROM person_info
WHERE name='Ashburn'AND	phone_number='15123983239';

这个查询只能⽤到name列的索引，phone_number的索引就⽤不上。

匹配范围值

idx_name_birthday_phone_number索引中，所有记录都是按照索引列的值从⼩到⼤的顺序排好序的。

SELECT * FROM person_info WHERE	name > 'Asa' AND name < 'Barlow';

上边的查询过程其实是这样的：

• 1、找到name值为Asa的记录。
• 2、找到name值为Barlow的记录。
• 3、由于所有记录都是由链表连起来的（记录之间⽤单链表，数据⻚之间⽤双链表），所以他们之间的记录都可以很容易的取出来
• 4、找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。

⽤于排序

查询语句经常需要对查询出来的记录通过ORDER BY⼦句按照某种规则进⾏排序。⼀般情况下，我们只能把记录都加载到内存中，再⽤⼀些排序算法，⽐如快速排序、归并排序等等在内存中对这些记录进⾏排序，有的时候可能查询的结果集太⼤需要借助磁盘的空间来存放中间结果，把这种在内存中或者磁盘上进⾏排序的⽅式统称为⽂件排序（filesort），但是如果ORDER BY⼦句⾥使⽤到了索引列，就有可能省去在内存或⽂件中排序的步骤。如：

SELECT * FROM person_info ORDER	BY name, birthday,phone_number LIMIT 10;

这个查询的结果集需要先按照name值排序，如果记录的name值相同，则需要按照birthday来排序，如果birthday的值相同，则需要按照phone_number排序,因为这个B+树索引本身就是按照上述规则排好序的，所以直接从索引中提取数据，然后进⾏回表操作取出该索引中不包含的列就好了。

使⽤联合索引进⾏排序注意事项

对于联合索引有个问题需要注意，ORDER BY的⼦句后边的列的顺序也必须按照索引列的顺序给出，如果给出ORDER BY phone_number, birthday, name的顺序，那也是⽤不了B+树索引

不可以使⽤索引进⾏排序的⼏种情况

ASC、DESC混⽤

想想这个idx_name_birthday_phone_number联合索引中记录的结构：

• 1、先按照记录的name列的值进⾏升序排列。
• 2、如果记录的name列的值相同，再按照birthday列的值进⾏升序排列。
• 3、如果记录的birthday列的值相同，再按照phone_number列的值进⾏升序排列。

如果查询中的各个排序列的排序顺序是⼀致的，⽐⽅说下边这种情况：

ORDER BY name, birthday LIMIT 10

这种情况直接从索引的最左边开始往右读10⾏记录就可以了，降序从右往左。

但是如果升序、降序排列混用的话，⽐如说这样的查询语句：

SELECT * FROM person_info ORDER BY name,birthday DESC LIMIT 10;

这样如果使⽤索引排序的话过程就是这样的：

• 1、先从索引的最左边确定name列最⼩的值，然后找到name列等于该值的所有记录，然后从name列等于该值的最右边的那条记录开始往左找10条记录。
• 2、如果name列等于最⼩的值的记录不⾜10条，再继续往右找name值第⼆⼩的记录，重复上边那个过程，直到找到10条记录为⽌。

效率极低，所以就规定使⽤联合索引的各个排序列的排序顺序必须是⼀致的。

WHERE⼦句中出现⾮排序使⽤到的索引列

SELECT * FROM person_info WHERE	country	= 'China' 
ORDER	BY name	LIMIT 10;

这个查询只能先把符合搜索条件country = 'China'的记录提取出来后再进⾏排序，是使⽤不到索引。

排序列包含⾮同⼀个索引的列

有时候⽤来排序的多个列不是⼀个索引⾥的，这种情况也不能使⽤索引进⾏排序，⽐⽅说：

SELECT * FROM person_info ORDER	BY name, country LIMIT 10;

name和country并不属于⼀个联合索引中的列，所以⽆法使⽤索引进⾏排序

排序列使⽤了复杂的表达式

要想使⽤索引进⾏排序操作，必须保证索引列是以单独列的形式出现，⽽不是修饰过的形式，⽐⽅说这样：

SELECT * FROM person_info ORDERBY UPPER(name) LIMIT 10;

使⽤了UPPER函数修饰过的列就不是单独的列

⽤于分组

有时候我们为了⽅便统计表中的⼀些信息，会把表中的记录按照某些列进⾏分组。⽐如下边这个分组查询：

SELECT name,birthday,phone_number,COUNT(*) 
FROM person_info GROUP BY name,birthday,phone_number

这个查询语句相当于做了3次分组操作：

1. 先把记录按照name值进⾏分组，所有name值相同的记录划分为⼀组。
2. 将每个name值相同的分组⾥的记录再按照birthday的值进⾏分组，将birthday值相同的记录放到⼀个⼩分组⾥，所以看起来就像在⼀个⼤分组⾥⼜化分了好
   多⼩分组。
3. 再将上⼀步中产⽣的⼩分组按照phone_number的值分成更⼩的分组，所以整体上看起来就像是先把记录分成⼀个⼤分组，然后把⼤分组分成若⼲个⼩分组，然后把若⼲个⼩分组再细分成更多的⼩⼩分组。

然后针对那些⼩⼩分组进⾏统计，⽐如在我们这个查询语句中就是统计每个⼩⼩分组包含的记录条数。恰巧这个分组顺序⼜和我们的B+树中的索引列的顺序是⼀致的，⽽我们的B+树索引⼜是按照索引列排好序的所以可以直接使⽤B+树索引进⾏分组。

标签：name,记录,number,索引,birthday,条件,排序
From： https://www.cnblogs.com/d111991/p/16874540.html