什么是索引?
索引能够提高对数据库表的查询速度,查询优化器通过索引能够快速定位数据,不需要对表全盘扫描。索引比原表小的多,通常存储在内存中,所以查询速度比较快。数据量比较小几百上千的没必要使用索引,数据量比较大上百万的这种,有无索引对查询效率的影响就很大了。
为什么要有索引?
索引能够减少io消耗,加快查询速度,提高系统性能,帮助MySQL快速的处理数据。
索引有哪些类型? 可以从哪些角度给索引进行分类?(全力给出所有你了解的答案)
按数据结构分类:
- 哈希索引(Hash Index): 使用哈希表来加速数据的查找,适合于等值查询。
- B树索引(B-tree Index): 一种平衡树结构,支持范围查询和排序,常用于数据库中。
- B+树索引(B+ Tree Index): 在B树的基础上优化,叶子节点形成一个链表,适合范围查询和排序。
- 全文索引(Full-text Index): 用于全文搜索的索引,支持关键词查询。
按索引方式分类:
- 唯一索引(Unique Index): 索引列的值必须唯一。
- 非唯一索引(Non-unique Index): 索引列的值可以重复。
按索引存储的数据结构分类:
- 聚簇索引(Clustered Index): 数据行的物理顺序与索引顺序一致,数据存储在索引中,常用于主键或唯一索引。
- 非聚簇索引(Non-clustered Index): 索引中的逻辑顺序与数据行的物理存储顺序不同,索引结构与数据分开存储。
按索引使用场景分类:
- 主键索引(Primary Key Index): 主键默认为唯一索引,用来保证数据完整性和快速查找。
- 外键索引(Foreign Key Index): 用于实现表与表之间的关联。
- 覆盖索引(Covering Index): 索引包含了所有需要查询的字段,避免了回表操作,提高查询效率。
- 复合索引(Composite Index): 索引包含多个字段,用于支持联合查询,提高查询效率。
mysql中的索引是如何存储的?数据结构长什么样子? 为什么要这样设计?有什么优点?
MySQL中常用的存储引擎是InnoDB和MyISAM。
InnoDB:索引在磁盘中以数据页的形式存储,一页通常16kb,采用B+树数据结构。索引和数据集成存储,叶子节点存储实际的数据,非叶子节点存储键值,称为聚集索引,其他的索引称为辅助索引,辅助索引的叶子节点存储主键值,不存储数据。
B+树查询路径短所以查询速度较快, 叶子节点采用双链表连接,适合 MySQL 中常见的基于范围的顺序查找,树的层级更矮从而减少了磁盘I/O操作。
MyISAM:索引也是以数据页的形式存储,大小通常1kb或8kb,也采用B+树数据结构,叶子节点中不存储数据,存储的是数据的物理地址,非叶子节点存储索引键和指向子节点的指针。MyISAM占用更少的内存和存储空间。
说一说B+树数据结构的特点,以及为什么要用B+树而不是其他结构存储索引?
B+树非叶子节点只存储键值和指向子节点的指针,不存储实际数据,相同大小的磁盘能容纳更多节点元素让树变得更“胖”,每个节点能够存储更多键值,减低了树的高度。
因为这种矮胖的设计,查询操作时能减少磁盘的读取次数,支持高效的范围查询和顺序扫描,查询性能也十分稳定,时间复杂度为O(log n)。
B树在非叶子节点中也存储数据,存储键值的空间变小会增加树的高度。
二叉树每个父节点的子节点只能有两个,树的高度会非常高导致增加io次数。
Hash不适合做范围查询,只适合等值查询。
索引是越多越好吗?
索引并不是越多越好,索引需要额外的存储空间来存放索引并且会占用内存,索引维护需要很大的开销,尤其是数据无序插入的情况,进行增删改操作时,数据库都要更新索引,会降低写操作性能。
所以在进行联表查询时不要关联太多表,尽量使用索引覆盖。减少索引数量,保留能够对查询性能有显著提升的索引。
InnoDB中的索引类型?
主键索引:每个表只能有一个主键索引,不能有空值。
唯一索引:确保字段中所有值是唯一的,不能有重复值,但可以有空值。
普通索引:非唯一索引,可以出现空值和重复值。
复合索引:包含多个字段的索引,可以是唯一索引或普通索引。
全文索引:能够加快大文本字段的查询速度。
聚簇索引和非聚簇索引的区别?
聚簇索引:索引和数据是存放在一起,数据按照索引键的顺序存储,找到了索引就找到了数据,每个表只能有一个聚簇索引。
非聚簇索引:索引和数据分开存放,索引顺序和数据的物理顺序无关,叶子节点存放索引键值和指向数据行的指针(主键),查找数据时通过非聚簇索引找到主键值,通过主键索引进行查询找到结果(回表)。当前字段值发生改变,只修改当前字段的非聚簇索引,除非对应的主键值也被更新。非聚簇索引可以有多个。
主键索引和普通索引的区别?
主键索引:值必须是唯一的,不能有空值,每个表只能有一个主键索引,一般主键索引就是聚簇索引。
普通索引:允许重复值和空值,一张表能有多个普通索引,普通索引就是非聚簇索引。
索引的数据结构,b树和b+树的区别,为什么mysql选择了b+树?
InnoDB存储引擎使用B+树作为索引数据结构。
区别:
-
- B树的所有节点都存储数据,B+树只在叶子节点存放数据,非叶子节点只存储键和指针。
- B树非叶子节点和叶子节点之间没有连接,B+树叶子节点之间通过链表连接形成双向链表,顺序查询和范围查询效率较高。
- B树可以在非叶子节点找到数据,可能会在不同层级的节点找到数据,进行的io次数较多,B+树遍历到叶子节点才能找到数据,由于非叶子节点较小能容纳更多节点,树通常较矮查询性能较高。
B+树叶子节点通过链表相连,进行范围查找和顺序查找时非常高效,能从一个叶子节点直接遍历到下一个叶子节点,能够避免回溯和重新查找的操作。
什么是回表,怎么减少回表的次数?
回表:通过索引找到主键值,通过主键索引进行查询找到结果。
减少回表方法:
-
- 使用覆盖索引,创建一个包含了查询所需要的所有字段,就不再需要回表。
- 尽量避免不必要的列,减少回表的需求。
索引不包括需要查询的字段,使用了索引覆盖但超过了最大索引长度(767字节),以上情况下会触发回表。
什么是索引覆盖?举出sql例子+索引例子?
一个查询完全能通过索引来获取所需要的数据,从而不需要回表查询实际的数据行。
CREATE INDEX idx_age ON users(age, name);
SELECT name, age FROM users WHERE age > 30;
索引失效场景有哪些,越多越好,并且说明为什么索引会失效,原理是什么?
对索引使用左或者左右模糊匹配( like %xx 或者 like %xx% )
失效原因:索引是按照索引值有序排列存储的,只能根据前缀进行比较,前缀模糊时存储引擎不知道从那个索引值开始比较,就会走全表扫描。
联合索引非最左匹配:
联合索引情况下,数据是按照索引第一列排序,第一列数据相同时才会按照第二列排序。
对索引使用函数或表达式
失效原因: 索引是按照字段的值来存储和排序的,而不是函数或表达式的计算结果 。MySQL5.7后新增函数索引,能对函数计算后的值建立索引。
索引字段的类型不匹配
失效原因:例如索引字段是字符串类型,查询条件种输入的参数是整型,会失效。
Where子句中的OR
失效原因:OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。只有一个条件列是索引列是没有意义的,只要有条件列不是索引列,就会进行全表扫描,所以需要两个条件都是索引列。
使用大范围查询
失效原因:使用了唯一性不好的字段查询时,查询条件覆盖了大部分数据或全部数据,优化器让认为全表扫描更高效,因为索引需要多次io操作访问大量的数据。
索引失效的原理:优化器查询执行计划后,根据查询条件、表结构、索引信息来评估最佳的查询策略。 如索引列不在查询条件中,或者使用了不支持的操作符,优化器可能会放弃使用索引走全表扫描,导致了索引失效。
怎么判断使用了部分联合索引?
使用 EXPLAIN
语句 通过分析 key_len
的值来判断。key_len
表示使用的索引字节数,会显示出使用的部分索引的长度,通过计算各个索引列的长度来判断使用了那些索引。
MySQL索引查询一定遵循最左前缀匹配吗,有没有特例?索引跳跃了解吗?
通常是需要遵循最左前缀匹配的原则,但是也存在一些特殊的情况。
索引覆盖:查询所需要的字段都包括在一个符合索引中,即使不符合最左前缀匹配也能使用索引。
索引合并:可以将多个单列的索引进行合并,这种情况也可以不完全遵守最左前缀匹配。
-- 当存在单列索引 name 和 city 时,也可以通过索引合并来优化查询
SELECT * FROM users WHERE name = 'John' OR city = 'New York';
索引跳跃是MySQL8.0版本支持的一种优化机制,在查询条件中,即使没有使用联合索引的第一个字段,仍然能够使用联合索引,像是跳过了第一个字段。但是会有限制,多表联查的时候无法触发、查询条件有分组时也无法触发,使用DISTINCT操作是无法触发。
锁具体锁的是什么? 数据 还是 索引?
行锁:行锁具体锁定的是索引,当执行一条SQL语句需要锁定行时,InnoDB先查询该行对应的索引,然后对索引进行加锁,由于索引指向具体的数据行,所以控制了该数据行的访问。如果查询没使用索引会退化成表锁,锁住整个表。
表锁:锁定的整个表。
间隙锁:锁定的不是直接的数据行,也不是索引本身,是索引记录之间的间隙。
设计索引的时候有哪些原则?
创建索引时选择频繁进行数据查询的字段
选择性高的字段(不同值较多),能够有效过滤字段
尽量使用索引覆盖避免回表
不要设计过多的索引
较长的文本字段可以只索引前缀部分
SQL执行计划分析的时候,你要关注哪些信息?
重点关注:type:查询时使用到的索引类型
key:查询优化器选择的索引
Extra:显示额外信息