MySQL索引重点小总结

x33g5p2x  于2021-10-02 转载在 Mysql  
字(3.8k)|赞(0)|评价(0)|浏览(298)

索引注意事项

  1. 全值匹配我最爱
  2. 最左前缀,中间不能断
  3. 不在索引列上做任何操作(计算,函数,类型转换),否则会导致索引失效而进行全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列,即范围右边索引全部失效,不包括当前范围列索引
  5. 尽量使用覆盖索引,只访问索引的查询(索引列和查询列一致),减少select/*
  6. mysql在使用!=或者<>的时候无法使用索引,会导致全表扫描
  7. is null,is not null也无法使用索引
  8. like以通配符开头(’%adc’)mysql索引失效
  9. 字符串不加单引号索引失效(隐式类型转换)
  10. 少用or,用它来连接时,索引会失效

覆盖索引

1、如果当前查询的索引或者复合索引包含所有需要查询的字段的值,我们称之为覆盖索引
2、不是所有类型的索引都可以称为覆盖索引,覆盖索引必须要存储索引列的值
3、不同的存储实现覆盖索引的方式不同,不是所有的引擎都支持覆盖索引,memory不支持覆盖索引

优势

1、索引条目通常远小于数据行大小,如果只需要读取索引,那么mysql就会极大的较少数据访问量
2、因为索引是按照列值顺序存储的,所以对于IO密集型的范围查询会比随机从磁盘读取每一行数据的IO要少的多
3、一些存储引擎如MYISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用,这可能会导致严重的性能问题

案例

1、当发起一个被索引覆盖的查询时,在explain的extra列可以看到using index的信息,此时就使用了覆盖索引

mysql> explain select store_id,film_id from inventory\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_store_id_film_id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.01 sec)

2、在大多数存储引擎中,覆盖索引只能覆盖那些只访问索引中部分列的查询。不过,可以进一步的进行优化,可以使用innodb的二级索引来覆盖查询。

例如:actor使用innodb存储引擎,并在last_name字段又二级索引,虽然该索引的列不包括主键actor_id,但也能够用于对actor_id做覆盖查询

mysql> explain select actor_id,last_name from actor where last_name='HOPPER'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ref
possible_keys: idx_actor_last_name
          key: idx_actor_last_name
      key_len: 137
          ref: const
         rows: 2
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

MySQL的索引是怎么加速查询的?

MySQL的索引是怎么加速查询的?

B+ Trees

mysql中, ,order by ,group by对联合索引的使用情况

如果对 a和b分别建立索引则 仅仅会走a索引或b索引;如果以a,b顺序建立联合索引,则会走联合索引,性能更高

为什么a和b分别建立索引时不能2个索引都用上,可能很多人也不知道为什么,其实道理很简单,每个索引在数据库中都是一个索引树,其数据节点存储了指向实际

数据的指针,如果用一个索引来查询,其原理就是从索引树上去检索,并获得这些指针,然后去取出数据,试想,如果你通过一个索引,得到过滤后的指针,这时,你的另一个条件索引如果再过滤一遍,将得到2组指针的集合,如果这时候取交集,未必就很快,因为如果每个集合都很大的话,取交集的时候,等于扫描两个集合,效率会很低,所以没法用2个索引。

Mysql本身支持两种方式的排序,FileSort 和 Index,Index效率高,指的是MySQL扫描索引本身完成排序操作,FileSort方式效率低

ORDER BY满足两种情况,会使用Index方式排序:

  • ORDER BY语句使用索引最左前列
  • 使用Where子句与Order by子句条件列组合满足索引最左前列和中间不可断原则

区间查询是否会走索引?

如果是(a,b,c)联合索引,条件如 a=‘a’ and b>‘b’ order by c,那么此时仅仅a,b字段走索引,c字段不会走索引。

order by子句是否会走索引?

a)当order by 字段出现在where条件中时,会利用索引而无需排序操作。

b) where条件+orderby刚好是连续的部分联合索引,order by才会走索引

c)当where条件+orderby虽然是联合索引时,但是条件时范围的时候,此时只有范围会走联合索引,order by不走索引

联合索引对范围查询的支持

范围查询指的是使用了in,like “ab%”,between and ,><的字段。

如果(a,b,c)是联合索引,where a=‘a’ and b>‘b’ and c=‘c’,那么c实际不会走索引。即联合索引中生效的只有a和b字段

mysql order/group by过程解析,排序与索引,联合索引中的范围查询,大量数据带条件的分页

where对索引的支持

1、只有where的情况,遵从最左原则,条件必须有左边的字段,才会用到索引,中间如果断开了,则都不会用到后面的索引,

例子: where c1 = ‘1’ and c2 = ‘1’ and c4 = ‘1’,这种情况因为c3没有在条件中,所以只会用到c1,c2索引。

使用范围条件的时候,范围后索引失效

例子: where c1 = ‘1’ and c2 > ‘1’ and c3 = ‘1’,这种情况从c2处已经断开,会使用到c1,c2索引,不会再使用到后面的c3,c4索引。

2、group by和order by 其实一样,也是遵从最左原则

Mysql优化_ORDER BY和GROUP BY (单路排序和双路排序)

Mysql优化_ORDER BY和GROUP BY (单路排序和双路排序)

为排序使用索引—order by 和 group by

MYSQL两种排序方式:文件排序和扫描有序索引排序

Mysql能为排序和查询使用相同的索引。也就是创建索引先把数据排序了,查询的时候再利用索引,一举两得。

KEY a_b_c(a,b,c)
order by 能使用索引最左前缀

ORDER BY a
ORDER BY a,b
ORDER BY a,b,c
ORDER BY a DESC,b DESC,c DESC

如果where使用缩印的最左前缀定义为常量,则order by 能使用索引

WHERE a = const ORDER BY b,c
WHERE a = const AND b = const ORDER BY c
WHERE a = const AND b > const ORDER BY b,c

不适用索引进行排序

ORDER BY a ASC,b DESC, c DESC //排序不一致
WHERE g = const ORDER BY b,c //丢失a索引
WHERE a = const ORDER BY c //丢失b索引
WHERE a = const ORDER BY a,d //d不是索引的一部分
WHERE a in (....) ORDER BY b,c //对于排序来说,多个相等条件也是范围

GROUP BY的优化,和order by基本一致:

GROUP BY实质上是先排序后进行分组,遵照索引的最佳左前缀。

当无法使用索隐裂,考虑增大max_length_for_sort_data和sort_buffer_size的参数设置。

WHERE 高于 HAVING,能写在WHERE限定的条件就不要去HAVING限定了。

小表驱动大表

优化原则: 小表驱动大表,即小的数据集驱动大的数据集

select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id=B.id

当B表的数据集小于A表的数据集时,用in优于exists

select * from A where exists (select 1 from B where B.id=A.id)
等价于:
for select * from A
for select * from B where B.id=A.id

当A表的数据集小于B表的数据集时,用exists优于in

注意: A表与B表的id字段应该建立索引

上面的语法可以理解为: 将主查询的数据,放到子查询中做条件验证,根据验证结果(true or false)来决定主查询的数据结构是否保留

  • EXISTS(subquery)只返回TRUE or FALSE ,因此子查询中的SELECT /* 也可以是 SELECT 1或者其他
  • EXISTS子查询的实际执行过程可以用条件表达式,其他子查询或者JOIN来替代,何种最优需要具体问题具体分析

相关文章