一条SQL要经过编译-->逻辑优化-->物理优化-->交给执行引擎 执行。
索引失效就是DBMS发现全表扫描的效率更高,那么显然不走索引了。
建立如下表结构和索引
1、使用!= 或者 <> 导致索引失效
SELECT a.* FROM coupon_state_manager a WHERE a.customer_id != 101
2、类型不一致导致的索引失效
字段varchar,条件为int,会导致失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_phone = 13756494953
字段int,条件字符型,不会导致索引失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_id = '101'
3、函数导致的索引失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE DATE(receive_date) = '2020-02-17 14:08:31'
4、运算符导致的索引失效
对列进行了(+,-,*,/,!), 那么都将不会走索引
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_id -1 = 100
5、OR引起的索引失效
or连接的字段,都建立了索引,不会失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_id = 100 or customer_name = '李四'
or连接的字段,如果其中一个没建立索引,则会是整个索引失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_id = 101 or coupon_id = 2
6、模糊搜索导致索引失效(并不是所有的模糊搜索都会导致索引失效)
'%abc' '%abc%'会导致索引失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_name LIKE '%李%'
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_name LIKE '%李'
'abc%'不会导致索引失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_name LIKE '李%'
7、NOT IN、NOT EXISTS导致索引失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_id NOT IN (100,101)
8、IS NOT NULL会导致索引失效
EXPLAIN SELECT * FROM coupon_state_manager WHERE customer_name IS NOT NULL
9、复合索引
重新设置数据库索引
最左匹配原则,如上图所示的复合索引,会建立如下的匹配,如果与where条件形成匹配,则索引生效(注:DBMS会将SQL优化,以最大可能的匹配索引,所以调换查询条件的顺序并不会影响索引的匹配)
1.customer_id
2.customer_id customer_name
3.customer_id customer_phone
4.customer_id customer_name customer_phone