MySQL中如何使用多列索引，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

多列索引

我们经常听到一些人说"把WHERE条件里的列都加上索引"，其实这个建议非常错误。在多个列上建立单独的索引大部分情况下并不能提高MySQL的查询性能。MySQL在5.0之后引入了一种叫“索引合并”(index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。但是当服务器对多个索引做联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上，特别是当其中有些索引的选择性不高，需要合并扫描大量的数据的时候。

这个时候，我们需要一个多列索引。

案例

创建一个测试数据库和数据表：

CREATEDATABASEIFNOTEXISTSdb_testdefaultcharsetutf8COLLATEutf8_general_ci;usedb_test;CREATETABLEpayment(idINTUNSIGNEDNOTNULLAUTO_INCREMENT,staff_idINTUNSIGNEDNOTNULL,customer_idINTUNSIGNEDNOTNULL,PRIMARYKEY(id))ENGINE=InnoDBDEFAULTCHARSET=utf8;

插入1000w行随机数据(利用存储过程):

DROPPROCEDUREIFEXISTSadd_payment;DELIMITER//createPROCEDUREadd_payment(innumINT)BEGINDECLARErowidINTDEFAULT0;SET@exesql='INSERTINTOpayment(staff_id,customer_id)values(?,?)';WHILErowid<numDOSET@staff_id=(1+FLOOR(5000*RAND()));SET@customer_id=(1+FLOOR(500000*RAND()));SETrowid=rowid+1;preparestmtFROM@exesql;EXECUTEstmtUSING@staff_id,@customer_id;ENDWHILE;END//DELIMITER;

或者你可以直接下载使用我的测试数据(也是利用上面的存储过程,但是我之后调整了数据):

测试数据

添加两个单列索引(执行过程要花点时间,建议分开一句一句执行):

ALTERTABLE`payment`ADDINDEXidx_customer_id(`customer_id`);ALTERTABLE`payment`ADDINDEXidx_staff_id(`staff_id`);

查询一条数据利用到两个列的索引:

selectcount(*)frompaymentwherestaff_id=2205ANDcustomer_id=93112;

查看执行计划:

mysql>explainselectcount(*)frompaymentwherestaff_id=2205ANDcustomer_id=93112;+----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|+----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+|1|SIMPLE|payment|index_merge|idx_customer_id,idx_staff_id|idx_staff_id,idx_customer_id|4,4|NULL|11711|Usingintersect(idx_staff_id,idx_customer_id);Usingwhere;Usingindex|+----+-------------+---------+-------------+------------------------------+------------------------------+---------+------+-------+-------------------------------------------------------------------------+1rowinset(0.00sec)

可以看到type是index_merge,Extra中提示Using intersect(idx_staff_id,idx_customer_id);

这便是索引合并,利用两个索引,然后合并两个结果(取交集或者并集或者两者都有)

查询结果:

mysql>selectcount(*)frompaymentwherestaff_id=2205ANDcustomer_id=93112;+----------+|count(*)|+----------+|178770|+----------+1rowinset(0.12sec)

然后删除以上索引,添加多列索引:

ALTERTABLEpaymentDROPINDEXidx_customer_id;ALTERTABLEpaymentDROPINDEXidx_staff_id;ALTERTABLE`payment`ADDINDEXidx_customer_id_staff_id(`customer_id`,`staff_id`);

注意,多列索引很关注索引列的顺序(因为customer_id的选择性更大,所以把它放前面)

查询:

mysql>selectcount(*)frompaymentwherestaff_id=2205ANDcustomer_id=93112;+----------+|count(*)|+----------+|178770|+----------+1rowinset(0.05sec)

发现多列索引加快的查询(这里数据量还是较小,更大的时候比较更明显)

注意

多列索引的列顺序至关重要，如何选择索引的列顺序有一个经验法则:将选择性***的列放到索引最前列(但是不是绝对的)。经验法则考虑全局的基数和选择性，而不是某个具体的查询：

mysql>selectcount(DISTINCTstaff_id)/count(*)ASstaff_id_selectivity,count(DISTINCTcustomer_id)/count(*)AScustomer_id_selectivity,count(*)frompayment\G;***************************1.row***************************staff_id_selectivity:0.0005customer_id_selectivity:0.0500count(*):100000001rowinset(6.29sec)

customer_id的选择性更高，所以将它作为索引列的***位。

多列索引只能匹配最左前缀，也就是说：

select*frompaymentwherestaff_id=2205ANDcustomer_id=93112;selectcount(*)frompaymentwherecustomer_id=93112;

可以利用索引，但是

select*frompaymentwherestaff_id=2205;

看完上述内容，你们掌握MySQL中如何使用多列索引的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！