精华内容
下载资源
问答
  • 最左前缀原则

    千次阅读 2015-07-20 21:32:54
    这两天看《构建高性能Web站点》这本书,感觉写的真是不错,很多实际项目中会碰到的问题都有所提及,今天看到一个最左前缀原则,以前也听说过,不过一直没搞明白,今天查了下。   通过实例理解单列索引、多列索引...

    这两天看《构建高性能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子句中使用最频繁的一列放在最左边。

    展开全文
  • 最左前缀原则查询主要针对组合索引,满足如下2个条件即可满足左前缀原则需要查询的列和组合索引的列顺序一致查询不要跨列构造数据如下,其中在name,address,country上建了联合索引CREATE TABLE `people` ( `name` ...
    b035a542bc3c991a1a07a8da82c5888a.png

    涉及到一些explain的知识点,不会的看这篇文章

    面试官:你是怎么用explain分析sql执行性能的?

    最左前缀原则

    查询

    主要针对组合索引,满足如下2个条件即可满足左前缀原则

    1. 需要查询的列和组合索引的列顺序一致
    2. 查询不要跨列

    构造数据如下,其中在name,address,country上建了联合索引

    CREATE TABLE `people` ( `name` varchar(50) NOT NULL, `address` varchar(50) NOT NULL, `country` varchar(50) NOT NULL, KEY `idx_name_addr_country` (`name`,`address`,`country`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    先简单举个例子,然后总结一下

    explain select * from people where name = "jack"and address = "beijing" and country = "china"
    767041db2ee2bb3d13df2461d355b849.png

    type为ref,key_len为456=(50*3+2)*3,联合索引的所有列都使用了

    explain select * from people where name = "jack"
    5bf03bf0a7b904f1bc24ebca530713a5.png

    type为ref,key_len为152=50*3+2,联合索引只使用了name列

    explain select * from people where address = "beijing"
    3bcfdc9b4d63d650d53b99ea778a0f6b.png

    type为index,并没有走索引,简单说一下index和ref的区别

    index:这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引,对这个索引并无特别要求,只要是索引,或者某个联合索引的一部分,mysql都可能会采用index类型的方式扫描。但是呢,缺点是效率不高,mysql会从索引中的第一个数据一个个的查找到最后一个数据,直到找到符合判断条件的某个索引。所以,上述语句会触发索引扫描

    ref:这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引,而不是会对索引中每一个数据都进行一一的扫描判断,也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找,索引却是有要求的,要实现这种能快速查找的算法,索引就要特定的数据结构。简单说,也就是索引字段的数据必须是有序的,才能实现这种类型的查找,才能利用到索引。

    总结几个典型的例子,联合索引为key idx_a_b_c(a,b,c)

    173cd9ab06a648bb01bfb0cc3f8c35c3.png

    排序

    最左前缀原则不仅用在查询中,还能用在排序中。MySQL中,有两种方式生成有序结果集:

    1. 通过有序索引顺序扫描直接返回有序数据
    2. Filesort排序,对返回的数据进行排序

    因为索引的结构是B+树,索引中的数据是按照一定顺序进行排列的,所以在排序查询中如果能利用索引,就能避免额外的排序操作。EXPLAIN分析查询时,Extra显示为Using index。

    所有不是通过索引直接返回排序结果的操作都是Filesort排序,也就是说进行了额外的排序操作。EXPLAIN分析查询时,Extra显示为Using filesort,当出现Using filesort时对性能损耗较大,所以要尽量避免Using filesort

    还是先举2个例子,然后总结

    explain select * from people order by name
    e06da918c0ef3496339add6305fefc2c.png

    Extra列只有Using index,即根据索引顺序进行扫描

    explain select * from people order by address
    7f1a464c972dd9864870e5711631d7c4.png

    Extra列有Using filesort,进行了额外排序

    总结:假如说有如下联合索引,key idx_a_b_c(a,b,c)

    order by 能使用索引排序

    order by aorder by a,border by a,b,corder by a desc, b desc, c descwhere a = const order by b,cwhere a = const and b = const order by cwhere a = const and b > const order by b,c

    order by 不能使用索引进行排序

    order by border by corder by b, corder by a asc, b desc, c desc //排序不一致where g = const order by b,c //丢失a索引where a = const order by c //丢失b索引where a = const order by a,d //d不是索引的一部分where a in (...) order by b,c //范围查询

    有时间会单开一篇文章介绍order by优化,这里只做个粗浅的介绍

    c55e1f87f938a750f43d884c0146516e.png

    联合索引特性

    增加开销。建一个联合索引(col1,col2,col3),实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大增加开销!

    覆盖索引。对联合索引(col1,col2,col3),如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据,而无需回表,这了很多的随机io操作。io操作,特别的随机io其实是dba主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。

    效率高。索引列越多,通过索引筛选出的数据越少。有1000W条数据的表,有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据,如果只有单值索引,那么通过该索引能筛选出1000W10%=100w条数据,然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据,然后再排序,再分页;如果是联合索引,通过索引筛选出1000w*10%*10% *10%=1w,效率提升可想而知!

    0bf279e50595a7d3dcb0cd4398aae979.png
    展开全文
  • innodb组合索引的最左前缀原则

    万次阅读 2020-08-21 16:37:16
    单列索引我之前已经写过一篇文章提过,今天我们主要讲一下怎么使用多列索引,和多列索引的最左前缀原则。 关于最左前缀的误解 早些年读高性能mysql这本书的时候,当年感觉书中对于最左前缀的描述相对来说晦涩一点,...

    我们在使用数据库的时候,为了增加访问速度,经常会给某张表增加一些索引。单列索引我之前已经写过一篇文章提过,今天我们主要讲一下怎么使用多列索引,和多列索引的最左前缀原则。

    关于最左前缀的误解

    早些年读高性能mysql这本书的时候,当年感觉书中对于最左前缀的描述相对来说晦涩一点,并且没有详细的例子来说明具体什么是最左前缀,所以在一段时间内我都以为where还要按照顺序写才能触发最左前缀,其实并不是这样。

    假设表结构如下

    CREATE TABLE `testindex` (
      `id` int(10) NOT NULL AUTO_INCREMENT,
      `name` varchar(25) NOT NULL DEFAULT '',
      `age` int(10) NOT NULL DEFAULT '0',
      `sex` tinyint(1) NOT NULL DEFAULT '1',
      PRIMARY KEY (`id`),
      KEY `multi_index` (`name`,`age`,`sex`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
    
    

    测试数据:

    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('1', 'zhangsan', '20', '1');
    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('2', 'zhangsan', '20', '0');
    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('3', 'zhangsan', '19', '1');
    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('4', 'zhangsan', '19', '0');
    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('5', 'lisi', '20', '1');
    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('6', 'lisi', '20', '0');
    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('7', 'lisi', '19', '1');
    INSERT INTO `test`.`testindex` (`id`, `name`, `age`, `sex`) VALUES ('8', 'lisi', '19', '0');
    
    

    这个表中,定义了一个多列索引(组合索引)名字为multi_index,分别由name,age,sex三个字段组成,那么如下几条语句是否会有差异呢:

    select id from testindex where name ='zhangsan' and age = 20 and sex = 1;
    select id from testindex where name ='zhangsan' and sex = 1 and age = 20;
    select id from testindex where age = 20 and name ='zhangsan' and sex = 1;
    

    使用explain来查看一下

    EXPLAIN select id from testindex where name ='zhangsan' and age = 20 and sex = 1;
    EXPLAIN select id from testindex where name ='zhangsan' and sex = 1 and age = 20;
    EXPLAIN select id from testindex where age = 20 and name ='zhangsan' and sex = 1;
    
    

    三条语句输出的结果一模一样,都是使用了multi_index,事实证明,mysql并不以字段在where中出现的先后顺序来判断是否触发多列索引。实际上我们想一下,mysql也不会做那么不灵活的功能出来。

    最左前缀是说的什么顺序

    最左前缀其实说的是,在where条件中出现的字段,如果只有组合索引中的部分列,则这部分列的触发索引顺序,是按照定义索引的时候的顺序从前到后触发,最左面一个列触发不了,之后的所有列索引都无法触发。这句话有点绕,我们来以上面三列的组合索引举例子,其索引中包含了三列

    • name
    • age
    • sex

    mysql在多列索引的时候不按照where的先后顺序,那么排列组合只有三种 如下:

    # 1.where中包含name age(谁放前面无所谓)
    EXPLAIN select id from testindex where name ='zhangsan' and age = 20;
    # 2.where中包含name sex(谁放前面无所谓)
    EXPLAIN select id from testindex where name ='zhangsan' and sex = 1;
    # 3.where中包含age sex(谁放前面无所谓)
    EXPLAIN select id from testindex where age =20 and sex = 1;
    

    其中3就没有使用索引了,也就是说索引顺序是(name,age,sex)的手,where条件中如果没有name,即使有age和sex字段也不能触发索引。其扫描的条数是8行

    那应该会有人想问,那如果有name和sex,中间跨过了age,是能触发索引呢,还是不能触发索引呢。答案是能!但是只能使用name部分的索引来过滤数据。

    在explain中的rows中证明了这一点,其中1的扫描行数是2行,2的扫描行数是4行。就是因为没有使用到sex这一列。

    最左前缀的后置模糊搜索

    EXPLAIN select id from testindex where name like 'zhang%' and age = 20;
    
    

    explain的结果证明,扫描行数为4行。也就是说如果使用了like这种不完全匹配的,后面的索引就失效了。为了针对这种类似的情况,mysql5.6之后,除了一种新功能叫索引下推。这个以后再进行介绍。

    索引覆盖

    有那么一种情况下,你想要查询的所有列,已经包含在你的组合索引的字段中,本来二级索引是只存储了索引数据和主键,如果需要的数据索引中不能满足,就需要回表查询数据。而这种索引覆盖了所有想要的列的情况,就就不需要再回表查询具体的数据。这就是索引覆盖。

    展开全文
  • 简单整理记录下,之前一直都没有关注过这个问题最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上,注:如果第一个字段是范围查询需要单独建一个索引注:在创建多列索引时,要根据业务...

    简单整理记录下,之前一直都没有关注过这个问题

    最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上,

    注:如果第一个字段是范围查询需要单独建一个索引

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

    当创建(a,b,c)复合索引时,相当于创建了(a)单列索引,(a,b)组合索引以及(a,b,c)组合索引

    想要索引生效的话,只能使用 a和a,b和a,b,c三种组合

    实例:以下是常见的几个查询:

    mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' ;

    mysql>SELECT `a`,`b`,`c` FROM A WHERE  `b`='bb' AND `c`='cc';

    mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' AND `c`='cc';

    请问:想要索引最大化的使用需要至少建几个索引

    答:需要建立两个复合索引:a,c、b,c

    MySQL 的索引和最左前缀原则

    这两天看这本书,感觉写的真是不错,很多实际项目中会碰到的问题都有所提及,今天看到一个最左前缀原则,以前也听说过,不过一直没搞明白,今天查了下. 通过实例理解单列索引 ...

    MySQL数据库中的索引(二)——索引的使用,最左前缀原则

    上文中,我们了解了MySQL不同引擎下索引的实现原理,在本文我们将继续探讨一下索引的使用以及优化. 创建索引可以大大提高系统的性能. 第一,通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性. ...

    MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序

    MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序   What's Index ? 索引就是帮助RDBMS高效获取数据的数据结构. 索引可以让我们避免一行一行进行全表扫描.它的 ...

    mysql 最左前缀匹配原则

    1.在mysql建立联合索引时会遵循最左前缀匹配的原则,即最左优先,在检索数据时从联合索引的最左边开始匹配,示例:对列col1.列col2和列col3建一个联合索引 KEY index_col1_co ...

    【转】MYSQL数据库四种索引类型的简单使用--MYSQL组合索引“最左前缀”原则

    MYSQL数据库索引类型包括普通索引,唯一索引,主键索引与组合索引,这里对这些索引的做一些简单描述: (1)普通索引 这是最基本的MySQL数据库索引,它没有任何限制.它有以下几种创建方式: 创建索引 ...

    MySQL 最左前缀(Leftmost Prefix) & 组合索引(复合索引,多列索引)

    资料来源于网络,仅供参考学习. CREATE TABLE test(a INT,b INT,c INT,KEY idx(a,b,c)); 优: SELECT * FROM test WHERE a=1 ...

    mysql多列索引和最左前缀

    数据库的索引可以加快查询速度,原因是索引使用特定的数据结构(B-Tree)对特定的列额外组织存放,加快存储引擎(索引是存储引擎实现)查找记录的速度.索引优化是数据库优化的最重要手段. 如果查询语句使用 ...

    mysql索引之一:索引基础(B-Tree索引、哈希索引、聚簇索引、全文(Full-text)索引区别)(唯一索引、最左前缀索引、前缀索引、多列索引)

    没有索引时mysql是如何查询到数据的 索引对查询的速度有着至关重要的影响,理解索引也是进行数据库性能调优的起点.考虑如下情况,假设数据库中一个表有10^6条记录,DBMS的页面大小为4K,并存储10 ...

    MySQL索引解析(联合索引/最左前缀/覆盖索引/索引下推)

    本节内容: 1)索引基础 2)索引类型(Hash索引.有序数组.B+树) 3)索引的几个常见问题 1)联合索引 2)最左前缀原则 3)覆盖索引 4)索引下推 1. 索引基础 索引对查询的速度有着至关重 ...

    随机推荐

    PHP环境配置

    PHP环境配置 1.Apache的安装 第一步: 1.    双击httpd-2.2.17-win32-x86-no_ssl.msi.出现 Windows 标准的软件安装欢迎界面,直接点“Next”继 ...

    [java基础]java中static关键字

    1.static概念 static是静态修饰符,什么叫静态修饰符呢?大家都知道,在程序中任何变量或者代码都是在编译时由系统自动分配内存来存储的,而所谓静态就是指在编译后所分配的内存会一直存在,直到程序 ...

    form表单提交和ajax提交的区别

    form表单是整个页面跳到服务器的地址然后提交数据: ajax是往这个地址post数据

    front-end

    http://info.1688.com/detail/1139720782.html http://segmentfault.com/q/1010000000136513 http://h5apps ...

    WebGL展示3D房屋内景

    展开全文
  • 通过实例理解单列索引、多列索引以及最左前缀原则实例:现在我们想查出满足以下条件的用户id:mysql>SELECT `uid` FROM people WHERE lname`=’Liu’ AND `fname`=’Zhiqun’ AND `age`=26因为我们不想...
  • 联合索引有一个最左前缀原则,所以建立联合索引的时候,这个联合索引的字段顺序非常重要下面写了例子说明这个:CREATE TABLE `test_myisam` (`id` int(11) NOT NULL AUTO_INCREMENT,`conference_id` varchar(200) ...
  • 关于mysql最左前缀原则

    万次阅读 多人点赞 2018-09-25 15:59:00
    最左前缀原则主要使用在联合索引中 数据库版本Mysql5.5.53 1.首先准备如下测试数据表 CREATE TABLE `student` ( `id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, `cid` int(11) DEFA...
  • 联合索引有一个最左前缀原则,所以建立联合索引的时候,这个联合索引的字段顺序非常重要下面写了例子说明这个:CREATE TABLE `test_myisam` ( `id` int(11) NOT NULL AUTO_INCREMENT, `conference_id` varchar(200) ...
  • MySql最左前缀原则

    2019-12-09 18:25:04
    最左前缀原则 通过实例理解单列索引、多列索引以及最左前缀原则 实例:现在我们想查出满足以下条件的用户id: mysql>SELECT `uid` FROM people WHERE lname`=’Liu’ AND `fname`=’Zhiqun’ AND `age`=...
  • 所谓最左前缀原则,顾名思义,就是在复合索引中从最左列使用索引。MySql一次查询只能使用一个索引,如果针对多列条件查询,请建复合索引。MySql一张表最多有16个索引,一个复合索引最多有16列,索引长度最大为256个...
  • 本文重在讲清啥是mysql中索引的最左前缀原则,并不在索引,索引这东西太大了,有点消化不良,不敢讲。首先,我是参考 SQL 常用优化手段总结 - 索引的应用 这位大哥的sql语句作为基础的,但是觉得他的讲解方式让我...
  • 使用OR(and)搜索时,条件顺序重要(最左前缀原则,索引失效,需要将索引放左边) 以下是题目内容和知识点: package org.j.mysql; /** * @author *** * @version 1.0 * @description 一个国家如果面积超过300万...
  • 索引的最左前缀原则

    2019-02-13 10:35:00
    索引的最左前缀原则 索引的最左前缀原则 原理 mysql建立多列索引(联合索引)有最左前缀的原则,即最左优先,如: 如果有一个2列的索引(col1,col2),则已经对(col1)、(col1,col2)上建立了索引; 如果有...
  • 数据库最左前缀原则

    2019-10-05 22:08:53
    最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用最频繁的一列放在最...
  • MySQL 索引最左前缀原则 索引最佳左前缀法则:带头大哥不能死、中间兄弟不能断 1、准备数据 建表 CREATE TABLE IF NOT EXISTS staff ( id INT PRIMARY KEY auto_increment, name VARCHAR(50), age INT, pos ...
  • 1. 关于索引的最左前缀原则 今天学习了下关于索引的最左前缀的原理,小有成就感,在这里做一个学习记录,以后学习的时候可以直接找出来复习。 相信熟悉数据库的大佬们跟索引达人们肯定都了解最索引的左前缀原理,...
  • 数据库索引最左前缀原则

    千次阅读 2018-11-30 12:53:53
    数据库最左前缀原则 最左前缀原则:顾名思义是最左优先,以最左边的为起点任何连续的索引都能匹配上, 注:如果第一个字段是范围查询需要单独建一个索引 注:在创建多列索引时,要根据业务需求,where子句中使用...
  • MySQL索引 索引分类 最左前缀原则 覆盖索引 索引下推 联合索引顺序What's Index ?索引就是帮助RDBMS高效获取数据的数据结构。索引可以让我们避免一行一行进行全表扫描。它的价值就是可以帮助你对数据进行快速定位。...
  • 最左前缀原则:简述:查询条件中从索引的最左前列开始,并且不跳过索引中的某个列或多个列。建表:CREATE TABLE`index_test` (`id`int(11) NOT NULLAUTO_INCREMENT,`title`char(255) NOT NULL DEFAULT '' COMMENT '...
  • 联合索引: (1)也称多列索引、复合索引。 (2)联合索引就是把多个列按照顺序放到一个索引树上。... 最左前缀原则:按照最左边的列开始的顺序并且不能跳过其中的列。 如:(name, age,posi...
  • 问题最近,在 mysql 测试最左前缀原则,发现了匪夷所思的事情。根据最左前缀原则,本来应该索引失效,走全表扫描的,但是,却发现可以正常走索引。表结构如下( Mysql 版本 5.7.22):CREATE TABLE `user` (`id` int...
  • 问题最近,在 mysql 测试最左前缀原则,发现了匪夷所思的事情。根据最左前缀原则,本来应该索引失效,走全表扫描的,但是,却发现可以正常走索引。表结构如下( Mysql 版本 5.7.22):CREATE TABLE `user` (`id` int...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,084
精华内容 433
关键字:

最左前缀原则