怎么建设个网站,下载百度极速版,深圳东门大厦,培训课程设计文章目录索引常见面试题什么是索引索引的分类什么时候需要 / 不需要创建索引#xff1f;有什么优化索引的方法#xff1f;从数据页的角度看B 树InnoDB是如何存储数据的#xff1f;B 树是如何进行查询的#xff1f;为什么MySQL采用B 树作为索引#xff1f;怎样的索引的数…
文章目录索引常见面试题什么是索引索引的分类什么时候需要 / 不需要创建索引有什么优化索引的方法从数据页的角度看B 树InnoDB是如何存储数据的B 树是如何进行查询的为什么MySQL采用B 树作为索引怎样的索引的数据结构是好的MySQL中的B 树MySQL单表不要超过2000W行靠谱吗索引失效有哪些MySQL使用like “%x”索引一定会失效吗count*和count1有什么区别哪个性能好索引常见面试题
什么是索引
索引就是帮助存储引擎快速获取数据的一种数据结构形象的说就是索引是数据的目录。存储引擎说白了就是如何为存储的数据建立索引、如何更新、查询数据等技术的实现方法。索引和数据就是位于存储引擎。
索引的分类
按「数据结构」分类Btree索引、Hash索引、Full-text索引。 InnoDB 存储引擎一定会为表创建一个聚簇索引创建的聚簇索引或者二级索引默认使用的都是 BTree 这种数据结构。BTree 存储千万级数据只需要 3-4 层高度就可以满足从千万级的表查询目标数据最多需要 3-4 次磁盘 I/O。回表就是先检索二级索引找到叶子节点最底层的节点获取主键值然后通过聚簇索引中的BTree 树查询到对应的叶子节点也就是要查两个 BTree 才能查到数据。索引覆盖就是当查询的数据是主键值时因为只在二级索引就能查询到不用再去聚簇索引查就表示发生了索引覆盖也就是只需要查一个 BTree就能找到数据。 按「存储类型」分类聚簇索引主键索引、二级索引辅助索引。术语“聚簇”表示数据行和相邻的键值聚簇地存储在一起。这也形象地说明了聚簇索引叶子节点的特点保存表的完整数据。 聚簇索引的 BTree 的叶子节点存放的是实际数据是表的所有完整数据。二级索引的 BTree 的叶子节点存放的是主键值而不是实际数据。 按「字段特性」分类主键索引、唯一索引、普通索引、前缀索引。 主键索引也叫聚簇索引就是建立在主键字段上的索引叶子节点按照主键大小排序在创建表的时候一起创建一张表最多只有一个主键索引索引列的值不允许有空值。唯一索引建立在 UNIQUE 字段上的索引表示索引列的值必须唯一允许有空值。但一张表可以有多个唯一索引。普通索引就是建立在普通字段上的索引既不要求字段为主键也不要求字段为 UNIQUE。前缀索引是指对字符类型字段的前几个字符建立的索引。使用前缀索引的目的是为了减少索引占用的存储空间提升查询效率。 按「字段个数」分类单列索引、联合索引。 通过将多个字段组合成一个索引该索引就被称为联合索引。联合索引按照最左匹配原则如果创建了一个 (a, b, c) 联合索引查询条件存在a就可以匹配上联合索引比如where a1 联合索引的最左匹配原则会一直向右匹配直到遇到范围查询、就会停止匹配。也就是范围查询的字段可以用到联合索引但是在范围查询字段之后的字段无法用到联合索引。注意对于 、、BETWEEN、like 前缀匹配的范围查询并不会停止匹配。 索引下推优化 index condition pushdown) 可以在联合索引遍历过程中对联合索引中包含的字段先做判断直接过滤掉不满足条件的记录从而减少回表次数。实际开发工作中在建立联合索引时建议把区分度大的字段排在前面区分度越大搜索的记录越少。 UUID 这类字段就比较适合做索引或排在联合索引列的靠前的位置。 比如性别的区分度就很小字段的值分布均匀那么无论搜索哪个值都可能得到一半的数据。
什么时候需要 / 不需要创建索引 索引的缺点 需要占用物理空间数量越多占用空间越大创建、维护索引要耗费时间这种时间随着数据量的增加而增大会降低表的增删改的效率B 树为了维护索引有序性增删改时需要进行动态维护。 什么时候适用索引 字段有唯一性限制比如商品编码。经常用于 WHERE 查询条件的字段这样能够提高整个表的查询速度。如果查询条件不是一个字段可以使用CREATE INDEX等语句建立联合索引。经常用于 GROUP BY 和 ORDER BY 的字段这样在查询的时候就不需要再去做一次排序了因为我们都已经知道了建立索引之后在 BTree 中的记录都是排序好的。 什么时候不需要创建索引 WHERE 条件GROUP BYORDER BY 里用不到的字段索引的价值是快速定位如果起不到定位作用的字段不需要创建索引。字段中存在大量重复数据不需要创建索引MySQL 有一个查询优化器查询优化器发现某个值出现在表的数据行中的百分比很高的时候它一般会忽略索引进行全表扫描。表数据太少的时候不需要创建索引。经常更新的字段不用创建索引因为索引字段频繁修改由于要维护 BTree的有序性那么就需要频繁的重建索引影响数据库性能。
有什么优化索引的方法
前缀索引优化使用某个字段中字符串的前几个字符建立索引。可以减小索引字段大小节省空间。可以增加一个索引页存储前缀索引值从而提高索引查询速度。 但前缀索引有一定的局限性 order by 就无法使用前缀索引因为前缀字符无法排序。无法把前缀索引用作覆盖索引一般只有查询主键值时会用到覆盖索引。 覆盖索引优化SQL 中查询的所有字段都能从二级索引中查询得到记录而不需要通过聚簇索引查询整行记录的所有信息可以避免回表的操作。主键索引最好是自增的如果我们使用主键自增那么每次插入的新数据就会按顺序添加到当前索引节点的位置不需要移动已有的数据当页面写满就会自动开辟一个新页面。这种插入数据的方法效率非常高。 如果我们使用非自增主键可能产生页分裂。页分裂还有可能会造成大量的内存碎片导致索引结构不紧凑从而影响查询效率。 索引列最好设置为NOT NULL约束 第一原因索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂更加难以优化因为可为 NULL 的列会使索引、索引统计、值的比较都更复杂。比如进行索引统计时count 会省略值为NULL 的行。第二个原因NULL 值是一个没意义的值但是它会占用物理空间。
从数据页的角度看B 树
InnoDB是如何存储数据的
InnoDB的数据是按照数据页为单位来读写的默认的大小是16KB。数据页由七个部分组成文件头File Header、页头Page Header、用户空间UserRecords 、最大、最小记录Infimum Supermum、空闲空间Free Space、页目录PageDirectory、文件尾File Trailer。数据页中的文件头有两个指针分别指向上一个和下一个数据页连接起来的数据页相当于一个双向链表。实现逻辑上的连续存储。数据页中的记录按照主键的顺序组成单向链表。页目录起到数据的索引作用。页目录由多个槽组成**槽相当于分组数据的索引。**我们通过槽查找记录时可以使用二分法快速定位要查询的记录在哪个槽哪个记录分组定位到槽后再遍历槽内的所有记录找到对应的记录槽对应的值都是这个组的主键最大的记录。在页的 7 个组成部分中我们自己存储的记录会按照我们指定的行格式存储到 用户空间User Records 部分。一开始生成页的时候并没有这个部分每当我们插入一条记录都会从 Free Space 部分申请一个记录大小的空间划分到User Records部分。当 Free Space 部分的空间全部被 User Records 部分替代掉之后也就意味着这个页使用完了如果还有新的记录插入的话就需要去申请新的页了。
B 树是如何进行查询的 B 树的每个节点都是一个数据页。B 树只有叶子节点才会存放数据非叶子节点仅用来存放目录项作为索引。所有节点按照索引键大小排序构成双向链表便于范围查找。 定位记录所在哪一个页时B 树通过二分法快速定位到包含该记录的页。定位到该页后又会在该页内进行二分法快速定位记录所在的分组槽号最后在分组内进行遍历查找。 B Tree索引又可以分成聚簇索引和二级索引非聚簇索引它们区别就在于叶子节点存放的是什么数据 聚簇索引的叶子节点存放的是实际数据所有完整的用户记录都存放在聚簇索引的叶子节点。 因为表的数据都是存放在聚簇索引的叶子节点里所以 InnoDB 存储引擎一定会为表创建一个聚簇索引且由于数据在物理上只会保存一份所以聚簇索引只能有一个。InnoDB 在创建聚簇索引时会根据不同的场景选择不同的列作为索引 如果有主键默认会使用主键作为聚簇索引的索引键如果没有主键就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键在上面两个都没有的情况下InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键 由于一张表只能有一个聚簇索引为了实现非主键字段的快速搜索就引出了二级索引非聚簇索引/辅助索引它也是利用了 B 树的数据结构但是二级索引的叶子节点存放的是主键值不是实际数据。
为什么MySQL采用B 树作为索引
MySQL 默认的存储引擎 InnoDB 采用的是 B 树作为索引的数据结构原因有如下几点
B 树的非叶子节点仅存放索引因此数据量相同的情况下相比既存索引又存记录的 B 树B树的非叶子节点可以存放更多的索引因此 B 树可以比 B 树更「矮胖」查询底层节点的磁盘 I/O次数会更少。B 树有大量的冗余节点所有非叶子节点都是冗余索引这些冗余索引让 B 树在插入、删除的效率都更高比如删除根节点的时候不会像 B 树那样会发生复杂的树的变化B 树叶子节点之间用双向链表连接既能向右遍历也能向左遍历有利于范围查询而 B 树要实现范围查询因此只能通过树的遍历来完成范围查询这会涉及多个节点的磁盘 I/O 操作范围查询效率不如 B 树。
怎样的索引的数据结构是好的
MySQL的数据是持久化的保存在磁盘上。磁盘读写的最小单位是扇区扇区只有512B大小操作系统会读写多个扇区最小的读取单位是块。Linux中块的大小为4KB也就是8个扇区。由于数据库的索引是保存在磁盘上的所以查询数据时要先读取索引到内存通过索引找到磁盘中的某行数据然后读入到内存I/O操作次数越多所消耗的时间也越大。所以设计MySQL索引的数据结构时要尽可能减少磁盘I/O次数并且能够高效地查找某一个记录也要能高效的范围查找。为什么不用二分查找树 二叉查找树的特点是一个节点的左子树的所有节点都小于这个节点右子树的所有节点都大于这个节点二叉查找树的搜索速度块解决了插入新节点的问题但是当每次插入的元素都是二叉查找树中最大的元素二叉查找树就会退化成了一条链表查找数据的时间复杂度变成了 O(n)。随着元素插入越多树的高度越高磁盘IO操作也就越多查询性能严重下降。 为什么不用自平衡二叉树AVL树 自平衡二叉树在二叉查找树的基础上增加了一些条件约束每个节点的左子树和右子树的高度差不能超过 1。但是随着插入的元素变多会导致树的高度变高磁盘IO操作次数就会变多影响整体数据查询效率。 为什么不用B树 B树解决了树的高度问题但是B树的每个节点都包含数据索引记录而用户记录的数据大小有可能远远超过索引数据就要花费更多的IO来读取到有用的索引数据。 B 树对B树进行了升级与B树的区别主要是以下几点 叶子节点最底部的节点才会存放实际数据索引记录非叶子节点只会存放索引所有索引都会在叶子节点出现叶子节点之间构成一个有序链表对范围查找非常有帮助非叶子节点的索引也会同时存在在子节点中并且是在子节点中所有索引的最大或最小。非叶子节点中有多少个子节点就有多少个索引
MySQL中的B 树
Innodb 使用的 B 树有一些特别的点比如 B 树的叶子节点之间是用「双向链表」进行连接这样的好处是既能向右遍历也能向左遍历。B 树节点的内容是数据页数据页里存放了用户的记录以及各种信息每个数据页默认大小是 16 KB。 Innodb 根据索引类型不同还分为聚集和二级索引。他们的共同点是都使用B 树这种数据结构。他们区别在于聚簇索引的叶子节点存放的是实际数据所有完整的用户记录都存放在聚集索引的叶子节点而二级索引的叶子节点存放的是主键值。 补充 B树 一个m阶的B树具有如下特征 有k个子树的中间节点包含有k个元素B树中是k-1个元素每个元素不保存数据只用来索引所有数据都保存在叶子节点。所有的叶子结点中包含了全部元素的信息及指向含这些元素记录的指针且叶子结点本身依关键字的大小自小而大顺序链接。所有的中间节点元素都同时存在于子节点在子节点元素中是最大或最小元素。 B树 一个 M 阶的 B 树M2有以下的特性 根节点的儿子数的范围是 [2,M]。每个中间节点包含 k-1 个关键字和 k 个孩子孩子的数量 关键字的数量 1k 的取值范围为[ceil(M/2), M]。叶子节点包括 k-1 个关键字叶子节点没有孩子k 的取值范围为 [ceil(M/2), M]。假设中间节点节点的关键字为Key[1], Key[2], …, Key[k-1]且关键字按照升序排序即 Key[i]Key[i1]。此时 k-1 个关键字相当于划分了 k 个范围也就是对应着 k 个指针即为P[1], P[2], …,P[k]其中 P[1] 指向关键字小于 Key[1] 的子树P[i] 指向关键字属于 (Key[i-1], Key[i]) 的子树P[k]指向关键字大于 Key[k-1] 的子树。 MySQL单表不要超过2000W行靠谱吗
InnoDB存储引擎的表数据时存放在一个.idb(innodb data)的文件中也叫做表空间。MySQL 的表数据是以页的形式存放的页在磁盘中不一定是连续的。页的空间是 16K, 但并不是所有的空间都是用来存放数据的会有一些固定的信息如页头页尾页码校验码等等。在 B 树中叶子节点和非叶子节点的数据结构是一样的区别在于叶子节点存放的是实际的行数据而非叶子节点存放的是主键和页号。索引结构不会影响单表最大行数2000W 也只是推荐值超过了这个值可能会导致 B 树层级更高影响查询性能。
索引失效有哪些
当我们使用左或者左右模糊匹配的时候也就是 like %xx 或者 like %xx% 这两种方式都会造成索引失效。 因为索引 B 树是按照「索引值」有序排列存储的只能根据前缀进行比较。 对索引列进行表达式计算、使用函数这些情况下都会造成索引失效。 因为索引保存的是索引字段的原始值而不是经过计算后的值。 索引列发生隐式类型转换。MySQL 在遇到字符串和数字比较的时候会自动把字符串转为数字然后再进行比较。如果字符串是索引列而输入的参数是数字的话那么索引列会发生隐式类型转换。 由于隐式类型转换是通过 CAST 函数实现的等同于对索引列使用了函数所以就会导致索引失效。 联合索引没有遵循最左匹配原则也就是按照最左边的索引优先的方式进行索引的匹配就会导致索引失效。在 WHERE 子句中如果在 OR 前的条件列是索引列而在 OR 后的条件列不是索引列那么索引会失效。 因为 OR 的含义就是两个只要满足一个即可只要有条件列不是索引列就会进行全表扫描。
MySQL使用like “%x”索引一定会失效吗 使用左模糊匹配like “%xx”并不一定会走全表扫描索引不一定失效如果数据库表中的字段只有主键二级索引那么即使使用了左模糊匹配也不会走全表扫描typeall而是走全扫描二级索引树(typeindex)。 联合索引要遵循最左匹配才能走索引但是如果数据库表中的字段都是索引的话即使查询过程中没有遵循最左匹配原则也是走全扫描二级索引树(typeindex)。
count*和count1有什么区别哪个性能好 性能count(*) count(1) count(主键字段) count(字段) count()是什么 count()是一个聚合函数函数的参数不仅可以是字段名也可以是其他任意的表达式作用是统计符合查询条件的记录中函数指定的参数不为 NULL 的记录有多少个。比如count(name)统计name不为NULL的字段有多少。count(1)统计1不为NULL的字段有多少。1永远不可能是NULL所以其实是在统计一共有多少条记录。 count(主键字段)执行过程是怎样的 如果表中只有主键索引没有二级索引InnoDB在遍历时就会遍历聚簇索引将读取到的记录返回给server层server层维护了一个count的变量然后读取记录中的主键值如果为NULL就将count变量 1。如果表中有二级索引InnoDB就会遍历二级索引。 count(1)执行过程是怎样的 如果表中只有主键索引没有二级索引InnoDB遍历时会遍历聚簇索引将读取到的记录返回给server层但是不会读取记录中的任何字段的值。因为 count 函数的参数是 1不是字段所以不需要读取记录中的字段值。如果表中有二级索引InnoDB就会遍历二级索引。 因为 count 函数的参数是 1不是字段所以不需要读取记录中的字段值。 count(*)执行过程是怎样的 count(*) 其实等于 count(0)也就是说当你使用 count(*) 时MySQL 会将 * 参数转化为参数 0 来处理。 count(字段)执行过程是怎样的 会采用全表扫描的方式来计数所以它的执行效率是比较差的。 count(1)、 count(*)、 count(主键字段)在执行的时候如果表里存在二级索引优化器就会选择二级索引进行扫描。所以如果要执行 count(1)、 count(*)、 count(主键字段) 时尽量在数据表上建立二级索引这样优化器会自动采用 key_len 最小的二级索引进行扫描相比于扫描主键索引效率会高一些。 为什么要通过遍历的方式计数 InnoDB 存储引擎是支持事务的同一个时刻的多个查询由于多版本并发控制MVCC的原因InnoDB 表“应该返回多少行”也是不确定的所以无法像 MyISAM一样只维护一个 row_count 变量。 如何优化count(*) 如果业务对于统计个数不需要很精确可以使用explain 命令来进行估算。使用额外表保存计数值将这个计数值保存到单独的一张计数表中。
