mysql索引查询

#在常用的链接类型中：system > const > eq_ref > ref > range > index > all

创建组合索引

create index idx_name_phone on user_innodb(name,phone)
#索引
explain select * from user_innodb where name=‘123’ and phone=13434465
#最左匹配原则 去掉name条件索引全部失效
explain select * from user_innodb where name = ‘123’ and phone=13434465
– 去掉phone name 索引生效
explain select * from user_innodb where name =‘123’
– 顺序错乱不会影响最左匹配原则
explain select * from user_innodb where phone=13434465 and name =‘123’

– 不在索引列是哪个左任何操作
– 在name字段上 加上去空格(TRIM)的函数 索引失效
explain select * from user_innodb where TRIM(name)=‘123’ and phone = 134334465

– 范围查找对索引,没有影响
explain select * from user_innodb where name = ‘冯一’ and phone > ‘13600891214’

– mysql在使用不等于(!=或者<>)主键不受影响
EXPLAIN select * from user_innodb where id != 123
– is not null无法使用
explain select * from user_innodb where name is not null

– is null 可以使用索引
EXPLAIN select * from user_innodb where name is not null

– like只有前模糊能走索引
EXPLAIN select * from user_innodb where name like ‘1%’
– 没有索引
explain select * from user_innodb where name like ‘%1’
– 没有索引
explain select * from user_innodb where name like ‘%1%’

– 字符串不加引号索引失效
explain select * from user_innodb where name = 123
– 使用or索引会失效
explain select * from user_innodb where name =‘123’ or phone >‘13600891214’

– 尽量使用覆盖索引,避免使用select *,而是使用select列名
EXPLAIN select name from user_innodb where name =‘123’ or phone >‘13600891214’

– 大偏移量 limit
EXPLAIN select * from user_innodb limit 90000,10
– 改成先过滤id,在limit
EXPLAIN select * from user_innodb where id>=90000 LIMIT 10;

– 当A表数据多于B表中数据的时候,这是我们使用in优于Exists
– 当B表数据多于A表中的数据中,这时我们使用Exists优于in
– EXISTS子查询只返回true或false
– in 和exists查询
select * from A where id in (select id from B)
– exists
select * from A e where exists (select 1 from A d where d.id = e.id)