前言大家好，我是田洛。金、三、银、四即将推出。我为索引准备了15个连续的问题。相信对大家阅读会有帮助。1、什么是指数？索引是一种可以提高数据库查询效率的数据结构。好比字典的目录，可以帮助你快速找到对应的记录。索引一般存储在磁盘上的文件中，占用物理空间。俗话说，水能载舟，亦能覆舟。合适的索引可以提高查询效率，过多的索引会影响数据库表的插入和更新功能。2、MySQL索引有哪几种类型？数据结构维度B+树索引：所有数据都存储在叶子节点中，复杂度为O(logn)，适合范围查询。哈希索引：适用于等值查询，检索效率高，一次性到位。全文索引：MyISAM和InnoDB都支持使用全文索引，一般在文本类型char、text、varchar上创建。R-Treeindex：用于为GIS数据类型创建SPATIAL索引物理存储维度聚簇索引：聚簇索引是以主键创建的索引，表中的数据存储在叶子节点中。（Innodb存储引擎）非聚集索引：非聚集索引是用非主键创建的索引，主键和索引列存储在叶子节点中。（Innodb存储引擎）逻辑维度主键索引：一种不允许空值的特殊唯一索引。普通索引：MySQL中的基本索引类型，允许空值和重复值。联合索引：由多个字段创建的索引在使用时遵循最左前缀原则。唯一索引：索引列中的值必须是唯一的，但允许空值。空间索引：MySQL5.7支持空间索引，在空间索引方面遵循OpenGIS几何数据模型规则。3、索引什么时候失效？查询条件包含or，可能会导致索引失效。如果字段类型是字符串，where必须用引号括起来，否则会像通配符一样索引失败，可能导致索引失败。对于联合索引，查询的条件列不是联合索引的第一列，索引失效。在索引列上使用mysql的内置函数，索引变得无效。对于索引列（如+、-、*、/）的操作，索引变得无效。在索引字段上使用(!=or<>,notin)时，可能会导致索引失败。在索引字段上使用isnull和isnotnull可能会导致索引失败。左连接查询或右连接查询关联的字段编码格式不同，可能会导致索引失败。mysql估计使用全表扫描比使用索引快，所以没有使用索引。4、哪些场景不适合做索引？数据量小的表不适合加索引。更新频繁的也不适合加索引。区分度低的字段不适合添加索引（比如性别）。后面不用的字段，比如where,groupby,orderby等，不需要创建索引已经有冗余索引（比如已经有a和b的联合索引，还有是不需要单独创建a的索引）5.为什么要用B+树，为什么不用二叉树？可以从几个维度来看这个问题，查询速度够不够快，效率是否稳定，存多少数据，查盘多少。为什么不是二叉树，为什么不是平衡二叉树，为什么不是B树，而是B+树？为什么不是一般的二叉树？如果将二叉树特化为链表，相当于全表扫描。与二叉搜索树相比，平衡二叉树的搜索效率更稳定，整体搜索速度更快。为什么不是平衡二叉树？我们知道在内存中查询效率要比在磁盘数据中快很多。如果使用树数据结构作为索引，那么我们每次查找数据都需要从磁盘中读取一个节点，也就是我们所说的磁盘块，但是平衡二叉树只存储一个键值和每个数据节点。如果是B树，可以存储更多的节点数据，树的高度也会降低，所以读磁盘的次数会减少，查询效率会更快。那么为什么不是B树而是B+树呢？B+树的非叶子节点不存储数据，只存储键值，而B树节点不仅存储键值，还存储数据。innodb中页面的默认大小是16KB。如果不存储数据，存储的key值越多，对应的树（节点的子节点树）的阶数越大，树越矮越胖。这样一来，我们需要为磁盘查找数据的IO次数就会再次减少，数据查询的效率也会更快。B+树索引的所有数据都存放在叶子节点中，数据按顺序排列，链表相连。那么B+树使得范围搜索、排序搜索、分组搜索和去重搜索变得异常简单。6.B+树索引树搜索过程假定如下表结构，并初始化这些数据CREATETABLE`employee`(`id`int(11)NOTNULL,`name`varchar(255)DEFAULTNULL,`age`int(11)DEFAULTNULL,`date`datetimeDEFAULTNULL,`sex`int(1)DEFAULTNULL,PRIMARYKEY(`id`),KEY`idx_age`(`age`)USINGBTREE)ENGINE=InnoDBDEFAULTCHARSET=utf8;insertintoemployeevalues(100,'Xiaolun',43,'2021-01-20','0');insertintoemployeevalues(200,'Junjie',48,'2021-01-21','0');insertintoemployeevalues(300,'Ziqi',36,'2020-01-21','1');insertintoemployeevalues(400,'Lihong',32,'2020-01-21','0');insertintoemployeevalues(500,'Yixun',37,'2020-01-21','1');insertintoemployeevalues(600,'Xiaojun',49,'2021-01-21','0');insertintoemployeevalues(700,'Xiaoyan',28,'2021-01-21','1');执行这个查询SQL，需要执行几次树搜索操作？可以画出对应的索引树结构图~select*fromTemployeewhereage=32;其实这个，可以先画idx_age普通索引的索引结构图，大致如下：然后画id主键索引，我们先画聚簇索引结构图如下：这个的执行流程SQL查询语句如下：查找idx_age索引树，将磁盘块1装入内存，由于32<43，查找左分支，在磁盘上寻址磁盘块2。将磁盘块2加载到内存中，由于32<36，搜索左分支，在磁盘上寻址磁盘块4。将磁盘块4加载到内存中，继续在内存中遍历，找到age=32的记录，得到id=400。得到id=400后，返回id主键索引树。搜索id主键索引树，将磁盘块1加载到内存中，因为300<400<500，所以选择中间的分支，在磁盘上寻址磁盘块3。虽然在diskblock3找到了id=400，但不是叶子节点，所以继续往下看。到磁盘寻址磁盘块8。将磁盘块8载入内存，遍历内存，找到id=400的记录，得到R4行的数据，ok，大功告成。7.什么是退货单？如何减少后台？当查询到的数据在索引树中找不到时，需要回到主键索引树中获取。这个过程称为回表。例如，在第6节中，使用了查询SQLselect*fromEmployeewhereage=32；需要查询所有列的数据，idx_age普通索引无法满足。得到主键id的值后，返回id主键索引查找获取，这个过程就是回表。8.什么是覆盖索引？如果我们查询SQLselect*，修改为selectid,age，其实是不需要返回表的。因为id和age的值都在idx_age索引树的叶子节点上，所以这里涉及到覆盖索引的知识点。覆盖索引是指只从索引中获取选中的数据列，而不返回表。换句话说，查询列必须被内置索引覆盖。9、谈谈索引的最左前缀原则索引的最左前缀原则可以是联合索引的最左N个字段。比如创建一个复合索引(a,b,c)，其实可以相当于建立三个索引(a),(a,b),(a,b,c)，大大提高了索引复用能力。当然，最左边的前缀也可以是字符串索引的最左边的M个字符。.比如你普通的索引树是姜子：这个SQL：select*fromemployeewherenamelike'small%'orderbyagedesc;也击中了指数。10、你了解索引下推吗？这个SQL的索引下推是什么：select*fromemployeewherenamelike'small%'andage=28andsex='0';其中name和age是联合索引(idx_name_age)。如果是Mysql5.6之前，在idx_name_age索引树中，找到所有第一个字符为“small”的人，得到他们的主键id，然后回表找数据行，然后比较年龄和性别等领域。如图：有的朋友可能会觉得奇怪，idx_name_age(name,age)不是联合索引吗？为什么选择包含“小”的词后不看年龄返回表格效率更高？所以，MySQL5.6引入了索引下推优化，可以在索引遍历过程中对索引包含的字段进行判断，直接过滤掉不符合条件的记录，减少回表次数。所以MySQL5.6以后，在选中包含“small”的词后，沿表过滤age=2811。如何为大表添加索引？表添加索引？我们要知道，在给表加索引的时候，表会被锁住。如果不小心操作，可能会发生生产事故。可以参考下面的方法：先新建一个与原表A数据结构相同的表B，在新表B中添加需要添加的新索引，将原表A中的数据导入到newtableBrename新表B为原表的表名A，将原表A替换为另一个表名；12、如何知道语句是否使用了索引查询？explain查看SQL执行计划，就知道索引是否被命中。当explain与SQL一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。一般来说，我们需要关注type、rows、filtered、extra、key。1.2.1typetype表示连接类型，查看索引执行情况的重要指标。以下表现从大到小依次为：system>const>eq_ref>ref>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALLsystem：该类型只需要数据库表中的一条数据，是常量类型。下一个不会出现。const：数据可以通过一个索引找到。一般用作主键或唯一索引作为条件。这种类型的扫描非常有效且非常快速。eq_ref：常用于主键或唯一索引扫描，一般指使用主键的关联查询ref：常用于非主键和唯一索引扫描。ref_or_null：这种连接类型和ref类似，不同的是MySQL会额外搜索包含NULL值的行index_merge：使用索引合并优化方式，查询使用两个以上的索引。unique_subquery：与eq_ref类似，条件使用in子查询index_subquery：不同于unique_subquery，用于非唯一索引，可以返回重复值。range：常用于范围查询，如：between...andorIn等操作index：全索引扫描ALL：全表扫描1.2.2rows该列表示MySQL估计需要读取的行来查找记录我们需要的号码。对于InnoDB表，此数字是一个估计值，不一定是精确值。1.2.3filtered该列为百分比值，即表中符合条件的记录数所占的百分比。简单来说，该字段表示存储引擎返回的数据经过过滤后，满足条件的记录数所占的比例。1.2.4extra该字段包含了MySQL如何解析查询的其他信息。它一般有这些值：Usingfilesort：表示按文件排序，一般在指定排序与索引排序不一致时出现。一般在orderby语句中看到Usingindex：表示是否使用覆盖索引。Usingtemporary：表示是否使用临时表。性能特别差，需要优化。一般见于groupby语句，或者union语句。Usingwhere：表示在什么地方使用条件过滤。使用索引条件：MySQL5.6后下推的新索引。数据过滤在存储引擎层进行，而不是在服务层进行，利用索引已有的数据减少返回表的数据。1.2.5key这一列表示实际使用的索引。一般可以和possible_keys一栏一起看。13、Hash索引和B+树有什么区别？你是如何选择设计索引的？B+树可以进行范围查询，而Hash索引不行。B+树支持联合索引的最左原则，而Hash索引不支持。B+树支持orderbysorting，而Hashindex不支持。哈希索引在等价查询上比B+树更高效。（但是，如果索引列中有很多重复值，Hash会发生冲突，效率会降低）。B+树在使用like进行模糊查询时，like后面的词（比如以%开头）可以起到优化作用，而Hash索引根本无法进行模糊查询。14、索引的优缺点是什么？优点：索引可以加快数据查询，减少查询时间唯一索引可以保证数据库表中每一行数据的唯一性缺点：创建和维护索引需要时间索引需要占用物理空间，除了数据表占用数据space，每个索引也占用一定的物理空间，当表中的数据进行增删改查时，索引也必须动态维护。15、聚簇索引和非聚簇索引的区别聚簇索引不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据存储在一起的索引。非聚集索引是索引结构和数据分开存储的索引。接下来，让我们谈谈不同的存储引擎。在MySQL的InnoDB存储引擎中，聚集索引和非聚集索引的最大区别在于叶子节点是否存储整行记录。聚集索引的叶子节点存放的是整行记录，而非聚集索引的叶子节点存放的是主键信息。所以一般的非聚集索引也需要回表查询。一张表中只能有一个聚簇索引（因为一般的聚簇索引都是主键索引），一张表中可以有多个非聚簇索引。一般来说，聚集索引查询比非聚集索引查询效率更高，因为不需要回表。在MyISM存储引擎中，它的主键索引和普通索引都是非聚集索引，因为数据和索引是分离的，叶子节点用一个地址指向真正的表数据。
