网站免费正能量入口,织梦网站去除技术支持,阿里巴巴国际站下载,数字重庆公司文章目录前言最左匹配原则为什么会有最左前缀呢#xff1f;联合索引的存储结构联合索引字段的先后顺序b树可以存储的数据条数总结前言
对于联合索引我们知道#xff0c;在使用的时候有一个最左前缀的原则#xff0c;除了这些呢#xff0c;比如字段放置的位置#xff0…
文章目录前言最左匹配原则为什么会有最左前缀呢联合索引的存储结构联合索引字段的先后顺序b树可以存储的数据条数总结前言
对于联合索引我们知道在使用的时候有一个最左前缀的原则除了这些呢比如字段放置的位置会不会对索引的效率产生影响呢 最左匹配原则
联合索引时会遵循最左前缀匹配的原则即最左优先在检索数据时从联合索引的最左边开始匹配示例
create table test
(id bigint auto_incrementprimary key,column_1 bigint null,column_2 bigint null,column_3 bigint null
);create index test_column_1_column_2_column_3_indexon test (column_1, column_2, column_3); 比如上面的test表我们建立了联合索引index test_column_1_column_2_column_3_index on test (column_1, column_2, column_3);当我们进行查询的时候按照最左前缀的原则当查询(column_1)、(column_1,column_2)(column_1,column_2,column_3)这三种组合是可以用到我们定义的联合索引的。如果我们查询(column_1,column_3)就只能用到column_1的索引了。我们不用太关心索引的先后顺序什么意思呢比如使用(column_1,column_2)和(column_2,column_1)的效果是一样的数据库的查询优化器会自动帮助我们优化我们的sql看哪个执行的效率最高 最后才生成最后执行的sql。
为什么会有最左前缀呢
当使用b树作为索引的存储数据结构时当我们创建联合索引的时候比如(column_1, column_2, column_3)b树建立索引是从左到右来建立搜索树的比如当我们来查询的时候WHERE column_1 1 AND column_2 2 AND column_3 3。b树会先通过最左边的建立索引的字段的左边的字段字段也就是column_1来确定下一步的查找对象然后找到column_2在通过column_2的索引找到column_3。所以(column_2,column_3)这样的查询命中不到索引了。因为最左前缀一定是从最左边的字段开始依次在b树的子节点查询然后确定下一个查找的子节点的数据。所以我们(column_1)、(column_1,column_2)、(column_1,column_2,column_3)这三种查询条件是可以使用到索引的。
联合索引的存储结构
定义联合索引(员工级别员工姓名员工出生年月)将联合索引按照索引顺序放入节点中新插入节点时先按照联合索引中的员工级别比较如果相同会按照是员工姓名比较如果员工级别和员工姓名都相同 最后是员工的出生年月比较。可以从图中从上到下从左到右看第一个B树的节点 是通过联合索引的员工级别比较的第二个节点是 员工级别相同会按照员工姓名比较第三个节点是 员工级别和员工姓名都相同会按照员工出生年月比较。 联合索引字段的先后顺序
我们定义多个字段的联合索引会考虑到字段的先后顺序。那么字段的先后顺序真的会对查询的效率产生影响吗比如上面的联合索引
index test_column_1_column_2_column_3_index on test (column_1, column_2, column_3);和index test_column_1_column_2_column_3_index on test (column_2, column_1, column_3);在查询效率上有差别吗我们试验下
写个函数批量插入下数据
CREATE PROCEDURE dowhile()
BEGINDECLARE v1 INT DEFAULT 20000000;WHILE v1 0 DOINSERT INTO test.test (column_1, column_2, column_3) VALUES (RAND() * 20000000, RAND() * 10000, RAND() * 20000000);SET v1 v1 - 1;
END WHILE;
END; 我们插入了20000000条数据然后先设置索引(column_1, column_2, column_3)中column_1的数值范围为0到20000000column_2的范围为0到10000。然后查询看看这个索引的效率。数据量太大插入的时间可能要好久。为什么插入20000000条呢因为b树可以高效存储的数据条数就是21902400具体见下文。
我们尝试下查询的效率
SELECT * FROM test WHERE column_119999834 AND column_23601OK时间: 0.001sEXPLAIN SELECT * FROM test WHERE column_119999834 AND column_23601我们看到索引的type为ref已经相当高效了。
type这列最重要显示了连接使用了哪种类别,有无使用索引是使用Explain命令分析性能瓶颈的关键项之一。 结果值从好到坏依次是 system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALL 一般来说得保证查询至少达到range级别最好能达到ref否则就可能会出现性能问题。
然后我们看下插入的效率
INSERT INTO test.test (column_1, column_2, column_3) VALUES (RAND() * 20000000, RAND() * 10000, RAND() * 20000000)Affected rows: 1时间: 0.002s 更改索引的顺序
drop index test_column_1_column_2_column_3_index on test;create index test_column_2_column_1_column_3_indexon test (column_2, column_1, column_3); 我们把column_2和column_1的索引位置更换了一下来比较联合索引的先后顺序对查询效率的影响。
SELECT * FROM test WHERE column_23601 AND column_119999834OK时间: 0.001sEXPLAIN SELECT * FROM test WHERE column_23601 AND column_119999834 发现更换了之后查询时间上没有什么出入还和上个查询的时间一样分析查询的效率一样很高。
再来看插入的效率
INSERT INTO test.test (column_1, column_2, column_3) VALUES (RAND() * 20000000, RAND() * 10000, RAND() * 20000000)Affected rows: 1时间: 0.003s 依然高效
所以我们可以总结出来联合索引中字段的先后顺序在sql层面的执行效率差别不大是可以忽略的。分析上面索引的数据结构也是可以推断出来的无非就是当建立联合索引更换索引字段的先后顺序匹配每个字段锁定的数据条数不一样但是对最终的查询效率没有太大的影响。但是这个字段的顺序真的就不用考虑吗不是的我们知道有最左匹配原则所以我们要考虑我们的业务比如说我们的业务场景中有一个字段enterpriseId,这个字段在80%的查询场景中都会遇到那么我们肯定首选将这个字段放在联合索引字段的第一个位置这样就能保证查询的高效能够命中我们建立的索引。
b树可以存储的数据条数
b树 正常的高度是1~3一个整型8b 指针占用6bmysql页文件默认16K16k的数据可以存储16/14b1170三层的数据大概就是1170*1170*1621902400千万条数据所以千万级别的数据对于建了索引的数据库查询的数据库也是很快的。
总结 对于联合索引我们不能忽略它的最左匹配原则即在检索数据时从联合索引的最左边开始匹配。对于创建联合索引时我们要根据我们的具体的查询场景来定联合索引字段的先后顺序联合索引字段的先后顺序在sql层面上没有太大差别,但是结合查询的场景和最左匹配的原则就能使一些查询的场景不能很好的命中索引这点使我们是不能忽略的。
