MySQL SQL优化二

建立联合索引：

ALTER TABLE tb_item_01 ADD INDEX idx_title_num_sp(title,num,sell_point);

EXPLAIN
SELECT * FROM tb_item_01 WHERE title = '阿尔卡特 (OT-979) 冰川白 联通3G手机';

结论：使用索引。

EXPLAIN
SELECT * FROM tb_item_01 WHERE title = '阿尔卡特 (OT-979) 冰川白 联通3G手机' AND num = 99999;
EXPLAIN
SELECT * FROM tb_item_01 WHERE title = '阿尔卡特 (OT-979) 冰川白 联通3G手机' AND num = 99999 AND sell_point = '清仓！仅上海仓有货！';

结论：使用索引。

 

EXPLAIN
SELECT * FROM tb_item_01 WHERE num = 99999 AND sell_point = '清仓！仅上海仓有货！';

 

EXPLAIN
SELECT * FROM tb_item_01 WHERE sell_point = '清仓！仅上海仓有货！';

 

结论：没有使用索引。 

如果索引为多列，从最左开始，第一个字段必须要有，才能使用到索引，否则无法使用到索引。

 

那如果我们使用第一个字段与第三个字段作为查询条件。

EXPLAIN
SELECT * FROM tb_item_01 WHERE title = '阿尔卡特 (OT-979) 冰川白 联通3G手机' AND sell_point = '清仓！仅上海仓有货！';

结论：可以看到key_len= 302，推断出只使用了第一个字段作为索引，第三个字段作为索引失效。所以第二个字段也是需要的。

这样我们如上总结出：最左前缀法则，索引从最左字段开始，且不跳过中间字段。

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN
SELECT * FROM tb_item_01 WHERE LEFT(title,24) = '阿尔卡特 (OT-979) 冰川白 联通3G手机';

 结论：索引列上，不要使用函数。

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN
SELECT title,num,sell_point FROM tb_item_01 WHERE title != '阿尔卡特 (OT-979) 冰川白 联通3G手机' AND num >= 99999 AND sell_point = '清仓！仅上海仓有货！';

 

结论：使用 !、<>会导致索引失效。 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN
SELECT title,num,sell_point FROM tb_item_01 WHERE title IS NOT NULL AND num >= 99999 AND sell_point = '清仓！仅上海仓有货！';

 

结论：is null 或者 is not null 也会导致索引失效。 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN
SELECT title,num,sell_point FROM tb_item_01 WHERE title LIKE '%阿尔卡特 (OT-979) 冰川白 联通3G手机%' AND num = 99999 AND sell_point = '清仓！仅上海仓有货！';

 

EXPLAIN
SELECT title,num,sell_point FROM tb_item_01 WHERE title LIKE '阿尔卡特 (OT-979) 冰川白 联通3G手机%' AND num = 99999 AND sell_point = '清仓！仅上海仓有货！';

结论：左边%会导致索引失效。右边%不会导致索引失效。 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

EXPLAIN
SELECT * FROM tb_item_01 WHERE title = 100 AND num = 99999 AND sell_point = '清仓！仅上海仓有货！';

 

结论：varchar类型如果不加单引号 ，会使索引失效。

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

未实现结论（可能是由于mysql版本问题）：少用or。少用select *，尽量使用覆盖索引。