精华内容
下载资源
问答
  • MySQL 索引

    2021-03-02 07:52:06
    Re: MySQL 索引 ============================ # 索引的作用 索引用于快速找出在某个列中有一特定值的行。不使用索引MySQL 就会从第1条记录开始读完整个表,直至找出相关的行。表越大,查询数据所花费的时间越多...
  • mysql索引

    千次阅读 2020-02-01 18:15:08
    最近学习了关于mysql索引的一些知识,再次做一个总结。首先建立mysql索引的作用是为了更快访问一些数据,mysql索引文件是存储在磁盘上的文件。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的...

    最近学习了关于mysql索引的一些知识,再次做一个总结。首先建立mysql索引的作用是为了更快访问一些数据,mysql索引文件是存储在磁盘上的文件。实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。

    目录

    mysql索引的数据结构类型:

    1.平衡二叉树:

    2.B树:

    3.B+树:

    4.Hash索引:

    mysql存储引擎:

    1.InnoDB(聚簇索引)

    2.MyIsam(非聚簇索引)

    3.联合索引 


    mysql索引的数据结构类型:

    1.平衡二叉树:

    平衡二叉树在数据结构中属于比较好理解的部分,按照键值的大小将索引存储在二叉树合适的位置,具体的存储方法就和平衡二叉树的存储是一样的。需要注意左右子树的高度差,进行二叉树的旋转保证二叉树的平衡。

    2.B树:

    特性如下图所示

    可以看到B树的每个结点内部都要存储一个指向关键字代表的具体数据的指针,因为每个盘块的大小是固定的,这样使得每个结点的度相对应较小,如果每个磁盘只存储对应的主键值或者只存储指向具体数据的指针这样可以解决存储空间。

    B树的存储方式很接近二叉树,这样会导致有些数据的查找较慢,比如有些数据的深度较大。采用B+树的方式使得查找更加稳定。

    3.B+树:

    B+树很好的弥补了B树的一些缺点提高了的查找效率和存储效率。

     

    4.Hash索引:

    和数据结构中的hash查找类似,通过计算关键字的hash值查找该数据对应的地址,可以理解为通过hash值并对hash值进行相应的处理确定数据的存储位置,如果发生数据冲突,解决的办法为链地址法。

    mysql存储引擎:

    1.InnoDB(聚簇索引)

    InnoDB的一个数据表中,它的索引文件和数据文件是同一个,也就是说,在查询时只要找到了索引就找到了数据文件。在InnoDB中及存储主键索引值,有存储行数据,叫做聚簇索引 。

    聚簇索引会将磁盘数据按照一列或者多列的值进行排序,通过是按照主键内一列的值进行排序的。聚簇索引的特点是存储的顺序和索引的顺序一致,一张表中只存在一个聚簇索引。

    2.MyIsam(非聚簇索引)

    非聚簇索引也就是数据和索引分开存储的

    3.联合索引 

    最左匹配原则,按照联合索引进行查找的原则是,查找字段的关键字中必须按照索引设定顺序出现才能按照索引进行查找。也就是说查找的关键字序列中出现了某一个关键字,但是该关键字在索引中的前一个关键字并未出现,则不能按照索引查找。

    比如我为一个字段设置了ABC三个关键字为联合索引。那么当我的查询条件出现了BC而没有写出A,那么将无法使用的索引的方式进行查询。

    展开全文
  • mysql 索引

    千次阅读 2019-07-26 16:34:11
    1.mysql索引的分列方式: ①单列索引:一个索引只包含单个列,一个表可以有多个单列索引 ②组合索引:一个索引包含多个列 2.索引的创建需要确保索引是引用在sql查询语句上 3.增加索引的优点: ①可以大大提高...

    1.mysql索引的分列方式:

    ①单列索引:一个索引只包含单个列,一个表可以有多个单列索引

    ②组合索引:一个索引包含多个列

    2.索引的创建需要确保索引是引用在sql查询语句上

    3.增加索引的优点:

    ①可以大大提高查询效率

    ②可以提高Mysql的检索速度

    4.增加索引的缺点:

    ①增加索引会降低更新表的速度,如执行insert,update,不仅要保存数据,还要保存索引文件

    ②索引会占用磁盘空间的索引文件

    5.索引创建语句:create index indecName on tableName(userName(length))

    6.修改表结构(添加索引):alter table tableName add index indexName(columnName)

    7.删除索引:drop index indexName on tableName

    8.唯一索引

      (1)唯一索引与其他索引类似,不同的是唯一索引的列值必须唯一,但允许有空值,如果是组合索引,则列值的组合必须唯一

      (2)创建索引:create unique index indexName on tableName(columnName(length))

      (3)修改表结构(增加索引): alter  table tableName add unique [indexName] (columnName(length))

     (4)删除索引: alter  table tableName drop index indexName

     

     

    展开全文
  • MySQL索引实现原理分析

    万次阅读 多人点赞 2018-10-10 17:59:07
    MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的...

           目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:image.png这里设表一共有三列,假设我  

            在 MySQL 中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论 MyISAM 和 InnoDB 两个存储引擎的索引实现方式。

    MyISAM 索引实现 

    MyISAM 引擎使用 B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。下图是 MyISAM 索引的原理图:
    MySQL索引实现原理分析

    这里设表一共有三列,假设我们以 Col1 为主键,则图 8 是一个 MyISAM 表的主索引(Primary key)示意。可以看出 MyISAM 的索引文件仅仅保存数据记录的地址。

    辅助索引 

    在 MyISAM 中,主索引和辅助索引(Secondary key)在结构上没有任何区别,只是主索引要求 key 是唯一的,而辅助索引的 key 可以重复。如果我们在 Col2 上建立一个辅助索引,则此索引的结构如下图所示


    MySQL索引实现原理分析

    同样也是一颗 B+Tree,data 域保存数据记录的地址。因此,MyISAM 中索引检索的算法为首先按照 B+Tree 搜索算法搜索索引,如果指定的 Key 存在,则取出其data 域的值,然后以 data 域的值为地址,读取相应数据记录。

    MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。


    InnoDB 索引实现 

    虽然 InnoDB 也使用 B+Tree 作为索引结构,但具体实现方式却与 MyISAM 截然不同。

    1.第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址

    而在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。


    MySQL索引实现原理分析

    上图是 InnoDB 主索引(同时也是数据文件)的示意图,可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为 InnoDB 的数据文件本身要按主键聚集,

     1 .InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

     同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:


    MySQL索引实现原理分析

    这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

     2.第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。
    例如,图 11 为定义在 Col3 上的一个辅助索引:


    MySQL索引实现原理分析
     

    聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引(回表):首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

     引申:为什么不建议使用过长的字段作为主键?

     因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。


    聚簇索引与非聚簇索引 

    InnoDB 使用的是聚簇索引, 将主键组织到一棵 B+树中, 而行数据就储存在叶子节点上, 若使用"where id = 14"这样的条件查找主键, 则按照 B+树的检索算法即可查找到对应的叶节点, 之后获得行数据。 若对 Name 列进行条件搜索, 则需要两个步骤:
    第一步在辅助索引 B+树中检索 Name, 到达其叶子节点获取对应的主键。
    第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。

    MyISM 使用的是非聚簇索引, 非聚簇索引的两棵 B+树看上去没什么不同, 节点
    的结构完全一致只是存储的内容不同而已, 主键索引 B+树的节点存储了主键, 辅助键索引B+树存储了辅助键。 表数据存储在独立的地方, 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据, 对于表数据来说, 这两个键没有任何差别。 由于索引树是独立的, 通过辅助键检索无需访问主键的索引树。

    为了更形象说明这两种索引的区别, 我们假想一个表如下图存储了 4 行数据。 其中Id 作为主索引, Name 作为辅助索引。 图示清晰的显示了聚簇索引和非聚簇索引的差异


    MySQL索引实现原理分析

     

    联合索引及最左原则

    联合索引存储数据结构图:

    最左原则:

    例如联合索引有三个索引字段(A,B,C)

    查询条件:

    (A,,)---会使用索引

    (A,B,)---会使用索引

    (A,B,C)---会使用索引

    (,B,C)---不会使用索引

    (,,C)---不会使用索引

     

    *最后来一个问题:mysql假设一行数据大小为1k,则一颗层高为3的b+树可以存放多少条数据?

    mysql页默认大小16k,如果数据行大小1k,叶子节点存放的完整数据,则叶子节点一页可以放16条数据;非叶子节点页面存放的是主键和指针,所以主要看主键是啥类型,假设是integer,则长度8字节,指针大小在innodb是6字节,一共14字节,所以非叶子节点每页可以存16384/14=1170个主键数据(1170个分叉),则三层b+树数据可以存1170*1170*16=21902400条数据。(千万级别)

    展开全文
  • MySQL索引的创建与使用

    万次阅读 多人点赞 2018-08-06 18:49:59
    声明:本人主要简单示例MySQL中的单列索引、组合索引的创建与使用。 索引的创建: 建表时创建: CREATE TABLE 表名( 字段名 数据类型 [完整性约束条件], ……, [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY ...

    索引有很多,且按不同的分类方式,又有很多种分类。不同的数据库,对索引的支持情况也不尽相同。

    声明:本人主要简单示例MySQL中的单列索引组合索引的创建与使用。


    索引的创建

    建表时创建

    CREATE TABLE 表名(

    字段名 数据类型 [完整性约束条件],

           ……,

    [UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY

    [索引名](字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

    );

    说明:

    • UNIQUE:可选。表示索引为唯一性索引。
    • FULLTEXT:可选。表示索引为全文索引。
    • SPATIAL:可选。表示索引为空间索引。
    • INDEX和KEY:用于指定字段为索引,两者选择其中之一就可以了,作用是    一样的。
    • 索引名:可选。给创建的索引取一个新名称。
    • 字段名1:指定索引对应的字段的名称,该字段必须是前面定义好的字段。
    • 长度:可选。指索引的长度,必须是字符串类型才可以使用。
    • ASC:可选。表示升序排列。
    • DESC:可选。表示降序排列。

    注:索引方法默认使用B+TREE。

    单列索引(示例):

    CREATE TABLE projectfile (
    	id INT AUTO_INCREMENT COMMENT '附件id',
    	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    	projectid INT COMMENT '项目id;此列受project表中的id列约束',
    	filename VARCHAR (512) COMMENT '附件名',
    	fileurl VARCHAR (512) COMMENT '附件下载地址',
    	filesize BIGINT COMMENT '附件大小,单位Byte',
    	-- 主键本身也是一种索引(注:也可以在上面的创建字段时使该字段主键自增)
            PRIMARY KEY (id),
    	-- 主外键约束(注:project表中的id字段约束了此表中的projectid字段)
    	FOREIGN KEY (projectid) REFERENCES project (id),
    	-- 给projectid字段创建了唯一索引(注:也可以在上面的创建字段时使用unique来创建唯一索引)
    	UNIQUE INDEX (projectid),
    	-- 给fileuploadercode字段创建普通索引
    	INDEX (fileuploadercode)
    	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

    注:这里只为示例如何创建索引,其他的合理性之类的先放一边。

    组合索引(示例):

    CREATE TABLE projectfile (
    	id INT AUTO_INCREMENT COMMENT '附件id',
    	fileuploadercode VARCHAR(128) COMMENT '附件上传者code',
    	projectid INT COMMENT '项目id;此列受project表中的id列约束',
    	filename VARCHAR (512) COMMENT '附件名',
    	fileurl VARCHAR (512) COMMENT '附件下载地址',
    	filesize BIGINT COMMENT '附件大小,单位Byte',
    	-- 主键本身也是一种索引(注:也可以在上面的创建字段时使该字段主键自增)
            PRIMARY KEY (id),
            -- 创建组合索引
    	INDEX (fileuploadercode,projectid)
    	-- 指定使用INNODB存储引擎(该引擎支持事务)、utf8字符编码
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT '项目附件表';

    建表后创建

    ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL]  INDEX | KEY  [索引名] (字段名1 [(长度)] [ASC | DESC]) [USING 索引方法]

    CREATE  [UNIQUE | FULLTEXT | SPATIAL]  INDEX  索引名 ON  表名(字段名) [USING 索引方法]

    示例一:

    -- 假设建表时fileuploadercode字段没创建索引(注:同一个字段可以创建多个索引,但一般情况下意义不大)
    -- 给projectfile表中的fileuploadercode创建索引
    ALTER TABLE projectfile ADD UNIQUE INDEX (fileuploadercode);

    示例二:

    ALTER TABLE projectfile ADD INDEX (fileuploadercode, projectid);

    示例三:

    -- 将id列设置为主键
    ALTER TABLE index_demo ADD PRIMARY KEY(id) ;
    -- 将id列设置为自增
    ALTER TABLE index_demo MODIFY id INT auto_increment;  

    查看已创建的索引

    show index from 表名;

    提示:我们也可以直接使用工具查看

    示例:


    索引的删除

    DROP INDEX 索引名 ON 表名

    ALTER TABLE 表名 DROP INDEX 索引名

    示例一:

    drop index fileuploadercode1 on projectfile;

    示例二:

    alter table projectfile drop index s2123;

    查看SQL语句对索引的使用情况(即:查询SQL的查询执行计划QEP)

    在select语句前加上EXPLAIN即可。

    示例:

    EXPLAIN SELECT * FROM `index_demo` ii WHERE ii.e_name = 'Jane';

    分析该SQL的性能为:

    提示:我们也可以使用SQL工具查看,如:navicat中的“解释”选项即可查看。

    说明:

    id:SELECT识别符。这是SELECT的查询序列号。

    select_type:SELECT类型。

    1. SIMPLE: 简单SELECT(不使用UNION或子查询)
    2. PRIMARY: 最外面的SELECT
    3. UNION:UNION中的第二个或后面的SELECT语句
    4. DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
    5. UNION RESULT:UNION的结果
    6. SUBQUERY:子查询中的第一个SELECT
    7. DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
    8. DERIVED:导出表的SELECT(FROM子句的子查询)

    table:表名

    type:联接类型。是SQL性能的非常重要的一个指标,结果值从好到坏依次是:system > const > eq_ref > ref
                > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。
                一般来说,得保证查询至少达到range级别。

    1. system:表仅有一行(=系统表)。这是const联接类型的一个特例。
    2. const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const用于用常数值比较PRIMARY KEY或UNIQUE索引的所有部分时。
    3. eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。eq_ref可以用于使用= 操作符比较的带索引的列。比较值可以为常量或一个使用在该表前面所读取的表的列的表达式。
    4. ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。如果联接只使用键的最左边的前缀,或如果键不是UNIQUE或PRIMARY KEY(换句话说,如果联接不能基于关键字选择单个行的话),则使用ref。如果使用的键仅仅匹配少量行,该联接类型是不错的。ref可以用于使用=或<=>操作符的带索引的列。
    5. ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。在解决子查询中经常使用该联接类型的优化。
    6. index_merge:该联接类型表示使用了索引合并优化方法。在这种情况下,key列包含了使用的索引的清单,key_len包含了使用的索引的最长的关键元素。
    7. unique_subquery:该类型替换了下面形式的IN子查询的ref:value IN (SELECT primary_key FROMsingle_table WHERE some_expr);unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
    8. index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:value IN (SELECT key_column FROM single_table WHERE some_expr)
    9. range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引。key_len包含所使用索引的最长关键元素。在该类型中ref列为NULL。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range
    10. index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
    11. all:对于每个来自于先前的表的行组合,进行完整的表扫描。如果表是第一个没标记const的表,这通常不好,并且通常在它情况下很差。通常可以增加更多的索引而不要使用ALL,使得行能基于前面的表中的常数值或列值被检索出。

    possible_keys:possible_keys列指出MySQL能使用哪个索引在该表中找到行。注意,该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。

    key:key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

    key_len:key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。注意通过key_len值我们可以确定MySQL将实际使用一个多部关键字的几个部分。

    ref:ref列显示使用哪个列或常数与key一起从表中选择行。

    rows:rows列显示MySQL认为它执行查询时必须检查的行数。

    Extra:该列包含MySQL解决查询的详细信息。

    1. Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
    2. Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
    3. range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。对前面的表的每个行组合,MySQL检查是否可以使用range或index_merge访问方法来索取行。
    4. Using filesort:MySQL需要额外的一次传递,以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序,并按排序顺序检索行。
    5. Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。当查询只使用作为单一索引一部分的列时,可以使用该策略。
    6. Using temporary:为了解决查询,MySQL需要创建一个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时。
    7. Using where:WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行,如果Extra值不为Using where并且表联接类型为ALL或index,查询可能会有一些错误。
    8. Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
    9. Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    单列索引的使用

    准备工作

    给id加主键索引:

    再分别给name、city、country、address加上普通索引:

    注:以上五个索引都是单列索引。

    使用情况

    只涉及到其中的一个字段时,都能使用到索引(以e_name为例):

     

    注:模糊查询时,%如果在前面,那么不会使用索引。

    涉及到多个索引字段时,如果这些索引字段中,存在主键索引,那么只会使用该索引(即:MYSQL优化器会选出并先执行最“严”的索引):

    提示:possible_key中,只是SQL语句里涉及到的索引;key中才是实际上执行查询操作时使用到了的索引。

    涉及到多个索引字段时,如果这些索引字段中,不存在主键索引的话,那么就会使用该使用的索引(注:如果通过其中的部分索引就能准确定位的话,那么其余的索引就不再被使用):

     

    注:多个索引时,先使用哪个索引后使用哪个索引,是由MySQL的优化器经过一些列计算后作出的抉择

    当对索引字段进行 >, <>= <=not inbetween …… and ……函数(索引字段)like模糊查询%在字段前时不会使用该索引

    注:这里对e_age字段进行了 “<” ,所以实际查询时,并没有使用e_age的索引。

    提示:在实际使用时,如果涉及到多列,我们一般都不会将这些列一 一创建为单列索引,而是将这些列创建为组合索引


    组合索引的使用

    最左原则

           假设组合索引为:a,b,c的话;那么当SQL中对应有:aa,ba,b,c的时候,可称为完全满足最左原则;当SQL中查询条件对应只有a,c的时候,可称为部分满足最左原则;当SQL中没有a的时候,可称为不满足最左原则

    注:MySQL5.7开始,会自动优化,如:会把c,b,a优化为a,b,c使之完全遵循最左原则;会把c,a优化为a,c使之部
           分遵循最左原则
    。即:SQL语句中的对应条件的先后顺序无关。

    准备工作

    创建了组合索引:e_name,e_age,e_country,e_city

    使用情况

    完全满足最左原则

    注:与条件的先后无关(这是因为MYSQL5.7开始,对索引全排列有优化,会自动优化为按组合索引的顺序进行查询)
           即:下面这样的话,也是会完整的走组合索引的:

    部分满足最左原则

    注:此SQL中,只有e_name和e_country满足部分最左原则(e_name满足),所以到e_name字段时会走组合所以,但是
           只会走到e_name那里,到e_country时就不会使用组合索引了。

    不满足最左原则

    满足(部分满足)最左原则的字段里,有字段不满足“索引”自身的使用规范

    说明:如果SQL语句里的字段里,满足了最左原则,但是不满足“索引”自身的使用规范,那么组合索引走到这里之后,
               不会再往下走了。

    如图所示:由于e_age字段使用了“>”符号,不符合“索引”自身的使用规范,那么当“e_name”走完组合索引后,
                      走到“e_age”时,该字段及其后面的字段不会再走组合索引了。


    【补充】使用组合索引时,不遵循最左原则仍然会走索引的特殊种情况

    相关概念

    聚集索引与非聚集索引

           每个InnoDB表具有一个特殊的索引称为聚簇索引(也叫聚集索引,聚类索引,簇集索引)。如果表上定义有主键,该主键索引就是聚簇索引。如果未定义主键,MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键,InnoDB使用它作为聚簇索引。如果没有这样的列,InnoDB就自己产生一个这样的ID值,它有六个字节,而且是隐藏的,使其作为聚簇索引。

           表中的聚簇索引(clustered index )就是一级索引,除此之外,表上的其他非聚簇索引都是二级索引,又叫辅助索引(secondary indexes)。

    回表

            当二级索引无法直接查询到(SQL中select需要的所有)列的数据时,会通过二级索引查询到聚簇索引(即:一级索引)后,再根据(聚集索引)查询到(二级索引中无法提供)的数据,这种通过二级索引查询出一级索引,再通过一级索引查询(二级索引中无法提供的)数据的过程,就叫做回表。

    当无需回表时,不遵循最左原则也是会走组合索引

    如,现有表:

    id是主键,其余三个字段组成联合索引:

    当不需要进行回表时,即便我们的SQL不满足组合索引最左原则,也会走组合索引的,如:

            这里where后直接是gender时, 是不遵循组合索引的最左原则的,但是查询计划显示使用了索引的。这是因为: 对这张表进行select *,相当于进行select id,name,age,gender,其中,id是主键(一级索引),name、age、gender是组合索引(二级索引),这里查询时,能直接从索引中拿到想要查询的所有列的数据,是不需要回表查询的,所以这里哪怕sql写法上不遵循最左原则,但是仍然是会走索引的。

    如果这个时候,我们加一个普通的motto字段:

     

    使用相同的SQL进行查询,可看到:

     

            此时进行select *,相当于进行select id,name,age,gender,motto,其中motto字段是从索引(一级索引、二级索引)里面获取不到数据的,是肯定需要回表的。而查询条件又不遵循最左原则,所以不会走组合索引。

    注:其它情况下,只有(完全或部分)遵循了最左原则,才会走组合索引


    ^_^ 如有不当之处,欢迎指正

    ^_^ 参考链接
                  https://www.cnblogs.com/DreamDrive/p/7752960.html
                  https://www.cnblogs.com/tommy-huang/p/4317305.html
                  https://blog.csdn.net/linjpg/article/details/56054994
                  https://www.jb51.net/article/118371.html

                  https://www.csdn.net/gather_2a/MtTaMgwsNzY0OS1ibG9n.html

    ^_^ 如涉及侵权问题,请及时联系我

    ^_^ 本文已经被收录进《程序员成长笔记(二)》,笔者JustryDeng

    展开全文
  • 一文搞懂MySQL索引(清晰明了)

    万次阅读 多人点赞 2021-02-02 17:30:43
    MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 MySQL中常用的索引结构(索引底层的数据结构)有:B-TREE ,B+TREE ,HASH 等。 MySQL 的索引有两种分类方式:逻辑分类和物理...
  • 一文搞懂MySQL索引所有知识点(建议收藏)

    万次阅读 多人点赞 2020-10-24 12:19:05
    Mysql索引 索引介绍 索引是什么 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 一般来说索引本身也很大,不可能全部存储在内存中,因此...
  • mysql进阶(二十六)MySQL 索引类型(初学者必看)  索引是快速搜索的关键。MySQL 索引的建立对于 MySQL 的高效运行是很重要的。下面介绍几种常见的 MySQL 索引类型。   在数据库表中,对字段建立索引可以大大...
  • Mysql 索引

    千次阅读 2012-03-28 17:15:13
    什么是索引 索引时一种特殊的文件,他们包涵着对数据表里所有记录的引用指针。 当对数据表记录进行更新后,都会对索引进行刷新。 索引会占用相当大的空间,应该只为经常查询和最经常排序的数据列建立索引。 ...
  • Mysql索引:图文并茂,深入探究索引的原理和使用

    万次阅读 多人点赞 2020-11-25 16:43:44
    关于Mysql索引的走心总结,建议收藏,反复阅读。
  • MySQL索引原理、失效情况

    万次阅读 多人点赞 2019-04-19 17:19:17
    1 mysql索引知识 1.1 B+Tree索引 1.2 主键索引和普通索引的区别 1.3 唯一索引vs普通索引 2 mysql索引优化 2.1 查看索引使用情况 2.2 mysql索引使用策略 2.3 mysql索引使用原则 1 mysql索引知识 1.1 ...
  • MySQL索引优化

    千次阅读 2021-02-19 09:42:44
    一、MySQL索引基础 本文主要讨论MySQL索引的部分知识。将会从MySQL索引基础、索引优化实战和数据库索引背后的数据结构三部分相关内容。 一、MySQL索引基础 首先,我们将从索引基础开始介绍一下什么是索引,分析...
  • GitHub 4.8k Star 的Java工程师成神之路 ,不来了解一下吗? GitHub 4.8k Star 的Java工程师成神之路 ,真的不来...相信很多人对于MySQL索引都不陌生,索引(Index)是帮助MySQL高效获取数据的数据结构。 因为索引...
  • MySQL 索引结构

    千次阅读 多人点赞 2021-05-26 20:42:33
    在上一篇 MySQL 索引类型 中,我们已经了解了索引的基本概念以及分类,那么,索引的结构是什么样的?为什么索引可以这么快?这一篇文章将继续探讨索引的实现原理和数据结构。 文章目录前言索引数据结构二叉树的局限...
  • 曾经,我以为我很懂MySQL索引

    万次阅读 多人点赞 2020-08-26 09:04:17
    MySQL索引,我们真的了解么?腾讯云数据库负责人林晓斌说过:“我们面试MySQL同事时只考察两点,索引和锁”。言简意赅,MySQL索引的重要性不言而喻。MySQL索引历经了多个版本的迭代,从语法到底层数据结构都有很多...
  • MySQL索引的使用实例

    千次阅读 多人点赞 2020-10-24 13:11:43
    MySQL索引的使用实例一. 慢查询日志二. 查询分析器——explain三. 索引的基本使用四. 复合索引五. 覆盖索引 一. 慢查询日志 //查看是否开启慢查询日志 mysql> show variables like '%slow%'; //临时开启慢查询...
  • MySQL索引创建及使用

    万次阅读 多人点赞 2019-03-12 17:56:59
    背景 最近在深化学习数据库相关知识,借此机会总结记录一番,本来想自己总结概括一下,但是看到一...关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL...
  • 为mysql数据库建立索引;mysql索引总结----mysql 索引类型以及创建;mysql_建立索引的优缺点
  •  关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就...
  • mysql 索引mysql 添加索引

    千次阅读 2017-11-24 11:35:28
     索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作...
  • MySQL索引与索引优化

    千次阅读 2020-09-04 16:48:00
    MySQL索引就是用于优化器上。 索引: MySQL官方对于索引的定义为:索引是帮助MySQL高效获取数据的数据结构。即可以理解为:索引是数据结构。 索引是对数据库表中一个或多个列的值进行排序的结构,建立索引有助于快速...
  • mysql索引总结----mysql 索引类型以及创建

    万次阅读 热门讨论 2018-04-29 17:18:18
    说明:本文是转载的文章 链接:我转载的地址是... ...关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。对于没有索引的...
  • 说到索引,很多人都知道“索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址,...因为索引并不是关系模型的组成部分,因此不同的DBMS有不同的实现,我们针对MySQL数据库的实现进...
  • MySQL 索引概览

    万次阅读 多人点赞 2021-05-25 20:38:15
    文章目录前言概览索引定义索引优缺点优点缺点索引类型按功能逻辑划分普通索引唯一索引主键索引全文索引按物理实现划分聚集索引(clustered index)非聚集索引(non-clustered index)按字段个数划分单一索引组合索引...
  • MySql索引类型

    万次阅读 2018-07-04 22:15:19
    Mysql支持哪几种索引索引是在MySql的存储引擎层中实现的,而不是在服务器层从数据结构角度1、B+树索引(O(log(n))):关于B+树索引,可以参考 MySQL索引背后的数据结构及算法原理BTREE在MyISAM里的形式和Innodb稍有...
  • 【mysql】mysql索引存储结构和特点

    万次阅读 2019-08-22 09:14:40
    MySQL索引存储结构和特点 一 理解索引的特性 二 索引的各种存储结构及其优缺点 (一)二叉树 ​(二)红黑树 (三)Hash (四)B-Tree (五) B+Tree(MySQL索引的真正存储结构) 三. 联合索引底层存储结构 ...
  • 细说mysql索引

    千次阅读 2017-12-01 19:29:48
    本文转自 细说mysql索引 本文从如何建立mysql索引以及介绍mysql的索引类型,再讲mysql索引的利与弊,以及建立索引时需要注意的地方 首先:先假设有一张表,表的数据有10W条数据,其中有一条数据是nickname='css',...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 432,112
精华内容 172,844
关键字:

mysql索引

mysql 订阅