在MySQL中,可以通过指定查询使用的索引来提高查询性能和优化查询执行计划。以下是一些常见的场景,可能需要考虑指定查询使用的索引:
- 查询性能问题:当一个查询在大表上运行缓慢时,可以考虑为该查询指定合适的索引。通过观察查询执行计划和分析查询语句,可以确定是否存在索引缺失或不合理使用的情况。
- 覆盖索引:当一个查询只需要访问索引中的数据而无需访问表的实际数据时,可以考虑使用覆盖索引。通过指定查询使用的索引,可以避免查询过程中对表中的数据进行额外的读取和IO操作,从而提高查询性能。
- 强制索引使用:有时候,MySQL优化器可能会选择不同于我们期望的索引来执行查询。在这种情况下,可以使用
FORCE INDEX
或USE INDEX
提示来强制MySQL使用特定的索引。这在某些特殊情况下可能会用到,但应谨慎使用,应先验证索引的效果。 - 查询优化:有些查询可能有多个有效的索引可用,但使用不同的索引可能导致查询性能的差异。在这种情况下,可以通过指定查询使用的索引来优化查询,找到最佳的索引策略,提高查询性能。
需要指定查询使用的索引时,可以使用以下方法:
- 在查询语句中使用
USE INDEX
提示,指定使用的索引名称或索引列表。 - 在查询语句中使用
FORCE INDEX
提示,强制使用指定的索引。 - 使用
FORCE INDEX FOR JOIN
来强制MySQL在连接操作中使用指定的索引。
表结构如下:
create table student(
id bigint primary key auto_increment,
name varchar(20),
phone varchar(11),
status char(1) default '1'
)
因为name字段经常被用来查询,所以需要给name创建索引:
create index idx_student_name on student(name);
查看当前表有哪些索引:
show index from student;
查询时指定使用哪个索引:
语法:use index(索引名)
select name,phone,status from student use index(idx_student_name) where name='小明'
举个多表查询,强制使用索引的例子
SELECT
sop.GROUP_ID groupId,
sop.DEPARTMENT_ID departmentId,
sotd.ORDER_SALECLASS_ID orderSaleclassId,
IFNULL(sotd.PAID_PRICE,0)+IFNULL(sotd.COUPON_DISC_AMT,0) paidPrice,
sotd.PRICE_ID priceId,
sot.ORDER_SOURCE orderSource,
sotd.SHOW_ID showId,
sotd.id detailId
FROM
xxx_order_pay_t sop force index (IDX_PAY_TIME)
left join xxx_order_ticket_t sot force index (idx_sot_payid) on sop.ID = sot.PAY_ID
left join xxx_order_ticket_detail_t sotd on sotd.ORDER_ID = sot.ID
WHERE
sop.IS_DEL = '0'
AND sot.IS_DEL = '0'
AND sot.ORDER_TYPE!='123'
AND sop.PAY_STATUS = '123'
AND sotd.IS_RETURN_TICKET = '0'
AND sotd.IS_DEL = '0'
AND (sot.GROUP_ID=1234 OR sot.THEATRE_ID=1234)
AND sot.GROUP_ID in(1,2,3,4,5)
AND (sop.DEPARTMENT_ID not in (11,12,16,18,20,21,22,23)
or sop.DEPARTMENT_ID is null)
AND sop.PAY_TIME >='2024-05-11 00:00:00'
AND sop.PAY_TIME <='2024-05-11 23:59:59'
其中,IDX_PAY_TIME是xxx_order_pay_t表的索引,idx_sot_payid是xxx_order_ticket_t表的索引。