前言本章内容章节顺序快速浏览索引的初步认识在了解什么是索引之前我们先来举个例子当我们想要查找一本书中的内容时我们会选择一页页全部翻看还是选择先通过目录来大致缩小查找范围呢毫无疑问我们的时间是宝贵的当然选择去查找目录。理解了这个我们就理解了索引。我们把目录比作索引当数据库查找具体内容时就会先通过索引来快速查找到内容。这种核心思想就是用空间来换取时间刷力扣的小伙伴肯定不陌生。通过上一篇我们理解到了MySQL分为Service服务层与存储引擎层。图解MySQL 执行全链路讲解我们在这里提到的索引与数据内容便是存储在这存储引擎中。提到了存储引擎我们需要对他的认识有如何存储一你请数据如何为存储的数据建立索引和如何更新查询数据等技术的实现方法。MySQL的存储引擎有‘MyISAMInnoDB、Memory其中 InnoDB 是在 MySQL 5.5 之后成为默认的存储引擎。索引的分类具体的分类细节已经如下分类好了我们在这里对他们的特点进行讲解按数据结构分类常见的有Btree索引、Hash索引、Full-text索引每种存储引擎支持的索引类型不同具体见下图InnoDB在MySQL 5.5版本后成为了MySQL的默认存储引擎其中的BTree是被采用最多的索引类型。InnoDB存储引擎创建表规则如果有主键就用主键作为key没有主键用第一个不含NULL值的唯一列作为key如果都没有InnoDB 将自动生成一个隐式自增 id 列作为ley我们通过一个例子来了解一下BTree 索引的存储和查询的过程先创建一张商品表id为主键CREATETABLEproduct(idint(11)NOTNULL,product_novarchar(20)DEFAULTNULL,namevarchar(255)DEFAULTNULL,pricedecimal(10,2)DEFAULTNULL,PRIMARYKEY(id)USINGBTREE)CHARACTERSETutf8COLLATEutf8_general_ci ROW_FORMATDynamic;商品表中有这些数据这些数据存储在BTree索引时是这样的BTree是一种多叉树叶子节点才存放数据非叶子节点只存放索引节点中的数据是按照主键顺序存放的。每一层父节点的索引值都会出现在下层子节点的索引值中也就是说在叶子节点中包括了所有的索引值信息。每个叶子节点都有两个指针分别指向下一个叶子节点和上一个叶子节点图中只画出了一个节点通过主键查询数据的过程select*fromproductwhereid5;查询过程如下BTree 会自顶向下逐层进行查找将 5 与根节点的索引数据 (1, 10, 20) 比较5 在 1 和 10 之间所以根据 BTree的搜索逻辑找到第二层的索引数据 (1, 4, 7)在第二层的索引数据 (1, 4, 7)中进行查找因为 5 在 4 和 7 之间所以找到第三层的索引数据4, 5, 6在叶子节点的索引数据4, 5, 6中进行查找然后我们找到了索引值为 5 的行数据。数据库的索引和数据都是存储在硬盘的我们可以把读取一个节点当作一次磁盘 I/O 操作。那么上面的整个查询过程一共经历了 3 个节点也就是进行了 3 次 I/O 操作。3-4层高度就可以满足BTree 存储千万级的数据。所以BTree 相较于B树和二叉树来说最大的优势在于查询效率高通过二级索引查询商品数据的过程主键索引的BTree 和二级索引的 BTree 区别如下主键索引下叶子节点中存储的是所有完整数据二级索引下叶子节点存放的是主键值不是实际数据。所有要查询两张表也就是回表操作建表如下我们以为例select*fromproductwhereproduct_no0002;具体流程先检索二级索引中BTree的索引值找到对应主键值。通过回表操作再到主键索引中的BTree中查询数据信息。也就是说要查两个 BTree 才能查到数据。这里出现了一个特例覆盖索引当查询的数据能子啊二级索引的BTree里的叶子节点里查询到这时就不用再查主键索引例如selectidfromproductwhereproduct_no0002;这种不需要回表在二级索引的BTree就能查询到结果的过程就叫覆盖索引为什么 MySQL InnoDB 选择 Btree 作为索引的数据结构我们来比较一下BTree/BTree/二叉树/Hash之间的特点BTree vs B TreeBTree只在叶子节点存储数据而B树的非叶子节点也要存储数据所以BTree的单个节点的数据量更小在相同磁盘I/O次数下能查询更多的节点。另外BTree叶子节点用的是双向链表连接适合MySQL中常见的基于范围的顺序查找。BTree vs 二叉树在磁盘中数据是按页Page默认 16KB读取的。二叉树每个节点只有两个分支存储千万级数据时树高可能达到 20-30 层。代价每找一个节点就要进行一次磁盘 I/O。查一条数据要读 20 次盘性能不可接受。BTree 的优势它是多叉树每个节点页能存上百个索引千万级数据只需 3-4 层仅需 3-4 次 I/O。BTree vs HashHash 索引在 WHERE id 1 时无敌快但实际业务中充斥着大量的范围查询、排序和模糊匹配LIKE ‘abc%’。代价Hash 后的数据是无序的遇到 id 100 只能全表扫描。BTree 的优势天然有序且叶子节点互联。按物理存储分类常见的有聚簇索引主键索引、二级索引辅助索引主键索引的BTree的叶子节点存放是是实际数据所有完整的用户记录都存放在叶子节点中二级索引的BTree的叶子节点主要存放的是主键值按字段特性分类常见的有主键索引、唯一索引、普通索引、前缀索引主键索引建立在主键字段上的索引通常在创建表的时候一起创建以后在那个表稚嫩那个有一个主键索引索引列表的只不允许有NULL创建主键索引的方式如下CREATETABLEtable_name(....PRIMARYKEY(index_column_1)USINGBTREE);唯一索引建立在UNIQUE字段上的索引一张表可以有多个唯一索引索引列的值必须唯一允许有NULL创建唯一索引的方式如下CREATETABLEtable_name(....UNIQUEKEY(index_column_1,index_column_2,...));普通索引创建普通索引的方式如下CREATETABLEtable_name(....INDEX(index_column_1,index_column_2,...));前缀索引前缀索引是指对字符类型的字段的前几个字符创建的索引而不是在整个字段上建立的索引前缀索引可以建立在字段类型为char vachar binary varbinary的列上使用前缀索引的目的是为了减少索引占用的存储空间提升查询效率。创建前缀索引的方式如下CREATETABLEtable_name(column_list,INDEX(column_name(length)));按字段个数分类常见的有单列索引、联合索引建立在单列上的索引称为单列索引主键索引建立在多列上的索引称为联合索引联合索引将多个字段组合成一个索引该索引就成为联合索引比如将商品表中的 product_no 和 name 字段组合成联合索引(product_no, name)创建联合索引的方式如下CREATEINDEXindex_product_no_nameONproduct(product_no,name);联合索引的BTree 示意图如下用了两个字段的值作为BTree的key值。当联合索引查询数据时先按照product_no 字段比较当product_no 字段相同时再按name字段比较。这就是最左前缀匹配原则按照最左优先的方式进行索引的匹配。最左前缀匹配原则假设当前有一个联合索引a,b,c)符合最左前缀匹配原则的查询原则wherea1;wherea1andb2;wherea1andb2andc3;不符合whereb2;wherec3;whereb2andc3;范围查询的特殊规则理解范围查询之前得先搞清楚联合索引的排序规则联合索引a,b,c)再B树中的排序是先a若a相同再按bb相同再按c。当遇到,这种范围查询时会停止匹配wherea1andb2andc3;所谓的停止匹配只有a能用上联合索引b和c不行。因为a经过范围查询筛选后不同a值之间的b和c时无序的。当a2与a3他们的b值之间没有任何关系。遇到,,between这种范围查询不会停止匹配。因为这些查询包含等值判断wherea1andb2andc3;可以查询定位到a1的数据a1内部的b和c是有序的。索引下推在学习前面的知识后我们了解了对于联合索引a,b执行select * from table where a 1 and b 2时只有a能用到索引。当我们找到满足a1a2的主键值时还需去判断其他条件是否满足这个判断是在哪里进行的呢在MySQL 5.6之前只能从主键值开始一个个回表回到主键索引上找出数据行再对比b字段值而MySQL 5.6之后引入的索引下推可以在联合索引遍历过程中对联合索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。什么时候需要/不需要创建索引索引的优点是极大的加速了查询速度但是索引也是有缺点的需要占用物理空间数量越大占用空间越大常见索引和维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改查的效率因为每次增删改索引B树为了维护索引有序性都需要进行动态维护。什么时候适用索引字段有唯一性限制的比如商品编码经常用于where查询条件的字段能够提高整个表的查询速度经常用于GROUP BY 和 ORDER BY的字段这样在查询的时候就不需要再去做一次排序。因为在建立索引之后在B树中的记录都是排序好的什么时候不需要创建索引WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的因为索引是会占用物理空间的。字段中存在大量重复数据不需要创建索引。比如性别字段只有男女无论查哪个字段都能得到另一字段数据。并且MySQL还有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候他一般会忽略索引进行全表扫面。表数据很少的时候不需要创建索引经常更新的字段不需要。因为字段频繁修改由于要维护B树的有序性那么基于需要频繁的重建索引。优化索引的方式前缀索引优化含义使用某个字段中字符串的前几个字符建立索引。使用前缀索引可以减少索引字段的大小可以增加一个索引页中存储的索引值有效提高索引的查询速度特例order by 无法使用前缀索引无法把前缀索引用作覆盖索引一句话简写索引字段只留少部分前缀覆盖索引优化含义SQL中query的所有字段在索引B树的叶子节点上都能找得到的哪些索引从二级索引中查询得到记录而不需要通过主键索引查询 获得可以避免回表操作。例我们只需要查询商品的名称价格有什么方式可以避免回表我们可以建立一个联合索引即商品ID、名称、价格。如果索引中存在这些数据查询将不会再次检索主键索引避免回表。一句话通过建立联合索引减少回表操作达到减少I/O的操作主键索引最好是自增的在 InnoDB 中数据行是存储在聚簇索引即主键索引的 B 树叶子节点上的。B 树要求叶子节点的数据必须是有序的。自增的优势在innodb插入新纪录是只需要将新纪录加到B树要求叶子节点的数据必须是有序的。自增主键的优势B 树有序插入页分裂极少因为新主键总是比旧主键大所以总是顺序插入不会引起页面的频繁分裂。空间利用率高每个页面都能填满索引结构非常紧凑。性能极佳磁盘 I/O 是顺序写效率非常高。非自增主键如随机 UUID的劣势页分裂与随机 I/O如果主键是随机的如 UUID新插入记录的主键值可能位于 B 树的任意位置。频繁页分裂新记录可能会被插入到一个已经塞满的页面中间。为了腾出空间InnoDB 必须将该页面分裂成两个并重新移动数据。空间碎片页分裂会导致页面填充率低索引变得“虚胖”且产生大量碎片。随机 I/O为了把数据插入到合适的位置InnoDB 需要频繁加载和写入磁盘上不连续的页面随机 I/O 导致性能急剧下降。一句话自增主键保证了B 树以最顺序、最紧凑、最少磁盘开销的方式增长。防止索引失效用上了索引并不代表索引一定会派上用场。一下是几种常见索引失效的情况使用左或左右模糊匹配。也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效在查询条件中对索引列做计算函数类型转换操作。联合索引要能正确使用需要遵循最左匹配原则也就是按照最左优先的方式进行索引的匹配否则就会导致索引失效实际情况中还会出现其他索引失效的场景。我们可以通过查看type字段查看所需数据时使用的扫描方式是什么常见扫描类型的执行效率从低到高的顺序为• All全表扫描• index全索引扫描• range索引范围扫描• ref非唯一索引扫描• eq_ref唯一索引扫描• const结果只有一条的主键或唯一索引扫描all 是最坏的情况因为采用了全表扫描的方式。index 和 all 差不多只不过 index 对索引表进行全扫描这样做的好处是不再需要对数据进行排序但是开销依然很大。所以要尽量避免全表扫描和全索引扫描。range 表示采用了索引范围扫描一般在 where 子句中使用 、、in、between 等关键词只检索给定范围的行属于范围查找。从这一级别开始索引的作用会越来越明显因此我们需要尽量让 SQL 查询可以使用到 range 这一级别及以上的 type 访问方式。ref 类型表示采用了非唯一索引或者是唯一索引的非唯一性前缀返回数据返回可能是多条。因为虽然使用了索引但该索引列的值并不唯一有重复。这样即使使用索引快速查找到了第一条数据仍然不能停止要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表因为索引是有序的即便有重复值也是在一个非常小的范围内扫描。eq_ref 类型是使用主键或唯一索引时产生的访问方式通常使用在多表联查中。比如对两张表进行联查关联条件是两张表的 user_id 相等且 user_id 是唯一索引那么使用 EXPLAIN 进行执行计划查看的时候type 就会显示 eq_ref。const 类型表示使用了主键或者唯一索引与常量值进行比较比如 select name from product where id1。总结本篇我们从了解什么是索引开始再到索引的具体分类按数据结构分物理存储分字段特性分字段个数分。然后理解了索引不是必须的究竟什么时候需要什么时候不需要索引进一步了解了有什么方式能够对索引进行优化。希望能够帮助大家对索引有更好的了解感谢感谢。