丨索引如何提高查询的速度

如果你去过馆，应该会知道馆的检索系统。馆为准备了检索包括L中的索，就相当于馆的检索 ，它是帮助QL一种结构。我们可以在索引中按照查询条件，检索索引字段的值，然后快速定位数据记录的位置，这样就不需要遍历整个数据表了。而且，数据表中的字段越多，表中数据记录越多，速度提升越是明显。我来举个例子，进一步解释下索引的作用。这里要用到销售流水表（demo.tran| | |YES|MUL| | |YES | | | |YES | | |datetime|YES|MUL| | | |YES | | | |YES | |cashiernumber| |YES|MUL| | | |YES|MUL| |transuniqueid| |YES | 14 159rowsinset(0.02某个门店的销售流水表有400100代代12345678mysql>->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=99+|++++ty|price|transdate+++|1.000|220.00|2020-12-1219:45:36|1.000|220.00|2020-12-1208:56:37++++2rowsinset(808可以看到，结果总共有28关联，这只是单表的查询，而且只是一个门店几个月的数据而已。而总部是把所有门店的数据都汇总到一起，查询速度更慢，这样的查询效率，我们肯定是不能接受的。MySQL支持单字段索引和组合索引，而单字段索引比较常用，我们先来学习下创建单字段创建单字段索引，一般有3CREATE多代代1CREATEINDEX索引名ONTABLE表名(字段代代3456CREATETABLE(字段数据类型{INDEX|KEY}索引名(字段)代代1ALTERTABLE表名ADD{INDEX|KEY}索引名(字段这里有个小问题要提醒你一下，给表设定主键约束或者唯一性约束的时候，L创建主键索引或唯一性索引。这也是我建议你在创建表的时候，一定要定义主键的原因之举个小例子，我们可以给表demo.trans代代123456789mysql>CREATEINDEXindex_transONdemo.trans(transdate(10));QueryOK,0rowsaffected(1min8.71sec)Records:0Duplicates:0Warnings:mysql>->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=+|++++ty|price|transdate+++|1.000|220.00|2020-12-1219:45:36|1.000|220.00|2020-12-1208:56:37 2rowsinset(0.300.320要知道索引是怎么起作用的，我们需要借助MySQL中的 IN这个关键字 INSQL代代123456789mysql>IN->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=+++++++|id|select_type|table|partitions|type|possible_keys|key|+++++++|1|SIMPLE|trans|NULL|range|index_trans|index_trans|6|NULL|+++++++1rowinset,1warning(0.00type=range：表示使用索引查询特定范围的数据记录。rows=5411：表示需要的记录数。possible_keys=index_trans：表示可以选index_transkey=index_trans：表示实际选择的索引是extra=Usingindexcondition;Usingwhere;UsingMRR：这里面的信息对SQL语句的执行细节做了进一步的解释，包含了3层含义：第一个是执行时使用了索引，第二个是执行时通过WHERE条件进行了筛选，第三个是使用了顺序磁盘的策略。通过这个小例子，我们可以发现，有了索引之后，MySQLSQL在刚刚的查询中，我们是选择transdate（时间）字段来当索引字段，你可能会问，为啥不选别的字段呢？这是因为，时间是查询条件。MySQL可以按照时间的限定“20201212不过，你有没有想过，itemnumberitemnumber代代mysql>CREATEINDEXindex_trans_itemnumberONdemo.transQueryOK,0rowsaffected(43.88Records:0Duplicates:0Warnings:代代123456789mysql>->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=-- +|+++--对商品编号的筛选，可以在itemnumber+ty|price|transdate+++|1.000|220.00|2020-12-1219:45:36|1.000|220.00|2020-12-1208:56:37++++2rowsinset(0.38itemnumber这是为啥呢？我们来看看MySQL代代123456789mysql>IN->->->->transdate>'2020-12-->ANDtransdate<'2020-12-->ANDitemnumber=--对itemnumber++++++|id|select_type|table|partitions|type|possible_keys|key|++++++|1|SIMPLE|trans|NULL|ref|index_trans,index_trans_itemnumber|++++++1rowinset,1warning(0.01我们发现，“possible_keys=index_trans,index_trans_itemnumber”，就是说MySQL认为可以选择的索引确实有2个，一个是用transdate字段创建的索引index_trans，itemnumberindex_trans_itemnumber。key=index_trans_itemnumber，说明MySQL实际选择使用的索引是itemnumber字段创建的索引index_trans_itemnumber。而rows=1192，就表示实际的数据记录数只有1192个，比用transdate创建的索引index_trans的实际记录数要少，这就是MySQL选择使用itemnumber索引的原因。所以，我建议你在选择索引字段的时候，要选择那些经常被用做筛选条件的字段能发挥索引的作用，提升检索的效率。在实际工作中，有时会遇到比较复杂的数据表，这种表包括的字段比较多，经常需要通过不同的字段筛选数据，特别是数据表中包含多个层级信息。比如我们的销售流水表就包含了门店信息、收款机信息和商品信息这3个收款机对应多个从这台收款机销售出去的商品。我们经常要把这些层次信息作为筛选条件，来进行查询。这个时候单字段的索引往往不容易发挥出索引的最大功效，可以使用组branchnumbercashiernumber代代1234567mysql>CREATEINDEXindex_trans_branchnumberONdemo.trans(branchnumber);QueryOK,0rowsaffected(41.49sec)Records:0Duplicates:0Warnings:mysql>CREATEINDEXindex_trans_cashiernumberONdemo.trans(cashiernumber);QueryOK,0rowsaffected(41.95sec)Records:0Duplicates:0Warnings:11|100|1.000|220.00|2020-07-1109:18:3512|100|1.000|220.00|2020-09-0621:21:5813|100|1.000|220.00|2020-11-1015:00:11|100|1.000|220.00|2020-12-2514:28:06|100|1.000|220.00|2021-01-0920:21:44|100|1.000|220.00|2021-02-0810:45:0517 186rowsinset(0.3160.31代代123mysql>IN->->4456789->->->branchnumber=11ANDcashiernumber=->ANDitemnumber=++++++|id|select_type|table|partitions|type|possible_keys|key|++++++|1|SIMPLE|trans|NULL|ref|++++++1rowinset,1warning(0.01MySQL有3个索引可以用，分别是用branchnumber创建的index_trans_branchnumbercashiernumberindex_trans_cashiernumber和用itemnumber创建的index_trans_itemnumber。最后，MySQLindex_trans_itemnumber1192，花费了0.31秒。MySQL3个字段的索引。MySQL最多支持由16个字段组成的组合索引。代代1CREATEINDEX索引名ONTABLE表名(字段1，字段代代CREATETABLE(字段数据类型{INDEX|KEY}索引名(字段1，字段)代代1ALTERTABLE表名ADD{INDEX|KEY}索引名(字段1，字段3branchnumber、cashiernumber、itemnumber组成的组合索引，如下所示：代代mysql>CREATEINDEXIndex_branchnumber_cashiernumber_itemnumberONQueryOK,0rowsaffected(59.26Records:0Duplicates:0Warnings:代代123456789mysql>->->->->->branchnumber=11ANDcashiernumber=->ANDitemnumber=+++++|itemnumberty|price|transdate+++++|100|1.000|220.00|2020-07-1109:18:35|||||+100|1.000|220.00|2020-09-0621:21:58100|1.000|220.00|2020-11-1015:00:11100|1.000|220.00|2020-12-2514:28:06100|1.000|220.00|2021-01-0920:21:44100|1.000|220.00|2021-02-0810:45:05++++6rowsinset(0.00几乎是瞬间就完成了，不超过10毫秒。我们看看MySQL代代123456789mysql>IN->->->->--->branchnumber=11ANDcashiernumber=->ANDitemnumber=++++++|id|select_type|table|partitions|type|possible_keys|key|++++++|1|SIMPLE|trans|NULL|ref|++++++1rowinset,1warning(0.01这个查询，MySQL4index_trans_itemnumber；MySQL选择了组合索引，筛选后的记录只有6条。组合索引被充分利用，筛选更加组合索引的多个字段是有序的，遵循左对齐的原则branchnbrrmber和nmber。因此，筛选的条件也要遵循从左向右的原则，如果中断，那么，断点后面的条件就没有办法利用索引了。比如说我们刚才的条件，branchnumber=11ANDcashiernumber=1ANDitemnumber100，包含了从左到右的所有字段，所以可以最大限度使用全部组合索假如把条件换成“cashiernumber=1ANDitemnumber=100”，由于我们的组合索引是按照branchnumber、cashiernumber和itemnumber的顺序建立的，最左边的字段branchnumber没有包含到条件当中，中断了，所以这个条件完全不能使用组合索引。如“branchnumber>10ANDcashiernumber=1ANDitemnumber=100”这个条件，只能用到组合索引中branchnumber>10的部分，后面的索引就都用不上了。我们来看看MySQL的运行计划：代代123456789mysql>IN->->->->->branchnumber>10A