【MySQL哪些字段适合建索引，哪些查询条件会导致索引失效】

MySQL五种索引类型

一、哪些字段适合建索引

1.对应重复率非常高的字段不要建索引，如state，type等

2.尽量不要使用复合索引，如果两个字段重复率都不高，两个字段分别见索引

3.如果确实需要复合索引，重复率低的放前面，如（orde_id,seq）订单号和序列

4.一般情况外键必须建立索引

5.能建唯一索引的必须建唯一索引,提升性能

6.不要设计过多的索引，这样会影响查询效率

二、范围查询索引失效问题

范围查询索引失效，只针对二级索引（除了主键，其他索引都是二级索引）
(1)、如果走二级索引查询，步骤就是：二级索引–>主键索引–>数据，这就是回表查询（mysql索引中关联的主键，而不是数据，所以二级索引查询会有一个回表的操作）。
(2)、硬盘随机I/O的性能远低于顺序I/O。

1、!= 和 <>失效问题

• 主键一定走索引
• 二级索引正常情况下不走索引，因为走索引效率不如全表；但如果!=过滤数据量超过总数据某个百分比的时候会走索引，因为这个时候sql优化器会认为走索引代价低于全表查询

2、其他范围查询失效问题

注意，放弃索引走全表查询需要满足以下几点：
1、二级索引
2、范围查询
3、查询数量超过了总数据的某个百分比
4、并且查询字段不止id和索引还包含了其他字段（select后面字段不仅仅为id和where后端索引字段）

• 其他范围查询 > >= < <= like虽然可以走索引，但是当查询数量超过了总数据的某个百分比，并且为二级索引，并且查询字段不止id和索引还包含了其他字段也会走全表查询，因为mysql的sql优化器会认为回表查询的随机I/O代价大于顺序I/O全表扫的情况下，将放弃走索引。
  如下这个sql是不走索引的，并且查询时间为7.337s，数据量为3557003
  
  
  如果我们强制他走索引，这时候去执行sql时，查询时间用了39.680s，数据量依然为3557003
  
  
  由上可见这里不用索引查询更快，因为通过索引查询会有一个回表操作

强制索引 select * from 表名 force index(索引名) where …
忽略索引 select * from 表名 ignore index(索引名) where …

2、左前缀原则

• 使用联合索引(id,name,age)查询数据,判断条件需遵循最左原则

3、函数导致索引失效

• SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’;
  create_time字段设置索引，那就无法使用函数，否则索引失效，因为函数会破坏索引有序性，sql优化器会放弃走索引。

4、字段类型不一致索引失效

• SELECT * FROM user WHERE height= 175;
  height为varchar类型导致索引失效，尤其多张表时注意。

5、运算符导致索引失效

• 如果你对列进行了（+，-，*，/，!）, 那么都将不会走索引。

  SELECT * FROM user WHERE age - 1 = 20;

6、or引起索引失效

• or两边必须都是索引，否则用or连接的左右语句会索引失效

例：（counter1 和 counter2是索引，counter5不是索引字段）

7、模糊查询导致索引失效

• SELECT * FROM user WHERE name LIKE ‘%冰’;
  模糊搜索如果你前缀也进行模糊搜索，那么不会走索引。

8、IN、NOT IN、 EXISTS、NOT EXISTS导致索引失效

• 和模糊查询类似，当数据量超过某个百分比后会放弃索引，通常情况下在这些条件查询的数据量不大还是会走索引的
  

9、IS NULL，IS NOT NULL索引失效问题

• is null 会走索引，跟数据量没关系，猜想跟等值符号一个逻辑
• is not null 跟返回数据量有关，返回数据量少则走索引，大于总数据某个百分比不走索引