创建索引三种方法
1、使用crate关键字,sap_queue_data是表名,biz_type是字段,index_biz_type 是索引名
CREATE index index_biz_type on sap_queue_data(biz_type)
2、使用alter关键字,sap_tr_item是表名,index_tr_item 是索引名称,tr_item是字段名称
ALTER TABLE `sap_tr_item` ADD INDEX index_tr_item ( `tr_item` )
3、手动创建,Ctrl+d打开需要添加索引的表,找到索引tab,点击添加索引
查看索引有没有用到
使用关键字explain
EXPLAIN SELECT
*
FROM
sap_tr_item
WHERE
del_flag = 0
AND create_time BETWEEN '2021-08-01 00:00:00'
AND '2022-09-30 00:00:00';
这里显示all,说明索引没起作用,原因是如果查询结果的数量约小于总数量的1/5,那么索引会有效;2.如果查询结果的数量超过1/5,那么走全表扫描,索引生效,加个limit,索引就生效了
EXPLAIN SELECT
*
FROM
sap_tr_item
WHERE
del_flag = 0
AND create_time BETWEEN '2021-08-01 00:00:00'
AND '2022-09-30 00:00:00'
LIMIT 300;
多表连接添加索引
今天刚学到,在关联的字段都加上索引会大大提升查询速度
SELECT
m.tr_item material_code,
sum( o.change_num ) change_num,
sum( m.quantity ) quantity
FROM
sap_tr_item m
LEFT JOIN sap_queue_data q ON q.id = m.head_id
LEFT JOIN nidec_out_stock_change_bill o ON o.nidec_component_requirement_detail_id = m.line_num
WHERE
m.del_flag = 0
AND q.del_flag = 0
AND q.biz_type IN ( 10, 20, 40, 41, 50, 51, 60, 70 )
AND m.create_time BETWEEN '2021-08-01 00:00:00'
AND '2022-09-30 00:00:00'
GROUP BY
m.tr_item
这里是没加索引的查询速度22.194s
这里是添加索引之后的查询速度,0.627s
主表sap_tr_item表的索引
sap_queue_data 表的索引
nidec_out_stock_change_bill 表的索引
这里m表的索引没有起作用,为啥呢,没排查出来,初步猜测是因为查询数据量大于1/5