`
yuri_liuyu
  • 浏览: 177873 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

最左前缀原则

阅读更多

这两天看《构建高性能Web站点》这本书,感觉写的真是不错,很多实际项目中会碰到的问题都有所提及,今天看到一个最左前缀原则,以前也听说过,不过一直没搞明白,今天查了下。

 

通过实例理解单列索引、多列索引以及最左前缀原则

实例:现在我们想查出满足以下条件的用户id:
mysql>SELECT `uid` FROM people WHERE 
lname`='Liu'  AND `fname`='Zhiqun' AND `age`=26
因为我们不想扫描整表,故考虑用索引。

单列索引:
ALTER TABLE people ADD INDEX lname (lname);
将lname列建索引,这样就把范围限制在lname='Liu'的结果集1上,之后扫描结果集1,产生满足fname='Zhiqun'的结果集2,再扫描结果集2,找到 age=26的结果集3,即最终结果。

由 于建立了lname列的索引,与执行表的完全扫描相比,效率提高了很多,但我们要求扫描的记录数量仍旧远远超过了实际所需 要的。虽然我们可以删除lname列上的索引,再创建fname或者age 列的索引,但是,不论在哪个列上创建索引搜索效率仍旧相似。

2.多列索引:

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);
为了提高搜索效率,我们需要考虑运用多列索引,由于索引文件以B-Tree格式保存,所以我们不用扫描任何记录,即可得到最终结果。

注:在mysql中执行查询时,只能使用一个索引,如果我们在lname,fname,age上分别建索引,执行查询时,只能使用一个索引,mysql会选择一个最严格(获得结果集记录数最少)的索引。

3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。

注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最左边。

分享到:
评论
4 楼 pc_cool 2012-12-19  
我在看这本书,刚好看到这个块了,不懂之处,还要向楼主请教,呵呵
3 楼 yuri_liuyu 2012-11-05  
gds_fighting 写道
兄台说“3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。”。

对于这句话,我有些疑问。不是说mysql一次查询只能使用一个索引么?你这里说多列索引相当于多个索引组合而成,那到底用哪个呢,效率感觉没有提高啊。。。

mysql每次确实只会用到它认为效率最高的一个索引。如果数据量不大,其实建立复杂索引的意义也不大。并且要确认确实用到了索引,可以explain一下。

比如文章中举的那个例子,如果sql写成这样:
SELECT `uid` FROM people WHERE lname`='Liu'  AND `fname`='Zhiqun' AND `age`=26便可以用到我们建的组合索引(lname,fname,age)。
如果sql写成这样
SELECT `uid` FROM people WHERE lname`='Liu'  AND `fname`='Zhiqun' 同样可以用到我们建的组合索引。只是这时候是按照(lname,fname)这个索引组合去查的
但是如果sql写成这样
SELECT `uid` FROM people WHERE fname`='Zhiqun'AND `age`=26便无法用到定义的索引了。

建议看下mysql中innodb中B-Tree索引的结构,一看便知为何。
2 楼 gds_fighting 2012-10-24  
兄台说“3.最左前缀:顾名思义,就是最左优先,上例中我们创建了lname_fname_age多列索引,相当于创建了(lname)单列索引,(lname,fname)组合索引以及(lname,fname,age)组合索引。”。

对于这句话,我有些疑问。不是说mysql一次查询只能使用一个索引么?你这里说多列索引相当于多个索引组合而成,那到底用哪个呢,效率感觉没有提高啊。。。
1 楼 Kensai 2011-06-22  
最近我也在看《构建高性能Web站点》确实不错。感谢楼主解释最左前缀原理,我查了n多地方都没解释清楚。

相关推荐

    计算机后端-PHP视频教程. mysql优化引 左前缀原则.wmv

    计算机后端-PHP视频教程. mysql优化引 左前缀原则.wmv

    整理了20道经典面试题和回答参考答案

    1.谈谈你对 SQL 注入式攻击的理解? 2.幻读是什么,用什么隔离级别可以防止幻读?...6.什么是最左前缀原则?什么是最左匹配原则? 7.什么是临时表,何时删除临时表? 8.使用 union 和 union all 时需要注意些什么?

    mysql索引(一篇就够).pdf

    3. 组合索引:由多个列组成的索引,只有查询条件涉及组合索引的最左侧列时,索引才会被有效利用(遵循最左前缀原则)。 4. Unique(唯一索引):索引列的值必须唯一,允许出现NULL值,但主键索引除外。 5. Key(普通...

    MySQL索引不会被用到的情况汇总

    联合索引:索引列有多个字段,使用时需要满足最左前缀原则 普通索引 这是最基本的索引,它没有任何限制。它有以下几种创建方式: 1.创建索引 代码如下: CREATE INDEX indexName ON mytable(username(length)); ...

    2022年MySQL最新面试题,很全,已拿大厂 offer

    包括常见的面试题和答案 一、数据库基础知识 ...8、什么是最左前缀原则?什么是最左匹配原则 9、数据库为什么使用B+树而不是B树 10、非聚簇索引一定会回表查询吗? 11、有哪些情况, 索引会失效, 可以简单说说吗?

    mysql多条件索引

    然而,多列索引的使用有其特定规则,被称为“最左前缀原则”。这意味着查询条件必须从索引的第一列开始,并按照索引列的顺序匹配,直到遇到范围查询(如`, `LIKE`等)或者中间缺失某列。例如,对于`name, age, id`的...

    05深入浅出索引(下).pptx

    综上所述,理解索引的工作原理,特别是聚簇索引、二级索引、覆盖索引、联合索引以及最左前缀原则,对于优化数据库查询性能至关重要。在实际应用中,应根据查询模式和数据分布来设计合理的索引策略,以达到最佳的查询...

    MySQL组合索引与最左匹配原则详解

    之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章,自以为就了解了其原理,最近面试时和面试官交流,发现遗漏了些东西,这里自己整理一下这方面的内容。 什么时候创建组合索引? 当我们的where查询存在多个...

    20道mysql面试题.docx

    要有效利用复合索引,应在 WHERE 子句中优先使用索引的最左侧列,并避免使用 NOT 或 OR 运算符,这样可以遵循最左前缀原则,最大化索引利用率。 5. **锁机制**: MySQL 锁分为共享锁(读锁)和排他锁(写锁)。...

    关于MySQL面试题中有关索引的九大难点全在这里了

    数据结构维度 oB+树索引:所有数据存储在...o联合索引:多个字段创建的索引,使用时遵循最左前缀原则。 o唯一索引:索引列中的值必须是唯一的,但是允许为空值。 空间索引:MySQL5.7之后支持空间索引,在空间索引这方面

    mysql高性能索引查询(查询性能问题、索引使用原则、索引创建、索引的优点缺点).docx

    对于组合索引,查询时需要遵循最左前缀原则,即查询条件必须包含索引定义的最左侧字段。 ```sql SELECT * FROM ds_order WHERE create_time BETWEEN '2023-09-01' AND '2023-09-07' AND order_state = 2; ``` ####...

    05.深入浅出索引(下)1

    综上所述,理解和利用好索引的这些特性,如覆盖索引和最左前缀原则,对于优化MySQL数据库的查询性能至关重要。在实际操作中,我们需要根据业务需求和查询模式来设计和调整索引策略,以达到最佳的数据库性能。

    43道MySQL面试题集合(附答案)

    14. 谈谈你对最左前缀原则的理解? 15. 怎么知道创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因? 16. 什么情况下索引会失效?即查询不走索引? 17. 查询性能的优化方法? 18. InnoDB 和 ...

    MySQL索引背后的数据结构及算法原理.pdf

    在使用索引的策略上,理解数据库最左前缀原则非常重要。当创建复合索引时,查询通常从索引的最左侧开始匹配,如果查询条件不连续覆盖索引的最左部分,索引的效率会降低。选择性高的列作为索引字段可以提高区分度,...

    MySQL数据库经典面试题解析

    - 在使用联合索引时,查询时会尽可能使用索引中最左边的部分,这就是所谓的最左前缀原则。 5. **索引下推**: - 索引下推允许在索引遍历过程中对索引本身进行条件判断,从而减少访问表数据的次数,进一步提高查询...

    数据库相关知识了解(一)

    * 最左前缀原则:在创建索引时,选择最左边的列作为索引列。 * 最左匹配原则:在使用索引时,选择最左边的列作为匹配列。 索引的数据结构有: * B+树索引:是一种常见的索引数据结构,用于快速查找和检索数据。 * ...

    北邮数据库实验8 数据查询分析优化实验

    - **理论**:复合索引按字段顺序进行排序,查询时会优先使用索引的第一个字段,即最左前缀原则。 - **案例分析**:在`tbATUDataNew`上创建包含`Longitude`、`Latitude`、`CellID`的组合索引。比较有最左前缀索引和...

    04-VIP-Mysql索引优化实战一.zip

    对于多列索引,应遵循最左前缀原则,即查询条件应覆盖索引的最左侧列。 优化器的选择和查询重构也是提高索引效率的方法。MySQL优化器会选择执行计划,但并非总是最优。通过EXPLAIN命令,我们可以分析查询的执行计划...

    MySQL索引_011

    对于多列索引,数据库会首先根据最左前缀原则使用最左侧的列进行索引查找,然后逐列进行匹配。 索引虽然能提高查询效率,但也会占用额外的存储空间,并在插入、删除和更新记录时增加开销。因此,合理地设计和使用...

    SQLServer高频面试题及答案

    最左前缀原则是数据库中的一个设计原则,指的是在数据库设计中,应该尽量使用最左边的前缀来提高查询效率。 20. 什么是索引? 索引是数据库中的一个数据结构,可以用来提高数据查询效率。索引可以分为聚集索引和非...

Global site tag (gtag.js) - Google Analytics