网站建设客户分析,郑州网站建设优化公司,南京传销是以网站开发,安徽天长建设局网站回头看我们 idx_name_birthday_phone_number 索引的 B 树示意图#xff0c;所有记录都是按照索引列的值从小到大的顺序排好序的#xff0c;所以这极大的方便我们查找索引列的值在某个范围内的记录。比方说下边这个查询语句#xff1a;
SELECT * FROM person_info WHERE nam…回头看我们 idx_name_birthday_phone_number 索引的 B 树示意图所有记录都是按照索引列的值从小到大的顺序排好序的所以这极大的方便我们查找索引列的值在某个范围内的记录。比方说下边这个查询语句
SELECT * FROM person_info WHERE name Asa AND name Barlow;
由于 B 树中的数据页和记录是先按 name 列排序的所以我们上边的查询过程其实是这样的
找到 name 值为 Asa 的记录。
找到 name 值为 Barlow 的记录。
由于所有记录都是由链表连起来的记录之间用单链表数据页之间用双链表所以他们之间的记录都可以很容易的取出来喽
找到这些记录的主键值再到 聚簇索引 中 回表 查找完整的记录。
不过在使用联合进行范围查找的时候需要注意如果对多个列同时进行范围查找的话只有对索引最左边的那个列进行范围查找的时候才能用到 B 树索引比方说这样
FROM person_info WHERE name Asa AND name Barlow AND birthday 1980-01-01;
上边这个查询可以分成两个部分
1. 通过条件 name Asa AND name Barlow 来对 name 进行范围查找的结果可能有多条 name 值不同的记录
2. 对这些 name 值不同的记录继续通过 birthday 1980-01-01 条件继续过滤。
这样子对于联合索引 idx_name_birthday_phone_number 来说只能用到 name 列的部分而用不到 birthday 列的部分因为只有 name 值相同的情况下才能用 birthday 列的值进行排序而这个查询中通过 name 进行范围查找的记录中可能并不是按照 birthday 列进行排序的所以在搜索条件中继续以 birthday 列进行查找时是用不到这个 B 树索引的。
7.2.5 精确匹配某一列并范围匹配另外一列
对于同一个联合索引来说虽然对多个列都进行范围查找时只能用到最左边那个索引列但是如果左边的列是精确查找则右边的列可以进行范围查找比方说这样
SELECT * FROM person_info WHERE name Ashburn AND birthday 1980-01-01 AND birthd
这个查询的条件可以分为3个部分
1. name Ashburn 对 name 列进行精确查找当然可以使用 B 树索引了。
2. birthday 1980-01-01 AND birthday 2000-12-31 由于 name 列是精确查找所以通过 name Ashburn 条件查找后得到的结果的 name 值都是相同的它们会再按照 birthday 的值进行排序。所以此时对 birthday 列进行范围查找是可以用到 B 树索引的。
3. phone_number 15100000000 通过 birthday 的范围查找的记录的 birthday 的值可能不同所以这个条件无法再利用 B 树索引了只能遍历上一步查询得到的记录。
同理下边的查询也是可能用到这个 idx_name_birthday_phone_number 联合索引的
SELECT * FROM person_info WHERE name Ashburn AND birthday 1980-01-01 AND AND p
7.2.6 用于排序
在写查询语句的时候经常需要对查询出来的记录通过 ORDER BY 子句按照某种规则进行排序。一般情况下我们只能把记录都加载到内存中再用一些排序算法比如快速排序、归并排序等等在内存中对这些记录进行排序有的时候可能查询的结果集太大以至于不能在内存中进行排序的话还可能暂时借助磁盘的空间来存放中间结果排序操作完成后再把排好序的结果集返回到客户端。在 MySQL 中把这种在内存中或者磁盘上进行排序的方式统称为文件排序英文名 filesort 跟 文件 这个词儿一沾边儿就显得这些排序操作非常慢了磁盘和内存的速度比起来就像是飞机和蜗牛的对比。但是如果 ORDER BY 子句里使用到了我们的索引列就有可能省去在内存或文件中排序的步骤比如下边这个简单的查询语句
SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;
这个查询的结果集需要先按照 name 值排序如果记录的 name 值相同则需要按照 birthday 来排序如果birthday 的值相同则需要按照 phone_number 排序。大家可以回过头去看我们建立的idx_name_birthday_phone_number 索引的示意图因为这个 B 树索引本身就是按照上述规则排好序的所以直接从索引中提取数据然后进行 回表 操作取出该索引中不包含的列就好了。
7.2.6.1 使用联合索引进行排序注意事项
对于 联合索引 有个问题需要注意 ORDER BY 的子句后边的列的顺序也必须按照索引列的顺序给出如果给出ORDER BY phone_number, birthday, name 的顺序那也是用不了 B 树索引这种颠倒顺序就不能使用索引的。
同理 ORDER BY name 、 ORDER BY name, birthday 这种匹配索引左边的列的形式可以使用部分的 B 树索引。当联合索引左边列的值为常量也可以使用后边的列进行排序比如这样
SELECT * FROM person_info WHERE name A ORDER BY birthday, phone_number LIMIT 10;
这个查询能使用联合索引进行排序是因为 name 列的值相同的记录是按照 birthday , phone_number 排序的。
7.2.6.2 不可以使用索引进行排序的几种情况
ASC、DESC混用
对于使用联合索引进行排序的场景我们要求各个排序列的排序顺序是一致的也就是要么各个列都是 ASC 规则排序要么都是 DESC 规则排序。
想想这个 idx_name_birthday_phone_number 联合索引中记录的结构
先按照记录的 name 列的值进行升序排列。
如果记录的 name 列的值相同再按照 birthday 列的值进行升序排列。
如果记录的 birthday 列的值相同再按照 phone_number 列的值进行升序排列。
如果查询中的各个排序列的排序顺序是一致的比方说下边这两种情况
ORDER BY name, birthday LIMIT 10
这种情况直接从索引的最左边开始往右读10行记录就可以了。
ORDER BY name DESC, birthday DESC LIMIT 10
这种情况直接从索引的最右边开始往左读10行记录就可以了。
但是如果我们查询的需求是先按照 name 列进行升序排列再按照 birthday 列进行降序排列的话比如说这样的查询语句
SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;
这样如果使用索引排序的话过程就是这样的
先从索引的最左边确定 name 列最小的值然后找到 name 列等于该值的所有记录然后从 name 列等于该值的最右边的那条记录开始往左找10条记录。
如果 name 列等于最小的值的记录不足10条再继续往右找 name 值第二小的记录重复上边那个过程直到找到10条记录为止。
WHERE子句中出现非排序使用到的索引列
如果WHERE子句中出现了非排序使用到的索引列那么排序依然是使用不到索引的比方说这样
SELECT * FROM person_info WHERE country China ORDER BY name LIMIT 10;
这个查询只能先把符合搜索条件 country China 的记录提取出来后再进行排序是使用不到索引。注意和下边这个查询作区别
SELECT * FROM person_info WHERE name A ORDER BY birthday, phone_number LIMIT 10;
虽然这个查询也有搜索条件但是 name A 可以使用到索引 idx_name_birthday_phone_number 而且过滤剩下的记录还是按照 birthday 、 phone_number 列排序的所以还是可以使用索引进行排序的。
排序列包含非同一个索引的列
有时候用来排序的多个列不是一个索引里的这种情况也不能使用索引进行排序比方说
SELECT * FROM person_info ORDER BY name, country LIMIT 10;
name 和 country 并不属于一个联合索引中的列所以无法使用索引进行排序。
排序列使用了复杂的表达式
要想使用索引进行排序操作必须保证索引列是以单独列的形式出现而不是修饰过的形式比方说这样
SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;
使用了 UPPER 函数修饰过的列就不是单独的列这样就无法使用索引进行排序。
