MySQL五种索引类型

sql执行顺序

（根据sql执行顺序来优化，可以把关联表判断条件放到on后面）
from  
on  
join  
where  
group by  
having  
select  
distinct  
union  
order by   
limit

一、哪些字段适合建索引

MySQL表索引和表分区 mysql分区表索引失效_数据库

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

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

1、范围查询失效问题

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

  • 主键一定走索引
  • 其他范围查询 >, >=, <, <=, like, !=, <>, is null, is not null虽然可以走索引，但是当查询数量超过了总数据的某个百分比，并且为二级索引，并且查询字段不止id和索引还包含了其他字段也会走全表查询，因为mysql的sql优化器会认为回表查询的随机I/O代价大于顺序I/O全表扫的情况下，将放弃走索引。
    如下这个sql是不走索引的，并且查询时间为7.337s，数据量为3557003


    如果我们强制他走索引，这时候去执行sql时，查询时间用了39.680s，数据量依然为3557003


    由上可见这里不用索引查询更快，因为通过索引查询会有一个回表操作

建议索引 select * from 表名 use index(索引名) where …
强制索引 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不是索引字段）

MySQL表索引和表分区 mysql分区表索引失效_数据库_02

MySQL表索引和表分区 mysql分区表索引失效_数据库索引_03

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

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

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

  • 和模糊查询类似，当数据量超过某个百分比后会放弃索引，通常情况下在这些条件查询的数据量不大还是会走索引的
  • NOT IN 和 NOT EXISTS 区别，not in会过滤掉null的数据，而exists不会，所以在用not in时要注意

MySQL表索引和表分区 mysql分区表索引失效_MySQL表索引和表分区_04


MySQL表索引和表分区 mysql分区表索引失效_MySQL表索引和表分区_05

注意：
exists 只检查存在性，出现多个一样的他只匹配一次
in 后面出现多个一样的他是会每个匹配一次
所以，可以的话尽量用exists

网上说的大表套小表用in，小表套大表用exists，因为exists后面会关联查询多次，经验证不存在这种问题情况，从下面执行计划中可以看出是先执行 SELECT 1 from om_obd_unbinding c WHERE required_order_code = 'OBD202210311122456538422' 因为这个sql查询的数据量刚好是1，然后在结果集的临时表中去获取最终结果。 这种情况用in或是exists都一样效果，区别无非是我上面说的相同数据匹配一次或多次区别！

MySQL表索引和表分区 mysql分区表索引失效_数据库索引_06


MySQL表索引和表分区 mysql分区表索引失效_数据库_07

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

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

MySQL表索引和表分区 mysql分区表索引失效_sql_08


MySQL表索引和表分区 mysql分区表索引失效_数据库_09

10、子查询引起索引失效

子查询有三种方式：
1、跟在select后面，会很慢（不建议）
2、跟在join 后面，会创建临时表
3、跟在 where 和 on后面

当WHERE后面跟子查询，

  • 用等号连接子查询会走索引

MySQL表索引和表分区 mysql分区表索引失效_数据库_10

  • 用 in 连接子查询不会走索引

MySQL表索引和表分区 mysql分区表索引失效_mysql_11

11、sql优化方案

可以根据以上导致索引失效、以及sql执行顺序来针对sql进行优化