0前言本文不讲解索引的基础知识，主要介绍MySQL数据库B+树索引的相关原理。里面的一些知识参考了MySQL技术内幕这本书，也是对这些知识的总结。B-tree和B+树相关的知识可以参考我的这篇博客：面试官问你B-tree和B+树，就把这篇文章扔给他1索引管理索引有很多种：普通索引、唯一索引、主键索引、复合索引、全文索引，我们来看看如何创建和删除以下几种索引。1.1如何创建索引索引可以在很多情况下创建。直接创建索引CREATE[UNIQUE|FULLLTEXT]INDEXindex_nameONtable_name(column_name(length))[UNIQUE|FULLLTEXT]：表示可选索引类型，唯一索引或全文索引，不带词的普通索引。table_name：表名，表示要在哪个表中添加索引。column_name(length)：column_name是表的列名，length表示在该列的第一条length行记录上加一个索引。修改表结构的方法是添加索引ALTERTABLEtable_nameADD[UNIQUE|FULLLTEXT]INDEXindex_name(column(length))创建表时同时创建索引CREATETABLE`table`(`id`int(11)NOTNULLAUTO_INCREMENT,`title`char(255)CHARACTERNOTNULL,PRIMARYKEY(`id`),[UNIQUE|FULLLTEXT]INDEXindex_name(title(length)))1.2如何创建主键索引和复合索引以上是创建普通索引的方法，唯一索引和全文索引，但是主键索引和复合索引的创建方式有点不同，所以分开说。组合索引创建方法创建表时，同时创建索引CREATETABLE`table`(`id`int(11)NOTNULLAUTO_INCREMENT,`title`char(255)CHARACTERNOTNULL,PRIMARYKEY(`id`),INDEXindex_name(id,title))修改表结构方式增加索引ALTERTABLEtable_nameADDINDEXname_city_age(name,city,age);主键索引的创建方法主键索引是一种特殊的唯一索引，一张表只能有一个主键，不允许有空值。一般在建表的时候会同时创建主键索引。CREATETABLE`table`(`id`int(11)NOTNULLAUTO_INCREMENT,`title`char(255)CHARACTERNOTNULL,PRIMARYKEY(`id`))1.3删除索引删除索引可以使用ALTERTABLE或DROPINDEX语句来删除索引。与CREATEINDEX语句类似，DROPINDEX可以作为ALTERTABLE内部的语句处理，语法如下。(1)DROPINDEXindex_nameONtalbe_name(2)ALTERTABLEtable_nameDROPINDEXindex_name(3)ALTERTABLEtable_nameDROPPRIMARYKEY第三条语句只在删除PRIMARYKEY索引时使用，因为一张表可能只有一个PRIMARYKEY索引，因此不需要指定索引名称。1.4索引示例上面已经介绍了基础知识，接下来，我们通过一个具体的例子来体会一下。step1:创建表createtabletable_index(idint(11)notnullauto_increment,titlechar(255)notnull,primarykey(id));step2：添加索引首先，我们通过直接添加索引来添加普通索引。CREATEINDEXidx_aONtable_index(标题);接下来，我们在修改表结构时添加索引。ALTERTABLEtable_indexADDUNIQUEINDEXidx_b(title(100));最后，我们添加另一个复合索引。ALTERTABLEtable_indexADDINDEXidx_id_title(id,title);至此，前面所有的索引方式我们都用完了，相信这些操作你都不陌生。step3：使用SHOWINDEX命令查看索引信息。如果要查看表中的索引信息，可以使用命令SHOWINDEX。下面的例子，我们查看表table_index的索引信息。SHOWINDEXFROMtable_index\G;得到上面的信息，上面的信息是什么意思？我们一一介绍！字段解释Table索引所在的表Non_unique非唯一索引，如果为0则表示唯一，也就是说如果列索引不包含重复值则为0，否则为1Key_name的索引的名称，如果是主键，则为PRIMARYseq_in_index列在索引中的位置，从1开始，如果是复合索引，则按字段顺序排列列索引建立。Collat??ion列如何存储在索引中。可以是A也可以是NULL，B+树索引总是A，已排序，sub_part是否是部分列被索引，如果只索引前100行，则显示100，如果是整列，则显示NULLpacked关键字是压缩的，如果不是，则为NULLIndex_type索引的类型，对于InnoDB只支持B+树索引，所以显示为BTREEstep4：删除索引直接删除索引方法DROPINDEXidx_aONtable_index；修改表结构时删除索引ALTERTABLEtable_indexDROPINDEXidx_b；1.5Cardinality关键字分析上面介绍了多个关键字的含义，但是关键字Cardinality非常关键，优化器会根据这个值来判断是否使用这个索引。在B+树索引中，只有高选择性的字段才有意义。高选择性意味着该领域具有广泛的价值。比如name字段，名字多，选择性高。一般来说，判断是否使用索引，可以使用Cardinality关键字来判断。如果非常接近1，则表示有必要使用它。如果很小，那么就需要考虑是否使用索引。一个需要注意的问题是这个关键字没有及时更新。如果需要更新，则需要使用ANALYZETABLE，例如。analyzetabletable_index;因为目前没有数据，你会发现这个值一直是0，没有变化。InnoDB存储引擎Cardinality策略在InnoDB存储引擎中，这个关键字的更新发生在两个操作中：insert和update。但是并不是每次都更新，会增加负载。于是就有了更新这个关键字的策略：表中1/16的数据发生变化。InnoDB存储引擎计数器stat_modified_conter>2000000000。InnoDB存储引擎默认会对8个叶子节点进行采样，采样过程如下：B+树索引中叶子节点的个数，记为A随机获取B+树索引中的8个叶子节点。统计每页不同记录的条数，分别为p1-p8根据采样信息得到Cardinality的估计值：(p1+p2+p3+...+p8)*A/8因为是随机采样，所以每次基数值都是不同的，只有一种情况会相同，即表中的叶子节点小于或等于8个。这时候不管这8个是怎么随机采样的，都是相同的。1.6快速创建索引在MySQL5.5之前，添加或删除索引，每次都需要创建一个临时表，然后将数据导入到临时表中，再删除原表。如果一个大表进行这样的操作，会非常耗时，这是一个很大的缺陷。InnoDB存储引擎从1.0.x版本开始增加了FastIndexCreation（快速索引创建）索引创建方式。该方法的策略是：在每次创建索引的表上加一个S锁（共享锁）。创建表时，不需要重建表。删除辅助索引只需要更新内部视图，标记辅助索引空间即可，这样效率就大大提高了。1.7在线数据定义MySQL5.6支持的在线数据定义操作是：在允许创建辅助索引的同时，还允许进行插入、更新、删除等其他DM操作，大大提高了数据库的易用性。因此，我们可以使用新的语法来创建索引：ALTERTABLEtable_nameADD[UNIQUE|FULLLTEXT]INDEXindex_name(column(length))[ALGORITHM={DEFAULT|INPLACE|COPY}][LOCK={DEFAULT|NONE|SHARED|EXLUSIVE}]ALGORITHM指定创建或删除索引的算法COPY：创建临时表的方式INPLACE：不需要创建临时表给表加锁的情况NONE：不加锁SHARE：加S锁，可以读并发，写操作需要等待EXCLUSIVE：加X锁，不能并发读写DEFAULT：先判断是否可以使用NONE，如果不能，再判断是否可以使用SHARE，如果不能，再判断是否可以使用EXCLUSIVE模式。2B+树索引的使用2.1联合索引联合索引是指对表的多个列进行索引。这部分我们将通过几个例子来讲解联合索引的相关知识点。首先，我们创建一个表，并为这个表创建一个联合索引。createtablet_index(achar(2)notnulldefault'',bchar(2)notnulldefault'',cchar(2)notnulldefault'',dchar(2)notnulldefault'')enginemyisamcharsetutf8;创建联合索引altertablet_indexaddindexabcd(a,b,c,d);插入几个测试数据insertintot_indexvalues('a','b','c','d'),('a2','b2','c2','d2'),('a3','b3','c3','d3'),('a4','b4','c4','d4'),('a5','b5','c5','d5'),('a6','b6','c6','d6');至此，我们已经基本准备好所需的数据，可以就联合索引进行进一步的讨论。我们什么时候需要创建联合索引？创建索引的主要目的是为了提高查询效率，所以联合索引的目的也是类似的。联合索引的目的是为了在有多个查询条件时提高效率，就像上面创建的表一样，有多个字段。当我们需要使用多个字段进行查询时，就需要使用联合索引。联合索引什么时候发挥作用？有时，我们会用到联合索引，但并不知道它的原理。我们不知道联合索引什么时候起作用，什么时候失效？带着这个问题，我们来看看联合索引的最左匹配原则。最左匹配原则：这个原则的意思是创建复合索引，基于最左边的列，只要查询条件包含最左边的列，查询就会使用该索引。下面，我们通过几个例子来了解这个原则。EXPLAINSELECT*FROMt_indexWHEREa='a'\G;让我们看看这条语句的结果。首先我们看到使用了索引，因为查询条件有最左边的列a，那么使用了多少个索引呢？我们需要查看key_len字段。我们知道utf8编码的一个字符是3个字节，而我们使用的数据类型是char(2)，占用两个字节。索引是2*3等于6个字节，所以只有一个索引可以解决问题。EXPLAINSELECT*FROMt_indexWHEREb='b2'\G;我们从这条语句可以看出没有使用索引，因为possible_keys为空，而从查询的行数可以看出是6（我们一共有6条测试数据），说明进行了一次全盘扫描已经执行了，说明这种情况不符合最左匹配原则，所以不会使用索引查询。EXPLAINSELECT*FROMt_indexWHEREa='a2'ANDb='b2'ORDERBYd\G;这种情况有点不一样，我们用一个排序，可以看到使用了索引，key_len为12得到2个索引a,b的使用，另外，在Extra选项中，可以看到Using使用的是filesort，即文件排序。这里使用文件排序的原因是：上面的查询使用了a和b索引，但是当我们使用d字段进行排序时，(a,d)或(b,d)这两个索引没有排序，使用联合索引有一个好处，就是索引的下一个字段会自动排序，在这种情况下，c字段是排序的，而d不是。如果我们用c来排序，会得到不同的结果。EXPLAINSELECT*FROMt_indexWHEREa='a2'ANDb='b2'ORDERBYc\G;看到没有，我们用c排序的时候，因为用了a和b索引，所以c是自动排序的，所以不需要filesorted。说到这里，相信通过上面的几个例子，联合索引的相关知识已经很透彻清楚了。最后，我们来谈谈一些常见的问题。Q1：为什么不为表中的每一列创建索引？首先，创建和维护索引需要时间，而且这个时间随着数据量的增加而增加。其次，索引需要占用物理空间。除了数据表占用的数据空间外，每个索引还占用一定的物理空间。如果要建立聚簇索引，需要的空间就更大了。第三，对表中的数据进行增删改查时，还必须动态维护索引，降低了数据维护的速度。Q2：为什么要使用联合索引来减少开销。建立一个联合索引(col1,col2,col3)其实相当于建立三个索引(col1),(col1,col2),(col1,col2,col3)。每个额外的索引都会增加写操作和磁盘空间的开销。对于数据量大的表，使用联合索引会大大减少开销！覆盖指数。对于联合索引(col1,col2,col3)，如果有如下sql：selectcol1,col2,col3fromtestwherecol1=1andcol2=2。那么MySQL可以直接遍历索引获取数据，不需要回表，减少了很多随机io操作。减少io操作，尤其是随机io，其实是dba的主要优化策略。因此，在实际应用中，覆盖索引是提高性能的主要优化手段之一。高效的。索引列越多，通过索引过滤掉的数据就越少。有一张表有1000W条数据，有如下sql：selectfromtablewherecol1=1andcol2=2andcol3=3，假设每个条件都能过滤掉10%的数据，如果只有一个单值索引，然后通过索引可以过滤出1000w10%=100w条数据，然后回表从这100w条数据中找到满足col2=2和col3=3的数据，然后排序分页;如果是联合索引，通过索引%*10%=1w过滤掉1000w10%10，效率提升可想而知！覆盖索引覆盖索引是可以从辅助索引中查询到的记录，而不需要查询聚簇索引中的记录。使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，因此大小比Clustered索引小很多，因此可以大大减少IO操作。覆盖索引的另一个好处是它们针对统计问题进行了优化。让我们看下面的例子。explainselectcount(*)fromt_index\G;如果是myisam引擎，Extra列会输出Selecttablesoptimizedaway语句。myisam引擎已经保存了总记录数，直接返回结果，不需要覆盖索引优化。如果是InnoDB引擎，Extra列会输出一条Usingindex语句，说明InnoDB引擎优化器使用了覆盖索引操作。2.2索引提示MySQL数据库支持索引提示功能。索引提示功能是告诉优化器使用哪个索引。通常，索引提示功能（INDEXHINT）可能会在以下两种情况下使用：MySQL数据库的优化器选择错误使用了某个索引，导致SQL运行很慢。某条SQL语句可以选择的索引有很多。这时候优化器选择执行计划时间的代价可能比SQL语句本身还要大。这里我们按照上面的例子来说明，首先，我们先在上面的t_index表中添加几个索引；altertablet_indexaddindexa(a);altertablet_indexaddindexb(b);altertablet_indexaddindexc(c);然后，我们执行以下语句；EXPLAINSELECT*FROMt_indexWHEREa='a'ANDb='b'ANDc='c'\G;你会发现这个语句可以使用三个索引。这时候我们可以通过索引提示使用索引a，如下：EXPLAINSELECT*FROMt_indexUSEINDEX(a)WHEREa='a'ANDb='b'ANDc='c'\G;这样，索引a就显示出来了。如果这样优化器还是没有选择到你想要的索引，那我们还有另外一种方式FORCEINDEX。EXPLAINSELECT*FROMt_indexFORCEINDEX(a)WHEREa='a'ANDb='b'ANDc='c'\G;这个方法肯定会选择你想要的索引。2.3索引优化Multi-RangeRead优化MySQL5.6开始支持，这个优化的目的是减少对磁盘的随机访问，将随机访问转化为更多的顺序数据访问，这个优化适用于range、ref、eq_ref类型询问。Multi-RangeRead优化的好处：使数据访问更具顺序性。减少缓冲区中页面被替换的次数。键值查询操作的批处理。我们可以通过参数optimizer_switch中的flag来控制是否开启Multi-RangeRead优化。下面的方法会设置为一直开启：SET@@optimizer_switch='mrr=on,mrr_cost_based=off';IndexConditionPushdown(ICP)optimization从MySQL5.6开始也支持这种优化方式，不支持之前的方式，在进行索引查询时，我们先根据索引查找记录，然后根据where条件过滤记录.但是支持ICP优化后，MySQL数据库在提取索引的时候会判断是否可以进行where条件过滤，即将where过滤部分放在存储引擎层，这样可以大大减少上层对记录的请求SQL。ICP支持range、ref、eq_ref、ref_or_null类型的查询，目前支持MyISAM和InnoDB存储引擎。我们可以使用如下语句开启ICP：set@@optimizer_switch="index_condition_pushdown=on"或者关闭：set@@optimizer_switch="index_condition_pushdown=off"开启ICP后，在执行中可以看到Usingindexcondition提示计划额外。3索引的特点、优缺点及适用场景索引的特点可以加快数据库的检索速度，降低数据库插入、修改、删除等维护速度，只能在表，而不是视图。它可以直接或间接创建。创建索引的好处创建唯一索引可以保证数据库表中每一行数据的唯一性，大大加快了数据的检索速度，加快了数据库表之间的连接，特别是在实现数据的参照完整性方面。当使用分组和排序子句执行数据检索时，查询时间也可以显着减少。通过使用索引，可以在查询中使用优化隐藏器来提高系统性能。索引的缺点是第一。创建和维护索引需要时间。随着数据量的增加而增加。其次，索引需要占用物理空间。除了数据表占用的数据空间外，每个索引还占用一定的物理空间。如果要建立聚簇索引，需要的空间就更大了。第三，对表中的数据进行增删改查时，还必须动态维护索引，降低了数据维护的速度。索引的适用场景Matchfullvalue为索引中的所有列指定特定的值，即匹配索引中所有列的值相等的条件。Rangequeriesformatchingvalues启用对索引值的范围查找。匹配最左前缀只使用索引中的最左列进行查询，例如col1+col2+col3字段上的联合索引可以用于包含col1，（col1+col2），（col1+col2+col3）的等效查询)但是，它不能被col2,(col2,col3)的等价查询使用。最左匹配原则可以看作是MySQL中B-Tree索引使用的第一原则。只查询索引当查询列都在索引字段中时，查询效率更高，所以应尽量避免使用select*，只查询需要哪些字段。匹配列前缀只使用索引中的第一列，并且只包括索引第一列的第一部分进行查找。可以实现索引匹配部分是精确匹配，另一部分是范围匹配。如果列名是索引，则usingcolumn_nameisnull将使用索引。比如下面会用到索引：explainselect*fromt_indexwhereaisnull\G经常出现在关键字orderby,groupby和distinct后面的字段在union等set的结果集字段中经常作为表连接的字段操作。考虑使用索引覆盖，数据很少更新。如果用户经常按值查询你的几个字段，你可以考虑在这里添加。在多个字段上创建索引，以便表扫描成为索引扫描。索引失败。Like以%开头的查询不能使用B-Tree索引。执行计划中key的值为null，表示没有使用索引数据类型。转换期间不会使用隐式索引。例如，其中'age'+10=30对索引列执行函数操作。原因同上。正则表达式不会使用索引字符串，数据比较不会使用索引复合索引。如果查询条件不包括索引列的最左部分，即不满足最左原则，则不会使用复合索引。如果MySQL估计索引比全表扫描慢，则不用or分隔索引的条件。If如果条件中的列之前或有索引，但后面的列中没有索引，则不会使用涉及的索引。使用否定查询（not,notin,notlike,<>,!=,!>,!<）不会使用索引
