mysql单列索引与联合索引介绍

#mysql单列索引与联合索引介绍

-- 1.or两边一个单列索引，一个没索引，索引不生效()
EXPLAIN
SELECT * 
FROM me_sku
WHERE sku_name_code = 'JCXCMFT' OR product_id = '7016497'

-- 2.or两边都是单列索引，两个索引生效(多个or，多个单列索引均生效),只要有一个条件不是索引就不走索引
EXPLAIN
SELECT * 
FROM me_sku
WHERE sku_name_code = 'JCXCMFT' OR original_product_code = '1012561000181'
OR spec = '2只'
OR package_unit = '21383'
-- 3.AND两边都是单列索引，两个索引只有一个生效(多个and多个单列索引也只有一个索引生效)
EXPLAIN
SELECT * 
FROM me_sku
WHERE sku_name_code = 'JCXCMFT' AND original_product_code = '1012561000181'
AND spec = '2只'
AND package_unit = '21383'
-- 4.or两边联合索引，用or不启用索引()
EXPLAIN
SELECT * 
FROM me_sku
WHERE sku_code = 'Q5190007' OR spu_code = '1208631'

ALTER TABLE me_sku DROP COLUMN sku_alias_text
-- 5.数据量较小的时候，or两边都是单列索引，也不启用索引(数据量大起作用)
EXPLAIN
SELECT * FROM orderitem
WHERE pid = '35' OR oid = '7455381656944af79b00568f1663984e'

-- 6.大于号小于号(视实际情况确定是否启用索引)
EXPLAIN
SELECT *
FROM me_sku
WHERE  standard_lib_id < 5020595
AND update_user = 'zhongtai_test6'
AND quality_standard = 'Q/JYW003-2018'