索引 订阅
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值,然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。 展开全文
在关系数据库中,索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针,然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值,然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快,可快速访问数据库表中的特定信息。当表中有大量记录时,若要对表进行查询,第一种搜索信息方式是全表搜索,是将所有记录一一取出,和查询条件进行一一对比,然后返回满足条件的记录,这样做会消耗大量数据库系统时间,并造成大量磁盘I/O操作;第二种就是在表中建立索引,然后在索引中找到符合查询条件的索引值,最后通过保存在索引中的ROWID(相当于页码)快速找到表中对应的记录。
信息
作    用
应于表的SQL语句执行得更快
分    类
数据库概念
中文名
索引
外文名
index
索引发展历史
旧称通检、备检或引得。组成的基本单位是索引款目。款目一般包括索引词、说明或注释语 、出处3项内容 。所有索引款目实现有序化编排。其本质特征是只揭示内容出处或文献线索 ,并不直接提供事实、资料本身。主要功能是为人们准确、迅速地获得文献资料提供线索性指引。常见的索引主要有报刊论文资料索引、文集篇目索引、语词索引、文句索引、关键词索引、专名索引、主题索引等。索引最早出现于西方,主要是中世纪欧洲宗教著作的索引。18世纪以后西方开始有主题索引,至19世纪末,内容分析索引被广泛使用。中国的索引出现较晚。一般认为,明末傅山所编的《两汉书姓名韵》是现存最早的人名索引。清代乾嘉时期,章学诚曾力倡编纂群书综合索引。20世纪20年代,随着西方索引理论与编制技术的传入,中国现代意义上的索引编制与研究才蓬勃展开 。1930年钱亚新发表《索引和索引法》,1932年洪业发表《引得说》,标志着具有中国特色的现代索引理论、技术已迅速发展起来。20世纪50年代,计算机技术被运用于索引编制 。此后,机编索引的大量出现,使索引编制理论、技术、索引载体形式发生了深刻变革。SQL标准中没有涉及索引,但商用关系数据库管理系统一般都支持索引机制,只是不同的关系数据库管理系统支持的索引类型不尽相同。索引已经成为关系数据库非常重要的部分。它们被用作包含所关心数据的表指针。通过一个索引,能从表中直接找到一个特定的记录,而不必连续顺序扫描这个表,一次一个地去查找。对于大的表,索引是必要的。没有索引,要想得到一个结果要等好几个小时、好几天,而不是几秒钟。 [1] 
收起全文
精华内容
下载资源
问答
  • 索引
    千次阅读
    2019-07-10 18:14:31

    面试题:Mysql中慢查询如何优化?

    答: 建索引

    索引是什么?

    索引是一种优化查询的数据结构

    索引为什么快?

    使用B+tree的数据结构,能够快速筛选出需要的记录,避免全表扫描

    为什么B+Tree快?

    1 ,B+Tree拥有B-Tree的优点,深度浅,数据块大
    2 ,因为只在叶子结点存储数据,从而导致扫全表的能力强,因为叶子结点是顺序的,从而导致排序功能更强。
    3 ,查询时间相对稳定,因为原因1:平衡二叉树,解决了查询不会受到结点分布的影响, 原因2:因为数据在叶子结点,导致每次查询的深度是一样的(相对于B-Tree)


    若采用二叉树:

    在这里插入图片描述

    缺点是:若是一个斜二叉树,查询速度和全表扫描没有区别,图如下,所以查询速度由结点的分布决定
    在这里插入图片描述

    若采用平衡二叉树(认为:二路平衡二叉树)

    在这里插入图片描述

    缺点:太深了:深度决定者io的次数,而io耗时大如若查询8,则需要进行3次io操作太小了:结点存储的信息太小了,从而导致频繁的io操作

    若采用多路平衡二叉树:(B-Tree B树)

    在这里插入图片描述

    路数是结点+1,因为这个是判断逻辑来决定如下图,图一是<1:选择p1,=15:则命中,>15:选择p2,图二会导致>35的无法选择。

    在这里插入图片描述

    一个磁盘块的结点个数:一个磁盘块的大小是16K,若节点存储是int型数据(4个字节)加结点的其他信息(如p1 4个字节),一个磁盘块的结点个数:16*1024/(4+4)=2048个结点,路数是2049路,从而解决了太深了和太小的问题

    若采用加强版多路平衡二叉树(B+Tree)
    在这里插入图片描述

    和B-Tree区别:
    判断逻辑:B+Tree采用的是闭合区间判断,B-Tree采用开合区间结点:B+Tree非叶子

    结点:关键字和子节点的引用,叶子结点:存储数据 B-Tree 非叶子结点和叶子结点存储的是关键字,数据和子节点的引用

    顺序:B+Tree叶子结点是顺序的,相邻结点存在顺序引用

    为什么选择B+Tree
    1 B+Tree拥有B-Tree的优点,深度浅,数据块大

    2因为只在叶子结点存储数据,从而导致扫全表的能力强,因为叶子结点是顺序的,从而导致排序功能更强。

    3查询时间相对稳定,因为原因1:平衡二叉树,解决了查询不会受到结点分布的影响, 原因2:因为数据在叶子结点,导致每次查询的深度是一样的(相对于B-Tree)

    B+Tree索引在mysql中的体现形式
    体现形式之一: myisam

    在这里插入图片描述

    体现形式之二: innodb

    在这里插入图片描述

    特点:以主键作为索引,同时也是聚集索引:记录在数据库的顺序和物理地址的顺序是一样的

    若以name作为索引,则先用辅助索引找到主键,在根据主键找到对应的数据库记录

    两者的区别:
    Innodb:通过辅助索引找到主键,在通过主键索引来找到记录,
    myisam通过索引找到物理地址,再通过物理地址找到对应的记录。

    索引的优点、缺点?

    索引的优点

    1.通过创建唯一索引,可以保证数据库每一行数据的唯一性
    2.可以大大提高查询速度
    3.可以加速表与表的连接
    4.可以显著的减少查询中分组和排序的时间。

    索引的缺点

    1.创建索引和维护索引需要时间,而且数据量越大时间越长
    2.创建索引需要占据磁盘的空间,如果有大量的索引,可能比数据文件更快达到最大文件尺寸
    3.当对表中的数据进行增加,修改,删除的时候,索引也要同时进行维护,降低了数据的维护速度

    如何建索引?

    1.不是越多越好,因为如果建立过多的索引,保存的速度就会下降
    2.常更新的表越少越好,因为在字段中做更新(插入)操作后,索引也会更新的,这样的话效率会大大降低
    3.数据量小的表最好不要建立索引,因为小的表即使建立索引也不会有大的用处,还会增加额外的索引开销
    4.不同的值比较多的列才需要建立索引
    5.某种数据本身具备唯一性的时候,建立唯一性索引,可以保证定义的列的数据完整性,以提高查询熟度
    6.频繁进行排序或分组的列(group by或者是order by)可以建立索引,提高搜索速度
    7.经常用于查询条件的字段应该建立索引

    不经常引用的列不要建立索引,因为不常用,即使建立了索引也没有多大意义
    经常频繁更新的列不要建立索引,因为肯定会影响插入或更新的效率
    索引并不是一劳永逸的,用的时间长了需要进行整理或者重建

    如何理解索引最左前缀匹配?

    在创建一个n列的索引时,需要遵循“最左前缀”原则。

    创建表:create table abc(a varchar(32) not null, b varchar(32), c date, d varchar(32) );

    创建普通索引:create index in_abc_acb on abc(a, c, b);

    1、必须用到索引的第一个字段

    select * from abc where  d='d' and b='b';  不会用到索引,必须要用到左边第一个字段;
    

    2、对于索引的第一个字段,用like时左边必须是固定值,通配符只能出现在右边

    select * from AAA where a like ‘1%’;会用到索引;而select * from abc where a like ‘%1’;不会用到索引。
    

    3、遇到范围(>、<、between、like)查询就停止匹配,

    select * from abc where a like '1%' and c=sysdate;  a 会用到索引,c 则不会
    

    4、字段前加了函数(表达式)索引会被抑制,

    select * from abc where trim(a) = 'a' ; a不会用到索引 
    select * from abc where a = 'a' and c + 1 > sysdate;  a会用到索引 c不会用到索引
    select * from abc where a = 'a' and c > sysdate - 2;  a会用到索引 c会用到索引
    

    5、索引是从左到右匹配,in 和 = 可以乱序

    select * from abc where b like 'b%' and c = sysdate and a='a' ;acb的索引都可以用到
    

    Mysql索引失效有哪几种情况?

    1.索引无法存储null值
      
      a.单列索引无法储null值,复合索引无法储全为null的值。

           b.查询时,采用is null条件时,不能利用到索引,只能全表扫描。

    为什么索引列无法存储Null值?

    a.索引是有序的。NULL值进入索引时,无法确定其应该放在哪里。(将索引列值进行建树,其中必然涉及到诸多的比较操作,null 值是不确定值无法比较,无法确定null出现在索引树的叶子节点位置。)

    b.如果需要把空值存入索引,方法有二:其一,把NULL值转为一个特定的值,在WHERE中检索时,用该特定值查找。其二,建立一个复合索引。例如

    create index ind_a on table(col1,1); 通过在复合索引中指定一个非空常量值,而使构成索引的列的组合中,不可能出现全空值。

    2.不适合键值较少的列(不适合重复数据较多的列)
      假如索引列TYPE有5个键值,如果有1万条数据,那么 WHERE TYPE = 1将访问表中的2000个数据块。

    再加上访问索引块,一共要访问大于200个的数据块。

    如果全表扫描,假设10条数据一个数据块,那么只需访问1000个数据块,既然全表扫描访问的数据块

    少一些,肯定就不会利用索引了。

    3.前导模糊查询不能利用索引(like '%XX’或者like ‘%XX%’)
      假如有这样一列code的值为’AAA’,‘AAB’,‘BAA’,‘BAB’ ,如果where code like '%AB’条件,由于前面是

    模糊的,所以不能利用索引的顺序,必须一个个去找,看是否满足条件。这样会导致全索引扫描或者全表扫

    描。如果是这样的条件where code like 'A % ',就可以查找CODE中A开头的CODE的位置,当碰到B开头的

    数据时,就可以停止查找了,因为后面的数据一定不满足要求。这样就可以利用索引了。

    4.其他几种索引失效的情况
      
    1.如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

    要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引

    2.对于多列索引,不是使用的第一部分,则不会使用索引

    3.like查询以%开头

    4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引

    5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

    5.MySQL主要提供2种方式的索引:B-Tree索引,Hash索引
      B树索引具有范围查找和前缀查找的能力,对于有N节点的B树,检索一条记录的复杂度为O(LogN)。相当于二分查找。

    哈希索引只能做等于查找,但是无论多大的Hash表,查找复杂度都是O(1)。

    显然,如果值的差异性大,并且以等值查找(=、 <、>、in)为主,Hash索引是更高效的选择,它有O(1)的查找复杂度。

    如果值的差异性相对较差,并且以范围查找为主,B树是更好的选择,它支持范围查找。

    Mysql索引如何优化?

    作为免费又高效的数据库,mysql基本是首选。良好的安全连接,自带查询解析、sql语句优化,使用读写锁(细化到行)、事物隔离和多版本并发控制提高并发,完备的事务日志记录,强大的存储引擎提供高效查询(表记录可达百万级),如果是InnoDB,还可在崩溃后进行完整的恢复,优点非常多。即使有这么多优点,仍依赖人去做点优化,看书后写个总结巩固下,有错请指正。

    完整的mysql优化需要很深的功底,大公司甚至有专门写mysql内核的,sql优化攻城狮,mysql服务器的优化,各种参数常量设定,查询语句优化,主从复制,软硬件升级,容灾备份,sql编程,需要的不是一星半点的知识与时间来掌握,作为一名像俺这样的菜鸟开发,强吃这么多消化不了也没意义:没地儿用啊,况且还有运维和dba,还不如把手头的业务写好,也就是写好点的sql,而且很多sql语句优化跟索引还是有很大关系的。

    首先,mysql的查询流程大致是:mysql客户端通过协议与mysql服务器建立连接,发送查询语句,先检查查询缓存,如果命中,直接返回结果,否则进行语句解析,有一系列预处理,比如检查语句是否写正确了,然后是查询优化(比如是否使用索引扫描,如果是一个不可能的条件,则提前终止),生成查询计划,然后查询引擎启动,开始执行查询,从底层存储引擎调用API获取数据,最后返回给客户端。怎么存数据、怎么取数据,都与存储引擎有关。然后,mysql默认使用的BTREE索引,并且一个大方向是,无论怎么折腾sql,至少在目前来说,mysql最多只用到表中的一个索引。

    mysql通过存储引擎取数据,自然跟存储引擎有很大关系,不同的存储引擎索引也不一样,如MyISAM的全文索引,即便索引叫一个名字内部组织方式也不尽相同,最常用的当然就是InnoDB了(还有完全兼容mysql的MariaDB,它的默引擎是XtraDB,跟InnoDB很像),这里写的是InnoDB引擎。而索引的实现也跟存储引擎,按照实现方式分,InnoDB的索引目前只有两种:BTREE索引和HASH索引。通常我们说的索引不出意外指的就是B树索引,InnoDB的BTREE索引,实际是用B+树实现的,因为在查看表索引时,mysql一律打印BTREE,所以简称为B树索引。至于B树与B+树的区别,原谅的俺数据结构没好好学,也是需要补的地方。

    使用了BTREE索引,意味着所有的索引是按顺序排列存储的(升序),mysql就是这么干的,mysl中的BTREE索引抽象结构如下图(参考高性能mysql)。

    结构中,每一层节点均从左往右从小到大排列,key1 < key2 < … < keyN,对于小于key1或在[key1,key2)或其他的值的节点,在进入叶子节点查找,是一个范围分布,同时,同一层节点之间可直接访问,因为他们之间有指针指向联系(MyISAM的BTREE索引没有)。每次搜索是一个区间搜索,有的话就找到了,没有的话就是空。索引能加快访问速度,因为有了它无需全表扫描数据(不总是这样),根据查找的值,跟节点中的值比较,通常使用二分查找,对于排好序的数值来说,平均速度几乎是最快的。

    val指向了哪里,对于InnoDB,它指向的就是表数据,因为InnoDB的表数据本身就是索引文件,这是与MyISAM索引的显著区别,MyISAM的索引指向的是表数据的地址(val指向的是类似于0x7DFF…之类)。比如对于InnoDB一个主键索引来说,可能是这样

    InnoDB的索引节点val值直接指向表数据,即它的叶子节点就是表数据,它们连在一起,表记录行没有再单独放在其他地方,叶子节点(数据)之间可访问。

    前面在BTREE的抽象结构中,索引值的节点是放在页中的,这里有两个需注意的问题:

    1. 叶子页、页中的值(上上图),即所谓的页是啥,俺加了个节点注释,即这里的页最小可近似当做是单个节点。我们知道计算机的存储空间是一块一块的,通常一块用完了再用另一块,如果上一块只剩余5kb,但这里刚好要申请8kb的空间,就得在一个新的块上申请这个空间,然后以后的申请又接在这个8kb后面,只要这个块的空间足够,那么上一块的5kb通常就成了所谓的“碎片”,电脑用多了会有很多这样零散的碎片空间,因此有碎片整理。在mysql中,这里的页可理解为块存储空间,即索引的树节点是存放在页中的,每一页(称为逻辑页)有固定大小,InnoDB目前是16kb,一页用完了,当继续插入表生成新的索引节点时,就去新的页中存储这个节点,再有新的节点就继续放在这个新的页的节点后面。

    2. 页分裂问题,一页总要被存满,然后新开一页继续,这种行为被称作页分裂。何时开辟新的页,mysql规定了一个分裂因子,达到页存储空间的15/16则存到下一页。页分裂的存在可能极大影响性能维护索引的性能。通常提倡的是,设定一个无意义的整数自增索引,有利于索引存储

    如果非自增或不是整数索引,如非自增整数、类似MD5的字符串,以他们作为索引值时,因为待插入的下一条数据的值不一定比上一条大,甚至比当前页所有值都小,需要跑到前几页去比较而找到合适位置,InnoDB无法简单的把新行插入到上一行后面,而找到并插入索引后,可能导致该页达到分裂因子阀值,需要页分裂,进一步导致后面所有的索引页的分裂和排序,数据量小也许没什么问题,数据量大的话可能会浪费大量时间,产生许多碎片。

    主键总是唯一且非空,InnoDB自动对它建立了索引(primary key),对于非主键字段上建立的索引,又称辅助索引,索引排列也是顺序排列,只是它还附带一个本条记录的主键值的数据域,不是指向本数据行的指针,在使用辅助索引查找时,先找到对应这一列的索引值,再根据索引节点上的另一个数据域—主键值,来查找该行记录,即每次查找实际经过查找了两次。额外的数据域存储主键值的好处是,当页分裂发生时,无需修改数据域的值,因为即使页分裂,该行的主键值是不变的,而地址就变了。比如name字段的索引简示如下

    包含一列的索引称为单列索引,多列的称为复合索引,因为BTREE索引是顺序排列的,所以比较适合范围查询,但是在复合索引中,还应注意列数目、列的顺序以及前面范围查询的列对后边列的影响。

    比如有这样一张表

    create table staffs(
        id int primary key auto_increment,
        name varchar(24) not null default '' comment '姓名',
        age int not null default 0 comment '年龄',
        pos varchar(20) not null default '' comment '职位',
        add_time timestamp not null default current_timestamp comment '入职时间'
      ) charset utf8 comment '员工记录表';
    

    添加三列的复合索引

    alter table staffs add index idx_nap(name, age, pos);
    

    在BTREE索引的使用上,以下几种情况可以用到该索引或索引的一部分(使用explain简单查看使用情况):

    1. 全值匹配

    select * from staffs where name = 'July' and age = '23' and pos = 'dev' ,key字段显示使用了idx_nap索引

    2. 匹配最左列,对于复合索引来说,不总是匹配所有字段列,但是可以匹配索引中靠左的列

    select * from staffs where name = 'July' and age = '23',key字段显示用到了索引,注意,key_len字段(表示本次语句使用的索引长度)数值比上一条小了,意思是它并未使用全部索引列(通常这个长度可估摸着用了哪些索引列,埋个坑),事实上只用到了name和age列

    再试试select * from staffs where name = 'July',它也用了索引,key_len值更小,实际只用到了索引中的name列

    3. 匹配列前缀,即一个索引中列的前一部分,主要用在模糊匹配,如select * fromstaffs where name like 'J%',explain信息的key字段表示使用了索引,但是mysql的B树索引不能非列前缀的模糊匹配,如select * from staffs where name like '%y' 或者 like ‘%u%’,`据说是由于底层存储引擎的API限制

    4. 匹配范围,如select * from staffs where name > 'Mary',但俺在测试时发现>可以,>=却不行,至少在字符串列上不行(测试mysql版本5.5.12),然而在时间类型(timestamp)上却可以,不测试下还真不能确定说就用到了索引==

    出于好奇测了下整型字段的索引(idx_cn(count, name),count为整型),发现整型受限制少很多,下面的都能用到索引,连前模糊匹配的都行

    select * from indexTest1 where count > '10'
      select * from indexTest1 where count >= '10'
      select * from indexTest1 where count > '10%'
      select * from indexTest1 where count >= '10%'
      select * from indexTest1 where count > '%10%'
      select * from indexTest1 where count >= '%10%'
    

    5. 精确匹配一列并范围匹配右侧相邻列,即前一列是固定值,后一列是范围值,它用了name与age两个列的索引(key_len推测)

    select * from staffs where name = 'July' and age > 25

    6. 只访问索引的查询,比如staffs表的情况,索引建立在(name,age,pos)上面,前面一直是读取的全部列,如果我们用到了哪些列的索引,查询时也只查这些列的数据,就是只访问索引的查询,如

     select name,age,pos from staffs where name = 'July' and age = 25 and pos = 'dev'
     select name,age from staffs where name = July and age > 25
    

    第一句用到了全部索引列,第二句只用了索引前两列,select的字段就最多只能是这两列,这种查询情况的索引,mysql称为覆盖索引,就是索引包含(覆盖)了查询的全部字段。是不是用到了索引查询,在explain中需要看最后一个Extra列的信息,Using index表明使用了覆盖索引,同时Using where表明也使用了where过滤

    7. 前缀索引

    区别于列前缀(类似like 'J%'形式的模糊匹配)和最左列索引(顺序取索引中靠左的列的查询),它只取某列的一部分作为索引。通常在说InnoDB跟MyISAM的区别时,一个明显的区别是:MyISAM支持全文索引,而InnoDB不行,甚至对于text、blob这种超长的字符串或二进制数据时,MyISAM会取前多少个字符作为索引,InnoDb的前缀索引跟这个类似,某些列,一般是字符串类型,很长,全部作为索引大大增加存储空间,索引也需要维护,对于长字符串,又想作为索引列,一个可取的办法就是取前一部分(前缀),代表一整列作为索引串,问题是:如何确保这个前缀能代表或大致代表这一列?所以mysql中有个概念是索引的选择性,是指索引中不重复的值的数目(也称基数)与整个表该列记录总数(#T)的比值,比如一个列表(1,2,2,3),总数是4,不重复值数目为3,选择性为3/4,因此选择性范围是[1/#T, 1],这个值越大,表示列中不重复值越多,越适合作为前缀索引,唯一索引(UNIQUE KEY)的选择性是1。

    比如有一列a varchar(255),以它作前缀索引,比如以7个测试,逐个增加看看选择性值增长到那个数基本不变,就表示可以代表整列了,再结合这个长度的索引列是否存储数据太多,做个权衡,基本就行了。但如果这个选择性本来就小的可怜还是算了

     select count(distinct left(a, 7))/count(*) as non_repeat from tab;
    

    定好一个前缀数目,如9,添加索引时可以这样

      alter table tab add index idx_cpn(count, name(9)) --复合前缀索引
    

    以上为常见的使用索引的方式,有这么些情况不能用或不能全用,有的就是上面情况的反例,以key(a, b, c)为例

    1. 跳过列,where a = 1 and c = 3,最多用到索引列a;where b = 2 and c = 3,一个也用不到,必须从最左列开始

    2. 前面是范围查询,where a = 1 and b > 2 and c = 3,最多用到 a, b两个索引列;

    3. 顺序颠倒,where c = 3 and b = 2 and a = 1,一个也用不到;

    4. 索引列上使用了表达式,如where substr(a, 1, 3) = ‘hhh’,where a = a + 1,表达式是一大忌讳,再简单mysql也不认。有时数据量不是大到严重影响速度时,一般可以先查出来,比如先查所有有订单记录的数据,再在程序中去筛选以’cp1001’开头的订单,而不是写sql过滤它;

    5. 模糊匹配时,尽量写 where a like ‘J%’,字符串放在左边,这样才可能用得到a列索引,甚至可能还用不到,当然这得看数据类型,最好测试一下。

    排序对索引的影响

    order by是经常用的语句,排序也遵循最左前缀列的原则,比如key(a, b),下面语句可以用到(测试为妙)

      select * from tab where a > 1 order by b
      select * from tab where a > 1 and b > '2015-12-01 00:00:00' order by b
      select * from tab order by a, b
    

    以下情况用不到

    1. 非最左列,select * from tab order by b;

    2. 不按索引列顺序来的,select * from tab where b > '2015-12-01 00:00:00' order by a;

    3. 多列排序,但列的顺序方向不一致,select * from tab a asc, b desc

    聚簇索引与覆盖索引

    前面说到,mysql索引从结构上只有两类,BTREE与HASH,覆盖索引只是在查询时,要查询的列刚好与使用的索引列完全一致,mysql直接扫描索引,然后就可返回数据,大大提高效率,因为不需再去原表查询、过滤,这种形式下的索引称作覆盖索引,比如key(a,b),查询时select a,b from tab where a = 1 and b > 2,本质原因:BTREE索引存储了原表数据。

    聚簇索引也不是单独的索引,前面简要写到,BTREE索引会把数据放在索引中,即索引的叶子页中,包括主键,主键是跟表数据紧挨着放在一起的,因为表数据只有一份,一列键值要跟每一行数据都紧挨在一起,所以一张表只有一个聚簇索引,对于mysql来说,就是主键列,它是默认的。

    聚簇索引将表数据组织到了一起(参考前面主键索引简略图),插入时严重依赖主键顺序,最好是连续自增,否则面临频繁页分裂问题,移动许多数据。

    哈希索引

    简要说下,类似于数据结构中简单实现的HASH表(散列表)一样,当我们在mysql中用哈希索引时,也是对索引列计算一个散列值(类似md5、sha1、crc32),然后对这个散列值以顺序(默认升序)排列,同时记录该散列值对应数据表中某行的指针,当然这只是简略模拟图

    比如对姓名列建立hash索引,生成hash值按顺序排列,但是顺序排列的hash值并不对应表中记录,从地址指针可反应出来,而且,hash索引可能建立在两列或者更多列上,取得是多列数据后的hash值,它不存储表中数据。它先计算列数据的hash值,与索引中的hash值比较,找到了然后比对列数据是否相等,可能涉及其他列条件,然后返回数据。hash当然会有冲突,即碰撞,除非有很多冲突,一般hash索引效率很高,否则hash维护成本较高,因此哈希索引通常用在选择性较高的列上面。哈希索引的结构决定了它的特点:

    1. hash索引只是hash值顺序排列,跟表数据没有关系,无法应用于order by;

    2. hash索引是对它的所有列计算哈希值,因此在查询时,必须带上所有列,比如有(a, b)哈希索引,查询时必须 where a = 1 and b = 2,少任何一个不行;

    3. hash索引只能用于比较查询 = 或 IN,其他范围查询无效,本质还是因不存储表数据;

    4. 一旦出现碰撞,hash索引必须遍历所有的hash值,将地址所指向数据一一比较,直到找到所有符合条件的行。

    填坑

    前面提到通过explain的key_len字段,可大致估计出用了哪些列,索引列的长度跟索引列的数据类型直接相关,一般,我们说int是4字节,bigint8字节,char是1字节,考虑到建表时要指定字符集,比如utf8,还跟选的字符集有关(==!),在utf8下边,一个char是3字节,但是知道这些仍不能说key_len就是将用到的索引列的数据类型代表字节数一加不就完啦?事实总有点区别,测试方法比较机械(以下基于mysql 5.5.2)

    建表,加索引,int型

    –测试表

     create table keyLenTest1(
        id int primary key auto_increment,
        typeKey int default 0 ,
        add_time timestamp not null default current_timestamp
      ) charset utf8
    

    –添加索引

      alter table keyLenTest1 add index idx_k(typeKey);
    

    可知int型索引默认长度为5,在4字节基础上+1

    char型

    –改为char型,1个字符

     alter table keyLenTest1 modify typeKey char(1);
    

    –改为char型,2个字符

      alter table keyLenTest1 modify typeKey char(2);
    

    可知,char型初始是4字节(3+1 bytes),后续按照3字节递增

    varchar型

    –改为varchar型,1个字符

      alter table keyLenTest1 modify typeKey varchar(1);
    

    –改为varchar型,2个字符

      alter table keyLenTest1 modify typeKey varchar(2);
    

    可知,varchar型,1个字符时,key_len为6,以后以3字节递增

    所以,如果一个语句用到了int、char、varchar,key_len如何计算以及用了哪些索引列应该很清楚了。

    如果想了解的更详细点,explain各字段意义,索引的更多细节,除了explain,还有show profiles、慢查询日志等(没细看),推荐看高性能mysql

    参考: https://www.jianshu.com/p/bd6e85e36edc
    https://zhidao.baidu.com/question/247587915.html
    https://www.cnblogs.com/yyjie/p/7486975.html
    http://www.jb51.net/article/76702.htm

    更多相关内容
  • mysql索引详解

    万次阅读 多人点赞 2021-07-07 21:40:09
    分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。 InnoDB:所有的表都...

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    目录

    一、MySQL三层逻辑架构

    1、第一层负责连接管理、授权认证、安全等等。

    2、第二层负责解析查询

    3、第三层是存储引擎

    二、对比InnoDB与MyISAM

    1、 存储结构

    2、 存储空间

    3、 可移植性、备份及恢复

    4、 事务支持

    5、 AUTO_INCREMENT

    6、 表锁差异

    7、 全文索引

    8、表主键

    9、表的具体行数

    10、CRUD操作

    11、 外键

    三、sql优化简介

    1、什么情况下进行sql优化

    2、sql语句执行过程

    3、sql优化就是优化索引

    四、索引

    1、索引的优势

    2、索引的弊端

    3、索引的分类

    4、创建索引

    5、MySQL索引原理 -> B+树

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    六、分析sql的执行计划---explain

    1、explan使用简介

    2、explain查询结果简介


    一、MySQL三层逻辑架构

    MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。下面是MySQL的逻辑架构图:

    1、第一层负责连接管理、授权认证、安全等等。

    每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池,避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时,服务器对其进行认证。可以通过用户名和密码的方式进行认证,也可以通过SSL证书进行认证。登录认证通过后,服务器还会验证该客户端是否有执行某个查询的权限。

    2、第二层负责解析查询

    编译SQL,并对其进行优化(如调整表的读取顺序,选择合适的索引等)。对于SELECT语句,在解析查询前,服务器会先检查查询缓存,如果能在其中找到对应的查询结果,则无需再进行查询解析、优化等过程,直接返回查询结果。存储过程、触发器、视图等都在这一层实现。

    3、第三层是存储引擎

    存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。存储引擎通过API与上层进行通信,这些API屏蔽了不同存储引擎之间的差异,使得这些差异对上层查询过程透明。存储引擎不会去解析SQL。

    二、对比InnoDB与MyISAM

    1、 存储结构

    MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件、数据文件、索引文件。第一个文件的名字以表的名字开始,扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

    InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件),InnoDB表的大小只受限于操作系统文件的大小,一般为2GB。

    2、 存储空间

    MyISAM: MyISAM支持支持三种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格,会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后,不会再进行修改操作,可以使用压缩表,极大的减少磁盘的空间占用。

    InnoDB: 需要更多的内存和存储,它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。

    3、 可移植性、备份及恢复

    MyISAM:数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

    InnoDB:免费的方案可以是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。

    4、 事务支持

    MyISAM:强调的是性能,每次查询具有原子性,其执行数度比InnoDB类型更快,但是不提供事务支持。

    InnoDB:提供事务支持事务,外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

    5、 AUTO_INCREMENT

    MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引,自动增长可以不是第一列,他可以根据前面几列进行排序后递增。

    InnoDB:InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引,如果是组合索引也必须是组合索引的第一列。

    6、 表锁差异

    MyISAM: 只支持表级锁,用户在操作myisam表时,select,update,delete,insert语句都会给表自动加锁,如果加锁以后的表满足insert并发的情况下,可以在表的尾部插入新的数据。

    InnoDB: 支持事务和行级锁,是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。

    7、 全文索引

    MyISAM:支持 FULLTEXT类型的全文索引

    InnoDB:不支持FULLTEXT类型的全文索引,但是innodb可以使用sphinx插件支持全文索引,并且效果更好。

    8、表主键

    MyISAM:允许没有任何索引和主键的表存在,索引都是保存行的地址。

    InnoDB:如果没有设定主键或者非空唯一索引,就会自动生成一个6字节的主键(用户不可见),数据是主索引的一部分,附加索引保存的是主索引的值。

    9、表的具体行数

    MyISAM: 保存有表的总行数,如果select count() from table;会直接取出出该值。

    InnoDB: 没有保存表的总行数,如果使用select count(*) from table;就会遍历整个表,消耗相当大,但是在加了wehre条件后,myisam和innodb处理的方式都一样。

    10、CRUD操作

    MyISAM:如果执行大量的SELECT,MyISAM是更好的选择。

    InnoDB:如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表。

    11、 外键

    MyISAM:不支持

    InnoDB:支持

    三、sql优化简介

    1、什么情况下进行sql优化

    性能低、执行时间太长、等待时间太长、连接查询、索引失效。

    2、sql语句执行过程

    (1)编写过程

    select distinct ... from ... join ... on ... where ... group by ... having ... order by ... limit ...

    (2)解析过程

    from ... on ... join ... where ... group by ... having ... select distinct ... order by ... limit ...

    3、sql优化就是优化索引

    索引相当于书的目录。

    索引的数据结构是B+树。

    四、索引

    1、索引的优势

    (1)提高查询效率(降低IO使用率)

    (2)降低CPU使用率

    比如查询order by age desc,因为B+索引树本身就是排好序的,所以再查询如果触发索引,就不用再重新查询了。

    2、索引的弊端

    (1)索引本身很大,可以存放在内存或硬盘上,通常存储在硬盘上。

    (2)索引不是所有情况都使用,比如①少量数据②频繁变化的字段③很少使用的字段

    (3)索引会降低增删改的效率

    3、索引的分类

    (1)单值索引

    (2)唯一索引

    (3)联合索引

    (4)主键索引

    备注:唯一索引和主键索引唯一的区别:主键索引不能为null

    4、创建索引

    alter table user add INDEX `user_index_username_password` (`username`,`password`)

    5、MySQL索引原理 -> B+树

    MySQL索引的底层数据结构是B+树

    B+Tree是在B-Tree基础上的一种优化,使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+Tree实现其索引结构。

    B-Tree结构图中每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+Tree的高度。

    B+Tree相对于B-Tree有几点不同:

    非叶子节点只存储键值信息。
    所有叶子节点之间都有一个链指针。
    数据记录都存放在叶子节点中。
    将上一节中的B-Tree优化,由于B+Tree的非叶子节点只存储键值信息,假设每个磁盘块能存储4个键值及指针信息,则变成B+Tree后其结构如下图所示:

    通常在B+Tree上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对B+Tree进行两种查找运算:一种是对于主键的范围查找和分页查找,另一种是从根节点开始,进行随机查找。

    可能上面例子中只有22条数据记录,看不出B+Tree的优点,下面做一个推算:

    InnoDB存储引擎中页的大小为16KB,一般表的主键类型为INT(占用4个字节)或BIGINT(占用8个字节),指针类型也一般为4或8个字节,也就是说一个页(B+Tree中的一个节点)中大概存储16KB/(8B+8B)=1K个键值(因为是估值,为方便计算,这里的K取值为〖10〗^3)。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。

    实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

    数据库中的B+Tree索引可以分为聚集索引(clustered index)和辅助索引(secondary index)。上面的B+Tree示例图在数据库中的实现即为聚集索引,聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据,而是存储相应行数据的聚集索引键,即主键。当通过辅助索引来查询数据时,InnoDB存储引擎会遍历辅助索引找到主键,然后再通过主键在聚集索引中找到完整的行记录数据。

    五、如何触发联合索引

    1、对user表建立联合索引username、password

    2、触发联合索引

    (1)使用联合索引的全部索引键可触发联合索引

    (2)使用联合索引的全部索引键,但是用or连接的,不可触发联合索引

    (3)单独使用联合索引的左边第一个字段时,可触发联合索引

    (4)单独使用联合索引的其它字段时,不可触发联合索引

    六、分析sql的执行计划---explain

    explain可以模拟sql优化执行sql语句。

    1、explan使用简介

    (1)用户表

    (2)部门表

    (3)未触发索引

    (4)触发索引

    (5)结果分析

    explain中第一行出现的表是驱动表。

    1. 指定了联接条件时,满足查询条件的记录行数少的表为[驱动表]
    2. 未指定联接条件时,行数少的表为[驱动表]

    对驱动表直接进行排序就会触发索引,对非驱动表进行排序不会触发索引。

    2、explain查询结果简介

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

    (2)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子句的子查询)

    (3)table:表名

    (4)type:联接类型

    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,使得行能基于前面的表中的常数值或列值被检索出。

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

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

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

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

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

    (10)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查询的所有列,而不要额外搜索硬盘访问实际的表。并且,按最有效的方式使用索引,以便对于每个组,只读取少量索引条目。

    通过相乘EXPLAIN输出的rows列的所有值,你能得到一个关于一个联接如何的提示。这应该粗略地告诉你MySQL必须检查多少行以执行查询。当你使用max_join_size变量限制查询时,也用这个乘积来确定执行哪个多表SELECT语句。

    🍅 作者简介:哪吒,CSDN2021博客之星亚军🏆、新星计划导师✌、博客专家💪

    🍅 哪吒多年工作总结:Java学习路线总结,搬砖工逆袭Java架构师

    🍅 关注公众号【哪吒编程】,回复1024,获取Java学习路线思维导图、大厂面试真题、加入万粉计划交流群、一起学习进步

    关注公众号,回复1024,获取Java学习路线思维导图,加入万粉计划交流群

    展开全文
  • 一文搞懂MySQL索引所有知识点(建议收藏)

    万次阅读 多人点赞 2020-10-24 12:19:05
    Mysql索引 索引介绍 索引是什么 官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度。 一般来说索引本身也很大,不可能全部存储在内存中,因此...

    Mysql索引

    索引介绍

    索引是什么

    • 官方介绍索引是帮助MySQL高效获取数据数据结构。更通俗的说,数据库索引好比是一本书前面的目录,能加快数据库的查询速度

    • 一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往是存储在磁盘上的文件中的(可能存储在单独的索引文件中,也可能和数据一起存储在数据文件中)。

    • 我们通常所说的索引,包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等,没有特别说明,默认都是使用B+树结构组织(多路搜索树,并不一定是二叉的)的索引。

    索引的优势和劣势

    优势:

    • 可以提高数据检索的效率,降低数据库的IO成本,类似于书的目录。

    • 通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

      • 被索引的列会自动进行排序,包括【单列索引】和【组合索引】,只是组合索引的排序要复杂一些。
      • 如果按照索引列的顺序进行排序,对应order by语句来说,效率就会提高很多。

    劣势:

    • 索引会占据磁盘空间

    • 索引虽然会提高查询效率,但是会降低更新表的效率。比如每次对表进行增删改操作,MySQL不仅要保存数据,还有保存或者更新对应的索引文件。

    索引类型

    主键索引

    索引列中的值必须是唯一的,不允许有空值。

    普通索引

    MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值。

    唯一索引

    索引列中的值必须是唯一的,但是允许为空值。

    全文索引

    只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时,如果创建普通索引,在进行like模糊查询时效率比较低,这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

    空间索引

    MySQL在5.7之后的版本支持了空间索引,而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。

    前缀索引

    在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时,可以指定索引列的长度,但是数值类型不能指定。

    其他(按照索引列数量分类)

    1. 单列索引

    2. 组合索引

      组合索引的使用,需要遵循最左前缀匹配原则(最左匹配原则)。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

    索引的数据结构

    Hash表

    Hash表,在Java中的HashMap,TreeMap就是Hash表结构,以键值对的方式存储数据。我们使用Hash表存储表数据Key可以存储索引列,Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高,时间复杂度为O(1);但是不支持范围快速查找,范围查找时还是只能通过扫描全表方式。

    显然这种并不适合作为经常需要查找和范围查找的数据库索引使用。

    二叉查找树

    二叉树,我想大家都会在心里有个图。

    在这里插入图片描述

    二叉树特点:每个节点最多有2个分叉,左子树和右子树数据顺序左小右大。

    这个特点就是为了保证每次查找都可以这折半而减少IO次数,但是二叉树就很考验第一个根节点的取值,因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”,这就很难受很不稳定。

    在这里插入图片描述

    显然这种情况不稳定的我们再选择设计上必然会避免这种情况的

    平衡二叉树

    平衡二叉树是采用二分法思维,平衡二叉查找树除了具备二叉树的特点,最主要的特征是树的左右两个子树的层级最多相差1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡,不会出现左子树很高、右子树很矮的情况。

    使用平衡二叉查找树查询的性能接近于二分查找法,时间复杂度是 O(log2n)。查询id=6,只需要两次IO。

    在这里插入图片描述

    就这个特点来看,可能各位会觉得这就很好,可以达到二叉树的理想的情况了。然而依然存在一些问题:

    1. 时间复杂度和树高相关。树有多高就需要检索多少次,每个节点的读取,都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms,在表数据量大时,查询性能就会很差。(1百万的数据量,log2n约等于20次磁盘IO,时间20*10=0.2s)

    2. 平衡二叉树不支持范围查询快速查找,范围查询时需要从根节点多次遍历,查询效率不高。

    B树:改造二叉树

    MySQL的数据是存储在磁盘文件中的,查询处理数据时,需要先把磁盘中的数据加载到内存中,磁盘IO 操作非常耗时,所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO,如果想要减少磁盘IO操作,就需要尽量降低树的高度。那如何降低树的高度呢?

    假如key为bigint=8字节,每个节点有两个指针,每个指针为4个字节,一个节点占用的空间16个字节(8+4*2=16)。

    因为在MySQL的InnoDB存储引擎一次IO会读取的一页(默认一页16K)的数据量,而二叉树一次IO有效数据量只有16字节,空间利用率极低。为了最大化利用一次IO空间,一个简单的想法是在每个节点存储多个元素,在每个节点尽可能多的存储数据。每个节点可以存储1000个索引(16k/16=1000),这样就将二叉树改造成了多叉树,通过增加树的叉树,将树从高瘦变为矮胖。构建1百万条数据,树的高度只需要2层就可以(1000*1000=1百万),也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了,查询数据的效率也就提高了。

    这种数据结构我们称为B树,B树是一种多叉平衡查找树,如下图主要特点:

    1. B树的节点中存储着多个元素,每个内节点有多个分叉。

    2. 节点中的元素包含键值和数据,节点中的键值从大到小排列。也就是说,在所有的节点都储存数据。

    3. 父节点当中的元素不会出现在子节点中。

    4. 所有的叶子结点都位于同一层,叶节点具有相同的深度,叶节点之间没有指针连接。

    在这里插入图片描述

    举个例子,在b树中查询数据的情况:

    假如我们查询值等于10的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

    第一次磁盘IO:将磁盘块1加载到内存中,在内存中从头遍历比较,10<15,走左路,到磁盘寻址磁盘块2。

    第二次磁盘IO:将磁盘块2加载到内存中,在内存中从头遍历比较,7<10,到磁盘中寻址定位到磁盘块5。

    第三次磁盘IO:将磁盘块5加载到内存中,在内存中从头遍历比较,10=10,找到10,取出data,如果data存储的行记录,取出data,查询结束。如果存储的是磁盘地址,还需要根据磁盘地址到磁盘中取出数据,查询终止。

    相比二叉平衡查找树,在整个查找过程中,虽然数据的比较次数并没有明显减少,但是磁盘IO次数会大大减少。同时,由于我们的比较是在内存中进行的,比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景,所以使用B树构建索引可以很好的提升查询的效率。

    过程如图:

    在这里插入图片描述

    看到这里一定觉得B树就很理想了,但是前辈们会告诉你依然存在可以优化的地方:

    1. B树不支持范围查询的快速查找,你想想这么一个情况如果我们想要查找10和35之间的数据,查找到15之后,需要回到根节点重新遍历查找,需要从根节点进行多次遍历,查询效率有待提高。

    2. 如果data存储的是行记录,行的大小随着列数的增多,所占空间会变大。这时,一个页中可存储的数据量就会变少,树相应就会变高,磁盘IO次数就会变大。

    B+树:改造B树

    B+树,作为B树的升级版,在B树基础上,MySQL在B树的基础上继续改造,使用B+树构建索引。B+树和B树最主要的区别在于非叶子节点是否存储数据的问题

    • B树:非叶子节点和叶子节点都会存储数据。
    • B+树:只有叶子节点才会存储数据,非叶子节点至存储键值。叶子节点之间使用双向指针连接,最底层的叶子节点形成了一个双向有序链表。

    在这里插入图片描述

    B+树的最底层叶子节点包含了所有的索引项。从图上可以看到,B+树在查找数据的时候,由于数据都存放在最底层的叶子节点上,所以每次查找都需要检索到叶子节点才能查询到数据。所以在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系,但是从另一方面来说,由于数据都被放到了叶子节点,所以放索引的磁盘块锁存放的索引数量是会跟这增加的,所以相对于B树来说,B+树的树高理论上情况下是比B树要矮的。也存在索引覆盖查询的情况,在索引中数据满足了当前查询语句所需要的全部数据,此时只需要找到索引即可立刻返回,不需要检索到最底层的叶子节点。

    举个例子:

    • 等值查询:

    假如我们查询值等于9的数据。查询路径磁盘块1->磁盘块2->磁盘块6。

    1. 第一次磁盘IO:将磁盘块1加载到内存中,在内存中从头遍历比较,9<15,走左路,到磁盘寻址磁盘块2。

    2. 第二次磁盘IO:将磁盘块2加载到内存中,在内存中从头遍历比较,7<9<12,到磁盘中寻址定位到磁盘块6。

    3. 第三次磁盘IO:将磁盘块6加载到内存中,在内存中从头遍历比较,在第三个索引中找到9,取出data,如果data存储的行记录,取出data,查询结束。如果存储的是磁盘地址,还需要根据磁盘地址到磁盘中取出数据,查询终止。(这里需要区分的是在InnoDB中Data存储的为行数据,而MyIsam中存储的是磁盘地址。)

    过程如图:

    在这里插入图片描述

    • 范围查询:

    假如我们想要查找9和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块6->磁盘块7。

    1. 首先查找值等于9的数据,将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样,发生了三次磁盘IO。

    2. 查找到15之后,底层的叶子节点是一个有序列表,我们从磁盘块6,键值9开始向后遍历筛选所有符合筛选条件的数据。

    3. 第四次磁盘IO:根据磁盘6后继指针到磁盘中寻址定位到磁盘块7,将磁盘7加载到内存中,在内存中从头遍历比较,9<25<26,9<26<=26,将data缓存到结果集。

    4. 主键具备唯一性(后面不会有<=26的数据),不需再向后查找,查询终止。将结果集返回给用户。

    在这里插入图片描述

    可以看到B+树可以保证等值和范围查询的快速查找,MySQL的索引就采用了B+树的数据结构。

    Mysql的索引实现

    介绍完了索引数据结构,那肯定是要带入到Mysql里面看看真实的使用场景的,所以这里分析Mysql的两种存储引擎的索引实现:MyISAM索引InnoDB索引

    MyIsam索引

    以一个简单的user表为例。user表存在两个索引,id列为主键索引,age列为普通索引

    CREATE TABLE `user`
    (
      `id`       int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL,
      `age`      int(11)     DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE = MyISAM
      AUTO_INCREMENT = 1
      DEFAULT CHARSET = utf8;
    

    在这里插入图片描述

    MyISAM的数据文件和索引文件是分开存储的。MyISAM使用B+树构建索引树时,叶子节点中存储的键值为索引列的值,数据为索引所在行的磁盘地址。

    主键索引

    在这里插入图片描述

    表user的索引存储在索引文件user.MYI中,数据文件存储在数据文件 user.MYD中。

    简单分析下查询时的磁盘IO情况:

    根据主键等值查询数据:

    select * from user where id = 28;
    
    1. 先在主键树中从根节点开始检索,将根节点加载到内存,比较28<75,走左路。(1次磁盘IO)
    2. 将左子树节点加载到内存中,比较16<28<47,向下检索。(1次磁盘IO)
    3. 检索到叶节点,将节点加载到内存中遍历,比较16<28,18<28,28=28。查找到值等于30的索引项。(1次磁盘IO)
    4. 从索引项中获取磁盘地址,然后到数据文件user.MYD中获取对应整行记录。(1次磁盘IO)
    5. 将记录返给客户端。

    磁盘IO次数:3次索引检索+记录数据检索。

    在这里插入图片描述

    根据主键范围查询数据:

    select * from user where id between 28 and 47;
    
    1. 先在主键树中从根节点开始检索,将根节点加载到内存,比较28<75,走左路。(1次磁盘IO)

    2. 将左子树节点加载到内存中,比较16<28<47,向下检索。(1次磁盘IO)

    3. 检索到叶节点,将节点加载到内存中遍历比较16<28,18<28,28=28<47。查找到值等于28的索引项。

      根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)

      我们的查询语句时范围查找,需要向后遍历底层叶子链表,直至到达最后一个不满足筛选条件。

    4. 向后遍历底层叶子链表,将下一个节点加载到内存中,遍历比较,28<47=47,根据磁盘地址从数据文件中获取行记录缓存到结果集中。(1次磁盘IO)

    5. 最后得到两条符合筛选条件,将查询结果集返给客户端。

    磁盘IO次数:4次索引检索+记录数据检索。

    在这里插入图片描述

    **备注:**以上分析仅供参考,MyISAM在查询时,会将索引节点缓存在MySQL缓存中,而数据缓存依赖于操作系统自身的缓存,所以并不是每次都是走的磁盘,这里只是为了分析索引的使用过程。

    辅助索引

    在 MyISAM 中,辅助索引和主键索引的结构是一样的,没有任何区别,叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的,而辅助索引的键值可以重复。

    查询数据时,由于辅助索引的键值不唯一,可能存在多个拥有相同的记录,所以即使是等值查询,也需要按照范围查询的方式在辅助索引树中检索数据。

    InnoDB索引

    主键索引(聚簇索引)

    每个InnoDB表都有一个聚簇索引 ,聚簇索引使用B+树构建,叶子节点存储的数据是整行记录。一般情况下,聚簇索引等同于主键索引,当一个表没有创建主键索引时,InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下:

    1. 在表上定义主键PRIMARY KEY,InnoDB将主键索引用作聚簇索引。
    2. 如果表没有定义主键,InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
    3. 如果以上两个都没有,InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

    除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB,辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时,InnoDB使用此主键值在聚簇索引中搜索行记录。

    这里以user_innodb为例,user_innodb的id列为主键,age列为普通索引。

    CREATE TABLE `user_innodb`
    (
      `id`       int(11) NOT NULL AUTO_INCREMENT,
      `username` varchar(20) DEFAULT NULL,
      `age`      int(11)     DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_age` (`age`) USING BTREE
    ) ENGINE = InnoDB;
    

    在这里插入图片描述

    InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式,是聚簇索引。

    主键索引的叶子节点会存储数据行,辅助索引只会存储主键值。

    在这里插入图片描述

    等值查询数据:

    select * from user_innodb where id = 28;
    
    1. 先在主键树中从根节点开始检索,将根节点加载到内存,比较28<75,走左路。(1次磁盘IO)

    2. 将左子树节点加载到内存中,比较16<28<47,向下检索。(1次磁盘IO)

    3. 检索到叶节点,将节点加载到内存中遍历,比较16<28,18<28,28=28。查找到值等于28的索引项,直接可以获取整行数据。将改记录返回给客户端。(1次磁盘IO)

      磁盘IO数量:3次。

    在这里插入图片描述

    辅助索引

    除聚簇索引之外的所有索引都称为辅助索引,InnoDB的辅助索引只会存储主键值而非磁盘地址。

    以表user_innodb的age列为例,age索引的索引结果如下图。

    在这里插入图片描述

    底层叶子节点的按照(age,id)的顺序排序,先按照age列从小到大排序,age列相同时按照id列从小到大排序。

    使用辅助索引需要检索两遍索引:首先检索辅助索引获得主键,然后使用主键到主索引中检索获得记录。

    画图分析等值查询的情况:

    select * from t_user_innodb where age=19;
    

    在这里插入图片描述

    根据在辅助索引树中获取的主键id,到主键索引树检索数据的过程称为回表查询。

    磁盘IO数:辅助索引3次+获取记录回表3次

    组合索引

    还是以自己创建的一个表为例:表 abc_innodb,id为主键索引,创建了一个联合索引idx_abc(a,b,c)。

    CREATE TABLE `abc_innodb`
    (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `a`  int(11)     DEFAULT NULL,
      `b`  int(11)     DEFAULT NULL,
      `c`  varchar(10) DEFAULT NULL,
      `d`  varchar(10) DEFAULT NULL,
      PRIMARY KEY (`id`) USING BTREE,
      KEY `idx_abc` (`a`, `b`, `c`)
    ) ENGINE = InnoDB;
    

    select * from abc_innodb order by a, b, c, id;

    在这里插入图片描述

    组合索引的数据结构:

    在这里插入图片描述

    组合索引的查询过程:

    select * from abc_innodb where a = 13 and b = 16 and c = 4;
    

    在这里插入图片描述

    最左匹配原则:

    最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。

    在组合索引树中,最底层的叶子节点按照第一列a列从左到右递增排列,但是b列和c列是无序的,b列只有在a列值相等的情况下小范围内递增有序,而c列只能在a,b两列相等的情况下小范围内递增有序。

    就像上面的查询,B+树会先比较a列来确定下一步应该搜索的方向,往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列,B+树就不知道第一步应该从哪个节点查起。

    可以说创建的idx_abc(a,b,c)索引,相当于创建了(a)、(a,b)(a,b,c)三个索引。、

    组合索引的最左前缀匹配原则:使用组合索引查询时,mysql会一直向右匹配直至遇到范围查询(>、<、between、like)就停止匹配。

    覆盖索引

    覆盖索引并不是说是索引结构,覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候,我们只可以拿到主键值,相当于获取数据还需要再根据主键查询主键索引再获取到数据。但是试想下这么一种情况,在上面abc_innodb表中的组合索引查询时,如果我只需要abc字段的,那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了,而不需要回表。这种情况就是覆盖索引

    可以看一下执行计划:

    覆盖索引的情况:

    在这里插入图片描述

    未使用到覆盖索引:

    在这里插入图片描述

    总结

    看到这里,你是不是对于自己的sql语句里面的索引的有了更多优化想法呢。比如:

    避免回表

    在InnoDB的存储引擎中,使用辅助索引查询的时候,因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引,索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能。那如何避免呢?

    使用索引覆盖,举个例子:现有User表(id(PK),name(key),sex,address,hobby…)

    如果在一个场景下,select id,name,sex from user where name ='zhangsan';这个语句在业务上频繁使用到,而user表的其他字段使用频率远低于它,在这种情况下,如果我们在建立 name 字段的索引的时候,不是使用单一索引,而是使用联合索引(name,sex)这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。

    这里就是一个典型的使用覆盖索引的优化策略减少回表的情况。

    联合索引的使用

    联合索引,在建立索引的时候,尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间,还可以更容易的使用到索引覆盖。试想一下,索引的字段越多,是不是更容易满足查询需要返回的数据呢。比如联合索引(a_b_c),是不是等于有了索引:a,a_b,a_b_c三个索引,这样是不是节省了空间,当然节省的空间并不是三倍于(a,a_b,a_b_c)三个索引,因为索引树的数据没变,但是索引data字段的数据确实真实的节省了。

    联合索引的创建原则,在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面,频繁使用代表索引利用率高,区分度高代表筛选粒度大,这些都是在索引创建的需要考虑到的优化场景,也可以在常需要作为查询返回的字段上增加到联合索引中,如果在联合索引上增加一个字段而使用到了覆盖索引,那我建议这种情况下使用联合索引。

    联合索引的使用

    1. 考虑当前是否已经存在多个可以合并的单列索引,如果有,那么将当前多个单列索引创建为一个联合索引。
    2. 当前索引存在频繁使用作为返回字段的列,这个时候就可以考虑当前列是否可以加入到当前已经存在索引上,使其查询语句可以使用到覆盖索引。
    展开全文
  • Mysql索引:图文并茂,深入探究索引的原理和使用

    万次阅读 多人点赞 2020-11-25 16:43:44
    关于Mysql索引的走心总结,建议收藏,反复阅读。

    前言

    相信每个IT界大佬,简历上少不了Mysql索引这个关键字,但如果被问起来,你能说出多少干货呢?先看下面几个问题测试一下吧:

    • 索引是怎么提高查询效率的?可以为了提高查询效率增加索引么?
    • mysql索引系统采用的数据结构是什么?
    • 为什么要使用B+树?
    • 聚集索引相对于非聚集索引的区别?
    • 什么是回表?
    • 什么是索引覆盖?
    • 什么是最左匹配原则?
    • 索引失效场景有哪些,如何避免?

    这些问题说不明白?不要慌!请带着问题向下看。
    在这里插入图片描述

    1 索引原理探究

    什么是数据库索引?先来个官方一些的定义吧。

    在关系数据库中,索引是一种单独的、物理的数对数据库表中一列或多列的值进行排序的一种存储结构,它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

    这段话有点绕,其实把索引理解为图书目录,就非常好理解了。

    如果我们想在图书中查找特定内容,在没有目录的情况下只能逐页翻找。与此类似,当执行下面这样一条SQL语句时,假如没有索引,数据库如何查找到相对应的记录呢?

    SELECT * FROM student WHERE name='叶良辰'
    

    搜索引擎只能扫描整个表的每一行,并依次对比判断name的值是否等于“叶良辰”。我们知道,单纯的内存运算是很快的,但从磁盘中取数据到内存中是相对慢的,当表中有大量数据时,内存与磁盘交互次数大大增加,这就导致了查询效率低下。

    1.1 B树与B+树

    相对于cpu和内存操作,磁盘IO开销很大,非常容易成为系统的性能瓶颈,因此计算机操作系统做了一些优化:

    当一次IO时,将相邻的数据也都读取到内存缓冲区内,而不是仅仅读取当前磁盘地址的数据。因为局部预读性原理告诉我们,当计算机访问一个地址的数据的时候,与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关,一般为4k或8k,也就是我们读取一页内的数据时候,实际上才发生了一次IO,这个理论对于索引的数据结构设计非常有帮助。

    为什么索引能提升数据库查询效率呢?根本原因就在于索引减少了查询过程中的IO次数。那么它是如何做到的呢?使用B+树。下面先简单了解一下B树和B+树。

    B树,即平衡多路查找树(B-Tree),是为磁盘等外存储设备设计的一种平衡查找树。

    B树简略示意图:
    在这里插入图片描述
    观察上图可见B树的两个特点:

    1. 树内的每个节点都存储数据
    2. 叶子节点之间无指针连接

    B+树简略示意图:
    在这里插入图片描述
    再看B+树相对于B树的两个特点:

    1. 数据只出现在叶子节点
    2. 所有叶子节点增加了一个链指针

    叶子结点是离散数学中的概念。一棵树当中没有子结点(即度为0)的结点称为叶子结点,简称“叶子”。 叶子是指出度为0的结点,又称为终端结点。

    但是,为什么是B+树而不是B树呢?原因有两点:

    1. B树每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点能存储的key的数量很小,要保存同样多的key,就需要增加树的高度。树的高度每增加一层,查询时的磁盘I/O次数就增加一次,进而影响查询效率。而在B+Tree中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+树的高度。
    2. B+树的叶子节点上有指针进行相连,因此在做数据遍历的时候,只需要对叶子节点进行遍历即可,这个特性使得B+树非常适合做范围查询。

    1.2 聚簇索引与非聚簇索引

    首先,为了方便理解,我们先了解一下聚集索引(clustered index)和非聚集索引(secondary index,也称辅助索引或普通索引)。这两种索引是按存储方式进行区分的。

    聚集索引(clustered)也称聚簇索引,这种索引中,数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表的物理顺序只有一种情况,因此对应的聚集索引只能有一个。如果某索引不是聚集索引,则表中的行物理顺序与索引顺序不匹配,与非聚集索引相比,聚集索引有着更快的检索速度。

    如果不好理解,请看下面这个表:

    idnamescore物理地址
    1叶良辰780×01
    2龙傲天880×02
    3赵日天560×03
    4徐胜虎770×04

    表中id和物理地址是保持一致顺序的,id较大的行,其物理地址也比较靠后。因为聚集索引的特性,它的建立有一定的特殊要求:

    1. Innodb中,聚簇索引默认就是主键索引。
    2. 如果表中没有定义主键,那么该表的第一个唯一非空索引被作为聚集索引。
    3. 如果没有主键也没有合适的唯一索引,那么innodb内部会生成一个隐藏的主键作为聚集索引,这个隐藏的主键是一个6个字节的列,改列的值会随着数据的插入自增。

    大家还记得,自增主键和uuid作为主键的区别么?由于主键使用了聚集索引,如果主键是自增id,那么对应的数据一定也是相邻地存放在磁盘上的,写入性能比较高。如果是uuid的形式,频繁的插入会使innodb频繁地移动磁盘块,写入性能就比较低了。

    1.3 索引原理图示

    下面用一个通过主键索引查找数据的案例演示一下索引的原理。假如有student表如下,id上建立了聚集索引,name上建立非聚集索引:

    idnamescore
    2叶良辰78
    4龙傲天88
    10赵日天56
    11徐胜虎77

    1.3.1 聚簇索引

    当我们执行下面的语句时,

    SELECT name FROM student WHERE id=2
    

    查询过程如下图所示:
    在这里插入图片描述

    用语言描述一下,是这样的:

    1. 先找到根节点所在磁盘块,读入内存。(第1次磁盘I/O操作)
    2. 在内存中判断id=3所在区间(0,8),找到该区间对应的指针1(第1次内存查找)
    3. 根据指针1记录的磁盘地址,找到磁盘块2并读入内存(第2次磁盘I/O操作)
    4. 在内存中判断id=3所在区间(0,4),找到该区间对应的指针2(第2次内存查找)
    5. 根据指针2记录的磁盘地址,找到磁盘块4并读入内存(第3次磁盘I/O操作)
    6. 在内存中查找到id=2对应的数据行记录(第3次内存查找)

    我们知道,磁盘I/O相对于内存运算(尤其内存中的主键是有序排列的,利用二分查找等算法效率非常高)耗时高得多,因此在数据库查询中,减少磁盘访问时数据库的性能优化的主要手段。

    而分析上面过程,发现整个查询只需要3次磁盘I/O操作(其实InnoDB引擎是将根节点常驻内存的,第1次磁盘I/O操作并不存在)和3次内存查找操作。相对于不使用索引的遍历式查找,大大减少了对磁盘的访问,因此查找效率大幅提高。但是,因为索引树要与表中数据保持一致,因此当表发生数据增删改时,索引树也要相应修改,导致写数据比没有索引时开销大一些。

    1.3.2 非聚簇索引

    好,聚集索引看完后,再看非聚集索引。
    在这里插入图片描述
    如上图,多加一个索引,就会多生成一颗非聚簇索引树。因此,索引不能随意增加。在做写库操作的时候,需要同时维护这几颗树的变化,导致效率降低!

    另外,仔细观察的人一定会发现,不同于聚集索引,非聚集索引叶子节点上不再是真实数据,而是存储了索引字段自身值和主键索引。因此,当我们执行以下SQL语句时:

    SELECT id,name FROM student WHERE name='叶良辰';
    

    整个查询过程与聚集索引的过程一样,只需要扫描一次索引树(n次磁盘I/O和内存查询),即可拿到想要的数据。

    但是,如果查询name索引树没有的数据时,情况就不一样了:

    SELECT score FROM student WHERE name='叶良辰';
    

    在这里插入图片描述
    注意看上图中的红色箭头,因为扫描完name索引后,Mysql只能获取到对应的idname,然后用id的值再去聚集索引中去查询score的值。这个过程相对于聚集索引查询的效率下降,可以理解了吧。

    这就是通常所说的回表或者二次查询:使用聚集索引查询可以直接定位到记录,而普通索引通常需要扫描两遍索引树,即先通过普通索引定位到主键值,在通过聚集索引定位到行记录,这就是所谓的回表查询,它的性能比扫描一遍索引树低。

    既然普通索引会导致回表二次查询,那么有什么办法可以应对呢?建立联合索引!

    1.3.3 联合索引

    所谓联合索引,也称多列所谓,就是建立在多个字段上的索引,这个概念是跟单列索引相对的。联合索引依然是B+树,但联合索引的健值数量不是一个,而是多个。构建一颗B+树只能根据一个值来构建,因此数据库依据联合索引最左的字段来构建B+树。

    例如在a和b字段上建立联合索引,索引结构将如下图所示:
    在这里插入图片描述
    一目了然,当我们再执行SELECT score FROM student WHERE name='叶良辰';时,可以直接通过扫描非聚集索引直接获取score的值,而不再需要到聚集索引上二次扫描了。

    最左前缀匹配

    联合索引中有一个重要的课题,就是最左前缀匹配。

    最左前缀匹配原则:在MySQL建立联合索引时会遵守最左前缀匹配原则,即最左优先,在检索数据时从联合索引的最左边开始匹配。

    这是为什么呢?我们再仔细观察索引结构,可以看到索引key在排序上,首先按a排序,a相等的节点中,再按b排序。因此,如果查询条件是a或a和b联查时,是可以应用到索引的。如果查询条件是单独使用b,因为无法确定a的值,因此无法使用索引。

    假如在table表的a,b,c三个列上建立联合索引,简要分类分析下联合索引的最左前缀匹配。

    首先看等值查询:

    1、全值匹配查询时(where子句搜索条件顺序调换不影响索引使用,因为查询优化器会自动优化查询顺序 ),可以用到联合索引

    SELECT * FROM table WHERE a=1 AND b=3 AND c=2
    SELECT * FROM table WHERE b=3 AND c=4 AND a=2
    

    2、匹配左边的列时,可以用到联合索引

    SELECT * FROM table WHERE a=1
    SELECT * FROM table WHERE a=1 AND b=3
    

    3、未从最左列开始时,无法用到联合索引

    SELECT * FROM table WHERE b=1 AND b=3
    

    4、查询列不连续时,无法使用联合索引(会用到a列索引,但c排序依赖于b,所以会先通过a列的索引筛选出a=1的记录,再在这些记录中遍历筛选c=3的值,是一种不完全使用索引的情况)

    SELECT * FROM table WHERE a=1 AND c=3
    

    再看范围查询:

    1、范围查询最左列,可以使用联合索引

    SELECT * FROM table WHERE a>1 AND a<5;
    

    2、精确匹配最左列并范围匹配其右一列(a值确定时,b是有序的,因此可以使用联合索引)

    SELECT * FROM table WHERE a=1 AND b>3;
    

    3、精确匹配最左列并范围匹配非右一列(a值确定时,c排序依赖b,因此无法使用联合索引,但会使用a列索引筛选出a>2的记录行,再在这些行中条件 c >3逐条过滤)

    SELECT * FROM table WHERE a>2 AND c>5;
    

    索引的原理探究到此结束,这部分内容堪称最难啃的骨头。不过,能坚持读下来的朋友,你的收获也一定良多。接下来的内容就轻松愉悦多了。
    在这里插入图片描述

    2 索引的正确使用姿势

    索引的优点如下:

    • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
    • 可以大大加快数据的查询速度,这是使用索引最主要的原因。
    • 在实现数据的参考完整性方面可以加速表与表之间的连接。
    • 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。

    既然索引这么好,那么我们是不是尽情使用索引呢?非也,索引优点明显,但相对应,也有缺点:

    • 创建和维护索引组要耗费时间,并且随着数据量的增加所耗费的时间也会增加。
    • 索引需要占磁盘空间,除了数据表占数据空间以外,每一个索引还要占一定的物理空间。
    • 当对表中的数据进行增加、删除和修改的时候,索引也要动态维护,这样就降低了数据的维护速度。

    因此,使用索引时要兼顾索引的优缺点,寻找一个最有利的平衡点。

    2.1 索引的类型区分

    InnoDB引擎为例,Mysql索引可以做如下区分。

    首先,索引可以分为聚集索引和非聚集索引,它们的区别和含义在前文有大幅介绍,此处不再赘述。

    其次,从逻辑上,索引可以区分为:

    • 普通索引:普通索引是 MySQL 中最基本的索引类型,它没有任何限制,唯一任务就是加快系统对数据的访问速度。普通索引允许在定义索引的列中插入重复值和空值。
    • 唯一索引:唯一索引与普通索引类似,不同的是创建唯一性索引的目的不是为了提高访问速度,而是为了避免数据出现重复。唯一索引列的值必须唯一,允许有空值。如果是组合索引,则列值的组合必须唯一。创建唯一索引通常使用 UNIQUE 关键字。例如在student 表中的 id 字段上建立名为 index_id 的索引CREATE UNIQUE INDEX index_id ON tb_student(id);
    • 主键索引:主键索引就是专门为主键字段创建的索引,也属于索引的一种。主键索引是一种特殊的唯一索引,不允许值重复或者值为空。创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。
    • 空间索引:空间索引是对空间数据类型的字段建立的索引,空间索引主要用于地理空间数据类型 ,很少用到。
    • 全文索引:全文索引主要用来查找文本中的关键字,只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。全文索引允许在索引列中插入重复值和空值。

    索引在实际使用上分为单列索引和多列索引。

    单列索引:单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引,单列索引只根据该字段进行索引。

    例如在student 表中的 address 字段上建立名为 index_addr 的单列索引,address 字段的数据类型为 VARCHAR(20),索引的数据类型为 CHAR(4)。SQL 语句如下:

    CREATE INDEX index_addr ON student(address(4));
    

    这样,查询时可以只查询 address 字段的前 4 个字符,而不需要全部查询。

    **多列索引也称为复合索引或组合索引。**相对于单列索引来说,组合索引是将原表的多个列共同组成一个索引。

    多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段,可以通过这几个字段进行查询。但是,只有查询条件中使用了这些字段中第一个字段时,索引才会被使用。

    下面在 student 表中的 name 和 address 字段上建立名为 index_na 的索引,SQL 语句如下:

    CREATE INDEX index_na ON tb_student(name,address);
    

    该索引创建好了以后,查询条件中必须有 name 字段才能使用索引。

    一个表可以有多个单列索引,但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引,以此来加快查询速度。比如,在一个表中创建了一个组合索引(c1,c2,c3),在实际查询中,系统用来实际加速的索引有三个:单个索引(c1)、双列索引(c1,c2)和多列索引(c1,c2,c3)。

    2.2 索引的查看

    查看索引的语法格式如下:

    SHOW INDEX FROM <表名>
    

    查询结果说明如下:

    参数说明
    Table表示创建索引的数据表名
    Non_unique表示该索引是否是唯一索引。若不是唯一索引,则该列的值为 1;若是唯一索引,则该列的值为 0。
    Key_name表示索引的名称。
    Seq_in_index表示该列在索引中的位置,如果索引是单列的,则该列的值为 1;如果索引是组合索引,则该列的值为每列在索引定义中的顺序。
    Column_name表示定义索引的列字段。
    Collation表示列以何种顺序存储在索引中。在 MySQL 中,升序显示值“A”(升序),若显示为 NULL,则表示无分类。
    Cardinality索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL 使用该索引的机会就越大。
    Sub_part表示列中被编入索引的字符的数量。若列只是部分被编入索引,则该列的值为被编入索引的字符的数目;若整列被编入索引,则该列的值为 NULL。
    Packed指示关键字如何被压缩。若没有被压缩,值为 NULL。
    Null用于显示索引列中是否包含 NULL。若列含有 NULL,该列的值为 YES。若没有,则该列的值为 NO。
    Index_type显示索引使用的类型和方法(BTREE、FULLTEXT、HASH、RTREE)。
    Comment显示评注。

    2.3 索引的创建

    创建索引有3种方式:

    1、CREATE INDEX直接创建:

    可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引,但该语句不能创建主键。

    CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
    

    语法说明如下:

    • <索引名>:指定索引名。一个表可以创建多个索引,但每个索引在该表中的名称是唯一的。
    • <表名>:指定要创建索引的表名。
    • <列名>:指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
    • <长度>:可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小,节省索引列所占的空间。在某些情况下,只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节(MyISAM 和 InnoDB 表的最大上限为 1000 个字节),如果索引列的长度超过了这个上限,就只能用列的前缀进行索引。另外,BLOB 或 TEXT 类型的列也必须使用前缀索引。
    • ASC|DESC:可选项。ASC指定索引按照升序来排列,DESC指定索引按照降序来排列,默认为ASC。

    例如,在studentname字段上创建索引:

    • 普通索引:CREATE INDEX index_name ON student (name)
    • 唯一索引:CREATE UNIQUE index_name ON student (name)

    创建普通索引使用的关键字,例如在studentname字段上创建一个普通索引index_name

    • 建表创建:CREATE TABLE student(id INT NOT NULL,name CHAR(45) DEFAULT NULL,INDEX(name));
    • ALTER TABLEALTER student ADD INDEX index_name (name)

    2、 CREATE TABLE时创建

    索引也可以在创建表(CREATE TABLE)的同时创建。在 CREATE TABLE 语句中添加以下语句。例如创建student表时在name字段添加索引:

    • 主键索引:CREATE TABLE student(name CHAR(45) PRIMARY KEY);
    • 唯一索引:CREATE TABLE student(id INT NOT NULL,name CHAR(45) DEFAULT NULL,UNIQUE INDEX(name));
    • 普通索引:CREATE TABLE student(id INT NOT NULL,name CHAR(45) DEFAULT NULL,INDEX(name));

    3、 ALTER TABLE时创建

    ALTER TABLE 语句也可以在一个已有的表上创建索引。例如在studentname字段上创建一个普通索引index_name

    • 主键索引:ALTER TABLE student ADD PRIMARY KEY (name);
    • 唯一索引:ALTER TABLE student ADD UNIQUE INDEX index_name(name);
    • 普通索引:ALTER TABLE student ADD INDEX index_name(name);

    2.4 索引失效场景

    创建了索引并不意味着高枕无忧,在很多场景下,索引会失效。下面列举了一些导致索引失效的情形,是我们写SQL语句时应尽量避免的。

    1、条件字段原因

    • 单字段有索引,WHERE条件使用多字段(含带索引的字段),例如 SELECT * FROM student WHERE name ='张三' AND addr = '北京市'语句,如果name有索引而addr没索引,那么SQL语句不会使用索引。
    • 多字段索引,违反最佳左前缀原则。例如,student表如果建立了(name,addr,age)这样的索引,WHERE后的第一个查询条件一定要是name,索引才会生效。

    2、<>、NOT、in、not exists

    当查询条件为等值或范围查询时,索引可以根据查询条件去找对应的条目。否则,索引定位困难(结合我们查字典的例子去理解),执行计划此时可能更倾向于全表扫描,这类的查询条件有:<>、NOT、in、not exists

    3、查询条件中使用OR

    如果条件中有or,即使其中有条件带索引也不会使用(因此SQL语句中要尽量避免使用OR)。要想使用OR,又想让索引生效,只能将OR条件中的每个列都加上索引。

    4、查询条件使用LIKE通配符

    SQL语句中,使用后置通配符会走索引,例如查询姓张的学生(SELECT * FROM student WHERE name LIKE '张%'),而前置通配符(SELECT * FROM student WHERE name LIKE '%东')会导致索引失效而进行全表扫描。

    5、索引列上做操作(计算,函数,(自动或者手动)类型装换)

    有以下几种例子:

    • 在索引列上使用函数:例如select * from student where upper(name)='ZHANGFEI';会导致索引失效,而select * from student where name=upper('ZHANGFEI');是会使用索引的。
    • 在索引列上计算:例如select * from student where age-1=17;

    6、在索引列上使用mysql的内置函数,索引失效

    例如,SELECT * FROM student WHERE create_time

    7、索引列数据类型不匹配

    例如,如果age字段有索引且类型为字符串(一般不会这么定义,此处只是举例)但条件值为非字符串,索引失效,例如SELECT * FROM student WHERE age=18会导致索引失效。

    8、索引列使用IS NOT NULL或者IS NULL可能会导致无法使用索引

    B-tree索引IS NULL不会使用索引,IS NOT NULL会使用,位图索引IS NULLIS NOT NULL都会使用索引。

    最后,对索引的使用做一个总结吧:

    1. 索引有利于查询,但不能随意加索引,因为索引不仅会占空间,而且需要在写库时进行维护。
    2. 如果多个字段常常需要一起查询,那么在这几个字段上建立联合索引是个好办法,同时注意最左匹配原则。
    3. 不要在重复度很高的字段上加索引,例如性别。
    4. 避免查询语句导致索引失效,哪些情况会导致索引失效请见前文。

    劝君莫做白嫖党!欢迎关注❤️、评论📝、点赞👍!

    展开全文
  • 之前松哥在前面的文章中介绍 MySQL 的索引时,有小伙伴表示被概念搞晕了,主键索引、非主键索引、聚簇索引、非聚簇索引、二级索引、辅助索引等等,今天咱们就来捋一捋这些概念。 1. 按照功能划分 按照功能来划分,...
  • 【MySQL】单表访问之索引合并

    万次阅读 2022-01-01 11:29:40
    MySQL在一般情况下执行一个查询时最多只会用到单个二级索引,但存在有特殊情况,在这些特殊情况下也可能在一个查询中使用到多个二级索引,MySQL中这种使用到多个索引来完成一次查询的执行方法称之为:索引合并/index...
  • 数据库索引详解

    万次阅读 多人点赞 2021-11-17 19:16:13
    1、什么是索引?为什么要用索引? 1.1、索引的含义 数据库索引,是数据库管理系统中一个排序的数据结构,以协助快速查询,更新数据库中表的数据。索引的实现通常使用B树和变种的B+树(MySQL常用的索引就是B+树)。...
  • 索引的种类

    千次阅读 2021-09-03 06:14:38
    索引的种类 索引有哪几种类型? 主键索引:数据列不允许重复,不允许为NULL,一个表只有一个主键。 唯一索引:数据列不允许重复,允许为NULL,一个表允许多个列创建唯一索引。 普通索引:基本的索引类型,没有唯一...
  • MySQL索引优化总结

    千次阅读 2021-09-29 23:17:15
    前言:相信大家都知道索引可以大大提高MySQL的检索速度,但是真正在平时工作中写SQL,真的会考虑到这条SQL如何能够用上索引提升执行效率?本篇博客详细的介绍了索引优化的20个原则,只要在工作中能够随时应用到,...
  • Mysql 索引

    千次阅读 2021-01-19 03:17:33
    开门见山,直接上图,下面的思维导图即是现在要讲的内容,可以先有个印象~常见索引类型(实现层面)索引种类(应用层面)聚簇索引与非聚簇索引覆盖索引最佳索引使用策略1.常见索引类型(实现层面)首先不谈Mysql怎么实现...
  • 一文搞懂MySQL索引(清晰明了)

    万次阅读 多人点赞 2021-02-02 17:30:43
    索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。 MySQL中常用的索引结构(索引底层的数据结构)有:B-TREE ,B+TREE ,...
  • MySQL索引及常见面试题

    千次阅读 2022-01-19 09:33:44
    一、索引是什么? 索引(Index)是帮助 MySQL 高效获取数据的数据结构,是对表中一列或多列值进行排序的结构。 就比如索引是一本书的目录,可以通过目录快速查找自己想要查询的东西。 二、索引为什么使用B+树? 先看...
  • Oracle索引详解

    千次阅读 2021-08-11 16:21:35
    2、使用索引的目的二、索引的分类及结构1、逻辑上:2、物理上:三、各种索引详解1、 B树索引(1)特点:(2)技巧:2、位图索引(1)特点:(2)技巧:3、 反向索引(1)特点:(2)技巧:4、HASH索引(1)特点:5、...
  • 一文搞懂 MySQL 中的索引

    千次阅读 多人点赞 2021-12-01 09:47:24
    1. 什么是索引 MySQL 官方对索引的定义为:索引(Index)是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。 举一个例子,平时看任何一本书,首先看到的都是目录,通过目录去查询书籍里面...
  • Mysql 创建索引

    千次阅读 2022-02-23 10:52:47
    数据库建立索引常用的规则如下: 1、表的主键、外键必须有索引;2、数据量超过300的表应该有索引; 3、经常与其他表进行连接的表,在连接字段上应该建立索引; 4、经常出现在Where子句中的字段,特别是大表的字段,...
  • MySQL 全文索引

    千次阅读 2021-09-23 13:09:16
    关系型数据库中的全文索引应该也是从这些搜索引擎里摸索出来的。 全文索引介绍: 在数据库中常用的查询方式一般是 等价,范围方式。当然也有LIKE %的模糊查询,虽然用不到索引,在文本内容比较少时是比较合适,...
  • 普通索引是对列值或列的前缀值进行索引,而MySQL 8.0.13之后支持函数索引,函数索引是对表中的列执行表达式计算后的结构进行索引,而不是对列或列前缀值。使用函数索引可以对未直接存储在表中的数据进行索引。 函数...
  • 使用索引也很简单,然而, 会使用索引是一回事, 而深入理解索引原理又能恰到好处使用索引又是另一回事。 这已经是两个相差甚远的技术层级了。 二、千万级数据表索引和无索引查询效率对比 现在有一个学生表student,...
  • mysql索引(七)唯一索引

    万次阅读 2021-07-19 11:17:54
    Mysql索引大概有五种类型: 普通索引(INDEX):最基本的索引,没有任何限制 唯一索引(UNIQUE):与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。 主键索引(PRIMARY):它 是一种特殊的唯一索引,不...
  • 索引操作2.1 创建标准索引:2.2 查看表中的索引:2.3 查看索引信息:2.4 分析索引是否存在索引碎片:2.5 重建索引:2.6 删除索引:三.执行计划(explain plan)3.1 执行计划概念3.2 如何查看执行计划3.3 表访问方式 一....
  • 首先我们创建第一张表为组合索引,如下所示: 第二种表为单列索引,如下所示: 创建完表之后我们开始
  • Python 列表查找,如何在列表中查找项目或者元素索引 在本文中,你将学习如何在Python中查找列表中包含元素的索引。 有几种方法可以实现这一点,在本文中,你将学习三种不同的方式用于查找列表元素的索引 三种技巧...
  • MySQL之详解索引

    万次阅读 2021-03-30 19:29:22
    索引的使用4.1 索引相关语句4.1.1 单列索引之普通索引4.1.2 单列索引之唯一索引4.1.3 单列索引之全文索引4.1.4 组合索引4.1.5 删除索引4.1.6 查看索引5.索引原理分析5.1 索引的存储结构5.1.1 B树5.1.2 B树和B+树的...
  • MySQL索引优化

    千次阅读 多人点赞 2021-02-19 09:42:44
    首先,我们将从索引基础开始介绍一下什么是索引,分析索引的几种类型,并探讨一下如何创建索引以及索引设计的基本原则。 此部分用于测试索引创建的user表的结构如下: desc user; 1. 什么是索引? "索引...
  • 索引的操作

    千次阅读 2021-11-17 22:42:17
    一、学习任务1:为什么使用索引 在MySQL数据库中,数据库对象表是存储和操作数据的逻辑结构,而本章所要介绍的数据库对象索引则是一种有效组合数据的方式.通过索引对象,可以快速查询到数据库对象表中的特定记录,...
  • MySQL高级篇——索引的数据结构

    千次阅读 多人点赞 2022-04-11 16:35:12
    1.为什么使用索引? 2.索引的优缺点 3.InnoDB中的索引 3.1 设计索引 3.2 常见索引概念 3.2.1 聚簇索引 3.2.2 非聚簇索引 3.2.3 联合索引 4.InnoDB与MyISAM的索引对比 5.B-Tree和B+Tree的差异 1.为什么使用...
  • MySQL中索引的使用方法

    千次阅读 多人点赞 2021-05-03 13:23:45
    MySQL中索引的使用方法 文章目录MySQL中索引的使用方法1. 为什么要加索引?2. 索引的创建2.1 使用 `ALTER TABLE` 语句创建索引2.2 使用 `CREATE INDEX` 语句对表增加索引3. 索引的删除4. 组合索引与前缀索引5. 索引...
  • MySql之索引

    万次阅读 多人点赞 2019-11-05 20:50:12
    Mysql 索引精讲 开门见山,直接上图,下面的思维导图即是现在要讲的内容,可以先有个印象~ 常见索引类型(实现层面) 索引种类(应用层面) 聚簇索引与非聚簇索引 覆盖索引 最佳索引使用策略 1.常见索引类型...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 2,812,965
精华内容 1,125,186
关键字:

索引

友情链接: CAT5171-D.PDF.zip