大家好，我是程序员，稀饭。给大家分享了*MySQLInnoDB索引模型。MySQLInnoDB删除数据后表数据文件大小没有变化的原因是什么？如何计算索引的长度？如何查看SQL的执行情况？计划以上文章都是部分理论知识。从今天开始，我们就开始MySQL索引的实用内容，详细介绍MySQL索引的使用方法。首先介绍一下索引的相关语法：索引语法--createindexCREATEINDEXindexNameONtable_name(column_name);ALTERtableNameADDINDEXindexName(columnName);--删除索引DROPINDEX[indexName]ONmytable;语法还是很简单的，就不多说了，按照相关语法规定即可。当然你也可以使用一些相关的MySQL客户端管理工具来创建，比如Navicat。下面介绍一些具体的使用语法：index以今天的实战内容为例：CREATETABLE`tb_item`(`id`bigintNOTNULLCOMMENT'bookid,andalsobooknumber',`title`varchar(100)NOTNULLCOMMENT'书名',`sell_point`varchar(500)DEFAULTNULLCOMMENT'书卖点',`price`bigintNOTNULLCOMMENT'书价，单位为：点',`num`intNOTNULLCOMMENT'库存数量',`barcode`varchar(30)DEFAULTNULLCOMMENT'图书条码',`image`varchar(500)DEFAULTNULLCOMMENT'图书图片',`cid`bigintNOTNULLCOMMENT'类别，叶子类别',`status`tinyintNOTNULLDEFAULT'1'COMMENT'图书状态，1-正常，2-下架，3-已删除',`created`datetimeNOTNULLCOMMENT'创建时间',`updated`datetimeNOTNULLCOMMENT'更新时间',`upload_id`bigintDEFAULTNULL,PRIMARYKEY(`id`),KEY`cid`(`cid`),KEY`status`(`status`),KEY`updated`(`updated`),KEY`tb_item_title_price_num`(`title`,`price`,`num`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb3COMMENT='booktable';InnoDB索引采用B+树数据结构，所以我们在使用中需要了解这个，具体可以查看我之前的文章：MySQLInnoDB索引模型。正是因为使用了这种结构，我们在使用中需要遵循一些原则，这样索引才不会失效。最左前缀规则如果是构建联合索引，那么我们在使用查询条件的时候，需要从索引最左边的列开始，不要跳过索引中的列；如果跳过某一列，将导致索引部分无效；比如你创建的联合索引字段是(a,b,c)，那么你的查询条件必须是wherea=andb=andc=的格式（abc或cba的具体顺序不会影响索引，MySQL优化器会自动优化这个顺序）；当然，如果直接去掉前缀，整个索引都会失效，而不是部分索引。例如，在上表中，我们使用查询语句explainselect*fromtb_itemwhereprice=45andnum=23232;我们创建的索引字段是title、price、num，但是我们的查询条件直接跳过了title字段，可以使用explain查看这条SQL的执行计划，key的值为Null，说明这条SQL确实不使用索引，而是扫描整个表。那么我们最合理的使用方式就是使用最左前缀匹配，将查询条件改为：explainselect*fromtb_itemwheretitle='compilationprinciple'andprice=45andnum=23232;再看执行计划，我们看到key变成了tb_item_title_price_num，索引长度为314，证明使用了联合索引tb_item_title_price_num的三个完整字段（索引长度的计算方法可以参考这个文章）。索引长度的计算公式：由于联合索引特征的结构，我们需要确认命中的索引tb_item_title_price_num是命中title列、price列还是num列。要想会分析，就需要掌握索引长度的计算方法。1、对于索引长度公式中的所有索引字段，如果不设置notnull，则需要增加一个字节。对于定长字段，int占4个字节，date占3个字节，char(n)占n个字符。对于变量字段varchar(n)，有n个字符+两个字节。不同的字符集，一个字符占用的字节数不同。对于latin1编码，一个字符占一个字节，对于gbk编码，一个字符占两个字节，对于utf8编码，一个字符占三个字节，对于utf8mb4编码，一个字符占四个字节indexlengthchar(),varchar()公式用于计算索引长度：CharacterSet:utf8mb4=4,utf8=3,gbk=2,latin1=1)*columnlength+1(nullisallowed)+2(variablelengthcolumn)可以从上面得到：在上面的tb_item表，使用了utf8编码，所以title字段的索引长度为3*100+0+2=302，price字段的索引长度为8num，tb_item_title_price_num字段的索引长度为4tb_item_title_price_num，并且索引总长度为302+8+4=314个连接接下来我们修改查询条件来解释select*fromtb_itemwheretitle='编译原理'andnum=23232;此时Keylen变为302，说明只使用了title索引，因为查询条件跳过了price字段，导致某些Index无效。同时Extra是Usingindexcondition，表示使用了索引，但是需要回表数据。覆盖索引在使用索引的过程中，尤其是在使用联合索引的过程中，我们如何合理的建立索引，如果加上合理的查询条件，就可以使用覆盖索引，减少返回的次数表，即减少IO的次数可以成倍提高查询效率。下面我们来演示覆盖索引的使用，例如使用如下查询语句：explainselectid,titlefromtb_itemwheretitle='compilationprinciple'andnum=12000;这时我们可以看到Extra的值是：Usingwhere；usingindex，表示本次查询使用了索引，由于要查询的列已经在索引中直接获取，不需要回表获取数据，直接查找即可索引中的必填字段，这也是一般的要求，不允许select*查询，因为这种情况下需要获取所有字段，不能使用覆盖索引来提高效率。关于执行计划中Extra字段的说明，可以参考我之前的文章。额外字段说明：usingindex：使用覆盖索引时，会出现usingwhere：使用索引查找时，需要回表查询需要的数据usingindex条件：查找使用索引,但需要回表使用索引查询数据；usingwhere：查找使用了索引，但是需要的数据都可以在索引列中找到，所以不需要回表。同时阿里的开发规范中对索引规范的建议也包含了覆盖索引的说明：范围查询在实际开发中，范围查询也是我们需要经常用到的东西，比如统计最近3天的用户数和过去7天的金额等等。但是这时候有个问题需要注意，就是在使用范围查询的时候，范围查询右边的列索引会失效。比如下图中的几个查询条件，我们可以看到写法差不多，但是最终索引字段的长度完全不同。其中第一个selectidfromtb_itemwheretitle='compilationprinciple'andprice=56andnum=10000就是我们常用的等值查询，在上一步最左边的前缀中已经讲过了，必须全使用了索引，执行计划也验证了我们的结论。第二条查询语句selectidfromtb_itemwheretitle='compilationprinciple'andprice>56andnum=10000，我们使用范围查询，此时可以看到key_len变成了310，说明有些索引失效了，也是范围查询右边的列，num列的索引无效。第三条查询语句selectidfromtb_itemwheretitle='compilationprinciple'andprice>=56andnum=10000可以很好的避免这种索引失败。我们可以使用大于或等于或小于或等于来代替大于或小于，在这种情况下可以充分利用索引。索引列操作我遇到过很多会用SQL做操作的开发者。这在你的数据量不大的时候确实可以给你提供方便，但是一旦你的数据量变大了，如果你在索引列上做计算，会直接导致索引失效，进而造成全表扫描。因为MySQL在索引的时候索引的是你的字段值本身而不是你计算出来的值，大家可以回顾一下B+树的索引模型，TODO，所以我们在实际使用中需要完全避免对索引的列进行计算，因为有没有理由我们应该这样做。例如这条查询语句解释select*fromtb_itemwheresubstring(title,4,4)='compositionprinciple';查看它的执行计划：可以看到根本没有使用索引，直接开始全表扫描。试想一下，如果你的表有几千万条数据，如果你全表扫描，你可能半夜都不敢睡觉。不带引号的字符串MySQL会自动转换索引查询中的字段类型。如果我们在查询数字格式的字符串字段时不使用单引号，那么MySQL查询优化器的类型将被自动触发。转变。比如你有一张存储手机号码的表，字段叫phone，然后查询语句selectidfromtb_userwherephone=1888888888，即使在phone字段上再建索引，也会不使用索引。因为这条语句会在查询优化器的处理下执行为selectidfromtb_userwherecast(phoneassignedint)=1888888888。这时，因为对索引列进行了函数操作，导致索引失效。模糊查询关于模糊查询，这相当于最左前缀原则。如果在字段头部进行模糊搜索，首先，如果不遵循最左前缀匹配原则，索引自然会失败。反之，如果使用tail字段进行模糊匹配，索引仍然有效。因此，如果我们真的需要模糊搜索功能，最好的办法是使用搜索引擎，而不是直接像MySQL中的查询那样。or连接条件用or分隔。如果or之前的条件列有索引，而后面的列没有索引，则索引无效。无论这两个字段中的哪个索引都是无效的。例如，我们的表tb_item中的条形码列没有索引。使用如下查询语句explainselectid,titlefromtb_itemwheretitle='编译原理'orbarcode='202457815';从执行计划可以看出所有的索引都是无效的。反之，如果or两边的字段都有索引，索引仍然可以生效。说明selectid,titlefromtb_itemwheretitle='编译原理'orprice=128;数据分布的影响其实有上面说的几个原则，但是在具体使用的时候，还是要根据实际情况来分析。首先，如何选择索引是MySQL自己的事情。如果MySQL评估使用索引会比全表慢，那么就不要使用索引。那么，它在什么情况下评价使用索引不如直接全表扫描呢？一种常见的情况是数据分析分布在表中。如果这个字段的值区分度不够明显，那么MySQL很有可能进行全表扫描。例如，使用这个查询语句explainselect*fromtb_itemwheretitle='AdvancedMathematics';执行计划显示没有索引。按理说我们建立了一个联合索引tb_item_title_price_num(title,price,num)，而且也是遵循最左前缀匹配原则，可以去索引，但是当前的执行计划说没有使用索引。接下来我们将查询条件修改为：explainselect*fromtb_itemwheretitle='编译原理';可以看到同一条查询语句，但是取值不同，会导致一个索引生效，一个索引失效。原因是，是因为表中标题为'高等数学'的数据占了太多，MySQL判断不如直接扫描全表而不是使用索引，所以索引无效。可以看到表中一共有841行数据，其中803行是高级数据。我在前缀索引之前的一篇文章中提到，InnoDB引擎对索引字段TODO的长度是有限制的，所以当我们遇到过长的字段类型时，我们可以截取一部分来建立索引，从而节省索引空间，提高查询效率。关于前缀索引，我们需要明确以下内容：创建索引，指定索引长度语法：createindexidx_xxxontable_name(column(n))前缀长度的选择：可以根据选择性来决定索引，选择性越高，查询效率越高，唯一索引的选择性为1，即索引选择性最好，性能最好。索引列判别查询，类似如下selectcount(distinctemail)/count(*)fromtb_user;selectcount(distinctsubstring(email,1,5))/cont(*)fromtb_user前缀索引的好处：使用prefixindex，定义好长度，可以节省空间而不增加太多额外的查询成本。前缀索引的缺点使用前缀索引没有使用覆盖索引来优化查询性能，这也是你在选择是否使用前缀索引时需要考虑的一个因素说到这里，我们简单总结一下：关于索引失效的原理而索引的使用，需要遵循大多数前缀匹配的原则，这是由B+树的索引模型决定的。另外，使用不当也会导致索引部分失效，比如范围查询、不带引号的字符串、使用索引列字段进行函数操作、当其中一个字段没有索引时使用Or查询条件等。内容很多，比较实用。虽然我提供了具体的例子，但还是希望大家自己动手做，这样大家记得更牢。下次见到面试官，直接扇他耳光。我是程序员，稀饭，关注我，我们一起在技术的海洋中向上成长。
