MySQL中覆盖索引怎么用

2025-03-15 技术教程

这篇文章主要为大家展示了“MySQL中覆盖索引怎么用”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL中覆盖索引怎么用”这篇文章吧。

查看测试表结构：

mysql>showcreatetableim_message\G***************************1.row***************************Table:im_messageCreateTable:CREATETABLE`im_message`(`id`int(11)NOTNULLAUTO_INCREMENT,`from_id`varchar(40)COLLATEutf8_binNOTNULL,`from_type`tinyint(1)NOTNULLDEFAULT'0',`to_id`varchar(40)COLLATEutf8_binNOTNULL,`to_type`tinyint(1)NOTNULLDEFAULT'0',`content`varchar(2048)COLLATEutf8_binDEFAULT'',`create_date`bigint(20)NOTNULL,`update_date`bigint(20)NOTNULL,`message_id`varchar(40)COLLATEutf8_binNOTNULL,`is_sync`tinyint(1)DEFAULT'0'COMMENT'是否同步0未同步1已同步',`is_read`tinyint(1)DEFAULT'0'COMMENT'是否已读0未读1已读',`is_withdraw`tinyint(1)DEFAULT'0'COMMENT'是否撤回0未撤1已撤',`is_lastest`tinyint(1)DEFAULT'0'COMMENT'是否是最新回话消息0不是1是',PRIMARYKEY(`id`),UNIQUEKEY`uidx_message_id`(`message_id`),KEY`idx_date`(`create_date`),KEY`idx_from_id`(`from_id`),KEY`idx_to_id`(`to_id`),KEY`idx_is_sync`(`is_sync`),KEY`idx_update_date`(`update_date`),KEY`idx_fid_tid`(`from_id`,`to_id`))ENGINE=InnoDBAUTO_INCREMENT=13264365DEFAULTCHARSET=utf8COLLATE=utf8_binROW_FORMAT=DYNAMIC1rowinset(0.00sec)

查看SQL执行效果：

selectmax(id)as"id"fromim_messageWHEREfrom_id='bd29879279c44672a2fdffcb8428b4d1'orto_id='bd29879279c44672a2fdffcb8428b4d1'groupbyfrom_id,to_id；|8547247||7152294||6897129||12874034||10011290||8027198||7852741||9960496||6059399||10860981||9963172||13253445|+----------+27827rowsinset(0.91sec)

查看执行计划：

mysql> explain select max(id) as "id" from im_message WHERE from_id='bd29879279c44672a2fdffcb8428b4d1' or to_id='bd29879279c44672a2fdffcb8428b4d1' group by from_id, to_id;

+----+-------------+------------+------------+-------------+-----------------------------------+-----------------------+---------+------+--------+----------+---------------------------------------------------------------------------------------+

1 row in set, 1 warning (0.00 sec)

创建覆盖索引：

mysql>altertableim_messageaddindexidx_from_id_id(from_id,id);QueryOK,0rowsaffected(1min1.94sec)Records:0Duplicates:0Warnings:0mysql>altertableim_messageaddindexidx_to_id_id(to_id,id);QueryOK,0rowsaffected(1min9.79sec)Records:0Duplicates:0Warnings:0

重新查看SQL执行效果：

selectmax(id)as"id"fromim_messageWHEREfrom_id='bd29879279c44672a2fdffcb8428b4d1'orto_id='bd29879279c44672a2fdffcb8428b4d1'groupbyfrom_id,to_id;|8547247||7152294||6897129||12874034||10011290||8027198||7852741||9960496||6059399||10860981||9963172||13253445|+----------+27827rowsinset(0.63sec)

查看执行计划：

mysql>explainselectmax(id)as"id"fromim_messageWHEREfrom_id='bd29879279c44672a2fdffcb8428b4d1'orto_id='bd29879279c44672a2fdffcb8428b4d1'groupbyfrom_id,to_id;+----+-------------+------------+------------+-------------+---------------------------------------------------------------+-----------------------------+---------+------+--------+----------+----------------------------------------------------------------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+------------+------------+-------------+---------------------------------------------------------------+-----------------------------+---------+------+--------+----------+----------------------------------------------------------------------------------------+|1|SIMPLE|im_message|NULL|index_merge|idx_from_id,idx_to_id,idx_fid_tid,idx_from_id_id,idx_to_id_id|idx_from_id_id,idx_to_id_id|122,122|NULL|162106|100.00|Usingunion(idx_from_id_id,idx_to_id_id);Usingwhere;Usingtemporary;Usingfilesort|+----+-------------+------------+------------+-------------+---------------------------------------------------------------+-----------------------------+---------+------+--------+----------+----------------------------------------------------------------------------------------+1rowinset,1warning(0.00sec)

发现优化器选择了新建的两个覆盖索引。

创建覆盖索引之后，利用索引的有序性，select max(id)可以快速的取到最大id。

以上是“MySQL中覆盖索引怎么用”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！