B+树索引使用(6)最左原则 --mysql从入门到精通（十八)

最新推荐文章于 2023-02-17 17:10:56 发布

后端从入门到精通

最新推荐文章于 2023-02-17 17:10:56 发布

阅读量271

点赞数

分类专栏： mysql进阶文章标签： java 编程语言 sql mysql

本文链接：https://blog.csdn.net/ke1ying/article/details/119817836

版权

mysql进阶专栏收录该内容

83 篇文章 11 订阅

订阅专栏

上篇文章我们说了，myISAM表和innoDB表的不同，myISAM吧用户记录数据放在数据文件，会给每行数据一个行号，myISAM会给主键生成索引，吧索引页放在索引文件。B+树的叶子节点存储的是主键+行号，意味着第一次通过主键查询只能查到行号，之后回表，通过行号查询数据文件整行数据。而innoDB的聚簇索引不需要二次查询。

B+树(5)myISAM简介 --mysql从入门到精通（十七)

索引的代价

理解索引的原理之后，就会知道索引并不是没有缺点的。

1、空间上的代价：我们知道每个页都是16kb大小，而一颗b+树有每个节点都属于一个页，这样建立太多索引对空间内存占用非常大。

2、时间上的代价：索引会吧每个页按从小到大组成一个双向链表，内节点或者底层叶子节点里的数据也是按索引从小到大组成一个单向链表，这样每次进行增，删，改操作可能都会对节点排序和页记录进行破坏，所以如果建立太多索引，对表的增删改性能影响很大。

B+树索引使用场景

下面我们开始使用b+树索引，所有使用技巧都源于你对b+树索引特征本质的理解，如果你还不能理解前面的文章，建议你去读一遍，不然下面的文字对你来说是一种折磨，看了就会忘记。下面我们来介绍b+树索引的查询情况，先建立个表，存储人的基本信息，设置id为主键，这样innoDB会默认创年聚簇索引，在显示创建idx_name_birthday_phone为复合索引，所以在列b+树的叶子节点会有name,birthdate,phone和主键id，不会有country。

mysql> create table person_info(
    -> id int not null auto_increment,
    -> name varchar(100) not null,
    -> birthday date not null,
    -> phone char(11) not null,
    -> country varchar(100) not null,
    -> primary key (id),
    -> key idx_name_birthday_phone (name,birthday,phone)
    -> );
Query OK, 0 rows affected (0.05 sec)

聚簇索引的叶子节点存储的是用户记录数据，因为我们创建了复合索引，复合索引的叶子节点存储的是name，birthday，phone，没有country，查询的时候：1）先按name排序。2）name相同按birthday排序。3）birthday也相同，按phone排序。

全值匹配查询

SELECT * FROM person_info WHERE name = '' AND birthday = '' AND phone = '';

//改成这样也不会有影响
SELECT * FROM person_info WHERE  birthday = '' AND phone = '' AND name = '' ;

当我们用如上sql时，就是全值匹配查询，不管用哪条sql，mysql的查询优化器，都会根据建立的联合索引查询，1）先查询name。2）name相同时候查询birthday。3）birthday也相同时候查询phone。

匹配最左原则

//sql1
SELECT * FROM person_info WHERE name = '' AND birthday = '';

//sql2
SELECT * FROM person_info WHERE name = '' ;

//sql3

SELECT * FROM person_info WHERE  birthday = '' AND phone = '';

//sql4
SELECT * FROM person_info WHERE name = ''  AND phone = '';

当我们用sql1和sql2查询的时候，依旧可以触发联合索引来查询数据，但如果使用sql3是不能触发联合索引的，因为我们建立的索引是按name先排序比较，比较相同之后，采用birthday比较，但现在没有查询name，导致无法使用联合索引查询。我们用sql4也能触发索引查询，但不能用phone比较，原因与sql3相同，必须birthday比较完才可以比较phone排序。